MySQL经典50题(建表、数据、题目列表及个人答案)

建表和插入数据

-- 建 表
-- 学 生 表
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);

题目列表

# 01 查询“01”课程比“02”课程成绩高的学生的信息及课程分数

# 02 查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)

# 03 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

# 04 查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

# 05 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

# 06 查询“李”姓老师的数量

# 07 查询学过张三老师授课的同学的信息

# 08 找出没有学过张三老师课程的学生

# 09 查询学过编号为 01,并且学过编号为 02 课程的学生信息

# 10 查询学过 01 课程,但是没有学过 02 课程的学生信息(注意和上面9题目的区别)

# 11 查询没有学完全部课程的同学的信息

# 12 查询至少有一门课与学号为 01 的同学所学相同的同学的信息

# 13 查询和 01 同学学习的课程完全相同的同学的信息

# 14 查询没有修过张三老师讲授的任何一门课程的学生姓名

# 15 查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩

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

# 17 按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩

# 18 查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格 率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90

# 19 按照各科成绩进行排序,并且显示排名

# 20 查询学生的总成绩,并进行排名

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

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

# 23 统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60] 及所占百分比

# 24 查询学生的平均成绩及名次

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

# 26 查询每门课被选修的学生数

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

# 28 查询男女生人数

# 29 查询名字中含有 风 字的学生信息

# 30 查询同名同性的学生名单,并统计同名人数

# 31 查询 1990 年出生的学生信息

# 32 查询每门课程的平均成绩,结果按平均成绩降序排列;平均成绩相同时,按课程编号 c_id 升序排列

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

# 34 查询课程名称为数学,且分数低于 60 的学生姓名和分数

# 35 查询所有学生的课程及分数情况

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

# 37 查询不及格的课程

# 38 查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名

# 39 每门课程的学生人数

# 40 查询选修“张三”老师所授课程的学生中,成绩最高的学生信息及其成绩

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

# 42 查询每门功成绩最好的前两名

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

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

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

# 46 查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减 1

# 47 查询本周过生日的学生

# 48 查询下周过生日的学生

# 49 查询本月过生的同学

# 50 查询下月过生的同学

01

查询“01”课程比“02”课程成绩高的学生的信息及课程分数

本质 行运算:比较运算
方法 1、直接比较:子查询
2、弄成两列:自关联(注意显式连接,提升查询速度)
备注 任一科成绩缺失不予比较
# 1、直接比较:子查询
select s.*,sc.s_score
from (
select sc1.s_id,sc1.s_score
from score sc1
where sc1.c_id='01'
and sc1.s_score>(select s_score from score where s_id=sc1.s_id and c_id='02')) sc join student s on sc.s_id=s.s_id


# 2.弄成两列:自关联(注意显式连接,提升查询速度)
select s.*,sc3.s_score 
from
	(
	select sc1.s_id,sc1.s_score 
	from
		( select s_id, s_score from score where c_id = '01' ) sc1
		join ( select s_id, s_score from score where c_id = '02' ) sc2 on sc1.s_id = sc2.s_id and sc1.s_score > sc2.s_score 
	) sc3
	join student s on sc3.s_id = s.s_id

# 2.2 直接自关联
select s.*,sc1.s_score
from student s 
join score sc1 on s.s_id=sc1.s_id and sc1.c_id='01'
join score sc2 on s.s_id=sc2.s_id and sc2.c_id='02' and sc1.s_score>sc2.s_score

02

查询“01”课程比“02”课程成绩低的学生的信息及课程分数(题目 1 是成绩高)

同上

03

查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

本质 行聚合,后having筛选
方法 直接聚合,后having对聚合函数进行筛选
select sc.s_id,s.s_name,round(avg(sc.s_score),0) as score
from score sc join student s on sc.s_id=s.s_id
group by sc.s_id
having score>=60

# 附加题:总分超过200分的同学
select sc.s_id,s.s_name,sum(sc.s_score) as score
from score sc join student s on sc.s_id=s.s_id
group by sc.s_id
having score>200

04

查询平均成绩小于 60 分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

注:Ctrl+R执行SQL语句

