MySQL 学习记录 1

原文：https://blog.iyatt.com/?p=12631

1 前言

去年年初报考 3 月的计算机二级（C 语言）【https://blog.iyatt.com/?p=9266 】考过了，这次打算报考 3 月的计算机三级（数据库）。数据库这一块，很久之前用过 SQLite，基本没怎么深入学习。准备现学 MySQL，主要是面向应用目的，顺带后续结合开发。三级考试似乎是用微软的 SQL Server，这个问题不大，很多东西都是相通的，MySQL 熟练使用，其它估计也不是问题。

2 环境

• MySQL 社区版 8.0.35
• IDEA 2023：之前学 Kotlin 和 Android 开发的时候安装的，里面也有内置 DataGrip，用来图形化操作数据库很方便，也支持 SQL 语句提示，作为辅助工具。(https://blog.iyatt.com/?p=12618)

注：

• 数据库中的关键字用大写或者小写都行，但是一般应该是用大写比较规范，读代码的时候一眼明了。至于自己命名部分的，像数据库名、表名等等，在 Windows 下大小写等同，Linux 下默认是会区分大小写的。MySQL 实际应用中应该还是在 Linux 服务器上为主，开始学习的时候还是规范大小写比较好，形成习惯。
• SQL 语句结束使用分号。
• 下面示例用法中使用中括号的语句代表可选。

3 数据类型

3.1 字符串类型

\begin{array}{|l|l|}
\hline
类型 & 描述 \\
\hline
CHAR & 纯文本字符串，字符串长度是固定的。当实际字段内容小于定义的长度时，MySQL 会用空白空白符好补足。 \\
\hline
VARCHAR & 纯文本字符串，字符串长度是可变的。\\
\hline
BINARY & 二进制字符串，字符串长度是固定的。 \\
\hline
VARBINARY & 二进制字符串，字符串长度是可变的。\\
\hline
TINYTEXT & 二进制字符串，最大为 255 个字节。\\
\hline
TEXT & 二进制字符串，最大为 65K。\\
\hline
MEDIUMTEXT & 二进制字符串，最大为 16M。 \\
\hline
LONGTEXT & 二进制字符串，最大为 4G。\\
\hline
ENUM & 枚举；每个列值可以分配一个 ENUM 成员。 \\
\hline
SET & 集合；每个列值可以分配零个或多个 SET 成员。 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

3.2 数字类型

\begin{array}{|l|l|}
\hline
类型 & 描述 \\
\hline
TINYINT & 一个非常小的整数，最大为 1 个字节。\\
\hline
SMALLINT & 一个小整数，最大为 2 个字节。\\
\hline
MEDIUMINT & 一个中等大小的整数，最大为 3 个字节。\\
\hline
INT & 标准整数，最大为 4 个字节。\\
\hline
BIGINT & 一个大整数，最大为 8 个字节。\\
\hline
DECIMAL & 一个定点数。\\
\hline
FLOAT & 单精度浮点数，最大为 4 个字节。\\
\hline
DOUBLE & 双精度浮点数，最大为 8 个字节。\\
\hline
BIT & 按位存储。\\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

3.3 布尔类型

MySQL 没有内置的布尔类型，但是有 BOOLEAN 和 BOOL 关键字，内部当做 TINYINT 类型处理，TRUE 对应 1，FALSE 对应 0。

3.4 日期和时间类型

\begin{array}{|l|l|}
\hline
类型 & 描述 \\
\hline
DATE & CCYY-MM-DD 格式的日期值 \\
\hline
TIME & hh:mm:ss 格式的时间值 \\
\hline
DATETIME & CCYY-MM-DD hh:mm:ss 格式的日期和时间值 \\
\hline
TIMESTAMP & CCYY-MM-DD hh:mm:ss 格式的时间戳值 \\
\hline
YEAR & CCYY 或 YY 格式的年份值 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

3.5 二进制类型

\begin{array}{|l|l|}
\hline
类型 & 描述 \\
\hline
TINYBLOB & 最大为 255 个字节。\\
\hline
BLOB & 最大为 65K。\\
\hline
MEDIUMBLOB & 最大为 16M。\\
\hline
LONGBLOB & 最大为 4G。\\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13

3.6 空间数据类型

\begin{array}{|l|l|}
\hline
类型 & 描述 \\
\hline
GEOMETRY & 任何类型的空间值 \\
\hline
POINT & 使用横坐标和纵坐标表示的一个点 \\
\hline
LINESTRING & 一条曲线（一个或多个 POINT 值） \\
\hline
POLYGON & 一个多边形 \\
\hline
GEOMETRYCOLLECTION & GEOMETRY 值的集合 \\
\hline
MULTILINESTRING & LINESTRING 值的集合 \\
\hline
MULTIPOINT & POINT 值的集合 \\
\hline
MULTIPOLYGON & POLYGON 值的集合 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

3.7 JSON 类型

MySQL 从 5.7.8 开始支持 JSON 数据类型。

4 符号

4.1 比较运算符

\begin{array}{|l|l|}
\hline
符号 & 描述 \\
\hline
\gt & 大于 \\
\hline
\gt= & 大于等于 \\
\hline
\lt & 小于 \\
\hline
\lt= & 小于等于 \\
\hline
= & 等于 \\
\hline
\lt\gt 或 != & 不等于 \\
\hline
BETWEEN ... AND ... & 在某个范围之内（含最小和最大值） \\
\hline
IN\ (...) & 在列表中的值（多选一）\\
\hline
LIKE\ 占位符 & 模糊匹配（\_匹配单个字符，\% 匹配任意多个字符）\\
\hline
IS NULL & 为空 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

4.2 逻辑运算符

\begin{array}{|l|l|}
\hline
符号 & 描述 \\
\hline
AND 或 \&\& & 和，多个条件同时成立 \\
\hline
OR 或 || & 或，任意一个条件成立 \\
\hline
NOT 或 | & 否 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11

5 DDL

Data Definition Language，数据定义语言

5.1 数据库操作

查询所有数据库

SHOW DATABASES;
1

创建数据库

CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
1

使用数据库

USE 数据库名
1

查询当前使用的数据库

SELECT DATABASE();
1

5.2 表操作

表操作前要先通过 USE 指定使用的数据库

创建表

CREATE TABLE 表名 (
字段1 字段1的类型 [COMMENT 字段1的注释],
字段2 字段2的类型 [COMMENT 字段2的注释],
字段3 字段3的类型 [COMMENT 字段3的注释],
......
);
1
2
3
4
5
6

查看当前数据库中的所有表

SHOW TABLES;
1

查看表结构

DESC 表名
1

查看创建表的语句

SHOW CREATE TABLE 表名
1

表添加字段

ALTER TABLE 表名 ADD 字段 类型 [COMMENT 注释] [约束]
1

表修改字段类型

ALTER TABLE 表名 MODIFY 字段 新类型 [COMMENT 注释] [约束]
1

表修改字段名和字段类型

ALTER TABLE 表名 CHANGE 旧字段 新字段 新类型 [COMMENT 注释] [约束
1

表删除字段

ALTER TABLE DROP 表名
1

修改表名

ALTER TABLE 表名 RENAME TO 新表名
1

删除表

DROP TABLE [IF EXISTS] 表名
1

删除表中的所有数据但是保留结构

TRUNCATE TABLE 表名
1

6 DML

Data Manipulation Language，数据操作语言

给指定字段添加数据

INSERT INTO 表名 (字段1, 字段2,...) VALUES (值1, 值2,...)
1

给全部字段添加数据

INSERT INTO 表名 VALUES （值1, 值2, ...);
1

修改数据

UPDATE 表名 SET 字段1=值1, 字段2=值2,... [WHERE 条件];
1

删除数据

DELETE FROM 表名 [WHERE 条件];
1

7 DQL

Data Query Language，数据查询语言

7.1 基本查询

# 查询指定字段
SELECT 字段1, 字段2, ... FROM 表名;

# 查询所有字段
SELECT * FROM 表名;
1
2
3
4
5

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件
1

7.2 聚合函数

常用聚合函数

\begin{array}{|l|l|}
\hline
函数 & 描述 \\
\hline
COUNT & 统计数量 \\
\hline
MAX & 最大值 \\
\hline
MIN & 最小值 \\
\hline
AVG & 平均值 \\
\hline
SUM & 求和 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

使用

SELECT 聚合函数(字段列表) FROM 表名
1

7.3 分组查询

SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后的过滤条件]
1

