数据库教程:浅谈MySQL中的group by

目录1、前言2、准备user表2.1 group by规则2.2 group by使用2.3 having使用2.4 order by与limit2.5 with rollup1、前言mysql的gr

目录
  • 1、前言
  • 2、准备user表
    • 2.1 group by规则
    • 2.2 group by使用
    • 2.3 having使用
    • 2.4 order by与limit
    • 2.5 with rollup

浅谈MySQL中的group by

1、前言

mysqlgroup by用于对查询的数据进行分组;此外mysql提供having子句对分组内的数据进行过滤。

mysql提供了许多select子句关键字,

它们在语句中的顺序如下所示:

子句 作用 是否必须/何时使用
select 查询要返回的数据或者表达式
from 指定查询的表
where 指定行级过滤
group by 分组 否/对数据分组时使用
having 分组过滤 否/对分组后的数据过滤使用
order by 返回数据时指定排序规则
limit 指定返回数据的行数

2、准备user表

准备一张user表,其ddl和表数据如下所示

  set names utf8mb4;  set foreign_key_checks = 0;    -- ----------------------------  -- table structure for user  -- ----------------------------  drop table if exists `user`;  create table `user`  (    `id` bigint(20) not null auto_increment comment '主键',    `name` varchar(255) character set utf8 collate utf8_general_ci not null comment '用户名',    `nation` varchar(255) character set utf8 collate utf8_general_ci null default null comment '民族',    `age` int(11) null default null comment '年龄',    `height` double null default null comment '身高',    `sex` smallint(6) null default null comment '性别',    primary key (`id`) using btree  ) engine = innodb character set = utf8 collate = utf8_general_ci row_format = dynamic;    -- ----------------------------  -- records of user  -- ----------------------------  insert into `user` values (1, '李子捌', '汉族', 18, 180, 1);  insert into `user` values (2, '张三', '回族', 20, 175, 1);  insert into `user` values (3, '李四', '维吾尔族', 45, 168, 0);  insert into `user` values (4, '王五', '蒙古族', 18, 177, 1);  insert into `user` values (5, '赵六', '汉族', 16, 184, 0);  insert into `user` values (6, '田七', '维吾尔族', 27, 192, 1);    

user表中数据如下所示:

  mysql> select * from user;  +----+--------+----------+------+--------+------+  | id | name   | nation   | age  | height | sex  |  +----+--------+----------+------+--------+------+  |  1 | 李子捌 | 汉族     |   18 |    180 |    1 |  |  2 | 张三   | 回族     |   20 |    175 |    1 |  |  3 | 李四   | 维吾尔族 |   45 |    168 |    0 |  |  4 | 王五   | 蒙古族   |   18 |    177 |    1 |  |  5 | 赵六   | 汉族     |   16 |    184 |    0 |  |  6 | 田七   | 维吾尔族 |   27 |    192 |    1 |  +----+--------+----------+------+--------+------+  6 rows in set (0.00 sec)    

2.1 group by规则

使用group by之前需要先了解group by使用的相关规则

  • group by子句置于where之后,order by子句之前
  • having 子句置于group by 之后,order by子句之前
  • group by子句中的每个列都必须是select的检索列或者有效表达式,不能使用聚集函数
  • select中使用的表达式,在group by子句中必须出现,并且不能使用别名
  • group by分组的数据中包含null值,null值被分为一组
  • group by子句可以嵌套,嵌套的分组在最后分组上汇总

2.2 group by使用

需求:

统计不同民族的用户数

语句:

  mysql> select nation, count(*) from user group by nation;  +----------+----------+  | nation   | count(*) |  +----------+----------+  | 汉族     |        2 |  | 回族     |        1 |  | 维吾尔族 |        2 |  | 蒙古族   |        1 |  +----------+----------+  4 rows in set (0.00 sec)    

group by可以结合where一起使用,不过where不能在group by之后进行过滤,使用where子句之后,分组的数据是where子句过滤后的数据集。

  mysql> select nation, count(*) as nation_num  from user where sex = 0 group by nation;  +----------+------------+  | nation   | nation_num |  +----------+------------+  | 维吾尔族 |          1 |  | 汉族     |          1 |  +----------+------------+  2 rows in set (0.00 sec)    

2.3 having使用

group by分组后的数据还需要再次过滤,就必须使用having子句。group by子句后使用where子句mysql服务器会抛出异常

  mysql> select nation, count(*) as nation_num  from user group by nation where nation = '汉族';  error 1064 (42000): you have an error in your sql syntax; check the manual that corresponds to your mysql server version for the right syntax to use near 'where nation = '汉族'' at line 1      

此时只需要将上面where子句替换成having子句即可,having子句支持所有的where操作符,通俗的说where子句能用的地方只有替换成having就可以在group by子句后使用了

  vmysql> select nation, count(*) as nation_num  from user group by nation having nation = '汉族';  +--------+------------+  | nation | nation_num |  +--------+------------+  | 汉族   |          2 |  +--------+------------+  1 row in set (0.00 sec)    

2.4 order by与limit

分组后的数据需要排序可以使用order byorder by子句需要更在having子句之后。

  mysql> select nation, count(*) as nation_num  from user group by nation having nation != '汉族' order by nation_num desc;  +----------+------------+  | nation   | nation_num |  +----------+------------+  | 维吾尔族 |          2 |  | 回族     |          1 |  | 蒙古族   |          1 |  +----------+------------+  3 rows in set (0.00 sec)    

对于输出的结果需要指定返回的行数,可以使用limit,limit子句在整个语句的最后。

  mysql> select nation, count(*) as nation_num  from user group by nation having nation != '汉族' order by nation_num desc limit 2;  +----------+------------+  | nation   | nation_num |  +----------+------------+  | 维吾尔族 |          2 |  | 回族     |          1 |  +----------+------------+  2 rows in set (0.00 sec)    

2.5 with rollup

在group by子句中,with rollup 可以实现在分组统计数据基础上再进行相同的统计(sum,avg,count…)

比如max():

  mysql> select nation, max(height) as nation_num  from user group by nation with rollup;  +----------+------------+  | nation   | nation_num |  +----------+------------+  | 回族     |        175 |  | 汉族     |        184 |  | 维吾尔族 |        192 |  | 蒙古族   |        177 |  | null     |        192 |  +----------+------------+  5 rows in set (0.00 sec)    

比如avg():

  mysql> select nation, avg(height) as nation_num  from user group by nation with rollup;  +----------+--------------------+  | nation   | nation_num         |  +----------+--------------------+  | 回族     |                175 |  | 汉族     |                182 |  | 维吾尔族 |                180 |  | 蒙古族   |                177 |  | null     | 179.33333333333334 |  +----------+--------------------+  5 rows in set (0.00 sec)    

比如count():

  mysql> select nation, count(*) as nation_num  from user group by nation with rollup;  +----------+------------+  | nation   | nation_num |  +----------+------------+  | 回族     |          1 |  | 汉族     |          2 |  | 维吾尔族 |          2 |  | 蒙古族   |          1 |  | null     |          6 |  +----------+------------+  5 rows in set (0.00 sec)  

到此这篇关于浅谈mysql中的group by的文章就介绍到这了,更多相关mysql中的group by内容请搜索<计算机技术网>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<计算机技术网>!

需要了解更多数据库技术:浅谈MySQL中的group by,都可以关注数据库技术分享栏目—计算机技术网(www.ctvol.com)

本文来自网络收集,不代表计算机技术网立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ctvol.com/dtteaching/948744.html

(0)
上一篇 2021年11月22日
下一篇 2021年11月22日

精彩推荐