1、数据库程序设计 SQL Server 2000 数据库程序设计,第1章 SQL Server 概述 第2章 创建和管理数据库 第3章 创建数据类型和表 第4章 实现数据完整性 第5章 Transact-SQL介绍 第6章 使用 Transact-SQL查询工具 第7章 检索数据 第8章 数据分组与汇总 第9章 多表联接 第10章 子查询 第11章 修改数据,第12章 全文索引查询 第13章 规划索引 第14章 创建和维护索引 第15章 实现视图 第16章 实现存储过程 第17章 实现用户定义函数 第18章 实现触发器 第19章 多服务器编程 第20章 优化查询性能 第21章 分析查询 第22章
2、 管理事务和锁,第15章 实现视图,视图介绍 视图的优点 定义视图 通过视图修改数据 使用视图优化性能 推荐操作,视图虚表，只有结构没有数据，源自一个或多个基本表，集中用户感兴趣的数据，提高数据的安全性,基表1,基表N,视图,视图介绍,15.1 视图介绍,视图提供了将预定义的查询存储成数据库中的对象以备将来使用的能力 在视图中被查询的表称为基表 可以将任何 SELECT 语句命名和存储为视图，除了一些例外 视图的常见实例 一个基表的行或列的子集 两个或多个表的合并 两个或多个表的联接 一个基表的统计摘要 另一个视图或视图和基表组合的子集,第15章 实现视图,视图介绍 视图的优点 定义视图 通过
3、视图修改数据 使用视图优化性能 推荐操作,视图的优点,对用户只显示特定数据：不需要的、敏感的或不合适的数据不引入视图内 只关注于重要的或适当的数据 限制对敏感数据的访问 屏蔽数据库复杂性 隐藏复杂的数据库设计。开发者可修改设计而不影响用户与数据库的交互。同时，提供给用户更友好的界面 简化复杂的查询，包括对异构数据的分布式查询。用户直接查询视图，而不需要写查询语句或执行脚本,15.2 视图的优点,视图的优点（续）,简化用户权限管理 不必对用户赋予查询基表中特定列的权限，而只需要赋予用户查询视图的权限即可 改进性能 视图允许存储复杂查询的结果，让其他查询直接使用。视图亦允许分区数据，可将单独的分区
4、放置于分散的计算机内 组织数据以便导出到其他应用程序 可基于联接两个或多个表的复杂查询创建视图，并将数据导出到其他应用程序以进行更深入的分析,15.2 视图的优点,第15章 实现视图,视图介绍 视图的优点 定义视图 通过视图修改数据 使用视图优化性能 推荐操作,定义视图,创建视图 示例 联接表的视图 更改和删除视图 避免破坏所有权链 查找视图定义信息 隐藏视图定义,15.3 定义视图,创建视图,创建视图 可通过使用创建视图向导、企业管理器或 Transact-SQL 语句创建视图创建视图时，SQL Server 2000 将检查视图定义语句中所有引用的数据库对象是否都存在 视图的命名必须遵守标
5、识符的命名规则，拥有者是可选的。建议使用一个一致的命名约定来区别表和视图 执行 CREATE VIEW 语句的用户必须是 sysadmin、db_owner 或 db_ddladmin 角色的成员，或具有 CREATE VIEW 权限。在视图中引用的所有表和视图上，也要拥有 SELECT 权限,15.3.1 创建视图,CREATE VIEW dbo.OrderSubtotalsView (OrderID, Subtotal) AS SELECT OD.OrderID,SUM(CONVERT(money,(OD.UnitPrice*Quantity*(1-Discount)/100)*100)
6、FROM Order Details OD GROUP BY OD.OrderID GO,创建视图（续）,使用 SELECT 语句指定视图的内容 必须在语句中指定列名的情况得自于算术表达式、内建函数或常量的视图列在联接中使用相同名字的基表列 指定列名的方法在 SELECT 语句中使用列别名在 CREATE VIEW 语句中指定,15.3.1 创建视图,创建视图（续）,视图定义的限制 CREATE VIEW 语句不能包括 COMPUTE 或 COMPUTE BY 子句，也不能包括 INTO 关键字 仅当使用 TOP 关键字的时候，CREATE VIEW 语句才能包括 ORDER BY 子句 视图
7、不能引用临时表或变量 视图不能引用超过1 024列 在单一批处理中，CREATE VIEW 语句不能和其他 Transact-SQL 语句组合使用,15.3.1 创建视图,2.使用企业管理器创建视图,1）在企业管理器，打开“视图设计器”2）单击工具栏上的“添加表”按钮，将表添加到视图设计器中3）根据新建视图的需要，从表中选择视图引用的列4）在网格窗格中的“准则”栏中设置过滤记录的条件5）设置视图的其他属性6）在视图设计器窗口中，检查SQL语法7）最后，单击“保存”按钮，为视图指定名称,步骤：,2.使用企业管理器创建视图,创建过程中的图解：,2.使用企业管理器创建视图,创建过程中的图解：,属性,
8、视图设计器,图 “添加表”对话框,图 选择表列,图 “属性”对话框,图 选择工具菜单中的向导命令,3.使用向导创建视图,图 “选择向导”对话框,图 欢迎使用创建视图向导对话框,图 选择数据库,图 “选择对象”对话框,图 “选择列”对话框,图 “定义限制”对话框,图 完成创建视图向导对话框,上机操作,以在PUBS数据库中创建B_TITLEAUTHOR（描述商业类书的情况）视图 ，包含authors表的au_lname,au_fname, titles表的title字段，指定TYPE字段的规则为BUSSINESS,示例 联接表的视图,15.3.2 示例 联接表的视图,创建视图以提供一个方便地从两个
9、或多个联接表中查看信息的方法,更改和删除视图,更改视图：可更改一个视图（包括索引视图）的定义，但不影响相关的存储过程或触发器，也不更改权限语法：ALTER VIEW 拥有者.视图名 (列 ,.n) WITH ENCRYPTION | SCHEMABINDING | VIEW_METADATA ,.n AS select语句 WITH CHECK OPTION 和 CREATE VIEW 的限制相同 保留分配的权限 使用新的 SELECT 语句和选项代替原来的定义 删除视图：从数据库中删除视图的定义和所有分配给它的权限 语法：DROP VIEW 视图名 ,.n,15.3.3 更改和删除视图,拥有
10、权链：相依赖的对象具有不同的拥有者，形成了拥有权链 若视图的拥有者亦拥有下层的对象，则让其他用户访问视图时只需要把视图的权限分配给其他用户；否则，需要把所有牵涉到的对象的权限分配给其他用户 为了避免破坏拥有权链，应该让 dbo 用户拥有所有视图 示例 玛丽亚基于露西娅的视图 view1 创建了视图 view2，则若皮埃尔想访问 view2，不但要取得玛丽亚对 view2的授权，还需要取得露西娅对 view1 的授权,避免破坏所有权链,15.3.4 避免破坏所有权链,避免破坏所有权链（续）,15.3.4 避免破坏所有权链,GRANT SELECT ON view2 TO pierre,SELEC
11、T * FROM maria.view2,相依赖的对象具有不同的拥有者玛丽亚执行皮埃尔执行,查找视图定义信息,查找视图定义 企业管理器 下列视图和系统表显示创建视图所使用的命令文本用企业管理器查询 INFORMATION_SCHEMA.VIEWS执行系统存储过程 sp_helptext：sp_helptext 对象名,15.3.5 查找视图定义信息,查找视图定义信息（续）,查找视图依赖：检索特定视图所依赖的表和视图以及依赖特定视图的对象 企业管理器 系统存储过程 sp_depends：sp_depends 对象名,15.3.5 查找视图定义信息,隐藏视图定义,若不加密，用户可以通过多种方法显示
12、视图的定义 企业管理器 INFORMATION_SCHEMA_VIEWS 视图 查询 syscomments 系统表 使用 WITH ENCRYPTION 选项 加密 syscomments 表中所包含的视图创建时的 CREATE VIEW 语句的文本 在加密之前，确保视图定义（脚本）已经保存到文件里，因为加密后无法再从系统表中查到定义文本了 不要删除 syscomments 表中的项 会造成视图无法使用、当数据库版本升级时无法重建视图的后果,15.3.6 隐藏视图定义,第15章 实现视图,视图介绍 视图的优点 定义视图 通过视图修改数据 使用视图优化性能 推荐操作,通过视图修改数据,视图维护
13、的并不是数据的副本，它显示的是一条查询在一个或多个基表上的结果集。所以，对视图数据的修改实际上是对基表的修改 一般来说，为了能通过视图修改数据，视图必须定义在单个表上，在 SELECT 语句中不能包括聚合函数或 GROUP BY 子句,15.4 通过视图修改数据,通过视图修改数据（续）,明确地说，通过视图进行的修改 不能影响超过一个的底层的表。可以修改从两个或更多表得到的视图，但是每次更新或修改只能影响一个表 不能对某些列进行。不允许改变是某个计算的结果的列，例如包括计算值、内建函数或行聚合函数的列 若修改影响了在视图中未引用的列，也有可能引起错误。例如往视图中插入行的时候，其他未被引用的列不
14、允许空值且没有默认值 若视图定义中指定了 WITH CHECK OPTION 选项，则修改被校验，以符合 SELECT 语句中指明的标准,15.4 通过视图修改数据,第15章 实现视图,视图介绍 视图的优点 定义视图 通过视图修改数据 推荐操作,9.2 管 理 视 图,一、 查看视图信息,如果视图定义没有加密（没有使用WITH ENCRYPTION选项），则可获取视图定义的有关信息 视图的信息存放在系统表中，可以使用查询语句或系统存储过程查询,1使用系统存储过程查看视图信息,（1）用系统存储过程显示视图系统描述信息（如创建时间、所有者)（2）用系统存储过程显示视图的T-SQL语句（3）显示视图
15、与其他表或视图的关联性信息,2使用企业管理器查看视图信息,下页开始分别介绍,（1）使用系统存储过程显示视图的名称、列名、拥有者、创建时间、列的属性等信息命令格式：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 jkerp ex
16、ec sp_depends v_rkdwml,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 AS select_sta
17、tement WITH CHECK OPTION ,重新定义视图,（3）使用企业管理器修改视图,4 删除视图,1使用T-SQL语言删除视图2使用企业管理器删除视图,格式：Drop view 视图名,5 使用视图加强数据安全,对访问者隐藏不希望他们看到的数据在视图中仅设置希望访问者看到的列在创建视图时利用条件语句限制提取的数据范围将基本表的所有许可权限取消，设置视图的访问许可权,9.4 使用视图更改数据,通过视图对数据进行更新有诸多的限制，实际意义不大，也非建立视图的初衷！ 所以不提倡，具体内容同学们自行阅读！,下页，简单介绍,（1）当视图有多个基表时，一次只能对视图的一个基表内的数据进行增、删
18、、改操作（2）从视图向基表中插入数据时，要求基表中的其他非视图列应该是允许空（NULL）或含有默认值（3）插入的数据必须满足基表的约束条件,插入数据,应用with check option 子句 利用with check option子句，此视图的插入和更新数据都要符合构成此视图语句的where子句的条件（符合where条件的数据才能插入和更新）。 在视图上如果使用了Group By、Distinct等操作时，不能执行Insert或Update操作,一次只能修改一个表 且修改后的数据不得违反基表中的各种约束关系,更新数据,1.只能对一个表进行更新 2.可以使用视图或者基础表上的instead
19、of触发器来对数据修改操作进行变更 3.如果在视图中包含了聚合函数或者group by子句，那么视图就是不可更新的 4.如果在视图中把一个子查询作为导出表来引用，那么就不能将这个导出表中的任何一列作为视图的列输入，但是，可以在作为导出表的子查询中使用聚合函数 5.如果视图包含了with check option ，那么，对视图的insert和update操作就必须满足where子句中条件的要求 6.要对进行insert或者update操作的列必须对应于基础表中的唯一一个列。如果同一个列名出现在两个表，就应该在选择列表中明确地使用table.column的方式来唯一地标识它,删除视图数据实质是基
20、表中的对应的数据 删除视图数据前提是视图只能有一个基表,删除数据,CREATE VIEW dbo.v_rkdwml AS SELECT cj_bh AS bh, cj_mc AS mc, cj AS lx FROM zd_cjml UNION ALL SELECT ck_bh AS bh, ck_mc AS mc, ck AS lx FROM zd_ckml UNION ALL SELECT gys_bh AS bh, gys_mc AS mc, gys AS lx FROM zd_gysml UNION ALL SELECT xz_bh AS bh, xz_mc AS mc, xz AS l
21、x FROM zd_xzml,例如有视图定义：V_RKDWML,请看下页,第15章 实现视图,视图介绍 视图的优点 定义视图 通过视图修改数据推荐操作,推荐操作,15.6 推荐操作,目标 使用向导创建视图 更改视图以加密其定义 更改视图以使用 WITH CHECK OPTION 选项 使用信息模式视图以获得视图的信息 练习 1 创建和测试视图 练习 2 加密视图的定义 练习 3 通过视图修改数据 练习 4 查找视图定义,实验 实现视图,回顾,学习完本章后，将能够： 描述视图的概念 列举视图的优点 使用 CREATE VIEW 语句定义视图 通过视图修改数据 使用视图优化性能,上机练习,在pub
22、s数据库中创建视图B_TITLE（包括TITLE_ID、TITLE和PRICE3个字段），并向B_TITLE视图中插入一条记录：(“BU1122”,“Fishing with computer”,56.00)。 将B_TITLE视图中所有书的价格增加8美元。 删除B_TITLE中价格小于$10的记录 。,上机作业,一、基于表“项目数据表”和“员工数据表”创建视图，要求为： （1）视图名为“员工项目”。 （2）包含字段“编号”“姓名”“名称”和“开始日期”。 （3）字段别名分别是“员工编号”“员工姓名”“项目名称”“项目开始日期” 二、使用INSERT语句通过视图向员工数据表添加一条记录，要求“姓名”字段为“张伯” 三、使用UPDATE语句通过视图将第二步中插入记录的员工姓名改为“张晓强” 四、执行系统存储过程将视图名更名为“employee_project_VIEW”.,
