之前學(xué)習(xí)的查詢都只涉及到單表，但在實(shí)際應(yīng)用中還有很大一部分需求，只靠單表查詢是實(shí)現(xiàn)不了的，比如：查詢一個(gè)班上的所有人；統(tǒng)計(jì)每個(gè)班的人數(shù)等。那這些需求或問(wèn)題應(yīng)該怎么分析和實(shí)現(xiàn)，這就是今天要講解的多表查詢。

知識(shí)要點(diǎn)：

• 子查詢

• 表連接

多表查詢有兩種形式：一種是子查詢，一種表連接。那什么時(shí)候使用子查詢？什么時(shí)候使用表連接呢？這里說(shuō)一下我個(gè)人的分析和使用步驟

• 1. 如果所有需要獲得信息列在一張表里可以找到，就用子查詢；

• 2. 其他情況肯定需要表連接；

• 3. 如果數(shù)據(jù)量很大，先子查詢?cè)偈褂帽磉B接

• 4. 使用性能工具分析 SQL 進(jìn)行優(yōu)化（執(zhí)行計(jì)劃和執(zhí)行 profile）

子查詢

子查詢可以使用在 select 語(yǔ)句的 2 個(gè)地方，一個(gè)是在 from 子句中，一個(gè)是在 where 子句中，在使用的時(shí)候需要放在哪里就看是要從這個(gè)子句結(jié)果中返回信息，還是這個(gè)子句結(jié)果只是作為過(guò)濾條件。我們通過(guò) 2 個(gè) sql 具體分析

查詢參與班級(jí) id 為 1或2 的所有學(xué)生信息

分析：查詢參與班級(jí) id 為 1和2 的所有學(xué)生信息

1. 操作類型：select （查詢）

2. 到哪里取數(shù)據(jù)：學(xué)生

3. 得到哪些信息：所有學(xué)生信息

4. 過(guò)濾條件：班級(jí) id 為 1 或 2

5. 排序字段：無(wú)

6. 取多少數(shù)據(jù)：所有數(shù)據(jù)（無(wú) limit）

我們將這些信息套入到 SELECT 語(yǔ)句結(jié)構(gòu)會(huì)得到如下：

select * from 學(xué)生

where 班級(jí) id 為 1 或 2 ;

這里學(xué)生表里面是沒有班級(jí)信息的，并且返回的信息只有學(xué)生信息，班級(jí) id 只是作為過(guò)濾條件，所以這里能確定 where 條件為一個(gè)子查詢。那這個(gè)子查詢應(yīng)該如何分析呢？我們回到最開始的 ER 圖可以發(fā)現(xiàn)和學(xué)生信息有直接聯(lián)系的是 參加 這個(gè)關(guān)系，同時(shí)它還和班級(jí)信息 最近 （這里是直接聯(lián)系），就從 參加 這個(gè)關(guān)系入手。由于在轉(zhuǎn)換成數(shù)據(jù)庫(kù)物理存儲(chǔ)的時(shí)候，參加 這個(gè)關(guān)系我們也是在一種獨(dú)立的數(shù)據(jù)表中存儲(chǔ)的，所以我們首先看這個(gè)表的實(shí)際物理存儲(chǔ)

1 2 3 4 5 6

CREATE TABLE student_join_class ( c_id int(11) NOT NULL COMMENT '班級(jí) id', s_id int(11) NOT NULL COMMENT '學(xué)生 id', cs_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '加入時(shí)間', PRIMARY KEY (c_id,s_id) )  COMMENT='學(xué)生班級(jí)關(guān)聯(lián)表';

發(fā)現(xiàn)這個(gè)表里面已經(jīng)了班級(jí) id c_id ，同時(shí)還包含學(xué)生 id s_id ，這樣就可以可以做到從 c_id -> s_id 的轉(zhuǎn)換，在我們知道了 s_id 的情況下，上面分析的 sql 結(jié)構(gòu)可以表示成，這里用 in 是由于通過(guò) student_join_class 得到的 s_id 有可能是多個(gè)

select * from 學(xué)生

where s_id in (參加班級(jí) 1 或 2 的學(xué)生 id);

