1. 全栈程序员必看首页

50道经典MySQL练习题（含解答）

全栈程序员-用户IM • • 未分类

50道经典MySQL练习题（含解答）本文摘要：本篇主要分享50道经典MySQL练习题（含解答）。

大家好，又见面了，我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。

Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺

在这里插入图片描述

✅✅作者主页：?孙不坚1208的博客

??精选专栏：?MySQL从入门到入土（持续更新中）

?? 本文摘要：本篇主要分享50道经典MySQL练习题（含解答）

??觉得文章还不错的话欢迎大家点赞?➕收藏⭐️➕评论?支持博主?

文章目录

数据表介绍

--1.学生表
Student(SId,Sname,Sage,Ssex)
--SId 学⽣编号,Sname 学⽣姓名,Sage 出⽣年⽉,Ssex 学⽣性别
--2.课程表
Course(CId,Cname,TId)
--CId 课程编号,Cname 课程名称,TId 教师编号
--3.教师表
Teacher(TId,Tname)
--TId 教师编号,Tname 教师姓名
--4.成绩表
SC(SId,CId,score)
--SId 学⽣编号,CId 课程编号,score 分数

表中数据如下

-- 学⽣表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex
varchar(10));
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙⻛' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '⼥');
insert into Student values('06' , '吴兰' , '1992-01-01' , '⼥');
insert into Student values('07' , '郑⽵' , '1989-01-01' , '⼥');
insert into Student values('09' , '张三' , '2017-12-20' , '⼥');
insert into Student values('10' , '李四' , '2017-12-25' , '⼥');
insert into Student values('11' , '李四' , '2012-06-06' , '⼥');
insert into Student values('12' , '赵六' , '2013-06-13' , '⼥');
insert into Student values('13' , '孙七' , '2014-06-01' , '⼥');
-- 科⽬表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values('01' , '语⽂' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
-- 教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
-- 成绩表 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);

50道练习题目

1.查询” 01 “课程比” 02 “课程成绩高的学生的信息及课程分数
SELECT stu.*,s.score FROM
    student AS stu
    JOIN (
            SELECT
                s1.sid,
                s1.score 
            FROM
                ( SELECT sid, score FROM sc WHERE Cid = 01 ) AS s1
                JOIN 
                ( SELECT sid, score FROM sc WHERE Cid = 02 ) AS s2 ON s1.sid = s2.sid 
            WHERE
                s1.score > s2.score 
    ) AS s ON stu.sid = s.sid;

+------+--------+---------------------+------+-------+
| SId  | Sname  | Sage                | Ssex | score |
+------+--------+---------------------+------+-------+
| 02   | 钱电   | 1990-12-21 00:00:00 | 男   |  70.0 |
| 04   | 李云   | 1990-12-06 00:00:00 | 男   |  50.0 |
+------+--------+---------------------+------+-------+
2.查询同时存在” 01 “课程和” 02 “课程的情况
SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM
(SELECT sid,score from sc WHERE cid=01) as s1
JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
on s1.sid = s2.sid;

