【Mysql学习之旅-2】经典sql面试题及答案分析

本文链接：https://blog.csdn.net/2401_85112250/article/details/139835310

UNION ALL

SELECT

student.*,c.rank,c.score,c.c_id

FROM

(SELECT a.s_id,a.score,a.c_id,@k:=@k+1 as rank from score a,(SELECT @k:=0)s 

WHERE a.c_id='03'  ORDER BY a.score DESC) c

LEFT JOIN

student ON c.s_id=student.s_id

WHERE rank BETWEEN 2 AND 3




> 简评：sql很长，不要被吓到，其实是3个相同的部分（where条件不同）通过UNION ALL合并而已。另外，筛选2、3名也是在排名的基础上进一步筛选。  

> 难度：★★★★☆  

> 频率：★★  

> 思路：  

> 1、先将01课程的所有人的分数排序，再进一步根据`rank BETWEEN 2 AND 3`筛选出2、3名  

> 2、使用同样方法，筛选02、03课程数据，使用 `UNION ALL`合并查询结果



**8、统计各科成绩各分数段人数：课程编号、课程名称、\[80-100\]，\[60-80\]，\[0-60\]及所占百分比**

SELECT

DISTINCT e.c_name,a.c_id,b.`80-100`,b.百分比,c.`60-80`,c.百分比,d.`0-60`,d.百分比

FROM

score a

LEFT JOIN

(SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS `80-100`,

ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 end)/count(*)),2)

AS 百分比 FROM score GROUP BY c_id)b ON a.c_id=b.c_id

LEFT JOIN

(SELECT c_id,SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 END) AS `60-80`,

ROUND(100*(SUM(CASE WHEN score >60 AND score <=80 THEN 1 ELSE 0 end)/count(*)),2) 

AS 百分比 FROM score GROUP BY c_id)c ON a.c_id=c.c_id

LEFT JOIN

(SELECT c_id,SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END) AS `0-60`,

ROUND(100*(SUM(CASE WHEN score >=0 AND score <=60 THEN 1 ELSE 0 END)/count(*)),2) 

AS 百分比 FROM score GROUP BY c_id)d ON a.c_id=d.c_id

LEFT JOIN

course e ON a.c_id = e.c_id




> 简评：`CASE WHEN`和sql语句内进行逻辑运算的又一次应用。  

> 难度：★★★★  

> 频率：★★  

> 思路：`SELECT c_id,SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END) AS`80-100`, ROUND(100*(SUM(CASE WHEN score >80 AND score <=100 THEN 1 ELSE 0 END)/count(*)),2) AS 百分比 FROM score GROUP BY c_id`得到所有课程80-100分成绩的统计临时表：  

> ![在这里插入图片描述](https://img-blog.csdnimg.cn/20200621113553841.png)  

> 然后再用同样的方式，得到0-60、60-80的统计临时表，三表联查。



**9、查询学生平均成绩及其名次**

SELECT a.s_id,

@i:=@i+1 as '不保留空缺排名',

@k:=(CASE WHEN @avg_score=a.avg_s THEN @k ELSE @i END) AS '保留空缺排名',

@avg_score:=avg_s AS '平均分'

FROM

(SELECT s_id,ROUND(AVG(score),2) AS avg_s FROM 

score GROUP BY s_id ORDER BY avg_s DESC)a,

(SELECT @avg_score:=0,@i:=0,@k:=0)b;




> 简评：与第5题类似，没什么特别需要说的。  

> 难度：★★★★  

> 频率：★★



**10、查询各学生的年龄**

SELECT

s_birth,(DATE_FORMAT(NOW(),'%Y')-DATE_FORMAT(s_birth,'%Y') - 

(CASE WHEN DATE_FORMAT(NOW(),'%m%d')>DATE_FORMAT(s_birth,'%m%d') THEN 0 ELSE 1 END)) 

AS age

FROM student;




> 简评：本身的实现思路并不难，`DATE_FORMAT`掌握其用法即可。



**11、分别查询本周、下周、本月、下月过生日的学生**

–本周

SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))=WEEK(s_birth)

