MySQL高級特性詳解

一、自關聯查詢

概念

自關聯查詢是指一個表與它自己進行連接的查詢。通常用于處理具有層級關系或遞歸結構的數據。

應用場景

員工與上級關系
分類的父子關系
地區的層級關系

示例

-- 創建員工表
CREATE TABLE employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(50),manager_id INT,FOREIGN KEY (manager_id) REFERENCES employees(emp_id)
);-- 插入示例數據
INSERT INTO employees VALUES 
(1, '張總', NULL),
(2, '李經理', 1),
(3, '王主管', 2),
(4, '趙員工', 3),
(5, '錢員工', 3);-- 查詢每個員工及其直接上級
SELECT e1.emp_name AS '員工',e2.emp_name AS '上級'
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.emp_id;-- 查詢所有下屬超過1人的管理者
SELECT e2.emp_name AS '管理者',COUNT(e1.emp_id) AS '下屬人數'
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.emp_id
GROUP BY e2.emp_id, e2.emp_name
HAVING COUNT(e1.emp_id) > 1;

二、子查詢操作

概念

子查詢是嵌套在其他SQL語句中的SELECT語句，可以出現在WHERE、FROM、SELECT等子句中。

子查詢分類

1. 標量子查詢（返回單個值）

-- 查詢工資高于平均工資的員工
SELECT emp_name, salary 
FROM employees 
WHERE salary > (SELECT AVG(salary) FROM employees);

2. 列子查詢（返回一列多行）

-- 使用IN操作符
SELECT * FROM employees 
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = '北京');-- 使用ANY/ALL操作符
SELECT * FROM employees 
WHERE salary > ANY (SELECT salary FROM employees WHERE dept_id = 10);

3. 行子查詢（返回一行多列）

-- 查詢與張三相同部門和職位的員工
SELECT * FROM employees 
WHERE (dept_id, position) = (SELECT dept_id, position FROM employees WHERE emp_name = '張三'
);

4. 表子查詢（返回多行多列）

-- FROM子句中的子查詢
SELECT t.dept_name, t.avg_salary
FROM (SELECT d.dept_name, AVG(e.salary) AS avg_salaryFROM employees eJOIN departments d ON e.dept_id = d.dept_idGROUP BY d.dept_name
) t
WHERE t.avg_salary > 10000;

EXISTS子查詢

-- 查詢有員工的部門
SELECT * FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);-- 查詢沒有下屬的員工
SELECT * FROM employees e1
WHERE NOT EXISTS (SELECT 1 FROM employees e2 WHERE e2.manager_id = e1.emp_id
);

三、窗口函數

概念

窗口函數在保留原表所有行的基礎上，為每一行計算聚合值。與GROUP BY不同，它不會減少返回的行數。

基本語法

函數名() OVER ([PARTITION BY 列名][ORDER BY 列名][窗口子句]
)

常用窗口函數

1. 聚合窗口函數

-- 計算累計銷售額
SELECT order_date,amount,SUM(amount) OVER (ORDER BY order_date) AS cumulative_sum,AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;-- 按部門計算工資占比
SELECT emp_name,dept_id,salary,salary / SUM(salary) OVER (PARTITION BY dept_id) * 100 AS salary_percentage
FROM employees;

2. 排名函數

-- ROW_NUMBER(): 生成唯一序號
-- RANK(): 相同值同排名，下一個排名會跳過
-- DENSE_RANK(): 相同值同排名，下一個排名連續SELECT emp_name,dept_id,salary,ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank_num,DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_num
FROM employees;-- NTILE(): 將數據分成N組
SELECT emp_name,salary,NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

3. 偏移函數

