前言

在實際項目開發(fā)中，數(shù)據(jù)庫絕不會只用單張表存儲所有業(yè)務(wù)數(shù)據(jù)，用戶、訂單、商品、分類等數(shù)據(jù)都需要多表關(guān)聯(lián)存儲。

一、數(shù)據(jù)關(guān)聯(lián)的必要性

單表存儲模式存在明顯弊端，不僅數(shù)據(jù)冗余嚴重，大量重復(fù)字段大幅耗費數(shù)據(jù)庫存儲空間，還會提升日常數(shù)據(jù)修改與維護難度，修改一處信息便需多處同步調(diào)整，極易引發(fā)數(shù)據(jù)錯亂不一致的問題，同時該設(shè)計違背數(shù)據(jù)庫三大范式原則，嚴重制約項目后續(xù)的功能拓展與版本迭代。

各類業(yè)務(wù)數(shù)據(jù)本身具備清晰的從屬、匹配與對應(yīng)邏輯，依靠單一數(shù)據(jù)表無法貼合實際業(yè)務(wù)架構(gòu)，唯有采用多表拆分設(shè)計，借助表與表之間的關(guān)聯(lián)查詢，才能順暢實現(xiàn)各類業(yè)務(wù)數(shù)據(jù)的聯(lián)動調(diào)用，適配真實業(yè)務(wù)運轉(zhuǎn)需求。

核心思想：拆分不同業(yè)務(wù)數(shù)據(jù)到不同數(shù)據(jù)表，通過關(guān)聯(lián)字段建立數(shù)據(jù)聯(lián)系，實現(xiàn)數(shù)據(jù)統(tǒng)一管理與聯(lián)合查詢。

二、MySQL 三大數(shù)據(jù)表關(guān)聯(lián)關(guān)系

1.一對一 關(guān)系

A 表一條數(shù)據(jù)唯一對應(yīng) B 表一條數(shù)據(jù)，互相唯一匹配。

常見場景：用戶基礎(chǔ)信息表 & 用戶隱私詳情表、員工表 & 員工身份證表

設(shè)計思路：任意一方添加對方主鍵作為關(guān)聯(lián)字段，可設(shè)置唯一約束保證一對一。

2. 一對多關(guān)系（開發(fā)最常用）

主表一條數(shù)據(jù)，對應(yīng)從表多條數(shù)據(jù)，反向多條數(shù)據(jù)對應(yīng)一條主表數(shù)據(jù)。

常見場景：

部門表（一）→ 員工表（多）
商品分類表（一）→ 商品表（多）
用戶表（一）→ 訂單表（多）
設(shè)計思路：多方數(shù)據(jù)表添加一方主鍵作為外鍵關(guān)聯(lián)字段。

3. 多對多關(guān)系

A 表多條數(shù)據(jù)對應(yīng) B 表多條數(shù)據(jù)，雙向均可多匹配。

常見場景：學生 & 課程、角色 & 權(quán)限、商品 & 購物車

設(shè)計思路：新增中間關(guān)聯(lián)表，存儲兩張主表主鍵，拆分多對多為兩個一對多關(guān)系

三、關(guān)聯(lián)操作（JOIN）詳解

我們以例子出發(fā)：

這是一張用戶表

這是一張訂單表

1. INNER JOIN（內(nèi)連接）

作用：只返回兩張表中匹配成功的數(shù)據(jù)（取交集）。

SELECT u.*, o.*
FROM users u
INNER JOIN orders o 
ON u.uid = o.user_id;

查詢結(jié)果：

只顯示有訂單的用戶 + 對應(yīng)用戶存在的訂單（張三、李四）。
趙六（無訂單）、訂單 104（無對應(yīng)用戶）都不顯示。

2. LEFT JOIN（左連接 / 左外連接）

作左表數(shù)據(jù)全部顯示，右表只顯示匹配的數(shù)據(jù)，不匹配顯示 NULL。

SELECT u.*, o.*
FROM users u
LEFT JOIN orders o 
ON u.uid = o.user_id;

查詢結(jié)果：

所有用戶（張三、李四、王五、趙六）全部顯示
有訂單的顯示訂單，沒訂單的訂單字段為 NULL
常用場景：查詢所有用戶，以及他們的訂單（包括沒下單的用戶）。

3. RIGHT JOIN（右連接 / 右外連接）

右表數(shù)據(jù)全部顯示，左表只顯示匹配的數(shù)據(jù)，不匹配顯示 NULL。

