阅读量： 171

最近更新于 2024-05-05 12:31

Table of Contents

环境

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

注：

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

数据类型

字符串类型

 $\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}$

数字类型

 $\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}$

布尔类型

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

日期和时间类型

 $\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}$

二进制类型

 $\begin{array}{|l|l|} \hline 类型 & 描述 \\ \hline TINYBLOB & 最大为 255 个字节。\\ \hline BLOB & 最大为 65K。\\ \hline MEDIUMBLOB & 最大为 16M。\\ \hline LONGBLOB & 最大为 4G。\\ \hline \end{array}$

空间数据类型

 $\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}$

JSON 类型

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

符号

比较运算符

 $\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}$

逻辑运算符

 $\begin{array}{|l|l|} \hline 符号 & 描述 \\ \hline AND 或 \&\& & 和，多个条件同时成立 \\ \hline OR 或 || & 或，任意一个条件成立 \\ \hline NOT 或 | & 否 \\ \hline \end{array}$

DDL

Data Definition Language，数据定义语言

数据库操作

查询所有数据库

SHOW DATABASES;

创建数据库

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

使用数据库

USE 数据库名

查询当前使用的数据库

SELECT DATABASE();

表操作

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

创建表

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

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

SHOW TABLES;

查看表结构

DESC 表名

查看创建表的语句

SHOW CREATE TABLE 表名

表添加字段

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

表修改字段类型

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

表修改字段名和字段类型

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

表删除字段

ALTER TABLE DROP 表名

修改表名

ALTER TABLE 表名 RENAME TO 新表名

删除表

DROP TABLE [IF EXISTS] 表名

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

TRUNCATE TABLE 表名

DML

Data Manipulation Language，数据操作语言

给指定字段添加数据

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

给全部字段添加数据

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

修改数据

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

删除数据

DELETE FROM 表名 [WHERE 条件];

DQL

Data Query Language，数据查询语言

基本查询

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

# 查询所有字段
SELECT * FROM 表名;

条件查询

SELECT 字段列表 FROM 表名 WHERE 条件

聚合函数

常用聚合函数

 $\begin{array}{|l|l|} \hline 函数 & 描述 \\ \hline COUNT & 统计数量 \\ \hline MAX & 最大值 \\ \hline MIN & 最小值 \\ \hline AVG & 平均值 \\ \hline SUM & 求和 \\ \hline \end{array}$

使用

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

分组查询

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

统计男女数量

统计男女各自的平均年龄

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

排序查询

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

ASC 升序，默认；
DESC 降序。

根据年龄排序（升序）

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

分页查询

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

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

DCL

Data Control Language，数据控制语言

用户管理

查询用户

USE mysql;
SELECT * FROM user;

创建用户

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

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

修改用户密码

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

删除用户

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

权限控制

常用权限

 $\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}$

查询权限

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

授予权限

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

撤销权限

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

常用内置函数

字符串函数

 $\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}$

数值函数

 $\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}$

日期函数

 $\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}$

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

流程函数

 $\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}$

约束

常用约束

 $\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}$

一般约束示例

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

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 "用户表";

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

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

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

外键约束示例

创建表时添加外键

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

这里先创建一个父表

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

父表插入内容

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

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

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 "学生表";

子表插入数据

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

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

现有表添加外键

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

假如是已经创建了一张表

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

子表插入数据

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

后期添加外键约束

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

删除外键

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

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

SHOW CREATE TABLE 表名

外键约束行为

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

指定外键约束行为

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


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

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

示例：
创建一个父表

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

父表插入内容

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

创建一个子表，外键约束行为都是 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 "学生表";

子表插入数据

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

修改父表

子表同步更新了 id

删除父表数据

子表同步删除了

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

多表查询

关系

一对一

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

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

插入数据

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

创建一张社会信息表

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

插入数据

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

建立外键

ALTER TABLE student
    ADD CONSTRAINT fk_student_person_id
    FOREIGN KEY (id)
    REFERENCES person (id);

查询

一对多

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

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

插入数据

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

创建班级表

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

插入数据

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

建立外键

ALTER TABLE student_class
    ADD CONSTRAINT fk_student_class_id
    FOREIGN KEY (class_id)
    REFERENCES classes (id);

多对多

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

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

插入数据

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

再创建一个课程表

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

插入数据

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

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

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 "学生课表";

插入数据

INSERT INTO stu_course VALUES
                           ("1", "2"),
                           ("2", "1"),
                           ("2", "3"),
                           ("3", "1"),
                           ("3", "2"),
                           ("3", "3");

内连接

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

隐式内连接

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

显式内连接

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

外连接

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

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

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

自连接

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

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

这里创建一张表用于演示

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

插入数据

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

内连接

外连接

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

联合查询

UNION [ALL]

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

SELECT * FROM test1
UNION ALL
SELECT * FROM test2;

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

子查询/嵌套查询

标量子查询

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

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

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

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

列子查询

常用的操作符

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

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

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

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

行子查询

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

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

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

表子查询

筛选多行多列
将学生表中年龄大于 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;

事务

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);

