使用多個表 · mysql 8.0 中文文檔

#### 3.3.4.9 使用多個表 `pet` 表記錄你擁有哪些寵物. 如果你想記錄它們它們生活中的其他事, 比如去看獸醫, 或者當幼崽出生時, 你需要另外一張表. 這張表應該是什么樣子呢? 它需要包含以下內心: - 寵物的名字, 這樣你就能知道每件事屬于哪一種動物. - 一個日期，以便你知道事情發生的時間. - 描述事情的字段. - 事情類型字段, 如果你想對事情進行分類. 考慮到這些因素, `event` 表的 [`CREATE TABLE`](https://dev.mysql.com/doc/refman/8.0/en/create-table.html) 語句可能像下面那樣: ```sql mysql> CREATE TABLE event (name VARCHAR(20), date DATE, type VARCHAR(15), remark VARCHAR(255)); ``` 與 `pet` 表一樣, 創建一個制表符分隔的包含以下信息的文本文件, 通過加載該文件來初始化記錄是最容易的. | name | date | type | remark | |:---:|:---:|:---:|:---:| | Fluffy | 1995-05-15 | litter | 4 kittens, 3 female, 1 male | | Buffy | 1993-06-23 | litter | 5 puppies, 2 female, 3 male | | Buffy | 1994-06-19 | litter | 3 puppies, 3 female | | Chirpy | 1999-03-21 | vet | needed beak straightened | | Slim | 1997-08-03 | vet | broken rib | | Bowser | 1991-10-12 | kennel | | | Fang | 1991-10-12 | kennel | | | Fang | 1998-08-28 | birthday | Gave him a new chew toy | | Claws | 1998-03-17 | birthday | Gave him a new flea collar | | Whistler | 1998-12-09 | birthday | First birthday | 加載記錄如下所示: ```sql mysql> LOAD DATA LOCAL INFILE 'event.txt' INTO TABLE event; ``` 基于你在 `pet` 表中運行的查詢中學到的知識, 你應該能夠對 `event` 表中的記錄執行檢索; 原理是一樣的. 但是什么時候 `event` 表本身不足以回答你可能提出的問題呢? 假設你想知道每只寵物生幼崽的年齡. 我們前面看到了如何從兩個日期計算年齡. 母親的產仔日期在 `event` 表中, 但是要計算該日期的年齡, 你需要它的出生日期, 其存儲在 `pet` 表中. 這意味著要查詢兩個表: ```sql 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` 子句連接兩個表, 因為查詢需要從這兩個表中提取信息. - 當從多個表組合 (`joining`) 信息時, 你需要指定如何將一個表中的記錄與另一個表中的記錄匹配. 這很簡單, 因為它們都有一個 `name` 列. 查詢使用 `ON` 子句基于 `name` 值來匹配兩個表中的值. 查詢使用 `INNER JOIN` 來組合表. 如果僅當兩個表的記錄都滿足 `ON` 子句中指定的條件時, `INNER JOIN` 允許兩個表中的記錄出現在結果中. 在本例中, `ON` 子句指定 `pet` 表中的 `name` 列與 `event` 表中的 `name` 列匹配. 如果 name 出現在一個表中而沒有出現在另一個表中, 那么由于 `ON` 子句中的條件失敗, 那么結果中將不會出現該行. - 因為 `name` 列同時出現在兩個表中, 所以在訪問該列時, 必須明確指明是哪個表. 這是通過將表名前置到列名前來實現的. 你不需要兩個不同的表執行連接. 有時候, 如果你想將表中的記錄與該表中的其他記錄進行比較, 那么將表連接到它自己是非常有用的. 例如, 要在你的寵物中找出配對進行繁殖, 你可以將 `pet` 表與它自己連接起來, 以生成存活物種的雌性和雄性的候選配對: ```sql 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 | +--------+------+-------+------+---------+ ``` 在這個查詢中, 我們指定了表的別名以指定訪問的列, 并保證訪問的列與對應的表的實例進行關聯.