一、账户与安全

1.用户的创建与授权

　　在MySQL5.7的版本：

　　> grant all privileges on *.* to '用户名'@'主机' identified by '密码'；

　　在MySQL8.0需要分开执行：

　　>create user '用户名'@'主机' identified by '密码'；

　　>grant all privileges on *.* to '用户名'@'主机'；

　　用以前的一条命令在8.0里面创建用户，会出现sql语法错误
 

2.认证插件更新

　　MySQL5.7默认身份插件是mysql_native_password

　　MySQL8.0默认的身份插件是caching_sha2_password
　　查看身份认证插件命令：show variables like 'default_authentication_plugin%';或select user,host,plugin from mysql.user;

　　身份认证插件可以通过以下2中方式改变：　　　　

       1）系统变量default_authentication_plugin去改变，在my.ini文件的[mysqld]下面设置default_authentication_plugin=mysql_native_password即可
　　2）如果希望只是某一个用户通过mysql_native_password的方式认证，可以修改数据库mysql下面的user表的字段，

             执行以下命令：　alter user '用户名'@'主机' identified width mysql_native_password by '密码';

3.密码管理　　

MySQL8.0的密码管理策略有3个变量　　

        password_history 修改密码不允许与最近几次使用或的密码重复，默认是0，即不限制　　　　

        password_reuse_interval 修改密码不允许与最近多少天的使用过的密码重复，默认是0,即不限制
　　password_require_current 修改密码是否需要提供当前的登录密码，默认是OFF,即不需要；如果需要，则设置成ON　　

查询当前MySQL密码管理策略相关变量，使用以下命令：　　　　

>show variables like 'password%';　　

1)设置全局的密码管理策略，在my.ini配置文件中，设置以上3个变量的值这种设置方式，需要重启mysql服务器；某些生产环境不允许重启，MySQL8.0提供了关键字persist 持久化，

执行以下命令：　　

>set persist password_history=6;　　

这条命令会在数据目录下生成新的配置文件（/var/lib/mysql/mysqld-auto.cnf），下次服务器重启的时候除了读取全局配置文件，还会读取这个配置文件,这条配置就会被读入从而达到持久化的目的
 

2)针对某一个用户单独设置密码管理策略　　

>alter user '用户名'@'主机' password history 5;　　这样，这个用户的password_history 就被设置成了5;

查看一下：　　

>show user,host,Password_reuse_history from user;　

4.角色管理

角色：一组权限的集合 一组权限赋予某个角色，再把某个角色赋予某个用户，那用户就拥有角色对应的权限

1)创建一个角色

>create role '角色1'; (角色也是在用户表中mysql.user)

2)为这个角色赋予相应权限

>grant insert,update on *.* to '角色1';

3)创建一个用户

>create user '用户1' identified by '用户1的密码';

4)为这个用户赋予角色的权限

>grant '角色1' on *.* to '用户1'；

执行完上面4步，用户1就拥有了插入与更新的权限

5)再创建1个用户

>create user '用户2' identified by '用户2的密码';

6)为这个用户赋予同样的角色

>grant '角色1' on *.* to '用户2'; 执行完上面2步，

用户2也用了角色1的权限，即插入与更新 查看用户权限，执行以下命令：

>show grants for '用户名' [using '角色名'];

7)启用角色,设置了角色，如果不启用，用户登录的时候，依旧没有该角色的权限

> select current_role();查看启用的角色

> set role '角色名'；启用角色

>set default role '角色名' to '用户名';设置默认角色

8)如果一个用户有多个角色，使用以下命令

>set default role all to '用户名';

MySQL中与用户角色相关的表：

mysql.default_roles、mysql.role_edges

9)撤销权限

>revoke insert,update on *.* from '角色名';　　

 

二、优化器索引

1.隐藏索引（invisible index）

　　隐藏索引不会被优化器使用，但仍需要维护

　　应用场景：

　　1）软删除

　　　　删除索引，在线上，如果删除错了索引，只能通过创建索引的方式将其添加回来，对于一些大的数据库而言，是比较耗性能的；为了避免删错，可以先将其设置为不可见，优化器这时候就不会使用它，但是后台仍然在维护，确定后，再删除。

　　2）灰度发布

　　　　与软删除差不多，如果想要测试一些索引的功能或者随后可能会使用到这个索引，可以先将其设置为隐藏索引，对于现有的查询不会产生影响，测试后，确定需要该索引，可以将其设置成可见索引。

