最近在学MySQL，网上有很多版本的MySQL经典50题解答，有时间会二刷并且加入性能方面的考量，后续也会继续整理加入收集到的面试题目。
原始表格及题目来源网络，分为四张表
1.学生表
Student(s_id,s_name,s_birth,s_sex)
学生编号,学生姓名, 出生年月,学生性别
2.课程表
Course(c_id,c_name,t_id)
课程编号, 课程名称, 教师编号
3.教师表
Teacher(t_id,t_name)
教师编号,教师姓名
4.成绩表
Score(s_id,c_id,s_score)
学生编号,课程编号,分数
------------------------------创建数据库、表、插入数据------------------------------
#创建数据库
CREATE DATABASE test;
#选择数据库
USE test;
#建表
#学生表
CREATE TABLE Student(
s_id VARCHAR(20) COMMENT '学生编号',
s_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '学生姓名',
s_birth VARCHAR(20) NOT NULL DEFAULT '' COMMENT '出生年月',
s_sex VARCHAR(10) NOT NULL DEFAULT '' COMMENT '学生性别',
PRIMARY KEY(s_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生表';
#课程表
CREATE TABLE Course(
c_id VARCHAR(20) COMMENT '课程编号',
c_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程名称',
t_id VARCHAR(20) NOT NULL COMMENT '教师编号',
PRIMARY KEY(c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '课程表';
#教师表
CREATE TABLE Teacher(
t_id VARCHAR(20) COMMENT '教师编号',
t_name VARCHAR(20) NOT NULL DEFAULT '' COMMENT '教师姓名',
PRIMARY KEY(t_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '教师表';
#成绩表
CREATE TABLE Score(
s_id VARCHAR(20) COMMENT '学生编号',
c_id VARCHAR(20) COMMENT '课程编号',
s_score INT(3) COMMENT '分数',
PRIMARY KEY(s_id,c_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '成绩表';
#插入学生表测试数据
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('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2017-12-30' , '女');
insert into Student values('12' , '赵六' , '2017-01-01' , '女');
insert into Student values('13' , '孙七' , '2018-01-01' , '女');
#课程表测试数据
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);
-----------------------------------------习题解答-----------------------------------------
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select s.*,sc.s_score AS c1,sc2.s_score AS c2 from score sc left join student s on sc.s_id=s.s_id inner join score sc2 on sc.s_id=sc2.s_id and sc.c_id='01' and sc2.c_id='02' where sc.s_score>sc2.s_score;
正确输出:
02 钱电 1990-12-21 男 70 60
04 李云 1990-08-06 男 50 30
1.1、查询同时存在" 01 "课程和" 02 "课程的情况
A.
select a.*,b.c_id,b.s_score
from score as a left join score as b
on a.s_id=b.s_id
where a.c_id='01' and b.c_id='02';
B.
select a.*,b.c_id,b.s_score
from score as a inner join score as b
on a.s_id=b.s_id and a.c_id='01' and b.c_id='02';
#left join后一定要接where
#因为and会合并满足a.s_id=b.s_id and a.c_id='01' and b.c_id='02'的位置
#但最后会以left表为基准，输出null值记录
正确输出：
+------+------+---------+------+---------+
| s_id | c_id | s_score | c_id | s_score |
+------+------+---------+------+---------+
| 01 | 01 | 80 | 02 | 90 |
| 02 | 01 | 70 | 02 | 60 |
| 03 | 01 | 80 | 02 | 80 |
| 04 | 01 | 50 | 02 | 30 |
| 05 | 01 | 76 | 02 | 87 |
+------+------+---------+------+---------+
1.2、查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null)
select a.*,b.c_id,b.s_score
from score as a left join score as b
on a.s_id=b.s_id and b.c_id='02'
where a.c_id='01';
正确输出：
+------+------+---------+------+---------+
| s_id | c_id | s_score | c_id | s_score |
+------+------+---------+------+---------+
| 01 | 01 | 80 | 02 | 90 |
| 02 | 01 | 70 | 02 | 60 |
| 03 | 01 | 80 | 02 | 80 |
| 04 | 01 | 50 | 02 | 30 |
| 05 | 01 | 76 | 02 | 87 |
| 06 | 01 | 31 | NULL | NULL |
+------+------+---------+------+---------+
1.3、查询不存在" 01 "课程但存在" 02 "课程的情况
select s_id from score where s_id not in
(select s_id from score where c_id='01')
and c_id='02';
正确输出：
+------+
| s_id |
+------+
| 07 |
+------+
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s.*,round(avg(s_score),2) avg from student s inner join score sc on s.s_id=sc.s_id group by s_id having avg>=60;
正确输出：
+------+--------+------------+-------+-------+
| s_id | s_name | s_birth | s_sex | avg |
+------+--------+------------+-------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 | 89.67 |
| 02 | 钱电 | 1990-12-21 | 男 | 70.00 |
| 03 | 孙风 | 1990-05-20 | 男 | 80.00 |
| 05 | 周梅 | 1991-12-01 | 女 | 81.50 |
| 07 | 郑竹 | 1989-07-01 | 女 | 93.50 |
+------+--------+------------+-------+-------+
☆3、查询在 SC 表存在成绩的学生信息
Q:
①怎么看哪种方法更好？
set profiling=1;show profiles;结果好像有波动，数据量小的时候看不出哪种方法更快
查了一些explain结果的含义，但不知道是不是具体效率和extra里的内容也有关系。
例如这题，按理说join更快，但A.B两种方法的type，key，row都相同，explain只有extra细微区别，
实际show profiles看执行速度的时候似乎有波动，不能确定哪个更快。
②用row_number()去重是不是只能多加一层嵌套，如C，是不是没有group高效？
A.select * from student where s_id in (select s_id from score group by s_id);
B.select s.* from student s inner join score sc on s.s_id=sc.s_id group by s.s_id;
C.select t.s_id,t.s_name,t.s_birth,t.s_sex from (select s.*,row_number() over (partition by s_id) as rank1 from student s inner join score sc on s.s_id=sc.s_id) t where rank1=1 ;
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
A.explain select * from student where s_id in (select s_id from score group by s_id);
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
| 1 | SIMPLE | score | NULL | index | PRIMARY | PRIMARY | 124 | NULL | 18 | 38.89 | Using index; LooseScan |
| 1 | SIMPLE | student | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.score.s_id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------+------+----------+------------------------+
B.explain select s.* from student s inner join score sc on s.s_id=sc.s_id group by s.s_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
| 1 | SIMPLE | sc | NULL | index | PRIMARY | PRIMARY | 124 | NULL | 18 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | s | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.sc.s_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+------------------------------+
C.explain select t.s_id,t.s_name,t.s_birth,t.s_sex from (select s.*,row_number() over (partition by s_id) as rank1 from student s inner join score sc on s.s_id=sc.s_id) t where rank1=1 ;
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 8 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | sc | NULL | index | PRIMARY | PRIMARY | 124 | NULL | 18 | 100.00 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | s | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.sc.s_id | 1 | 100.00 | NULL |
+----+-------------+------------+------------+--------+---------------+-------------+---------+--------------+------+----------+----------------------------------------------+
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s.*,(case when s_score is not null then count(1) else 0 end) as num,sum(case when s_score is not null then s_score else 0 end) as total from student s left join score sc on s.s_id=sc.s_id group by s.s_id;
正确输出：
+------+--------+------------+-------+-----+-------+
| s_id | s_name | s_birth | s_sex | num | total |
+------+--------+------------+-------+-----+-------+
| 01 | 赵雷 | 1990-01-01 | 男 | 3 | 269 |
| 02 | 钱电 | 1990-12-21 | 男 | 3 | 210 |
| 03 | 孙风 | 1990-05-20 | 男 | 3 | 240 |
| 04 | 李云 | 1990-08-06 | 男 | 3 | 100 |
| 05 | 周梅 | 1991-12-01 | 女 | 2 | 163 |
| 06 | 吴兰 | 1992-03-01 | 女 | 2 | 65 |
| 07 | 郑竹 | 1989-07-01 | 女 | 2 | 187 |
| 09 | 张三 | 2017-12-20 | 女 | 0 | 0 |
| 10 | 李四 | 2017-12-25 | 女 | 0 | 0 |
| 11 | 李四 | 2017-12-30 | 女 | 0 | 0 |
| 12 | 赵六 | 2017-01-01 | 女 | 0 | 0 |
| 13 | 孙七 | 2018-01-01 | 女 | 0 | 0 |
+------+--------+------------+-------+-----+-------+
5、查询"李"姓老师的数量
SELECT COUNT(t_name) AS TeacherNamedLi FROM teacher WHERE t_name LIKE "李%"
正确输出：
1
6、查询学过"张三"老师授课的同学的信息
select s.* from student s inner join score sc on s.s_id=sc.s_id inner join course c on c.c_id=sc.c_id inner join teacher t on t.t_id=c.t_id where t.t_name="张三";
正确输出：
01 赵雷 1990-01-01 男
02 钱电 1990-12-21 男
03 孙风 1990-05-20 男
04 李云 1990-08-06 男
05 周梅 1991-12-01 女
07 郑竹 1989-07-01 女
7、查询没有学全所有课程的同学的信息
select s.* from student s left join score sc on s.s_id=sc.s_id group by s.s_id having count(1)<3;
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
| 09 | 张三 | 2017-12-20 | 女 |
| 10 | 李四 | 2017-12-25 | 女 |
| 11 | 李四 | 2017-12-30 | 女 |
| 12 | 赵六 | 2017-01-01 | 女 |
| 13 | 孙七 | 2018-01-01 | 女 |
+------+--------+------------+-------+
☆8、查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
Q:这题和3有些像，还是不清楚哪种情况更快。
老师之前有在群里说过distinct和子查询最好不要用
但看explain结果type，ref、row都是子查询方法更佳。
A.用join
select s.* from student s left join score sc on s.s_id=sc.s_id where c_id in (select c_id from score where s_id='01') group by s_id;
explain select s.* from student s left join score sc on s.s_id=sc.s_id where c_id in (select c_id from score where s_id='01') group by s_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+
| 1 | SIMPLE | score | NULL | ref | PRIMARY | PRIMARY | 62 | const | 3 | 100.00 | Using index; Using temporary |
| 1 | SIMPLE | sc | NULL | index | PRIMARY | PRIMARY | 124 | NULL | 18 | 10.00 | Using where; Using index; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | s | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.sc.s_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+-----------------------------------------------------------------+
B.子查询group
select * from student where s_id in (select distinct s_id from score where c_id in (select c_id from score where s_id='01') and s_id!='01');
explain select * from student where s_id in (select distinct s_id from score where c_id in (select c_id from score where s_id='01') and s_id!='01');
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+
| 1 | SIMPLE | score | NULL | range | PRIMARY | PRIMARY | 62 | NULL | 16 | 43.75 | Using where; Using index; LooseScan |
| 1 | SIMPLE | score | NULL | eq_ref | PRIMARY | PRIMARY | 124 | const,test.score.c_id | 1 | 100.00 | Using index; FirstMatch(score) |
| 1 | SIMPLE | student | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.score.s_id | 1 | 100.00 | NULL |
+----+-------------+---------+------------+--------+---------------+---------+---------+-----------------------+------+----------+-------------------------------------+
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
| 05 | 周梅 | 1991-12-01 | 女 |
| 06 | 吴兰 | 1992-03-01 | 女 |
| 07 | 郑竹 | 1989-07-01 | 女 |
+------+--------+------------+-------+
☆9、查询和"01"号的同学学习的课程完全相同的其他同学的信息
Q:想问下老师还有没有更好的方法？
select @group:=group_concat(c_id)
from score group by s_id having s_id='01';
select s.* from student s inner join score sc on sc.s_id=s.s_id
where s.s_id!='01' group by s.s_id having group_concat(c_id)=@group;
正确输出
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+------------+-------+
10、查询没学过"张三"老师授课的同学的信息
select s.* from student s inner join score sc on s.s_id=sc.s_id inner join course c on c.c_id=sc.c_id inner join teacher t on t.t_id=c.t_id where t.t_name!="张三";
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 06 | 吴兰 | 1992-03-01 | 女 |
| 09 | 张三 | 2017-12-20 | 女 |
| 10 | 李四 | 2017-12-25 | 女 |
| 11 | 李四 | 2017-12-30 | 女 |
| 12 | 赵六 | 2017-01-01 | 女 |
| 13 | 孙七 | 2018-01-01 | 女 |
+------+--------+------------+-------+
11、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select s.s_id,s.s_name,round(avg(s_score),2) as avg from student s inner join score sc on s.s_id=sc.s_id where s.s_id in(select s_id from score where s.s_score<60 group by s_id having count(1)>=2) group by s_id;
正确输出：
+------+-------+--------+
| s_id | s_avg | s_name |
+------+-------+--------+
| 04 | 33.33 | 李云 |
| 06 | 32.50 | 吴兰 |
+------+-------+--------+
12、检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s.*,sc.s_score from student as s
inner join score as sc
on s.s_id=sc.s_id and sc.c_id='01' and sc.s_score<60
order by s_score desc;
正确输出：
+------+--------+------------+-------+---------+
| s_id | s_name | s_birth | s_sex | s_score |
+------+--------+------------+-------+---------+
| 04 | 李云 | 1990-08-06 | 男 | 50 |
| 06 | 吴兰 | 1992-03-01 | 女 | 31 |
+------+--------+------------+-------+---------+
☆13、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
Q:看到老师在群里说数据量大的时候不要出现太多join，但是又说应该减少子查询
所以我不太清楚究竟什么时候用join什么时候子查询？
select t3.*,round(s_avg,2) as s_avg from
(select t2.*,s3.s_score as 03_score from
(select t1.*,s2.s_score as 02_score from
(select s.*,s1.s_score as 01_score from
student as s left join score as s1 on s.s_id=s1.s_id and s1.c_id='01') as t1
left join score as s2 on t1.s_id=s2.s_id and s2.c_id='02') as t2
left join score as s3 on t2.s_id=s3.s_id and s3.c_id='03') as t3
left join (select s_id,avg(s_score)as s_avg from score group by s_id) as t4
on t4.s_id=t3.s_id order by s_avg desc;
正确输出：
+------+--------+------------+-------+----------+----------+----------+-------+
| s_id | s_name | s_birth | s_sex | 01_score | 02_score | 03_score | s_avg |
+------+--------+------------+-------+----------+----------+----------+-------+
| 07 | 郑竹 | 1989-07-01 | 女 | NULL | 89 | 98 | 93.50 |
| 01 | 赵雷 | 1990-01-01 | 男 | 80 | 90 | 99 | 89.67 |
| 05 | 周梅 | 1991-12-01 | 女 | 76 | 87 | NULL | 81.50 |
| 03 | 孙风 | 1990-05-20 | 男 | 80 | 80 | 80 | 80.00 |
| 02 | 钱电 | 1990-12-21 | 男 | 70 | 60 | 80 | 70.00 |
| 04 | 李云 | 1990-08-06 | 男 | 50 | 30 | 20 | 33.33 |
| 06 | 吴兰 | 1992-03-01 | 女 | 31 | NULL | 34 | 32.50 |
| 11 | 李四 | 2017-12-30 | 女 | NULL | NULL | NULL | NULL |
| 12 | 赵六 | 2017-01-01 | 女 | NULL | NULL | NULL | NULL |
| 13 | 孙七 | 2018-01-01 | 女 | NULL | NULL | NULL | NULL |
| 09 | 张三 | 2017-12-20 | 女 | NULL | NULL | NULL | NULL |
| 10 | 李四 | 2017-12-25 | 女 | NULL | NULL | NULL | NULL |
+------+--------+------------+-------+----------+----------+----------+-------+
14、查询各科成绩最高分、最低分和平均分： 以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select c.c_name,t.* from
(select c_id,
max(s_score) as max,
min(s_score) as min,
round(avg(s_score),2) as avg,
count(1) as num,
round(sum(if(s_score>=60,1,0))*100/count(1),2) as 'pass rate',
round(sum(if(s_score>=70,1,0))*100/count(1),2) as 'medium rate',
round(sum(if(s_score>=80,1,0))*100/count(1),2) as 'good rate',
round(sum(if(s_score>=90,1,0))*100/count(1),2) as 'excellent rate'
from score group by c_id) as t left join course as c on c.c_id=t.c_id
order by num desc,c_id asc;
正确输出：
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+
| c_name | c_id | max | min | avg | num | pass rate | medium rate | good rate | excellent rate |
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+
| 语文 | 01 | 80 | 31 | 64.50 | 6 | 66.67 | 66.67 | 33.33 | 0.00 |
| 数学 | 02 | 90 | 30 | 72.67 | 6 | 83.33 | 66.67 | 66.67 | 16.67 |
| 英语 | 03 | 99 | 20 | 68.50 | 6 | 66.67 | 66.67 | 66.67 | 33.33 |
+--------+------+------+------+-------+-----+-----------+-------------+-----------+----------------+
15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select *,rank() over (partition by c_id order by s_score) as 'rank' from score;
正确输出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06 | 01 | 31 | 1 |
| 04 | 01 | 50 | 2 |
| 02 | 01 | 70 | 3 |
| 05 | 01 | 76 | 4 |
| 01 | 01 | 80 | 5 |
| 03 | 01 | 80 | 5 |
| 04 | 02 | 30 | 1 |
| 02 | 02 | 60 | 2 |
| 03 | 02 | 80 | 3 |
| 05 | 02 | 87 | 4 |
| 07 | 02 | 89 | 5 |
| 01 | 02 | 90 | 6 |
| 04 | 03 | 20 | 1 |
| 06 | 03 | 34 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
| 07 | 03 | 98 | 5 |
| 01 | 03 | 99 | 6 |
+------+------+---------+------+
15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
select *,dense_rank() over (partition by c_id order by s_score) as 'rank' from score;
正确输出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06 | 01 | 31 | 1 |
| 04 | 01 | 50 | 2 |
| 02 | 01 | 70 | 3 |
| 05 | 01 | 76 | 4 |
| 01 | 01 | 80 | 5 |
| 03 | 01 | 80 | 5 |
| 04 | 02 | 30 | 1 |
| 02 | 02 | 60 | 2 |
| 03 | 02 | 80 | 3 |
| 05 | 02 | 87 | 4 |
| 07 | 02 | 89 | 5 |
| 01 | 02 | 90 | 6 |
| 04 | 03 | 20 | 1 |
| 06 | 03 | 34 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
| 07 | 03 | 98 | 4 |
| 01 | 03 | 99 | 5 |
+------+------+---------+------+
16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select s.*,t.total,t.rank from(select s_id,sum(s_score) as total,rank() over (order by sum(s_score) desc)as 'rank' from score group by s_id)as t right join student as s on s.s_id=t.s_id;
正确输出：
+------+--------+------------+-------+-------+------+
| s_id | s_name | s_birth | s_sex | total | rank |
+------+--------+------------+-------+-------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 269 | 1 |
| 02 | 钱电 | 1990-12-21 | 男 | 210 | 3 |
| 03 | 孙风 | 1990-05-20 | 男 | 240 | 2 |
| 04 | 李云 | 1990-08-06 | 男 | 100 | 6 |
| 05 | 周梅 | 1991-12-01 | 女 | 163 | 5 |
| 06 | 吴兰 | 1992-03-01 | 女 | 65 | 7 |
| 07 | 郑竹 | 1989-07-01 | 女 | 187 | 4 |
| 09 | 张三 | 2017-12-20 | 女 | NULL | NULL |
| 10 | 李四 | 2017-12-25 | 女 | NULL | NULL |
| 11 | 李四 | 2017-12-30 | 女 | NULL | NULL |
| 12 | 赵六 | 2017-01-01 | 女 | NULL | NULL |
| 13 | 孙七 | 2018-01-01 | 女 | NULL | NULL |
+------+--------+------------+-------+-------+------+
16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select s.*,t.total,t.rank from(select s_id,sum(s_score) as total,dense_rank() over (order by sum(s_score) desc)as 'rank' from score group by s_id)as t right join student as s on s.s_id=t.s_id;
正确输出：
+------+--------+------------+-------+-------+------+
| s_id | s_name | s_birth | s_sex | total | rank |
+------+--------+------------+-------+-------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 269 | 1 |
| 02 | 钱电 | 1990-12-21 | 男 | 210 | 3 |
| 03 | 孙风 | 1990-05-20 | 男 | 240 | 2 |
| 04 | 李云 | 1990-08-06 | 男 | 100 | 6 |
| 05 | 周梅 | 1991-12-01 | 女 | 163 | 5 |
| 06 | 吴兰 | 1992-03-01 | 女 | 65 | 7 |
| 07 | 郑竹 | 1989-07-01 | 女 | 187 | 4 |
| 09 | 张三 | 2017-12-20 | 女 | NULL | NULL |
| 10 | 李四 | 2017-12-25 | 女 | NULL | NULL |
| 11 | 李四 | 2017-12-30 | 女 | NULL | NULL |
| 12 | 赵六 | 2017-01-01 | 女 | NULL | NULL |
| 13 | 孙七 | 2018-01-01 | 女 | NULL | NULL |
+------+--------+------------+-------+-------+------+
17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
select c.c_name,t.* from(select c_id,
sum(if(s_score <60,1,0)) as '[0,60)',
round(sum(if(s_score <60,1,0))/count(1)*100,2) as '[0,60)%',
sum(if(s_score >=60 and s_score<70,1,0)) as '[60,70)',
round(sum(if(s_score >=60 and s_score<70,1,0))/count(1)*100,2) as '[60,70)%',
sum(if(s_score >=70 and s_score<85,1,0)) as '[70,85)',
round(sum(if(s_score >=70 and s_score<85,1,0))/count(1)*100,2) as '[70,85)%',
sum(if(s_score >=85 and s_score<=100,1,0)) as '[85,100]',
round(sum(if(s_score >=85 and s_score<=100,1,0))/count(1)*100,2) as '[85,100]%'
from score group by c_id) as t left join course as c on c.c_id=t.c_id;
正确输出：
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+
| c_name | c_id | [0,60) | [0,60)% | [60,70) | [60,70)% | [70,85) | [70,85)% | [85,100] | [85,100]% |
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+
| 语文 | 01 | 2 | 33.33 | 0 | 0.00 | 4 | 66.67 | 0 | 0.00 |
| 数学 | 02 | 1 | 16.67 | 1 | 16.67 | 1 | 16.67 | 3 | 50.00 |
| 英语 | 03 | 2 | 33.33 | 0 | 0.00 | 2 | 33.33 | 2 | 33.33 |
+--------+------+--------+---------+---------+----------+---------+----------+----------+-----------+
18.查询各科成绩前三名的记录
select * from (select *,rank() over (partition by c_id order by s_score) as 'rank' from score)as t where t.rank<=3;
正确输出：
+------+------+---------+------+
| s_id | c_id | s_score | rank |
+------+------+---------+------+
| 06 | 01 | 31 | 1 |
| 04 | 01 | 50 | 2 |
| 02 | 01 | 70 | 3 |
| 04 | 02 | 30 | 1 |
| 02 | 02 | 60 | 2 |
| 03 | 02 | 80 | 3 |
| 04 | 03 | 20 | 1 |
| 06 | 03 | 34 | 2 |
| 02 | 03 | 80 | 3 |
| 03 | 03 | 80 | 3 |
+------+------+---------+------+
19.查询每门课程被选修的学生数
select c_id,count(1) as num from score group by c_id;
正确输出：
+------+-----+
| c_id | num |
+------+-----+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-----+
20.查询出只选修两门课程的学生学号和姓名
select s.s_id,s.s_name from student s inner join score sc on s.s_id=sc.s_id group by s_id having count(1)=2；
正确输出：
+------+--------+
| s_id | s_name |
+------+--------+
| 05 | 周梅 |
| 06 | 吴兰 |
| 07 | 郑竹 |
+------+--------+
21.查询男生、女生人数
select s_sex,count(1)as num from student group by s_sex;
正确输出：
+-------+-----+
| s_sex | num |
+-------+-----+
| 男 | 4 |
| 女 | 8 |
+-------+-----+
☆22.查询名字中含有「风」字的学生信息
select * from student where s_name like '%风%';
如果s_name是索引字段用%风%会导致索引失效，最好用风%
Q:那请问老师这种情况的查询就要先搜索%风再搜索风%最后合并还是怎样？
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 03 | 孙风 | 1990-05-20 | 男 |
+------+--------+------------+-------+
23.查询同名同性学生名单，并统计同名人数
select s_name,s_sex,count(1)as num from student group by s_name,s_sex having num>1;
正确输出：
+--------+-------+-----+
| s_name | s_sex | num |
+--------+-------+-----+
| 李四 | 女 | 2 |
+--------+-------+-----+
24.查询 1990 年出生的学生名单
select * from student where year(s_birth)=1990;
如果s_birth是索引的话where字段用函数会导致索引失效，因此最好用where s_birth like "1990%"
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+------------+-------+
25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c.c_id,c.c_name,round(avg(s_score),2) as avg from course c inner join score sc on sc.c_id=c.c_id group by c_id order by avg desc,c_id asc;
正确输出：
+--------+------+-------+
| c_name | c_id | avg |
+--------+------+-------+
| 数学 | 02 | 72.67 |
| 英语 | 03 | 68.50 |
| 语文 | 01 | 64.50 |
+--------+------+-------+
26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select s.s_id,s.s_name,round(avg(s_score),2) as avg from student s inner join score sc on sc.s_id=s.s_id group by s_id having avg(s_score)>=85;
正确输出：
+--------+------+-------+
| s_name | s_id | avg |
+--------+------+-------+
| 赵雷 | 01 | 89.67 |
| 郑竹 | 07 | 93.50 |
+--------+------+-------+、
27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select s.s_name,sc.* from student as s inner join score as sc on sc.s_id=s.s_id inner join course c on c.c_id=sc.c_id where s_score<60 and c_name="数学";
正确输出：
+--------+------+------+---------+
| s_name | s_id | c_id | s_score |
+--------+------+------+---------+
| 李云 | 04 | 02 | 30 |
+--------+------+------+---------+
28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select s.*,sc.c_id,sc.s_score from student s left join score sc on s.s_id=sc.s_id;
正确输出：
+------+--------+------------+-------+------+---------+
| s_id | s_name | s_birth | s_sex | c_id | s_score |
+------+--------+------------+-------+------+---------+
| 01 | 赵雷 | 1990-01-01 | 男 | 01 | 80 |
| 01 | 赵雷 | 1990-01-01 | 男 | 02 | 90 |
| 01 | 赵雷 | 1990-01-01 | 男 | 03 | 99 |
| 02 | 钱电 | 1990-12-21 | 男 | 01 | 70 |
| 02 | 钱电 | 1990-12-21 | 男 | 02 | 60 |
| 02 | 钱电 | 1990-12-21 | 男 | 03 | 80 |
| 03 | 孙风 | 1990-05-20 | 男 | 01 | 80 |
| 03 | 孙风 | 1990-05-20 | 男 | 02 | 80 |
| 03 | 孙风 | 1990-05-20 | 男 | 03 | 80 |
| 04 | 李云 | 1990-08-06 | 男 | 01 | 50 |
| 04 | 李云 | 1990-08-06 | 男 | 02 | 30 |
| 04 | 李云 | 1990-08-06 | 男 | 03 | 20 |
| 05 | 周梅 | 1991-12-01 | 女 | 01 | 76 |
| 05 | 周梅 | 1991-12-01 | 女 | 02 | 87 |
| 06 | 吴兰 | 1992-03-01 | 女 | 01 | 31 |
| 06 | 吴兰 | 1992-03-01 | 女 | 03 | 34 |
| 07 | 郑竹 | 1989-07-01 | 女 | 02 | 89 |
| 07 | 郑竹 | 1989-07-01 | 女 | 03 | 98 |
| 09 | 张三 | 2017-12-20 | 女 | NULL | NULL |
| 10 | 李四 | 2017-12-25 | 女 | NULL | NULL |
| 11 | 李四 | 2017-12-30 | 女 | NULL | NULL |
| 12 | 赵六 | 2017-01-01 | 女 | NULL | NULL |
| 13 | 孙七 | 2018-01-01 | 女 | NULL | NULL |
+------+--------+------------+-------+------+---------+
29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s.s_name,sc.* from student s left join score sc on s.s_id=sc.s_id where s_score>70;
正确输出：
+--------+------+------+---------+
| s_name | s_id | c_id | s_score |
+--------+------+------+---------+
| 赵雷 | 01 | 01 | 80 |
| 赵雷 | 01 | 02 | 90 |
| 赵雷 | 01 | 03 | 99 |
| 钱电 | 02 | 03 | 80 |
| 孙风 | 03 | 01 | 80 |
| 孙风 | 03 | 02 | 80 |
| 孙风 | 03 | 03 | 80 |
| 周梅 | 05 | 01 | 76 |
| 周梅 | 05 | 02 | 87 |
| 郑竹 | 07 | 02 | 89 |
| 郑竹 | 07 | 03 | 98 |
+--------+------+------+---------+
30.查询不及格的课程
select score.* from score where s_score<60;
正确输出：
+------+------+---------+
| s_id | c_id | s_score |
+------+------+---------+
| 04 | 01 | 50 |
| 04 | 02 | 30 |
| 04 | 03 | 20 |
| 06 | 01 | 31 |
| 06 | 03 | 34 |
+------+------+---------+
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s.s_name,sc.* from student s inner join score sc on s.s_id=sc.s_id where c_id="01" and s_score>80;
正确输出：
Empty set
32.求每门课程的学生人数
select c_id,count(1) as num from score group by c_id;
正确输出：
+------+-----+
| c_id | num |
+------+-----+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-----+
☆33.假设成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,sc.c_id,sc.s_score from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="张三") order by s_score desc limit 1;
正确输出：
+------+--------+------------+-------+------+---------+
| s_id | s_name | s_birth | s_sex | c_id | s_score |
+------+--------+------------+-------+------+---------+
| 01 | 赵雷 | 1990-01-01 | 男 | 02 | 90 |
+------+--------+------------+-------+------+---------+
和27题相似的问题，因为27题加入一个子查询比较快，所以我试图在33题中也加入一个，相比直接 inner join确实快了
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 47 | 0.00080375 | select s.*,sc.c_id,sc.s_score from score sc inner join student s on s.s_id=sc.s_id inner join course c on sc.c_id=c.c_id inner join teacher t on c.t_id=t.t_id and t.t_name='????' order by sc.s_score desc limit 1 |
| 48 | 0.00072225 | select s.*,sc.c_id,sc.s_score from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="????") order by s_score desc limit 1 |
+----------+------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
子查询explain:
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| 1 | PRIMARY | sc | NULL | ALL | PRIMARY | NULL | NULL | NULL | 18 | 10.00 | Using where; Using filesort |
| 1 | PRIMARY | s | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.sc.s_id | 1 | 100.00 | NULL |
| 2 | SUBQUERY | t | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where |
| 2 | SUBQUERY | c | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
join explain:
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
| 1 | SIMPLE | t | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | sc | NULL | ALL | PRIMARY | NULL | NULL | NULL | 18 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | s | NULL | eq_ref | PRIMARY | PRIMARY | 62 | test.sc.s_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------------------------------------------+
34.假设成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select * from(select s.*,sc.c_id,sc.s_score,rank() over (order by s_score desc) as 'ran' from student s inner join score sc on s.s_id=sc.s_id and c_id=(select c_id from teacher t inner join course c on t.t_id=c.t_id and t.t_name="张三"))as t where ran=1;
正确输出：
+------+--------+------------+-------+------+---------+-----+
| s_id | s_name | s_birth | s_sex | c_id | s_score | ran |
+------+--------+------------+-------+------+---------+-----+
| 01 | 赵雷 | 1990-01-01 | 男 | 02 | 90 | 1 |
+------+--------+------------+-------+------+---------+-----+
35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select * from score sc inner join score sc2 on sc.s_id=sc2.s_id where sc.c_id!=sc2.c_id and sc.s_score=sc2.s_score group by sc.s_id ,sc.c_id;
正确输出：
+------+------+---------+------+------+---------+
| s_id | c_id | s_score | s_id | c_id | s_score |
+------+------+---------+------+------+---------+
| 03 | 01 | 80 | 03 | 02 | 80 |
| 03 | 02 | 80 | 03 | 01 | 80 |
| 03 | 03 | 80 | 03 | 01 | 80 |
+------+------+---------+------+------+---------+
36.查询每门功成绩最好的前两名
select * from (select s_id,s_score,c_id,rank() over (partition by c_id order by s_score desc) as ran from score)as t where ran<=2 ;
正确输出：
+------+---------+------+-----+
| s_id | s_score | c_id | ran |
+------+---------+------+-----+
| 01 | 80 | 01 | 1 |
| 03 | 80 | 01 | 1 |
| 01 | 90 | 02 | 1 |
| 07 | 89 | 02 | 2 |
| 01 | 99 | 03 | 1 |
| 07 | 98 | 03 | 2 |
+------+---------+------+-----+
37.统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select c_id,count(1) as num from score group by c_id having num>5;
正确输出：
+------+-----+
| c_id | num |
+------+-----+
| 01 | 6 |
| 02 | 6 |
| 03 | 6 |
+------+-----+
38.检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(1)>=2;
正确输出：
+------+
| s_id |
+------+
| 01 |
| 02 |
| 03 |
| 04 |
| 05 |
| 06 |
| 07 |
+------+
39.查询选修了全部课程的学生信息
select s.* from student s inner join (select s_id,count(1)as num from score group by s_id) as t on s.s_id=t.s_id where num=(select count(1) from course);
#如果用having+子查询效率非常低
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 02 | 钱电 | 1990-12-21 | 男 |
| 03 | 孙风 | 1990-05-20 | 男 |
| 04 | 李云 | 1990-08-06 | 男 |
+------+--------+------------+-------+
40.查询各学生的年龄，只按年份来算
select *,year(now())-year(s_birth)as age from student;
正确输出：
+------+--------+------------+-------+------+
| s_id | s_name | s_birth | s_sex | age |
+------+--------+------------+-------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 30 |
| 02 | 钱电 | 1990-12-21 | 男 | 30 |
| 03 | 孙风 | 1990-05-20 | 男 | 30 |
| 04 | 李云 | 1990-08-06 | 男 | 30 |
| 05 | 周梅 | 1991-12-01 | 女 | 29 |
| 06 | 吴兰 | 1992-03-01 | 女 | 28 |
| 07 | 郑竹 | 1989-07-01 | 女 | 31 |
| 09 | 张三 | 2017-12-20 | 女 | 3 |
| 10 | 李四 | 2017-12-25 | 女 | 3 |
| 11 | 李四 | 2017-12-30 | 女 | 3 |
| 12 | 赵六 | 2017-01-01 | 女 | 3 |
| 13 | 孙七 | 2018-01-01 | 女 | 2 |
+------+--------+------------+-------+------+
41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select *,timestampdiff(year,s_birth,now()) as age from student;
正确输出：
+------+--------+------------+-------+------+
| s_id | s_name | s_birth | s_sex | age |
+------+--------+------------+-------+------+
| 01 | 赵雷 | 1990-01-01 | 男 | 30 |
| 02 | 钱电 | 1990-12-21 | 男 | 29 |
| 03 | 孙风 | 1990-05-20 | 男 | 29 |
| 04 | 李云 | 1990-08-06 | 男 | 29 |
| 05 | 周梅 | 1991-12-01 | 女 | 28 |
| 06 | 吴兰 | 1992-03-01 | 女 | 27 |
| 07 | 郑竹 | 1989-07-01 | 女 | 30 |
| 09 | 张三 | 2017-12-20 | 女 | 2 |
| 10 | 李四 | 2017-12-25 | 女 | 2 |
| 11 | 李四 | 2017-12-30 | 女 | 2 |
| 12 | 赵六 | 2017-01-01 | 女 | 3 |
| 13 | 孙七 | 2018-01-01 | 女 | 2 |
+------+--------+------------+-------+------+
☆42.查询本周过生日的学生
Q：这种方法好像也会让索引失效，不知道有没有更好的方法？
select * from student where weekofyear(date_format(s_birth,"2020-%m-%d"))=weekofyear(now());
Empty set (0.00 sec)
43.查询下周过生日的学生
select * from student where weekofyear(date_format(s_birth,"2020-%m-%d"))=weekofyear(now())+1;
Empty set (0.00 sec)
44.查询本月过生日的学生
select * from student where s_birth like concat("____-0",month(now()),"%");
正确输出：
+------+--------+------------+-------+
| s_id | s_name | s_birth | s_sex |
+------+--------+------------+-------+
| 01 | 赵雷 | 1990-01-01 | 男 |
| 12 | 赵六 | 2017-01-01 | 女 |
| 13 | 孙七 | 2018-01-01 | 女 |
+------+--------+------------+-------+
45.查询下月过生日的学生
select * from student where s_birth like concat("____-0",month(now())+1,"%");
Empty set (0.00 sec)
