MySQL_50道练习题

最新推荐文章于 2021-02-28 07:15:59 发布
最新推荐文章于 2021-02-28 07:15:59 发布
阅读量646 收藏 6
点赞数
分类专栏： MySQL 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Lin_Xiao_Dai/article/details/106392710
版权

前言

网上有好多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练习题，做完这些你的ＳＱＬ就过关了

林_胖
关注
  • 0
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql经典50题--第一弹
查数菇乐园
09-18 5159
在初学时期看到了大家都在发的Mysql经典50题，做过一遍之后的第二次回顾
mysql经典50题
04-01
mysql经典50题。以及注释，经过实战运行及改善，可以直接用。挺有用的，对于sql入门及网申笔试来说。
MySQL 50道练习题及答案
M10F的博客
03-30 7079
练习题参考知乎 https://zhuanlan.zhihu.com/p/50662216 以下是根据自己思路编写的代码，部分参考了知乎，途中遇到了不少问题 整个实践过程中总是忘记指定字段所在的表名，出现如下错误 ERROR 1052 (23000): Column ‘’ in field list is ambiguous， 指定列重复，即两张表存在相同字段，没有在表字段前指明表名，导致指代不清...
Mysql基本命令
lin_bing110的博客
03-02 158
-- 数据库的操作 -- 连接数据库 mysql -u用户名 -p musql -u用户名 -p密码 -- 退出数据库 exit quit ctrl + d -- sql语句最后需要分毫(;)结尾 -- 显示数据库版本 select version(); -- 显示时间 select now(); -- 查看所有数据库 show databases; -- 创建数据库...
50道mysql练习题
kavito的博客
02-20 1079
student(sid,sname,sage,ssex) 学生表 sc(sid,cid,score) 成绩表 course(cid,cname,tid) 课程表 teacher(tid,tname) 教师表 1、建表语句： CREATE TABLE student ( sid INT, sname nvarchar(32), sage ...
MySQL50道练习题.zip
04-13
这里我们有“MySQL50道练习题”的压缩包，它包含了一系列问题，旨在帮助用户深入理解和掌握MySQL的基本操作、查询技巧以及高级功能。通过解答这些练习题，你可以提升自己的SQL技能，更好地应对实际工作中的数据管理...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
15_mysql基础练习题15.docx
10-24
15_mysql基础练习题15.docx
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题50道.pdf 本资源为MySQL数据库练习题，共50道题，涵盖数据库设计、数据表创建、数据插入、查询等多方面的知识点。下面是对该资源的详细知识点总结： 一、数据库设计 * 数据库设计是指根据具体的...
MySQL常用练手题目集合 MySQL查询练习题（50题） 共18页.pdf
08-21
MySQL查询练习题旨在帮助用户熟悉和提升SQL查询技巧，涵盖了多方面的数据库操作，如联接查询、...通过解决这些练习题，用户可以深入了解和掌握SQL查询语言的核心功能，为实际数据库管理与数据分析工作打下坚实基础。
MySQL经典练习50题
08-08
MySQL经典题库50题，无答案。MySQL经典题库50题，无答案。
MySQL基础练习题及答案
03-11
针对于MySQL刚入门和有一定基础的学员，适合多次练习。
mysql数据库练习题_50道SQL练习题及答案（MySQL版）
weixin_29345939的博客
02-28 2246
网上流传的50道sql练习，发现其答案部分有问题，故在此记录下个人用mysql 的实现。数据表介绍--1.学生表Student(SId,Sname,Sage,Ssex)--SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别--2.课程表Course(CId,Cname,TId)--CId 课程编号,Cname 课程名称,TId 教师编号--3.教师表Teacher(TI...
MySQL数据库入门练习200句
wayblink的博客
12-22 1万+
本文用于MySQL的入门学习和练习，延续我之前的[MySQL入门练习100句](http://blog.csdn.net/picway/article/details/53646994)，增加到200句，主要涉及SQL语言的增删改查。部分内容稍欠周详，在后续我自己练习的时候会再补上。
sql进阶练习题
weixin_34194702的博客
08-24 120
student SNO SNAME SAGE SSEX01 赵雷 1990-01-01 00:00:00 男02 钱电 1990-12-21 00:00:00 男03 孙风 1990-05-20 00:00:00 男04 李云 1990-08-06 00:00:00 男06 ...
MySQL数据库简单练习题
chuanluwang的博客
01-03 9827
简单巩固： create table student( id int(10) primary key auto_increment, name varchar(20) unique, age int(5) default 16, sex char(2) not null ); show databases; create database libry; use libry
经典50道SQL练习题
热门推荐
wbc199685的博客
09-15 2万+
前言 作为一个SQL小白，在一个月的努力后终于把这50道练习题做完了，分享一下我的答案给大家，说不定能给你提供一些思路。 我使用的是在线SQL，个人认为十分方便，使用的版本是“My SQL Server 2017”。 一、数据表 学生表 Student(SId,Sname,Sage,Ssex)–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 create table Student(SId varchar(10),Sname varchar(10),Sage dateti
leetcode--SQL练习-按照出题频率
认真学代码的shell
11-17 402
题解链接 262. 行程和用户 添加链接描述 一个表的两个字段是另一个表的外键，round函数 # Write your MySQL query statement below select t.Request_at as Day ,round( sum(if( t.Status ='completed',0,1))/count(t.Status),2 ) as 'Cancellati...
mysql练习题
喀喀喀的博客
11-05 2963
（1）create创建练习 offices表结构 字段名 数据类型 主键 外键 非空 唯一 自增 officeCode INT(10) 是 否 是 是 否 city INT(11) 否 否 是 否 否 address VARCHAR(50) 否 否 否 否 否 country VARCHAR(50) 否 否 否 否 否 postalCode VARCHAR(25) 否 否 否 是 否 employees表结构
mysql经典50道练习题pdf
最新发布
08-17
MySQL经典50道练习题pdf是一本经典的MySQL数据库练习题集合，对于想要提高MySQL数据库操作能力的人士来说是非常有价值的。 这本练习题集合涵盖了各种MySQL数据库操作方面的题目，包括数据库创建、表创建、数据插入...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值