恢复数据

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

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

SELECT @@autocommit;

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

SET @@autocommit = 0;

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

COMMIT;

回滚事务

ROLLBACK;

显式启用事务

start transaction;

转账模拟实现

# 查询余额
SELECT * FROM account;

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

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

# 查询余额
SELECT * FROM account;

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

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

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

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

特性（ACID）

原子性（Atomicity）

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

一致性（Consistency）

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

隔离性（Isolation）

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

持久性（Durability）

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

并发事务存在的问题

脏读
一个事务读到另外一个事务还没有提交的数据
不可重复读
一个事务先后读取同一条记录，但两次读取的数据不同。一个事务有两个读取操作，第一次读取后，在第二次读取前，另外一个事务对数据进行了修改，第二次读取时数据就和第一次不一样了。
幻读
一个事务在读取数据时，没有对应的数据，尝试插入数据的时候发现已经存在了。一个事务在读取操作的时候发现数据不存在，然后准备插入数据，在插入之前另外一个事务先执行了插入，等到原事务打算插入的时候又发现已经存在。

事务隔离级别

 $\begin{array}{l} 隔离级别 & 脏读 & 不可重复读 & 幻读 \\ READ\ UNCOMMITTED & &# & &# & &# \\ READ\ COMMITTED & &# & &# & &# \\ REPEATABLE\ READ(默认) & &# & &# & &# \\ SERIALIZABLE & &# & &# & &# \end{array}$

查看事务隔离级别

SELECT @@TRANSACTION_ISOLATION;

设置事务隔离级别

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

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

存储引擎

查看一下前面创建的一张表的创建语句，当时并没有显式指定引擎，MySQL 自动指定的 InnoDB，即默认引擎是这个。
创建表的时候要显式指定引擎可以参考这个语句

查看当前 MySQL 版本支持的引擎有那些

SHOW ENGINES ;

 $\begin{array}{|l|l|l|l|} \hline 特点 & InnoDB & MyISAM & Memory \\ \hline 事务安全 & 支持 & - & - \\ 锁机制 & 行锁 & 表锁 &表锁 \\ B+tree索引 & 支持 & 支持 & 支持 \\ Hash索引 & - & - & 支持 \\ 全文索引 & 支持（5.6版本之后）& 支持 & - \\ 空间使用 & 高 & 低 & N/A \\ 内存使用 & 高 & 低 & 中等 \\ 批量插入速度 & 低 & 高 & 高 \\ 支持外键 & 支持 & - & - \\ \hline \end{array}$

InnoDB：MySQL 的默认存储引擎，支持事务、外键。如果对事务的完整性有比较高的要求，在并发条件下要求数据的一致性。另外对数据的操作除了插入和查询之外，还包含很多的更新、删除操作，那么选择这个引擎比较合适。
MyISAM：如果是以读取和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么可以选择这个引擎。
MEMORY：所有数据都保存在内存中，访问速度快，通常用于临时表及缓存。不过存在一定的缺陷，对表的大小有限制，太大的表无法存储在内存中，并且无法保障数据的安全性（意外断电、宕机等可能造成数据丢失）

索引 – 查询优化

 $\begin{array}{|l|l|} \hline 索引结构 & 描述 \\ \hline B+Tree & 最常见的索引类型，大部分存储引擎都支持。 \\ Hash索引 & 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。\\ R-tree & 这是 MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少。 \\ Full-text & 通过建立倒排索引，快速匹配文档的方式。\\ \hline \end{array}$

 $\begin{array}{|l|l|l|l|} \hline 索引 & InnoDB & MyISAM & Memory \\ \hline B+tree & 支持 & 支持 & 支持 \\ Hash & 不支持 & 不支持 & 支持 \\ R-tree & 不支持 & 支持 & 不支持 \\ Full-text & 支持（5.6版本后） & 支持 & 不支持 \\ \hline \end{array}$

数据结构可视化：https://iyatt.com/tools/DataStructureVisualizations/Algorithms.html

B+tree 依次插入100、65、169、368、900、556、780、35、215、1200、234、888、158、90、1000、88、120、268、250

 $\begin{array}{|l|l|l|l|} \hline 分类 & 含义 & 特点 & 关键字 \\ \hline 主键索引 & 针对表中主键创建的索引 & 默认自动自动创建，只有一个 & PRIMARY \\ 唯一索引 & 避免同一个表中某列数据重复 & 可以有多个 & UNIQUE \\ 常规索引 & 快速定位特定数据 & 可以有多个 & \\ 全文索引 & 全文索引查找的是文本中的关键词，而不是比较索引中的值 & 可以有多个 & FULLTEXT \\ \hline \end{array}$

在 InnoDB 中

 $\begin{array}{|l|l|l|} \hline 分类 & 含义 & 特点 \\ \hline 聚集索引（Clustered Index） & 将数据存储与索引放在了一块，索引结构的叶子节点保存了行数据 & 必须有，且只有一个 \\ 二级索引（Secondary Index） & 将数据和索引分开存储，索引结构的叶子节点关联的是对应的主键 & 可以存在多个 \\ \hline \end{array}$

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引
如果不存在主键，将使用第一个唯一索引作为聚集索引
如果前两者都没有合适的，则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引

