{access数据库}查询典型语句

数据库的分组查询和统计查询

在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=https://www.360docs.net/doc/e211988814.html,o )

二. 分组查询

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 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.