一、视图和表
1、什么是视图
1)视图是一种数据库对象,是从一个或多个数据表或视图中导出来的虚表,视图所对应的数据并不真正存储在视图中,而是存储在所引用的表中,视图的结构和数据是对数据表进行查询的结果。
2)根据创建视图时所给定的条件,视图可以是一个数据表的一部分,也可以是多个基表的联合,它存储了查询语句的定义,以便在引用视图时使用。
2、视图和表的区别
表中保存的是实际数据,而视图中保存的是从表中取出数据所使用的select语句,视图本身不存储数据。
视图是保存select结果的临时表,视图不会保存数据。
3、视图的优点
1)视图无需保存数据,因此可以节省存储设备的容量。
2)可以将频繁使用的复杂查询保存为视图,避免重复书写。
3)限制某个视图只能访问基表中的部分列或部分行,有一定的安全性。
4)可以从多张基表中按一定的业务逻辑抽出用户需要的部分,形成虚拟表。
二、创建视图
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询语句
[WITH READ ONLY CONSTRAINT]
语法解析:
1)OR REPLACE:如果视图已经存在,则替换旧视图。
2)FORCE:即使基表不存在,也可以创建该视图,但是该视图不能正常使用,当基表创建成功后,视图才能正常使用。
3)NOFORCE:如果基表不存在,无法创建视图,该项是默认选项。
4)WITH READ ONLY:默认可以通过视图对基表执行增删改操作,但是有很多在基表上的限制(比如:基表中某列不能为空,但是该列没有出现在视图中,则不能通过视图执行insert操作),WITH READ ONLY说明视图是只读视图,不能通过该视图进行增删改操作。
1、给用户授权
sys用户给Scott用户授权,若没有授权则不能创建视图。
2、创建简单视图
1) 使用视图
对视图可以像表一样查询。在from子句中使用视图的查询,通常有如下步骤:
a、 首先执行定义视图的select语句
b、 根据得到的结果,再执行使用视图的select语句
即使用视图查询需要执行2条或2条以上的select语句。
2) 视图的限制
a、定义视图时不能使用orderby子句
因为视图和表一样,数据行是没有顺序的。
b、 对视图进行更新,如果定义视图的select语句能够满足某些条件,那么这个视图可以被更新,具有代表性的条件如下:
1> select语句中未使用distinct
2> from子句中只有一张表
3> 未使用group by 子句
4> 未使用having子句
也就是通过汇总得到的视图无法进行更新
3) 视图的DML更新
查询结果:
基表也发生了改变:
撤销操作,恢复测试表emp
4) 只读视图
更新基表,只读视图也会发生改变。
三、删除视图
Drop view view_name 命令删除视图
a、删除视图的定义不影响基表的数据。
b、只有视图所有者和具备drop view权限的用户可以删除视图。
c、视图被删除后,基于被删除视图的其他视图或存储过程等都将无效。
d、视图被删除后,该视图的定义会从词典中被删除,并且在该视图上授予的权限也会被删除。
四、更改视图
对视图进行更改或重定义之前需要了解的问题:
a、 视图只是一个虚表,其中没有数据,所以更改视图只是改变数据字典中对该视图的定义信息,视图的所有基础对象不会受到影响。
b、 更改视图之后,依赖于该视图的所有视图和PL/SQL程序都将变为invalid失效状态。
c、 如果以前的视图中具有withcheck option选项,但是重定义时没有使用该选项,则以前的该选项会自动删除。
1)更改视图的定义
执行create or replace view语句。这种方法代替了先删除后创建的方法,会保留视图上的权限,但是与该视图相关的存储过程和视图会失效。
2)视图的重新编译
Alter view 视图名 compile;
当视图依赖的基表改变后,视图会失效,为了确保这种改变不影响视图和依赖于该视图的其他对象,应该使用alter语句重新编译该视图,从而在运行视图前发现重新编译的错误,视图被重新编译后,若错误,则依赖该对象的视图也会失效。若没有错误,视图会有效。
当访问基表改变了的视图时,oracle会自动重新编译这些视图。即对视图进行查询时,会自动编译视图。
![](https://i-blog.csdnimg.cn/blog_migrate/55b03acf6247707b988e33da03b0bf0f.png)
只有修改表的结构后才会影响视图的有效性,而修改表的数据不会影响视图的有效性。但是,并非针对所有基础表的修改后,通过编译视图都可以通过。比如修改基础表的列名无法自动编译通过。
改变基础表的结构,视图失效,重新编译,视图变有效:
重新改回去基表结构,视图失效,查询一次视图,自动编译,视图变有效:
五、查看视图
使用数据字典视图:
dba_views——DBA视图描述数据库中的所有视图
all_views——ALL视图描述用户“可访问的”视图
user_views——USER视图描述“用户拥有的”视图
dba_tab_columns——DBA视图描述数据库中的所有视图的列(或表的列)
all_tab_columns——ALL视图描述用户“可访问的”视图的列(或表的列)
user_tab_columns——USER视图描述“用户拥有的”视图的列(或表的列)
1)查询当前所有视图的信息
2)查看当前指定视图的列名信息
六、连接视图
视图分为简单视图和复杂视图。
简单视图只从单表里获取数据;复杂视图从多表里获取数据。
简单视图不包含函数和数据组;复杂视图包含函数和数据组。
简单视图可以实现DML操作;复杂视图不可以。
1)创建连接视图
指基于多个表所创建的视图,即定义视图的查询是一个连接查询。主要目的是为了简化连接查询。
示例: 查询部门编号为10和30的部门及雇员信息
2)连接视图上的DML操作
在视图上进行的所有DML操作,最终都会在基表上完成。
select 视图没有什么限制,但insert/delete/update有一些限制。
3)键值保存表
如果连接视图中的一个基表的键(主键、唯一键)在它的视图中仍然存在,并且基表的键任然是连接视图的键,即某列在基表中是主键|唯一键,在视图中仍然是主键|唯一键,则称这个基表为“键值保存表”。
一般地,由主外键关系的2个表组成的连接视图,外键表就是键值保存表,而主键表不是。
4)连接视图的更新
a、一般准则
1>任何DML操作,只能对视图中的键值保存表进行更新, 即,“不能通过连接视图修改多个基表”。
2>在DML操作中,只能使用连接视图定义过的列。
3>自连接视图的所有列都是可更新(增删改)的。
b、insert准则
1>在insert语句中不能使用非键值保存表中的列(包括连接列)。
2>执行insert操作的视图,至少应该包含键值保存表中所有设置了约束的列。
3>如果在定义连接视图时使用了WITHCHECK OPTION 选项,则不能针对连接视图执行insert操作。
c、update准则
1>键值保存表中的列是可以更新的。
2>如果在定义连接视图时使用了WITH CHECK OPTION 选项,则连接视图中的连接列(一般就是共有列)和基表中的其他共有列是不可更新的,连接列和共有列之外的其他列是可更新的。
d、delete准则
如果在定义连接视图时使用了WITH CHECK OPTION 选项,依然可以针对连接视图执行delete操作
5)可更新连接视图
如果创建连接视图的select查询不包含如下结构,并且遵守连接视图的更新准则,则这样的连接视图是可更新的:
一) 集合运算符(union,intersect,minus)
二) DISTINCT关键字
三) GROUP BY,ORDER BY,CONNECT BY或START WITH子句
四) 子查询
五) 分组函数
六) 需要更新的列不是由列表达式定义的
七) 基表中所有NOT NULL列均属于该视图
七、复杂视图
复杂视图定义:是指包含函数、表达式、或分组数据的视图。主要目的是为了简化查询。主要用于执行查询操作,并不用于执行DML操作。
注意:当视图的select查询中包含函数或表达式时,必须为其定义列别名。
示例:查询目前每个岗位的平均工资、工资总和、最高工资和最低工资。