创建索引
（如果要创建常规索引，则不指定 UNIQUE 或 FULLTEXT）

CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名 (索引列名);

查看索引

SHOW INDEX FROM 表名;

删除索引

DROP INDEX 索引名 ON 表名;

语法

创建一张表，创建语句使用：https://blog.iyatt.com/?p=12631#_%E4%B8%80%E8%88%AC%E7%BA%A6%E6%9D%9F%E7%A4%BA%E4%BE%8B
查看这张表的索引
（加上 \G 会按行显示）

SHOW INDEX FROM new_user\G;

可以看到列名 id 的 Key_name 是 PRIMARY 主键索引（创建表指定了主键约束），列名 name 的 Key_name 就是列名，该列在创建表时指定了唯一约束，其它列则没有索引。

现在手动为 age 列创建一个常规索引名为 index_age

CREATE INDEX index_age ON new_user(age);

再次查看可以看到 age 列的索引

删除创建的常规索引

DROP INDEX index_age ON new_user;

再次为 age 和 status 同时创建一个唯一索引（联合索引）

CREATE UNIQUE INDEX index_age ON new_user(age, status);

查看索引

性能分析

查询 SQL 执行频次

查询状态信息

SHOW [SESSIOn | GLOBAL] STATUS;

筛选出 SQL 语句执行次数，全局查询含有 Com 的且后续还有 7 个字母的变量，用 7 个下划线 _ 匹配

SHOW GLOBAL STATUS LIKE 'Com_______';

这里我在当前博客的服务器数据库上查询（Mariadb 和 MySQL 基本上兼容）
可以看到插入了 948768 次，删除了 339467 次，查询了 34850658 次，修改了 667195 次。可以看到里面查询次数是最多的，毕竟博客大多数时候都是浏览查看，所以优化的重点就在查询上。

慢查询日志

当 MySQL 中某个语句执行超过设定时间，就会记录到日志中，默认是没有打开的。

查看是否开启慢查询日志
当前是关闭的

SELECT @@slow_query_log;

查看慢查询时间
默认是 10s，查询时间超过它就会记录日志

SELECT @@long_query_time;

如果要开启慢查询日志可以配置：

Windows：前往路径 C:\ProgramData\MySQL\MySQL Server 版本，编辑 my.ini（打开显示隐藏文件，不然看不到这个路径）

这个文件默认是没有编辑权限的，可以在这个文件上右键打开属性

给自己的当前用户添加修改权限

这样就可以编辑这个文件了，Windows 默认是打开状态的
slow-query-log 设置 1 就是开启，设置 0 就是关闭。
slow_query_log_file 设置文件名，日志文件位于 C:\ProgramData\MySQL\MySQL Server 版本\Data 下。
long_query_time 设置超时时间。

修改完保存，并重启 MySQL 服务器

Linux：以 root 权限编辑 /etc/my.cnf，我博客服务器用的 Mariadb 10.3.38 中这个文件在 /etc/mysql/my.cnf。配置参数方法同上。

如果要临时设置可以使用下面命令（重启恢复为配置文件中的默认状态），后续其它变量一样

# SESSION 只在当前会话中，GLOBAL 在所有客户端都生效
SET [SESSION | GLOBAL] 变量名 = 变量值；

在 Windows 中默认试打开的，在 Linux 中默认是关闭的。估计因为一般开发是在 Windows 上，这个打开本来就是用于调试，而实际生产环境部署一般是在 Linux 上，所以默认是关闭的，在生产环境上开启这些记录只会增加资源消耗，浪费本该用于业务执行的性能。

profile

查看语句执行耗时

查看是否支持 profile

SELECT @@have_profiling;

查看打开状态

SELECT @@profiling;

使用 SET 把这个变量改为 1 即可开启，在执行语句后会记录执行时间，通过命令可以查询

SHOW PROFILES ;

查看指定 query_id 的语句的详细耗时

SHOW PROFILE FOR QUERY 查询ID;

查看指定 query_id 的语句的 CPU 使用情况

SHOW PROFILE CPU FOR QUERY 查询ID;

explain 执行计划

在执行的语句前面加上 EXPLAIN 或 DESC，查询项含义：

id：表查询的序列号，相同则从上往下，越大的越先执行
select_type：查询类型，常见的有 SIMPLE（简单表，不使用表连接或子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（包含子查询）等
type：连接类型，性能从低到高：NULL、system、const、eq_ref、ref、range、index、all
possible_key：表中可能用到的索引。
key：实际使用的索引
key_len：索引字段的最大可能长度
rows：必须要执行查询的行数，在 InnoDB 中是一个估计值
filltered：结果返回的行数占需要读取行数的百分比，越大越好。

采用这里的三张表来演示：https://blog.iyatt.com/?p=12631#3_%E5%A4%9A%E5%AF%B9%E5%A4%9A

内连接

EXPLAIN SELECT * FROM stu s, course c, stu_course sc WHERE s.id = sc.stu_id AND c.id = sc.course_id \G;

