葡京网投哪个正规 > 新葡亰-数据 > 视图的概述和基本操作【葡京网投哪个正规】,SQLServer之视图篇

原标题:视图的概述和基本操作【葡京网投哪个正规】,SQLServer之视图篇

浏览次数:107 时间:2019-11-30

1 视图介绍

         视图是从一个或者几个基本表(或视图)导出的表。它与基本表不同,是一个虚表。数据库中只存放视图的定义,而不存在视图对应的数据,这些数据仍然存放在原来的基本表中。所以一旦基本表中的数据发生变化,从视图中查询的数据也就随之改变了。从这个意义上讲,视图就像一个窗口,透过它可以看到数据库中自己感兴趣的数据及其变化。

1.1 视图的概述

      视图是从一个或者多个表导出的,它的行为与表非常相似,但视图是一个虚拟表,在视图中可以使用SELECT语句查询数据,以及使用insert、update和delete语句修改记录,对于视图的操作最终转化为对基本数据表的操作。视图不仅可以方便操作,而且可以保障数据库系统的安全性。

 视图一经定义便存储在数据库中,与其相对应的数据并没有像表数据那样在数据库中在存储一份,通过视图看到的数据只是存放在基本表中的数据。可以对其进行增删该查,通过视图对数据修改,基本表数据也对应变化,反之亦然。

 数据库中的视图是一个虚拟表。同真实的表一样,视图包含一系列带有名称的列和行数据,行和列数据用来自由定义视图和查询所引用的表,并且在引用视图时动态产生。本篇将通过一些实例来介绍视图的概念,视图的作用,创建视图,查看视图,修改视图,更新和删除视图等SQL Server的数据库知识。

目录:一、视图的定义;

前言

1.2 使用视图的目的与好处

1.聚焦特定数据:使用户只能看到和操作与他们有关的数据,提高了数据的安全性。
2.简化数据操作:使用户不必写复杂的查询语句就可对数据进行操作。
3.定制用户数据:使不同水平的用户能以不同的方式看到不同的数据。
4.合并分离数据:视图可以从水平和垂直方向上分割数据,但原数据库的结构保持不变。

一:视图的概述

 视图是从一个或者多个表导出的,它的行为与表非常相似,但视图是一个虚拟表,在视图中可以使用SELECT语句查询数据,以及使用insert、update和delete语句修改记录,对于视图的操作最终转化为对基本数据表的操作。视图不仅可以方便操作,而且可以保障数据库系统的安全性。

 视图一经定义便存储在数据库中,与其相对应的数据并没有像表数据那样在数据库中在存储一份,通过视图看到的数据只是存放在基本表中的数据。可以对其进行增删该查,通过视图对数据修改,基本表数据也对应变化,反之亦然。

二、视图的作用;

视图是数据库系统中一种非常有用的数据库对象。MySQL 5.0 之后的版本添加了对视图的支持。

2 创建视图

语法:

 [ with check option ] --强制所有通过是同修改的数据,都要满足select语句中指定的条件
select查询语句
as
[ with encryption ] --用于加密视图的定义,用户只能查看不能修改。
[ (列名表) ]
create view 视图

先创建一个学生表

use marvel_db;
--创建一个学生表
create table stuTable(
    id int identity(1,1)primary key,--id 主键,自增
    name varchar(20),
    gender char(2),
    age int,
)
--往表中插入数据
insert into stuTable (name,gender,age)
values
    ('刘邦','男',23),
    ('项羽','男',22),
    ('韩信','男',21); 
insert into stuTable(name,gender,age) values('萧何','男',24)  

创建视图

--创建视图
if (exists (select * from sys.objects where name = 'stu_view'))
    drop view stu_view
go
--stu_view()不实用参数,默认为基础表中的列名称
--注意 create view 必须是批处理里面的语句
create view stu_view 
as 
select name,age from stuTable where age>20;
go
--执行视图
select * from stu_view;  

查询结果:

葡京网投哪个正规 1

二:视图的分类

 SQL Server的视图可以分为3类,分别是:标准视图,索引视图,分区视图

2.1.标准视图

 标准视图组合了一个或多个表中的数据,可以获得使用视图中的大多数好处,包括重点将放在特定的数据上及简化数据操作。

2.2.索引视图

 索引视图是被具体化了的视图,即它已经经过计算并存储。可以为视图创建索引,对视图创建一个唯一的聚集索引。索引视图可以显著提高某些类型查询的性能,索引视图尤其适于聚合许多行的查询,但它们不太适于经常更新的基本数据集。

2.3.分区视图

 分区视图在一台多多台服务器间水平连接一组成员表的分区数据,这样,数据看上去如同来自一个表。连接本地同一个SQL Server实例中的成员表的视图是一个本地区分视图。

三、创建视图;

认识视图

3 修改视图

 

go
alter view stu_view 
as
select * from stuTable where age>22;
go
select * from stu_view  

显示结果:

葡京网投哪个正规 2

三:视图的优点和作用

与直接从表中读取数据相比,视图具有一下优点

3.1.简单化

 看到的就是需要的,视图不仅可以简化用户对数据的理解,也可以简化对它们的操作,那些经常使用的查询可以被定义为视图,从而使得用户不必为以后的每次操作指定全部的条件。

