本篇文章給大家?guī)?lái)了關(guān)于mysql中連接查詢(xún)的相關(guān)知識(shí)，其中包括內(nèi)連接、外連接、多表連接以及子查詢(xún)的相關(guān)問(wèn)題，希望對(duì)大家有幫助。

再次認(rèn)識(shí)關(guān)系表

我們之前一直使用student_info和student_score兩個(gè)表來(lái)分別存儲(chǔ)學(xué)生的基本信息和學(xué)生的成績(jī)信息，其實(shí)合并成一張表也不是不可以，假設(shè)將兩張表合并后的新表名稱(chēng)為student_merge，那它應(yīng)該長(zhǎng)這樣：

student_merge表

有了這個(gè)合并后的表，我們就可以在一個(gè)查詢(xún)語(yǔ)句中既查詢(xún)到學(xué)生的基本信息，也查詢(xún)到學(xué)生的成績(jī)信息，比如這個(gè)查詢(xún)語(yǔ)句：

SELECT number, name, major, subject, score FROM student_merge;

其中查詢(xún)列表處的name和major屬于學(xué)生的基本信息，subject和score屬于學(xué)生的成績(jī)信息，而number既屬于成績(jī)信息也屬于基本信息，我們可以在一個(gè)對(duì)student_merge表的查詢(xún)語(yǔ)句中很輕松的把這些信息都查詢(xún)出來(lái)。但是別忘了一個(gè)學(xué)生可能會(huì)有很多門(mén)學(xué)科的成績(jī)信息，也就是說(shuō)每當(dāng)我們想為一個(gè)學(xué)生增加一門(mén)學(xué)科的成績(jī)信息時(shí)，我們必須把他的基本信息再抄一遍，這種同一個(gè)學(xué)生的基本信息被冗余存儲(chǔ)會(huì)帶來(lái)下邊的問(wèn)題：

• 問(wèn)題一：浪費(fèi)存儲(chǔ)空間。

• 問(wèn)題二：當(dāng)修改某個(gè)學(xué)生的基本信息時(shí)必須修改多處，很容易造成信息的不一致，增大維護(hù)的困難。

所以為了盡可能少的存儲(chǔ)冗余信息，一開(kāi)始我們就把這個(gè)所謂的student_merge表拆分成了student_info和student_score表，但是這兩張表之間有某種關(guān)系作為紐帶，這里的某種關(guān)系指的就是兩個(gè)表都擁有的number列。

連接的概念

拆分之后的表的確解決了數(shù)據(jù)冗余問(wèn)題，但是查詢(xún)數(shù)據(jù)卻成了一個(gè)問(wèn)題。截至目前為止，在我們介紹的查詢(xún)方式中，查詢(xún)結(jié)果集只能是一個(gè)表中的一個(gè)列或者多個(gè)列，也就是說(shuō)到目前為止還沒(méi)有一種可以在一條查詢(xún)語(yǔ)句中把某個(gè)學(xué)生的number、name、major、subject、score這幾個(gè)信息都查詢(xún)出來(lái)的方式。

小貼士： 雖然我們前邊介紹的子查詢(xún)可以在一個(gè)查詢(xún)語(yǔ)句中涉及到多個(gè)表，但是整個(gè)查詢(xún)語(yǔ)句最終產(chǎn)生的結(jié)果集還是用來(lái)展示外層查詢(xún)的結(jié)果，子查詢(xún)的結(jié)果只是被當(dāng)作中間結(jié)果來(lái)使用。

時(shí)代在召喚一種可以在一個(gè)查詢(xún)語(yǔ)句結(jié)果集中展示多個(gè)表的信息的方式，連接查詢(xún)承擔(dān)了這個(gè)艱巨的歷史使命。當(dāng)然，為了故事的順利發(fā)展，我們先建立兩個(gè)簡(jiǎn)單的表并給它們填充一點(diǎn)數(shù)據(jù)：

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兩個(gè)表，這兩個(gè)表都有兩個(gè)列，一個(gè)是INT類(lèi)型的，一個(gè)是CHAR(1)類(lèi)型的，填充好數(shù)據(jù)的兩個(gè)表長(zhǎng)這樣：

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>

