MySQL_50道练习题

前言

网上有好多MySQL练习题,今天偷摘别人的练习题自己也来做一下。
部分题目有写思路。请注意注释。

源码

-- 学生表
CREATE TABLE `Student`(
`s_id` VARCHAR(20),
`s_name` VARCHAR(20) NOT NULL DEFAULT '',
`s_birth` VARCHAR(20) NOT NULL DEFAULT '',
`s_sex` VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(`s_id`)
);

-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

-- 插入学生表测试数据
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');

-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

-- 成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);

-- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s1.s_id from score s1 left join score s2 on s1.s_id=s2.s_id and s1.c_id='01' and s2.c_id='02' where s1.s_score>s2.s_score;

-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select s1.s_id from score s1 left join score s2 on s1.s_id=s2.s_id and s1.c_id='01' and s2.c_id='02' where s2.c_id is not null;

-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
SELECT * from (SELECT * from score where c_id='01')A LEFT JOIN (SELECT * from score where c_id='02')B on A.s_id=B.s_id;

-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from score where c_id='02' and s_id not in (SELECT s_id from score where c_id = '01');

-- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select s.s_id,s.s_name,b.a avg from (select s_id,AVG(s_score) a from score group by s_id having a >=60)b,student s WHERE s.s_id = b.s_id;

-- 3. 查询在 SC 表存在成绩的学生信息
select * from student where s_id in (SELECT DISTINCT s_id from score);

-- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select s.s_id,s.s_name,a.c 选课总数,a.s 总成绩 from student s left JOIN (select s_id,count(*) c,sum(s_score) s from score GROUP BY s_id ) a on s.s_id = a.s_id;

-- 4.1 查有成绩的学生信息
-- in适合外表比内表大,exists适合内表比外表大
select * from student where s_id in (select s_id from score);
select * from student where exists (SELECT s_id from score WHERE score.s_id = student.s_id);
-- 5. 查询「李」姓老师的数量 
select count(*) from teacher where t_name like '李%';

-- 6. 查询学过「张三」老师授课的同学的信息 
SELECT * from student where s_id in (select DISTINCT s_id from score where c_id in (select c_id from course where t_id in (SELECT t_id from teacher where t_name ='张三')));

-- 7. 查询没有学全所有课程的同学的信息 
SELECT * from student where s_id in (select s_id from score GROUP BY s_id HAVING COUNT(c_id)<(select count(DISTINCT c_id) from course));

-- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 
SELECT * from student where s_id in (SELECT DISTINCT s_id from score where c_id in (SELECT c_id from score where s_id='01'));

-- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 
-- 我的不靠谱猜测
select @01c_id:=GROUP_CONCAT(c_id),@c:= count(c_id) from score where s_id='01';
select * from student where student.s_id in (select a.s_id from (select s_id,GROUP_CONCAT(c_id) c_ids from score group by s_id having count(*)=@c) a where a.c_ids = @01c_id and a.s_id <>'01');
-- 网上大佬的答案
select * from student where s_id in (select s_id from score where c_id in (select DISTINCT c_id from score where s_id='01') and s_id<>'01' GROUP BY s_id HAVING COUNT(c_id)>=3);

-- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
SELECT s_name from student where s_id in (select distinct s_id from score where c_id not in (select c_id from course WHERE t_id in (select t_id from teacher where t_name ='张三')));

-- 11. 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩 
select s.s_id,s.s_name,a.sco from student s right join (select s_id,ROUND(avg(s_score),2) sco from score where s_score<60 group by s_id having count(c_id)>=2)a on s.s_id=a.s_id;

-- 12. 检索" 01 "课程分数小于 60,按分数降序排列的学生信息
select s.* from student s RIGHT JOIN (select s_id,s_score from score where c_id='01' and s_score<60)a on s.s_id = a.s_id ORDER BY a.s_score desc;

-- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s_id,max(case c_id when '01' then s_score else 0 end) '01',max(case c_id when '02' then s_score else 0 end) '02',max(case c_id when '03' then s_score else 0 end) '03',avg(s_score) a from score GROUP BY s_id ORDER BY a desc;

