史上最簡單MySQL教程詳解（基礎篇）之多表聯合查詢

---

在上篇文章史上最簡單MySQL教程詳解（基礎篇）之資料庫設計正規化及應用舉例我們介紹過，在關係型資料庫中，我們通常為了減少資料的冗餘量將對資料表進行規範，將資料分割到不同的表中。當我們需要將這些資料重新合成一條時，就需要用到我們介紹來將要說到的表連線。

常用術語

• 冗餘（Redundancy）：儲存兩次資料，以便使系統更快速。
• 主鍵（Primary Key）：主鍵是唯一的。同一張表中不允許出現同樣兩個鍵值。一個鍵值只對應著一行。
• 外來鍵（Foreign Key）：用於連線兩張表。

表連線的方式

• 內連線
• 外連線
• 自連線

我們接下來將對這三種連線進行詳細的介紹。

資料準備

我們需要建立下面的資料表來作為示例：

student表

表結構：

欄位	解釋
studentId	學號(主鍵)
name	姓名
phone	電話
collegeId	學生所在學院ID（外來鍵）

SQL語句：

CREATE TABLE `student` (
  `studentId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `phone` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `collegeId` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`studentId`),
  KEY `collegeId` (`collegeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

資料：

college表

表結構：

欄位	解釋
collegeId	學院ID(主鍵)
collegeName	學院名

SQL語句：

CREATE TABLE `college` (
  `collegeId` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `collegeName` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`collegeId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

資料：

內連線

內連線就是表間的主鍵與外來鍵相連，只取得鍵值一致的，可以獲取雙方表中的資料連線方式。語法如下：

SELECT 列名1,列名2... FROM 表1 INNER JOIN 表2 ON 表1.外來鍵=表2.主鍵 WhERE 條件語句;

執行結果：

mysql> SELECT student.name,college.collegeName FROM student INNER JOIN college ON student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 張三 | 清華 |
| 李四 | 北大 |
| 王五 | 浙大 |
+——+————-+
3 rows in set (0.04 sec)

這樣，我們就成功將【student】表中的【name】和【college】表中的【collegeName】進行了重新結合，並檢索出來。

外連線

與取得雙方表中資料的內連線相比，外連線只能取得其中一方存在的資料，外連線又分為左連線和右連線兩種情況。接下來，我們將介紹這兩種連線方式。

左外連線

左連線是以左表為標準，只查詢在左邊表中存在的資料，當然需要兩個表中的鍵值一致。語法如下：

SELECT 列名1 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.外來鍵=表2.主鍵 WhERE 條件語句;

執行結果：

mysql> SELECT student.name,college.collegeName FROM student LEFT OUTER JOIN college ON student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 張三 | 清華 |
| 李四 | 北大 |
| 王五 | 浙大 |
| 趙六 | NULL |
| 錢七 | NULL |
+——+————-+
5 rows in set (0.00 sec)

我們可以看出，與內連線查詢結果不同的是：【趙六】、【錢七】這兩個學生雖然沒有學校ID但是也被查出來了，這就是我們所說的，他會以左連線中的左表的全部資料作為基準進行查詢。

右外連線

同理，右連線將會以右邊作為基準，進行檢索。語法如下：

SELECT 列名1 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.外來鍵=表2.主鍵 WhERE 條件語句;

執行結果：

mysql> SELECT student.name,college.collegeName FROM student RIGHT OUTER JOIN college ON student.collegeId = college.collegeId;
+——+————-+
| name | collegeName |
+——+————-+
| 張三 | 清華 |
| 李四 | 北大 |
| 王五 | 浙大 |
| NULL | 廈大 |
+——+————-+
4 rows in set (0.00 sec)

我們可以看出，這裡就是以右邊的表【college】為基準進行了檢索，因為【student】中並沒有【廈大】的學生，所以檢索出來的為【NULL】

注意事項：

• 內連線是抽取兩表間鍵值一致的資料，而外連線（左連線，右連線）時，是以其中一個表的全部記錄作為基準進行檢索。
• 左連線和右連線只有資料基準的區別，本質上是一樣的，具體使用哪一種連線，根據實際的需求所決定
• 無論是內連線還是外連線，在查詢的時候最好使用【表名.列名】的方式指定需要查詢的列名，否則一旦兩個表中出現了列名一致的資料時，可能會報錯，養成良好的習慣很重要。
• 表的別名：其實我們在查詢的過程中，如果遇到了特別複雜的資料表名，我們可以通過取別名的方式來實現，使用的是我們以前使用過的【AS】語句，例如，我們的內連線就可以化簡為下面的語句：
  SELECT s.name,c.collegeName FROM student AS s INNER JOIN college AS c ON s.collegeId = c.collegeId;查詢結果一致，是不是瞬間覺得語句簡潔很多呢？

自連線

自連線顧名思義就是自己跟自己連線，有人或許會問，這樣的連線有意義嗎？答案是肯定的。
例如，我們將【student】的資料改為下圖：

執行結果如圖：

mysql> SELECT * FROM student s ,student a where a.collegeId=s.collegeId AND a.name <> s.name ORDER BY a.collegeId;
+———–+——+——-+———–+———–+——+——-+———–+
| studentId | name | phone | collegeId | studentId | name | phone | collegeId |
+———–+——+——-+———–+———–+——+——-+———–+
| 4 | 趙六 | 136 | 11 | 1 | 張三 | 139 | 11 |
| 1 | 張三 | 139 | 11 | 4 | 趙六 | 136 | 11 |
| 5 | 錢七 | 135 | 22 | 2 | 李四 | 130 | 22 |
| 2 | 李四 | 130 | 22 | 5 | 錢七 | 135 | 22 |
+———–+——+——-+———–+———–+——+——-+———–+
4 rows in set (0.00 sec)

可以看出，我們就將【student】表中在同一個學校的學生查出來了。
語句釋義：

• 【student s】和【student a】的含義就是分別給我們的【student】表取了兩個不同的別名；
• 【a.collegeId = s.collegeId AND a.name <> s.name 】的含義是找出【collegeId】相同，但是【name】不同的人.
• 【ORDER BY a.collegeId;】將結果順序輸出；

自連線的使用情況還是很多的，比如當我們找某個站點所經過的所有公交等，都可以採用自連線的方式進行檢索；

子查詢

通常我們在查詢的SQL中巢狀查詢，稱為子查詢。子查詢通常會使複雜的查詢變得簡單，但是相關的子查詢要對基礎表的每一條資料都進行子查詢的動作，所以當表單中資料過大時，一定要慎重選擇。基本語法如下：

SELECT 列名1 ...FROM 表名 WHERE 列名 比較運算子 （SELECT 命令）;

例如：我們利用上面的內連線的例子，在它的基礎上查出學校為【清華】的學生的姓名

mysql> SELECT * FROM (SELECT student.name,college.collegeName FROM student INNER JOIN college ON student.collegeId = college.collegeId)b WHERE b.collegeName = ‘清華’;
+——+————-+
| name | collegeName |
+——+————-+
| 張三 | 清華 |
| 趙六 | 清華 |
+——+————-+
2 rows in set (0.00 sec)

查詢成功。
到此，已經介紹完了所有關於MySQL基礎篇的內容，接下來，我們將介紹史上最簡單MySQL教程詳解（進階篇）之儲存引擎及預設引擎設定；