MySQL（学生表、教师表、课程表、成绩表）多表查询
1、表架构
student(sid,sname,sage,ssex) 学生表
course(cid,cname,tid) 课程表
sC(sid,cid,score) 成绩表
teacher(tid,tname) 教师表
2、建表sql语句
1 CREATE TABLE student 2 ( 3 sid INT PRIMARY KEY NOT NULL, 4 sname VARCHAR(30), 5 sage INT, 6 ssex VARCHAR(8) 7 ) 8 9 CREATE TABLE course 10 ( 11 cid INT PRIMARY KEY NOT NULL, 12 cname VARCHAR(30), 13 tid INT 14 ) 15 16 CREATE TABLE sc 17 ( 18 sid INT NOT NULL, 19 cid INT NOT NULL, 20 score INT 21 ) 22 23 CREATE TABLE teacher 24 ( 25 tid INT PRIMARY KEY NOT NULL, 26 tname VARCHAR(30) 27 )
3、问题：
（1）查询“30001”课程的所有学生的学号与分数；
SELECT sid,score FROM sc WHERE cid="30001"
（2）查询“001”课程比“002”课程成绩高的所有学生的学号与分数；
SELECT a.sid,a.score FROM (SELECT sid,score FROM sc WHERE cid="30001") a, (SELECT sid,score FROM sc WHERE cid="30002") b WHERE a.score>b.score AND a.sid=b.sid
（3）查询平均成绩大于60分的同学的学号和平均成绩；
SELECT sid,AVG(score)FROM scGROUP BY sid HAVING AVG(score)>60
（4）查询所有同学的学号、姓名、选课数、总成绩
SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数目", SUM(sc.score) AS "总分数"FROM student s, sc scWHERE s.sid=sc.sidGROUP BY s.sid
（5）查询姓“李”的老师的个数；
select count(distinct(Tname)) from teacher where tname like '李%';
（6）查询学过“张三”老师课的同学的学号、姓名
SELECT s.sid AS "学号", s.sname AS "姓名"FROM student s, sc sc, course c, teacher tWHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三"
（7）查询没有学过“张三”老师课的同学的学号、姓名
SELECT s.sid, s.snameFROM student sWHERE s.sid NOT IN (SELECT s.sidFROM student s, sc sc, course c, teacher tWHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid AND t.tname="张三")
（8）查询学过“30001”并且也学过编号“30002”课程的同学的学号、姓名
SELECT s.sid, s.snameFROM student s, sc scWHERE s.sid=sc.sid AND sc.cid="30001" AND EXISTS( SELECT * FROM sc AS sc2 WHERE sc2.sid=sc.sid AND sc2.cid="30002")
（9）查询学过“叶平”老师所教的所有课的同学的学号、姓名；
SELECT sid, snameFROM studentWHERE sid IN (SELECT sc.sidFROM sc sc, course c, teacher tWHERE sc.cid=c.cid AND c.tid=t.tid AND t.tname="张二")
（10）查询所有课程成绩小于60分的同学的学号、姓名
SELECT sid, sname FROM studentWHERE sid NOT IN (SELECT DISTINCT(sc.sid) FROM student s, sc scWHERE sc.sid=s.sid AND sc.score>60)
（11）查询没有学全所有课的同学的学号、姓名；
SELECT sid, sname FROM student WHERE sid NOT IN(SELECT s.sid FROM student s, sc scWHERE sc.sid=s.sidGROUP BY s.sidHAVING COUNT(sc.cid)=(SELECT COUNT(cid) FROM course))
（12）查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT cid AS "课程ID", MAX(score) AS "最高分", MIN(score) AS "最低分"FROM scGROUP BY cid
（13）按各科平均成绩从低到高和及格率的百分数从高到低顺序
(方式一)SELECT sc.cid AS "课程ID",c.cname AS "课程名", AVG(sc.score) AS "平均成绩",SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS "及格百分数"FROM sc sc, course cWHERE sc.cid=c.cidGROUP BY sc.cidORDER BY AVG(sc.score) ASC,SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 DESC(方式二)SELECT sc.cid AS "课程ID",c.cname AS "课程名", IFNULL(AVG(sc.score),0) AS "平均成绩",100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS "及格百分数"FROM sc sc, course cWHERE sc.cid = c.cidGROUP BY sc.cidORDER BY AVG(sc.score) ASC,100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
（14）查询所有学生的所有科目的成绩单（学号、姓名、语文、数学、英语、物理、平均分、总分（按照总分由高到低排序））
SELECT s.sid AS "学号", s.sname AS "姓名",SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",IFNULL(AVG(sc.score),0) AS "平均分",IFNULL(SUM(sc.score),0) AS "总分"FROM student sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BY s.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESC
（15）查询总分排名在200-300（包含200和第300）之间的学生所有成绩单信息
SELECT s.sid AS "学号", s.sname AS "姓名",SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",IFNULL(AVG(sc.score),0) AS "平均分",IFNULL(SUM(sc.score),0) AS "总分"FROM student sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BY s.sid, s.snameHAVING IFNULL(SUM(sc.score),0) BETWEEN 200 AND 300ORDER BY IFNULL(SUM(sc.score),0) DESC
（16）查询总分排名在前四名的学生所有成绩单信息
SELECT s.sid AS "学号", s.sname AS "姓名",SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",IFNULL(AVG(sc.score),0) AS "平均分",IFNULL(SUM(sc.score),0) AS "总分"FROM student sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BY s.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESCLIMIT 0,4
（17）查询总分排名在前二名到四名的学生所有成绩单信息（limit 1,3表示从第二条数据开始，连续三条数据）
SELECT s.sid AS "学号", s.sname AS "姓名",SUM(CASE c.cname WHEN "语文" THEN sc.score ELSE 0 END) AS "语文",SUM(CASE c.cname WHEN "数学" THEN sc.score ELSE 0 END) AS "数学",SUM(CASE c.cname WHEN "英语" THEN sc.score ELSE 0 END) AS "英语",SUM(CASE c.cname WHEN "物理" THEN sc.score ELSE 0 END) AS "物理",IFNULL(AVG(sc.score),0) AS "平均分",IFNULL(SUM(sc.score),0) AS "总分"FROM student sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BY s.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESCLIMIT 1,3
（18）查询学生平均成绩及其名次
(非本人)SELECT 1+(SELECT COUNT( distinct 平均成绩)FROM (SELECT sid,AVG(score) AS 平均成绩FROM scGROUP BY sid ) AS T1WHERE 平均成绩 > T2.平均成绩) as 名次, S# as 学生学号,平均成绩FROM (SELECT sid,AVG(score) 平均成绩FROM sc GROUP BY sid ) AS T2ORDER BY 平均成绩 desc
赞 (0)