大型数据库应用-第4章视图

1、大型数据库应用 SQL Server主讲教师：肖连2第第4章（章（2） 视图视图 视图是视图是从一个或多个表（或视图）中导出的虚拟表从一个或多个表（或视图）中导出的虚拟表。它是。它是数据库用户使用数据库的观点。数据库用户使用数据库的观点。 如：存储学生信息的表可能有一个或多个，在不改变表的如：存储学生信息的表可能有一个或多个，在不改变表的物理结构的基础上，可以根据用户使用数据的重点，将不物理结构的基础上，可以根据用户使用数据的重点，将不同表中的数据形成一个虚拟的表供用户处理数据，这个虚同表中的数据形成一个虚拟的表供用户处理数据，这个虚拟表就是视图。拟表就是视图。3第第4章章 视图视图 视图的概

2、念视图的概念 视图是一个虚拟表，并不表示任何物理数据，只是用视图是一个虚拟表，并不表示任何物理数据，只是用来查看数据的窗口而已。它的数据是由对基本表的查来查看数据的窗口而已。它的数据是由对基本表的查询获得的。询获得的。 数据库中只存放视图的定义，并不存放视图对应的数数据库中只存放视图的定义，并不存放视图对应的数据，视图中所涉及的数据还在基本表中。据，视图中所涉及的数据还在基本表中。4第第4章章 视图视图 视图与表的区别视图与表的区别 视图像表一样也包含字段和数据行，但不像表一样具视图像表一样也包含字段和数据行，但不像表一样具有物理结构。有物理结构。 视图一经定义，就可以和表一样被查询、修改或删

3、除。视图一经定义，就可以和表一样被查询、修改或删除。 对视图的更新操作有一定的限制。对视图的更新操作有一定的限制。 视图中的数据改变会改变基本表的数据，反之亦然。视图中的数据改变会改变基本表的数据，反之亦然。5第第4章章 视图视图 视图的作用视图的作用 简化操作简化操作 视图可以为用户集中数据，简化用户的数据查询和视图可以为用户集中数据，简化用户的数据查询和处理。处理。 便于数据共享便于数据共享 视图允许不同的用户以不同的方式查看同一数据。视图允许不同的用户以不同的方式查看同一数据。6第第4章章 视图视图 视图的作用视图的作用 屏蔽数据库的复杂性屏蔽数据库的复杂性 视图中的数据可以来自不同基础

4、表的数据，也许表结构比较复视图中的数据可以来自不同基础表的数据，也许表结构比较复杂，但用户不必了解。杂，但用户不必了解。 增强数据的安全性增强数据的安全性 可以为各种用户授予在视图上的操作权限，而不授权用户在表可以为各种用户授予在视图上的操作权限，而不授权用户在表上的操作权限，这样就可以增强数据的安全性。上的操作权限，这样就可以增强数据的安全性。 方便数据导出方便数据导出 可以重新组织数据以便输出到其他应用程序中。可以重新组织数据以便输出到其他应用程序中。7第第4章章 视图视图 使用视图的注意事项：使用视图的注意事项： 只有在当前数据库中才能创建视图。只有在当前数据库中才能创建视图。 视图的命

5、名必须遵循标志符命名规则。视图的命名必须遵循标志符命名规则。 视图名不能与表名重复，而且对每个用户也必须是唯视图名不能与表名重复，而且对每个用户也必须是唯一的。一的。 不能把规则、默认值或触发器与视图相关联。不能把规则、默认值或触发器与视图相关联。 可以在视图上建立索引，见索引一节。可以在视图上建立索引，见索引一节。8第第4章章 视图视图 创建视图的方法：创建视图的方法： 菜单操作方式菜单操作方式设计器设计器 命令方式（命令方式（CREATE VIEW）9第第4章章 视图视图 例例创建反映海尔电器的视图创建反映海尔电器的视图 视图设计器视图设计器 关系图窗格：表间联接线，连接类型关系图窗格：表

6、间联接线，连接类型 网格窗格：列名，别名，排序，准则（网格窗格：列名，别名，排序，准则（where） SQL窗格：窗格：select语句语句 结果窗格：运行可见结果结果窗格：运行可见结果 前三个窗格同步前三个窗格同步，可任选设置，可任选设置10第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONview_name：视图名视图名column_name：视图的列名，可省略：视图的列名，可省略11第第4章章 视图视

