SQL Basic 關鍵字速查

每個關鍵字一句話解釋＋一個範例。語法以 MySQL 8 為準（DELIMITER、CREATE EVENT、SHOW VARIABLES 都是 MySQL 專屬）。標＋的是原清單沒有、我認為缺了會不好用而補上的。

範例統一用這三張表：

employees(id, name, dept_id, salary, hired_at, manager_id)
departments(id, name)
orders(id, employee_id, amount, created_at)

0. 先記邏輯執行順序（＋）

寫的順序和跑的順序不一樣，這張順序表能直接解釋後面一半的疑問：

FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

推論出來的三件事：

WHERE 在分組前、HAVING 在分組後 → 所以聚合條件只能寫在 HAVING
SELECT 比 WHERE 晚跑 → 所以 WHERE 不能用 AS 取的別名，但 ORDER BY／HAVING 可以
LIMIT 最後才跑 → 它不會讓前面的掃描變少，見 WHERE 決定回傳幾列，索引才決定要看過幾列

1. 查詢骨架

SELECT — 指定要回傳哪些欄位（或運算結果）。
```
SELECT name, salary FROM employees;
```
FROM — 指定資料來源的表（或子查詢、CTE）。
```
SELECT * FROM employees;
```
AS — 給欄位或表取別名，讓輸出欄名或長表名變好讀（表別名可省略 AS）。
```
SELECT e.name AS employee_name, e.salary * 12 AS annual_salary
FROM employees AS e;
```

WHERE — 對單一列過濾，發生在分組之前。

SELECT name FROM employees WHERE salary > 50000;

GROUP BY — 把列依指定欄位收成群組，讓聚合函式對「每一群」算一個值。
```
SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id;
```

HAVING — 對群組過濾（可以用聚合結果當條件），發生在分組之後。

SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > 60000;

ORDER BY — 指定回傳結果的排序（ASC 遞增為預設，DESC 遞減）。
```
SELECT name, salary FROM employees ORDER BY salary DESC, name ASC;
```
DISTINCT（＋） — 去除結果中重複的列。
```
SELECT DISTINCT dept_id FROM employees;
```
LIMIT / OFFSET（＋） — 限制回傳筆數／先跳過幾筆；分頁用。
```
SELECT name FROM employees ORDER BY id LIMIT 20 OFFSET 40;  -- 第 3 頁
```
⚠️ 深分頁不要用 OFFSET → OFFSET 分頁的總成本是 O(頁數平方)，cursor 分頁才是 O(頁數)
UNION — 把兩個查詢的結果上下疊起來，並去除重複列（欄位數與型別要對得上）。
```
SELECT name FROM employees
UNION
SELECT name FROM departments;
```
UNION ALL（＋） — 同樣是疊起來但不去重，因為不用做去重比對所以比 UNION 快；確定不會重複時該用這個。
```
SELECT name FROM employees
UNION ALL
SELECT name FROM departments;
```

2. JOIN（＋，整組補上）

沒有 JOIN 就只能一張表一張表查，這組是實務最常用的。成本觀念見 JOIN 貴在每一列都要去另一張表配對。

ON — 指定兩張表用什麼條件配對。

INNER JOIN — 只留兩邊都配對成功的列。

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

LEFT JOIN — 左表全留；右表配不到的欄位補 NULL（找「沒有對應資料的那些」時最常用）。

SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;
 
-- 找出沒有任何訂單的員工
SELECT e.name
FROM employees e
LEFT JOIN orders o ON o.employee_id = e.id
WHERE o.id IS NULL;

RIGHT JOIN — 反過來，右表全留；通常改寫成 LEFT JOIN 比較好讀。
CROSS JOIN — 笛卡兒積，左表每一列配右表每一列（列數相乘，小心爆量）。
```
SELECT e.name, d.name FROM employees e CROSS JOIN departments d;
```

SELF JOIN — 同一張表 join 自己，用別名區分（例如查每個人的主管）。

SELECT e.name AS staff, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

3. 條件與運算子

AND — 兩個條件都要成立。

SELECT name FROM employees WHERE salary > 50000 AND dept_id = 3;

OR — 任一條件成立即可（和 AND 混用時用括號講清楚優先序）。
```
SELECT name FROM employees WHERE dept_id = 1 OR dept_id = 2;
```

