基本信息Student(`S#`,Sname,Sage,Ssex) 学生表Course(`C#`,Cname,`T#`) 课程表SC(`S#`,`C#`,score) 成绩表Teacher(`T#`,Tname) 教师表问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.`S#` from (select `S#`,score from SC where `C#`='001') a,(select `S#`,scorefrom SC where `C#`='002') b where a.score>b.score and a.`S#`=b.`S#`;↑一张表中存在多对多情况的2、查询平均成绩大于60分的同学的学号和平均成绩；答案一：select `S#`,avg(score) from sc group by `S#` having avg(score) >60;↑一对多，对组进行筛选答案二：SELECT s ,scrFROM (SELECT sc.`S#` s,AVG(sc.`score`) scr FROM sc GROUP BY sc.`S#`) rsWHERE rs.scr>60 ORDER BY rs.scr DESC↑嵌套查询可能影响效率3、查询所有同学的学号、姓名、选课数、总成绩；答案一：select Student.`S#`,Student.Sname,count(`C#`),sum(score) from Student left Outer join SC on Student.`S#`=SC.`S#` group by Student.`S#`,Sname↑如果学生没有选课，仍然能查出，显示总分null（边界情况）答案二：SELECT student.`S#`,student.`Sname`,COUNT(sc.`score`) 选课数,SUM(sc.`score`) 总分FROM Student,scWHERE student.`S#`=sc.`S#` GROUP BY sc.`S#`↑如果学生没有选课，sc表中没有他的学号，就查不出该学生，有缺陷！4、查询姓“李”的老师的个数；select count(distinct(Tname)) from Teacher where Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；select Student.`S#`,Student.Sname from Student where `S#` not in (select distinct(SC.`S#`) from SC,Course,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平');↑反面思考Step1：先找学过叶平老师课的学生学号，三表联合查询Step2：在用not in 选出没学过的Step3：distinct以防叶平老师教多节课；否则若某同学的几节课都由叶平教，学号就会出现重复6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` and SC.`C#`='001'and exists( Select * from SC as SC_2 where SC_2.`S#`=SC.`S#` and SC_2.`C#`='002' );↑注意目标字段`S#`关联exists subquery 可以用in subquery代替，如下select Student.`S#`,Student.Sname from Student,Sc where Student.`S#`=SC.`S#` and SC.`C#`='001'and sc.`s#` in ( select sc_2.`s#` from sc as sc_2 where sc_2.`c#`='002' );↑不同之处，in subquery此处就不需要关联了7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；select `S#`,Sname from Studentwhere `S#` in(select `S#` from SC ,Course ,Teacher where SC.`C#`=Course.`C#` and Teacher.`T#`=Course.`T#` and Teacher.Tname='叶平' group by `S#`having count(SC.`C#`)=(select count(`C#`) from Course,Teacher where Teacher.`T#`=Course.`T#` and Tname='叶平'));8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；（太混乱）Select `S#`,Sname from (select Student.`S#`,Student.Sname,score ,(select score from SC SC_2 where SC_2.`S#`=Student.`S#` and SC_2.`C#`='002') score2 from Student,SC where Student.`S#`=SC.`S#` and `C#`='001') S_2 where score2 <score;自己写的另一种方法：SELECT student.`S#`,student.Sname FROM studentWHERE `S#` IN( SELECT a.`S#` FROM(SELECT * FROM sc WHERE `C#`='001') a ,(SELECT * FROM sc WHERE `C#`='002') bWHERE a.score>b.score AND a.`S#`=b.`S#`) ;↑子查询的应用方式与第1题类似，在一对多关系表中，如果多组之间需要比较，可以将不同组抽出为几个子查询，再比较。

这里的“一”指课程编号。

9、查询所有课程成绩小于60分的同学的学号、姓名；↓初始答案（效率最低）：select `S#`,Sname from Student where `S#` not in (select Student.`S#` from Student,SC where Student.`S#`=SC.`S#` and score>60); （第二个select根本不需要联合查询）↓改进简化版（效率更高）：select `S#`,Sname from Studentwhere `S#` not in(select distinct `S#` from SC where score>60); （从反面思考更简化）↓自己写的另一种方法（效率其次，但有缺陷。

边界情况：没有学任何课程的人，查不出来）：SELECT Student.`S#`,Student.Sname FROM StudentWHERE `S#` IN(SELECT `S#` FROM sc GROUP BY `S#` HAVING MAX(score)<60);In 和not in 去构造，有时候查出来的结果并不一样，需要考虑目标字段`S#`是否在几个表中都有10、查询没有学全所有课的同学的学号、姓名；select Student.`S#`,Student.Sname from Student,SC where Student.`S#`=SC.`S#` group by Student.`S#`,Student.Sname having count(`C#`) <(select count(`C#`) from Course);↑有缺陷，没有选任何课的人查不出来。

因为使用了关联查询，若存在关联不上的（一张表有，另一张表没有），就会遗漏。

select student.`s#`,student.sname from studentwhere student.`s#` not in(select `s#` from sc group by `s#` having count(`c#`) = (select count(`c#`) from course));↑可以查出没有选任何课的人，单表查询操作，不涉及关联。

11、查询至少有一门课与学号为“P1001”的同学所学相同的同学的学号和姓名；select DISTINCT Student.`S#`,Sname from Student,SC where Student.`S#`=SC.`S#` and `C#` in (select `C#` from SC where `S#`='P1001') ; (存在性用in即可）↑没有排除自身，↓把结果中的P1001自己去掉12、查询至少学过学号为“P1001”同学所有一门课的其他同学学号和姓名；select distinct SC.`S#`,Sname from Student,SC where Student.`S#`=SC.`S#` and `C#` in (select `C#` from SC where `S#`='001') AND Student.`s#` != 'P1001';←(绿色为补充，排除P1001本身)13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；（有错误，很混乱）update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.`C#`=SC.`C#` ) from Course,Teacher where Course.`C#`=SC.`C#` and Course.`T#`=Teacher.`T#` and Teacher.Tname='叶平'); mysql报错，可能其他数据库能这么写题目特点，把一张表的值查出来再插到这张表中，但实际不允许，会报错。