連接的本質(zhì)就是把各個(gè)表中的記錄都取出來(lái)依次匹配的組合加入結(jié)果集并返回給用戶(hù)。我們把t1和t2兩個(gè)表連接起來(lái)的過(guò)程如下圖所示：

這個(gè)過(guò)程看起來(lái)就是把t1表的記錄和t2表的記錄連起來(lái)組成新的更大的記錄，所以這個(gè)查詢(xún)過(guò)程稱(chēng)之為連接查詢(xún)。連接查詢(xún)的結(jié)果集中包含一個(gè)表中的每一條記錄與另一個(gè)表中的每一條記錄相互匹配的組合，像這樣的結(jié)果集就可以稱(chēng)之為笛卡爾積。因?yàn)楸?code>t1中有3條記錄，表t2中也有3條記錄，所以這兩個(gè)表連接之后的笛卡爾積就有3×3=9行記錄。在MySQL中，連接查詢(xún)的語(yǔ)法也很隨意，只要在FROM語(yǔ)句后邊跟多個(gè)用逗號(hào),隔開(kāi)的表名就好了，比如我們把t1表和t2表連接起來(lái)的查詢(xún)語(yǔ)句可以寫(xiě)成這樣：

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)

查詢(xún)列表處的*代表從FROM語(yǔ)句后列出的表中選取每個(gè)列，上邊的查詢(xún)語(yǔ)句其實(shí)和下邊這幾種寫(xiě)法都是等價(jià)的：

• 寫(xiě)法一：

  SELECT t1.m1, t1.n1, t2.m2, t2.n2 FROM t1, t2;

  這種寫(xiě)法是將t1、t2表中的列名都顯式的寫(xiě)出來(lái)，也就是使用了列的全限定名。

• 寫(xiě)法二：

  SELECT m1, n1, m2, n2 FROM t1, t2;

  由于t1、t2表中的列名并不重復(fù)，所以沒(méi)有可能讓服務(wù)器懵逼的二義性，在查詢(xún)列表上直接使用列名也是可以的。

• 寫(xiě)法三：

  SELECT t1.*, t2.* FROM t1, t2;

  這種寫(xiě)法意思就是查詢(xún)t1表的全部的列，t2表的全部的列。

連接過(guò)程簡(jiǎn)介

如果我們樂(lè)意，我們可以連接任意數(shù)量張表，但是如果沒(méi)有任何限制條件的話(huà)，這些表連接起來(lái)產(chǎn)生的笛卡爾積可能是非常巨大的。比方說(shuō)3個(gè)100行記錄的表連接起來(lái)產(chǎn)生的笛卡爾積就有100×100×100=1000000行數(shù)據(jù)！所以在連接的時(shí)候過(guò)濾掉特定記錄組合是有必要的，在連接查詢(xún)中的過(guò)濾條件可以分成兩種：

• 涉及單表的條件

  這種只涉及單表的過(guò)濾條件我們之前都提到過(guò)一萬(wàn)遍了，我們之前也一直稱(chēng)為搜索條件，比如t1.m1 > 1是只針對(duì)t1表的過(guò)濾條件，t2.n2 < 'd'是只針對(duì)t2表的過(guò)濾條件。

• 涉及兩表的條件

  這種過(guò)濾條件我們之前沒(méi)見(jiàn)過(guò)，比如t1.m1 = t2.m2、t1.n1 > t2.n2等，這些條件中涉及到了兩個(gè)表，我們稍后會(huì)仔細(xì)分析這種過(guò)濾條件是如何使用的哈。

