GROUP BY
作用: 根据给定数据列的每个成员,对查询结果进行分组统计,关于这句话,有两个解释:
- 满足
SELECT字句中的列名必须为分组列或者列函数
.
也就是说: 要么是group by 中用到了列,要么就是带有max这类的列函数的列.
注意: 上面的条件只针对同一张表成立,如果用到了多张表,就算列名不满足条件也是正确的,具体看例子2
- 列函数对于group by子句定义的每个组各返回一个结果.
GROUP BY使用案例:
数据库表的关系图如图:
student表通过student_id
与score表相连
course表通过course_id
与score表相连
student表结构:
score表结构:
course表结构:
问题1: 查询所有同学的学号,选课数, 成绩,按学号分组
select
student_id,count(course_id), sum(score)
from score
group by student_id;
这条语句刚好满足了group by需要的两个条件.
问题2: 查询所有同学的学号,姓名, 选课数, 总成绩,按学号分组
select
s.student_id,stu.name, count(s.course_id), sum(s.score)
from
score s,
student stu
where
s.student_id = stu.student_id
group by s.student_id;
注意where语句一定要写在group by前面
这条语句刚好不满足group by第一个条件中的列名必须被group by使用或者使用了列函数
,但是一样成功了,因为他们不只有一张表.
HAVING
- 通常与GROUP BY子句一起使用,在GROUP BY子句后指定过滤的条件,
如果省略了GROUP BY子句,HAVING子句就和WHERE子句一样,HAVING支持所有WHERE操作符
. - WHERE过滤行,HAVING过滤组.
- 在同一个sql里出现的顺序: WHERE > GROUP BY > HAVING
HAVING使用案例:
还是使用上面的三张表
问题1: 查询平均成绩大于60分的同学的学号和平均成绩,按学号排序
select s.student_id, avg(s.score)
from score s
group by s.student_id
having avg(s.score) > 60;>
问题2: 查询srudent_id为1的学生的成绩情况
select s.student_id, avg(s.score), sum(s.score)
from score s
having student_id = 1;
这个语句完全等于
select s.student_id, avg(s.score), sum(s.score)
from score s
where student_id = 1;
问题3: 查询没有学全所有课的同学的学号,姓名,按学号分组
select stu.student_id, stu.name
from student stu,score sc
where stu.student_id = sc.student_id
group by stu.student_id
having count() < (select count() from course)
问题4: 查询平均成绩大于60分的同学的学号和平均成绩
统计相关的函数: COUNT(求总数), SUM(求和), MAX(求最大值), MIN(求最小值), AVG(求平均值)