子查询

EXPLAIN SELECT * FROM stu s WHERE s.id IN (
    SELECT stu_id FROM stu_course sc WHERE sc.course_id
                                     = (SELECT id FROM course c WHERE c.name = '高数')
    ) \G;

使用

索引有效条件 – 最左前缀法则

如果索引了多列（联合索引），要遵循最左前缀法则。

创建一张表

CREATE TABLE info (
                      id tinyint,
                      age tinyint,
                      name char(2),
                      class int
);

插入数据

INSERT INTO info VALUES
                     (1, 21, '小强', 1),
                     (2, 21, '小红', 2),
                     (3, 20, '小张', 1);

创建联合索引（后续提到的左右都是基于创建索引时指定的列顺序）

CREATE INDEX index_info ON info(age, name, class);

获取每个列的 key_len
age name class 分别为 2 9 5

同时指定三列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and name = '小强' and class = 1;

使用了全部索引

指定最左列和最右列查询

EXPLAIN SELECT * FROM info WHERE age = 21 and class = 1;

key_len 为 2，只有最左列 age 使用索引

不指定最左侧列

EXPLAIN SELECT * FROM info WHERE name = '小强' and class = 1;

没有使用索引

age 列指定范围

EXPLAIN SELECT * FROM info WHERE age > 20 and name = '小强' and class = 1;

key_len 为 2，从 age 列右侧下一列开始没有使用索引

打乱顺序

EXPLAIN SELECT * FROM info WHERE class = 1 and name = '小强' and age = 21;

顺序不影响索引使用

即联合索引必须包含最左列才会使用索引，且中间如果有留空，则从留空列开始不使用索引

索引失效情况 1 – 索引列运算

用上面的表演示，在这个表中查询年龄为 21，名字第二个字为“强”的

EXPLAIN SELECT * FROM info WHERE age = 21 and substring(name, 2, 1) = '强';

key_len 为 2，即 name 字段索引失效了

索引失效情况 2 – 范围索引

上面最左前缀法则中，age 指定范围演示过。从范围索引的右侧列开始索引失效。

索引失效情况 3 – 模糊查询

第一个字采用模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '_强';

key_len 为 2，name 索引失效

非第一个字模糊匹配

EXPLAIN SELECT * FROM info WHERE age = 21 AND name LIKE '小_';

索引正常工作

第一个字符模糊查询会导致索引失效，非第一个字符模糊查询索引正常工作。

索引失效情况 4 – OR 连接的条件

OR 连接的条件，一个有索引，一个没有索引，那么此时两者都不会使用索引。

这里的 age 有索引，id 没有索引

EXPLAIN SELECT * FROM info WHERE age = 21 OR id = 1;

结果都没有使用索引

索引失效情况 5 – 数据分布影响（优化）

当使用索引可能更慢的时候，MySQL 会决定不使用索引。

创建一张表用于演示

CREATE TABLE info1 (
    id int,
    name char(2)
);

INSERT INTO info1 (id, name) VALUES
                                (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, '朱七'),
                                (26, '秦八'),
                                (27, '尤九'),
                                (28, '许十'),
                                (29, '薛一'),
                                (30, '侯二'),
                                (31, '夏三'),
                                (32, '邱四'),
                                (33, '方五'),
                                (34, '石六'),
                                (35, '姚七'),
                                (36, '谭八'),
                                (37, '廖九'),
                                (38, '范十'),
                                (39, '汪一'),
                                (40, '陆二'),
                                (41, '金三'),
                                (42, '魏四'),
                                (43, '陶五'),
                                (44, '戴六'),
                                (45, '郭七'),
                                (46, '洪八'),
                                (47, '邹九'),
                                (48, '江十'),
                                (49, '章一'),
                                (50, '董二');

CREATE INDEX index_info1 ON info1(id);

查询 id > 10 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 10;

没有使用索引

查询 id > 30 的数据

EXPLAIN SELECT * FROM info1 WHERE id > 30;

使用了索引

当查询的数据是表中的少部分的时候，MySQL 会使用索引，这样速度更快，但是查询的是表中的大部分数据的时候，可能不如直接暴力遍历的速度。

索引提示

当一个列存在多个索引时，可以指定使用某个索引。

这里示例还是使用上面创建的 info 表
为 age 列再创建一个单列索引

CREATE INDEX index_age ON info(age);

此时 age 同时具有前面的联合索引，又有了一个单列索引

EXPLAIN SELECT * FROM info WHERE age = 21;

可能用到的索引有两个，实际用的是联合索引

指定使用 index_age 索引（单列）- 建议 MySQL 使用，MySQL 通过一定算法判断是否使用
USE INDEX (索引名)

EXPLAIN SELECT * FROM info USE INDEX (index_age) WHERE age = 21;

忽略索引 index_info（联合）
IGNORE INDEX (索引名)

EXPLAIN SELECT * FROM info IGNORE INDEX (index_info) WHERE age = 21;

强制使用索引 index_age（单列）
FORCE INDEX (索引名)

