MySQL
简介
基础、进阶、高级、应用这四章将关注SQL的精华部分DQL数据查询语句、DML数据操作语句（涉及较少）
诸如数据定义、事务管理、权限管理、函数过程、触发器、事件以及更宏观的数据库设计可根据需求学习
基础
前置知识
注释
-- 这是一条单行注释
# 这也是一条单行注释
/*
这是
多行
注释
*/
转义字符
# 对于特殊字符如：引号，应使用转义字符反斜杠
SELECT '\''; # 输出单引号
SELECT '\\'; # 输出反斜杠
SELECT '\\\''; # 输出反斜杠和单引号
书写规范
# SQL对大小写不敏感，但还是应使用大小写对不同类型进行区分
SELECT
# 关键字大写
PI.product_category AS 'product category',
# 列名小写，根据实际要求命名或按照对应含义的字母缩写
# 尽可能使用下划线替代空格，如名称包含空格，使用引号包裹
SUM(PI.product_price * PS.product_amount) AS psum
# 函数名大写
# 使用缩进使结构清晰，必要时使用多层缩进
FROM
product_sale AS PS LEFT JOIN product_info AS PI
# 表名选择大写，字母为缩写:P:product,S:sale,I:information
USING(product_id)
WHERE
YEAR(PS.sale_date) = 2021
# 空格也可使内容更易读
GROUP BY PI.product_category
# 对于较短的行，有时似乎没必要缩进，可根据是否易读决定是否缩进（这里选择不缩进）
HAVING SUM(PI.product_price * PS.product_amount) > 10000
ORDER BY psum DESC
LIMIT 2,5;
# 语句结束以分号结尾
/*
以上代码表示：
产品销售表左联接产品信息表且联接条件为产品编号，
筛选出销售日期为2021年的产品，
按产品类别分组并筛选出销售总额大于一万的分组，
输出产品类别及对应的销售总额，
按照销售总额降序排列，
从第三行起取五行记录。
即 取销售总额为第三至第七名的产品类别名称及其销售总额
*/
基础查询
SELECT
SELECT 1+1;# SELECT可以没有FROM
SELECT 1+1 FROM DUAL;# 与上句等价，DUAL只是形式上的表
SELECT 1 AS a;# 起别名
SELECT 1 a;# 与上句等价，AS可以省略，但不建议省略
FROM
SELECT name # 输出名字列
FROM student;
SELECT name,score # 输出name,score列
FROM student;
SELECT * # 输出所有列
FROM student;
SELECT
CONCAT('姓名:',name) AS name # 输出计算列
FROM student;
SELECT
*,# 表示所有列的*必须在前，其他在后
name # 输出所有列和name列，即name列输出了两次
FROM student;
SELECT MAX(score) # 输出聚合值
FROM student;
SELECT DISTINCT name # 输出去重后的学生姓名
FROM student;
SELECT COUNT(DISTINCT name) # 输出去重后的学生姓名总数
FROM student;
SELECT
E.*,D.dept_name AS dept_name # 输出其中一个表的所有列和另一个表的指定列
FROM
employee AS E # 起别名
INNER JOIN department AS D # 内部联接
USING(dept_id);
WHERE
SELECT name
FROM student
WHERE
age=21;# 输出年龄为21岁的学生姓名
SELECT name
FROM student
WHERE
id<=1005;# 输出学号小于1005的学生姓名
SELECT name
FROM student
WHERE
score BETWEEN 90 AND 100;# 输出成绩在[90,100]区间的学生姓名
SELECT name
FROM student
WHERE
name LIKE '张%';# 输出名字开头为张的学生姓名
SELECT name
FROM student
WHERE
address IS NOT NULL;# 输出住址不为空的学生姓名
GROUP BY
SELECT
class,
AVG(score)
FROM student
WHERE
age=21
GROUP BY class;# 按班级分组
SELECT
class,
AVG(score)
FROM student
WHERE
age=21
GROUP BY class,gender;# 先按班级分组再按性别分组
HAVING
SELECT
class,
AVG(score)
FROM student
WHERE
age=21
GROUP BY class
HAVING AVG(score)>=80;# 班级平均分大于等于80的班级
SELECT
class,
COUNT(name) # 班级学生姓名总数
FROM student
WHERE
age=21
GROUP BY class
HAVING COUNT(DISTINCT name)>=30;# 班级不重复学生姓名总数大于等于30的班级
ORDER BY
SELECT
name
FROM student
WHERE
age=21
ORDER BY class ASC;# 按班级名称升序
SELECT
name
FROM student
WHERE
age=21
ORDER BY class;# 按班级名称升序，ASC可省略，但不建议省略
SELECT
name
FROM student
WHERE
age=21
ORDER BY class ASC,score DESC;# 先按班级名称升序，再按学生成绩降序
LIMIT
SELECT
name
FROM student
LIMIT 5;# 输出前五行
SELECT
name
FROM student
LIMIT 5 OFFSET 3;# 输出从第四行起的五行，偏移值从0开始！！！
SELECT
name
FROM student
LIMIT 3,5;# 输出从第四行起的五行
联表查询
联接方式
通常来讲，联接时由主表（事实表）在左侧，副表（维度表）在右侧
事实表：学生考试成绩表，订单销售表等
维度表：学生信息表，考试信息表，产品信息表等
如果难以区分事实表和维度表，可根据分组字段决定，分组字段所在的为主表，另一表为副表
自连接：员工邻接表（管理者同时也是员工，且每个员工的上级编号就在该员工所在行的字段），左侧作为上级表，右侧作为员工表
交叉联接
# 笛卡尔积：每个员工都与所有部门组合，假定有n个员工和m个部门，则返回n*m行
SELECT
dept_name,emp_name
FROM
employee AS E
CROSS JOIN department AS D;# 注意：交叉联接没有联接条件
SELECT
dept_name,emp_name
FROM
employee AS E,
department AS D; # 内部联接可以简写为,形式
内部联接
# 保留有部门的员工和有员工的部门
SELECT
dept_name,emp_name
FROM
employee AS E
INNER JOIN department AS D
ON E.dept_id=D.dept_id;
SELECT
dept_name,emp_name
FROM
employee AS E
JOIN department AS D # 内部联接可以简写为JOIN形式
USING(dept_id); # 若联接的字段在两表中相同则可以简写为USING形式
外部联接
# 保留所有员工和有员工的部门
SELECT
dept_name,emp_name
FROM
employee AS E
LEFT OUTER JOIN department AS D
ON E.dept_id=D.dept_id;
SELECT
dept_name,emp_name
FROM
employee AS E
LEFT JOIN department AS D # 左外联接可以简写为LEFT JOIN形式
USING(dept_id);# 若联接的字段在两表中相同则可以简写为USING形式
# 保留有部门的员工和所有部门
SELECT
dept_name,emp_name
FROM
employee AS E
RIGHT OUTER JOIN department AS D
ON E.dept_id=D.dept_id;
SELECT
dept_name,emp_name
FROM
employee AS E RIGHT JOIN department AS D # 右外联接可以简写为RIGHT JOIN形式
USING(dept_id);# 若联接的字段在两表中相同则可以简写为USING形式
联接的进阶用法
# 交叉联接+联接条件：内部联接
SELECT
dept_name,emp_name
FROM
employee AS E,
department AS D
WHERE
E.dept_id=D.dept_id;
# 内部联接+无联接条件：交叉联接
SELECT
dept_name,emp_name
FROM
employee AS E
JOIN department AS D;
# 在左不在右
SELECT
dept_name,emp_name
FROM
employee AS E
LEFT JOIN department AS D
ON E.dept_id=D.dept_id
WHERE D.dept_id IS NULL;
# 在右不在左
SELECT
dept_name,emp_name
FROM
employee AS E
RIGHT JOIN department AS D
ON E.dept_id=D.dept_id
WHERE E.dept_id IS NULL;
# 完全外部联接：在左不在右和在右不在左
SELECT
dept_name,emp_name
FROM
employee AS E
LEFT JOIN department AS D
ON E.dept_id=D.dept_id
WHERE D.dept_id IS NULL;
UNION
SELECT
dept_name,emp_name
FROM
employee AS E
RIGHT JOIN department AS D
ON E.dept_id=D.dept_id
WHERE E.dept_id IS NULL;
多表联接
SELECT
*
FROM
student_exam_record AS R
LEFT JOIN exam_info AS EI
USING(exam_id)
LEFT JOIN student_info AS SI
USING(student_id);
# 多表联接是按顺序的，例如：A,B,C，实际是(A,B),C
集合查询
集合方式
并集
# 去重
(SELECT
name,score
FROM student_exam_record_2020)
UNION
(SELECT
name,score
FROM student_exam_record_2021);
# 不去重
(SELECT
name,score
FROM student_exam_record_2020)
UNION ALL # UNION默认对合并的表进行去重，若不去重需要使用ALL声明
(SELECT
name,score
FROM student_exam_record_2021);
# 并集后的排序1
(SELECT
name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
name,score
FROM student_exam_record_2021)
ORDER BY name ASC;
# 并集后的排序2
((SELECT
name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
name,score
FROM student_exam_record_2021))
ORDER BY name ASC;
# 并集前的排序1：错误排序，并集之前进行单独排序是没有意义的，UNION仍会按原表排序
(SELECT
name,score
FROM student_exam_record_2020
ORDER BY name ASC)
UNION ALL
(SELECT
name,score
FROM student_exam_record_2021
ORDER BY name ASC);
# 并集前的排序2：正确排序
SELECT
2020 AS tag, # 并集后的列名以并集前首个查询的列名为准
name,score
FROM student_exam_record_2020
UNION ALL
SELECT
2021,
name,score
FROM student_exam_record_2021
ORDER BY tag ASC,name ASC;
# 并集后的分页
(SELECT
name,score
FROM student_exam_record_2020)
UNION ALL
(SELECT
name,score
FROM student_exam_record_2021)
LIMIT 5;
# 并集前的分页
(SELECT
name,score
FROM student_exam_record_2020
LIMIT 5)
UNION ALL
(SELECT
name,score
FROM student_exam_record_2021
LIMIT 5);
# 并集前同时使用排序和分页的结果等其他操作请参考官方文档
其他集合操作
INTERSECT交集，EXCEPT差集的使用方式及细节要求同UNION
当前牛客网尚无法使用交集与差集，可能是服务器MySQL版本较低
若想达到交集或并集的效果请使用联接方式，具体步骤请自行实现（注意是否去重）
重点强调
1.注意集合使用时的去重
2.掌握集合操作前后的排序和分页
3.注意联接的选择及联接条件的使用
4.联接条件与查询条件的区别（请参考专栏
子查询
嵌套子查询
子查询总共执行一次，执行完毕将值返回主查询
标量子查询
SELECT *
FROM student
WHERE
score=(SELECT MAX(score) FROM student);
SELECT *
FROM student
WHERE
score>(SELECT AVG(score) FROM student);
列子查询
# SOME等价于ANY，IN等价于=ANY或=SOME，NOT IN等价于<>ALL
SELECT *
FROM student
WHERE
score>=ALL (SELECT score FROM student);
SELECT *
FROM student
WHERE
score>=ANY (SELECT AVG(score) FROM student GROUP BY class);
SELECT *
FROM student
WHERE
score IN (SELECT score FROM student WHERE date='2024-06-01');
# 等价于
SELECT *
FROM student
WHERE
score=ANY (SELECT score FROM student WHERE date='2024-06-01');
SELECT *
FROM student
WHERE
score NOT IN (SELECT score FROM student WHERE date='2024-06-01');
# 等价于
SELECT *
FROM student
WHERE
score<>ALL (SELECT score FROM student WHERE date='2024-06-01');
行子查询
SELECT *
FROM student1
WHERE
(name,score)=(SELECT name,score FROM student2 WHERE name='Bob');
表子查询
SELECT *
FROM student
WHERE
(class,score) IN (SELECT class,MAX(score) FROM student GROUP BY class);
相关子查询
主查询执行一行，子查询执行一次，如此往复至主查询执行完毕
SELECT *
FROM student AS S1
WHERE
score=(SELECT MAX(score) FROM student WHERE class=S1.class);
# EXISTS常用于相关子查询
# 查找在student1中但不在student2的学生信息
SELECT *
FROM student1 AS S1
WHERE
NOT EXISTS (SELECT * FROM student2 WHERE name=S1.name);
# 如果不存在id为5的记录则插入
INSERT INTO student(id,name,date,score)
VALUES(5,'Alice','2023-01-02',89)
WHERE
NOT EXISTS (SELECT * FROM student WHERE id=5);
派生表查询
# 派生表必须指定别名
SELECT AVG(max_score)
FROM
(SELECT class,MAX(score) AS max_score FROM student GROUP BY class) AS T;
# 大多数时候派生表都可用CTE表达式实现
子查询拓展
最新版MySQL的派生表同样支持相关子查询，详情见官方文档
部分操作可能不被支持，例如同时使用IN/SOME/ANY和LIMIT
子查询按位置划分可以是SELECT,WHERE,HAVING,FROM等
多数时候采用子查询的实现方式是多样的，并且可以与联接互相转换
基本函数
数学函数
TODO
聚合函数
TODO
字符串函数
# MySQL字符串下标从1开始
# 字节数，数字字母英文符号等为单字节，汉字中文符号等字符根据字符集而定（此处按两字节）
SELECT LENGTH(' 1B C.男。♂ ');# 13
# 字符数，不考虑字符的字节数，例如'男','。','♂'
SELECT CHAR_LENGTH(' 1B C.男。♂ ');# 9
SELECT LEFT('ABCDE',3);# ABC
SELECT RIGHT('ABCDE',3);# CDE
SELECT MID('ABCDE',3,2);# CD
SELECT LOWER('AaBb');# aabb
SELECT UPPER('AaBb');# AABB
# 字符串连接
SELECT CONCAT('AB','CD');# ABCD
# 查找子串首次出现的位置
SELECT INSTR('ABCDACD','CD');# 3
# 将指定子串全部替换为另一字符串
SELECT REPLACE('ABCDACD','CD','男');# AB男A男
# 删除前导空格
SELECT LENGTH(LTRIM(' A A '));# 7
# 删除尾随空格
SELECT LENGTH(RTRIM(' A A '));# 5
# 删除前导和尾随空格
SELECT LENGTH(TRIM(' A A '));# 4
# 重复字符串指定次数
SELECT REPEAT('AB',3);# ABABAB
# 反转字符串
SELECT REVERSE('ABCDE');# EDCBA
# 生成指定个数的空格
SELECT LENGTH(SPACE(3));# 3
# 返回第N个指定字符前的内容，正值为从左至右数，负数=值为从右至左数
SELECT SUBSTRING_INDEX('A,BC,DEDF,CD,N.GF',',',3);# A,BC,DEDF
SELECT SUBSTRING_INDEX('A,BC,DEDF,CD,N.GF',',',-2);# CD,N.GF
# 熟记以上函数足以解决大多数字符串处理的问题
# 更多内容及细节请参考官方文档
日期时间函数
由于MySQL本身的问题，这些函数有时即使功能类似，语法及使用范围也可能不同，实际使用时应特别注意
对于高频日期函数或特殊日期函数应熟记，前者经常使用，后者则可能对特殊问题有奇效
日期生成类
SELECT NOW();# 返回当前日期与时间
SELECT CURDATE();# 返回当前日期
SELECT CURTIME();# 返回当前时间
SELECT DATE(NOW());# 返回当前日期
SELECT TIME(NOW());# 返回当前时间
SELECT MAKEDATE(2024,355);# 指定年份与第N天返回年份对应的日期，此处返回2024-12-20
SELECT MAKETIME(13,11,31);# 指定时分秒返回时间，此处返回13:11:31
日期计算类
# 注意操作时不要超过数据类型的取值范围
# 常规操作
SELECT ADDDATE('2023-04-26',11);# 默认单位为天
SELECT ADDDATE('2023-04-26',INTERVAL 11 DAY);
SELECT ADDDATE('2023-04-26',INTERVAL 11 MONTH);
SELECT SUBDATE('2024-04-26',INTERVAL 11 WEEK);
SELECT SUBDATE('2023-04-26',INTERVAL 11 QUARTER);
# SELECT ADDTIME('12:25:31',10);不建议此操作，超过±60则结果错误
SELECT ADDTIME('12:25:31','01:10:22');
SELECT SUBTIME('12:25:31','00:10:22');
# 特殊操作
SELECT ADDDATE('2023-04-26',-38);# 返回'2023-03-19'
SELECT SUBDATE('2024-04-26',INTERVAL -11 YEAR);# 返回'2035-04-26'
SELECT ADDDATE('2023-04-26',INTERVAL 11 HOUR);# 返回'2023-04-26 11:00:00'
SELECT SUBDATE('2023-04-26',INTERVAL 11 MINUTE);# 返回'2023-04-25 23:49:00'
SELECT ADDDATE('2024-04-26 12:25:31',INTERVAL 11 SECOND);# 返回'2024-04-26 12:25:42'
SELECT SUBDATE('2024-04-26 12:25:31',INTERVAL 11 DAY);# 返回'2024-04-15 12:25:31'
# SELECT ADDDATE('12:25:31',INTERVAL 11 SECOND);不可操作
# SELECT SUBDATE('12:25:31',INTERVAL 11 DAY);不可操作
SELECT ADDTIME('2024-04-26 12:25:31','01:10:22');# 返回'2024-04-26 13:35:53'
SELECT SUBTIME('2024-04-26 12:25:31','18:10:22');# 返回'2024-04-25 18:15:09'
SELECT SUBTIME('2024-04-26 12:25:31','-18:10:22');# 返回'2024-04-27 06:35:53'
# SELECT ADDTIME('2023-04-26','01:10:22');不可操作
# SELECT SUBTIME('2023-04-26','00:10:22');不可操作
/*
总结：
ADDDATE/SUBDATE可用于日期/日期时间的操作，只能增减形如INTERVAL n Type或n(天)，值可正可负，
当日期增减时间时默认把日期转为日期时间，日期部分保持不变，时间为'00:00:00'
ADDTIME/SUBTIME可用于时间/日期时间的操作，只能增减形如'01:00:10'，值可正可负，
不可增减日期
*/
# 数学形式的增减
# SELECT '2023-04-26'+11;不可简化为数字
SELECT '2023-04-26'+INTERVAL 11 YEAR;
SELECT '2023-04-26'-INTERVAL 11 SECOND;
SELECT '2024-04-26 12:25:31'+INTERVAL 11 YEAR;
SELECT '2024-04-26 12:25:31'-INTERVAL 11 SECOND;
# SELECT '12:25:31'+'01:00:01';不可操作
# SELECT '12:25:31'-INTERVAL 11 SECOND;不可操作
# SELECT '2024-04-26 12:25:31'+'01:00:01';不可操作
# SELECT '2024-04-26'-'01:00:01';不可操作
/*
总结：等价于ADDDATE/SUBDATE的操作
*/
SELECT DATEDIFF('2023-04-26','2021-01-20');
SELECT DATEDIFF('2023-04-26 12:25:31','2021-01-20 02:15:11');
SELECT DATEDIFF('2023-04-26 12:25:31','2021-01-20');
# SELECT DATEDIFF('2023-04-26 12:25:31','02:15:11');不可操作
# SELECT DATEDIFF('12:25:31','02:15:11');不可操作
SELECT TIMEDIFF('12:25:31','02:15:11');
SELECT TIMEDIFF('2023-04-26 12:25:31','2021-01-20 02:15:11');
# SELECT TIMEDIFF('2023-04-26 12:25:31','2021-01-20');
# SELECT TIMEDIFF('2023-04-26 12:25:31','02:15:11');
# SELECT TIMEDIFF('2023-04-26','2021-01-20');
/*
总结：
DATEDIFF可用于日期对日期/日期时间对日期时间/日期时间对日期的操作，返回值为天
其中的日期时间本质是被截断为日期，时间部分不参与计算
TIMEDIFF可用于时间对时间/日期时间对日期时间，返回值为标准时间
*/
SELECT TIMESTAMPDIFF(DAY,'2023-04-25 13:15:11','2023-04-26 12:25:31');# 返回值为0
SELECT TIMESTAMPDIFF(DAY,'2023-04-25 03:15:11','2023-04-26 12:25:31');# 返回值为1
SELECT TIMESTAMPDIFF(MINUTE,'2023-04-25 03:15:11','2023-04-25 03:25:01');# 返回9
SELECT TIMESTAMPDIFF(HOUR,'2023-04-25','2023-04-25 03:25:01');# 返回3
# SELECT TIMESTAMPDIFF(MINUTE,'03:15:11','2023-04-25 03:25:01');不可操作
/*
总结：
TIMESTAMPDIFF可用于日期对日期时间，日期对日期，日期时间对日期时间的操作
日期自动被拓展为日期时间，时间部分为'00:00:00'
重点1：DATEDIFF会将日期时间截断为日期，TIMESTAMPDIFF则会根据所需精度进行舍入！！！
重点2：DATEDIFF为前者减后者，TIMESTAMPDIFF则是后者减前者！！！
*/
格式转换类
SELECT DATE('2023-04-25 13:15:01');# 2023-04-25
SELECT TIME('2023-04-25 13:15:01');# 13:15:01
SELECT MONTH('2023-04-25 13:15:01');# 4
SELECT SECOND('2023-04-25 13:15:01');# 1
SELECT DATE_FORMAT('2023-04-25 13:15:01','%Y%m');# 202304
SELECT DATE_FORMAT('2023-04-25 13:15:01','%Y-%m-%d');# 2023-04-25
SELECT DATE_FORMAT('2023-04-25 13:15:01','%H:%i:%s');# 13:15:01
SELECT DATE_FORMAT('2023-04-25 13:15:01','%h:%i:%s');# 01:15:01
SELECT DATE_FORMAT('2023-04-25 13:15:01','%m');# 04
SELECT DATE_FORMAT('2023-04-25 13:15:01','%s');# 01
/*
总结：
DATE_FORMAT会返回标准格式的年月日时分秒，位数不足会补0
形如MONTH位数不足则不补0
*/
其他相关函数
SELECT LAST_DAY('2023-04-25 13:15:01');# 返回所属月份的最后一天，返回2024-04-30
SELECT LAST_DAY('2024-02-25');# 2024-02-29
SELECT WEEKOFYEAR('2023-02-26');
# 返回本年第几周，从本年第一个周一起每七天数值+1，返回8（首个周一前的日期计入去年最后一周）
SELECT WEEKDAY('2024-02-25');
# 返回当前周几，周一至周日：0-6，返回6，即周日
# 更多函数及细节请参考官方文档
空值处理
NULL的处理往往是查询过程的处理重点，下面探讨一些可能的情况
NULL的特性
# NULL与任何值比较或运算的结果都是NULL，包括其本身
SELECT 1>NULL;# NULL
SELECT 1+NULL;# NULL
SELECT NULL=NULL;# NULL
# NULL的判定必须使用IS NULL或IS NOT NULL
SELECT NULL IS NULL;# 1
聚合函数中的NULL
# 聚合函数默认忽略NULL
# 假定colname列中存在NULL
SELECT COUNT(colname) FROM tbname;# 返回不包含NULL的总数
SELECT AVG(colname) FROM tbname;
SELECT SUM(colname)/COUNT(colname) FROM tbname;# 等价于上行查询
# 假定colname列中存在NULL，NULL视为0，求colname的平均值
SELECT AVG(IF(colname IS NULL,0,colname)) FROM tbname;
SELECT SUM(colname)/COUNT(*) FROM tbname;# 等价于上行查询
# 假定colname列中存在NULL，求NULL的个数
SELECT SUM(colname IS NULL) FROM tbname;
# 假定colname列全为NULL，聚合函数的结果
SELECT COUNT(colname) FROM tbname;# 返回0
SELECT MAX(colname) FROM tbname;# 返回NULL
# 假定colname1列存在NULL，求colname1列NULL值对应记录的colname2列的平均值
SELECT AVG(IF(colname1 IS NULL,colname2,NULL)) FORM tbname;
# 假定colname1列存在NULL，按colname1是否为NULL分组求colname2列的总和
SELECT
IF(colname1 IS NULL,'NULL汇总','其他汇总') AS group_name,
SUM(colname2)
FROM tbname
GROUP BY
IF(colname1 IS NULL,'NULL汇总','其他汇总');
联接中的NULL
TODO
进阶
CTE通用表达式
复杂查询的多层嵌套往往会造成逻辑混乱，给阅读也带来一定的障碍
一般地，可利用CTE表达式帮助理清逻辑，也便于查找错误
# 单个表达式
WITH T AS
(SELECT name,score
FROM student
WHERE name LIKE 'A*')
SELECT AVG(score) FROM T;
# 多个表达式
WITH T1 AS
(SELECT date,score
FROM student
WHERE name='Alice'),# 多个表达式以逗号分隔
T2 AS
(SELECT
date,
SUM(socre) OVER(ORDER BY date ASC)
FROM T1)
# 后面的表达式可引用前面表达式已定义的表
SELECT * FROM T2 WHERE date>='2024-06-20';
Window窗口函数
取值函数
FIRST_VALUE
SELECT
score-FIRST_VALUE(score) OVER(PARTITION BY name)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的分组中的第一行
FROM
student;
# 等价于
SELECT
score-(SELECT score FROM student AS S WHERE S.name=P.name LIMIT 1)
FROM
student AS P;
SELECT
score-FIRST_VALUE(score) OVER(PARTITION BY name ORDER BY date ASC)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的第一行
FROM
student;
# 等价于
SELECT
score-(SELECT score FROM student AS S WHERE S.name=P.name ORDER BY date ASC LIMIT 1)
FROM
student AS P;
# PARTITION BY类似GROUP BY
# 不难看出，窗口函数在某种程度上与SELECT的相关子查询类似
LAST_VALUE
SELECT
score-LAST_VALUE(score) OVER(PARTITION BY name)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的分组中的最后一行
FROM
student;
# 等价于（由于LIMIT不支持子查询也不支持变量，因此该实现较复杂，可待有一定基础后再看）
WITH T AS
(SELECT
S.*,
@seq:=@seq+1 AS seq # 每一行都令变量+1
FROM student AS S,(SELECT @seq:=0) AS VT)# 定义一个变量用于标记行号，初始值为1
SELECT
score-(SELECT score FROM T WHERE name=P.name ORDER BY seq DESC LIMIT 1)
# 利用行号逆序取首行，即取最后一行
FROM
T AS P;
# 可以看出该实现已经很麻烦，这也正是窗口函数的优势所在
SELECT
score-LAST_VALUE(score)
OVER(PARTITION BY name
ORDER BY date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的最后一行
# 注意此处使用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 用于将范围定为整个分区
FROM
student;
# 等价于
SELECT
score-(SELECT score FROM student AS S WHERE S.name=P.name ORDER BY date DESC LIMIT 1)
FROM
student AS P;
# 现在已引出窗口的全部三要素：分区PARTITION BY，排序ORDER BY和范围 ROWS或RANGE
# 事实上，三要素高度依赖上下文（即当前行），这与相关子查询不谋而合
# 分区与排序通过相关子查询的演示其本质已经显而易见
# 范围是分区的子集，且根据上下文改变而改变
# UNBOUNDED PRECEDING与UNBOUNDED FOLLOWING分别表示分区的上界和下界，当范围设置为两者时则范围等价于分区
# n PRECEDING与n FOLLOWING（n为数字）分别表示从当前行向上偏移的行数和向下偏移的行数
# CURRENT ROW为当前行
# 为什么第二个查询要使用范围？因为ORDER BY的使用会导致范围隐式指定为分区上界到当前行，这有时不符合需求
# 对于排序函数和LAG,LEAD，指定范围无意义，即使指定MySQL也会忽略
# 可以仅指定范围的起始，ROWS 3 PRECEDING等价于ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
# 也可以同时指定起始和结束,ROWS BETWEEN 3 PRECEDING AND 1 FOLLOWING
NTH_VALUE
SELECT
score-NTH_VALUE(score,3)
OVER(PARTITION BY name
ORDER BY date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的第三行
FROM
student;
LEAD|LAG
SELECT
score-LEAD(score,1)
OVER(PARTITION BY name
ORDER BY date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中'对应的下一行'（没有下一行则返回NULL）
# 注意'对应的下一行'的实际意义，此处指当前行在排序后的下一行
FROM
student;
# 等价于
WITH T AS
(SELECT
S.*,
@seq:=@seq+1 AS seq # 每一行都令变量+1
FROM student AS S,(SELECT @seq:=0) AS VT
ORDER BY name ASC,date ASC)# 定义一个变量用于标记行号，初始值为1
SELECT
score-(SELECT score FROM T WHERE name=P.name AND seq=P.seq+1)
# 利用排序后的临时表
FROM
T AS P;
SELECT
score-LEAD(score,1,score)
OVER(PARTITION BY name
ORDER BY date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
# 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的对应下一行（没有下一行则返回当前行）
FROM
student;
# 窗口函数就好像时复制一份原表后进行一系列操作后再返回所需值给查询，也就意味着窗口函数内的操作不影响查询主体
# LAG用法等同于LEAD，只是它返回的是当前行的上一行xxxxxxxxxx SELECT score-LEAD(score,1) OVER(PARTITION BY name ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) # 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中'对应的下一行'（没有下一行则返回NULL） # 注意'对应的下一行'的实际意义，此处指当前行在排序后的下一行FROM student;# 等价于WITH T AS(SELECT S.*, @seq:=@seq+1 AS seq # 每一行都令变量+1FROM student AS S,(SELECT @seq:=0) AS VTORDER BY name ASC,date ASC)# 定义一个变量用于标记行号，初始值为1SELECT score-(SELECT score FROM T WHERE name=P.name AND seq=P.seq+1) # 利用排序后的临时表FROM T AS P;SELECT score-LEAD(score,1,score) OVER(PARTITION BY name ORDER BY date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) # 窗口函数：按姓名分组，取出姓名等于当前姓名的按日期排序后的分组中的对应下一行（没有下一行则返回当前行）FROM student;# 窗口函数就好像时复制一份原表后进行一系列操作后再返回所需值给查询，也就意味着窗口函数内的操作不影响查询主体# LAG用法等同于LEAD，只是它返回的是当前行的上一行D
排名函数
SELECT
*,
DENSE_RANK() OVER(PARTITION BY name ORDER BY score DESC)
# 根据ORDER BY的列进行排名
FROM
student;
# 排名函数的语法皆如上所示
# 排名函数的区别：
# 对于数值列：10，20，20，30
# DENSE_RANK：密集排名（也称中国式排名），返回1，2，2，3
# RANK：国际排名，返回1，2，2，4
# ROW_NUMBER：行编号，返回1，2，3，4
# PERCENT_RANK：国际排名的百分比形式
# 另外NTILE和CUME_DIST请参考官方文档
聚合函数
SELECT
*,
AVG(score)
OVER(PARTITION BY name
ORDER BY date ASC
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
# 移动平均值，返回每个学生每次考试的（本次得分加前两行得分）的三次得分的平均值
FROM
student;
# 聚合函数语法皆如上所示
# 聚合函数有AVG,SUM,COUNT,MAX,MIN
ROWS和RANGE的区别
SELECT
*,
AVG(score)
OVER(PARTITION BY name ROWS 2 PRECEDING)
FROM
student;
# 偏移指定行数，不依赖排序函数
SELECT
*,
AVG(score)
OVER(PARTITION BY name
ORDER BY date ASC
RANGE INTERVAL 2 DAY PRECEDING)
FROM
student;
# 表示（当前行得分+当前行对应的日期的前两日的得分）的平均值
# 假定当前行对应日期为2023-03-06，前两日为2023-03-04和2023-03-05
# 日期为此三天的所有得分（不一定是三个）均参与平均值计算
SELECT
*,
AVG(score)
OVER(PARTITION BY name
ORDER BY score ASC
RANGE 2 PRECEDING)
FROM
student;
# 同理，但此处为数值
# RANGE必须依赖ORDER BY且排序的必须是数值或日期
# 文字描述可能不够清晰，此处差异最好自行尝试对比
窗口函数的再探索
越界
LEAD,LAG,NTH_VALUE或聚合函数超出分区或范围会返回NULL
其中LEAD,LAG可以显式指定超出范围后返回的内容
命名窗口
SELECT
*,
AVG(score) OVER w,
SUM(score) OVER w
FROM
student
WINDOW w AS (PARTITION BY name ORDER BY date ASC RANGE INTERVAL 2 DAY PRECEDING);
# 如果同一窗口定义多次使用则可以使用命名窗口，简化代码量同时提高阅读性
窗口函数的执行顺序
SELECT
name,
AVG(score),
DENSE_RANK()
OVER(PARTITION BY name ORDER BY AVG(score) DESC)
FROM
student
GROUP BY name;
# 实际上，窗口函数执行在SELECT之后
# 因此，窗口函数基于WHERE,GROUP BY,HAVING之后的表，而不是FROM后的表
# 也因此，窗口函数只能在SELECT中使用
# 如果对查询主体使用GROUP BY，应非常了解窗口函数的执行顺序，否则将造成错误xxxxxxxxxx SELECT name, AVG(score), DENSE_RANK() OVER(PARTITION BY name ORDER BY AVG(score) DESC)FROM studentGROUP BY name;# 实际上，窗口函数执行在SELECT之后# 因此，窗口函数基于WHERE,GROUP BY,HAVING之后的表，而不是FROM后的表# 也因此，窗口函数只能在SELECT中使用# 如果对查询主体使用GROUP BY，应非常了解窗口函数的执行顺序，否则将造成错误D
其他特性
聚合函数内不能嵌套聚合函数，即二次聚合问题
排名值是无符号整数，存在排名值的计算时应将其转为有符号整数
SELECT的执行顺序及注意事项
了解SELECT语句的执行顺序是非常重要的，如果缺少了解，在面对较复杂的查询时可能首先会被语法错误绊倒，此文较为详细全面的展示了每个组成部分的执行顺序及注意要点，其中很多内容在学习初期可能未涉及，可随着学习的深入不断加深理解，部分内容可参考专栏其他文章
数据准备
演示代码
# 如果是学习初期，不要被此代码吓到，后面会有详细解释，不懂之处可在进一步学习时巩固
WITH D AS
(SELECT
dept_id,
dept_name
FROM department
WHERE
dept_id<='1004')
# 取出部门编号小于'1004'的部门编号及对应部门名称，形成表D（CTE表达式）
SELECT
ANY_VALUE(D.dept_id) AS dept_id,
IF(GROUPING(D.dept_name),'汇总',D.dept_name) AS name,
# 将WITH ROLLIP总计的组名改为汇总
COUNT(DISTINCT E.emp_name) AS dept_cnt,
# 部门内员工去重计数
DENSE_RANK() OVER w-1 AS w_rank
# 根据部门去重人数逆序排名并将排名-1（w1为命名窗口）
FROM
employee AS E LEFT JOIN D
# 左外联接
ON E.dept_id=D.dept_id
# 联接条件为部门编号
WHERE
E.age BETWEEN 20 AND 50
# 筛选年龄在20-50岁的员工
GROUP BY D.dept_name WITH ROLLUP
# 按部分分组，WITH ROLLUP总计
HAVING COUNT(DISTINCT E.emp_name)>=3
# 去重计数后员工数不小于3的部门保留
WINDOW w AS (ORDER BY COUNT(DISTINCT E.emp_name) DESC)
# 窗口w的定义
ORDER BY GROUPING(D.dept_name) DESC,dept_cnt ASC
# 先按照是否为总计行排序，再按部门人数升序
LIMIT 4;
# 取出前四行
# 以上仅供演示所用，实际完成此查询未必需要如此操作
执行顺序
1.CTE
获得不含id='1005'的记录的临时表D
2.FROM
2.1对表E和表D进行交叉联接，形成虚拟表VT-J1
2.2.根据ON条件筛选满足条件的行，形成VT-J2
2.3.由于LEFT JOIN，需要保留在表E中不满足筛选条件的行，形成VT-J3
获得所有员工信息及对应的部门信息的虚拟表VT-J
3.WHERE
筛选年龄在20-50岁的记录，形成虚拟表VT-WH
4.GROUP BY
4.1.按照部门信息分组，组名就是部门名（dept_name为NULL的所有记录为一组，组名为NULL），形成虚拟表VT-G1
4.2.由于WITH ROLLUP，还需要新增总计行，组名为NULL，形成虚拟表VT-G2
获得按部门分组后的虚拟表VT-G
5.HAVING
筛选部门的不重复员工总数不少于3的分组，形成虚拟表VT-H
注意1：组名为NULL的分组因不重复员工数为3而被保留
注意2：组名为NULL的总计行因不重复员工数为19而被保留（总计行计算的是VT-WH的所有非重复员工数，不区分部门）
6.SELECT
保留部门编号，部门名称，各部门不重复员工数，形成虚拟表VT-S
注意1：由于dept_id既不是分组列也并非聚合列，因此直接引用会报错，此处使用ANY_VALUE可正确引用
注意2：此处想仅给总计行重命名为'汇总'，使用GROUPING判断是否为总计行，如果是则重命名，否则保持原名
7.WINDOW
将各部门不重复员工数逆序作为窗口进行密集排名，将排名值减一作为实际排名，将该排名列加入VT-S，形成虚拟表VT-WI
注意1：此步骤虽基于VT-S，但因处在SELECT中无法用dept_cnt替代COUNT(DISTINCT E.emp_name)
注意2：事实上，窗口函数是上下文相关的（请参考专栏内
8.ORDER BY
先根据是否为总计行（是则为1，否则为0）逆序（即总计行排至第一行），再根据部门的不重复人数升序，形成虚拟表VT-O
9.LIMIT
输出VT-O中的前4行
高级
分组和排序的高级应用
正则匹配
基于CTE的递归
应用
极值与排名的实现
最多人数的解法
留存率与连续天数的通解
DDL数据定义语句
Show信息获取语句
TCL事务控制语句
DCL数据控制语句
过程和函数
触发器
事件
分区、视图、数据表、模式、数据库
#MySQL#计算机编程合集 文章被收录于专栏
本专栏包含Java、Linux、MySQL、Redis、RabbitMQ、Docker、HTML、CSS、JS等等，作为个人学习记录及知识总结，将长期进行更新！
美团一面2109人在聊
查看10道真题和解析