您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 入門筆記 Day 3：常用內建函數、多表聯結、集合運算與進階查詢技巧

編輯：Oracle 資料庫基礎

一、字串函數

函數	說明	範例	結果
`UPPER(str)`	轉大寫	`UPPER('abc')`	ABC
`LOWER(str)`	轉小寫	`LOWER('ABC')`	abc
`INITCAP(str)`	首字大寫	`INITCAP('hello world')`	Hello World
`LENGTH(str)`	字串長度	`LENGTH('Oracle')`	6
`SUBSTR(str, m, n)`	子字串(從 m 起取 n 字元)	`SUBSTR('Oracle', 2, 3)`	rac
`INSTR(str1, str2)`	搜尋 str2 在 str1 位置	`INSTR('Oracle', 'ra')`	3
`REPLACE(str, old, new)`	取代	`REPLACE('Jack', 'J', 'B')`	Back
`TRIM/LTRIM/RTRIM`	去除空白/指定字元	`TRIM(' hi ')`	hi
`LPAD/RPAD(str, len, pad)`	左/右填充	`LPAD('7', 3, '0')`	007
`CONCAT(str1, str2)`	串接(同 `\|\|`)	`CONCAT('A','B')`	AB
`ASCII(str)`	首字元 ASCII 碼	`ASCII('A')`	65

二、數值函數

函數	說明	範例
`ABS(n)`	絕對值	`ABS(-5)=5`
`MOD(m, n)`	餘數	`MOD(10,3)=1`
`ROUND(n, m)`	四捨五入保留 m 位小數	`ROUND(1.567,2)=1.57`
`TRUNC(n, m)`	截斷保留 m 位小數	`TRUNC(1.567,2)=1.56`
`CEIL/FLOOR`	無條件進位/捨去	`CEIL(1.1)=2`
`POWER/SQRT`	次方/平方根	`POWER(2,3)=8`

三、日期函數

重要：日期相減 = 天數差（數值），如 SYSDATE - hiredate。

四、轉換函數

函數	說明	範例
`SYSDATE`	目前日期時間	`SELECT SYSDATE FROM dual;`
`ADD_MONTHS(d, n)`	加減 n 月	`ADD_MONTHS(SYSDATE, 3)`
`MONTHS_BETWEEN(d1, d2)`	月份差(數值)	`MONTHS_BETWEEN('2024-12-31','2024-01-01')≈11.97`
`NEXT_DAY(d, '星期')`	下一個指定星期	`NEXT_DAY(SYSDATE, 'FRIDAY')`
`LAST_DAY(d)`	該月最後一天	`LAST_DAY(SYSDATE)`
`ROUND/TRUNC(date, 'fmt')`	日期四捨五入/截斷	`ROUND(SYSDATE, 'MONTH')`

函數	說明	常用格式元素
`TO_CHAR(date/num, 'fmt')`	轉字串	YYYY, MM, DD, HH24, MI, SS, DAY, MON, DY, FM(抑制空白)
`TO_DATE(str, 'fmt')`	字串轉日期	同上
`TO_NUMBER(str, 'fmt')`	字串轉數值	9, 0, ., ,, $, C, L

範例：TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') → '2024-01-15 14:30:05'

五、多表聯結

類型	語法特徵	結果
笛卡爾積	無 JOIN 條件	M×N 列，通常為錯誤
內部聯結	`INNER JOIN ... ON` 或 `WHERE a.id=b.id`	僅符合條件的列
左外部聯結	`LEFT JOIN ... ON`	左表全保留，右表無配對則 NULL
右外部聯結	`RIGHT JOIN ... ON`	右表全保留
完整外部聯結	`FULL JOIN ... ON`	雙表全保留
自聯結	表別名 + 條件關聯自身	階層/上下級查詢

ANSI 語法範例：

SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno;

六、集合運算

運算子	說明	重複列
`UNION`	聯集	去除
`UNION ALL`	聯集	保留
`INTERSECT`	交集	去除
`MINUS`	差集(第一組減第二組)	去除

規則：欄位數、型別、順序必須一致；ORDER BY 僅能放在最後。

七、進階查詢：子查詢與巢狀查詢

查詢區塊：一個 SELECT 陳述式
子查詢：出現在 WHERE, FROM, SELECT 列表中的 SELECT
不相關子查詢：獨立執行，結果作為常數/集合供外層使用
相關子查詢：參考外層查詢欄位，每列執行一次

-- 不相關：查詢薪資高於平均的員工
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp);

-- 相關：查詢各部門薪資最高的員工
SELECT * FROM emp e1
WHERE sal = (SELECT MAX(sal) FROM emp e2 WHERE e2.deptno = e1.deptno);

-- 純量子查詢（SELECT 列表）
SELECT ename, (SELECT dname FROM dept WHERE deptno = e.deptno) AS dept_name
FROM emp e;

-- 內嵌視圖（FROM 子句）
SELECT deptno, avg_sal
FROM (SELECT deptno, AVG(sal) avg_sal FROM emp GROUP BY deptno)
WHERE avg_sal > 2000;