【SQL】视图VIEW

目录

定义

创建语法

测试

方法一 多表关联查询

方法二 创建中间表来使查询简化

方法三 创建视图

创建视图

视图修改

with read only子句

with check option子句

基表不能通过视图增、删、改的情况

组函数

group by子句

distinct关键字

字段表达式

没有null null列

删除视图

---

定义

视图中不存放数据，他只是一个定义(物化视图除外)

踏实经过制定的方式来显示一个或多个表的数据

视图所依据的表被称为基表

 

他可以提供另一种级别的表安全性，不想被看到的内容不会显示

可以使复杂的数据隐藏起来

简化多表查询的sql

通过对列的重命名，进行另一个角度的数据提供

 

创建语法

create [or replace] [force] view VIEW_NAME [(alias[, alias]…)] as SELECT_STATEMENT [witch check option] [with read only];

 

测试

查询emp表中每位员工的部门位置以及工资等级情况

方法一 多表关联查询

SQL> select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by grade;   ENAME     DNAME           GRADE --------- ---------- ---------- JAMES     SALES               1 ADAMS     RESEARCH            1 SMITH     RESEARCH            1 MARTIN    SALES               2 WARD      SALES               2 MILLER    ACCOUNTING          2 ALLEN     SALES               3 TURNER    SALES               3 BLAKE     SALES               4 JONES     RESEARCH            4 SCOTT     RESEARCH            4 FORD      RESEARCH            4 CLARK     ACCOUNTING          4 KING      ACCOUNTING          5   14 rows selected.

sql太长了，如果想简化sql，可以将查询的数据插入到一个新的表中

方法二 创建中间表来使查询简化

SQL> create table tb_view01 as select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal     order by grade;   Table created.   SQL> select * from tb_view01;   ENAME     DNAME           GRADE --------- ---------- ---------- JAMES     SALES               1 ADAMS     RESEARCH            1 SMITH     RESEARCH            1 MARTIN    SALES               2 WARD      SALES               2 MILLER    ACCOUNTING          2 ALLEN     SALES               3 TURNER    SALES               3 BLAKE     SALES               4 JONES     RESEARCH            4 SCOTT     RESEARCH            4 FORD      RESEARCH            4 CLARK     ACCOUNTING          4 KING      ACCOUNTING          5   14 rows selected.

这样虽然可以select *，但是tb_view01不能随着基表变化

 

方法三 创建视图

SQL> create view view_01 as select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal order by grade;   View created.   SQL> set line 50 SQL> desc view_01;  Name                    Null?    Type  ----------------------- -------- ----------------  ENAME                            VARCHAR2(10)  DNAME                            VARCHAR2(14)  GRADE                            NUMBER   SQL> select * from view_01;   ENAME     DNAME           GRADE --------- ---------- ---------- JAMES     SALES               1 ADAMS     RESEARCH            1 SMITH     RESEARCH            1 MARTIN    SALES               2 WARD      SALES               2 MILLER    ACCOUNTING          2 ALLEN     SALES               3 TURNER    SALES               3 BLAKE     SALES               4 JONES     RESEARCH            4 SCOTT     RESEARCH            4 FORD      RESEARCH            4 CLARK     ACCOUNTING          4 KING      ACCOUNTING          5   14 rows selected.   SQL> select * from tab where tname='VIEW_01';   TNAME      TABTYPE                CLUSTERID ---------- --------------------- ---------- VIEW_01    VIEW

 

创建视图

创建视图需要有create view权限

如果没有则会报错

SQL> conn scott/tiger; Connected. SQL> create view view_02 as select ename from emp; create view view_02 as select ename from emp             * ERROR at line 1: ORA-01031: insufficient privileges

授权方式很简单

SQL> conn / as sysdba Connected. SQL> grant create view to scott;   Grant succeeded.   SQL> conn scott/tiger Connected. SQL> create view view_02 as select ename from emp;   View created.

使用revoke即可回收

SQL> conn / as sysdba Connected. SQL> revoke create view from scott;   Revoke succeeded.

可以通过视图对基表进行dml操作，前提是视图中必须有几表关键字段

