MySQL 中级篇

MySQL 中级篇

1. 视图

1.1. 数据库对象

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

1.2. 视图概述

视图一方面可以帮我们使用表的一部分而不是所有的表，另一方面也可以针对不同的用户制定不同的查询视图。

视图的理解

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

1.3. 创建视图

• 在 CREATE VIEW 语句中嵌入子查询

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

• 精简版

CREATE VIEW 视图名称 
AS 查询语句

1.4. 查看视图

语法1：查看数据库的表对象、视图对象

SHOW TABLES;

语法2：查看视图的结构

DESC / DESCRIBE 视图名称;

语法3：查看视图的属性信息

# 查看视图信息（显示数据表的存储引擎、版本、数据行数和数据大小等） 
SHOW TABLE STATUS LIKE '视图名称'

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

语法4：查看视图的详细定义信息

SHOW CREATE VIEW 视图名称;

1.4. 更新视图的数据

1.4.1. 一般情况

MySQL 支持使用 INSERT、UPDATE 和 DELETE 语句对视图中的数据进行插入、更新和删除操作。当视图中的数据发生变化时，数据表中的数据也会发生变化，反之亦然。

1.4.2. 不可更新的视图

要使视图可更新，视图中的行和底层基本表中的行之间必须存在 一对一 的关系。另外当视图定义出现如下情况时，视图不支持更新操作：

• 在定义视图的时候指定了 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；
• 视图定义基于一个 不可更新视图；
• 常量视图

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

1.5. 修改、删除视图

1.5.1. 修改视图

方式1：使用 CREATE OR REPLACE VIEW 子句修改视图

方式2：ALTER VIEW

ALTER VIEW 视图名称 
AS
查询语句

1.5.2. 删除视图

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

DROP VIEW IF EXISTS 视图名称;

1.6. 优缺点

1.6.1. 优点

1. 操作简单
2. 减少数据冗余
3. 数据安全：根据权限将用户对数据的访问限制在某些视图上，用户不需要查询数据表，可以直接通过视图获取数据表中的信息；
4. 适应灵活多变的需求
5. 能够分解复杂的查询逻辑

1.6.2. 缺点

如果实际数据表的结构变更了，需要及时对相关的视图进行相应的维护；

特别是嵌套的视图（就是在视图的基础上创建视图），维护会变得比较复杂， 可读性不好 ，容易变成系统的潜在隐患。

实际项目中，如果视图过多，会导致数据库维护成本的问题。

2. 存储过程与函数

2.1. 存储过程

存储过程(Stored Procedure)，是一组经过 预先编译 的 SQL 语句的封装；

存储过程预先存储在 MySQL 服务器，客户端向服务端发送调用存储过程的命令，服务器端就把宇轩存储的 SQL 语句全部执行；

2.1.1. 优点

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

2.1.2. 创建存储过程

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

1、参数前面的符号的意思

• IN：默认值，当前参数为输入参数，也就是表示入参；
• OUT：当前参数为输出参数，也就是表示出参；
• INOUT：当前参数既可以为输入参数，也可以为输出参数。

2、形参类型可以是 MySQL 数据库中的任意类型。

3、characteristics表示创建存储过程时指定的对存储过程的约束条件

4、存储过程体中可以有多条 SQL 语句，如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END

5、需要设置新的结束标记

DELIMITER 新的结束标记

Examples1：创建存储过程 select_all_data()，查看 emps 表的所有数据

DELIMITER $ 

CREATE PROCEDURE select_all_data() 
BEGIN
	SELECT * FROM emps; 
END $ 

DELIMITER;

Examples2：创建存储过程 avg_employee_salary()，返回所有员工的平均工资

DELIMITER // 

CREATE PROCEDURE avg_employee_salary() 
BEGIN
	SELECT AVG(salary) AS avg_salary 
	FROM emps; 
END // 

DELIMITER ;

2.1.3. 调用存储过程

调用存储过程格式：

CALL 存储过程名(实参列表)

调用 in 模式的参数（默认值）

CALL sp1('值');

调用 out 模式的参数

SET @name;
CALL sp1(@name);
SELECT@name

调用 inout 模式的参数

SET @name=值; 
CALL sp1(@name); 
SELECT @name;

2.1.4. ⭐阿里巴巴规范

阿里开发规范

【强制】禁止使用存储过程，存储过程难以调试和扩展，更没有移植性。

1、可移植性差： 存储过程不能跨数据库移植，比如在 MySQL、Oracle 和 SQL Server 里编写的存储过程，在换成其他数据库时都需要重新编写。

2、调试困难： 只有少数 DBMS 支持存储过程的调试。对于复杂的存储过程来说，开发和维护都不容易。虽然也有一些第三方工具可以对存储过程进行调试，但要收费。

