经典MySQL 必刷50题（个人版）【大厂数据分析岗密码】

最新推荐文章于 2022-11-29 19:51:55 发布

你们卷的我睡不着QAQ

最新推荐文章于 2022-11-29 19:51:55 发布

阅读量297

点赞数

分类专栏： MySQL 文章标签： sqlserver mysql

本文链接：https://blog.csdn.net/qq_44182694/article/details/118000137

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

经典SQL50题

#1、查询“01”课程比“02”课程成绩高的所有学生的学号（重点）；
#解法一：
–（构建一张（只有sid,c01,c02）字段的数据表）
SELECT a.SId ,a.score ,b.score FROM
(
SELECT SId,score FROM sc WHERE CId=‘01’
)as a
INNER JOIN
(
SELECT SId ,score FROM sc WHERE CId=‘02’
)as b ON a.SId=b.SId
WHERE a.score>b.score;

–解法二：
–(把第一张表的课程为01的取出来；把第二张的表课程为02的取出来；然后第一张表的分>第二章的的分数)

SELECT sc1.SId FROM sc as sc1
INNER JOIN sc as sc2 ON sc1.SId=sc2.SId
AND sc1.CId=‘01’ AND sc2.CId= ‘02’ and sc1.score>sc2.score;

#2、查询平均成绩大于60分的同学的学号和平均成绩（简单）；
–（尽量不要把group by 的字段以外字段写到select里面）

SELECT SId ,avg(score) FROM sc GROUP BY SId HAVING avg(score)>60;

#3、查询所有同学的学号、姓名、选课数、总成绩（重点）
–()
SELECT total.SId,total.Sname,SUM(total.score),COUNT(*)
FROM (
SELECT student.SId,student.Sname,sc.score FROM student LEFT JOIN sc on student.SId=sc.SId
)as total
GROUP BY total.SId;

#4、查询姓“李”的老师的个数；(简单)
–（模糊查询（like）%是不定字长）

SELECT * FROM teacher WHERE teacher.Tname LIKE ‘李%’;

#5、查询没学过“张三”老师课的同学的学号、姓名（重点）；
–(把学过张三老师的同学找出来，然后就是没学过的了)

SELECT student.SId,student.Sname FROM student
WHERE student.SId NOT in
(
SELECT sc.SId FROM sc WHERE sc.CId=
(
SELECT course.CId FROM course WHERE course.TId=
(
SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’
)))

–解法二：
–（构造学号、课程号、成绩、教师编号、教师姓名的一张表）

SELECT student.Sname,student.SId FROM student WHERE student.SId not in (
SELECT DISTINCT( sc.SId)
FROM sc INNER JOIN course on sc.CId=course.CId
INNER JOIN teacher ON course.TId=teacher.TId
)

#6、查询学过编号“01”并且也学过编号“02”课程的同学的学号、姓名（重点）；
–(解法一：两个自连接)
SELECT student.SId,student.Sname FROM student WHERE student.SId in (
SELECT sc1.SId FROM sc as sc1 JOIN sc as sc2 ON sc1.SId=sc2.SId and sc1.CId=‘01’ and sc2.CId=‘02’)

–（解法二：先找到选01的SID，再找到02的SID,再取交集）

SELECT student.Sname,student.SId FROM student WHERE student.SId IN(
SELECT a.SId
FROM(
(SELECT sc.SId FROM sc WHERE sc.CId=‘01’)as a
INNER JOIN
(SELECT sc.SId FROM sc WHERE sc.CId=‘02’)as b
on a.SId=b.SId) )

#7、查询学过“张三”老师课的同学的学号、姓名（重点）；

SELECT student.Sname,student.SId FROM student WHERE student.SId in (
SELECT sc.SId FROM sc WHERE sc.CId=(
SELECT course.CId FROM course WHERE course.TId=(
SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’)))

#8、查询课程编号“01”的成绩比课程编号“02”课程低的所有同学的学号、姓名；（重点）（题1 题6 ）
–（解法一）

SELECT student.SId,student.Sname FROM student WHERE student.SId in (
SELECT sc1.SId FROM sc sc1 INNER JOIN sc sc2 on sc1.SId=sc2.SId AND sc1.CId=‘01’ and sc2.CId=‘02’ and sc1.score>sc2.score);

–（常规解法）

SELECT student.Sname,student.SId FROM student WHERE student.SId in (
SELECT a.SId
FROM(
(SELECT sc.SId,sc.CId,sc.score FROM sc WHERE sc.CId=‘01’ )as a
INNER JOIN
(SELECT sc.SId, sc.CId,sc.score FROM sc WHERE sc.CId=‘02’) as b
on a.SId= b.SId )WHERE a.score>b.score
)
#9、查询所有课程成绩小于60分的同学的学号、姓名(简单)；

SELECT student.SId,student.Sname FROM student WHERE student.SId in(
SELECT DISTINCT sc.SId FROM sc WHERE sc.score<60 );

