SQL 語法速查表

SELECT id, name, created_at FROM users

按列查詢，生產環境別用 SELECT *，否則加字段會牽動所有調用方

SELECT * FROM users WHERE id = 1

查全部列，只適合臨時排查

SELECT DISTINCT city FROM users

去重，注意它會對整行去重而不是單列

SELECT user_name AS name FROM users

給列或表達式起別名

SELECT id FROM orders ORDER BY created_at DESC

排序，DESC 降序、ASC 升序（默認）

SELECT id, score FROM exam ORDER BY score DESC, id ASC

多列排序，先按 a 再按 b；分頁時務必加唯一列兜底

SELECT id FROM orders ORDER BY id LIMIT 10 OFFSET 20

分頁，取第 21 到 30 行

SELECT TOP 10 id FROM orders ORDER BY id DESC

SQL Server 的取前 N 行寫法

SELECT id FROM orders ORDER BY id FETCH FIRST 10 ROWS ONLY

標準 SQL 的取前 N 行，Oracle 與新版 PG 都支持

SELECT email FROM users UNION SELECT email FROM leads

合併結果並去重，列數與類型必須一致

SELECT id FROM a UNION ALL SELECT id FROM b

合併但不去重，比 UNION 快很多

WITH paid AS (SELECT * FROM orders WHERE status = 1) SELECT COUNT(*) FROM paid

公用表表達式，把複雜查詢拆成可讀的步驟

WITH RECURSIVE tree AS (SELECT id, parent_id FROM nodes WHERE parent_id IS NULL UNION ALL SELECT n.id, n.parent_id FROM nodes n JOIN tree t ON n.parent_id = t.id) SELECT * FROM tree

遞歸 CTE，查樹形或圖結構

SELECT CASE WHEN score >= 60 THEN 1 ELSE 0 END AS passed FROM exam

條件表達式，在結果裡做分支

SELECT CAST(price AS DECIMAL(10,2)) FROM orders

顯式類型轉換，比隱式轉換安全

SELECT COALESCE(nickname, user_name) FROM users

取第一個非 NULL 值，處理空值最常用

SELECT CONCAT(first_name, surname) FROM users

字符串拼接，MySQL 與 PG 也支持 || 運算符

SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn FROM users

加行號（詳見窗口函數組）

WHERE status = 1

等值篩選

WHERE status <> 0

不等篩選，也可寫 !=

WHERE status IN (1, 2, 5)

多值匹配，比多個 OR 好讀

WHERE id NOT IN (SELECT user_id FROM bans)

排除子查詢結果，子查詢返回 NULL 時整個條件會變空集

WHERE created_at BETWEEN 2 AND 8

閉區間包含兩端，日期用 BETWEEN 會漏掉當天末尾時刻

WHERE email LIKE 'admin%'

前綴匹配，能用到索引

WHERE remark LIKE '%退货%'

包含匹配，前導通配符用不上索引

WHERE deleted_at IS NULL

判空必須用 IS NULL，= NULL 永遠不成立

WHERE email IS NOT NULL

非空判斷，也可用於過濾未賦值字段

WHERE status = 1 AND (type = 2 OR type = 3)

括號決定優先級：AND 先於 OR，不寫括號極易出邏輯錯

WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)

存在性判斷，通常比 IN 更快且不受 NULL 影響

WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'

時間範圍推薦用左閉右開，不要對列套函數

SELECT u.name, o.amount FROM users u INNER JOIN orders o ON u.id = o.user_id

內連接，只保留兩邊都匹配的行

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id

左連接，保留左表全部行，右表缺失補 NULL

SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id

全外連接，兩邊不匹配的行都保留

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c

笛卡爾積，生成組合矩陣時故意這麼寫

SELECT u.* FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE o.id IS NULL

找左表有而右表沒有的行（差集）

SELECT * FROM users JOIN profiles USING (user_id)

同名列連接，結果裡該列只出現一次

SELECT e.name, m.name AS manager FROM emp e JOIN emp m ON e.mgr_id = m.id

自連接，查層級關係

SELECT u.name, o.amount FROM users u LEFT JOIN orders o ON u.id = o.user_id AND o.status = 1