统计男女数量

统计男女各自的平均年龄

首先用 WHERE 筛选出年龄大于 20 的，再根据城市分组，然后 HAVING 从分组中找 COUNT 计数大于 1 的，最后 SELECT 显示出对应的 city 和数量。

7.4 排序查询

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式, 字段2 排序方式, ...
1

ASC 升序，默认；
DESC 降序。

根据年龄排序（升序）

按年龄降序排序，年龄相同时会采用第二个字段身高排序（不指定默认升序）

7.5 分页查询

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
1

起始索引从 0 开始，和大多数编程语言里的数组索引一样。
下面示例是查询从索引 3 开始（第 4 个）的 3 个数据

8 DCL

Data Control Language，数据控制语言

8.1 用户管理

查询用户

USE mysql;
SELECT * FROM user;
1
2

创建用户

CREATE USER "用户名"@"主机名" IDENTIFIED BY "密码";
1

其中 localhost 指本地主机，如果要任意主机都可访问，可以使用 %。

修改用户密码

ALTER USER "用户名"@"主机名" IDENTIFIED WITH mysql_native_password BY "新密码";
1

删除用户

DROP USER "用户名"@"主机名";
1

8.2 权限控制

常用权限

\begin{array}{|l|l|}
\hline
权限 & 描述 \\
\hline
ALL/ALL\ PRIVILEGES & 所有权限 \\
\hline
SELECT & 查询数据 \\
\hline
INSERT & 插入数据 \\
\hline
UPDATE & 修改数据 \\
\hline
DELETE & 删除数据 \\
\hline
ALTER & 修改表 \\
\hline
DROP & 删除数据库/表/试图 \\
\hline
CREATE & 创建数据库/表 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

