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

知识要点:

  • 子查询
  • 表连接

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

1. 如果所有需要获得信息列在一张表里可以找到,就用子查询;

2. 其他情况肯定需要表连接;

3. 如果数据量很大,先子查询再使用表连接

4. 使用性能工具分析 SQL 进行优化(执行计划和执行 profile)

子查询

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

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

分析:查询参与班级 id 为 1和2 的所有学生信息

  1. 操作类型:select (查询)
  2. 到哪里取数据:学生
  3. 得到哪些信息:所有学生信息
  4. 过滤条件:班级 id 为 1 或 2
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

我们将这些信息套入到 SELECT 语句结构会得到如下:

select * from 学生

where 班级 id 为 1 或 2 ;

这里学生表里面是没有班级信息的,并且返回的信息只有学生信息,班级 id 只是作为过滤条件,所以这里能确定 where 条件为一个子查询。那这个子查询应该如何分析呢?我们回到最开始的 ER 图

可以发现和学生信息有直接联系的是 参加 这个关系,同时它还和班级信息 最近 (这里是直接联系),就从 参加 这个关系入手。由于在转换成数据库物理存储的时候,参加 这个关系我们也是在一种独立的数据表中存储的,所以我们首先看这个表的实际物理存储

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 通过之前的单表分析方式,不能得到查询语句:

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 的班级个数

  1. 操作类型:select (查询)
  2. 到哪里取数据:未知 (由于没有任何一张物理表有班级参与人数信息)
  3. 得到哪些信息:班级个数
  4. 过滤条件:班级参与人数大于 3
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

通过上面分析得到如下查询结构

select count(*) from 未知

where 班级参与人数大于 3;

这里如果我们将 未知 理解成一张表的话,问题就转换成:分析 未知表 里面应该包含哪些信息?。其实从查询结构不难分析 未知表 只需要包含每个班级的参与人数就够了。因为返回的信息只是统计班级的数量(这里统计之关系有这条数据,不关心数据里的具体内容),但是过滤条件需要使用班级的参与人数进行比较,所以这里 未知表最小信息就是 班级参与人数 。现在问题转变成

查询每个班级的参与人数 (未知表)

每个班级有哪些人参与的信息,全部都在关联表 student_join_class 里面,所以通过统计不难得出每个班级的参与人数 sql

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 查看结果输出

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;

笛卡尔积

我们来看第一个基本概念:笛卡尔积(也叫全连接)。在数学中笛卡尔积的定义是

两个集合 XY笛卡儿积(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 点

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

我们可以试想一下如果每个表的数据量都在 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 中连接方式:内连接、左连接、右连接。再具体说明之前,先看一下示意图。

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

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

内连接

内连接使用 inner join 子句,表示笛卡尔积中满足过滤条件的数据,也是我们在有过滤条件时的默认方式。

select * from A inner join B on a_id=b_id;

这其实和普通连接里的 3 个语句是一样的。

左连接

内连接使用 left join 子句,表示笛卡尔积中满足过滤条件的数据,在合并左表中不满足过滤条件的数据。

select * from A left join B on a_id=b_id;

这里就是表示返回图中满足过滤条件的 a x b不满足过滤条件的 A(m-a)

右连接

内连接使用 left join 子句,表示笛卡尔积中满足过滤条件的数据,在合并右表中不满足过滤条件的数据。

select * from A right join B on a_id=b_id;

这里就是表示返回图中满足过滤条件的 (a , b)不满足过滤条件的 B(n-b)

在分析任何链接查询数据量的时候,都可以简单的把查询过程抽象成 3 个逻辑步骤:

  1. 现将需要链接的表(实体表或临时表)做笛卡尔积
  2. 根据过滤条件删选需要的数据
  3. 根据不同查询方式合并剩余数据

注意:这个抽象过程只针对数据量分析,并不是数据库的真实执行过程**

下面我们来看一下实际怎么在问题中分析使用

查询学生的姓名、性别和参与班级名称

分析:查询学生的姓名、性别和参与班级名称

  1. 操作类型:select (查询)
  2. 到哪里取数据:学生、班级、学生参与班级表
  3. 得到哪些信息:学生姓名、学生性别、参与班级名称
  4. 过滤条件:学生参与的班级
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

通过上面分析得到如下查询结构

select 学生姓名,学生性别,参与班级名称

from 学生,班级,学生参与班级表

where 学生参与的班级;

由上面关于表连接的方式知道,这里需要一个有意义的过滤条件:学生参与的班级 ,由于学生参与班级的信息存储在学生参与班级表 student_join_class 中,所以这里需要连接此表。并且有意的过滤条件也是通过关联表进行匹配的。

select s.s_name, s.s_sex, c.c_name
from student s, class c, student_join_class sjc
where s.s_id=sjc.s_id and c.c_id=sjc.c_id;

执行 sql 得到如下结果

思考:这里为什么 s.s_id=sjc.s_id and c.c_id=sjc.c_id 是有意义的

查询所有班级名称和参与学生数量

分析:查询所有班级名称和参与学生数量

  1. 操作类型:select (查询)
  2. 到哪里取数据:班级、未知
  3. 得到哪些信息:班级名称,参与学生数量
  4. 过滤条件:无
  5. 排序字段:无
  6. 取多少数据:所有数据(无 limit)

通过上面分析得到如下查询结构

select 班级名称,参与学生数量

from 班级,未知;

根据需求可以知道,这里所有的班级名称都需要获取到,不论这个班级有没有人参与,所以这里基本可以确定使用 左连接 。由上了 SQL 实例分析已经得到查询一个班级的人数只需要 student_join_class 就可以了,所以可以确定 班级表左连接学生参与表 就行。表之间的关联关系是使用的数据字段是班级 id,所以这里的有意义过滤条件就是 班级 id 相等。最后为了得到每个班级的参与人数需要根据 班级信息作为分组统计条件

select 班级名称,参与学生数量

from 班级

left join 学生参与表 on 班级 id 相等

group by 班级;

这里班级的区分是通过班级 id 进行唯一区分的,可以有相同的班级名称,所以不能只以班级名称作为分组或分类条件。

select c.c_id, c.c_name, count(sjc.c_id) 
from class c 
left join student_join_class sjc on sjc.c_id=c.c_id
group by c.c_id, c.c_name;

注意:这里是 count(sjc.c_id) ,不能是 count(*) 。可以使用 count(*) 替换 count(sjc.c_id) 看看结果会有什么不同。 为什么请查看前面统计里面关于 count 函数的说明。

试试

总结

  • 子查询:from 子查询、where 子查询
  • 表连接:笛卡尔积、内连接、左连接、右连接
  • 多表查询分析方法

SQL 里面的查询语句学习到这里就截止了,后面将学习 DDL(数据定义语句) 学习和数据据的插入、修改、删除操作,最后简要介绍表索引、试图和存储过程。