数据库的分组查询和统计查询
在group by之后不能使用where，只能使用having，在group by之前可以使用where，即表示对过滤后的结果分组
select sname,sum(score) from s_k group by sname
select count(distinct sname) from s_k
select sname,avg(score) from s_k group by sname
select kname,avg(score),max(score),min(score) from s_k group by kname
select avg(score),max(score),min(score) from s_k where kname='语文'
select sname,sum(score),avg(score) from s_k group by sname
select * from s_k;
select kname,avg(score) avgs from s_k group by kname
一. 使用聚集函数：
1． 查询学生总人数：
Select Count(*) as 学生总数 from student
2. 查询选修了课程的学生总数：
select count(distinct sno) as 选课学生总数 from sc
3. 查询所有课程的总学分数和平均学分数,以及最高学分和最低学分：
select sum(credit) as 总credit,avg(credit) as 课程平均学分,max(credit) as 最高学分,
min(credit) as 最低学分 from course
4. 计算1号课程的学生的平均成绩, 最高分和最低分:
select avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分
from scwhere cno='1'
5. 查询’信息系’(IS)学生”数据结构”课程的平均成绩:
select avg(grade) from student, course, sc where student.sno=sc.sno and
(select max(grade) from sc where sno=A.sno )
7*. 求成绩低于该门课程平均成绩的学生的成绩信息(sno,cno,grade)
select * from grade A where grade=
(select avg(grade) from sc where cno=Ao )
二. 分组查询
8. 查询各系的学生的人数并按人数从多到少排序 :
selectsdept, Count(*) as 人数 from student group by sdept order by 人数 desc
9. 查询各系的男女生学生总数, 并按系别,升序排列, 女生排在前:
select sdept,ssex,Count(*) as 人数 from student group by sdept, ssex order by sdept,ssex desc
10. 查询选修了3门课程已上的学生的学号和姓名:
select distinct fromselect sno, sname from student where sno in
(select sno from sc group by (sno) having count(*)>3)
1选课门数:
select sno, avg(grade) as 平均成绩,max(grade) as 最高分, min(grade) as 最低分,
count(*) as 选课门数 from sc group by sno
12. 查询至少选修了2门课程的学生的平均成绩:
select sno, avg(grade) as 平均成绩, from sc group by sno having count(*)>=2
13. 查询平均分超过80分的学生的学号和平均分:
Select sno, avg(grade) as 平均成绩from sc group by sno having avg(*)>=80
比较: 求各学生的60分以上课程的平均分:
select sno, avg(grade) as 平均成绩 from sc where grade>=60 group by sno
14. 查询”信息系”(IS)中选修了5门课程以上的学生的学号:
select sno from sc where sno in (select sno from student where sdept='IS') group by sno having count(*)>=2
三. 集合查询
15. 查询数学系和信息系的学生的信息;
select * from student where sdept=’MA’ union select * from student where sdept='IS'
16. 查询选修了1号课程或2号课程的学生的学号:
select sno from sc where cno='1'
Union
select sno from sc where cno='2'
比较实验三之3.

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。