Mysql面试必看45题

最新推荐文章于 2022-07-31 17:59:02 发布

百年城

最新推荐文章于 2022-07-31 17:59:02 发布

阅读量209

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/lfh996/article/details/108020612

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

Mysql练习

一、数据表和数据

学生表 *Student*

create table Student(
SId varchar(10) ,
Sname varchar(10),
Sage datetime,
Ssex varchar(10)
);
insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男')
insert into Student values('02' , N'钱电' , '1990-12-21' , N'男')
insert into Student values('03' , N'孙风' , '1990-05-20' , N'男')
insert into Student values('04' , N'李云' , '1990-08-06' , N'男')
insert into Student values('05' , N'周梅' , '1991-12-01' , N'女')
insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女')
insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女')
insert into Student values('08' , N'王菊' , '1990-01-20' , N'女')

科目表 Course

create table Course(
CId varchar(10),
Cname nvarchar(10),
TId varchar(10)
); 
insert into Course values('01' , N'语文' , '02')
insert into Course values('02' , N'数学' , '01')
insert into Course values('03' , N'英语' , '03')

教师表 Teacher

create table Teacher(TId varchar(10),Tname varchar(10))
insert into Teacher values('01' , N'张三')
insert into Teacher values('02' , N'李四')
insert into Teacher values('03' , N'王五')

成绩表 SC

create table SC(
SId varchar(10),
CId varchar(10),
score decimal(18,1)
); 
insert into SC values('01' , '01' , 80)
insert into SC values('01' , '02' , 90)
insert into SC values('01' , '03' , 99)
insert into SC values('02' , '01' , 70)
insert into SC values('02' , '02' , 60)
insert into SC values('02' , '03' , 80)
insert into SC values('03' , '01' , 80)
insert into SC values('03' , '02' , 80)
insert into SC values('03' , '03' , 80)
insert into SC values('04' , '01' , 50)
insert into SC values('04' , '02' , 30)
insert into SC values('04' , '03' , 20)
insert into SC values('05' , '01' , 76)
insert into SC values('05' , '02' , 87)
insert into SC values('06' , '01' , 31)
insert into SC values('06' , '03' , 34)
insert into SC values('07' , '02' , 89)
insert into SC values('07' , '03' , 98)

二、题目

查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数

答案：

SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
WHERE b.score>c.score;

思路：

#查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
#1.查询学生的信息及课程分数(使用内连接将学生的信息表（student）及课程分数表（sc）通过and b.CId=01查询01的成绩)
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
#2.在一的基础上，通过再一次内连接拼接CId=02的列
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
#3.添加条件" 01 "课程比" 02 "课程成绩高
SELECT *  FROM student as a INNER JOIN  sc as b on a.sid=b.sid and b.CId=01
INNER JOIN sc c on a.Sid=c.SId and  c.CId=02
WHERE b.score>c.score;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况（题目解析：查询同时拥有01和02课程成绩的学生 逻辑：有成绩的学生肯定选修了课程）

答案1：

#子查询
SELECT * FROM 
(SELECT * FROM Sc where cid=01) a
inner
JOIN 
(SELECT * FROM Sc where cid=02) b
on  a.sid=b.sid;

答案1思路：

#1.查询选修课程1的学生
SELECT * FROM Sc where cid=01
#2.查询选修课程2的学生
SELECT * FROM Sc where cid=02
#3.将1和2作为子查询，同时存在" 01 "课程和" 02 "课程作为条件，即1和2的sid相等
SELECT * FROM 
(SELECT * FROM Sc where cid=01) a
inner
JOIN 
(SELECT * FROM Sc where cid=02) b
on  a.sid=b.sid;

答案2

SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
WHERE a.CId=01 and 
b.CId=02

答案2思路

#1.将sc和sc自连接
SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
#2.别名为a的sc取01，别名为a的sc取02进行连接
SELECT * FROM sc a INNER JOIN sc b on a.SId=b.SId
WHERE
a.CId=01 
and 
b.CId=02

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )（题目解析：查询拥有01但是没有02课程成绩的学生 逻辑：有成绩的学生肯定选修了课程，没有成绩的可能没有选修（为啥是可能不是肯定?选修了课程但被取消成绩?））

答案

