IT SQL 语法大全--sql_15视图.ppt

《IT SQL 语法大全--sql_15视图.ppt》由会员分享，可在线阅读，更多相关《IT SQL 语法大全--sql_15视图.ppt（67页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库程序设计数据库程序设计 SQL Server 2000 数据库程序设数据库程序设计计n第第1章章 SQL Server 概述概述n第第2章章 创建和管理数据库创建和管理数据库n第第3章章 创建数据类型和表创建数据类型和表n第第4章章 实现数据完整性实现数据完整性n第第5章章 Transact-SQL介绍介绍n第第6章章 使用使用 Transact-SQL查查询工具询工具n第第7章章 检索数据检索数据n第第8章章 数据分组与汇总数据分组与汇总n第第9章章 多表联接多表联接n第第10章章 子查询子查询n第第11章章 修改数据修改数据n第第12章章 全文索引查询全文索引查询 n第第13章章 规

2、划索引规划索引n第第14章章 创建和维护索引创建和维护索引n第第15章章 实现视图实现视图n第第16章章 实现存储过程实现存储过程n第第17章章 实现用户定义函数实现用户定义函数n第第18章章 实现触发器实现触发器n第第19章章 多服务器编程多服务器编程n第第20章章 优化查询性能优化查询性能n第第21章章 分析查询分析查询n第第22章章 管理事务和锁管理事务和锁第第15章章 实现视图实现视图n视图介绍视图介绍n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n使用视图优化性能使用视图优化性能n推荐操作推荐操作视图视图 虚表，只有结构没有数据，源自一个或多个基本表，虚表

3、，只有结构没有数据，源自一个或多个基本表，集中用户感兴趣的数据，提高数据的安全性集中用户感兴趣的数据，提高数据的安全性基表基表1基表基表N视图视图视图介绍视图介绍15.1 视图介绍视图介绍n视图提供了将预定义的查询存储成数据库中的对象视图提供了将预定义的查询存储成数据库中的对象以备将来使用的能力以备将来使用的能力l在视图中被查询的表称为基表l可以将任何 SELECT 语句命名和存储为视图，除了一些例外n视图的常见实例视图的常见实例l一个基表的行或列的子集l两个或多个表的合并l两个或多个表的联接l一个基表的统计摘要l另一个视图或视图和基表组合的子集第第15章章 实现视图实现视图n视图介绍视图介绍

4、n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n使用视图优化性能使用视图优化性能n推荐操作推荐操作视图的优点视图的优点n对用户只显示特定数据：不需要的、敏感的或不合对用户只显示特定数据：不需要的、敏感的或不合适的数据不引入视图内适的数据不引入视图内l只关注于重要的或适当的数据l限制对敏感数据的访问n屏蔽数据库复杂性屏蔽数据库复杂性l隐藏复杂的数据库设计。开发者可修改设计而不影响用户与数据库的交互。同时，提供给用户更友好的界面l简化复杂的查询，包括对异构数据的分布式查询。用户直接查询视图，而不需要写查询语句或执行脚本15.2 视图的优点视图的优点视图的优点（续）视图的

5、优点（续）n简化用户权限管理简化用户权限管理l不必对用户赋予查询基表中特定列的权限，而只需要赋予用户查询视图的权限即可n改进性能改进性能l视图允许存储复杂查询的结果，让其他查询直接使用。视图亦允许分区数据，可将单独的分区放置于分散的计算机内n组织数据以便导出到其他应用程序组织数据以便导出到其他应用程序l可基于联接两个或多个表的复杂查询创建视图，并将数据导出到其他应用程序以进行更深入的分析15.2 视图的优点视图的优点第第15章章 实现视图实现视图n视图介绍视图介绍n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n使用视图优化性能使用视图优化性能n推荐操作推荐操作定义视

6、图定义视图n创建视图创建视图n示例示例 联接表的视图联接表的视图n更改和删除视图更改和删除视图n避免破坏所有权链避免破坏所有权链n查找视图定义信息查找视图定义信息n隐藏视图定义隐藏视图定义15.3 定义视图定义视图创建视图创建视图n创建视图创建视图l可通过使用创建视图向导、企业管理器或 Transact-SQL 语句创建视图 l创建视图时，SQL Server 2000 将检查视图定义语句中所有引用的数据库对象是否都存在l视图的命名必须遵守标识符的命名规则，拥有者是可选的。建议使用一个一致的命名约定来区别表和视图l执行 CREATE VIEW 语句的用户必须是 sysadmin、db_owne