3.2.安全性

 视图可以作为一种安全机制。通过视图用户只能查看和修改他们所能看到的数据。其它或表既不可见也不可以访问。如果某一用户想要访问视图的,必须授予其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。

3.3.逻辑数据独立性

 视图可以帮助用户屏蔽真实表结构变化带来的影响。

  1.权限

视图是一个虚拟表,其内容由查询定义。同真实表一样,视图包含一系列带有名称的列和行数据,但视图并不是数据库真实存储的数据表。

4 删除视图

 

go
--语法
drop view view_name1,view_name2,......,view_nameN;
--该语句可以同时删除多个视图,只要在删除各视图名称之间用逗号分隔即可。

例如:删除视图 stu_view

--语法
drop view stu_view;
--该语句可以同时删除多个视图,只要在删除各视图名称之间用逗号分隔即可。  

5 通过视图管理表中的数据

(1).通过视图向基本表中插入数据

注意:

1.可通过视图向基表中插入数据,但插入的数据实际上存放在基表中,而不是存放在视图中。

2.如果视图引用了多个表,使用insert语句插入的列必须属于同一个表。

3.若创建视图时定义了“with check option”选项,则使用视图向基表中插入数据时,必须保证插入后的数据满足定义视图的限制条件。

--(1).通过视图向基本表中插入数据
go
create view stu_insert_view(编号,姓名,性别,年龄)
as
select id,name,gender,age from stuTable;
go
select * from stuTable;
---插入一条数据
insert into stu_insert_view values('孙权','男',34);
----查看插入记录之后表中的内容。
select * from stuTable;  

显示结果:

葡京网投哪个正规 3

(2).通过视图修改基本表的数据

--查看修改之前的数据
select * from stuTable;  

显示结果:

葡京网投哪个正规 4

--修改数据
update stu_insert_view set 年龄=30 where 姓名='刘邦';
--查看修改后的数据
select * from stuTable;  

结果显示:

葡京网投哪个正规 5

(3).通过视图删除基本表的数据

注意:

1.要删除的数据必须包含在视图的结果集中。

2.如果视图引用了多个表时,无法用delete命令删除数据。

语法

--语法
delete stu_insert_view where condition;  

删除之前:

葡京网投哪个正规 6

删除:

--例子
delete stu_insert_view where 姓名 ='刘邦';
select * from stu_insert_view;
select * from stuTable;  

显示结果:

葡京网投哪个正规 7

四:视图的基本操作和语法

4.1.创建视图

--语法
CREATE VIEW view_name
 AS
    SELECT column_name(s) FROM table_name
WHERE condition

测试数据准备:

use sample_db;
create table studentTable(
    id int identity(1,1)primary key,
    name varchar(20),
    gender char(2),
    age int,
)
insert into studentTable (name,gender,age)
values
    ('刘备','男',28),
    ('张飞','男',24),
    ('关羽','男',26); 

--创建视图
if (exists (select * from sys.objects where name = 'student_view'))
    drop view student_view
go
--student_view()不实用参数,默认为基础表中的列名称
create view student_view 
as
select name,age from studentTable where age>24;
--执行视图
select * from student_view;

查看视图的信息

  • 使用sp_help存储过程查看视图的定义信息
  • 使用sp_helptext系统存储过程使用来显示规则,默认值,未加密的存储过程,用户定义函数,触发器或视图的文本,语法

    exec sp_help 'student_view'; exec sp_helptext 'student_view';

创建加密视图:

--加密视图
if (exists (select * from sys.objects where name = 'student_encryption'))
    drop view student_encryption
go
create view student_encryption
with encryption --加密
as
    select id, name, age from studentTable
go
--view_definition is null
--查看加密视图
select * from information_schema.views 
where table_name like 'student_encryption';

 葡京网投哪个正规 8

  从执行结果可看出view_definition字段为 NULL

4.2.使用视图修改基本表数据

(1).通过视图向基本表中插入数据

--(1).通过视图向基本表中插入数据
create view stu_insert_view(编号,姓名,性别,年龄)
as
select id,name,gender,age from studentTable;
go
select * from studentTable;
---插入一条数据
insert into stu_insert_view values('曹操','男',40);
----查看插入记录之后表中的内容。
select * from studentTable;   

(2).通过视图修改基本表的数据

--(2).通过视图修改基本表的数据
--查看修改之前的数据
select * from studentTable;
--修改数据
update student_view set age=30
where name='刘备';
--查看修改后的数据
select * from studentTable;

(3).通过视图删除基本表的数据

--语法
delete view_name where condition;
--例子
delete student_view where name ='张飞';
select * from student_view;
select * from studentTable;

 4.3.修该视图

--修改视图
alter view student_view 
as
select * from studentTable where age>26;

4.4.删除视图

 

--语法
drop view view_name1,view_name2,......,view_nameN;
--该语句可以同时删除多个视图,只要在删除各视图名称之间用逗号分隔即可。
drop view studentTable;

 

  2.语法

视图是从一个、多个表或者视图中导出的表,包含一系列带有名称的数据列和若干条数据行。

6总结

1.使用场景:

1.经常用到的查询,或较复杂的联合查询应当创立视图,这是会优化性能的
2.涉及到权限管理方面,比如某表中的部分字段含有机密信息,不应当让低权限的用户访问到的情况,这时候给这些用户提供一个适合他们权限的视图,供他们阅读自己的数据就行了。
2.视图与表的区别:

1.视图是已经编译好的SQL语句,是基于SQL语句的结果集的可视化表,而表不是;
2.视图(除过索引视图)没有实际的物理记录,而基本表有;
3.表示内容,视图是窗口;
4.表占物理空间,而视图不占物理空间,视图只是逻辑概念的存在;
5.视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全角度说,视图可以防止用户接触数据表,从而不知表结构;
6.表属于全局模式的表,是实表;视图数据局部模式的表,是虚表;
7.视图的建立和删除只影响视图本身,不影响对应的基本表。

 

转载自:

五:视图和表的区别

  1. 视图是已经编译好的SQL语句,是基于SQL语句的结果集的可视化表,而表不是;
  2. 视图(除过索引视图)没有实际的物理记录,而基本表有;
  3. 表示内容,视图是窗口;
  4. 表占物理空间,而视图不占物理空间,视图只是逻辑概念的存在;
  5. 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些SQL语句的集合。从安全角度说,视图可以防止用户接触数据表,从而不知表结构;
  6. 表属于全局模式的表,是实表;视图数据局部模式的表,是虚表;
  7. 视图的建立和删除只影响视图本身,不影响对应的基本表。

      3.1  创建简单视图

视图并不同于数据表,它们的区别在于以下几点:

    3.2  创建连接视图

视图不是数据库中真实的表,而是一张虚拟表,其结构和数据是建立在对数据中真实表的查询基础上的。 存储在数据库中的查询操作 SQL 语句定义了视图的内容,列数据和行数据来自于视图查询所引用的实际表,引用视图时动态生成这些数据。 视图没有实际的物理记录,不是以数据集的形式存储在数据库中的,它所对应的数据实际上是存储在视图所引用的真实表中的。 视图是数据的窗口,而表是内容。表是实际数据的存放单位,而视图只是以不同的显示方式展示数据,其数据来源还是实际表。 视图是查看数据表的一种方法,可以查询数据表中某些字段构成的数据,只是一些 SQL 语句的集合。从安全的角度来看,视图的数据安全性更高,使用视图的用户不接触数据表,不知道表结构。 视图的建立和删除只影响视图本身,不影响对应的基本表。

     3.2.1 连接视图定义

视图与表在本质上虽然不相同,但视图经过定义以后,结构形式和表一样,可以进行查询、修改、更新和删除等操作。

     3.2.2 创建连接视图

1、准备工作

     3.2.3 连接视图上的DML操作

在MySQL数据库中创建两张表balance并插入数据。

     3.2.4 键值保存表

create table customer primary key, name char not null, role char not null, phone char not null, sex char not null, address charENGINE=InnoDB DEFAULT CHARSET=utf8;#外键为customerIdcreate table balance primary key, customerId int not null, balance DECIMAL, foreign key references customerENGINE=InnoDB DEFAULT CHARSET=utf8;

     3.2.5 连接视图的更新准则

向客户表和余额表中各插入3条数据。

*     3.2.6 *可更新连接视图**

insert into customer values(0001,"xiaoming",'vip1','12566666','male','江宁区888号');insert into customer values(0002,"xiaohong",'vip10','15209336760','male','建邺区888号');insert into customer values(0003,"xiaocui",'vip11','15309336760','female','新街口888号');insert into balance values;insert into balance values;insert into balance values;

  3.3 创建复杂视图

2、视图简介

  3.4 强制创建视图

视图可以简单理解成虚拟表,它和数据库中真实存在数据表不同,视图中的数据是基于真实表查询得到的。视图和真实表一样具备相似的结构。真实表的更新,查询,删除等操作,视图也支持。那么为什么需要视图呢?

四 更改视图

a、提升真实表的安全性:视图是虚拟的,可以只授予用户视图的权限而不授予真实表的权限,起到保护真实表的作用。

     4.1 更改视图的定义

b、定制化展示数据:基于同样的实际表,可以通过不同的视图来向不同需求的用户定制化展示数据。

  4.2 视图的重新编译

c、简化数据操作:适用于查询语句比较复杂使用频率较高的场景,可以通过视图来实现。

五、删除视图

需要说明一点的是:视图相关的操作需要用户具备相应的权限。以下操作使用root用户,默认用户具备操作权限。

六、查看视图

create view as ;

七、 在视图上执行DML操作的步骤和原理

修改视图名称可以先删除,再用相同的语句创建。

  7.1 查询视图“可更新”(包括“增删改”)的列

#更新视图结构alter view  as ;#更新视图数据相当于更新实际表,不适用基于多表创建的视图update ....

*  *7.2 视图DML执行遵循的原则:

注意:部分视图的数据是无法更新,也就是无法使用update,insert等语句更新,比如:

八、视图容易出现的问题

a、select语句包含多个表

九、附加“视图的种类与区别”

b、视图中包含having子句

     9.1 关系视图

c、试图中包含distinct关键字

  9.2 内嵌视图

drop view

  9.3 对象视图

3、视图的操作

  9.4 物化视图

mysql> create view bal_view -> as -> select * from balance;Query OK, 0 rows affected 

 

创建完成后,查看bal_view的结构和记录。可以发现通过视图查询到数据和通过真实表查询得到的结果完全一样。

 

