MySQL :: MySQL 8.4 參考手冊 :: 5.3.4.9 使用多個表格

版本 8.4

8.0 日文

MySQL 8.4 參考手冊 / ... / 使用多個表格

5.3.4.9 使用多個表格

pet 表格追蹤您擁有的寵物。如果您想記錄關於牠們的其他資訊，例如獸醫就診或幼崽出生的日期等生活事件，您需要另一個表格。這個表格應該是什麼樣子？它需要包含以下資訊：

寵物名稱，以便您知道每個事件與哪隻動物有關。
日期，以便您知道事件發生的時間。
描述事件的欄位。
事件類型欄位，如果您想對事件進行分類。

考慮到這些因素，CREATE TABLE 陳述式，用於建立 event 表格，可能如下所示：

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
       type VARCHAR(15), remark VARCHAR(255));

與 pet 表格一樣，最簡單的載入初始記錄方法是建立一個包含以下資訊的以 Tab 字元分隔的文字檔。

名稱	日期	類型	備註
Fluffy	1995-05-15	litter	4 隻幼崽，3 隻雌性，1 隻雄性
Buffy	1993-06-23	litter	5 隻幼犬，2 隻雌性，3 隻雄性
Buffy	1994-06-19	litter	3 隻幼犬，3 隻雌性
Chirpy	1999-03-21	vet	需要矯正鳥喙
Slim	1997-08-03	vet	肋骨斷裂
Bowser	1991-10-12	kennel
Fang	1991-10-12	kennel
Fang	1998-08-28	birthday	給牠一個新的咀嚼玩具
Claws	1998-03-17	birthday	給牠一個新的跳蚤項圈
Whistler	1998-12-09	birthday	第一個生日

如下載入記錄：

mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event;

根據您從在 pet 表格上執行的查詢中學到的內容，您應該能夠在 event 表格中的記錄上執行檢索；原則是相同的。但是，什麼時候 event 表格本身不足以回答您可能會問的問題？

假設您想找出每隻寵物產崽時的年齡。我們稍早看到如何從兩個日期計算年齡。母親產崽的日期在 event 表格中，但要計算她在那天的年齡，您需要她的出生日期，該日期儲存在 pet 表格中。這表示查詢需要兩個表格

mysql> SELECT pet.name,
       TIMESTAMPDIFF(YEAR,birth,date) AS age,
       remark
       FROM pet INNER JOIN event
         ON pet.name = event.name
       WHERE event.type = 'litter';
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

關於此查詢，有幾點需要注意

FROM 子句會聯結兩個表格，因為查詢需要從這兩個表格中提取資訊。
當合併（聯結）來自多個表格的資訊時，您需要指定如何將一個表格中的記錄與另一個表格中的記錄進行比對。這很容易，因為它們都有 name 欄。查詢使用 ON 子句根據 name 值比對兩個表格中的記錄。
查詢使用 INNER JOIN 來合併表格。只有當兩個表格都符合 ON 子句中指定的條件時，INNER JOIN 才允許結果中出現任一表格的列。在此範例中，ON 子句指定 pet 表格中的 name 欄必須與 event 表格中的 name 欄相符。如果名稱出現在一個表格中，但未出現在另一個表格中，則該列不會出現在結果中，因為 ON 子句中的條件失敗。
因為 name 欄出現在兩個表格中，所以您必須明確指出您指的是哪個表格的欄。這是透過將表格名稱放在欄名稱之前來完成的。

您不需要兩個不同的表格即可執行聯結。有時，如果您想將表格中的記錄與同一個表格中的其他記錄進行比較，將表格聯結到自身會很有用。例如，若要找出您寵物中的繁殖對，您可以將 pet 表格與自身聯結，以產生類似物種的活雄性和雌性候選配對

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
       FROM pet AS p1 INNER JOIN pet AS p2
         ON p1.species = p2.species
         AND p1.sex = 'f' AND p1.death IS NULL
         AND p2.sex = 'm' AND p2.death IS NULL;
+--------+------+-------+------+---------+
| name   | sex  | name  | sex  | species |
+--------+------+-------+------+---------+
| Fluffy | f    | Claws | m    | cat     |
| Buffy  | f    | Fang  | m    | dog     |
+--------+------+-------+------+---------+

在此查詢中，我們為表格名稱指定別名，以參照欄，並保持每個欄參照與哪個表格執行個體相關的清晰度。