Chapter 14 View

Chapter 14 View

最基本的表也就是实体是不会发生变化的，变化的应该是视图。

create view average
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
group by d.dname

select *
from average;

create or replace view acct
("名字","工资","职位","雇佣日期")
as
select ename, sal, job, hiredate
from emp;

select * from acct;

select view_name, text_length, text
from user_views;


create view sales30
as
select *
from emp
where deptno = 30
with check option constraint sales30_ck;
--所有通过该视图进行的DML操作操作都不能违反了在创建视图是用where子句所限定的条件

SQL> desc sales30;
Name     Type         Nullable Default Comments
-------- ------------ -------- ------- --------
EMPNO    NUMBER(4)                              
ENAME    VARCHAR2(10) Y                         
JOB      VARCHAR2(9)  Y                         
MGR      NUMBER(4)    Y                         
HIREDATE DATE         Y                         
SAL      NUMBER(7,2)  Y                         
COMM     NUMBER(7,2)  Y                         
DEPTNO   NUMBER(2)    Y            


select *
from sales30;


update sales30
set deptno = 10
where job = 'MANAGER';


update acct
set "工资" =9999;

select *
from acct;

select ename, sal, job
from emp
where deptno = 10;

rollback;


create or replace view acct
as
select d.dname "部门", avg(e.sal) "平均工资", avg(nvl(comm,0)) "平均佣金", count(*) "员工数"
from emp e, dept d
where e.deptno = d.deptno
and e.deptno = 10
group by d.dname
with read only;


update acct
set "工 资" = 9999;

delete from acct;

select view_name, text_length
from user_views

drop view acct;

select
from a , (select * from ...) m



select *
from
where rownum <=5;