查询权限

SHOW GRANTS FOR "用户名"@"主机名";
1

授予权限

GRANT 权限列表 ON 数据库名.表名 TO "用户名"@"主机名"；
1

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM "用户名"@"主机名";
1

9 常用内置函数

9.1 字符串函数

\begin{array}{|l|l|}
\hline
函数 & 描述 \\
\hline
CONCAT(S1, S2,...,Sn) & 字符串拼接 \\
\hline
LOWER(S) & 将字符串转为小写 \\
\hline
UPPER(S) & 将字符串转为大写 \\
\hline
LPAD(S, n, pad) & 字符串左侧填充，用 pad 填充 S 的左侧，使总长度达到 n \\
\hline
RPAD(S, n, pad) & 字符串又填充，用 pad 填充 S 的右侧，使总长度达到 n \\
\hline
TRIM(S) & 去掉字符串头尾的空格 \\
\hline
SUBSTRING(S, start, len) & 返回字符串 S 从 start 开始的 len 个长度的字符串 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

9.2 数值函数

\begin{array}{|l|l|}
\hline
函数 & 描述 \\
\hline
CEIL(x) & 向上取整 \\
\hline
FLOOR(x) & 向下取整 \\
\hline
MOD(x, y) & 计算 x / y 的模 \\
\hline
RAND() & 生成 0-1 之间的随机数 \\
\hline
ROUND(x, y) & 求 x 的四舍五入值，保留 y 位小数 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

9.3 日期函数

