oracle——视图

视图概念：

/*简单视图*/
--创建视图
create view empview
as
select employee_id,last_name,salary
from employees
where department_id = 80

select * from empview

update empview set salary = 20000 where employee_id = 179   --修改视图

select * from employees where employee_id = 179     --修改视图后整个表被更新

--创建视图后起别名
create view empview1
as
select employee_id 编号 ,last_name 姓名,salary 工资
from employees
where department_id = 80

select * from empview1

--基于多表创建视图
create view empview2
as
select employee_id 编号 ,last_name 姓名,salary 工资
from employees e,departments d
where e.department_id = d.department_id

select * from empview2

--修改视图
create or replace view empview2
as
select employee_id 编号,salary 工资
from employees e,departments d
where e.department_id = d.department_id


--with read only 权限
create or replace view empview2
as
select employee_id 编号 ,last_name 姓名,salary 工资
from employees e,departments d
where e.department_id = d.department_id
with read only

update empview2 set 工资 = 20000 where 编号 = 206  --修改视图，提示无法对只读视图进行DML操作


/*复杂视图（用到了分组或函数）*/
create or replace view empview3
as
select department_name name,avg(salary) avg_sal
from employees e,departments d
where e.department_id = d.department_id
group by department_name

select * from empview3

--视图的删除
drop view empview3


--TOP—N分析(top10)--等价于mysql中的limit
select rownum, employee_id, last_name, salary
  from (select employee_id, last_name, salary
          from employees
         order by salary desc)
 where rownum <= 10


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