　　创建隐藏索引，执行如下命令（如果是不隐藏，则不需要后面的invisible关键字）：

　　>create index 索引名称 on 表名(字段名) invisible;

　　查询某一张表的索引，执行如下命令：

　　>show index from 表名；

　　使用explain语句查看查询优化器对索引的使用情况

　　>explain select * from 表名 where 条件;

　　查询优化器有很多开关，有一个是use_invisible_indexes(是否使用隐藏索引),默认是off(不适用)，将其设置成on,即可使用隐藏索引。查看当前查询优化器的所有开关，执行如下命令：

　　>select @@optimizer_switch;

       打开开关

       >set session optimizer_switch="use_invisible_indexes=on";

　　设置已经存在的索引为可见或者隐藏，执行如下命令：

　　>alter table 表名 alter index 索引名 visible;

　　>alter table 表名 alter index 索引名 invisible;
　　主键不可以设置为隐藏所以。

2.降序索引（descending index）

　　MySQL8.0开始真正支持降序索引，只有InnoDB引擎支持降序索引，且必须是BTREE降序索引，MySQL8.0不在对group by操作进行隐式排序。

mysql5.7

> create table t1(c1 int, c2 int, index idx(c1 asc, c2 desc));

> show create table t1;

> insert into t1(c1,c2) values(1,100),(2,300),(3,110),(4,30);

> explain select * from t1 order by c1,c2 desc\G;

mysql8

> create table t1(c1 int, c2 int, index idx(c1 asc, c2 desc));

> show create table t1;

> insert into t1(c1,c2) values(1,100),(2,300),(3,110),(4,30);

> explain select * from t1 order by c1,c2 desc\G;

> explain select * from t1 order by c1 desc,c2 \G;

3.函数索引

　　索引中使用函数表达式
　　
　　支持降序索引，支持JSON数据节点的索引

　　函数索引是基于虚拟列的功能实现的

假设用户表（tb_user）的用户登录账号(username)不需要区分大小写，则可以创建一个函数索引

>create index username_upper_index on tb_user((upper(username)));
这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。

上面的函数索引，也可以通过MySQL5.7已有的虚拟计算列来模拟，为用户表（tb_user）创建新的一列（new_column）,这一列是计算列，不需要赋值，它的值就是username的大写。

>alter tbale tb_user add column new_column varchar(10) generated always as (upper(username));

然后给new_column创建一个索引，可以达到模拟MySQL8.0中的函数索引的效果。这样在查询的时候 SELECT * FROM tb_user WHERE upper(username) = 'ABD123DSJ'; 就会使用索引。

 

json数据节点的索引

 ->> （内联路径）运算符，该运算符等效于调用 JSON_UNQUOTE()的结果JSON_EXTRACT()。

> create table emp(data json, index( (CAST(data->>'$.name' as char(30)) )));

> explain select * from emp where CAST(data->>'$.name' as char(30)) = 'abc'\G;

 

三、通用表表达式

1、非递归CTE

> with cte1(id) as (select 1),

cte2(id) as (select id+1 from cte1)

select * from cte1 join cte2;

 

> With cte1(country_id,country_name) as (select id,name from sch_city where level = 0),
 cte2(province_id,province_name) as (select id,name from sch_city where level = 1),
cte3(city_id,city_name) as (select id,name from sch_city where level = 2)
Select a.country_id, country_name,a.province_id, province_name,a.city_id,city_name,a.address 
from sch_address a 
Left join cte1 on a.country_id = cte1.country_id
Left join cte2 on a.province_id = cte2.province_id
Left join cte3 on a.city_id = cte3.city_id;

2、递归CTE

With recursive cte(n) as 
(
Select 1
Union all
Select n+1 from cte where n < 5
) 
Select * from cte;

> Create table employees(id int, name varchar(32), manager_id int);

> Insert into employees values(29,'Pedro',198),(72,'Pierre',29),(123,'Adil',692),(198,'John',333), (692,'Tarek',333),(4610,'Sarah',29);

> Insert into employees(id,name) values(333,'Yasmina');

With recursive employee_paths(id,name,path) as
(
Select id,name,cast(id as char(200))
From employees
Where manager_id is null
Union all
Select e.id,e.name,concat(ep.path,',',e.id)
From employee_paths as ep join employees as e
On ep.id = e.manager_id
)
Select * from employee_paths;

 

