约束

数据完整性是指数据的精确性和可靠性。它是防止数据中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，SQL规范以约束的方式对表数据进行额外的条件限制，从以下四个方面考虑：

实体完整性：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性：例如，年龄范围0 - 120，性别范围男 / 女
引用完整性：例如，员工所在部门，在部分表中要能找到这个部门
用户自定义完整性：例如，用户名唯一、密码不能为空等，本部分经理的工资不得高于本部门职工的平均工资的5倍

非空约束

限定某个字段 / 某列的值不允许为空。关键字：NOT NULL。默认所有的类型的值都可以是NULL，包括INT、FLOAT等数据类型；非空约束只能出现在表对象的列上，只能某个列单独限定非空，不能组合非空；一个表可以有很多列都分别限定了非空；空字符串不等于NULl，0也不等于NULL

1 2	ALTER TABLE emp MODIFY sex VARCHAR(30) NOT NULL;

唯一性约束

用来限制某个字段 / 某列的值不能重复。关键字：UNIQUE。同一个表可以有多个唯一约束；唯一约束可以是某一个列的值唯一，也可以是多个列组合的值唯一；唯一性约束允许列值为空；在创建唯一约束的时候，如果不给唯一约束命名，就默认和列名相同；MySQL会给唯一约束的列默认创建一个唯一索引

1	ALTER TABLE emp add UNIQUE key(字段列表)

主键约束

主键约束相当于唯一约束 + 非空约束的组合，主键约束列不允许重复，也不允许出现空值

一个表最多只能有一个主键约束，建立主键约束可以在列级别创建，也可以在表级别上创建
主键约束对应着表中的一列或者多列（复合主键）
如果是多列组合的复合主键约束，那么这些列都不允许为空值，并且组合的值不允许重复
MySQL的主键名总是PRIMARY，就算自己命名了主键约束名也没用
当创建主键约束时，系统默认会在所在的列或列组合上建立对应的主键索引（能够根据主键查询的，就根据主键查询，效率更高）如果删除了主键约束，主键约束对应的索引就自动删除了
需要注意的一点是，不要修改主键字段的值。因为主键是数据记录的唯一标识，如果修改了主键的值，就有可能破坏数据的完整性

自增列

某个列的值自增，关键字：auto_increment。一个表最多只能有一个自增长列；当需要产生唯一标识符或顺序值时，可设置自增长；自增长列约束的列必须是键列（主键列，唯一键列）自增约束的列的数据类型必须是整数类型；如果自增列指定了0和NULL，会在当前最大值的基础上自增；如果自增列手动指定了具体值，直接赋值为具体值

MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key) + 1，在MySQL重启后，会重置AUTO_INCREMENT = max(primary) + 1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。但是在MySQL 8.0将自增主键的计算器持久化到了重做日志中，每次计数器发生变化，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值

外键约束

限定某个表的某个字段的引用完整性。关键字：FOREIGN KEY。主表：被引用的表，被参考的表；从表：引用别人的表，参考别人的表；例如员工表的员工所在部门这个字段的值要参考部门表：部门表是主表。员工表是从表

从表的外键列，必须引用 / 参考主表的键主键或唯一约束的列。因为被依赖 / 被参考的值必须是唯一的
在创建外键约束时，如果不给外键约束命名，默认名不是列名，而是自动产生一个外键名，也可以指定外键约束名
创建表时就指定外键约束的话，先创建主表，再创建从表
删表时，先删从表，再删除主表
当主表的记录被从表参照是，主表的记录将不允许删除，如果要删除数据，需要先删除从表中依赖该记录的数据，然后才可以删除主表的数据
在从表中指定外键约束，并且一个表可以建立多个外键约束
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样，逻辑意思一致
当创建外键约束时，系统会默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名
删除外键约束时，必须手动删除对应的索引

在MySQL里，外键约束是有成本的，需要消耗系统资源。对于大并发的SQL操作，有可能不适合。比如大型网站的中央数据库，可能会因为外键约束的系统开销变得非常慢。所以，MySQL允许你不实用系统自带的外键约束，在应用层面完成检查数据一致性的逻辑，也就是说，即使你不用外键约束，也要想办法通过应用层面的附加逻辑，来实现外键约束的功能，确保数据的一致性

CHECK约束

检查某个字段的值是否符号xx要求，一般指的是值的范围，关键字：CHECK。MySQL 5.7可以使用check约束，但check约束对数据验证没有任何作用。添加数据时，没有任何错误和警告，但是在MySQL 8.0中可以使用check约束了

CREATE TABLE emp (
  eit INT PRIMARY KEY,
  ename VARCHAR(5),
  gender CHAR CHECK (男 or 女)
);

DEFAULT约束

给某个字段 / 某列指定默认值，一旦设置默认值，在插入数据时，如果此字段没有显示赋值，则赋值为默认值，关键字：DEFAULT。

CREATE TABLE emp (
  id INT,
  last_name VARCHAR(15),
  salary DECIMAL(10,2) DEFAULT 2000
);

面试

为什么建表时加NOT NULL DEFAULT 0？

不想让表中出现NULL值

为什么不想要NULL值？

不好比较，NULL是一种特殊值，比较时只能用专门的IS NULL 和IS NOT NULL来比较。碰到运算符，通常返回NULL；效率不高，影响提高索引效果。因此，我们往往在建表时 NOT NULL DEFAULT 0

AUTO_INCREMENT约束的字段值是从1开始的码？

在MySQL中，默认AUTO_INCREMENT的初始值是1，每新增一条记录，字段值自动加1。设置自增属性的时候，还可以指定第一条插入记录的自增字段的值，这样新插入的记录自增字段值从初始值开始递增，如在表中插入第一条记录，勇士指定id值为5，则以后插入的记录的id值就会从6开始往上增加。添加主键约束时，往往需要设置字段自动增加属性

并不是每个表都可以任意选择存储引擎？

外键约束不能跨引擎使用。MySQL支持多种存储引擎，每一个表都可以指定不同的存储引擎，需要注意的是：外键约束是用来保证数据的参照完整性的，如果表之间需要关联外键，却指定了不同的存储引擎，那么这些表之间是不能创建外键约束的。所以说，存储引擎的选择也不完全是随意的

视图

常见的数据库对象

对象	描述
表(TABLE)	表是存储数据的逻辑单元，以行和列的形式存在，列就是字段，行就是记录
数据字典	就是系统表，存放数据库相关信息的表。系统表的数据通常由数据库系统维护，程序员通常不应该修改，只可查看
约束(CONSTRAINT)	执行数据校验的规则，用于保证数据完整性的规则
视图(VIEW)	一个或者多个数据表里的数据的逻辑显示，视图并不存储数据
索引(INDEX)	用于提高查询性能，相当于书的目录
存储过程(PROCEDURE)	用于完成一次完整的业务处理，没有返回值，但可通过传出参数将多个值传给调用环境
存储函数(FUNCTION)	用于完成一次特定的计算，具有一个返回值
触发器(TRIGGER)	相当于一个时间监听器，当数据库发生特定事件后，触发器被触发，完成相应的处理

视图概述

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。比如，针对一个公司的销售人员，我们只想给他看部分数据，而某些特殊的数据，比如采购的价格，则不会提供给他，再比如，人员薪酬是个敏感的字段，那么只给某个级别以上的人员开放，其他人的查询视图中则不提供这个字段

视图是一种虚拟表，本身是不具有数据的，占用很少的内存空间，它是SQL中的一个重要概念
视图建立在已有表的基础上，视图赖以建立的这些表称为基表
视图的创建和删除只影响视图本身，不影响对应的基表，但是当对视图的数据进行增加、删除和修改操作时，数据表中的数据会相应地发生变化，反之亦然
向视图提供数据内容的语句为SELECT语句，可以将视图理解为存储起来的SELECT语句
视图是向用户提供基表数据的另一种表现形式。通常情况下，小型项目的数据库可以不使用视图，但是在大型项目中，以及数据表比较复杂的情况下，视图的价值就凸显出来了，它可以帮我们把经常查询的结果集放到虚拟表中，提升使用效率。理解和使用起来都非常方便

