2018-11-26多表连接嵌套

/*
Course（课程表） 表结构 CID(课程编号),Cname（课程名）,TID(老师编号)
SC(成绩表) 表结构 SID(学生编号),CID(课程编号),score(成绩)
Student(学生表) 表结构 SID(学生编号),,Sage(学生出生年月),Ssex(学生性别)
Teacher(老师表) 表结构 TID(老师编号),Tname(老师姓名)

*/

-- 1、查询出所有学生的姓名
SELECT sname FROM Student;

-- 2、查询出所有学生的姓名和性别
SELECT Sname,Ssex FROMStudent;

-- 3、查询出学生表全部信息
SELECT * FROMStudent;

-- 4、查询出成绩大于80分的学生编号，成绩
SELECT SID,score FROM SC WHERE score>'80';

-- 5、查询出成绩等于80分的学生编号，成绩
SELECT SID,score FROM SC WHERE score='80';

-- 6、查询出成绩不超过80分的学生编号，成绩
SELECT SID,score FROM SC WHERE score<='80';

-- 7、查询出成绩不低于80分的学生编号，成绩
SELECT SID,score FROM SC WHERE score>='80';

-- 8、查询出成绩低于80分的学生编号，成绩
SELECT SID,score FROM SC WHERE score<'80';

-- 9、查询出成绩在60到80分之间的学生编号，成绩
SELECT SID,score FROMSC WHERE score BETWEEN 60 AND 80;

-- 10、查询出姓王的学生
SELECT * FROM Student WHERE Sname LIKE '王%';

-- 11、查询出姓名中包含兰字的学生
SELECT * FROMStudent WHERE Sname LIKE'%兰%';

-- 12、查询出姓名中第二个字为风字的学生
SELECT Sname FROMStudent WHERE Sname LIKE'_风%';

-- 13、查询出不姓王的女学生
SELECT * FROM Student WHERE Sname NOT LIKE '王%'AND Ssex='女';
-- 14、查询出成绩不在60到80分之间的学生
SELECT * FROMSC WHERE score NOT BETWEEN 60 AND 80;
-- 15、查询出成绩为80分或者60分的学生编号并对学生编号去重
SELECT DISTINCT,SID FROMSC WHERE score=60 OR score=80;
-- 16、查询出成绩不为60分和80分的学生，并对学生编号去重
SELECT DISTINCT ,SID FROMSC WHERE score= '60' AND score=80;
-- 17、查询出学生编号01的学生，成绩高于80分的课程编号
SELECT CID FROMSC WHERE SID='01'AND score>80;
-- 18、查询出所有学生的成绩并按照从高到低的顺序排序
SELECT SID,score FROMSC WHERE SID ORDER BY score DESC;
-- 19、查询出所有学生信息，并按照出生日期从远及近排序
SELECT * FROMStudent WHERE SID ORDER BY Sage ASC;
-- 20、查询出所有学生的成绩，取成绩最高的三条记录
SELECT * FROMSCWHERE SID ORDER BY score DESC LIMIT 3;
-- 21、查询出所有学生的成绩，取3到5条记录
SELECT * FROM SCWHERE score LIMIT 2,3;

-- 22、统计出所有学生的总成绩
SELECT SUM(score)FROMSC ;

-- 23、统计出所有学生的平均成绩
SELECT AVG(score)FROMSC;

-- 24、统计出所有学生的最高成绩
SELECT MAX(score)FROMSC;

-- 25、统计出所有学生的最低成绩
SELECT MIN(score)FROMSC;

-- 26、统计出成绩表中共有多少条记录
SELECT COUNT(score)FROMSC

-- 27、统计每个学生的总成绩
SELECT SID ,SUM(score) FROMSCGROUP BY SID ;

-- 28、统计每个学生的平均成绩
SELECT SID,AVG(score)FROMSCGROUP BY SID;

-- 29、统计每个学生的最高成绩
SELECT SID,MAX(score)FROMSCGROUP BY SID;

-- 30、统计每个学生的最低成绩
SELECT SID,MIN(score)FROMSCGROUP BY SID;

-- 31、统计每门课及格的学生数
SELECT SID,COUNT(*) FROMSCWHERE score >=60 GROUP BY CID;

-- 32、统计每门课都及格的学生编号
SELECT sid,MIN(score) FROM SC s GROUP BY sid HAVING MIN(score) >=60;

-- 33、查询出名字叫周梅每门课的成绩
SELECT stu.Sname,c.Cname,s.score FROM Student stu INNER JOIN SC s ON stu.SID=s.SID
INNER JOIN Course c ON s.CID=c.CID
WHERE stu.Sname='周梅'

-- 34、查询出李四教的所有学生
SELECT b.Tname,st.Sname FROM Course a
INNER JOIN Teacher b ON a.TID=b.TID
INNER JOIN SC s ON s.CID=a.CID
INNER JOIN Student st ON st.SID=s.SID
WHERE b.tname='李四';

