MySQL8.0新特性--Group by
发表于:2024-10-17 作者:千家信息网编辑
千家信息网最后更新 2024年10月17日,Group by 语句用于结合聚合函数(如count,sum,avg,max,min),根据一个或多个列对结果集进行分组。(1)去掉重复值:根据group by后面的关键字只显示一行结果;(2)mys
千家信息网最后更新 2024年10月17日MySQL8.0新特性--Group by
Group by 语句用于结合聚合函数(如count,sum,avg,max,min),根据一个或多个列对结果集进行分组。
(1)去掉重复值:根据group by后面的关键字只显示一行结果;
(2)mysql5.7默认开启参数ONLY_FULL_GROUP_BY,表示完全group by,即select后面跟的列group by后面也必须有,但是group by后面跟的列,select后面不一定需要出现;
mysql> select @@version;+-----------+| @@version |+-----------+| 8.0.13 |+-----------+1 row in set (0.00 sec)mysql> show variables like '%sql_mode%';+---------------+-----------------------------------------------------------------------------------------------------------------------+| Variable_name | Value |+---------------+-----------------------------------------------------------------------------------------------------------------------+| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |+---------------+-----------------------------------------------------------------------------------------------------------------------+1 row in set (0.01 sec)mysql> select * from t_group;+--------+---------+------------+------------+| emp_no | dept_no | from_date | to_date |+--------+---------+------------+------------+| 22744 | d006 | 1986-12-01 | 9999-01-01 || 24007 | d005 | 1986-12-01 | 9999-01-01 || 30970 | d005 | 1986-12-01 | 2017-03-29 || 31112 | d002 | 1986-12-01 | 1993-12-10 || 40983 | d005 | 1986-12-01 | 9999-01-01 || 46554 | d008 | 1986-12-01 | 1992-05-27 || 48317 | d008 | 1986-12-01 | 1989-01-11 || 49667 | d007 | 1986-12-01 | 9999-01-01 || 50449 | d005 | 1986-12-01 | 9999-01-01 || 10004 | d004 | 1986-12-01 | 9999-01-01 |+--------+---------+------------+------------+10 rows in set (0.00 sec)mysql> select dept_no,count(*) from t_group group by dept_no;+---------+----------+| dept_no | count(*) |+---------+----------+| d006 | 1 || d005 | 4 || d002 | 1 || d008 | 2 || d007 | 1 || d004 | 1 |+---------+----------+6 rows in set (0.00 sec)mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'employees.t_group.emp_no' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by关闭ONLY_FULL_GROUP_BY参数后,不报错,但是结果是不完全group by;mysql> set session sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';Query OK, 0 rows affected (0.01 sec)mysql> select dept_no,emp_no,count(*) from t_group group by dept_no;+---------+--------+----------+| dept_no | emp_no | count(*) |+---------+--------+----------+| d006 | 22744 | 1 || d005 | 24007 | 4 || d002 | 31112 | 1 || d008 | 46554 | 2 || d007 | 49667 | 1 || d004 | 10004 | 1 |+---------+--------+----------+6 rows in set (0.00 sec)
(3)mysql5.7group by 默认还有排序功能,8.0默认只分组不排序,需要加order by才排序,这点可以从执行结果是否有Using filesort来判断
mysql> select @@version;+-----------+| @@version |+-----------+| 8.0.13 |+-----------+1 row in set (0.00 sec)mysql> select dept_no,count(*) from t_group group by dept_no;+---------+----------+| dept_no | count(*) |+---------+----------+| d006 | 1 || d005 | 4 || d002 | 1 || d008 | 2 || d007 | 1 || d004 | 1 |+---------+----------+6 rows in set (0.00 sec)mysql> desc select dept_no,count(*) from t_group group by dept_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+| 1 | SIMPLE | t_group | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------+1 row in set, 1 warning (0.00 sec)root@localhost [testdb]>select @@version;+------------+| @@version |+------------+| 5.7.16-log |+------------+1 row in set (0.00 sec)root@localhost [testdb]>select dept_no,count(*) from t_group group by dept_no;+---------+----------+| dept_no | count(*) |+---------+----------+| d002 | 1 || d004 | 1 || d005 | 4 || d006 | 1 || d007 | 1 || d008 | 2 |+---------+----------+6 rows in set (0.00 sec)root@localhost [testdb]>desc select dept_no,count(*) from t_group group by dept_no;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+| 1 | SIMPLE | t_group | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | Using temporary; Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+1 row in set, 1 warning (0.00 sec)
(4) group by是否能排序会直接影响分页查询结果
8.0.13版本mysql> select dept_no,count(*) from t_group group by dept_no limit 1;+---------+----------+| dept_no | count(*) |+---------+----------+| d006 | 1 |+---------+----------+1 row in set (0.01 sec)5.7.16版本:root@localhost [testdb]>select dept_no,count(*) from t_group group by dept_no limit 1;+---------+----------+| dept_no | count(*) |+---------+----------+| d002 | 1 |+---------+----------+1 row in set (0.00 sec)
参考链接
8.2.1.15 GROUP BY Optimization
MySQL 5.7有关group by说明的片段如下:
In MySQL, GROUP BY
is used for sorting, so the server may also apply ORDER BY
optimizations to grouping. However, relying on implicit or explicit GROUP BY
sorting is deprecated. See Section 8.2.1.14, "ORDER BY Optimization".
结果
排序
参数
版本
分组
一行
关键
关键字
函数
功能
多个
片段
语句
链接
参考
影响
有关
查询
特性
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全宣传教育周活动总结
软件开发类技术指标
博兴采购软件开发价格
apk连接免费数据库
软件交互工程师和软件开发
数据库国产安全许可证
服务器一核4g怎么样
二道区智能网络技术哪家好
夜总会招聘软件开发
安阳市网络安全教育
数据库中的控制语句
合鼎网络技术有限公司
怎样替换数据后的数据库
青岛佳欣网络技术公司
大学生网络安全宣传周
互联网科技贵州人
防止迷恋网络安全教育记录
兰州市城关区网络安全信息公司
虎牙电视版服务器关了
服务器被d
在jsp中调用数据库数据
博雅数据库江西一分一段表
入侵网游服务器
服务器认证系统算法
手机加宝塔做服务器
国务院网络安全审查
数据库技术在物流中的运用
促进网络安全人才交流
zoom服务器数量分布
两个cpu服务器内存条怎么插