mysql8.0新变化-----自增变量持久化和窗口函数--(9.4更新)

最新推荐文章于 2023-03-21 14:49:20 发布

CodeMartain

最新推荐文章于 2023-03-21 14:49:20 发布

阅读量393

点赞数

分类专栏： Database_All 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_54061333/article/details/119976333

版权

Database_All 专栏收录该内容

62 篇文章 1 订阅

订阅专栏

MySQL 8.0的新特性2——自增变量的持久化

在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary
key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary
key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。

建表来感受以下8.0的新特性

在这里插入图片描述插入数据------

在这里插入图片描述
插入的数据按照升序自增字段升序排列的;

接下来删除一个字段id=4的记录,然后再插入数据id=4的记录

在这里插入图片描述
关于自增变量持久化的验证----
删除id=6的字段,然后插入新记录

这是自增是从7开始的,而不是从6,
虽然删除了id为6的记录，但是再次插入空值时，并没有重用被删除的6，而是分配了7。

所以要想插入id=6的记录,只能自己指定;

接着删除id=7 的字段后,重新启动数据库后再插入数据;
在这里插入图片描述
对比mysql8.0之前的,会发现重启数据库之后新插入的数据是从第一个缺省位开始的,比如下图-----
重启后再次插入数据----是从3开始的;

MySQL8.0将自增主键的计数器持久化到重做日志中。每次计数器发生改变，都会将其写入重做日志中。如果数据库重启，InnoDB会根据重做日志中的信息来初始化计数器的内存值。为了尽量减小对系统性能的影响，计数器写入到重做日志时并不会马上刷新数据库系统。

自增并非一定从0开始;

自增是从第一条记录的的值开始的,即如果第一条记录的值为10,那么下一条记录是从10开始自增的;

***~~一下内容9.4更新…~~ ***

MySQL 8.0的新特性2------窗口函数

在MySQL 8.0版本中，新增了一个窗口函数，用它可以实现很多新的查询方式。窗口函数类似于**SUM()、COUNT()**那样的集合函数，但它并不会将多行查询结果合并为一行，而是将结果放回多行当中。也就是说，窗口函数是不需要 GROUPBY的。

表数据-----

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
14 rows in set (0.02 sec)

对表中信息进行操作------按照薪资大小从大到小进行排序


mysql> select empno,ename ,sal,comm ,rank() over w as '薪资排名' from emp window w as ( ORDER BY sal+ifnull(comm,0) desc);
+-------+--------+---------+---------+----------+
| empno | ename  | sal     | comm    | 薪资排名 |
+-------+--------+---------+---------+----------+
|  7839 | KING   | 5000.00 |    NULL |        1 |
|  7788 | SCOTT  | 3000.00 |    NULL |        2 |
|  7902 | FORD   | 3000.00 |    NULL |        2 |
|  7566 | JONES  | 2975.00 |    NULL |        4 |
|  7698 | BLAKE  | 2850.00 |    NULL |        5 |
|  7654 | MARTIN | 1250.00 | 1400.00 |        6 |
|  7782 | CLARK  | 2450.00 |    NULL |        7 |
|  7499 | ALLEN  | 1600.00 |  300.00 |        8 |
|  7521 | WARD   | 1250.00 |  500.00 |        9 |
|  7844 | TURNER | 1500.00 |    0.00 |       10 |
|  7934 | MILLER | 1300.00 |    NULL |       11 |
|  7876 | ADAMS  | 1100.00 |    NULL |       12 |
|  7900 | JAMES  |  950.00 |    NULL |       13 |
|  7369 | SMITH  |  800.00 |    NULL |       14 |
+-------+--------+---------+---------+----------+
14 rows in set (0.00 sec)

--窗口函数的格式---
**select   字段名1,字段名2,....字段名n ,  窗口函数 over (partition by 字段 order by 字段 desc) as ranking from 表名 ;**
-- 或者--- 
select   字段名1,字段名2,....字段名n ,  窗口函数 over  窗口名 as ranking from 表名 window    窗口名 as order by 字段 desc;
 ---- 区别在于 over 中是否有参数,一般习惯上不给窗口命名;

