MySQL 資料操縱語言（DML）詳解：SELECT、INSERT、UPDATE、DELETE 完整語法與範例

SELECT 語法完整結構

SELECT [STRAIGHT_JOIN]
    [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
    [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
    [DISTINCT | DISTINCTROW | ALL]
    select_expr, ...
    [INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position} [ASC | DESC], ...]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [FOR UPDATE | LOCK IN SHARE MODE]]

關鍵子句執行順序（邏輯順序 ≠ 書寫順序）

1. FROM / JOIN → 確定資料來源
2. WHERE → 列級過濾
3. GROUP BY → 分組彙總
4. HAVING → 群組過濾
5. SELECT → 選取輸出欄位
6. DISTINCT → 去重
7. ORDER BY → 排序
8. LIMIT → 限制筆數

注意：WHERE 不可使用欄位別名，HAVING 與 ORDER BY 可使用。

實用範例

1. 別名與排序

SELECT CONCAT(last_name, ', ', first_name) AS full_name
FROM employees
ORDER BY full_name;

2. 索引提示

SELECT * FROM orders USE INDEX (idx_customer_id)
WHERE customer_id = 123;

若 EXPLAIN 顯示用錯索引，可用 USE INDEX、IGNORE INDEX、FORCE INDEX 強制指定。

3. LIMIT 分頁

SELECT * FROM articles ORDER BY created_at DESC LIMIT 10 OFFSET 20;  -- 第 3 頁，每頁 10 筆

INSERT / UPDATE / DELETE 核心語法

INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name, ...)]
    {VALUES | VALUE} (value_list), ...
    [ON DUPLICATE KEY UPDATE col_name=expr, ...]

UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col_name = expr, ...
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

最佳實踐提醒

• 永遠在 UPDATE/DELETE 加上 WHERE，避免誤傷全表；
• 大量刪除優先用 TRUNCATE 或分批 DELETE ... LIMIT 1000；
• INSERT ... ON DUPLICATE KEY UPDATE 可替代 REPLACE INTO，避免主鍵刪除重建導致自增 ID 跳躍。