创建视图

在CREATE VIEW 语句中嵌入子查询

CREATE [OR REPLACE]
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称[(字段列表)]
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION]

查看视图

查看数据库的表对象、视图对象

1	SHOW TABLES;

查看视图的结构

1	DESC / DESCRIBE 视图名称;

查看视图的属性信息

1	SHOW TABLE STATUS LIKE 视图名称\G

执行结果显示，注释Comment为VIEW，说明该表为视图，其他的信息为NULL，说明这是一个虚表

查看视图的详细定义信息

1	SHOW CREATE VIEW 视图名称;

更新视图

MySQL支持使用INSERT、UPDATE和DELETE语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。要使视图可更新，视图中的行和底层基本表中的行之间必须存在一对一的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

在定义视图的时候指定了ALGORITHM = TEMPTABLE，视图将不支持INSERT和DELETE操作
视图中不包含基表中所有被定义为非空又未指定默认值的列，视图将不支持INSERT操作
在定义视图的SELECT语句中使用了JOIN联合查询，视图将不支持INSERT和DELETE操作
在定义视图的SELECT语句后的字段列表中使用了数学表达式或子查询，视图将不支持INSERT，也不支持UPDATE使用了数学表达式、子查询的字段值
在定义视图的SELECT语句后的字段列表中使用DISTINCT、聚合函数、GROUP BY、 HAVING、UNION等，视图将不支持INSERT、UPDATE、DELETE
在定义视图的SELECT语句中包含了子查询，而子查询中引用了FROM后面的表，视图将不支持INSERT、UPDATE、DELETE
视图定义基于一个不可更新视图

虽然可以更新视图数据，但总的来说，视图作为虚拟表，主要用于方便查询，不建议更新视图数据。视图数据的更改，都是通过对实际数据表里数据的操作来完成的

修改视图

使用CREATE OR REPLACE VIEW子句修改视图

CREATE OR REPLACE VIEW emp
(id_number, name, sal, department_id)
AS
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 80;

说明：CREATE VIEW子句中各列的别名应和子查询中各列相对应

ALTER VIEW

1
2
3

ALTER VIEW 视图名称
AS
查询语句

删除视图

删除视图只是删除视图的定义，并不会删除基表的数据

1	DROP VIEW IF EXISTS 视图名称1，视图名称2，视图名称3，...;

说明：基于视图A、B创建了新的视图C，如果将视图A或者视图B删除，会导致视图C的查询失败。这样的视图C需要收到那个删除或修改，否则影响使用

总结

视图优点

操作简单

将经常使用的查询操作定义为视图，可以使开发人员不需要关心视图对应的数据表的结构、表于表之间的关联关系，也不需要关心数据表之间的业务逻辑和查询条件，而只需要简单地操作视图即可，极大简化了开发人员对数据库的操作

减少数据冗余

视图跟实际数据表不一样，它存储的是查询语句。所以，在使用的时候，我们要通过定义视图的查询语句来获取结果集。而视图本身不存储数据，不占用数据存储的资源，减少了数据冗余

数据安全

MySQL将用户对数据的访问限制在某些数据的结果集上，而这些数据的结果集可以使用视图来实现。用户不必直接查询或操作数据表。这也可以理解为视图具有隔离性。视图相当于在用户和实际的数据表之间加了一层虚拟表，同时，MySQL可以根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息。这在一定程度上保障了数据表中数据的安全性

适应灵活多变的需求

当业务系统的需求发生变化后，如果需要改动数据表的结构，则工作量相对较大，可以使用视图来减小改动的工作量，这种方式在实际工作中使用得比较多

能够分解复杂的查询逻辑

数据库中如果存在复杂的查询逻辑，则可以将问题进行分解，创建多个视图获取数据，再将创建的多个视图结合起来，完成复杂的查询逻辑

视图不足

如果我们在实际数据表的基础上创建了视图，那么，如果实际数据表的结果变更了，我们就需要即使对相关的视图进行相应的维护。特别是嵌套的视图（就是在视图的基础上创建视图）维护会变得比较复杂，可读性不好，容易变成系统的潜在隐患。因为创建吃土的SQL查询可能会对字段重命名，也可能包含复杂的逻辑，这些都会增加维护的成本。实际项目中，如果视图过多，会导致数据库维护成本的问题。所以，在创建视图的时候，你要结合实际项目需求，综合考虑视图的优点和不足，这样才能正确使用视图，使系统整体达到最优

存储过程

存储过程概述

存储过程的英文是Stored Procedure。它的思想很简单，就是一组经过预先编译的SQL语句的封装。执行过程：存储过程预先存储在MySQL服务器上，需要执行的时候，客户端只需要向服务器发出调用存储过程的命令，服务端就可以把预先存储好的这一系列SQL语句全部执行

简化操作，提高了SQL语句的重用性，减少了开发程序员的压力
减少操作过程中失误，提高效率
减少网络传输量（客户端不需要把所有的SQL语句通过网络发给服务器）
减少了SQL语句暴露在网上的风险，也提高了数据查询的安全性

存储过程和视图、函数的对比？

它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。不过它和视图不同，视图是虚拟表，通常不对底层数据直接操作，而存储过程是程序化的SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些复杂的数据处理。一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可，相比较与函数，存储过程是没有返回值的

创建存储过程

CREATE PROCEDURE 存储过程名(IN | OUT | INOUT 参数名 参数类型,...)
[characteristics]
BEGIN
  存储过程体
END

查询最低工资

CREATE PROCEDURE show_min_salary ( OUT ms DOUBLE ) 
BEGIN
	SELECT MIN( salary ) INTO ms 
	FROM employees; 
END

调用

1	CALL show_min_salary(@ms);

查看变量值

1	SELECT @ms;

函数

创建函数

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics...]
BEGIN
  函数体
END

若在创建存储函数中报错This function has none of DETERMINISTIC，有两种处理方法：

加上必要的函数特性DETERMINISTIC ｜ NO SQL ｜ READS SQL DATA
SET GLOBAL log_bin_trust_function_creator = 1;

CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25)
	DETERMINISTIC
	CONTAINS SQL
	READS SQL DATA
BEGIN
	RETURN (SELECT email FROM employees WHERE last_name = 'Abel');
END

调用

1	SELECT email_by_name();

对比存储函数和存储过程

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL存储过程	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT函数	只能是一个	一般用于查询结果为一个值并返回

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表、删除表）和事务操作，这些功能是存储函数不具备的

查看函数/存储过程

使用SHOW CREATE语句查看存储过程和函数的创建信息

1	SHOW CREATE {PROCEDURE \| FUNCTION} 存储过程名或函数名;

使用SHOW STATUS 语句查看存储过程和函数的状态信息

1	SHOW {PROCEDURE \| FUNCTION} STATUS [LIKE pattern];

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。[LIKE pattern]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数信息

从information_schema.Routines表中查看存储过程和函数信息
MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息

1 2	SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 存储过程或函数的名 [AND ROUTINE_TYPE = {PROCEDURE \| FUNCTION}];

修改函数/存储过程

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现

1	ALTER{PROCEDURE \| FUNCTION} 存储过程或函数的名 [characteristic ...]

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同

1
2
3

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

CONTAINS SQL：表示子程序包含SQL语句，但不包含读或写数据的语句
NO SQL：表示子程序中不包含SQL语句
READS SQL DATA：表示子程序中包含读数据的语句
MODIFIES SQL DATA：白噢是子程序中包含写数据的语句
SQL SECURITY { DEFINER | INVOKER }：指明谁有原先来执行。DEFINER表示只有定义者自己才能够执行。INVOKER表示调用者可以执行
COMMENT：表示注释信息

删除函数/存储过程

删除存储过程和函数，可有使用DROP语句，其语法结构如下：

1	DROP {PROCEDURE \| FUNCTION} [IF EXISTS] 存储过程或函数名

关于存储过程使用的争议

优点

