11、Oracle中的檢視

画个一样的我發表於2024-07-14
Oracle

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

學習目標：

  • 描述檢視
  • 建立和修改檢視的定義，刪除檢視
  • 從檢視中查詢資料
  • 透過檢視插入, 修改和刪除資料
  • 使用“Top-N” 分析（重點）

1、視 圖

  • 檢視是一種虛表。
  • 檢視建立在已有表的基礎上, 檢視賴以建立的這些表稱為基表。
  • 向檢視提供資料內容的語句為 SELECT 語句, 可以將檢視理解為儲存起來的 SELECT 語句.
  • 檢視向使用者提供基表資料的另一種表現形式

1.1、為什麼使用檢視

  • 控制資料訪問
  • 簡化查詢
  • 避免重複訪問相同的資料

1.2、簡單檢視和複雜檢視

2、建立檢視

在 CREATE VIEW 語句中嵌入子查詢

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
  [(alias[, alias]...)]
 AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

子查詢可以是複雜的 SELECT 語句

create or replace view empview 
as 
select employee_id emp_id,last_name name,department_name
from employees e,departments d
Where e.department_id = d.department_id
  • 建立檢視舉例
CREATE VIEW 	empvu80
 AS SELECT  employee_id, last_name, salary
    FROM    employees
    WHERE   department_id = 80;
View created.
  • 描述檢視結構
DESCRIBE empvu80
  • 建立檢視時在子查詢中給列定義別名
CREATE VIEW 	salvu50
 AS SELECT  employee_id ID_NUMBER, last_name NAME,
            salary*12 ANN_SALARY
    FROM    employees
    WHERE   department_id = 50;
View created.
  • 在選擇檢視中的列時應使用別名

建立複雜檢視
複雜檢視舉例:

CREATE VIEW	dept_sum_vu
  (name, minsal, maxsal, avgsal)
AS SELECT	 d.department_name, MIN(e.salary), 
             MAX(e.salary),AVG(e.salary)
   FROM      employees e, departments d
   WHERE     e.department_id = d.department_id 
   GROUP BY  d.department_name;
View created.

3、查詢檢視

SELECT *
FROM	salvu50;

4、修改檢視

  • 使用CREATE OR REPLACE VIEW 子句修改檢視
CREATE OR REPLACE VIEW empvu80
  (id_number, name, sal, department_id)
AS SELECT  employee_id, first_name || ' ' || last_name, 
           salary, department_id
   FROM    employees
   WHERE   department_id = 80;
View created.
  • CREATE VIEW 子句中各列的別名應和子查詢中各列相對應

5、檢視中使用DML的規定

  • 可以在簡單檢視中執行 DML 操作
  • 當檢視定義中包含以下元素之一時不能使用delete:
    • 組函式
    • GROUP BY 子句
    • DISTINCT 關鍵字
    • ROWNUM 偽列
create or replace view sal_view
as select
avg(salary) avg_sal from employees
group by department_id

  • 當檢視定義中包含以下元素之一時不能使用update:

    • 組函式
    • GROUP BY子句
    • DISTINCT 關鍵字
    • ROWNUM 偽列
    • 列的定義為表示式

  • 當檢視定義中包含以下元素之一時不能使insert:

    • 組函式
    • GROUP BY 子句
    • DISTINCT 關鍵字
    • ROWNUM 偽列
    • 列的定義為表示式
    • 表中非空的列在檢視定義中未包括

6、遮蔽 DML 操作

  • 可以使用 WITH READ ONLY 選項遮蔽對檢視的DML 操作
  • 任何 DML 操作都會返回一個Oracle server 錯誤
CREATE OR REPLACE VIEW empvu10
    (employee_number, employee_name, job_title)
AS SELECT	employee_id, last_name, job_id
   FROM     employees
   WHERE    department_id = 10
   WITH READ ONLY;
View created.

7、刪除檢視

刪除檢視只是刪除檢視的定義，並不會刪除基表的資料

drop view view;

--示例
drop view empvu80;

8、Top-N 分析

  • Top-N 分析查詢一個列中最大或最小的 n 個值:

    • 銷售量最高的十種產品是什麼?
    • 銷售量最差的十種產品是什麼?

  • 最大和最小的值的集合是 Top-N 分析所關心的

查詢最大的幾個值的 Top-N 分析:

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

注意:
對 ROWNUM 只能使用 < 或 <=, 而用 =, >, >= 都將不能返回任何資料。

查詢工資最高的三名員工:

查詢工資排名在 40（不包括） 到 50 的員工資訊。

SELECT
	*
FROM
	(
	SELECT
		rownum rn,
		employee_id,
		salary
	FROM
		(
		SELECT
			employee_id,
			salary,
			last_name
		FROM
			employees
		ORDER BY
			salary DESC
)
)
	WHERE rn <= 50
	AND rn >40



結果：
RN|EMPLOYEE_ID|SALARY|
--+-----------+------+
41|        154|  7500|
42|        171|  7400|
43|        172|  7300|
44|        164|  7200|
45|        155|  7000|
46|        178|  7000|
47|        161|  7000|
48|        113|  6900|
49|        165|  6800|
50|        203|  6500|

相關文章