SELECT *:所有欄位。SELECT 欄位1, 欄位2:指定欄位與顯示順序。SELECT 昵稱=nickname, 電子郵件=email 或 SELECT nickname AS 昵稱。含特殊字元用引號 SELECT [中文別名]=nickname。DISTINCT 去除重複行;TOP n [PERCENT] 限制回傳行數。SELECT username, citytable.cityid FROM usertable, citytable WHERE usertable.cityid=citytable.cityid。FROM usertable a, citytable b。FROM (SELECT ... FROM ...) AS t。| 類別 | 運算符 | 說明 |
|---|---|---|
| 比較 | >, >=, =, <, <=, <>, !>, !< | 大小比較 |
| 範圍 | BETWEEN ... AND ..., NOT BETWEEN ... AND ... | 包含邊界 |
| 列表 | IN (項1,項2...), NOT IN (...) | 離散值匹配 |
| 模式 | LIKE, NOT LIKE | 通配符:% (任意長度)、_ (單字元)、[] (字元集合)、[^] (排除集合) |
| 空值 | IS NULL, IS NOT NULL | 判斷 NULL |
| 邏輯 | NOT, AND, OR | 優先級 NOT > AND > OR |
範例:age BETWEEN 10 AND 30 等同 age>=10 AND age<=30;country IN ('Germany','China');name LIKE '[A]%' (以 A 開頭)、name LIKE '[^A]%' (非 A 開頭)。
SELECT * FROM usertable ORDER BY age DESC, userid ASC;
可依運算式排序;ntext/text/image 不可排序。
SELECT 欄位 FROM 表1 UNION [ALL] SELECT 欄位 FROM 表2 [UNION [ALL] SELECT ...]
ALL 保留重複行,預設去重。查詢1 UNION (查詢2 UNION 查詢3)。SQL-92 標準語法:FROM 表1 JOIN類型 表2 ON 連接條件。推薦寫於 FROM 子句,區隔連接邏輯與過濾條件。
LEFT [OUTER] JOIN:保留左表所有行。RIGHT [OUTER] JOIN:保留右表所有行。FULL [OUTER] JOIN:保留兩表所有行。表與自身連接,需用別名區分:FROM emp e1 INNER JOIN emp e2 ON e1.mgr = e2.empno。