通過(guò)上面的分析知道：可以通過(guò) student_join_class 表數(shù)據(jù)，用已知的班級(jí) id c_id 數(shù)據(jù)查詢出參與班級(jí)的學(xué)生 id s_id ，這個(gè) sql 通過(guò)之前的單表分析方式，不能得到查詢語(yǔ)句：

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

將此語(yǔ)句放入分析得到的 sql 語(yǔ)句結(jié)構(gòu)，就得到最終的查詢 sql 語(yǔ)句。

通過(guò)上面的分析，得到如下 sql 語(yǔ)句

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

執(zhí)行得到如下結(jié)果

查詢參與人數(shù)大于 3 的班級(jí)個(gè)數(shù)

分析：查詢參與人數(shù)大于 3 的班級(jí)個(gè)數(shù)

1. 操作類型：select （查詢）

2. 到哪里取數(shù)據(jù)：未知 （由于沒有任何一張物理表有班級(jí)參與人數(shù)信息）

3. 得到哪些信息：班級(jí)個(gè)數(shù)

4. 過(guò)濾條件：班級(jí)參與人數(shù)大于 3

5. 排序字段：無(wú)

6. 取多少數(shù)據(jù)：所有數(shù)據(jù)（無(wú) limit）

通過(guò)上面分析得到如下查詢結(jié)構(gòu)

select count(*) from 未知

where 班級(jí)參與人數(shù)大于 3;

• 這里如果我們將 未知 理解成一張表的話，問(wèn)題就轉(zhuǎn)換成：分析 未知表 里面應(yīng)該包含哪些信息？。其實(shí)從查詢結(jié)構(gòu)不難分析 未知表 只需要包含每個(gè)班級(jí)的參與人數(shù)就夠了。因?yàn)榉祷氐男畔⒅皇墙y(tǒng)計(jì)班級(jí)的數(shù)量(這里統(tǒng)計(jì)之關(guān)系有這條數(shù)據(jù)，不關(guān)心數(shù)據(jù)里的具體內(nèi)容），但是過(guò)濾條件需要使用班級(jí)的參與人數(shù)進(jìn)行比較，所以這里 未知表 的 最小信息就是 班級(jí)參與人數(shù) ?，F(xiàn)在問(wèn)題轉(zhuǎn)變成

• 查詢每個(gè)班級(jí)的參與人數(shù) (未知表)

每個(gè)班級(jí)有哪些人參與的信息，全部都在關(guān)聯(lián)表 student_join_class 里面，所以通過(guò)統(tǒng)計(jì)不難得出每個(gè)班級(jí)的參與人數(shù) sql

1 2	select c_id, count(*) from student_join_class group by c_id

這里所有我們需要的條件都完成了，組合得到的查詢結(jié)構(gòu)和 sql 就行。

通過(guò)上面的分析得到如下是 SQL。注意：這里在組合的時(shí)候?qū)?nbsp;count(*) 使用了別名 (as 關(guān)鍵字)，這是由于需要在外部查詢中使用結(jié)果值時(shí)，是不能直接用count（*）作為其列名稱。其實(shí)所有的函數(shù)操作都不行。

使用建議：對(duì)查詢返回中使用的所有函數(shù)列操作都定義別名

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

執(zhí)行的結(jié)果為：3 。

在分析的時(shí)候?qū)?nbsp;未知表 理解成一張表，這里數(shù)據(jù)庫(kù)實(shí)際執(zhí)行的時(shí)候確實(shí)會(huì)生成一張表，稱作 臨時(shí)表。數(shù)據(jù)庫(kù)在執(zhí)行 sql 的時(shí)候，會(huì)為每一個(gè)子查詢生成一個(gè)臨時(shí)表（沒有執(zhí)行優(yōu)化的情況），看數(shù)據(jù)庫(kù)是否為一個(gè) sql 查詢生成臨時(shí)表可以通過(guò)查詢 SQL 執(zhí)行計(jì)劃 來(lái)判斷。

查詢 SQL 執(zhí)行計(jì)劃使用 explain 關(guān)鍵字，執(zhí)行以下 sql 查看結(jié)果輸出

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;

表連接

為了讓大家更好的理解表連接，這里先講解一下表連接的基本知識(shí)。這里假設(shè)我們有 A、B 兩張表，表中的數(shù)據(jù)數(shù)量分別是 m 和 n。我們現(xiàn)在分別創(chuàng)建 A、B 兩張表，并且錄入一些數(shù)據(jù)