7、r 或 db_ddladmin 角色的成员，或具有 CREATE VIEW 权限。在视图中引用的所有表和视图上，也要拥有 SELECT 权限15.3.1 创建视图创建视图CREATE VIEW dbo.OrderSubtotalsView(OrderID,Subtotal)ASSELECT OD.OrderID,SUM(CONVERT(money,(OD.UnitPrice*Quantity*(1-Discount)/100)*100)FROM Order Details ODGROUP BY OD.OrderIDGO创建视图（续）创建视图（续）n使用使用 SELECT 语句指定视图的内容语句

8、指定视图的内容l必须在语句中指定列名的情况n 得自于算术表达式、内建函数或常量的视图列n 在联接中使用相同名字的基表列l指定列名的方法n 在 SELECT 语句中使用列别名n 在 CREATE VIEW 语句中指定15.3.1 创建视图创建视图创建视图（续）创建视图（续）n视图定义的视图定义的限制限制lCREATE VIEW 语句不能包括 COMPUTE 或 COMPUTE BY 子句，也不能包括 INTO 关键字l仅当使用 TOP 关键字的时候，CREATE VIEW 语句才能包括 ORDER BY 子句l视图不能引用临时表或变量l视图不能引用超过1 024列l在单一批处理中，CREATE

9、VIEW 语句不能和其他 Transact-SQL 语句组合使用15.3.1 创建视图创建视图2.使用企业管理器创建视图使用企业管理器创建视图 1）在企业管理器，）在企业管理器，打开“视图设计器”2）单击工具栏上的）单击工具栏上的“添加表添加表”按钮，将按钮，将表添加到视图设计器中设计器中 3）根据新建视图的需要，）根据新建视图的需要，从表中选择视图引用的列 4）在网格窗格中的）在网格窗格中的“准则准则”栏中栏中设置过滤记录的条件 5）设置视图的）设置视图的其他属性 6）在视图设计器窗口中，）在视图设计器窗口中，检查SQL语法 7）最后，单击）最后，单击“保存”按钮，为视图按钮，为视图指定名称

10、步骤：步骤：2.使用企业管理器创建视图使用企业管理器创建视图创建过程中的图解：创建过程中的图解：2.使用企业管理器创建视图使用企业管理器创建视图创建过程中的图解：创建过程中的图解：“添加表”按钮关系图窗格网格窗格SQL窗格结果窗格属性 视图设计器 图 “添加表”对话框 图 选择表列图 “属性”对话框 图 选择工具菜单中的向导命令3.使用向导创建视图使用向导创建视图图 “选择向导”对话框 图 欢迎使用创建视图向导对话框图 选择数据库 图 “选择对象”对话框 图 “选择列”对话框 图 “定义限制”对话框 图 完成创建视图向导对话框 上机操作上机操作n以在以在PUBS数据库中创建数据库中创建B_TI

11、TLEAUTHOR（描述商业类（描述商业类书的情况）视图书的情况）视图，包含，包含authors表的表的au_lname,au_fname,titles表的表的title字段，指定字段，指定TYPE字段的规则为字段的规则为BUSSINESS 示例示例 联接表的视图联接表的视图15.3.2 示例示例 联接表的视图联接表的视图创建视图以提供一个方便地从两个或多个联接表中查看信息的方法OrderIDOrderID1066310827104271045110515CustomerIDCustomerIDBONAP BONAP PICCO QUICKQUICK RequiredDateRequiredD

12、ate1997-09-241998-01-261997-02-241997-03-051997-05-07ShippedDateShippedDate1997-10-031998-02-061997-03-031997-03-121997-05-23OrdersCustomersShipStatusViewUSE NorthwindGOCREATE VIEW dbo.ShipStatusViewASSELECT OrderID,RequiredDate,ShippedDate,ContactNameFROM Customers c INNER JOIN Orders o ON c.Custom

13、erID=O.CustomerIDWHERE RequiredDate ShippedDateCustomerIDCustomerIDBONAPPICCOQUICKCompanyNameCompanyNameBon appPiccolo und mehrQUICK-StopContactNameContactNameLaurence LebihanGeorg PippsHorst KlossOrderIDOrderID1026410271102801996-08-211996-08-291996-09-11ShippedDateShippedDate1996-08-231996-08-3019

14、96-09-12ContactNameContactNameLaurence LebihanGeorg PippsHorst Kloss更改和删除视图更改和删除视图n更改视图：可更改一个视图（包括索引视图）的定更改视图：可更改一个视图（包括索引视图）的定义，但不影响相关的存储过程或触发器，也不更改义，但不影响相关的存储过程或触发器，也不更改权限权限语法：ALTER VIEW 拥有者.视图名(列,.n)WITH ENCRYPTION|SCHEMABINDING|VIEW_METADATA,.n AS select语句 WITH CHECK OPTIONl和 CREATE VIEW 的限制相同l保

15、留分配的权限l使用新的 SELECT 语句和选项代替原来的定义n删除视图：从数据库中删除视图的定义和所有分配删除视图：从数据库中删除视图的定义和所有分配给它的权限给它的权限语法：DROP VIEW 视图名,.n15.3.3 更改和删除视图更改和删除视图n拥有权链：相依赖的对象具有不同的拥有者，形成了拥有权链：相依赖的对象具有不同的拥有者，形成了拥有权链拥有权链l若视图的拥有者亦拥有下层的对象，则让其他用户访问视图时只需要把视图的权限分配给其他用户；否则，需要把所有牵涉到的对象的权限分配给其他用户l为了避免破坏拥有权链，应该让 dbo 用户拥有所有视图n示例示例l玛丽亚基于露西娅的视图 view

16、1 创建了视图 view2，则若皮埃尔想访问 view2，不但要取得玛丽亚对 view2的授权，还需要取得露西娅对 view1 的授权避免破坏所有权链避免破坏所有权链15.3.4 避免破坏避免破坏所所有权链有权链避免破坏所有权链（续）避免破坏所有权链（续）15.3.4 避免破坏避免破坏所所有权链有权链GRANT SELECT ON view2 TO pierreSELECT*FROM maria.view2n相依赖的对象具有不同的拥有者相依赖的对象具有不同的拥有者玛丽亚执行皮埃尔执行maria.view2lucia.view1lucia.table1查找视图定义信息查找视图定义信息n查找视图定

17、义查找视图定义l企业管理器l下列视图和系统表l显示创建视图所使用的命令文本n 用企业管理器查询 INFORMATION_SCHEMA.VIEWSn 执行系统存储过程 sp_helptext：sp_helptext 对象名15.3.5 查找视图定义信息查找视图定义信息信息模式视图或系统表信息模式视图或系统表显示信息显示信息INFORMATION_SCHEMA.TABLES 或 sysobjects视图名INFORMATION_SCHEMA.VIEW_TABLE_USAGE 或 sysdepends基对象名INFORMATION_SCHEMA.VIEWS 或 syscomments视图定义INFO

18、RMATION_SCHEMA.VIEW_COLOMN_USAGE 或 syscolumns视图中定义的列查找视图定义信息（续）查找视图定义信息（续）n查找视图依赖：检索特定视图所依赖的表和视图以查找视图依赖：检索特定视图所依赖的表和视图以及依赖特定视图的对象及依赖特定视图的对象l企业管理器l系统存储过程 sp_depends：sp_depends 对象名15.3.5 查找视图定义信息查找视图定义信息隐藏视图定义隐藏视图定义n若不加密，用户可以通过多种方法显示视图的定义若不加密，用户可以通过多种方法显示视图的定义l企业管理器lINFORMATION_SCHEMA_VIEWS 视图l查询 sysc

19、omments 系统表n使用使用 WITH ENCRYPTION 选项选项l加密 syscomments 表中所包含的视图创建时的 CREATE VIEW 语句的文本l在加密之前，确保视图定义（脚本）已经保存到文件里，因为加密后无法再从系统表中查到定义文本了n不要删除不要删除 syscomments 表中的项表中的项l会造成视图无法使用、当数据库版本升级时无法重建视图的后果15.3.6 隐藏视图定义隐藏视图定义第第15章章 实现视图实现视图n视图介绍视图介绍n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n使用视图优化性能使用视图优化性能n推荐操作推荐操作通过视图修改

20、数据通过视图修改数据n视图维护的并不是数据的副本，它显示的是一条查视图维护的并不是数据的副本，它显示的是一条查询在一个或多个基表上的结果集。所以，询在一个或多个基表上的结果集。所以，对视图数对视图数据的修改实际上是对基表的修改据的修改实际上是对基表的修改n一般来说，为了能通过视图修改数据，一般来说，为了能通过视图修改数据，视图必须定视图必须定义在义在单个表单个表上上，在，在 SELECT 语句中语句中不能包括不能包括聚合函聚合函数或数或 GROUP BY 子句子句15.4 通过视图修改数据通过视图修改数据通过视图修改数据（续）通过视图修改数据（续）n明确地说，通过视图进行的修改明确地说，通过视

21、图进行的修改l不能影响超过一个的底层的表。可以修改从两个或更多表得到的视图，但是每次更新或修改只能影响一个表l不能对某些列进行。不允许改变是某个计算的结果的列，例如包括计算值、内建函数或行聚合函数的列l若修改影响了在视图中未引用的列，也有可能引起错误。例如往视图中插入行的时候，其他未被引用的列不允许空值且没有默认值l若视图定义中指定了 WITH CHECK OPTION 选项，则修改被校验，以符合 SELECT 语句中指明的标准15.4 通过视图修改数据通过视图修改数据第第15章章 实现视图实现视图n视图介绍视图介绍n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n推

22、荐操作推荐操作9.2 管管 理理 视视 图图一、一、查看视图信息查看视图信息A.如果视图定义没有加密（没有使用WITH ENCRYPTION选项），则可获取视图定义的有关信息B.视图的信息存放在系统表中，可以使用查询语句或系统存储过程查询1使用系统存储过程查看视图信息使用系统存储过程查看视图信息（1）用系统存储过程显示视图系统描述信息（如创建时间、所有者)（2）用系统存储过程显示视图的T-SQL语句（3）显示视图与其他表或视图的关联性信息2使用企业管理器查看视图信息使用企业管理器查看视图信息下页开始分别介绍下页开始分别介绍（1）使用系统存储过程显示视图的名称、列名、拥有者、）使用系统存储过程显