-- LAG/LEAD: 訪問前后行數據
SELECT month,sales,LAG(sales, 1) OVER (ORDER BY month) AS prev_month_sales,LEAD(sales, 1) OVER (ORDER BY month) AS next_month_sales,sales - LAG(sales, 1) OVER (ORDER BY month) AS month_over_month_change
FROM monthly_sales;-- FIRST_VALUE/LAST_VALUE: 獲取窗口內第一個/最后一個值
SELECT emp_name,dept_id,salary,FIRST_VALUE(emp_name) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS highest_paid,LAST_VALUE(emp_name) OVER (PARTITION BY dept_id ORDER BY salary DESCRANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lowest_paid
FROM employees;

四、MySQL常用內置函數

1. 時間日期函數

-- 獲取當前時間
SELECT NOW(), CURDATE(), CURTIME();-- 日期格式化
SELECT DATE_FORMAT(NOW(), '%Y年%m月%d日 %H:%i:%s');-- 日期計算
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS '7天后',DATE_SUB(NOW(), INTERVAL 1 MONTH) AS '1個月前',DATEDIFF('2024-12-31', NOW()) AS '距離年底天數',TIMESTAMPDIFF(YEAR, '1990-01-01', NOW()) AS '年齡';-- 提取日期部分
SELECT YEAR(NOW()) AS '年',MONTH(NOW()) AS '月',DAY(NOW()) AS '日',DAYNAME(NOW()) AS '星期',WEEKDAY(NOW()) AS '星期索引';

2. 字符串函數

-- 字符串連接
SELECT CONCAT('Hello', ' ', 'World');
SELECT CONCAT_WS(',', 'A', 'B', 'C'); -- 使用分隔符-- 字符串長度和位置
SELECT LENGTH('你好'), CHAR_LENGTH('你好'); -- 字節長度vs字符長度
SELECT LOCATE('world', 'hello world'); -- 查找位置-- 字符串截取
SELECT LEFT('abcdefg', 3) AS '左截取',RIGHT('abcdefg', 3) AS '右截取',SUBSTRING('abcdefg', 2, 3) AS '中間截取';-- 字符串轉換
SELECT UPPER('hello') AS '大寫',LOWER('HELLO') AS '小寫',REPLACE('hello world', 'world', 'MySQL') AS '替換',TRIM('  hello  ') AS '去空格',REVERSE('hello') AS '反轉';

3. 數學函數

-- 基礎數學運算
SELECT ABS(-10) AS '絕對值',CEIL(4.3) AS '向上取整',FLOOR(4.7) AS '向下取整',ROUND(4.567, 2) AS '四舍五入',TRUNCATE(4.567, 2) AS '截斷';-- 高級數學函數
SELECT POW(2, 3) AS '冪運算',SQRT(16) AS '平方根',MOD(10, 3) AS '取余',RAND() AS '隨機數',FLOOR(RAND() * 100) AS '0-99隨機整數';

五、CASE WHEN條件判斷

簡單CASE語法

SELECT emp_name,salary,CASE dept_idWHEN 1 THEN '技術部'WHEN 2 THEN '銷售部'WHEN 3 THEN '人事部'ELSE '其他部門'END AS dept_name
FROM employees;

搜索CASE語法

-- 工資等級劃分
SELECT emp_name,salary,CASE WHEN salary >= 20000 THEN '高級'WHEN salary >= 10000 THEN '中級'WHEN salary >= 5000 THEN '初級'ELSE '實習'END AS salary_level
FROM employees;-- 條件統計
SELECT dept_id,COUNT(CASE WHEN salary >= 10000 THEN 1 END) AS high_salary_count,COUNT(CASE WHEN salary < 10000 THEN 1 END) AS low_salary_count,SUM(CASE WHEN gender = '男' THEN salary ELSE 0 END) AS male_total_salary,SUM(CASE WHEN gender = '女' THEN salary ELSE 0 END) AS female_total_salary
FROM employees
GROUP BY dept_id;-- 動態排序
SELECT * FROM employees
ORDER BY CASE WHEN @sort_type = 'name' THEN emp_name END,CASE WHEN @sort_type = 'salary' THEN salary END DESC;

六、事務概念及應用

事務的概念

事務是一組不可分割的操作單元，要么全部成功，要么全部失敗。

ACID特性

原子性(Atomicity)：事務是不可分割的最小操作單元
一致性(Consistency)：事務完成后，數據必須處于一致狀態
隔離性(Isolation)：多個事務之間相互獨立
持久性(Durability)：事務一旦提交，改變是永久的

事務操作

-- 開啟事務
START TRANSACTION; -- 或 BEGIN;-- 執行SQL操作
UPDATE accounts SET balance = balance - 1000 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 1000 WHERE account_id = 2;-- 提交或回滾
COMMIT; -- 提交事務
ROLLBACK; -- 回滾事務-- 設置保存點
SAVEPOINT point1;
-- 回滾到保存點
ROLLBACK TO point1;

事務隔離級別

-- 查看當前隔離級別
SELECT @@transaction_isolation;-- 設置隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- 讀未提交
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- 讀已提交
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;  -- 可重復讀（MySQL默認）
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;      -- 串行化

應用場景示例

-- 轉賬操作
DELIMITER $$
CREATE PROCEDURE transfer_money(IN from_account INT,IN to_account INT,IN amount DECIMAL(10,2)
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGINROLLBACK;SELECT 'Transaction failed, rolled back' AS message;END;START TRANSACTION;UPDATE accounts SET balance = balance - amount WHERE account_id = from_account AND balance >= amount;IF ROW_COUNT() = 0 THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';END IF;UPDATE accounts SET balance = balance + amount WHERE account_id = to_account;COMMIT;SELECT 'Transaction successful' AS message;
END$$
DELIMITER ;

七、索引概念及應用

索引的概念

索引是幫助MySQL高效獲取數據的數據結構，類似于書的目錄。

索引類型

主鍵索引(PRIMARY KEY)：唯一且不為NULL
唯一索引(UNIQUE)：唯一，可以為NULL
普通索引(INDEX)：最基本的索引
全文索引(FULLTEXT)：用于全文搜索
組合索引：多列組成的索引

索引操作

-- 創建索引
CREATE INDEX idx_emp_name ON employees(emp_name);
CREATE UNIQUE INDEX idx_emp_email ON employees(email);
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary); -- 組合索引-- 查看索引
SHOW INDEX FROM employees;-- 刪除索引
DROP INDEX idx_emp_name ON employees;-- 使用EXPLAIN分析查詢
EXPLAIN SELECT * FROM employees WHERE emp_name = '張三';

索引使用原則

-- 1. 最左前綴原則（組合索引）
-- 假設有索引 (a, b, c)
SELECT * FROM table WHERE a = 1; -- 使用索引
SELECT * FROM table WHERE a = 1 AND b = 2; -- 使用索引
SELECT * FROM table WHERE b = 2; -- 不使用索引-- 2. 避免索引失效的情況
-- 函數操作
SELECT * FROM employees WHERE YEAR(hire_date) = 2024; -- 索引失效
SELECT * FROM employees WHERE hire_date >= '2024-01-01' AND hire_date < '2025-01-01'; -- 使用索引-- 類型不匹配
SELECT * FROM employees WHERE emp_id = '123'; -- 如果emp_id是整數，可能失效-- LIKE通配符
SELECT * FROM employees WHERE emp_name LIKE '%張%'; -- 索引失效
SELECT * FROM employees WHERE emp_name LIKE '張%'; -- 使用索引

索引優化建議

選擇性高的列適合建索引
頻繁作為查詢條件的列建索引
經常需要排序的列建索引
避免過多索引，影響寫入性能
定期分析和優化索引

八、視圖概念及應用

視圖的概念

視圖是一個虛擬表，其內容由查詢定義。視圖不存儲數據，只存儲SQL查詢語句。

視圖的優點

簡化復雜查詢
數據安全性（隱藏敏感列）
邏輯數據獨立性

視圖操作

-- 創建視圖
CREATE VIEW v_emp_dept AS
SELECT e.emp_id,e.emp_name,e.salary,d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;-- 使用視圖
SELECT * FROM v_emp_dept WHERE salary > 10000;-- 創建可更新視圖
CREATE VIEW v_emp_simple AS
SELECT emp_id, emp_name, salary
FROM employees
WHERE dept_id = 1
WITH CHECK OPTION; -- 確保通過視圖的修改符合WHERE條件-- 修改視圖
ALTER VIEW v_emp_dept AS
SELECT e.emp_id,e.emp_name,e.salary,e.hire_date,d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id;-- 刪除視圖
DROP VIEW IF EXISTS v_emp_dept;

視圖應用場景

-- 1. 權限控制：為不同用戶創建不同視圖
CREATE VIEW v_emp_public AS
SELECT emp_id, emp_name, dept_id
FROM employees; -- 隱藏工資信息-- 2. 簡化復雜查詢
CREATE VIEW v_sales_summary AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,SUM(amount) AS total_sales,COUNT(DISTINCT customer_id) AS customer_count,AVG(amount) AS avg_order_value
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m');-- 3. 計算字段
CREATE VIEW v_emp_annual AS
SELECT emp_id,emp_name,salary,salary * 12 AS annual_salary,salary * 0.1 AS bonus
FROM employees;

九、ER模型和3NF三范式

ER模型（實體-關系模型）

基本概念

實體(Entity)：現實世界中的對象，如學生、課程
屬性(Attribute)：實體的特征，如學生的姓名、年齡
關系(Relationship)：實體之間的聯系

關系類型

一對一(1:1)：一個實體對應另一個實體的一個實例
一對多(1:N)：一個實體對應另一個實體的多個實例
多對多(M:N)：多個實體對應另一個實體的多個實例

ER圖示例

-- 學生與課程的多對多關系
-- 學生表
CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50),age INT
);-- 課程表
CREATE TABLE courses (course_id INT PRIMARY KEY,course_name VARCHAR(100),credits INT
);-- 選課表（關系表）
CREATE TABLE enrollments (student_id INT,course_id INT,grade DECIMAL(3,1),PRIMARY KEY (student_id, course_id),FOREIGN KEY (student_id) REFERENCES students(student_id),FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

三范式(3NF)

第一范式(1NF)：原子性

每個字段都是不可分割的原子值。

-- 違反1NF的設計
CREATE TABLE bad_students (student_id INT,student_name VARCHAR(50),phone_numbers VARCHAR(200) -- 存儲多個電話號碼，如"13812345678,13987654321"
);-- 符合1NF的設計
CREATE TABLE students (student_id INT PRIMARY KEY,student_name VARCHAR(50)
);CREATE TABLE student_phones (student_id INT,phone_number VARCHAR(20),phone_type VARCHAR(20),PRIMARY KEY (student_id, phone_number),FOREIGN KEY (student_id) REFERENCES students(student_id)
);

第二范式(2NF)：完全依賴主鍵

非主鍵字段必須完全依賴于主鍵，不能只依賴主鍵的一部分。

-- 違反2NF的設計
CREATE TABLE bad_order_items (order_id INT,product_id INT,quantity INT,product_name VARCHAR(100), -- 只依賴于product_id，不依賴于order_idproduct_price DECIMAL(10,2), -- 只依賴于product_idPRIMARY KEY (order_id, product_id)
);-- 符合2NF的設計
CREATE TABLE products (product_id INT PRIMARY KEY,product_name VARCHAR(100),product_price DECIMAL(10,2)
);CREATE TABLE order_items (order_id INT,product_id INT,quantity INT,PRIMARY KEY (order_id, product_id),FOREIGN KEY (product_id) REFERENCES products(product_id)
);

第三范式(3NF)：消除傳遞依賴

非主鍵字段之間不能有依賴關系，都應該直接依賴于主鍵。

-- 違反3NF的設計
CREATE TABLE bad_employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(50),dept_id INT,dept_name VARCHAR(50), -- 傳遞依賴：dept_name依賴于dept_id，而非直接依賴于emp_iddept_location VARCHAR(100) -- 傳遞依賴
);-- 符合3NF的設計
CREATE TABLE departments (dept_id INT PRIMARY KEY,dept_name VARCHAR(50),dept_location VARCHAR(100)
);CREATE TABLE employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(50),dept_id INT,FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

