5类聚集函数

聚集函数用于对于一个集合中的数据进行处理，不是一行一行的数据

count统计行数、sum求和、max最大值、min最小值、avg平均值

计数

语法 count([all/distinct] 列名称/*)

获取总学生数

select count(*) from tb_student;
select count(1) from tb_student;
select count(distinct dept) from tb_student; -- 获取系的个数
mysql> select * from tb_student;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 3 | 仗义 | 19 | 1 | 大数据 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
+----+--------+------+------+--------+
6 rows in set (0.00 sec)
select count(dept) from tb_student; -- 进行计数统计时，null不参与统计

需要统计选修1号课程的学生人数

select count(*) from tb_choice where cid=1;

假设选择表中需要记录补考成绩，也就是意味着课程会有重复，则问题1：primary key(cid,sid)就是错误的，由于出现重复计数，则选修人数统计出错

select count(distinct sid) from tb_choice where cid=1;

 统计值

用于数值类型的列，不要用于其他类型。max min sum avg

• max/min可以用于日期类型比较，最新的日期最大

ysql> select * from tb_student;
+----+--------+------+------+--------+
| id | name | age | sex | dept |
+----+--------+------+------+--------+
| 1 | 小胖 | 18 | 1 | 软工 |
| 2 | 东方 | 16 | 0 | 机壳 |
| 3 | 仗义 | 19 | 1 | 大数据 |
| 4 | 张骞 | 16 | 1 | 软工 |
| 5 | 张小骞 | 16 | 1 | 软工 |
| 6 | 从来 | 16 | 1 | NULL |
| 7 | 张展 | 16 | 1 | NULL |
+----+--------+------+------+--------+
7 rows in set (0.00 sec)
mysql> select max(age) from tb_student;
+----------+
| max(age) |
+----------+
|       19 |
+----------+
1 row in set (0.00 sec)
mysql> select min(age) from tb_student;
+----------+
| min(age) |
+----------+
|       16 |
+----------+
1 row in set (0.00 sec)
mysql> select avg(age) from tb_student; -- null值不参与计算，除非使用空值处理函数
将其转换为确定数值才可以
+----------++----------+
|  16.7143 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(age) from tb_student;
+----------+
| sum(age) |
+----------+
|      101 |
+----------+
1 row in set (0.00 sec)
| avg(age) |

 查询选修1号课程的学生最高成绩和平均成绩

select max(score),avg(score) from tb_choice where cid=1;
mysql> select sum(age),max(age),min(age),avg(age) from tb_student;
+----------+----------+----------+----------+
| sum(age) | max(age) | min(age) | avg(age) |
+----------+----------+----------+----------+
|      101 |       19 |       16 |  16.8333 |
+----------+----------+----------+----------+
1 row in set (0.00 sec)

 问题

select count(*) 、 select count(1) 和 select count(列名称)

• 列名为主键 count(列名) 比 count(1) 速度快；如果列名不是主键，则 count(1) 比 count(列名) 快
• 如果表多个列并且没有主键，则 count(1) 比 count(*) 速度快，如果有主键 `count(主键列名) 速 度最快，如果表中只有一个列 count(*) 速度最快
• count(1) 会统计表中的所有记录数，包括字段为null的记录； count(列名称) 则列为null时不统计
• MySQL针对不同的存储引擎进行了优化处理，MyISAM会将表的总行数记录下来，供 count(*) 查 询使用； Innodb则会在扫描表时选择最小的索引成本执行，所以在Innodb中 count(*) 和 count(1) 实质上没有区别，而且执行效率一致，只有 count(列名称) 需要进行null值列的判断， 所以效率低一些

对查询结果分组

可以使用group by子句对查询结果进行分组处理，经常会使用聚集函数

• 如果不使用分组，聚集函数则用于处理所有查询结果数据
• 如果使用分组，则分别作用于各个分组查询的结果数据

获取男女生的平均年龄

• 按照性别进行分组 group by sex ，不同的sex值则放入不同的组中
• 平均值就是聚集函数，针对一个集合中的数据进行处理 

mysql> select sex,avg(age) from tb_student group by sex;
+------+----------+
| sex | avg(age) |
+------+----------+
|   1 |  17.0000 |
|   0 |  16.0000 |
| NULL|  16.0000 |
+------+----------+
3 rows in set (0.00 sec)

 注意：如果在select之后不在聚集函数中的列名称一定出现在group by之后，否则语法错误

having可以对分组进行条件选择

需要获取人数多余2人的不同性别学生的平均年龄

• 按照性别分组
• 要统计的组必须人数多余2人，小于等于2人的分组不进行显示处理 

select sex,avg(age) from tb_student group by sex having count(1)>2;

 having和where之间的区别

只有满足条件的数据才会输出显示

最大区别在于：作用的对象不同

• where子句用于基表或者视图，从中选择满足条件的元组
• having子句用于分组，从多个分组中选择满足条件的分组