–下周

SELECT * FROM student WHERE WEEK(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 = WEEK(s_birth)

–本月

SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),‘%Y%m%d’)) =MONTH(s_birth)

–下月

SELECT * FROM student WHERE MONTH(DATE_FORMAT(NOW(),‘%Y%m%d’))+1 =MONTH(s_birth)




[]( )进阶

=============================================================



**1、查询及格学生（每门课程的分数>=60）的学生姓名**

SELECT s_name FROM

student

WHERE s_id NOT IN

(SELECT DISTINCT(s_id) FROM score WHERE score<60)




或者：

SELECT s_name FROM

student a JOIN score b ON a.s_id=b.s_id

GROUP BY b.s_id HAVING MIN(score)>60




**2、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 (包括有成绩的和无成绩的)**

SELECT

s_name,ROUND(avg(score),2) AS avg

FROM

student

JOIN

score ON student.s_id=score.s_id

GROUP BY

score.s_id HAVING avg<60

UNION ALL

SELECT

s_name,0 AS avg

FROM

student

WHERE s_id NOT IN(SELECT DISTINCT(s_id) FROM score)

– 方法2

SELECT

s_name,IFNULL(ROUND(avg(score),2),0) AS avg

FROM

student

LEFT JOIN

score ON student.s_id=score.s_id

GROUP BY score.s_id HAVING avg<60 OR avg is NULL




**3、查询学过"张三丰"老师授课的同学的信息**

SELECT

FROM

score JOIN student ON score.s_id=student.s_id

WHERE c_id IN

(SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰')




**4、查询没学过"张三丰"老师授课的同学的信息**

SELECT

FROM

student

WHERE s_id NOT IN

(SELECT s_id FROM score WHERE c_id

(SELECT c_id FROM course c JOIN teacher t ON c.t_id=t.t_id WHERE t_name='张三丰'))




**5、查询没有学全所有课程的同学的信息**

SELECT

s_name,COUNT(c_id) AS count

FROM

student

LEFT JOIN

score ON student.s_id=score.s_id

GROUP BY

student.s_id HAVING count<(SELECT COUNT(1) FROM course);




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

SELECT * FROM

student

WHERE s_id IN

(SELECT DISTINCT a.s_id FROM score a WHERE a.c_id 

IN

(SELECT a.c_id FROM score a WHERE a.s_id='01'));




**7、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩**

SELECT

a.s_id,a.s_name,ROUND(AVG(b.score))

FROM

student a

LEFT JOIN

score b ON a.s_id = b.s_id

WHERE a.s_id IN

(SELECT s_id from score WHERE score<60 GROUP BY s_id HAVING count(1)>=2)

GROUP BY a.s_id,a.s_name




**8、查询所有学生的课程及分数情况；**

SELECT

a.s_id,a.s_name,

SUM(CASE c.c_name WHEN '内功' THEN b.score ELSE 0 END) AS '内功',

SUM(CASE c.c_name WHEN '剑法' THEN b.score ELSE 0 END) AS '剑法',

SUM(CASE c.c_name WHEN '拳法' THEN b.score ELSE 0 END) AS '拳法',

SUM(b.score) as  '总分'

FROM

student a

LEFT JOIN

score b ON a.s_id = b.s_id

LEFT JOIN

course c ON b.c_id = c.c_id

GROUP BY a.s_id,a.s_name




**9、查询选修"张三丰"老师所授课程的学生中，成绩最高的学生信息及其成绩**

SELECT

s_id,MAX(score)

FROM

score

WHERE

c_id

(SELECT c_id FROM teacher JOIN course 

ON teacher.t_id=course.t_id WHERE t_name='张三丰')




**10、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩**

SELECT * FROM score a,score b where a.c_id != b.c_id and a.score = b.score




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

SELECT

c_id,COUNT(*) AS total

FROM

score

GROUP BY c_id HAVING total>4 ORDER BY total DESC,c_id ASC




**12、查询选修了全部课程的学生信息**

SELECT

* FROM student WHERE s_id

(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(*)=(SELECT COUNT(*) FROM course))




[]( )基础

=============================================================



**1、查询不及格的课程**

SELECT

a.s_id,a.c_id,b.c_name,a.score

FROM

score a

LEFT JOIN

course b ON a.c_id = b.c_id

WHERE a.score<60




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

SELECT

a.s_id,b.s_name

FROM

score a

LEFT JOIN

student b ON a.s_id = b.s_id

WHERE a.c_id = ‘01’ AND a.score>80




**3、查询每个同学的姓名、选课数、总成绩**

SELECT

s_name,COUNT(c_id),SUM(score)

FROM

student a

LEFT JOIN

score b ON a.s_id=b.s_id

GROUP BY b.s_id




**4、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩**

SELECT

s_name,ROUND(avg(score),2) AS avg

FROM

student

JOIN

score ON student.s_id=score.s_id

GROUP BY score.s_id HAVING avg>60




**5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩**

SELECT student.s_name,COUNT(c_id),IFNULL(SUM(score),0) AS sum FROM student LEFT JOIN score ON student.s_id=score.s_id GROUP BY student.s_id ORDER BY sum DESC




**6、查询"张"姓老师的数量**

SELECT COUNT(t_id) FROM teacher WHERE t_name LIKE ‘张%’




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

SELECT

a.*,b.c_id,b.score

FROM

student a,score b

WHERE

a.s_id=b.s_id

AND

b.c_id='01' AND b.score<60

ORDER BY

b.score DESC;




**8、查询任何一门课程成绩在70分以上的姓名、课程名称和分数**

SELECT

a.s_name,b.c_name,c.score

FROM

course b

LEFT JOIN

score c ON b.c_id = c.c_id

LEFT JOIN

student a ON a.s_id=c.s_id

WHERE c.score>=70




**9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息**

SELECT a.* FROM

student a,score b,score c

WHERE a.s_id = b.s_id AND a.s_id = c.s_id AND b.c_id=‘01’ AND c.c_id=‘02’;




**10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息**

SELECT a.* FROM

student a

WHERE

a.s_id

(SELECT s_id FROM score WHERE c_id='01' )

AND

a.s_id NOT IN(SELECT s_id FROM score WHERE c_id='02')




**11、查询男生、女生人数**

SELECT s_sex,COUNT(s_sex) FROM student GROUP BY s_sex




**12、查询名字中含有"圆"字的学生信息**

SELECT * FROM student WHERE s_name LIKE ‘%圆%’;




**13、查询同名同性学生名单，并统计同名人数**

SELECT

a.s_name,a.s_sex,count(*)

FROM

student a JOIN student b

a.s_id !=b.s_id and a.s_name = b.s_name

AND

a.s_sex = b.s_sex

GROUP BY a.s_name,a.s_sex




**14、查询1990年出生的学生名单**

SELECT s_name FROM student WHERE s_birth LIKE ‘1337%’




**15、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列**

SELECT

c_id,ROUND(AVG(score),2) AS avg_score

FROM

score

GROUP BY c_id ORDER BY avg_score DESC,c_id ASC




**16、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩**

SELECT

a.s_id,b.s_name,ROUND(avg(a.score),2) AS avg

FROM

score a

LEFT JOIN

student b on a.s_id=b.s_id

GROUP BY s_id HAVING avg>=85




**17、查询课程名称为"内功"，且分数低于60的学生姓名和分数**

SELECT

a.s_name,b.score

FROM

score b

JOIN

student a ON a.s_id=b.s_id

WHERE

b.c_id=(SELECT c_id FROM course WHERE c_name ='内功')

AND b.score<60




**18、求每门课程的学生人数**

SELECT c_id,count(*) FROM score GROUP BY c_id;




**19、检索至少选修两门课程的学生学号**

SELECT s_id,count(*) AS count FROM score GROUP BY s_id HAVING count>=2




[]( )数据初始化

================================================================

– Table structure for course

DROP TABLE IF EXISTS student;

CREATE TABLE student (

s_id varchar(20) NOT NULL,

s_name varchar(20) NOT NULL DEFAULT ‘’,

s_birth varchar(20) NOT NULL DEFAULT ‘’,

s_sex varchar(10) NOT NULL DEFAULT ‘’,

PRIMARY KEY (s_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Records of student

INSERT INTO student VALUES (‘01’, ‘张无忌’, ‘1337-06-17’, ‘男’);

INSERT INTO student VALUES (‘02’, ‘宋青书’, ‘1333-12-21’, ‘男’);

INSERT INTO student VALUES (‘03’, ‘丁敏君’, ‘1336-06-21’, ‘女’);

INSERT INTO student VALUES (‘04’, ‘周芷若’, ‘1340-09-26’, ‘女’);

INSERT INTO student VALUES (‘05’, ‘圆真’, ‘1320-12-01’, ‘男’);

INSERT INTO student VALUES (‘06’, ‘圆觉’, ‘1316-03-01’, ‘男’);

INSERT INTO student VALUES (‘07’, ‘殷梨亭’, ‘1310-07-01’, ‘男’);

INSERT INTO student VALUES (‘08’, ‘纪晓芙’, ‘1310-01-20’, ‘女’);

– Table structure for course

DROP TABLE IF EXISTS course;

CREATE TABLE course (

c_id varchar(20) NOT NULL,

c_name varchar(20) NOT NULL DEFAULT ‘’,

t_id varchar(20) NOT NULL,

PRIMARY KEY (c_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Records of course

INSERT INTO course VALUES (‘01’, ‘内功’, ‘03’);

INSERT INTO course VALUES (‘02’, ‘剑法’, ‘02’);

INSERT INTO course VALUES (‘03’, ‘拳法’, ‘01’);

– Table structure for teacher

DROP TABLE IF EXISTS teacher;

CREATE TABLE teacher (

t_id varchar(20) NOT NULL,

t_name varchar(20) NOT NULL DEFAULT ‘’,

PRIMARY KEY (t_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Records of teacher

INSERT INTO teacher VALUES (‘01’, ‘张三丰’);

INSERT INTO teacher VALUES (‘02’, ‘灭绝师太’);

INSERT INTO teacher VALUES (‘03’, ‘空见’);

– Table structure for score

DROP TABLE IF EXISTS score;

CREATE TABLE score (

s_id varchar(20) NOT NULL,

c_id varchar(20) NOT NULL,

score int(3) DEFAULT NULL,

PRIMARY KEY (s_id,c_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

面试题总结

其它面试题（springboot、mybatis、并发、java中高级面试总结等）

– Table structure for teacher

DROP TABLE IF EXISTS teacher;

CREATE TABLE teacher (

t_id varchar(20) NOT NULL,

t_name varchar(20) NOT NULL DEFAULT ‘’,

PRIMARY KEY (t_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

– Records of teacher

INSERT INTO teacher VALUES (‘01’, ‘张三丰’);

INSERT INTO teacher VALUES (‘02’, ‘灭绝师太’);

INSERT INTO teacher VALUES (‘03’, ‘空见’);

– Table structure for score

DROP TABLE IF EXISTS score;

CREATE TABLE score (

s_id varchar(20) NOT NULL,

c_id varchar(20) NOT NULL,

score int(3) DEFAULT NULL,

PRIMARY KEY (s_id,c_id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

面试题总结

其它面试题（springboot、mybatis、并发、java中高级面试总结等）

[外链图片转存中…(img-NyW5irdr-1718870317816)]

[外链图片转存中…(img-FWYAdF4c-1718870317817)]

[外链图片转存中…(img-nAccJq0T-1718870317817)]