存储过程可以一次编译多次使用。存储过程只在创建时进行编译，之后的使用都不需要重新编译，这就提升了SQL的执行效率
可以减少开发工作量。将代码封装成模块，实际上时编程的核心思想之一，这样可以把复杂的问题拆解成不同的模块，然后模块之间可以重复使用，在减少开发工作量的同时，还能保证代码的结构清晰
存储过程的安全性强，我们在设定存储过程的时候可以设置对用户的使用权限，这样就和视图一样具有较强的安全性
可以减少网络传输量。因为代码封装到存储过程中，每次使用只需要调用存储过程即可，这样就减少了网络传输量
良好的封装行。在进行相对复杂的数据库操作时，原本需要使用一条一条的SQL语句，可能要连接多次数据库才能完成的操作，现在变成了一次存储过程，只需要连接一次即可

缺点

可移植性差。存储过程不能跨数据移植，比如在MySQL、Oracle和SQL Server里编写的存储过程，再换成其他数据库时都需要重新编写
调试困难。只有少数DBMS支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程调试，但要收费
存储过程的版本管理很困难。比如数据表索引发生了变化，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本空值，版本迭代更新的时候很麻烦
它不适合高并发的场景。高并发的场景需要减少数据库的压力，有时候数据库采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护，增加数据库压力，显然就不适用了

阿里开发规范强制禁止使用存储过程，存储过程难以调试和扩展，更没有移植性

变量

系统变量分为全局系统变量（需要添加global关键字）以及会话系统变量（需要添加session关键字），有时也把全局系统变量简称为全局变量，有时也会把会话系统变量称为local变量，如果不写，默认会话级别。静态变量属于特殊的全局系统变量

全局系统变量针对与所有会话有效，但不能跨重启
会话系统变量仅针对当前会话有效，会话期间，当前会话某个会话系统变量值修改，不会影响其他会话同一个会话系统变量的值
会话1对某个全局系统变量值的修改会导致会话2中同一个全局系统变量值的修改

在MySQL中有些系统变量只能时全局的，例如max_connections用于限制服务器的最大连接数；有些系统变量作用域可以是全局又可以是会话，例如character_set_client用于设置客户端的字符集；有些系统变量的作用域只能是当前会话，例如pseudo_thread_id用于标记当前会话的MySQL连接ID

查看系统变量

查看所有全局/会话变量

1	SHOW GLOBAL / SESSION VARIABLES;

作为MySQL编码规范，MySQL中的系统变量以两个@开头，其中@@GLOBAL仅用于标记全局系统变量，@@SESSION仅用于标记会话系统变量。@@首先标记会话系统变量，如果会话系统不存在。则标记全局系统变量

1	SELECT @@GLOVAL / SESSION.变量名;

修改系统变量

有些时候，数据库管理员需要修改系统变量的默认值，以便修改当前会话或者MySQL服务示例的属性、特征。具体方法：

修改MySQL配置文件，继而修改MySQL系统变量的值（该方法需要重启MySQL服务）
在MySQL服务运行期间，使用SET命名重新设置系统变量的值

1	SET @@ GLOBAL.变量名 = 变量值;

用户变量

用户变量是用户自己定义的，作为编码规范，MySQL中的用户变量以一个@开头。根据作用范围不同，又分为会话用户变量和局部变量

会话用户变量：作用域和会话变量一样，只对当前连接会话有效
局部变量：只在BEGIN和END语句块中有效。局部变量只能在存储过程和函数中使用

1
2
3

SET @m := 1;
SELECT @count := COuNT(*) FROM employees;
SELECT AVG(salary) INTO @avg_sal FROM employees;

局部变量

可以使用DECLARE语句定义一个局部变量。作用域：仅仅在定义它的BEGIN END 中有效。位置：只能放在BEGIN END，而且只能放在第一句

CREATE PROCEDURE test() 
BEGIN
  DECLARE a INT DEFAULT 0;
  DECLARE b INT;
  SET a = 1;
  SET b := 2;
  SELECT last_name INTO emp_name FROM employees WHERE employee_id = 101;
  SELECT a,b,emp_name
END

对比会话用户比纳凉与局部变量

	作用域	定义位置	语法
会话用户变量	当前会话	会话的任何地方	加@符号，不用指定符号
局部变量	定义它的BEGIN END中	BEGIN END的第一句话	一般不用加@，需要指定类型

定义条件与处理程序

定义条件是事先定义程序执行过程中可能遇到的问题，处理程序定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能够继续执行。这样可以增强存储过程程序处理问题的能力，避免程序一场停止运行。说明：定义条件和处理程序在存储过程、存储函数中都是支持的

定义条件

定义条件就是给MySQL中的错误命名，这有助于存储的程序代码更清晰。它将一个错误名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中，语法格式如下：

1	DECLARE 错误名称 CONDITION FOR 错误码(或错误条件)

错误码的说明：

MySQL_error_code和sqlstate_value都可以表示MySQL的错误
MySQL_error_code是数值类型错误码
sqlstate_value是长度为5的字符串类型错误码
例如，在ERROR 1418(HY000)中，1418是MySQL_error_code，HY000是sqlstate_value

定义处理程序

可以为SQL执行过程中发生的某种类型的错误定义特殊的处理程序。定义处理程序时，使用DECLARE语句的语法如下：

1	DECLARE 处理方式 HANDLER FOR 错误类型处理语句

处理方式有3个取值：CONTINUE、EXIT、UNDO

CONTINUE：表示遇到错误不处理、继续执行
EXIT：表示遇到错误马上退出
UNDO：表示遇到错误后撤回之前的操作。MySQL暂时不支持这样的操作

错误类型（即条件）可以有如下取值：

SQLSTATE 字符串错误码：表示长度为5的sqlstate_value类型的错误代码
MySQL_error_code：匹配数值类型的代码
错误名称：表示DECLARE CONDITION定义的错误条件代码
SQLWARNING：匹配所有以01开头的SQLSTATE错误代码
NOT FOUND：匹配所有以02开头的SQLSTATE错误代码
SQLEXCEPTION：匹配所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE错误代码

处理语句：如果出现上述条件之一，则采用对应的处理方式，并执行指定的处理语句。语句可以是像SET 变量 = 值这样的简单语句，也可以是使用BEGIN END编写的复合语句

流程控制

解决复杂问题不可能通过一个SQL语句完成，我们需要执行多个SQL操作。流程控制语句的作用就是控制存储过程中SQL语句的执行顺序，是我们完成复杂操作不可少的一部分。只要是执行的程序，流程就分三大类：

顺序结构：程序从上往下一次执行
分支结构：程序按条件进行选择执行，从两条或多条路径中选择一条执行
循环结构：程序满足一定条件下，重复执行一组语句

针对于MySQL的流程控制语句主要有3类。注意：只能用于存储结构。

条件判断语句：IF语句和CASE语句
循环语句：LOOP、WHILE和REPEAT语句
跳转语句：ITERATE和LEAVE语句

IF

IF 表达式1 THEN 操作1
[ELSEIF 表达式2 THEN 操作2]......
[ELSE 操作N]
END IF

根据表达式的结果为TRUE或FALSE执行相应的语句。这里[]中的内容是可选的。特点：不同的表达式对应不同的操作；使用在BEGIN END中

CREATE PROCEDURE test_if()
BEGIN
	DECLARE stu_name VARCHAR(15);
	IF stu_name IS NULL THEN SELECT 'stu_name is null';
	END IF;
END

CASE

CASE语句的语法结构1：

CASE 表达式
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case](如果是放在BEGIN END中需要加上case，如果放在select后面不需要)

CASE语句的语法结构2：

CASE
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号)
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号)
...
ELSE 结果n或语句n(如果是语句，需要加分号)
END [case](如果是放在BEGIN END中需要加上case，如果放在SELECT后面不需要)

CREATE PROCEDURE test_case()
BEGIN
  DECLARE var INT DEFAULT 2;
  CASE var
    WHEN 1 THEN SELECT 'var = 1';
    WHEN 2 THEN SELECT 'var = 2';
    WHEN 3 THEN SELECT 'var = 3';
    ELSE SELECT 'other value';
  END CASE;
