ORACLE視圖
基本概念：
視圖：
從表中抽出的邏輯上相關的數據集合。
視圖是一種虛表。
視圖建立在已有表的基礎上, 視圖賴以建立的這些表稱為基表。
向視圖提供數據內容的語句為 SELECT 語句, 可以將視圖理解為存儲起來的 SELECT 語句.
視圖向用戶提供基表數據的另一種表現形式
為什麼使用視圖？
控制數據訪問
簡化查詢
避免重復訪問相同的數據
常用腳本：
--創建視圖
create view v_emp as select * from employees t where t.department_id = '60';
select * from v_emp;
update v_emp01 t set t.SALARY=11100;
select * from employees where employees.first_name='Alexander';
--多表連接創建視圖
create view v_emp00 as select e.employee_id id , e.last_name name ,e.salary , d.department_name from employees e ,departments d
where e.department_id = d.department_id;
--修改視圖
create or replace view v_emp00 as select e.employee_id id , e.last_name name , d.department_name from employees e ,departments d
where e.department_id = d.department_id;
--使視圖只有讀的操作
create view v_emp01 as select e.employee_id id , e.last_name name ,e.salary , d.department_name from employees e ,departments d
where e.department_id = d.department_id with read only;
--復雜視圖 使用了組函數 是不能進行增刪改的
create view v_emp02 as select department_name ,avg(salary) avg_sal from employees e ,departments d where e.employee_id = d.department_id
group by department_name;
--top N 工資最高的前十個人
select rownum employee_id , first_name ,salary from
(select employee_id ,first_name ,salary from employees order by salary desc)
where rownum <= 10;
--對 ROWNUM 偽列 只能使用 < 或 <=, 而用 =, >, >= 都將不能返回任何數據，以下sql查不到數據
select rownum employee_id , first_name ,salary from
(select employee_id ,first_name ,salary from employees order by salary desc)
where rownum <= 50 and rownum >40 ;
--通過以下方式解決
select rn employee_id , first_name ,salary from(
select rownum rn ,employee_id , first_name ,salary from
(select employee_id ,first_name ,salary from employees order by salary desc)
)where rn <= 50 and rn >40 ;