#查询bal_view的结构mysql> desc bal_view;+------------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+---------------+------+-----+---------+-------+| id | int | NO | | NULL | || customerId | int | NO | | NULL | || balance | decimal | YES | | NULL | |+------------+---------------+------+-----+---------+-------+3 rows in set #查询bal_view中的记录mysql> select * from bal_view;+----+------------+----------+| id | customerId | balance |+----+------------+----------+| 1 | 1 | 900.55 || 2 | 2 | 900.55 || 3 | 3 | 10000.00 |+----+------------+----------+3 rows in set 

一. 视图的定义

通过创建视图的语句不难得出结论:当真实表中的数据发生改变时,视图中的数据也会随之改变。那么当视图中的数据发生改变时,真实表中的数据会变化吗?来实验一下,修改id=1的客户balance为2000。

视图(view),也称虚表, 不占用物理空间,这个也是相对概念,因为视图本身的定义语句还是要存储在数据字典里的。视图只有逻辑定义。每次使用的时候,只是重新执行SQL。

mysql> update bal_view set balance=2000 where id=1;Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0

  视图是从一个或多个实际表中获得的,这些表的数据存放在数据库中。那些用于产生视图的表叫做该视图的基表。一个视图也可以从另一个视图中产生。

来看一下真实表balance中的数据。

  视图的定义存在数据库中,与此定义相关的数据并没有再存一份于数据库中。通过视图看到的数据存放在基表中。

mysql> select * from bal_view where id=1;+----+------------+---------+| id | customerId | balance |+----+------------+---------+| 1 | 1 | 2000.00 |+----+------------+---------+1 row in set 

  视图看上去非常象数据库的物理表,对它的操作同任何其它的表一样。当通过视图修改数据时,实际上是在改变基表中的数据;相反地,基表数据的改变也会自动反映在由基表产生的视图中。由于逻辑上的原因,有些Oracle视图可以修改对应的基表,有些则不能(仅仅能查询)。

结论:视图表中的数据发生变化时,真实表中的数据也会随之改变。

  还有一种视图:物化视图(MATERIALIZED VIEW ),也称实体化视图,快照 (8i 以前的说法) ,它是含有数据的,占用存储空间。

基于多表创建视图

  注意: 查询视图没有什么限制, 插入/更新/删除视图的操作会受到一定的限制; 所有针对视图的操作都会影响到视图的基表; 为了防止用户通过视图间接修改基表的数据, 可以将视图创建为只读视图(带上with read only选项)

创建视图cus_bal,共两个字段客户名称和余额。

 

mysql> create view cus_bal ->  -> as -> select customer.name,balance.balance from customer ,balance -> where customer.id=balance.customerId;Query OK, 0 rows affected #查看cus_bal中的数据mysql> select * from cus_bal;+----------+----------+| cname | bal |+----------+----------+| xiaoming | 2000.00 || xiaohong | 900.55 || xiaocui | 10000.00 |+----------+----------+3 rows in set 

二. 视图的作用

修改视图

1)提供各种数据表现形式, 可以使用各种不同的方式将基表的数据展现在用户面前, 以便符合用户的使用习惯(主要手段: 使用别名);

将cus_bal视图中的cname改成cusname。

2)隐藏数据的逻辑复杂性并简化查询语句, 多表查询语句一般是比较复杂的, 而且用户需要了解表之间的关系, 否则容易写错; 如果基于这样的查询语句创建一个视图, 用户就可以直接对这个视图进行"简单查询"而获得结果. 这样就隐藏了数据的复杂性并简化了查询语句.这也是oracle提供各种"数据字典视图"的原因之一,all_constraints就是一个含有2个子查询并连接了9个表的视图(在catalog.sql中定义);

mysql> alter view cus_bal ->  -> as -> select customer.name,balance.balance from customer ,balance -> where customer.id=balance.customerId;Query OK, 0 rows affected #查看修改后视图结构。mysql> desc cus_bal;+---------+---------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+---------+---------------+------+-----+---------+-------+| cusname | char | NO | | NULL | || bal | decimal | YES | | NULL | |+---------+---------------+------+-----+---------+-------+2 rows in set 

3)执行某些必须使用视图的查询. 某些查询必须借助视图的帮助才能完成. 比如, 有些查询需要连接一个分组统计后的表和另一表, 这时就可以先基于分组统计的结果创建一个视图, 然后在查询中连接这个视图和另一个表就可以了;

修改基于多表创建的视图

4)提供某些安全性保证. 视图提供了一种可以控制的方式, 即可以让不同的用户看见不同的列, 而不允许访问那些敏感的列, 这样就可以保证敏感数据不被用户看见;

mysql> insert into cus_bal values ;ERROR 1393 : Can not modify more than one base table through a join view 'rms.cus_bal'

*      5)简化用户权限的管理. 可以将视图的权限授予用户, 而不必将基表中某些列的权限授予用户, 这样就简化了用户权限的定义。*

删除视图

 

删除视图cus_bal

三 创建视图

drop view cus_bal;mysql> drop view cus_bal;Query OK, 0 rows affected 

1权限: 要在当前方案中创建视图, 用户必须具有create view系统权限; 要在其他方案中创建视图, 用户必须具有create any view系统权限. 视图的功能取决于视图拥有者的权限.

总结

*2 语法:*

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对脚本之家的支持。

