[大数据]Oracle数据库管理（16）——视图

视图以经过定制的方式显示来自一个或多个表的数据。
视图可视为“虚拟表”或“存储的查询”。
创建视图所依据的表，称为“基表”。
视图中是不存放数据的，它只是一个定义，查询视图实际上是从基表中查询数据。

视图的优点：

A、提供了另外一种级别的表安全性
B、隐藏的数据的复杂性
C、简化的用户SQL命令
D、隔离基表结构的改变
E、通过重命名列，从另一个角度提供数据

一、创建视图

语法：

create [or replace] [force] view 视图名[(列1,列2,......)] as 查询 [with check option] [with read only];

force：即使基表不存在也可以创建视图。不加“force”若基表不存在则不创建视图并报错。
with check option：当对视图进行更新使视图中的列数量变少时，禁止该更新操作。
witrh read only：视图不可更新。

//先用system用户给scott用户赋予创建视图的权限
SQL> conn system/system@orcl;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as system@ORCL
SQL> grant create view to scott;
Grant succeeded
//切换到scott用户，当前用户下没有student2表，尝试创建student2的视图
SQL> conn scott/scott;
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
Connected as scott
SQL> select * from student2;
select * from student2
ORA-00942: table or view does not exist
//不加“force”直接创建视图，不能创建视图并报错
SQL> create view view1 as select * from student2;
create view view1 as select * from student2
ORA-00942: table or view does not exist
//可以看到没有创建视图
SQL> select * from user_views;
VIEW_NAME                      TEXT_LENGTH TEXT                                                                             TYPE_TEXT_LENGTH TYPE_TEXT                                                                        OID_TEXT_LENGTH OID_TEXT                                                                         VIEW_TYPE_OWNER                VIEW_TYPE                      SUPERVIEW_NAME                 EDITIONING_VIEW READ_ONLY
------------------------------ ----------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ---------

//添加“force”后可以创建视图，但创建的视图带有编译错误
SQL> create force view view1 as select * from student2;
Warning: View created with compilation errors
SQL> select * from user_views;
VIEW_NAME                      TEXT_LENGTH TEXT                                                                             TYPE_TEXT_LENGTH TYPE_TEXT                                                                        OID_TEXT_LENGTH OID_TEXT                                                                         VIEW_TYPE_OWNER                VIEW_TYPE                      SUPERVIEW_NAME                 EDITIONING_VIEW READ_ONLY
------------------------------ ----------- -------------------------------------------------------------------------------- ---------------- -------------------------------------------------------------------------------- --------------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ------------------------------ --------------- ---------
VIEW1                                   23 select * from student2                                                                                                                                                                                                                                                                                                                                                           N               N
//访问视图会报错
SQL> select * from view1;
select * from view1
ORA-04063: view "SCOTT.VIEW1" 有错误
//创建之前不存在的student2表，并插入数据
SQL> create table student2(id number,name varchar2(2));
Table created
SQL> insert into student2 values(1,'A');
1 row inserted
//现在可以访问view1
SQL> select * from view1;
        ID NAME
---------- ----
         1 A
//创建一个view2,查询条件为id=1
SQL> create view view2 as select * from student2 where id =1;
View created
SQL> select * from view2;
        ID NAME
---------- ----
         1 A
//对view2进行更新
SQL> update view2 set id=2 where id=1;
1 row updated
//基表也会同时更新
SQL> select * from student2;
        ID NAME
---------- ----
         2 A
//但是view2就变成空了，因为基表中id=1的列不存在
SQL> select * from view2;
        ID NAME
---------- ----

//创建一个带有“with check option”的view3
SQL> create view view3 as select * from student2 where id =2 with check option;
View created
SQL> select * from view3;
        ID NAME
---------- ----
         2 A
//此时，将wiew3中id更新为3的操作会被禁止
SQL> update view3 set id =3;
update view3 set id =3
ORA-01402: view WITH CHECK OPTION where-clause violation
//创建一个带有“ with read only”的view4
SQL> create view view4 as select * from student2 with read only;
View created
//view4不可更新
SQL> update view4 set name='qwer';
update view4 set name='qwer'
ORA-42399: cannot perform a DML operation on a read-only view
//可以直接在创建视图时排序
SQL> create view view5 as select * from student2 order by id desc;
View created
SQL> insert into student2 values(3,'AB');
1 row inserted
SQL> insert into student2 values(4,'BC');
1 row inserted
SQL> select * from view5;
        ID NAME
---------- ----
         4 BC
         3 AB
         2 A
//**SQLServer中创建视图时不能排序，想要排序只能像下面这样写**
//create view view5 as select * from student2;
//select * from view5 order by id desc;
//相等链接的第一种写法：
select table1.id1,table2.id2 from table1,table2 where table1.id1 = table2.id2;
//相等链接的第二种写法：
select table1.id1,table2.id2 from table1 inner join table2 on table1.id1 = table2.id2;
//左外链接的第一种写法：
select table1.id1,table2.id2 from table1 left outer join table2 on table1.id1 = table2.id2;
//左外链接的第二种写法：
select table1.id1,table2.id2 from table1,table2 where table1.id1 = table2.id2(+);
//右外链接的第一种写法：
select table1.id1,table2.id2 from table1 right outer join table2 on table1.id1 = table2.id2;
//右外链接的第二种写法：
select table1.id1,table2.id2 from table1,table2 where table1.id1(+) = table2.id2;
//全外链接只有一种写法：
select table1.id1,table2.id2 from table1 full outer join table2 on table1.id1 = table2.id2;

