MYSQL经典面试题（50）

桃扇骨 2023-10-11 21:31 47阅读 0赞

student（学生表）

![de556d05df7d4994b9b6db211fc4b2b1.png][]学生表数据

![521c19f61ce84b9298ea801a49275fe5.png][]

course（课程表）

![890dc6bce7cc4f4ab28e0a33041b49cd.png][]

课程表数据

![9bad13d101214fc28324b1236e327fa5.png][]

sc（成绩表）

![5241f610ff0d407883f28027392a78ee.png][]

成绩表数据

![903e6d55f4a143a78fb9217433f27019.png][]

teacher（教师表）

![af68a73a288a42698ce1b72e83dd831b.png][]

教师表数据

![cb8deef3e53d44548c2ee635e65fe8cb.png][]

**\#1、查询“001”课程比“002”课程成绩高的所有学生的学号**  
SELECT s1.Sid 学号 FROM (SELECT Sid,Score FROM sc WHERE Cid=1) s1,  
(SELECT Sid,Score FROM sc WHERE Cid=2) s2 WHERE s1.Sid=s2.Sid AND s1.Score>s2.Score

**\#2、查询平均成绩大于60分的同学的学号和平均成绩**  
SELECT Sid 学号,AVG(score) 平均成绩 FROM sc GROUP BY Sid HAVING 平均成绩>60

**\#3、查询所有同学的学号、姓名、选课数、总成绩**  
SELECT s.Sid 学号,s.Sname 姓名,COUNT(sc.Cid) 选课数,SUM(sc.Score) 总成绩  
FROM student s LEFT JOIN sc ON s.Sid=sc.Sid GROUP BY s.Sid,s.Sname

**\#4、查询姓‘李’的老师的个数：**  
SELECT COUNT(tid) FROM teacher WHERE tname LIKE '张%';

**\#5、查询没有学过“叶平”老师课的同学的学号、姓名：**  
SELECT Sid,Sname FROM student  
WHERE sid NOT IN  
(SELECT DISTINCT sc.sid FROM sc,course,teacher  
WHERE sc.cid=course.cid AND course.tid=teacher.tid AND teacher.tname="张大仙")

**\#6、查询学过“叶平”老师所教的所有课的同学的学号、姓名：**  
SELECT Sid,Sname FROM student  
WHERE sid IN  
(SELECT DISTINCT sc.sid FROM sc,course,teacher  
WHERE sc.cid=course.cid AND course.tid=teacher.tid AND teacher.tname="张大仙")

**\#7、查询学过“011”并且也学过编号“002”课程的同学的学号、姓名：**  
SELECT s.Sid 学号,s.Sname 姓名 FROM student s RIGHT JOIN  
(SELECT DISTINCT sc.Sid FROM sc LEFT JOIN course c ON sc.Cid=c.Cid WHERE sc.Cid=1 OR sc.Cid=2) c  
ON s.Sid=c.Sid

**\#8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名：**  
SELECT s1.Sid 学号 FROM (SELECT Sid,Score FROM sc WHERE Cid=1) s1,  
(SELECT Sid,Score FROM sc WHERE Cid=2) s2 WHERE s1.Sid=s2.Sid AND s1.Score>s2.Score

**\#9、查询所有课程成绩小于60的同学的学号、姓名：**  
SELECT s.Sid 学号,s.Sname 姓名 FROM student s  
RIGHT JOIN (SELECT Sid FROM sc GROUP BY Sid HAVING SUM(Score)<60) sc  
ON s.Sid=sc.Sid

**\#10、查询没有学全所有课的同学的学号、姓名：**  
SELECT Sid,Sname FROM student s WHERE (SELECT COUNT(\*)  
FROM sc WHERE s.Sid=sc.Sid) < (SELECT COUNT(\*) FROM course)

**\#11、查询至少有一门课与学号为“001”同学所学相同的同学的学号和姓名：**  
SELECT DISTINCT s.Sid,s.Sname FROM student s  
LEFT JOIN sc ON s.Sid =sc.Sid WHERE sc.Cid IN  
(SELECT DISTINCT sc.Cid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid)

**\#12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；**  
SELECT DISTINCT s.Sid,s.Sname FROM student s  
LEFT JOIN sc ON s.Sid =sc.Sid WHERE sc.Cid IN  
(SELECT DISTINCT sc.Cid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid)

**\#13、把“SC”表中“吕德华”老师教的课的成绩都更改为此课程的平均成绩：**  
UPDATE sc INNER JOIN  
(SELECT Cid,AVG(Score) c FROM sc WHERE Cid IN  
(SELECT Cid FROM course c LEFT JOIN teacher t ON c.Tid=t.Tid WHERE t.Tname="吕德华") GROUP BY Cid) a  
SET sc.Score=a.c WHERE sc.Cid=a.Cid