SELECT * FROM  sc a LEFT JOIN  sc b
ON a.SId=b.SId and b.CId=02
where a.CId=01

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况

SELECT * FROM 
(SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)) a
INNER JOIN sc b
on a.sid=b.sid and b.cid=02

思路

#1.SELECT sid FROM sc where cid=0查询拥有课程id的所有学生的id
SELECT sid FROM sc where cid=01
#2.查询不拥有课程id的所有学生的信息
SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)
#3.内联a和b且b用于课程2
SELECT * FROM 
(SELECT * FROM sc where sid not in(SELECT sid FROM sc where cid=01)) a
INNER JOIN sc b
on a.sid=b.sid and b.cid=02

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

SELECT * FROM
(SELECT sid,sname FROM student) a
LEFT JOIN
(SELECT sid,avg(score) as avg FROM sc GROUP BY sid) b
on a.Sid=b.Sid
HAVING b.avg>60

\3. 查询在 SC 表存在成绩的学生信息

SELECT * FROM student WHERE Sid  in (SELECT distinct sid FROM sc)

\4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

SELECT * FROM
(SELECT sid,sname FROM student) a
LEFT JOIN 
(SELECT sid,sum(cid),SUM(score) FROM sc GROUP BY sid) b
on a.sid=b.Sid

4.1 查有成绩的学生信息

SELECT * FROM student WHERE Sid in (SELECT distinct sid FROM sc)

\5. 查询「李」姓老师的数量

SELECT * FROM teacher WHERE tname like '%李%';
SELECT count(1) as cons FROM teacher WHERE tname like '%李%';

\6. 查询学过「张三」老师授课的同学的信息

SELECT d.sid,d.sname,a.tname FROM 
(SELECT * FROM teacher) a
INNER JOIN 
(SELECT * FROM course) b
on a.tid=b.tid

INNER JOIN 
(SELECT * FROM sc) c
on b.cid=c.cid

INNER JOIN
(SELECT * FROM student) d
on c.sid=d.sid
where a.tname="张三";

\7. 查询没有学全所有课程的同学的信息

SELECT * FROM 
(SELECT * FROM student) a
INNER JOIN 
(SELECT sid,count(score) as count FROM sc GROUP BY sid)b
on a.sid=b.sid
HAVING b.count != (SELECT COUNT(1) FROM course)

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

SELECT  * FROM 
student a
INNER JOIN
(SELECT  * FROM sc where cid in (SELECT cid FROM sc where sid=01) GROUP BY sid) b
on a.Sid=b.sid

SELECT DISTINCT a.* FROM 
student a
INNER JOIN
sc b
on a.Sid=b.sid
where b.cid in (SELECT cid FROM sc where sid=01)

\9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息

SELECT * FROM 
sc
where 
sid not in (SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01))
and sid !=01
GROUP BY sid
HAVING count(1) =(SELECT count(1) FROM sc where sid=01)

思路

#1.查询 01 号的同学学习的课程
SELECT cid FROM sc where sid=01
#2.查询学习了01 号的同学没有学习的课程的同学
SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01)
#3.条件 和01 号的同学学习的课程总数相同
count(1) =(SELECT count(1) FROM sc where sid=01)
#4.没有学习1号同学没有学习的课程且课程数量相同可认为和01 号的同学学习的课程完全相同
SELECT * FROM 
sc
where 
sid not in (SELECT sid FROM sc where cid not in(SELECT cid FROM sc where sid=01) )
and sid !=01
GROUP BY sid
HAVING count(1) =(SELECT count(1) FROM sc where sid=01)

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

#1.查询张三的教师号tid
SELECT tid FROM teacher where tname='张三'
#2.通过tid查询张三的所有课程的cid
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='张三') a
INNER JOIN 
course b
on a.tid=b.tid
#3.查询cid在张三课程中的学生sid
SELECT sid FROM sc where cid  in(
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='张三') a
INNER JOIN 
course b
on a.tid=b.tid
)GROUP BY sid
#4.查询学生表和成绩表的内联表，且成绩表中sid不在3中
SELECT DISTINCT c.Sname
FROM
student c
INNER JOIN
sc d
on c.Sid=d.SId and d.sid not in (SELECT sid FROM sc where cid  in(
SELECT b.cid FROM
(SELECT tid FROM teacher where tname='张三') a
INNER JOIN 
course b
on a.tid=b.tid
)GROUP BY sid)

