0. ★ SQL 的「執行順序」

寫的順序跟資料庫執行的順序不一樣，很多題目的答案都跟這個有關：

寫的順序：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT
執行順序：FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT

• WHERE 不能用 SELECT 取的別名（WHERE 執行時別名還不存在），ORDER BY 可以
• WHERE 不能放聚合函數（WHERE count(*) > 5 ❌），要放 HAVING
• WHERE 先過濾「列」，HAVING 再過濾「分組」

1. 已經會的（速查）

SELECT DISTINCT city FROM customers;
SELECT count(*), max(price), min(price), round(avg(price), 2) FROM products;

SELECT c.city, count(*) AS orders
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
HAVING count(*) > 1000
ORDER BY orders DESC
LIMIT 5;

分頁：LIMIT 20 OFFSET 40（第 3 頁，每頁 20 筆）

2. JOIN 全家

-- 找「沒有」對應資料：LEFT JOIN + IS NULL
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- SELF JOIN：每個分類和它的上層分類
SELECT c.name AS category, p.name AS parent
FROM categories c
LEFT JOIN categories p ON p.id = c.parent_id;

★ LEFT JOIN 的陷阱：條件放 ON 還是 WHERE？

-- 條件放 ON：所有會員都留著，只是「已取消訂單」才會對上
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'cancelled'
GROUP BY c.id;

-- 條件放 WHERE：沒取消過的會員 o.status 是 NULL，被過濾掉 → 變成 INNER JOIN 了
SELECT c.id, count(o.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'cancelled'
GROUP BY c.id;

3. ★ NULL 的處理

SELECT name, COALESCE(city, '未填寫') AS city FROM customers;
SELECT count(*) AS all_rows, count(birth_date) AS has_birthday FROM customers;
SELECT revenue / NULLIF(orders, 0) FROM ...;   -- orders 是 0 時得到 NULL 而不是報錯

★ **NOT IN 遇到 NULL 會失效**：子查詢只要有一個 NULL，NOT IN 就一筆都查不到。找「不存在」請用 NOT EXISTS。

-- 找「沒有子分類」的分類。categories.parent_id 有 NULL（最上層分類）→ 這句回傳 0 筆
SELECT * FROM categories WHERE id NOT IN (SELECT parent_id FROM categories);

-- 正確
SELECT * FROM categories c
WHERE NOT EXISTS (SELECT 1 FROM categories ch WHERE ch.parent_id = c.id);

4. 子查詢

-- 純量子查詢：回傳單一值
SELECT name, price FROM products
WHERE price > (SELECT avg(price) FROM products);

-- IN：比對一串值
SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE status = 'returned');

-- EXISTS：只問「有沒有」，找到一筆就停
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.status = 'cancelled');

-- 衍生表：FROM 裡面放子查詢，一定要取別名
SELECT avg(cnt) FROM (
  SELECT customer_id, count(*) AS cnt FROM orders GROUP BY customer_id
) t;

-- 相關子查詢：子查詢引用外層欄位，每一列都會重算一次
SELECT p.name, p.price
FROM products p
WHERE p.price > (SELECT avg(price) FROM products WHERE category_id = p.category_id);

5. CASE WHEN 與條件彙總

SELECT name, price,
       CASE WHEN price >= 10000 THEN '高價'
            WHEN price >= 1000  THEN '中價'
            ELSE '平價' END AS level
FROM products;

-- 一次算多種條件（列轉欄 / pivot）
SELECT shipping_city,
       count(*) FILTER (WHERE status = 'delivered')              AS delivered,   -- PostgreSQL 寫法
       sum(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)   AS cancelled    -- 通用寫法
FROM orders
GROUP BY shipping_city;

6. 集合運算

兩邊的欄位數量和型別要對得上。

SELECT customer_id FROM orders
EXCEPT
SELECT customer_id FROM orders WHERE status = 'returned';   -- 下過單、但從沒退過貨的會員

7. CTE（WITH）

把查詢拆成有名字的步驟，比巢狀子查詢好讀。

WITH spend AS (
  SELECT o.customer_id, sum(oi.quantity * oi.unit_price) AS total
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  GROUP BY o.customer_id
)
SELECT c.name, s.total
FROM spend s JOIN customers c ON c.id = s.customer_id
ORDER BY s.total DESC
LIMIT 10;

遞迴 CTE：處理樹狀結構（組織圖、分類樹）