SQL> update view_02 set ename='HAHA' where ename='SMITH';   1 row updated.   SQL> select * from emp;   EMPNO ENAME     JOB          MGR HIREDATE              SAL  COMM DEPTNO ----- --------- ---------- ----- ------------------- ----- ----- ------  7369 HAHA      CLERK       7902 1980-12-17 00:00:00   800           20  7499 ALLEN     SALESMAN    7698 1981-02-20 00:00:00  1600   300     30  7521 WARD      SALESMAN    7698 1981-02-22 00:00:00  1250   500     30  7566 JONES     MANAGER     7839 1981-04-02 00:00:00  2975           20  7654 MARTIN    SALESMAN    7698 1981-09-28 00:00:00  1250  1400     30  7698 BLAKE     MANAGER     7839 1981-05-01 00:00:00  2850           30  7782 CLARK     MANAGER     7839 1981-06-09 00:00:00  2450           10  7788 SCOTT     ANALYST     7566 1987-04-19 00:00:00  3000           20  7839 KING      PRESIDENT        1981-11-17 00:00:00  5000           10  7844 TURNER    SALESMAN    7698 1981-09-08 00:00:00  1500     0     30  7876 ADAMS     CLERK       7788 1987-05-23 00:00:00  1100           20  7900 JAMES     CLERK       7698 1981-12-03 00:00:00   950           30  7902 FORD      ANALYST     7566 1981-12-03 00:00:00  3000           20  7934 MILLER    CLERK       7782 1982-01-23 00:00:00  1300           10   14 rows selected.   SQL> rollback;   Rollback complete.

另外如果基表主键中的定义在视图中没有，那么也无法对基表进行赋值

SQL> insert into view_02 values('AAA'); insert into view_02 values('AAA') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")

 

视图修改

with read only子句

将view_02修改为with read only 属性

SQL> create or replace view view_02 as select ename,sal from emp with read only;   View created.   SQL> select * from view_02;   ENAME       SAL --------- ----- SMITH       800 ALLEN      1600 WARD       1250 JONES      2975 MARTIN     1250 BLAKE      2850 CLARK      2450 SCOTT      3000 KING       5000 TURNER     1500 ADAMS      1100 JAMES       950 FORD       3000 MILLER     1300   14 rows selected.

将视图改为只读以后增、删、改等DML都不允许了

SQL> insert into view_02 values('HAHA',9999); insert into view_02 values('HAHA',9999) * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view   SQL> delete view_02 where ename='KING'; delete view_02 where ename='KING'        * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view   SQL> update view_02 set ename='HAHA' where ename='SMITH'; update view_02 set ename='HAHA' where ename='SMITH'                                       * ERROR at line 1: ORA-42399: cannot perform a DML operation on a read-only view

with check option子句

满足条件的数据才能通过视图而看到，其他的不可以

修改view_02并添加with check option子句

SQL> create or replace view view_02 as select * from emp where deptno=10 with check option;   View created.   SQL> select * from view_02;   EMPNO ENAME     JOB          MGR HIREDATE              SAL  COMM DEPTNO ----- --------- ---------- ----- ------------------- ----- ----- ------  7782 CLARK     MANAGER     7839 1981-06-09 00:00:00  2450           10  7839 KING      PRESIDENT        1981-11-17 00:00:00  5000           10  7934 MILLER    CLERK       7782 1982-01-23 00:00:00  1300           10

测试通过视图修改使用with check option限定字段deptno的值，包括外键限定的值

SQL> update view_02 set deptno=40; update view_02 set deptno=40        * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation     SQL> update view_02 set deptno=99; update view_02 set deptno=99 * ERROR at line 1: ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not found

不能通过视图修改不属于deptno=10的数据

SQL> update view_02 set ename='HAHA' where deptno=20;   0 rows updated.   SQL> update view_02 set ename='HAHA' where empno=7902;   0 rows updated.   SQL> update view_02 set ename='HAHA' where deptno=10;   3 rows updated.   SQL> rollback;   Rollback complete.

 

基表不能通过视图增、删、改的情况

组函数

当视图中包含组函数的时候，不可以增、删、改

创建测试视图

SQL> create view view_03 as select sum(sal),avg(sal) from emp; create view view_03 as select sum(sal),avg(sal) from emp                               * ERROR at line 1: ORA-00998: must name this expression with a column alias

创建包含组函数的视图时必须要给列起别名

SQL> create view view_03 as select sum(sal) s,avg(sal) a from emp;   View created.   SQL> desc view_03  Name                    Null?    Type  ----------------------- -------- ----------------  S                                NUMBER  A                                NUMBER   SQL> select * from view_03;            S          A ---------- ----------      29025 2073.21429

增删改测试

