MySQL上机第三章-多表连接查询与普通子查询

一、上机目的

1、熟悉数据库的多表数据查询；

2、熟悉子查询中的普通子查询。

---

二、上机内容

（1）查询“王平”老师所讲授的课程，要求列出教师姓名、系别和课程号。（至少2种方法：① where+连接条件和选取条件; ②内连接 inner join..on..）

SELECT T.TN,T.Dept,TC.CNo  FROM T,TC WHERE T.TNo=TC.TNo AND T.TN LIKE('王平');

SELECT T.TN,T.Dept,TC.CNo FROM T JOIN TC ON T.TNo=TC.TNo WHERE T.TN LIKE('王平');

（2）查询“王平”老师所讲授的课程，要求列出教师姓名、系别、课程号和课程名。（同上，至少2种方法）

SELECT T.TN,T.Dept,TC.CNo,C.CN FROM T,TC,C WHERE T.TNo=TC.TNo AND C.CNo=TC.CNo AND T.TN LIKE('王平');

SELECT T.TN,T.Dept,TC.CNo,C.CN FROM T JOIN TC ON T.TNo=TC.TNo JOIN C ON C.CNo=TC.CNo WHERE TN LIKE('王平');

（3）查询所有选课学生的学号、姓名、选课名称及成绩。（未选课学生信息不显示，同上，至少2种方法）

SELECT S.Sno,S.SN,C.CN,SC.Score FROM S,SC,C WHERE S.Sno=SC.Sno AND C.CNo=SC.CNo;

SELECT S.Sno,S.SN,C.CN,SC.Score FROM SC JOIN S ON SC.Sno=S.Sno JOIN C ON SC.CNo=C.CNo;

（4）查询所有学生的学号、姓名、选课名称、课程号及成绩。（未选课学生选课信息显示为空，外连接left join..on..）

SELECT S.Sno,S.SN,C.CN,C.CNo,SC.Score FROM S LEFT JOIN SC ON SC.Sno=S.Sno LEFT JOIN C ON C.CNo=SC.CNo;

（5）查询每门课程的课程号、课程名、授课教师姓名及所在系别，并统计课程选课人数。（所有课程都有学生来选，所以分别用内连接和外连接2种方法来做）

SELECT SC.CNo,C.CN,T.TN,T.Dept,COUNT(SC.Sno)选课人数 FROM 
SC JOIN C ON SC.CNo=C.CNo JOIN 
TC ON TC.CNo = C.CNo JOIN
T  ON TC.TNo=T.TNo JOIN
S ON S.Sno=SC.Sno
GROUP BY C.CNo,T.TN;

SELECT SC.CNo,C.CN,T.TN,T.Dept,COUNT(SC.Sno)选课人数 FROM 
SC LEFT JOIN C ON SC.CNo=C.CNo LEFT JOIN 
TC ON TC.CNo = C.CNo LEFT JOIN
T  ON TC.TNo=T.TNo LEFT JOIN
S ON S.Sno=SC.Sno
GROUP BY C.CNo,T.TN;

（6）对教师信息表T和教师授课表TC进行交叉查询。

SELECT * FROM T CROSS JOIN TC;

（7）查询所有比“王平”老师年龄大的教师姓名、职称、系别，并显示王平老师姓名及年龄。（至少2种方法）

SELECT TN,Prof,Dept,Age FROM T WHERE Age > ANY(SELECT Age FROM T WHERE TN LIKE ('王平')) OR TN LIKE('王平');

SELECT A.TN,A.Prof,A.Dept,A.age FROM (SELECT TN,Prof,Dept,Age FROM T)
AS A INNER JOIN(SELECT TN,Prof,Dept,Age FROM T WHERE TN='王平')AS
B ON A.Age>B.Age; 

（8）查询每个学生选修课程超过他所选修所有课程平均成绩的课程，最后显示的字段为学号、课程号、课程名。

SELECT SC.Sno, SC.CNo, C.CN, SC.Score, T.Score
FROM SC
         RIGHT JOIN S ON SC.Sno = S.Sno
         LEFT JOIN
     (
         SELECT avg(SC.Score) Score, SC.Sno
         FROM SC
                  RIGHT JOIN S ON SC.Sno = S.Sno
         GROUP BY SC.Sno
     ) T ON SC.Score > T.Score AND SC.Sno = T.Sno
         RIGHT JOIN C ON C.Cno = SC.CNo
WHERE T.Score IS NOT NULL;

（9）查询每个学生选修课程超过他选修课程平均成绩的课程，并按照学号、课程号排序，最终显示字段为学号、姓名、课程号、课程名、成绩和平均成绩。

SELECT SC.Sno, SC.CNo, C.CN, SC.Score, T.Score
FROM SC
         RIGHT JOIN S ON SC.Sno = S.Sno
         LEFT JOIN
     (
         SELECT avg(SC.Score) Score, SC.Sno
         FROM SC
                  RIGHT JOIN S ON SC.Sno = S.Sno
         GROUP BY SC.Sno
     ) T ON SC.Score > T.Score AND SC.Sno = T.Sno
         RIGHT JOIN C ON C.Cno = SC.CNo
WHERE T.Score IS NOT NULL
ORDER BY C.CNo;

（10）查询讲授课程号为C5的教师姓名。（至少3种方法）

SELECT T.TN FROM T,TC WHERE T.TNo=TC.TNo AND TC.CNo='C5';

SELECT T.TN FROM T,TC WHERE T.TNo=TC.TNo AND TC.CNo IN ('C5');

SELECT T.TN FROM T JOIN TC ON TC.TNo=T.TNo WHERE TC.CNo='C5';

（11）查询其他系中比计算机系某一教师工资高的教师的姓名和工资。

SELECT TN,Sal FROM T WHERE Sal >ANY(SELECT Sal FROM T WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE('计算机');

（12）查询其他系中比计算机系所有教师工资都高的教师的姓名和工资。

SELECT TN,Sal FROM T WHERE Sal >All(SELECT Sal FROM T WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE('计算机');

（13）查询不讲授课程号为C5的教师姓名。

SELECT DISTINCT TN FROM T,TC WHERE T.TNo=TC.TNo AND TC.CNo NOT IN('C5');

（14）查询其他系中比计算机系某一个学生年龄小的学生姓名、年龄和系别。

SELECT SN,Age,Dept FROM S WHERE Age<ANY(SELECT Age FROM S WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE ('计算机');

（15）查询其他系中比计算机系所有学生年龄都小的学生姓名及年龄。

SELECT SN,Age,Dept FROM S WHERE Age<ALL(SELECT Age FROM S WHERE Dept LIKE ('计算机')) AND Dept NOT LIKE ('计算机');

---

此章节需要注意 有难度的题目为 第八题，第九题  此做法虽然复杂但是严谨  请仔细思考题目 也可以不用join left...join  用where

这里仅提供第八题的参考，代码如下

select X.SNo,X.CNo,C.CN from SC as X,C,        
 (select SNo,AVG(Score) as AVG from SC group by SNo)as Y
where X.CNo=C.CNo and X.SNo=Y.SNo and X.Score>Y.AVG

禁止抄袭。搬运需要著名出处，违者必究。