左連接的多餘條件必須寫在 ON 裡；寫進 WHERE 會退化成內連接

SELECT city, COUNT(*) FROM users GROUP BY city

按列分組統計

SELECT COUNT(*) FROM orders

統計行數，包含 NULL 行

SELECT COUNT(email) FROM users

統計該列非 NULL 的行數，和 COUNT(*) 結果可能不同

SELECT COUNT(DISTINCT user_id) FROM orders

統計去重後的數量

SELECT SUM(amount), AVG(amount) FROM orders

求和、平均、最小、最大；AVG 忽略 NULL 會讓分母變小

SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1

分組後篩選，找重複數據最常用

SELECT GROUP_CONCAT(name SEPARATOR ', ') FROM users GROUP BY city

MySQL 把分組內多個值拼成一個字符串，PG 用 STRING_AGG 並指定分隔符

SELECT COUNT(*) FILTER (WHERE status = 1) AS paid FROM orders

條件聚合，一次掃描算多個指標；MySQL 用 SUM(cond) 替代

SELECT city, status, COUNT(*) FROM orders GROUP BY ROLLUP (city, status)

自動補小計與總計行

SELECT city, status FROM orders GROUP BY city, status

多列分組；SELECT 裡的非聚合列必須都出現在 GROUP BY 中

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn

組內排名，不併列，是「每組取最新一條」的核心

RANK() OVER (ORDER BY score DESC) AS r

並列排名，跳過後續名次（1,2,2,4）

DENSE_RANK() OVER (ORDER BY score DESC) AS r

並列排名，不跳號（1,2,2,3）

LAG(amount, 1, 0) OVER (ORDER BY created_at) AS prev_amount

取上一行的值，算環比與差值

LEAD(amount) OVER (ORDER BY created_at) AS next_amount

取下一行的值

SUM(amount) OVER (ORDER BY created_at) AS running_total

累計求和，默認窗口是從開頭到當前行

AVG(amount) OVER (PARTITION BY user_id) AS user_avg

組內平均，不折疊行數，可與明細並列顯示

NTILE(4) OVER (ORDER BY amount DESC) AS quartile

把結果均分成 N 桶，分位數與分層抽樣用

FIRST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS first_amount

組內第一行的值

LAST_VALUE(amount) OVER (PARTITION BY user_id ORDER BY created_at ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_amount

取組內最後一行；不寫完整窗口幀會得到當前行而非末行

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) rn FROM orders) x WHERE rn = 1

取每組最新一條的標準寫法，必須套一層子查詢

SELECT * FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY g ORDER BY t) rn FROM t) x WHERE rn <= 3

窗口函數在 WHERE 時還未求值，必須先物化成子查詢或 CTE

INSERT INTO users (name, email) VALUES ('Ada', 'ada@example.com')

插入一行或多行，多行寫成多個括號更快

INSERT INTO archive SELECT * FROM orders WHERE created_at < 2025

從查詢結果批量插入

INSERT INTO s (id, v) VALUES (1, 10) ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v

PostgreSQL 的 upsert：存在則更新，不存在則插入

INSERT INTO s (id, v) VALUES (1, 10) ON DUPLICATE KEY UPDATE v = VALUES(v)

MySQL 的 upsert 寫法

UPDATE users SET status = 1 WHERE id = 10

更新指定行，WHERE 漏寫會更新全表

UPDATE orders o SET price = p.price FROM products p WHERE o.product_id = p.id

PostgreSQL 的連接更新；MySQL 寫 JOIN，SQL Server 寫 FROM

DELETE FROM sessions WHERE expires_at < NOW()

按條件刪除

TRUNCATE TABLE staging_rows

清空整表，比 DELETE 快且重置自增值，但不可回滾（多數庫）

MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE SET t.v = s.v WHEN NOT MATCHED THEN INSERT VALUES (s.id, s.v)

標準 SQL 的合併寫入，Oracle 與 SQL Server 支持

REPLACE INTO s (id, v) VALUES (1, 10)

MySQL 特有：先刪後插，自增主鍵會被改變，觸發器行為也和 upsert 不同