\begin{array}{|l|l|}
\hline
CURDATA() & 返回当前日期 \\
\hline
CURTIME() & 返回当前时间 \\
\hline
NOW() & 返回当前日期和时间 \\
\hline
YEAR(date) & 获取 date 的年份 \\
\hline
MONTH(date) & 获取 date 的月份 \\
\hline
DAY(date) & 获取 date 的日期 \\
\hline
DATE\_ADD(date, INTERVAL expr type) & 返回 date 加上时间间隔 expr 后的时间值 \\
\hline
DATEDIFF(date1, date2) & 返回 date1 和 date2 之间的天数差值 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

不得不感慨从小学开始读书至今已经过去 5974 天了

9.4 流程函数

\begin{array}{|l|l|}
\hline
日期 & 函数 \\
\hline
IF(value, t, f) & 如果 value 为 TRUE，则返回 t，否为返回 f \\
\hline
IFNULL(value1, value2) & 如果 value1 不为空则返回 value1，否则返回 value2 \\
\hline
CASE\ WHEN\ [val1]\ THEN\ [res1]\ ...\ ELSE\ [default]\ END & 如果 val1 为 TRUE，返回res1,...，否则返回 default 默认值 \\
\hline
CASE\ expr\ WHEN\ [val1]\ THEN\ [res1]\ ...\ ELSE\ [default]\ END & 如果 expr 等于val1，返回 res1,...,否则返回default默认值 \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13

10 约束

常用约束

\begin{array}{|l|l|l|}
\hline
约束 & 描述 & 关键字 \\
\hline
非空约束 & 限制字段数据不能为 NULL & NOT NULL \\
\hline
唯一约束 & 字段的所有数据都是唯一的，不能重复 & UNIQUE \\
\hline
主键约束 & 主键是一行数据的唯一标识，要求非空且唯一 & PRIMARY\ KEY \\
\hline
默认约束 & 未指定字段的值采用默认值 & DEFAULT \\
\hline
检查约束（8.0.16 以后）& 保证字段值满足某条件 & CHECK \\
\hline
外键约束 & 让两张表的数据建立连接，保证数据的一致性和完整性 & FOREIGN\ KEY \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

10.1 一般约束示例

创建一个满足下面结构要求的表

• id：唯一标识，字段类型 INT，约束条件：主键且自动增长
• name：姓名，字段类型 VARCHAR(10)，约束条件：不为空且唯一
• age：年龄，字段类型 TINYINT，约束条件：不小于 0 且不超过 200
• status：身体健康状态，字段类型 CHAR(1)，约束条件：不指定默认为 1
• gender：性别，字段类型 CHAR(1)，约束条件：值为男或女

创建表

CREATE TABLE new_user (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT "唯一标识",
    name VARCHAR(10) NOT NULL UNIQUE COMMENT "姓名",
    age TINYINT CHECK ( age >=0 && age <= 200 ),
    status CHAR(1) DEFAULT "1" COMMENT "健康状态",
    gender CHAR(1) CHECK ( gender = "男" || gender = "女" ) COMMENT "性别"
) COMMENT "用户表";
1
2
3
4
5
6
7

插入数据验证：
ID 可以不用填写，提交自动从 1 开始生成
当 name 重复时，提交报错

当年龄超出约束范围，提交报错

status 不填写，提交默认为 1
当性别填写非男非女时，提交报错

10.2 外键约束示例

10.2.1 创建表时添加外键

CREATE TABLE 表名 (
	字段名 类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名)
);
1
2
3
4
5

这里先创建一个父表

CREATE TABLE classes (
                         id TINYINT COMMENT "班级标识" PRIMARY KEY , # 主键约束
                         name CHAR(2) COMMENT "班名"
) COMMENT "班级表";
1
2
3
4

父表插入内容

INSERT INTO classes VALUES
                        (1, "1班"),
                        (2, "2班"),
                        (3, "3班");
1
2
3
4

创建一个关联到父表的子表

CREATE TABLE students (
    name VARCHAR(10) COMMENT "姓名",
    age TINYINT COMMENT "年龄",
    class_id TINYINT COMMENT "班级标识",
    CONSTRAINT fk_students_classes_id FOREIGN Key (class_id) REFERENCES classes(id)
) COMMENT "学生表";
1
2
3
4
5
6