根据上表查询每个部门的员工人数占总人数的百分比;

查询思路----就像数学计算一样,
先要统计每个部门人数----- count(deptno) group by(deptno)
然后求百分比------count(deptno)/sum(count(deptno)),
其次是按照要求进行显示------为了一看就明白


mysql> select DISTINCT deptno  as 部门 ,count(deptno) as '部门人数', count(deptno)/sum(count(deptno)) over() as rate from emp group by deptno;
+------+----------+--------+
| 部门 | 部门人数 | rate   |
+------+----------+--------+
|   10 |        3 | 0.2143 |
|   20 |        5 | 0.3571 |
|   30 |        6 | 0.4286 |
+------+----------+--------+
3 rows in set (0.00 sec)

mysql>

窗口函数都有哪些呢?----

1）专用窗口函数，rank(), dense_rank, row_number等专用窗口函数。

2）聚合函数，如sum(). avg(), count(), max(), min()等
窗口函数原则上只能写在select子句中,因为窗口函数是对where或者group by子句处理后的结果进行操作.

group by 与partition by 有什么区别-----
别着急先看案例再自己总结----

还是上表,我们来看-------
操作一-------按照部门分组,并按照薪资进行排序

PARTITION by DEPTNO ORDER BY (sal + ifnull( comm,0))表示按照员工部门进行分组,按照薪资进行排序;

SELECT 
   DEPTNO 员工部门,
	empno 员工编号,
	ename 员工姓名,
	sal + ifnull( comm, 0 ) 员工薪资,
	rank ( ) over (PARTITION by DEPTNO ORDER BY (sal + ifnull( comm, 0 ))
	 ) AS 排名 
FROM
	emp;    -> emp;
+----------+----------+----------+----------+------+
| 员工部门 | 员工编号 | 员工姓名 | 员工薪资 | 排名 |
+----------+----------+----------+----------+------+
|       10 |     7934 | MILLER   |  1300.00 |    1 |
|       10 |     7782 | CLARK    |  2450.00 |    2 |
|       10 |     7839 | KING     |  5000.00 |    3 |
|       20 |     7369 | SMITH    |   800.00 |    1 |
|       20 |     7876 | ADAMS    |  1100.00 |    2 |
|       20 |     7566 | JONES    |  2975.00 |    3 |
|       20 |     7788 | SCOTT    |  3000.00 |    4 |
|       20 |     7902 | FORD     |  3000.00 |    4 |
|       30 |     7900 | JAMES    |   950.00 |    1 |
|       30 |     7844 | TURNER   |  1500.00 |    2 |
|       30 |     7521 | WARD     |  1750.00 |    3 |
|       30 |     7499 | ALLEN    |  1900.00 |    4 |
|       30 |     7654 | MARTIN   |  2650.00 |    5 |
|       30 |     7698 | BLAKE    |  2850.00 |    6 |
+----------+----------+----------+----------+------+
14 rows in set (0.00 sec)

SELECT
	DEPTNO 员工部门,
	empno 员工编号,
	ename 员工姓名,
	sal + ifnull( comm, 0 ) 员工薪资 
FROM
	emp 
GROUP BY
	deptno 
ORDER BY
	( sal + ifnull( comm, 0 ) );
+----------+----------+----------+----------+
| 员工部门 | 员工编号 | 员工姓名 | 员工薪资 |
+----------+----------+----------+----------+
|       20 |     7369 | SMITH    |   800.00 |
|       30 |     7499 | ALLEN    |  1900.00 |
|       10 |     7782 | CLARK    |  2450.00 |
+----------+----------+----------+----------+
3 rows in set (0.00 sec)

其他窗口函数-----
排名函数-----rank, dense_rank, row_number
聚合函数-----sum. avg, count, max, min等
我们来试验一下-----排名函数