本质 行聚合,后having筛选
方法 直接聚合,聚合后用having筛选,注意主表
备注 1、主表是学生表,没有成绩的需要包含在内,故left join
2、having可看作聚合函数的where:为空判断,比较运算,子查询
3、用ifnull处理空/left join情况
# null 判断
select s.s_id,s.s_name,round(avg(sc.s_score),0) as score
from student s 
left join score sc on s.s_id=sc.s_id 
group by s.s_id
having score is null or score < 60

# ifnull 函数:更兼容
select s.s_id,s.s_name,round(avg(ifnull(sc.s_score,0)),0) as score
from student s 
left join score sc on s.s_id=sc.s_id 
group by s.s_id
having score < 60

05

查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

本质 行聚合
方法 直接聚合,注意对象范围
备注 主表是学生表,需考虑没有选课or没有成绩的情况
#本题sql
select s.s_id,s.s_name,
count(distinct sc.c_id) as c_num,
sum(sc.s_score) as score
from student s left join score sc on s.s_id=sc.s_id 
group by s.s_id

#如果需要聚合后筛选,则使用if和ifnull函数
#if和ifnull是好函数,处理null情况非常方便。注:ifnull搭配值运算函数使用,if函数搭配count函数
select s.s_id,s.s_name,
if(sc.s_id is null,0,count(distinct sc.c_id)) as c_num,
sum(ifnull(sc.s_score,0)) as score
from student s left join score sc on s.s_id=sc.s_id 
group by s.s_id

06

查询“李”姓老师的数量

本质 行筛选聚合
方法 筛选计数
备注
#问题:like是最优的吗
#like通配符
SELECT count(DISTINCT t_id) as t_num
FROM Teacher
WHERE t_name like '李%'

#截取姓氏,然后判断
SELECT count(DISTINCT t_id) as t_num
FROM Teacher
WHERE SUBSTR(t_name,1,1)='李'

07

查询学过张三老师授课的同学的信息

本质 行筛选:逐级
方法 表关联or子查询
备注
# 逐级子查询,这样更好理解
SELECT s.*
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id in(SELECT c_id FROM Course WHERE t_id in(SELECT t_id FROM Teacher WHERE t_name='张三'))


# 表关联
select s.*
from score sc
join course c on c.c_id=sc.c_id
join teacher t on t.t_id=c.t_id and t.t_name='张三'
join student s on sc.s_id=s.s_id

08

找出没有学过张三老师课程的学生

本质 反向行筛选
方法 按照学过筛选,最后取相反情况left join 或right join,且is null
备注
# 表关联
select s.*
from score sc 
join course c on c.c_id=sc.c_id
join teacher t on t.t_id=c.t_id and t.t_name='张三'
right join student s on sc.s_id=s.s_id
where sc.s_id is null

# not in,这个好理解
select *
from student
where s_id not in(
	select sc.s_id
	from score sc 
	join course c on c.c_id=sc.c_id
	join teacher t on t.t_id=c.t_id and t.t_name='张三'
	)

09

查询学过编号为 01,并且学过编号为 02 课程的学生信息

本质 行筛选:取交集
方法 自关联 or 子查询
备注
# 自关联
SELECT s.*
FROM Score sc1
JOIN Score sc2 on sc1.s_id=sc2.s_id and sc1.c_id='01' and sc2.c_id='02'
JOIN Student s on sc1.s_id=s.s_id

# 子查询
SELECT s.*
FROM Score sc1
JOIN Student s on sc1.s_id=s.s_id
WHERE sc1.c_id='01'
and sc1.s_id in(SELECT s_id FROM Score sc2 WHERE sc1.s_id=sc2.s_id and sc2.c_id='02')

10

查询学过 01 课程,但是没有学过 02 课程的学生信息(注意和上面9题目的区别)

本质 行筛选:取交集
方法 子查询,不能使用自关联,因为一条cid一条记录,不能用否判断
备注
# 子查询
SELECT s.*
FROM Score sc1
JOIN Student s on sc1.s_id=s.s_id
WHERE sc1.c_id='01'
and sc1.s_id not in(SELECT s_id FROM Score sc2 WHERE sc1.s_id=sc2.s_id and sc2.c_id='02')

11

查询没有学完全部课程的同学的信息

本质 行聚合,后having筛选
方法 直接聚合,注意表对象范围
备注 having可看作聚合函数的where:为空判断,比较运算,子查询
select s.*
from student s
left join score sc on sc.s_id=s.s_id 
group by s.s_id
having count(distinct sc.c_id)<(select count(*) from course)

