MySQL经典50题纠错版_mysql查询本周过生日的学生-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43530798/article/details/128954606

建表sql

# Student 学生表
CREATE TABLE Student
(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL,
s_birth VARCHAR(20) NOT NULL, 
s_sex VARCHAR(10) NOT NULL,
PRIMARY KEY(s_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', '女');


# Course 课程表
CREATE TABLE Course
(
c_id VARCHAR(20),
c_name VARCHAR(20) NOT NULL,
t_id VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id)
);

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


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

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


# Score 分数表
CREATE TABLE Score
(
s_id VARCHAR(20),
c_id VARCHAR(20),
s_score INT(3),
PRIMARY KEY(s_id, c_id)  # 注意这里是联合主键
);

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 b.*,a.score1,a.score2 from 
    (select s1.s_id,s1.s_score score1,s2.s_score score2
     from Score s1 inner join Score s2
     on s1.s_id = s2.s_id
     where s1.c_id = '01' 
     and s2.c_id = '02' 
     and s1.s_score > s2.s_score)a 
inner join Student b 
on a.s_id = b.s_id;

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

#解题思路：分组查询出平均成绩，再过滤掉小于平均分小于60的学生
select s_id,avg(s_score) avg_score from Score
group by s_id
having avg_score > 60;

3. 查询所有成绩小于60分的学生信息

#解题思路：首先先分组查询出每个学生的最大成绩，然后再判断最大成绩是否大于等于60进行过滤
select st.id,s_name,s_birth,s_sex from Student st join
    (select s_id,max(s_score) max_score from Score
    group by s_id
    having max_score < 60) a
on a.s_id = st.s_id;

4. 查询平均成绩小于60分的学生的学号和平均成绩，考虑没参加考试的情况

select a.s_id,avg(a.score) avg_score from
    (select st.s_id,if(sc.s_score is null,0,sc.s_score) score 
    from Student st 
    left join Score sc
    on st.s_id = sc.s_id) a
group by a.s_id
having avg_score < 60;

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

select st.s_id,st.s_name,if(a.num is null,0,a.num),if(a.all_score is null,0,a.all_score)
from Student st left join
    (select sc.s_id,count(1) num,sum(sc.s_score) all_score
    from Score sc 
    group by sc.s_id) a
on st.s_id = a.s_id;

6. 查询姓"猴"的老师的个数

select count(1) from Teacher where t_name like '%猴%';

7.查询没学过"张三"老师课的学生的学号、姓名

select st.s_id,st.s_name 
from Student st
where s_id not in
    (select sc.s_id from Score sc
    join Course c
    on sc.c_id = c.c_id
    join Teacher t 
    on c.t_id = t.t_id
    where t.t_name = '张三');

8. 查询学过"张三"老师所教的所有课的同学的学号、姓名

select st.s_id,st.s_name
from Student st
where s_id in 
    (select sc.s_id from Score sc
    join Course c 
    on sc.c_id = c.c_id
    join Teacher t
    on c.t_id = t.t_id
    where t.t_name = '张三');

9. 查询学过编号为'01'的课程并且也学过编号为'02'的课程的学生的学号、姓名

select st.s_id,st.s_name
from Student st
left join
    (select distinct sc1.s_id 
    from Score sc1 
    join Score sc2
    on sc1.s_id = sc2.s_id
    where sc1.c_id ='01' and sc2.c_id = '02') a
on st.s_id = a.s_id;

10. 查询学过编号为'01'的课程但没有学过编号为'02'的课程的学生的学号、姓名

select st.s_id,st.s_name
from Student st
    left join
    (select distinct sc1.s_id 
    from Score sc1
    join Score sc2
    on sc1.s_id = sc2.s_id
    where sc1.c_id = '01'
    and sc2.c_id != '02') a
on st.s_id = a.s_id;

select st.s_id,st.s_name 
from Student st
join 
    (select s_id,group_concat(c_id) c
    from Score sc
    group by s_id) a
on st.s_id = a.s_id
where c like '%01%' and c not like '%02%';

11.查询课程编号为'02'的总成绩

select sum(s_score)
from Score
group by c_id
having c_id = '02';

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

select st.s_id,st.s_name 
from Student st
join 
    (select s_id,min(s_score) min_score
    from Score 
    group by s_id
    having min_score < 60) a
on st.s_id = a.s_id;

13. 查询没有学全所有课的学生的学号、姓名

#解题思路：先查询出课程表的所有课程，再分组查询出每个学生的课程数量，再判断课程数量是否和总课程数相等
select distinct st.s_id,st.s_name
from Student st
join 
(select s_id,count(c_id) count_cid
from Score
group by s_id) a
on st.s_id = a.s_id
where a.count_cid =
(select count(c_id)
from Course c);

14. 查询至少有一门课与学号为'01'的学生所学课程相同的学生的学号、姓名

#解题思路：先查询出学号为'01'的学生的课程号，再连表查出课程号包含在集合里的学生
select distinct st.s_id,st.s_name 
		from Student st
		join Score sc1
		on st.s_id = sc1.s_id
		join Course c
		on sc1.c_id = c.c_id
		where sc1.c_id in
				(select sc.c_id 
				from Score sc 
				where sc.s_id = '01'
				)
				and sc1.s_id != '01';

15. 查询和'01'号同学所学课程完全相同的其他同学的学号

select st.s_id 
from Student st

select c_id 
from Course c

16. 查询没学过'张三'老师讲授的任一门课程的学生姓名

select s_name from Student 
where s_id not in
		(select sc.s_id
		from Student st
		join Score sc
		on st.s_id = sc.s_id
		join Course c1 
		on c1.c_id = sc.c_id
		where c1.c_name = 
				(select c.c_name 
				from Course c
				join Teacher t
				on c.t_id = t.t_id
				and t.t_name = '张三'));

17. 查询两门及其以上不及格课程的同学的学号、姓名及其平均成绩

select d.s_id,d.s_name,d.avg_score from 
		(select st.s_id,st.s_name,avg(sc.s_score) avg_score
		from Student st
		join Score sc
		on st.s_id = sc.s_id
		group by st.s_id) d
right join 
		(select  a.s_id,count(1) num
		from 
				(select s_id,s_score
					from Score
					having s_score < 60) a
		group by a.s_id
		having num >= 2) b
on d.s_id = b.s_id;

18. 检索'01'课程分数小于60，按分数降序排列的学生信息

select * from Student st
join Score sc 
on st.s_id = sc.s_id
where sc.c_id = '01'
and sc.s_score < 60
order by sc.s_score desc;

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

#解题思路：先查询出所有学生的平均成绩，再左连接成绩表，最后再根据平均成绩降序排列
select sc1.s_id,a.s_name,sc1.s_score,a.avg_score from Score sc1
left join 
				(select st.s_id,st.s_name,avg(s_score) avg_score
					from Score sc
					join Student st
					on sc.s_id = st.s_id
					group by st.s_id) a
on sc1.s_id = a.s_id
order by a.avg_score desc;

20. 查询各科成绩最高分、最低分和平均分：以如下形式显示：

– 课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
– （及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90）（！！）

select sc.c_id "课程ID",
			 c.c_name "课程名称",
			 max(sc.s_score) "最高分",
			 min(sc.s_score) "最低分",
			 avg(sc.s_score) "平均分",
			 sum(if(sc.s_score between 60 and 70,1,0))/count(*) "及格率",
			 sum(if(sc.s_score between 70 and 80,1,0))/count(*) "中等率",
			 sum(if(sc.s_score between 80 and 90,1,0))/count(*) "优良率",
			 sum(if(sc.s_score > 90,1,0))/count(*) "优秀率"
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id;

21. 按各科成绩进行排序，并显示排名

#解题要点：需要用到窗口函数，rank() over(partition by 字段名 order by 字段名)
#分区排名
select s_id,
			 c_id,
			 s_score,
       rank(partition by c_id,
            order by s_score desc) "排名"
from Score
order by s_score desc;

#全部排名
select s_id,
			 c_id,
			 s_score,
       row_number() over (partition by c_id order by s_score desc) rk_score
from Score
order by rk_score;

22. 查询学生的总成绩并进行排名

select a.*,
			 rank() over (order by sum_score desc) "排名"
from (select s_id,sum(s_score) sum_score
			from Score 
			group by s_id) a

23. 查询不同老师所教不同课程平均分从高到低显示

select a.t_id,
			 a.t_name,
			 avg(a.s_score) avg_score
from 
		(select t.t_id,t.t_name,c.c_id,c.c_name,sc.s_score
		from Teacher t
		join Course c
		on t.t_id = c.t_id
		join Score sc
		on c.c_id = sc.c_id) a
group by a.t_id
order by avg_score desc;

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

select a.s_id,
			 a.s_name,
			 a.s_score,
			 a.c_id,
			 a.rk
from 
			(select st.s_id,
						 st.s_name,
						 sc.s_score,
						 sc.c_id,
						 rank() over (partition by sc.c_id order by sc.s_score) rk
			from Score sc
			join Student st
			on st.s_id = sc.s_id) a
where a.rk = 2 or a.rk = 3;

25. 使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

select a.c_id,
			 a.c_name,
			 sum(if(a.s_score between 85 and 100,1,0)), 
			 sum(if(a.s_score between 75 and 85,1,0)), 
			 sum(if(a.s_score between 60 and 75,1,0)),
			 sum(if(a.s_score < 60,1,0)) 
from 
			(select c.c_id,
						 c.c_name,
						 sc.s_score
			from Score sc
			join Course c
			on sc.c_id = c.c_id) a
group by a.c_id;

26. 查询学生平均成绩及其名次

select a.s_id,
			 a.s_name,
			 a.avg_score,
			 rank() over (order by a.avg_score desc)
from 
			(select st.s_id,
						 st.s_name,
						 avg(s_score) avg_score
			from Score sc
			join Student st
			on sc.s_id = st.s_id
			group by st.s_id) a

27. 查询各科成绩前三名的记录

select st.* 
from Student st
join Score sc 
on st.s_id = sc.s_id
join Course c
on sc.c_id = c.c_id

28. 查询每门课程被选修的记录数

select c.c_id,c.c_name,count(c.c_id)
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id;

29. 查询出只有两门课程的全部学生的学号和姓名

select sc.s_id,
			 st.s_name,
       count(*) count_two 
from Score sc
right join Student st
on sc.s_id = st.s_id
group by st.s_id
having count_two = 2;

30. 查询男生、女生人数

select sum(if(s_sex = "男" ,1 ,0)) "男生",
			 sum(if(s_sex = "女" ,1 ,0)) "女生"
from Student

31. 查询1990年出生的学生名单

select s_name
from Student
where s_birth like "%1990%";

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

select distinct st.s_id,
			 st.s_name,
			 a.avg_score
from Score sc
join Student st
join 
				(select s_id,
								avg(s_score) avg_score
					from Score
					group by s_id
				) a
on st.s_id = a.s_id
where a.avg_score > 85;

33. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排序

select c_id,
			 avg(s_score) avg_score
from Score 
group by c_id
order by avg_score asc,c_id desc;

34. 查询课程名称为"数学"，且分数低于60的学生姓名和分数

select st.s_name,
			 sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
where sc.c_id = 
				(select c_id 
				 from Course 
				 where c_name = "数学")
and s_score < 60;

35. 查询所有学生的课程及分数情况(存在学生没成绩，没选课的情况)

select  st.s_id, st.s_name, sc.c_id, c.c_name, sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on c.c_id = sc.c_id;

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

select st.s_name,
			 c.c_name,
			 sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on sc.c_id  = c.c_id
where sc.s_score > 70;

37. 查询学生不及格的课程并按课程号从大到小排列

select st.s_name,
			 c.c_id,
			 c.c_name,
			 sc.s_score
from Student st
join Score sc
on st.s_id = sc.s_id
join Course c
on sc.c_id = c.c_id
where sc.s_score < 60
order by c.c_id;

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

select st.s_id,
			 st.s_name
from Student st
join Score sc
on st.s_id = sc.s_id
where sc.c_id = 03 and sc.s_score >80;

39. 求每门课程的学生人数

select c.c_id,
			 c.c_name,
			 count(sc.s_id)
from Course c
join Score sc
on c.c_id = sc.c_id
group by c.c_id;

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

select a.s_name,
			 a.s_score
from 
				(select st.s_name,
							 sc.c_id,
							 sc.s_score,
							 rank() over (order by sc.s_score desc) rank_score
				from Score sc
				join Student st
				on st.s_id = sc.s_id
				where sc.c_id = 
								(select c.c_id 
								 from Course c
								 join Teacher t
								 on c.t_id = t.t_id 
								 where t.t_name = '张三')) a
where a.rank_score = 1;

41. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select sc1.s_id,
       sc1.c_id,
       sc1.s_score
from Score sc1
join Score sc2
on sc1.s_score = sc2.s_score
where sc1.s_score = sc2.s_score
and sc1.s_id != sc2.s_id
and sc1.c_id != sc2.c_id;

42. 查询每门课程成绩最好的前两名学生信息

select * from 
(select a.s_name,
       a.c_id,
			 a.s_score,
			 rank() over(partition by c_id order by s_score) rank_score
from 
(select st.s_name,
			 sc.c_id,
			 sc.s_score
	from Student st
	join Score sc
	on sc.s_id = st.s_id
) a) b
where b.rank_score <= 2;

43. 统计每门课程的学生选修人数(超过5人的课程才统计)

select c.c_id,
			 c.c_name,
			count(s_id) cnt_sid
from Score sc
join Course c
on sc.c_id = c.c_id
group by c.c_id
having cnt_sid > 5
order by cnt_sid desc,c_id;

44. 检索至少选修两门课程的学生学号

select st.s_id,
			 st.s_name,
			 count(sc.c_id) cnt_cid
from Student st
join Score sc
on st.s_id = sc.s_id
group by sc.s_id
having cnt_cid >= 2;

45. 查询选修了全部课程的学生信息

select distinct a.s_id,
								a.s_name,
								a.s_birth,
								a.s_sex,
								a.cnt_course
from 
		(select st.s_id,
						st.s_name,
						st.s_birth,
						st.s_sex,
						count(1) over(partition by s_id) cnt_course
		from Score sc
		join Student st
		on sc.s_id = st.s_id
		) a
where a.cnt_course = 
(select count(c_id)
from Course);

46. 查询各学生的年龄(精确到月份)

SELECT IF(m.month != 0, CONCAT(m.year, "岁", m.month, "个月"), CONCAT(m.year, "岁"))  "年龄"
FROM 
		(
		SELECT FLOOR(DATEDIFF(CURRENT_DATE, s_birth) / 365) year, 
					 ROUND((DATEDIFF(CURRENT_DATE, s_birth) % 365) / 30) month
		FROM Student
		) m

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

select st1.s_name
from 
	(select distinct st.s_name
	from Student st
	join Score sc
	on st.s_id = sc.s_id
	where sc.c_id = 
	(select c.c_id 
	from Course c
	join Teacher t
	on c.t_id = t.t_id
	where t.t_name = "张三")) a
right join Student st1 
on a.s_name = st1.s_name
where a.s_name is null;

48. 查询两门以上不及格课程的同学

select b.s_name,
			 b.count_score,
			 c.avg_score
from 
			(select distinct a.s_name,
											count(a.s_score) count_score
			from 
					(select st.s_name,
								 sc.s_score
					from Student st
					join Score sc
					on st.s_id = sc.s_id
					where sc.s_score < 60
					) a
			group by a.s_name) b
join 
			(select st1.s_name,
						 avg(sc1.s_score) avg_score
			from Student st1
			join Score sc1
			on st1.s_id = sc1.s_id
			group by st1.s_id) c
on b.s_name = c.s_name;

49. 查询本月过生日的学生

select s_name
from Student 
where month(s_birth) = month(current_date);

50. 查询本周过生日的学生

select s_name
from Student
where week(s_birth) = week(current_date);