\11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

#查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
#1.查询学生不及格课程总数
SELECT sid FROM sc where score<60  GROUP BY sid HAVING COUNT(1) >=2;
#2.查询查询学生不及格课程总数大于2的学生sid 
SELECT sid FROM sc where score<60  GROUP BY sid
#3.查询学号，姓名及其平均成绩且学号sid在2中
SELECT a.Sname,b.* FROM
student a
INNER JOIN
(
SELECT sid,avg(score) as avgscore FROM sc GROUP BY sid HAVING sid  in (SELECT sid FROM sc where score<60  GROUP BY sid HAVING COUNT(1) >=2)
)b
on 
a.sid=b.sid

\12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

SELECT a.*,b.score FROM 
student A
INNER JOIN
(SELECT * FROM SC WHERE CID=01 and score<60)B
ON a.sid=b.sid
ORDER BY score desc

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

SELECT * FROM sc a
LEFT JOIN
(SELECT sid,avg(score) as avg1 FROM sc GROUP BY sid ORDER BY  avg1) b
on a.Sid=b.sid
ORDER BY b.avg1 desc

\14. 查询各科成绩最高分、最低分和平均分：

以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

#2.sum(case when score>=70 and score<80 THEN 1 else 0 end)/count(1) as 中等率, sum循环，满足THEN 1（加一）不满足else 0（+0）
SELECT a.CId,a.Cname,b.* FROM
course a
RIGHT JOIN 
(SELECT cid,max(score) as max_score,min(score) as min_score,avg(score) as avg_score,COUNT(1) as 选修人数,
sum(case when score>=60 THEN 1 else 0 end)/count(1)as 及格率,
sum(case when score>=70 and score<80 THEN 1 else 0 end)/count(1) as 中等率,
sum(case when score>=80 and score<90 THEN 1 else 0 end)/count(1) as 优良率,
sum(case when score>=90  THEN 1 else 0 end)/count(1) as 优秀率
FROM sc GROUP BY cid)b
on a.cid=b.cid
ORDER BY b.选修人数 desc,b.cid asc

\15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺

SELECT 
sid,cid,score,
@rank:=@rank+1
FROM sc,(SELECT @rank:=0) as t ORDER BY score desc;

15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次

SELECT a.sid,a.cid,a.score,
case when @sco=score THEN @rank:=@rank
else @rank:=@rank+1 END as rn,
@sco:=score
FROM 
sc as a,
(SELECT @rank:=0,@sco:=null) as b
ORDER BY score desc;

SELECT a.sid,a.cid,a.score,
case when @sco=score THEN @rank:=@rank
     when @sco:=score then @rank:=@rank+1 
		 END  as rm
FROM 
sc as a,
(SELECT @rank:=0,@sco:=null) as b
ORDER BY score desc;

\16. 查询学生的总成绩，并进行排名，总分重复时保留名次空缺

SELECT sid,sum(score)as 总成绩,@rank:=@rank+1 as 排名  FROM sc,(SELECT @rank:=0) as b GROUP BY sid ORDER BY 总成绩 desc;

16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺

SELECT 
sid,sum(score)as 总成绩,
case when score=@sco then @rank:=@rank
		 when @sco:=score then @rank:=@rank+1
		 END as 排名
FROM sc,(SELECT @rank:=0,@sco:=null) as b 
GROUP BY sid 
ORDER BY 总成绩 desc;

\17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

SELECT a.Cname as 课程名称,b.*
FROM course  as a
INNER JOIN
(SELECT 
cid as 课程编号,
count(1)  as 总人数,
concat( sum(case when score>85 and score<=100 THEN 1 else 0 end)/count(1)*100,'%')  as '[100-85)',
concat(sum(case when score>70 and score<=85 THEN 1 else 0 end)/count(1)*100,'%')  as '[85-70)',
concat(sum(case when score>60 and score<=70 THEN 1 else 0 end)/count(1)*100,'%') as '[70-60)',
concat(sum(case when score>0 and score<=60 THEN 1 else 0 end)/count(1)*100,'%')  as '[60-0]'
FROM sc GROUP BY cid) as b
on a.cid=b.课程编号;