EXPLAIN SELECT * FROM info FORCE INDEX (index_age) WHERE age = 21;

覆盖索引

查询的列数据都包含在索引中

使用上面的 info 表，删掉创建的索引，只保留原先的联合索引

当查询项都在索引中时

EXPLAIN SELECT age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

当查询项多了一个 id（不在索引中）

EXPLAIN SELECT id, age, name, class FROM info WHERE age = 21 AND name = '小强' AND class = 1;

或者查 *

EXPLAIN SELECT * FROM info WHERE age = 21 AND name = '小强' AND class = 1;

Using index condition：表示使用了索引条件下推（Index Condition Pushdown，ICP）的优化，即在存储引擎层对索引进行过滤，减少回表查询的次数。
Using where：表示在服务器层对数据进行过滤，通常是因为索引不能完全满足查询条件，或者没有使用索引。
Using index：表示使用了覆盖索引（Covering Index）的优化，即索引已经包含了所需的所有列，无需访问数据表

前缀索引

当字段类型为字符串时，有时候需要索引很长的字符串，但是这样会让索引变得很大，查询的时候磁盘 IO 占用会非常高，影响查询效率。因此可以只将字符串的一部分前缀建立索引，这样可以提高索引效率。

CREATE INDEX 索引名 ON 表名(列名(前n个字符))

至于这前n个字符具体取多少个，可以参考“索引的选择性”。比如某列的字段内容较长，考虑建立前缀索引，所在的表中有100行数据，如果每行的该字段都只取前 10 个字符，结果这 100 行都没有重复，那么选择性 = 没有重复的行数 100 / 总行数 100 = 1。然后又尝试往前推进看看，该字段取前 9 个字符，结果有 10 行和已经存在的重复，那么选择性= 去除重复的行数 (100-10) / 总行数 100 = 0.9。前一种前缀取 10，每行都是独一无二的，这种情况下索引速度必然好，后一种情况前缀取 9，但是只有 0.9 的比例为非重复，会影响一定效率（遇到重复等于索引失效，要回表查询），但是索引能节省一个字符的空间。索引的选择性就是一种作为参考的参数，来辅助选择前缀大小，综合前缀长度减小又能保证较好的选择性数值。

下面是一个示例表，创建它

CREATE TABLE info2 (
    name char(2),
    email varchar(32)
);

INSERT INTO info2 VALUES
                      ('小强', '123456789@qq.com'),
                      ('小王', '123459875@foxmail.com'),
                      ('小李', 'xiaoli@baidu.com'),
                      ('小红', '1234567@douyin.com'),
                      ('小张', '123986@360.com'),
                      ('小谢', 'xioaoxie@a.com'),
                      ('小陈', 'xiaochen@b.cn'),
                      ('小杨', 'xiaoyang@c.cpn'),
                      ('小赵', 'xiaozhao@d.com'),
                      ('小唐', '1234587@t.com');

SELECT count(DISTINCT substring(email, 1, 前缀长度)) / count(*) FROM info2;

前缀取 8 的时候选择性为 1，前缀取到 7、6 都是 0.9，取到 5 就只有 0.7 了，那我就取 6

CREATE INDEX prefix_index_email ON info2(email(6));

查看索引时，其中 Sub_part 字段为 6，非前缀索引这个值就是 NULL

第一个 rows 为 1，第二个为 2。因为第一个在前缀索引取 6 时没有重复的索引，而后一个索引有重复的，那么就需要回到数据表中去确认是否重复，也就还需要额外查一次。

其它优化

插入优化

多条数据，INSERT 最好一次性插入多条数据，而不是每次一条。
当数据量过于太大时，也不要一次性插入，可以一次性插入 500-1000 条
连续执行 INSERT 时最好显式开启事务，执行完所有插入后一次性提交

从文件导入数据

这里写了一个 Python 脚本用来生成 csv 数据

import random

def write_to_file(filename, delimiter, newline, rows):
    with open(filename, 'w', encoding='utf-8') as f:
        for i in range(rows):
            id = i + 1
            line = str(id) + delimiter + ''.join(random.sample('abcdefghijklmnopqrstuvwxyz', 12)) + delimiter + str(random.randint(0,1)) + delimiter + str(random.randint(0, 100))
            f.write(line + newline)

# 生成文件名 test.csv
# 分隔符为英文逗号
# 换行符为 \r
# 数据行数 1000000
write_to_file('test.csv', ',', '\r', 1000000)

这里连接数据库的命令要加上额外参数

mysql --local-infile -u root -p

启用文件导入

SET GLOBAL local_infile = 1;

然后创建一张和数据列匹配的表，比如匹配这里的例子的表

CREATE TABLE person (
    id int PRIMARY KEY COMMENT '唯一标识',
    name char(12) COMMENT '名字',
    gender tinyint COMMENT '性别',
    age tinyint COMMENT '年龄'
);

导入文件

LOAD DATA LOCAL INFILE 文件路径 INTO TABLE 表名 FIELDS TERMINATED BY 分隔符 LINES TERMINATED BY 换行符;

导入一百万行数据用了 22.96s

主键优化

