15、Oracle中的高階子查詢

画个一样的我發表於2024-08-10
Oracle

最近專案要用到Oracle，奈何之前沒有使用過，所以在B站上面找了一個學習影片，用於記錄學習過程以及自己的思考。
影片連結：
【尚矽谷】Oracle資料庫全套教程，oracle從安裝到實戰應用
如果有侵權，請聯絡刪除，謝謝。

學習目標：

  • 書寫多列子查詢
  • 在 FROM 子句中使用子查詢
  • 在SQL中使用單列子查詢
  • 書寫相關子查詢
  • 使用 EXISTS 和 NOT EXISTS 運算子
  • 使用子查詢更新和刪除資料
  • 使用 WITH 子句

1、子查詢

子查詢是巢狀在 SQL 語句中的另一個SELECT 語句

SELECT	select_list
FROM	table
WHERE	expr operator (SELECT select_list
		         FROM	  table);

子查詢 (內查詢) 在主查詢執行之前執行
主查詢(外查詢)使用子查詢的結果

子查詢應用舉例
問題：查詢工資大於149號員工工資的員工的資訊

SELECT last_name
FROM   employees
WHERE  salary > 
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 149) ;

2、多列子查詢

主查詢與子查詢返回的多個列進行比較

多列子查詢中的比較分為兩種:

  • 成對比較
  • 不成對比較

2.1、成對比較舉例

問題：查詢與141號或174號員工的manager_id和department_id相同的其他員工的employee_id, manager_id, department_id

SELECT	employee_id, manager_id, department_id
FROM	employees
WHERE  (manager_id, department_id) IN
                      (SELECT manager_id, department_id
                       FROM   employees
                       WHERE  employee_id IN (141,174))
AND	employee_id NOT IN (141,174);

(column1, column2) in (column1, column2)
以及最後面的排查不需要的資料 NOT IN (141,174);

2.2、不成對比較舉例