\18. 查询各科成绩前三名的记录（若大于这个成绩的成绩数量少于三，则这条数据为前三名）

SELECT * 
FROM sc as a
where 
(SELECT count(1) FROM sc b where b.cid=a.cid and b.score>a.score)<=3 ORDER BY cid,score desc

\19. 查询每门课程被选修的学生数

SELECT cid,count(1) as 选修的学生数 FROM sc GROUP BY cid;

\20. 查询出只选修两门课程的学生学号和姓名

SELECT a.Sname,b.sid
FROM student as a
INNER JOIN
(SELECT count(1) as aa,sid FROM sc GROUP BY sid HAVING aa=2) as b
on a.sid=b.sid

\21. 查询男生、女生人数

SELECT count(1),Ssex FROM student GROUP BY ssex;

\22. 查询名字中含有「风」字的学生信息

SELECT * FROM student where Sname like '%风%'

\23. 查询同名同性学生名单，并统计同名人数

SELECT count(1) FROM 
student as a
INNER JOIN student as b
on a.Sname=b.Sname and a.Ssex=b.Ssex and a.sid != b.sid;

\24. 查询 1990 年出生的学生名单

SELECT * FROM student where YEAR(Sage)=1990;

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

SELECT cid as 课程编号,avg(score) as 平均成绩 FROM sc GROUP BY cid ORDER BY 平均成绩 desc,课程编号 asc ;

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

SELECT 
b.`学号`,
a.sname as 姓名,
b.`平均成绩`
FROM
student as a
INNER JOIN
(SELECT 
sid as 学号,
avg(score) as 平均成绩
FROM sc GROUP BY sid HAVING 平均成绩>=85) as b
on a.Sid=b.学号;

\27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT a.Sname,b.score
FROM student as a
INNER JOIN
(SELECT score,sid
FROM sc 
WHERE 
cid=(SELECT cid FROM course where Cname='数学')
and score<60) as b
on a.Sid=b.sid
;

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

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
LEFT JOIN
sc as b
on a.SId=b.SId;

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

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
inner JOIN
(SELECT * FROM sc where score>70)as b
on a.SId=b.SId;

\30. 查询不及格的课程

SELECT a.sid,a.sname,b.cid,b.score FROM 
student as a
inner JOIN
(SELECT * FROM sc where score<60)as b
on a.SId=b.SId;

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

SELECT sid,Sname FROM student where sid in(SELECT sid FROM sc where cid=01 and score>=80);

\32. 求每门课程的学生人数

SELECT cid,count(1) FROM sc GROUP BY cid;

\33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT sid,max(score) FROM sc where cid in(SELECT cid FROM course where tid=(SELECT Tid FROM teacher where Tname='张三'))

\34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

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

SELECT DISTINCT * FROM sc as a
INNER JOIN sc as b
on a.sid=b.sid and a.cid != b.cid and a.score =b.score GROUP BY a.sid,a.cid;

\36. 查询每门功成绩最好的前两名

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

SELECT cid,count(1) as 学生人数  FROM sc GROUP BY cid HAVING 学生人数>5;

\38. 检索至少选修两门课程的学生学号

SELECT sid,count(1) as 课程数量  FROM sc GROUP BY sid HAVING 课程数量>2;

\39. 查询选修了全部课程的学生信息

SELECT * FROM student where sid in (SELECT sid FROM sc GROUP BY sid HAVING COUNT(1)=(SELECT COUNT(1) FROM course))

\40. 查询各学生的年龄，只按年份来算

SELECT YEAR(NOW())-YEAR(sage) as age FROM student;

\41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

SELECT *,TIMESTAMPDIFF(YEAR,Sage,NOW())  FROM student;

\42. 查询本周过生日的学生

SELECT * FROM student where week(sage)=WEEK('2020-01-02')

\43. 查询下周过生日的学生

SELECT * FROM student where week(sage)=WEEK('2020-05-15')+1

\44. 查询本月过生日的学生

SELECT * FROM student where MONTH(sage)=MONTH(NOW())

\45. 查询下月过生日的学生

SELECT * FROM student where MONTH(sage)=MONTH('2020-06-01')+1

百年城

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录