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

于 2025-01-24 14:52:09 发布
阅读量527 收藏 6
点赞数 20
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/2501_90389543/article/details/145341309
版权

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

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值