SQL> insert into view_03 values(100,100); insert into view_03 values(100,100)             * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view     SQL> delete view_03; delete view_03        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view     SQL> update view_03 set a=200 where s=29025; update view_03 set a=200 where s=29025        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view

group by子句

当视图中包含group by子句时，不可以增、删、改

创建测试视图

SQL> create view view_04 as select deptno,sum(sal) s from emp group by deptno;   View created.   SQL> desc view_04;  Name                    Null?    Type  ----------------------- -------- ----------------  DEPTNO                           NUMBER(2)  S                                NUMBER   SQL> select * from view_04;   DEPTNO          S ------ ----------     30       9400     20      10875     10       8750

增删改测试

SQL> insert into view_04 values(40,9999); insert into view_04 values(40,9999) * ERROR at line 1: ORA-01733: virtual column not allowed here     SQL> delete view_04; delete view_04        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view     SQL> update view_04 set s=9999 where deptno=10; update view_04 set s=9999 where deptno=10        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view

distinct关键字

当视图中包含distinct关键字的时候，不可以增、删、改

创建测试视图

SQL> create view view_05 as select distinct job from emp;   View created.   SQL> desc view_05;  Name                    Null?    Type  ----------------------- -------- ----------------  JOB                              VARCHAR2(9)   SQL> select * from view_05;   JOB ---------- CLERK SALESMAN PRESIDENT MANAGER ANALYST

增删改测试

SQL> insert into view_05 values('HAHA'); insert into view_05 values('HAHA')             * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view     SQL> delete view_05; delete view_05        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view     SQL> update view_05 set job='HAHA' where job='CLERK'; update view_05 set job='HAHA' where job='CLERK'        * ERROR at line 1: ORA-01732: data manipulation operation not legal on this view

字段表达式

当视图中包含功过基表中的字段表达式而得来的信息时，不可以增、改

SQL> desc view_06;  Name                    Null?    Type  ----------------------- -------- ----------------  EMPNO                   NOT NULL NUMBER(4)  YEAR                             NUMBER   SQL> select * from view_06;   EMPNO       YEAR ----- ----------  7369       9600  7499      19500  7521      15500  7566      35700  7654      16400  7698      34200  7782      29400  7788      36000  7839      60000  7844      18000  7876      13200  7900      11400  7902      36000  7934      15600   14 rows selected.

增删改测试

SQL> insert into view_06 values(9999,99999); insert into view_06 values(9999,99999) * ERROR at line 1: ORA-01733: virtual column not allowed here     SQL> update view_06 set year=99999 where empno=7788; update view_06 set year=99999 where empno=7788                    * ERROR at line 1: ORA-01733: virtual column not allowed here     SQL> delete from view_06 where empno=7788;   1 row deleted.   SQL> rollback;   Rollback complete.

可以对基表进行删除

没有null null列

当视图中不包含基表中被定义为null null的列时，不可以增

创建测试表

SQL> create view view_07 as select ename,job from emp;   View created.   SQL> desc emp;  Name                    Null?    Type  ----------------------- -------- ----------------  EMPNO                   NOT NULL NUMBER(4)  ENAME                            VARCHAR2(10)  JOB                              VARCHAR2(9)  MGR                              NUMBER(4)  HIREDATE                         DATE  SAL                              NUMBER(7,2)  COMM                             NUMBER(7,2)  DEPTNO                           NUMBER(2)   SQL> desc view_07;  Name                    Null?    Type  ----------------------- -------- ----------------  ENAME                            VARCHAR2(10)  JOB                              VARCHAR2(9)   SQL> select * from view_07;   ENAME     JOB --------- ---------- SMITH     CLERK ALLEN     SALESMAN WARD      SALESMAN JONES     MANAGER MARTIN    SALESMAN BLAKE     MANAGER CLARK     MANAGER SCOTT     ANALYST KING      PRESIDENT TURNER    SALESMAN ADAMS     CLERK JAMES     CLERK FORD      ANALYST MILLER    CLERK   14 rows selected.

增删改测试

SQL> insert into view_07 values('HAHA','HEHE'); insert into view_07 values('HAHA','HEHE') * ERROR at line 1: ORA-01400: cannot insert NULL into ("SCOTT"."EMP"."EMPNO")     SQL> delete view_07 where ename='SMITH';   1 row deleted.   SQL> update view_07 set JOB='HAHA' where ename='KING';   1 row updated.

 

删除视图

SQL> drop view view_01;   View dropped.

因为视图本身没有数据，因此不存在空间释放的问题