MySQL新特性

1.MySQL新特性

#①更简便的NoSQL支持
    #NoSQL泛指非关系型数据库和数据存储。做了优化，以便更灵活的方式实现NoSQL功能，不在依赖模式（schema）
    
#② 更好的索引
    #新增了隐藏索引和降序索引
    #隐藏索引：可以用来测试去掉索引对查询性能的影响
    #在查询混合存在多列索引时，使用降序索引可以提高查询的性能
    
#③ 更完善的JSON支持
    #增加了聚合函数JSON_ARRAYAGG() 和JSON_OBJECTAGG()
    
#④ 安全和账户管理
    #新增了caching_sha2_password授权插件、角色、密码历史记录和FIPS模式支持
    
#⑤ InnoBD的变化
    #InnoDB是MySQL默认的存储引擎
    
#⑥ 窗口函数

#⑦ 公式表表达式

其他......

2. 窗口函数

#使用场景：
 #需要用到分组统计的结果，对每一条记录进行计算，并显示到一张表上的场景，使用窗口函数更好。
 #8.0版本开始支持窗口函数，其作用类似于在查询中对数据进行分组，不同的是，分组操作会把分组的结果聚合成一条记录，
 #而窗口函数是将结果置于每一条数据记录中。
 
#小结：
#窗口函数的特点是可以分组，而且可以在分组内排序。
#窗口函数不会因为分组而减少原表中的行数。
#这对于我们在原表数据的基础上进行统计和排序非常有用。

#① 序号函数 ROW_NUMBER()
SELECT ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    id, `name`, salary, telephone, department_id FROM employee;

#② 序号函数 RANK()
SELECT RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    id, `name`, salary, telephone, department_id FROM employee;

# ③ 序号函数 DENSE_RANK()
SELECT DENSE_RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    id, `name`, salary, telephone, department_id FROM employee;

#④ 分布函数 PERCENT_RANK()
    #PERCENT_RANK()函数是等级值百分比函数，如下方式计算：
    #（rank-1）/（rows-1）
    #rank是使用RANK()函数产生的序号，rows的值为当前窗口的总记录数
    
SELECT RANK() OVER(PARTITION BY department_id ORDER BY salary) AS r
    ,PERCENT_RANK() OVER(PARTITION BY department_id ORDER BY salary) AS p
    ,id, `name`, salary, telephone, department_id FROM employee
    WHERE department_id = '12';

#⑤ 分布函数 CUME_DIST()
SELECT CUME_DIST() OVER(PARTITION BY department_id ORDER BY salary) AS c
    ,id, `name`, salary, telephone, department_id FROM employee;

#⑥ 前后函数 LAG(expr, n)

　　SELECT
　　A.id, A.name, A.telephone, A.department_id, A.salary, A.pre_salary, (A.salary - A.pre_salary) AS diff_salary
　　FROM
　　(
　　SELECT id, `name`, telephone, department_id, salary
　　,LAG(salary, 1) OVER(PARTITION BY department_id ORDER BY salary ASC) AS pre_salary FROM employee
　　) A;

#⑦ 前后函数 LEAD(expr, n)
SELECT 
A.id, A.name, A.telephone, A.department_id, A.salary, A.next_salary, (A.salary - A.next_salary) AS diff_salary
FROM
(
SELECT id, `name`, telephone, department_id, salary
    ,LEAD(salary, 1) OVER(PARTITION BY department_id ORDER BY salary ASC) AS next_salary FROM employee
) A;

#⑧ 首尾函数 FIRST_VALUE(expr)
SELECT id, `name`, telephone, department_id, salary
    ,FIRST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary ASC) AS first_salary FROM employee;

#⑨ 首尾函数 LAST_VALUE(expr)
SELECT id, `name`, telephone, department_id, salary
    ,LAST_VALUE(salary) OVER(PARTITION BY department_id ORDER BY salary ASC) AS last_salary FROM employee;

#⑩ 其他函数 NTH_VALUE(expr, n)
　　#取分组排序后的前2 3名数据
SELECT id, `name`, telephone, department_id, salary
    ,NTH_VALUE(salary, 2) OVER(PARTITION BY department_id ORDER BY salary ASC) AS second_salary 
    ,NTH_VALUE(salary, 3) OVER(PARTITION BY department_id ORDER BY salary ASC) AS three_salary 
    FROM employee;

#⑪ 其他函数 NTILE(n)
    #将分区中的有序数据分为n个桶，记录桶编号 
SELECT NTILE(2) OVER(PARTITION BY department_id ORDER BY salary ASC) AS bucket_num
       ,id ,`name`, telephone, department_id, salary
    FROM employee;

3.公用表表达式

#3.1 介绍
    #又称通用表表达式，简称CTE(Common Table Expressions)。 CTE是一个命名的临时结果集，
    #作用范围是当前语句。CTE可以理解成一个可以复用的子查询，当然跟子查询还是有区别的，
    #CTE可以引用其他CTE，但子查询不能引用其他子查询。所以，可以考虑代替子查询。
    
    #分为：“普通公用表表达式” 和 “递归公用表表达式” 2种

#3.2 普通公用表表达式
    #格式
    /*
    WITH CTE名称
    AS (子查询)   //没有分号;    
    */

SELECT 
*
FROM department 
WHERE department_id IN
        (
          SELECT DISTINCT department_id FROM employee
        );
        
#等同于

#下面2条SQL语句要一起执行
WITH cte_employee
AS (SELECT DISTINCT department_id FROM employee)

SELECT 
d.*
FROM department d
JOIN cte_employee c ON d.department_id = c.department_id;

#3.3 递归公用表表达式    
    #格式
    /*
    WITH RECURSIVE
    CTE名称 AS (子查询) //没有分号;
    */
SELECT * FROM employee;

#找出employee里的下下属（第三级成员），例如：frank 一级  Eipt 二级  tom 就是三级
WITH RECURSIVE cte
AS
(
SELECT id, `name`, salary, telephone, manage_id, 1 AS n FROM employee WHERE id = 100
UNION ALL
SELECT a.id, a.name, a.salary, a.telephone, a.manage_id, n+1 FROM employee AS a
JOIN cte cte ON a.manage_id=cte.id
)
SELECT id, `name`, salary, telephone, manage_id FROM cte WHERE n >= 3;