【SQL】多表查询语句(嵌套子查询，多表连接)操作实例

背景知识： 一、连接：分成内连接和外连接，内连接相当于取交集，外连接相当于取并集 二、嵌套子查询：连接操作浪费资源，使用嵌套子查询可以避免连接同时加快执行速度，分成相关子查询和非相关子查询

方法不唯一，有问题欢迎留言讨论！代码块见文章末尾

select * from student select * from sc select * from course

– 求学号为‘20022037’的同学的每门课的成绩，输出格式为：学号，课程名，课程成绩 select sno,cname,grade from sc,course where sc.cno = course.cno and sno = ‘20022037’

select sno,cname,grade from sc inner join course on sc.cno = course.cno where sno = ‘20022037’

– 查询每个学生的每门课程的成绩，要求输出学号，课程名，成绩 select sno,cname,grade from sc,course where sc.cno = course.cno

select sno,cname,grade from sc inner join course on sc.cno = course.cno

– 查询每个学生的每门课程的成绩，要求输出学号，姓名，课程号，成绩 select student.sno,sname,cno,grade from sc,student where sc.sno = student.sno

select student.sno,sname,cno,grade from sc inner join student on sc.sno = student.sno

– 查询选修了’线性代数’课程的学生学号、姓名 select cname,sno,sname from student,course where cname = ‘线性代数’

–从选修’218801’课程的同学中，选出成绩高于’季莹’的学生的学号和成绩 select sno,grade from sc where cno = ‘218801’ and grade > ( select grade from sc where cno = ‘218801’ and sno = ( select sno from student where sname = ‘季莹’))

–查询成绩比该课程平均成绩低的学生成绩表 select sno,cno,grade from sc as a where grade < ( select avg(grade) from sc as b where a.cno = b.cno)

–H 查询所有学生都选修的课程名 select cname from course where not exists( select sno from student where not exists( select sno from sc where sc.sno = student.sno and sc.cno = course.cno ) ) select cname from course where cno in( select distinct cno from sc group by cno having count(distinct sno) in (select sno from sc) )

–查询选修了’线性代数’课程或’英语口语’课程的学生学号、姓名 select sno,sname from student where sno in ( select sno from sc where sno in ( select sno from course where cname = ‘线性代数’ or cname = ‘英语口语’) )

–用集合操作符 UNION 查询选修了’线性代数’课程或’英语口语’课程的学生学号、姓名 select sno,sname from student where sno in ( select sno from sc where sno in ( select sno from course where cname = ‘英语口语’) ) union select sno,sname from student where sno in ( select sno from sc where sno in ( select sno from course where cname = ‘线性代数’) )

–查询选修了’218801’课程但没有选修’216301’课程的学生学号。 select sno from sc where cno = ‘218801’ except select sno from sc where cno = ‘216301’ – 方法二 select sno from sc where cno = '218801’and sno not in( select sno from sc where cno = ‘216301’)

–求同时选修’218801’课程和’216301’课程的学生学号、姓名。,也可以使用多表查询 select sc.sno,sname from sc,student where sc.sno = ‘218801’ intersect select sc.sno,sname from sc,student where sc.sno = ‘216301’

–查询所有学生及其选课信息 select student.sno,sname,cno,grade from student left outer JOIN sc on student.SNO=sc.SNO

–创建课程平均分视图

–以列的方式统计每门课程的分数段人数。分数段为：不及格、60-70、70-80、80-90、90-100 (select cname,‘不及格’ AS fsd ,COUNT() AS rs FROM sc,course WHERE sc.CNO=course.CNO AND GRADE = all(select grade from sc)

–（7） 查询“线性代数”的所有授课班级的平均成绩，列出课程名和平均成绩 select avg(grade) as 平均成绩,cname from sc,course where cname = ‘线性代数’ group by cname

–（8） 查询“线性代数”成绩最高的前 5 名学生的姓名及成绩，结果按成绩降序 select top 5 sname,grade from sc,student order by grade desc

–（9） 查询学生“20002059”选修课程的总学分数 select sum(grade) as 总分数 from sc where cno = ‘20002059’

–（10） 对每个同学，查找其获得最高成绩的课程号 select sno,cno from sc as a where grade >= all(select grade from sc as b where a.sno = b.sno)