插入的数据会按照主键顺序储存，最好是按照主键顺序插入，这样每次插入都是接着前一个的后面顺序插入，乱序插入时需要移动数据来保持主键顺序影响效率（页分裂）。创建表的时候最好使用自增主键，业务操作的时候尽量不要修改主键。
尽量降低主键的长度

排序优化

使用前面的 100 万行数据的例子，按照年龄排序，并输出前 1000 行

SELECT * FROM person ORDER BY age LIMIT 1000;

用时 0.69s

再为年龄创建一个索引（因为数据量大，创建过程可能比较耗时）

再次按年龄排序查询，耗时可以忽略不记

分组优化

上面创建了 age 的索引，现在根据 age 分类统计每个年龄的人数

SELECT age, COUNT(*) FROM person GROUP BY age;

然后删掉 age 的索引，再次执行，耗时差不多翻倍

视图

现有数据库中的表查询结果创建出一张新的表（虚拟表），这张新的表就是视图，视图可以看作是一个快捷方式，创建的时候给指定语句整体起个别名，后续使用别名查询就是在创建语句的基础上操作，创建视图并不额外存储表，都是重新执行语句去查询。

基本使用

创建视图

CREATE VIEW 视图名 AS SELECT语句;

查询创建语句

SHOW CREATE VIEW 视图名;

查询视图

SELECT * FROM 视图名;

修改视图方式一

CREATE OR REPLACE VIEW 视图名 AS SELECT语句;

修改视图方式二

ALTER VIEW 视图名 AS SELECT语句;

删除视图

DROP VIEW 视图名;

检查选项

创建视图的时候在后面加上检查选项，当查询语句存在一定的 WHERE 条件，而尝试对视图插入的数据不满足 WHERE 条件时会阻止插入。

CREATE VIEW 视图名 AS SELECT语句 WITH [ CASCADED | LOCAL ] CHECK OPTION ;

不指定 CASCADED 或 LOCAL，默认就是 CASCADED。基于表创建视图的时候，两个没有区别，都会对当前视图的创建语句的 WHERE 条件检查。区别在于基于视图创建视图的时候，LOCAL 只检查当前视图的创建语句，而对于它的所有上级视图的条件不管，而 CASCADED 则是对所有的上级视图起作用。

创建一张表进行测试

CREATE TABLE test (
    id int PRIMARY KEY AUTO_INCREMENT,
    num int
);

LOCAL 测试，创建四个视图

CREATE OR REPLACE VIEW test_view1 AS SELECT * FROM test;
CREATE OR REPLACE VIEW test_view2 AS SELECT * FROM test_view1 WHERE num > 0 WITH LOCAL CHECK OPTION ;
CREATE OR REPLACE VIEW test_view3 AS SELECT * FROM test_view2 WHERE num > 5;
CREATE OR REPLACE VIEW test_view4 AS SELECT * FROM test_view3 WHERE num < 10 WITH LOCAL CHECK OPTION ;

插入 -1 不满足 test_view2 的条件

插入 2 成功，满足 test_view2 和 test_view4 的条件，虽然不满足 test_view3 的条件，但是 test_view3 没有检查选项不受影响

CASCADED 测试，重新创建视图

CREATE OR REPLACE VIEW test_view1 AS SELECT * FROM test;
CREATE OR REPLACE VIEW test_view2 AS SELECT * FROM test_view1 WHERE num > 0;
CREATE OR REPLACE VIEW test_view3 AS SELECT * FROM test_view2 WHERE num > 5;
CREATE OR REPLACE VIEW test_view4 AS SELECT * FROM test_view3 WHERE num < 10 WITH CASCADED CHECK OPTION ;

再次尝试插入 -1 和 2 都失败了，此时 test_view4 的检查条件为 CASCADED，虽然往上都没有检查选项，但是 CASCADED 往上递归，全都会检查，插入 7 的时候成功了，它满足所有视图的条件。

存储过程

“储存过程”的作用有点像一般编程语言里的函数，把一堆功能语句封装起来调用。

语句分隔符修改

MySQL 默认的语句分隔符是英文分号 ;
在 MySQL 客户端中创建存储过程时，还是用分号作为分隔符的话会导致无法正常识别语句，所以需要修改（只在当前会话生效）

DELIMITER 新的分隔符

基本使用

创建存储过程

DELIMITER $$ # 修改分隔符
CREATE PROCEDURE 存储过程名字()
BEGIN
    执行的操作
END $$
DELIMITER ; # 切换回分号

调用自定义的存储过程

CALL 存储过程名字();

查看存储过程定义

SHOW CREATE PROCEDURE 存储过程名字;

删除存储过程

DROP PROCEDURE 存储过程名字;

变量

系统变量

由 MySQL 定义的具有特殊含义的变量，有全局 GLOBAL 和 SESSION 两类，前者针对整个数据库系统生效，后者只对当前客户端连接生效，不指定默认为 SESSION。

查看系统变量

SHOW [SESSION | GLOBAL] VARIABLES; # 查看全部

SHOW [SESSION | GLOBAL] VARIABLES LIKE '关键词'; # 模糊匹配