子表插入数据

INSERT INTO students VALUES
                         ("小强", 19, 1),
                         ("小红", 20, 2),
                         ("小张", 20, 3),
                         ("小军", 18, 2);
1
2
3
4
5

如果尝试删除父表中的行数据就会提示不能操作

10.2.2 现有表添加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY （外键字段名) REFERENCES 主表(主表列名);
1

假如是已经创建了一张表

CREATE TABLE stu (
    name VARCHAR(10) COMMENT "名字",
    class_id TINYINT COMMENT "班级标识"
) COMMENT "学生表";
1
2
3
4

子表插入数据

INSERT INTO stu VALUES
                    ("小明", 1),
                    ("小红", 2),
                    ("小强", 3);
1
2
3
4

后期添加外键约束

ALTER TABLE stu ADD CONSTRAINT fk_stu_class_id FOREIGN KEY (class_id) REFERENCES classes(id);
1

10.2.3 删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
1

外键名称可以通过查看表的创建语句看到

SHOW CREATE TABLE 表名
1

10.2.4 外键约束行为

\begin{array}{|l|l|}
\hline
行为 & 描述 \\
\hline
NO ACTION & 父表中删除或更新记录时，首先检查该记录是否有对应外键，有则不允许删除或更新 \\
\hline
RESTRICT & 作用同上，只是 RESTRICT 是在操作发生前就起作用，而 NO ACTION 是在删除或更新操作触发时才起作用，可以看做延迟检查。默认行为。 \\
\hline
CASCADE & 父表中删除或更新记录时，首先检查记录是否有对应外键，如果有，则也删除或更新外键在子表中的记录 \\
\hline
SET NULL & 父表中删除记录时，首先检查记录是否有对应外键，有则设置子表中该外键为 NULL（需要改外键允许取 NULL) \\
\hline
\end{array}
1
2
3
4
5
6
7
8
9
10
11
12
13

指定外键约束行为

```mysql
CREATE TABLE 表名 (
	字段名 类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名) ON UPDATE 更新时行为 ON DELETE 删除时行为;
);
1
2
3
4
5
6

```mysql
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 更新时行为 ON DELETE 删除时行为;
1
2
3

注：如果已有外键约束，要修改行为，需要先删除原有的外键约束，然后重新建立外键时指定行为。

示例：
创建一个父表

CREATE TABLE classes (
                         id TINYINT COMMENT "班级标识" PRIMARY KEY , # 主键约束
                         name CHAR(2) COMMENT "班名"
) COMMENT "班级表";
1
2
3
4

父表插入内容

INSERT INTO classes VALUES
                        (1, "1班"),
                        (2, "2班"),
                        (3, "3班");
1
2
3
4

创建一个子表，外键约束行为都是 CASCADE

CREATE TABLE students (
                          name VARCHAR(10) COMMENT "姓名",
                          age TINYINT COMMENT "年龄",
                          class_id TINYINT COMMENT "班级标识",
                          CONSTRAINT fk_students_classes_id FOREIGN Key (class_id) REFERENCES classes(id) ON UPDATE CASCADE ON DELETE CASCADE
) COMMENT "学生表";
1
2
3
4
5
6

子表插入数据

INSERT INTO students VALUES
                         ("小强", 19, 1),
                         ("小红", 20, 2),
                         ("小张", 20, 3),
                         ("小军", 18, 2);
1
2
3
4
5

修改父表

子表同步更新了 id

删除父表数据

子表同步删除了

如果设置的外键行为是 SET NULL，则修改父表或删除子表的某行元素后，子表中对应的外键会改为 NULL。

11 多表查询

11.1 关系

11.1.1 一对一

比如学生可以有学生信息，也有社会信息，分别建立一张表，可以通过身份证号建立外键关联起来
创建一张学生信息表

