视图规划和实际操作

1、优秀精品课件文档资料SQL Server第9章 视图的规划与操作 视图是用户查看数据库内数据的一种方式，它相当于一个虚拟表，用户通过它来浏览表中感兴趣的部分或全部数据。使用视图可以将用户注意力聚焦在特定的数据上，并达到数据安全保护的目的，还能简化数据查询和处理操作。 第9章 视图的规划与操作9.1 视图的作用与规划9.2 视图操作9.3 视图应用综合实例分析 9.1 视图的作用与规划视图是关系数据库系统提供给用户以多种角度观察数据库中数据的重要机制。 视图对应于三级模式中的外模式（用户模式），它是从一个或几个基本表导出的表，由CREATE VIEW命令创建。 视图又称为虚拟表，因为数据库中存放

2、着视图的定义及其关联的基本表名等信息，而不存放视图对应的数据。视图一经定义，就可以和基本表一样被查询、被删除，但对视图的更新（增加、删除、修改）操作则有一定的限制 。9.1.1 视图的作用 数据库使用视图机制主要有以下优点：（1）视图能够简化用户的操作视图机制使用户可以将注意力集中在所关心的数据上。 （2）视图使用户能以多种角度看待同一数据视图机制能使不同岗位、不同职责、不同需求的用户按照自己的方式看待同一数据（3）视图为数据库重构提供了一定程度的逻辑独立性。 （4）视图能够对机密数据提供安全保护对不同的用户定义不同的视图，使机密数据不出现在不应看到这些数据的用户视图上，这样视图机制就自动提供

3、了对机密数据的安全保护功能。9.1.2 视图的规划在设计好数据库的全局逻辑结构后，还应该根据局部应用的需求，结合DBMS的特点，设计局部应用的数据库局部逻辑结构，即设计更符合局部用户需要的用户视图。定义数据库全局逻辑结构主要从系统的时间效率、空间效率、易维护等角度出发。 定义用户局部视图时可以注重考虑用户的习惯与方便。 9.1.2 视图的规划 定义用户局部视图时可以主要考虑以下几个方面：（1）使用更符合用户习惯的别名在设计数据库总体结构时，同一关系和属性具有唯一的名字，但是，在局部应用中，对同一关系或属性，有自己更加习惯的名字。我们可以用视图机制在设计用户视图时重新定义某些属性名，使其与用户习

4、惯一致，以方便使用。 （2）可以对不同级别的用户定义不同的视图，以保证系统的安全性。 （3）简化用户对系统的使用如果某些局部应用中经常要使用某些很复杂的查询，为了方便用户，可以将这些复杂查询定义为视图，用户每次只对定义好的视图进行查询，大大简化了用户的使用。 9.2 视图操作 9.2.1 创建视图SQL SERVER 提供了使用SSMS和SQL 命令两种方法来创建视图。 在创建或使用视图时应该注意到以下情况：只能在当前数据库中创建视图，在视图中最多只能引用1024 列；如果视图引用的表被删除，则当使用该视图时将返回一条错误信息，如果创建具有相同的表的结构新表来替代已删除的表视图则可以使用，否则

5、必须重新创建视图；如果视图中某一列是函数、数学表达式、常量或来自多个表的列名相同，则必须为列定义名字；定义视图的查询语句不能包含COMPUTE或COMPUTEBY子句；不能包含ORDER BY子句，除非在SELECT语句的选择列表中也有一个TOP子句；不能包含INTO关键字；不能引用临时表或表变量。 不能在视图上创建全文索引、规则、默认值和after触发器；不能在规则、缺省、触发器的定义中引用视图；不能创建临时视图，也不能在临时表上建立视图。 1使用SQL SERVER企业管理器来创建视图在SQL SERVER中使用SSMS来创建视图。步骤如下： 启动SSMS ，登录到指定的服务器； 打开要创