下邊我們就要看一下攜帶過(guò)濾條件的連接查詢(xún)的大致執(zhí)行過(guò)程了，比方說(shuō)下邊這個(gè)查詢(xún)語(yǔ)句：

SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd';

在這個(gè)查詢(xún)中我們指明了這三個(gè)過(guò)濾條件：

• t1.m1 > 1

• t1.m1 = t2.m2

• t2.n2 < 'd'

那么這個(gè)連接查詢(xún)的大致執(zhí)行過(guò)程如下：

1. 首先確定第一個(gè)需要查詢(xún)的表，這個(gè)表稱(chēng)之為驅(qū)動(dòng)表。此處假設(shè)使用t1作為驅(qū)動(dòng)表，那么就需要到t1表中找滿(mǎn)足t1.m1 > 1的記錄，符合這個(gè)條件的t1表記錄如下所示：

   +------+------+ | m1   | n1   | +------+------+ |    2 | b    | |    3 | c    | +------+------+ 2 rows in set (0.01 sec)

   我們可以看到，t1表中符合t1.m1 > 1的記錄有兩條。

2. 上一步驟中從驅(qū)動(dòng)表每獲取到一條記錄，都需要到t2表中查找匹配的記錄，所謂匹配的記錄，指的是符合過(guò)濾條件的記錄。因?yàn)槭歉鶕?jù)t1表中的記錄去找t2表中的記錄，所以t2表也可以被稱(chēng)之為被驅(qū)動(dòng)表。上一步驟從驅(qū)動(dòng)表中得到了2條記錄，也就意味著需要查詢(xún)2次t2表。此時(shí)涉及兩個(gè)表的列的過(guò)濾條件t1.m1 = t2.m2就派上用場(chǎng)了：

   • 對(duì)于從t1表種查詢(xún)得到的第一條記錄，也就是當(dāng)t1.m1 = 2, t1.n1 = 'b'時(shí)，過(guò)濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 2，所以此時(shí)t2表相當(dāng)于有了t2.m2 = 2、t2.n2 < 'd'這兩個(gè)過(guò)濾條件，然后到t2表中執(zhí)行單表查詢(xún)，將得到的記錄和從t1表中查詢(xún)得到的第一條記錄相組合得到下邊的結(jié)果：

     +------+------+------+------+ | m1   | n1   | m2   | n2   | +------+------+------+------+ |    2 | b    |    2 | b    | +------+------+------+------+

   • 對(duì)于從t1表種查詢(xún)得到的第二條記錄，也就是當(dāng)t1.m1 = 3, t1.n1 = 'c'時(shí)，過(guò)濾條件t1.m1 = t2.m2就相當(dāng)于t2.m2 = 3，所以此時(shí)t2表相當(dāng)于有了t2.m2 = 3、t2.n2 < 'd'這兩個(gè)過(guò)濾條件，然后到t2表中執(zhí)行單表查詢(xún)，將得到的記錄和從t1表中查詢(xún)得到的第二條記錄相組合得到下邊的結(jié)果：

     +------+------+------+------+ | m1   | n1   | m2   | n2   | +------+------+------+------+ |    3 | c    |    3 | c    | +------+------+------+------+

   所以整個(gè)連接查詢(xún)的執(zhí)行最后得到的結(jié)果集就是這樣：

   +------+------+------+------+ | m1   | n1   | m2   | n2   | +------+------+------+------+ |    2 | b    |    2 | b    | |    3 | c    |    3 | c    | +------+------+------+------+ 2 rows in set (0.00 sec)

從上邊兩個(gè)步驟可以看出來(lái)，我們上邊嘮叨的這個(gè)兩表連接查詢(xún)共需要查詢(xún)1次t1表，2次t2表。當(dāng)然這是在特定的過(guò)濾條件下的結(jié)果，如果我們把t1.m1 > 1這個(gè)條件去掉，那么從t1表中查出的記錄就有3條，就需要查詢(xún)3次t2表了。也就是說(shuō)在兩表連接查詢(xún)中，驅(qū)動(dòng)表只需要查詢(xún)一次，被驅(qū)動(dòng)表可能會(huì)被查詢(xún)多次。

