MySQL — 關聯(lián)

來自MySQL的學習筆記，寫的不對的地方大家多多指教哦

專注于為中小企業(yè)提供網站制作、網站設計服務,電腦端+手機端+微信端的三站合一,更高效的管理,為中小企業(yè)平昌免費做網站提供優(yōu)質的服務。我們立足成都，凝聚了一批互聯(lián)網行業(yè)人才，有力地推動了近千家企業(yè)的穩(wěn)健成長，幫助中小企業(yè)通過網站建設實現(xiàn)規(guī)模擴充和轉變。

什么是外鍵？

假設有 2 個表，分別是表 A 和表 B，它們通過一個公共字段“id”發(fā)生關聯(lián)關系，我們把這個關聯(lián)關系叫做 R。如果“id”在表 A 中是主鍵，那么，表 A 就是這個關系 R 中的主表。相應的，表 B 就是這個關系中的從表，表 B 中的“id”，就是表 B 用來引用表 A 中數(shù)據的，叫外鍵。所以，外鍵就是從表中用來引用主表中數(shù)據的那個公共字段。

語法結構：

在創(chuàng)建表時添加外鍵約束：

在修改表時定義外鍵約束：

例子1：創(chuàng)建表時添加外鍵約束

首先創(chuàng)建主表：importhead

創(chuàng)建從表：test_mysql.importdetails

查詢外鍵約束的相關信息：

查詢結果為：

例子2：修改表時定義外鍵約束

修改表時定義從表test_mysql.importdetails的外鍵約束

刪除外鍵約束使用DROP，語法結構為：

例子：刪除從表test_mysql.importdetails的外鍵約束

在 MySQL 中，有 2 種類型的連接，分別是內連接（INNER JOIN）和外連接（OUTER JOIN）。

在 MySQL 里面，關鍵字 JOIN、INNER JOIN、CROSS JOIN 的含義是一樣的，都表示內連接。我們可以通過 JOIN 把兩個表關聯(lián)起來，來查詢兩個表中的數(shù)據。

例子：有一張銷售表，如下圖：

有一張會員信息表，如下圖：

通過內連接，查詢會員的銷售記錄：

運行語句，結果如下：

根據上面的結果，其實可以得知：內連接查詢到結果集為兩個表的交集部分。

跟內連接只返回符合連接條件的記錄不同的是，外連接還可以返回表中的所有記錄，它包括兩類，分別是左連接和右連接。

例子1：左外連接

如果需要查詢所有銷售記錄，則可以使用左外連接

運行語句，結果為：

從上面的結果可以得知，LEFT JOIN左邊的表會返回全部記錄，而右邊的表只返回符合連接條件的記錄

例子2：右外連接：

運行語句，結果為：

從上面的結果可以得知，RIGHT JOIN右邊的表會返回全部記錄，而左邊的表只返回符合連接條件的記錄

MySQL中的交叉連接、內連接、外連接

學生student表：

課程course表：

交叉連接：

結果為兩張表的笛卡爾積：

內連接（inner join ，inner 可以省略）

顯示內連接和隱示內連接獲得的查詢結果是一樣的，都是A表和B表的交集（例：A.id = B.id），但是只能查到有關系的信息，如果A表的一條數(shù)據的與B表關聯(lián)的字段沒有對應的信息（如：A.id = null），即：這條數(shù)據在B表中沒有對應的信息，則無法獲得。

外連接（outer join，outer可以省略）

左外連接獲得的查詢結果是左邊的表A的全部信息和A，B兩張表的交集，左邊A表的全部包含A表中在B表中沒有對應關系的信息。

右外連接獲得的查詢結果是右邊的表B的全部信息和A，B兩張表的交集，右邊B表的全部包含B表中在A表中沒有對應關系的信息。

mysql連接方式

左連接：返回包括左表中的所有記錄和右表中聯(lián)結字段相等的記錄;

右連接：返回包括右表中的所有記錄和左表中聯(lián)結字段相等的記錄;

全連接：返回兩個連接表的所有記錄；mysql沒有全連接

內連接：返回兩個連接表連接字段相同的記錄；

union：對兩個結果集進行并集，并去重，當交換兩個select語句順序后，最終結果與不交換時無區(qū)別；

union all：對兩個結果集進行并集，不去重，當交換兩個select語句順序后，最終結果與不交換時有區(qū)別；

舉個例子 有一個學生表 班級 姓名 一個成績表 姓名 成績 我們需要返回X班的所有學生的成績，但是班上有人缺考，也就是成績表里沒有姓名,我們先用on得到的就是有考試成績的名字，通過外連接，我們就可以得到全班人的名字以及成績。

MySQL 中的各種 JOIN

本文主要介紹 SQL 標準中定義的各種連接的意義和區(qū)別，例如，交叉連接（ CROSS JOIN ）、內連接（ INNER JOIN ）、外連接（ OUTER JOIN ）、自然連接（ NATURAL JOIN ）等，并結合例子講解這些連接在 MySQL 中的語法和表現(xiàn)。