create [ or replace ] [ force ] view [schema.]view_name
                      [ (column1,column2,...) ]
                      as 
                      select ...
                      [ with check option ]                      [ constraint constraint_name ]
                      [ with read only ]; 

 

其中:

       1. or replace:  如果存在同名的视图, 则使用新视图"替代"已有的视图
   2. force:  "强制"创建视图,不考虑基表是否存在,也不考虑是否具有使用基表的权限
   3. column1,column2,...: 视图的列名, 列名的个数必须与select查询中列的个数相同; 如果select查询包含函数或表达式, 则必须为其定义列名.此时, 既可以用column1, column2指定列名, 也可以在select查询中指定列名.
   4. with check option:  指定对视图执行的dml操作必须满足“视图子查询”的条件即,对通过视图进行的增删改操作进行"检查",要求增删改操作的数据, 必须是select查询所能查询到的数据,否则不允许操作并返回错误提示. 默认情况下, 在增删改之前"并不会检查"这些行是否能被select查询检索到. 
   5. with read only: 创建的视图只能用于查询数据, 而不能用于更改数据.

 

3.1 创建简单视图

简单视图定义:是指基于单个表建立的,不包含任何函数、表达式和分组数据的视图。

 SQL> conn /as sysdba

Connected.

SQL> grant create view to scott;

SQL> conn scott/tiger

Connected.

SQL> create view vw_emp as select empno,ename,job,hiredate,deptno from emp;

SQL> desc vw_emp

 Name                                      Null?    Type

 ----------------------------------------- -------- ----------------------------

 EMPNO                                     NOT NULL NUMBER(4)

 ENAME                                              VARCHAR2(10)

 JOB                                                VARCHAR2(9)

 HIREDATE                                           DATE

 DEPTNO                                             NUMBER(2)


SQL> select * from vw_emp where deptno=10;


     EMPNO ENAME      JOB       HIREDATE      DEPTNO

----------  ----------     ---------    ---------      

      7782 CLARK      MANAGER    09-JUN-81          10

      7839 KING       PRESIDENT   17-NOV-81         10

      7934 MILLER     CLERK       23-JAN-82          10

 

对简单的视图进行DAM操作:

 SQL> select empno,ename,job,hiredate,deptno from emp where deptno=10;


     EMPNO ENAME      JOB       HIREDATE      DEPTNO

---------- ---------- --------- --------- ----------

      7782 CLARK      MANAGER   09-JUN-81         10

      7934 MILLER     CLERK     23-JAN-82         10

         1 a          aa        05-JUN-88         10


SQL> select object_name,object_type from user_objects;

OBJECT_NAME                    OBJECT_TYPE

------------------------------ -------------------

VW_EMP                         VIEW

SALGRADE                       TABLE

BONUS                          TABLE

PK_EMP                         INDEX

EMP                            TABLE

DEPT                           TABLE

PK_DEPT                        INDEX 

 

创建只读视图:

 SQL> create view vw_emp_readonly as select empno,ename,job,hiredate,deptno from emp with read only;


SQL> select * from vw_emp_readonly where deptno=10;


     EMPNO ENAME      JOB       HIREDATE      DEPTNO

---------- ---------- --------- --------- ----------

      7782 CLARK      MANAGER   09-JUN-81         10

      7934 MILLER     CLERK     23-JAN-82         10

         1 a          aa        05-JUN-88         10

 

只能查询,无法进行更改:

 

 SQL> delete vw_emp_readonly where empno=1;

delete vw_emp_readonly where empno=1

       *

ERROR at line 1:

ORA-42399: cannot perform a DML operation on a read-only view

 

更新基表,只读视图也发生改变:

 SQL> update emp set empno=2 where ename='a';

SQL> select * from vw_emp_readonly where deptno=10;


     EMPNO ENAME      JOB       HIREDATE      DEPTNO

---------- ---------- --------- --------- ----------

      7782 CLARK      MANAGER   09-JUN-81         10

      7934 MILLER     CLERK     23-JAN-82         10

         2 a          aa        05-JUN-88         10

 

创建检查约束视图with check option:

 Sql>create view vw_emp_check as select empno,ename,job,hiredate,deptno from emp where deptno=10 with check option;


SQL> insert into vw_emp_check values('2','c','cc','02-JAN-55',10);


SQL> insert into vw_emp_check values('3','d','dd','02-JAN-65',20);

insert into vw_emp_check values('3','d','dd','02-JAN-65',20)

            *

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation 

注意:

创建检查视图:对通过视图进行的增删改操作进行检查,要求增删改操作的数据必须是select查询所能查询到的数据

20号部门不在查询范围内,违反检查约束,所以无法插入;

 

 SQL> delete vw_emp_check where empno=2;


1 row deleted.

--------所删除的数据在查询范围内,不违反检查约束  

 

 

3.2 连接视图

3.2.1 连接视图定义:是指基于多个表所创建的视图,即,定义视图的查询是一个连接查询。 主要目的是为了简化连接查询;

3.2.2 创建连接视图

示例1: 查询部门编号为10和30的部门及雇员信息

 SQL> create view vw_dept_emp

  2  as select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal from dept a,emp b where a.deptno=b.deptno and a.deptno in(10,30);


View created.


SQL> select * from vw_dept_emp;


    DEPTNO DNAME          LOC             EMPNO ENAME             SAL

