MySQL50道练习题之学习分享

MySQL 50道练习题

前言

本学期数据库与MySQL的学习基本告一段落,在知乎上偶然看到号称是MySQL必刷五十题,毕竟是十几年一路考过来的,心中有竟有恋爱感觉。不过话说回来,刷题亦是成长的必由之路。于是,我愿深深的扎入生活,吮尽生活的骨髓,过得扎实简单。激励别人,鞭策自己。

准备

建表

建立学生表 编号(主键) 学生姓名 生日 性别

CREATE TABLE student(
s_id int PRIMARY key auto_increment comment '学号',
s_name VARCHAR(20) not null comment '姓名',
s_birthday VARCHAR(20) not null comment '生日',
s_sex VARCHAR(20) not null comment '性别'
);

建立课程表 编号(主键) 课程名 教师

CREATE TABLE course(
c_id int  auto_increment comment '编号',
c_name VARCHAR(20) not null comment '课程名',
t_id int not null comment '教师',
PRIMARY KEY(c_id)
);

建立教师表 编号(主键)教师姓名

CREATE TABLE teacher(
t_id int key auto_increment comment '编号',
t_name VARCHAR(20) not null comment '教师姓名'
);

建立成绩表 编号(主键) 课程编号(主键) 成绩

CREATE TABLE score(
s_id int comment '编号',
c_id int comment '课程编号',
s_score FLOAT(4,2) comment '成绩',
PRIMARY KEY(s_id,c_id)
);

插入值

student表

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' , '女');

course表

INSERT INTO Course VALUES('01' , '语文' , '02');
INSERT INTO Course VALUES('02' , '数学' , '01');
INSERT INTO Course VALUES('03' , '英语' , '03');

teacher表

INSERT INTO Teacher VALUES('01' , '张三');
INSERT INTO Teacher VALUES('02' , '李四');
INSERT INTO Teacher VALUES('03' , '王五');

score表

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);
select * from student;
select * from course;
select * from teacher;
select * from score;

答题

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。

select s_id from score s1 inner join score s2 using(s_id)
where s1.c_id = 1 and s2.c_id = 2 and s1.s_score > s2.s_score;

2、查询平均成绩大于60分的学生的学号和平均成绩。

select s_id,avg(s_score) from score 
GROUP BY s_id HAVING avg(s_score) > 60;

3、查询所有学生的学号、姓名、选课数、总成绩。

select s1.s_id as '学号',s2.s_name as '姓名',count(s1.s_id) as '选课数',sum(s_score) as '总成绩' 
from score s1 INNER join student s2 using(s_id)  
GROUP BY s1.s_id;

、4、查询姓“张”的老师的个数。

select count(*) as '姓张老师数' from teacher where t_name like '张%';

5、查询学过“张三”老师所教的所有课的同学的学号、姓名。

select s_id,s_name FROM student where s_id in 
(SELECT s_id from score inner join course using(c_id) 
join teacher using(t_id) 
where t_name = '张三');

6、查询课程编号为“02”的总成绩。

select sum(s_score) from score where c_id = 2;

7、查询所有课程成绩小于60分的学生的学号、姓名。

select s_id,s_name,c_id from student 
inner join score using(s_id) 
where s_score < 60;

8、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名。

SELECT s_id,s_name FROM student inner join score using(s_id) 
where s_id in
 (SELECT s_id FROM score where c_id = '01')and c_id = '02';

9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息。

SELECT s_id as '学号',s_name as '姓名',s_sex as '性别',s_birthday as '生日' from student 
WHERE s_id in (SELECT s_id FROM score where c_id = 1) 
and s_id not in (SELECT s_id FROM score WHERE c_id =2);

10、查询没有学全所有课的学生的学号、姓名。

SELECT s_id,s_name from student wheres_id in
(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) <3 );

-- 若不知道表内科目的数量,更严谨的写法应为
SELECT COUNT(*) into @nn from course;
SELECT s_id,s_name from student where s_id in
(SELECT s_id FROM score GROUP BY s_id HAVING COUNT(c_id) < @nn );