**\#14、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名：**  
SELECT Sid,Sname FROM student WHERE Sid IN  
(SELECT sc.Sid FROM student s LEFT JOIN sc ON s.Sid=sc.Sid GROUP BY sc.Sid HAVING COUNT(\*) =  
(SELECT COUNT(\*) FROM student s LEFT JOIN sc ON s.Sid=sc.Sid WHERE s.Sid=2 GROUP BY sc.Sid))

**\#15、删除学习“胖炸”老师课的SC表记录：**  
DELETE FROM sc WHERE sc.Cid=  
(SELECT c.Cid FROM course c LEFT JOIN teacher t ON c.Tid=t.Tid WHERE Tname="胖炸")

**\#16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、002号课的平均成绩：**  
INSERT INTO sc (Sid,Cid,Score)  
SELECT DISTINCT st.Sid,sc.Cid,(SELECT AVG(Score)FROM sc WHERE Cid='2')  
FROM student st,sc WHERE NOT EXISTS (SELECT \* FROM sc WHERE Cid='2' AND sc.Sid=st.Sid) AND sc.Cid='2';

**\#17、按平均成绩从高到低显示所有学生的“英雄联盟”、“王者荣耀”、“和平精英”三门的课程成绩，按如下形式显示：学生ID，英雄联盟，王者荣耀，和平精英，有效课程数，有效平均分：**  
SELECT Sid AS 学生ID,  
(SELECT Score FROM sc WHERE sc.Sid=t.Sid AND sc.Cid =1) AS 英雄联盟,  
(SELECT Score FROM sc WHERE sc.Sid=t.Sid AND sc.Cid =2) AS 王者荣耀,  
(SELECT Score FROM sc WHERE sc.Sid=t.Sid AND sc.Cid =4) AS 和平精英,  
COUNT(\*) AS 有效课程数, AVG(t.score) AS 平均成绩  
FROM sc AS t  
GROUP BY Sid  
ORDER BY AVG(t.Score) DESC

**\#18、查询各科成绩最高和最低的分：以如下的形式显示：课程ID，最高分，最低分**  
SELECT L.Cid AS 课程ID,L.Score AS 最高分,R.Score AS 最低分  
FROM sc L ,SC AS R  
WHERE L.Cid = R.Cid AND  
L.Score = (SELECT MAX(IL.Score)  
FROM SC AS IL,Student AS IM  
WHERE L.Cid = IL.Cid AND IM.Sid=IL.Sid  
GROUP BY IL.Cid)  
AND  
R.Score = (SELECT MIN(IR.Score)  
FROM SC AS IR  
WHERE R.Cid = IR.Cid  
GROUP BY IR.Cid  
)

**\#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序：**  
SELECT sc.cid 课程编号,AVG(score) 平均成绩,d.f/(b.c\*100) 及格率  
FROM sc,(SELECT cid,SUM(score) f FROM sc GROUP BY cid) d,(SELECT cid,COUNT(sid) c FROM sc GROUP BY cid) b  
WHERE sc.cid=d.cid AND sc.cid=b.cid  
GROUP BY sc.cid  
ORDER BY 平均成绩 ASC,及格率 DESC

**\#20、查询如下课程平均成绩和及格率的百分数(用”1行”显示): 英雄联盟（001），王者荣耀（002），永劫无间（003），和平精英（004）：**  
SELECT b.p 英雄联盟平均成绩,b.q 英雄联盟及格率,c.o 王者荣耀平均成绩,c.w 王者荣耀及格率,  
d.i 永劫无间平均成绩,d.r 永劫无间及格率,e.u 和平精英平均成绩,e.t 和平精英及格率  
FROM sc,  
(SELECT cid,AVG(score) p,SUM(score)/(COUNT(sid)\*100) q FROM sc GROUP BY cid HAVING cid=1) b,  
(SELECT cid,AVG(score) o,SUM(score)/(COUNT(sid)\*100) w FROM sc GROUP BY cid HAVING cid=2) c,  
(SELECT cid,AVG(score) i,SUM(score)/(COUNT(sid)\*100) r FROM sc GROUP BY cid HAVING cid=3) d,  
(SELECT cid,AVG(score) u,SUM(score)/(COUNT(sid)\*100) t FROM sc GROUP BY cid HAVING cid=4) e  
WHERE sc.cid=b.cid OR sc.cid=c.cid OR sc.cid=d.cid OR sc.cid=e.cid  
GROUP BY b.q,c.w,d.r,e.t,b.p,c.o,d.i,e.u