-- 35、查询出李四老师教的课程的平均成绩
SELECT t.Tname,AVG(s.score) FROM Teacher t INNER JOIN Course c ON t.TID=c.TID
INNER JOIN SC s ON c.CID=s.CID
WHERE t.Tname='李四';

SELECT a.Tname,b.‘Cname’ AVG(c.score)
FROM teacher a,course b,Sc c
WHERE a.TID=b.TID AND b.CID=c.CID AND a.Tname='李四'
GROUP BY b.CID;

-- 36、查询出每个老师教的课程的平均成绩
SELECT Tname,AVG(c.score)
FROM Teachera,Studentb,SCc,Coursed
WHERE a.TID=d.TID AND d.CID=c.CID AND c.SID=b.SID
GROUP BY a.Tname;

-- 37、查询出每个老师教的学生人数
SELECT Tname,COUNT(c.score)
FROM Teachera,Studentb,SCc,Coursed
WHERE a.TID=d.TID AND d.CID=c.CID AND c.SID=b.SID
GROUP BY a.TID;

-- 38、查询出选课人数不低于2人的课程和老师姓名
SELECT COUNT(c.score),Cname,Tname
FROM Teachera,Studentb,SCc,Coursed
WHERE a.TID=d.TID AND d.CID=c.CID AND c.SID=b.SID
GROUP BY a.Tname
HAVING COUNT(c.score)>=2;

SELECT a.Tname,COUNT()
FROM Student a,Course b,SC c
WHERE a.TID= b.TID AND b.CID=c.CID
GROUP BY a.TID
HAVING COUNT()>=2;

-- 39、查询出总分最高的课程及老师姓名
SELECT SUM(c.score),Tname
FROM Teachera,Studentb,SCc,Coursed
WHERE a.TID=d.TID AND d.CID=c.CID AND c.SID=b.SID
GROUP BY c.CID
ORDER BY SUM(c.score) DESC
LIMIT 1;

SELECT a.Tname SUM(c.score)
FROM teacher a,course b, Sc c
WHERE a.TID=b.TID AND b.CID=c.CID
GROUP BY a.TID,b.Cname
ORDER BY SUM(c.score)
LIMIT 1;

-- 40、查询平均成绩最高的学生姓名
SELECT AVG(c.score),Sname
FROM Teachera,Studentb,SCc,Coursed
WHERE a.TID=d.TID AND d.CID=c.CID AND c.SID=b.SID
GROUP BY a.TID
ORDER BY SUM(c.score) DESC
LIMIT 1;

SELECT a.Sname,AVG(b.score)
FROM Student a,SC b
WHERE a.SID=b.SID
GROUP BY a.SID
ORDER BY AVG(b.score)DESC
LIMIT 1;

/*
Course（课程表） 表结构 CID(课程编号),Cname（课程名）,TID(老师编号)
SC(成绩表) 表结构 SID(学生编号),CID(课程编号),score(成绩)
Student(学生表) 表结构 SID(学生编号),,Sage(学生出生年月),Ssex(学生性别)
Teacher(老师表) 表结构 TID(老师编号),Tname(老师姓名)

*/
-- 1.查询出每个老师教的所有课程和学生
SELECT a.Tname,b.Cname,d.Sname
FROM Teacher a,Course b,SC c,Student d
WHERE a.TID=b.TID AND b.CID=c.CID AND c.SID=d.SID
ORDER BY a.TID

-- 2.查询平均成绩最高的学生姓名(两表关联)
SELECT a.Sname,AVG(b.score)
FROM Student a JOIN SC b ON a.SID =b.SID
GROUP BY a.SID
ORDER BY AVG(b.score)DESC
LIMIT 1;

-- 3.查询出总分最高的课程及老师姓名（三表关联）
SELECT a.Tname, b.Cname,SUM(c.score)
FROM Teacher a JOIN Course b ON a.TID=b.TID JOIN SC c ON b.CID=c.CID
ORDER BY SUM(c.score) DESC
LIMIT 1;

-- 4.查询出每个老师教的所有课程和学生
SELECT a.Tname, b.Cname,d.Sname
FROM Teacher a JOIN Course b ON a.TID=b.TID JOIN SC c ON b.CID=c.CID JOIN Student d ON c.SID=d.SID
ORDER BY a.TID;

-- 6、查询出选课表中，选修了课程的学生
SELECT DISTINCT SID FROM SC
SELECT *
FROMStudent
WHERE SID IN(SELECT DISTINCT SID FROM SC);

-- 7、查询出成绩最高的学生信息
SELECT MAX(score) FROM SC
SELECT SID FROM SC WHERE score=99
SELECT * FROM Student WHERE SID IN ('01','04');
SELECT * FROM SC WHERE score IN (SELECT MAX(score) FROM SC);