mysql> SELECT deptno,empno,ename ,sal + ifnull ( comm, 0 ),dense_rank ( ) over ( PARTITION BY deptno ORDER BY (sal + ifnull ( comm, 0 )) DESC ) as 排名 FROM emp;
+--------+-------+--------+--------------------------+------+
| deptno | empno | ename  | sal + ifnull ( comm, 0 ) | 排名 |
+--------+-------+--------+--------------------------+------+
|     10 |  7839 | KING   |                  5000.00 |    1 |
|     10 |  7782 | CLARK  |                  2450.00 |    2 |
|     10 |  7934 | MILLER |                  1300.00 |    3 |
|     20 |  7788 | SCOTT  |                  3000.00 |    1 |
|     20 |  7902 | FORD   |                  3000.00 |    1 |
|     20 |  7566 | JONES  |                  2975.00 |    2 |
|     20 |  7876 | ADAMS  |                  1100.00 |    3 |
|     20 |  7369 | SMITH  |                   800.00 |    4 |
|     30 |  7698 | BLAKE  |                  2850.00 |    1 |
|     30 |  7654 | MARTIN |                  2650.00 |    2 |
|     30 |  7499 | ALLEN  |                  1900.00 |    3 |
|     30 |  7521 | WARD   |                  1750.00 |    4 |
|     30 |  7844 | TURNER |                  1500.00 |    5 |
|     30 |  7900 | JAMES  |                   950.00 |    6 |
+--------+-------+--------+--------------------------+------+
14 rows in set (0.00 sec)

mysql> SELECT deptno,empno,ename ,sal + ifnull ( comm, 0 ),rank ( ) over ( PARTITION BY deptno ORDER BY (sal + ifnull ( comm, 0 )) DESC ) as 排名 FROM emp;
+--------+-------+--------+--------------------------+------+
| deptno | empno | ename  | sal + ifnull ( comm, 0 ) | 排名 |
+--------+-------+--------+--------------------------+------+
|     10 |  7839 | KING   |                  5000.00 |    1 |
|     10 |  7782 | CLARK  |                  2450.00 |    2 |
|     10 |  7934 | MILLER |                  1300.00 |    3 |
|     20 |  7788 | SCOTT  |                  3000.00 |    1 |
|     20 |  7902 | FORD   |                  3000.00 |    1 |
|     20 |  7566 | JONES  |                  2975.00 |    3 |
|     20 |  7876 | ADAMS  |                  1100.00 |    4 |
|     20 |  7369 | SMITH  |                   800.00 |    5 |
|     30 |  7698 | BLAKE  |                  2850.00 |    1 |
|     30 |  7654 | MARTIN |                  2650.00 |    2 |
|     30 |  7499 | ALLEN  |                  1900.00 |    3 |
|     30 |  7521 | WARD   |                  1750.00 |    4 |
|     30 |  7844 | TURNER |                  1500.00 |    5 |
|     30 |  7900 | JAMES  |                   950.00 |    6 |
+--------+-------+--------+--------------------------+------+
14 rows in set (0.00 sec)

	
mysql> SELECT deptno,empno,ename ,sal + ifnull ( comm, 0 ),row_number ( ) over ( PARTITION BY deptno ORDER BY (sal + ifnull ( comm, 0 )) DESC ) as 排名 FROM emp;
+--------+-------+--------+--------------------------+------+
| deptno | empno | ename  | sal + ifnull ( comm, 0 ) | 排名 |
+--------+-------+--------+--------------------------+------+
|     10 |  7839 | KING   |                  5000.00 |    1 |
|     10 |  7782 | CLARK  |                  2450.00 |    2 |
|     10 |  7934 | MILLER |                  1300.00 |    3 |
|     20 |  7788 | SCOTT  |                  3000.00 |    1 |
|     20 |  7902 | FORD   |                  3000.00 |    2 |
|     20 |  7566 | JONES  |                  2975.00 |    3 |
|     20 |  7876 | ADAMS  |                  1100.00 |    4 |
|     20 |  7369 | SMITH  |                   800.00 |    5 |
|     30 |  7698 | BLAKE  |                  2850.00 |    1 |
|     30 |  7654 | MARTIN |                  2650.00 |    2 |
|     30 |  7499 | ALLEN  |                  1900.00 |    3 |
|     30 |  7521 | WARD   |                  1750.00 |    4 |
|     30 |  7844 | TURNER |                  1500.00 |    5 |
|     30 |  7900 | JAMES  |                   950.00 |    6 |
+--------+-------+--------+--------------------------+------+
14 rows in set (0.00 sec)

