约束

数据完整性是指数据的精确性和可靠性。它是防止数据中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。为了保证数据的完整性，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部分组成，分别是种子查询和递归查询，中间通过关键字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;