SELECT u.*, o.*
FROM users u
RIGHT JOIN orders o 
ON u.uid = o.user_id;

查詢結(jié)果：

所有訂單全部顯示
訂單 104（user_id=5）沒有對應(yīng)用戶，用戶字段為 NULL

4. FULL JOIN（全外連接）

兩張表所有數(shù)據(jù)都顯示，匹配不上的字段填 NULL（取并集）。
MySQL 不直接支持 FULL JOIN，用 UNION 實現(xiàn)。

SELECT u.*, o.* FROM users u LEFT JOIN orders o ON u.uid = o.user_id
UNION
SELECT u.*, o.* FROM users u RIGHT JOIN orders o ON u.uid = o.user_id;

結(jié)果：所有用戶 + 所有訂單，缺失數(shù)據(jù)填 NULL。

5. CROSS JOIN（交叉連接）

作用：笛卡爾積 —— 左表每一行都和右表每一行拼接。

例如：

A：(a,b,c)

B：(1,2,3)

A與B作笛卡爾積—> a1,a2,a3,b1,b2,b3,c1,c2,c3

-- 無條件：4個用戶 × 4個訂單 = 16條數(shù)據(jù)
SELECT * FROM users CROSS JOIN orders;

-- 帶條件等價于 INNER JOIN
SELECT * FROM users CROSS JOIN orders ON users.uid = orders.user_id;

四、JOIN 核心語法規(guī)則

SELECT 字段
FROM 表1
[JOIN類型] JOIN 表2 
ON 表1.關(guān)聯(lián)字段 = 表2.關(guān)聯(lián)字段  #必須寫關(guān)聯(lián)條件
WHERE 過濾條件;

• 其中的關(guān)鍵區(qū)別：ON 和 WHERE
• ON：關(guān)聯(lián)時的匹配條件（JOIN 必須搭配 ON）
• WHERE：關(guān)聯(lián)后對結(jié)果集過濾
• 外連接中：ON 不過濾主表，WHERE 會過濾所有數(shù)據(jù)

五、實例解析

現(xiàn)有兩張表

第一張學生表Student

第二張成績表SC

要求查詢" 01 “課程?” 02 "課程成績?的學?的信息及課程分數(shù).。

完整代碼如下：

SELECT 
stu.*,
a.score 01課程分數(shù),
b.score 02課程分數(shù)
FROM Student stu
JOIN SC a ON stu.SId=a.SId AND a.CId='01'
LEFT JOIN SC b ON stu.SId=b.SId AND b.CId='02'
WHERE a.score > IFNULL(b.score,0)

Student stu   -- 學生表，別名 stu
SC a          -- 成績表，別名 a（專門放 01 課程）
SC b          -- 成績表，別名 b（專門放 02 課程）

一張成績表 SC 用了兩次，分別取不同課程，這叫自連接。

FROM Student stu
JOIN SC a ON stu.SId=a.SId AND a.CId='01'

只保留有 01 課程成績的學生，沒有 01 成績的學生直接被過濾掉。

為什么？

其中INNER JOIN = 交集

必須兩邊表都能匹配上，才會出現(xiàn)在結(jié)果里。

LEFT JOIN SC b ON stu.SId = b.SId AND b.CId = '02'

左邊學生（已經(jīng)有 01 成績），嘗試匹配 02 課程成績，匹配不到也保留學生，只是 02 分數(shù)顯示 NULL。

其中 LEFT JOIN = 以左表為準

左表有，右表沒有 → 右表字段填 NULL

不會刪除學生記錄。

WHERE a.score > IFNULL(b.score, 0)

條件判斷01 課程分數(shù) > 02 課程分數(shù)

總結(jié)

MySQL 數(shù)據(jù)關(guān)聯(lián)是后端開發(fā)必備核心知識點，從表結(jié)構(gòu)設(shè)計到聯(lián)表查詢貫穿整個項目開發(fā)流程。熟練掌握一對多、多對多設(shè)計思路，靈活運用內(nèi)外連接查詢，就能輕松搞定商城、后臺管理、社交系統(tǒng)等絕大多數(shù)業(yè)務(wù)的數(shù)據(jù)關(guān)聯(lián)需求。

到此這篇關(guān)于MySQL數(shù)據(jù)關(guān)聯(lián)之外鍵、表關(guān)系、聯(lián)表查詢實戰(zhàn)的文章就介紹到這了,更多相關(guān)MySQL外鍵、表關(guān)系、聯(lián)表查詢內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論