MySQL基礎：多表查詢

多表關係

• 概述

​ 項目開發中，在進行資料庫表結構設計時，會根據業務需求及模塊之間的關係，分析並設計表結構，由於業務之間相互關聯，所以各個表結構之間也存在著各種聯繫，基本上分為三種：

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

• 一對多（多對一）

  • 案例：部門與員工的關係
  • 關係：一個部門對應多個員工，一個員工對應一個部門
  • 實現：在多（N）的一方建立外鍵，指向一的一方的主鍵

• 多對多

  • 案例：學生與課程的關係
  • 關係：一個學生可以選修多門課程，一門課程也可以供多個學生選擇
  • 實現：建立第三張中間表，中間表至少包含兩個外鍵，分別關聯兩方主鍵

• 一對一

  • 案例：用戶與用戶詳情的關係
  • 關係：一對一關係，多用於單表拆分，將一張表的基礎欄位放在一張表中，其他詳情欄位放在另一張表中，以提升操作效率
  • 實現：在任意一方加入外鍵，關聯另外一方的主鍵，並且設置外鍵為唯一的（UNIQUE）

多表查詢概述

• 概述：指從多張表中查詢數據。
• 笛卡爾積：笛卡爾乘積是指在數學中，兩個集合A集合和B集合的所有組合情況（在多表查詢時，需要消除無效的笛卡爾積）

例子：

select * from emp,dept where emp.dept_id = dept.id;

• 多表查詢分類

  • 子查詢

  • 連接查詢

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

    • 內連接：相當於查詢A、B交集部分數據

    • 外連接：

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

連接查詢--內連接

內連接查詢的是兩張表交集的部分。

• 內連接查詢語法

1. 隱式內連接

SELECT 欄位列表 FROM 表1,表2 WHERE 條件...;

2. 顯式內連接

SELECT 欄位列表 FROM 表1 [INNER] JOIN 表2 ON 連接條件...;

連接查詢-外連接

• 外連接查詢語法：

1. 左外連接

SELECT 欄位列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 條件...;

相當於查詢表1(左表)的所有數據，包含表1和表2交集部分的數據。

2. 右外連接

SELECT 欄位列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 條件...;

相當於查詢表2(右表)的所有數據，包含表1和表2交集部分的數據。

連接查詢-自連接

自連接查詢語法：

SELECT 欄位列表 FROM 表A 別名A JOIN 表A 別名B ON 條件...;

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

聯合查詢-union，uinon all

對於union查詢，就是把多次查詢的結果合併起來，形成一個新的查詢結果集。

SELECT 欄位列表 FROM 表A ...
UNION [ALL]
SELECT 欄位列表 FROM 表B ...;

註意：

1. union all是直接將查詢的結果合併，union會對合併之後的結果去重。

2. 對於聯合查詢的多張表的列數必須保持一致，欄位類型也需要保持一致。

子查詢

• 概念：SQL語句中嵌套SELECT語句，稱為嵌套查詢，又稱子查詢。

SELECT * FROM 表1 WHERE column1 = (SELECT column1 FROM 表2);

子查詢外部的語句可以是INSERT/UPDATE/DELETE/SELECT的任何一個。

• 根據子查詢結果不同，分為：

  • 標量子查詢（子查詢結果為單個值）
  • 列子查詢（子查詢結果為一列）
  • 行子查詢（子查詢結果為一行）
  • 表子查詢（子查詢結果為多行多列）

• 根據子查詢的位置，分為：WHERE之後、FROM之後、SELECT之後。

標量子查詢

子查詢返回的結果是單個值（數字、字元串、日期等），最簡單的形式，這種子查詢稱為標量子查詢。

常用的操作符：= <> > >= < <=

列子查詢

子查詢返回的結果是一列（可以是多行），這種子查詢稱為列子查詢。

常用的操作符：IN、NOT IN、ANY、SOME、ALL

行子查詢

子查詢返回的結果是一行（可以是多列），這種子查詢稱為行子查詢。

常用的操作符：=、<>、NOT、NOT IN

例子：

-- 查詢單行數據
select * from emp where (salary,managerid) = (select salary,managerid from emp where name='張無忌'); 

表子查詢

子查詢返回的結果是多行多列，這種子查詢稱為表子查詢。

常用的操作符：IN