#10、查询没有学全所有课的同学的学号、姓名；
–(注意有几个根本没有选课，他们就在sc表就就没有数据，这样很容易漏掉他们)
SELECT student.Sname,student.SId FROM student WHERE student.SId NOT IN (
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*)=(
SELECT COUNT(1) FROM course ) )

–（解法二，有学生表左连杰成绩表,这样没有选课的同学数据就不会没有）

SELECT stu.SId,stu.Sname FROM student AS stu WHERE stu.SId not IN(
SELECT student.SId FROM student LEFT JOIN sc ON student.SId=sc.SId GROUP BY student.SId HAVING COUNT(1)=(
SELECT COUNT(1) FROM course))

#11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；(简单)
SELECT student.SId,student.Sname FROM student WHERE student.SId IN(
SELECT DISTINCT sc.SId FROM sc WHERE sc.CId in (SELECT sc.CId FROM sc WHERE sc.SId=‘01’))AND student.SId !=‘01’

#12、查询和"01"号的同学学习的课程完全相同的其他同学的学号和姓名(重点)
–（注意有一种情况：1号同学（1 2 3）
2号同学（1 2 3）
3号同学（1 2 4）
4号同学（1 2 3 4）
此时这种情况时候只有2号同学与1号同学完全相同
因此不能简单粗暴的让课程数=3，这样会把3号同学选进去）

SELECT student.SId,student.Sname FROM student WHERE student.SId in (
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT()=(SELECT COUNT() FROM sc WHERE sc.SId=‘01’) )AND student.SId !=‘01’

–（正确解法：01号学了01 02 03
1、选出所学的课不在（01 02 03）的同学 -排除
2、剩下的同学肯定选了01 02 03 中的某一门，判断所学的课程是否等于3）

SELECT sc.SId FROM sc WHERE sc.SId!=‘01’ GROUP BY sc.SId HAVING count(1)=(
SELECT count(1) FROM sc WHERE sc.SId=‘01’)
AND
sc.CId not IN(
SELECT sc.SId FROM sc WHERE sc.CId not IN(
SELECT sc.CId FROM sc WHERE sc.SId=‘01’))

#13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩；
SELECT avg(sc.score) FROM sc WHERE sc.CId =(
SELECT course.CId FROM course WHERE course.TId =(
SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’))

#14、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT student.Sname FROM student WHERE student.SId NOT IN (
SELECT sc.SId FROM sc WHERE sc.CId =(
SELECT course.CId FROM course WHERE course.TId=(
SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’) ))

#15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT *
FROM (
(SELECT sc.SId,sc.score FROM sc WHERE sc.score<60 GROUP BY sc.SId HAVING COUNT(1)>=2) AS a
INNER JOIN
(SELECT student.Sname,student.SId FROM student)as b
ON a.SId=b.SId)

#16、检索"01"课程分数小于60，按分数降序排列的学生信息
SELECT student.Sname,student.SId,sc.score FROM student INNER JOIN sc ON student.SId=sc.SId WHERE sc.score<60 AND sc.CId=‘01’ ORDER BY sc.score DESC

#17、按平均成绩从高到低显示所有学生的平均成绩

SELECT a.*,RANK() over ( ORDER BY 平均成绩 DESC) as 排名 FROM
(SELECT sc.SId,AVG(sc.score) 平均成绩 FROM sc GROUP BY sc.SId ORDER BY AVG(sc.score) DESC) as a

#18、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率(COUNT(if))（重点）

SELECT a.CId,a.Cname,b.*
FROM (
(SELECT course.CId,course.Cname FROM course )as a
INNER JOIN
(SELECT sc.CId, MAX(sc.score) 最高分,MIN(sc.score) 最低分,avg(sc.score) 平均分,count(IF(sc.score>=60,sc.score,NULL))/count(1) 及格率 FROM sc GROUP BY sc.CId) as b
on a.CId=b.CId)

#19、按各科平均成绩从低到高和及格率的百分数从高到低顺序(重点，count（if,）)

– count(IF（条件，条件为真结果，条件为假时结果）)

#case when then 表达式
– case when THEN
– CASE SCORE WHEN ‘A’ THEN ‘优’ ELSE ‘不及格’ END
– CASE SCORE WHEN ‘B’ THEN ‘良’ ELSE ‘不及格’ END
– CASE SCORE WHEN ‘C’ THEN ‘中’ ELSE ‘不及格’ END

 -- CASE WHEN SCORE = 'A' THEN '优'

– WHEN SCORE = ‘B’ THEN ‘良’
– WHEN SCORE = ‘C’ THEN ‘中’ ELSE ‘不及格’ END

 --  ORDER BY 两个字段时（order by  字段1 DESC, 字段2 DESC）
 
 SELECT a.*,RANK() over (ORDER BY 平均成绩 DESC,及格率 DESC ) AS '排名' FROM 
 (SELECT sc.CId,AVG(sc.score) as 平均成绩,COUNT(IF(sc.score>=60,sc.score,NULL))/count(1) AS 及格率 
 FROM sc GROUP BY sc.CId ) AS a

#20、查询学生的总成绩并进行排名
SELECT a.*,RANK() over (ORDER BY sum DESC ) FROM
(SELECT sc.SId,SUM(sc.score) as sum FROM sc GROUP BY sc.SId )as a