從網上的資料看， JOIN 更多翻譯為連接，本文中凡是出現(xiàn)中文“連接”的地方都是指 JOIN 。

本文中用到的所有例子涉及兩張表—— customers 用戶表和 orders 訂單表，其中訂單表中的 cust_id 字段表示用戶的唯一 ID，也就是用戶表的主鍵 cust_id 。兩張表的數(shù)據如下：

注：兩張表都經過了簡化，實際業(yè)務中這兩張表肯定還包括其他字段。

英文維基百科 JOIN 詞條 對連接的定義如下：

翻譯過來就是，“連接可以根據一張（自連接）或多張表中的共同值將這些表的列數(shù)據合并為一個新的結果集，標準 SQL 定義了五種連接：內連接、左外連接、右外連接、全外連接和交叉連接。”

也就是說，連接是 SQL 標準中定義的一種組合多張表的方式，當然一張表自身也可以和自身組合，稱為自連接。連接后得到的結果集的每一列其實都來自用于連接的多張表，不同的連接類型只是區(qū)分了這些列具體從哪張表里來，列里填充的是什么數(shù)據。

其實英文維基百科的 JOIN 詞條已經把各種連接的類型解釋地非常清楚了，非常值得去看一下。

我們來看一下 SQL 標準中定義的各種連接類型，理解各種連接最好的方法就是把需要連接的表想象成集合，并畫出可以反映集合的交與并的情況的圖——韋恩圖，例如下圖就畫出了 SQL 中定義的幾種主要連接。

請先仔細查看一下圖中的內容，你可以從中歸納出幾種連接類型呢？

雖然圖中畫了 7 種集合的交并情況，但是總結起來，主要是兩種連接類型在起作用——內連接（ INNER JOIN ）和外連接（ OUTER JOIN ），其中外連接又分為了左外連接（ LEFT OUTER JOIN ）、右外連接（ RIGHT OUTER JOIN ）和全外連接（ FULL OUTER JOIN ）。

下面先簡單介紹一下 SQL 標準中各種連接的定義，然后在「MySQL 中的連接」一節(jié)再用例子來演示 MySQL 中支持的各種連接。

連接既然是用來合并多張表的，那么要定義一個連接就必須指定需要連接的表，并指定可選的連接條件。例如，一個典型的 SQL 連接語句如下：

我們用表 A 和表 B 指代需要連接的兩張表，經過 內連接 后得到的結果集 僅 包含所有滿足 連接條件 的數(shù)據；而經過 外連接 后得到的數(shù)據集 不僅 包含滿足 連接條件 的數(shù)據，還包含其他數(shù)據，具體的差別是：

在上面「SQL 標準定義的主要連接」一圖中并沒有列出交叉連接，交叉連接會對連接的兩張表做笛卡爾積，也就是連接后的數(shù)據集中的行是由第一張表中的每一行與第二張表中的每一行配對而成的，而不管它們 邏輯上 是否可以搭配在一起。假設交叉連接的兩張表分別有 m 和 n 行數(shù)據，那么交叉連接后的數(shù)據集就包含 m 乘以 n 行數(shù)據。

連接根據連接的條件不同，又可以區(qū)分為等值連接和非等值連接，「SQL 標準定義的主要連接」圖中畫出的連接的連接條件都是比較兩個字段是否相等，它們都是等值連接。

自然連接是等值連接的一種特殊形式，自然連接會自動選取需要連接的兩張表中字段名相同的 所有 列做相等比較，而不需要再指定連接條件了。

注：以下內容全部基于 MySQL 5.7 版本，所有例子只保證在 MySQL 5.7 上是可以正確執(zhí)行的。

MySQL 中支持的連接類型和關鍵字如下：

上面的表示方法摘自 MySQL 5.7 版本 官方文檔 ，其中 | 表示兩者皆可出現(xiàn)， [] 表示的是可選的， {} 表示的是必選的，例如 NATURAL LEFT JOIN 和 NATURAL JOIN 都是合法的。

可以看到，除了全外連接（ FULL OUTER JOIN ）以外， MySQL 基本支持了 SQL 標準中定義的各種連接。在 MySQL 中全外連接可以通過 UNION 合并的方式做到，當然前提是你知道自己為什么需要這么做，具體參見： Full Out Join in MySQL 。

MySQL 語法中還支持一個并不在 SQL 標準中的 STRAIGHT_JOIN ，它在 表現(xiàn)上 和內連接或者交叉連接并無區(qū)別，只是一種給 MySQL 優(yōu)化器的一個提示， STRAIGHT_JOIN 提示 MySQL 按照語句中表的順序加載表，只有在你明確清楚 MySQL 服務器對你的 JOIN 語句做了負優(yōu)化的時候才可能用到它。