范式化的優缺點

優點

減少數據冗余
保證數據一致性
節省存儲空間
更新異常少

缺點

查詢時需要更多JOIN操作
可能影響查詢性能

反范式化場景

在某些情況下，為了提高查詢性能，可能會適度違反范式：

-- 適度冗余以提高查詢性能
CREATE TABLE order_summary (order_id INT PRIMARY KEY,order_date DATE,customer_name VARCHAR(100), -- 冗余字段，避免每次都JOINtotal_amount DECIMAL(10,2), -- 計算字段，避免每次都SUMitem_count INT -- 統計字段
);

實戰練習題

綜合練習：電商數據分析

-- 創建示例表
CREATE TABLE orders (order_id INT PRIMARY KEY,customer_id INT,order_date DATE,total_amount DECIMAL(10,2)
);-- 1. 使用窗口函數計算客戶的購買排名
SELECT customer_id,SUM(total_amount) AS total_purchase,RANK() OVER (ORDER BY SUM(total_amount) DESC) AS customer_rank
FROM orders
GROUP BY customer_id;-- 2. 使用子查詢找出消費超過平均值的客戶
SELECT DISTINCT customer_id
FROM orders o1
WHERE (SELECT SUM(total_amount) FROM orders o2 WHERE o2.customer_id = o1.customer_id
) > (SELECT AVG(customer_total) FROM (SELECT SUM(total_amount) AS customer_total FROM orders GROUP BY customer_id) t
);-- 3. 使用CASE WHEN進行客戶分類
SELECT customer_id,SUM(total_amount) AS total_amount,CASE WHEN SUM(total_amount) >= 10000 THEN 'VIP客戶'WHEN SUM(total_amount) >= 5000 THEN '重要客戶'WHEN SUM(total_amount) >= 1000 THEN '普通客戶'ELSE '潛在客戶'END AS customer_level
FROM orders
GROUP BY customer_id;