Mysql 经典例题练习与总结_mysql 练习
一、创建数据表并插入数据
-- 1、学生表
-- Student(s_id,s_name,s_birth,s_sex) ：学生编号、姓名、年月、性别
CREATE TABLE
IF NOT EXISTS `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`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
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', '女');
-- 2、课程表
-- Course(c_id,c_name,t_id) ：课程编号、 课程名称、 教师编号
CREATE TABLE
IF NOT EXISTS `Course` (
`c_id` VARCHAR (20),
`c_name` VARCHAR (20) NOT NULL DEFAULT '',
`t_id` VARCHAR (20) NOT NULL,
PRIMARY KEY (`c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Course VALUES ('01', '语文', '02');
INSERT INTO Course VALUES ('02', '数学', '01');
INSERT INTO Course VALUES ('03', '英语', '03');
-- 3、教师表
-- Teacher(t_id,t_name) ：教师编号、教师姓名
CREATE TABLE
IF NOT EXISTS `Teacher` (
`t_id` VARCHAR (20),
`t_name` VARCHAR (20) NOT NULL DEFAULT '',
PRIMARY KEY (`t_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
INSERT INTO Teacher VALUES ('01', '张三');
INSERT INTO Teacher VALUES ('02', '李四');
INSERT INTO Teacher VALUES ('03', '王五');
-- 4、成绩表
-- Score(s_id,c_id,s_score) ：学生编号、课程编号、分数
CREATE TABLE
IF NOT EXISTS `Score` (
`s_id` VARCHAR (20),
`c_id` VARCHAR (20),
`s_score` INT (3),
PRIMARY KEY (`s_id`, `c_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 插入数据
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);
二、开始解题
2.1 题目 1：查询 "01" 课程比 "02" 课程成绩高的学生的信息、课程分数
思路: 将 01 课程成绩和 02 课程成绩要对比, 所以必须要 2 个成绩表对比,
解法 1: 三表联合 筛选
解法 2: 根据学号查询两表对比后符合条件的学生
select * FROM student a,score b,score c
WHERE a.s_id = b.s_id
and a.s_id = c.s_id
and b.c_id = '01'
and c.c_id = '02'
and b.s_score > c.s_score
2.2 题目 2：查询平均成绩大于等于 60 分且总分大于 200 分的同学且必须考 3 门的学生编号和学生姓名和平均成绩
思路:
-
必须考三门 成绩表 count(s_score) = 3
-
平均成绩大于等于 60 avg(s_score) >= 60
注意, 这里因为平均成绩可能是循环小数, 所以用到保留函数
round(x,y) X 是所要修改的值, y 表示修改后的小数位数 -
总分大于 200 sum(s_score) > 200
三个都是和成绩有关, 而且学生编号是主体, 显然按学生分组
group by s_id
select
a.s_id,
a.s_name,
ROUND(AVG(b.s_score),2) avg_score,
ROUND(SUM(b.s_score),2) sum_score
from
student a
JOIN score b on a.s_id = b.s_id
GROUP BY
a.s_id
HAVING
avg_score >= 60
and sum_score > 200
and count(b.s_score) = 3;
2.3 题目 3：查询平均成绩小于 60 分的同学的学生编号、学生姓名、平均成绩（包括有成绩的和无成绩）
思路:
- 平均成绩小于 60 avg(s_score) < 60
- 包括无成绩 ,ifnull 如果是 NULL 则为 0 而且无成绩, 是每个学生都要有成绩, 所以需要学生表左连接右表 left join on
select
a.s_id,
a.s_name,
ROUND(AVG(IFNULL(b.s_score,0)),2) avg_score
from student a
left join score b on a.s_id = b.s_id
group by a.s_id
having
avg_score < 60
题目 4：查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
思路:
- 查询学生姓名 student
- 选课总数 count(score.c_id)
- 所有课程总成绩 sum(score.)
select
a.s_id 学生编号,
a.s_name 学生姓名,
COUNT(b.c_id) 选课总数,
SUM(IFNULL(b.s_score,0)) 总成绩
from student a
left join score b on a.s_id = b.s_id
GROUP BY a.s_id
ORDER BY 总成绩 desc
题目 5：查询 “李” 姓老师的数量
思路: like ''李 % ’
_代表一个占位符 % 代表任意个占位符
select *
from teacher
WHERE teacher.t_name LIKE "李%"
题目 6：查询学过张三老师授课的同学信息
思路:
解法 1. 多表联合查询张三老师授课的学生
解法 2. 张三在教师表中, 从课程表中找出张三课 id, 然后和成绩表连接, 最后取出学生信息
select a.*
from student a
join score b
on a.s_id = b.s_id
WHERE b.c_id in (
select c.c_id from course c where c.t_id =(
SELECT teacher.t_id from teacher WHERE t_name ='张三'))
select a.*
from
student a
join score b on a.s_id = b.s_id
join course c on b.c_id = c.c_id
join teacher d on c.t_id = d.t_id
WHERE d.t_name = '张三';
题目 7：找出没有学过张三老师课程的学生
思路 先将学过张三老师课程的学生找出来, 再 not in
SELECT t.*
from student t
WHERE t.s_id not in(
select a.s_id
from student a
join score b
on a.s_id = b.s_id
WHERE b.c_id in (
select c.c_id from course c where c.t_id =(
SELECT teacher.t_id from teacher WHERE t_name ='张三'))
)
题目 8：查询学过编号为 01，并且学过编号为 02 课程的学生信息
思路:
方法 1 : 成绩表自连接, 左表学 01 课程, 右表学 02 课程, 然后学号相等, 就是两个都学过的学生 , 然后 student 查询
方法 2 学过 02 课程的学生 学号 in 学过 01 的学生学号 , 然后 student 查询
select c.*
from score a
join score b on a.s_id = b.s_id and a.c_id = '01' and b.c_id = '02'
join student c on a.s_id = c.s_id;
题目 9：查询学过 01 课程，但是没有学过 02 课程的学生信息
思路: 学过 01 课程的学生 not in 学过 02 课程的学生 , 然后 student 查询
select a.*
from student a
join score b on a.s_id = b.s_id and b.c_id = '01'
WHERE a.s_id not in (select c.s_id from score c WHERE c.c_id = '02');
题目 10：查询没有学完全部课程的同学的信息 (本篇内容不考虑重修)
思路: 成绩表中, 按学号分组, 根据每个学生学习过的课程数量与课程总数对比
select a.*,count(b.c_id) cnt
from student a
join score b on a.s_id = b.s_id
GROUP BY a.s_id
having cnt <(select count(c_id) from course)
题目 11：查询至少有一门课与学号为 01 的同学所学相同的同学的信息
思路:
同学学习过的课程只需要有一个在学号 01 学生学过的课程中即可
c_id in(01 学过的课程)
select a.*
from student a
join score b on a.s_id = b.s_id and a.s_id != '01'
WHERE b.c_id in (select c.c_id from score c where c.c_id = '01')
题目 12：查询和 01 同学学习的课程完全相同的同学的信息
思路:
方法 1: 首先 个数要相同 其次 同学没学过的课程 01 没学过
方法 2: 使用 group_concat 将列连接, 这样就可以直接进行相等了
select a.*,GROUP_CONCAT(b.c_id) course_t
from student a
join score b on b.s_id = a.s_id and a.s_id != '01'
GROUP BY a.s_id
HAVING course_t = (select GROUP_CONCAT(c.c_id) from score c WHERE c.s_id = '01');
题目 13：查询没有修过张三老师讲授的任何一门课程的学生姓名
思路: 这个题的话, 找出张三老师教授的课程, 然后找出学过的学生, not in 学号
select s.*
from student s
WHERE s.s_id not in (
select a.s_id -- 1 2 3 4 5 7
from score a
join course b on a.c_id = b.c_id
join teacher c on c.t_id = b.t_id and c.t_name = '张三')
ORDER BY s_id
题目 14：检索 01 课程分数小于 60，按分数降序排列的学生信息
select a.* ,b.s_score
from
student a
join
score b on a.s_id = b.s_id
and b.s_score < 60
and b.c_id = '01'
order by b.s_score desc ;
题目 15：按平均成绩从高到低（降序）显示所有学生的所有课程的成绩以及平均成绩
思路:
注意这里要显示所有课程的成绩, 以及平均成绩
方法 1: 每个课程的成绩都得查询, 并且一个课程一个表, 就需要原成绩表 3 个单列成绩表 4 个表, 这样太复杂了
方法 2: case when
select
a.s_id
,max(case when a.c_id = '01' then a.s_score else 0 end ) 语文
,max(case when a.c_id = '02' then a.s_score else 0 end ) 数学
,max(case when a.c_id = '03' then a.s_score else 0 end ) 英语
,ROUND(AVG(a.s_score)) avgr
from score a
GROUP BY a.s_id
order BY avgr desc
题目 16：查询各科成绩最高分、最低分和平均分：以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率（及格：>=60），中等率（中等为：70-80），优良率（优良为：80-90），优秀率（优秀为：>=90）
思路:
根据 c_id 分组 然后 max(s_score) min(s_score) avg(s_score) case
注意 因为执行顺序 GROUP BY 先于 select 所以 select 的每行的数据都是对应 group by 的, 所以这时候对应的每个课程的全部分数
select
a.c_id
,max(a.s_score)
,min(a.s_score)
,round(sum(case when a.s_score >= 60 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end),2) 及格率
,round(sum(case when a.s_score >=70 and a.s_score <=80 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end),2) 中等率
,round(sum(case when a.s_score >=80 and a.s_score <=90 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end),2) 优良率
,round(sum(case when a.s_score >=90 then 1 else 0 end)/sum(case when a.s_score then 1 else 0 end),2) 优良率
from score a
GROUP BY a.c_id
题目 17：按照各科成绩进行排序，并且显示排名—比较综合，多看！
思路: 按照各科成绩成绩进行排名, 这个题的话, 需要进行 union 拼接
set @rank = 0;
select
a.c_id
,a.s_score
,(
select COUNT(DISTINCT t.s_score)
from score t
WHERE t.s_score >= a.s_score and a.c_id = t.c_id) 语文排名
from score a
order by a.c_id,a.s_score desc;
题目 18：查询学生的总成绩，并进行排名—比较综合，多看！
思路: 先把排名查出来看看, 因为要考虑并列情况
按课程号和成绩进行分组, 这样就可以按课程和成绩进行组合排名
select
a.c_id
,a.s_score
,(
select COUNT(DISTINCT t.s_score)
from score t
WHERE t.s_score >= a.s_score and a.c_id = t.c_id) 单科排名
from score a
order by a.c_id,a.s_score desc;
题目 19：查询学生的总成绩，并进行排名—比较综合，多看！
– 整 2 个总分表 总分表是一个学生对好几门课的成绩，所以按成绩分组了
– 然后总分表进行比较查出该学生的排名
select t1.s_id,t1.sc1_sum,count(*) AS no
from
(select sc1.s_id,SUM(sc1.s_score) sc1_sum
from score sc1
GROUP BY sc1.s_id) t1
join
(select sc2.s_id,SUM(sc2.s_score) sc2_sum
from score sc2
GROUP BY sc2.s_id) t2 on t1.sc1_sum <= t2.sc2_sum
GROUP BY t1.s_id
order by no;
题目 20：查询所有课程的成绩第 2 名到第 3 名的学生信息及该课程成绩
思路: 先按成绩进行排名, 然后按照 limit 查询出需要的学生信息
select a1.*,t1.s_score,@cid := '01' c_id
from student a1
join (
select c1.s_id,c1.s_score
from score c1
WHERE c1.c_id = '01'
ORDER BY c1.s_score
limit 1,2
) t1 on a1.s_id = t1.s_id
UNION
select a2.*,t2.s_score,@cid := '02' c_id
from student a2
join (
select c2.s_id,c2.s_score
from score c2
WHERE c2.c_id = '02'
ORDER BY c2.s_score
limit 1,2
) t2 on a2.s_id = t2.s_id
UNION
select a3.*,t3.s_score,@cid := '03' c_id
from student a3
join (
select c3.s_id,c3.s_score
from score c3
WHERE c3.c_id = '03'
ORDER BY c3.s_score
limit 1,2
) t3 on a3.s_id = t3.s_id;
题目 21：统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[0-60] 及所占百分比
思路: 按课程编号进行分组, 然后使用 case when 语句分析出每科的相应分段人数
解法只写了一个段的类型占比
select
b.c_id
,b.c_name
,ROUND(SUM(case when a.s_score >= 85 and a.s_score <= 100 then 1 else 0 end)/count(*),2) '100-80'
,ROUND(SUM(case when a.s_score <= 85 and a.s_score >=70 then 1 else 0 end)/count(*),2) '85-70'
,ROUND(SUM(case when a.s_score <= 70 and a.s_score >=60 then 1 else 0 end)/count(*),2) '70-60'
,ROUND(SUM(case when a.s_score <= 60 and a.s_score >=0 then 1 else 0 end)/count(*),2) '0-60'
,count(*)
from score a
join course b on a.c_id = b.c_id
GROUP BY a.c_id;
题目 22：查询学生的平均成绩及名次—比较综合，多看，定义变量，实现 rank 函数
-- 分析 先查询平均成绩
select
a.s_id -- 学号
,@i:=@i+1 as '不保留空缺排名' -- 直接i的自加，顺序一直变大，声明变量需加@ 由于要放入列中需要 :=
,@k:=(case when @i=1 or @avg_score=a.avg_s then @k else @k+1 end) as '保留空缺排名'
-- 因为第一次必定是1所以和i一致为1 后面则按分数是否和上一个相同排名
,@avg_score:=avg_s as '平均分' -- 表a中的值
from (select
s_id
,round(avg(s_score), 2) as avg_s
from Score
group by s_id
order by 2 desc)a -- 表a：平均成绩的排序和学号
,(select @avg_score:=0, @i:=0, @k:=1) b -- 表b：进行变量初始化，固定写法。
-- order by时,把要定义的变量定义在放在后面
题目 23：查询每门课被选修的学生数
思路：根据课程统计有成绩的学生 成绩表和课程表联合查询
select a.c_id,COUNT(b.s_score)
from course a
join score b on a.c_id = b.c_id
GROUP BY a.c_id
题目 24：查询出只有两门课程的全部学生的学号和姓名
思路：根据学号对成绩表分组，然后统计每个学生的课程分数 再通过学生表查询即可
select a.s_id,a.s_name
from student a
join score b on b.s_id = a.s_id
GROUP BY b.s_id
HAVING count(b.s_score) = 2;
题目 24：查询男女生人数
思路：根据性别分组查询，然后 count
select s_sex,COUNT(s_sex)
from student a
GROUP BY s_sex
题目 24：查询名字中含有风字的学生信息
思路： 含有风 使用 % % 风 % % 代指任意个字符包括 0 个
select a.*
from student a
WHERE a.s_name LIKE '%风%';
本题选自 https://blog.csdn.net/qq_40216188/article/details/118670474
题目 24：查询同名同性的学生名单，并统计同名人数
思路： 姓名相同性别相同，要进行比较，学生表自连接， 姓名相同 性别相同 学号不同 查询 distinct 姓名
select a.s_name, COUNT(b.s_id)
from student a
join student b on a.s_name = b.s_name
and a.s_id!= b.s_id
and a.s_sex = b.s_sex
GROUP BY a.s_name
题目 25：查询每门课程的平均成绩，结果按平均成绩降序排列；平均成绩相同时，按课程编号 c_id 升序排列
思路：查询每门课程的平均成绩，按成绩表的课程号分组 平均
SELECT a.c_id,ROUND(AVG(a.s_score),2) avgr
from score a
GROUP BY a.c_id
ORDER BY avgr desc,c_id asc;
题目 26：查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
思路：
- 查询学生的姓名 学生表
- 平均成绩 成绩表
- 平均成绩， 成绩表按学号分组 group by(score.s_id)
- 平均成绩大于 85 having avg(s_score) > 85
- 子查询或者联合查询
select
a.s_id
,a.s_name
,ROUND(AVG(b.s_score),2) avgr
from student a
join score b on a.s_id = b.s_id
GROUP BY b.s_id
HAVING AVG(s_score) > 85
题目 27：查询所有学生的课程及分数（均分、总分）情况
思路：
- 学生 ID 是主要的，要查询均分和总分， 所以必定是 group by 学生 id avg sum
- 因为要查询每门成绩，所以这里需要用 case when 否则就得用 多表拼接，太麻烦
select
a.s_id
,max(case WHEN c_id = '01' then a.s_score else 0 END) 语文
,max(case WHEN c_id = '02' then a.s_score else 0 END) 数学
,max(case WHEN c_id = '03' then a.s_score else 0 END) 英语
,ROUND(AVG(a.s_score),2) 平均分
,SUM(a.s_score) 总分
from score a
GROUP BY s_id
题目 28：查询选修 "张三" 老师所授课程的学生中，成绩最高的学生信息及其成绩
思路：
这个题有点奇怪，如果是多个学生并列的话，应该怎么做呢，假设前提是张三只教一门课程, 用 having 直接过滤选出成绩等于最大的学生 ID，然后信息就 student 查询
select a.s_id,a.s_score,t.*
from score a
join student t on a.s_id = t.s_id
join course b on a.c_id = b.c_id
join teacher c on b.t_id = c.t_id
and c.t_name = '张三'
HAVING a.s_score = max(s_score)
ORDER BY a.s_score desc;
题目 29: 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 (不需要看)
思路：
- 不同课程成绩相同 课程表自联结 课程不同就是课程号不同， 成绩相同，
- 同一个学生，所以 s_id 相同
只能说是破题… 重复问题没法解决
SELECT a.*,b.c_id,b.s_score
from score a
join score b on a.c_id !=b.c_id
and a.s_id = b.s_id
and a.s_score = b.s_score
题目的要求就是找出每门课的前 2 名同学—多看，比较综合，解决前几名排序的问题
题目 30：找出每门课的前 2 名同学 (值得学习)
解法：成绩表自联结找出每个同学每门课的排名 然后根据 having count 找出每门课要求的前几名的同学
求排名的方法：两表自连或者子查询，比较然后通过 having count 确定排名
- 存在并列排名，所以表连接条件中成绩比较不能带等号 这样的话，就必须使用左查询了，不然成绩相等的直接去掉了
- 因为是比每门课的成绩 所以按课程号和学生号分组
- 因为存在 count = 0 的情况，可是排名是不可能为 0 的，所以需要使用 case when 语句对 0 特殊处理
select a.c_id,a.s_id
,(case when COUNT(b.s_id) > 0 then COUNT(b.s_id) else 1 end ) 排名
from score a
left join score b on a.s_id != b.s_id
and a.c_id = b.c_id
and a.s_score < b.s_score
GROUP BY c_id,s_id
HAVING COUNT(b.s_id) <= 2
ORDER BY a.c_id,排名
题目 31：统计每门课程的学生选修人数（超过 5 人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
思路：
不考虑重修，统计每门课程的选修人数，显然按课程进行分组，count 人数 先按人数降序，再按课程号升序
SELECT c_id,COUNT(s_id) 选修人数
from score a
GROUP BY c_id
order by 2 desc,1 asc
题目 32：检索至少选修两门课程的学生学号
思路： 成绩表 按学生学号分组，然后统计课程数大于等于 2 即可
SELECT
s_id,
count(*)
FROM
Score
GROUP BY
s_id
HAVING
count(*) >= 2;
题目 33：查询选修了全部课程的学生信息
思路：因为不考虑重修，所以直接选修课程个数等于最大课程个数即可
select a.*
from student a
WHERE a.s_id in (
select b.s_id
from score b
GROUP BY b.s_id
HAVING count(b.c_id) = (select count(c_id) from course ) )
题目 34：查询各学生的年龄：按照出生日期来算，当前月日 > 出生年月的月日则，年龄减 1
思路：显然这是 控制流语句 条件就是 当前月 日 > 出生年月的月日 case when
case when 当前月 日 > 出生年月的月日 then year(出生)- year(当前) -1 else then year(出生)- year(当前) end
select
a.s_id
,a.s_name
,a.s_birthday
,(case when DATE_FORMAT(CURRENT_DATE,'%m%d') > DATE_FORMAT(a.s_birthday,'%m%d')
then YEAR(CURRENT_DATE)-YEAR(a.s_birthday)-1
else YEAR(CURRENT_DATE)-YEAR(a.s_birthday) end)
from student a
题目 35：查询本周过生日的学生
解法1 ：查询本周过生日的学生 学生今年过生日的周数 计算需要用到字符串拼接 因为他是今年 月份是生日月份
select a.*
from student a
WHERE WEEK(CURRENT_DATE,1) =
WEEK( concat(
YEAR(
CURRENT_DATE),DATE_FORMAT(a.s_birthday,'%m%d')),1)
查询下周过生日的学生
思路：+1
select a.*
from student a
WHERE WEEK(CURRENT_DATE,1) +1=
WEEK( concat(
YEAR(
CURRENT_DATE),DATE_FORMAT(a.s_birthday,'%m%d')),1)
本文是原创文章，采用 CC BY-NC-ND 4.0 协议，完整转载请注明来自 奇怪的阿峰
阅读建议
评论
匿名评论
隐私政策
你无需删除空行，直接评论以获取最佳展示效果