END

LOOP

LOOP循环语句用来重复执行某些语句。LOOP内的语句移植重复执行直到循环退出（使用LEAVE子句），跳出循环过程。LOOP语句基本格式如下：

1
2
3

[loop_label:] LOOP
  循环执行的语句
END LOOP [loop_label];

其中loop_label表示LOOP语句的标注名称，该参数可以忽略

CREATE PROCEDURE test_loop() 
BEGIN
  DECLARE num INT DEFAULT 1;
  loop_label:LOOP
    SET num = num + 1;
    IF num >= 10 THEN LEAVE loop_label;
    END IF;
  END LOOP loop_label;
  SELECT num;
END

WHILE

WHILE语句创建一个带条件判断的循环过程。WHILE在执行语句时，先对指定的表达式进行判断，如果为真，就执行循环内的语句，否则就退出循环。WHILE语句的基本格式如下：

1
2
3

[while_label:] WHILE 循环条件 DO
  循环体
END WHILE [while_label];

while_label为WHILE语句的标注名称；如果循环条件为真，WHILE语句内的语句或语句群被执行，直至循环条件为假，退出循环

CREATE PROCEDURE test_while()
BEGIN
  DECLARE num INT DEFAULT 1;
  WHILE num <= 10 DO
    SET num = num + 1;
  END WHILE;
  SELECT num;
END

REPEAT

REPEAT语句创建一个带条件判断的循环过程。与WHILE循环不同的是，REPEAT循环首先会执行一次循环，然后在UNTIL中进行表达式的判断，如果满足条件就退出，即END REPEAT；如果条件不满足，则会就继续执行循环，直到满足退出条件为止。REPEAT语句的基本格式如下：

[repeat_label:] REPEAT
  循环体的语句
UNTIL 结束循环的条件表达式
END REPEAT [repeat_label]

repeat_label为REPEAT语句的标注名称，该参数可以省略；REPEAT语句内的语句或语句群被重复，直至expr_condition为真

CREATE PROCEDURE test_repeat()
BEGIN
  DECLARE num INT DEFAULT 1;
  REPEAT
    SET num = num + 1;
    UNTIL num >= 10
  END REPEAT;
  SELECT num;
END

对比这三种循环结构

这三种循环都可以省略名称，但如果循环中添加了循环控制语句（LEAVE或ITERATE）则必须添加名称。LOOP:一般用于实现简单的死循环；WHILE：先判断后执行；REPEAT：先执行后判断，无条件至少执行一次

LEAVE

LEAVE语句可以用在循环语句内，或者以BEGIN和END包裹起来的程序体内，表示跳出循环或者跳出程序体的操作。如果你有面向过程的编程语言的使用经验，你可以把LEAVE理解为break，基本格式如下：

LEAVE 标记名

CREATE PROCEDURE leave_begin(IN num INT)
begin_label:BEGIN
  IF num <= 0
    THEN LEAVE begin_label;
  ELSEIF num = 1
    THEN SELECT AVG(salary) FROM employees;
  ELSEIF num = 2
    THEN SELECT MIN(salary) FROM employees;
  ELSE 
    SELECT MAX(salary) FROM employees;
END

ITERATE

ITERATE语句只能用子啊循环语句（LOOP、REPEAT和WHILE语句）内，表示重新开始循环，将执行顺序转到语句段开头处。如果你有面向过程的编程语言的使用经验，你可以把ITERATE理解为continue。基本语法格式如下：

1	ITERATE label

CREATE PROCEDURE test_iterate()
BEGIN
  DECLARE num INT DEFAULT 0;
  loop_label:LOOP:
    SET num = num + 1;
    IF num < 10 
      THEN ITERATE loop_label;
    ELSEIF num > 15
      THEN LEAVE loop_label;
    END IF;
    SELECT num;
  END LOOP;
END

游标

虽然我们也可以通过筛选条件WHERE和HAVING，或者是限定返回记录的关键字LIMIT返回记录，但是，却无法在结果集中像指针一样，向前定位一条记录、向后定位一条记录，或者是随意定位到某一条记录，并对记录的数据进行处理。

这个时候，就可以用到游标。游标提供了一种灵活的操作方式，让我们能够对结果集中的每一条记录进行定义，并对指向的记录中的数据进行操作的数据结构。游标让SQL这种面向集合的语言有了面向过程开发的能力。在MySQL中，使用DECLARE关键字来声明游标，其语法基本形式如下：

1	DECLARE cursor_name CURSOR FOR select_statement;

这个语法适用于MySQL、SQL Server、DB2和MariaDB。如果是Oracle或者PostgreSQL，需要写成：

1	DECLARE cursor_name CURSOR IS select_statement;

要使用SELECT语句来获取数据结果集，而此时还没有开始遍历数据，这里select_statement代表的是SELECT语句，返回一个用于创建游标的饿结果集。例如：

1 2	DECLARE cur_emp CURSOR FOR SELECT employee_id, salary FROM employees;

打开游标的语法如下：

1	OPEN cursor_name

当我们定义好游标之后，如果想要使用游标，必须先打开游标。打开游标的时候SELECT语句的查询结果集就会送到游标工作区，为后面游标的逐条读取结果集中的记录做准备

使用游标（从游标中取得数据）语法如下：

1	FETCH cursor_name INTO var_name [, var_name]...

这句的作用是使用cursor_name这个右边来读取当前行，并且将数据保存到var_name这个变量中，游标指针指到下一行。如果游标读取的数据列有多个列名，则在INTO关键字后面赋值给多个变量名即可。注意：var_name必须在声明游标之前就定义好。游标的查询结果集中的字段必须跟INTO后面的变量数一致，否则在存储过程执行的时候MySQL会提示错误

有OPEN就会有CLOSE，也就是打开和关闭游标，当我们使用完游标后需要关闭该游标，因为游标会占用系统资源，如果不及时关闭，游标会一致保持到存储过程结束，影响系统运行效率。而关闭游标的操作，会释放游标占用的系统资源。其语法如下：

1	CLOSE cursor_name

举例

CREATE PROCEDURE get_count_by_limit_total_salary(IN limit_total_salart DOUBLE, OUT total_count INT)
BEGIN
  DECLARE sum_sal DOUBLE DEFAULT 0.0;
  DECLARE emp_sal DOUBLE;
  DECLARE emp_count INT DEFAULT 0;
  DECLARE emp_cursor CURSOR FOR SELECT salary FROM employees ORDER BY salary DESC;
  OPEN emp_cursor;
  REPEAT
    FETCH emp_cursor INTO emp_sal;
    SET sum_sal = sum_sal + emp_sal;
    SET emp_count = emp_count + 1;
    UNTIL sum_sal >= limit_total_salary
  END REPEAT;
  SET total_count = emp_count;
  CLOSE emp_cursor;
END

游标是MySQL的一个重要的功能，为逐条读取结果集中的数据，提供了完美地解决方案。跟在应用层面实现相比，游标可以在存储程序中使用，效率高，程序也更加简洁。但同时也会带来一些性能问题，比如在使用游标的过程中，会对数据进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会消耗系统资源，造成内存不足，这是因为游标是在内存中进行的处理

触发器

MySQL从5.0.2版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。触发器是由事件来触发某个操作，这些时间包括INSERT、UPDATE、DELETE时间。所谓时间就是指用户的动作或者触发某项行为。如果定义了触发程序，当数据执行这些语句时候，就像雨事件发生了，就会自动激发触发器执行相应的操作

创建触发器

创建触发器的语法结构是：

CREATE TRIGGER 触发器名称
{BEGIN | AFTER} {INSERT | UPDATE | DELETE} ON 表名
FOR EACH ROW
触发器执行的语句块;

CREATE TRIGGER before_insert_test_tri
BEFORE INSERT ON test_trigger
FOR EACH ROW
BEGIN
  INSERT INTO test_trigger_log(t_lig)
  VALUES('before insert...')
END

查看触发器