7、图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONselect_statement ： 用来创建视图的用来创建视图的SELECT语句语句12第第4章章 视图视图 例例创建反映海尔电器的视图创建反映海尔电器的视图create view 产品产品_海尔海尔Asselect * from 产品产品表表 where left(产品名称产品名称,2)=海尔海尔 可用系统存储过程查看视图定义：可用系统存储过程查看视图定义：SP_HELP

8、TEXT 如：如：sp_helptext 产品产品_海尔海尔 给视图修改名称：给视图修改名称：SP_RENAME 如：如：sp_rename 产品产品_海尔海尔,产品海尔产品海尔 使用系统存储过程使用系统存储过程sp_depends可以可以 获得视图对象的参照对象和字段获得视图对象的参照对象和字段改表名：改表名：sp_rename 销售商销售商,客户客户改列名：改列名：sp_rename 销售商销售商.地区地区,公司所在地公司所在地13第第4章章 视图视图例例创建反映销售电器的产品信息和销售商信息。创建反映销售电器的产品信息和销售商信息。create viewcreate view xscpx

9、x(cpbh,cpmc,jg,kul,gsmc,xssfzr) xscpxx(cpbh,cpmc,jg,kul,gsmc,xssfzr)as as selectselect cp. cp.产品编号产品编号,cp.,cp.产品名称产品名称,cp.,cp.价格价格, ,cp.cp.库存量库存量,xs.,xs.客户名称客户名称,xs.,xs.负责人负责人fromfrom 产品产品 cp inner join cp inner join 产品销售产品销售 cpxs cpxs on cp.on cp.产品编号产品编号=cpxs.=cpxs.产品编号产品编号inner join inner join 销售

10、商销售商 xs xs on cpxs.on cpxs.客户编号客户编号=xs.=xs.客户编号客户编号注：用户可以为视图的每一列指定列的名称，注：用户可以为视图的每一列指定列的名称，等价于在等价于在select中给列起别名中给列起别名14第第4章章 视图视图用户可以为视图的每一列指定列的名称，尤其计算列必须起名。用户可以为视图的每一列指定列的名称，尤其计算列必须起名。例例 新建视图，包含每种产品的编号、总成本和总库存。新建视图，包含每种产品的编号、总成本和总库存。create view zjzl(create view zjzl(产品编号产品编号, ,总成本总成本, ,总库存总库存) )as

11、as select select 产品编号产品编号,sum(,sum(价格价格),sum(),sum(库存量库存量) )from from 产品产品group by group by 产品编号产品编号与以下命令等价与以下命令等价: :create view zjzlcreate view zjzlas as select select 产品编号产品编号,sum(,sum(价格价格) )总成本总成本,sum(,sum(库存量库存量) )总库存总库存from from 产品产品group by group by 产品编号产品编号15第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创

12、建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONSELECT语句需要受到限制：语句需要受到限制：1.不能在临时表或表变量上创建视图。不能在临时表或表变量上创建视图。2.不能使用不能使用INTO子句、子句、ORDER BY子句、子句、COMPUTE或或COMPUTE BY子句。子句。16第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_s

13、tatement WITH CHECK OPTIONWITH CHECK OPTION ：强制视图上执行的所有强制视图上执行的所有数据修改语句都必须符合在数据修改语句都必须符合在select查询语句中设置的查询语句中设置的 准则。通过视图修改时，准则。通过视图修改时， WITH CHECK OPTION可可 以确保以确保 提交修改后，仍可以通过视图看到修改的数据。提交修改后，仍可以通过视图看到修改的数据。17-创建视图创建视图 create view 产品产品_海尔海尔 as select * from 产品产品 where left(产品名称产品名称,2)=海尔海尔-更新视图更新视图upda

14、te 产品产品_海尔海尔set 产品名称产品名称=美的冰箱美的冰箱where 产品名称产品名称=海尔冰箱海尔冰箱再次浏览视图的数据，再次浏览视图的数据，看不到修改为看不到修改为美的冰箱美的冰箱的记录。的记录。第第4章章 视图视图18-创建视图创建视图 create view 产品产品_海尔海尔 wco as select * from 产品产品 where left(产品名称产品名称,2)=海尔海尔 with check option-更新视图更新视图update 产品产品_海尔海尔wcoset 产品名称产品名称=美的彩电美的彩电where 产品名称产品名称=海尔电冰箱海尔电冰箱 报错：报错：

