一起聊聊MySQL基礎之連線查詢
再次認識關係表
我們之前一直使用student_info和student_score兩個表來分別儲存學生的基本資訊和學生的成績資訊，其實合併成一張表也不是不可以，假設將兩張表合併後的新表名稱為student_merge，那它應該長這樣：
student_merge表
| number | name | sex | id_number | department | major | enrollment_time | subject | score |
|---|---|---|---|---|---|---|---|---|
| 20180101 | 杜子騰 | 男 | 158177199901044792 | 計算機學院 | 電腦科學與工程 | 2018-09-01 | 母豬的產後護理 | 78 |
| 20180101 | 杜子騰 | 男 | 158177199901044792 | 計算機學院 | 電腦科學與工程 | 2018-09-01 | 論薩達姆的戰爭準備 | 88 |
| 20180102 | 杜琦燕 | 女 | 151008199801178529 | 計算機學院 | 電腦科學與工程 | 2018-09-01 | 母豬的產後護理 | 100 |
| 20180102 | 杜琦燕 | 女 | 151008199801178529 | 計算機學院 | 電腦科學與工程 | 2018-09-01 | 論薩達姆的戰爭準備 | 98 |
| 20180103 | 範統 | 男 | 17156319980116959X | 計算機學院 | 軟體工程 | 2018-09-01 | 母豬的產後護理 | 59 |
| 20180103 | 範統 | 男 | 17156319980116959X | 計算機學院 | 軟體工程 | 2018-09-01 | 論薩達姆的戰爭準備 | 61 |
| 20180104 | 史珍香 | 女 | 141992199701078600 | 計算機學院 | 軟體工程 | 2018-09-01 | 母豬的產後護理 | 55 |
| 20180104 | 史珍香 | 女 | 141992199701078600 | 計算機學院 | 軟體工程 | 2018-09-01 | 論薩達姆的戰爭準備 | 46 |
| 20180105 | 範劍 | 男 | 181048200008156368 | 航天學院 | 飛行器設計 | 2018-09-01 | NULL | NULL |
| 20180106 | 朱逸群 | 男 | 197995199801078445 | 航天學院 | 電子資訊 | 2018-09-01 | NULL | NULL |
有了這個合併後的表，我們就可以在一個查詢語句中既查詢到學生的基本資訊，也查詢到學生的成績資訊，比如這個查詢語句：
SELECT number, name, major, subject, score FROM student_merge;
其中查詢列表處的name和major屬於學生的基本資訊，subject和score屬於學生的成績資訊，而number既屬於成績資訊也屬於基本資訊，我們可以在一個對student_merge表的查詢語句中很輕鬆的把這些資訊都查詢出來。但是別忘了一個學生可能會有很多門學科的成績資訊，也就是說每當我們想為一個學生增加一門學科的成績資訊時，我們必須把他的基本資訊再抄一遍，這種同一個學生的基本資訊被冗餘儲存會帶來下邊的問題：
問題一：浪費儲存空間。
問題二：當修改某個學生的基本資訊時必須修改多處，很容易造成資訊的不一致，增大維護的困難。
所以為了儘可能少的儲存冗餘資訊，一開始我們就把這個所謂的student_merge表拆分成了student_info和student_score表，但是這兩張表之間有某種關係作為紐帶，這裡的某種關係指的就是兩個表都擁有的number列。
連線的概念
拆分之後的表的確解決了資料冗餘問題，但是查詢資料卻成了一個問題。截至目前為止，在我們介紹的查詢方式中，查詢結果集只能是一個表中的一個列或者多個列，也就是說到目前為止還沒有一種可以在一條查詢語句中把某個學生的number、name、major、subject、score這幾個資訊都查詢出來的方式。
小貼士： 雖然我們前邊介紹的子查詢可以在一個查詢語句中涉及到多個表，但是整個查詢語句最終產生的結果集還是用來展示外層查詢的結果，子查詢的結果只是被當作中間結果來使用。
時代在召喚一種可以在一個查詢語句結果集中展示多個表的資訊的方式，連線查詢承擔了這個艱鉅的歷史使命。當然，為了故事的順利發展，我們先建立兩個簡單的表並給它們填充一點資料：
mysql> CREATE TABLE t1 (m1 int, n1 char(1)); Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE t2 (m2 int, n2 char(1)); Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO t1 VALUES(1, 'a'), (2, 'b'), (3, 'c'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 VALUES(2, 'b'), (3, 'c'), (4, 'd'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
我們成功建立了t1、t2兩個表，這兩個表都有兩個列，一個是INT型別的，一個是CHAR(1)型別的，填充好資料的兩個表長這樣：
mysql> SELECT * FROM t1; +------+------+ | m1 | n1 | +------+------+ | 1 | a | | 2 | b | | 3 | c | +------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t2; +------+------+ | m2 | n2 | +------+------+ | 2 | b | | 3 | c | | 4 | d | +------+------+ 3 rows in set (0.00 sec) mysql>
連線的本質就是把各個表中的記錄都取出來依次匹配的組合加入結果集並返回給使用者。我們把t1和t2兩個表連線起來的過程如下圖所示：
這個過程看起來就是把t1表的記錄和t2表的記錄連起來組成新的更大的記錄，所以這個查詢過程稱之為連線查詢。連線查詢的結果集中包含一個表中的每一條記錄與另一個表中的每一條記錄相互匹配的組合，像這樣的結果集就可以稱之為笛卡爾積。因為表t1中有3條記錄，表t2中也有3條記錄，所以這兩個表連線之後的笛卡爾積就有3×3=9行記錄。在MySQL中，連線查詢的語法也很隨意，只要在FROM語句後邊跟多個用逗號,隔開的表名就好了，比如我們把t1表和t2表連線起來的查詢語句可以寫成這樣：
mysql> SELECT * FROM t1, t2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | +------+------+------+------+ 9 rows in set (0.00 sec)
查詢列表處的*代表從FROM語句後列出的表中選取每個列，上邊的查詢語句其實和下邊這幾種寫法都是等價的：
寫法一：
SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;
這種寫法是將
t1、t2表中的列名都顯式的寫出來，也就是使用了列的全限定名。寫法二：
SELECT m1, n1, m2, n2 FROM t1, t2;
由於
t1、t2表中的列名並不重複，所以沒有可能讓伺服器懵逼的二義性，在查詢列表上直接使用列名也是可以的。寫法三：
SELECT t1.*, t2.* FROM t1, t2;
這種寫法意思就是查詢
t1表的全部的列，t2表的全部的列。
連線過程簡介
如果我們樂意，我們可以連線任意數量張表，但是如果沒有任何限制條件的話，這些表連線起來產生的笛卡爾積可能是非常巨大的。比方說3個100行記錄的表連線起來產生的笛卡爾積就有100×100×100=1000000行資料！所以在連線的時候過濾掉特定記錄組合是有必要的，在連線查詢中的過濾條件可以分成兩種：
涉及單表的條件
這種只涉及單表的過濾條件我們之前都提到過一萬遍了，我們之前也一直稱為
搜尋條件，比如t1.m1 > 1是隻針對t1表的過濾條件，t2.n2 < 'd'是隻針對t2表的過濾條件。涉及兩表的條件
這種過濾條件我們之前沒見過，比如
t1.m1 = t2.m2、t1.n1 > t2.n2等，這些條件中涉及到了兩個表，我們稍後會仔細分析這種過濾條件是如何使用的哈。
下邊我們就要看一下攜帶過濾條件的連線查詢的大致執行過程了，比方說下邊這個查詢語句：
SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';
在這個查詢中我們指明瞭這三個過濾條件：
t1.m1 > 1t1.m1 = t2.m2t2.n2 < 'd'
那麼這個連線查詢的大致執行過程如下：
首先確定第一個需要查詢的表，這個表稱之為
驅動表。此處假設使用t1作為驅動表，那麼就需要到t1表中找滿足t1.m1 > 1的記錄，符合這個條件的t1表記錄如下所示：+------+------+ | m1 | n1 | +------+------+ | 2 | b | | 3 | c | +------+------+ 2 rows in set (0.01 sec)
我們可以看到，
t1表中符合t1.m1 > 1的記錄有兩條。上一步驟中從驅動表每獲取到一條記錄，都需要到
t2表中查詢匹配的記錄，所謂匹配的記錄，指的是符合過濾條件的記錄。因為是根據t1表中的記錄去找t2表中的記錄，所以t2表也可以被稱之為被驅動表。上一步驟從驅動表中得到了2條記錄，也就意味著需要查詢2次t2表。此時涉及兩個表的列的過濾條件t1.m1 = t2.m2就派上用場了：對於從
t1表種查詢得到的第一條記錄，也就是當t1.m1 = 2, t1.n1 = 'b'時，過濾條件t1.m1 = t2.m2就相當於t2.m2 = 2，所以此時t2表相當於有了t2.m2 = 2、t2.n2 < 'd'這兩個過濾條件，然後到t2表中執行單表查詢，將得到的記錄和從t1表中查詢得到的第一條記錄相組合得到下邊的結果：+------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | +------+------+------+------+
對於從
t1表種查詢得到的第二條記錄，也就是當t1.m1 = 3, t1.n1 = 'c'時，過濾條件t1.m1 = t2.m2就相當於t2.m2 = 3，所以此時t2表相當於有了t2.m2 = 3、t2.n2 < 'd'這兩個過濾條件，然後到t2表中執行單表查詢，將得到的記錄和從t1表中查詢得到的第二條記錄相組合得到下邊的結果：+------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 3 | c | 3 | c | +------+------+------+------+
所以整個連線查詢的執行最後得到的結果集就是這樣：
+------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec)
從上邊兩個步驟可以看出來，我們上邊嘮叨的這個兩表連線查詢共需要查詢1次t1表，2次t2表。當然這是在特定的過濾條件下的結果，如果我們把t1.m1 > 1這個條件去掉，那麼從t1表中查出的記錄就有3條，就需要查詢3次t2表了。也就是說在兩表連線查詢中，驅動表只需要查詢一次，被驅動表可能會被查詢多次。
內連線和外連線
瞭解了連線查詢的執行過程之後，視角再回到我們的student_info表和student_score表。現在我們想在一個查詢語句中既查詢到學生的基本資訊，也查詢到學生的成績資訊，就需要進行兩表連線了。連線過程就是從student_info表中取出記錄，在student_score表中查詢number值相同的成績記錄，所以過濾條件就是student_info.number = student_score.number，整個查詢語句就是這樣：
mysql> SELECT student_info.number, name, major, subject, score FROM student_info, student_score WHERE student_info.number = student_score.number; +----------+-----------+--------------------------+-----------------------------+-------+ | number | name | major | subject | score | +----------+-----------+--------------------------+-----------------------------+-------+ | 20180101 | 杜子騰 | 電腦科學與工程 | 母豬的產後護理 | 78 | | 20180101 | 杜子騰 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 88 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 母豬的產後護理 | 100 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 98 | | 20180103 | 範統 | 軟體工程 | 母豬的產後護理 | 59 | | 20180103 | 範統 | 軟體工程 | 論薩達姆的戰爭準備 | 61 | | 20180104 | 史珍香 | 軟體工程 | 母豬的產後護理 | 55 | | 20180104 | 史珍香 | 軟體工程 | 論薩達姆的戰爭準備 | 46 | +----------+-----------+--------------------------+-----------------------------+-------+ 8 rows in set (0.00 sec) mysql>
小貼士： student_info表和student_score表都有number列，不過我們在上述查詢語句的查詢列表中只放置了student_info表的number列，這是因為我們的過濾條件是student_info.number = student_score.number，從兩個表中取出的記錄的number列都相同，所以只需要放置一個表中的number列到查詢列表即可，也就是說我們把student_score.number放到查詢列表處也是可以滴～
從上述查詢結果中我們可以看到，各個同學對應的各科成績就都被查出來了，可是有個問題，範劍和朱逸群同學，也就是學號為20180105和20180106的同學因為某些原因沒有參加考試，所以在studnet_score表中沒有對應的成績記錄。那如果老師想檢視所有同學的考試成績，即使是缺考的同學也應該展示出來，但是到目前為止我們介紹的連線查詢是無法完成這樣的需求的。我們稍微思考一下這個需求，其本質是想：驅動表中的記錄即使在被驅動表中沒有匹配的記錄，也仍然需要加入到結果集。為了解決這個問題，就有了內連線和外連線的概念：
對於
內連線的兩個表，驅動表中的記錄在被驅動表中找不到匹配的記錄，該記錄不會加入到最後的結果集，我們上邊提到的連線都是所謂的內連線。對於
外連線的兩個表，驅動表中的記錄即使在被驅動表中沒有匹配的記錄，也仍然需要加入到結果集。在
MySQL中，根據選取驅動表的不同，外連線仍然可以細分為2種：左外連線
選取左側的表為驅動表。
右外連線
選取右側的表為驅動表。
可是這樣仍然存在問題，即使對於外連線來說，有時候我們也並不想把驅動表的全部記錄都加入到最後的結果集。這就犯難了，有時候匹配失敗要加入結果集，有時候又不要加入結果集，這咋辦，有點兒愁啊。。。噫，把過濾條件分為兩種不就解決了這個問題了麼，所以放在不同地方的過濾條件是有不同語意的：
WHERE子句中的過濾條件WHERE子句中的過濾條件就是我們平時見的那種，不論是內連線還是外連線，凡是不符合WHERE子句中的過濾條件的記錄都不會被加入最後的結果集。ON子句中的過濾條件對於外連線的驅動表的記錄來說，如果無法在被驅動表中找到匹配
ON子句中的過濾條件的記錄，那麼該記錄仍然會被加入到結果集中，對應的被驅動表記錄的各個欄位使用NULL值填充。需要注意的是，這個
ON子句是專門為外連線驅動表中的記錄在被驅動表找不到匹配記錄時應不應該把該記錄加入結果集這個場景下提出的，所以如果把ON子句放到內連線中，MySQL會把它和WHERE子句一樣對待，也就是說：內連線中的WHERE子句和ON子句是等價的。
一般情況下，我們都把只涉及單表的過濾條件放到WHERE子句中，把涉及兩表的過濾條件都放到ON子句中，我們也一般把放到ON子句中的過濾條件也稱之為連線條件。
小貼士： 左外連線和右外連線簡稱左連線和右連線，所以下邊提到的左外連線和右外連線中的`外`字都用括號擴起來，以表示這個字兒可有可無。
左（外）連線的語法
左（外）連線的語法還是挺簡單的，比如我們要把t1表和t2表進行左外連線查詢可以這麼寫：
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 連線條件 [WHERE 普通過濾條件];
其中中括號裡的OUTER單詞是可以省略的。對於LEFT JOIN型別的連線來說，我們把放在左邊的表稱之為外表或者驅動表，右邊的表稱之為內表或者被驅動表。所以上述例子中t1就是外表或者驅動表，t2就是內表或者被驅動表。需要注意的是，對於左（外）連線和右（外）連線來說，必須使用ON子句來指出連線條件。瞭解了左（外）連線的基本語法之後，再次回到我們上邊那個現實問題中來，看看怎樣寫查詢語句才能把所有的學生的成績資訊都查詢出來，即使是缺考的考生也應該被放到結果集中：
mysql> SELECT student_info.number, name, major, subject, score FROM student_info LEFT JOIN student_score ON student_info.number = student_score.number; +----------+-----------+--------------------------+-----------------------------+-------+ | number | name | major | subject | score | +----------+-----------+--------------------------+-----------------------------+-------+ | 20180101 | 杜子騰 | 電腦科學與工程 | 母豬的產後護理 | 78 | | 20180101 | 杜子騰 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 88 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 母豬的產後護理 | 100 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 98 | | 20180103 | 範統 | 軟體工程 | 母豬的產後護理 | 59 | | 20180103 | 範統 | 軟體工程 | 論薩達姆的戰爭準備 | 61 | | 20180104 | 史珍香 | 軟體工程 | 母豬的產後護理 | 55 | | 20180104 | 史珍香 | 軟體工程 | 論薩達姆的戰爭準備 | 46 | | 20180105 | 範劍 | 飛行器設計 | NULL | NULL | | 20180106 | 朱逸群 | 電子資訊 | NULL | NULL | +----------+-----------+--------------------------+-----------------------------+-------+ 10 rows in set (0.00 sec) mysql>
從結果集中可以看出來，雖然範劍和朱逸群並沒有對應的成績記錄，但是由於採用的是連線型別為左（外）連線，所以仍然把它放到了結果集中，只不過在對應的成績記錄的各列使用NULL值填充而已。
右（外）連線的語法
右（外）連線和左（外）連線的原理是一樣一樣的，語法也只是把LEFT換成RIGHT而已：
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 連線條件 [WHERE 普通過濾條件];
只不過驅動表是右邊的表，被驅動表是左邊的表，具體就不嘮叨了。
內連線的語法
內連線和外連線的根本區別就是在驅動表中的記錄不符合ON子句中的連線條件時不會把該記錄加入到最後的結果集，我們最開始嘮叨的那些連線查詢的型別都是內連線。不過之前僅僅提到了一種最簡單的內連線語法，就是直接把需要連線的多個表都放到FROM子句後邊。其實針對內連線，MySQL提供了好多不同的語法，我們以t1和t2表為例瞅瞅：
SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 連線條件] [WHERE 普通過濾條件];
也就是說在MySQL中，下邊這幾種內連線的寫法都是等價的：
SELECT * FROM t1 JOIN t2;
SELECT * FROM t1 INNER JOIN t2;
SELECT * FROM t1 CROSS JOIN t2;
上邊的這些寫法和直接把需要連線的表名放到FROM語句之後，用逗號,分隔開的寫法是等價的：
SELECT * FROM t1, t2;
現在我們雖然介紹了很多種內連線的書寫方式，不過熟悉一種就好了，這裡我們推薦INNER JOIN的形式書寫內連線（因為INNER JOIN語意很明確嘛，可以和LEFT JOIN和RIGHT JOIN很輕鬆的區分開）。這裡需要注意的是，由於在內連線中ON子句和WHERE子句是等價的，所以內連線中不要求強制寫明ON子句。
我們前邊說過，連線的本質就是把各個連線表中的記錄都取出來依次匹配的組合加入結果集並返回給使用者。不論哪個表作為驅動表，兩表連線產生的笛卡爾積肯定是一樣的。而對於內連線來說，由於凡是不符合ON子句或WHERE子句中的條件的記錄都會被過濾掉，其實也就相當於從兩表連線的笛卡爾積中把不符合過濾條件的記錄給踢出去，所以對於內連線來說，驅動表和被驅動表是可以互換的，並不會影響最後的查詢結果。但是對於外連線來說，由於驅動表中的記錄即使在被驅動表中找不到符合ON子句連線條件的記錄也會被加入結果集，所以此時驅動表和被驅動表的關係就很重要了，也就是說左外連線和右外連線的驅動表和被驅動表不能輕易互換。
小結
上邊說了很多，給大家的感覺不是很直觀，我們直接把表t1和t2的三種連線方式寫在一起，這樣大家理解起來就很easy了：
mysql> SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | +------+------+------+------+ 2 rows in set (0.00 sec) mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | 1 | a | NULL | NULL | +------+------+------+------+ 3 rows in set (0.00 sec) mysql> SELECT * FROM t1 RIGHT JOIN t2 ON t1.m1 = t2.m2; +------+------+------+------+ | m1 | n1 | m2 | n2 | +------+------+------+------+ | 2 | b | 2 | b | | 3 | c | 3 | c | | NULL | NULL | 4 | d | +------+------+------+------+ 3 rows in set (0.00 sec)
連線查詢產生的結果集就好像把散佈到兩個表中的資訊被重新貼上到了一個表，這個貼上後的結果集可以方便我們分析資料，就不用老是兩個表對照的看了。
多表連線
上邊說過，如果我們樂意的話可以連線任意數量的表，我們再來建立一個簡單的t3表：
mysql> CREATE TABLE t3 (m3 int, n3 char(1)); ERROR 1050 (42S01): Table 't3' already exists mysql> INSERT INTO t3 VALUES(3, 'c'), (4, 'd'), (5, 'e'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql>
與t1和t2表的結構一樣，也是一個INT列，一個CHAR(1)列，現在我們看一下把這3個表連起來的樣子：
mysql> SELECT * FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.m1 = t2.m2 AND t1.m1 = t3.m3; +------+------+------+------+------+------+ | m1 | n1 | m2 | n2 | m3 | n3 | +------+------+------+------+------+------+ | 3 | c | 3 | c | 3 | c | +------+------+------+------+------+------+ 1 row in set (0.00 sec) mysql>
其實上邊的查詢語句也可以寫成這樣，用哪個取決於你的心情：
SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 INNER JOIN t3 ON t1.m1 = t3.m3;
這個查詢的執行過程用虛擬碼錶示一下就是這樣：
for each row in t1 {
for each row in t2 which satisfies t1.m1 = t2.m2 {
for each row in t3 which satisfies t1.m1 = t3.m3 {
send to client;
}
}
}其實不管是多少個表的連線，本質上就是各個表的記錄在符合過濾條件下的自由組合。
表的別名
我們前邊曾經為列命名過別名，比如說這樣：
mysql> SELECT number AS xuehao FROM student_info; +----------+ | xuehao | +----------+ | 20180104 | | 20180102 | | 20180101 | | 20180103 | | 20180105 | | 20180106 | +----------+ 6 rows in set (0.00 sec) mysql>
我們可以把列的別名用在ORDER BY、GROUP BY等子句上，比如這樣：
mysql> SELECT number AS xuehao FROM student_info ORDER BY xuehao DESC; +----------+ | xuehao | +----------+ | 20180106 | | 20180105 | | 20180104 | | 20180103 | | 20180102 | | 20180101 | +----------+ 6 rows in set (0.00 sec) mysql>
與列的別名類似，我們也可以為表來定義別名，格式與定義列的別名一致，都是用空白字元或者AS隔開，這個在表名特別長的情況下可以讓語句表達更清晰一些，比如這樣：
mysql> SELECT s1.number, s1.name, s1.major, s2.subject, s2.score FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number; +----------+-----------+--------------------------+-----------------------------+-------+ | number | name | major | subject | score | +----------+-----------+--------------------------+-----------------------------+-------+ | 20180101 | 杜子騰 | 電腦科學與工程 | 母豬的產後護理 | 78 | | 20180101 | 杜子騰 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 88 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 母豬的產後護理 | 100 | | 20180102 | 杜琦燕 | 電腦科學與工程 | 論薩達姆的戰爭準備 | 98 | | 20180103 | 範統 | 軟體工程 | 母豬的產後護理 | 59 | | 20180103 | 範統 | 軟體工程 | 論薩達姆的戰爭準備 | 61 | | 20180104 | 史珍香 | 軟體工程 | 母豬的產後護理 | 55 | | 20180104 | 史珍香 | 軟體工程 | 論薩達姆的戰爭準備 | 46 | +----------+-----------+--------------------------+-----------------------------+-------+ 8 rows in set (0.00 sec) mysql>
這個例子中，我們在FROM子句中給student_info定義了一個別名s1，student_score定義了一個別名s2，那麼在整個查詢語句的其他地方就可以參照這個別名來替代該表本身的名字了。
自連線
我們上邊說的都是多個不同的表之間的連線，其實同一個表也可以進行連線。比方說我們可以對兩個t1表來生成笛卡爾積，就像這樣：
mysql> SELECT * FROM t1, t1; ERROR 1066 (42000): Not unique table/alias: 't1' mysql>
咦，報了個錯，這是因為設計MySQL的大叔不允許FROM子句中出現相同的表名。我們這裡需要的是兩張一模一樣的t1表進行連線，為了把兩個一樣的表區分一下，需要為表定義別名。比如這樣：
mysql> SELECT * FROM t1 AS table1, t1 AS table2; +------+------+------+------+ | m1 | n1 | m1 | n1 | +------+------+------+------+ | 1 | a | 1 | a | | 2 | b | 1 | a | | 3 | c | 1 | a | | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | +------+------+------+------+ 9 rows in set (0.00 sec) mysql>
這裡相當於我們為t1表定義了兩個副本，一個是table1，另一個是table2，這裡的連線過程就不贅述了，大家把它們認為是不同的表就好了。由於被連線的表其實是源自同一個表，所以這種連線也稱為自連線。我們看一下這個自連線的現實意義，比方說我們想檢視與'史珍香'相同專業的學生有哪些，可以這麼寫：
mysql> SELECT s2.number, s2.name, s2.major FROM student_info AS s1 INNER JOIN student_info AS s2 WHERE s1.major = s2.major AND s1.name = '史珍香' ; +----------+-----------+--------------+ | number | name | major | +----------+-----------+--------------+ | 20180103 | 範統 | 軟體工程 | | 20180104 | 史珍香 | 軟體工程 | +----------+-----------+--------------+ 2 rows in set (0.01 sec) mysql>
s1、s2都可以看作是student_info表的一份副本，我們可以這樣理解這個查詢：
根據
s1.name = '史珍香'搜尋條件過濾s1表，可以得到該同學的基本資訊：+----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | number | name | sex | id_number | department | major | enrollment_time | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | 20180104 | 史珍香 | 女 | 141992199701078600 | 計算機學院 | 軟體工程 | 2018-09-01 | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+
因為通過查詢
s1表，得到了'史珍香'所在的專業其實是'軟體工程'，接下來就應該查詢s2表了，查詢s2表的時候的過濾條件s1.major = s2.major就相當於s2.major = '軟體工程'，於是查詢到2條記錄：+----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | number | name | sex | id_number | department | major | enrollment_time | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | 20180103 | 範統 | 男 | 17156319980116959X | 計算機學院 | 軟體工程 | 2018-09-01 | | 20180104 | 史珍香 | 女 | 141992199701078600 | 計算機學院 | 軟體工程 | 2018-09-01 | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+
而我們只需要
s2表的number、name、major這3個列的資料，所以最終的結果就長這樣：+----------+-----------+--------------+ | number | name | major | +----------+-----------+--------------+ | 20180103 | 範統 | 軟體工程 | | 20180104 | 史珍香 | 軟體工程 | +----------+-----------+--------------+
連線查詢與子查詢的轉換
有的查詢需求既可以使用連線查詢解決，也可以使用子查詢解決，比如
SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '電腦科學與工程');
這個子查詢就可以被替換：
SELECT s2.* FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.major = '電腦科學與工程';
大家在實際使用時可以按照自己的習慣來書寫查詢語句。
小貼士： MySQL伺服器在內部可能將子查詢轉換為連線查詢來處理，當然也可能用別的方式來處理，不過對於我們剛入門的小白來說，這些都不重要，知道這個語句會把哪些資訊查出來就好了！
推薦學習：
以上就是一起聊聊MySQL基礎之連線查詢的詳細內容，更多請關注TW511.COM其它相關文章！