承接上篇，MARK一下多表查询。
有个常犯的错误要特别注意：
完全相同， 不是In 而是not in的思路，详见第7题。
-- 多表查询：
-- 1.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
-- 2表查询+返回值(原始字段+计算值)
-- 两表查询：student, sc；返回值:s.sid,s.sname.avg(sc.score)
-- note： 筛选条件：分组后用having avg(sc.score)>85 而不是where
select s.sid,s.sname,avg(score) from sc
inner join student s on sc.sid=s.sid
group by s.sid ,s.sname having avg(sc.score)>85
-- 2.查询课程名为“睡觉”，且分数低于60的学生姓名和分数
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course； 返回值: s.sname,sc.score
-- note：关联查询：inner join ..on
-- 条件直接堆后面
select s.sname,sc.score from studnet s inner join sc on s.sid=sc.sid
inner join course c on s.cid=c.cid
where c.cname='睡觉'
and sc.score<60
-- 3.查询所有学生的选课情况
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course； 返回值:s.sname,c.cname
-- student 全集，
-- note： 关联查询：student 全集 所以left join
select s.sname,c.cname from student s
left join sc on s.sid=sc.sid
left join course c on sc.cid=c.cid
-- 4.查询任何一门课程成绩在70分以上的姓名、课程名称和分数
-- 3表查询+返回值(原始字段)
-- 3表查询 student,sc,course; 返回值:s.sname,c.cname,sc.score
-- note: 关联查询：inner join ..on
-- 筛选条件： where sc.score>70
select s.sname,c.cname,sc.score from student s
inner join sc on s.sid=sc.sid
inner join course c on sc.cid=c.cid
where sc.score>70
-- 5.查询每门课程被选修的学生数
-- 2表查询+返回值(原始字段，计算字段)
-- 2表查询 sc,course； 返回值: c.cname,count（*）(计算)
-- note：关联查询 inner join ..on
-- 分组：每门课 group by c.cname(或者c.cid只要唯一标识就好)
select c.cname ,count(*) from course c
inner join sc sc on c.cid=sc.cid
group by c.cname
-- 6.查询不同老师所教不同的课程平均分从高到低显示
-- 3表查询+返回值(原始字段，计算字段)
-- 3表查询 sc,course,teacher；返回值: t.tname,c.cname,avg(sc.score)
-- note： 关联查询 inner join
-- 分组：每门课 group by c.cname,t.tname
-- 从高到低显示:order by
select t.tname,c.cname,avg(sc.score) from teacher t
inner join course c on t.tid=c.tid
inner join sc on c.cid=sc.cid
group by t.tname,c.cname
-- 7. 查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名
-- 2表查询+返回值(原始字段)
-- 2表查询： student sc; 返回值: s.sid,s.sname
-- note ：完全相同的科目名+相同的数量+其它同学
-- not in 而不是in
-- count(*)=count()
-- ！=
-- 1.科目名 科目名学过和没学过两种情况
-- 1.1.2号同学学习的课程 select sc.cid from sc where sc.cid=2 返回课程id
-- 1.2.学过的同学 select sc.sid from sc where sc.cid in(select sc.cid from sc where sc.cid=2) 括号里为课程id
-- 1.3.没学过的同学 select sc.sid from sc where sc.cid not in(select sc.cid from sc where sc.cid=2)
-- 1.4.把没学过的摘出去
-- select sc.sid from sc where sc.cid not in(
-- select sc.sid from sc where sc.cid not in(select sc.cid from sc where sc.cid=2))
-- 2.数量 按这个条件分组
-- group by sc.sid having count(*)=select count(*) from sc where sc.sid=2
-- select * from sc where sc.cid not in(
-- select sc.sid from sc where sc.cid not in(
-- select sc.cid from sc where sc.sid=2))
-- group by sc.sid having count(*)=（select count(*) from sc where sc.sid=2）
-- 3.其他人sc.sid!=2
select s.sid ,s.sname from student s where s.sid in(
select * from sc where sc.cid not in(
select sc.sid from sc where sc.cid not in(
select sc.cid from sc where sc.sid=2)
)and sc.sid!=2
group by sc.sid having count(*)=（select count(*) from sc where sc.sid=2）
)
-- 9.查询学过“黄观”老师所教的所有课的同学的学号、姓名
-- 3表查询+返回值(原始字段)
-- 3表查询： student ,teacher,sc,course 返回值: s.sid,s.sname
-- note ：
-- 1.黄观老师教的所有课(2表关联 inner join in
-- 筛选 where )
-- select c.cid from course c
-- inner join teacher t on c.tid=t.tid
-- where t.tname='黄观
-- 2.学过课的学生（3表关联 inner join in
-- 筛选 where
-- 子查询
-- select sc.sid,sc.cid from sc where sc.cid in (
-- select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname='黄观')
-- 4.数量 老师教课的数量count(c.cid)
-- select count(c.cid) from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'
-- 学生学的课 数量 学生分组 计数 group by sc.sid having count(*)
-- 两数量一样
select sc.sid from sc where sc.cid in(
select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname=‘黄观‘)
group by sc.sid having count(*)= (select count(*) from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'
-- 10.查询没学过“黄观”老师课的同学的学号、姓名
-- 4表查询+返回值(原始字段)
-- A."黄观"老师教过什么课 两表联合查询（t,c） 返回的是课程 c.id
-- select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'
-- B.谁学过这些课（学生，课程， 三表查询(s,sc,c )， 学过什么课 所以条件: sc.cid)
select sc.sid from sc where sc.cid in(
select c.cid from course c inner join teacher t on c.tid=t.tid -- 前面查询返回的结果
where t.tname='黄观'）
-- c.学生没学过 not in
-- 此处为什么不可以直接在B的基础上直接把in 改成 not in ? 因为你要返回的是student的表的字段，而不是 sc表的字段 --
select * from student where sid not in(
select sc.sid from sc where sc.cid in(
select c.cid from course c inner join teacher t on c.tid=t.tid where t.tname='黄观'))
备注：数据表和题目是网上找来，注释和总结是我写的，希望对大家写sql时有所启发与帮助。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