#发现筛选,先筛选出学完全部课程的同学
SELECT *
FROM Student
WHERE s_id not in(SELECT s_id FROM Score GROUP BY s_id HAVING count(c_id)=(SELECT count(*) FROM Course))

12

查询至少有一门课与学号为 01 的同学所学相同的同学的信息

本质 行筛选:子查询
方法 1、学号01所学课程c_id01 2、学号不等于01且c_id in c_id01
备注
select s.*
from score sc 
join student s on sc.s_id=s.s_id and sc.s_id<>'01'
where sc.c_id in(select c_id from score where s_id='01')
group by s.s_id

13***

查询和 01 同学学习的课程完全相同的同学的信息

本质 行筛选:子查询
方法 1、学号01所学课程 2、和01所学相同课程且课程数等于01课程数
备注 关键是思考方法

group_concat()函数用法

group_concat([DISTINCT] 字 段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])
# 方法1 课程相同的数量相同
select s.*
from score sc 
join student s on sc.s_id=s.s_id
#课程数量相等
where sc.s_id in(SELECT s_id FROM Score WHERE s_id <>'01' GROUP BY s_id HAVING count(distinct c_id)=(select count(distinct c_id) from score where s_id='01'))
#课程相同的数量相同
and c_id in(select c_id from score where s_id='01')
group by s.s_id
having count(distinct sc.c_id)=(select count(distinct c_id) from score where s_id='01')


# 方法2 使用group_concat()函数,如果记录数比较大,建议采用方法1
select s.*
from student s
join score sc on s.s_id=sc.s_id and sc.s_id<>'01'
group by s.s_id
having group_concat(sc.c_id order by sc.c_id)=
(select group_concat(c_id order by c_id) from score where s_id='01' group by s_id)

14

查询没有修过张三老师讲授的任何一门课程的学生姓名

本质 反向行筛选
方法 修过张三老师任一门课即不符合要求
备注 注意表对象是全体学生,主表是学生表,因对s_id筛选,故不用再和score关联
select s_name
from student
where s_id not in(
select s_id from score
where c_id in(select c_id from course where t_id=(select t_id from teacher where t_name='张三')))

15*

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

本质 行筛选并聚合
方法 1、行筛选:及格与否 2、求不及格课程数 3、having筛选
备注 修过才会有及格与否之说,故score为主表
# 方式1
select sc.s_id,s.s_name,round(avg(sc.s_score),0) as avg_score
from score sc
join student s on sc.s_id=s.s_id
group by sc.s_id
having sum(if(sc.s_score<60,1,0))>=2

# 方式2 查询表更小
select sc.s_id,s.s_name,round(avg(sc.s_score),0) as avg_score
from score sc
join student s on sc.s_id=s.s_id and sc.s_score<60
group by sc.s_id
having count(*)>=2

16

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

本质 行筛选并排序
方法 分数<60、分数降序
备注 排序函数区别
1、row_number()over:123
2、rank()over():113
3、dense_rank()over():112
SELECT s.*
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id='01'
AND s_score<60
ORDER BY s_score DESC

17

按平均成绩从高到低(降序)显示所有学生的所有课程的成绩以及平均成绩

本质 表关联并排序
方法 找到主表,找到排序字段
备注 如何将排序字段和其他字段关联是关键点
这里的平均成绩是所选课程的平均成绩
# 纵表
SELECT s.s_name,c.c_name,sc.s_score,sc2.avg_score
FROM Student s 
JOIN Course c 
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
LEFT JOIN (SELECT s_id,ROUND(AVG(s_score),0) AS avg_score FROM Score GROUP BY s_id) sc2 ON s.s_id=sc2.s_id
ORDER BY sc2.avg_score DESC

#横表
SELECT s.s_name,
MAX(CASE sc.c_id WHEN '01' THEN sc.s_score END) AS '语文',
MAX(CASE sc.c_id WHEN '02' THEN sc.s_score END) AS '数学',
MAX(CASE sc.c_id WHEN '03' THEN sc.s_score END) AS '英语',
sc2.avg_score as '平均成绩'
FROM Student s 
JOIN Course c 
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
LEFT JOIN (SELECT s_id,ROUND(AVG(s_score),0) AS avg_score FROM Score GROUP BY s_id) sc2 ON s.s_id=sc2.s_id
GROUP BY s.s_name,sc2.avg_score
ORDER BY sc2.avg_score DESC