3、存储过程的版本管理很困难： 比如数据表索引发生变化了，可能会导致存储过程失效。我们在开发软件的时候往往需要进行版本管理，但是存储过程本身没有版本控制，版本迭代更新的时候很麻烦。

4、它不适合高并发的场景： 高并发的场景需要减少数据库的压力，有时数据库会采用分库分表的方式，而且对可扩展性要求很高，在这种情况下，存储过程会变得难以维护， 增加数据库的压力 ，显然就不适用了。

小结：

存储过程既方便，又有局限性。尽管不同的公司对存储过程的态度不一，但是对于我们开发人员来说，不论怎样，掌握存储过程都是必备的技能之一。

2.2. 函数

2.2.1. 创建函数

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型 
[characteristics ...] 
BEGIN
	函数体 # 函数体中必须有 RETURN 语句 
END

2.2.1. 调用函数

SELECT 函数名(实参列表)

创建存储函数 count_by_id()，参数传入 dept_id，该函数查询 dept_id 部门的员工人数，并返回，数据类型为整型。

DELIMITER // 
CREATE FUNCTION count_by_id(dept_id INT) 
RETURNS INT 
	LANGUAGE SQL 
	NOT DETERMINISTIC 
	READS SQL DATA 
	SQL SECURITY DEFINER 
	COMMENT '查询部门平均工资' 
BEGIN
	RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id); 
END // 
DELIMITER ;

调用：

SET @dept_id = 50; 
SELECT count_by_id(@dept_id);

2.3. 存储过程和函数的查看、修改、删除

2.3.1. 查看

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

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

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

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

3. 从 information_schema.Routines 表中查看存储过程和函数的信息

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

2.3.2. 修改

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

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

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

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

2.3.3. 删除

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

3. 变量、流程控制与游标

3.1. 变量

在 MySQL 存储过程和函数中，可以使用变量来存储查询或计算的中间结果数据，或者输出最终的结果数据；

在 MySQL 数据库中，变量分为 系统变量 和 用户自定义变量；

3.1.1. 系统变量

系统变量由系统自定义，属于服务器层面，主要是遍历 MySQL 时参数和配置文件（my.ini）中的参数值；

系统变量分为全局系统变量（添加 global 关键字，又称 全局变量），以及会话系统变量（添加 session 关键字，又称 local 变量，默认值）；

查看系统变量

• 查看所有或部分系统变量

#查看所有全局变量 
SHOW GLOBAL VARIABLES; 
#查看所有会话变量 
SHOW SESSION VARIABLES; 
#或
SHOW VARIABLES;

#查看满足条件的部分系统变量。 
SHOW GLOBAL VARIABLES LIKE '%标识符%';
#查看满足条件的部分会话变量 
SHOW SESSION VARIABLES LIKE '%标识符%';

• 查看指定系统变量

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

#查看指定的系统变量的值 
SELECT @@global.变量名; 
#查看指定的会话变量的值 
SELECT @@session.变量名; 
#或者 
SELECT @@变量名;

修改系统变量

方式 1：修改MySQL 配置文件 ，继而修改 MySQL 系统变量的值（该方法需要重启 MySQL 服务）

方式 2：在 MySQL 服务运行期间，使用 set 命令重新设置系统变量的值

#为某个系统变量赋值 
#方式1： 
SET @@global.变量名=变量值; 
#方式2： 
SET GLOBAL 变量名=变量值;

#为某个会话变量赋值 
#方式1： 
SET @@session.变量名=变量值; 
#方式2： 
SET SESSION 变量名=变量值;

3.1.2. 用户变量

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

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

会话用户变量

定义会话用户变量

#方式1：“=”或“:=” 
SET @用户变量 = 值; 
SET @用户变量 := 值; 

#方式2：“:=” 或 INTO 关键字 
SELECT @用户变量 := 表达式 [FROM 等子句]; 
SELECT 表达式 INTO @用户变量 [FROM 等子句];

查看会话用户变量的值 （查看、比较、运算等）

SELECT @用户变量

局部变量

定义：使用 DECLARE 语句定义局部变量

作用域：BEGIN ··· END 中

位置：BEGIN ··· END 第一句

BEGIN
	#声明局部变量 
	DECLARE 变量名1 变量数据类型 [DEFAULT 变量默认值]; 
	DECLARE 变量名2,变量名3,... 变量数据类型 [DEFAULT 变量默认值];
    #为局部变量赋值 
    SET 变量名1 = 值; 
    SELECT 值 INTO 变量名2 [FROM 子句]; 
    #查看局部变量的值 
    SELECT 变量1,变量2,变量3; 
END

定义变量

DECLARE 变量名 类型 [default 值]; # 如果没有 DEFAULT 子句，初始值为 NULL

变量赋值

方式1：一般用于赋简单的值

SET 变量名 = 值; 
SET 变量名 := 值;

方式2：一般用于赋表中的字段值