---------- -------------- ------------- ---------- ---------- ----------

        30 SALES          CHICAGO             7499 ALLEN            1600

        30 SALES          CHICAGO             7521 WARD             1250

        30 SALES          CHICAGO             7654 MARTIN           1250

        30 SALES          CHICAGO             7698 BLAKE            2850

        10 ACCOUNTING    NEW YORK           7782 CLARK            2450

        30 SALES          CHICAGO             7844 TURNER           1500

        30 SALES          CHICAGO             7900 JAMES             950

        10 ACCOUNTING    NEW YORK           7934 MILLER           1300

        10 ACCOUNTING     NEW YORK               1 a


9 rows selected. 

 

3.2.3 连接视图上的DML操作

 SQL> insert into vw_dept_emp values(10,'aaa','aaaa',22,'a',5000);

insert into vw_dept_emp values(10,'aaa','aaaa',22,'a',5000)

*

ERROR at line 1:

ORA-01779: cannot modify a column which maps to a non key-preserved table 

注意:在视图上进行的所有DML操作,最终都会在基表上完成;
select 视图没有什么限制,但insert/delete/update有一些限制;

 

 

*3.2.4**键值保存表***

如果连接视图中的一个“基表的键”(主键、唯一键)在它的视图中仍然存在,并且“基表的键”仍然是“连接视图中的键”(主键、唯一键);即,某列在基表中是主键|唯一键,在视图中仍然是主键|唯一键,则称这个基表为“键值保存表”。   
  一般地,由主外键关系的2个表组成的连接视图,外键表就是键值保存表,而主键表不是。  

3.2.5 连接视图的更新准则

 一:一般准则——(讲)
     1. 任何DML操作,只能对视图中的键值保存表进行更新, 即,“不能通过连接视图修    改多个基表”;
     2. 在DML操作中,“只能使用连接视图定义过的列”;
     3. 自连接视图”的所有列都是可更新(增删改)的
 二:insert准则
     1. 在insert语句中不能使用“非键值保存表”中的列(包括“连接列”);
     2. 执行insert操作的视图,至少应该“包含”键值保存表中所有设置了约束的列;
     3. 如果在定义连接视图时使用了WITH CHECK OPTION 选项,则“不能”针对连接视 图执行insert操作 
 三:update准则
     1. 键值保存表中的列是可以更新的;
     2. 如果在定义连接视图时使用了WITH CHECK OPTION 选项,则连接视图中的连接列(一般就是“共有列”)和基表中的“其他共有列”是“不可”更新的,连接列和共有列之外的 其他列是“可以”更新的
 四:delete准则
     1. 如果在定义连接视图时使用了WITH CHECK OPTION 选项,依然“可以”针对连接视图执行delete操作 

3.2.6 可更新连接视图

  如果创建连接视图的select查询“不包含”如下结构,并且遵守连接视图的“更新准则”,则这样的连接视图是“可更新”的:

  6.1 集合运算符(union,intersect,minus)
  6.2:DISTINCT关键字
  6.3:GROUP BY,ORDER BY,CONNECT BY或START WITH子句
  6.4:子查询
  6.5:分组函数
  6.6:需要更新的列不是由“列表达式”定义的
  6.7:基表中所有NOT NULL列均属于该视图 

 

 

3.3 创建复杂视图

复杂视图定义:是指包含函数、表达式、或分组数据的视图。主要目的是为了简化查询。主要用于执行查询操作,并不用于执行DML操作。
  注意:当视图的select查询中包含函数或表达式时,必须为其定义列别名。

 示例1:查询目前每个岗位的平均工资、工资总和、最高工资和最低工资。

 

 SQL> create view vw_emp_job_sal(job,avgsal,sumsal,maxsal,minsal)

  2  as select job,avg(sal),sum(sal),max(sal),min(sal) from emp group by job;


View created.


SQL> select * from vw_emp_job_sal;


JOB           AVGSAL     SUMSAL     MAXSAL     MINSAL

--------- ---------- ---------- ---------- ----------

CLERK         1037.5       4150       1300        800

SALESMAN        1400       5600       1600       1250

aa

MANAGER   2758.33333       8275       2975       2450

ANALYST         3000       6000       3000       3000

 

 

3.4 强制创建视图

强制视图定义:正常情况下,如果基表不存在,创建视图就会失败。但是可以使用force选项强制创建视图(前提:创建视图的语句没有语法错误!),此时该视图处于失效状态。

 SQL> create force view vw_test_tab

  2  as select c1,c2 from test_tab; 

Warning: View created with compilation errors. 警告: 创建的视图带有编译错误。

 SQL> select * from vw_test_tab;

select * from vw_test_tab

              *

ERROR at line 1:

ORA-04063: view "SCOTT.VW_TEST_TAB" has errors


SQL> select object_name,status from user_objects where object_name='VW_TEST_TAB';


OBJECT_NAME                    STATUS

------------------------------ -------

VW_TEST_TAB                    INVALID ---------视图状态为不可用


SQL> create table test_tab(c1 number(9) primary key,c2 varchar2(20),c3 varchar2(30));


Table created.


SQL> select * from vw_test_tab;


no rows selected



SQL> select object_name,status from user_objects where object_name='VW_TEST_TAB';


OBJECT_NAME                    STATUS

------------------------------ -------

VW_TEST_TAB                    VALID  --------------视图状态为可用

 

四 更改视图

