MySQL经典50道练习题
MySQL练习
50道经典SQL练习题
数据表介绍
-
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) ); -
课程表 Course
create table Course( CId varchar(10), Cname nvarchar(10), TId varchar(10) ); -
教师表 Teacher
create table Teacher( TId varchar(10), Tname varchar(10) ); -
成绩表 SC
create table SC( SId varchar(10), CId varchar(10), score decimal(18,1) );
插入数据
-
学⽣表 Student
-- 学生表 Student 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
-- 科⽬表 Course insert into Course values('01' , '语文' , '02'); insert into Course values('02' , '数学' , '01'); insert into Course values('03' , '英语' , '03'); -
教师表 Teacher
-- 教师表 Teacher insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五'); -
成绩表 SC
-- 成绩表 SC 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);
练习题目含答案
1.查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数
通过题目需求可知，需要用到两张表：学生表和成绩表
学生表：
+------+--------+---------------------+------+
| SId | Sname | Sage | Ssex |
+------+--------+---------------------+------+
| 01 | 赵雷 | 1990-01-01 00:00:00 | 男 |
| 02 | 钱电 | 1990-12-21 00:00:00 | 男 |
| 03 | 孙风 | 1990-12-20 00:00:00 | 男 |
| 04 | 李云 | 1990-12-06 00:00:00 | 男 |
| 05 | 周梅 | 1991-12-01 00:00:00 | 女 |
| 06 | 吴兰 | 1992-01-01 00:00:00 | 女 |
| 07 | 郑竹 | 1989-01-01 00:00:00 | 女 |
| 09 | 张三 | 2017-12-20 00:00:00 | 女 |
| 10 | 李四 | 2017-12-25 00:00:00 | 女 |
| 11 | 李四 | 2012-06-06 00:00:00 | 女 |
| 12 | 赵六 | 2013-06-13 00:00:00 | 女 |
| 13 | 孙七 | 2014-06-01 00:00:00 | 女 |
+------+--------+---------------------+------+
成绩表：
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 01 | 02 | 90.0 |
| 01 | 03 | 99.0 |
| 02 | 01 | 70.0 |
| 02 | 02 | 60.0 |
| 02 | 03 | 80.0 |
| 03 | 01 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
| 04 | 01 | 50.0 |
| 04 | 02 | 30.0 |
| 04 | 03 | 20.0 |
| 05 | 01 | 76.0 |
| 05 | 02 | 87.0 |
| 06 | 01 | 31.0 |
| 06 | 03 | 34.0 |
| 07 | 02 | 89.0 |
| 07 | 03 | 98.0 |
+------+------+-------+
查询" 01 “课程⽐” 02 "课程成绩⾼的学⽣的信息及课程分数
分析：
(1)先将所有学生”01“的成绩从成绩表SC中提取出来
select * from SC where CId = '01';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 02 | 01 | 70.0 |
| 03 | 01 | 80.0 |
| 04 | 01 | 50.0 |
| 05 | 01 | 76.0 |
| 06 | 01 | 31.0 |
+------+------+-------+
(2)再将所有”02“的成绩从成绩表SC中提取出来
select * from SC where CId = '02';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 02 | 90.0 |
| 02 | 02 | 60.0 |
| 03 | 02 | 80.0 |
| 04 | 02 | 30.0 |
| 05 | 02 | 87.0 |
| 07 | 02 | 89.0 |
+------+------+-------+
(3)关联：以”01“课程为准，作为左表，与”02“进行左连接
(select * from SC where CId = '01') as t1
left join ------------------------左连接
(select * from SC where CId = '02') as t2
on t1.SId = t2.SId ------------------------关联条件
(4)查询显示关联后的结果（在最前面加select * from）
select * from
(select * from SC where CId = '01') as t1
left join
(select * from SC where CId = '02') as t2
on t1.SId = t2.SId;
+------+------+-------+------+------+-------+
| SId | CId | score | SId | CId | score |
+------+------+-------+------+------+-------+
| 01 | 01 | 80.0 | 01 | 02 | 90.0 |
| 02 | 01 | 70.0 | 02 | 02 | 60.0 |
| 03 | 01 | 80.0 | 03 | 02 | 80.0 |
| 04 | 01 | 50.0 | 04 | 02 | 30.0 |
| 05 | 01 | 76.0 | 05 | 02 | 87.0 |
| 06 | 01 | 31.0 | NULL | NULL | NULL |
+------+------+-------+------+------+-------+
(5)通过上述表格发现，有重复的列名，提取t1的的SId、CId、score和t2的score即可
顺便给t1.score,t2.score起个别名（若不起别名，输出的结果列名一样，不容区分）
内部也做一下修改，不建议使用*
select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
(select SId,CId,score from SC where CId = '01') as t1
left join
(select SId,CId,score from SC where CId = '02') as t2
on t1.SId = t2.SId;
+------+------+---------+---------+
| SId | CId | t1score | t2score |
+------+------+---------+---------+
| 01 | 01 | 80.0 | 90.0 |
| 02 | 01 | 70.0 | 60.0 |
| 03 | 01 | 80.0 | 80.0 |
| 04 | 01 | 50.0 | 30.0 |
| 05 | 01 | 76.0 | 87.0 |
| 06 | 01 | 31.0 | NULL |
+------+------+---------+---------+
(6)最后，结尾加个where条件比较t1score和t2score的大小
select t1.SId,t1.CId,t1.score as t1score,t2.score as t2score from
(select SId,CId,score from SC where CId = '01') as t1
left join
(select SId,CId,score from SC where CId = '02') as t2
on t1.SId = t2.SId
where t1.score > t2.score;
+------+------+---------+---------+
| SId | CId | t1score | t2score |
+------+------+---------+---------+
| 02 | 01 | 70.0 | 60.0 |
| 04 | 01 | 50.0 | 30.0 |
+------+------+---------+---------+
到这里，" 01 “课程⽐” 02 "课程成绩⾼的学⽣SId就出来了
(7)想要知道学⽣SId对应的学生信息和其他的课程成绩，
需要将得出表作为一个整体再与学生表、成绩表进行关联...
因为要和成绩表关联了，上个表中的CId，t1score，t2score就没什么用了，只保留SId
外连接和内连接都可以：
最终代码：
select tt1.SId,tt2.Sname,tt3.CId,tt3.score from
(select t1.SId from
(select SId,CId,score from SC where CId = '01') as t1
left join
(select SId,CId,score from SC where CId = '02') as t2
on t1.SId = t2.SId
where t1.scor
e > t2.score) as tt1
join Student as tt2 on tt1.SId = tt2.SId
join SC as tt3 on tt1.SId = tt3.SId;
执行结果：
+------+--------+------+-------+
| SId | Sname | CId | score |
+------+--------+------+-------+
| 02 | 钱电 | 01 | 70.0 |
| 02 | 钱电 | 02 | 60.0 |
| 02 | 钱电 | 03 | 80.0 |
| 04 | 李云 | 01 | 50.0 |
| 04 | 李云 | 02 | 30.0 |
| 04 | 李云 | 03 | 20.0 |
+------+--------+------+-------+
2.查询同时存在" 01 “课程和” 02 "课程的情况
题目意思：查询既学习”01“课程，也学习了”02“课程的学生（只需要得到他们的编号即可）
分析：需要用到成绩表
只有在成绩表中，通过看哪些学生考了哪个课程，才能知道哪些学生学了哪些课程
(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
（目的是看哪些学生考了”01“课程，从而知道哪些学生学了”01“这门课）
select * from SC where CId = '01';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 02 | 01 | 70.0 |
| 03 | 01 | 80.0 |
| 04 | 01 | 50.0 |
| 05 | 01 | 76.0 |
| 06 | 01 | 31.0 |
+------+------+-------+
(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
（目的是看哪些学生考了”02“课程，从而知道哪些学生学了”02“这门课）
select * from SC where CId = '02';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 02 | 90.0 |
| 02 | 02 | 60.0 |
| 03 | 02 | 80.0 |
| 04 | 02 | 30.0 |
| 05 | 02 | 87.0 |
| 07 | 02 | 89.0 |
+------+------+-------+
(3)将上面得出的两个表进行关联，可以得到哪些学生既学了”01“课程，也学习了”02“课程
不需要用*查询全部，提取我们需要的即可（只需要得到他们的编号即可）
最终代码：
select t1.SId from
(select SId from SC where CId = '01') as t1
join
(select SId from SC where CId = '02') as t2
on t1.SId = t2.SId;
执行结果：
+------+
| SId |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
+------+
3.查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
分析：需要用到成绩表，以”01“为准，进行左连接关联
过程：
(1)先将所有学生学习”01“的课程情况从成绩表SC中提取出来
（目的是看哪些学生考了”01“课程，从而知道哪些学生学了”01“这门课）
select * from SC where CId = '01';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 01 | 80.0 |
| 02 | 01 | 70.0 |
| 03 | 01 | 80.0 |
| 04 | 01 | 50.0 |
| 05 | 01 | 76.0 |
| 06 | 01 | 31.0 |
+------+------+-------+
(2)再将所有学生学习”02“课程情况从成绩表SC中提取出来
（目的是看哪些学生考了”02“课程，从而知道哪些学生学了”02“这门课）
select * from SC where CId = '02';
+------+------+-------+
| SId | CId | score |
+------+------+-------+
| 01 | 02 | 90.0 |
| 02 | 02 | 60.0 |
| 03 | 02 | 80.0 |
| 04 | 02 | 30.0 |
| 05 | 02 | 87.0 |
| 07 | 02 | 89.0 |
+------+------+-------+
(3)以第一个表为准进行左关联，题目需求需要显示null，
那么将左表的SId,CId,score，右表的CId,score提取出来
给t2.CId,t2.score起个别名，利于观看
最终代码：
select t1.SId,t1.CId,t1.score, t2.CId as t2CId, t2.score as t2score from
(select SId,CId,score from SC where CId = '01') as t1
left join
(select SId,CId,score from SC where CId = '02') as t2
on t1.SId = t2.SId;
执行结果：
+------+------+-------+-------+---------+
| SId | CId | score | t2CId | t2score |
+------+------+-------+-------+---------+
| 01 | 01 | 80.0 | 02 | 90.0 |
| 02 | 01 | 70.0 | 02 | 60.0 |
| 03 | 01 | 80.0 | 02 | 80.0 |
| 04 | 01 | 50.0 | 02 | 30.0 |
| 05 | 01 | 76.0 | 02 | 87.0 |
| 06 | 01 | 31.0 | NULL | NULL |
+------+------+-------+-------+---------+
4.查询不存在" 01 “课程但存在” 02 "课程的情况
分析：与第3题相反，更换两处地方即可
将首行的t1全部换成t2
将left换成right
最终代码：
select t2.SId,t2.CId,t2.score, t1.CId as t1CId, t1.score as t1score from
(select SId,CId,score from SC where CId = '01') as t1
right join
(select SId,CId,score from SC where CId = '02') as t2
on t1.SId = t2.SId;
执行结果：
+------+------+-------+-------+---------+
| SId | CId | score | t1CId | t1score |
+------+------+-------+-------+---------+
| 01 | 02 | 90.0 | 01 | 80.0 |
| 02 | 02 | 60.0 | 01 | 70.0 |
| 03 | 02 | 80.0 | 01 | 80.0 |
| 04 | 02 | 30.0 | 01 | 50.0 |
| 05 | 02 | 87.0 | 01 | 76.0 |
| 07 | 02 | 89.0 | NULL | NULL |
+------+------+-------+-------+---------+
5.查询平均成绩⼤于等于 60 分的同学的学⽣编号和学⽣姓名和平均成绩
分析：需要用到学生表、成绩表
过程：
（1）利用avg(),先将平均成绩算出来(基于成绩表SC)
右SC表可知，需要根据SId先分组，然后在组内求平均
select SId,avg(score) as avg_score from SC group by SId;
+------+-----------+
| SId | avg_score |
+------+-----------+
| 01 | 89.66667 |
| 02 | 70.00000 |
| 03 | 80.00000 |
| 04 | 33.33333 |
| 05 | 81.50000 |
| 06 | 32.50000 |
| 07 | 93.50000 |
+------+-----------+
小数位太多，利用round()取两位小数
select SId, round(avg(score),2) as avg_score from SC group by SId;
+------+-----------+
| SId | avg_score |
+------+-----------+
| 01 | 89.67 |
| 02 | 70.00 |
| 03 | 80.00 |
| 04 | 33.33 |
| 05 | 81.50 |
| 06 | 32.50 |
| 07 | 93.50 |
+------+-----------+
(2)平均分要大于等于60，
方法一：以平均分为整体，在其后面加where条件（因为where执行顺序优先于group by）
select * from
(select SId, round(avg(score),2) as avg_score from SC group by SId) t1
where t1.vag_score >=60;
方法二：利用having条件，直接加在末尾
select SId, round(avg(score),2) as avg_score from SC group by SId
having avg_score >= 60;
+------+-----------+
| SId | avg_score |
+------+-----------+
| 01 | 89.67 |
| 02 | 70.00 |
| 03 | 80.00 |
| 05 | 81.50 |
| 07 | 93.50 |
+------+-----------+
（3）题目需求中的学生编号和平均成绩都有了，还差一个学生姓名
把上面得出的表当作子表，与学生表进行关联
最终代码：
select t1.SId,t1.avg_score,t2.SName from
(select SId, round(avg(score),2) as avg_score from SC group by SId
having avg_score >= 60) t1
join Student t2
on t1.SId = t2.SId;
执行结果：
+------+-----------+--------+
| SId | avg_score | SName |
+------+-----------+--------+
| 01 | 89.67 | 赵雷 |
| 02 | 70.00 | 钱电 |
| 03 | 80.00 | 孙风 |
| 05 | 81.50 | 周梅 |
| 07 | 93.50 | 郑竹 |
+------+-----------+--------+
6.查询在 SC 表存在成绩的学⽣信息
分析：由学生表可知学生有13个；由成绩表可知，有成绩的学生只有7个
过程：
（1）将有成绩的学生SId提取出来
select sId from SC;
+------+
| sId |
+------+
| 01 |
| 01 |
| 01 |
| 02 |
| 02 |
| 02 |
| 03 |
| 03 |
| 03 |
| 04 |
| 04 |
| 04 |
| 05 |
| 05 |
| 06 |
| 06 |
| 07 |
| 07 |
+------+
利用distinct去重
select distinct sId from SC;
+------+
| sId |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
+------+
（2）将上述表与学生表进行关联
最终代码：
select t1.SId,t2.SName from
(select distinct sId from SC) t1
join Student t2
on t1.SId = t2.SId;
执行结果：
+------+--------+
| SId | SName |
+------+--------+
| 01 | 赵雷 |
| 02 | 钱电 |
| 03 | 孙风 |
| 04 | 李云 |
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
+------+--------+
7.查询所有同学的学⽣编号、学⽣姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
分析：基于成绩表，算一个总分，算一个课程总数
将得出的表与学生表进行关联（学生表为准）
过程：
（1）基于成绩表，先将学生的总分和课程总数提取出来
先将SId分组，利用count()求课程总数，利用sum()求总分
select SId, count(CId) as cnt, sum(score) as sum_score
from SC group by SId;
+------+-----+-----------+
| SId | cnt | sum_score |
+------+-----+-----------+
| 01 | 3 | 269.0 |
| 02 | 3 | 210.0 |
| 03 | 3 | 240.0 |
| 04 | 3 | 100.0 |
| 05 | 2 | 163.0 |
| 06 | 2 | 65.0 |
| 07 | 2 | 187.0 |
+------+-----+-----------+
（2）将上面得出的表与学生表进行关联（学生表为主）
学生表在前，就使用左关联；学生表在后就使用右关联
最终代码：
select t2.SId,t2.SName,t1.cnt,t1.sum_score from
(select SId, count(CId) as cnt, sum(score) as sum_score
from SC group by SId) t1
right join Student t2
on t1.SId = t2.SId;
执行结果：
+------+--------+------+-----------+
| SId | SName | cnt | sum_score |
+------+--------+------+-----------+
| 01 | 赵雷 | 3 | 269.0 |
| 02 | 钱电 | 3 | 210.0 |
| 03 | 孙风 | 3 | 240.0 |
| 04 | 李云 | 3 | 100.0 |
| 05 | 周梅 | 2 | 163.0 |
| 06 | 吴兰 | 2 | 65.0 |
| 07 | 郑竹 | 2 | 187.0 |
| 09 | 张三 | NULL | NULL |
| 10 | 李四 | NULL | NULL |
| 11 | 李四 | NULL | NULL |
| 12 | 赵六 | NULL | NULL |
| 13 | 孙七 | NULL | NULL |
+------+--------+------+-----------+
8.查询「李」姓⽼师的数量
分析：利用到老师表，count(),where,like
代码：
select count(Tname) from Teacher where Tname like '李%';
执行结果：
+--------------+
| count(Tname) |
+--------------+
| 1 |
+--------------+
9.查询学过「张三」⽼师授课的同学的信息
分析：
根据老师的姓名，在教师表里找对应的老师编号
根据老师的编号，在课程表里找该老师所教的科目
根据所教的科目，在成绩表里找对应学生的SId
根据学生的SId,在学生表里找对应的其他学生信息
过程：
（1）根据”张三“这个名字Tname，去教师表里找该老师的编号TId
select TId from Teacher where Tname = '张三'；
+------+
| TId |
+------+
| 01 |
+------+
（2）根据老师的编号TId，在课程表里找该老师所教的科目CId
因为一个老师只有一个编号（一条记录），所以此处可以用等于号
select CId from Course where TId
= (select TId from Teacher where Tname = '张三');
+------+
| CId |
+------+
| 02 |
+------+
注意：如果括号里的结果是多条记录，那么就需要使用 in 来替代等于号
例如：...where TId in (select TId from Teacher where Tname = '张三','李四');
（3）根据所教的科目CId，在成绩表里找学该科目的学生的SId
因为一个老师编号只对应一个科目，中间用等于号连接
select SId from SC where CId
=
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'));
+------+
| SId |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 07 |
+------+
（4）根据学生的SId，与学生表关联，在学生表里找对应的其他学生信息
执行结果：
select t2.SId,t2.SName from
(select SId from SC where CId
=
(select CId from Course where TId = (select TId from Teacher where Tname = '张三'))) t1
join Student t2 on t1.SId = t2.SId;
执行结果：
+------+--------+
| SId | SName |
+------+--------+
| 01 | 赵雷 |
| 02 | 钱电 |
| 03 | 孙风 |
| 04 | 李云 |
| 05 | 周梅 |
| 07 | 郑竹 |
+------+--------+
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！