mysql查询学⽣平均成绩及其名次_MySQL（学⽣表、教师表、课程表、成绩表）多表查询...
1、表架构
student(sid,sname,sage,ssex) 学⽣表
course(cid,cname,tid) 课程表
sC(sid,cid,score) 成绩表
teacher(tid,tname) 教师表
2、建表sql语句
1 CREATE TABLEstudent
2 (
3 sid INT PRIMARY KEY NOT NULL,
4 sname VARCHAR(30),
5 sage INT,
6 ssex VARCHAR(8)
7 )8
9 CREATE TABLEcourse10 (11 cid INT PRIMARY KEY NOT NULL,12 cname VARCHAR(30),13 tid INT
14 )15
16 CREATE TABLEsc17 (18 sid INT NOT NULL,19 cid INT NOT NULL,20 score INT
21 )22
23 CREATE TABLEteacher24 (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") bWHERE a.score>b.score AND a.sid=b.sid
(3)查询平均成绩⼤于60分的同学的学号和平均成绩；
SELECT sid,AVG(score)FROMscGROUP BY sid HAVING AVG(score)>60
(4)查询所有同学的学号、姓名、选课数、总成绩
SELECT s.sid AS "学号", s.sname AS "姓名", COUNT(sc.cid) AS "课程数⽬", SUM(sc.score) AS"总分数"FROMstudent s, sc scWHERE s.sid=sc.sidGROUP BY s.sid
(5)查询姓“李”的⽼师的个数；
select count(distinct(Tname))fromteacherwhere tname like '李%';
(6)查询学过“张三”⽼师课的同学的学号、姓名
SELECT s.sid AS "学号", s.sname AS"姓名"FROMstudent s, sc sc, course c, teacher tWHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid ame="张三"
(7)查询没有学过“张三”⽼师课的同学的学号、姓名
SELECTs.sid, s.snameFROMstudent sWHERE s.sid NOT IN(SELECTs.sidFROMstudent s, sc sc, course c, teacher tWHERE s.sid=sc.sid AND c.cid=sc.cid AND t.tid=c.tid ame="张三"
)
(8)查询学过“30001”并且也学过编号“30002”课程的同学的学号、姓名
SELECTs.sid, s.snameFROMstudent 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)查询学过“叶平”⽼师所教的所有课的同学的学号、姓名；
SELECTsid, snameFROMstudentWHERE sid IN(SELECTsc.sidFROMsc sc, course c, teacher tWHERE sc.cid=c.cid AND
c.tid=t.tid ame="张⼆"
)
(10)查询所有课程成绩⼩于60分的同学的学号、姓名
SELECT sid, sname FROMstudentWHERE sid NOT IN(SELECT DISTINCT(sc.sid) FROMstudent s, sc scWHERE sc.sid=s.sid AND sc.score>60)
(11)查询没有学全所有课的同学的学号、姓名；
SELECT sid, sname FROMstudentWHERE sid NOT IN(SELECT s.sid FROMstudent s, sc scWHERE sc.sid=s.sidGROUP
BYs.sidHAVING COUNT(sc.cid)=(SELECT COUNT(cid) FROM course))
distinct查询(12)查询各科成绩最⾼和最低的分：以如下形式显⽰：课程ID，最⾼分，最低分
SELECT cid AS "课程ID", MAX(score) AS "最⾼分", MIN(score) AS"最低分"FROMscGROUP BY cid
(13)按各科平均成绩从低到⾼和及格率的百分数从⾼到低顺序
(⽅式⼀)SELECT sc.cid AS "课程ID",came AS "课程名", AVG(sc.score) AS"平均成绩",SUM(CASE WHEN sc.score > 60 THEN 1 ELSE 0 END)/COUNT(1)*100 AS"及格百分数"FROMsc sc, course cWHERE sc.cid=c.cidGROUP BYsc.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",came AS "课程名", IFNULL(AVG(sc.score),0) AS"平均成绩",100*SUM(CASE WHEN IFNULL(sc.score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS"及格百分数"FROMsc sc, course cWHERE sc.cid =c.cidGROUP BYsc.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 came WHEN "语⽂" THEN sc.score ELSE 0 END) AS"语
⽂",SUM(CASE came WHEN "数学" THEN sc.score ELSE 0 END) AS"数学",SUM(CASE came
WHEN "英语" THEN sc.score ELSE 0 END) AS"英语",SUM(CASE came WHEN "物理" THEN sc.score ELSE 0 END) AS"物理",
IFNULL(AVG(sc.score),0) AS"平均分",
IFNULL(SUM(sc.score),0) AS"总分"FROMstudent sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BYs.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESC
(15)查询总分排名在200-300(包含200和第300)之间的学⽣所有成绩单信息
SELECT s.sid AS "学号", s.sname AS"姓名",SUM(CASE came WHEN "语⽂" THEN sc.score ELSE 0 END) AS"语
⽂",SUM(CASE came WHEN "数学" THEN sc.score ELSE 0 END) AS"数学",SUM(CASE came WHEN "英语" THEN sc.score ELSE 0 END) AS"英语",SUM(CASE came WHEN "物理" THEN sc.score ELSE 0 END) AS"物理",
IFNULL(AVG(sc.score),0) AS"平均分",
IFNULL(SUM(sc.score),0) AS"总分"FROMstudent sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BYs.sid, s.snameHAVING IFNULL(SUM(sc.score),0) BETWEEN 200 AND 300
ORDER BY IFNULL(SUM(sc.score),0) DESC
(16)查询总分排名在前四名的学⽣所有成绩单信息
SELECT s.sid AS "学号", s.sname AS"姓名",SUM(CASE came WHEN "语⽂" THEN sc.score ELSE 0 END) AS"语
⽂",SUM(CASE came WHEN "数学" THEN sc.score ELSE 0 END) AS"数学",SUM(CASE came WHEN "英语" THEN sc.score ELSE 0 END) AS"英语",SUM(CASE came WHEN "物理" THEN sc.score ELSE 0 END) AS"物理",
IFNULL(AVG(sc.score),0) AS"平均分",
IFNULL(SUM(sc.score),0) AS"总分"FROMstudent sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BYs.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESCLIMIT0,4
(17)查询总分排名在前⼆名到四名的学⽣所有成绩单信息(limit 1,3表⽰从第⼆条数据开始，连续三条数据)
SELECT s.sid AS "学号", s.sname AS"姓名",SUM(CASE came WHEN "语⽂" THEN sc.score ELSE 0 END) AS"语
⽂",SUM(CASE came WHEN "数学" THEN sc.score ELSE 0 END) AS"数学",SUM(CASE came WHEN "英语" THEN sc.score ELSE 0 END) AS"英语",SUM(CASE came WHEN "物理" THEN sc.score ELSE 0 END) AS"物理",
IFNULL(AVG(sc.score),0) AS"平均分",
IFNULL(SUM(sc.score),0) AS"总分"FROMstudent sLEFT OUTER JOIN sc sc ON s.sid=sc.sidLEFT OUTER JOIN course c ON sc.cid=c.cidGROUP BYs.sid, s.snameORDER BY IFNULL(SUM(sc.score),0) DESCLIMIT1,3
(18)查询学⽣平均成绩及其名次
(⾮本⼈)SELECT 1+(SELECT COUNT( distinct平均成绩)FROM(SELECT sid,AVG(score) AS平均成绩FROMscGROUP BY sid ) AST1WHERE 平均成绩 > T2.平均成绩) as 名次, S# as学⽣学号，平均
成绩FROM (SELECT sid,AVG(score) 平均成绩FROM sc GROUP BY sid ) AST2ORDER BY 平均成绩 desc