05-MySQL8新特性

MySQL8新特性

公共表表达式

• 生成一个命名的临时表，并且只能在查询期间有效，该临时表在一个查询中可多次引用，甚至可以自引用
• 相对与子查询来说，该临时表在sql语句中可多次引用

with 临时表名 as(子查询) sql语句;	-- 定义临时表
with 临时表名(字段列表) as(子查询) sql语句;	-- 定义临时表，并重新定义列名
with recursive 临时表名(字段列表) as(子查询) sql语句;	-- 定义可自引用的临时表

使用递归查询生成序列

with recursive test as(
  select 1 as n
  union all
  select 1+n from test where n<10
)
select n from test;

窗口函数

函数名	描述
所有聚合函数	都可以作为窗口函数
row_number()	返回窗口分区内数据的行号
rank()	与row_number()类似，只是对于相同数据会产生重复的行号，之后数据行号会产生间隔
dense_rank()	与rank()类似，之后数据行号会不会产生间隔

窗口函数 over(partition by 字段名)	-- 仅进行分组
窗口函数 over(partition by 字段名 order by 字段名 排序规则)	-- 分组后再进行排序

• 只能用于select子句中
• 不会改变原来数据行数，会在该行增加一个字段用来存储统计信息，具体看下面：

/*
现有score表
+----------+------------+-----------+
| stu_name | stu_course | stu_score |
+----------+------------+-----------+
| zhangsan | mysql      |        95 |
| zhangsan | redis      |        99 |
| zhangsan | mongodb    |        99 |
| lisi     | mysql      |        97 |
| lisi     | redis      |        95 |
| lisi     | mongodb    |        97 |
+----------+------------+-----------+
*/
select stu_name,sum(stu_score) from score group by stu_name;
/*
+----------+----------------+
| stu_name | sum(stu_score) |
+----------+----------------+
| zhangsan |            293 |
| lisi     |            289 |
+----------+----------------+
2 rows in set (0.00 sec)
*/
select stu_name,sum(stu_score) over(partition by stu_name) from score;
/*
+----------+--------------------------------------------+
| stu_name | sum(stu_score) over(partition by stu_name) |
+----------+--------------------------------------------+
| lisi     |                                        289 |
| lisi     |                                        289 |
| lisi     |                                        289 |
| zhangsan |                                        293 |
| zhangsan |                                        293 |
| zhangsan |                                        293 |
+----------+--------------------------------------------+
6 rows in set (0.00 sec)
*/