在对视图进行更改(或重定义)之前,需要考虑如下几个问题:
  1. 由于视图只是一个虚表,其中没有数据,所以更改视图只是改变数据字典中对该视图的
 定义信息,视图的所有基础对象都不会受到任何影响
  2. 更改视图之后,依赖于该视图的所有视图和PL/SQL程序都将变为INVALID(失效)状态
  3. 如果以前的视图中具有with check option选项,但是重定义时没有使用该选项,则以前                 

的此选项将自动删除。 

 

4.1***更改视图的定义***
          方法——执行create or replace view语句。这种方法代替了先删除(“权限也将随之删除”)后创建的方法,会保留视图上的权限,但与该视图相关的存储过程和视图会失效。

 

示例1:将视图改为改为只读:

 SQL> create or replace view vw_emp

  2  as

  3  select empno,ename,job,hiredate,deptno from emp with read only;


View created. 

 

 

4.2***视图的重新编译*    语法:alter view 视图名 compile;   作用:当视图依赖的基表改变后,视图会“失效”。为了确保这种改变“不影响”视图和依赖于该视图的其他对象,应该使用 alter view 语句“明确的重新编译”该视图,从而在运行视图前发现重新编译的错误。视图被重新编译后,若发现错误,则依赖该视图的对象也会失效;若没有错误,视图会变为“有效”。   权限:为了重新编译其他模式中的视图,必须拥有alter any table系统权限。   注意:**当访问基表改变后的视图时,oracle会“自动重新编译”这些视图。

 

示例1:

 SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';


LAST_DDL_ OBJECT_NAME                    STATUS

--------- ------------------------------ -------

23-AUG-14 VW_TEST_TAB                    VALID     -----视图的状态:有效 


SQL> alter table test_tab modify(c2 varchar2(30)); ——修改基表,c2列的长度


SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';


LAST_DDL_ OBJECT_NAME                    STATUS

--------- ------------------------------ -------

23-AUG-14 VW_TEST_TAB                    INVALID     ——视图的状态:失效 


SQL> alter view vw_test_tab compile;                 ---重新编译


SQL> select last_ddl_time,object_name,status from user_objects where object_name='VW_TEST_TAB';


LAST_DDL_ OBJECT_NAME                    STATUS

--------- ------------------------------ -------

23-AUG-14 VW_TEST_TAB                    VALID         ---视图有效

请读者思考一个问题:

    若上述代码修改的不是列长,而是表名,结果又会如何?
   <警告:更改的视图带有编译错误;视图状态:失效>

 

五 删除视图

   语法:DROP VIEW VIEW_NAME

  可以删除当前模式中的任何视图,删除视图的定义不影响基表中的数据。

   如果要删除其他模式中的视图,必须拥有DROP ANY VIEW系统权限;
   视图被删除后,该视图的定义会从词典中被删除,并且在该视图上授予的“权限”也将被删除。
   视图被删除后,其他引用该视图的视图及存储过程等都会失效。
       示例1:drop view vw_test_tab; 

六 查看视图

使用数据字典视图
1 dba_views——DBA视图描述数据库中的所有视图
2 all_views——ALL视图描述用户“可访问的”视图
3 user_views——USER视图描述“用户拥有的”视图 
4 dba_tab_columns——DBA视图描述数据库中的所有视图的列(或表的列)
5 all_tab_columns——ALL视图描述用户“可访问的”视图的列(或表的列)
6 user_tab_columns——USER视图描述“用户拥有的”视图的列(或表的列) 

示例1:查询当前方案中所有视图的信息

 SQL> select view_name,text from user_views;

VIEW_NAME          TEXT

------------------ -------------------------------------------------------

VW_DEPT_EMP        select a.deptno,a.dname,a.loc,b.empno,b.ename,b.sal fro

                     m dept a,emp b where a.de


VW_EMP              select empno,ename,job,hiredate,deptno from emp

VW_EMP_CHECK       select empno,ename,job,hiredate,deptno from emp where d

                     eptno=10 with check optio


VW_EMP_JOB_SAL     select job,avg(sal),sum(sal),max(sal),min(sal) from emp

                    group by job


VW_EMP_READONLY    select empno,ename,job,hiredate,deptno from emp with re

                      ad only


VW_TEST_TAB        select c1,c2 from test_tab


6 rows selected. 

示例2:查询当前方案中指定视图(或表)的列名信息

select * from user_tab_columns where table_name='VW_DEPT'; 

七 在视图上执行DML操作的步骤和原理

第一步:将针对视图的SQL语句与视图的定义语句(保存在数据字典中)“合并”成一条SQL语句

第二步:在内存结构的共享SQL区中“解析”(并优化)合并后的SQL语

葡京网投哪个正规,第三步:“执行”SQL语句

示例:假设视图v_emp的定义语句如下:

create view v_emp 
as
select empno,ename,loc from employees emp,departments dept
where

 emp.deptno=dept.deptno and dept.deptno=10; 

当用户执行如下查询语句时:

select ename from v_emp where empno=9876; 

oracle将把这条SQL语句与视图定义语句“合并”成如下查询语句:

select ename from employees emp,departments dept
where

 emp.deptno=dept.deptno and dept.deptno=10 and empno=9876; 

然后,解析(并优化)合并后的查询语句,并执行查询语句;

葡京正网网投, 

