↓快轉到主要內容

Oracle SQL 常用指令、操作

·
類別 
後端開發 常用指令
標籤 
Oracle Sql
Eason Chiu
作者
Eason Chiu
一個不做筆記就容易忘記的工程師
目錄

一、建立資料表
#

1. 建立員工表 EMPLOYEES
#

sql
CREATE TABLE employees (
    emp_id      NUMBER PRIMARY KEY,
    emp_name    VARCHAR2(50),
    dept_id     NUMBER,
    salary      NUMBER,
    hire_date   DATE
);

INSERT INTO employees VALUES (1, 'Alice',   10, 6000, TO_DATE('2015-01-10', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (2, 'Bob',     10, 8000, TO_DATE('2016-03-12', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (3, 'Charlie', 20, 5000, TO_DATE('2017-06-01', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (4, 'David',   30, 9000, TO_DATE('2014-08-20', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (5, 'Eva',     20, 7000, TO_DATE('2018-11-15', 'YYYY-MM-DD'));
INSERT INTO employees VALUES (6, 'Frank',   30, 8000, TO_DATE('2015-12-01', 'YYYY-MM-DD'));

查詢結果：

sql
SELECT * FROM employees;
EMP_ID EMP_NAME DEPT_ID SALARY HIRE_DATE
1 Alice 10 6000 2015-01-10
2 Bob 10 8000 2016-03-12
3 Charlie 20 5000 2017-06-01
4 David 30 9000 2014-08-20
5 Eva 20 7000 2018-11-15
6 Frank 30 8000 2015-12-01

2. 建立部門表 DEPARTMENTS
#

sql
CREATE TABLE departments (
    dept_id   NUMBER PRIMARY KEY,
    dept_name VARCHAR2(50)
);

INSERT INTO departments VALUES (10, 'Sales');
INSERT INTO departments VALUES (20, 'IT');
INSERT INTO departments VALUES (30, 'HR');

查詢結果：

sql
SELECT * FROM departments;
DEPT_ID DEPT_NAME
10 Sales
20 IT
30 HR

3. 建立專案表 PROJECTS
#

sql
CREATE TABLE projects (
    project_id NUMBER PRIMARY KEY,
    project_name VARCHAR2(50),
    emp_id NUMBER,
    start_date DATE,
    end_date DATE
);

INSERT INTO projects VALUES (101, 'ERP Upgrade', 1, TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2020-12-31','YYYY-MM-DD'));
INSERT INTO projects VALUES (102, 'Mobile App', 3, TO_DATE('2021-03-01','YYYY-MM-DD'), TO_DATE('2021-09-30','YYYY-MM-DD'));
INSERT INTO projects VALUES (103, 'Data Migration', 5, TO_DATE('2019-05-01','YYYY-MM-DD'), TO_DATE('2020-03-31','YYYY-MM-DD'));
INSERT INTO projects VALUES (104, 'Cloud Migration', 2, TO_DATE('2021-06-01','YYYY-MM-DD'), TO_DATE('2022-02-28','YYYY-MM-DD'));

查詢結果：

sql
SELECT * FROM projects;
PROJECT_ID PROJECT_NAME EMP_ID START_DATE END_DATE
101 ERP Upgrade 1 2020-01-01 2020-12-31
102 Mobile App 3 2021-03-01 2021-09-30
103 Data Migration 5 2019-05-01 2020-03-31
104 Cloud Migration 2 2021-06-01 2022-02-28

二、SQL 常用查詢
#

1. SELECT
#

執行前資料：

sql
SELECT emp_id, emp_name, salary FROM employees ORDER BY salary DESC;
EMP_ID EMP_NAME SALARY
4 David 9000
2 Bob 8000
6 Frank 8000
5 Eva 7000
1 Alice 6000
3 Charlie 5000

執行後結果：

sql
-- 查詢薪資超過 7000 的員工，按薪資降序排列
SELECT emp_id, emp_name, salary
FROM employees
WHERE salary > 7000
ORDER BY salary DESC;

結果：

EMP_ID EMP_NAME SALARY
4 David 9000
2 Bob 8000
6 Frank 8000

2. DISTINCT
#

執行前資料：

sql
SELECT emp_name, dept_id FROM employees ORDER BY dept_id;
EMP_NAME DEPT_ID
Alice 10
Bob 10
Charlie 20
Eva 20
David 30
Frank 30

執行後結果：

sql
-- 查詢員工所屬的所有部門，去除重複值
SELECT DISTINCT dept_id
FROM employees;

結果：

DEPT_ID
10
20
30

3. 字串處理
#

執行前資料：

sql
SELECT emp_name FROM employees ORDER BY emp_name;
EMP_NAME
Alice
Bob
Charlie
David
Eva
Frank

執行後結果：

sql
-- 將員工姓名轉為大寫，並計算姓名長度
SELECT emp_name, UPPER(emp_name) AS upper_name, LENGTH(emp_name) AS name_len
FROM employees;

結果：

EMP_NAME UPPER_NAME NAME_LEN
Alice ALICE 5
Bob BOB 3
Charlie CHARLIE 7
David DAVID 5
Eva EVA 3
Frank FRANK 5

4. 日期處理
#

執行前資料：

sql
SELECT emp_name, hire_date FROM employees ORDER BY hire_date;
EMP_NAME HIRE_DATE
David 2014-08-20
Alice 2015-01-10
Frank 2015-12-01
Bob 2016-03-12
Charlie 2017-06-01
Eva 2018-11-15

執行後結果：

sql
-- 計算員工雇用日期後 6 個月的日期，以及工作月數
SELECT emp_name,
       hire_date,
       ADD_MONTHS(hire_date, 6) AS after_6_months,
       MONTHS_BETWEEN(SYSDATE, hire_date) AS months_worked
FROM employees;

結果：

EMP_NAME HIRE_DATE AFTER_6_MONTHS MONTHS_WORKED
Alice 2015-01-10 2015-07-10 108.5
Bob 2016-03-12 2016-09-12 96.8
Charlie 2017-06-01 2017-12-01 82.2
David 2014-08-20 2015-02-20 127.3
Eva 2018-11-15 2019-05-15 64.7
Frank 2015-12-01 2016-06-01 108.1

5. GROUP BY
#

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 按部門分組，計算各部門的員工數、總薪資和平均薪資
SELECT dept_id,
       COUNT(*) AS emp_count,
       SUM(salary) AS total_salary,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

結果：

DEPT_ID EMP_COUNT TOTAL_SALARY AVG_SALARY
10 2 14000 7000
20 2 12000 6000
30 2 17000 8500

三、JOIN 操作
#

1. INNER JOIN
#

執行前資料：

sql
-- 員工表
SELECT emp_name, dept_id FROM employees ORDER BY dept_id, emp_name;
EMP_NAME DEPT_ID
Alice 10
Bob 10
Charlie 20
Eva 20
David 30
Frank 30
sql
-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;
DEPT_ID DEPT_NAME
10 Sales
20 IT
30 HR

執行後結果：

sql
-- 連接員工表和部門表，顯示員工姓名和部門名稱
SELECT e.emp_name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

結果：

EMP_NAME DEPT_NAME
Alice Sales
Bob Sales
Charlie IT
David HR
Eva IT
Frank HR

2. LEFT JOIN
#

執行前資料：

sql
-- 員工表
SELECT emp_name, dept_id FROM employees ORDER BY dept_id, emp_name;
EMP_NAME DEPT_ID
Alice 10
Bob 10
Charlie 20
Eva 20
David 30
Frank 30
sql
-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;
DEPT_ID DEPT_NAME
10 Sales
20 IT
30 HR

執行後結果：

sql
-- 左連接員工表和部門表，保留所有員工資料
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

結果：

EMP_NAME DEPT_NAME
Alice Sales
Bob Sales
Charlie IT
David HR
Eva IT
Frank HR

3. 多表 JOIN
#

執行前資料：

sql
-- 員工表
SELECT emp_id, emp_name, dept_id FROM employees ORDER BY emp_name;
EMP_ID EMP_NAME DEPT_ID
1 Alice 10
2 Bob 10
3 Charlie 20
4 David 30
5 Eva 20
6 Frank 30
sql
-- 部門表
SELECT dept_id, dept_name FROM departments ORDER BY dept_id;
DEPT_ID DEPT_NAME
10 Sales
20 IT
30 HR
sql
-- 專案表
SELECT emp_id, project_name FROM projects ORDER BY emp_id;
EMP_ID PROJECT_NAME
1 ERP Upgrade
2 Cloud Migration
3 Mobile App
5 Data Migration

執行後結果：

sql
-- 三表連接：員工表、部門表、專案表，顯示員工的部門和專案資訊
SELECT e.emp_name, d.dept_name, p.project_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
LEFT JOIN projects p ON e.emp_id = p.emp_id
ORDER BY e.emp_name;

結果：

EMP_NAME DEPT_NAME PROJECT_NAME
Alice Sales ERP Upgrade
Bob Sales Cloud Migration
Charlie IT Mobile App
David HR NULL
Eva IT Data Migration
Frank HR NULL

四、分析函數
#

1. RANK()、DENSE_RANK()、ROW_NUMBER()
#

  • RANK(): 相同值會得到相同排名，下一個排名會跳過（如：1,1,3,4）
  • DENSE_RANK(): 相同值會得到相同排名，下一個排名連續（如：1,1,2,3）
  • ROW_NUMBER(): 每行都有唯一的連續編號，不考慮相同值
sql
-- 按部門分組，對每個部門內的員工按薪資進行排名
SELECT emp_name,
       dept_id,
       salary,
       RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rank_salary,
       DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS dense_rank_salary,
       ROW_NUMBER() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS row_num
FROM employees;

結果：

EMP_NAME DEPT_ID SALARY RANK_SALARY DENSE_RANK_SALARY ROW_NUM
Bob 10 8000 1 1 1
Alice 10 6000 2 2 2
Eva 20 7000 1 1 1
Charlie 20 5000 2 2 2
David 30 9000 1 1 1
Frank 30 8000 2 2 2

2. SUM() + PARTITION BY
#

可以在每一行上計算聚合結果，而不需要 GROUP BY。PARTITION BY 用於分組，OVER() 定義計算範圍：

  • SUM() OVER(): 計算分組內的總和
  • AVG() OVER(): 計算分組內的平均值
  • COUNT() OVER(): 計算分組內的記錄數

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 在每行顯示部門的總薪資、平均薪資和員工數
SELECT emp_name,
       dept_id,
       salary,
       SUM(salary) OVER(PARTITION BY dept_id) AS dept_salary_total,
       AVG(salary) OVER(PARTITION BY dept_id) AS dept_avg_salary,
       COUNT(*) OVER(PARTITION BY dept_id) AS dept_emp_count
FROM employees;

結果：

EMP_NAME DEPT_ID SALARY DEPT_SALARY_TOTAL DEPT_AVG_SALARY DEPT_EMP_COUNT
Alice 10 6000 14000 7000 2
Bob 10 8000 14000 7000 2
Charlie 20 5000 12000 6000 2
Eva 20 7000 12000 6000 2
David 30 9000 17000 8500 2
Frank 30 8000 17000 8500 2

3. 累計計算 (Running Total)
#

累計計算用於分析資料的累積趨勢，常見於財務報表、銷售分析等場景：

  • ORDER BY: 定義累計的順序
  • ROWS UNBOUNDED PRECEDING: 從分組開始到當前行的所有記錄
  • 無 ROWS 子句: 從分組開始到當前行的累計（相同值會合併）

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 計算每個部門內按薪資降序的累計總和，區分不同累計方式
SELECT emp_name,
       dept_id,
       salary,
       SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC) AS running_total,
       SUM(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS UNBOUNDED PRECEDING) AS cumulative_total
FROM employees;

結果：

EMP_NAME DEPT_ID SALARY RUNNING_TOTAL CUMULATIVE_TOTAL
Bob 10 8000 8000 8000
Alice 10 6000 14000 14000
Eva 20 7000 7000 7000
Charlie 20 5000 12000 12000
David 30 9000 9000 9000
Frank 30 8000 17000 17000

4. 百分比計算
#

百分比計算用於分析各項指標在整體中的佔比，幫助理解資料的相對重要性：

  • 部門內佔比: 員工薪資在部門總薪資中的百分比
  • 整體佔比: 員工薪資在公司總薪資中的百分比
  • ROUND(): 控制小數位數，提高可讀性

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 計算每個員工薪資在部門內和整體的佔比百分比
SELECT emp_name,
       dept_id,
       salary,
       ROUND(salary * 100.0 / SUM(salary) OVER(PARTITION BY dept_id), 2) AS salary_percentage,
       ROUND(salary * 100.0 / SUM(salary) OVER(), 2) AS total_percentage
FROM employees
ORDER BY dept_id, salary DESC;

結果：

EMP_NAME DEPT_ID SALARY SALARY_PERCENTAGE TOTAL_PERCENTAGE
Bob 10 8000 57.14 18.60
Alice 10 6000 1 13.95
Eva 20 7000 58.33 16.28
Charlie 20 5000 41.67 11.63
David 30 9000 52.94 20.93
Frank 30 8000 47.06 18.60

5. 移動平均 (Moving Average)
#

移動平均用於平滑資料波動，識別趨勢，常見於時間序列分析和財務分析：

  • 3點移動平均: 當前行及其前後各1行的平均值
  • 累計移動平均: 從分組開始到當前行的平均值
  • ROWS BETWEEN: 定義移動平均的範圍

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 計算 3 點移動平均和累計移動平均，用於趨勢分析
SELECT emp_name,
       dept_id,
       salary,
       ROUND(AVG(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS moving_avg_3,
       ROUND(AVG(salary) OVER(PARTITION BY dept_id ORDER BY salary DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) AS moving_avg_all
FROM employees;

結果：

EMP_NAME DEPT_ID SALARY MOVING_AVG_3 MOVING_AVG_ALL
Bob 10 8000 7000.00 8000.00
Alice 10 6000 7000.00 7000.00
Eva 20 7000 6000.00 7000.00
Charlie 20 5000 6000.00 6000.00
David 30 9000 8500.00 9000.00
Frank 30 8000 8500.00 8500.00

6. 分位數計算
#

分位數計算用於將資料分成等份，識別資料的分布情況和相對位置：

  • NTILE(): 將資料分成指定數量的等份（如3等份：1,2,3）
  • PERCENT_RANK(): 計算每行在分組中的百分位排名（0.00 到 1.00）
  • 應用場景: 薪資等級劃分、績效評估、風險分析等

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 將每個部門的員工按薪資分成 3 等份，並計算百分位排名
SELECT emp_name,
       dept_id,
       salary,
       NTILE(3) OVER(PARTITION BY dept_id ORDER BY salary DESC) AS salary_tier,
       PERCENT_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS percentile_rank
FROM employees;

結果：

EMP_NAME DEPT_ID SALARY SALARY_TIER PERCENTILE_RANK
Bob 10 8000 1 0.00
Alice 10 6000 2 1.00
Eva 20 7000 1 0.00
Charlie 20 5000 2 1.00
David 30 9000 1 0.00
Frank 30 8000 2 1.00

五、複雜查詢範例
#

1. 找出每個部門薪水最高的員工
#

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 使用子查詢和 RANK 函數，找出每個部門薪資最高的員工
SELECT emp_name, dept_id, salary
FROM (
    SELECT emp_name,
           dept_id,
           salary,
           RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rk
    FROM employees
)
WHERE rk = 1;

結果：

EMP_NAME DEPT_ID SALARY
Bob 10 8000
Eva 20 7000
David 30 9000

2. 找出每個部門薪水排名前 2 的員工
#

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 使用子查詢和 DENSE_RANK 函數，找出每個部門薪資排名前 2 的員工
SELECT emp_name, dept_id, salary
FROM (
    SELECT emp_name,
           dept_id,
           salary,
           DENSE_RANK() OVER(PARTITION BY dept_id ORDER BY salary DESC) AS rnk
    FROM employees
)
WHERE rnk <= 2
ORDER BY dept_id, salary DESC;

結果：

EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

3. 找出專案期間重疊的員工
#

執行前資料：

sql
SELECT emp_id, project_name, start_date, end_date FROM projects ORDER BY emp_id, start_date;
EMP_ID PROJECT_NAME START_DATE END_DATE
1 ERP Upgrade 2020-01-01 2020-12-31
2 Cloud Migration 2021-06-01 2022-02-28
3 Mobile App 2021-03-01 2021-09-30
5 Data Migration 2019-05-01 2020-03-31

執行後結果：

sql
-- 自連接專案表，找出同一個員工負責的時間重疊專案
SELECT p1.emp_id, p1.project_name, p2.project_name
FROM projects p1
JOIN projects p2
  ON p1.emp_id = p2.emp_id
 AND p1.project_id < p2.project_id
 AND p1.start_date <= p2.end_date
 AND p1.end_date >= p2.start_date;

結果：

EMP_ID PROJECT_NAME PROJECT_NAME_2
1 ERP Upgrade Cloud Migration
3 Mobile App Data Migration

4. GROUP BY + PARTITION BY 混合使用
#

執行前資料：

sql
SELECT emp_name, dept_id, salary FROM employees ORDER BY dept_id, salary DESC;
EMP_NAME DEPT_ID SALARY
Bob 10 8000
Alice 10 6000
Eva 20 7000
Charlie 20 5000
David 30 9000
Frank 30 8000

執行後結果：

sql
-- 結合 GROUP BY 和 PARTITION BY，創建部門薪資綜合分析報表
SELECT 
    dept_id,
    COUNT(*) AS dept_emp_count,
    SUM(salary) AS dept_total_salary,
    ROUND(AVG(salary), 2) AS dept_avg_salary,
    MAX(salary) AS dept_max_salary,
    MIN(salary) AS dept_min_salary,
    ROUND(
        (MAX(salary) - MIN(salary)) * 100.0 / AVG(salary), 2
    ) AS salary_range_percentage,
    RANK() OVER(ORDER BY AVG(salary) DESC) AS dept_rank_by_avg,
    ROUND(
        AVG(salary) * 100.0 / SUM(AVG(salary)) OVER(), 2
    ) AS dept_avg_percentage
FROM employees
GROUP BY dept_id
ORDER BY dept_id;

結果：

DEPT_ID DEPT_EMP_COUNT DEPT_TOTAL_SALARY DEPT_AVG_SALARY DEPT_MAX_SALARY DEPT_MIN_SALARY SALARY_RANGE_PERCENTAGE DEPT_RANK_BY_AVG DEPT_AVG_PERCENTAGE
10 2 14000 7000.00 8000 6000 28.57 2 33.33
20 2 12000 6000.00 7000 5000 33.33 3 28.57
30 2 17000 8500.00 9000 8000 11.76 1 38.10

相關文章

Html2canvas + Fabricjs 應用
類別 
前端開發
標籤 
Html2canvas Fabricjs
把 Codepen 嵌入 Hugo文章上
類別 
Hugo網站相關
標籤 
Codepen Hugo
使用Can I Use 檢查瀏覽器相容性
類別 
前端開發
標籤 
Browser