CREATE TABLE users (id BIGINT PRIMARY KEY, name VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT NOW())

建表，主鍵與非空是最基本的約束

CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT, CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id))

外鍵約束，保證引用完整性但會影響寫入性能

CREATE UNIQUE INDEX uk_email ON users (email)

唯一約束，也能阻止併發寫入重複值

ALTER TABLE users ADD COLUMN phone VARCHAR(20)

加字段，大表加非空默認值字段可能鎖表

ALTER TABLE users DROP COLUMN phone

刪字段

ALTER TABLE users ALTER COLUMN phone TYPE VARCHAR(30)

改字段類型（PostgreSQL 寫法；MySQL 用 MODIFY COLUMN）

ALTER TABLE users RENAME TO app_users

改表名

DROP TABLE IF EXISTS tmp_import

刪表，加 IF EXISTS 避免腳本重跑報錯

CREATE VIEW v_paid AS SELECT * FROM orders WHERE status = 1

視圖，只存查詢定義不存數據

CREATE TABLE users_backup AS SELECT * FROM users

按查詢結果建表，約束與索引不會帶過來

CREATE INDEX idx_created ON orders (created_at)

普通索引，加速查詢但拖慢寫入

CREATE INDEX idx_user_time ON orders (user_id, created_at)

複合索引遵守最左前綴：只查 b 用不上它

DROP INDEX idx_created

刪索引（SQL Server 需寫 DROP INDEX t.idx）

EXPLAIN SELECT * FROM orders WHERE user_id = 1

查看執行計劃，重點看 type 與 key（MySQL）或 Seq Scan（PG）

EXPLAIN ANALYZE SELECT COUNT(*) FROM orders

真正執行並給出實際耗時與行數，比 EXPLAIN 準

CREATE INDEX idx_user ON orders (user_id) INCLUDE (amount)

覆蓋索引，把要查的列帶上，免去回表

BEGIN

開啓事務（也可寫 START TRANSACTION）

COMMIT

提交事務

ROLLBACK

回滾事務

SAVEPOINT sp1

設置保存點，可部分回滾

ROLLBACK TO SAVEPOINT sp1

回滾到保存點，之前的操作仍保留

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

設置隔離級別：讀未提交/讀已提交/可重複讀/串行化

SELECT * FROM accounts WHERE id = 1 FOR UPDATE

加寫鎖，防併發超賣；必須在事務內使用

SELECT * FROM jobs WHERE status = 0 ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED

跳過已加鎖的行，做任務隊列搶佔的標準寫法

SELECT * FROM t WHERE deleted_at = NULL

NULL 表示未知，與任何值比較結果都是未知，必須用 IS NULL

SELECT * FROM a WHERE id NOT IN (SELECT a_id FROM b)

子查詢返回哪怕一個 NULL，NOT IN 就會返回空集，改用 NOT EXISTS

SELECT * FROM users WHERE phone = 13800000000

隱式類型轉換會讓索引失效，參數類型必須與列類型一致

WHERE created_at >= '2026-01-01' AND created_at < '2026-01-02'

對列套函數會讓索引失效，改寫成範圍條件

SELECT * FROM t WHERE a = 1 UNION SELECT * FROM t WHERE b = 2

OR 跨列時單列索引都用不上，可拆成兩次查詢再 UNION

SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20

深分頁要先掃描再丟棄前面所有行，改用遊標式翻頁

DELETE FROM t WHERE created_at < 2024

漏寫 WHERE 會清空整表；執行前先用同條件的 SELECT 驗證

SELECT id, name FROM t ORDER BY score DESC, id ASC LIMIT 20

排序值有並列時順序不確定，分頁會漏行或重複，務必加唯一列兜底

-- 改成每批 1000 行循环提交 DELETE FROM logs WHERE created_at < 2024 LIMIT 1000;

大事務會長時間持鎖、撐爆回滾段，改成小批多次提交

SELECT * FROM t WHERE name = 'Ada' -- 大小写敏感取决于 collation

MySQL 默認排序規則不區分大小寫，換庫後行為可能相反