6、建视图的数据库文件夹，选中视图图标，此时在右面的窗格中显示当前数据库的所有视图，右击图标，在弹出菜单中选择新建视图选项，打开新建视图对话框。在新建视图对话框中共有四个区：表区、列区SQL script 区、数据结果区，当然刚打开时是空白。2Transact-SQL 命令创建视图使用Transact-SQL 命令CREATE VIEW 创建视图。语法格式：CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VI

7、EW_METADATA 2Transact-SQL 命令创建视图 参数说明：（1）view_name：是视图的名称。视图名称必须符合标识符规则。可以选择是否指定视图所有者名称。（2）Column：是视图中的列名。只有在下列情况下，才必须命名CREATE VIEW中的列：当列是从算术表达式、函数或常量派生的，两个或更多的列可能会具有相同的名称（通常是因为联接），视图中的某列被赋予了不同于派生来源列的名称，以便符合用户习惯。还可以在SELECT语句中指派列名。如果未指定column，则视图列与SELECT语句中的列具有相同的名称。 2Transact-SQL 命令创建视图（3）AS：是视图要执行的

8、操作。（4）select_statement：是定义视图的SELECT语句。该语句可以使用多个表或其它视图。若要从创建视图的SELECT子句所引用的对象中选择，必须具有适当的权限。 视图不必是具体某个表的行和列的简单子集。可以用具有任意复杂性的 SELECT 子句，使用多个表或其它视图来创建视图。 在索引视图定义中，SELECT语句必须是单个表的语句或带有可选聚合的多表JOIN。 在select_statement中可以使用函数。select_statement可使用多个由UNION或UNION ALL分隔的SELECT语句。2Transact-SQL 命令创建视图（5）WITH CHECK

9、OPTION：强制视图上执行的所有数据修改语句都必须符合由 select_statement设置的准则。通过视图修改行时，WITH CHECK OPTION可确保提交修改后，仍可通过视图看到修改的数据。（6）WITH ENCRYPTION：表示 SQL Server 加密包含CREATE VIEW语句文本的系统表列。使用WITH ENCRYPTION可防止将视图作为SQL Server复制的一部分发布。（7）SCHEMABINDING：将视图绑定到架构上。 （8）VIEW_METADATA：指定为引用视图的查询请求浏览模式的元数据时，SQL Server 将向DBLIB、ODBC和OLE DB

10、 API返回有关视图的元数据信息，而不是返回基表或表 。3视图创建实例【例9-1】使用简单的 CREATE VIEW下例创建具有简单 SELECT 语句的视图。当需要频繁地查询列的某种组合时，简单视图非常有用。USE 教学管理 WHERE TABLE_NAME = courses_VIEW) DROP VIEW courses_VIEWGOCREATE VIEW courses_VIEWAS SELECT cno, cname, cbname, cedi, cpubFROM courseGO【例9-2】使用WITH ENCRYPTION下例使用WITH ENCRYPTION选项和内置函数，使用

11、函数时，必须为派生列指定列名。USE 教学管理CREATE VIEW stu_avg (sno, sname, AVG_SCORE)WITH ENCRYPTIONAS SELECT S.sno, sname, AVG(grade)FROM student S, enrollment EGROUP BY S.sno,snameGOselect c.id,c.text from syscomments c,sysobjects owhere c.id=o.id and =stu_avg【例9-3】使用WITH CHECK OPTION下例显示名为ISonly的视图，该视图使得只能对信息

12、学院的学生做数据修改。USE 教学管理CREATE VIEW ISonlyAS SELECT sno, sssn, sname, ssex, smtel, scity, smajor, sdepa, sgpaFROM studentWHERE sdepa = 信息学院WITH CHECK OPTIONGOinsert into isonlyvalues(s060501,11111111,张,男,1111,宁波,会计学,会计学院,162)【例9-4】如果如本章第一节概述所述，若某一全国连锁的销售企业将销售数据表按照省份进行水平分割，那我们可以使用以下视图重新装载表的数据。 Create view

