MySQL SQL 語法完整參考手冊：DDL、DML、查詢、索引、交易

一、資料定義語言 (DDL)

1. 建立資料庫

CREATE DATABASE db_name
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_general_ci;

2. 建立資料表

CREATE TABLE table_name (
    col1 datatype [NOT NULL] [DEFAULT default_val] [AUTO_INCREMENT] [PRIMARY KEY],
    col2 datatype [NOT NULL] [DEFAULT default_val],
    ...
    CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent_tbl(parent_col) ON DELETE CASCADE,
    INDEX idx_name (col1, col2),
    UNIQUE KEY uk_name (col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

常用資料類型：

3. 修改表結構

-- 新增欄位
ALTER TABLE tbl ADD COLUMN col_name datatype [FIRST|AFTER col2];

-- 修改欄位
ALTER TABLE tbl MODIFY COLUMN col_name new_datatype;
ALTER TABLE tbl CHANGE COLUMN old_name new_name new_datatype;

-- 刪除欄位
ALTER TABLE tbl DROP COLUMN col_name;

-- 新增/刪除索引
ALTER TABLE tbl ADD INDEX idx_name (col1, col2);
ALTER TABLE tbl DROP INDEX idx_name;

-- 新增/刪除外鍵
ALTER TABLE tbl ADD CONSTRAINT fk_name FOREIGN KEY (col) REFERENCES parent(col);
ALTER TABLE tbl DROP FOREIGN KEY fk_name;

4. 刪除物件

DROP TABLE [IF EXISTS] tbl_name;
DROP DATABASE [IF EXISTS] db_name;
DROP INDEX idx_name ON tbl_name;

二、資料操作語言 (DML)

1. 插入資料

-- 基本插入
INSERT INTO tbl (col1, col2) VALUES (val1, val2), (val3, val4);

-- 從查詢插入
INSERT INTO tbl (col1, col2) SELECT col_a, col_b FROM other_tbl WHERE cond;

-- 忽略重複鍵
INSERT IGNORE INTO tbl (col1, col2) VALUES (val1, val2);

-- UPSERT (MySQL 8.0.19+ 標準語法)
INSERT INTO tbl (pk, col1) VALUES (1, 'a')
ON DUPLICATE KEY UPDATE col1 = VALUES(col1);

2. 查詢資料

-- 基本
SELECT col1, col2 FROM tbl WHERE cond ORDER BY col2 DESC LIMIT 10;

-- 聚合
SELECT COUNT(*), SUM(col), AVG(col), MAX(col), MIN(col) FROM tbl WHERE cond;

-- 分組
SELECT col1, COUNT(*) FROM tbl GROUP BY col1 HAVING COUNT(*) > 1;

-- 多表聯接
SELECT t1.col1, t2.col2
FROM tbl1 t1
INNER JOIN tbl2 t2 ON t1.fk = t2.pk
LEFT JOIN tbl3 t3 ON t1.fk2 = t3.pk
WHERE t1.cond = 1;

-- 子查詢 / 衍生表
SELECT * FROM tbl WHERE col IN (SELECT col FROM other WHERE cond);
SELECT * FROM tbl WHERE EXISTS (SELECT 1 FROM other WHERE other.fk = tbl.pk);

-- 集合運算
SELECT col FROM tbl1 UNION [ALL] SELECT col FROM tbl2;

3. 更新資料

UPDATE tbl SET col1 = val1, col2 = val2 WHERE cond;
-- 多表更新
UPDATE tbl1 t1 JOIN tbl2 t2 ON t1.fk = t2.pk
SET t1.col = t2.col WHERE t1.cond = 1;

4. 刪除資料

DELETE FROM tbl WHERE cond;
-- 多表刪除
DELETE t1 FROM tbl1 t1 JOIN tbl2 t2 ON t1.fk = t2.pk WHERE t2.cond = 1;
-- 清空表 (重置自增、不可回滾)
TRUNCATE TABLE tbl;

三、進階查詢技巧

1. 視窗函數 (MySQL 8.0+)

SELECT 
    id, 
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
    RANK() OVER (ORDER BY salary DESC) AS rk,
    LAG(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS prev_sal
FROM employees;

2. 共用資料表運算式 (CTE)

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

3. JSON 操作 (5.7+)

-- 提取
SELECT json_col->>'$.path' AS val FROM tbl;
-- 修改
UPDATE tbl SET json_col = JSON_SET(json_col, '$.path', 'new_val') WHERE id = 1;
-- 索引 (虛擬欄位 + 索引)
ALTER TABLE tbl ADD COLUMN v_name VARCHAR(50) GENERATED ALWAYS AS (json_col->>'$.name');
CREATE INDEX idx_v_name ON tbl(v_name);

四、索引與效能

1. 索引類型

• B+ Tree：預設、支援 =、>、<、>=、<=、BETWEEN、LIKE 'prefix%'、ORDER BY、GROUP BY
• Hash：Memory 引擎、僅支援 =、IN
• Fulltext：MyISAM/InnoDB (5.6+)、自然語言/布林模式
• Spatial (R-Tree)：幾何資料

2. 索引建立原則

• WHERE/JOIN/ORDER BY/GROUP BY 欄位
• 高選擇性 (基數大) 優先
• 複合索引遵循最左前綴 (a,b,c)
• 覆蓋索引：查詢欄位全含於索引 → 避免回表
• 避免過度索引 (寫入放大、空間浪費)

3. 索引維護

ANALYZE TABLE tbl;           -- 更新統計資訊
OPTIMIZE TABLE tbl;          -- 整理碎片、重建索引 (等同 ALTER TABLE ... ENGINE=InnoDB)
SHOW INDEX FROM tbl;         -- 查看索引資訊

五、交易控制

START TRANSACTION;
    INSERT INTO orders ...;
    UPDATE inventory SET qty = qty - 1 WHERE sku = 'ABC';
    INSERT INTO order_items ...;
COMMIT;  -- 或 ROLLBACK;

-- 隔離等級
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; -- 預設
-- READ UNCOMMITTED / READ COMMITTED / REPEATABLE READ / SERIALIZABLE

-- 保存點
SAVEPOINT sp1;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

六、日期比較跨資料庫差異速查

七、常用管理指令

SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE tbl\G
DESCRIBE tbl;  -- 或 DESC tbl
SHOW PROCESSLIST;
KILL connection_id;
SHOW VARIABLES LIKE 'max_connections';
SET GLOBAL max_connections = 500;
FLUSH PRIVILEGES;
ANALYZE TABLE tbl;
CHECK TABLE tbl;
REPAIR TABLE tbl;  -- MyISAM

總結

此手冊涵蓋 MySQL 核心 SQL 語法。實戰中請配合 EXPLAIN 驗證執行計畫、慢查詢日誌 發現問題、參數化查詢 防注入、備份演練 確保可恢復。語法細節請參考官方文檔對應版本章節。