WITH RECURSIVE tree AS (
  SELECT id, name, 1 AS depth FROM categories WHERE parent_id IS NULL   -- 起點
  UNION ALL
  SELECT c.id, c.name, t.depth + 1                                      -- 每次往下一層
  FROM categories c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

8. ★ 視窗函數（Window Function）

跟 GROUP BY 最大的差別：不會把資料列壓成一列，每一列都保留，同時能看到整組的統計。

函數() OVER (PARTITION BY 分組欄位 ORDER BY 排序欄位 [視窗範圍])

-- ★ 分組取前 N 名：每個分類價格前 3 高的商品
SELECT * FROM (
  SELECT name, category_id, price,
         dense_rank() OVER (PARTITION BY category_id ORDER BY price DESC) AS rk
  FROM products
) t
WHERE rk <= 3;

-- 累計加總
SELECT order_date, amount,
       sum(amount) OVER (ORDER BY order_date) AS running_total
FROM ...;

-- 7 日移動平均
SELECT day, revenue,
       avg(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM ...;

-- 跟上一筆比較
SELECT month, revenue, revenue - lag(revenue) OVER (ORDER BY month) AS diff
FROM ...;

-- 佔比：每件商品佔該分類營收的百分比
SELECT name, category_id, price,
       round(100.0 * price / sum(price) OVER (PARTITION BY category_id), 2) AS pct
FROM products;

★ 視窗函數不能直接寫在 WHERE（執行順序在 WHERE 之後），要包一層子查詢或 CTE。

9. 常用函數（PostgreSQL）

字串

'a' || 'b'                     -- 串接（NULL || 'b' 會得到 NULL）
concat('a', NULL, 'b')         -- 串接，忽略 NULL
length(s)   lower(s)   upper(s)   trim(s)
substring(s, 1, 3)   left(s, 3)   right(s, 3)
replace(s, '舊', '新')
split_part('[email protected]', '@', 2)  -- 'b.com'
s LIKE '王%'                    -- % 任意長度，_ 一個字
s ILIKE '%apple%'              -- 不分大小寫（PostgreSQL 專屬）
string_agg(name, ', ')         -- 聚合成一個字串

日期時間

now()   current_date
date_trunc('month', order_date)            -- 截到月初，做月報表必備
extract(year FROM order_date)              -- 取出年 / month / dow（星期幾）/ hour
order_date::date                           -- timestamp 轉 date
current_date - interval '30 days'
age(current_date, birth_date)              -- 算年齡
to_char(order_date, 'YYYY-MM')             -- 格式化
order_date >= '2026-01-01' AND order_date < '2026-02-01'   -- ★ 範圍查詢，比 extract 更能用到索引

數字

round(x, 2)   ceil(x)   floor(x)   abs(x)   x % 3

★ 整數除法陷阱：5 / 2 = 2。要小數請寫 5.0 / 2 或 5::numeric / 2。

型別轉換：'123'::int、CAST('123' AS int)、price::text

10. 新增、修改、刪除（DML）

INSERT INTO customers (name, email, signup_date)
VALUES ('測試帳號', '[email protected]', current_date)
RETURNING id;                                   -- RETURNING 拿回新 id

-- ★ UPSERT：存在就更新，不存在就新增（依主鍵或 UNIQUE 欄位判斷）
INSERT INTO categories (id, name, parent_id)
VALUES (28, '寵物用品', NULL)
ON CONFLICT (id)
DO UPDATE SET name = EXCLUDED.name;             -- EXCLUDED = 這次想插入的那一列

UPDATE products SET price = price * 0.9 WHERE category_id = 19;

-- 用其他表的資料來更新
UPDATE products p SET is_active = false
FROM categories c
WHERE c.id = p.category_id AND c.name = '生鮮';

DELETE FROM orders WHERE status = 'cancelled' RETURNING id;   -- order_items 有 ON DELETE CASCADE，明細會一起刪

★ DELETE vs TRUNCATE vs DROP

11. 建表與約束（DDL）

CREATE TABLE coupons (
  id         serial PRIMARY KEY,                        -- 自動遞增主鍵
  code       text NOT NULL UNIQUE,
  discount   numeric(3,2) CHECK (discount BETWEEN 0 AND 1),
  customer_id int REFERENCES customers(id) ON DELETE CASCADE,  -- 外鍵
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE coupons ADD COLUMN used boolean DEFAULT false;
ALTER TABLE coupons DROP COLUMN used;
DROP TABLE coupons;

View

CREATE VIEW v_order_total AS
SELECT order_id, sum(quantity * unit_price * (1 - discount)) AS total
FROM order_items GROUP BY order_id;            -- 只存查詢，每次查都重算

CREATE MATERIALIZED VIEW mv_order_total AS ...;  -- 把結果存起來，查詢快
REFRESH MATERIALIZED VIEW mv_order_total;        -- 資料要手動更新

12. ★ 索引與 EXPLAIN

CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_cust_date ON orders (customer_id, order_date);  -- 複合索引
CREATE UNIQUE INDEX ... ;
DROP INDEX idx_orders_customer;

EXPLAIN SELECT ...;          -- 預估的執行計畫
EXPLAIN ANALYZE SELECT ...;  -- 真的執行，顯示實際耗時

索引用不到的常見情況

• 對欄位做運算或函數：WHERE extract(year FROM order_date) = 2026 ❌
• 開頭是萬用字元：LIKE '%abc' ❌（LIKE 'abc%' 可以）
• 複合索引 (a, b) 只查 b（最左前綴原則）
• 回傳的資料佔全表很大比例，資料庫判斷直接全掃比較快

索引的代價：佔空間，而且 INSERT / UPDATE / DELETE 都要同步更新索引，會變慢。

13. ★ 交易（Transaction）

BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO orders (...) VALUES (...);
COMMIT;      -- 確認；出錯就 ROLLBACK 全部取消

ACID

隔離等級與會發生的問題（PostgreSQL 預設 Read Committed）

鎖住要修改的列：SELECT ... FOR UPDATE（例如扣庫存前先鎖，避免超賣）

14. ★ 面試經典題速答

15. ★ PostgreSQL 進階

JSONB

products.specs 是 JSONB，例如 {"color": "黑", "storage_gb": 256, "warranty": {"years": 2}}

★ 陷阱：

• ->> 取出的是 text，比大小前要轉型：(specs->>'storage_gb')::int > 64。直接寫 > '64' 會變成逐字比較，'128' > '64' 是 false
• NULL || '{…}' 的結果是 NULL，要寫 COALESCE(specs, '{}') || '{…}'
• JDBC 的 ? 是參數佔位符，寫 specs ? 'key' 要改成 ?? 或 jsonb_exists(specs, 'key')
• json 與 jsonb：jsonb 存成二進位、會去掉重複的鍵和空白、支援索引，幾乎都用 jsonb

Array

products.tags 是 text[]，例如 {熱銷,特價}

★ array_length(tags, 1) 遇到空陣列會回傳 NULL，不是 0。

Java：WHERE id = ANY(?) 搭配 ps.setArray(1, conn.createArrayOf("int", ids))，取代自己組 IN (…) 字串。

generate_series：產生連續的值

SELECT generate_series(1, 5);                                        -- 1~5
SELECT generate_series('2026-09-01'::date, '2026-09-30', '1 day');   -- 每天

-- ★ 報表補零：先產生完整時間軸，再 LEFT JOIN（條件放 ON）
SELECT d::date, count(o.id)
FROM generate_series('2026-09-01'::date, '2026-09-30', '1 day') d
LEFT JOIN orders o ON o.order_date >= d AND o.order_date < d + interval '1 day'
GROUP BY d ORDER BY d;

也常用來產生大量測試資料（這個練習庫就是這樣做的）。

RETURNING 與 UPSERT

INSERT INTO customers (name, email, signup_date)
VALUES ('王小明', '[email protected]', current_date)
RETURNING id;                                         -- 直接拿回新的 id

UPDATE products SET price = price * 1.1 WHERE id = 1 RETURNING id, price;   -- 更新後的值
DELETE FROM orders WHERE status = 'pending' RETURNING id;

-- ★ UPSERT：原子操作，沒有「先查再寫」的競態問題
INSERT INTO categories (id, name) VALUES (2, '智慧型手機')
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name;  -- EXCLUDED = 想插入的那一列

INSERT … ON CONFLICT (email) DO NOTHING;              -- 重複就跳過（冪等）

-- 搬資料：刪除與歸檔在同一句、同一個交易
WITH moved AS (DELETE FROM orders WHERE … RETURNING *)
INSERT INTO orders_archive SELECT * FROM moved;

★ 序列不受交易控制：ROLLBACK 之後用掉的 id 不會還回去，所以 id 會跳號。

進階索引

BRIN 的前提：資料在磁碟上的順序要和欄位值一致（相關性高）。隨機寫入的欄位建 BRIN 完全沒用。

16. psql 常用指令