SELECT [ALL | DISTINCT] select_list
FROM table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression [ASC | DESC]]| 子句 | 用途 | 關鍵點 |
|---|---|---|
| SELECT | 指定回傳欄位 | * 所有欄；DISTINCT 去重；可使用別名 AS |
| FROM | 指定資料來源 | 資料表、檢視表、衍生表、表值函數、JOIN |
| WHERE | 列篩選 (行過濾) | 執行於 GROUP BY 之前；不可使用彙總函數 |
| GROUP BY | 分組彙總 | SELECT 清單中非彙總欄位必須出現在 GROUP BY |
| HAVING | 分組後篩選 | 可使用彙總函數；執行於 GROUP BY 之後 |
| ORDER BY | 結果排序 | 支援多欄位、ASC/DESC、運算式、資料行序數 |
= <> > < >= <= !> !<BETWEEN ... AND ... -- 等同 >= 且 <=
NOT BETWEEN ... AND ...IN (值1, 值2, ...)
NOT IN (值1, 值2, ...)% -- 任意長度字串 (含零字元)
_ -- 單一任意字元
[] -- 指定字元集合/範圍 [a-f]
[^] -- 排除字元集合範例：WHERE name LIKE 'A%' (A 開頭)；WHERE code LIKE '%[0-9]' (數字結尾)；WHERE name LIKE '[^A]%' (非 A 開頭)
IS NULL
IS NOT NULLAND OR NOT-- 多表聯查 + 分組彙總 + 條件篩選 + 排序
SELECT c.CustomerID, c.CompanyName, COUNT(o.OrderID) AS OrderCount
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
WHERE c.Country = 'Taiwan'
GROUP BY c.CustomerID, c.CompanyName
HAVING COUNT(o.OrderID) > 5
ORDER BY OrderCount DESC, c.CompanyName
