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 ;
基本概念：
视图：
	从表中抽出的逻辑上相干的数据聚集。
	视图是一种虚表。 
	视图树立在已有表的