-- ----------------------------
-- 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;

笛卡爾積

• 我們來(lái)看第一個(gè)基本概念：笛卡爾積（也叫全連接）。在數(shù)學(xué)中笛卡爾積的定義是

• 兩個(gè)集合 X 和 Y 的笛卡兒積（Cartesian product），又稱直積，表示為 X × *Y*，是其第一個(gè)對(duì)象是 X的成員而第二個(gè)對(duì)象是 Y 的一個(gè)成員的所有可能的有序?qū)?。

這個(gè)難以理解的定義在 SQL 里面怎么理解呢？我們鏈接 A、B 兩個(gè)表的時(shí)候，不加入任何條件，讓 A 表中的每一條數(shù)據(jù)都和 B 表中的每一條數(shù)據(jù)做關(guān)聯(lián)，就是 A 和 B 的笛卡爾積，也叫全連接。具體 SQL 實(shí)現(xiàn)如下：

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

會(huì)發(fā)現(xiàn)上面的 3 個(gè) sql 語(yǔ)句執(zhí)行的結(jié)果是一樣的。我們?cè)倏催@ 3 中鏈接形式過(guò)后的數(shù)據(jù)量

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

我們會(huì)發(fā)現(xiàn) 2 點(diǎn)

1. 全連接后的數(shù)據(jù)量是 m × n

2. 全連接后的絕大部分?jǐn)?shù)據(jù)是沒有意義的

我們可以試想一下如果每個(gè)表的數(shù)據(jù)量都在 100w 這個(gè)數(shù)量級(jí)（在真實(shí)生產(chǎn)環(huán)境這個(gè)數(shù)據(jù)量是小的），那全連接過(guò)后的數(shù)據(jù)量將是 1億 數(shù)量級(jí)，里面的絕大多數(shù)數(shù)據(jù)是沒有意義的，并且這里才考慮了 2 個(gè)表，這個(gè)很可怕的。所以在使用表連接時(shí)盡量避免產(chǎn)生全連接。

普通鏈接

既然上面說(shuō)到使用 全連接后的絕大部分?jǐn)?shù)據(jù)是沒有意義的 ，那我們?cè)趺串a(chǎn)生有意義的鏈接呢？其實(shí)也很簡(jiǎn)單，我們只需要在鏈接的時(shí)候加上有意義的鏈接條件就行。以下 3 個(gè)語(yǔ)句其實(shí)是一樣的，只是表現(xiàn)形式不同

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;

大家可以發(fā)現(xiàn)這里出現(xiàn)的結(jié)果其實(shí)都在上面的 笛卡爾積 中，只是從 笛卡爾積 中按照 a_id=b_id 的過(guò)濾條件選出了少部分?jǐn)?shù)據(jù)，這里的 a_id=b_id 就是 有意義的條件 。在解決實(shí)際的問(wèn)題的時(shí)候，什么是有 意義的條件 要根據(jù)你的問(wèn)題和設(shè)計(jì)仔細(xì)分析。

明白了上面的 笛卡爾積 和 有意義的條件 ，我們來(lái)看一下最常用的 3 中連接方式：內(nèi)連接、左連接、右連接。再具體說(shuō)明之前，先看一下示意圖。

這個(gè)示意圖和大家在網(wǎng)上使用集合表示的示意圖有些不同，主要是我感覺使用集合的示意圖不能完全詮釋 左連接 和 右連接 的概念和數(shù)據(jù)量，所以這里根據(jù)我自己的理解畫了這個(gè)示意圖。

1. A表數(shù)據(jù)量 m，B 表數(shù)據(jù)量 n

2. A、B 的數(shù)據(jù)量 m x n，這個(gè)笛卡爾積后的結(jié)果集和原來(lái)的 A、B 沒有任何關(guān)系

3. 在笛卡爾積中滿足過(guò)濾條件的數(shù)據(jù)(a, b)，其中 a 是 A表滿足過(guò)濾條件的數(shù)量，b 是 B 表滿足過(guò)濾條件的數(shù)量。但是注意滿足條件的數(shù)據(jù)量并不是 a x b

4. A表中還有 m-a 條數(shù)據(jù)不滿足過(guò)濾條件，B 表中還有 n-b 條數(shù)據(jù)不滿足過(guò)濾條件