MySQL题库练习
...大约 14 分钟
MySQL题库练习
学习核心
- 熟练掌握MySQL基础语法查询
学习资料
案例准备
版本说明
SQL 文件对照版本为8.0.33，此处测试也可使用5.7.44（注意版本问题，以及一些参数配置的切换 ）
# 查看对应版本支持的排序规则
show collation;
show collation like '%gen%';
数据表说明：
- user：用户表
- student：学生表
- result：成绩表
- t_student：学生表
user.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int NOT NULL,
`name` varchar(255) DEFAULT NULL,
`age` int DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_name` (`name`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (1, 'Emma', 20, '深圳');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (2, 'Liam', 36, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (3, 'Noah', 25, '广州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (4, 'Jackson', 15, '上海');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (5, 'Sophia', 41, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (6, 'Aiden', 47, '北京');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (7, 'Isabella', 23, '广州');
INSERT INTO `user` (`id`, `name`, `age`, `city`) VALUES (8, 'Lucas', 17, '深圳');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
student.sql
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` int NOT NULL,
`sid` int DEFAULT NULL,
`classid` int DEFAULT NULL,
`sname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_sid` (`sid`) USING BTREE,
KEY `idx_classe` (`classid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (1, 10001, 1, '路飞');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (2, 10002, 1, '索隆');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (3, 10003, 1, '山治');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (4, 10004, 2, '娜美');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (5, 10005, 2, '乔巴');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (6, 10006, 3, '罗宾');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (7, 10007, 3, '布洛克');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (8, 10008, 4, '乌索普');
INSERT INTO `student` (`id`, `sid`, `classid`, `sname`) VALUES (9, 10009, 1, '路西');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
t_student.sql
/*
Navicat Premium Data Transfer
Source Server : 127.0.0.01
Source Server Type : MySQL
Source Server Version : 80033 (8.0.33)
Source Host : 127.0.0.01:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 80033 (8.0.33)
File Encoding : 65001
Date: 11/09/2023 20:16:13
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for t_student
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` int NOT NULL,
`student_id` int DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`course_id` int DEFAULT NULL,
`course_score` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_id_stuid` (`id`,`student_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of t_student
-- ----------------------------
BEGIN;
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (2, 1001, 'jj', 2, '88');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (3, 1001, 'jj', 3, '92');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (4, 1001, 'jj', 4, '98');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (5, 1002, 'nn', 1, '88');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (6, 1002, 'nn', 2, '90');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (7, 1002, 'nn', 3, '100');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (8, 1003, 'tt', 1, '66');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (9, 1003, 'tt', 2, '45');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (10, 1003, 'tt', 3, '55');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (11, 1004, 'yy', 1, '53');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (12, 1004, 'yy', 2, '88');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (13, 1005, 'uu', 1, '58');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (14, 1005, 'uu', 2, '58');
INSERT INTO `t_student` (`id`, `student_id`, `name`, `course_id`, `course_score`) VALUES (100, 1001, 'jj', 1, '88');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
result.sql
/*
Navicat Premium Data Transfer
Source Server : 127.0.0.01
Source Server Type : MySQL
Source Server Version : 80033 (8.0.33)
Source Host : 127.0.0.01:3306
Source Schema : test
Target Server Type : MySQL
Target Server Version : 80033 (8.0.33)
File Encoding : 65001
Date: 19/08/2023 18:59:13
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for result
-- ----------------------------
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result` (
`id` int NOT NULL,
`sid` int DEFAULT NULL,
`courseid` int DEFAULT NULL,
`cname` varchar(255) DEFAULT NULL,
`score` int DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_sid` (`sid`) USING BTREE,
KEY `idx_score` (`score`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- ----------------------------
-- Records of result
-- ----------------------------
BEGIN;
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (1, 10001, 1, '语文', 75);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (2, 10001, 2, '数学', 60);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (3, 10001, 3, '英语', 20);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (4, 10002, 1, '语文', 80);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (5, 10002, 2, '数学', 30);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (6, 10002, 3, '英语', 50);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (7, 10003, 1, '语文', 90);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (8, 10003, 2, '数学', 80);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (9, 10003, 3, '英语', 96);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (10, 10004, 1, '语文', 89);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (11, 10004, 2, '数学', 88);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (12, 10004, 3, '英语', 78);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (13, 10005, 1, '语文', 84);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (14, 10005, 2, '数学', 96);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (15, 10005, 3, '英语', 88);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (16, 10006, 1, '语文', 78);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (17, 10006, 2, '数学', 67);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (18, 10006, 3, '英语', 86);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (19, 10007, 1, '语文', 81);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (20, 10007, 2, '数学', 88);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (21, 10007, 3, '英语', 92);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (22, 10008, 1, '语文', 97);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (23, 10008, 2, '数学', 88);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (24, 10008, 3, '英语', 96);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (25, 10009, 1, '语文', 99);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (26, 10009, 2, '数学', 99);
INSERT INTO `result` (`id`, `sid`, `courseid`, `cname`, `score`) VALUES (27, 10009, 3, '英语', 99);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
样例练习1（student、result）
1.查询“索隆”各个科目的成绩
select stu.sname,res.cname,res.score
from student stu
left join result res on res.sid = stu.sid
where stu.sname = '索隆'
2.打印出路飞的数学成绩
select stu.sname,res.cname,res.score
from student stu
left join result res on res.sid = stu.sid
where stu.sname = '路飞' and res.cname = '数学'
3.打印出1班语文前2名的学生信息
# 思路1：子查询（5.7.44版本提示不支持子查询中有limit、in、any、some等子句，This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'）
select *
from student
where sid in (
select sid from result
where cname = '语文' and courseid = 1
order by score desc
limit 0,2
)
# 思路2：join 连接查询（直接过滤条件）
select stu.sname,res.cname,res.score
from student stu
left join result res on res.sid = stu.sid
where res.courseid = 1 and res.cname = '语文'
order by res.score desc
limit 0,2;
4.求各个科目前3名的学生信息（👻分组TOP-K问题）
MySQL5.7.44、MySQL8 针对分组TOP-K问题处理有所不同。
MySQL5.7.44 处理思路：依次分析每个学生当前科目得分是否为前3（转化为当前科目得分比他高的数量小于3则认为其在当前科目中排行前3），然后将筛选出来的记录关联student表获取到对应的学生信息信息详情（这种思路会允许一些同分的记录）
# 思路1：关联子查询
select *
from result ra
where (select count(*) from result rb where ra.cname = rb.cname and ra.score < rb.score) < 3
order by ra.cname
# 思路2：join查询
select ra.*
from result ra
left join result rb on ra.cname = rb.cname and ra.score < rb.score
group by ra.id,ra.cname
having count(1) < 3
order by ra.cname ,ra.score desc
# 思路3：result 与 student 表 关联查找：（获取到详细学生信息，和上述思路类似，此处多了一步关联查询学生详细信息的操作）
-- 子查询：计算每个科目中得分高于当前学生数量（如果该数量小于3，则表示当前学生为当前科目的前3名）
-- 外部inner join：通过连接查询关联到对应学生信息，并按照排序规则进行排序
select r.courseid,r.cname,s.sname,r.score
from result r
inner join student s on s.sid = r.sid
where (
select count(*)
from result
where cname = r.courseid and score > r.score
)<3
order by r.cname,r.score desc
MySQL8.0 处理思路：MySQL8中借助窗口函数解决，首先按照分组规则、排序规则筛选出记录，并借助窗口函数返回每个分组内的对应行号，然后根据这个分组内行号来封装排行效果
# 1.借助窗口函数row_number获取到每个分组内的记录行号
SELECT
*,
row_number() OVER (PARTITION BY cname ORDER BY score DESC) AS row_num
FROM result;
# 2.将上述结果当做一个新表进行检索，返回每个分组内的row_num<3(即对应前3记录)
SELECT *
FROM RankedResults -- 此处RankedResults对照为步骤1中获取到的结果集
WHERE row_num <= K;
# MySQL8.0中引入WITH关键字用于构建临时结果集（用于定义可重用的子查询，可以在一次查询中多次引用）,则上述语句可改造为
WITH AS RankedResults(
SELECT
*,
row_number() OVER (PARTITION BY cname ORDER BY score DESC) AS row_num
FROM result;
)
SELECT *
FROM RankedResults
WHERE row_num <= K;
5.求每个班级的总分
# 思路：根据student的classid关联做分组统计
select s.classid,sum(score) "totalScore"
from student s
left join result r on r.sid = s.sid
group by s.classid
6.求每门课程的最大分数
# 思路：根据课程进行分组，然后借助聚合函数获取每个分组的最大分数
select courseid,max(score) "maxScore"
from result
group by courseid
7.取总成绩最高的三位学生展示，输出：学号、总成绩
# 思路：先统计出每个学生的总成绩（学号、总成绩），然后按照总成绩排序获取top3
select s.sid,sum(r.score) "totalScore"
from student s
left join result r on r.sid = s.sid
group by s.sid
order by sum(r.score) desc
limit 0,3
8.查询平均分低于80的同学的姓名
# 思路：检索每个学生的平均分，然后过滤出平均分低于80的学生信息
select s.sname,avg(r.score) "avgScore"
from student s
left join result r on r.sid = s.sid
group by s.sid
having avg(r.score) < 80
9.查询每个班级有多少学生
select classid,count(sid) "stuNum"
from student
group by classid
10.统计同一个班级同名的学生姓名
# 思路：循环检索学生记录，校验当前班级是否有和他同名的学生，满足条件则加入结果集（这种方式必须过滤掉和自身的比较，否则始终会有1条自身的记录满足）
select sa.sname
from student sa
inner join student sb on sa.classid = sb.classid and sa.sname = sb.sname and sa.sid != sb.sid
group by sa.classid
-- 注意这种思路关联检索会将”自身“也纳入检索范围，需要排除掉自身记录，且需注意按照班级分组
# 思路2：按照classid,sname进行分组，然后统计每个分组的sname总和，如果count()大于1则说明重复（需要排除自身记录）
select sname
from student
group by classid,sname
having count(sname) > 1
样例练习2（student、classes、course）
1.输出语文成绩大于80的班级名称，并输出平均分
- 班级表（class）：cid（班级ID），cname（班级名称）
- 学生表（student）：sid（学生ID）、cid（关联班级ID）
- 课程表（course）：co_id（课程ID）、co_name（课程名称）、score（课程分数）、sid（关联学生ID）
涉及表：class、student、course
select co.co_name,avg(co.score) "avgScore"
from class c
inner join student s on s.cid = c.cid
inner join course co on co.sid = s.sid
where co.co_name = '语文' and co.score > 80
group by c.cname
2.求各班前10名的同学
- 学生-班级-成绩表（student_class）：id（ID）、name（学生姓名）、class_id（班级ID）、score（成绩）
涉及表：student_class
# 校验每个学生信息，关联学生表检索同班同学中只要分数比他高的学生人数少于10则说明该学生位于班级前10（思路类似于分组求top-K）
select
from student_class sca
where (select count(*) from student_class sca where sca.class_id = scb.class_id and sca.score < scb.score) < 10
order by sca.score desc
3.学生-选课成绩表（student_course）
- 学生-选课成绩表（student_course）：id（ID）、sid（学生ID）、sname（学生姓名）、course_id（课程ID）、course_score（课程分数）
查询至少选择4门课程以上的学生id、name
# 按照sid、sname进行分组，统计选课4门以上的学生信息（需注意对course_id进行去重处理，可能出现学生多次选课的情况）
select sc.sid,sc.sname
from student_course sc
group by sc.sid,sc.sname
having count(distinct sc.course_id) >= 4
查询所有不及格课程的学生各科平均分、姓名（按照各科平均成绩倒序输出）
# 按照学生信息进行分组，筛选出最小的课程分数校验不及格（此处只需要确认分数最少的记录即可判断其是否满足及格要求）
select sc.sname,sc.sname,avg(sc.score) "avgScore"
from student_course sc
group by sc.sname
having min(sc.course_score)<60
order by avg(sc.score) desc
查找两门及以上科目分数相同的学生信息
# 将student_course进行自连接，遍历学生信息获取到当前学生不同科目、分数相同的记录信息，然后将结果集进行分组统计
select sca.sid
from student_score sca,student_score scb
where sca.sid = scb.sid and sca.course_id != scb.course_id and sca.course_score = scb.course_score
group by sca.sid
having count(sdc.sid) >= 2
统计每门科目成绩都在80分以上的学号（转换思路：学生的最小成绩大于80分即可满足当前学生每门成绩都在80以上）
select sc.sid
from student_score sc
group by sc.sid
having min(sc.score) > 80
4.学生成绩单
- 学生成绩表（student_score）：cid（班级ID）、sid（学生ID）、course（课程信息）、score（课程成绩）
统计每个班有多少学生
select sc.cid,count(sc.sid) "studentCount"
from student_score sc
group by sc.cid
统计每个学生的平均成绩
select sc.sid,avg(sc.score) "avgScore"
from student_score sc
group by sc.sid
统计每个班级各科成绩的前3名（窗口函数）
# 传统子查询方式：校验每个班级、各个科目的学生成绩，如果大于同等条件下当前学生成绩的学生的记录数不超过3条，则说明其在当前条件位居前三
select sca.sid,sca.course,sca.score
from student_score sca
where (select count(*) from student_score scb where sca.cid = scb.cid and sca.course = scb.course and sca.score < scb.score)<3
# join 连接查询方式
select sca.sid,sca.course,sca.score
from student_score sca
inner join student_score scb on sca.cid = scb.cid and sca.course = scb.course and sca.score < scb.score
having count(1) < 3
# MYSQL 8 窗口函数
WITH AS RankedResults(
SELECT
*,
row_number() OVER (PARTITION BY cid,course ORDER BY score DESC) AS row_num
FROM student_score;
)
SELECT *
FROM RankedResults
WHERE row_num <= 3;
统计每个班级数学成绩大于英语成绩的学生信息
# 思路：分组统计获取到每个班级、每个学生 数学、英语成绩各自的最大值,然后过滤出数据成绩大于英语成绩的记录
select sub.*
from (
select
cid,
sid,
max(case when course = 'math' then score end) as max_math_score,
max(case when course = 'english' then score end) as max_english_score,
from student_score sc
)sub
where sub.max_math_score > sub.max_english_score
5.用户信息表
用户信息表（t_user）：id、name、age、city
统计大于18岁的用户个数
select count(id) "totalUser"
from t_user
where age > 18
求每个城市下年龄最大的员工信息
# 思路1：按照城市进行分组，随后获取年龄最大的值，然后再从表中根据该值匹配到最大年龄的员工信息
select tu.*
from t_user tu
left join (select city,max(age) "maxAge"
from t_user
group by city)subUser on subUser.city = tu.city and subUser.age = tu.age
# 思路2：参考分组TOP-K的子查询思路，循环检索每个用户的年龄，只要当前城市满足年龄比他大的员工记录数为0，则认为其是最大年龄的人，将记录返回结果集即可
select u1.*
from t_user u1
where (select count(1) form t_user u2 on u1.city = u2.city and u2.age > u1.age) = 0;
统计每个城市的用户数量
select city,count(id)
from t_user
group by city
按照年龄段分组统计人数
# 按照年龄分组
select age,count(id)
from t_user
group by age
# 按照年龄段分组（对age进行范围划定）
select
case
when age >=0 and age<18 then '0-17'
when age >=18 and age<30 then '18-30'
when age >=30 and age<60 then '30-60'
when age >=60 and age<100 then '60-100'
else '100+'
end as age_group,
count(*) as age_group_count
from t_user
group by age_group;
查询出现次数最多的城市
# 根据城市进行分组，统计出现次数并进行降序排序，选择第一条
select city,count(*) as city_count
from t_user
group by city
order by city_count desc
limit 1;
6.订单表
t_order：id、money、country
查找country是中国的、money倒序的第100条至200条记录
select *
from t_order
where country = '中国'
order by money desc
limit 99,100
7.其他场景
查询存在于a表而不存在于b表的数据
select column_name
from tableA
where column_name not in (select column_name from tableB);
A、B表中都存在字段tid，A、B表关联，返回B表中为null的在A中的记录
select ta.*
from tableA ta
left join tableB tb on ta.tid and tb.tid
where tb.tid is null
8.视频热度
数据表：播放时间、用户id、视频id
找出过去1小时内10个最热门视频
# 统计过去1h内的视频播放记录，然后进行降序排序，筛选出10条记录
select video_id,count(1) view_count
from t_video
where play_time > DATE_SUB(NOW(),INTERVAL 1 HOUR)
group by video_id
order by view_count desc
limit 10;
Powered by Waline v3.1.3