一、MySQL8新特性概述
MySQL从5.7版本直接跳跃发布了8.0版本
MySQL8版本在功能上做了显著的改进与增强，（1）开发者对MySQL的源代码进行了重构，（2）最突出的一点是多MySQL Optimizer优化器进行了改进
（1）MySQL8.0新增特性
- 更简便的NoSQL支持
- 更好的索引：MySQL8中新增了隐藏索引和降序索引
- 更完善的JSON支持：MySQL8新增了聚合函数JSON_ARRAYAGG()和JSON_OBJECTAGG()
- 安全和账户管理：MySQL8中新增了caching_sha2_password授权插件、角色、密码历史记录和FIPS模式支持
- InnoDB的变化：在MySQL8版本中，InnoDB在自增、索引、加密、死锁、共享锁等方面做了大量的改进和优化，并且支持原子数据定义语言（Automic DDL)
- 数据字典：MySQL 8新增了事务数据字典
- 原子数据定义语句：
-
MySQL8开始支持原子数据定义语句（Automic DDL），即原子DDL。目前，只有InnoDB存储引擎支持原子DDL
-
在执行DROP TABLE、CREATE TABLE、ALTER TABLE、 RENAME TABLE、TRUNCATE TABLE、CREATE TABLESPACE、DROP TABLESPACE等操作时，都支持原子操作，即事务要么完全操作成功，要么失败后回滚，不再进行部分提交
-
对于从MySQL5.7复制到MySQL8版本中的语句，可以添加IF EXISTS或IF NOT EXISTS语句来避免发生错误
-
- 资源管理：MySQL8开始支持创建和管理资源组，允许将服务器内运行的线程分配给特定的分组，以便线程根据组内可用资源执行
- 字符集支持：MySQL8中默认的字符集由latin1更改为utf8mb4，并首次增加了日语所特定使用的集合，utf8mb4_ja_0900_as_cs
- 优化器增强：MySQL8优化器开始支持隐藏索引和降序索引。隐藏索引不会被优化器使用，验证索引的必要性时不需要删除索引，先将索引隐藏。降序索引允许优化器对多个列进行排序，并且允许排序顺序不一致
- 公用表表达式：公用表表达式（Common Table Expressions）简称为CTE，MySQL现在支持递归和非递归两种形式的CTE。基础语法如下：
WITH cte_name (col_name1,col_name2 ...) AS (Subquery) SELECT * FROM cte_name;Subquery代表子查询，子查询前使用WITH语句将结果集命名为cte_name，在后续的查询中即可使用cte_name进行查询
- 窗口函数：MySQL8开始支持窗口函数。在之前的版本中已存在的大部分聚合函数在MySQL8中也可以作为窗口函数来使用
- 正则表达式支持：增加了REGEXP_LIKE()、EGEXP_INSTR()、REGEXP_REPLACE()和 REGEXP_SUBSTR()等函数来提升性能
- 内部临时表：TempTable存储引擎取代MEMORY存储引擎成为内部临时表的默认存储引擎。TempTable存储引擎为VARCHAR和VARBINARY列提供高效存储。internal_tmp_mem_storage_engine会话变量定义了内部临时表的存储引擎，可选的值有两个，TempTable和MEMORY，其中TempTable为默认的存储引擎。temptable_max_ram系统配置项定义了TempTable存储引擎可使用的最大内存数量
- 日志记录：在MySQL8中错误日志子系统由一系列MySQL组件构成。这些组件的构成由系统变量log_error_services来配置，能够实现日志事件的过滤和写入
- 备份锁：新的备份锁允许在线备份期间执行数据操作语句，同时阻止可能造成快照不一致的操作
- 增强的MySQL复制
（2）MySQL8.0移除的旧特性
在MySQL5.7版本上开发的应用程序如果使用了MySQL8.0 移除的特性，语句可能会失败，或者产生不同的执行结果
- 查询缓存：查询缓存已被移除
- 加密相关：删除了一些加密相关的内容
- 空间函数相关：删除了一些空间函数相关的内容
- \N和NULL：在SQL语句中，解析器不再将\N视为NULL。这项变化不会影响使用LOAD DATA INFILE或者SELECT…INTO OUTFILE操作文件的导入和导出。在这类操作中，NULL仍等同于\N
- mysql_install_db：移除了mysql_install_db程序，数据字典初始化需要调用带着–initialize或者–initialize-insecure的mysqld来代替实现。另外，–bootstrap和INSTALL_SCRIPTDIR CMake也已被删除
- 通用分区处理程序：移除了通用分区处理程序。要对表进行分区，表所使用的存储引擎需要自有分区处理程序。有两个提供本地分区支持的存储引擎：InnoDB和NDB，而在MySQL8中只支持InnoDB
- 系统和状态变量信息：在INFORMATION_SCHEMA数据库中，对系统和状态变量信息不再进行维护。被删除的内容都可使用性能模式中对应的内容进行替代
- mysql_plugin工具：mysql_plugin工具用来配置MySQL服务器插件，现已被删除
二、新特性1：窗口函数
（1）使用窗口函数前后对比
- 假设我现在有这样一个数据表，它显示了某购物网站在每个城市每个区的销售额
CREATE TABLE sales( id INT PRIMARY KEY AUTO_INCREMENT, city VARCHAR(15), county VARCHAR(15), sales_value DECIMAL ); INSERT INTO sales(city,county,sales_value) VALUES ('北京','海淀',10.00), ('北京','朝阳',20.00), ('上海','黄埔',30.00), ('上海','长宁',10.00); SELECT * FROM sales;查询：
- 需求：现在计算这个网站在每个城市的销售总额、在全国的销售总额、每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率 如果用分组和聚合函数，就需要分好几步来计算： 第一步，计算总销售金额，并存入临时表a：
查看一下临时表a ：CREATE TEMPORARY TABLE a -- 创建临时表 SELECT SUM(sales_value) AS sales_value -- 计算总计金额 FROM sales;
第二步，计算每个城市的销售总额并存入临时表b： CREATE TEMPORARY TABLE b -- 创建临时表 SELECT city,SUM(sales_value) AS sales_value -- 计算城市销售合计 FROM sales GROUP BY city;
第三步，计算各区的销售占所在城市的总计金额的比例，和占全部销售总计金额的比例。我们可以通过 下面的连接查询获得需要的结果：
结果显示：市销售金额、市销售占比、总销售金额、总销售占比都计算出来了mysql> SELECT s.city AS 城市,s.county AS 区,s.sales_value AS 区销售额, -> b.sales_value AS 市销售额,s.sales_value/b.sales_value AS 市比率, -> a.sales_value AS 总销售额,s.sales_value/a.sales_value AS 总比率 -> FROM sales s -> JOIN b ON (s.city=b.city) -> JOIN a -> ORDER BY s.city,s.county; +--------+--------+--------------+--------------+-----------+--------------+-----------+ | 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 | +--------+--------+--------------+--------------+-----------+--------------+-----------+ | 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 | | 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 | | 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 | | 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 | +--------+--------+--------------+--------------+-----------+--------------+-----------+ 4 rows in set (0.00 sec) - 使用窗口函数：
- 同样的查询，如果用窗口函数，就简单多了。我们可以用下面的代码来实现
mysql> SELECT city AS 城市,county AS 区,sales_value AS 区销售额, -> SUM(sales_value) OVER(PARTITION BY city) AS 市销售额, -- 计算市销售额 -> sales_value/SUM(sales_value) OVER(PARTITION BY city) AS 市比率, -> SUM(sales_value) OVER() AS 总销售额, -- 计算总销售额 -> sales_value/SUM(sales_value) OVER() AS 总比率 -> FROM sales -> ORDER BY city,county; +------+------+----------+----------+--------+----------+--------+ | 城市 | 区 | 区销售额 | 市销售额 | 市比率 | 总销售额 | 总比率 | +------+------+----------+----------+--------+----------+--------+ | 上海 | 长宁 | 10 | 40 | 0.2500 | 70 | 0.1429 | | 上海 | 黄埔 | 30 | 40 | 0.7500 | 70 | 0.4286 | | 北京 | 朝阳 | 20 | 30 | 0.6667 | 70 | 0.2857 | | 北京 | 海淀 | 10 | 30 | 0.3333 | 70 | 0.1429 | +------+------+----------+-----------+--------+----------+--------+ 4 rows in set (0.00 sec) - 结果显示，我们得到了与上面那种查询同样的结果
- （1）使用窗口函数，只用了一步就完成了查询。而且，（2）由于没有用到临时表，执行的效率也更高了
- 很显然在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好
- 同样的查询，如果用窗口函数，就简单多了。我们可以用下面的代码来实现
（2）窗口函数分类
- MySQL从8.0版本开始支持窗口函数。窗口函数的作用类似于在查询中对数据进行分组。不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中
- 窗口函数可以分为静态窗口函数和动态窗口函数
- 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同
- 动态窗口函数的窗口大小会随着记录的不同而变化
- MySQL官方网站窗口函数的网址为：MySQL :: MySQL 8.0 Reference Manual :: 14.20.1 Window Function Descriptions
- 窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其它函数，如下表：
（3）语法结构
- 窗口函数的语法结构是：
函数 OVER（[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]） 或者 函数 OVER 窗口名 … WINDOW 窗口名 AS （[PARTITION BY 字段名 ORDER BY 字段名 ASC|DESC]） - OVER关键字指定窗口函数的范围：
- 如果省略后面括号中的内容，则窗口会包含满足WHERE条件的所有记录
- 如果OVER关键字后面的括号不为空，则可以使用上述语法设置窗口
- 窗口名：用来标识窗口
- PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组中分别执行
- ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号
- FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用
（4）分类讲解
准备工作：
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');下面针对goods表中的数据来验证每个窗口函数的功能
2.4.1序号函数
- ROW_NUMBER()函数：查询goods数据表中（1）每个商品分类下（2）价格降序排列的各个商品信息
mysql> select row_number() over (partition by category_id order by price desc) as row_num, -> id, category_id, category, NAME, price, stock -> from goods; +---------+----+-------------+---------------------+-----------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+-----------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 5 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------------+-----------------+---------+-------+ 12 rows in set (0.00 sec) - ROW_NUMBER()函数：查询goods数据表中（1）每个商品分类下（2）价格最高的3种商品信息
mysql> 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; +---------+----+-------------+---------------------+-----------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+-----------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 3 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | +---------+----+-------------+---------------------+-----------------+---------+-------+ 6 rows in set (0.00 sec)SELECT ROW_NUMBER() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num的执行步骤： （1）将表按照category_id列的值进行分组，每个不同的category_id值形成一个独立的分区（2）对每个分区内的记录按照price列进行降序排序 （3）ROW_NUMBER函数会为每个分区内的行依次分配一个唯一的整数行号
- RANK()函数：使用RANK()函数获取 goods 数据表中（1）各类别的（2）价格从高到低排序的各商品信息
mysql> select rank() over (partition by category_id order by price desc) as row_num, -> id, category_id, category, NAME, price, stock -> from goods; +---------+----+-------------+---------------------+-----------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+-----------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 6 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------------+-----------------+---------+-------+ 12 rows in set (0.00 sec)可以发现，使用RANK()函数得出的序号为1、2、2、4，相同价格的商品序号相同，后面的商品序号是不连续的，跳过了重复的序号
- RANK()函数：使用RANK()函数获取goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息
mysql> SELECT * -> FROM( -> SELECT RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods) t -> WHERE category_id = 1 AND row_num <= 4; +---------+----+-------------+---------------------+--------------+--------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+--------------+--------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+-------------+---------------------+--------------+--------+-------+ 4 rows in set (0.00 sec) - DENSE_RANK()函数：使用DENSE_RANK()函数获取goods数据表中（1）各类别的（2）价格从高到低排序的各商品信息
mysql> select dense_rank() over (partition by category_id order by price desc) as row_num, -> id, category_id, category, NAME, price, stock -> from goods; +---------+----+-------------+---------------------+-----------------+---------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+-----------------+---------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 4 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 5 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | | 1 | 8 | 2 | 户外运动 | 山地自行车 | 1399.90 | 2500 | | 2 | 11 | 2 | 户外运动 | 运动外套 | 799.90 | 500 | | 3 | 12 | 2 | 户外运动 | 滑板 | 499.90 | 1200 | | 4 | 7 | 2 | 户外运动 | 自行车 | 399.90 | 1000 | | 4 | 10 | 2 | 户外运动 | 骑行装备 | 399.90 | 3500 | | 5 | 9 | 2 | 户外运动 | 登山杖 | 59.90 | 1500 | +---------+----+-------------+---------------------+-----------------+---------+-------+ 12 rows in set (0.00 sec)可以看到，使用DENSE_RANK()函数得出的行号为1、2、2、3，相同价格的商品序号相同，后面的商品序号是连续的，并且没有跳过重复的序号
- DENSE_RANK()函数：使用DENSE_RANK()函数获取goods数据表中类别为“女装/女士精品”的价格最高的4款商品信息
mysql> SELECT * -> FROM( -> SELECT DENSE_RANK() OVER(PARTITION BY category_id ORDER BY price DESC) AS row_num, -> id, category_id, category, NAME, price, stock -> FROM goods) t -> WHERE category_id = 1 AND row_num <= 3; +---------+----+-------------+---------------------+--------------+--------+-------+ | row_num | id | category_id | category | NAME | price | stock | +---------+----+-------------+---------------------+--------------+--------+-------+ | 1 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 3 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | +---------+----+-------------+---------------------+--------------+--------+-------+ 4 rows in set (0.00 sec)
2.4.2分布函数
- PERCENT_RANK()函数：
- 计算过程示例：
- 计算 goods 数据表中名称为“女装/女士精品”的类别下的商品的PERCENT_RANK值
#写法一 mysql> 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; +---+-----+----+-------------+---------------------+--------------+--------+-------+ | r | pr | id | category_id | category | NAME | price | stock | +---+-----+----+-------------+---------------------+--------------+--------+-------+ | 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | +---+-----+----+-------------+---------------------+--------------+--------+-------+ 6 rows in set (0.00 sec)#写法二 mysql> select rank() over w as r, -> percent_rank() over w as pr, -> id, category_id, category, NAME, price, stock -> from goods -> where category_id = 1 window w as (partition by category_id order by price desc); +---+-----+----+-------------+---------------------+--------------+--------+-------+ | r | pr | id | category_id | category | NAME | price | stock | +---+-----+----+-------------+---------------------+--------------+--------+-------+ | 1 | 0 | 6 | 1 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | | 2 | 0.2 | 3 | 1 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | | 2 | 0.2 | 4 | 1 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | | 4 | 0.6 | 2 | 1 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | | 5 | 0.8 | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | | 6 | 1 | 5 | 1 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | +---+-----+----+-------------+---------------------+--------------+--------+-------+ 6 rows in set (0.00 sec) - 详细执行步骤分析：（1）首先执行FROM和WHERE子句，对读取的数据进行筛选（2）将筛选出的结果，按照category_id的不同值划分为多个独立的分区（3）在每个分区内按照价格降序排序（4）rank函数执行（5）percent_rank函数：数据库会为分区内的每一行计算百分比排名（6）最后，数据库会将筛选、分区、排序和计算后的结果按照SELECT子句指定的列进行投影
- 计算过程示例：
- CUME_DIST()函数：
- CUME_DIST()函数计算过程示例：
- 查询goods数据表中小于或等于当前价格的比例
mysql> select cume_dist() over (partition by category_id order by price desc) as cd, -> id, category, NAME, price -> from goods; +---------------------+----+---------------------+-----------------+---------+ | cd | id | category | NAME | price | +---------------------+----+---------------------+-----------------+---------+ | 0.16666666666666666 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 0.5 | 3 | 女装/女士精品 | 卫衣 | 89.90 | | 0.5 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 0.6666666666666666 | 2 | 女装/女士精品 | 连衣裙 | 79.90 | | 0.8333333333333334 | 1 | 女装/女士精品 | T恤 | 39.90 | | 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 | | 0.16666666666666666 | 8 | 户外运动 | 山地自行车 | 1399.90 | | 0.3333333333333333 | 11 | 户外运动 | 运动外套 | 799.90 | | 0.5 | 12 | 户外运动 | 滑板 | 499.90 | | 0.8333333333333334 | 7 | 户外运动 | 自行车 | 399.90 | | 0.8333333333333334 | 10 | 户外运动 | 骑行装备 | 399.90 | | 1 | 9 | 户外运动 | 登山杖 | 59.90 | +---------------------+----+---------------------+-----------------+---------+ 12 rows in set (0.00 sec)
- CUME_DIST()函数计算过程示例：
2.4.3前后函数
- LAG(expr,n)函数：
- LAG(expr,n)函数返回当前行的前n行的expr的值
- 查询goods数据表中前一个商品价格与当前商品价格的差值
mysql> select id,category,name,price,pre_price,price-pre_price as diff_price -> from ( -> select id, category, NAME, price,lag(price,1) over w as pre_price -> from goods -> window w as (partition by category_id order by price )) t; +----+---------------------+-----------------+---------+-----------+------------+ | id | category | name | price | pre_price | diff_price | +----+---------------------+-----------------+---------+-----------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL | | 1 | 女装/女士精品 | T恤 | 39.90 | 29.90 | 10.00 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 40.00 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 79.90 | 10.00 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 89.90 | 0.00 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 89.90 | 310.00 | | 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL | | 7 | 户外运动 | 自行车 | 399.90 | 59.90 | 340.00 | | 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 0.00 | | 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 100.00 | | 11 | 户外运动 | 运动外套 | 799.90 | 499.90 | 300.00 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 799.90 | 600.00 | +----+---------------------+-----------------+---------+-----------+------------+ 12 rows in set (0.00 sec) - 详细执行步骤分析： 子查询部分执行流程： （1）FROM （2）按照category_id的不同值划分为多个独立的分区 （3）在每个分区内按照价格升序排序 （4）lag函数：在每个分区内，从第二行开始，会取前一行的price值作为pre_price； 对于每个分区的第一行，由于没有前一行，pre_price会返回NULL （5）子查询会选取id, category, NAME, price列，同时计算得到pre_price列，将这些 列组合成一个临时结果集。这个临时结果集将作为外层查询的输入 外查询部分执行流程： （1）外层查询从子查询生成的临时结果集中获取数据 （2）对于临时结果集中的每一行，计算price - pre_price的值，并将其命名为 diff_price （3）最终外层查询将这些列作为最终的查询结果集返回
- LEAD(expr,n)函数：
- LEAD(expr,n)函数返回当前行的后n行的expr的值
- 查询goods数据表中后一个商品价格与当前商品价格的差值
mysql> select id,category,name,behind_price,price,behind_price-price as diff_price -> from( -> select id,category,name,price,lead(price,1) over w as behind_price -> from goods -> window w as (partition by category_id order by price)) t; +----+---------------------+-----------------+--------------+---------+------------+ | id | category | name | behind_price | price | diff_price | +----+---------------------+-----------------+--------------+---------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 39.90 | 29.90 | 10.00 | | 1 | 女装/女士精品 | T恤 | 79.90 | 39.90 | 40.00 | | 2 | 女装/女士精品 | 连衣裙 | 89.90 | 79.90 | 10.00 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 89.90 | 0.00 | | 4 | 女装/女士精品 | 牛仔裤 | 399.90 | 89.90 | 310.00 | | 6 | 女装/女士精品 | 呢绒外套 | NULL | 399.90 | NULL | | 9 | 户外运动 | 登山杖 | 399.90 | 59.90 | 340.00 | | 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 0.00 | | 10 | 户外运动 | 骑行装备 | 499.90 | 399.90 | 100.00 | | 12 | 户外运动 | 滑板 | 799.90 | 499.90 | 300.00 | | 11 | 户外运动 | 运动外套 | 1399.90 | 799.90 | 600.00 | | 8 | 户外运动 | 山地自行车 | NULL | 1399.90 | NULL | +----+---------------------+-----------------+--------------+---------+------------+ 12 rows in set (0.00 sec)
2.4.4首尾函数
- FIRST_VALUE(expr)函数：
- FIRST_VALUE(expr)函数返回第一个expr的值
- 按照价格排序，查询第1个商品的价格信息
mysql> 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); +----+---------------------+-----------------+---------+-------+-------------+ | id | category | name | price | stock | first_price | +----+---------------------+-----------------+---------+-------+-------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 | | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 29.90 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 29.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 29.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 29.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 29.90 | | 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 | | 7 | 户外运动 | 自行车 | 399.90 | 1000 | 59.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 59.90 | | 12 | 户外运动 | 滑板 | 499.90 | 1200 | 59.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 500 | 59.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 59.90 | +----+---------------------+-----------------+---------+-------+-------------+ 12 rows in set (0.00 sec) - 详细执行步骤分析：（1）FROM（2）按照category_id的不同值划分为多个独立的分区（3）在每个分区内按照价格升排序（4）FIRST_VALUE函数：每个分区中，无论当前处理的是哪一行列，first_price列都会显示该分区内价格最低的商品的价格。数据库会逐行处理结果集，为每一行计算first_price的值
- LAST_VALUE(expr)函数：
- LAST_VALUE(expr)函数返回最后一个expr的值
- 按照价格排序，查询最后一个商品的价格信息（出现下面问题的原因是：默认的窗口框架，对于当前正在处理的行，窗口的起始位置是分区内的第一行，结束位置是当前行）
mysql> select id, category, NAME, price, stock, -> last_value(price) over w as last_price -> from goods -> window w as (partition by category_id order by price); +----+---------------------+-----------------+---------+-------+------------+ | id | category | NAME | price | stock | last_price | +----+---------------------+-----------------+---------+-------+------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | 500 | 29.90 | | 1 | 女装/女士精品 | T恤 | 39.90 | 1000 | 39.90 | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 2500 | 79.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 1500 | 89.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 3500 | 89.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 1200 | 399.90 | | 9 | 户外运动 | 登山杖 | 59.90 | 1500 | 59.90 | | 7 | 户外运动 | 自行车 | 399.90 | 1000 | 399.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 3500 | 399.90 | | 12 | 户外运动 | 滑板 | 499.90 | 1200 | 499.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 500 | 799.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 2500 | 1399.90 | +----+---------------------+-----------------+---------+-------+------------+ 12 rows in set (0.00 sec)
2.4.5其他函数
- NTH_VALUE(expr,n)函数：
- NTH_VALUE(expr,n)函数返回第n个expr的值
- 查询goods数据表中排名第2和第3的价格信息
mysql> select id,category,name,price, -> nth_value(price,2) over w as second_price, -> nth_value(price,3) over w as third_price -> from goods -> window w as (partition by category_id order by price); +----+---------------------+-----------------+---------+--------------+-------------+ | id | category | name | price | second_price | third_price | +----+---------------------+-----------------+---------+--------------+-------------+ | 5 | 女装/女士精品 | 百褶裙 | 29.90 | NULL | NULL | | 1 | 女装/女士精品 | T恤 | 39.90 | 39.90 | NULL | | 2 | 女装/女士精品 | 连衣裙 | 79.90 | 39.90 | 79.90 | | 3 | 女装/女士精品 | 卫衣 | 89.90 | 39.90 | 79.90 | | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | 39.90 | 79.90 | | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | 39.90 | 79.90 | | 9 | 户外运动 | 登山杖 | 59.90 | NULL | NULL | | 7 | 户外运动 | 自行车 | 399.90 | 399.90 | 399.90 | | 10 | 户外运动 | 骑行装备 | 399.90 | 399.90 | 399.90 | | 12 | 户外运动 | 滑板 | 499.90 | 399.90 | 399.90 | | 11 | 户外运动 | 运动外套 | 799.90 | 399.90 | 399.90 | | 8 | 户外运动 | 山地自行车 | 1399.90 | 399.90 | 399.90 | +----+---------------------+-----------------+---------+--------------+-------------+ 12 rows in set (0.00 sec)
- NTILE(n)函数：
- NTILE(n)函数的主要作用是在每个分区内，按照指定的排序规则，分成n个大致相同的组，并为每一行分配一个组号，组号范围从1到n
- 将goods表中的商品按照价格分为3组
mysql> select ntile(3) over w as nt,id, category, NAME, price -> from goods -> window w as (partition by category_id order by price); +----+----+---------------------+-----------------+---------+ | nt | id | category | NAME | price | +----+----+---------------------+-----------------+---------+ | 1 | 5 | 女装/女士精品 | 百褶裙 | 29.90 | | 1 | 1 | 女装/女士精品 | T恤 | 39.90 | | 2 | 2 | 女装/女士精品 | 连衣裙 | 79.90 | | 2 | 3 | 女装/女士精品 | 卫衣 | 89.90 | | 3 | 4 | 女装/女士精品 | 牛仔裤 | 89.90 | | 3 | 6 | 女装/女士精品 | 呢绒外套 | 399.90 | | 1 | 9 | 户外运动 | 登山杖 | 59.90 | | 1 | 7 | 户外运动 | 自行车 | 399.90 | | 2 | 10 | 户外运动 | 骑行装备 | 399.90 | | 2 | 12 | 户外运动 | 滑板 | 499.90 | | 3 | 11 | 户外运动 | 运动外套 | 799.90 | | 3 | 8 | 户外运动 | 山地自行车 | 1399.90 | +----+----+---------------------+-----------------+---------+ 12 rows in set (0.02 sec)
（5）小结
窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原表数据的基础上进行统计和排序非常有用
三、新特性2：公用表表达式
公用表表达式（或通用表表达式）简称为CTE（Common Table Expressions）
CTE可以理解成一个可以复用的子查询，当然跟子查询还是有点区别的，CTE可以引用其它CTE，但子查询不能引用其它子查询。所以，可以考虑用CTE代替子查询
依据语法结构和执行方式的不同，公用表表达式可以分为普通公用表表达式和递归公用表表达式2种
（1）普通公用表表达式
- 普通公用表表达式的语法结构是：
WITH CTE名称 AS （子查询） SELECT|DELETE|UPDATE 语句; - 普通公用表表达式类似于子查询，不过，跟子查询不同的是，它可以被多次引用，而且可以被其他的普通公用表表达式所引用
- 举例：查询员工所在的部门的详细信息（用子查询实现）
mysql> select * from departments -> where department_id in ( -> select distinct department_id -> from employees -> ); +---------------+------------------+------------+-------------+ | department_id | department_name | manager_id | location_id | +---------------+------------------+------------+-------------+ | 10 | Administration | 200 | 1700 | | 20 | Marketing | 201 | 1800 | | 30 | Purchasing | 114 | 1700 | | 40 | Human Resources | 203 | 2400 | | 50 | Shipping | 121 | 1500 | | 60 | IT | 103 | 1400 | | 70 | Public Relations | 204 | 2700 | | 80 | Sales | 145 | 2500 | | 90 | Executive | 100 | 1700 | | 100 | Finance | 108 | 1700 | | 110 | Accounting | 205 | 1700 | +---------------+------------------+------------+-------------+ 11 rows in set (0.02 sec) - 举例：查询员工所在的部门的详细信息（用普通公用表表达式实现）
mysql> with emp_dept_id -> as (select distinct department_id from employees) -> select * -> from departments d join emp_dept_id -> on d.department_id = emp_dept_id.department_id; +---------------+------------------+------------+-------------+---------------+ | department_id | department_name | manager_id | location_id | department_id | +---------------+------------------+------------+-------------+---------------+ | 10 | Administration | 200 | 1700 | 10 | | 20 | Marketing | 201 | 1800 | 20 | | 30 | Purchasing | 114 | 1700 | 30 | | 40 | Human Resources | 203 | 2400 | 40 | | 50 | Shipping | 121 | 1500 | 50 | | 60 | IT | 103 | 1400 | 60 | | 70 | Public Relations | 204 | 2700 | 70 | | 80 | Sales | 145 | 2500 | 80 | | 90 | Executive | 100 | 1700 | 90 | | 100 | Finance | 108 | 1700 | 100 | | 110 | Accounting | 205 | 1700 | 110 | +---------------+------------------+------------+-------------+---------------+ 11 rows in set (0.09 sec)例子说明，公用表表达式可以起到子查询的作用。以后如果遇到需要使用子查询的场景，你可以在查询之前，先定义公用表表达式，然后在查询中用它来代替子查询。而且，跟子查询相比，公用表表达式有一个优点，就是定义过公用表表达式之后的查询，可以像一个表一样多次引用公用表表达式，而子查询则不能
（2）什么是递归公用表表达式？
- 递归公用表表达式也是一种公用表表达式。只不过，除了普通公用表表达式的特点以外，它还有自己的特点，就是可以自己调用自己，它的语法结构是：
WITH RECURSIVE CTE名称 AS （子查询） SELECT|DELETE|UPDATE 语句; - 案例：针对于我们常用的employees表，包含employee_id，last_name和manager_id三个字段。如果a是b的管理者，那么，我们可以把b叫做a的下属，如果同时b又是c的管理者，那么c就是b的下属，是a的下下属
- 我们尝试用查询语句列出所有具有下下属身份的人员信息。如果用我们之前学过的知识来解决，会比较复杂，至少要进行4次查询才能搞定：
- 第一步，先找出初代管理者，就是不以任何别人为管理者的人，把结果存入临时表
- 第二步，找出所有以初代管理者为管理者的人，得到一个下属集，把结果存入临时表
- 第三步，找出所有以下属为管理者的人，得到一个下下属集，把结果存入临时表
- 第四步，找出所有以下下属为管理者的人，得到一个结果集
- 如果第四步的结果集为空，则计算结束，第三步的结果集就是我们需要的下下属集了，否则就必须继续进行第四步，一直到结果集为空为止。比如上面的这个数据表，就需要到第五步，才能得到空结果集。而且，最后还要进行第六步：把第三步和第四步的结果集合并，这样才能最终获得我们需要的结果集
- 如果用递归公用表表达式，就非常简单了：
- 用递归公用表表达式中的种子查询，找出初代管理者。字段 n 表示代次，初始值为 1，表示是第一代管理者
- 用递归公用表表达式中的递归查询，查出以这个递归公用表表达式中的人为管理者的人，并且代次的值加 1。直到没有人以这个递归公用表表达式中的人为管理者了，递归返回
- 在最后的查询中，选出所有代次大于等于 3 的人，他们肯定是第三代及以上代次的下属了，也就是下下属了。这样就得到了我们需要的结果集
- 代码：
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; - 迭代步骤：
- 种子查询执行：
- 第一次递归查询：
- 后续递归迭代：
- 种子查询执行：
（3）小结
公用表表达式的作用是可以替代子查询，而且可以被多次引用。递归公用表表达式对查询有一个共同根节点的树形结构数据非常高效，可以轻松搞定其他查询方式难以处理的查询
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