SELECT 字段名或表达式 INTO 变量名 FROM 表;

使用变量（查看、比较、运算等）

SELECT 局部变量名;

对比

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

3.2. 定义条件与处理程序

• 定义条件 是事先定义程序执行过程中可能遇到的问题；
• 处理程序 定义了在遇到问题时应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行，避免程序异常停止运行。

具体用法略，尚硅谷的课好多啊啊啊啊，学不玩了，用到的时候再补；

3.3. 流程控制

3.3.1. 分支结构之 IF

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

3.3.2. 分支结构之 CASE

CASE 语句的语法结构 1：

#情况一：类似于 switch 
CASE 表达式 
WHEN 值1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 值2 THEN 结果2或语句2(如果是语句，需要加分号) 
... 
ELSE 结果 n 或语句 n(如果是语句，需要加分号) 
END [case]（如果是放在begin end中需要加上case，如果放在select 后面不需要）

CASE 语句的语法结构2：

#情况二：类似于多重 if 
CASE 
WHEN 条件1 THEN 结果1或语句1(如果是语句，需要加分号) 
WHEN 条件2 THEN 结果2或语句2(如果是语句，需要加分号) 
... 
ELSE 结果 n 或语句n(如果是语句，需要加分号) 
END [case]（如果是放在begin end中需要加上case，如果放在select后面不需要）

3.3.3. 循环结构之 LOOP

LOOP 循环语句用来重复执行某些语句，一直循环直到 LEAVE；

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

3.3.4. 循环结构之 WHILE

先判断后执行，相当于 Java 中的 while

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

3.3.5. 循环结构之 REPEAT

先执行后判断，相当于 Java 中的 do ··· while

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

对比三种循环结构：

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

3.3.6. 跳转语句之 LEAVE

相当于 Java 中的 break；

LEAVE 标记名

跳转语句之 ITERATE

相当于 Java 中的 continue；

ITERATE label

3.4. 游标

游标让 SQL 这种面向集合的语言有了面向过程开发的能力。

游标能够对结果集中的每一条记录进行定位，并对指向的记录中的数据进行操作；

3.4.1. 使用游标

游标必须在声明处理程序之前被声明，并且变量和条件还必须在声明游标或处理程序之前被声明。

声明游标

DECLARE cursor_name CURSOR FOR select_statement;

打开游标

OPEN cursor_name

使用游标（从游标中取得数据）

FETCH cursor_name INTO var_name [, var_name] ...

注意：游标的查询结果集中的字段数，必须跟 INTO 后面的变量数一致，否则，在存储过程执行的时候，MySQL 会提示错误。

关闭游标

CLOSE cursor_name

游标会占用系统资源 ，如果不及时关闭，游标会一直保持到存储过程结束，影响系统运行的效率。

DELIMITER //

CREATE PROCEDURE get_count_by_limit_total_salary(
  IN limit_total_salary DOUBLE,
  OUT total_count INT
)
BEGIN

	# 声明局部变量
	DECLARE sum_sal DOUBLE DEFAULT  0.0; # 记录累加的工资总额
	DECLARE emp_sal DOUBLE; # 记录每一个员工的工资
	DECLARE emp_count INT DEFAULT 0; # 记录累加的人数

	# 1. 声明游标
	DECLARE emp_cursor CURSOR FOR SELECT salary	FROM employees ORDER BY salary DESC;

	# 2. 打开游标
	open emp_cursor;

	REPEAT
		# 3. 使用游标
		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;
	# 4. 关闭游标
	CLOSE emp_cursor;

END //

DELIMITER ;

CALL get_count_by_limit_total_salary(200000, @t_count);
SELECT @t_count;

3.4.2. 小结

建议：养成用完之后就关闭的习惯，这样才能提高系统的整体效率。

游标是 MySQL 的一个重要的功能，为 逐条读取 结果集中的数据，提供了完美的解决方案。跟在应用层面实现相同的功能相比，游标可以在存储程序中使用，效率高，程序也更加简洁。

在使用游标的过程中，会对数据行进行加锁，这样在业务并发量大的时候，不仅会影响业务之间的效率，还会 消耗系统资源，造成内存不足，因为游标是在内存中进行的处理。

3.5. 全局变量持久化

MySQL 8.0的新特性—全局变量的持久化

使用 SET GLOBAL 语句设置的变量值只会 临时生效。数据库重启后，服务器又会从MySQL配置文件中读取变量的默认值。 MySQL 8.0 版本新增了 SET PERSIST 命令。

SET PERSIST global max_connections = 1000;

MySQL 会将该命令的配置保存到数据目录下的 mysqld-auto.cnf 文件中，下次启动时会读取该文件，用其中的配置来覆盖默认的配置文件。

4. 触发器

4.1. 触发器概述

