一、通過關(guān)聯(lián)表（N-N）

正常兩張表進(jìn)行關(guān)聯(lián)，我們可以采用中間表的方式，這是最靈活的方式，它可以直接將兩張表的數(shù)據(jù)根據(jù)某個(gè)字段直接關(guān)聯(lián)起來。

下面是一個(gè)簡單的例子來解釋這個(gè)概念： 假設(shè)我們有兩個(gè)表：students（學(xué)生）和 courses（課程）。一個(gè)學(xué)生可以選修多門課程，同時(shí)一門課程也可以被多個(gè)學(xué)生選修。這就是一個(gè)典型的多對多關(guān)系。

1.students 表

2.courses 表

為了表示學(xué)生和課程之間的多對多關(guān)系，我們可以使用一個(gè)中間表 student_courses：

3.student_courses 表

在這個(gè)中間表中，每一行都表示一個(gè)學(xué)生和一門課程之間的關(guān)聯(lián)。例如，第一行表示 Alice（學(xué)生ID為1）選修了 Math（課程ID為1）。 通過查詢這個(gè)中間表，我們可以輕松地獲取某個(gè)學(xué)生選修的所有課程，或者獲取選修了某門課程的所有學(xué)生。 這種使用中間表的方式非常靈活，因?yàn)樗试S我們輕松地添加、刪除或修改學(xué)生和課程之間的關(guān)聯(lián)，而不需要修改原始的 students 或 courses 表。

二、主從設(shè)計(jì)（1-N）

除了上面那種方式，還有一種主從設(shè)計(jì)，就是一張主表，一張明細(xì)表（或者叫做從表）。

主從設(shè)計(jì)或稱為父子表設(shè)計(jì)是數(shù)據(jù)庫中常見的另一種表關(guān)聯(lián)方式。在這種設(shè)計(jì)中，主表通常存儲(chǔ)主要實(shí)體的信息，而明細(xì)表或從表則存儲(chǔ)與主表實(shí)體相關(guān)的詳細(xì)或子項(xiàng)信息。這種設(shè)計(jì)常用于一對多關(guān)系，即一個(gè)主表記錄對應(yīng)多個(gè)明細(xì)表記錄。 以下是一個(gè)主從設(shè)計(jì)的例子：

1.主表：orders（訂單）

2.明細(xì)表：order_items（訂單項(xiàng)）

在這個(gè)例子中：

• orders 表是主表，它存儲(chǔ)了訂單的基本信息，如訂單ID、客戶ID、訂單日期和總金額。
• order_items 表是明細(xì)表或從表，它存儲(chǔ)了每個(gè)訂單的詳細(xì)項(xiàng)，如訂單項(xiàng)ID、所屬的訂單ID、產(chǎn)品ID、數(shù)量和單價(jià)。

通過 order_id 字段，order_items 表與 orders 表建立了關(guān)聯(lián)。這樣，我們可以輕松地查詢某個(gè)訂單的所有項(xiàng)，或者查詢某個(gè)產(chǎn)品的所有訂單項(xiàng)。 主從設(shè)計(jì)的優(yōu)點(diǎn)是：

• 結(jié)構(gòu)清晰：主表和明細(xì)表各司其職，主表存儲(chǔ)總體信息，明細(xì)表存儲(chǔ)詳細(xì)信息。
• 靈活擴(kuò)展：如果需要添加更多的與主表相關(guān)的詳細(xì)信息，可以在明細(xì)表中添加更多字段，而不會(huì)影響主表的結(jié)構(gòu)。
• 易于維護(hù)：由于主表和明細(xì)表是分離的，所以對其中一個(gè)表的修改不會(huì)影響到另一個(gè)表。

需要注意的是，在設(shè)計(jì)數(shù)據(jù)庫時(shí)，應(yīng)根據(jù)實(shí)際業(yè)務(wù)需求和數(shù)據(jù)關(guān)系來選擇合適的表關(guān)聯(lián)方式。有時(shí)，可能需要結(jié)合使用中間表、主從設(shè)計(jì)或其他設(shè)計(jì)模式來滿足復(fù)雜的業(yè)務(wù)需求。

三、關(guān)聯(lián)設(shè)計(jì)（1-N）

除了上面說的主從設(shè)計(jì)，還有一些情況，就是兩張表并非主從關(guān)系，但是也有一定的邏輯關(guān)聯(lián)性。比如一個(gè)手機(jī)生產(chǎn)訂單，我們要根據(jù)這個(gè)訂單生成一個(gè)多個(gè)工單，分為原料采購工單，組裝工單，包裝工單等。這種也是一對多的關(guān)系，但并非主從關(guān)系，針對這種情況，我們需要做關(guān)聯(lián)設(shè)計(jì)。

我們可以為手機(jī)訂單表和工單表創(chuàng)建相應(yīng)的數(shù)據(jù)庫表結(jié)構(gòu)，并模擬一些基礎(chǔ)數(shù)據(jù)。以下是使用SQL語言創(chuàng)建表和插入數(shù)據(jù)的示例：

1. 創(chuàng)建手機(jī)訂單表 (phone_orders)