查看触发器是查看数据库存在的触发器定义、状态和语法信息等。查看当前数据库的所有触发器的定义：

1	SHOW TRIGGERS\G;

查看当前数据库中某个触发器的定义：

1	SHOW CREATE TRIGGER 触发器名

从系统库information_schema的TRIGGERS表中查看salary_check_trigger触发器的信息

1	SELECT * FROM information_schema.TRIGGERS;

删除触发器

触发器也是数据库对象，删除触发器也用DROP语句，语法格式如下：

1	DROP TRIGGER IF EXISTS 触发器名称

触发器的优缺点

优点

触发器可以确保数据的完整性
触发器可以帮助我们记录操作日志
触发器还可以用在操作数据前，对数据进行合法性检查

缺点

触发器最大的一个问题就是可读性差
相关数据的变更，可能会导致触发器出错

MySQL 8.0其他新特性

MySQL5.7版本直接条约发布了8.9版本，可见这是一个令人兴奋的里程碑版本。MySQL 8版本在功能上做了显著的改进与增强，开发者对MySQL的源代码进行了重构，最突出的一点是多MySQL Optimizer优化器进行了改进。不仅在速度上得到了改善，还为用户带来了更好的性能和更棒的体验

MySQL 8.0新增特性

更简便的NoSQL支持

NoSQL泛指非关系型数据库和数据存储。随着互联网平台的该墓飞速发展，传统的关系型数据库已经越来越不能满足需求，从5.6版本开始，MySQL就开始支持简单的NoSQL存储功能。MySQL 8对这一功能做了优化，以更灵活的方式实现NoSQL功能，不再依赖模式（schema）

更好的索引

在查询中，正确地使用索引可以提高查询效率。MySQL 8汇总新增了隐藏索引和降序索引。隐藏索引可以用来测试去掉索引对查询性能的影响。在查询中混合存在多列索引时，使用降序索引可以提高查询的性能

更完善的JSON支持

MySQL从5.7开始支持原生JSON数据的存储，MYSQL 8对这一功能做了优化，增加了聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()，将参数聚合为JSON数组或对象，新增了行内操作符 ->>，是列路径运算符 -> 的增强，对JSON排序做了提升，并优化了JSON的更新操作

安全很账户管理

MySQL 8中新增了caching_sha2_password授权插件、角色、密码历史记录和FIPS模式支持，这些特性提高了数据库的安全性和性能，使数据库管理员能够更灵活地进行账户管理工作

InnoDB的变化

InnoDB是MySQL默认的存储引擎，是事务型的首选引擎，支持事务安全表（ACID），支持行锁定和外键。在MySQL 8的版本中，InnoDB在自增、索引、加密、死锁、共享锁等当面做了大量的改进和优化，并且支持原子数据定义语言（DDL），提高了数据安全性，对事务提供了更好的支持

数据字典

在之前的MySQL 版本中，字典数据都存储在元数据文件和非事务表中。从MySQL 8开始新增了事务数据字典，在字典里存储着数据库对象信息，这些数据字典存储在内部事务表中

原子数据定义语句

MySQL 8开始支持原子数据定义语句（Automic DDL），即原子DDL。目前，只有InnoDB存储引擎支持原子DDL。原子数据定义语句（DDL）将与DDL操作相关的数据字典更新。存储引擎操作、二进制日志写入结合到一个单独的原子事务中，这使得即使服务器崩溃，事务也会提交或回滚

资源管理
MySQL 8开始支持创建和管理资源组，允许将服务器内运行的线程分配给特定的分组，以便线程根据组内可用资源执行。组属性能够控制组内资源，启用或限制组内资源消耗。数据库管理员能够根据不同的工作负载适当地更改这些属性
字符集支持

MySQL 8中默认的字符集由latin1更改为utf9mb4，并首次增加了日语所特定使用的集合，utf8mb4_ja_0900_as_cs

优化器增强

MySQL优化器开始支持隐藏索引和降序索引。隐藏索引不会被优化器使用，验证索引的必要性时不需要删除索引，先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。降序索引允许优化器对多个列进行排序，并且允许排序下不一致

公用表达式

公用表达式（Common Table Expressions）简称为CTE，MySQL现在支持递归和非递归两种形式的CTE。CTE通过在SELECT语句或其他特定语句前使用WITH语句对临时结果集进行命名。基础语法如下：

1 2	WITH cte_name(col_name1,colname2...) AS (Subquery) SELECT * FROM cte_name

Subquery代表子查询，子查询前使用WITH语句将结果集命名为cte_name，在后续查询中即可使用cte_name进行查询

窗口函数

MySQL开始支持窗口函数。在之前的版本中已存在的大部分聚合函数在MySQL 8中也可以作为窗口函数来使用

函数名称	描述
CUME_DIST()	累计的分布值
DENSE_RANK()	对当前记录不间断排序
FIRST_VALUE()	返回窗口首行记录的对应字段值
LAG()	返回对应字段的前N行记录
LAST_VALUE()	返回窗口尾行记录的对应字段值
LEAD()	返回对应字段的后N行记录
NTH_VALUE()	返回第N条记录对应的字段值
NTILE()	将区划分为N组，并返回组的数量
PERCENT_RANK()	返回0到1之间的小数，表示某个字段值在数据分区中的排名
RANK()	返回分区内每条记录对应的排名
ROW_NUMBER()	返回每一条记录对应的序号，且不重复

正则表达式支持

MySQL在8.4以后的版本中采用支持Unicode的国际化组建实现正则表达式操作，这种方式不仅能提供完全的Unicode支持，而且是多字节安全编码。MySQL增加了REGEXP_LIKE()、EGEXP_INSTR()、REGEXP_REPLACE()和REGEXP_SUBSTR()等函数来提升性能。另外，regexp_stack_limit和regexp_time_limit系统变量能够通过匹配引擎来控制资源消耗

内部临时表

TempTable存储引擎取代MEMORY存储引擎成为内部临时表的默认存储引擎。TempTable存储引擎为VARCHAR和VARBINARY列提供高效存储。inter_tmp_mem_storage_engine会话变量定义了内部临时表的存储引擎，可选的值两个，TempTable和MEMORY，其中TempTable为默认存储引擎。temptable_max_ram系统匹配项定义了TempTable存储引擎可食用的最大内存数量

日志记录

在MySQL 8中错误日志子系统由一系列MySQL组建构成。这些组建的构成由系统变量log_error_services来配置，能够实现日志事件的过滤和写入

备份锁

新的备份锁允许在线备份期间执行数据操作语句，同时组织可能造成快找不一致的操作。新备份锁由LOCK INSTANCE FOR BACKUP和UNLOCK INSTANCE语法提供支持，执行这些操作需要备份管理员特权

增强的MySQL赋值

MySQL 9 赋值支持对JSON文档进行部分更新的二进制日志记录，该记录使用紧凑的二进制格式，从而节省记录完整JSON文档的空间。当使用基于语句的日志记录时，这种紧凑的日志记录会自动完成，并且可以通过将新的binlog_row_value_options系统变量值设置为PARTIAL_JSON来启用

MySQL 8.0移除的旧特性

在MySQL 5.7版本上开发的应用程序使用了MySQL 8.0移除的特性，语句可能会失败，或者产生不同的执行结果。为了避免这些问题，对于使用了移除特性的应用，应当尽力修正避免使用这些特性，并尽可能使用替代方法

查询缓存

查询缓存已被移除，删除的项由：

语句：FLUSH QUERY CACHE和RESET QUERY CACHE
系统变量：query_cache_limit、query_cache_min_res_unit、query_cache_size、query_cache_type、query_cache_wlock_invalidate
状态变量：Qcache_free_blocks、Qcache_queries_in_cache、Qcache_total_blocks
线程状态：checking privileges on cache query、checking query cache for query、invalidating query cache entries、sending cache result to client、storing result in query cache、waiting for query cache lock

加密相关