18

查询各科成绩最高分、最低分和平均分:以如下形式显示:课程 ID,课程 name,最高分,最低分,平均分,及格 率,中等率,优良率,优秀率;及格:>=60,中等为:70-80,优良为:80-90,优秀为:>=90

本质 表关联并聚合
方法 主表course,left join 成绩表score,求最值、均值及分组
备注 注意小数位数及百分率符号
SELECT c.c_id AS '课程id',c.c_name AS '课程name',
MAX(sc.s_score) AS '最高分',
MIN(sc.s_score) AS '最低分',
ROUND(AVG(sc.s_score),2) AS '平均分',
CONCAT(ROUND(SUM(if(sc.s_score>=60,1,0))/COUNT(sc.s_id)*100,2),'%') AS '及格率',
CONCAT(ROUND(SUM(if(sc.s_score>=70 AND sc.s_score<80,1,0))/COUNT(sc.s_id)*100,2),'%') AS '中等率',
CONCAT(ROUND(SUM(if(sc.s_score>=80 AND sc.s_score<90,1,0))/COUNT(sc.s_id)*100,2),'%') AS '优良率',
CONCAT(ROUND(SUM(if(sc.s_score>=90,1,0))/COUNT(sc.s_id)*100,2),'%') AS '优秀率'
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id
GROUP BY c.c_id

19

按照各科成绩进行排序,并且显示排名

本质 分组排序
方法 选择排序依据并排名
备注
SELECT c.c_name,s.s_name,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS '排名'
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
JOIN Course c ON sc.c_id=c.c_id

20

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

本质 行聚合后排序
方法 sum、排序
备注
SELECT s.s_name,SUM(sc.s_score) AS '总成绩',
ROW_NUMBER()OVER(ORDER BY SUM(sc.s_score) DESC) AS '排名'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name

21

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

本质 表关联后聚合再排序
方法 主表是教师表teacher和课程表course笛卡尔积,left join 成绩表score,求均值后order by
备注
SELECT t.t_name,c.c_name,ROUND(AVG(sc.s_score),2) AS '平均分'
FROM Teacher t
JOIN Course c ON t.t_id=c.t_id
LEFT JOIN Score sc ON c.c_id=sc.c_id
GROUP BY t.t_name,c.c_name
ORDER BY AVG(sc.s_score) DESC

22

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

本质 行排序后筛选
方法 所有课程成绩排名,再取2和3名
备注
SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM
(SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id)r
JOIN Student s ON r.s_id=s.s_id and r.rank_num in(2,3)

23

统计各科成绩各分数段人数:课程编号,课程名称,[100-85],[85-70],[70-60],[0-60] 及所占百分比

本质 行分组聚合
方法 先列基本:科目、成绩,后分组计数
备注
SELECT sc.c_id,c.c_name,
SUM(if(sc.s_score>=85,1,0)) AS '[100-85]人数',
SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0)) AS '[85-70]人数',
SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0)) AS '[70-60]人数',
SUM(if(sc.s_score<60,1,0)) AS '[0-60]人数',
CONCAT(ROUND(SUM(if(sc.s_score>=85,1,0))/COUNT(*)*100,2),'%') AS '[100-85]百分比',
CONCAT(ROUND(SUM(if(sc.s_score>=70 AND sc.s_score<85,1,0))/COUNT(*)*100,2),'%') AS '[85-70]百分比',
CONCAT(ROUND(SUM(if(sc.s_score>=60 AND sc.s_score<70,1,0))/COUNT(*)*100,2),'%') AS '[70-60]百分比',
CONCAT(ROUND(SUM(if(sc.s_score<60,1,0))/COUNT(*)*100,2),'%') AS '[0-60]百分比'
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY sc.c_id

24

查询学生的平均成绩及名次

本质 行聚合后排序
方法 求平均成绩,后排序
备注
SELECT s.s_name,ROUND(AVG(sc.s_score),2) AS '总成绩',
ROW_NUMBER()OVER(ORDER BY AVG(sc.s_score) DESC) AS '排名'
FROM Student s 
LEFT JOIN Score sc ON s.s_id=sc.s_id
GROUP BY s.s_name