MySQL从 5.0.2 版本开始支持触发器。MySQL的触发器和存储过程一样，都是嵌入到MySQL服务器的一段程序。

触发器是由 事件来触发 某个操作，这些事件包括 INSERT、UPDATE、DELETE 事件。

事件是指用户的动作或者触发某项行为。如果定义了触发程序，当数据库执行这些语句时候，就相当于事件发生了，就会 自动 激发触发器执行相应的操作。

当对数据表中的数据执行插入、更新和删除操作，需要自动执行一些数据库逻辑时，可以使用触发器来实现。

4.2. 创建触发器

创建触发器语法

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

说明：

• 表名：表示触发器监控的对象；
• BEFORE|AFTER：表示在时间发生 之前/之后 触发；
• INSERT|UPDATE|DELETE：表示触发的事件；
  • INSERT 表示插入记录时触发；
  • UPDATE 表示更新记录时触发；
  • DELETE 表示删除记录时触发。

DELIMITER // 
CREATE TRIGGER before_insert 
BEFORE INSERT ON test_trigger 
FOR EACH ROW 
BEGIN
	INSERT INTO test_trigger_log (t_log) 
	VALUES('before_insert'); 
END // 
DELIMITER ;

4.3. 查看触发器

方式1：查看当前数据库的所有触发器的定义

SHOW TRIGGERS

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

SHOW CREATE TRIGGER 触发器名

方式3：从系统库 information_schema 的 TRIGGERS 表中查询“salary_check_trigger”触发器的信息。

SELECT *
FROM information_schema.TRIGGERS;

4.4. 删除触发器

DROP TRIGGER IF EXISTS 触发器名称;

4.5. 触发器优缺点

优点

1、触发器可以确保数据的完整性。

2、触发器可以帮助我们记录操作日志。

3、触发器还可以用在操作数据前，对数据进行合法性检查。

缺点

1、触发器最大的一个问题就是可读性差。

比如触发器中的数据插入操作多了一个字段，系统提示错误。可是，如果你不了解这个触发器，很可能会认为是更新语句本身的问题，或者是表的结构出了问题。

2、相关数据的变更，可能会导致触发器出错。

特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

注意点

注意，如果在子表中定义了外键约束，并且外键指定了 ON UPDATE/DELETE CASCADE/SET NULL子句，此时修改父表被引用的键值或删除父表被引用的记录行时，也会引起子表的修改和删除操作，此时基于子表的 UPDATE 和 DELETE 语句定义的触发器并不会被激活。

5. MySQL8 其它新特性

5.1. 窗口函数

5.1.1. 窗口函数分类

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

窗口函数可以分为 静态窗口函数 和 动态窗口函数。

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同；

• 动态窗口函数的窗口大小会随着记录的不同而变化。

函数分类	函数	函数说明
序号函数	ROW_NUMBER()	顺序排序
	RANK()	并列排序，会跳过重复的序号，比如序号为1、1、3
	DENSE_RANK()	并列排序，不会跳过重复的序号，比如序号为1、1、2
分布函数	PERCENT_RANK()	等级值百分比
	CUME_DIST()	累积分布值
前后函数	LAG(expr, n)	返回当前行的前n行的expr的值
	LEAD(expr, n)	返回当前行的后n行的expr的值
首尾函数	FIRST_VALUE(expr)	返回第一个expr的值
	LAST_VALUE(expr)	返回最后一个expr的值
其他函数	NTH_VALUE(expr, n)	返回第n个expr的值
	NTILE(n)	将分区中的有序数据分为n个桶，记录桶编号

5.1.2. 语法结构

窗口函数的语法结构是：

函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

或者是：

函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]）

• OVER 关键字指定函数窗口的范围。
  • 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于所有满足WHERE条件的记录进行计算。
  • 如果OVER关键字后面的括号不为空，则可以使用如下语法设置窗口。
• 窗口名：为窗口设置一个别名，用来标识窗口。
• PARTITION BY 子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行。
• ORDER BY 子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
• FRAME 子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。

SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, id, category_id, category, NAME, price, stock
FROM goods;

5.1.3. 小 结

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

5.2. 公用表表达

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

依据语法结构和执行方式的不同，公用表表达式分为普通公用表表达式和递归公用表表达式2 种。

5.2.1. 普通公用表表达式

WITH CTE名称 
AS （子查询） 
SELECT|DELETE|UPDATE 语句;

举例：查询员工所在的部门的详细信息。

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

5.2.2. 递归公用表表达式

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

案例： 针对于常用的 employees 表，包含 employee_id，last_name 和 manager_id 三个字段。如果 a 是 b 的管理者，那么可以把 b 叫做 a 的下属，如果同时 b 又是 c 的管理者，那么 c 就是 b 的下属，是 a 的下下属。

• 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者。

• 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回。

• 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集。

代码实现：

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;

5.2.3. 小 结

公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询。