CREATE TABLE phone_orders (  
    sid INT PRIMARY KEY NOT NULL,  
    phone_name VARCHAR(100) NOT NULL,  
    phone_quantity INT NOT NULL  
);

2. 創(chuàng)建工單表 (work_orders)
3. 
   

CREATE TABLE work_orders (  
    sid INT PRIMARY KEY NOT NULL,  
    sSrcSlaveId INT NOT NULL, -- 源單號(hào)，即手機(jī)訂單表的sid  
    dProductPQty INT NOT NULL, -- 產(chǎn)品數(shù)量  
    FOREIGN KEY (sSrcSlaveId) REFERENCES phone_orders(sid) ON DELETE CASCADE  
);

這里，我們?yōu)閣ork_orders表的sSrcSlaveId字段設(shè)置了外鍵約束，以確保它引用的是phone_orders表中存在的sid。使用ON DELETE CASCADE選項(xiàng)意味著當(dāng)刪除一個(gè)手機(jī)訂單時(shí)，與該訂單相關(guān)聯(lián)的所有工單也會(huì)被自動(dòng)刪除。

3. 模擬基礎(chǔ)數(shù)據(jù)

首先，向手機(jī)訂單表中插入一些數(shù)據(jù)：

INSERT INTO phone_orders (sid, phone_name, phone_quantity) VALUES  
(1, 'iPhone 13', 0),  
(2, 'Galaxy S22', 0),  
(3, 'Pixel 6', 0);

然后，向工單表中插入與手機(jī)訂單相關(guān)聯(lián)的數(shù)據(jù)：

INSERT INTO work_orders (sid, sSrcSlaveId, dProductPQty) VALUES  
(1, 1, 20), -- 對應(yīng)phone_orders中sid為1的訂單，產(chǎn)品數(shù)量為20  
(2, 1, 30), -- 同一個(gè)訂單的另一個(gè)工單，產(chǎn)品數(shù)量為30  
(3, 2, 50), -- 對應(yīng)phone_orders中sid為2的訂單，產(chǎn)品數(shù)量為50

這里的sid字段在兩張表中都是唯一的，但在各自的表中可以重復(fù)。對于work_orders表，sSrcSlaveId字段對應(yīng)于phone_orders表的sid，用于表示工單與哪個(gè)手機(jī)訂單相關(guān)聯(lián)。 手機(jī)訂單的總數(shù)量為0，我們一般需要在生成工單的時(shí)候，去回填訂單表的數(shù)量字段，這是很常見的需求。 嘗試寫sql如下：

update phone_orders A join (
 SELECT sSrcSlaveId,SUM(dProductPQty) dProductPQty from work_orders GROUP BY  sSrcSlaveId
) B on A.sid = B.sSrcSlaveId
set A.phone_quantity = B.dProductPQty
where  A.sid = 1;

基于您提供的SQL更新語句，這條語句的目的是更新phone_orders表中sid為1的記錄，將其phone_quantity字段設(shè)置為與該訂單相關(guān)聯(lián)的所有工單的產(chǎn)品數(shù)量之和。

首先，我們來分析這條SQL語句的各個(gè)部分：

4.子查詢：

SELECT sSrcSlaveId, SUM(dProductPQty) dProductPQty 
FROM work_orders 
GROUP BY sSrcSlaveId

這個(gè)子查詢從work_orders表中選取sSrcSlaveId（即源單號(hào)，對應(yīng)于phone_orders表的sid）和每個(gè)源單號(hào)對應(yīng)的所有工單的產(chǎn)品數(shù)量之和（通過SUM(dProductPQty)計(jì)算）。結(jié)果集包含兩列：sSrcSlaveId和計(jì)算后的產(chǎn)品數(shù)量dProductPQty。

5.JOIN操作：

UPDATE phone_orders A 
JOIN (
    ...子查詢...
) B 
ON A.sid = B.sSrcSlaveId

這里使用了JOIN操作來連接phone_orders表（別名為A）和子查詢的結(jié)果集（別名為B）。連接條件是A.sid = B.sSrcSlaveId，即phone_orders表的唯一鍵sid與子查詢結(jié)果集中的sSrcSlaveId相匹配。

6.SET操作：

SET A.phone_quantity = B.dProductPQty

此部分將phone_orders表（別名為A）中的phone_quantity字段更新為子查詢結(jié)果集（別名為B）中對應(yīng)的dProductPQty值。

7.WHERE條件：

WHERE A.sid = 1

這個(gè)條件限制了更新的范圍，只更新phone_orders表中sid為1的記錄。

這條SQL語句的作用是：找出所有與phone_orders表中sid為1的訂單相關(guān)聯(lián)的工單，計(jì)算這些工單的產(chǎn)品數(shù)量之和，然后將phone_orders表中sid為1的記錄的phone_quantity字段更新為這個(gè)總和。

執(zhí)行后得到結(jié)果：

思考題

上面的例子，如果我們換成left join，并且去查詢A.sid = 3會(huì)發(fā)生什么？

update phone_orders A left join (
 SELECT sSrcSlaveId,SUM(dProductPQty) dProductPQty from work_orders GROUP BY  sSrcSlaveId
) B on A.sid = B.sSrcSlaveId
set A.phone_quantity = B.dProductPQty
where  A.sid = 3;