內(nèi)連接和外連接

了解了連接查詢(xún)的執(zhí)行過(guò)程之后，視角再回到我們的student_info表和student_score表?，F(xiàn)在我們想在一個(gè)查詢(xún)語(yǔ)句中既查詢(xún)到學(xué)生的基本信息，也查詢(xún)到學(xué)生的成績(jī)信息，就需要進(jìn)行兩表連接了。連接過(guò)程就是從student_info表中取出記錄，在student_score表中查找number值相同的成績(jī)記錄，所以過(guò)濾條件就是student_info.number = student_score.number，整個(gè)查詢(xún)語(yǔ)句就是這樣：

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 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |    78 | | 20180101 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    88 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |   100 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    98 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    59 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    61 | | 20180104 | 史珍香    | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    55 | | 20180104 | 史珍香    | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    46 | +----------+-----------+--------------------------+-----------------------------+-------+ 8 rows in set (0.00 sec)  mysql>

小貼士： student_info表和student_score表都有number列，不過(guò)我們?cè)谏鲜霾樵?xún)語(yǔ)句的查詢(xún)列表中只放置了student_info表的number列，這是因?yàn)槲覀兊倪^(guò)濾條件是student_info.number = student_score.number，從兩個(gè)表中取出的記錄的number列都相同，所以只需要放置一個(gè)表中的number列到查詢(xún)列表即可，也就是說(shuō)我們把student_score.number放到查詢(xún)列表處也是可以滴～

從上述查詢(xún)結(jié)果中我們可以看到，各個(gè)同學(xué)對(duì)應(yīng)的各科成績(jī)就都被查出來(lái)了，可是有個(gè)問(wèn)題，范劍和朱逸群同學(xué)，也就是學(xué)號(hào)為20180105和20180106的同學(xué)因?yàn)槟承┰驔](méi)有參加考試，所以在studnet_score表中沒(méi)有對(duì)應(yīng)的成績(jī)記錄。那如果老師想查看所有同學(xué)的考試成績(jī)，即使是缺考的同學(xué)也應(yīng)該展示出來(lái)，但是到目前為止我們介紹的連接查詢(xún)是無(wú)法完成這樣的需求的。我們稍微思考一下這個(gè)需求，其本質(zhì)是想：驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中沒(méi)有匹配的記錄，也仍然需要加入到結(jié)果集。為了解決這個(gè)問(wèn)題，就有了內(nèi)連接和外連接的概念：

• 對(duì)于內(nèi)連接的兩個(gè)表，驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表中找不到匹配的記錄，該記錄不會(huì)加入到最后的結(jié)果集，我們上邊提到的連接都是所謂的內(nèi)連接。

• 對(duì)于外連接的兩個(gè)表，驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中沒(méi)有匹配的記錄，也仍然需要加入到結(jié)果集。

  在MySQL中，根據(jù)選取驅(qū)動(dòng)表的不同，外連接仍然可以細(xì)分為2種：

  • 左外連接

    選取左側(cè)的表為驅(qū)動(dòng)表。

  • 右外連接

    選取右側(cè)的表為驅(qū)動(dòng)表。

可是這樣仍然存在問(wèn)題，即使對(duì)于外連接來(lái)說(shuō)，有時(shí)候我們也并不想把驅(qū)動(dòng)表的全部記錄都加入到最后的結(jié)果集。這就犯難了，有時(shí)候匹配失敗要加入結(jié)果集，有時(shí)候又不要加入結(jié)果集，這咋辦，有點(diǎn)兒愁啊。。。噫，把過(guò)濾條件分為兩種不就解決了這個(gè)問(wèn)題了么，所以放在不同地方的過(guò)濾條件是有不同語(yǔ)義的：