删除的加密相关的内容有：ENCODE()、DECODE()、ENCRYPT()、DES_ENCRYPT()和DES_DECRYPT函数，配置项des-key-file，系统变量have_crypt，FLUSH语句的DES_KEY_FILE选项，HAVE_CRYPT CMAKE选项。对于移除的ENCRYPT函数，考虑使用SHA2()替代，对于其他移除的函数，使用AES_ENCRYPT()和AES_DECRYPT()替代

空间函数相关

在MySQL 5.7版本中，多个空间函数已被标记为过时。这些过时函数在MySQL 8中都已被移除，只保留了对应的ST_和MBR函数

\N和NULL

在SQL语句中，解析器不再将\N视为NULL，所以在SQL语句中应使用NULL代替\N。这项变化不会影响使用LOAD DATA INFILE或者SELECT A INTO OUTFILE操作文件的导入和导出。在这类操作中，NULL仍等同于\N

mysql_install_db

在MySQL分布中，已移除了mysql_install_db程序，数据字典初始化需要调用带着—initialize或者—initialize-insecure选项的mysqlId来代替实现。另外—bootstrap和INSTALL_SCRIPTDIR CMake也已被删除

通用分区处理程序

通用分区处理程序已从MySQL服务中被移除。为了实现给定表分区，表所使用的存储引擎需要自有的分区处理程序。提供本地分区支持的MySQL存储引擎有两个，即InnoDB和NDB，而在MySQL 8中只支持InnoDB

系统和状态变量信息

在INFORMATION_SCHEMA数据库中，对系统和状态变量信息不再维护。GLOBAL_VARIABLES、SESSION_VARIABLES、GLOBAL_STATUS、SESSION_STATUS表都已被删除。另外，系统变量show_compatibility_56也已被删除。被删除的状态变量由Slave_heartbeat_period、Slave_last_heartbeat，Slave_received_heartbeats、Slave_retried_transactions、Slave_running。以上被删除的内容都可使用性能模式中对应内容进行替代

mysql_plugin工具

mysql_plugin工具用来配置MySQL服务器插件，现已被删除，可使用—plugin-load或—plugin-load-add选项在服务器启动时家在插件或者在运行时使用INSTALL PLUGIN语句家在插件来替代该工具

窗口函数

MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。窗口函数可以分为静态窗口函数和动态窗口函数：

静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同
动态窗口函数的窗口大小会随着记录的不同而变化

窗口函数的语法结构是：

1	函数 OVER([PARTITION BY 字段名 ORDER BY 字段名 ASC \| DESC])

或者是

1	函数 OVER 窗口名 ... WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC \| DESC])

序号函数

ROW_NUMBER()函数能够对数据中序号进行顺序显示。

SELECT * 
FROM (
  SELECT ROW_NUMBER() OVER(PARTTION BY category_id ORDER BY price DESC) AS row_num,
  id, category_id, category, name, price, stock
  FROM goods
) t
WHERE row_num <= 3;

RANK()函数得出的序列号为1，2，3，4，相同的价格的商品序号相同，后面的商品序号是不连续的，体哦啊过了重复的序号

1 2	SELECT RANK() OVER(PARTTION BY category_id ORDER BY price DESC) AS r, id, category_id, category, name, price, stock FROM goods;

DENSE_RANK()函数对序号进行并列排序，并且不会跳过重复的序号，比如1、1、2