NOT（＋） — 把條件反過來。

SELECT name FROM employees WHERE NOT dept_id = 3;

BETWEEN — 落在區間內，含兩端（等價於 >= AND <=）。

SELECT name FROM employees WHERE salary BETWEEN 40000 AND 60000;

IN — 值落在一組候選值裡；候選值可以是寫死的清單，也可以是子查詢。

SELECT name FROM employees WHERE dept_id IN (1, 2, 5);
 
-- 子查詢版：在台北的部門的員工
SELECT name FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name LIKE '台北%');

EXISTS（＋） — 子查詢「有沒有任何一列」就回 true；比 IN 適合大量資料，因為找到一筆就能停。
```
SELECT e.name FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.employee_id = e.id);
```
IS NULL — 判斷是否為 NULL。不能寫 = NULL，因為 NULL 代表「未知」，跟任何值比較（含它自己）結果都是未知而不是 true。
```
SELECT name FROM employees WHERE manager_id IS NULL;   -- 沒有主管的人
SELECT name FROM employees WHERE manager_id IS NOT NULL;
```
LIKE（＋） — 字串模糊比對，% 代表任意長度、_ 代表單一字元。
```
SELECT name FROM employees WHERE name LIKE '王%';
```

CASE … WHEN … THEN … ELSE … END — SQL 裡的 if/else，依條件產生不同的值（原清單寫的 CASE THEN）。

SELECT name,
       CASE
         WHEN salary >= 80000 THEN 'high'
         WHEN salary >= 50000 THEN 'mid'
         ELSE 'low'
       END AS salary_band
FROM employees;

常用技巧：塞在聚合函式裡做條件式計數。

SELECT dept_id,
       COUNT(*) AS total,
       SUM(CASE WHEN salary > 60000 THEN 1 ELSE 0 END) AS high_paid
FROM employees GROUP BY dept_id;

COALESCE / IFNULL（＋） — 取第一個不是 NULL 的值，用來給預設值。
```
SELECT name, COALESCE(manager_id, 0) AS manager_id FROM employees;
```

4. 聚合函式（＋）

GROUP BY 的另一半，沒有它分組沒有意義。

COUNT — 計數；COUNT(*) 算列數，COUNT(欄位) 會跳過 NULL。

SELECT COUNT(*) AS rows_all, COUNT(manager_id) AS has_manager FROM employees;

SUM / AVG — 總和／平均（同樣忽略 NULL）。

SELECT dept_id, SUM(salary) AS total, AVG(salary) AS avg FROM employees GROUP BY dept_id;

MIN / MAX — 最小／最大值。

SELECT MIN(hired_at) AS first_hire, MAX(salary) AS top_salary FROM employees;

GROUP_CONCAT — 把群組內的值串成一個字串（MySQL 專屬）。

SELECT dept_id, GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS members
FROM employees GROUP BY dept_id;

5. String Function

LENGTH — 字串長度（MySQL 的 LENGTH 算的是 bytes，要算字元數用 CHAR_LENGTH）。
```
SELECT LENGTH('abc'), CHAR_LENGTH('中文');   -- 3, 2
```
UPPER — 轉全大寫。SELECT UPPER('sql'); -- 'SQL'
LOWER — 轉全小寫。SELECT LOWER('SQL'); -- 'sql'

TRIM — 去掉頭尾空白（也能指定去掉特定字元）。

SELECT TRIM('  hi  ');                  -- 'hi'
SELECT TRIM(BOTH '-' FROM '--hi--');    -- 'hi'

LTRIM — 只去左邊空白。SELECT LTRIM(' hi'); -- 'hi'
RTRIM — 只去右邊空白。SELECT RTRIM('hi '); -- 'hi'
LEFT — 從左取 n 個字元。SELECT LEFT('abcdef', 3); -- 'abc'
RIGHT — 從右取 n 個字元。SELECT RIGHT('abcdef', 2); -- 'ef'
SUBSTRING — 從第 n 個字元起取 m 個（索引從 1 開始，不是 0）。
```
SELECT SUBSTRING('abcdef', 2, 3);   -- 'bcd'
```

REPLACE — 把字串裡所有出現的子字串換掉。

SELECT REPLACE('2026-09-15', '-', '/');   -- '2026/09/15'