• WHERE子句中的過(guò)濾條件

  WHERE子句中的過(guò)濾條件就是我們平時(shí)見(jiàn)的那種，不論是內(nèi)連接還是外連接，凡是不符合WHERE子句中的過(guò)濾條件的記錄都不會(huì)被加入最后的結(jié)果集。

• ON子句中的過(guò)濾條件

  對(duì)于外連接的驅(qū)動(dòng)表的記錄來(lái)說(shuō)，如果無(wú)法在被驅(qū)動(dòng)表中找到匹配ON子句中的過(guò)濾條件的記錄，那么該記錄仍然會(huì)被加入到結(jié)果集中，對(duì)應(yīng)的被驅(qū)動(dòng)表記錄的各個(gè)字段使用NULL值填充。

  需要注意的是，這個(gè)ON子句是專(zhuān)門(mén)為外連接驅(qū)動(dòng)表中的記錄在被驅(qū)動(dòng)表找不到匹配記錄時(shí)應(yīng)不應(yīng)該把該記錄加入結(jié)果集這個(gè)場(chǎng)景下提出的，所以如果把ON子句放到內(nèi)連接中，MySQL會(huì)把它和WHERE子句一樣對(duì)待，也就是說(shuō)：內(nèi)連接中的WHERE子句和ON子句是等價(jià)的。

一般情況下，我們都把只涉及單表的過(guò)濾條件放到WHERE子句中，把涉及兩表的過(guò)濾條件都放到ON子句中，我們也一般把放到ON子句中的過(guò)濾條件也稱(chēng)之為連接條件。

小貼士： 左外連接和右外連接簡(jiǎn)稱(chēng)左連接和右連接，所以下邊提到的左外連接和右外連接中的`外`字都用括號(hào)擴(kuò)起來(lái)，以表示這個(gè)字兒可有可無(wú)。

左（外）連接的語(yǔ)法

左（外）連接的語(yǔ)法還是挺簡(jiǎn)單的，比如我們要把t1表和t2表進(jìn)行左外連接查詢(xún)可以這么寫(xiě)：

SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON 連接條件 [WHERE 普通過(guò)濾條件];

其中中括號(hào)里的OUTER單詞是可以省略的。對(duì)于LEFT JOIN類(lèi)型的連接來(lái)說(shuō)，我們把放在左邊的表稱(chēng)之為外表或者驅(qū)動(dòng)表，右邊的表稱(chēng)之為內(nèi)表或者被驅(qū)動(dòng)表。所以上述例子中t1就是外表或者驅(qū)動(dòng)表，t2就是內(nèi)表或者被驅(qū)動(dòng)表。需要注意的是，對(duì)于左（外）連接和右（外）連接來(lái)說(shuō)，必須使用ON子句來(lái)指出連接條件。了解了左（外）連接的基本語(yǔ)法之后，再次回到我們上邊那個(gè)現(xiàn)實(shí)問(wèn)題中來(lái)，看看怎樣寫(xiě)查詢(xún)語(yǔ)句才能把所有的學(xué)生的成績(jī)信息都查詢(xún)出來(lái)，即使是缺考的考生也應(yīng)該被放到結(jié)果集中：

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 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |    78 | | 20180101 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    88 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |   100 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    98 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    59 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    61 | | 20180104 | 史珍香    | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    55 | | 20180104 | 史珍香    | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    46 | | 20180105 | 范劍      | 飛行器設(shè)計(jì)               | NULL                        |  NULL | | 20180106 | 朱逸群    | 電子信息                 | NULL                        |  NULL | +----------+-----------+--------------------------+-----------------------------+-------+ 10 rows in set (0.00 sec)  mysql>