SELECT @@[SESSION | GLOBAL] 变量名; # 查看指定的

设置系统变量

SET [SESSION | GLOBAL] 变量名 = 值;
SET @@[SESSION | GLOBAL] 变量名 = 值;

用户自定义变量

用户自定义变量没有全局变量，只能在当前客户端连接生效，系统变量是两个@，用户自定义变量则只有一个@。

赋值

SET @变量名 = 值;

SET @变量名 := 值;

SELECT @变量名 := 表达式;

SELECT 字段名 INTO @变量名 FROM 表名; # 将表中的字段值赋值给变量

查看值

SELECT @变量名;

局部变量

可以在储存过程中使用，作用范围介于 BEGIN 和 END 之间。

定义局部变量，赋值操作同上

DECLARE 变量名 变量类型 [DEFAULT ...];

演示

DELIMITER $$
CREATE PROCEDURE p1()
BEGIN
    DECLARE info_count int DEFAULT 0; # 定义局部变量 info_count，默认值为 0
    SELECT count(*) INTO info_count FROM info;
    SELECT info_count;
END $$
DELIMITER ;

CALL p1();

存储过程的参数传递

类似于一般变成语言中函数传入参数和返回值。

这里给出一个例子，创建一个存储过程 p2，有三个参数，分别是传入参数 in_arg，传出参数 out_arg，传入传出参数 inout_arg

DELIMITER $$
CREATE PROCEDURE p2(IN in_arg int,
                    OUT out_arg int,
                    INOUT inout_arg int)
BEGIN
    SET out_arg := in_arg + inout_arg;
    SET inout_arg := 2 * out_arg;
END $$
DELIMITER ;

调用存储过程

SET @inout = 3;
CALL p2(7, @out, @inout);
SELECT @out, @inout;

条件判断

IF

IF 条件1 THEN
...
ELSEIF 条件2 THEN
...
ELSE
...
END IF;

示例

DELIMITER $$
CREATE PROCEDURE p3(score float)
BEGIN
    IF score > 90 THEN
        SELECT '优秀';
    ELSEIF score > 75 THEN
        SELECT '良好';
    ELSEIF score > 60 THEN
        SELECT '及格';
    ELSE
        SELECT '不及格';
    END IF;
END $$
DELIMITER ;

CALL p3(85);

CASE

两种语法参考流程函数部分：https://blog.iyatt.com/?p=12631#_%E6%B5%81%E7%A8%8B%E5%87%BD%E6%95%B0

例一

DELIMITER $$
CREATE PROCEDURE p4(score float)
BEGIN
    CASE
        WHEN score > 90 THEN
            SELECT '优秀';
        WHEN score > 75 THEN
            SELECT '良好';
        WHEN score > 60 THEN
            SELECT '及格';
        ELSE
            SELECT '不及格';
    END CASE;
END $$
DELIMITER ;

CALL p4(59);

例二

DELIMITER $$
CREATE PROCEDURE p5(grades char(1))
BEGIN
    CASE grades
        WHEN 'A' THEN
            SELECT '优秀';
        WHEN 'B' THEN
            SELECT '良好';
        WHEN 'C' THEN
            SELECT '一般';
        ELSE
            SELECT '未知';
    END CASE;
END $$
DELIMITER ;

CALL p5('C');

循环

WHILE

WHILE 条件 DO
    ...
END WHILE

示例
累加 1-100