**\#21、查询不同老师所教不同课程平均分从高到低显示：**  
SELECT MAX(t.tid) 老师编号,t.tname 老师名称,c.cid 课程编号,AVG(score) 平均成绩  
FROM sc,course c,teacher t  
WHERE sc.cid=c.cid AND c.tid=t.tid  
GROUP BY c.Cid  
ORDER BY AVG(score) DESC

**\#22、查询如下课程成绩第3名到第6名的学生成绩单：英雄联盟(001)，王者荣耀(002)，永劫无间(003)：**  
SELECT s.sid,sname,sc.score,cname  
FROM student s LEFT JOIN sc ON s.sid=sc.sid LEFT JOIN course c ON sc.cid=c.cid  
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="英雄联盟") LIMIT 0,3) d  
ON sc.sid=d.f  
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="王者荣耀") LIMIT 0,3) k  
ON sc.sid=k.f  
LEFT JOIN (SELECT sc.sid f,score,cid g FROM sc WHERE cid=(SELECT cid FROM course WHERE cname="永劫无间") LIMIT 0,3) l  
ON sc.sid=l.f  
WHERE (s.sid=d.f AND sc.cid=d.g) OR (s.sid=k.f AND sc.cid=k.g) OR (s.sid=l.f AND sc.cid=l.g)  
ORDER BY cname

**\#23、统计下列各科成绩，各分数段人数：课程ID，课程名称，\[100-85\],\[85-70\],\[70-60\],\[ 小于60\]**   
SELECT sc.cid AS 课程ID, Cname AS 课程名称,  
SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS "\[100 - 85\]",  
SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS "\[85 - 70\]",  
SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS "\[70 - 60\]",  
SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS "\[60分以下\]"  
FROM sc,course  
WHERE sc.cid=course.cid  
GROUP BY sc.cid,cname

**\#24、查询学生平均成绩及其名次：**  
SELECT s.sid,sname,b.c FROM student s  
RIGHT JOIN (SELECT sid,AVG(score) c  
FROM sc GROUP BY sid ORDER BY AVG(score) DESC) b  
ON s.sid=b.sid

**\#25、查询各科成绩前三名的记录（不考虑成绩并列情况）：**  
SELECT sid,cid,score FROM sc s  
WHERE sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid)<=3) ORDER BY sid

**\#26、查询每门课程被选修的学生数：**  
SELECT cname,COUNT(sid) FROM sc LEFT JOIN course c ON sc.Cid=c.cid GROUP BY c.cid

**\#27、查询出只选修一门课程的全部学生的学号和姓名：**  
SELECT s.sid,sname FROM student s  
LEFT JOIN sc ON s.sid=sc.sid  
WHERE s.sid IN (SELECT sid FROM sc GROUP BY sid HAVING COUNT(sid)=1)

**\#28、查询男生、女生人数：**  
SELECT ssex,COUNT(ssex) FROM student GROUP BY ssex

**\#29、查询姓“小”的学生名单：**  
SELECT \* FROM student WHERE sname LIKE'小%'

**\#30、查询同名同姓的学生名单，并统计同名人数：**  
SELECT sname,COUNT(\*) FROM student GROUP BY sname HAVING COUNT(\*)>1

**\#31、1981年出生的学生名单（注：student表中sage列的类型是datetime）:**  
SELECT \* FROM student WHERE DATE\_FORMAT(birthday,'%y')=81

**\#32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩：**  
SELECT s.sid,sname,AVG(score) FROM student s  
LEFT JOIN sc ON s.sid=sc.sid GROUP BY s.sid HAVING AVG(score)>85

**\#33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列：**  
SELECT cid,AVG(score) FROM sc GROUP BY cid ORDER BY AVG(score),cid DESC

**\#34、查询课程名称为“王者荣耀”，且分数低于60的学生名字和分数：**  
SELECT sname,score FROM student s  
LEFT JOIN sc ON s.sid=sc.sid  
LEFT JOIN course c ON sc.cid=c.cid WHERE score<60 AND cname="王者荣耀"

**\#35、查询所有学生的选课情况：**  
SELECT s.sid,sname,c.cname FROM student s  
LEFT JOIN sc ON s.sid=sc.sid LEFT JOIN course c ON sc.cid=c.cid

**\#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数：**  
SELECT sname,cname,score FROM sc  
LEFT JOIN course c ON sc.cid=c.cid  
LEFT JOIN student s ON sc.sid=s.sid WHERE Score>70

**\#37、查询不及格的课程，并按课程号从大到小的排列：**  
SELECT DISTINCT sc.Cid,c.Cname FROM sc  
RIGHT JOIN course c ON sc.Cid=c.cid  
WHERE score<60 ORDER BY sc.Cid DESC

