之前學(xué)習(xí)的查詢都只涉及到單表，但在實際應(yīng)用中還有很大一部分需求，只靠單表查詢是實現(xiàn)不了的，比如：查詢一個班上的所有人；統(tǒng)計每個班的人數(shù)等。那這些需求或問題應(yīng)該怎么分析和實現(xiàn)，這就是今天要講解的多表查詢。
知識要點：
子查詢
表連接
多表查詢有兩種形式：一種是子查詢，一種表連接。那什么時候使用子查詢？什么時候使用表連接呢？這里說一下我個人的分析和使用步驟
1. 如果所有需要獲得信息列在一張表里可以找到，就用子查詢；
2. 其他情況肯定需要表連接；
3. 如果數(shù)據(jù)量很大，先子查詢再使用表連接
4. 使用性能工具分析 SQL 進行優(yōu)化（執(zhí)行計劃和執(zhí)行 profile）
子查詢
子查詢可以使用在 select 語句的 2 個地方，一個是在 from 子句中，一個是在 where 子句中，在使用的時候需要放在哪里就看是要從這個子句結(jié)果中返回信息，還是這個子句結(jié)果只是作為過濾條件。我們通過 2 個 sql 具體分析
查詢參與班級 id 為 1或2 的所有學(xué)生信息
分析：查詢參與班級 id 為 1和2 的所有學(xué)生信息
操作類型：select （查詢）
到哪里取數(shù)據(jù)：學(xué)生
得到哪些信息：所有學(xué)生信息
過濾條件：班級 id 為 1 或 2
排序字段：無
取多少數(shù)據(jù)：所有數(shù)據(jù)（無 limit）
我們將這些信息套入到 SELECT 語句結(jié)構(gòu)會得到如下：
select * from 學(xué)生
where 班級 id 為 1 或 2 ;
這里學(xué)生表里面是沒有班級信息的，并且返回的信息只有學(xué)生信息，班級 id 只是作為過濾條件，所以這里能確定 where 條件為一個子查詢。那這個子查詢應(yīng)該如何分析呢？我們回到最開始的 ER 圖
可以發(fā)現(xiàn)和學(xué)生信息有直接聯(lián)系的是 參加 這個關(guān)系，同時它還和班級信息 最近 （這里是直接聯(lián)系），就從 參加 這個關(guān)系入手。由于在轉(zhuǎn)換成數(shù)據(jù)庫物理存儲的時候，參加 這個關(guān)系我們也是在一種獨立的數(shù)據(jù)表中存儲的，所以我們首先看這個表的實際物理存儲
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 '學(xué)生 id',
cs_created timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '加入時間',
PRIMARY KEY (c_id,s_id)
) COMMENT='學(xué)生班級關(guān)聯(lián)表';
發(fā)現(xiàn)這個表里面已經(jīng)了班級 id c_id ，同時還包含學(xué)生 id s_id ，這樣就可以可以做到從 c_id -> s_id 的轉(zhuǎn)換，在我們知道了 s_id 的情況下，上面分析的 sql 結(jié)構(gòu)可以表示成，這里用 in 是由于通過 student_join_class 得到的 s_id 有可能是多個
select * from 學(xué)生
where s_id in (參加班級 1 或 2 的學(xué)生 id);
通過上面的分析知道：可以通過 student_join_class 表數(shù)據(jù)，用已知的班級 id c_id 數(shù)據(jù)查詢出參與班級的學(xué)生 id s_id ，這個 sql 通過之前的單表分析方式，不能得到查詢語句：
1
2
select s_id from student_join_class
where c_id in (1,2)
將此語句放入分析得到的 sql 語句結(jié)構(gòu)，就得到最終的查詢 sql 語句。
通過上面的分析，得到如下 sql 語句
1
2
3
4
5
select * from student
where s_id in (
select s_id from student_join_class
where c_id in (1,2)
);
執(zhí)行得到如下結(jié)果
查詢參與人數(shù)大于 3 的班級個數(shù)
分析：查詢參與人數(shù)大于 3 的班級個數(shù)
操作類型：select （查詢）
到哪里取數(shù)據(jù)：未知 （由于沒有任何一張物理表有班級參與人數(shù)信息）
得到哪些信息：班級個數(shù)
過濾條件：班級參與人數(shù)大于 3
排序字段：無
取多少數(shù)據(jù)：所有數(shù)據(jù)（無 limit）
通過上面分析得到如下查詢結(jié)構(gòu)
select count(*) from 未知
where 班級參與人數(shù)大于 3;
這里如果我們將 未知 理解成一張表的話，問題就轉(zhuǎn)換成：分析 未知表 里面應(yīng)該包含哪些信息？。其實從查詢結(jié)構(gòu)不難分析 未知表 只需要包含每個班級的參與人數(shù)就夠了。因為返回的信息只是統(tǒng)計班級的數(shù)量(這里統(tǒng)計之關(guān)系有這條數(shù)據(jù)，不關(guān)心數(shù)據(jù)里的具體內(nèi)容），但是過濾條件需要使用班級的參與人數(shù)進行比較，所以這里 未知表 的 最小信息就是 班級參與人數(shù) ?，F(xiàn)在問題轉(zhuǎn)變成
查詢每個班級的參與人數(shù) (未知表)
每個班級有哪些人參與的信息，全部都在關(guān)聯(lián)表 student_join_class 里面，所以通過統(tǒng)計不難得出每個班級的參與人數(shù) sql
1
2
select c_id, count(*) from student_join_class
group by c_id
這里所有我們需要的條件都完成了，組合得到的查詢結(jié)構(gòu)和 sql 就行。
通過上面的分析得到如下是 SQL。注意：這里在組合的時候?qū)?nbsp;count(*) 使用了別名 (as 關(guān)鍵字)，這是由于需要在外部查詢中使用結(jié)果值時，是不能直接用count（*）作為其列名稱。其實所有的函數(shù)操作都不行。
使用建議：對查詢返回中使用的所有函數(shù)列操作都定義別名
1
2
3
4
5
select count(*) from (
select c_id, count(*) as number from student_join_class
group by c_id
) a
where number > 3;
執(zhí)行的結(jié)果為：3 。
在分析的時候?qū)?nbsp;未知表 理解成一張表，這里數(shù)據(jù)庫實際執(zhí)行的時候確實會生成一張表，稱作 臨時表。數(shù)據(jù)庫在執(zhí)行 sql 的時候，會為每一個子查詢生成一個臨時表（沒有執(zhí)行優(yōu)化的情況），看數(shù)據(jù)庫是否為一個 sql 查詢生成臨時表可以通過查詢 SQL 執(zhí)行計劃 來判斷。
查詢 SQL 執(zhí)行計劃使用 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è)我們有 A、B 兩張表，表中的數(shù)據(jù)數(shù)量分別是 m 和 n。我們現(xiàn)在分別創(chuàng)建 A、B 兩張表，并且錄入一些數(shù)據(jù)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-- ----------------------------
-- 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;
笛卡爾積
我們來看第一個基本概念：笛卡爾積（也叫全連接）。在數(shù)學(xué)中笛卡爾積的定義是
兩個集合 X 和 Y 的笛卡兒積（Cartesian product），又稱直積，表示為 X × *Y*，是其第一個對象是 X的成員而第二個對象是 Y 的一個成員的所有可能的有序?qū)?。
這個難以理解的定義在 SQL 里面怎么理解呢？我們鏈接 A、B 兩個表的時候，不加入任何條件，讓 A 表中的每一條數(shù)據(jù)都和 B 表中的每一條數(shù)據(jù)做關(guān)聯(lián)，就是 A 和 B 的笛卡爾積，也叫全連接。具體 SQL 實現(xiàn)如下：
1
2
3
select * from A,B;
select * from A join B;
select * from A cross join B;
會發(fā)現(xiàn)上面的 3 個 sql 語句執(zhí)行的結(jié)果是一樣的。我們再看這 3 中鏈接形式過后的數(shù)據(jù)量
1
2
3
select count(*) from A,B;
select count(*) from A join B;
select count(*) from A cross join B;
我們會發(fā)現(xiàn) 2 點
全連接后的數(shù)據(jù)量是 m × n
全連接后的絕大部分?jǐn)?shù)據(jù)是沒有意義的
我們可以試想一下如果每個表的數(shù)據(jù)量都在 100w 這個數(shù)量級（在真實生產(chǎn)環(huán)境這個數(shù)據(jù)量是小的），那全連接過后的數(shù)據(jù)量將是 1億 數(shù)量級，里面的絕大多數(shù)數(shù)據(jù)是沒有意義的，并且這里才考慮了 2 個表，這個很可怕的。所以在使用表連接時盡量避免產(chǎn)生全連接。
普通鏈接
既然上面說到使用 全連接后的絕大部分?jǐn)?shù)據(jù)是沒有意義的 ，那我們怎么產(chǎn)生有意義的鏈接呢？其實也很簡單，我們只需要在鏈接的時候加上有意義的鏈接條件就行。以下 3 個語句其實是一樣的，只是表現(xiàn)形式不同
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;
大家可以發(fā)現(xiàn)這里出現(xiàn)的結(jié)果其實都在上面的 笛卡爾積 中，只是從 笛卡爾積 中按照 a_id=b_id 的過濾條件選出了少部分?jǐn)?shù)據(jù)，這里的 a_id=b_id 就是 有意義的條件 。在解決實際的問題的時候，什么是有 意義的條件 要根據(jù)你的問題和設(shè)計仔細分析。
明白了上面的 笛卡爾積 和 有意義的條件 ，我們來看一下最常用的 3 中連接方式：內(nèi)連接、左連接、右連接。再具體說明之前，先看一下示意圖。
這個示意圖和大家在網(wǎng)上使用集合表示的示意圖有些不同，主要是我感覺使用集合的示意圖不能完全詮釋 左連接 和 右連接 的概念和數(shù)據(jù)量，所以這里根據(jù)我自己的理解畫了這個示意圖。
A表數(shù)據(jù)量 m，B 表數(shù)據(jù)量 n
A、B 的數(shù)據(jù)量 m x n，這個笛卡爾積后的結(jié)果集和原來的 A、B 沒有任何關(guān)系
在笛卡爾積中滿足過濾條件的數(shù)據(jù)(a, b)，其中 a 是 A表滿足過濾條件的數(shù)量，b 是 B 表滿足過濾條件的數(shù)量。但是注意滿足條件的數(shù)據(jù)量并不是 a x b
A表中還有 m-a 條數(shù)據(jù)不滿足過濾條件，B 表中還有 n-b 條數(shù)據(jù)不滿足過濾條件