SELECT  employee_id, manager_id, department_id
FROM    employees
WHERE   manager_id IN                (SELECT  manager_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND     department_id IN               (SELECT  department_id
                   FROM    employees
                   WHERE   employee_id IN (174,141))
AND	employee_id NOT IN(174,141);

與上面的成對比較不同，成對比較是需要前面的(column1, column2)和後面的相同才可以。

3、在 FROM 子句中使用子查詢

問題：返回比本部門平均工資高的員工的last_name, department_id, salary及平均工資

方法一：

select last_name,department_id,salary,
(select avg(salary)from employees e3 
where e1.department_id = e3.department_id 
group by department_id) avg_salary
from employees e1
where salary > 
         (select avg(salary)
          from employees e2  
          where e1.department_id = e2.department_id
          group by department_id
          )

方法二：

SELECT  a.last_name, a.salary, 
        a.department_id, b.salavg
FROM    employees a, (SELECT   department_id, 
                      AVG(salary) salavg
                      FROM     employees
                      GROUP BY department_id) b
WHERE   a.department_id = b.department_id
AND     a.salary > b.salavg;

4、單列子查詢表示式

  • 單列子查詢表示式是在一行中只返回一列的子查詢
  • Oracle8i 只在下列情況下可以使用, 例如:
    • SELECT 語句 (FROM 和 WHERE 子句)
    • INSERT 語句中的VALUES列表中
  • Oracle9i中單列子查詢表示式可在下列情況下使用:
    • DECODE 和 CASE
    • SELECT 中除 GROUP BY 子句以外的所有子句中

4.1、單列子查詢應用舉例

  • 在 CASE 表示式中使用單列子查詢

問題：顯示員工的employee_id,last_name和location。其中，若員工的department_id與location_id為1800的department_id相同，則location為’Canada’,其餘則為’USA’。

SELECT employee_id, last_name,
       (CASE
        WHEN department_id =
                           (SELECT department_id FROM departments
							WHERE location_id = 1800)
        THEN 'Canada' ELSE 'USA' END) location
FROM   employees;
  • 在 ORDER BY 子句中使用單列子查詢

問題：查詢員工的employee_id,last_name,要求按照員工的department_name排序

SELECT   employee_id, last_name
FROM     employees e
ORDER BY  (SELECT department_name
           FROM departments d
           WHERE e.department_id = d.department_id);

5、相關子查詢

相關子查詢按照一行接一行的順序執行，主查詢的每一行都執行一次子查詢

重要的時看懂這裡的執行順序

 SELECT column1, column2, ...
 FROM   table1  outer
 WHERE  column1 operator 
			  (SELECT  colum1, column2
                       FROM    table2
                       WHERE   expr1 = outer.expr2);

其中的outer只是表的別名，取其他名稱也可以

子查詢中使用主查詢中的列

5.1 相關子查詢舉例

問題：查詢員工中工資大於本部門平均工資的員工的last_name,
salary和其department_id

問題：若employees表中employee_id與job_history表中employee_id相同的數目不小於2，輸出這些相同id的員工的employee_id,last_name和其job_id

SELECT e.employee_id, last_name,e.job_id
FROM   employees e 
WHERE  2 <= (SELECT COUNT(*)
             FROM   job_history 
             WHERE  employee_id = e.employee_id);

執行順序：
1、先獲取到主查詢中的列
2、將主查詢用到的列用於子查詢中，注意：這裡會取主查詢中用到的表裡的每一行資料，用於下面的步驟（即3、4）。
3、判斷 employees 表中的employee_id與job_history相同，計算相同的相同的數量
4、判斷子查詢得到的數量是否 >= 2，條件成立的則返回。

6、EXISTS 運算子

  • EXISTS 運算子檢查在子查詢中是否存在滿足條件的行
  • 如果在子查詢中存在滿足條件的行:
    • 不在子查詢中繼續查詢
    • 條件返回 TRUE
  • 如果在子查詢中不存在滿足條件的行:
    • 條件返回 FALSE
    • 繼續在子查詢中查詢

6.1、EXISTS 運算子應用舉例

問題：查詢公司管理者的employee_id,last_name,job_id,department_id資訊

SELECT employee_id, last_name, job_id, department_id
FROM   employees outer
WHERE  EXISTS ( SELECT 'X'
                 FROM   employees
                 WHERE  manager_id = 
                        outer.employee_id);

注意查詢條件是 manager_id = outer.employee_id。

6.2、NOT EXISTS 運算子應用舉例

問題：查詢departments表中，不存在於employees表中的部門的department_id和department_name

7、相關更新

UPDATE table1 alias1
SET    column = (SELECT expression
                 FROM   table2 alias2
                 WHERE  alias1.column =    
                        alias2.column);

使用相關子查詢依據一個表中的資料更新另一個表的資料

7.1、相關更新應用舉例

1、
ALTER TABLE employees
ADD(department_name VARCHAR2(14));


2、
UPDATE employees e
SET    department_name = 
              (SELECT department_name 
	       FROM   departments d
	       WHERE  e.department_id = d.department_id);

8、相關刪除

 DELETE FROM table1 alias1
 WHERE  column operator 
	(SELECT expression
 	 FROM   table2 alias2
 	 WHERE  alias1.column = alias2.column);

使用相關子查詢依據一個表中的資料刪除另一個表的資料

8.1、相關刪除應用舉例

問題：刪除表employees中，其與emp_history表皆有的資料

DELETE FROM employees E
WHERE employee_id =  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = E.employee_id);

9、WITH 子句

  • 使用 WITH 子句, 可以避免在 SELECT 語句中重複書寫相同的語句塊
  • WITH 子句將該子句中的語句塊執行一次並儲存到使用者的臨時表空間中
  • 使用 WITH 子句可以提高查詢效率

4、WITH 子句應用舉例

問題：查詢公司中各部門的總工資大於公司中各部門的平均總工資的部門資訊

WITH dept_costs  AS (
   SELECT  d.department_name, SUM(e.salary) AS dept_total
   FROM    employees e, departments d
   WHERE   e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost    AS (
   SELECT SUM(dept_total)/COUNT(*) AS dept_avg
   FROM   dept_costs)
SELECT * 
FROM   dept_costs 
WHERE  dept_total >
        (SELECT dept_avg 
         FROM avg_cost)
ORDER BY department_name;

相關文章