Oracle資料庫入門：查詢基礎與條件過濾實戰

基本表操作快速入門

-- 建表
CREATE TABLE student (name VARCHAR2(20), age NUMBER(3));

-- 插入
INSERT INTO student VALUES ('stone', 24);

-- 檢視結構
DESC student;

-- 查詢
SELECT * FROM student;

-- 刪除資料
DELETE FROM student WHERE 條件;

-- 刪除表
DROP TABLE student;

• VARCHAR2(20)：變長字串，上限 20 位元組。
• NUMBER(3)：3 位整數，無小數；單用 NUMBER 預設 10 位整數。
• 字串常量用單引號；關鍵字不分大小寫（慣例 DBA 用大寫、開發用小寫）。
• DML 執行後未永久生效，需 COMMIT 提交。
• SQL*Plus 註解用 --。

SELECT 基礎語法

SELECT * | {欄位名, ...} FROM 表名;

• 欄位間用逗號分隔，逗號後加空格增強可讀性。
• SELECT * FROM emp; 或 SELECT empno, ename, sal FROM emp;

預設顯示對齊

算術運算式

針對 NUMBER 型別可用 + - * /。

SELECT empno, ename, sal, sal*12 AS 年薪 FROM emp;
SELECT empno, ename, sal, sal*12+1000 FROM emp;
SELECT empno, ename, sal, sal*(12+1000) FROM emp;  -- 括號改變優先序

串接運算子 (||)

將欄位、字串、運算式合併為新字串欄位。

SELECT ename || ' is a ' || job FROM emp;
SELECT empno, ename || '''s annual salary is ' || sal*12 FROM emp;

• 單引號為字串界定符；字串內出現單引號用兩個單引號表示（無需跳脫字元）。

欄位別名

SELECT 欄位名 | 運算式 [[AS] 別名], ... FROM 表名;

SELECT empno, ename "Ename", sal*12 "年 薪" FROM emp;
SELECT empno AS 員工編號, ename 員工姓名, sal*12 "年薪" FROM emp;

• AS 可省略。
• 別名含特殊字元（空格、大小寫敏感）需雙引號；否則預設顯示為大寫。

空值 (NULL) 處理

• NULL ≠ 0 ≠ 空字串；代表「未知、不適用、未指定」。
• INSERT INTO student(age) VALUES(24); → name 自動為 NULL。

運算中遇到 NULL

SELECT ename, sal, comm,
       ename || ' - ' || comm,   -- NULL 視為空字串
       sal + comm                -- 結果為 NULL
FROM emp;

解決：用 NVL(comm, 0) 給予預設值再運算。

去除重複列 (DISTINCT)

SELECT deptno FROM emp;                    -- 含重複
SELECT DISTINCT deptno FROM emp;           -- 去重
SELECT DISTINCT deptno, job FROM emp;      -- 組合不重複即顯示

DISTINCT 作用於後續所有欄位組合。

查詢結果排序 (ORDER BY)

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;  -- 多欄位排序

• 多欄位排序：先按第一欄，重複值再按第二欄。
• 各欄位可獨立指定 ASC/DESC：deptno, sal DESC、deptno DESC, sal 等。

條件查詢 (WHERE)

• 字串、日期值必須用單引號括起。
• 字串比較區分大小寫；日期格式敏感，預設 DD-MON-RR。

SELECT * FROM emp WHERE ename = 'SMITH';          -- 大小寫敏感
SELECT * FROM emp WHERE hiredate = '02-4月-81';   -- 自動轉型
SELECT SYSDATE FROM dual;                         -- 取得系統時間

DUAL 表

• 單列單欄虛擬表，不儲存資料，作為無表查詢的媒介。
• SELECT 3+2 FROM dual; → 1 列結果 5。
• SELECT 3+2 FROM emp; → 每列各算一次，回傳 N 列 5。

關係運算子

SELECT * FROM emp WHERE sal BETWEEN 1600 AND 2900;
SELECT * FROM emp WHERE ename IN ('SMITH','KING','King');  -- 大小寫敏感
SELECT * FROM emp WHERE comm IS NULL;
SELECT * FROM emp WHERE comm IS NOT NULL;  -- 0 ≠ NULL

LIKE 萬用字元

• %：零或多個任意字元。
• _：單一任意字元。
• ESCAPE：指定跳脫字元。

SELECT * FROM emp WHERE ename LIKE 'S%';       -- 以 S 開頭
SELECT * FROM emp WHERE ename LIKE '_A%';      -- 第 2 字母為 A
SELECT * FROM emp WHERE ename LIKE '%_%' ESCAPE '\';  -- 含底線的姓名

邏輯運算子

優先序：NOT → AND → OR

SELECT * FROM emp WHERE deptno = 20 AND sal > 2000;
SELECT * FROM emp WHERE deptno = 20 OR sal > 2000;
SELECT * FROM emp WHERE deptno NOT IN (20, 30, 40);

最佳實務建議

簡單資料處理（查詢、組合、過濾、型別轉換、分組統計、去重、去空格等）交由資料庫層完成；複雜商業邏輯置於應用層，實現鬆耦合，利於維護與擴展。