-- 还有一种对于本题严谨的写法
SELECT s_id,s_name from student join score USING(s_id)
GROUP BY s_id HAVING count(c_id) <(SELECT count(1) from course);

11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名。

SELECT DISTINCT s_id,s_name from student INNER join score using(s_id) 
where c_id in(SELECT c_id from score where s_id = 1) and s_id <> 1;

12、查询和“01”号同学所学课程完全相同的其他同学的学号。

select s_id,s_name from student join score using(s_id)
where c_id in 
(SELECT c_id from score where s_id = '01') and s_id <> '01' 
GROUP BY s_id 
HAVING count(1) = (SELECT count(c_id) FROM score WHERE s_id = '01')

13.把“Score”表中“张三”老师教的课的成绩都更改为此课程的平均成绩。

update score join 
(SELECT AVG(s_score) a,c_id FROM score s1 
join course c using(c_id) 
join teacher t using(t_id) 
WHERE t_name = '张三' GROUP BY c_id) b 
USING(c_id) 
set s_score = b.a

15、删除学习“张三”老师课的SC表记录。

DELETE from score WHERE c_id in
(select c_id from course join teacher using(t_id) where t_name = '张三');

16.检索"01"课程分数小于60,按分数降序排列的学生信息。

select DISTINCT s_id,s_name,s_score from student inner join score using(s_id) 
where c_id = 1 and s_score < 60 
ORDER BY s_score desc

这里把前面删改的课程改回去,将sc删表重建一下。

17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩。

SELECT s_id,
max(CASE when c_id = 1 then s_score END) as '语文',
max(CASE when c_id = 2 then s_score END) as '数学',
max(CASE when c_id = 3 then s_score END) as '英语',
AVG(s_score) a from score GROUP BY s_id ORDER BY a DESC

18、查询各科成绩最高和最低的分:以如下的形式显示:课程ID,最高分,最低分。

SELECT c_id,max(s_score) as '最高分',min(s_score) as '最低分' from score GROUP BY c_id;

19、按各科平均成绩从低到高和及格率的百分数从高到低排列,以如下形式显示:课程号,课程名,平均成绩,及格百分数。

select c_id as '课程号', c_name as '课程名', round(avg(s_score),2) as '平均成绩',
concat(round((SELECT count(*) FROM score s1 where s2.c_id = s1.c_id and s_score >= 60) / count(*)*100,2) , '%') 
as '及格率' 
FROM score s2 inner join course using(c_id) 
GROUP BY c_id;

20、查询学生的总成绩并进行排名。

SELECT s_id as '学号', na as '姓名', total as '总分',(@i:=@i+1) as '排名' 
FROM (SELECT s_id,s_name as na,sum(s_score) as total
FROM score inner join student USING(s_id) 
GROUP BY s_id ORDER 
BY sum(s_score) DESC) j,
(select @i:=0) as r;

21、查询不同老师所教不同课程平均分从高到低显示。

SELECT round(avg(s_score),2) 平均分 FROM score 
inner join course using(c_id) 
inner join teacher using(t_id) 
GROUP BY t_id
ORDER BY 平均分 desc

22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩。

SELECT s_id 学号,s_name 姓名,s_sex 性别,b 学科,c 分数,排名 from
(SELECT s_id a,c_id b,s_score c,@i:=@i+1 排名 from score g,(select @i:=0) d 
where c_id = 1 ORDER BY s_score desc) as e join student f on e.a = f.s_id 
where 排名 in (2,3)
UNION
SELECT s_id 学号,s_name 姓名,s_sex 性别,b 学科,c 分数,排名 from
(SELECT s_id a,c_id b,s_score c,@j:=@j+1 排名 from score g,(select @j:=0) d 
where c_id = 2 ORDER BY s_score desc) as e join student f on e.a = f.s_id 
where 排名 in (2,3)
UNION
SELECT s_id 学号,s_name 姓名,s_sex 性别,b 学科,c 分数,排名 from
(SELECT s_id a,c_id b,s_score c,@k:=@k+1 排名 from score g,(select @k:=0) d
where c_id = 3 ORDER BY s_score desc) as e join student f on e.a = f.s_id 
where 排名 in (2,3);

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称。

