多表查詢(上)

一. 多表關係

在實際應用中，根據需求，設計的表結構之間存在聯繫，聯繫一般分為以下三種

1. 一對多（多對一）
2. 多對多
3. 一對一

1. 一對多（多對一）

案例：部門與員工的關係，一個部門對應多個員工，一個員工對應一個部門。

• 實現：在多的一方建立外鍵，指向一的一方的主鍵

2. 多對多

案例：學生與課程的關係，一個學生可以選修多門課程，一門課程也可以供多個學生選擇。

• 實現：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

3. 一對一

案例：用戶與用戶信息的關係。

多用於單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另一張表中，以提升效率。

• 實現：在任意一方加入外鍵，管理另外一方的主鍵，並且設置外鍵為唯一的（UNIQUE）。

二. 多表查詢

多表查詢：從多張表中查詢數據

• 笛卡爾積：指兩個集合的所有組合情況。而多表查詢目的就是消除無效的笛卡爾積。

• 多表查詢的分類

  • 連接查詢
    • 內連接：查詢A、B交集部分數據
    • 外連接：左(右)外連接：查詢左表(右表)所有數據，以及兩張表交集部分數據
    • 自連接：當前表與自身的連接查詢，自連接必須使用表別名
  • 子查詢

1. 連接查詢

1.1 內連接

內連接：查詢A、B交集部分數據

# 隱式內連接
select {欄位列表} from {表1},{表2} where {條件};

# 顯式內連接
select {欄位列表} from {表1} [inner] join {表2} on {連接條件};

• 在編寫條件時，有些情況下表名會很長，不方便，可以在表名後取一個別名，後面的條件就用別名代替表名。註意：取別名後，在寫條件或欄位列表時原名就不能用了，會報錯。

select {欄位列表} from {表1} {別名1},{表2} {別名2} where {條件};

1.2 外連接

外連接：左(右)外連接：查詢左表(右表)所有數據，以及兩張表交集部分數據

# 左外連接
select {欄位列表} from {表1} left [outer] join {表2} on {條件};

# 右外連接
select {欄位列表} from {表1} right [outer] join {表2} on {條件};

• 外連接和內連接的區別：比如說有一張員工表和一張部門表，如果其中有一條數據沒有部門，如果使用內連接查詢員工和對應的部門，沒有部門的那個員工就查不到，但是使用外連接是可以查詢到的。

1.3 自連接

自連接：當前表與自身的連接查詢，自連接必須使用表別名

案例：在一張表裡有員工信息，包括直屬領導id（領導也在員工表內），查詢員工與對應的領導，用自連接。

• 自連接可以是內連接查詢，也可以是外連接查詢。

# 內連接語法，外連接類似
select {欄位列表} from {表A} {別名A} join {表A} {別名B} on {條件};

• 自連接可以看成兩張一樣的表連接查詢。