讲义第 29 讲——select 查询之GROUP BY"/>
MySQL讲义第 29 讲——select 查询之GROUP BY
MySQL讲义第 29 讲——select 查询之GROUP BY
文章目录
- MySQL讲义第 29 讲——select 查询之GROUP BY
- 一、不带聚合函数的分组查询
- 1、select 选择的字段列表只包含 GROUP BY 后面的字段名或表达式
- 2、select 选择的字段列表包含了其他的字段名或表达式
- 二、使用聚合函数实现分组统计
- 1、分别统计男生和女生的平均身高
- 2、统计每个系的学生人数
- 3、统计各个年级的学生人数
- 4、统计各个年级的男生人数
- 三、使用 HAVING 对分组进行选择
- 1、查询学生人数超过 3 个人的系的信息及学生人数
- 2、查询女生人数超过 3 个人的年级及女生的人数
- 3、查询女生平均身高超过 165 的系的信息及女生的平均身高
- 4、查询所学课程平均分大于 85分 的学生信息及所学课程的平均分
- 四、按照多个字段分组
- 五、使用 WITH ROLLUP 参数
- 1、统计每个系的学生人数及平均身高
- 2、查询各个系的男生和女生人数及平均分高
- 六、group_concat( ) 函数的用法
使用 GROUP BY 关键字可以将查询结果按照某个字段或多个字段进行分组。分组的依据为 GROUP BY 后面的字段中取值相等的分为一组。GROUP BY 通常与聚合函数一起使用。语法格式如下:
GROUP BY 字段名|表达式 [HAVING 条件表达式] [WITH ROLLUP]说明:
(1)使用分组查询时,select后面的字段列表只能包含 GROUP BY 后面的字段名或表达式以及聚合函数,不能包含其他字段或表达式,否则会报错。
(2)字段名|表达式:分组依据,按字段或表达式进行分组。
(3)HAVING 条件表达式:对分组进行选择,符合条件表达式的结果才会显示。
(4)WITH ROLLUP:在所有记录的最后加上一条记录,该记录为对所有行的统计结果。
(5)可以使用 GROUP_CONCAT() 函数把某个字段中的所有值连接成一个字符串。
一、不带聚合函数的分组查询
1、select 选择的字段列表只包含 GROUP BY 后面的字段名或表达式
mysql> SELECT-> gender-> FROM-> stu-> GROUP BY-> gender;
+--------+
| gender |
+--------+
| 女 |
| 男 |
+--------+
2 rows in set (0.00 sec)
2、select 选择的字段列表包含了其他的字段名或表达式
mysql> SELECT-> stu_name,gender-> FROM -> stu-> GROUP BY-> gender;ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'wgx.stu.stu_name' which is not func
tionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
二、使用聚合函数实现分组统计
GROUP BY 通常与聚合函数一起使用,以实现对每一个分组的统计。
1、分别统计男生和女生的平均身高
mysql> SELECT-> gender,avg(height) as 平均身高-> FROM-> stu-> GROUP BY-> gender;
+--------+--------------+
| gender | 平均身高 |
+--------+--------------+
| 女 | 164.80000 |
| 男 | 176.50000 |
+--------+--------------+
2 rows in set (0.00 sec)
2、统计每个系的学生人数
mysql> SELECT-> d.dept_id,-> d.dept_name,-> count(*) as 学生人数-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY -> d.dept_id;
+---------+--------------+--------------+
| dept_id | dept_name | 学生人数 |
+---------+--------------+--------------+
| D01 | 管理系 | 4 |
| D02 | 计算机系 | 4 |
| D03 | 数学系 | 2 |
+---------+--------------+--------------+
3 rows in set (0.01 sec)
3、统计各个年级的学生人数
mysql> SELECT -> left(stu_id,4),-> count(*) as 学生人数-> FROM-> stu-> GROUP BY-> left(stu_id,4);
+----------------+--------------+
| left(stu_id,4) | 学生人数 |
+----------------+--------------+
| 2018 | 7 |
| 2019 | 4 |
+----------------+--------------+
2 rows in set (0.00 sec)
4、统计各个年级的男生人数
mysql> SELECT -> left(stu_id,4),-> count(*) as 男生人数-> FROM-> stu-> WHERE-> gender = '男'-> GROUP BY-> left(stu_id,4);
+----------------+--------------+
| left(stu_id,4) | 男生人数 |
+----------------+--------------+
| 2018 | 3 |
| 2019 | 3 |
+----------------+--------------+
2 rows in set (0.00 sec)--说明:本例先对 stu 表进行选择,把所有的男生信息取出再按年级进行分组,统计人数。
三、使用 HAVING 对分组进行选择
使用 having 子句可以对分组进行选择。当 having 子句与 where 子句同时使用时,查询执行的顺序为:先使用 where 对表中的记录进行筛选,然后对满足条件的记录分组与统计,再使用 having 子句对分组进行选择。例如:
1、查询学生人数超过 3 个人的系的信息及学生人数
mysql> SELECT -> d.dept_id,-> d.dept_name,-> count(*) as 学生人数-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_id-> HAVING -> count(*) > 3;
+---------+--------------+--------------+
| dept_id | dept_name | 学生人数 |
+---------+--------------+--------------+
| D01 | 管理系 | 4 |
| D02 | 计算机系 | 4 |
+---------+--------------+--------------+
2 rows in set (0.01 sec)
2、查询女生人数超过 3 个人的年级及女生的人数
mysql> SELECT -> left(stu_id,4),-> count(*) as 女生人数-> FROM-> stu-> WHERE-> gender = '女'-> GROUP BY-> left(stu_id,4)-> HAVING-> count(*) > 3;
+----------------+--------------+
| left(stu_id,4) | 女生人数 |
+----------------+--------------+
| 2018 | 4 |
+----------------+--------------+
1 row in set (0.00 sec)
3、查询女生平均身高超过 165 的系的信息及女生的平均身高
mysql> SELECT -> d.dept_id,-> d.dept_name,-> avg(height) as 女生平均身高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> WHERE-> gender = '女'-> GROUP BY-> d.dept_id-> HAVING -> avg(height) > 165;
+---------+--------------+--------------------+
| dept_id | dept_name | 女生平均身高 |
+---------+--------------+--------------------+
| D01 | 管理系 | 167.00000 |
| D02 | 计算机系 | 167.50000 |
+---------+--------------+--------------------+
2 rows in set (0.00 sec)
4、查询所学课程平均分大于 85分 的学生信息及所学课程的平均分
mysql> SELECT-> s.stu_id,-> s.stu_name,-> avg(score) as 平均分-> FROM-> stu s JOIN score sc ON s.stu_id = sc.stu_id-> GROUP BY-> s.stu_id-> HAVING-> avg(score) > 85;
+-----------+-----------+-----------+
| stu_id | stu_name | 平均分 |
+-----------+-----------+-----------+
| 201801102 | 刘国强 | 98.0000 |
| 201801201 | 王艳艳 | 89.0000 |
+-----------+-----------+-----------+
2 rows in set (0.02 sec)
四、按照多个字段分组
例如:查询各个系的男生和女生人数及平均分高
mysql> SELECT -> d.dept_name,-> gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_id,-> gender;
+--------------+--------+--------+--------------+
| dept_name | gender | 人数 | 平均分高 |
+--------------+--------+--------+--------------+
| 管理系 | 女 | 1 | 167.00000 |
| 管理系 | 男 | 3 | 178.00000 |
| 计算机系 | 女 | 2 | 167.50000 |
| 计算机系 | 男 | 2 | 175.50000 |
| 数学系 | 女 | 1 | 160.00000 |
| 数学系 | 男 | 1 | 174.00000 |
+--------------+--------+--------+--------------+
6 rows in set (0.01 sec)
五、使用 WITH ROLLUP 参数
在所有记录的最后加上一条记录,该记录为对所有行的统计结果。例如:
SELECT
coalesce(d.dept_name,'总人数及平均身高') as dept_name,
coalesce(gender,'总人数及平均身高') as gender,
count(*) as 人数,
avg(height) as 平均分高
FROM
stu s JOIN dept d ON s.dept_id = d.dept_id
GROUP BY
d.dept_name,
gender
with rollup;
1、统计每个系的学生人数及平均身高
mysql> SELECT -> d.dept_name,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name-> with rollup;
+--------------+--------+--------------+
| dept_name | 人数 | 平均分高 |
+--------------+--------+--------------+
| 数学系 | 2 | 167.00000 |
| 管理系 | 4 | 175.25000 |
| 计算机系 | 4 | 171.50000 |
| NULL | 10 | 172.10000 |
+--------------+--------+--------------+
4 rows in set (0.00 sec)--可以使用 coalesce() 函数为最后一行的第一列指定名称。
--coalesce( ) 函数的作用:返回参数中的第一个非空表达式。
mysql> select coalesce('a','b'),coalesce(null,'c');
+-------------------+--------------------+
| coalesce('a','b') | coalesce(null,'c') |
+-------------------+--------------------+
| a | c |
+-------------------+--------------------+
1 row in set (0.00 sec)mysql> SELECT -> coalesce(d.dept_name,'总人数') as d.dept_name,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name-> with rollup;
+-----------------------------------+--------+--------------+
| dept_name | 人数 | 平均分高 |
+-----------------------------------+--------+--------------+
| 数学系 | 2 | 167.00000 |
| 管理系 | 4 | 175.25000 |
| 计算机系 | 4 | 171.50000 |
| 总人数 | 10 | 172.10000 |
+-----------------------------------+--------+--------------+
4 rows in set (0.00 sec)
2、查询各个系的男生和女生人数及平均分高
mysql> SELECT -> d.dept_name,-> gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name,-> gender-> with rollup;
+--------------+--------+--------+--------------+
| dept_name | gender | 人数 | 平均分高 |
+--------------+--------+--------+--------------+
| 数学系 | 女 | 1 | 160.00000 |
| 数学系 | 男 | 1 | 174.00000 |
| 数学系 | NULL | 2 | 167.00000 |
| 管理系 | 女 | 1 | 167.00000 |
| 管理系 | 男 | 3 | 178.00000 |
| 管理系 | NULL | 4 | 175.25000 |
| 计算机系 | 女 | 2 | 167.50000 |
| 计算机系 | 男 | 2 | 175.50000 |
| 计算机系 | NULL | 4 | 171.50000 |
| NULL | NULL | 10 | 172.10000 |
+--------------+--------+--------+--------------+
10 rows in set (0.00 sec)
为 NULL 值指定内容:
mysql> SELECT -> coalesce(d.dept_name,'总人数及平均身高') as dept_name,-> coalesce(gender,'总人数及平均身高') as gender,-> count(*) as 人数,-> avg(height) as 平均分高-> FROM-> stu s JOIN dept d ON s.dept_id = d.dept_id-> GROUP BY-> d.dept_name,-> gender-> with rollup;
+--------------------------+--------------------------+--------+--------------+
| dept_name | gender | 人数 | 平均分高 |
+--------------------------+--------------------------+--------+--------------+
| 数学系 | 女 | 1 | 160.00000 |
| 数学系 | 男 | 1 | 174.00000 |
| 数学系 | 总人数及平均身高 | 2 | 167.00000 |
| 管理系 | 女 | 1 | 167.00000 |
| 管理系 | 男 | 3 | 178.00000 |
| 管理系 | 总人数及平均身高 | 4 | 175.25000 |
| 计算机系 | 女 | 2 | 167.50000 |
| 计算机系 | 男 | 2 | 175.50000 |
| 计算机系 | 总人数及平均身高 | 4 | 171.50000 |
| 总人数及平均身高 | 总人数及平均身高 | 10 | 172.10000 |
+--------------------------+--------------------------+--------+--------------+
10 rows in set, 1 warning (0.00 sec)
六、group_concat( ) 函数的用法
可以使用 GROUP_CONCAT() 函数把某个字段中的所有值连接成一个字符串。和聚合函数的用法相似。格式如下:
group_concat(字段名 | 表达式)
举例:查询各个年级的学生名单
mysql> SELECT-> left(stu_id,4) as 年级,-> group_concat(stu_name)-> FROM -> stu-> GROUP BY -> left(stu_id,4);
+--------+--------------------------------------------------------------------+
| 年级 | group_concat(stu_name) |
+--------+--------------------------------------------------------------------+
| 2018 | 王占峰,刘国强,巩莉,宋丹风,王艳艳,赵牡丹,王鹏飞 |
| 2019 | 王宏伟,张静静,李刚,刘鹏 |
+--------+--------------------------------------------------------------------+
2 rows in set (0.01 sec)
更多推荐
MySQL讲义第 29 讲——select 查询之GROUP BY
发布评论