SELECT c_id as '课程编号',c_name as '课程名',
sum(case when s_score >= 85 and s_score <= 100 then 1 else 0 END) '100>分数>85',
sum(case when s_score >= 70 and s_score <85 then 1 else 0 end) '85>分数>70',
sum(case when s_score >= 60 and s_score <70 then 1 else 0 end) '70>分数>60',
sum(case when s_score < 60 then 1 else 0 end) '60>分数'
FROM score inner join course using(c_id) GROUP BY c_id,c_name;

24、查询学生平均成绩及其名次。

SELECT a as '学号',b as '姓名',c as '平均分',@i:=@i+1 '排名'
FROM 
(select s_id a,s_name b,round(avg(s_score),2) c from score i 
inner join student USING(s_id) GROUP BY s_id ORDER BY k DESC) j ,
(SELECT @i:=0) k;

25、查询各科成绩前三名的记录。

SELECT * from score s1 where
(select count(1) from score s2 where s1.score<s2.score and s1.c_id = s2.c_id)
order by c_id ,s_score desc;

-- 第一步
SELECT * from score s1 
order by c_id ,s_score desc;
-- 第二步把值带进括号内的子查询(一条一条带入),例如带入第一条 1 1 80
SELECT count(1) from score s2
where 80<s2.s_score
and 1=s2.c_id       -- 查询结果为0  说明比他大的数没有也就是<=2,该数据带出回到夫循环保留待命

-- 如此一条一条运行下去,直到全部运行完
-- 第三步把子查询的值带出来到夫查询,经过筛选剩下的都是前三名的值,再进行排序就行
SELECT * from score s1 where
。。。。。
order by c_id ,s_score desc
-- 这段代码的<=2的意思是,假如我查的是80分,看看比80分大的数据有没有两个以上,没有说明80分是第三名以内,有就说明是第三名以外。

26、查询每门课程被选修的学生数。

SELECT c_id as '科目',count(c_id) as '选修人数' from score GROUP BY c_id;

27、查询出只选修了两门课程的全部学生的学号和姓名。

SELECT a as '学号', c as '姓名' FROM 
(select s_id a,count(c_id) b,s_name c FROM score inner join student using(s_id)  GROUP BY (s_id)) i 
WHERE b = 2 ;
-- 所用查询时间0.021s

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);
-- 所用查询时间0.030s

28、查询男生、女生人数。

SELECT s_sex as '性别',count(s_id) as '人数' from student GROUP BY s_sex;

29、查询名字中含有“风”字的学生信息。

SELECT s_id as '学号',s_sex as '性别',s_name as '名字',s_birthday as '生日' FROM student where s_name like '%风%';

30、查询同名同姓学生名单并统计同名人数。

select count(s_name) as '同名人数' FROM student s1 where s_name in 
(SELECT s_name from student s2 WHERE s1.s_id <> s2.s_id);

31、1990年出生的学生名单。

SELECT s_name as '姓名',s_birthday as '出生年日' FROM student where s_birthday like '1990%';

32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 。

SELECT s_id as '学号',s_name as '姓名',b as '平均分' FROM 
(SELECT s_id a,round(AVG(s_score),2) b FROM score GROUP BY s_id) j 
join student s1 on j.a = s1.s_id 
where b > 85 ORDER BY b desc;
-- 0.024s

SELECT student.*,avg(s_score) from student join score
USING(s_id) GROUP BY s_id HAVING avg(s_score)>85 ;
-- 0.030s

33、查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列 。

SELECT c_id as '课程名',round(avg(s_score),2) 平均分 from score GROUP BY c_id ORDER BY c_id,平均分;

