Mysql - 基础篇
mysql - 基础篇
本博客是根据黑马程序员 MySQL数据库入门到精通学习时所做的笔记
一、概述
主要介绍两个部分，数据库相关概念及MySQL数据库的介绍、下载、安装、启动及连接。
1.1数据库相关概念
三个概念：数据库 、数据库管理系统 、SQL 。
| 名称 | 全称 | 简称 |
|---|---|---|
| 数据库 | 存储数据的仓库，数据是有组织的进行存储 | DataBase（DB） |
| 数据库管理系统 | 操纵和管理数据库的大型软件 | DataBase Management System (DBMS) |
| SQL | 操作关系型数据库的编程语言，定义了一套操作 | Structured Query |
目前主流的关系型数据库管理系统的市场占有率排名如下：
- Oracle：大型的收费数据库，Oracle公司产品，价格昂贵。
- MySQL：开源免费的中小型数据库，后来Sun公司收购了MySQL，而Oracle又收购了Sun公司。目前Oracle推出了收费版本的MySQL，也提供了免费的社区版本。SQL Server：Microsoft 公司推出的收费的中型数据库，C#、.net等语言常用。
- PostgreSQL：开源免费的中小型数据库。
- DB2：IBM公司的大型收费数据库产品。
- SQLLite：嵌入式的微型数据库。Android内置的数据库采用的就是该数据库。
- MariaDB：开源免费的中小型数据库。是MySQL数据库的另外一个分支、另外一个衍生产品，与 MySQL数据库有很好的兼容性。
而不论我们使用的是上面的哪一个关系型数据库，最终在操作时，都是使用SQL语言来进行统一操作， 因为我们前面讲到SQL语言，是操作关系型数据库的 统一标准 。所以即使我们现在学习的是MySQL， 假如我们以后到了公司，使用的是别的关系型数据库，如：Oracle、DB2、SQLServer，也完全不用 担心，因为操作的方式都是一致的。
1.2 MySQL数据库
1.2.1 版本
MySQL官方提供了两种不同的版本：
- 社区版本（MySQL Community Server）免费， MySQL不提供任何技术支持
商业版本（MySQL Enterprise Edition）
- 收费，可以使用30天，官方提供技术支持
1.2.2 下载
下载地址：https://downloads.mysql.com/archives/installer/
1.2.3 安装
要想使用MySQL，我们首先先得将MySQL安装好，我们可以根据下面的步骤，一步一步的完成MySQL的安装。
- 双击官方下来的安装包文件
mysql-installer-community-8.0.26.0.msi
根据安装提示进行安装
安装MySQL的相关组件，这个过程可能需要耗时几分钟，耐心等待。
输入MySQL中root用户的密码,一定记得记住该密码配置
安装好MySQL之后，还需要配置环境变量，这样才可以在任何目录下连接MySQL。
- 在此电脑上，右键选择属性
点击左侧的 “高级系统设置”，选择环境变量
找到 Path 系统变量, 点击 “编辑”
选择 “新建” , 将MySQL Server的安装目录下的bin目录添加到环境变量
1.2.4 启动停止
MySQL安装完成之后，在系统启动时，会自动启动MySQL服务，我们无需手动启动了。
当然，也可以手动的通过指令启动停止，以管理员身份运行cmd，进入命令行执行如下指令：
1 | net start mysql80 |
注意 ： 上述的 mysql80 是我们在安装MySQL时，默认指定的mysql的系统服务名，不是固
定的，如果未改动，默认就是mysql80。
1.2.5 客户端连接
方式一：使用MySQL提供的客户端命令行工具
方式二：使用系统自带的命令行工具执行指令
1 | mysql [-h 127.0.0.1] [-P 3306] -u root -p |
[] 内为可选参数，如果需要连接远程的MySQL，需要加上这两个参数来指定远程主机IP、端口，如果
连接本地的MySQL，则无需指定这两个参数。
注意： 使用这种方式进行连接时，需要安装完毕后配置PATH环境变量。
二、SQL
2.1 SQL通用语法
在了解具体的SQL语句之前，先来了解一下SQL语言的通用语法。
- SQL语句可以单行或多行书写，以分号结尾。
- SQL语句可以使用空格/缩进来增强语句的可读性。
- MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
- 注释：
- 单行注释：– 注释内容 或 # 注释内容
- 多行注释：/* 注释内容 */
2.2 SQL 分类
SQL语句，根据其功能，主要分为四类：DDL、DML、DQL、DCL。
| 分类 | 全称 | 说明 |
|---|---|---|
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象(数据库，表， 字段) |
| DML | Data Manipulation Language | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的 |
2.3 DDL
Data Definition Language，数据定义语言，用来定义数据库对象(数据库，表，字段) 。
2.3.1 数据库操作
查询所有数据库
1
show databases ;
查询当前数据库
1
select database() ;
创建数据库
1
create database [ if not exists ] 数据库名 [ default charset 字符集 ] [ collate 排序 规则 ] ;
删除数据库
1 | drop database [ if exists ] 数据库名 ; |
如果删除一个不存在的数据库，将会报错。此时，可以加上参数 if exists ，如果数据库存在，再执行删除，否则不执行删除。
切换数据库
1
use 数据库名 ;
2.3.2 表操作
2.3.2.1 表操作 - 查询创建
- 查询当前数据库所有表
1 | show tables; |
比如,我们可以切换到sys这个系统数据库,并查看系统数据库中的所有表结构。
1 | use sys; |
查看指定表结构
1
desc 表名 ;
通过这条指令，我们可以查看到指定表的字段，字段的类型、是否可以为NULL，是否存在默认值等信息。
查询指定表的建表语句
1 | show create table 表名 ; |
通过这条指令，主要是用来查看建表语句的，而有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如：存储引擎、字符集等
创建表结构
1
2
3
4
5
6
7CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ],
字段2 字段2类型 [COMMENT 字段2注释 ],
字段3 字段3类型 [COMMENT 字段3注释 ],
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) [ COMMENT 表注释 ] ;注意: […] 内为可选参数，最后一个字段后面没有逗号
2.3.2.2 表操作 - 数据类型
MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间 类型。
数值类型
类型 大小 有符号(SIGNED)范围 无符号(UNSIGNED)范围 描述 TINYINT 1byte (-128，127) (0，255) 小整数值 SMALLINT 2bytes (-32768，32767) (0，65535) 大整数值 MEDIUMINT 3bytes (-8388608，8388607) (0，16777215) 大整数值 INT/INTEGER 4bytes (-2147483648，2147483647) (0，4294967295) 大整数值 BIGINT 8bytes (-2^63^，2^63^-1) (0，2^64^-1) 极大整数值 FLOAT 4bytes (-3.402823466 E+38，3.402823466351 E+38) 0 和 (1.175494351 E-38，3.402823466 E+38) 单精度浮点数值 DOUBLE 8bytes (-1.7976931348623157E+308，1.7976931348623157E+308) 0 和(2.2250738585072014E-308，1.7976931348623157E+308) 双精度浮点数值 DECIMAL 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值 小数值(精确定点数) 1
2
3
4
5如:
1). 年龄字段 -- 不会出现负数, 而且人的年龄不会太大
age tinyint unsigned
2). 分数 -- 总分100分, 最多出现一位小数
score double(4,1)字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串(需要指定长度) |
| VARCHAR | 0-65535 bytes | 变长字符串(需要指定长度) |
| TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
char 与 varchar 都可以描述字符串，char是定长字符串，指定长度多长，就占用多少个字符，和字段值的长度无关 。而varchar是变长字符串，指定的长度为最大占用长度 。相对来说，char的性能会更高些。
1 | 如： |
- 日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值，时间戳 |
1 | 如: |
2.3.2.3 表操作 - 案例
设计一张员工信息表，要求如下：
- 编号（纯数字）
- 员工工号 (字符串类型，长度不超过10位)
- 员工姓名（字符串类型，长度不超过10位）
- 性别（男/女，存储一个汉字）
- 年龄（正常人年龄，不可能存储负数）
- 身份证号（二代身份证号均为18位，身份证中有X这样的字符）
- 入职时间（取值年月日即可）
对应的建表语句如下:
1 | create table emp( |
SQL语句编写完毕之后，就可以在MySQL的命令行中执行SQL，然后也可以通过 desc 指令查询表结构信息：
表结构创建好了，里面的name字段是varchar类型，最大长度为10，也就意味着如果超过10将会报错，如果我们想修改这个字段的类型 或 修改字段的长度该如何操作呢？接下来再来讲解DDL语句中， 如何操作表字段。
2.3.2.4 表操作 - 修改
- 添加字段
1 | ALTER TABLE 表名 ADD 字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ]; |
案例: 为 emp 表增加一个新的字段”昵称”为nickname，类型为varchar(20)；
1 | ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称'; 1 |
修改数据类型
1
ALTER TABLE 表名 MODIFY 字段名 新数据类型 (长度);
修改字段名和字段类型
1
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型 (长度) [ COMMENT 注释 ] [ 约束 ];
删除字段
1
ALTER TABLE 表名 DROP 字段名;
修改表名
1 | ALTER TABLE 表名 RENAME TO 新表名; |
2.3.2.5 表操作 - 删除
删除表
1
DROP TABLE [ IF EXISTS ] 表名;
删除指定表, 并重新创建表
1
TRUNCATE TABLE 表名;
注意: 在删除表的时候，表中的全部数据也都会被删除。
2.4 DML
2.4.1 添加数据
- 给指定字段添加数据
1 | INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...); |
给全部字段添加数据
1
INSERT INTO 表名 VALUES (值1, 值2, ...);
批量添加数据
1
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值 1, 值2, ...) ;
1
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...) ;
注意事项:
• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
• 字符串和日期型数据应该包含在引号中。
• 插入的数据大小，应该在字段的规定范围内。
2.4.2 修改数据
修改数据的具体语法为:
1 | UPDATE 表名 SET 字段名1 = 值1 , 字段名2 = 值2 , .... [ WHERE 条件 ] ; |
注意事项: 修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。
2.4.3 删除数据
删除数据的具体语法为：
1 | DELETE FROM 表名 [ WHERE 条件 ] ; |
注意事项:
• DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
• DELETE 语句不能删除某一个字段的值(可以使用UPDATE，将该字段值置为NULL即可)。
2.5 DQL
DQL英文全称是Data Query Language(数据查询语言)，数据查询语言，用来查询数据库中表的记录。
查询关键字: SELECT
在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。
数据准备：
1 | drop table if exists employee; |
2.5.1 基本语法
DQL 查询语句，语法结构如下：
1 | SELECT 字段列表 FROM 表名列表 WHERE 条件列表 GROUP BY 分组字段列表 HAVING 分组后条件列表 ORDER BY 排序字段列表 LIMIT 分页参数 |
在讲解这部分内容的时候，会将上面的完整语法进行拆分，分为以下几个部分：
- 基本查询（不带任何条件）
- 条件查询（WHERE）
- 聚合函数（count、max、min、avg、sum）
- 分组查询（group by）
- 排序查询（order by）
- 分页查询（limit）
2.5.2 基础查询
在基本查询的DQL语句中，不带任何的查询条件，查询的语法如下：
查询多个字段
1
SELECT 字段1, 字段2, 字段3 ... FROM 表名 ;
1
SELECT * FROM 表名 ;
注意 : * 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）。
字段设置别名
1
SELECT 字段1 [ AS 别名1 ] , 字段2 [ AS 别名2 ] ... FROM 表名;
1
SELECT 字段1 [ 别名1 ] , 字段2 [ 别名2 ] ... FROM 表名;
去除重复记录
1
SELECT DISTINCT 字段列表 FROM 表名;
案例：
- 查询指定字段 name, workno, age并返回
1 | select name,workno,age from emp; |
- 查询返回所有字段
1 | select id ,workno,name,gender,age,idcard,workaddress,entrydate from emp; |
- 查询所有员工的工作地址,起别名
1 | select workaddress as '工作地址' from emp; |
- 查询公司员工的上班地址有哪些(不要重复)
1 | select distinct workaddress '工作地址' from emp; |
2.5.3 条件查询
语法
1
SELECT 字段列表 FROM 表名 WHERE 条件列表 ;
条件
常用的比较运算符如下:
比较运算符 功能 > 大于 >= 大于等于 < 小于 <= 小于等于 = 等于 <> 或 != 不等于 BETWEEN … AND … 在某个范围之内(含最小、最大值) IN(…) 在in之后的列表中的值，多选一 LIKE 占位符 模糊匹配(_匹配单个字符, %匹配任意个字符) IS NULL 是NULL 常用的逻辑运算符如下:
逻辑运算符 功能 AND 或 && 并且 (多个条件同时成立) OR 或 或者 (多个条件任意一个成立) NOT 或 ! 非 , 不是 案例:
A. 查询年龄等于 88 的员工
1
select * from emp where age = 88;
B. 查询年龄小于 20 的员工信息
1
select * from emp where age < 20;
C. 查询年龄小于等于 20 的员工信息
1
select * from emp where age <= 20;
D. 查询没有身份证号的员工信息
1
select * from emp where idcard is null;
E. 查询有身份证号的员工信息
1
select * from emp where idcard is not null;
F. 查询年龄不等于 88 的员工信息
1
2select * from emp where age != 88;
select * from emp where age <> 88;G. 查询年龄在15岁(包含) 到 20岁(包含)之间的员工信息
1
2
3select * from emp where age >= 15 && age <= 20;
select * from emp where age >= 15 and age <= 20;
select * from emp where age between 15 and 20;H. 查询性别为 女 且年龄小于 25岁的员工信息
1
select * from emp where gender = '女' and age < 25;
I. 查询年龄等于18 或 20 或 40 的员工信息
1
select * from emp where gender = '女' and age < 25;
J. 查询姓名为两个字的员工信息 _ %
1
select * from emp where name like '__';
K. 查询身份证号最后一位是X的员工信息
1
2select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
2.5.4 聚合函数
- 介绍
将一列数据作为一个整体，进行纵向计算 。
常见的聚合函数
函数 功能 count 统计数量 max 最大值 min 最小值 avg 平均值 sum 求和 语法
1
SELECT 聚合函数(字段列表) FROM 表名 ;
注意 : NULL值是不参与所有聚合函数运算的。
案例：
A. 统计该企业员工数量
1
2
3select count(*) from emp; -- 统计的是总记录数
select count(idcard) from emp; -- 统计的是idcard字段不为null的记录数对于count聚合函数，统计符合条件的总记录数，还可以通过 count(数字/字符串)的形式进行统计
查询，比如：
1
select count(1) from emp;
对于count(*) 、count(字段)、 count(1) 的具体原理，我们在进阶篇中SQL优化部分会详细讲解，此处只需要知道如何使用即可。
B. 统计该企业员工的平均年龄
1
select avg(age) from emp;
C. 统计该企业员工的最大年龄
1
select max(age) from emp;
D. 统计该企业员工的最小年龄
1
select min(age) from emp;
E. 统计西安地区员工的年龄之和
1
select sum(age) from emp where workaddress = '西安';
2.5.5 分组查询
语法
1
SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组 后过滤条件 ];
where与having区别
执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
判断条件不同：where不能对聚合函数进行判断，而having可以。
注意事项:
• 分组之后，查询的字段一般为
聚合函数和分组字段，查询其他字段无任何意义。• 执行顺序: where > 聚合函数 > having 。
• 支持多字段分组, 具体语法为 : group by columnA,columnB
2.5.6 排序查询
语法
1
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1 , 字段2 排序方式2 ;
排序方式
- ASC : 升序(默认值)
- DESC: 降序
注意事项：
- 如果是升序, 可以不指定排序方式ASC ;
- 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序 ;
- 和 group by 连用时 order by 的排序字段必须是 存在于分组字段中的；
2.5.7 分页查询
- 语法
1 | SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数 ; |
注意事项:
- 起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。
- 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。
- 如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。
2.5.8 执行顺序
2.6 DCL
DCL英文全称是Data Control Language(数据控制语言)，用来管理数据库用户、控制数据库的访问权限。
2.6.1 管理用户
- 查询用户
1 | select * from mysql.user; |
查询的结果如下:
其中 Host代表当前用户访问的主机, 如果为localhost, 仅代表只能够在当前本机访问，是不可以远程访问的。 User代表的是访问该数据库的用户名。在MySQL中需要通过Host和User来唯一标识一个用户。
- 创建用户
1 | CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码'; |
- 修改用户密码
1 | ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码' ; |
删除用户
1
DROP USER '用户名'@'主机名' ;
注意事项:
- 在MySQL中需要通过用户名@主机名的方式，来唯一标识一个用户。
- 主机名可以使用 % 通配。
- 这类SQL开发人员操作的比较少，主要是DBA（ Database Administrator 数据库 管理员）使用。
2.6.2 权限控制
MySQL中定义了很多种权限，但是常用的就以下几种：
| 权限 | 说明 |
|---|---|
| ALL, ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 修改数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
上述只是简单罗列了常见的几种权限描述，其他权限描述及含义，可以直接参考 官方文档
查询权限
1
SHOW GRANTS FOR '用户名'@'主机名' ;
授予权限
1
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
1
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意事项：
- 多个权限之间，使用逗号分隔
- 授权时， 数据库名和表名可以使用 * 进行通配，代表所有
三、函数
3.1 字符串函数
演示如下：
A. concat : 字符串拼接
1 | select concat('Hello' , ' MySQL'); |
B. lower : 全部转小写
1 | select lower('Hello'); |
C. upper : 全部转大写
1 | select upper('Hello'); |
D. lpad : 左填充
1 | select lpad('01', 5, '-'); |
E. rpad : 右填充
1 | select rpad('01', 5, '-'); |
F. trim : 去除空格
1 | select trim(' Hello MySQL '); |
G. substring : 截取子字符串
1 | select substring('Hello MySQL',1,5); |
3.2 数值函数
演示如下：
A. ceil：向上取整
1 | select ceil(1.1); |
B. floor：向下取整
1 | select floor(1.9); |
C. mod：取模
1 | select mod(7,4); |
D. rand：获取随机数
1 | select rand(); |
E. round：四舍五入
1 | select round(2.344,2); |
3.3 日期函数
常见的日期函数如下：
演示如下：
A. curdate：当前日期
1 | select curdate(); |
B. curtime：当前时间
1 | select curtime(); |
C. now：当前日期和时间
1 | select now(); |
D. YEAR , MONTH , DAY：当前年、月、日
1 | select YEAR(now()); |
E. date_add：增加指定的时间间隔
1 | select date_add(now(), INTERVAL 70 YEAR ); |
F. datediff：获取两个日期相差的天数
1 | select datediff('2021-10-01', '2021-12-01'); |
3.4 流程函数
演示如下：
A. if
1 | select if(false, 'Ok', 'Error'); |
B. ifnull
1 | select ifnull('Ok','Default'); |
C. case when then else end
需求: 查询emp表的员工姓名和工作地址 (北京/上海 —-> 一线城市 , 其他 —-> 二线城市)
1 | select name, |
四、约束
概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。
目的：保证数据库中数据的正确、有效性和完整性。
分类:
| 约束 | 描述 | 关键字 |
|---|---|---|
| 非空约束 | 限制该字段的数据不能为null | NOT NULL |
| 唯一约束 | 保证该字段的所有数据都是唯一、不重复的 | UNIQUE |
| 主键约束 | 主键是一行数据的唯一标识，要求非空且唯一 | PRIMARY KEY |
| 默认约束 | 保存数据时，如果未指定该字段的值，则采用默认值 | DEFAULT |
| 检查约束(8.0.16版本之后) | 保证字段值满足某一个条件 | CHECK |
| 外键约束 | 用来让两张表的数据之间建立连接，保证数据的一致 性和完整性 | FOREIGN KEY |
注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
4.2 约束演示
上面我们介绍了数据库中常见的约束，以及约束涉及到的关键字，那这些约束我们到底如何在创建表、修改表的时候来指定呢，接下来我们就通过一个案例，来演示一下。
1 | CREATE TABLE tb_user( |
在为字段添加约束时，我们只需要在字段之后加上约束的关键字即可，需要关注其语法。我们执行上面 的SQL把表结构创建完成，然后接下来，就可以通过一组数据进行测试，从而验证一下，约束是否可以生效。
4.3 外键约束
4.3.1 介绍
外键：用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性
左侧的emp表是员工表，里面存储员工的基本信息，包含员工的ID、姓名、年龄、职位、薪资、入职日期、上级主管ID、部门ID，在员工的信息中存储的是部门的ID dept_id，而这个部门的ID是关联的部门表dept的主键id，那emp表的dept_id就是外键,关联的是另一张表的主键。
注意：目前上述两张表，只是在逻辑上存在这样一层关系；在数据库层面，并未建立外键关联，所以是无法保证数据的一致性和完整性的。
4.3.2 语法
- 添加外键
1 | ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表 (主表列名) ; |
注意：
其他约束也可以 这样添加
- 删除外键
4.3.3 删除/更新行为
添加了外键之后，再删除父表数据时产生的约束行为，我们就称为删除/更新行为。具体的删除/更新行为有以下几种:
| 行为 | 说明 |
|---|---|
| NO ACTION | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 RESTRICT 一致) 默认行为 |
| RESTRICT | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许删除/更新。 (与 NO ACTION 一致) 默认行为 |
| CASCADE | 当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有，则也删除/更新外键在子表中的记录。 |
| SET NULL | 当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（这就要求该外键允许取null）。 |
| SET DEFAULT | 父表有变更时，子表将外键列设置成一个默认的值 (Innodb不支持) |
具体语法为:
1 | ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名 (主表字段名) ON UPDATE CASCADE ON DELETE CASCADE; |
五、多表查询
之前在讲解SQL语句的时候，讲解了DQL语句，也就是数据查询语句，但是之前讲解的查询都是单表查询，而本章节我们要了解的则是多表查询操作，主要从以下几个方面进行讲解。
5.1 多表关系
项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
- 一对多(多对一)
- 多对多
- 一对一
5.1.1 一对多
- 案例: 部门 与 员工的关系
- 关系: 一个部门对应多个员工，一个员工对应一个部门
- 实现: 在多的一方建立外键，指向一的一方的主键
5.1.2 多对多
- 案例: 学生 与 课程的关系
- 关系: 一个学生可以选修多门课程，一门课程也可以供多个学生选择
- 实现: 建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
5.1.3 一对一
- 案例: 用户 与 用户详情的关系
- 关系: 一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
- 实现: 在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)
5.2 多表查询概述
5.2.1 数据准备
- 删除之前 emp, dept表的测试数据
- 执行如下脚本，创建emp表与dept表并插入测试数据
1 | -- 创建dept表，并插入数据 |
dept表共6条记录，emp表共17条记录。
5.2.2 概述
多表查询就是指从多张表中查询数据。
原来查询单表数据，执行的SQL形式为：select * from emp; 那么我们要执行多表查询，就只需要使用逗号分隔多张表即可，如： select * from emp , dept ; 具体的执行结果如下:
此时,我们看到查询结果中包含了大量的结果集，总共102条记录，而这其实就是员工表emp所有的记录 (17) 与 部门表dept所有记录(6) 的所有组合情况，这种现象称之为笛卡尔积。接下来，就来简单介绍下笛卡尔积。
笛卡尔积: 笛卡尔乘积是指在数学中，两个集合 A集合 和 B集合的所有组合情况。
而在多表查询中，我们是需要消除无效的笛卡尔积的，只保留两张表关联部分的数据。
在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。
1 | select * from emp , dept where emp.dept_id = dept.id; |
而由于id为17的员工，没有dept_id字段值，所以在多表查询时，根据连接查询的条件并没有查询到。
5.2.3 分类
连接查询
- 内连接：相当于查询A、B交集部分数据
- 左外连接：查询左表所有数据，以及两张表交集部分数据
- 右外连接：查询右表所有数据，以及两张表交集部分数据
- 自连接：当前表与自身的连接查询，自连接必须使用表别名
子查询
5.3 内连接
内连接查询的是两张表交集部分的数据。(也就是绿色部分的数据)
内连接的语法分为两种: 隐式内连接 、显式内连接 。先来了解一下具体的语法结构。
隐式内连接
1
SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 ... ;
显式内连接
1
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ... ;
案例:
A. 查询每一个员工的姓名 , 及关联的部门的名称 (隐式内连接实现)
表结构: emp , dept
连接条件: emp.dept_id = dept.id
1 | select emp.name , dept.name from emp , dept where emp.dept_id = dept.id ; |
B. 查询每一个员工的姓名 , 及关联的部门的名称 (显式内连接实现) — INNER JOIN … ON …
表结构: emp , dept
连接条件: emp.dept_id = dept.id
1 | select e.name, d.name from emp e inner join dept d on e.dept_id = d.id; |
表的别名:
- tablea as 别名1 , tableb as 别名2 ;
- tablea 别名1 , tableb 别名2 ;
注意事项:
- SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 … ;
- SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 … ;
5.4 外连接
外连接分为两种，分别是：左外连接 和 右外连接。具体的语法结构为：
左外连接
1
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ... ;
左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。
右外连接
1
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ... ;
右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。
注意事项：
左外连接和右外连接是可以相互替换的，只需要调整在连接查询时SQL中，表结构的先后顺序就可以了。而我们在日常开发使用时，更偏向于左外连接。
5.5 自连接
5.5.1 自连接查询
自连接查询，顾名思义，就是自己连接自己，也就是把一张表连接查询多次。先来了解一下自连接的查询语法:
1 | SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ... ; |
而对于自连接查询，可以是内连接查询，也可以是外连接查询。
案例：
A. 查询员工 及其 所属领导的名字
表结构: emp
1 | select a.name , b.name from emp a , emp b where a.managerid = b.id; |
B. 查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来表结构: emp a , emp b
1 | select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id; |
注意事项:
在自连接查询中，必须要为表起别名，要不然我们不清楚所指定的条件、返回的字段，到底是哪一张表的字段。
5.5.2 联合查询
对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。
1 | SELECT 字段列表 FROM 表A ... |
- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。
注意：
如果多条查询语句查询出来的结果，字段数量不一致，在进行union/union all联合查询时，将会报错。
5.6 子查询
5.6.1 概述
- 概念
SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
1 | SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ); |
子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个
分类
根据子查询结果不同，分为：
- 标量子查询（子查询结果为单个值）
- 列子查询(子查询结果为一列)
- 行子查询(子查询结果为一行)
- 表子查询(子查询结果为多行多列)
根据子查询位置，分为：
- WHERE之后
- FROM之后
- SELECT之后
5.6.2 标量子查询
子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。常用的操作符：
= <> > >= < <=
案例:
A. 查询 “销售部” 的所有员工信息
完成这个需求时，我们可以将需求分解为两步：
- 查询 “销售部” 部门ID
1 | select id from dept where name = '销售部'; |
- 根据 “销售部” 部门ID, 查询员工信息
1 | select * from emp where dept_id = (select id from dept where name = '销售部'); |
5.6.3 列子查询
子查询返回的结果是一列（可以是多行），这种子查询称为列子查询。 常用的操作符：
IN 、NOT IN 、 ANY 、SOME 、 ALL
| 操作符 | 描述 |
|---|---|
| IN | 在指定的集合范围之内，多选一 |
| NOT IN | 不在指定的集合范围之内 |
| ANY | 子查询返回列表中，有任意一个满足即可 |
| SOME | 与ANY等同，使用SOME的地方都可以使用ANY |
| ALL | 子查询返回列表的所有值都必须满足 |
案例:
A. 查询 “销售部” 和 “市场部” 的所有员工信息
分解为以下两步:
- 查询 “销售部” 和 “市场部” 的部门ID
1 | select id from dept where name = '销售部' or name = '市场部'; |
- 根据部门ID, 查询员工信息
1 | select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部'); |
B. 查询比 财务部 所有人工资都高的员工信息
分解为以下两步:
- 查询所有 财务部 人员工资
1 | select id from dept where name = '财务部'; |
- 比 财务部 所有人工资都高的员工信息
1 | select * from emp where salary > all ( select salary from emp where dept_id = (select id from dept where name = '财务部') ); |
5.6.4 行子查询
子查询返回的结果是一行（可以是多列），这种子查询称为行子查询。常用的操作符：
= 、<> 、IN 、NOT IN
案例:
A. 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
这个需求同样可以拆解为两步进行:
①. 查询 “张无忌” 的薪资及直属领导
1 | select salary, managerid from emp where name = '张无忌'; |
- 查询与 “张无忌” 的薪资及直属领导相同的员工信息 ;
1 | select * from emp where (salary,managerid) = (select salary, managerid from emp where name = '张无忌'); |
5.6.5 表子查询
子查询返回的结果是多行多列，这种子查询称为表子查询。 常用的操作符：IN
案例:
A. 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
分解为两步执行:
- 查询 “鹿杖客” , “宋远桥” 的职位和薪资
1 | select job, salary from emp where name = '鹿杖客' or name = '宋远桥'; |
- 查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息
1 | select * from emp where (job,salary) in ( select job, salary from emp where name = '鹿杖客' or name = '宋远桥' ); |
六、事务
6.1 事务简介
事务 是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
注意： 默认MySQL的事务是自动提交的，也就是说，当执行完一条DML语句时，MySQL会立即隐式的提交事务。
6.2 事务操作
数据准备：
1 | drop table if exists account; |
6.2.1 未控制事务
- 测试正常情况
1 | -- 1. 查询张三余额 |
测试完毕之后检查数据的状态, 可以看到数据操作前后是一致的。
- 测试异常情况
1 | -- 1. 查询张三余额 |
我们把数据都恢复到2000， 然后再次一次性执行上述的SQL语句(出错了…. 这句话不符合SQL语法,执行就会报错)，检查最终的数据情况, 发现数据在操作前后不一致了。
6.2.2 控制事务一
- 查看/设置事务提交方式
1 | SELECT @@autocommit ; |
提交事务
1
COMMIT;
回滚事务
1 | ROLLBACK; |
注意：上述的这种方式，我们是修改了事务的自动提交行为, 把默认的自动提交修改为了手动提交, 此时我们执行的DML语句都不会提交, 需要手动的执行commit进行提交。
6.2.3 控制事务二
开启事务
1
START TRANSACTION 或 BEGIN ;
提交事务
1
COMMIT;
回滚事务
1
ROLLBACK;
6.3 事务四大特性
- 原子性（Atomicity）：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性（Consistency）：事务完成时，必须使所有的数据都保持一致状态。
- 隔离性（Isolation）：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性（Durability）：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。
上述就是事务的四大特性，简称ACID。
6.4 并发事务问题
- 赃读：一个事务读到另外一个事务还没有提交的数据。
比如B读取到了A未提交的数据。
- 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读。
事务A两次读取同一条记录，但是读取到的数据却是不一样的。
- 幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了 “幻影”。
6.5 事务隔离级别
为了解决并发事务所引发的问题，在数据库中引入了事务隔离级别。主要有以下几种：
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read uncommitted | √ | √ | √ |
| Read committed | × | √ | √ |
| Repeatable Read(默认) | × | × | √ |
| Serializable | × | × | × |
查看事务隔离级别
1
SELECT @@TRANSACTION_ISOLATION;
设置事务隔离级别
1 | SET [ SESSION | GLOBAL ] TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } |
注意：事务隔离级别越高，数据越安全，但是性能越低