前言

本篇跟着知乎文章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 != '指定课程号' ，因为一个学生可能学过好多们课，那这种查询方式只能把学过指定课程号的记录给删除，该学生的其他记录还在，没有办法起到筛选的作用。

后记

To be continued…