1、聚合查询举例
查询students表一共有多少条记录为例,我们可以使用SQL内置的COUNT()函数查询:
SELECT COUNT(*) FROM students;
结果如下:
COUNT(*) |
10 |
上诉结果返回的是一个二维表,我们可以给结果起一个别名
SELECT COUNT(*) as num FROM students;
结果如下:
num |
10 |
COUNT(*)和COUNT(id)实际上是一样的效果。
另外注意,聚合查询同样可以使用WHERE条件,因此我们可以方便地统计出有多少男生、多少女生、多少80分以上的学生等:
SELECT COUNT(*) boys FROM students WHERE gender = 'M';
结果如下:
boys |
5 |
2、常用聚合函数
函数 | 说明 |
SUM | 计算某一列的合计值,该列必须为数值类型 |
AVG | 计算某一列的平均值,该列必须为数值类型 |
MAX | 计算某一列的最大值 |
MIN | 计算某一列的最小值 |
COUNT | 计数 |
DISTINCT | 去重 |
CEILING | 给某个数向上取整,即1.1为2, 2.9为3 |
FLOOR | 给某个数向下取整,即2.2为2, 2.9为2 |
统计男生的平均成绩,我们用下面的聚合查询:
SELECT AVG(score) average FROM students WHERE gender = 'M';
结果如下:
average |
81.4 |
再比如:
我要查询班级有哪些:
SELECT DISTINCT class_id from students;
class_id |
1 |
2 |
3 |
要特别注意:如果聚合查询的WHERE条件没有匹配到任何行,COUNT()会返回0,而SUM()、AVG()、MAX()和MIN()会返回NULL
3、分组查询
SQL还提供了“分组聚合”的功能。我们观察下面的聚合查询,
执行这个查询,COUNT()的结果不再是一个,而是3个,这是因为,GROUP BY子句指定了按class_id分组,因此,执行该SELECT语句时,会把class_id相同的列先分组,再分别计算,因此,得到了3行结果。
SELECT COUNT(*) num FROM students GROUP BY class_id;
结果:
num |
4 |
3 |
3 |
我们想统计各班的男生和女生人数:
SELECT class_id, gender, COUNT(*) num FROM students GROUP BY class_id, gender;
class_id | gender | num |
1 | M | 2 |
1 | F | 2 |
2 | F | 1 |
2 | M | 2 |
3 | F | 2 |
3 | M | 1 |
练习:
- 查询查出每个班级的平均分
- 查询查出每个班级男生和女生的平均分
答案:
- SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id;
- SELECT class_id, gender, AVG(score) as avg_score FROM students GROUP BY class_id, gender;
接上面练习:
我们要查出每个班级的平均分,并且平均分大于85的班级是哪些?
有同学可能说,这还不简单
- SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id WHERE AVG(score) > 85;
事实上这里会报错,因为分组查询之后已经有一个结果集,不能用WHERE,需要用HAVING
改为:
- SELECT class_id, AVG(score) as avg_score FROM students GROUP BY class_id HAVING AVG(score) > 85;
结果:
class_id | avg_score |
1 | 86.5 |
3 | 89.33333333333333 |
顺道插一嘴(高频面试题,where和having区别):
1,两者在select语句中都可以作为条件;
2,having是在分组之后对数据进行过滤,where是在分组之前对数据进行过滤;
3,having之后还可以跟聚合函数,而where是不可以的;
4,where用于精准查询,除了与like配合使用之外;
5,having的使用必须与group by 结合使用,否则报错。
4、小结
- 常用聚合查询有SUM、AVG、MAX、MIN、COUNT、DISTINCT、CEILING、FLOOR
- 分组查询用GROUP BY,如果要对分组查询之后的结果做筛选,需要用HAVING而不是用WHERE