–创建简单视图
–建立用于查询员工号、姓名、工资的视图。
create view emp_view
as
select empno,ename,sal from emp;
–查询视图
select * from emp_view;
–创建视图时指定视图的列的别名
create view emp_view2(员工号,姓名,工资)
as
select empno,ename,sal from emp;
–查询视图
select * from emp_view2;
–连接视图
–建立用于获得部门号为10的部门号、部门名称及员工信息。
create view dept_emp_view
as
select d.deptno,d.dname,e.empno,e.ename,e.job
from dept d, emp e
where d.deptno = e.deptno and d.deptno = 10;
–查询视图
select * from dept_emp_view;
–只读视图
–建立查看10号部门员工信息的视图。
create view emp_view3
as
select * from emp where deptno = 10
with read only;
–查询视图
select * from emp_view3;
–测试
update emp_view3 set sal = sal +50;
–视图上的dml操作
–创建视图
create view empnew_view
as
select empno,ename,sal from empnew;
–select
select * from empnew_view;
–insert
insert into empnew_view(empno,ename,sal) values(8888,’layna’,6666);
select * from empnew;
–update
update empnew_view set sal = sal + 100 where empno = 8888;
–delete
delete from empnew_view where empno = 8888;
commit;
–在视图上定义check约束
create view empnew_view2
as
select * from empnew where deptno = 20
with check option constraint ck_view;
–查询视图
select * from empnew_view2;
–测试
–insert或update
update empnew_view2 set deptno = 30 where empno = 7566;
–修改视图
–修改前查询
select * from empnew_view;
–修改empnew_view视图
create or replace view empnew_view
as
select * from emp where job = ‘salesman’;
–修改后查询
select * from empnew_view;
–删除视图
drop view empnew_view;
select * from emp;
–创建复杂视图
create view job_view(job, avgsal, sumsal, maxsal, minsal)
as
select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;
–查看复杂视图
select * from job_view;
–通过with read only子句为复杂视图屏蔽dml操作
create view job_view
as
select job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal from emp group by job
with read only;