13、 sales_table As Select * from sales_beijing union Select * from sales_tianjin union Select * from sales_shanghai 9.2.2 特殊类型视图简介 索引视图：建立唯一聚簇索引的视图为索引视图。分区视图：分区视图是通过对具有相同结构的成员表使用UNION ALL 所定义的视图。信息架构视图：Microsoft提供的用于SQL Server元数据的内部视图，这些视图符合SQL-92标准中的INFORMATION_SCHEMA定义。1索引视图 由于视图返回的结果集与具有行列结构的表有着相同的表

14、格形式，并且我们可以在SQL 语句中像引用表那样引用视图，所以我们常把视图称为虚表。标准视图的数据的物理存放依然是在数据库的基本表中，只是在执行引用了视图的查询时，SQL Server 才把相关的基本表中的数据合并成视图的逻辑结构。所以，这类视图也称做存储查询。问题：由于是在执行了引用了视图的查询时，SQL Server 才把相关的基本表中的数据合并成视图的逻辑结构，那么当查询所引用的视图包含大量的数据行或涉及到对大量数据行进行合计运算或连接操作，毋庸置疑，动态地创建视图结果集将给系统带来沉重的负担，尤其是经常引用这种大容量视图。 如何解决？索引视图：建立唯一聚簇索引的视图称做索引视图 在视图

15、上创建索引可存储创建索引时存在的数据。优点：查询优化器开始在查询中使用视图索引，而不是直接在FROM子句中命名视图。这样一来，可从索引视图检索数据而无需重新编码，由此带来的高效率也使现有查询获益。缺点：它降低了对视图基表数据的修改操作的速度，且维护索引视图比维护基础表的索引更为复杂 。适合情况：非常频繁地检索视图数据，或很少修改基表数据时。在视图上创建聚集索引之前，该视图必须满足下列要求： 1.当执行CREATE VIEW语句时，ANSI_NULLS和QUOTED_IDENTIFIER选项必须设置为ON。2.为执行所有CREATE TABLE语句以创建视图引用的表，ANSI_NULLS 选项必

16、须设置为ON。3. 该视图所引用的对象仅包括基础表而不包括其它的视图；4. 视图引用的所有基表必须与视图位于同一个数据库中，并且所有者也与视图相同。5.必须使用SCHEMABINDING选项创建视图。SCHEMABINDING将视图绑定到基础基表的架构。6.如果视图引用了用户自定义函数，那么在创建这些用户自定义函数时也必须使用SCHEMABINDING选项 ； 7.视图必须以的形式来使用所引用的表或用户自定义函数；8.视图中的表达式所引用的所有函数必须是确定性的。9.视图中的SELECT语句不能包含下列T-SQL语法元素*选择列表不能使用*或table_name.*语法指定列。必须 显式给出列

17、名。*不能在多个视图列中指定用作简单表达式的表的列名。* 派生表。* 行集函数。* UNION 运算符。* 子查询。* 外联接或自联接。* TOP 子句。* ORDER BY 子句。* DISTINCT 关键字。* COUNT(*)（允许COUNT_BIG(*)。）* AVG、MAX、MIN、STDEV、STDEVP、VAR或VARP聚合函数。 注意：通常而言，可以在视图上创建多个索引，但是应该记住，在视图上所创建的第一个索引必须是聚簇索引，然后才可以创建其它的非聚簇索引。如果准备为视图创建索引，在执行CREATE INDEX命令以前，您必须确保以下条件 ： * CREATE INDEX命令的

18、执行者必须是视图的所有者； * 在执行创建索引命令期间，ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、 ARITHABORT、CONCAT_NULL_YIELDS_NULL、 QUOTED_IDENTIFIERS诸选项应被设置成ON状态； * NUMERIC_ROUNDABORT选项被设置为OFF状态； * 视图不能包括text、ntext、image类型的数据列； * 如果视图定义中的SELECT语句指定了一个GROUP BY子句，则唯一聚集索引的键只能引用在GROUP BY子句中指定的列。2分区视图 分区视图在一个或多个服务器间水平连接一组成员表中的分区数据，