+------+----------+----------+
| sid  | 01_score | 02_score |
+------+----------+----------+
| 01   |     80.0 |     90.0 |
| 02   |     70.0 |     60.0 |
| 03   |     80.0 |     80.0 |
| 04   |     50.0 |     30.0 |
| 05   |     76.0 |     87.0 |
+------+----------+----------+
5 rows in set (0.00 sec)
3.查询存在” 01 “课程但可能不存在” 02 “课程的情况(不存在时显示为 null )
`mysql SELECT s1.sid,s1.score as 01_score,s2.score as 02_score FROM (SELECT sid,score from sc WHERE cid=01) as s1 LEFT JOIN (SELECT sid,score from sc WHERE cid=02) as s2 on s1.sid = s2.sid;`
4.查询不存在” 01 “课程但存在” 02 “课程的情况
SELECT s2.sid,s1.score as 01_score,s2.score as 02_score FROM
(SELECT sid,score from sc WHERE cid=01) as s1
RIGHT JOIN
(SELECT sid,score from sc WHERE cid=02) as s2
on s1.sid = s2.sid;
5.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT stu.sid,stu.`sname`,s1.avg_score
from student as stu JOIN 
(SELECT sid,avg(score) as avg_score from sc GROUP BY sid) as s1
on stu.sid = s1.sid
WHERE s1.avg_score >60;
6.查询在 SC 表存在成绩的学生信息
SELECT distinct stu.* from student as stu,sc WHERE stu.SId=sc.SId
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT stu.SId,stu.Sname,count(sc.SId) as '选课总数',sum(sc.score) as '所有课程的总成绩' 
from student as stu LEFT join sc on stu.SId = sc.SId GROUP BY stu.SId,stu.sname;
8.查询「李」姓老师的数量
SELECT count(*) FROM teacher WHERE tname like '李%'
9.查询学过「张三」老师授课的同学的信息
SELECT s1.* FROM 
(SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
JOIN 
(SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
on s1.cid = c1.cid WHERE c1.tname = '张三'
10.查询没有学全所有课程的同学的信息
SELECT stu.* FROM student as stu 
where sid not in 
(SELECT s1.sid FROM (SELECT sid,count(sid) as count_sid FROM sc GROUP BY sid) as s1 WHERE s1.count_sid=3)
11.查询至少有一门课与学号为” 01 “的同学所学相同的同学的信息
SELECT DISTINCT stu.* from 
student as stu JOIN sc on stu.sid = sc.SId 
WHERE sc.CId 
in
(SELECT cid FROM sc where sid=01)
12.查询和” 01 “号的同学学习的课程 完全相同的其他同学的信息
SELECT stu.* FROM student as stu JOIN
(SELECT s2.sid FROM sc as s1 JOIN sc as s2 on s1.cid = s2.cid and s1.sid=01 and s2.sid!=01
GROUP BY s2.sid HAVING count(s2.cid)=(SELECT count(*) from sc where sid=01)) as s
on stu.SId = s.sid
13.查询没学过”张三”老师讲授的任一门课程的学生姓名
SELECT * from student  
WHERE SId not in 
(SELECT s1.sid FROM 
(SELECT stu.*,sc.CId from student as stu join sc on stu.SId = sc.SId) as s1 
JOIN 
(SELECT teacher.Tname,course.cid FROM course join teacher on course.tid = teacher.Tid) as c1 
on s1.cid = c1.cid WHERE c1.tname = '张三')
14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
SELECT stu.sname,stu.sid,s1.avg_score from 
student as stu 
JOIN 
(SELECT sid,AVG(score) as avg_score from sc where score<60 GROUP BY sid HAVING count(*)>=2) as s1 
on stu.sid = s1.sid
15.检索” 01 “课程分数小于 60，按分数降序排列的学生信息16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT * FROM student WHERE sid in (SELECT sid from sc WHERE cid=01 and score<60 ORDER BY score DESC)
16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT sc.*,s2.avg_score 
FROM sc  
join (SELECT sid,AVG(score) as avg_score from sc GROUP BY sid) as s2
on sc.sid = s2.sid
ORDER BY s2.avg_score DESC
17.查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修⼈数，查询结果按⼈数降序排列，若人数相同，按课程号升序排列
SELECT sc.cid,course.Cname,max(sc.score) as '最高分',min(sc.score) as '最低分',
AVG(sc.score) as '平均分',count(sc.CId) as '选修人数',
SUM(case when sc.score>=60 then 1 else 0 end)/count(sc.CId) as '及格率',
SUM(case when sc.score>=70 and sc.score<80 then 1 else 0 end)/count(sc.CId) as '中等率',
SUM(case when sc.score>=80 and sc.score<90 then 1 else 0 end)/count(sc.CId) as '优良率',
SUM(case when sc.score>=90 then 1 else 0 end)/count(sc.CId) as '优秀率'
from sc,course WHERE sc.CId=course.CId 
GROUP BY cid,course.Cname
ORDER BY '选修人数' DESC,sc.cid;
18.按各科平均成绩进行排序，并显示排名， Score 重复时保留名次空缺
select s2.cid,s2.avg_sc,count(s1.avg_sc) as rank
from 
(SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s1
join
(SELECT cid,ROUND(AVG(score),2) as avg_sc from sc GROUP BY cid ) as s2
on s1.avg_sc>=s2.avg_sc and s1.cid = s1.cid
group by s2.cid, s2.avg_sc
order by rank;
19.按各科平均成绩进行排序，并显示排名， Score 重复时不保留名次空缺
SELECT b.cid,b.avg_sc,@i:=@i+1 as rank 
from (SELECT @i :=0) as a,
(SELECT cid,round(avg(score),2) as avg_sc from sc GROUP BY cid ORDER BY avg_sc desc) as b
20.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
SELECT s2.sid,s2.sum_sc,COUNT(s2.sum_sc) as rank from
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s1
JOIN 
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sid) as s2
on s1.sum_sc>=s2.sum_sc
group by s2.sid,s2.sum_sc
order by rank;
21.查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
SELECT b.sid,b.sum_sc,@i:=@i+1 as rank 
from (SELECT @i :=0) as a,
(SELECT sid,sum(score) as sum_sc from sc GROUP BY sid ORDER BY sum_sc desc) as b
22.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0]及所占百分比
SELECT sc.CId,c.cname,
SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end) as '[100-85]',
SUM(case when sc.score>85 and sc.score<=100 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end) as '[85-70]',
SUM(case when sc.score>70 and sc.score<=85 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end) as '[70-60]',
SUM(case when sc.score>60 and sc.score<=70 then 1 else 0 end)/count(sc.CId) as '百分比',
SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end) as '[60-0]',
SUM(case when sc.score>0 and sc.score<=60 then 1 else 0 end)/count(sc.CId) as '百分比'
FROM sc join course as c on sc.CId=c.cid
GROUP BY sc.CId,c.cname
23.查询各科成绩前三名的记录
(select CId,score from SC where CId = '01' order by score desc limit 3)
union all
(select CId,score from SC where CId = '02' order by score desc limit 3)
union all
(select CId,score from SC where CId = '03' order by score desc limit 3)
24.查询每门课程被选修的学生数
SELECT cid,count(cid) as 选课人数 FROM sc GROUP BY CId;
25.查询出只选修两门课程的学生学号和姓名
SELECT s2.sid,s2.sname FROM
(SELECT sid,count(sid) as '选修课程数'  FROM sc GROUP BY SId) as s1
JOIN student as s2 on s1.SId = s2.SId
WHERE s1.选修课程数=2
26.查询男生、女生人数
SELECT ssex,count(SId) FROM student GROUP BY ssex
27.查询名字中含有「风」字的学生信息
SELECT * FROM student WHERE Sname like '%风%'
28.查询同名同姓学生名单，并统计同名⼈数
select sname,count(*) as 人数
from student
group by sname
having count(*)>=2;
29.查询 1990 年出生的学生名单
SELECT * FROM student WHERE YEAR(sage)=1990
30.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
SELECT cid,AVG(score) FROM sc GROUP BY CId ORDER BY AVG(score),CId
31.查询平均成绩⼤于等于 85 的所有学生的学号、姓名和平均成绩
SELECT s1.sid,s1.sname,s2.平均分
FROM student as s1 
join
(SELECT sid,AVG(score) as '平均分' FROM sc GROUP BY sid) as s2
on s1.SId = s2.SId
WHERE s2.平均分>=85
32.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
SELECT s2.sname,s1.score
FROM
course as c1 JOIN sc as s1 on c1.cid=s1.CId
JOIN student as s2 on s1.sid =s2.SId
WHERE c1.cname='数学' and s1.score<60
33.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
SELECT s2.sname,s2.sid,c1.cname,s1.score
FROM
 sc as s1
