【一日速撕sql】MySql经典45题（含答案及解析过程）

 

一、建表工作

# 学生表 Student：

create table Student(SId varchar(10) ,Sname varchar(10),Sage datetime,Ssex varchar(10));

insert into Student values('01' , '赵雷' , '1990-01-01' , '男'),('02' , '钱电' , '1990-12-21' , '男'),('03' , '孙风' , '1990-05-20' , '男'),('04' , '李云' , '1990-08-06' , '男'),('05' , '周梅' , '1991-12-01' , '女'),('06' , '吴兰' , '1992-03-01' , '女'),('07' , '郑竹' , '1989-07-01' , '女'),('09' , '张三' , '2017-12-20' , '女'),('10' , '李四' , '2017-12-25' , '女'),('11' , '李四' , '2017-12-30' , '女'),('12' , '赵六' , '2017-01-01' , '女'),('13' , '孙七' , '2018-01-01' , '女');

# 教师表 Teacher:

create table Teacher(TId varchar(10),Tname varchar(10)); 

insert into Teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');

# 科目表 Course:

create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10)); 

insert into Course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03');

# 成绩表 SC:

create table SC(SId varchar(10),CId varchar(10),score decimal(18,1)); 

insert into SC values('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99),('02' , '01' , 70),('02' , '02' , 60),('02' , '03' , 80),('03' , '01' , 80),('03' , '02' , 80),('03' , '03' , 80),('04' , '01' , 50),('04' , '02' , 30), ('04' , '03' , 20),('05' , '01' , 76), ('05' , '02' , 87),('06' , '01' , 31),('06' , '03' , 34),('07' , '02' , 89),('07' , '03' , 98);

二、题目准备及解答

-----1、查询"01"课程成绩比"02"课程成绩高的学生的信息及分数

思路：【关键词】学生信息及分数

需要联表查询，先进行01课程比02课程大的比较，然后再拿出SID相等的数据

select s.*,sc.score from student s 
left join sc on s.SId = sc.SId 
left join sc a on a.SId = sc.SId 
and sc.CId='01' and a.CId='02'
where sc.score>a.score

 -----2、查询平均成绩>=60分同学的学生编号、姓名和平均成绩

思路：

主要考察group by聚合函数，where和having的区别以及Sql的执行顺序。

HAVING AVG(score)>=60是在select前执行的，需要写完整

SELECT s.SId,s.Sname,AVG(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
GROUP BY s.SId 
HAVING AVG(score)>=60

 -----3、查询在SC表中有成绩的学生信息

SELECT *
FROM sc
LEFT JOIN student s
ON s.SId = sc.SId
GROUP BY sc.SId;

-----4、查询所有同学的学生编号、姓名、选课总数、所有课程的总成绩（没成绩显示null）

SELECT s.SId,s.Sname,SUM(sc.CId),SUM(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
GROUP BY sc.SId;

 -----5、查询姓李老师的数量

思路：COUNT函数

SELECT COUNT(Tname)
FROM teacher 
WHERE Tname LIKE "李%";

-----6、查询学过【张三】老师授课的同学的信息

SELECT s.*
FROM student s 
INNER JOIN 
(
SELECT SId
FROM sc 
LEFT JOIN course c
ON sc.CId = c.CId
LEFT JOIN teacher t
ON course.TId = t.TId
WHERE t.Tname = '张三'
) AS a
ON s.SId = a.SId;

 -----7、查询没有学全所有课程的同学信息

思路：没有学全=课程数量<3

SELECT s.*
FROM student s 
LEFT JOIN sc
ON s.SId=sc.SId
GROUP BY s.SId
HAVING COUNT(CId)<3;

 -----8、查询至少有一门课与学号为"01"的同学所学课程相同的同学信息

SELECT s.*
FROM student s 
LEFT JOIN sc
ON s.SId=sc.SId
WHERE sc.CId IN (SELECT sc.CId FROM sc WHERE sc.SId = '01')
GROUP BY s.SId
HAVING COUNT(sc.CId)>=1

 -----9、查询与学号为"01"的同学所学课程完全相同的同学信息

思路：01同学的全部课程；其他同学

提取一个表不含01同学的，然后在里面过滤课程信息（科目和数量）一样的同学出来

SELECT s.*
FROM student s 
INNER JOIN sc
ON s.SId = sc.SId
WHERE s.SId != '01'  AND sc.CId IN (SELECT sc.CId FROM sc WHERE sc.SId = '01')
GROUP BY s.SId
HAVING COUNT(CId) = (SELECT COUNT(CId) FROM sc WHERE SId = '01')

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

SELECT s.Sname 
FROM student s 
WHERE s.SId NOT IN 
(SELECT sc.SId 
FROM sc 
INNER JOIN course c 
ON sc.CId=c.CId 
INNER JOIN teacher t 
ON c.TId=t.TId 
WHERE Tname="张三");

 -----11、查询两门及以上不合格课程的同学的学号，姓名和平均成绩

思路：score小于60，count(CId)大于2

SELECT s.SId,s.Sname,AVG(sc.score)
FROM student s
LEFT JOIN sc
ON s.SId = sc.SId
WHERE sc.score<60
GROUP BY s.SId
HAVING COUNT(sc.CId)>2

 -----12、查询"01"课程分数小于60的学生信息，按分数降序排列

思路：学过01课程学生拉一个表，然后过滤score<60的学生，加order by DESC

SELECT s.*
FROM student s
LEFT JOIN sc
ON s.SId=sc.SId
WHERE sc.CId='01' AND sc.score>60
ORDER BY sc.score DESC

 -----13、按平均成绩从高到低显示所有学生的所有课程成绩及平均成绩

思路：平均值的表因为涉及到聚合，要另外新建，然后联合。

SELECT a.SId,a.Sname,a.CId,a.score,b.avg_score 
FROM
(SELECT s.SId,s.Sname,sc.CId,sc.score 
FROM student s 
LEFT JOIN sc 
ON s.SId=sc.SId) AS a 
LEFT JOIN 
(SELECT sc.SId,AVG(score) avg_score 
FROM sc 
GROUP BY sc.SId) AS b 
ON a.SId=b.SId 
ORDER BY b.avg_score DESC;

 ------14、查询各科成绩的最高分、平均分、最低分、选修人数、合格率、中等率、优良率、优秀率，按人数降序，课程升序

思路：最高分MAX等聚合函数，CASE WHEN的简单函数和搜索函数的区别

SELECT sc.CId,c.Cname,MAX(sc.score) AS 最高分,AVG(sc.score) AS 平均分,
MIN(sc.score) AS 最低分,COUNT(*) AS 选修人数,
(SUM(CASE WHEN sc.score>=60 THEN 1 ELSE 0 END)/COUNT(*)) AS 合格率,
(SUM(CASE WHEN sc.score>=70 AND sc.score<80 THEN 1 ELSE 0 END)/COUNT(*)) AS 中等率,
(SUM(CASE WHEN sc.score>=80 AND sc.score<90 THEN 1 ELSE 0 END)/COUNT(*)) AS 优良率,
(SUM(CASE WHEN sc.score>=90 THEN 1 ELSE 0 END)/COUNT(*)) AS 优秀率
FROM sc
INNER JOIN course c
ON sc.CId = c.CId
GROUP BY sc.CId
ORDER BY 选修人数 DESC,CId ASC

-----15、按各科成绩进行排序，显示排名，分数重复继续排名 

思路：窗口函数row_number(),rank(),dense_rank()的区别，以及row_number()使用方法；over确定其细节，partition by等价于分类

SELECT *,
row_number() over (PARTITION BY CId ORDER BY score DESC) AS 排名 
FROM sc 

 -----16、查询学生的总成绩并进行排名，总成绩重复时保留名额空缺

SELECT SId,SUM(score) sum_score,rank() over (ORDER BY SUM(score)) AS Rank_
FROM sc
GROUP BY SId;

-----17、查询各科成绩前三名的记录

思路：各科成绩分类进行排序列一个表，然后从这个表里取前三名的出来。（）表要起别名

SELECT *
FROM
(SELECT	*,dense_rank() over(PARTITION BY CId ORDER BY score) AS cid_rank
FROM sc) a
WHERE cid_rank<=3

 -----18、统计各科成绩各分数段人数：课程编号、课程名称、[0-60](60-70](70-85](85-100]所占百分比

思路：先拿出各个科目总人数（count（） group by），然后进行case when分类，然后再取百分数

SELECT CId,
CONCAT(SUM(CASE WHEN score<=60 THEN 1 ELSE 0 END)/COUNT(SId)*100,'%') AS '[0-60]所占百分比',
CONCAT(SUM(CASE WHEN score>60 AND score<=70 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(60-70]所占百分比',
CONCAT(SUM(CASE WHEN score>70 AND score<=85 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(70-85]所占百分比',
CONCAT(SUM(CASE WHEN score>85 THEN 1 ELSE 0 END)/COUNT(SId),'%') AS '(85-100]所占百分比'
FROM sc
GROUP BY CId

-----19、查询每门课程被选修的学生数
 

SELECT CId,COUNT(CId) AS '选修的学生数'
FROM sc
GROUP BY CId

 -----20、查询只选修两门课程的学生学号和姓名

SELECT s.Sname,sc.SId
FROM student s
INNER JOIN sc
ON s.SId=sc.SId
GROUP BY SId
HAVING COUNT(CId)=2

 -----21、查询男生、女生人数

SELECT Ssex,COUNT(*)
FROM student 
GROUP BY Ssex

 -----22、查询名字中含有"风"字的学生信息

SELECT *
FROM student 
WHERE Sname LIKE "%风%"

 -----23、查询同名同性学生名单，并统计同名同姓人数

思路：重复一个表，过滤ID不同但名字相同的人

select * ,count(*)
from student a 
inner join student b 
on a.SId!=b.SId 
and a.Sname=b.Sname 
and a.Ssex=b.Ssex

-----24、查询1990年出生的学生信息

select *
from student
where year(Sage)=1990

 -----25、查询每门课程平均成绩，并降序排列，平均成绩相同，则按课程编号升序排列

select CId,avg(score)
from sc
group by CId
order by AVG(score) desc

 ----26、查询平均成绩大于等于85分学生的学号、姓名、平均成绩

select s.SId,s.Sname,avg(sc.`score`)
from student s
left join sc
on s.SId=sc.SId
group by SId
having AVG(sc.`score`)>85

 ----27、查询课程为数学，且分数低于60的学生姓名和分数

select s.`Sname`,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on sc.`CId`=c.`CId`
where sc.`score`<60 and c.`Cname` like "数学"

----28、查询所有学生的课程及分数情况（存在学生没成绩、没选课情况）

SELECT s.`Sname`,c.`Cname`,sc.`score`
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
LEFT JOIN course c
ON sc.`CId`=c.`CId`

 

 ----29、查询任意一门课程成绩在70分以上的学生姓名、所选课程名称和分数

select s.`Sname`,c.`Cname`,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on sc.`CId`=c.`CId`
where score>70

 ----30、查询存在不及格的课程

select c.`Cname`
from sc
left join course c
on sc.`CId`=c.`CId`
where score<60
group by c.`Cname`

 ----31、查询课程编号为01 且成绩在80分以上的学生学号和姓名

SELECT s.`SId`,s.`Sname`,sc.`CId`,sc.`score`
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
WHERE sc.`score`>=80 AND sc.`CId`=1

 ----32、求每门课程的学生人数

select CId,count(*)
from sc 
group by CId;

----33、假设成绩不重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩
 

select s.*,sc.`score`
from student s
left join sc
on s.`SId`=sc.`SId`
left join course c
on c.`CId`=sc.`CId`
left join teacher t
on t.`TId`=c.`TId`
where t.`Tname`="张三"
having max(sc.`score`)

 ----34、假设成绩重复，查询选修"张三"老师课程的学生中，成绩最高学生信息及成绩

思路：成绩重复要考虑同样成绩的学生，需要进行排序表整理

SELECT a.SId,a.Sname,a.Sage,a.Ssex,a.score
FROM
(SELECT s.*,sc.`score`,rank() over(ORDER BY sc.`score`) AS rank_
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
LEFT JOIN course c
ON c.`CId`=sc.`CId`
LEFT JOIN teacher t
ON t.`TId`=c.`TId`
WHERE t.`Tname`="张三") AS a
WHERE rank_<=1

---35、查询不同课程成绩相同学生的学生编号、课程编号、学生成绩

SELECT a.*
FROM
(SELECT sc.`SId`,sc.`CId`,sc.`score`
FROM sc) AS a
LEFT JOIN sc
ON sc.`SId`=a.SId
WHERE sc.`score`=a.score AND sc.`CId`!=a.CId
GROUP BY sc.`CId`

 ---36、查询每门课程成绩最好的前两名

SELECT a.SId,a.CId,s.`Sname`
FROM
(SELECT row_number() over(PARTITION BY sc.`CId` ORDER BY sc.`score` DESC) AS rank_,sc.*
FROM sc) AS a
LEFT JOIN student s
ON s.`SId`=a.SId
WHERE rank_<=2

 ---37、统计每门课程的学生选修人数（超过5人的课程才统计）

SELECT COUNT(sc.`SId`) AS 选修人数
FROM sc
GROUP BY sc.`CId`
HAVING COUNT(sc.`SId`)>=5

 

 ---38、检索至少选修两门课程的学生学号

select SId 
from sc 
group by SId 
having count(*)>=2;

---39、查询选修全部课程的学生信息

SELECT s.*
FROM student s
LEFT JOIN sc
ON s.`SId`=sc.`SId`
GROUP BY s.`SId`
HAVING COUNT(sc.`CId`)=3

 ---40、查询各学生的年龄，只按年份来算

SELECT s.`Sname`,(YEAR(NOW())-YEAR(s.`Sage`)) AS 年龄
FROM student s

 ---41、按照出生日期来算（当前月日<出生年月的月日则年龄减一）

SELECT s.SId,s.Sname,
TIMESTAMPDIFF(YEAR,s.Sage,NOW()) AS 年龄
FROM student s 

 ---42、查询本周过生日的同学信息

SELECT s.SId,s.Sname,s.Sage
FROM student s 
WHERE WEEK(NOW())=WEEK(s.`Sage`)

 ---43、查询下周过生日的学生信息

SELECT s.SId,s.Sname,s.Sage
FROM student s 
WHERE WEEK(NOW())-WEEK(s.`Sage`)=-1

---44、查询本月过生日的同学信息

SELECT s.*
FROM student s 
WHERE MONTH(s.`Sage`)=MONTH(NOW())

 45、查询下月过生日的学生信息

SELECT s.*
FROM student s 
WHERE MONTH(s.`Sage`)-MONTH(NOW())=1