還有一點需要說明的是，根據 官方文檔 ，在 MySQL 中， JOIN 、 CROSS JOIN 和 INNER JOIN 實現(xiàn)的功能是一致的，它們在語法上是等價的。從語義上來說， CROSS JOIN 特指無條件的連接（沒有指定 ON 條件的 JOIN 或者沒有指定 WHERE 連接條件的多表 SELECT ）， INNER JOIN 特指有條件的連接（指定了 ON 條件的 JOIN 或者指定了 WHERE 連接條件的多表 SELECT ）。當然，如果你非要寫 ... CROSS JOIN ... ON ... 這樣的語法，也是可以執(zhí)行的，雖然寫著交叉連接，實際上執(zhí)行的是內連接。

下面我們就用例子來看一看 MySQL 中支持的幾種連接的例子。

注：下面的例子都沒有指定 ORDER BY 子句，返回結果的順序可能會因為數(shù)據插入順序的不同而略有不同。

MySQL 的交叉連接或內連接有兩種寫法，一種是使用 JOIN 并用 ON 或者 USING 子句指定連接條件的寫法，一種是普通的 SELECT 多表，并且用 WHERE 子句指定連接的鍵的寫法。

下面的例子是一個交叉連接：

上面的寫法等價于：

當然，第二種寫法中如果將 CROSS JOIN 替換成 JOIN 或者 INNER JOIN 也是可以正確執(zhí)行的。上面兩條語句的執(zhí)行結果如下：

可以看到共返回了 30 行結果，是兩張表的笛卡爾積。

一個內連接的例子如下：

上面的寫法等價于：

在連接條件比較的字段相同的情況下，還可以改用 USING 關鍵字，上面的寫法等價于：

上面三條語句的返回結果如下：

可以看到只返回了符合連接條件 customers.cust_id = orders.cust_id 的 6 行結果，結果的含義是所有有訂單的用戶和他們的訂單。

左外連接和右外連接的例子如下，其中的 OUTER 關鍵字可以省略：

其中右外連接的返回與內連接的返回是一致的（思考一下為什么），左外連接的返回結果如下：

可以看到一共返回了 8 行數(shù)據，其中最后兩行數(shù)據對應的 order_id 的值為 NULL ，結果的含義是所有用戶的訂單，不管這些用戶是否已經有訂單存在了。

根據前面介紹的自然連接的定義，自然連接會自動用參與連接的兩張表中 字段名相同 的列做等值比較，由于例子中的 customers 和 orders 表只有一列名稱相同，我們可以用自然連接的語法寫一個與上面的內連接的例子表現(xiàn)行為一樣的語句如下：

可以看到，使用自然連接就不能再用 ON 子句指定連接條件了，因為這完全是多余的。

當然，自然連接同樣支持左外連接和右外連接。

下面用一個 customers 表自連接的例子再來說明一下自然連接，語句如下：

因為是自連接，因此必須使用 AS 指定別名，否則 MySQL 無法區(qū)分“兩個” customers 表，運行的結果如下：

可以看到結果集和 customers 表完全一致，大家可以思考一下為什么結果是這樣的。

文章之前也提到了，MySQL 還支持一種 SQL 標準中沒有定義的“方言”， STRAIGHT_JOIN ， STRAIGHT_JOIN 支持帶 ON 子句的內連接和不帶 ON 子句的交叉連接，我們來看一個 STRAIGHT_JOIN 版本的內連接的例子：

返回結果與前面內連接的例子是一致的，如下：

STRAIGHT_JOIN 的表現(xiàn)和 JOIN 是完全一致的，它只是一種給 MySQL 優(yōu)化器的提示，使得 MySQL 始終按照語句中表的順序讀取表（上面的例子中，MySQL 在執(zhí)行時一定會先讀取 customers 表，再讀取 orders 表），而不會做改變讀取表的順序的優(yōu)化。關于 MySQL 優(yōu)化器的話題這里不做展開，需要說明的是除非你非常清楚你在做什么，否則不推薦直接使用 STRAIGHT_JOIN 。

你能理解上面的語句是在檢索什么數(shù)據嗎？

本文主要介紹了 SQL 標準里定義的各種連接的概念，以及 MySQL 中的實現(xiàn)，并通過各種例子來介紹了這些連接的區(qū)別。這些連接不一定都能在實際開發(fā)中用到，但是做到心中有知識也還是很有必要的。

那么，現(xiàn)在再回憶一下，什么是內連接、外連接、自連接、等值連接和自然連接？他們的區(qū)別是什么？

最后，給大家留一個思考題，為什么 MySQL 中沒有左外連接或者右外連接版本的 STRAIGHT_JOIN ？

本文標題：mysql內連接怎么用 mysql內連接用法
文章轉載：http://jinyejixie.com/article42/dophihc.html

成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站維護、全網營銷推廣、虛擬主機、響應式網站、網站內鏈、關鍵詞優(yōu)化

廣告

聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)