19、使数据看起来就象来自一个表。Microsoft SQL Server 区分本地分区视图和分布式分区视图。在本地分区视图中，所有的参与表和视图驻留在同一个 SQL Server 实例上。在分布式分区视图中，至少有一个参与表驻留在不同的（远程）服务器上。此外，SQL Server 还区分可更新的分区视图和作为基础表只读复本的视图。 在实现分区视图之前，必须先水平分割表。原始表被分成若干个较小的成员表。每个成员表包含与原始表相同数量的列，并且每一列具有与原始表中的相应列同样的特性（如数据类型、大小、排序规则）。 成员表设计好后，每个表基于键值的范围存储原始表的一块水平区域。键值范围基于分区列中的数据

20、值。 例如，正在将一个顾客信息 Customer 表分区成三个表。这些表的 CHECK 约束为：- On Server1:CREATE TABLE Customer_33 (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 1 AND 32999), . - Additional column definitions)- On Server2:CREATE TABLE Customer_66 (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 33000 AND 65999), .

21、- Additional column definitions)- On Server3:CREATE TABLE Customer_99 (CustomerID INT PRIMARY KEY CHECK (CustomerID BETWEEN 66000 AND 99999), . - Additional column definitions)生成分布式分区视图的方式 在每一个含有在其它成员服务器上执行分布式查询所需连接信息的成员服务器上添加链接服务器定义。这将使得分布式分区视图能够访问其它服务器上的数据。建以下分布式分区视图： CREATE VIEW Customers ASUNION

22、 ALLUNION ALL3信息架构视图 信息架构视图基于SQL-92标准中针对架构视图的定义，这些视图独立于系统表，提供了关于SQL Server元数据的内部视图。信息架构视图的最大优点是，即使我们对系统表进行了重要的修改，应用程序也可以正常地使用这些视图进行访问。因此对于应用程序来说，只要是符合SQL-92标准的数据库系统，使用信息架构视图总是可以正常工作的。表9-2 常用的信息架构视图（部分）信息架构视图描 述CHECK_CONSTRAINTS返回有关列或过程参数的信息，如是否允许空值，是否为计算列等。COLUMN_DOMAIN_USAGE当前数据库中每个带有用户定义数据类型的列在该视图

23、中占一行。该信息架构视图返回当前用户对其拥有权限的对象的有关信息。COLUMN_PRIVILEGES每一个带有特权的列在该视图中占一行，这个特权是由当前数据库中的当前用户授予的，或者授予了当前数据库中的当前用户。该信息架构视图返回当前用户对拥有特权的列的相关信息。COLUMNS返回当前数据库中当前用户可以访问的所有列及其基本信息。在访问信息架构视图时，必须同时说明视图所属模式，即采用以下语法格式：例如，我们要得到某个表有多少列，可以使用以下语句：SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME=mytable9.2

24、.3 视图的修改、重命名和删除 1修改视图 修改一个先前创建的视图的定义，使用ALTER VIEW语句。ALTER VIEW语句不影响相关的存储过程或触发器，也不更改权限。 语法格式：ALTER VIEW . . view_name ( column ,.n ) WITH ,.n ASselect_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 参数说明：如果原来的视图定义是用WITH ENCRYPTION或CHECK OPTION创建的，那么只有在ALTER VIEW中也包含这些选项时，这些选

25、项才有效。如果使用ALTER VIEW更改当前正在使用的视图，SQL Server将在该视图上放一个排它架构锁。当锁已授予，并且该视图没有活动用户时，SQL Server 将从过程缓存中删除该视图的所有复本。引用该视图的现有计划将继续保留在缓存中，但当唤醒调用时将重新编译。ALTER VIEW可应用于索引视图。然而，ALTER VIEW将无条件地除去视图上的所有索引。【例9-5】更改视图下例创建称为All_teachers的视图，该视图包含全部的教师，并将该视图的查询权授予所有用户。但是由于该视图中包含了教师的编号、身份证号等个人信息，需使用ALTER VIEW替换该视图，不包括编号、身份证号