23、示视图的名称、列名、拥有者、创建时间、列的属性等信息创建时间、列的属性等信息命令格式：sp_help objname=name 例：查看视图t_view 的相关信息 sp_help t_view下页看结果下页看结果格式：格式：sp_helptext objname=name（2）使用系统存储过程显示定义视图的T-SQL语句例：查看视图t_view 的定义T-SQL语句 sp_helptext t_view下页看结果下页看结果（3）显示视图与其他表或视图的关联性信息 格式：格式：sp_depends objname=name例：例：use jkerpexec sp_depends v_rkdwm

24、l下页看结果下页看结果2使用企业管理器查看视图信息使用企业管理器查看视图信息3.修改视图修改视图视图建立完毕后，可以修改它的名称，可以修改它的定义。(1)使用T-SQL语句修改视图名注意：只有数据库的所有者或视图的所有者能够对视图进行重命名工作命令格式：sp_rename objname=view_name,newname=new_name旧名新名(2)使用使用T-SQL语句修改视图定义语句修改视图定义命令格式：ALTER VIEW database_name.owner.view_name (column,.n )WITH ENCRYPTION ASselect_statement WITH

25、 CHECK OPTION 重新定义视图（3）使用企业管理器修改视图）使用企业管理器修改视图4 删除视图删除视图1使用T-SQL语言删除视图2使用企业管理器删除视图 格式：格式：Drop view 视图名视图名5 使用视图加强数据安全使用视图加强数据安全1.对访问者隐藏不希望他们看到的数据2.在视图中仅设置希望访问者看到的列3.在创建视图时利用条件语句限制提取的数据范围4.将基本表的所有许可权限取消，设置视图的访问许可权9.4 使用视图更改数据使用视图更改数据通过视图对数据进行更新有诸多的限制，实际意义不大，也非建立视图的初衷！所以不提倡，具体内容同学们自行阅读！下页，简单介绍（1）当当视视图