-- 14. 查询各科成绩最高分、最低分和平均分:
--     以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
--     及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
--     要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT s.c_id,course.c_name,max(s.s_score) 最高分,MIN(s.s_score) 最低分, AVG(s.s_score) 平均分, d.`及格率`,c.`中等率`,b.`优良率`,a.`优秀率`,count(*) 选修人数 from score s LEFT JOIN
(SELECT c_id ,sum(case when s_score>=90 then 1 else 0 end)/count(s_score) 优秀率 from score GROUP BY c_id)a on s.c_id=a.c_id LEFT JOIN
(SELECT c_id ,sum(case when s_score>=80 and s_score<90 then 1 else 0 end)/count(s_score) 优良率 from score GROUP BY c_id)b on s.c_id=b.c_id LEFT JOIN
(SELECT c_id ,sum(case when s_score>=70 and s_score<80 then 1 else 0 end)/count(s_score) 中等率 from score GROUP BY c_id)c on s.c_id=c.c_id LEFT JOIN
(SELECT c_id ,sum(case when s_score>=60 then 1 else 0 end)/count(s_score) 及格率 from score GROUP BY c_id)d on s.c_id=d.c_id LEFT JOIN
course on s.c_id = course.c_id
GROUP BY s.c_id ORDER BY 选修人数 desc,s.c_id asc;

-- 15. 按各科成绩进行排序,并显示排名, Score 重复时保留名次空缺
-- 题目也没说清楚是否降序,就默认把它降序吧,也没说清楚是一个人的每门课成绩排名还是一门课每位学生的排名,我这边实现的是一门课每位学生排名
-- 这应该是考察rank(),densc_rank()和row_number(),关于这三个函数的区别可以参考底下这篇博客	
-- https://blog.csdn.net/Lin_Xiao_Dai/article/details/106416145
select *,row_number() over (partition by c_id order by s_score desc) from score ;

-- 15.1 按各科成绩进行排序,并显示排名, Score 重复时合并名次
select * ,rank() over(order by s_score desc) from score;

-- 16.  查询学生的总成绩,并进行排名,总分重复时保留名次空缺
select s_id,sum(s_score),row_number() over(order by s_score desc) from score GROUP BY s_id;

-- 16.1 查询学生的总成绩,并进行排名,总分重复时不保留名次空缺
select s_id,sum(s_score),rank() over(order by s_score) from score GROUP BY s_id;

-- 17. 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[60-0] 及所占百分比
SELECT
	score.c_id,course.c_name,
	sum( CASE WHEN s_score <= 100 AND s_score > 85 THEN 1 ELSE 0 END ) '[100-85]',
	CONCAT(round( sum( CASE WHEN s_score <= 100 AND s_score > 85 THEN 1 ELSE 0 END ) / count( s_id )*100, 2 ),'%') as '占比',
	sum( CASE WHEN s_score <= 85 AND s_score > 70 THEN 1 ELSE 0 END ) '[85-70]',
	CONCAT(round( sum( CASE WHEN s_score <= 85 AND s_score > 70 THEN 1 ELSE 0 END ) / count( s_id )*100, 2 ),'%') as '占比',
	sum( CASE WHEN s_score <= 70 AND s_score > 60 THEN 1 ELSE 0 END ) '[70-60]',
	CONCAT(round( sum( CASE WHEN s_score <= 70 AND s_score > 60 THEN 1 ELSE 0 END ) / count( s_id )*100, 2 ),'%') as '占比',
	sum( CASE WHEN s_score <= 60 AND s_score >= 0 THEN 1 ELSE 0 END ) '[60-0]',
	CONCAT(round( sum( CASE WHEN s_score <= 60 AND s_score >= 0 THEN 1 ELSE 0 END ) / count( s_id )*100, 2 ),'%') as '占比' from 
	score LEFT JOIN course on score.c_id = course.c_id
GROUP BY
	c_id;
	
-- 18. 查询各科成绩前三名的记录
select * from (select *,row_number() over (PARTITION by c_id order by s_score desc rows BETWEEN 0 preceding and 2 preceding) as rn from score)a where a.rn<=3;

-- 19. 查询每门课程被选修的学生数 
select c_id,count(s_id) from score group by c_id;

-- 20. 查询出只选修两门课程的学生学号和姓名 
select s_id,s_name from student where s_id in (select s_id from score group by s_id having count(c_id)=2);

-- 21. 查询男生、女生人数
SELECT s_sex,count(s_sex) FROM student GROUP BY s_sex;

-- 22. 查询名字中含有「风」字的学生信息
select * from student where s_name like '%风%';

-- 23. 查询同名同性学生名单,并统计同名人数
select s_name,s_sex,count(*) as '同名人数'  from student group by s_name,s_sex;