從結(jié)果集中可以看出來(lái)，雖然范劍和朱逸群并沒(méi)有對(duì)應(yīng)的成績(jī)記錄，但是由于采用的是連接類(lèi)型為左（外）連接，所以仍然把它放到了結(jié)果集中，只不過(guò)在對(duì)應(yīng)的成績(jī)記錄的各列使用NULL值填充而已。

右（外）連接的語(yǔ)法

右（外）連接和左（外）連接的原理是一樣一樣的，語(yǔ)法也只是把LEFT換成RIGHT而已：

SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON 連接條件 [WHERE 普通過(guò)濾條件];

只不過(guò)驅(qū)動(dòng)表是右邊的表，被驅(qū)動(dòng)表是左邊的表，具體就不嘮叨了。

內(nèi)連接的語(yǔ)法

內(nèi)連接和外連接的根本區(qū)別就是在驅(qū)動(dòng)表中的記錄不符合ON子句中的連接條件時(shí)不會(huì)把該記錄加入到最后的結(jié)果集，我們最開(kāi)始嘮叨的那些連接查詢(xún)的類(lèi)型都是內(nèi)連接。不過(guò)之前僅僅提到了一種最簡(jiǎn)單的內(nèi)連接語(yǔ)法，就是直接把需要連接的多個(gè)表都放到FROM子句后邊。其實(shí)針對(duì)內(nèi)連接，MySQL提供了好多不同的語(yǔ)法，我們以t1和t2表為例瞅瞅：

SELECT * FROM t1 [INNER | CROSS] JOIN t2 [ON 連接條件] [WHERE 普通過(guò)濾條件];

也就是說(shuō)在MySQL中，下邊這幾種內(nèi)連接的寫(xiě)法都是等價(jià)的：

• SELECT * FROM t1 JOIN t2;

• SELECT * FROM t1 INNER JOIN t2;

• SELECT * FROM t1 CROSS JOIN t2;

上邊的這些寫(xiě)法和直接把需要連接的表名放到FROM語(yǔ)句之后，用逗號(hào),分隔開(kāi)的寫(xiě)法是等價(jià)的：

 SELECT * FROM t1, t2;

現(xiàn)在我們雖然介紹了很多種內(nèi)連接的書(shū)寫(xiě)方式，不過(guò)熟悉一種就好了，這里我們推薦INNER JOIN的形式書(shū)寫(xiě)內(nèi)連接（因?yàn)镮NNER JOIN語(yǔ)義很明確嘛，可以和LEFT JOIN和RIGHT JOIN很輕松的區(qū)分開(kāi)）。這里需要注意的是，由于在內(nèi)連接中ON子句和WHERE子句是等價(jià)的，所以?xún)?nèi)連接中不要求強(qiáng)制寫(xiě)明ON子句。

我們前邊說(shuō)過(guò)，連接的本質(zhì)就是把各個(gè)連接表中的記錄都取出來(lái)依次匹配的組合加入結(jié)果集并返回給用戶(hù)。不論哪個(gè)表作為驅(qū)動(dòng)表，兩表連接產(chǎn)生的笛卡爾積肯定是一樣的。而對(duì)于內(nèi)連接來(lái)說(shuō)，由于凡是不符合ON子句或WHERE子句中的條件的記錄都會(huì)被過(guò)濾掉，其實(shí)也就相當(dāng)于從兩表連接的笛卡爾積中把不符合過(guò)濾條件的記錄給踢出去，所以對(duì)于內(nèi)連接來(lái)說(shuō)，驅(qū)動(dòng)表和被驅(qū)動(dòng)表是可以互換的，并不會(huì)影響最后的查詢(xún)結(jié)果。但是對(duì)于外連接來(lái)說(shuō)，由于驅(qū)動(dòng)表中的記錄即使在被驅(qū)動(dòng)表中找不到符合ON子句連接條件的記錄也會(huì)被加入結(jié)果集，所以此時(shí)驅(qū)動(dòng)表和被驅(qū)動(dòng)表的關(guān)系就很重要了，也就是說(shuō)左外連接和右外連接的驅(qū)動(dòng)表和被驅(qū)動(dòng)表不能輕易互換。