26、图有有多多个个基基表表时时，一次只能对视图的一个基表内的数据进行增、删、改操作的数据进行增、删、改操作（2）从从视视图图向向基基表表中中插插入入数数据据时时，要要求求基基表表中中的的其其他他非视图列应该是非视图列应该是允许空（NULL）或含有或含有默认值（3）插入的数据必须满足）插入的数据必须满足基表的约束条件插入数据插入数据n应用应用with check option 子句子句n利用利用with check option子句，此视图的插入和更新数子句，此视图的插入和更新数据都要符合构成此视图语句的据都要符合构成此视图语句的where子句的条件子句的条件（符合（符合where条件的数据才能插入

27、和更新）。条件的数据才能插入和更新）。n在视图上如果使用了在视图上如果使用了Group By、Distinct等操作时，等操作时，不能执行不能执行Insert或或Update操作操作 1.1.一次只能修改一个表一次只能修改一个表2.2.且修改后的数据不得违反基表中的各种约且修改后的数据不得违反基表中的各种约束关系束关系更新数据更新数据n1.只能对一个表进行更新只能对一个表进行更新2.可以使用视图或者基础表上的可以使用视图或者基础表上的instead of触发器来触发器来对数据修改操作进行变更对数据修改操作进行变更3.如果在视图中包含了聚合函数或者如果在视图中包含了聚合函数或者group by子