-- 24. 查询 1990 年出生的学生名单
select * from student where YEAR(s_birth)=1990;

-- 25. 查询每门课程的平均成绩,结果按平均成绩降序排列,平均成绩相同时,按课程编号升序排列
select c_id,avg(s_score) avg from score GROUP BY c_id ORDER BY avg desc, c_id asc;

-- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
SELECT s.s_id,s.s_name,a.avgfrom student s RIGHT JOIN (
SELECT s_id,avg(s_score) avgfrom score GROUP BY s_id HAVING avg>=85) a ON s.s_id=a.s_id;

-- 27. 查询课程名称为「数学」,且分数低于 60 的学生姓名和分数 
SELECT s.s_name,a.s_score FROM student s RIGHT JOIN (SELECT s_id,s_score FROM score WHERE c_id=(SELECT c_id FROM course WHERE c_name='数学') AND s_score< 60) a ON s.s_id=a.s_id;

-- 28. 查询所有学生的课程及分数情况(存在学生没成绩,没选课的情况)
select a.s_id,b.c_id,b.s_score from student a LEFT JOIN score b on a.s_id = b.s_id;

-- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.s_name,b.c_name,s.s_score from score s LEFT JOIN student a on s.s_id = a.s_id LEFT JOIN course b on s.c_id = b.c_id where s_score>70;

-- 30. 查询不及格的课程
select * from score where s_score<60;

-- 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
SELECT s_id,s_name from student where s_id in (SELECT s_id from score where c_id='01' and s_score>80);

-- 32. 求每门课程的学生人数 
select c_id,count(s_id) '学生人数' from score group by c_id;

-- 33. 成绩不重复,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT student.*,score.s_score FROM score RIGHT JOIN student ON score.s_id=student.s_id WHERE c_id IN (SELECT c_id FROM course WHERE t_id IN (SELECT t_id FROM teacher WHERE t_name='张三')) ORDER BY s_score DESC LIMIT 1;

-- 34. 成绩有重复的情况下,查询选修「张三」老师所授课程的学生中,成绩最高的学生信息及其成绩
SELECT * FROM (
SELECT student.*,score.s_score,dense_rank () over (ORDER BY score.s_score DESC) r FROM score RIGHT JOIN student ON score.s_id=student.s_id WHERE c_id IN (
SELECT c_id FROM course WHERE t_id IN (
SELECT t_id FROM teacher WHERE t_name='张三'))) a WHERE a.r<2;

-- 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select DISTINCT s1.* from score s1 join score s2 on s1.s_id =s2.s_id and s1.c_id<>s2.c_id and s1.s_score = s2.s_score;

-- 36. 查询每门功成绩最好的前两名
select * from (select *,row_number() over (partition by c_id order by s_score desc) r from score )a where a.r<3;

-- 37. 统计每门课程的学生选修人数(超过 5 人的课程才统计)。
select c_id,count(s_id) from score group by c_id having count(s_id)>5;

-- 38. 检索至少选修两门课程的学生学号 
select s_id from score GROUP BY s_id having count(c_id)>=2;

-- 39. 查询选修了全部课程的学生信息
select * from student where s_id in (select s_id from score group by s_id having count(c_id)>=(select count(c_id) from course));

-- 40. 查询各学生的年龄,只按年份来算 
select *,year(now())-year(s_birth) age from student;

-- 41. 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一
select *,timestampdiff(YEAR,s_birth,now()) age from student;

-- 42. 查询本周过生日的学生
-- 将生日的月日和当前日期的年进行拼接并转为date类型,然后和当前日期的week数进行对比
select * from student where WEEKOFYEAR(cast(concat(year(now()),'/',month(s_birth),'/',day(s_birth)) as date)) = WEEKOFYEAR(now());

-- 43. 查询下周过生日的学生
select * from student where WEEKOFYEAR(cast(concat(year(now()),'/',month(s_birth),'/',day(s_birth)) as date)) = WEEKOFYEAR(now())+1;

-- 44. 查询本月过生日的学生
SELECT * from student where month(s_birth)=MONTH(now());

-- 45. 查询下月过生日的学生
SELECT *,case when if(month(s_birth)=1,13,month(s_birth))=MONTH('2020/11/1')+1 then 'Y' else 'N' end  from student;

参考文献

超经典SQL练习题,做完这些你的SQL就过关了

  • 0
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值