right JOIN student as s2 on s1.sid =s2.SId
left JOIN course as c1 on s1.cid=c1.cid
GROUP BY s2.sid,s2.sname,c1.cname,s1.score
34.查询任何⼀门课程成绩在 70 分以上的姓名、课程名称和分数
SELECT s2.sname,c1.cname,s1.score
FROM
 sc as s1
JOIN student as s2 on s1.sid =s2.SId
JOIN course as c1 on s1.cid=c1.cid
WHERE s1.score>70
35.查询不及格的课程
SELECT s2.sname,c1.cname,s1.score
FROM
 sc as s1
JOIN student as s2 on s1.sid =s2.SId
JOIN course as c1 on s1.cid=c1.cid
WHERE s1.score<60
36.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT s1.sid,s2.Sname
FROM
 (SELECT sid,score FROM sc WHERE cid='01') as s1
JOIN student as s2 on s1.sid =s2.SId
WHERE score>=80


select SC.SId,Student.Sname
from SC join Student on SC.SId = Student.SId
where SC.Score > 80 and SC.CId = '01';
37.求每门课程的学生人数
SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid
38.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s2.sname,s2.SId,s1.score
FROM
course as c1 JOIN sc as s1 on c1.cid=s1.CId
JOIN student as s2 on s1.sid =s2.SId
JOIN teacher as t1 on c1.tid=t1.tid
WHERE t1.tname='张三'
ORDER BY s1.score DESC  LIMIT 1
39.成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
SELECT s2.sname,s2.sid,s1.score FROM student as s2 JOIN sc as s1 on s1.SId =s2.SId
WHERE score=(
SELECT max(score) FROM sc as s1 WHERE cid=(SELECT c1.cid FROM course as c1 JOIN teacher as t1 on c1.tid = t1.tid WHERE t1.tname = '张三'))