CREATE TABLE student (
    id CHAR(1) COMMENT "身份证号",
    name VARCHAR(10) COMMENT "姓名",
    name_of_school VARCHAR(36) COMMENT "学校名"
) COMMENT "学生信息";
1
2
3
4
5

插入数据

INSERT INTO student VALUES
                        ("1", "小强", "A school"),
                        ("2", "小红", "B school"),
                        ("3", "小张", "C school");
1
2
3
4

创建一张社会信息表

CREATE TABLE person (
    id CHAR(1) COMMENT "身份证号" PRIMARY KEY,
    name VARCHAR(10) COMMENT "姓名",
    age TINYINT COMMENT "年龄",
    address VARCHAR(128) COMMENT "地址"
) COMMENT "社会信息";
1
2
3
4
5
6

插入数据

INSERT INTO person VALUES
                        ("1", "小强", 20, "AAA"),
                        ("2", "小红", 19, "BBB"),
                        ("3", "小张", 20, "CCC");
1
2
3
4

建立外键

ALTER TABLE student
    ADD CONSTRAINT fk_student_person_id
    FOREIGN KEY (id)
    REFERENCES person (id);
1
2
3
4

查询

11.1.2 一对多

一个学生只属于一个班，一个班里有多个学生。
创建一个学生表

CREATE TABLE student_class (
    name CHAR(2) COMMENT "名字",
    class_id CHAR(1) COMMENT "所属班级代号"
) COMMENT "学生-班级表";
1
2
3
4

插入数据

INSERT INTO student_class VALUES 
                              ("小强", "1"),
                              ("小张", "2"),
                              ("小红", "3"),
                              ("小刚", NULL);
1
2
3
4
5

创建班级表

CREATE TABLE classes (
    id CHAR(1) COMMENT "班级代号" PRIMARY KEY,
    name CHAR(3) COMMENT "班级名称"
) COMMENT "班级表";
1
2
3
4

插入数据

INSERT INTO classes VALUES 
                        ("1", "火箭班"),
                        ("2", "实验班"),
                        ("3", "平行班");
1
2
3
4

建立外键

ALTER TABLE student_class
    ADD CONSTRAINT fk_student_class_id
    FOREIGN KEY (class_id)
    REFERENCES classes (id);
1
2
3
4

11.1.3 多对多

一个学生可以上多门课，每门课可以有多个学生上，这就是一种多对多的关系。
首先创建一个学生表

CREATE TABLE stu (
    id CHAR(1) COMMENT "学号" PRIMARY KEY,
    name CHAR(2) COMMENT "姓名"
) COMMENT "学生表";
1
2
3
4

插入数据

INSERT INTO stu VALUES 
                    ("1", "小强"),
                    ("2", "小张"),
                    ("3", "小红");
1
2
3
4

再创建一个课程表

CREATE TABLE course (
    id CHAR(1) COMMENT "课程代号" PRIMARY KEY ,
    name CHAR(2) COMMENT "课程名称"
) COMMENT "课程表";
1
2
3
4

插入数据

INSERT INTO course VALUES
                       ("1", "高数"),
                       ("2", "大物"),
                       ("3", "英语");
1
2
3
4

再创建一张表，建立外链关联两张表

CREATE TABLE stu_course (
    stu_id CHAR(1) COMMENT "学号",
    course_id CHAR(1) COMMENT "课程代号",
    CONSTRAINT fk_stu_course_stu_id FOREIGN KEY (stu_id) REFERENCES stu(id),
    CONSTRAINT fk_stu_course_course_id FOREIGN KEY (course_id) REFERENCES course(id)
) COMMENT "学生课表";
1
2
3
4
5
6

插入数据

INSERT INTO stu_course VALUES
                           ("1", "2"),
                           ("2", "1"),
                           ("2", "3"),
                           ("3", "1"),
                           ("3", "2"),
                           ("3", "3");
1
2
3
4
5
6
7

11.2 内连接

内连接主要是查询两张表的交集部分，示例使用上面一对多创建的表。

隐式内连接