LOCATE — 找子字串第一次出現的位置，找不到回 0（所以判斷「有沒有」是 > 0）。
```
SELECT LOCATE('@', 'me@mail.com');   -- 3
```

CONCAT — 串接多個字串；任一個是 NULL 整串就變 NULL（要避開用 CONCAT_WS）。

SELECT CONCAT(name, ' (', dept_id, ')') FROM employees;
SELECT CONCAT_WS(' ', 'a', NULL, 'b');   -- 'a b'

（＋）FORMAT / LPAD — 補位與千分位這種輸出格式化。

SELECT LPAD('7', 3, '0'), FORMAT(1234567.891, 2);   -- '007', '1,234,567.89'

6. Window Function

聚合函式會把多列收成一列；window function 保留每一列，只是額外算一個「看得到同組其他列」的值。

OVER — 宣告這是視窗函式，括號裡定義它看得到的範圍。

SELECT name, salary, AVG(salary) OVER () AS company_avg FROM employees;

PARTITION BY — 把視窗切成群組（概念像 GROUP BY，但不會把列合併掉）。

SELECT name, dept_id, salary,
       AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

ROW_NUMBER — 在視窗內依排序給每列一個不重複的序號（1, 2, 3…）。

-- 每個部門薪水最高的那個人
SELECT * FROM (
  SELECT name, dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn
  FROM employees
) t
WHERE rn = 1;

RANK / DENSE_RANK（＋） — 同樣是排名，但同分處理不同：RANK 同分並列後跳號（1,1,3），DENSE_RANK 不跳號（1,1,2）。

