SQL语句（7）--- 视图

1. 视图的用途：

　　1. 限制数据的存取：用户只能看到基表的部分信息

　　2. 可以使复杂的查询变得容易

　　3. 提供数据的独立性

2. 简单视图：视图与基表一对一，包含聚合函数的除外，对简单视图修改就是对基表修改

创建视图 V1
SQL> create view v1 as select ename,empno,sal from test where deptno=10;

View created.

SQL> select * from v1;

ENAME              EMPNO        SAL
-------------------- ---------- ----------
CLARK               7782       2450
KING               7839       5000
MILLER               7934       1300

更新视图
SQL> update v1 set  ename='aaaa' where empno=7782;

1 row updated.

查看基表变化
SQL> select ename,empno from test where empno=7782;

ENAME              EMPNO
-------------------- ----------
aaaa               7782

SQL> rollback;

Rollback complete.

SQL> select ename, empno from test where empno=7782;

ENAME              EMPNO
-------------------- ----------
CLARK               7782

SQL>

3. 复杂视图：视图与基表记录一对多，或者包含聚合函数，复杂视图不能被修改，使用聚合函数时，必须要设置别名

 1 创建复杂视图 V2 包含聚合函数
 2 SQL> create view v2 as select deptno,sum(sal) as SUMSAL from test group by deptno;
 3 
 4 View created.
 5 
 6 SQL> select * from v2;
 7 
 8     DEPTNO     SUMSAL
 9 ---------- ----------
10     30     9400
11     20    10875
12     10     8750
13 
14 尝试更新复杂视图，出现报错
15 SQL> update v2 set deptno=20 where SUMSAL=9400;
16 update v2 set deptno=20 where SUMSAL=9400
17        *
18 ERROR at line 1:
19 ORA-01732: data manipulation operation not legal on this view
20 
21 创建复杂视图 V3 使用多表关联
22 SQL> create view v3 as select t. deptno,sum(sal) as sumsal,d.loc from test t,dept d where t.deptno=d.deptno group by t.deptno,d.loc;
23 
24 View created.
25 
26 SQL> select * from v3;
27 
28     DEPTNO     SUMSAL LOC
29 ---------- ---------- --------------------------
30     20    10875 DALLAS
31     30     9400 CHICAGO
32     10     8750 NEW YORK
33 
34 尝试更新复杂视图，出现报错
35 SQL> update v3 set loc='Shanghai' where deptno=20;
36 update v3 set loc='Shanghai' where deptno=20
37        *
38 ERROR at line 1:
39 ORA-01732: data manipulation operation not legal on this view

4. 特殊语句

　　1. FORCE：在没有基表的前提下，创建视图。用法：SQL> create FORCE VIEW v4 as select * from xxx;

　　2. WITH CHECK OPTION：对视图WHERE子句进行约束，保证结果集稳定。用法：SQL> create view v5 as select * from emp where deptno=10 WITH CHECK OPTION; ---不能插入非10号部门的数据，不能更改视图中10号部门的数据，但是可以删除数据

　　3. WITH READ ONLY：禁止对视图进行DML操作。

5. 实验：创建一个简单视图，授权视图给HR用户，查看HR是否有权限查看基表，授权基表给HR，查看HR是否有权限查看视图

 1 创建简单视图 V1
 2 SQL> create view v1 as select empno,ename from emp where deptno=10;
 3 
 4 View created.
 5 
 6 授权视图SELECT权限给HR
 7 SQL> grant select on v1 to hr;
 8 
 9 Grant succeeded.
10 
11 SQL> conn hr/hr@erp
12 Connected.
13 
14 HR可以查看视图信息
15 SQL> select * from scott.v1;
16 
17      EMPNO ENAME
18 ---------- --------------------
19       7782 CLARK
20       7839 KING
21       7934 MILLER
22 
23 HR不能查看基表信息
24 SQL> select * from scott.emp;
25 select * from scott.emp
26                     *
27 ERROR at line 1:
28 ORA-00942: table or view does not exist
29 
30 SQL> conn scott/tiger@erp
31 Connected.
32 SQL> revoke select on v1 from hr;
33 
34 Revoke succeeded.
35 
36 授权基表SELECT权限给HR
37 SQL> grant select on emp to hr;
38 
39 Grant succeeded.
40 
41 SQL> conn hr/hr@erp
42 Connected.
43 
44 HR可以查看基表信息
45 SQL> select * from scott.emp where deptno=10;
46 
47      EMPNO ENAME        JOB              MGR HIREDATE             SAL       COMM     DEPTNO
48 ---------- -------------------- ------------------ ---------- ------------------- ---------- ---------- ----------
49       7782 CLARK        MANAGER          7839 1981-06-09 00:00:00    2450            10
50       7839 KING         PRESIDENT              1981-11-17 00:00:00    5000            10
51       7934 MILLER        CLERK             7782 1982-01-23 00:00:00    1300            10
52 
53 HR不能查看视图信息
54 SQL> select * from scott.v1;
55 select * from scott.v1
56                     *
57 ERROR at line 1:
58 ORA-00942: table or view does not exist