25

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

本质 行排序后筛选
方法 各科成绩排序,取前3
备注
SELECT r.c_name,r.rank_num,s.s_name,r.s_score
FROM
(SELECT c.c_name,sc.s_id,sc.s_score,
ROW_NUMBER()OVER(PARTITION BY c.c_name ORDER BY sc.s_score DESC) AS rank_num
FROM Course c
LEFT JOIN Score sc ON c.c_id=sc.c_id)r
JOIN Student s ON r.s_id=s.s_id and r.rank_num<=3

26

查询每门课被选修的学生数

本质 行聚合
方法 成绩表按科目对s_id计数
备注
SELECT c.c_name,COUNT(DISTINCT sc.s_id) AS s_num
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_name

27

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

本质 行聚合
方法 成绩表按学生对c_id计数
备注
SELECT s.s_id,s.s_name
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT sc.c_id)=2

28

查询男女生人数

本质 行筛选聚合
方法 学生表student按性别分组计数
备注
SELECT s_sex,COUNT(DISTINCT s_id) AS '人数'
FROM Student
GROUP BY s_sex

29

查询名字中含有 风 字的学生信息

本质 模糊筛选
方法
备注
SELECT * 
FROM Student
WHERE s_name LIKE '%风%'

30

查询同名同性的学生名单,并统计同名人数

本质 分组聚合
方法 分组计数后having筛选
备注
SELECT s1.s_name,s1.s_sex,s2.num AS '同名人数'
FROM Student s1 
JOIN (SELECT s_name,COUNT(*) AS num FROM Student GROUP BY s_name HAVING COUNT(*)>=2)s2 ON s1.s_name=s2.s_name
GROUP BY s1.s_name,s1.s_sex
HAVING COUNT(*)>=2

31

查询 1990 年出生的学生信息

本质 行筛选
方法 获取出生年份,并筛选
备注 DATE()可以将varchar转成日期型
YEAR()函数返回一个指定日期or时间的年份值,范围为10009999,如果日期为零,YEAR()函数返回0
SELECT *
FROM Student
WHERE YEAR(DATE(s_birth))=1990

32

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

本质 行聚合后排序
方法 对课程求均值,后排序
备注 order by后可跟聚合函数
SELECT c.c_name,ROUND(AVG(sc.s_score),2) AS '平均分'
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_id
ORDER BY AVG(sc.s_score) DESC,c.c_id ASC

33

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

本质 行聚合后筛选
方法 对学生求成绩均值,后筛选
备注
SELECT s.s_id,s.s_name,ROUND(AVG(sc.s_score),2) AS '平均成绩'
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING AVG(sc.s_score)>=85

34

查询课程名称为数学,且分数低于 60 的学生姓名和分数

本质 行筛选
方法 按要求筛选,用到子查询
备注
SELECT s.s_name,sc.s_score
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM Course WHERE c_name='数学')
AND s_score<60

35

查询所有学生的课程及分数情况

本质 表关联后聚合
方法 学生表与课程表笛卡尔积,获取所有学生及所有课程,然后关联成绩表获取成绩,最后聚合
注:成绩表为窄表,故外层需要聚合
备注
SELECT s.s_name,
SUM(CASE c.c_name WHEN '语文' THEN sc.s_score ELSE 0 END) AS '语文',
SUM(CASE c.c_name WHEN '数学' THEN sc.s_score ELSE 0 END) AS '数学',
SUM(CASE c.c_name WHEN '英语' THEN sc.s_score ELSE 0 END) AS '英语',
SUM(sc.s_score) AS '总分'
FROM Student s
JOIN Course c
LEFT JOIN Score sc ON s.s_id=sc.s_id AND c.c_id=sc.c_id
GROUP BY s.s_name

36

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

本质 行筛选
方法 成绩表筛选>70
备注
SELECT s.s_name,c.c_name,sc.s_score
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
JOIN Course c ON sc.s_id=c.c_id
WHERE s_score>70

37

查询不及格的课程

本质 行筛选
方法
备注
SELECT sc.c_id,c.c_name,sc.s_score
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id
WHERE sc.s_score<60

38