DELIMITER $$
CREATE PROCEDURE p6(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    DECLARE counter int DEFAULT 0;
    WHILE counter < n DO
        SET counter := counter + 1;
        SET sum := sum + counter;
    END WHILE;
    SELECT sum;
END $$
DELIMITER ;

CALL p6(100);

REPEAT

WHILE 是满足条件执行循环，REPEAT 是满足条件退出循环

REPEAT
    ...
    UNTIL 条件
END REPEAT;

示例

DELIMITER $$
CREATE PROCEDURE p7(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    REPEAT
        SET sum := sum + n;
        SET n := n - 1;
    UNTIL n = 0
    END REPEAT;
    SELECT sum;
END $$
DELIMITER ;

CALL p7(100);

LOOP

LOOP 循环本身不带退出条件判断，是可以实现无限循环的，通过自行调用 LEAVE 退出循环，或者调用 ITERATE 跳过本轮循环，像一般编程语言里的 break 和 continue。

自定义循环标签名: LOOP

END LOPP 标签名;

示例
累加 1-100 的偶数

DELIMITER $$
CREATE PROCEDURE p8(n int)
BEGIN
    DECLARE sum int DEFAULT 0;
    my_sum: LOOP
        IF n <= 0 THEN
            LEAVE my_sum; # 退出循环
        END IF ;

        IF n % 2 = 1 THEN
            SET n := n -1;
            ITERATE my_sum; # 跳过本轮循环
        END IF ;

        SET sum := sum + n;
        SET n := n - 1;
    END LOOP my_sum;
    SELECT sum;
END $$
DELIMITER ;

CALL p8(100);

游标和条件处理程序

游标的作用和一般编程语言里面的迭代器类似。

声明

DECLARE 游标名称 CURSOR FOR 查询语句;

打开

OPEN 游标名称;

获取游标记录

FETCH 游标名称 INTO 变量;

关闭游标

CLOSE 游标名称;

条件处理程序
这个有点像 Linux 捕获信号

DECLARE hander_action HANDLER FOR condition_value statement;

hander_action：

CONTINUE 继续执行当前程序
EXIT 终止执行当前程序

condition_value：

SQLSTATE sqlstate_value 状态码，如 02000
SQLWARNING 所有以 01 开头的 SQLSTATE 的简写
NOT FOUND 所有以 02 开头的 SQLSTATE 的简写
SQLEXCEPTIO 所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 的简写

示例
查询 info 表的 name 列和 age 列，并将它们存到另外一张表 temp_table 中

DELIMITER $$
CREATE PROCEDURE p9()
BEGIN
    DECLARE name1 char(2);
    DECLARE age1 tinyint;
    DECLARE my_cursor CURSOR FOR SELECT name, age FROM info; # 声明一个遍历 info 表 name、age 列的游标
    DECLARE EXIT HANDLER FOR SQLSTATE '02000' CLOSE my_cursor; # 没有这个条件处理程序，当 FETCH 遍历到没有数据的位置会报错 02000，所以这里捕获这个状态码，来主动执行退出

    OPEN my_cursor; # 打开游标

    # 临时表
    DROP TABLE IF EXISTS temp_table;

    CREATE TABLE temp_table (
        id tinyint PRIMARY KEY AUTO_INCREMENT,
        name char(2),
        age tinyint
    );

    WHILE TRUE DO
        FETCH my_cursor INTO name1, age1; # 从游标中取值
        INSERT INTO temp_table (name, age) VALUES (name1, age1); # 将取值插入新表中
    END WHILE ;

    CLOSE my_cursor; # 关闭游标

END $$
DELIMITER ;

CALL p9();

info 表

新建的表

存储函数

存储函数等同于有专用返回值的存储过程，存储函数能做的，储存过程也能实现。存储函数要求必须有返回值，而存储过程可以通过参数进行返回，也可以不用。

CREATE FUNCTION 存储函数名称([参数列表])
RETURNS type [characteristic]
BEGIN
    ...
    RETURN ...;
END

characteristic：

DETERMINSTIC 相同的输入参数总是产生相同的结果
NO SQL 不包含 SQL 语句
READS SQL DATA 包含读取数据的语句，但不包含写入数据的语句

示例
从1累加到100

DELIMITER $$
CREATE FUNCTION f(n int)
RETURNS int DETERMINISTIC
BEGIN
    DECLARE sum int DEFAULT 0;
    WHILE n > 0 DO
        SET sum := sum + n;
        SET n := n - 1;
    END WHILE ;
    RETURN sum;
END $$
DELIMITER ;

SET @sum = f(100);
SELECT @sum;

触发器

类似于事件监听，出现插入、修改、删除操作的时候可以完成指定操作，使用别名 OLD 和 NEW 引用发生变化的内容。
目前只支持行级触发器，不支持语句级触发器。比如一个插入语句插入多行数据，每插入一行就会触发一次，而不是整个语句只触发一次。

INSERT 触发器：NEW 表示将要或者已经新增的数据
UPDATE 触发器：OLD 表示修改前的数据，NEW 表示将要或者已经修改后的数据
DELETE 触发器：OLD 表示将要或者已经删除的数据

创建

CREATE TRIGGER 触发器名字
BEFORE / AFTER INSERT / UPDATE / DELETE
ON 表名 FOR EACH ROW
BEGIN
    ...
END;

查看

SHOW TRIGGERS;

删除

DROP TRIGGER 触发器名字;

示例
创建一张表 new_table，然后创建一张日志表 user_logs。创建一个触发器，在对 new_table 执行插入操作后向 user_logs 日志表写入操作类型，操作时间，操作的 ID，操作写入了什么内容。

CREATE TABLE new_table (
    id tinyint PRIMARY KEY AUTO_INCREMENT,
    name char(2),
    age tinyint
);

CREATE TABLE user_logs (
    id tinyint NOT NULL PRIMARY KEY AUTO_INCREMENT,
    operation varchar(20) NOT NULL COMMENT '操作类型',
    operate_time datetime NOT NULL COMMENT '操作时间',
    operate_id tinyint NOT NULL COMMENT '操作的 ID',
    operate_params varchar(500) COMMENT '操作参数'
);

DELIMITER $$
CREATE TRIGGER new_table_trigger
    AFTER INSERT ON new_table FOR EACH ROW
BEGIN
    INSERT INTO user_logs VALUES
                              (NULL, 'insert', now(), NEW.id, concat('插入的数据内容为：id=', NEW.id, 'name=', NEW.name, 'age=', NEW.age));
END $$
DELIMITER ;

INSERT INTO new_table (name, age) VALUES
                          ('小强', 10),
                          ('小红', 20);

在进行插入操作后，日志表中通过触发器自动记录了操作内容

MySQL 学习记录