小白学 SQL 第七天：多表查询

L罗乐 2019-04-14

展开全文

之前学习的查询都只涉及到单表，但在实际应用中还有很大一部分需求，只靠单表查询是实现不了的，比如：查询一个班上的所有人；统计每个班的人数等。那这些需求或问题应该怎么分析和实现，这就是今天要讲解的多表查询。

知识要点：

子查询
表连接

多表查询有两种形式：一种是子查询，一种表连接。那什么时候使用子查询？什么时候使用表连接呢？这里说一下我个人的分析和使用步骤

1. 如果所有需要获得信息列在一张表里可以找到，就用子查询；
2. 其他情况肯定需要表连接；
3. 如果数据量很大，先子查询再使用表连接
4. 使用性能工具分析 SQL 进行优化（执行计划和执行 profile）

子查询

子查询可以使用在 select 语句的 2 个地方，一个是在 from 子句中，一个是在 where 子句中，在使用的时候需要放在哪里就看是要从这个子句结果中返回信息，还是这个子句结果只是作为过滤条件。我们通过 2 个 sql 具体分析

查询参与班级 id 为 1或2 的所有学生信息

分析：查询参与班级 id 为 1和2 的所有学生信息
操作类型：select （查询）
到哪里取数据：学生
得到哪些信息：所有学生信息
过滤条件：班级 id 为 1 或 2
排序字段：无
取多少数据：所有数据（无 limit）
我们将这些信息套入到 SELECT 语句结构会得到如下：
select * from 学生
where 班级 id 为 1 或 2 ;
这里学生表里面是没有班级信息的，并且返回的信息只有学生信息，班级 id 只是作为过滤条件，所以这里能确定 where 条件为一个子查询。那这个子查询应该如何分析呢？我们回到最开始的 ER 图可以发现和学生信息有直接联系的是参加这个关系，同时它还和班级信息最近（这里是直接联系），就从参加这个关系入手。由于在转换成数据库物理存储的时候，参加这个关系我们也是在一种独立的数据表中存储的，所以我们首先看这个表的实际物理存储
1
2
3
4
5
6
CREATE TABLE student_join_class (
c_id int(11) NOT NULL COMMENT '班级 id',
s_id int(11) NOT NULL COMMENT '学生 id',
cs_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '加入时间',
PRIMARY KEY (c_id,s_id)
)  COMMENT='学生班级关联表';
发现这个表里面已经了班级 id c_id ，同时还包含学生 id s_id ，这样就可以可以做到从 c_id -> s_id 的转换，在我们知道了 s_id 的情况下，上面分析的 sql 结构可以表示成，这里用 in 是由于通过 student_join_class 得到的 s_id 有可能是多个
select * from 学生
where s_id in (参加班级 1 或 2 的学生 id);
通过上面的分析知道：可以通过 student_join_class 表数据，用已知的班级 id c_id 数据查询出参与班级的学生 id s_id ，这个 sql 通过之前的单表分析方式，不能得到查询语句：
1
2
select s_id from student_join_class
where c_id in (1,2)
将此语句放入分析得到的 sql 语句结构，就得到最终的查询 sql 语句。

通过上面的分析，得到如下 sql 语句

select * from student
where s_id in (
select s_id from student_join_class
where c_id in (1,2)
);

执行得到如下结果

查询参与人数大于 3 的班级个数

