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
blank

相关推荐

  • Apache .htaccess规则RewriteCond 和RewriteRule-实操解释说明

    Apache .htaccess规则RewriteCond 和RewriteRule-实操解释说明

    Apache .htaccess规则RewriteCond 和RewriteRule-实操解释说明如果你在看的时候有些迷惑，或许你需要配合下面这篇文章一起看《Apache.htaccess规则说明》https://blog.csdn.net/cplvfx/article/details/94725685该文章转自https://justcoding.iteye.com/blog/547384RewriteCond重写规则的条件RewriteCondSyntax:…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月18日
  • pycharm2022激活码【在线注册码/序列号/破解码】 idea

    pycharm2022激活码【在线注册码/序列号/破解码】

    pycharm2022激活码【在线注册码/序列号/破解码】，https://javaforall.cn/100143.html。详细ieda激活码不妨到全栈程序员必看教程网一起来了解一下吧！

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年3月20日
  • 菜鸟开发—应具备的搜索技巧[通俗易懂]

    菜鸟开发—应具备的搜索技巧[通俗易懂]

    菜鸟开发—应具备的搜索技巧

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年1月25日
  • UFT12.02安装

    UFT12.02安装

    UFT12.02安装1.打开安装程序 2.选择语言3.选择插件4.UFT配置5.安装6.安装完成7.自述文件8.启动UFT9.进入UFT后的界面

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月8日
  • ITDSD- 3.分布式工程学综述

    ITDSD- 3.分布式工程学综述

    ITDSD- 3.分布式工程学综述英文版地址：ITDSD-3.OverviewofDistributedEngineeringSunshuo(sun.shuo@aliyun.com)导论这是关于分布式架构新手入门的第三篇文章。这一篇文章主要简要的介绍分布式工程学在理论上的基本概念，历史和现状，以及未来发展方向。让大家能够了解为什么学习分布式工程学。分布式工程学在计算机科学中的地位，以及分布式工程学要解决的问…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月31日
  • 计算机组成原理知识点总结（第2篇 第3、4章）[通俗易懂]

    计算机组成原理知识点总结（第2篇 第3、4章）[通俗易懂]

    计算机组成原理知识点总结（第2篇 第3、4章）[通俗易懂]基于计算机组成原理（第2版）唐朔飞编著第2篇计算机系统的硬件结构 第3章系统总线总线是连接多个部件（模块）的信息传输线，是各部件共享的传输介质。在某一时刻只允许有一个部件向总线发送信息，但多个部件可以同时从总线上接收相同的信息。总线通信分为异步和同步两大类。总线的定义：为多个功能组件服务的一组公用信息线。按功能分类：地址总线、数据总线、控制总线。按连接部件不同分类：片内总线、系…

    全栈程序员-用户IM 全栈程序员-用户IM
    2022年5月10日

发表回复

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

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