前言

本篇跟着知乎文章SQL面试必会50题 - 知乎的顺序记录SQL刷题过程。

参考资料：

在每道题的标题后面会有一些符号：

  • 无任何符号：没有思路 or 解题过程中参考了一些题解思路
  • ✔：Acess！
  • ❗：在编写代码的过程中遇到一些语法上的问题，或者是一些巧妙的解题思路，需要留意
  • 每题的题号后会增加重点或超级重点的标识，需要特别留意

题目数据依赖

本节将详细说明刷题所依赖的数据内容。

表结构和关联关系

题目所依赖的表结构和关系如上图所示👆

总共包含四张表：学生表、成绩表、课程表、教师表。

四张表的关联字段：

  • 学生表（Student） - 成绩表（Score）：Student.s_id = Score.s_id【通过学号关联】
  • 成绩表（Score） - 课程表（Course）：Score.c_id = Course.c_id【通过课程编号关联】
  • 课程表（Course） - 教师表（Teacher）：Course.t_id = Teacher.t_id【通过教师号关联】

建表和测试数据插入语句

建库

数据库属性如下：

  • 数据库名：50_SQL_Practice_Exercises
  • 字符集：utf8
  • 排序规则：utf8_unicode_ci

建表

学生表：

1
2
3
4
5
6
7
8
-- 学生表
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`)
);

课程表：

1
2
3
4
5
6
7
-- 课程表
CREATE TABLE `Course`(
`c_id` VARCHAR(20),
`c_name` VARCHAR(20) NOT NULL DEFAULT '',
`t_id` VARCHAR(20) NOT NULL,
PRIMARY KEY(`c_id`)
);

教师表：

1
2
3
4
5
6
-- 教师表
CREATE TABLE `Teacher`(
`t_id` VARCHAR(20),
`t_name` VARCHAR(20) NOT NULL DEFAULT '',
PRIMARY KEY(`t_id`)
);

成绩表：

1
2
3
4
5
6
7
-- 成绩表
CREATE TABLE `Score`(
`s_id` VARCHAR(20),
`c_id` VARCHAR(20),
`s_score` INT(3),
PRIMARY KEY(`s_id`,`c_id`)
);

测试数据

学生表测试数据：

1
2
3
4
5
6
7
8
9
-- 学生表测试数据
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' , '女');

课程表测试数据：

1
2
3
4
-- 课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

教师表测试数据：

1
2
3
4
-- 教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

成绩表测试数据：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 成绩表测试数据
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”的课程比“02”的课程成绩高的所有学生的学号

题解：

1
2
3
4
5
6
7
SELECT table_A.s_id FROM
	(SELECT s_id, c_id, s_score FROM score WHERE `c_id` = '01') AS table_A
	INNER JOIN
	(SELECT s_id, c_id, s_score FROM score WHERE `c_id` = '02') AS table_B
	ON table_A.s_id = table_B.s_id
	WHERE
		table_A.s_score > table_B.s_score;

思路：构建一个临时表，表中包含学生学号、01课程成绩、02课程成绩，通过在此表中创建简单的查询语句就可以实现

  1. 首先在成绩表中拿到01课程的信息：SELECT s_id, c_id, s_score FROM score WHERE c_id = '01'
  2. 然后在成绩表中拿到02课程的信息：SELECT s_id, c_id, s_score FROM score WHERE c_id = '02'
  3. 通过INNER JOIN将两个表做内连接，连接字段为学生学号
  4. 接下来就是简单查询的步骤，查询字段为s_id，查询条件为：table_A.s_score > table_B.s_score

第二题【重点】✔❗

题目描述：查询平均成绩大于60分的学生的学号和平均成绩

题解：

1
2
3
SELECT s_id, AVG(s_score) AS avg_score FROM score 
	GROUP BY s_id
	HAVING avg_score > 60;

主要考察GROUP BY的使用

注意❗：一般在查找字段中使用了函数，如果该字段在过滤条件中也使用到了，那么通常要给该字段增加别名，否则在过滤条件中又使用函数，会使得查找效率降低。

第三题❗

题目描述：查询所有学生的学号、姓名、选课数、总成绩

题解：

1
2
3
4
5
6
7
SELECT table_A.s_id, table_A.s_name, COUNT(table_B.c_id), SUM(CASE WHEN table_B.s_score IS NULL THEN 0 ELSE table_B.s_score END) 
	FROM
		(SELECT s_id, s_name FROM student) AS table_A
		LEFT JOIN
		(SELECT s_id, c_id, s_score FROM score) AS table_B
		ON table_A.s_id = table_B.s_id
		GROUP BY table_A.s_id, table_A.s_name;

思路：构建一个临时表，表中包含学生学号、学生姓名、课程号、课程成绩，通过在此表中创建简单的查询语句就可以实现

注意❗：

  1. table_A（学生表）和table_B（成绩表）需要使用左连接LEFT JOIN而非内连接INNER JOIN，因为有可能学生没有任何成绩，那么通过内连接查询的话不会显示该学生。
  2. 为了更严谨，GROUP BY中的字段应该是在查询字段中出现的，否则有可能报错，所以这里GROUP BY同时使用table_A.s_id和table_A.s_name，其效果和仅使用其中一个字段是一样的。
  3. 如果学生没有成绩的话，则直接在查询中使用SUM(table_B.s_score)会出现成绩为Null的情况，为了避免，可以使用条件语句设置为0。

第四题✔

题目描述：查询姓“猴”的老师的个数

题解：

1
SELECT COUNT(*) FROM teacher WHERE t_name LIKE '猴%';

简单查询

第五题【重点】❗

题目描述：查询没学过“张三”老师课的学生的学号、姓名

题解：

1
2
3
4
SELECT s_id, s_name FROM student
	WHERE s_id NOT IN(SELECT s_id FROM score 
		WHERE c_id = (SELECT c_id FROM course 
			WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')))

思路：一步一步执行子查询

  1. 先从教师表（teacher）中找到姓名为“张三”老师的教师号t_id
  2. 再从课程表（course）中找到“张三”老师教的课的课程编号c_id
  3. 接下来从成绩表（score ）中找到学过“张三”老师教的课的学生的学号s_id
  4. 最后从全体学生中排除学过该课的学生

注意❗：

正确的思路是先拿到学过“张三”老师课的学生，然后从全体学生中排除（针对此题的情况），因为判断学生是否学过课程是根据成绩表查询的，如果对应课程有成绩，则说明学生学过课程。但是不可以使用SELECT * FROM score WHERE c_id != '指定课程号' ，因为一个学生可能学过好多们课，那这种查询方式只能把学过指定课程号的记录给删除，该学生的其他记录还在，没有办法起到筛选的作用。

第六题【重点】✔

题目描述：查询学过“张三”老师所教的所有课的同学的学号、姓名

题解一（嵌套子查询）：

1
2
3
4
SELECT s_id, s_name FROM student
	WHERE s_id in (SELECT s_id FROM score 
		WHERE c_id = (SELECT c_id FROM course 
			WHERE t_id = (SELECT t_id FROM teacher WHERE t_name = '张三')));

思路：可以按照第五题的思路逐步写嵌套子查询：

  1. 先从教师表（teacher）中找到姓名为“张三”老师的教师号t_id
  2. 再从课程表（course）中找到“张三”老师教的课的课程编号c_id
  3. 接下来从成绩表（score ）中找到学过“张三”老师教的课的学生的学号s_id
  4. 最后全体学生中查找学过该课的学生

题解二（表连接查询）：

1
2
3
4
5
6
SELECT student.s_id, student.s_name 
	FROM student
		INNER JOIN score ON student.s_id = score.s_id
		INNER JOIN course ON score.c_id = course.c_id
		INNER JOIN teacher ON course.t_id = teacher.t_id
		WHERE teacher.t_name = '张三';

思路：可以将嵌套子查询的语句更改为表连接查询。

第七题【重点】❗

题目描述：查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名

题解：

1
2
3
4
5
6
SELECT s_id, s_name FROM student
	WHERE s_id IN (SELECT table_A.s_id FROM
		(SELECT s_id FROM score WHERE c_id = '01') AS table_A
		INNER JOIN
		(SELECT s_id FROM score WHERE c_id = '02') AS table_B
		ON table_A.s_id = table_B.s_id);

思路：先查找学过“01”课程的学生编号，再查找学过“02”课程的学生编号，将两个表做内连接得到“临时编号集”，最后从学生表中查询在“临时编号集”中的学生。

注意❗：

切记不可以这样写：SELECT s_id FROM score WHERE c_id = '01' AND c_id = '02';

解释：一个字段不可能存在c_id既为01也为02的情况。

第八题✔

题目描述：查询课程编号为“02”的总成绩

题解：

1
SELECT SUM(s_score) FROM score WHERE c_id = '02';

简单查询

第九题❗

题目描述：查询所有课程成绩小于60分的学生的学号、姓名

题解：

1
2
3
4
5
6
7
8
9
10
11
SELECT student.s_id, student.s_name FROM
		(SELECT s_id, COUNT(c_id) AS cnt FROM score
		WHERE s_score < 60
		GROUP BY s_id) AS table_A
	INNER JOIN
		(SELECT s_id, COUNT(c_id) AS cnt FROM score
		GROUP BY s_id) AS table_B
	ON table_A.s_id = table_B.s_id
	INNER JOIN student
	ON  student.s_id = table_A.s_id
	WHERE table_A.cnt = table_B.cnt

思路：

  1. 首先从成绩表（score ）中查找所有成绩小于60分的学号s_id，以及小于60分的课程数量，通过GROUP BY s_id返回结果
  2. 再从成绩表（score ）中查找每一位同学选过的课程的数量，通过GROUP BY s_id返回结果
  3. 对比两个课程数是否一致，通过WHERE条件判断

注意❗：

另外一种思路不可取：先找到所选课程最高分大于等于60分的所有同学的集合，再从学生表中排除（NOT IN）这个该集合，剩下的就是要查询的结果。

为什么不可取？因为有同学没有选择任何课程（即成绩表（score ）中没有该同学的信息），那么这种方式会筛选出这样的同学，这并非预期结果。

第十题【重点】❗

题目描述：查询没有学全所有课的学生的学号、姓名

题解：

1
2
3
4
5
SELECT table_A.s_id, table_A.s_name FROM student AS table_A
	LEFT JOIN score AS table_B
		ON table_A.s_id = table_B.s_id
	GROUP BY table_A.s_id
		HAVING COUNT(table_A.s_id) < (SELECT COUNT(c_id) FROM course)

思路：

  1. 利用左外连接（LEFT JOIN）连接学生表（student）和成绩表（score）
  2. 按照s_id分组显示，并且统计s_id的数量，使其小于课程总数

注意❗：如果按照去成绩表（score）中查询每位同学的课程数量，然后查找条件为小于课程总数。这样会丢掉没有任何课程成绩的同学

第十一题【重点】✔

题目描述：查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名

题解：

1
2
3
4
5
6
SELECT table_A.s_id, table_A.s_name FROM student AS table_A
	INNER JOIN score AS table_B 
		ON table_A.s_id = table_B.s_id
	WHERE table_B.c_id IN (SELECT c_id FROM score WHERE s_id = "01")
   		AND table_A.s_id != "01"
 GROUP BY table_A.s_id

思路：

  1. 先查询s_id为“01”同学的课程c_id
  2. 用IN去找至少有一门课在目标课程的同学

注意：记得删除“01”同学本身

后记

To be continued…