分析：查询参与人数大于 3 的班级个数
操作类型：select （查询）
到哪里取数据：未知（由于没有任何一张物理表有班级参与人数信息）
得到哪些信息：班级个数
过滤条件：班级参与人数大于 3
排序字段：无
取多少数据：所有数据（无 limit）
通过上面分析得到如下查询结构
select count(*) from 未知
where 班级参与人数大于 3;
这里如果我们将未知理解成一张表的话，问题就转换成：分析未知表里面应该包含哪些信息？。其实从查询结构不难分析未知表只需要包含每个班级的参与人数就够了。因为返回的信息只是统计班级的数量(这里统计之关系有这条数据，不关心数据里的具体内容），但是过滤条件需要使用班级的参与人数进行比较，所以这里 未知表 的 最小信息就是班级参与人数 。现在问题转变成
查询每个班级的参与人数 (未知表)
每个班级有哪些人参与的信息，全部都在关联表 student_join_class 里面，所以通过统计不难得出每个班级的参与人数 sql
1
2
select c_id, count(*) from student_join_class
group by c_id
这里所有我们需要的条件都完成了，组合得到的查询结构和 sql 就行。

通过上面的分析得到如下是 SQL。注意：这里在组合的时候对 count(*) 使用了别名 (as 关键字)，这是由于需要在外部查询中使用结果值时，是不能直接用count（*）作为其列名称。其实所有的函数操作都不行。

使用建议：对查询返回中使用的所有函数列操作都定义别名

select count(*) from (
select c_id, count(*) as number from student_join_class
group by c_id
) a
where number > 3;

执行的结果为：3 。

在分析的时候将 未知表 理解成一张表，这里数据库实际执行的时候确实会生成一张表，称作临时表。数据库在执行 sql 的时候，会为每一个子查询生成一个临时表（没有执行优化的情况），看数据库是否为一个 sql 查询生成临时表可以通过查询 SQL 执行计划来判断。
查询 SQL 执行计划使用 explain 关键字，执行以下 sql 查看结果输出
1
2
3
4
5
6
explain
select count(*) from (
select c_id, count(*) as number from student_join_class
group by c_id
) a
where number > 3;

表连接

为了让大家更好的理解表连接，这里先讲解一下表连接的基本知识。这里假设我们有 A、B 两张表，表中的数据数量分别是 m 和 n。我们现在分别创建 A、B 两张表，并且录入一些数据

-- ----------------------------
-- Table structure for A
-- ----------------------------
CREATE TABLE `A` (
`a_id` int(11) NOT NULL
);
-- ----------------------------
-- Records of A
-- ----------------------------
BEGIN;
INSERT INTO `A` VALUES (1),(2),(3),(4),(4),(5),(5);
COMMIT;
-- ----------------------------
-- Table structure for B
-- ----------------------------
CREATE TABLE `B` (
`b_id` int(11) NOT NULL
);
-- ----------------------------
-- Records of B
-- ----------------------------
BEGIN;
INSERT INTO `B` VALUES (2),(3),(5),(7),(4),(4),(4);
COMMIT;

笛卡尔积

我们来看第一个基本概念：笛卡尔积（也叫全连接）。在数学中笛卡尔积的定义是
两个集合 X 和 Y 的笛卡儿积（Cartesian product），又称直积，表示为 X × *Y*，是其第一个对象是 X的成员而第二个对象是 Y 的一个成员的所有可能的有序对。

这个难以理解的定义在 SQL 里面怎么理解呢？我们链接 A、B 两个表的时候，不加入任何条件，让 A 表中的每一条数据都和 B 表中的每一条数据做关联，就是 A 和 B 的笛卡尔积，也叫全连接。具体 SQL 实现如下：

select * from A,B;
select * from A join B;
select * from A cross join B;

会发现上面的 3 个 sql 语句执行的结果是一样的。我们再看这 3 中链接形式过后的数据量

select count(*) from A,B;
select count(*) from A join B;
select count(*) from A cross join B;

我们会发现 2 点

全连接后的数据量是 m × n
全连接后的绝大部分数据是没有意义的

我们可以试想一下如果每个表的数据量都在 100w 这个数量级（在真实生产环境这个数据量是小的），那全连接过后的数据量将是 1亿数量级，里面的绝大多数数据是没有意义的，并且这里才考虑了 2 个表，这个很可怕的。所以在使用表连接时尽量避免产生全连接。

普通链接

既然上面说到使用 全连接后的绝大部分数据是没有意义的 ，那我们怎么产生有意义的链接呢？其实也很简单，我们只需要在链接的时候加上有意义的链接条件就行。以下 3 个语句其实是一样的，只是表现形式不同

select * from A,B where a_id=b_id;
select * from A join B on a_id=b_id;
select * from A join B where a_id=b_id;

大家可以发现这里出现的结果其实都在上面的 笛卡尔积 中，只是从 笛卡尔积 中按照 a_id=b_id 的过滤条件选出了少部分数据，这里的 a_id=b_id 就是有意义的条件。在解决实际的问题的时候，什么是有意义的条件要根据你的问题和设计仔细分析。

明白了上面的笛卡尔积和有意义的条件，我们来看一下最常用的 3 中连接方式：内连接、左连接、右连接。再具体说明之前，先看一下示意图。

这个示意图和大家在网上使用集合表示的示意图有些不同，主要是我感觉使用集合的示意图不能完全诠释 左连接 和 右连接 的概念和数据量，所以这里根据我自己的理解画了这个示意图。

A表数据量 m，B 表数据量 n
A、B 的数据量 m x n，这个笛卡尔积后的结果集和原来的 A、B 没有任何关系
在笛卡尔积中满足过滤条件的数据(a, b)，其中 a 是 A表满足过滤条件的数量，b 是 B 表满足过滤条件的数量。但是注意满足条件的数据量并不是 a x b
A表中还有 m-a 条数据不满足过滤条件，B 表中还有 n-b 条数据不满足过滤条件