网站建设企业宣传,东港区网站制作,qq刷赞网站怎么做,网站开发制作计算器MySQL 实验 10#xff1a;数据查询#xff08;3#xff09;—— 聚合函数与分组查询 目录 MySQL 实验 10#xff1a;数据查询#xff08;3#xff09;—— 聚合函数与分组查询一、聚合函数1、计数函数#xff08;COUNT#xff09;2、求和函数#xff08;SUM#xff0…MySQL 实验 10数据查询3—— 聚合函数与分组查询 目录 MySQL 实验 10数据查询3—— 聚合函数与分组查询一、聚合函数1、计数函数COUNT2、求和函数SUM3、求平均值函数AVG4、求最大值函数MAX5、求最小值函数MIN6、连接数据值函数GROUP_CONCAT 二、分组查询1、GROUP BY 的语法2、使用列名分组3、使用表达式分组4、分组的同时使用 WHERE 子句5、使用 HAVING 对分组进行选择6、WITH ROLLUP 参数 聚合函数又称为统计函数可以对查询结果进行统计和汇总对表中某一列的数据值进行计算并返回一个单一值。
使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。
一、聚合函数
常用的聚合函数包括 SUM、COUNT、AVG、MAX 和 MIN实现对表中数据的统计GROUP_CONCAT 函数的功能与聚合函数类似可以对某一列中的数据值进行连接操作。聚合函数的语法格式如下
-- 1、计数函数count使用 count(*) 时不忽略 NULL 值使用 count(列名|表达式) 时忽略 NULL 值
-- 使用 distinct 可以去除重复数据
COUNT(distinct *|列名|表达式)-- 2、求和函数sum列的类型或表达式返回值的类型必须是数值类型
SUM(列名|表达式)-- 3、求平均值函数列的类型或表达式返回值的类型必须是数值类型
AVG(列名|表达式)-- 4、求最大值函数列的类型或表达式返回值的类型可以是任意类型
MAX(列名|表达式)-- 5、求最小值函数列的类型或表达式返回值的类型可以是任意类型
MIN(列名|表达式)-- 6、连接数据值函数列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符如果省略 separator则默认的分隔符为逗号
-- 使用 distinct 可以去除重复数据
GROUP_CONCAT([distinct] 列名 separator 分隔符)说明
1如果查询中使用了聚合函数在没有分组的情况下查询结果只有一行只有一个统计结果。如果使用 GROUP BY 分组则每一个分组有一个统计结果。
2如果查询中使用了聚合函数在没有分组的情况下SELECT 后面除了聚合函数之外一般不能有列名。
1、计数函数COUNT
使用 COUNT( ) 函数用于统计记录数量通常与 GROUP BY 子句合用。
语法格式如下
COUNT(distinct *|列名|表达式)-- 说明使用 count(*) 时不忽略 NULL 值使用 count(字段|表达式) 时忽略 NULL 值例如
1统计学生人数
mysql select count(*) stu_cnt from stu;
---------
| stu_cnt |
---------
| 14 |
---------
1 row in set (0.00 sec)2统计学生所在院系的数量
mysql select count(distinct dept_id) as dept_cnt from stu;
----------
| dept_cnt |
----------
| 4 |
----------
1 row in set (0.00 sec)3统计女生人数
mysql select count(1) stu_cnt from stu where gender女;
---------
| stu_cnt |
---------
| 6 |
---------
1 row in set (0.00 sec)2、求和函数SUM
使用 SUM( ) 函数可以对表中某一列的数据求和统计时忽略 NULL 值。如果没有匹配行则返回 NULL 值。常与 GROUP BY 子句合用。语法格式如下
SUM(字段|表达式)-- 说明列的类型或表达式返回值的类型必须是数值类型例如
1统计学号为【20220124002】的同学选修的所有课程的总分
mysql select sum(score) total_score from xk where s_id20220124002;
-------------
| total_score |
-------------
| 226.00 |
-------------
1 row in set (0.00 sec)2统计女生人数
mysql select sum(1) stu_cnt from stu where gender女;
---------
| stu_cnt |
---------
| 6 |
---------
1 row in set (0.00 sec)3统计男生人数
mysql select sum(if(gender男,1,0)) stu_cnt from stu;
---------
| stu_cnt |
---------
| 8 |
---------
1 row in set (0.00 sec)3、求平均值函数AVG
使用 AVG( ) 函数可以计算表中某一列数据的平均值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下
AVG(字段|表达式)-- 说明列的类型或表达式返回值的类型必须是数值类型例如
1统计学号为【20220124002】的同学选修的所有课程的平均分
mysql select avg(score) avg_score from xk where s_id20220124002;
-------------
| avg_score |
-------------
| 75.333333 |
-------------
1 row in set (0.00 sec)2统计学生的平均年龄
mysql select avg(year(now())-year(birth)) avg_age from stu;
---------
| avg_age |
---------
| 22.0714 |
---------
1 row in set (0.00 sec)4、求最大值函数MAX
使用 MAX( ) 函数统计某一列数据的最大值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下
MAX(字段|表达式)-- 说明列的类型或表达式返回值的类型可以是任意类型例如
1查询所有学生的最大年龄
mysql select max(year(now())-year(birth)) max_age from stu;
---------
| max_age |
---------
| 24 |
---------
1 row in set (0.00 sec)2查询编号为【C01002】的课程的最高分
mysql select max(score) max_score from xk where c_idC01002;
-----------
| max_score |
-----------
| 69.00 |
-----------
1 row in set (0.00 sec)5、求最小值函数MIN
使用 MIN( ) 函数统计某一列数据的最小值。统计时忽略 NULL 值。常与 GROUP BY 子句合用。语法格式如下
MIN(字段|表达式)-- 说明列的类型或表达式返回值的类型可以是任意类型例如
1查询所有学生的最小年龄
mysql select min(year(now())-year(birth)) min_age from stu;
---------
| min_age |
---------
| 21 |
---------
1 row in set (0.00 sec)2查询编号为【C01002】的课程的最低分
mysql select min(score) min_score from xk where c_idC01002;
-----------
| min_score |
-----------
| 68.00 |
-----------
1 row in set (0.00 sec)6、连接数据值函数GROUP_CONCAT
使用 GROUP_CONCAT( ) 函数可以把某一列的数据值连接成一个字符串数据值之间使用指定的分隔符分隔默认为逗号。语法格式如下
GROUP_CONCAT([distinct] 列名 separator 分隔符)-- 说明列的类型或表达式返回值的类型可以是任意类型
-- 使用 separator 指定数据之间的分隔符如果省略 separator则默认的分隔符为逗号例如
1查询编号为【D01】的学院的学生名单
mysql select group_concat(s_name) from stu where dept_idD01;
-----------------------------
| group_concat(s_name) |
-----------------------------
| 薛智玲,杨铭华,张从超,孙金航 |
-----------------------------
1 row in set (0.00 sec)2查询编号为【D02】的学院的学生名单学生之间用空格分隔
mysql select group_concat(s_name separator ) from stu where dept_idD01;
-------------------------------------
| group_concat(s_name separator ) |
-------------------------------------
| 薛智玲 杨铭华 张从超 孙金航 |
-------------------------------------
1 row in set (0.00 sec)二、分组查询
使用 GROUP BY 关键字可以将查询结果按照一个或多个列或者表达式进行分组分组的依据为 GROUP BY 后面的列名或表达式。GROUP BY 通常与聚合函数合用。
1、GROUP BY 的语法
GROUP BY 子句的语法格式如下
GROUP BY 列名|表达式[,...] [HAVING 条件表达式] [WITH ROLLUP]--说明
1使用分组查询时select 后面的字段列表只能包含 GROUP BY 后面的列名或表达式以及聚合函数不能包含其他的列或表达式否则会报错。
2列名|表达式分组依据按列名或表达式进行分组。
3HAVING 条件表达式对分组进行选择符合条件表达式的结果才会显示。
4WITH ROLLUP在所有记录的最后加上一条记录该记录为对所有行的统计结果求和。2、使用列名分组
例如按照 s_id 分组统计每个学生所学课程的平均分
mysql select s_id, avg(score) avg_score from xk group by s_id;
------------------------
| s_id | avg_score |
------------------------
| 20220124001 | 73.666667 |
| 20220124002 | 75.333333 |
| 20220124003 | 94.500000 |
| 20220124004 | 88.500000 |
| 20220214001 | 85.666667 |
| 20220214002 | 68.000000 |
| 20220214003 | 87.333333 |
| 20220325101 | 80.000000 |
| 20220325102 | 80.333333 |
| 20220325103 | 69.333333 |
| 20220410101 | 82.666667 |
| 20220410102 | 70.666667 |
| 20220410103 | 69.333333 |
| 20220410104 | 69.500000 |
------------------------
14 rows in set (0.00 sec)3、使用表达式分组
例如按照学生的姓氏分组查询每组对应的学生人数
mysql select left(s_name, 2) surname, count(*) stu_cnt from stu group by surname;
------------------
| surname | stu_cnt |
------------------
| 孙 | 1 |
| 夏 | 1 |
| 谭 | 1 |
| 杨 | 1 |
| 薛 | 1 |
| 赵 | 2 |
| 张 | 2 |
| 裴 | 1 |
| 聂 | 1 |
| 周 | 1 |
| 董 | 1 |
| 江 | 1 |
------------------
12 rows in set (0.00 sec)4、分组的同时使用 WHERE 子句
分组时如果使用了 WHERE 子句则先使用 WHERE 对表中的数据进行筛选然后进行分组和统计。
例如利用 xk 表统计每门课程考试分数大于 80 分的学生人数
mysql select c_id, count(*) stu_cnt from xk where score80 group by c_id;
-----------------
| c_id | stu_cnt |
-----------------
| C01001 | 3 |
| C01003 | 3 |
| C02102 | 1 |
| C02103 | 2 |
| C03201 | 2 |
| C03202 | 2 |
| C04112 | 2 |
| C04113 | 1 |
-----------------
8 rows in set (0.00 sec)5、使用 HAVING 对分组进行选择
使用 HAVING 子句可以对分组进行选择。当 HAVING 子句与 WHER 子句同时使用时查询执行的顺序为先使用 WHERE 对表中的记录进行筛选然后对满足条件的记录分组与统计再使用 HAVING 子句对分组进行选择。
例如
1利用 xk 表查询每门课的选修人数并且只显示选修人数大于 3 门的课程信息
mysql select c_id, count(*) stu_cnt from xk group by c_id having stu_cnt3;
-----------------
| c_id | stu_cnt |
-----------------
| C01001 | 4 |
| C01003 | 4 |
| C04111 | 4 |
| C04112 | 4 |
-----------------
4 rows in set (0.00 sec)2利用 stu 表查询每个学院的男生人数并且只显示男生人数大于 3 人的学院信息
mysql select dept_id, count(*) stu_cnt from stu where gender男 group by dept_id having stu_cnt2;
------------------
| dept_id | stu_cnt |
------------------
| D03 | 3 |
------------------
1 row in set (0.00 sec)6、WITH ROLLUP 参数
在所有记录的最后加上一条记录该记录为对所有行的统计结果求和。
例如
1利用 stu 表查询每个学院的学生人数以及总人数
-- 把 dept_id 为空的学生的 dept_id 修改为 D04
mysql update stu set dept_idD04 where dept_id is null;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0-- 分组查询
mysql select dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
------------------
| dept_id | stu_cnt |
------------------
| D01 | 4 |
| D02 | 3 |
| D03 | 3 |
| D04 | 4 |
| NULL | 14 |
------------------
5 rows in set (0.00 sec)2利用 stu 表查询每个学院的学生人数以及总人数并把最后一行的 NULL 修改为 stu_total
mysql select ifnull(dept_id, total) dept_id, count(*) stu_cnt from stu group by dept_id with rollup;
------------------
| dept_id | stu_cnt |
------------------
| D01 | 4 |
| D02 | 3 |
| D03 | 3 |
| D04 | 4 |
| total | 14 |
------------------
5 rows in set, 1 warning (0.00 sec)