15、试图进行的插入或更新已失败，原因是目标视图或者目标视试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了图所跨越的某一视图指定了 WITH CHECK OPTION，而该操作的一，而该操作的一个或多个结果行又不符合个或多个结果行又不符合 CHECK OPTION 约束的条件。约束的条件。第第4章章 视图视图19第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONview_attribu

16、te ：指定视图的属性。指定视图的属性。包括包括ENCRYPTION、SCHEMABINDING、VIEW_METADATA。 20第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONENCRYPTION：说明在系统表说明在系统表syscomments中存中存储的储的CREATE VIEW语句时进行加密。这样可防止语句时进行加密。这样可防止将视图作为将视图作为SQL Server复制的一部分进行发布。复制的一

17、部分进行发布。21第第4章章 视图视图create view 产品产品_海尔海尔we with encryptionas select * from 产品产品 where 产品名称产品名称 like 海尔海尔%-视图加密视图加密 此时查看视图定义：此时查看视图定义：sp_helptext 产品产品_海尔海尔we 结果显示结果显示对象备注已加密对象备注已加密。 且无法使用视图设计器修改该视图。且无法使用视图设计器修改该视图。 只能使用只能使用alter view命令修改该视图。命令修改该视图。22第第4章章 视图视图 使用使用CREATE VIEW语句创建语句创建 CREATE VIEW vie

18、w_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTIONSCHEMABINDING：说明将视图与其所依赖的表说明将视图与其所依赖的表或视图结构相关联。此时，在或视图结构相关联。此时，在select_statement中中如果包含所引用的表、视图或用户定义的函数，如果包含所引用的表、视图或用户定义的函数，则这些对象的名称前则这些对象的名称前必须包含所有者前缀必须包含所有者前缀。23第第4章章 视图视图create view 产品产品_海尔海尔sch with schemabindingas select 产品编号

19、产品编号,产品名称产品名称,价格价格,库存量库存量 from dbo.产品产品 where 产品名称产品名称 like 海尔海尔%-视图绑定到基础表的架构视图绑定到基础表的架构 修改用于生成当前视图的表或视图时，一旦对当前视图产修改用于生成当前视图的表或视图时，一旦对当前视图产生影响（导致视图失效），则不允许修改。生影响（导致视图失效），则不允许修改。 select语句中的表名前必须有所有者前缀语句中的表名前必须有所有者前缀 select语句中不能使用语句中不能使用*指代所有字段指代所有字段 为视图建立索引时，要求视图必须绑定为视图建立索引时，要求视图必须绑定测试：测试：alter table

20、 产品产品drop column 产品名称产品名称24第第4章章 视图视图 修改视图的方法：修改视图的方法： 菜单操作方式菜单操作方式视图设计器视图设计器 命令方式（命令方式（ALTER VIEW）注：注： 视图设计器无法修改加密视图，视图设计器无法修改加密视图，需用命令方式。需用命令方式。25第第4章章 视图视图 修改视图修改视图 ALTER VIEW view_name (column_name ,n ) WITH ,n AS select_statement WITH CHECK OPTION26-修改视图修改视图 alter view 产品产品_海尔海尔wco as select *

21、from 产品产品 where 产品名称产品名称=海尔手机海尔手机 with check option-建立建立产品产品_海尔海尔 wco时使用了时使用了with check option，若要若要保留选项提供的功能，则修改时也需包含该选项。保留选项提供的功能，则修改时也需包含该选项。第第4章章 视图视图27-修改加密视图修改加密视图 alter view 产品产品_海尔海尔we with encryption as select * from 产品产品 where 产品名称产品名称=海尔手机海尔手机 第第4章章 视图视图28第第4章章 视图视图 删除视图删除视图 DROP VIEW （vie

22、w_name ） ,n ) -可一次删除多个视图 drop view 产品产品_海尔海尔wco,产品产品_海尔海尔we 注意删除视图后，会对信赖于该视图的其他对象产生影响。注意删除视图后，会对信赖于该视图的其他对象产生影响。29第第4章章 视图视图 视图的应用视图的应用 1.通过视图检索表数据通过视图检索表数据 2.通过视图添加表数据通过视图添加表数据 3.通过视图修改表数据通过视图修改表数据 4.通过视图删除表数据通过视图删除表数据30第第4章章 视图视图 查询视图查询视图 视图消解视图消解(View Resolution)：在查询视图时，首先找到：在查询视图时，首先找到视图的定义，然后把此