SELECT name, salary,
       RANK()       OVER (ORDER BY salary DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rk
FROM employees;

LAG / LEAD（＋） — 取同一視窗內前一列／後一列的值，算環比、找差異用。

SELECT created_at, amount,
       amount - LAG(amount) OVER (ORDER BY created_at) AS diff_from_prev
FROM orders;

ROWS BETWEEN（＋） — 指定視窗的框（frame），做移動平均、累計加總。

SELECT created_at, amount,
       SUM(amount) OVER (ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

7. CTE

WITH — 先給子查詢取一個名字，再在主查詢裡當表用；把巢狀子查詢攤平成由上而下可讀的步驟。

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;

WITH RECURSIVE（＋） — 讓 CTE 引用自己，用來展開樹狀／階層資料（組織圖、分類目錄）。

WITH RECURSIVE chain AS (
  SELECT id, name, manager_id, 1 AS lvl FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, c.lvl + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain ORDER BY lvl;

8. Temporary Tables / 建表

CREATE TABLE — 建立一張表並定義欄位與型別。

CREATE TABLE departments (
  id   INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL
);

TEMPORARY — 建成暫存表：只有當前連線看得到，連線關掉就自動消失，適合放中間結果。
```
CREATE TEMPORARY TABLE tmp_high_paid AS
SELECT * FROM employees WHERE salary > 80000;
```
CREATE TABLE … AS SELECT（＋） — 直接用查詢結果建表（上面那個寫法就是；只複製資料與欄位，不複製索引）。

（＋）ALTER TABLE / DROP TABLE / TRUNCATE — 改結構／刪整張表／清空資料但保留表。

ALTER TABLE employees ADD COLUMN email VARCHAR(100);
TRUNCATE TABLE tmp_high_paid;   -- 清空，比 DELETE 快但不可 rollback
DROP TABLE tmp_high_paid;

9. 資料寫入（DML）

INSERT INTO — 新增資料列。

INSERT INTO departments (name) VALUES ('Engineering');

VALUES — 接在 INSERT INTO 後面列出要寫進去的值（一次可以塞多列）。
```
INSERT INTO departments (name) VALUES ('Sales'), ('HR'), ('Finance');
```

INSERT INTO … SELECT（＋） — 把查詢結果直接寫進另一張表。

INSERT INTO employees_archive SELECT * FROM employees WHERE hired_at < '2020-01-01';

UPDATE / SET（＋） — 修改既有資料；忘了 WHERE 就會改到全表。
```
UPDATE employees SET salary = salary * 1.05 WHERE dept_id = 3;
```
DELETE（＋） — 刪除資料列，同樣要記得 WHERE。
```
DELETE FROM employees WHERE id = 42;
```

ON DUPLICATE KEY UPDATE（＋） — 主鍵／唯一鍵撞到時改成更新，MySQL 版的 upsert。

INSERT INTO departments (id, name) VALUES (1, 'Eng')
ON DUPLICATE KEY UPDATE name = VALUES(name);

10. Stored Procedure

DELIMITER — 暫時換掉「一句 SQL 結束」的符號（原清單筆誤成 DELTMITER）。因為 procedure 內部本身就有 ;，不換的話 client 會在中途就把語句切斷。
```
DELIMITER $$
-- ... 這裡面的 ; 不會被當成結尾 ...
DELIMITER ;
```

CREATE PROCEDURE — 把一段 SQL 流程存在資料庫裡，之後用名字呼叫。

DELIMITER $$
CREATE PROCEDURE raise_salary(IN p_dept INT, IN p_pct DECIMAL(5,2))
BEGIN
  UPDATE employees SET salary = salary * (1 + p_pct / 100) WHERE dept_id = p_dept;
END$$
DELIMITER ;

BEGIN … END — 把多句 SQL 包成一個程式區塊（procedure、trigger、event 的主體）。
CALL — 執行一個 stored procedure。
```
CALL raise_salary(3, 5);
```
（＋）IN / OUT / INOUT — 參數方向：傳入、傳出、雙向。
```
CREATE PROCEDURE count_dept(IN p_dept INT, OUT p_total INT) ...
```
（＋）DECLARE — 在區塊裡宣告區域變數（必須寫在 BEGIN 之後、其他語句之前）。
```
DECLARE v_count INT DEFAULT 0;
```
（＋）DROP PROCEDURE IF EXISTS — 重建前先移除，開發時幾乎必寫。

11. Trigger

CREATE TRIGGER — 建立觸發器：某張表發生 INSERT/UPDATE/DELETE 時自動跑一段 SQL。
BEFORE — 在該動作寫入之前觸發，所以可以改寫即將寫入的值。
AFTER — 在該動作完成之後觸發，適合寫稽核紀錄、同步其他表。
FOR EACH ROW — 每影響一列就跑一次（MySQL 只支援這種 row-level trigger）。

NEW — 指「即將寫入／更新後」的那一列；對應的 OLD（＋） 是「更新前／被刪除」的那一列。

DELIMITER $$
CREATE TRIGGER trg_employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
  SET NEW.name = TRIM(NEW.name);          -- 寫入前先清掉頭尾空白
END$$
 
CREATE TRIGGER trg_employees_after_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_log (employee_id, old_salary, new_salary)
  VALUES (OLD.id, OLD.salary, NEW.salary);
END$$
DELIMITER ;

⚠️ trigger 是隱形的副作用：讀 application 的程式碼看不到它，debug 時很容易漏掉。會改變業務結果的邏輯盡量別放這裡。

12. Event（排程）

CREATE EVENT — 在資料庫內建立定時任務（MySQL 內建的 cron）。
ON SCHEDULE — 宣告排程規則的開頭（接 EVERY ... 或 AT ...）。
EVERY — 每隔多久跑一次。
SECOND — 時間單位（同類還有 MINUTE、HOUR、DAY、WEEK、MONTH）。

DO — 接這個 event 到時間要執行的語句。

CREATE EVENT ev_cleanup_temp
ON SCHEDULE EVERY 30 SECOND
DO
  DELETE FROM sessions WHERE created_at < NOW() - INTERVAL 1 DAY;

一次性版本：ON SCHEDULE AT NOW() + INTERVAL 1 HOUR。

SHOW VARIABLES — 查看伺服器設定值；這裡最常用來確認排程器有沒有開，event_scheduler 是 OFF 的話 event 不會跑。
```
SHOW VARIABLES LIKE 'event_scheduler';
SET GLOBAL event_scheduler = ON;
```
（＋）SHOW EVENTS / ALTER EVENT … DISABLE — 列出已建立的 event／停用某個 event。

附：兩個容易出錯的地方（＋）

NULL 不參與比較：WHERE col = NULL 永遠不成立，要用 IS NULL；NOT IN (含 NULL 的子查詢) 也會整個變成空結果。
GROUP BY 的 SELECT 清單：只能放分組欄位與聚合結果，放其他欄位在嚴格模式（ONLY_FULL_GROUP_BY）下會直接報錯。

知識冒險者

探索

SQL Basic