MySQL8.0其他新特性

MySQL8.0新特性概述

MySQL8.0新增特性

MySQL8.0移除的旧特性

新特性1：窗口函数

窗口函数的分类

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

窗口函数分为静态和动态

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

语法格式

函数 OVER (| PARTITION BY 字段名 ORDER BY 字段名 ASC | DESC);

或是

函数 OVER 窗口名 _WINDOW 窗口名 AS (| PARTITION BY 字段名 ORDER BY 字段名 ASC | DESC);

举例说明

序号函数

1. ROW_NUMBER()函数

能够对数据的序号进行顺序排序，在表中第一行添加一列字段（从1—n）

#查询每个商品分类下价格从高到低的商品信息
SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
       #  序号函数           分类                     排序                 添加字段的别名
要查询的字段
from goods;#要查询的表

#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)

2. RANK()函数

#查询每个商品分类下价格从高到低的商品信息
SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查询的字段
from goods;

#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)
#但是当价格一样时，序号会是一样的，eg:1、2、2、4

3. DENSE_RANK()函数

#查询每个商品分类下价格从高到低的商品信息
SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS ROW_NUM,
要查询的字段
from goods;

#结果：将category_id相同的放在一起，并编号到1—n(每一类重新排序)
#但是当价格一样时，序号会是一样的，eg:1、2、2、3

分布函数

1. PERCENT_RANK()函数

计算方式：(rank-1) / (rows-1)

#查询表中category_id = 1的商品类别下的PERCENT_RANK的值
SELECT RANK() OVER w AS r,
PERCENT_RANK() OVER w AS pr,
要查询的字段
WHERE category_id = 1 WINDOW w AS (PARTITION BY category_id ORDER BY price DESC) AS pr;

#此题的比率是序号-1 / 总数-1；

2. CUME_DIST()函数

主要是查询小于或是等于某个数的值

#查询goods数据表下小于或是等于当前价格的比例
SELECT CUME_DIST() OVER (PARTITION BY category_id ORDER BY price ASC) AS cd,
要查询的字段
FROM goods;

#当前数据小于、等于全部数据的的概率

前后函数

1. LAG(expr,n)函数（前）

#查询前一个商品的价格于当前价格的查值

SELECT 基本信息，price - pre_price AS diff_price
FROM (SELECT 基本信息，LAG(expr,1) OVER w AS pre_price#子查询：多出一列是上一行的价格
      FROM goods
      WINDOW w AS (PARTITION BY category_id ORDER BY price ASC))t;    

2. LEAD(expr,n)函数（后）

和前面的几乎一样

首位函数

1. FIRST_VALUE(expr)函数

FIRST_VALUE(expr)函数返回第一个expr的值

#按照价格排序，查询第一个商品的价格信息
SELECT 基本信息，FIRST_VALUE(price) OVER w AS first_price
FROM goods WINDOW w AS (ARTITION BY category_id ORDER BY price ASC)

#新增加的列是数据是该分类下的第一行价格数据

2. LAST_VALUE(expr)函数

LAST_VALUE(expr)函数返回最后一个expr的值

其他函数

1. WTH_VALUE(expr,n)函数

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

2. NTILE(n)函数

NTILE(n)将每一类平均进行n组

小结

窗口函数可以对数据进行分组、排序，并且不会减少由表中的行数，对统计和排序非常有用

公用表表达式

普通公用表表达式

• 语法结果

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

• 举个例子

#查询员工所在部门的详细信息
方式一：子查询实现
SELECT *
FROM departments
WHERE department_id IN (SELECT DISTINCT department_id
                        FROM departments
                       );
方式二:CTE实现
WITH CIE_emp t2
AS (SELECT DISTINCT department_id FROM departments)#我感觉是创建了一个临时的新表

SELECT *
FROM departments t1 JOIN CIE_emp t2#多表连接了
WHERE t1.department_id = t2.department_id;

递归公用表表达式

递归;自己调用自己

• 语法格式：

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

• 举个例子

#查询所有的下下属
WITH PECURSIVE cte 
AS
(
 SELECT 基本信息，1 AS n FROM employees WHERE id = 100 #初始值
 UNION ALL
 SELECT 基本信息，n+1 FROM employees AS a JOIN cte 
 ON a.id = cte.id#递归调用
)
SELECT 基本信息 
FROM cte 
WHERE n > 3;