查询课程编号为 01 且课程成绩大于等于 80 的学生的学号和姓名

本质 行筛选
方法
备注
SELECT s.s_id,s.s_name
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id='01'
AND s_score>=80

39

每门课程的学生人数

本质 行聚合
方法
备注
SELECT c.c_name,count(DISTINCT sc.s_id) AS '人数'
FROM Score sc
JOIN Course c ON sc.c_id=c.c_id
GROUP BY c.c_name

40

查询选修“张三”老师所授课程的学生中,成绩最高的学生信息及其成绩

本质 行筛选后聚合
方法 张三老师所授课程中,成绩最高(max、order by、row_number()over)的学生
1、max需要关联表匹配
2、order by和limit 1配合使用,推荐
3、row_number()over需要外层表限制rank_num=1
备注
SELECT s.*,s_score
FROM Score sc
JOIN Student s ON sc.s_id=s.s_id
WHERE c_id=(SELECT c_id FROM Course WHERE t_id=(SELECT t_id FROM Teacher WHERE t_name='张三'))
ORDER BY s_score DESC
LIMIT 1

41

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

本质 行筛选
方法 符合条件的成绩:按成绩分组,对课程id计数且>2
备注
SELECT *
FROM Score
WHERE s_score in(SELECT s_score FROM Score GROUP BY s_score HAVING COUNT(DISTINCT c_id)>=2)

42

查询每门功课成绩最好的前两名

本质 行排序后筛选
方法 这里分组取前两名,需窗口函数row_number()over
备注
SELECT r.*
FROM(
SELECT c_name,s_id,s_score,
ROW_NUMBER()OVER(PARTITION BY c_name ORDER BY s_score DESC) as rank_num
FROM Score sc 
JOIN Course c ON sc.c_id=c.c_id)r
WHERE r.rank_num<=2

43

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

本质 行聚合后排序
方法
备注
SELECT c_id,COUNT(DISTINCT s_id) AS '人数'
FROM Score
GROUP BY c_id
ORDER BY '人数' DESC,c_id ASC

44

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

本质 行聚合后筛选
方法
备注
SELECT s_id
FROM Score
GROUP BY s_id
HAVING COUNT(DISTINCT c_id)>=2

45

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

本质 行聚合后筛选
方法
备注
SELECT s.*
FROM Score sc 
JOIN Student s ON sc.s_id=s.s_id
GROUP BY s.s_id
HAVING COUNT(DISTINCT c_id)=(SELECT COUNT(*) FROM Course)

46

查询各学生的年龄:按照出生日期来算,当前月日 < 出生年月的月日则,年龄减 1

本质 行筛选:日期-年月日
方法 年月日获取year()、month()、day()
备注
SELECT *,
CASE 
	WHEN MONTH(NOW())<MONTH(DATE(s_birth)) THEN
		YEAR(NOW())-YEAR(DATE(s_birth))-1
	WHEN MONTH(NOW())=MONTH(DATE(s_birth)) AND DAY(NOW())<DAY(DATE(s_birth)) THEN
		YEAR(NOW())-YEAR(DATE(s_birth))-1
	ELSE
		YEAR(NOW())-YEAR(DATE(s_birth))
END AS age
FROM Student

47

查询本周过生日的学生

本质 行筛选:日期-周
方法 周获取week()
备注
SELECT *
FROM Student
WHERE WEEK(DATE(s_birth))=WEEK(NOW())

48

查询下周过生日的学生

本质 行筛选:日期-周
方法 周获取week()
备注
SELECT *
FROM Student
WHERE WEEK(DATE(s_birth))=WEEK(NOW())+1

49

查询本月过生的同学

本质 行筛选:日期-月
方法
备注
SELECT *
FROM Student
WHERE MONTH(DATE(s_birth))=MONTH(NOW())

50

查询下月过生的同学

本质 行筛选:日期-月
方法
备注
SELECT *
FROM Student
WHERE MONTH(DATE(s_birth))=MONTH(NOW())+1
上一篇:Java机试题:验证尼科彻斯定理,即:任何一个整数m的立方都可以写成m个连续奇数之和。(等差数列)


下一篇:JAVA编程_11-16_读数字(switch练习)/求素数 /毕业聚会/冒泡算法/选择排序 / 输入单词倒置