合肥网站排名,广西建设局网站首页,义乌公司网站建设,网站建设方案书模板下载--创建简单视图--建立用于查询员工号、姓名、工资的视图。create view emp_viewasselect empno,ename,sal from emp; --查询视图select * from emp_view; --创建视图时指定视图的列的别名create view emp_view2(员工号,姓名,工资)asselect empno,ename,sal from emp; --查询视图…--创建简单视图--建立用于查询员工号、姓名、工资的视图。create view emp_viewasselect empno,ename,sal from emp; --查询视图select * from emp_view; --创建视图时指定视图的列的别名create view emp_view2(员工号,姓名,工资)asselect empno,ename,sal from emp; --查询视图select * from emp_view2; --连接视图--建立用于获得部门号为10的部门号、部门名称及员工信息。create view dept_emp_viewasselect d.deptno,d.dname,e.empno,e.ename,e.jobfrom dept d, emp ewhere d.deptno e.deptno and d.deptno 10; --查询视图select * from dept_emp_view; --只读视图--建立查看10号部门员工信息的视图。create view emp_view3asselect * from emp where deptno 10with read only; --查询视图select * from emp_view3;--测试update emp_view3 set sal sal 50;--视图上的DML操作 --创建视图create view empnew_viewasselect empno,ename,sal from empnew; --selectselect * from empnew_view; --insertinsert into empnew_view(empno,ename,sal) values(8888,LAYNA,6666);select * from empnew; --updateupdate empnew_view set sal sal 100 where empno 8888; --deletedelete from empnew_view where empno 8888;commit; --在视图上定义check约束create view empnew_view2asselect * from empnew where deptno 20with check option constraint ck_view; --查询视图select * from empnew_view2; --测试--insert或updateupdate empnew_view2 set deptno 30 where empno 7566; --修改视图 --修改前查询select * from empnew_view; --修改empnew_view视图create or replace view empnew_viewasselect * 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)asselect 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_viewasselect job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal from emp group by jobwith read only; 转载于:https://www.cnblogs.com/xiaomifeng1010/p/11111958.html
