mysql数据库的基本操作
1.简介
MySQL 是最流行的关系型数据库管理系统,在 WEB 应用方面 MySQL 是最好的 RDBMS(关系数据库管理系统)应用软件之一
什么是数据库?
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。
所以,现在我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。
所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
RDBMS 即关系数据库管理系统的特点:
- 1.数据以表格的形式出现
- 2.每行为各种记录名称
- 3.每列为记录名称所对应的数据域
- 4.许多的行和列组成一张表单
- 5.若干的表单组成database
RDBMS 术语
在我们开始学习MySQL 数据库前,让我们先了解下RDBMS的一些术语:
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- 行: 一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
- 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键: 外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
- 索引: 使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性。
MySQL 为关系型数据库, 这种所谓的”关系型”可以理解为”表格”的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格:
- 表头(header): 每一列的名称;
- 列(col): 具有相同数据类型的数据的集合;
- 行(row): 每一行用来描述某条记录的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 键的值在当前列中具有唯一性。
MySQL数据库
MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。
MySQL 是一种关联数据库管理系统,关联数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
- MySQL 是开源的,目前隶属于 Oracle 旗下产品。
- MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
- MySQL 使用标准的 SQL 数据语言形式。
- MySQL 可以运行于多个系统上,并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
- MySQL 对 PHP 有很好的支持,PHP 是很适合用于 Web 程序开发。
- MySQL 支持大型数据库,支持 5000 万条记录的数据仓库,32 位系统表文件最大可支持 4GB,64 位系统支持最大的表文件为8TB。
- MySQL 是可以定制的,采用了 GPL 协议,你可以修改源码来开发自己的 MySQL 系统。
2.MYSQL数据库的链接
MYSQL登录与退出
启动输入以下命令即可:
1 | service mysql stop/start |
1 | cd D:\phpstudy_pro\Extensions\MySQL5.7.26\bin |
输入
1 | mysql -u root -proot -P 3306 -h 127.0.0.1 |
通过 MySQL 自带的客户端工具登录到 MySQL 数据库中,输入以下格式的命名:
1 | mysql -h 主机名 -u 用户名 -p |
参数说明:
- -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
- -u : 登录的用户名;
- -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项。
- -P :指定登录主机的地址
如果我们要登录本机的 MySQL 数据库,只需要输入以下命令即可:
1 | mysql -u root -proot |
按回车确认, 如果安装正确且 MySQL 正在运行, 会得到以下响应:
1 | Enter password: |
若密码存在, 输入密码登录, 不存在则直接按回车登录。
登录成功后你将会看到 Welcome to the MySQL monitor… 的提示语。
退出的三种方法
1 | mysql > exit; |
3.常用命令
以下列出了使用Mysql数据库过程中常用的命令:
USE 数据库名:
选择要操作的Mysql数据库使用该命令后所有Mysql命令都只针对该数据库。
1
2mysql> use RUNOOB;
Database changedSHOW DATABASES:
列出 MySQL 数据库管理系统的数据库列表。1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| RUNOOB |
| cdcol |
| mysql |
| onethink |
| performance_schema |
| phpmyadmin |
| test |
| wecenter |
| wordpress |
+--------------------+
10 rows in set (0.02 sec)SHOW TABLES:
显示指定数据库的所有表使用该命令前需要使用 use 命令来选择要操作的数据库。
1
2
3
4
5
6
7
8
9
10
11mysql> use RUNOOB;
Database changed
mysql> SHOW TABLES;
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl |
| runoob_tbl |
| tcount_tbl |
+------------------+
3 rows in set (0.00 sec)SHOW COLUMNS FROM *数据表*:
显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。1
2
3
4
5
6
7
8
9
10mysql> SHOW COLUMNS FROM runoob_tbl;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id | int(11) | NO | PRI | NULL | |
| runoob_title | varchar(255) | YES | | NULL | |
| runoob_author | varchar(255) | YES | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
4.MySQL查看数据库
数据库可以看作是一个专门存储数据对象的容器,每一个数据库都有唯一的名称,并且数据库的名称都是有实际意义的,这样就可以清晰的看出每个数据库用来存放什么数据。在 MySQL 数据库中存在系统数据库和自定义数据库,系统数据库是在安装 MySQL 后系统自带的数据库,自定义数据库是由用户定义创建的数据库。
在 MySQL 中,可使用 SHOW DATABASES 语句来查看或显示当前用户权限范围以内的数据库。查看数据库的语法格式为:
1 | SHOW DATABASES [LIKE '数据库名']; |
语法说明如下:
- LIKE 从句是可选项,用于匹配指定的数据库名称。LIKE 从句可以部分匹配,也可以完全匹配。
- 数据库名由单引号
' '
包围。
实例1:查看所有数据库
列出当前用户可查看的所有数据库:
1 | mysql> SHOW DATABASES; |
可以发现,在上面的列表中有 6 个数据库,它们都是安装 MySQL 时系统自动创建的,其各自功能如下:
- information_schema:主要存储了系统中的一些数据库对象信息,比如用户表信息、列信息、权限信息、字符集信息和分区信息等。
- mysql:MySQL 的核心数据库,类似于 SQL Server 中的 master 表,主要负责存储数据库用户、用户访问权限等 MySQL 自己需要使用的控制和管理信息。常用的比如在 mysql 数据库的 user 表中修改 root 用户密码。
- performance_schema:主要用于收集数据库服务器性能参数。
- sakila:MySQL 提供的样例数据库,该数据库共有 16 张表,这些数据表都是比较常见的,在设计数据库时,可以参照这些样例数据表来快速完成所需的数据表。
- sys:MySQL 5.7 安装完成后会多一个 sys 数据库。sys 数据库主要提供了一些视图,数据都来自于 performation_schema,主要是让开发者和使用者更方便地查看性能问题。
- world:world 数据库是 MySQL 自动创建的数据库,该数据库中只包括 3 张数据表,分别保存城市,国家和国家使用的语言等内容。
实例2:创建并查看数据库
先创建一个名为 test_db 的数据库:
mysql> CREATE DATABASE test_db;
Query OK, 1 row affected (0.12 sec)
再使用 SHOW DATABASES 语句显示权限范围内的所有数据库名,如下所示:
1 | mysql> SHOW DATABASES; |
你看,刚才创建的数据库已经被显示出来了。
实例3:使用 LIKE 从句
先创建三个数据库,名字分别为 test_db、db_test、db_test_db。
\1) 使用 LIKE 从句,查看与 test_db 完全匹配的数据库:
1 | mysql> SHOW DATABASES LIKE 'test_db'; |
\2) 使用 LIKE 从句,查看名字中包含 test 的数据库:
1 | mysql> SHOW DATABASES LIKE '%test%'; |
\3) 使用 LIKE 从句,查看名字以 db 开头的数据库:
1 | mysql> SHOW DATABASES LIKE 'db%'; |
\4) 使用 LIKE 从句,查看名字以 db 结尾的数据库:
1 | mysql> SHOW DATABASES LIKE '%db'; |
4.MySQL 创建数据库
我们可以在登陆 MySQL 服务后,使用 create 命令创建数据库,语法如下:
1 | CREATE DATABASE 数据库名; |
以下命令简单的演示了创建数据库的过程,数据名为 RUNOOB:
1 | mysql> create DATABASE RUNOOB; |
可以使用
1 | CREATE DATABASE IF NOT EXISTS RUNOOB; |
创建数据库,该命令的作用:
1 | 如果数据库不存在则创建,存在则不创建。 |
可以使用SHOW CREATE DATABASE
查看数据库的定义声明,发现该数据库的指定字符集为 utf8
运行结果如下所示:
1 | mysql> SHOW CREATE DATABASE test_db_char; |
“1 row in set (0.00 sec)”表示集合中有 1 行信息,处理时间为 0.00秒。时间为 0.00 秒并不代表没有花费时间,而是时间非常短,小于 0.01 秒。
5.MySQL 删除数据库
使用普通用户登陆 MySQL 服务器,你可能需要特定的权限来创建或者删除 MySQL 数据库
所以我们这边使用 root 用户登录,root 用户拥有最高权限。
在删除数据库过程中,务必要十分谨慎,因为在执行删除命令后,所有数据将会消失。
Drop 命令删除数据库
drop 命令格式:
1 | drop database <数据库名>; |
例如删除名为 RUNOOB 的数据库:
1 | mysql> drop database RUNOOB; |
6.MySQL 选择数据库
在你连接到 MySQL 数据库后,可能有多个可以操作的数据库,所以你需要选择你要操作的数据库。
在 mysql> 提示窗口中可以很简单的选择特定的数据库。你可以使用SQL命令来选择指定的数据库。
以下实例选取了数据库 RUNOOB:
1 | mysql> use RUNOOB |
执行以上命令后,你就已经成功选择了 RUNOOB 数据库
在后续的操作中都会在 RUNOOB 数据库中执行
7.MySQL 修改数据库
校对规则进行修改,数据库的这些特性都储存在 db.opt 文件中。下面我们来介绍一下修改数据库的基本操作。
在 MySQL 中,可以使用 ALTER DATABASE 来修改已经被创建或者存在的数据库的相关参数。修改数据库的语法格式为:
1 | ALTER DATABASE [数据库名] { [ DEFAULT ] CHARACTER SET <字符集名> |[ DEFAULT ] COLLATE <校对规则名>} |
语法说明如下:
- ALTER DATABASE 用于更改数据库的全局特性。
- 使用 ALTER DATABASE 需要获得数据库 ALTER 权限。
- 数据库名称可以忽略,此时语句对应于默认数据库。
- CHARACTER SET 子句用于更改默认的数据库字符集。
查看 test_db 数据库的定义声明的执行结果如下所示:
1 | mysql> SHOW CREATE DATABASE test_db; |
使用命令行工具将数据库 test_db 的指定字符集修改为 gb2312,默认校对规则修改为 gb2312_unicode_ci,输入 SQL 语句与执行结果如下所示:
1 | mysql> ALTER DATABASE test_db |
7.MySQL 创建数据表
创建MySQL数据表需要以下信息:
- 表名
- 表字段名
- 定义每个表字段
以下为创建MySQL数据表的SQL通用语法:
1 | 在 MySQL 中,可以使用 CREATE TABLE 语句创建表。其语法格式为: |
以下为创建数据表 runoob_tbl 实例:
1 | mysql> use RUNOOB; |
注意:MySQL命令终止符为分号 ; 。
注意: -> 是换行符标识,不要复制。
8.MySQL 删除数据表
MySQL中删除数据表是非常容易操作的,但是你在进行删除表操作时要非常小心,因为执行删除命令后所有数据都会消失。
以下为删除MySQL数据表的通用语法:
1 | DROP TABLE table_name ; |
以下实例删除了数据表runoob_tbl:
1 | mysql> use RUNOOB; |
9.MySQL 插入数据
MySQL 表中使用 INSERT INTO SQL语句来插入数据。
你可以通过 mysql> 命令提示窗口中向数据表中插入数据,或者通过PHP脚本来插入数据。
以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法:
1 | INSERT INTO table_name ( field1, field2,...fieldN ) |
如果数据是字符型,必须使用单引号或者双引号,如:”value”。
以下我们将使用 SQL INSERT INTO 语句向 MySQL 数据表 runoob_tbl 插入数据
以下实例中我们将向 runoob_tbl 表插入三条数据:
1 | mysql> use RUNOOB; |
注意:
使用箭头标记 -> 不是 SQL 语句的一部分,它仅仅表示一个新行,如果一条SQL语句太长,我们可以通过回车键来创建一个新行来编写 SQL 语句,SQL 语句的命令结束符为分号 ;
在以上实例中,我们并没有提供 runoob_id 的数据,因为该字段我们在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性。 所以,该字段会自动递增而不需要我们去设置。实例中 NOW() 是一个 MySQL 函数,该函数返回日期和时间。
接下来我们可以通过以下语句查看数据表数据:
读取数据表:
1 | select * from runoob_tbl; |
输出结果:
10.MySQL 查询数据
MySQL 数据库使用SQL SELECT语句来查询数据。
你可以通过 mysql> 命令提示窗口中在数据库中查询数据,或者通过PHP脚本来查询数据。
以下为在MySQL数据库中查询数据通用的 SELECT 语法:
1 | SELECT column_name,column_name FROM table_name |
- 查询语句中你可以使用一个或者多个表,表之间使用逗号(,)分割,并使用WHERE语句来设定查询条件。
- SELECT 命令可以读取一条或者多条记录。
- 你可以使用星号(*)来代替其他字段,SELECT语句会返回表的所有字段数据
- 你可以使用 WHERE 语句来包含任何条件。
- 你可以使用 LIMIT 属性来设定返回的记录数。
- 你可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
以下实例我们将通过 SQL SELECT 命令来获取 MySQL 数据表 runoob_tbl 的数据:
以下实例将返回数据表 runoob_tbl 的所有记录:
1 | select * from runoob_tbl; |
输出结果:
11.MySQL WHERE 子句
我们知道从 MySQL 表中使用 SQL SELECT 语句来读取数据。
如需有条件地从表中选取数据,可将 WHERE 子句添加到 SELECT 语句中。
以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法:
1 | SELECT field1, field2,...fieldN FROM table_name1, table_name2... |
- 查询语句中你可以使用一个或者多个表,表之间使用逗号, 分割,并使用WHERE语句来设定查询条件。
- 你可以在 WHERE 子句中指定任何条件。
- 你可以使用 AND 或者 OR 指定一个或多个条件。
- WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的 if 条件,根据 MySQL 表中的字段值来读取指定的数据。
以下实例将读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录:
SQL SELECT WHERE 子句
1 | SELECT * from runoob_tbl WHERE runoob_author='菜鸟教程'; |
输出结果:
MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
12.基本操作
1. 数据库的增删改查
1 | 增:create database db1; |
2.查看表结构
1 | desc 表名; |
3.查看表的内容
1 | select * from runoob_tbl; |
4.查看指定的数据库
1 | show create database db1; |
5.选择数据库
1 | use db1 |
6.显示一些系统特定资源的信息
例如,正在运行的线程数量。
1 | show status; |
7.查看当前库里面所有的表
1 | show tables; |
8.查看当前数据库 里面的表user 用户为root的密码
1 | select password from user where user='root'; |
9.建立表
完整的建立表的语句
1 | create table users |
1 | mysql> create table users |
1 | CREATE TABLE 表名 |
字段名就是属性名
10.约束条件
1 | PRIMARY KEY 标识该属性为该表的主键,可以唯一的标识对应的元组 |
13.数据的增删改查
为表中所有字段添加数据
语法:INSERT INTO 表名(字段名1,字段名2,…)VALUES(值1,值2,…);
1 | mysql> insert into users (id,username,password)values(1,'moon','123456'); |
1.1. 增
语法:INSERT INTO 表名(字段1,字段2,…)VALUES(值1,值2,…)
1 | mysql> insert into users values(null,'test','123456'); |
同时添加多条数据
语法:INSERT INTO 表名[(字段名1,字段名2,…)]VALUES (值1,值2,…),(值1,值2,…),
1 | mysql> insert into users (id,username,password)values(null,'moon','123456'),(null,'alex','123456'); |
INSERT INTO 表名[(字段名1,字段名2,…)]VALUES (值1,值2,…),(值1,值2,…),(值1,值2,…)
1 | mysql> insert into sb values(null,'moon1','123456'), |
1.2. 删
DELETE FROM 表名 [WHERE 条件表达式]
1 | delete from sb where id=1; |
删除全部数据
若 DELETE 语句中没有使用WHERE语句,则会将表中所有记录都删除。
1 | DELETE FROM 表名 |
1.3. 更新
语法:UPDATE 表名 SET 字段名1=值1,[ ,字段名2=值2,…][ WHERE 条件表达式 ]
1 |
|
1.4. 查
1 | select * from sb; |
星号代表所有的字段
查询指定的字段
1 | select username,password from users; |
按条件查询
语法:SELECT 字段名1,字段名2,…FROM 表名WHERE 条件表达式
带 BETWEEN AND 关键字的查询
1 | select * from sb where id not between 1 and 10; |
带 DISTINCT 关键字的查询
1 | select distinct username from sb; |
like查询 一般都会给跟着%
1 | select * from users where username like "%m%" ; |
下划线 _ 匹配一个字符
1 | select* from users where username like "moo_"; |
and查询
满足多个条件
1 | select * from sb where id=2 and username='test'; |
or查询
满足任意一个条件
1 | select * from sb where id=1 or username='moon'; |
OR 和 AND 一起使用的情况
OR 和 AND 一起使用的时候,AND 的优先级高于 OR,因此二者一起使用时,会先运算 AND 两边的表达式,再运算 OR 两边的表达式。
1 | mysql> select * from sb where id >5 and password='123456c' or username='moon1'; |