-- 其它⽅案
select a.sname,b.score
from student a join sc b on a.sid=b.sid
and b.cid in (select cid from course where tid in (select tid from teacher
where tname='张三'))
join (select cid,max(score) m from sc group by cid) c on b.cid=c.cid and
b.score=c.m;
40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT sid,cid,score FROM sc WHERE sid=(select sid from (select sid,score from sc group by sid,score) as s1
group by sid having count(sid)=1)

-- 其他方案
select distinct s1.SId,s1.CId,s1.Score
from SC s1 join SC s2
on s1.CId != s2.CId and s1.score = s2.score
group by s1.SId,s1.CId,s1.Score
41.查询每门课程成绩最好的前两名
SELECT s1.* FROM sc s1 WHERE
(
SELECT COUNT(1) FROM sc s2 WHERE
s1.cid=s2.cid AND s2.score>=s1.score
)<=2
ORDER BY s1.cid,s1.score DESC;

-- 其他方案
(select CId,score from SC where CId = '01' order by score desc limit 2)
union all
(select CId,score from SC where CId = '02' order by score desc limit 2)
union all
(select CId,score from SC where CId = '03' order by score desc limit 2)
42.统计每门课程的学生选修人数（超过 5 ⼈的课程才统计）。
SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>5
43.检索至少选修两门课程的学生学号
SELECT cid,count(cid) as '学生人数' FROM sc GROUP BY cid HAVING count(cid)>=5
44.查询选修了全部课程的学生信息
SELECT sid FROM sc GROUP BY SId HAVING count(sid)=3
45.查询各学生的年龄，只按年份来算
SELECT sname,year(now())-YEAR(sage) as '年龄' FROM student
46.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减⼀

TIMESTAMPDIFF() 从日期时间表达式中减去间隔 https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html

select student.sid, student.sname,student.ssex, sage,
timestampdiff(year,sage,now()) as '按月日计算',  
year(now())-year(sage) as '按年份计算'  
from student;
47.查询本周过生日的学生

返回日期从范围内的数字日历星期1到53

select sid, sname,ssex, sage
from student
WHERE WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())
48.查询下周过生日的学生
select sid, sname,ssex, sage
from student
where WEEKOFYEAR(student.Sage)=WEEKOFYEAR(CURDATE())+1
49.查询本月过生日的学生
select *
from student
where month(student.sage)=month(now());
50.查询下月过生日的学生
select *
from student
where month(student.sage)=month(now())+1;

在这里插入图片描述

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

发布者：全栈程序员-用户IM，转载请注明出处：https://javaforall.cn/192061.html原文链接：https://javaforall.cn

【正版授权，激活自己账号】： Jetbrains全家桶Ide使用，1年售后保障，每天仅需1毛

【官方授权 正版激活】： 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

赞 (0)
全栈程序员-用户IM全栈程序员-用户IM
0 0


相关推荐

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号