Oracle SQL 查詢基礎涵蓋 SELECT 語法、運算子、字面值、NULL 語意、排序與條件過濾。本文由淺入深整理核心語法與常見誤區。
SELECT [* | {col1 [alias], expr [alias], ...}] FROM table;
* 代表所有欄位,生產環境建議明確列出欄位。col AS alias、col alias、col "Alias With Space" (雙引號保留大小寫/特殊字元)。SELECT empno, ename, sal, sal*12 AS annsal, sal*12+1000 AS annsal_bonus FROM emp; -- 括號改變優先序 SELECT sal*(12+1000) FROM emp;
SELECT ename || ' is a ' || job AS info FROM emp; -- 單引號跳脫:兩個單引號代表字串中的一個單引號 SELECT ename || '''s salary is ' || sal FROM emp;
col = NULL 永遠為 FALSE,必須用 col IS NULL。SELECT DISTINCT deptno FROM emp; -- 單欄去重 SELECT DISTINCT deptno, job FROM emp; -- 組合去重
SELECT empno, ename, sal FROM emp ORDER BY sal; -- ASC 預設 SELECT empno, ename, sal FROM emp ORDER BY sal DESC; -- 降序 SELECT empno, ename, sal*12 annsal FROM emp ORDER BY annsal; -- 別名排序 SELECT deptno, empno, ename, sal FROM emp ORDER BY deptno, sal DESC; -- 多欄排序
| 運算子 | 說明 | 範例 |
|---|---|---|
| =, !=, ^=, <> | 等於/不等於 | sal = 3000 |
| >, >=, <, <= | 大小比較 | sal > 2000 |
| BETWEEN ... AND ... | 區間 (含邊界,小值在前) | sal BETWEEN 1500 AND 3000 |
| IN (list) | 集合成員 | deptno IN (10,20,30) |
| LIKE | 模糊比對 | ename LIKE 'S%' / ename LIKE '_A%' |
| IS [NOT] NULL | NULL 判斷 | comm IS NULL |
%:零或多個任意字元。_:單一任意字元。ESCAPE '\':自訂跳脫字元,查詢實際 % 或 _。AND, OR, NOT -- 優先序:NOT > AND > OR,建議用括號明確分組 SELECT * FROM emp WHERE (deptno=10 OR deptno=20) AND sal > 1500;
'SMITH',**大小寫敏感**。'17-NOV-2023',**格式敏感**,預設 DD-MON-RR (受 NLS_DATE_FORMAT 影響)。TO_DATE('2023-11-17','YYYY-MM-DD') 明確指定格式。SELECT SYSDATE FROM dual; SELECT 3+2 FROM dual; -- 單行運算結果
DUMMY VARCHAR2(1) 值為 'X')。