MySQL8.0其他新特性

database

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(每一类重新排序)

  1. 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

  1. 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;

  1. 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;

  1. 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)

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

  1. LAST_VALUE(expr)函数

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

其他函数

  1. WTH_VALUE(expr,n)函数

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

  1. 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;

以上是 MySQL8.0其他新特性 的全部内容, 来源链接: utcz.com/z/536371.html

回到顶部