With recursive sch_city_paths(id, name, path) as
(
Select id,name,cast(id as char(200))
From sch_city
Where level = 0
Union all
Select c.id,c.name,concat(cp.path,',',c.id)
From sch_city_paths as cp join sch_city as c
On cp.id = c.pid
)
Select * from sch_city_paths;

3、递归限制

递归表达式的查询中需要包含一个终止递归的条件

cte_max_recursive_depath 最大递归层数
max_execution_time

4、CTE小结

通用表表达式与派生表类似，就像语句级别的临时表或视图。

CTE可以在查询中多次引用，可以引用其他CTE，可以递归

CTE支持SELECT/INSERT/UPDATE/DELETE等语句

 

 

 

 

 

 

 

四、窗口函数

1、基本概念
mysql8支持窗口函数（window function），也称分析函数
窗口函数与分组聚合函数类似，但是每一行数据都生成一个结果
聚合窗口函数：sum/avg/count/max/min等等

create table sales (year year, country varchar(32),product varchar(32),profit int);
Insert into sales values
('2000','Finland','Computer',1500),
('2001','USA','Computer',1200),
('2001','Finland','Phone',10),
('2000','India','Calculator',75),
('2001','USA','TV',150),
('2000','India','Computer',1200),
('2000','USA','Calculator',5),
('2000','USA','Computer',1500),
('2000','Finland','Phone',100),
('2001','USA','Calculator',50),
('2001','USA','Computer',1500),
('2000','India','Calculator',75),
('2001','USA','TV',100);

传统聚合函数

> select country ,sum(profit) from sales group by country ;

窗口分析函数

> select year, country product, profit,
sum(profit) over (partition by country) as country_profit
From sales;

> select year, country product, profit,
avg(profit) over (partition by country) as avg_profit
From sales;

2、专用聚合函数

2.1序号函数：row_number()，rank()，dense_rank()

序号函数的作用是显示分区中每行的行号。三者的区别在于，当排序字段相同时，三个函数的处理结果不同：

row_number()：排序字段相同时，行号随机排，下一行行号正常加1。显然此函数得到的行号是连续的。

rank()：排序字段相同时，行号相同，下一行行号按照排名确定。这个逻辑比较像是现实中的排名规则，当出现并列冠军时，第三个人排名第三，没有亚军。显然此函数得到的行号是不连续的。

dense_rank()：排序字段相同时，行号相同，下一行行号顺序加1。也就是出现冠军时，第三个人排名是第二。显然此函数得到的行号是连续的。

2.2、分布函数：percent_rank() / cume_dist()

分布函数得到的是一个百分比，计算公式用到rank()函数

percent_rank()

实际计算公式：(rank()-1)/(rows-1)

其中rank()是rank()函数得到的结果，rows是分区行数。

 

cume_dist()

实际计算公式：rank()/rows

这个函数的含义比较好理解一点，就是小于等于自己排名的行占总行数的比例。
 

2.3、前后函数：lead() / lag()

lead (expr,n)代表当前行前面第n行记录的expr表达式（或字段值）。

lag(expr,n)代表当前行后面的第n行记录的expr表达式（或字段值）。

比如lead (add_time,1)就代表当前行前面一行的add_time字段。这两个函数是基于排序的

 

2.4、头尾函数：first_value() / last_value()

first_value(expr)代表分区第一行记录的expr表达式（或字段值）。

last_value (expr)代表分区最后一行记录的expr表达式（或字段值）。

这两个函数是基于分组的。

 

2.5、其他函数：nth_value() / nfile()

nth_value(expr,n)代表区间第n条记录的expr表达式（或字段值）。

比如：nth_value(amount,2)代表区间第二行的amount字段。

nfile(n)代表把区间列分为n个组，返回组号。

注意：这个分组只能是尽量平均，但有时候不能完全平均，比如分区内有7条数据，用nfile(3)分三组，记录的组号就会是：1,1,1,2,2,3,3。
 

 

create table numbers(val int);
Insert into numbers values(1),(1),(2),(3),(3),(3),(3),(4),(4),(5);

>Select val,row_number() over (partition by val order by val) as 'row_number' from numbers;

>Select val,row_number() over (order by val) as 'row_number' from numbers;

>Select val,dense_rank() over (order by val) as 'row_number'  from numbers;

>Select val,first_value(val) over (order by val) as 'first',lead(val,1) over(order by val) as 'lead'  from numbers;

>Select val,first_value(val) over (order by val) as 'first',lead(val,2) over(order by val) as 'lead'  from numbers;