小結(jié)

上邊說(shuō)了很多，給大家的感覺(jué)不是很直觀(guān)，我們直接把表t1和t2的三種連接方式寫(xiě)在一起，這樣大家理解起來(lái)就很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)

連接查詢(xún)產(chǎn)生的結(jié)果集就好像把散布到兩個(gè)表中的信息被重新粘貼到了一個(gè)表，這個(gè)粘貼后的結(jié)果集可以方便我們分析數(shù)據(jù)，就不用老是兩個(gè)表對(duì)照的看了。

多表連接

上邊說(shuō)過(guò)，如果我們樂(lè)意的話(huà)可以連接任意數(shù)量的表，我們?cè)賮?lái)創(chuàng)建一個(gè)簡(jiǎn)單的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表的結(jié)構(gòu)一樣，也是一個(gè)INT列，一個(gè)CHAR(1)列，現(xiàn)在我們看一下把這3個(gè)表連起來(lái)的樣子：

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>

其實(shí)上邊的查詢(xún)語(yǔ)句也可以寫(xiě)成這樣，用哪個(gè)取決于你的心情：

SELECT * FROM t1 INNER JOIN t2 ON t1.m1 = t2.m2 INNER JOIN t3 ON t1.m1 = t3.m3;

這個(gè)查詢(xún)的執(zhí)行過(guò)程用偽代碼表示一下就是這樣：

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

其實(shí)不管是多少個(gè)表的連接，本質(zhì)上就是各個(gè)表的記錄在符合過(guò)濾條件下的自由組合。

表的別名

我們前邊曾經(jīng)為列命名過(guò)別名，比如說(shuō)這樣：

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>

與列的別名類(lèi)似，我們也可以為表來(lái)定義別名，格式與定義列的別名一致，都是用空白字符或者AS隔開(kāi)，這個(gè)在表名特別長(zhǎng)的情況下可以讓語(yǔ)句表達(dá)更清晰一些，比如這樣：

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 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |    78 | | 20180101 | 杜子騰    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    88 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 母豬的產(chǎn)后護(hù)理              |   100 | | 20180102 | 杜琦燕    | 計(jì)算機(jī)科學(xué)與工程         | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    98 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    59 | | 20180103 | 范統(tǒng)      | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    61 | | 20180104 | 史珍香    | 軟件工程                 | 母豬的產(chǎn)后護(hù)理              |    55 | | 20180104 | 史珍香    | 軟件工程                 | 論薩達(dá)姆的戰(zhàn)爭(zhēng)準(zhǔn)備          |    46 | +----------+-----------+--------------------------+-----------------------------+-------+ 8 rows in set (0.00 sec)  mysql>

這個(gè)例子中，我們?cè)?code>FROM子句中給student_info定義了一個(gè)別名s1，student_score定義了一個(gè)別名s2，那么在整個(gè)查詢(xún)語(yǔ)句的其他地方就可以引用這個(gè)別名來(lái)替代該表本身的名字了。

自連接

我們上邊說(shuō)的都是多個(gè)不同的表之間的連接，其實(shí)同一個(gè)表也可以進(jìn)行連接。比方說(shuō)我們可以對(duì)兩個(gè)t1表來(lái)生成笛卡爾積，就像這樣：

mysql> SELECT * FROM t1, t1; ERROR 1066 (42000): Not unique table/alias: 't1' mysql>

咦，報(bào)了個(gè)錯(cuò)，這是因?yàn)樵O(shè)計(jì)MySQL的大叔不允許FROM子句中出現(xiàn)相同的表名。我們這里需要的是兩張一模一樣的t1表進(jìn)行連接，為了把兩個(gè)一樣的表區(qū)分一下，需要為表定義別名。比如這樣：

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>