23、定义与用户对视图的查询结合起来，视图的定义，然后把此定义与用户对视图的查询结合起来，转换成对基本表的查询，这一转换过程称为视图消解。转换成对基本表的查询，这一转换过程称为视图消解。 例例从视图从视图“产品产品 _海尔海尔”中查询库存量大于中查询库存量大于300的产品记的产品记录。录。 select * from 产品产品 _海尔海尔 where 库存量库存量30031第第4章章 视图视图 通过视图添加表数据通过视图添加表数据 INSERT INTO 视图名视图名 VALUES(列值列值1,列值列值2,列值列值n) 注意：注意： 插入视图中的列值插入视图中的列值个数、数据类型应该和视图定义的列数

24、、基础表对应的个数、数据类型应该和视图定义的列数、基础表对应的数据类型保持一致数据类型保持一致。 如果视图的定义中只选择了基础表的部分列，基础表的其余列至少有一列如果视图的定义中只选择了基础表的部分列，基础表的其余列至少有一列不允许为空，且该列未设置默认值，则插入操作会失败。因为，视图中无不允许为空，且该列未设置默认值，则插入操作会失败。因为，视图中无法向未出现的列中插入值。法向未出现的列中插入值。 如果在视图定义中使用了如果在视图定义中使用了WITH CHECK OPTION子句，子句，则在视图上执行的数据插入语句必须符合则在视图上执行的数据插入语句必须符合定义视图的定义视图的SELECT语

25、句中所设定的条件。语句中所设定的条件。32第第4章章 视图视图可更新视图可更新视图要通过视图更新基本表数据，必须保证视图是可更新视图。一个可要通过视图更新基本表数据，必须保证视图是可更新视图。一个可更新视图可以是满足以下条件的视图：更新视图可以是满足以下条件的视图：创建视图的创建视图的SELECTSELECT语句中没有聚合函数，且没有语句中没有聚合函数，且没有GROUP BYGROUP BY、UNIONUNION子句及子句及DISTINCTDISTINCT关键字；关键字；创建视图的创建视图的SELECTSELECT语句中不包含从基本表列通过计算所得的列；语句中不包含从基本表列通过计算所得的列；

26、创建视图的创建视图的SELECTSELECT语句的语句的FROMFROM子句中至少要包含一个基本表。子句中至少要包含一个基本表。注：注：当视图所依赖的基本表有多个时，不能向该视图插入数据。当视图所依赖的基本表有多个时，不能向该视图插入数据。33第第4章章 视图视图通过视图添加表数据通过视图添加表数据创建视图如下：创建视图如下： create view 产品产品_海尔海尔Asselect * from 产品产品表表 where left(产品名称产品名称,2)=海尔海尔插入一条记录：插入一条记录：Insert into 产品产品_海尔海尔 values(b1008,海尔平板电脑海尔平板电脑,$3

27、000,500)34第第4章章 视图视图通过视图添加表数据通过视图添加表数据创建视图如下：创建视图如下： create view 产品产品_销售销售Asselect 产品表产品表.产品编号产品编号,库存量库存量,数量销售量数量销售量 from 产品表产品表,产品销售表产品销售表 where 产品表产品表.产品编号产品编号=产品销售表产品销售表.产品编号产品编号插入一条记录：插入一条记录：Insert into 产品产品_销售销售 values(b1008, 300,50)-出错出错, 视图或函数视图或函数产品产品_销售销售 不可更新，不可更新， 因为修改会影响多个基表。因为修改会影响多个基表。

28、35第第4章章 视图视图通过视图添加表数据通过视图添加表数据创建视图如下：创建视图如下： create view 产品产品_海尔海尔2Asselect * from 产品产品表表 where left(产品名称产品名称,2)=海尔海尔 with check option插入一条记录：插入一条记录：Insert into 产品产品_海尔海尔2 values(b1009,平板电脑平板电脑,$3000,500)-出错出错, 试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某试图进行的插入或更新已失败，原因是目标视图或者目标视图所跨越的某一视图指定了一视图指定了WITH CHECK OPTION，而该操作的一个或多个结果行又不符合，而该操作的一个或多个结果行又不符合CHECK OPTION 约束。约束。36第第4章章 视图视图通过视图修改表数据通过视图修改表数据 UPDATE 视图名视图名 SET 列列1=列值列值1 列列2=列值列值2 列列n=列值列值n) 注意：注意： 1.如果在视图定义中使用了如果在视图定义中使用了WITH CHECK OPTION子句，且要更改的数据不符合视图定义子句，且要更改的数据不符合