可以发现区别在于排序如果遇到重复值的时候处理方式不一样,

**rank()-----排名并列且一致,下一名在顺延(+1)的基础上排名要加上并列的记录数如1,1,1,4

row_numbe()----不考虑并列,下一名顺延(+1),如 1,2,3,4

dense_rank()----排名并列且一致,下一名顺延(+1),如1,1,1,2**

再来试验一下聚合函数,以avg 为例子------
各部门的平均工资,在求总的平均薪资

SELECT DISTINCT
	( deptno ) AS 部门编号,
	ename 员工姓名,
	job 员工职位,
	sal + ifnull( comm, 0 ) AS 薪资,
	avg( sal + ifnull( comm, 0 ) ) over ( PARTITION BY deptno ORDER BY sal + ifnull( comm, 0 ) ) as 平均薪资
FROM
	emp;
	+----------+----------+-----------+---------+-------------+
| 部门编号 | 员工姓名 | 员工职位  | 薪资    | 平均薪资    |
+----------+----------+-----------+---------+-------------+
|       10 | MILLER   | CLERK     | 1300.00 | 1300.000000 |
|       10 | CLARK    | MANAGER   | 2450.00 | 1875.000000 |
|       10 | KING     | PRESIDENT | 5000.00 | 2916.666667 |
|       20 | SMITH    | CLERK     |  800.00 |  800.000000 |
|       20 | ADAMS    | CLERK     | 1100.00 |  950.000000 |
|       20 | JONES    | MANAGER   | 2975.00 | 1625.000000 |
|       20 | SCOTT    | ANALYST   | 3000.00 | 2175.000000 |
|       20 | FORD     | ANALYST   | 3000.00 | 2175.000000 |
|       30 | JAMES    | CLERK     |  950.00 |  950.000000 |
|       30 | TURNER   | SALESMAN  | 1500.00 | 1225.000000 |
|       30 | WARD     | SALESMAN  | 1750.00 | 1400.000000 |
|       30 | ALLEN    | SALESMAN  | 1900.00 | 1525.000000 |
|       30 | MARTIN   | SALESMAN  | 2650.00 | 1750.000000 |
|       30 | BLAKE    | MANAGER   | 2850.00 | 1933.333333 |
+----------+----------+-----------+---------+-------------+
14 rows in set (0.00 sec)

-----从该表中可以发现 avg ()函数的求平均的方式第二个的值为(第一个+第二个)/2,第三个为(第一个+第二个+第三个)/3,以此类推,所以如果想要各部门平均值只需要看各部门最后一条记录-----
同理其他聚合函数也是如此;

窗口函数的应用-----
主要解决----排名问题,
topN问题,用limit 来筛选出前十名,
rank函数排名如果遇到一样的则保持排名一致,
如 -------
在这里插入图片描述

CodeMartain

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
mysql8.0新变化-----自增变量持久化和窗口函数--(9.4更新)

MySQL 8.0的新特性2——自增变量的持久化在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。建表来感受以下8.0的新特性插入数据------插入的数据按照升序自增字段升序排列的;接下来删除一个字段id=4的记录,然后再插入数据id=4的记录关于自增变量持久化的验证----
复制链接

扫一扫