**\#38、查询课程编号为“003”且课程成绩在80分以上的学生的学号和姓名：**  
SELECT sc.Sid,s.Sname FROM sc  
LEFT JOIN student s ON sc.sid=s.sid  
WHERE cid=3 AND score>80

**\#39、求选了课程的学生人数：**  
SELECT COUNT(\*) FROM (SELECT DISTINCT Sid FROM sc WHERE score IS NOT NULL) b

**\#40、查询选修“张大仙”老师所授课程的学生中，成绩最高的学生姓名及其成绩：**  
SELECT MAX(score) FROM course c  
LEFT JOIN sc ON c.cid=sc.cid  
WHERE c.tid=(SELECT tid FROM teacher WHERE tname="张大仙")

**\#41、查询各个课程及相应的选修人数：**  
SELECT cid,COUNT(\*) FROM sc GROUP BY Cid

**\#42、查询不同课程成绩相同的学生和学号、课程号、学生成绩：**  
SELECT a.Sid,a.cid,a.score FROM sc a,sc b  
WHERE a.score=b.score AND a.cid != b.cid

**\#43、查询每门课程成绩最好的前两名：**  
SELECT CO.cname 课程,  
(SELECT sc.sid  
FROM sc  
WHERE sc.Cid = co.cid  
ORDER BY sc.sid LIMIT 0, 1) 第一名学生编号,  
(SELECT sc.sid  
FROM sc  
WHERE sc.cid = co.cid  
ORDER BY sc.sid LIMIT 1, 1) 第二名学生编号  
FROM course co

**\#44、统计每门课程的学生选修人数(超过10人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序：**  
SELECT Cid,COUNT(Cid) FROM sc GROUP BY Cid ORDER BY COUNT(Cid) DESC,Cid ASC

**\#45、检索至少选修两门课程的学生学号：**  
SELECT Sid FROM sc GROUP BY Sid HAVING COUNT(\*)>=2

**\#46、查询全部学生选修的课程和课程号和课程名：**  
SELECT sc.Sid,sc.Cid,c.Cname FROM sc  
LEFT JOIN course c ON sc.Cid=c.Cid  
WHERE Score IS NOT NULL

**\#47、查询没学过”树叶”老师讲授的任一门课程的学生姓名：**  
SELECT Sid FROM student WHERE Sid NOT IN(  
SELECT Sid FROM sc RIGHT JOIN course c ON sc.Cid=c.Cid  
WHERE Tid =(SELECT Tid FROM teacher WHERE Tname="树叶")  
)

**\#48、查询两门以上不及格课程的同学的学号以及其平均成绩：**  
SELECT sc.Sid,AVG(Score) FROM sc  
WHERE sid IN (SELECT Sid FROM SC WHERE Score <60 GROUP BY Sid HAVING COUNT(\*)>2  
) GROUP BY Sid

**\#49、检索“004”课程分数小于60，按分数降序排列的同学学号：**  
SELECT sc.Sid FROM course c  
LEFT JOIN sc ON c.Cid=sc.Cid  
WHERE Score<60 ORDER BY Score DESC

**\#50、删除“002”同学的“001”课程的成绩：**  
DELETE FROM sc WHERE sc.Sid=2 AND sc.Cid=1

[de556d05df7d4994b9b6db211fc4b2b1.png]: https://img-blog.csdnimg.cn/de556d05df7d4994b9b6db211fc4b2b1.png
[521c19f61ce84b9298ea801a49275fe5.png]: https://img-blog.csdnimg.cn/521c19f61ce84b9298ea801a49275fe5.png
[890dc6bce7cc4f4ab28e0a33041b49cd.png]: https://img-blog.csdnimg.cn/890dc6bce7cc4f4ab28e0a33041b49cd.png
[9bad13d101214fc28324b1236e327fa5.png]: https://img-blog.csdnimg.cn/9bad13d101214fc28324b1236e327fa5.png
[5241f610ff0d407883f28027392a78ee.png]: https://img-blog.csdnimg.cn/5241f610ff0d407883f28027392a78ee.png
[903e6d55f4a143a78fb9217433f27019.png]: https://img-blog.csdnimg.cn/903e6d55f4a143a78fb9217433f27019.png
[af68a73a288a42698ce1b72e83dd831b.png]: https://img-blog.csdnimg.cn/af68a73a288a42698ce1b72e83dd831b.png
[cb8deef3e53d44548c2ee635e65fe8cb.png]: https://img-blog.csdnimg.cn/cb8deef3e53d44548c2ee635e65fe8cb.png