3、窗口定义

window_function(expr)
Over (
partition by ...
Order by ...
frame_clause ...
)

 

select year,country,product,profit,
sum(profit) over(partition by country order by profit rows unbounded preceding) as running_total
From sales
Order by country, profit;

select year,country,product,profit,
avg(profit) over(partition by country order by profit rows between 1 preceding and 1 following) as running_avg
From sales
Order by country, profit;

select year,country,product,profit,
first_value(profit) over w as 'first',
last_value(profit) over w as 'last'
From sales
Window w as (partition by country order by profit rows unbounded preceding)
Order by country, profit;

 

五、innodb增强

1、集成数据字典

mysql8删除了之前版本的元数据文件，如.frm,.opt等；

将系统表（mysql）和数据字典表全部改为innodb存储引擎

支持原子ddl语句

简化了infomation_schema的实现，提高了访问性能

提供了序列化字典信息（sdi）的支持，以及ibd2sdi工具

数据字典使用上的差异，例如innodb_read_only影响所有的存储引擎；数据字典表不可见，不能直接查询和修改。

2、原子操作ddl

Mysql8开始支持原子ddl操作，其中与表相关的原子ddl只支持innodb存储引擎

一个原子ddl操作内容包括：更新数据字典，存储引擎层的操作，在binlog中记录ddl操作

支持的原子ddl操作

支持与表相关的ddl：数据库、表空间、表、索引的create\alter\drop\以及truncate table

支持的其他的ddl：存储过程、触发器、视图、udf的create、drop以及alter语句

支持的账户管理相关的ddl：用户和角色的create\alter\drop以及适用的rename，以及grant和revoke语句

3、自增列的持久化

mysql5.7以及早期版本，innodb自增列计数器（auto_increment）的值只存储在内存中

mysql8每次变化时将自增计数器的最大值写入redo log,同时在每次检查点将其写入引擎私有的系统表

解决了长期以来的自增字段可能重复的bug

show variables like 'innodb_autoinc%';

4、死锁检查控制

mysql8增加了一个新的动态变量，用于控制系统是否执行innodb死锁检查

innodb_deadlock_detect

对于高并发的系统，禁用死锁检查可能带来性能的提高

5、锁定语句选项

select ... for share 和 select ... for update 中支持nowait、skip locked选项；select ... for update [nowait | skip locked]

对于nowait，如果请求的行被其他事务锁定时，语句立即返回。
对于skip locked，从返回的结果集中移除被锁定的行。

6、其他功能改进

支持部分快速ddl,alter table ... algorithm=instant;

Innodb 临时表使用共享的临时表空间ibtmp1

新增静态变量innodb_dedicated_server,自动配置innodb内存参数：innodb_buffer_pool_size/innodb_log_file_size等。

新增表INFORMATION_SCHEMA.INNODB_CACHED_INDEXES,显示没个索引缓存在innodb缓存池中的索引页数

新增视图INFORMATION_SCHEMA.INNODB_TABLESPACES_BRIEF,为innodb表空间提供相关元数据信息。

默认创建2个undo表空间，不再使用系统表空间

支持alter tablespace ... rename to 重命名通用表空间

支持使用innodb_directories选项在服务器停止时将表空间文件移动到新的位置

innodb表空间加密特性支持重做日志和撤销日志

 

六、json增强

1、内联路径操作符

主要用于获取json对象在某些节点或某些路径上的数据值

mysql8增加了json操作符column->>path，等价于 
json_unquote(column->path)
json_unquote(json_extract(column, path))

mysql5.7

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select json_unquote(data->'$.name') from doc;

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select json_unquote(json_extract(data,'$.name')) from doc;

Select json_extract('["a","b","c"]', '$[1]');

 

mysql8

With doc(data) as 
(Select json_object('id','3','name','Barney'))
Select data->>'$.name' from doc;

支持范围查找

Select json_extract('["a","b","c","d","3"]', '$[1 to 3]');

Select json_extract('["a","b","c","d","3"]', '$[last-2 to last]');

 

2、json聚合函数

Create table t(o_id int, attribute varchar(32),value varchar(32));
Insert into t values
(2,'color','red'),
(2,'fabric','silk'),
(3,'color','green'),
(3,'shape','square');

 

mysql8（mysql5.7.22）增加了2个聚合函数：

json_arrayagg(),用于生成json数组

Select o_id,json_arrayagg(attribute) as attributes from t group by o_id;

json_objectagg(),用于生成json对象