這里相當(dāng)于我們?yōu)?code>t1表定義了兩個(gè)副本，一個(gè)是table1，另一個(gè)是table2，這里的連接過(guò)程就不贅述了，大家把它們認(rèn)為是不同的表就好了。由于被連接的表其實(shí)是源自同一個(gè)表，所以這種連接也稱(chēng)為自連接。我們看一下這個(gè)自連接的現(xiàn)實(shí)意義，比方說(shuō)我們想查看與'史珍香'相同專(zhuān)業(yè)的學(xué)生有哪些，可以這么寫(xiě)：

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 | 范統(tǒng)      | 軟件工程     | | 20180104 | 史珍香    | 軟件工程     | +----------+-----------+--------------+ 2 rows in set (0.01 sec)  mysql>

s1、s2都可以看作是student_info表的一份副本，我們可以這樣理解這個(gè)查詢(xún)：

• 根據(jù)s1.name = '史珍香'搜索條件過(guò)濾s1表，可以得到該同學(xué)的基本信息：

  +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | number   | name      | sex  | id_number          | department      | major        | enrollment_time | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | 20180104 | 史珍香    | 女   | 141992199701078600 | 計(jì)算機(jī)學(xué)院      | 軟件工程     | 2018-09-01      | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+

• 因?yàn)橥ㄟ^(guò)查詢(xún)s1表，得到了'史珍香'所在的專(zhuān)業(yè)其實(shí)是'軟件工程'，接下來(lái)就應(yīng)該查詢(xún)s2表了，查詢(xún)s2表的時(shí)候的過(guò)濾條件s1.major = s2.major就相當(dāng)于s2.major = '軟件工程'，于是查詢(xún)到2條記錄：

  +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | number   | name      | sex  | id_number          | department      | major        | enrollment_time | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+ | 20180103 | 范統(tǒng)      | 男   | 17156319980116959X | 計(jì)算機(jī)學(xué)院      | 軟件工程     | 2018-09-01      | | 20180104 | 史珍香    | 女   | 141992199701078600 | 計(jì)算機(jī)學(xué)院      | 軟件工程     | 2018-09-01      | +----------+-----------+------+--------------------+-----------------+--------------+-----------------+

  而我們只需要s2表的number、name、major這3個(gè)列的數(shù)據(jù)，所以最終的結(jié)果就長(zhǎng)這樣：

  +----------+-----------+--------------+ | number   | name      | major        | +----------+-----------+--------------+ | 20180103 | 范統(tǒng)      | 軟件工程     | | 20180104 | 史珍香    | 軟件工程     | +----------+-----------+--------------+

連接查詢(xún)與子查詢(xún)的轉(zhuǎn)換

有的查詢(xún)需求既可以使用連接查詢(xún)解決，也可以使用子查詢(xún)解決，比如

SELECT * FROM student_score WHERE number IN (SELECT number FROM student_info WHERE major = '計(jì)算機(jī)科學(xué)與工程');

這個(gè)子查詢(xún)就可以被替換：

SELECT s2.* FROM student_info AS s1 INNER JOIN student_score AS s2 WHERE s1.number = s2.number AND s1.major = '計(jì)算機(jī)科學(xué)與工程';

大家在實(shí)際使用時(shí)可以按照自己的習(xí)慣來(lái)書(shū)寫(xiě)查詢(xún)語(yǔ)句。

小貼士： MySQL服務(wù)器在內(nèi)部可能將子查詢(xún)轉(zhuǎn)換為連接查詢(xún)來(lái)處理，當(dāng)然也可能用別的方式來(lái)處理，不過(guò)對(duì)于我們剛?cè)腴T(mén)的小白來(lái)說(shuō)，這些都不重要，知道這個(gè)語(yǔ)句會(huì)把哪些信息查出來(lái)就好了！

推薦學(xué)習(xí)：mysql視頻教程