MySQL 基礎知識體系全景圖：從 SQL 語法到存儲過程、觸發器、事務與優化

MySQL 基礎知識體系全景圖：從 SQL 語法到存儲過程、觸發器、事務與優化

本文系統梳理 MySQL 核心知識體系，涵蓋 SQL 語言分類、數據定義與操作、完整性約束、字符類型、核心函數、流程控制、子查詢、表連接、事務、視圖、觸發器、存儲過程、索引、臨時表等 23 大模塊，附大量實戰代碼片段，構建從入門到進階的完整知識地圖。

1. SQL 語言三大分類

MySQL 語法特點： 字符串用單引號 '...'；賦值與比較均用 =；不區分大小寫；無布爾類型（用 TINYINT(1)）；邏輯運算 AND/OR/NOT 或 &&/||/!。

2. 數據庫與表創建實戰

創建數據庫（指定字符集、路徑、文件組）

CREATE DATABASE `school` 
DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

創建表（字段類型、約束、主外鍵）

CREATE TABLE `teacher` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '主鍵',
  `name` VARCHAR(50) NOT NULL COMMENT '姓名',
  `class_id` INT NOT NULL COMMENT '班級FK',
  `gender` BIT NOT NULL COMMENT '性別',
  `age` INT COMMENT '年齡',
  `salary` DECIMAL(10,2) COMMENT '薪資',
  `birthday` DATETIME COMMENT '生日',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`),
  CONSTRAINT `fk_teacher_class` FOREIGN KEY (`class_id`) REFERENCES `classes`(`class_id`)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教師表';

3. 數據完整性約束體系

外鍵級聯動作： CASCADE（級聯刪除/更新）、SET NULL、SET DEFAULT、RESTRICT/NO ACTION（默認阻止）。操作順序： 增刪改查遵循「先主後從、先從後主」原則。

4. 字符類型深度解析

關鍵函數： LEN() 字符數，DATALENGTH() 字節數。CHAR 不回收空間，VARCHAR 自動收縮。

5. 核心 DML 操作模板

插入

-- 單行
INSERT INTO teacher (name, class_id, gender, age, salary, birthday) 
VALUES ('張三', 1, 1, 30, 5000, '1990-01-01');

-- 批量（高效）
INSERT INTO teacher (name, class_id, gender, age, salary, birthday) VALUES
('李四', 1, 0, 28, 4800, '1992-05-10'),
('王五', 2, 1, 35, 6000, '1985-11-20');

-- 複制表數據
INSERT INTO teacher_archive SELECT * FROM teacher WHERE age > 40;

更新

-- 單表
UPDATE teacher SET salary = salary * 1.1 WHERE class_id = 1;

-- 多表連接更新 (MySQL 特有語法)
UPDATE teacher t
JOIN classes c ON t.class_id = c.class_id
SET t.salary = t.salary + 500
WHERE c.class_name = '重點班';

刪除

DELETE FROM teacher WHERE age > 60;           -- 逐行刪除，寫日志，觸發器生效
TRUNCATE TABLE teacher_archive;               -- 瞬時清空，重置自增，無日志，不可回滾

6. 查詢進階：聚合、分組、模糊、分頁

-- 聚合函數（自動過濾 NULL）
SELECT COUNT(*), AVG(salary), MAX(age), MIN(birthday) FROM teacher;

-- 分組統計 + HAVING 過濾組
SELECT class_id, COUNT(*) AS cnt FROM teacher 
GROUP BY class_id HAVING cnt >= 5 ORDER BY cnt DESC;

-- 模糊查詢
SELECT * FROM teacher WHERE name LIKE '張%';           -- 張開頭
SELECT * FROM teacher WHERE name LIKE '%老師%';        -- 包含老師
SELECT * FROM teacher WHERE name LIKE '_三';           -- 兩字且第二字為三
SELECT * FROM teacher WHERE name REGEXP '^張.*[0-9]$'; -- 正則

-- NULL 處理
SELECT name, IFNULL(phone, '未填寫') AS phone FROM teacher;
SELECT * FROM teacher WHERE phone IS NULL / IS NOT NULL;

-- 分頁 (LIMIT offset, count)
SELECT * FROM teacher ORDER BY id LIMIT 0, 10;   -- 第 1 頁
SELECT * FROM teacher ORDER BY id LIMIT 10, 10;  -- 第 2 頁

7. 流程控制：CASE、IF、WHILE、LOOP

CASE 表達式（查詢中轉換值）

-- 簡單 CASE（等值判斷）
SELECT name, 
  CASE class_id WHEN 1 THEN '一班' WHEN 2 THEN '二班' ELSE '其他' END AS class_name
FROM teacher;

-- 搜索 CASE（範圍/表達式判斷，支援 NULL 判斷）
SELECT name, age,
  CASE 
    WHEN age < 30 THEN '青年'
    WHEN age BETWEEN 30 AND 45 THEN '中堅'
    WHEN age IS NULL THEN '未知'
    ELSE '資深'
  END AS age_group
FROM teacher;

存儲過程流程控制

-- IF ELSE
IF avg_score >= 60 THEN
  SELECT '及格';
ELSE
  SELECT '不及格';
END IF;

-- WHILE 循環
WHILE counter < total DO
  UPDATE scores SET value = value + 2 WHERE subject_id = sub_id AND value < 60;
  SET counter = (SELECT COUNT(*) FROM scores WHERE subject_id = sub_id AND value < 60);
END WHILE;

-- LOOP + LEAVE + ITERATE
loop_label: LOOP
  SET i = i + 1;
  IF i > 10 THEN LEAVE loop_label; END IF;
  IF i % 2 = 0 THEN ITERATE loop_label; END IF;
  -- 處理奇數
END LOOP;

8. 子查詢三大用法

1. 條件子查詢： WHERE col = (SELECT ...) / IN (SELECT ...) / EXISTS (SELECT ...)
2. 派生表（FROM 子查詢）： SELECT * FROM (SELECT ... ) t WHERE ... —— 必須起別名，列必須命名
3. 列子查詢： SELECT (SELECT name FROM user WHERE id=t.uid) AS uname FROM orders t

分頁優化： ROW_NUMBER() OVER (ORDER BY id) 窗口函數（8.0+）替代 LIMIT offset, count 深分頁性能問題。

9. 表連接 JOIN 全圖解

-- 內連接：雙方匹配
SELECT s.name, c.class_name FROM student s INNER JOIN class c ON s.class_id = c.id;

-- 左連接：左表全保留，右表無匹配則 NULL
SELECT s.name, c.class_name FROM student s LEFT JOIN class c ON s.class_id = c.id;

-- 右連接：右表全保留
SELECT s.name, c.class_name FROM student s RIGHT JOIN class c ON s.class_id = c.id;

-- 全連接：左右並集（MySQL 不支援 FULL JOIN，用 UNION 模擬）
SELECT * FROM student LEFT JOIN class ON ...
UNION
SELECT * FROM student RIGHT JOIN class ON ...;

10. 事務 ACID 與實戰模板

START TRANSACTION;
  UPDATE account SET balance = balance - 500 WHERE user = 'A';
  UPDATE account SET balance = balance + 500 WHERE user = 'B';
  -- 檢查錯誤
  IF @@ERROR <> 0 THEN ROLLBACK; ELSE COMMIT; END IF;
-- 或
SAVEPOINT sp1;
  -- 操作
ROLLBACK TO sp1;  -- 回滾到保存點
RELEASE SAVEPOINT sp1;

隔離級別： READ UNCOMMITTED < READ COMMITTED < REPEATABLE READ (默認) < SERIALIZABLE。MVCC 實現 RR 級別下的非鎖定讀。

11. 視圖：虛擬表封裝複雜查詢

CREATE VIEW v_teacher_class AS
SELECT t.id, t.name, c.class_name 
FROM teacher t JOIN class c ON t.class_id = c.id;

SELECT * FROM v_teacher_class WHERE class_name = '一班';
-- 可更新視圖條件：單表、無聚合、無 DISTINCT、無 GROUP BY、包含 PK

12. 觸發器：事件驅動自動化

-- AFTER INSERT：新增後同步統計
CREATE TRIGGER tr_class_after_insert AFTER INSERT ON class
FOR EACH ROW
BEGIN
  UPDATE school_stat SET class_count = class_count + 1 WHERE school_id = NEW.school_id;
END;

-- 兩大臨時表：NEW (INSERT/UPDATE 後), OLD (UPDATE/DELETE 前)
CREATE TRIGGER tr_teacher_audit BEFORE UPDATE ON teacher
FOR EACH ROW
BEGIN
  IF NEW.salary <> OLD.salary THEN
    INSERT INTO salary_log (teacher_id, old_salary, new_salary, change_time) 
    VALUES (OLD.id, OLD.salary, NEW.salary, NOW());
  END IF;
END;

13. 存儲過程：業務邏輯封裝與復用

CREATE PROCEDURE sp_get_teacher_by_class (
  IN p_class_name VARCHAR(50),
  IN p_gender BIT,
  OUT p_total INT
)
BEGIN
  DECLARE v_class_id INT;
  SELECT id INTO v_class_id FROM class WHERE name = p_class_name;
  
  SELECT * FROM teacher WHERE class_id = v_class_id AND gender = p_gender;
  SELECT COUNT(*) INTO p_total FROM teacher WHERE class_id = v_class_id AND gender = p_gender;
END;

-- 調用
CALL sp_get_teacher_by_class('一班', 1, @total);
SELECT @total;

分頁存儲過程模板

CREATE PROCEDURE sp_page_teacher (
  IN p_page_index INT, IN p_page_size INT, OUT p_total_pages INT
)
BEGIN
  DECLARE v_total INT;
  SELECT COUNT(*) INTO v_total FROM teacher;
  SET p_total_pages = CEILING(v_total / p_page_size);
  
  SELECT * FROM teacher 
  ORDER BY id 
  LIMIT (p_page_index - 1) * p_page_size, p_page_size;
END;

14. 索引策略

• 聚簇索引： InnoDB 主鍵即聚簇索引，葉子節點存完整行數據。無主鍵則選第一個非空唯一鍵，否則生成隱式 6 字節 RowID。
• 非聚簇索引： 葉子節點存主鍵值，查詢需「回表」查聚簇索引。
• 覆蓋索引： 查詢列全包含在索引中，避免回表。
• 最左前綴原則： 聯合索引 (a,b,c) 支援 a/a,b/a,b,c 查詢。

15. 臨時表與表變量

總結

MySQL 知識體系如金字塔：SQL 基礎語法 為基石，約束與類型 保障數據質量，查詢與函數 滿足業務檢索，流程控制 賦予邏輯靈活性，事務 保證一致性，視圖/觸發器/存儲過程 封裝復用業務規則，索引 決定性能上限。系統掌握這 15 大模塊，並能靈活組合解決實際問題，即為合格的 MySQL 開發與運維工程師。