SELECT 字段列表 FROM 表1, 表2 WHERE 条件
1

显式内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件;
1

11.3 外连接

查询其中一张表及两张表交集的部分
左外连接(查询表1及表1和表2的交集部分)，右外连接，把 LEFT 改成 RIGHT 就行，也可以把表1和表2对换，一样的效果

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
1

还是使用前面一对多创建的表演示

11.4 自连接

可以是内连接也可以是外连接

SELECT 字段列表 FROM 表A 别名1 JOIN 表A 别名2 ON 条件;
1

这里创建一张表用于演示

CREATE TABLE emp (
    id TINYINT COMMENT "工号",
    name VARCHAR(10) COMMENT "姓名",
    mid TINYINT COMMENT "领导的工号"
) COMMENT "职工表";
1
2
3
4
5

插入数据

INSERT INTO emp VALUES
                    (1, "AAA", NULL),
                    (2, "BBB", 1),
                    (3, "CCC", 1),
                    (4, "DDD", 2),
                    (5, "EEE", 2),
                    (6, "FFF", 3);
1
2
3
4
5
6
7

内连接

外连接

左外连接查询结果中，左边罗列出所有职工，右边是对应职工的领导，没有领导的就显示 NULL
右外连接查询结果中，右边是罗列出所有职工，左边给出对应的下属，没有下属就显示 NULL

11.5 联合查询

UNION [ALL]
1

这里随便创建了两张表用于演示查询，使用 UNINO ALL 会直接把两张表的结果拼起来

SELECT * FROM test1
UNION ALL
SELECT * FROM test2;
1
2
3

如果去掉 ALL，只使用 UNINO，则呈现的结果是去除重复的

11.6 子查询/嵌套查询

11.6.1 标量子查询

用查询到的一个结果作为条件进一步查询

这里使用前面创建的两张表演示

首先在班级表里查询火箭班的 ID，然后再到学生表中查询具有这个 ID 的学生信息

SELECT * FROM student_class
         WHERE class_id = (SELECT id FROM classes WHERE name = "火箭班");
1
2

11.6.2 列子查询

常用的操作符

\begin{array}{l l}
操作符 & 描述 \\
\hline
IN & 在指定的集合范围内 \\
NOT IN & 不在指定的集合范围内 \\
ANY & 子查询返回列表中，有任意一个满足即可 \\
SOME & 与 ANY 等同，使用 SOME 的地方都可以使用 ANY \\
ALL & 子查询返回列表的所有值都必须满足
\end{array}
1
2
3
4
5
6
7
8
9

前面是先查询出一个结果，再用这个结果作为条件查询。列子查询则是查询出符合某条件的一列结果，将这一列结果作为条件进行查询。
有下面两张表：

查询班级平均成绩大于 80 的班级中的学生信息

SELECT * FROM student WHERE class_id in (SELECT class_id
                                   FROM class
                                   WHERE average_grades > 80);
1
2
3

11.6.3 行子查询

列子查询是同时筛选一列的多行，行子查询就是筛选一行的多列（多表头）
在上面表上加上个人学生成绩

现在要找出在火箭班中成绩与班级平均成绩相同的

SELECT * FROM student WHERE (grades, class_id) = (
    SELECT average_grades, class_id FROM class WHERE name = '火箭班');
1
2

11.6.4 表子查询

筛选多行多列
将学生表中年龄大于 16 的数据筛选出来，将这些数据和班级表结合起来外连接查询

SELECT s.name, c.name FROM (SELECT * FROM student WHERE age > 16) s LEFT JOIN class c on s.class_id = c.class_id;
1

12 事务

MySQL 中默认每次执行修改数据指令后会立即提交（默认自动事务），但是这种情景中（A向B转账，首先查询A的余额，余额足够，从A的余额扣除，再给B增加余额），可能就会出现问题（如果在A扣除余额后和在B增加余额之间的操作中出现异常导致终止），这种情形下A的余额扣了，但是B的余额没有增加。
MySQL 中的（手动）事务则可以应对这个情况，可以将整个流程操作作为一个事务（查询A余额，扣除A余额，增加B余额），中间的操作不会最终修改原始数据，只是暂存，操作成功最后提交修改就行，操作失败放弃暂存的操作，原始数据不修改（回滚）。