Select o_id,json_objectagg(attribute,value) as attributes from t group by o_id;

insert into t values (3,'color','yellow');

Select o_id,json_objectagg(attribute,value) as attributes from t group by o_id;//相同属性值，后边的覆盖前边的值

 

3、json实用函数

mysql8（mysql5.7.22）增加了json_pretty()

Select json_pretty(json_object("id","3",'name','Barney'));

mysql8（mysql5.7.22）增加了json_storage_size()

mysql8增加了json_storage_free()

 

4、json合并函数

mysql8（mysql5.7.22）增加了json_merge_patch()

Select json_merge_patch('{"a":1,"b":2}', '{"a":3,"c":4}');//相同节点，后边的覆盖前边的

mysql8（mysql5.7.22）增加了json_merge_preserve()

Select json_merge_preserve('{"a":1,"b":2}', '{"a":3,"c":4}');//相同节点都保留

msyql8废弃了json_merge()

 

5、json表函数

mysql8增加了json_table(),将json数据转化为关系表，可以将该函数返回结果当作一个普通的表，使用sql进行查询

Select * from 
json_table(
'[{"a":3}, {"a":2}, {"b":1}, {"a":0}, {"a":[1,2]}]',
"$[*]" columns(
Rowed for ordinality,
ac varchar(100) path "$.a" default '999' on error default '111' on empty,
aj json path "$.a" default '{"x":333}' on empty,
bx int exists path "$.b"
)
) as tt;

 

 

 

 

 

 

 

 

 

MySQL8开始支持窗口函数。在之前的版本中已存在的大部分集合函数在MySQL8中也可以作为窗口函数使用。

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);
​

查询：

mysql> select * from sales;
+----+------+--------+-------------+
| id | city | county | sales_value |
+----+------+--------+-------------+
|  1 | 北京 | 海淀   |          10 | 
|  2 | 北京 | 朝阳   |          20 |
|  3 | 上海 | 黄埔   |          30 |
|  4 | 上海 | 长宁   |          10 |
+----+------+--------+-------------+
4 rows in set (0.03 sec)

现有需求：计算这个网站在每个城市的销售总额、在全国的销售总额，每个区的销售额占所在城市销售额中的比率，以及占总销售额中的比率。

实现方式一：如果我们使用分组和聚合函数来做，需要分几步来做

第一步：创建临时表a，记录全国的销售总额

​mysql> CREATE TEMPORARY TABLE a
    -> SELECT SUM(sales_value) AS sales_value
    -> FROM sales;
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM a;
+-------------+
| sales_value |
+-------------+
|          70 |
+-------------+
1 row in set (0.00 sec)

第二步：创建临时表b，记录每个城市的销售总额

mysql> CREATE TEMPORARY TABLE b
    -> SELECT city,SUM(sales_value) AS sales_value
    -> FROM sales
    -> GROUP BY city;
Query OK, 2 rows affected (0.03 sec)
mysql> SELECT * FROM b;
+------+-------------+
| city | sales_value |
+------+-------------+
| 北京 |          30 |
| 上海 |          40 |
+------+-------------+
2 rows in set (0.00 sec)

第三步：多表连接

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 |
+------+------+----------+----------+--------+----------+--------+

实现方式二：使用窗口函数

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)

我们也可以得到和前面的同样结果。

使用窗口函数，只用了一步完成了查询。而且，由于没有用到临时表，执行效率也就高了。很显然，在这种需要用到分组统计的结果对每一条记录进行计算的场景下，使用窗口函数更好。

2、窗口函数的分类

窗口函数的作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，而窗口函数是将结果置于每一条数据记录中。

窗口函数可以分为静态窗口函数和动态窗口函数

• 静态窗口函数的窗口大小是固定的，不会因为记录的不同而不同

• 动态窗口函数的窗口大小会随着记录的不同而变化。

窗口函数总体上可以分为序号函数、分布函数、前后函数、收尾函数和其他函数，如下图：

3、窗口函数的语法结构

语法结构：

函数 over([partition by 字段名 order by 字段名 ASC|DESC])

或者：

函数 over 窗口名 ... WINDOW 窗口名 AS ([partition by 字段名 order by 字段名 ASC|DESC])

关键字说明

• OVER 指定函数的窗口范围

  • 如果省略括号内容，则窗口会包含满足WHERE条件的所有记录，窗口函数会基于满足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');

4.1序号函数

