視圖-Oracle高級知識(1)-資料庫（21）
這篇文章算是Oracle資料庫的高級知識：視圖(View)）是非常重要的內容。知識點繁雜，靠理解、實戰和筆記來熟練運用。
1 視圖(View)
1.1 什麼是視圖
可以通過創建表的視圖來表現數據的邏輯子集或數據的組合。視圖是基於表或另一個視
圖的邏輯表，一個視圖並不包含它自己的數據，它象一個窗口，通過該窗口可以查看或改變
表中的數據。視圖基於其上的表稱為基表。
1.2 視圖的優越性
? 視圖限制數據的訪問，因為視圖能夠選擇性的顯示錶中的列。
? 視圖可以用來構成簡單的查詢以取回複雜查詢的結果。例如，視圖能用於從多表中查
詢信息，而用戶不必知道怎樣寫連接語句。
? 視圖對特別的用戶和應用程序提供數據獨立性，一個視圖可以從幾個表中取回數據。
1.3 視圖類型
簡單視圖和複雜視圖
1.4 簡單視圖 VS 複雜視圖
視圖有兩種分類：簡單和複雜，基本區別涉及 DML (INSERT、UPDATE 和 DELETE)
操作。
簡單視圖：
? 數據僅來自一個表
? 不包含函數或數據分組
? 能通過視圖執行 DML 操作
複雜視圖：
? 數據來自多個表
? 包含函數或數據分組
? 不允許通過視圖進行 DML 操作。
1.5 創建簡單視圖。
1.5.1 示例
創建一個視圖，視圖中包含部門 id 為 80 的員工的 id，名字以及薪水。
create view emp80 as select
e.employee_id,e.last_name,e.salary from
employees e;
1.6 用子查詢中的列別名創建視圖
如果在創建視圖的查詢語句中含有列別名，那麼列別名將作為視圖的列名。
1.6.1 示例
創建一個視圖，包含部門 id 為 50 的員工 id 使用 ID_NUMBER 命名該列，包含員工名
字使用 NAME 命名該列，包含員工的年薪使用 ANN_SALARY 命名該列。
create view emp50 as select e.employee_id id_number,e.last_name name,12*e.salary
ann_salary from employees e;
1.7 從視圖中取回數據
1.7.1 示例一
查詢部門 id 為 80 的員工信息，包含他們的 id，名字以及薪水。
select * from emp80;
1.7.2 示例二
查詢部門 id 為 50 的員工信息，包含他們的 id 和薪水。
select e.id_number,e.ann_salary from emp50 e;
1.8 創建複雜視圖
1.8.1 示例
創建一個視圖，包含每個部門的部門名稱，部門最低薪水、部門最高薪水以及部門的平
均薪水。
create view dept_name as select d.department_name,min(e.salary)
min ,max(e.salary) max ,avg(e.salary)avg from employees e, departments d where
e.department_id = d.department_id group by d.department_name;
1.9 在定義視圖時指定列名
1.9.1 示例
創建一個視圖，包含每個部門的部門名稱、部門最低薪水、部門最高薪水以及部門的平
均薪水。將部門名稱命名為 name、最低薪水命名為 minsal、最高薪水命名為 maxsal、平
均薪水命名為 avgsal。
create view dept_name1(name,minsal,maxsal,avgsal) as
select d.department_name,min(e.salary) ,max(e.salary) ,avg(e.salary)avg from
employees e,departments d where e.department_id = d.department_id group by
d.department_name;
1.10 視圖中 DML 操作的執行規則
如果視圖中包含下面的部分就不能修改數據：
? 組函數
? GROUP BY 子句
? DISTINCT 關鍵字
? 用表達式定義的列
1.10.1 示例
刪除 emp80 視圖中僱員 ID 為 190 的僱員。
delete from emp80 e where e.employee_id = 190;
1.11 拒絕 DML 操作
1.11.1 示例
創建一個簡單視圖，包含 employees 表中的所有數據，單該視圖拒絕 DML 操作。
create view v_emp as select * from employees with read only;
1.12 通過工具創建視圖
1.13 刪除視圖
刪除視圖不會丟失數據，因為視圖是基於資料庫中的基本表的。
1.13.1 示例
刪除名稱為 emp90 的視圖。
drop view emp90;
1.14 內建視圖
1.14.1 是什麼內建視圖
? 內建視圖是一個帶有別名 (或相關名) 的可以在 SQL 語句中使用的子查詢。
? 一個主查詢的在 FROM 子句中指定的子查詢就是一個內建視圖。
內建視圖：內建視圖由位於 FROM 子句中命名了別名的子查詢創建。該子查詢定義一
個可以在主查詢中引用數據源。
1.14.1.1 示例
顯示那些僱員低於他們部門最高薪水的僱員的名字、薪水、部門號和他們部門最高的薪水
select
em.last_name,em.salary,em.department_id,e.maxsal from employees em ,(select
e.department_id, max(e.salary) maxsal from employees e group by
e.department_id)e whereem.department_id = e.department_id and
em.salary < e.maxsal;
1.15 Top-N 分析
1.15.1 什麼是「Top-N」 分析
Top-N 查詢在需要基於一個條件，從表中顯示最前面的 n 條記錄或最後面的 n 條記
錄時是有用的。該結果可以用於進一步分析，例如，用 Top-N 分析你可以執行下面的查詢
類型：
? 在中掙錢最多的三個人
? 公司中最新的四個成員
? 銷售產品最多的兩個銷售代表
? 過去 6 個月中銷售最好的 3 種產品
1.15.2 執行「Top-N」 分析
Top-N 查詢使用一個帶有下面描述的元素的一致的嵌套查詢結構：
?子查詢或者內建視圖產生數據的排序列表，該子查詢或者內建視圖包含 ORDER BY 子
句來確保排序以想要的順序排列。為了取回最大值，需要用 DESC 參數。
? 在最後的結果集中用外查詢限制行數。外查詢包括下面的組成部分：
? ROWNUM 偽列，它為從子查詢返回的每一行指定一個從 1 開始的連續的值
? 一個 WHERE 子句，它指定被返回的 n 行，外 WHERE 子句必須用一個<或
者<=操作。
1.15.3 示例一
從 EMPLOYEES 表中顯示掙錢最多的 3 個人的名字及其薪水。
select rownum ,last_name,salary from (select last_name, salary from employees order
by salary desc) where rownum <=3;
1.15.4 示例二
顯示公司中 4 個資格最老的僱員顯示他們的入職時間與名字。
select rownum, e.last_name,e.hire_date from (select last_name, hire_date from
employees order by hire_date) e where rownum <= 4;
1.16 Oracle 的分頁查詢
1.16.1 什麼是分頁查詢
當查詢的結果集數據量過大時，可能會導致各種各樣的問題發生，例如：伺服器資源被
耗盡，因數據傳輸量過大而使處理超時，等等。最終都會導致查詢無法完成。解決這個問題
的一個策略就是「分頁查詢」，也就是說不要一次性查詢所有的數據，每次只查詢一部分數
據。這樣分批次地進行處理，可以呈現出很好的用戶體驗，對伺服器資源的消耗也不大。
分頁查詢原則：
在內建視圖中通過 rownum 偽劣值的判斷來指定獲取數據的數量。
1.16.2 示例
查詢僱員表中數據，每次只返回 10 條數據。
select * from (select rownum rn,e.* from employees e) em where em.rn between 11
and 20;
推薦閱讀：
