MySQL8 的新特性：NoSQL、索引、账户管理、窗口函数...

1
2

WITH cte_name (col_name1,clo_name2..) AS (Suquery)
SELECT * FROM cte_name;

1
2
3
4

#方式一
函数 OVER ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])
#方式二
函数 OVER 窗口名 ... WINDOW 窗口名 AS ([PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC])

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

CREATE TABLE goods (
	id INT PRIMARY KEY AUTO_INCREMENT,
	category_id INT,
	category VARCHAR ( 15 ),
	NAME VARCHAR ( 30 ),
	price DECIMAL ( 10, 2 ),
	stock INT,
	upper_time DATETIME 
);
	
INSERT INTO goods ( category_id, category, NAME, price, stock, upper_time )
VALUES
	( 1, '女装/女士精品', 'T恤', 39.90, 1000, '2020-11-10 00:00:00' ),
	( 1, '女装/女士精品 ', '连衣裙', 79.90, 2500, '2020-11-10 00:00:00' ),
	( 1, '女装/女士精品', '卫衣', 89.90, 1500, ' 2020-11-10 00:00:00 ' ),
	( 1, '女装/女士精品', '牛仔裤', 89.90, 3500, '2020-11-10 00:00:00' ),
	( 1, '女装/女士精品','百褶裙',29.90,500,' 2020-11-10 00:00:00' ),
	( 1, '女装/女士精品', '呢绒外套', 399.90, 1200, '2020-11-10 00:00:00' ),
	( 2, '户外运动', '自行车', 399.90, 1000, '2020-11-10 00:00:00 ' ),
	( 2, '户外运动', '山地自行车', 1399.90, 2500, '2020-11-10 00:00:00' ),
	( 2, '户外运动', '登山杖', 59.90, 1500, '2020-11-10 00:00:00' ),
	( 2, '户外运动', '骑行装备', 399.90, 3500, '2020-11-10 00:00:00' ),
	( 2, '户外运动', '运动外套', 799.90, 500, ' 2020-11-10 00:00:00 ' ),
	( 2, '户外运动', '滑板', 499.90, 1200, ' 2020-11-10 00:00:00' );	

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

#ROW NUMBER()函数：按PARTITION来给每个区添加顺序排序
#举例：查询goods数据表中每个商品分类下价格降序排列的各个商品信息。
SELECT
	ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
	id,
	category_id,
	category,
	NAME,
	price,
	stock
FROM
	goods;
	
#举例：查询goods数据表中每个商品分类下价格最高的3种商品信息。
SELECT
	* 
FROM
	(
	SELECT
		ROW_NUMBER() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
		id,
		category_id,
		category,
		NAME,
		price,
		stock 
	FROM
		goods 
	) t 
WHERE
	row_num <= 3;
	
#RANK()函数：按PARTITION来给每个区添加名次排序（并列第三就是1,2,3,3,5）
#举例：使用RANK()函数获取goods数据表中各类别的价格从高到低排序的各商品信息。
SELECT
	RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
	id,
	category_id,
	category,
	NAME,
	price,
	stock
FROM
	goods;

# DENSE_RANK()函数：按PARTITION来给每个区添加排序（并列第三就是1,2,3,3,4）
SELECT
	DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS row_num,
	id,
	category_id,
	category,
	NAME,
	price,
	stock 
FROM
	goods;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#分布函数
#PERCENT_RANK()函数:公式(rank -1)/(rows - 1)
#举例：计算goods数据表中名称为“女装/女士精品"的类别下的商品的PERCENT_RANK值。
SELECT
	RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS r,
	PERCENT_RANK() OVER ( PARTITION BY category_id ORDER BY price DESC ) AS pr,
	id,
	category_id,
	category,
	NAME,
	price,
	stock 
FROM
	goods 
WHERE
	category_id = 1;
	
	
#CUME_DIST()函数:当前价格/区的最大价格
#举例：查询goods数据表中小于或等于当前价格的比例。
SELECT
	CUME_DIST() OVER ( PARTITION BY category_id ORDER BY price ASC ) AS cd,
	id,
	category,
	NAME,
	price 
FROM
	goods;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49

#LAG(expr,n)函数：返回当前行的前n行的expr值
#举例：查询goods数据表中前一个商品价格与当前商品价格的差值。
SELECT
	id,
	category,
	NAME,
	price,
	pre_price,
	price - pre_price AS diff_price 
FROM
	(
	SELECT
		id,
		category,
		NAME,
		price,
		LAG( price, 1 ) OVER ( PARTITION BY category_id ORDER BY price ASC ) AS pre_price 
	FROM
	goods WINDOW w AS ( PARTITION BY category_id ORDER BY price )) t;

#其中，子查询是查询当前行数据 + 上一行价格
SELECT
	id,
	category,
	NAME,
	price,
	LAG( price, 1 ) OVER ( PARTITION BY category_id ORDER BY price ASC) AS pre_price
FROM
	goods

#LEAD(expr,n)函数：返回当前行的后n行的expr值
#举例：查询goods数据表中后一个商品价格与当前商品价格的差值。
SELECT
	id,
	category,
	NAME,
	price,
	behind_price,
	price - behind_price AS diff_price 
FROM
	(
	SELECT
		id,
		category,
		NAME,
		price,
		LEAD( price, 1 ) OVER ( PARTITION BY category_id ORDER BY price ASC ) AS behind_price 
	FROM
	goods WINDOW w AS ( PARTITION BY category_id ORDER BY price )) t;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

#FIRST_VALUE(expr)函数:返回第一个expr的值
#举例：按照价格排序，查询第1个商品的价格信息。
SELECT
	id,
	category,
	NAME,
	price,
	stock,
	FIRST_VALUE( price ) OVER w AS first_price 
FROM
	goods WINDOW w AS ( PARTITION BY category_id ORDER BY price );
	
#LAST_VALUE(expr)函数:返回最一个expr的值

1
2
3

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

#子查询语句
SELECT
	* 
FROM
	departments 
WHERE
	department_id IN ( SELECT DISTINCT department_id FROM employees );
	
#普通公用表达式
WITH department_id_socpe AS ( SELECT DISTINCT department_id FROM employees )
SELECT
* 
FROM
	departments d
	JOIN department_id_socpe d_s 
WHERE
	d.department_id = d_s.department_id;

1
2
3

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

#递归公用表达式
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;