7.1***查询视图“可更新”(包括“增删改”)的列***

使用数据字典视图

dba_updatable_columns——显示数据库所有视图中的所有列的可更新状态

all_updatable_columns——显示用户可访问的视图中的所有列的可更新状态                                  

      user_updatable_columns——显示用户拥有的视图中的所有列的可更新状态

 

示例1:

 SQL> select table_name,column_name,insertable,updatable,deletable from user_updatable_columns;


TABLE_NAME                     COLUMN_NAME                    INS UPD DEL

------------------------------ ------------------------------ --- --- ---

VW_EMP_READONLY                EMPNO                          NO  NO  NO

VW_EMP_READONLY                ENAME                          NO  NO  NO

VW_EMP_READONLY                JOB                            NO  NO  NO

VW_EMP_READONLY                HIREDATE                       NO  NO  NO

VW_EMP_READONLY                DEPTNO                         NO  NO  NO

VW_EMP_CHECK                   EMPNO                          YES YES YES

VW_EMP_CHECK                   ENAME                          YES YES YES

VW_EMP_CHECK                   JOB                            YES YES YES

VW_EMP_CHECK                   HIREDATE                       YES YES YES 





9 row selected.

 

7.2 视图DML执行遵循的原则:

1.简单视图可以执行DML操作;
2.在视图包含GROUP 函数,GROUP BY子句,DISTINCT关键字时不能删除数据行;
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据:

a.视图中包含GROUP 函数,GROUP BY子句,DISTINCT关键字;
b.使用表达式定义的列;
c.ROWNUM伪列。
d.基表中未在视图中选择的其他列定义为非空且无默认值。
WITH CHECK OPTION 子句
通过视图执行的INSERTS和UPDATES操作不能创建该视图检索不到的数据行, 因为它会对插入或修改的数据行执行完整性约束和数据有效性检查。 (也就是说在执行INSERTS、UPDATES时,WHERE条件中除需要INSERT、UPDATE本身的限制条件之外,还需要加上视图创建时的WHERE条件。)

 

八、视图容易出现的问题

Oracle视图非常强大的功能之一在于其可以创建一个带有错误的视图。比如说视图里的字段在基表里不存在,该视图仍然可以创建成功,但是非法的且无法执行。当基表里加入了该字段,或者说某个字段修改成视图里的该字段名称,那么视图马上就可以成为合法的。这个功能很有意思。
例子:
创建基表:

 create table v_test (name varchar2(32),age number(12)); 

创建带错误的视图:

create force view view_test as select name,age,address from v_test; --(注意加上force选项) 

由于address字段在v_test里不存在,所以会报warning: View created with compilation errors的警告,而且执行select * from view_test;时会报“ORA-04063: view “SCOTT.VIEW_TEST” 有错误”的异常。

但是如果在v_test里加上address字段,那么视图就会合法。
对基表进行修改:

alter table v_test add (address varchar2(128));

现在再执行

select * from view_test

就会执行成功了。

 

其他问题总结:
1、视图上是否可以创建索引?
一般视图上不用建立索引,对视图的操作最终会转化为对表的操作。
一个讨论:

 

九、 视图的种类与区别

      9.1  关系视图:Oracle视图是作为数据库对象存在的,因此,创建之后也可以通过工具或数据字典来查看视图的相关信息。这是大家常用的视图

  如:create view 视图名称 as 查询语句|关系运算

  9.2  内嵌视图:在from语句中的可以把表改成一个子查询,如:select a.id ,b.id from emp a,(select id from dept) b where a.id=b.id内嵌视图不属于任何用户,也不是对象,内嵌视图是子查询的一种,可以与数据表、视图一样作为查询语句的数据源存在,但在形式上有较大的区别,内嵌视图不必使用create view命令进行创建,因此,在数据字典中也无法获得相应信息。内嵌视图的特点在于无须创建真正的数据库对象,而只是封装查询,因此会节约数据库资源,同时不会增加维护成本。但是内嵌视图不具有可复用性,因此当预期将在多处调用到同一查询定义时,还是应该使用关系视图。

  9.3  对象视图:对象类型在数据库编程中有许多好处,但有时,应用程序已经开发完成。为了迎合对象类型而重建数据表是不现实的。对象视图正是解决这一问题的优秀策略。

  对象视图创建之后,同样可以在数据字典中获得其相应信息。利用Oracle内置视图user_views可以获得对象视图相关信息。Oracle中的对象数据实际仍然以关系数据的形式存储。但是,对象的特性,例如继承、封装等,都为开发人员提供了更加灵活的处理形式。同样,可以构造复杂的对象类型来封装复杂的多表查询。

  9.4  物化视图:常用于数据库的容灾,不是传统意义上虚拟视图,是实体化视图,和表一样可以存储数据、查询数据。主备数据库数据同步通过物化视图实现,主备数据库通过data link连接,在主备数据库物化视图进行数据复制。当主数据库垮掉时,备数据库接管,实现容灾。

 

 

 

 

本文由葡京网投哪个正规发布于新葡亰-数据,转载请注明出处:视图的概述和基本操作【葡京网投哪个正规】,SQLServer之视图篇

关键词:

上一篇:Mysql占用内存过高的优化过程,ini配置说明详解_Mysql_脚本之家

下一篇:如何在数据表当中找出被删掉的数据行ID,执行计划教会我如何创建索引