大家好！今天我們要深入探討 MySQL 中兩個非常重要的主題——多表操作 和 復雜查詢。

---

一. 多表操作

什么是多表操作？

在實際應用中，數據通常分布在多個表中，需要通過多表操作來獲取完整信息。比如，一個學生表和一個課程表之間的關系，我們需要通過多表操作來查詢某個學生的選課情況。

核心內容：

• 多表關系
• 外鍵約束
• 聯合查詢

1. 多表關系

一對一關系

一個表中的一條記錄對應另一個表中的一條記錄。例如，用戶表和個人信息表之間可以是一對一的關系。

-- 用戶表
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL UNIQUE
);-- 個人信息表
CREATE TABLE user_profiles(user_id INT PRIMARY KEY,address VARCHAR(255),phone VARCHAR(20),FOREIGN KEY (user_id) REFERENCES users(id)
);

一對多關系

一個表中的一條記錄對應另一個表中的多條記錄。例如，訂單表和用戶表之間可以是一對多的關系。

-- 用戶表
CREATE TABLE users(id INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(50) NOT NULL UNIQUE,email VARCHAR(100) NOT NULL UNIQUE
);-- 訂單表
CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,order_number VARCHAR(50) NOT NULL,user_id INT,order_date DATE,FOREIGN KEY (user_id) REFERENCES users(id)
);

多對多關系

一個表中的多條記錄對應另一個表中的多條記錄。例如，學生表和課程表之間可以是多對多的關系。

-- 學生表
CREATE TABLE students(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);-- 課程表
CREATE TABLE courses(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL
);-- 學生選課表（中間表）
CREATE TABLE student_courses(student_id INT,course_id INT,score DECIMAL(5,2),PRIMARY KEY(student_id, course_id),FOREIGN KEY(student_id) REFERENCES students(id),FOREIGN KEY(course_id) REFERENCES courses(id)
);

注意：

• 多對多關系需要通過中間表來實現。
• 中間表包含兩個外鍵，分別引用兩個表的主鍵。

2. 外鍵約束

創建外鍵約束

外鍵約束用于建立表與表之間的關聯關系，確保數據的完整性和一致性。

-- 創建訂單表，包含訂單ID、訂單號、客戶ID、訂單日期字段
CREATE TABLE orders(id INT PRIMARY KEY AUTO_INCREMENT,order_number VARCHAR(50) NOT NULL,customer_id INT,order_date DATE,FOREIGN KEY (customer_id) REFERENCES customers(id)
);

驗證外鍵約束的作用

-- 插入有效員工數據
INSERT INTO employees VALUES(1, '張三', 1), (2, '李四', 2);-- 插入無效員工數據（dept_id不存在）
INSERT INTO employees VALUES(3, '王五', 3); -- 會報錯-- 刪除被引用的部門
DELETE FROM departments WHERE id = 1; -- 會報錯

注意：

• 禁止插入無效的外鍵值。
• 禁止刪除被引用的父表記錄。
• 可以配置級聯操作（如?ON DELETE CASCADE）。

3. 聯合查詢

交叉連接（CROSS JOIN）

返回兩個表的笛卡爾積，即兩個表中所有行的組合。

-- 交叉連接
SELECT * FROM departments CROSS JOIN employees;-- 或者
SELECT * FROM departments, employees;

內連接（INNER JOIN）

返回兩個表中匹配條件的行。

-- 內連接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
INNER JOIN departments d ON e.dept_id = d.id;

左外連接（LEFT JOIN）

返回左表中的所有行，以及右表中匹配條件的行。如果右表中沒有匹配的行，則返回 NULL。

-- 左外連接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
LEFT JOIN departments d ON e.dept_id = d.id;

右外連接（RIGHT JOIN）

返回右表中的所有行，以及左表中匹配條件的行。如果左表中沒有匹配的行，則返回 NULL。

-- 右外連接
SELECT e.id, e.name, d.name AS dept_name, e.salary 
FROM employees e 
RIGHT JOIN departments d ON e.dept_id = d.id;