• row_number()

  需求一：查询goods表中每个商品分类下价格降序排列的各个商品信息

  SELECT 
      *,
      row_number() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods; 

  显示结果：可以看到，当价格相同时，排序不会重复

  

  需求二：查询goods表中每个商品分类下价格最高的3种商品信息

  其实就是将上一个需求做子查询，再用where字句筛选

  SELECT *
  FROM (
      SELECT 
          *,
          row_number() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
      FROM goods
  ) t
  WHERE row_num<=3;

  结果如下：

  

• rank()

  需求：使用rank()函数获取goods表中各类别的价格从高到低排序的各商品信息

  SELECT 
      *,
      rank() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods;

  结果如下：价格相同时，排名会重复，排名会有缺失 1、2、2、4

  

• dense_rank()

  需求：使用dense_rank()函数获取goods表中个类别的价格从高到低排序的各商品信息

  SELECT 
      *,
      dense_rank() over(PARTITION BY category_id ORDER BY price DESC) AS row_num
  FROM goods;

  结果如下：价格相同时，排名会重复，排名不会缺失1、2、2、3

  

4.2分布函数

• PERCENT_RANK()函数

  PERCENT_RANK(函数是等级百分比函数。按照如下方式进行计算。

  (rank-1) / (rows-1)

  其中，rank的值为使用rank()函数产生的序号,，rows的值为当前窗口的总记录数。

  需求：计算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
  FROM goods
  WHERE category_id=1;

  写法二：

  SELECT
      rank() over w AS r,
      percent_rank() over w AS pr,
      id,category_id,category,NAME,price
  FROM goods
  WHERE category_id=1
  window w AS (PARTITION BY category_id ORDER BY price DESC);

  结果如下：

  

• CUME_DIST()函数

  CUME_DIST()函数主要用于查询小于或等于某个值的比例。

  需求：查询goods表中小于或等于当前价格的比例。

  SELECT 
      cume_dist() over(PARTITION BY category_id ORDER BY price ASC) AS cd,
      category_id,category,NAME,price
  FROM goods;

  结果如下：

  

4.3前后函数

• LAG(expr,n)函数

  LAG(expr,n)函数返回当前行的前n行的expr的值，如果当前行前面没有行，则返回NULL

  需求：查询goods数据表中前一个商品价格与当前商品价格的差值。

  SELECT id,category,NAME,price,price_pr,price-price_pr AS 差值
  FROM
  (SELECT
  id,category,NAME,price,LAG(price,1) over(PARTITION BY category_id ORDER BY price) AS price_pr
  FROM goods) t;

  结果如下：

  

• LEAD(expr,n)函数

  LEAD(expr,n)函数返回当前行的后n行的expr的值，如果当前行后面没有行，则返回NULL

  需求：查询goods数据表中后一个商品价格与当前商品价格的差值

  SELECT id,category,NAME,price,price_behind,price-price_behind AS 差值
  FROM
  (SELECT
  id,category,NAME,price,LEAD(price,1) over(PARTITION BY category_id ORDER BY price DESC) AS price_behind
  FROM goods) t;

  结果如下：

  

4.4 首尾函数

• FIRST_VALUE(expr)函数

• LAST_VALUE(expr)函数

这里以FIRST_VALUE(expr)举例

需求：获取goods表中各类别的价格从高到低排序的各商品信息，查询每个类别的第一个商品价格

​SELECT
*,
first_value(price) over(PARTITION BY category_id ORDER BY price DESC) AS price_hight
FROM goods;

结果如下：

4.5其他函数

• NTH_VALUE(expr,n)函数

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

  需求：查询goods表中，价格排名第2和第3的商品的价格信息

  SELECT id,category,NAME,price,
  nth_value(price,2) over(PARTITION BY category_id ORDER BY price) AS second_price,
  nth_value(price,3) over(PARTITION BY category_id ORDER BY price) AS third_price
  FROM goods;;

  结果如下：

  

• NTILE(n)函数

  NTILE(n)函数将分区中的有序数据分为n个桶，记录桶编号。

  需求：将goods表中的商品按照价格分为3组

  SELECT
  NTILE(3) over(PARTITION BY category_id ORDER BY price) AS nt,
  id,category,NAME,price,stock,upper_time
  FROM goods;

  结果如下：

  

4.6小结

窗口函数的特点是可以分组，而且可以在分组内排序。另外，窗口函数不会因为分组而减少原表中的行数，这对我们在原数据表的基础上进行统计和排序非常有用。