28、句，子句，那么视图就是不可更新的那么视图就是不可更新的4.如果在视图中把一个子查询作为导出表来引用，如果在视图中把一个子查询作为导出表来引用，那么就不能将这个导出表中的任何一列作为视图的那么就不能将这个导出表中的任何一列作为视图的列输入，但是，可以在作为导出表的子查询中使用列输入，但是，可以在作为导出表的子查询中使用聚合函数聚合函数5.如果视图包含了如果视图包含了with check option，那么，对视图，那么，对视图的的insert和和update操作就必须满足操作就必须满足where子句中条件子句中条件的要求的要求6.要对进行要对进行insert或者或者update操作的列必须对应于

29、基操作的列必须对应于基础表中的唯一一个列。如果同一个列名出现在两个础表中的唯一一个列。如果同一个列名出现在两个表，就应该在选择列表中明确地使用表，就应该在选择列表中明确地使用table.column的的方式来唯一地标识它方式来唯一地标识它 1.1.删除视图数据实质是基表中的对应的数据删除视图数据实质是基表中的对应的数据2.2.删除视图数据前提是视图删除视图数据前提是视图只能有一个基表删除数据删除数据CREATE VIEW dbo.v_rkdwmlASSELECT cj_bh AS bh,cj_mc AS mc,cj AS lxFROM zd_cjmlUNION ALLSELECT ck_bh

30、AS bh,ck_mc AS mc,ck AS lxFROM zd_ckmlUNION ALLSELECT gys_bh AS bh,gys_mc AS mc,gys AS lxFROM zd_gysmlUNION ALLSELECT xz_bh AS bh,xz_mc AS mc,xz AS lxFROM zd_xzml例如有视图定义：例如有视图定义：V_RKDWML请看下页请看下页第第15章章 实现视图实现视图n视图介绍视图介绍n视图的优点视图的优点n定义视图定义视图n通过视图修改数据通过视图修改数据n推荐操作推荐操作dbo 应该拥有所有视图应该拥有所有视图删除对象之前检查对象依赖删除对象

31、之前检查对象依赖谨慎基于视图创建视图谨慎基于视图创建视图禁止禁止在在 syscomments 表中表中删除条目删除条目使用标准的命名约定使用标准的命名约定推荐操作推荐操作15.6 推荐操作推荐操作30分钟分钟n目标目标n使用向导创建视图使用向导创建视图n更改视图以加密其定义更改视图以加密其定义n更改视图以使用更改视图以使用 WITH CHECK OPTION 选项选项n使用信息模式视图以获得视图的信息使用信息模式视图以获得视图的信息n练习练习 1 创建和测试视图创建和测试视图n练习练习 2 加密视图的定义加密视图的定义n练习练习 3 通过视图修改数据通过视图修改数据n练习练习 4 查找视图定义

32、查找视图定义实验实验 实现视图实现视图回顾回顾学习完本章后，将能够：学习完本章后，将能够：n描述视图的概念描述视图的概念n列举视图的优点列举视图的优点n使用使用 CREATE VIEW 语句定义视图语句定义视图n通过视图修改数据通过视图修改数据n使用视图优化性能使用视图优化性能上机练习上机练习n在在pubs数据库中创建视图数据库中创建视图B_TITLE（包括（包括TITLE_ID、TITLE和和PRICE3个字段），并向个字段），并向B_TITLE视图中插入视图中插入一条记录：一条记录：(“BU1122”,“Fishing with computer”,56.00)。n将将B_TITLE视图中

33、所有书的价格增加视图中所有书的价格增加8美元。美元。n删除删除B_TITLE中价格小于中价格小于$10的记录的记录。上机作业上机作业一、基于表一、基于表“项目数据表项目数据表”和和“员工数据表员工数据表”创建视创建视图，要求为：图，要求为：（1 1）视图名为）视图名为“员工项目员工项目”。（2 2）包含字段）包含字段“编号编号”“”“姓名姓名”“”“名称名称”和和“开始开始日期日期”。（3 3）字段别名分别是）字段别名分别是“员工编号员工编号”“”“员工姓名员工姓名”“”“项目名称项目名称”“”“项目开始日期项目开始日期”二、使用二、使用INSERT语句通过视图向员工数据表添加一条语句通过视图向员工数据表添加一条记录，要求记录，要求“姓名姓名”字段为字段为“张伯张伯”三、使用三、使用UPDATE语句通过视图将第二步中插入记录语句通过视图将第二步中插入记录的员工姓名改为的员工姓名改为“张晓强张晓强”四、执行系统存储过程将视图名更名为四、执行系统存储过程将视图名更名为“employee_project_VIEW”.