#21、查询不同老师所教不同课程平均分从高到低显示（简单）
SELECT *FROM
((SELECT course.CId,teacher.Tname FROM course LEFT JOIN teacher on course.TId=teacher.TId) as a
INNER JOIN
(SELECT sc.CId,AVG(sc.score) as 平均成绩 FROM sc GROUP BY sc.CId ORDER BY AVG(sc.score) DESC)as b
ON a.CId=b.CId)

#24、查询学生平均成绩及其名次(重点、窗口函数)
– (不能用partition by 因为排序键是聚合函数)
SELECT *,ROW_NUMBER()over(ORDER BY a.avg1 desc) as rank1
FROM(SELECT SId,AVG(score) avg1 FROM sc GROUP BY SId)as a

#26、查询每门课程名字被选修的学生数（简单）
SELECT a.Cname,b.count
FROM(
(SELECT course.CId,course.Cname FROM course )as a
INNER JOIN
(SELECT sc.CId,COUNT(*)as count FROM sc GROUP BY sc.CId) as b
on a.CId=b.CId)

#27、查询出只选修了两门课程的全部学生的学号和姓名
SELECT student.SId,student.Sname FROM student WHERE student.SId in(
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(*)=2);

#28、查询男生、女生人数
SELECT student.Ssex,count(1) FROM student GROUP BY student.Ssex;

#29、查询名字中含有"风"字的学生信息
SELECT * FROM student WHERE student.Sname like ‘%风%’;

#30、查询同名同性学生名单，并统计同名人数
SELECT student.Ssex,COUNT(*) FROM student GROUP BY student.Ssex

#31、查询1990年出生的学生名单(注：Student表中Sage列的类型是datetime)
SELECT * FROM student WHERE YEAR(student.Sage)=‘1990’

#32、查询每个课程之间的平均分并排序（从大到小排序）
SELECT sc.CId,AVG(sc.score) FROM sc GROUP BY sc.CId ORDER BY AVG(sc.score) DESC

#37、查询04号同学不及格的课程，并按课程号从大到小排列
SELECT sc.CId FROM sc WHERE sc.SId=‘04’ AND sc.score<60 ORDER BY sc.CId DESC

#38、查询课程编号为"01"且课程成绩在60分以上的学生的学号和姓名；

SELECT student.SId,student.Sname FROM student WHERE student.SId IN(
SELECT sc.SId FROM sc WHERE sc.CId=‘01’ AND sc.score>=60)

#40、查询选修“张三”老师所授课程的学生中，成绩最高的学生姓名及其成绩

SELECT student.Sname FROM student WHERE student.SId=(
SELECT sc.SId FROM sc WHERE sc.CId=(
SELECT course.CId FROM course WHERE course.TId=(
SELECT teacher.TId FROM teacher WHERE teacher.Tname=‘张三’)) ORDER BY sc.score DESC)

#43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT sc.CId,COUNT(1) FROM sc GROUP BY sc.CId HAVING COUNT(1)>=5

#44、检索至少选修两门课程的学生学号(简单)
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(1)>=2

#45、查询选修了全部课程的学生信息
SELECT * FROM student WHERE student.SId IN(
SELECT sc.SId FROM sc GROUP BY sc.SId HAVING COUNT(1)=(
SELECT COUNT(1) FROM course))

#46、查询各学生的年龄(重点)
– YEAR(NOW())-YEAR(student.Sage) 是本题的重点
– YEAR(date) 转换为年 NOW()表示当前时间
SELECT student.Sname,YEAR(NOW())-YEAR(student.Sage) as 年龄 FROM student

#47、查询本周过生日的学生

SELECT WEEK(NOW())#查看当前星期
SELECT WEEK(sage) FROM Student#查看所有学生生日对应的星期
SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())

#48、查询下周过生日的学生

SELECT sid,sname FROM Student WHERE WEEK(sage)=WEEK(NOW())+1

#49、查询本月过生日的学生
SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())

#50、查询下月过生日的学生
SELECT sid,sname FROM Student WHERE MONTH(sage)=MONTH(NOW())+1

#51、查询课程编号为02的总成绩（简单）
SELECT sc.CId,SUM(sc.score) FROM sc WHERE sc.CId=‘02’
SELECT sum(sc.score)/COUNT(*) FROM sc WHERE sc.CId=‘02’

#52查询所有成绩小于60分的学号与姓名（有陷阱！！！）
–(注意有的同学只选了2门课程，因此就不能直接不及格科目数=3)
–(思路：构建一个同学选了多少门课的表，构造一个同学不及格科目数的表
两者取交集，这样就能求出所有课程不及格的学号与姓名)
SELECT student.Sname,student.SId FROM student WHERE student.SId in(
SELECT a.SId
FROM(
(SELECT sc.SId,COUNT() FROM sc GROUP BY sc.SId) as a
INNER JOIN
(SELECT sc.SId,COUNT() FROM sc WHERE sc.score<60 GROUP BY sc.SId) as b
ON a.SId=b.SId))