34、查询课程名称为“数学”且分数低于60的学生姓名和分数。

SELECT s_name as '姓名',s_score as '分数' from student join score using(s_id) join course using(c_id) 
where c_name = '数学' and s_score >= 60;

35、查询所有学生的选课情况。

SELECT DISTINCT s_id,s_name,c_id,c_name from student
join score USING(s_id)
join course USING(c_id);

36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数。

SELECT s_name as '姓名',c_name as '课程名',s_score as '分数' from score 
join student using(s_id) 
join course using(c_id) 
where s_score > 70;

37、查询不及格的课程并按课程号从大到小排列。

select c_id,c_name,s_id,s_name,s_score from student 
join score using(s_id) 
join course using(c_id) 
where s_score < 60 ORDER BY c_id,s_score;

38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名。

select s_id,s_name from student 
join score using(s_id) 
join course using(c_id) 
where s_score >80 and c_id = 3;

39、查询选了课程的学生人数。

SELECT count(DISTINCT s_id) from score ;

40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩。

SELECT s_name,s_score FROM student  
join score using(s_id) 
join course using(c_id) 
join  teacher using(t_id) 
where t_name = '张三' ORDER BY s_score desc limit 1;

41、查询各个课程及相应的选修人数。

SELECT c_id as '学科编号',count(s_id) as '人数'  from score GROUP BY c_id;

42、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩。

SELECT s_id,c_id,s_score FROM score s1 WHERE s_id in 
(SELECT s_id FROM score s2 WHERE s1.s_score = s2.s_score and s1.c_id <> s2.c_id)

43、查询每门课程成绩最好的前两名。

SELECT c_id as '课程编号',s_id as '学生编号',s_score as '分数' FROM score s1 WHERE 
(SELECT count(1) FROM score s2 WHERE s1.s_score < s2.s_score and s1.c_id = s2.c_id) <= 1 
ORDER BY c_id,s_score desc

44、统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数,查询结果按人数降序排序,若人数相同,按课程号升序排序。

SELECT a as '科目',b as '人数' FROM (SELECT c_id a,count(s_id) b FROM score
GROUP BY c_id) i WHERE b>5 ORDER BY b DESC 

45、查询至少选修两门课程的学生学号。

SELECT s_id as '学号',count(c_id) as '选择学科数量' FROM score GROUP BY s_id HAVING count(c_id) >=2

46、查询选修了全部课程的学生信息。

SELECT s_id as '学号',s_name as '姓名',s_sex as '性别',s_birthday as '生日' from student
join score USING(s_id)
GROUP BY s_id HAVING count(c_id)=
(SELECT count(c_id) from course);

47、查询没学过“张三”老师讲授的任一门课程的学生姓名。

SELECT s_name as '姓名' FROM student where s_id not in 
(SELECT s_id FROM score join course using(c_id) join teacher using(t_id) 
where t_name = '张三');

48、查询两门以上不及格课程的同学的学号及其平均成绩。

SELECT s_id as '学号',round(avg(s_score),2) as '平均分'
FROM score WHERE s_score < 60
GROUP BY s_id 
HAVING count(s_id) >= 2;

49、检索课程编号为“03”且分数小于60的学生学号,结果按按分数降序排列。

SELECT s_id as '学号',s_score as '分数' from score 
WHERE c_id = 3 and s_score < 60 
ORDER BY s_score desc;

50、删除学生编号为“02”的课程编号为“01”的成绩。

DELETE from score where s_id=‘02’ and c_id=‘01’;

总结

刷完这五十题,受益匪浅,首先把我这学期所学的关系数据库好好巩固了一遍,在刷题的过程中碰到了许多困难点,在通过大量百度,与询问同学的方式下慢慢得已解决,数据库可谓是博大精深。自从疫情滞留在家,好久没有过一次刷这么多题的体验了,刷完这些,不禁会去静下心来去思考自己的种种,不止于学习。还需继续努力。

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 数字20 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读