二、对视图进行增、删、改

对视图进行增、删、改有如下限制：
1、基表是单表；
2、如果修改违反了基表的约束条件，则无法更新视图；
3、如果视图包含链接操作符、DISTINCT关键字、集合操作符、聚合函数或group by字句，则无法更新视图；
4、如果视图包含伪列或表达式，则将无法更新视图。

//先看下已存在的表student2
SQL> select * from student2;
        ID NAME
---------- ----
         3 AB
         4 BC
         2 A
//创建一个新表，并插入值
SQL> create table address(sid number,addr varchar2(10));
Table created
SQL> insert into address values(2,'Beijing');
1 row inserted
SQL> insert into address values(3,'Tianjin');
1 row inserted
SQL> insert into address values(4,'Shanghai');
1 row inserted
//创建一个相等链接的视图
SQL> create view v_s_a as select s.id,s.name,a.sid,a.addr from student2 s,address a where s.id=a.sid;
View created
//这个视图可以查询
SQL> select * from v_s_a;
        ID NAME        SID ADDR
---------- ---- ---------- ----------
         2 A             2 Beijing
         3 AB            3 Tianjin
         4 BC            4 Shanghai
//尝试修改视图会报错：“无法修改与非键保存表对应的列”
SQL> update v_s_a set name='qa' where id =2;
update v_s_a set name='qa' where id =2
ORA-01779: cannot modify a column which maps to a non key-preserved table
//删除视图
SQL> drop view view1;
View dropped

1、键保存表

假设有t1、t2两张表，t1和t2都设置了主键，t1中的某列关联t2的主键，当t1、t2关联形成一个新的表时，新表继承了t1的主键。t1就叫键保留表，r2就叫非键保存表。
在视图中，键保存表中的所有列都可更新，非键保存表中的列不可更新。

//创建2个带有主键的表，并插入数据
SQL> create table student1(sid number primary key,sname varchar2(10),city number);
Table created
SQL> create table city(cid number primary key ,cname varchar2(20));
Table created
SQL> insert into student1 values(1,'Tom',1);
1 row inserted
SQL> insert into student1 values(2,'Jim',1);
1 row inserted
SQL> insert into student1 values(3,'Lily',2);
1 row inserted
SQL> insert into city values(1,'Beijing');
1 row inserted
SQL> insert into city values(2,'Shanghai');
1 row inserted
SQL> insert into city values(3,'Guangzhou');
1 row inserted
SQL> select * from student1;
       SID SNAME            CITY
---------- ---------- ----------
         1 Tom                 1
         2 Jim                 1
         3 Lily                2
SQL> select * from city;
       CID CNAME
---------- --------------------
         1 Beijing
         2 Shanghai
         3 Guangzhou
将两张表进行关联，此时产生的新表继承了student1的主键，student1表就叫做键保存表，city表就叫非键保存表
SQL> select s.sid,s.sname,city,c.cid,c.cname from student1 s inner join city c on s.city=c.cid;
       SID SNAME            CITY        CID CNAME
---------- ---------- ---------- ---------- --------------------
         1 Tom                 1          1 Beijing
         2 Jim                 1          1 Beijing
         3 Lily                2          2 Shanghai
//创建视图
SQL> create view v_s_c as select s.sid,s.sname,city,c.cid,c.cname from student1 s inner join city c on s.city=c.cid;
View created
SQL> select * from v_s_c;
       SID SNAME            CITY        CID CNAME
---------- ---------- ---------- ---------- --------------------
         1 Tom                 1          1 Beijing
         2 Jim                 1          1 Beijing
         3 Lily                2          2 Shanghai
//可以修改sid、sname列的值，但不能修改cid、cname
SQL> update v_s_c set sid =4 where sid =1;
1 row updated
SQL> select * from v_s_c;
       SID SNAME            CITY        CID CNAME
---------- ---------- ---------- ---------- --------------------
         4 Tom                 1          1 Beijing
         2 Jim                 1          1 Beijing
         3 Lily                2          2 Shanghai
SQL> update v_s_c set sname='Bob' where sid =2;
1 row updated
SQL> select * from v_s_c;
       SID SNAME            CITY        CID CNAME
---------- ---------- ---------- ---------- --------------------
         4 Tom                 1          1 Beijing
         2 Bob                 1          1 Beijing
         3 Lily                2          2 Shanghai
SQL> update v_s_c set cid=5 where cid =2;
update v_s_c set cid=5 where cid =2
ORA-01779: cannot modify a column which maps to a non key-preserved table
SQL> update v_s_c set cname='Shenzhen' where cid =2;
update v_s_c set cname='Shenzhen' where cid =2
ORA-01779: cannot modify a column which maps to a non key-preserved table