1 2	SELECT DENSE_RANK() OVER(PARTTION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, name, price, stock FROM goods;

分布函数

PERCENT_RANK()函数是等级值百分比函数。按照如下方式进行计算：

1	(rank - 1) / (rows - 1)

其中，rank的值为使用RANK()函数产生的序号，rows的值为当前窗口的总记录数

SELECT 
  RANK() OVER(PARTTION BY category_id ORDER BY price DESC) AS r,
  PERCENT_RANK() OVER(PARTTION BY category_id ORDER BY price DESC) AS pr,
  id, category_id, category, name, price, stock
FROM goods
WHERE category_id = 1;

CUME_DIST()函数主要用于查询小于或等于某个值的比例

1 2	SELECT CUME_DIST() OVER(PARTTION BY category_id ORDER BY price DESC) AS cd, id, category_id, category, name, price, stock FROM goods;

前后函数

LAG(expr,n)函数返回当前行的前n行的expr的值，LEAD(expr,n)函数返回当前行的后n行的expr的值

SELECT 
  id, category, name, price, pre_price, price - pre_price AS diff_price
FROM (
  SELECT id, category, name, price, LAG(price,1) OVER w AS pre_price
  FROM goods
  WINDOW w AS (PARTITION BY category_id ORDER BY price)) t;

首尾函数

FIRST_VALUE(expr)函数返回第一个expr的值，lAST_VALUE(expr)函数返回值最后一个expr的值

1 2	SELECT id, category, name, price, stock, FIRST_VALUE(price) OVER w AS first_price FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

其他函数

NTH_VALUE(expr,n)函数返回第n个expr的值

SELECT
  id, category, name, price, NTH_VALUE(price,2) OVER w AS second_price,
  NTH_VALUE(price,3) OVER w AS third_price
FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号

1 2	SELECT NTILE(3) OVER w AS nt, id, category, name, price FROM goods WINDOW w AS (PARTITION BY category_id ORDER BY price);

窗口函数的特点就是可以分组，而且可以在分组排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表基础上进行统计和排序非常有用

公用表达式

公用表达式（或通用表表达式）简称为CTE。CTE是一个命名的临时结果集，作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。一句语法结果和执行方式的不同，公用表达式分为普通公用表表达式和递归公用表表达式

普通公用表表达式

普通公用表表达式的语法结构是：

1
2
3

WITH CTE名称
AS (子查询)
SELECT | DELETE | UPDATE 语句;

普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用

WITH cte_emp
AS (SELECT DISTINCT department_id FROM employees)
SELECT * 
FROM departments d JOIN cte_emp ON d.department_id = e.department_id;

递归公用表表达式

递归公用表表达式也是一种公用表表达式，只不过，除了普通公用表表达式的特点以外，他还有自己的特点，就是可以调用自己。它的语法结构是：

1
2
3

WITH RECURSIVE
CTE名称 AS (子查询)
SELECT | DELETE | UPDATE 语句;

递归公用表表达式由2部分组成，分别是种子查000询和递归查询，中间通过关键字UNION[ALL]进行连接。这里的种子查询，意思就是获得递归的初始值。这个查询只会运行一次，以创建初始数据集，之后递归查询会一致执行，直到没有任何新地查询数据产生，递归返回

WITH RECURSIVE cte
AS
(
  SELECT employee_id, last_name, manager_id, 1 AS n FROM employees WHERE employee_id = 100
  UNION ALL
  SELECT a.employee_id, a.last_name, a.manager_id, n + 1 FROM employees AS a JOIN cte
  ON (a.manager_id = cte.employee_id)
)
SELECT employee_id, last_name FROM cte WHERE n >= 3;

utf8与utf8mb4

utf字符集表示一个字符需要使用1～4个字节，但是我们常用的一些字符使用1~3个字节就可以表示了。而字符集表示一个字符所用的最大字节长度，在某些方面会影响系统的存储和性能，所以设计MySQL的设计者偷偷地定一个两个概念：

utf8mb3：阉割过的utf8字符集，只使用1~3个字节表示一个字符
utf8mb4：完整的utf8字符集，使用1~4个字节表示一个字符

在MySQL中utf8是utf8mb3的别名，所以之后在MySQL中提到utf8就意味着使用1~3个字节来表示一个字符。如果大家有使用4字节编码的情况，比如存储一些emoji表情，那请使用utf8mb4。后缀表示该比较规则是否区分语言中的重音、大小写。具体如下：

后缀	英文释义	描述
_ai	accent insensitive	不区分重音
_as	accent sensitive	区分重音
_ci	case insensitive	不区分大小写
_cs	case sensitive	区分大小写
_bin	binary	以二进制方式比较

角色管理

创建角色

在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。创建角色使用CREATE ROLE语句，语法如下：

1	CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']] ...;

角色名称的命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空

角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们要给角色授权。给角色授权的语法结构是：

1	GRANT privileges ON table_name TO 'role_name'[@'host_name'];

privileges是权限列表，table_name是表名，role_name是角色名，host_name是主机名。

查看角色权限

赋予角色权限之后，我们可以通过SHOW GRANTS语句，来查看权限是否创建成功：

1	SHOW GRANTS FOR 'role_name'[@'host_name'];

只要你创建了一个角色，系统就会自动给你一个USAGE权限，意思是连接登录数据库的权限

回收权限

角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句，语法结构如下：

1	REVOKE privileges ON table_name FROM 'role_name'[@'host_name'];

删除角色

删除角色使用DROP ROLE语句，语法结构如下：

1	DROP ROLE 'role_name'[@'host_name'];

注意：如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限

用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。给用户添加角色可使用GRANT语句，语法形式如下：

1	GRANT 'role_name'[@'host_name'] TO 'user_name'[@'host_name'];

激活角色

MySQL中角色是不会自动激活的，默认都是没有被激活，也就是不能用，必须要手动激活，激活以后用户才能拥有角色对应的权限，激活角色有两种方式：

使用SET DEFAULT ROLE命令激活角色 SET DEFAULT ROLE ALL TO user;用户需要退出重新登录，才能看到赋予的角色
将activate_all_roles_on_login参数设置为ON，这样用户登录时，就会自动激活所有的角色

撤销用户角色

撤销用户角色使用REVOKE语句，语法结构如下：

1	REVOKE 'role_name'[@'host_name'] FROM 'user_name'[@'host_name'];

设置强制角色(mandatory role)

强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE或者DROP

服务启动前设置mandatory_roles=role1,role2,role3
运行时设置SET GLOBAL mandatory_roles=role1,role2,role3

索引

聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

术语聚簇表示数据行和相邻的键值聚簇的存储在一起

使用记录主键值的大小进行记录和页的排序，这包括三个方面的含义：

页内的记录是按照主键的大小顺序排成一个单向连标
各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表
存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表

B+树的叶子结点存储的是完整的用户记录。所谓完整的用户记录，就是指这个记录中存储了所有列的值（包括隐藏列），我们把具有这两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX语句区创建，InnoDB存储引擎会自动为我们创建聚簇索引。

优点：1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快。2.聚簇索引对于主键的排序查找和范围查找速度非常快。3.按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的IO操作。

缺点：1.插入速度严重依赖与插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此对于InnoDB表，我们一般都会定义一个自增的ID列为主键。2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。

限制：1.对于MySQL数据库目前只有InnoDB支持聚簇索引，而MyISAM并不支持聚簇索引。2.由于数据物理存储方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。3.如果没有定义主键，InnoDB会选择非空的唯一索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。4.为了充分利用聚簇索引的聚簇的特性，所以InnoDB表的主键列尽量选用有序的顺序ID，而不建议用无序的ID，比如UUID、MD5、HASH、字符串列作为作为主键无法保证数据的顺序增长

非聚簇索引

聚簇索引的叶子节点存储的就是我们的数据记录，非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序
一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但可以有多个非聚簇索引，也就是多个索引目录提供数据检查
使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入、删除、更新等操作，效率会比非聚簇索引低

联合索引

联合索引是指多个字段组合成一个索引，这样可以提高查询效率，但是会降低更新表的效率，因为更新表时，MySQL不仅要保存该行的数据，还要保存索引文件。本质上也是一个非聚簇索引，不同点如下：

建立联合索引只会建立一颗B+树
建立非聚簇索引会建立多颗B+树

InnoDB的B+树索引注意事项

根页面位置万年不动

我们前面介绍B+树索引的时候，为了大家理解上方便，先把存储用户记录的叶子节点都画出来，然后接着画存储目录项记录的内节点，实际上B+树的形成过程是这样的：

每当为某个表创建一个B+树索引（聚簇索引不是人为创建的，默认就有）的时候，都会为这个索引创建一个根节点页面。最开始表中没有数据的时候，每个B+树索引对应的根节点中既没有用户记录，也没有目录项记录
随后向表中插入用户记录时，先把用户记录存储到这个根节点中
当根节点中的可用空间用完时继续插入记录，此时会将根节点中的所有记录复制到一个新分配的页，比如页A中，然后对这个新页进行页分裂的操作，得到另一个新页，比如页B。这时新插入的记录根据键值（也就是聚簇索引中的主键值，二级索引中对应的索引列的值）的大小就会被分配到页A或者页B中，而根节点便升级为存储目录项记录的页

这个过程特别注意的是：一个B+树索引的根节点自诞生之日起，便不会再移动。这样只要我们对某个表建立一个索引，那么它的根节点的页号便会被记录到某个地方，然后凡是InnoDB存储引擎需要用到这个索引的时候，都会从那个固定的地方取出根节点的页号，从而来访问这个索引

内节点中目录项记录的唯一性

我们知道B+树索引的内节点中目录项记录内容是索引列+页号的搭配，但是这个搭配对于二级索引来说有点不严谨。为了保证二级索引的唯一性，我们需要保证在B+树的同一层内节点的目录项记录页号这个字段以外是唯一的。所以对于二级索引的内节点的目录项的内容实际上是由三个部分构成：

索引列的值
页号
主键值

也就是我们把主键值页添加到了二级索引内节点中的目录项记录了，这个就能保证B+树每一层节点中各条目录项记录处页号这个字段外是唯一的

一个页面最少存储2条记录

我们知道B+树索引的叶子节点中存储的是用户记录，而内节点中存储的是目录项记录。叶子节点中的用户记录是按照键值的大小顺序排列的，而内节点中的目录项记录是按照键值的大小顺序排列的。这样的话，如果一个页面中只存储了一条记录，那么这个页面就没有意义了，因为这个页面中的记录是无法被访问到的。所以InnoDB存储引擎规定，一个页面中至少要存储两条记录，这样才能保证页面的有效性

MyISAM中的索引方案

索引/存储引擎	MyISAM	InnoDB	Memory
B-Tree索引	支持	支持	支持

即使多个存储引擎支持同一种类型索引，但是他们的实现原理也是不同的。InnoDB和MyISAM默认的索引是B-Tree索引，而Memory存储引擎默认的索引是Hash索引。MyISAM引擎使用B-Tree索引作为索引结构，叶子节点的data域存放的是数据记录地址

MyISAM的索引原理

我们知道InnoDB索引即数据，也就是聚簇索引的那颗B+树的叶子节点中已经把所有完整的用户记录都包含了，而MyISAM的索引方案虽然也使用树形结构，但是却将索引和数据分开存储：

将表中的记录按照记录的插入顺序单独存储在一个文件中，称之为数据文件。这个文件并不划分为若干个数据页，有多个记录就往这个文件中塞多少记录就好了。由于在插入数据的时候并没有刻意按照主键大小排序，所以我们并不能在这些数据上使用二分法进行查找
使用MyISAM存储引擎的表会把索引信息另外存储到一个称之为索引文件的另一个文件中。MyISAM会单独为表的主键创建一个索引，只不过在索引的叶子节点中存储的不是完整的用户信息，而是主键值 + 数据记录地址的组合

MyISAM与InnoDB对比

MyISAM的索引方式都是非聚簇的，与InnoDB包含一个聚簇索引是不同的。两种引擎中索引的区别：

在InnoDB存储引擎中，我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录，而在MyISAM中却需要进行一次回表操作，意味着MyISAM中建立的索引相当于全部都是二级索引
InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分离的，索引文件仅保存数据巨鹿的地址
InnoDB的非聚簇索引data域存储相应记录的主键的值，而MyISAM索引记录的是地址。换句话说，InnoDB的所有非聚簇索引都引用主键作为data域
MyISAM的回表操作是十分快速的，因为是拿着地址偏移量直接到文件中取数据的，反观InnoDB是通过获取主键之后再去聚簇索引里找记录，虽然说也不慢，但还是比不上直接用地址去访问
InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以非空且唯一标识数据记录的列作为主键。如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型

MySQL数据结构选择的合理性

从MySQL的角度讲，不得不考虑一个现实问题就是磁盘IO。如果我们能让索引的数据结构尽量减少磁盘IO操作，所消耗的时间也就越小。可以说，磁盘IO的操作次数对索引的使用效率至关重要。查看都是索引操作，一般来说索引非常大，尤其是关系型数据库，当数据量较大的时候，索引的大小有可能几个G甚至更多，为了减少索引在内存的占用，数据库索引存储在外部磁盘上的。当我们利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载，那么MySQL衡量查询效率的标准就是磁盘IO次数

Hash结构

Hash本身是一个函数，又被称为散列函数，它可以帮助我们大幅提升检索数据的效率。Hash算法是通过某种确定性算法（比如MD5、SHA1、SHA2\SHA3）将输入变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。加速查找速度的数据结构，常见的有两类：

树，例如平衡二叉搜索树，查找、插入、修改、删除的平均时间复杂度都是O(log2N)
哈希，例如HashMap，查找、插入、修改、删除的平均时间复杂度都是O(1)

采用Hash进行检索效率非常高，基本上一次检索就可以找到数据，而B+树需要向下查找，多次访问节点才能找到数据，中间多次IO操作，从效率来说Hash比B+树更快

Hash结构效率高，那么为什么索引结构要设计成树形呢？

Hash索引仅能满足=、<>和IN查询。如果进行范围查询，哈希型索引，时间复杂度会退化为O(n)；而树型的有序特性，依然能够保持O(log2N)的高效率
Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序，这样就会导致性能下降
对于联合索引的情况下，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询
对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这时因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以，Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等

Hash索引适用的存储引擎如表所示：

索引/存储引擎	MyISAM	InnoDB	Memory
HASH索引	不支持	不支持	支持

Hash索引的适用性：

Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash索引效率更高，比如在键值型（Key-Value）数据库中，Redis存储的核心就是Hash表
MySQL中的Memory存储引擎支持Hash存储，如果我们需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash索引，比如字符串类型的字段，进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择
另外，InnoDB本身不支持Hash索引，但是提供自适应Hash索引，什么情况下才会使用自适应Hash索引呢？如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面所在的位置。这样让B+树也具备了Hash索引的有点

二叉搜索树

如果我们利用二叉树作为索引结构，那么磁盘的IO次数和索引树的高度是相关的

一个节点只能有两个子节点，也就是一个节点度不能超过2
左子节点 < 本节点；右子节点 >= 本节点，比我大的向右，比我小的向左

AVL树

为了解决二叉查找树退化成链表的问题，人们提出了平衡二叉搜索树，又称为AVL树，它在二叉搜索树的基础上增加了约束，具有以下性质：它是一棵空树或它的左右两个子树的高度差的绝对值不超过1，并且左右两个子树都是一棵平衡二叉树。这里说一下，常见的平衡二叉树有很多种，包括了平衡二叉搜索树、红黑树、数堆、伸展树。平衡二叉搜索树是最早提出来的自平衡二叉搜索树，当我们提到平衡二叉树时一般指的就是平衡二叉搜索树。事实上，第一棵树就属于平衡二叉搜索树，搜索时间复杂度就是O(log2n)。

数据查询的时间主要依赖于磁盘IO的次数，如果我们采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是O(log2n)，当n比较大时，深度也是比较高的

B-Tree

B树的英文是Balance Tree，也就是多路平衡查找树。简写为B-Tree。它的高度远小于平衡二叉树的高度。B树作为多路平衡查找树，它的每一个节点最多可以包括M个子节点，M称为B树的阶。每个磁盘块中包括了关键字和子节点的指针。如果一个磁盘块中还包括了x个关键字，那么指针数就是x + 1。对于一个100阶的B树来说，如果有3层的话最多可以存储约100万的索引数据。对于大量的索引数据来说，采用B树的结构是非常适合的，因为树的高度远小于二叉树的高度。

B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡
关键字集合分布在整棵树中，即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束
其搜索性能等价于在关键字全集内做一次二分查找

B+Tree

B+树也是一种多路搜索树，基于B树做出了改进，主流的DBMS都支持B+树的索引方式，比如MySQL。相比于B-Tree，B+Tree适合文件索引系统

B+树和B树的差异？

有K个孩子的节点就有K个关键字。也就是孩子数量 = 关键字数，而B树中，孩子数量 = 关键字数 + 1
非叶子节点的关键字也会同时存在子节点中，并且是在子节点中所有关键字的最大（或最小）
非叶子节点仅用于索引，不保存数据记录，根记录有关的信息都放在叶子节点中。而B树中，非叶子节点既保存索引，也保存数据记录
所有关键字都在叶子节点出现，叶子节点构成一个有序链表，而且叶子节点本身按照关键字的大小从小到大顺序链接

B+树中间节点并不直接存储数据，这样的好处是什么？

首先，B+树查询效率更稳定。因为B+树每次只有访问到叶子及诶单才能找到对应的数据，而在B树中，非叶子节点也会存储数据，这样就会造成查询效率不稳定的情况，有时候访问到了叶子节点就可以找到关键字，而有时需要访问到叶子节点才能找到关键字

其次，B+树查询效率更高。这时因为通常B+树比B树更矮胖（阶树更大，深度更低），查询所需要的磁盘IO也会更少。同样的磁盘页大小，B+树可以存储更多的节点关键字，不仅是对单个关键字查询上，在查询范围上，B+树的效率也比B树高。这是因为所有关键字都出现在B+树的叶子节点中，叶子节点之间会有指针，数据又是递增的，这使得我们范围查找可以通过指针连接查找。而在B树中则需要通过中序遍历才能完成查询范围的查找，效率要低很多

B树和B+树都可以作为索引的数据结构，在MySQL中采用的是B+树
但B树和B+树各有自己的应用场景，不能说B+树完全比B树好，反之亦然

B+树的存储能力如何？为何说一般查找行记录，最多只需1~3此磁盘IO

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿条记录（这里假设一个数据页也存储10^3条行记录数据）

实际情况中每个节点可能填不满，因此在数据库中，B+Tree的高度一般都在2~4层。MySQL的InnoDB存储引擎在设计时将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3此磁盘IO操作

Hash索引和B+树索引的区别？

Hash索引不能进行范围查询，而B+树可以。这是因为Hash索引指向的数据是无序的，而B+树的叶子节点是个有序的链表
Hash索引不支持联合索引的最左侧原则（即联合索引的部分索引无法使用），而B+树可以，对于联合索引来说，Hash索引在计算值的时候是将索引键合并后再一起计算Hash值，所以不会针对每个索引单独计算Hash值。因此如果用到联合索引的一个或者几个索引时，联合索引无法被利用
Hash索引不支持ORDER BY排序，因为Hash索引指向的数据是无序的，因此无法起到排序优化的作用，而B+树索引树是有序的，可以起到对该字段ORDER BY排序优化的作用。同理，我们也无法用Hash索引进行模糊查询，而B+树使用LIKE进行模糊查询的时候，LIKE后面模糊查询的话就可以起到优化作用
InnoDB不支持Hash索引

R树

R-Tree在MySQL很少使用，仅支持geometry数据类型，支持该类型存储引擎只有MyISAM、InnoDB几种。举个R树在现实领域中能够解决的例子：查找20英里以内所有的餐厅。如果没有R树你会怎么解决？一般情况下我们会把餐厅的坐标(x,y)分为两个字段存放在数据库中，一个字段记录经度，另一个字段记录纬度。这样的话我们就需要遍历所有的餐厅获取其位置信息，然后计算是否满足。如果一个地区有100家餐厅的话，我们就需要进行100次位置计算操作了，如果应用到谷歌、百度地图这种超大数据库中，这种方法必定不可行了。R树就很好解决了这种高维空间搜索问题。它把B树的思想很好的扩展到了多维空间，采用了B树分割空间的思想，并在添加、删除时采用合并、分解结点的方法，保证树的平衡性。因此，R树就是一棵用来存储高维数据的平衡树。相对于B-Tree，R-Tree的优势在于范围查找