---

二. 復雜查詢

什么是復雜查詢？

復雜查詢是指通過子查詢、聚合函數、分組等手段進行的高級查詢操作。它可以幫助我們從大量數據中提取所需的信息。

核心內容：

• 子查詢
• 聚合函數
• 分組查詢

1. 子查詢

基本子查詢

嵌套在其他 SQL 語句中的查詢。它可以返回單個值、多個值或表。

-- 標量子查詢：查詢工資高于平均工資的員工
SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees
);-- 列子查詢：查詢技術部和市場部的員工
SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE name IN ('技術部', '市場部')
);

ALL 關鍵字

表示與子查詢返回的所有值進行比較。

-- 查詢工資高于所有市場部員工的員工
SELECT * FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = (SELECT id FROM departments WHERE name = '市場部')
);

ANY/SOME 關鍵字

表示與子查詢返回的任意一個值進行比較。

-- 查詢工資高于市場部任意一個員工的員工
SELECT * FROM employees WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = (SELECT id FROM departments WHERE name = '市場部')
);

EXISTS 關鍵字

表示子查詢是否返回結果集。

-- 查詢有員工的部門
SELECT * FROM departments d WHERE EXISTS (SELECT * FROM employees e WHERE e.dept_id = d.id
);

2. 聚合函數

聚合函數用于統計、求和、平均值等操作。

-- 統計學生總數
SELECT COUNT(*) AS 學生總數 FROM students;-- 計算總成績
SELECT SUM(score) AS 總成績 FROM students;-- 最高分和最低分
SELECT MAX(score) AS 最高分, MIN(score) AS 最低分 FROM students;-- 按班級分組，統計每個班級的學生數量
SELECT class_id, COUNT(*) AS 學生數量 FROM students GROUP BY class_id;-- 按班級分組，計算每個班級的平均成績
SELECT class_id, AVG(score) AS 平均成績 FROM students GROUP BY class_id;-- 分組后篩選，只顯示學生數量大于2的班級
SELECT class_id, COUNT(*) AS 學生數量 
FROM students 
GROUP BY class_id 
HAVING COUNT(*) > 2;

3. 自關聯查詢

自關聯查詢是指表與自身進行連接查詢，通常用于處理層次結構數據。

-- 創建員工表，包含上級ID
CREATE TABLE employees(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,manager_id INT
);-- 插入數據
INSERT INTO employees VALUES (1, '張三', NULL), (2, '李四', 1), (3, '王五', 1), (4, '趙六', 2);-- 自關聯查詢，查詢員工及其上級
SELECT e.name AS 員工, m.name AS 上級
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

---

三. 總結與最佳實踐

總結

操作類型	說明
一對一關系	一個表中的一條記錄對應另一個表中的一條記錄
一對多關系	一個表中的一條記錄對應另一個表中的多條記錄
多對多關系	一個表中的多條記錄對應另一個表中的多條記錄，需通過中間表實現
外鍵約束	建立表與表之間的關聯關系，確保數據的完整性和一致性
交叉連接	返回兩個表的笛卡爾積
內連接	返回兩個表中匹配條件的行
左外連接	返回左表中的所有行，以及右表中匹配條件的行
右外連接	返回右表中的所有行，以及左表中匹配條件的行
子查詢	嵌套在其他 SQL 語句中的查詢
聚合函數	用于統計、求和、平均值等操作
自關聯查詢	表與自身進行連接查詢

最佳實踐

• 合理使用多表關系：根據業務需求選擇合適的關系類型。
• 靈活運用聯合查詢：根據需求選擇合適的連接方式。
• 優化復雜查詢性能：避免不必要的復雜查詢，適當使用索引。

一句話總結：

多表操作幫助我們構建復雜的數據庫模型，而復雜查詢則是我們的“數據探索工具”，幫助我們在數據庫中高效地獲取所需信息。

---

結尾

通過這篇博客，我們詳細講解了 MySQL 中多表操作和復雜查詢的核心概念和使用方法。