创建用于测试的表

CREATE TABLE account(
    name varchar(2),
    balance int
);

INSERT into account VALUES
                        ('小明', 2000),
                        ('小红', 2000);
1
2
3
4
5
6
7
8

恢复数据

UPDATE account set balance = 2000 WHERE name = '小明' or name = '小红';
1

查看事务提交方式
0 为手动，1 为自动

SELECT @@autocommit;
1

改为手动就把这个变量值设置为 0

SET @@autocommit = 0;
1

提交事务
在手动事务状态(或显式启用事务)下，执行了修改操作不会直接修改，在执行完每个指令或者一系列指定后手动执行这个指令才会提交生效

COMMIT;
1

回滚事务

ROLLBACK;
1

显式启用事务

start transaction;
1

---

转账模拟实现

# 查询余额
SELECT * FROM account;

# 小明余额 -1000
UPDATE account SET balance = balance - 1000 WHERE name = '小明';

# 小红余额 +1000
UPDATE account SET balance = balance + 1000 WHERE name = '小红';

# 查询余额
SELECT * FROM account;
1
2
3
4
5
6
7
8
9
10
11

模拟异常
我在小明扣除转出金额核小红增加金额中间添加了一个错误的语句

这样就出现了小明余额扣除，但是小红余额没有增加的情况

---

可以显式启用事务，在操作时遇到异常就执行回滚，则会恢复原数据并结束当前事务。

事务这个设计有点像 Git，添加删除修改等操作后只是添加到暂存区，最终操作完使用 commit 才提交。只是 MySQL 默认状态每执行一次操作就会自动提交一次，设置显式事务后，则不会自动提交，中间可以回滚放弃修改。

12.1 特性（ACID）

• 原子性（Atomicity）

事务是最小的操作单元，一个事务可以是一个操作或者多个操作的集合（但不可分割），其中任一组员执行失败就是整个事务的失败，而全部执行成功，事务才执行成功。

• 一致性（Consistency）

一致性可以体现在上面的转账案例中，小明转出了钱，余额减少了，转给小红了，小红余额对应增加了，不会出现小明余额减少了，但小红余额没有增加的情况。

• 隔离性（Isolation）

多个事务并发执行时互不影响，各自独立执行。

• 持久性（Durability）

事务被提交（或回滚）对数据的修改就是永久的，写入了硬盘中的数据库文件里了。

12.2 并发事务存在的问题

• 脏读
  一个事务读到另外一个事务还没有提交的数据

• 不可重复读
  一个事务先后读取同一条记录，但两次读取的数据不同。一个事务有两个读取操作，第一次读取后，在第二次读取前，另外一个事务对数据进行了修改，第二次读取时数据就和第一次不一样了。

• 幻读
  一个事务在读取数据时，没有对应的数据，尝试插入数据的时候发现已经存在了。一个事务在读取操作的时候发现数据不存在，然后准备插入数据，在插入之前另外一个事务先执行了插入，等到原事务打算插入的时候又发现已经存在。

12.3 事务隔离级别

\begin{array}{l}
隔离级别 & 脏读 & 不可重复读 & 幻读 \\
READ\ UNCOMMITTED & ✓ & ✓ & ✓ \\
READ\ COMMITTED & ✖ & ✓ & ✓ \\
REPEATABLE\ READ(默认) & ✖ & ✖ & ✓ \\
SERIALIZABLE & ✖ & ✖ & ✖
\end{array}
1
2
3
4
5
6
7

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;
1

设置事务隔离级别

SET 作用范围 TRANSACTION ISOLATION LEVEL 隔离级别;
1

作用范围可以写 SESSION（只在当前客户端生效）和 GLOBAL（全局）