26、等个人信息，以保护教师个人隐私。- CREATE a VIEW FROM the teacer table that contains all teachers.CREATE VIEW All_teacher (tno,tssn,tname,tmtel,tcity,tdepa,trank)AS SELECT tno,tssn,tname,tmtel,tcity,tdepa,trankFROM 教学管理.teacherGO- Grant SELECT permissions on the VIEW to public.GRANT SELECT ON All_teacher TO publicGO

27、- The VIEW needs to be changed to exclude the tno, tssn, tcity attribute of all teachers ALTER VIEW All_teacher (tname,tmtel,tdepa,trank)AS SELECT tname,tmtel,tdepa,trankFROM 教学管理.teacherGO2视图重命名 使用系统存储过程sp_rename 对已创建的视图进行重命名。语法格式：sp_rename objname = object_name , newname = new_name , objtype = obj

28、ect_type 【例9-6】将例9-5中称为All_teacher的视图重命名。 语法格式： exec sp_rename All_teacher,All_teacher_view3删除视图 从当前数据库中删除一个或多个视图。可执行DROP VIEW语句。语法格式：DROP VIEW View_name ,.n 【例9-7】下例删除stu_avg视图。USE 教学管理IF EXISTS (SELECT TABLE_NAME FROM WHERE TABLE_NAME = stu_avg) DROP VIEW stu_avgGO9.2.4 查询视图 视图定义后，用户就可以象对基本表一样对视图进

29、行查询了。【例9-8】如果要查询信息学院每个学生的情况，只要从视图ISonly查询即可。 Select * From ISonly【例9-9】创建信息学院每个学生的成绩视图，包括学生的学号、姓名、所选课程号、课程名，成绩，并进行查询。USE 教学管理IF EXISTS (SELECT TABLE_NAME FROM WHERE TABLE_NAME = ISstu_score) DROP VIEW ISstu_scoreGOCREATE VIEW ISstu_score (sno, sname, cno,cname,grade)AS SELECT S.sno, sname, C o,cname

30、,gradeFROM student S, enrollment E, offering O, course CWHERE S.sno=E.sno AND E.ono=O.ono AND sdepa=信息学院GO9.2.5 更新视图 更新视图是指通过视图来插入（INSERT）、删除（DELETE）和修改（UPDATE）数据。由于视图是不实际存储数据的虚表，因此对视图的更新，最终要转换为对基本表的更新。 Microsoft SQL Server 以两种方法增强可更新视图的类别： INSTEAD OF触发器 分区视图 【例9-10】在例9-3中，ISonly是一可更新视图，但由于视图使用了with

31、 CHECK OPTION选项，只允许更新信息学院学生数据。下面例子说明，如果没有with CHECK OPTION选项，则不能保护非视图数据库被插入、修改和删除。-首先，创建会计学院学生视图，不带with CHECK OPTION选项 -再用INSERT语句通过ACConly视图插入一工商管理学院的学生 -然后输入前面不能通过ISonly视图插入的学生元组。 通过视图对数据进行更新与删除时需要注意到以下几个问题：（1）不带with CHECK OPTION选项的视图，能够插入非视图数据，因为数据最终存储在视图所引用的基本表，但插入后，不在视图数据集，故无法通过视图查询该数据；（2）执行UPDATE DELETE时，所删除与更新的数据，必须包含在视图结果集中，否则失败，例子中通过ACConly视图对S060601学生数据的修改和删除操作均失败；（3）如果视图引用多个表时，无法用DELETE 命令删除数据，若使用UPDATE则应与INSERT操作一样，被更新的列必须属于同一个表。9.3 视图应用综合实例分析 【例9-12】一般学生信息视图（视图1）由于学生的一些个人私密信息如：身份证号、出生日期、家庭地址、家庭 等信息是不能随便透露的，为保证学生信息安全，于是为一般用户创建一般学生信息视图