SQL Server数据库应用与开发：第07章索引和视图

1、SQL Server 2005 数据库应用与开发第07章 索引和视图内容提要：在SQL Server 2005中，有效的索引是影响数据库性能的因素中最重要的一项，一个完善、良好的索引可以显著提高数据库的性能。视图是从一个或几个基表（或视图）导出的虚拟表。视图中行和列数据来源于由定义视图的查询所引用的表，并且在引用视图时动态生成。统计信息是查询优化器进行查询优化的依据，及时更新统计信息对优化的效果至关重要。实现对统计信息的创建及更新功能有自动和手动两种方式。第07章 索引和视图7.1 规划索引7.2 创建索引7.3 维护索引7.4 统计信息及应用7.5 视图的定义7.6 视图的修改7.7 通过视

2、图修改数据7.8小结7.1 规划索引7.1.1索引的用途概念：SQL Server的索引是一个单独的、物理的数据结构，是为了加速对表中数据行的检索而创建的一种分散的存储结构。索引是针对一个表而建立的，每个索引页面中的行都含有逻辑指针，指向数据库表中的物理位置，以便加速检索物理数据。索引包含从表或视图中一个或多个列生成的键，以及映射到指定数据的存储位置的指针。当SQL Server 2005执行查询时，查询优化器会对可用的数据检索方法的成本进行估计，从中选用最有效的查询计划。7.1 规划索引7.1.1索引的用途在数据库中使用索引的优点如下：加速数据检索：索引能够提供以一列或多列的值为基础迅速查找

3、或存储表的行的能力。加速连接、排序和分组：连接、排序和分组都需要数据检索，在建立好索引后，其数据检索速度会加快，从而加速连接等操作。优化查询：在执行查询时，都会先对查询进行优化。查询优化器是依赖于索引起作用的，用于决定到底哪些索引可以使该查询最快。强制实施行的唯一性：通过给列创建唯一索引，可以保证表中的数据不重复。7.1 规划索引7.1.2索引的类型SQL Server 2005中提供的索引类型如表7.1所示，其中常用的有聚集索引、非聚集索引和唯一索引3种类型。聚集索引和非聚集索引是按照索引的存储结构划分的，而唯一索引和非唯一索引是按照索引取值划分的。（1）聚集索引。在聚集索引中，索引键值的顺

4、序与数据表中记录的物理顺序相同，由于记录行只能按一个物理顺序存储，因此每个表只能有一个聚集索引。7.1 规划索引7.1.2索引的类型（2）非聚集索引。非聚集索引具有独立于数据行的结构，非聚集索引的每个键值项都有指向包含该键值的数据行的指针。每个表最多可以创建249个非聚集索引，用以满足多种查询的需要。（3）唯一索引。唯一索引可以确保所有数据行中任意两行的索引列不包括NULL在内的重复值。创建主键约束时，如果表上还没有创建聚集索引，则SQL Server 将自动在创建主键约束的列或组合上创建聚集唯一索引，主键列不允许为空值。7.1 规划索引7.1.3 设计索引的基本原则在数据库表上设计索引时，应

5、考虑以下常用的基本原则：一个表创建大量索引，会影响INSERT、UPDATE和DELETE语句的性能。若表的数据量大，对表数据的更新较少而查询较多，可以创建多个索引来提高性能。当视图包含聚合、表连接或两者的组合时，在视图上创建索引可以显著的提升性能。可以对唯一列或非空列创建聚集索引。每个表只能创建一个聚集索引。在包含大量重复值的列上创建索引，查询的时间会较长。若查询语句中存在计算列，则可考虑对计算列值创建索引。索引大小的限制，最大键列数为16，最大索引键大小为900字节。在实际创建时一定要考虑此限制。7.2 创建索引SQL Server 2005 中创建索引的方法包括：使用SQL Server

6、 Management Studio创建索利用CREATE INDEX语句创建索引在CREATE TABLE或ALTER TABLE语句为定义或修改表结构时自动创建索引。创建索引之前，应该考虑到权限问题，只有表的拥有者才能在表上创建索引。在创建聚集索引时还要考虑到数据库剩余空间的问题，创建聚集索引时所需要的可用空间是数据库表中数据量的120%。如果空间不足会降低性能，甚至导致索引操作失败。7.2 创建索引7.2.1利用SQL Server Management Studio创建索引使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：（1）展开“资

7、源管理器”|“数据库”|teaching数据库|“表”。（2）选择表并展开，右击“索引”项，在快捷菜单中选择“新建索引”命令。（3） 在弹出的“新建索引”对话框中，选择“常规”选项卡，输入所引名称C_student如图7.1所示。设置下列各项的值。 表名、索引名称、索引类型、唯一7.2 创建索引7.2.1利用SQL Server Management Studio创建索引使用SQL Server Management Studio创建独立于约束的聚集索引的操作步骤如下：（1）展开“资源管理器”|“数据库”|teaching数据库|“表”。（2）选择表并展开，右击“索引”项，在快捷菜单中选择“新

8、建索引”命令。（3） 在弹出的“新建索引”对话框中，选择“常规”选项卡，输入所引名称C_student如图7.1所示。设置下列各项的值。 表名、索引名称、索引类型、唯一7.2 创建索引7.2.1利用SQL Server Management Studio创建索引操作步骤如下：（4）设置完成后，单击索引键列的“添加”按钮，出现如图7.2所示的从Student表中选择列对话框。在“表列”列表中选中要建立索引的一列或多列。（5）索引键列设置完毕，单击“确定”按钮，返回到“新建索引”对话框，在“索引键列”中的“排序顺序”组合框中可以选择“升序”。（6）在“新建索引”对话框中查看“选项”、“包含性列”、

9、“存储”等选项卡进行必要的设置后，单击“确定”按钮，即完成了创建聚集索引的操作。在SQL Server Management Studio中创建非聚集索引和唯一索引操作步骤基本相同。7.2 创建索引7.2.2利用CREATE INDEX命令创建索引创建索引的Transact-SQL语句是CREATE INDEX语法格式如下：CREATE UNIQUE CLUSTERED|NONCLUSTERED INDEX index_name ON table_or_view_name (column ASC|DESC ,.n ) INCLUDE (column_name ,.n) WITH ( ,.n )

10、 ON partition_scheme_name(column_name) |filegroup_name |default ; 选项relational_index_option的定义格式如下： :=PAD_INDEX = ON | OFF | FILLFACTOR = fillfactor| SORT_IN_TEMPDB = ON | OFF | IGNORE_DUP_KEY = ON | OFF | STATISTICS_NORECOMPUTE = ON | OFF | DROP_EXISTING = ON | OFF | ONLINE = ON | OFF | ALLOW_ROW_L

11、OCKS = ON | OFF | ALLOW_PAGE_LOCKS = ON | OFF | MAXDOP = max_degree_of_parallelism 7.2 创建索引7.2.2利用CREATE INDEX命令创建索引例7.1 编程在 teaching 数据库中的 student 表的 sname 列上创建唯一索引IDX_sname。 程序代码如下：USE teachingGOCREATE UNIQUE INDEX IDX_sname ON student(sname)本例在student表上创建非聚集唯一索引，该索引将自动检查表中是否存在重复值。执行如下插入语句：INSERT

12、INTO student(studentno,sname,sex,birthday,classno)VALUES(0938211038,梁欣,女,1990-6-3,090802)唯一性约束确保索引列不包含重复的值，由于IGNORE_DUP_KEY默认设置为OFF，则插入操作出现重复键值时会发出错误消息。7.3 维护索引7.3.1在SQL Server Management Studio中修改索引修改索引的参考操作步骤如下：（1）启动SQL Server Management Studio，展开“资源管理器”|“数据库”|teaching |“表”|student 。（2）选择并展开“索引”项，

13、右击C_studentno索引，在快捷菜单中选择“属性”命令。（3）出现“索引属性”对话框，在各选项卡中可以修改索引的设置。在“常规”选项卡中可实现对于索引类型、索引键列、键列排序顺序的修改。如在“索引类型”组合框中选择“非聚集”项。7.3 维护索引7.3.1在SQL Server Management Studio中修改索引修改索引的参考操作步骤如下：（4）在“选项”选项卡中可实现对于在访问索引时是否使用行锁和页锁、填充因子等索引选项的修改。如设置填充因子为80%。（5）在“包含性列”选项卡中可添加包含在索引中的列，该选项卡只对非聚集索引可用。在“存储”选项卡中可实现对于索引的文件组和分区属

14、性的修改。（6）切换至“碎片”选项卡，该选项卡用于查看索引碎片数据以确定是否需要重新组织索引。如选中“重新组织索引”复选框，则执行修改可重新组织索引。（7）切换至“扩展属性”选项卡，可以修改与索引相关的扩展信息。（8）修改完毕，单击“确定”按钮，即可完成操作。7.3 维护索引7.3.3 检测索引碎片当索引包含的页中基于键值的逻辑排序与数据文件中的物理排序不匹配时，就会存在碎片。碎片非常多的索引可能会降低查询性能，导致应用程序响应缓慢。SQL Server可以通过重新组织索引或重新生成索引来修复索引碎片，以解决上述问题。决定使用哪种碎片整理方法的前提是检测索引碎片并分析以确定碎片程度。SQL S

15、erver 2005提供了查看和检测有关索引碎片信息的方法，并且可以通过对检测结果的分析，确定处理碎片的最佳方法。在检测结果中，逻辑碎片的百分比属性中的取值可用来决定下一步的处理方法。一般情况下，如该属性值30%，推荐采用索引重建。7.3 维护索引7.3.4 索引重组索引重组是通过对索引的叶级页进行物理重新排序，使其与叶结点的逻辑顺序相匹配。使页有序排列可以提高索引扫描的性能。索引重组需要注意的问题：索引在分配给它的现有页内重新组织，而不会分配新页。如果索引跨越多个文件，则将一次重新组织一个文件，不会在文件之间迁移页。重新组织还会压缩索引页。如果还有可用的磁盘空间，将删除此压缩过程中生成的所有

16、空页。压缩基于设置的填充因子值。重新组织进程使用最少的系统资源，且是自动联机执行的。索引碎片不太多时，可以重新组织索引。如果索引碎片非常多，重新生成索引则可以获得更好的结果。7.3 维护索引7.3.4 索引重组使用ALTER INDEX REORGANIZE语句可实现对索引的重新组织，其基本语法格式为：ALTER INDEX index_name | ALL ON REORGANIZE PARTITION = partition_number WITH ( LOB_COMPACTION = ON | OFF ) 例7.3 重新组织 teaching 数据库中 student 表上的 IDX_s

17、c 索引。程序代码如下：USE teachingGOALTER INDEX IDX_sc ON dbo.student REORGANIZE7.3 维护索引7.3.5 重建索引 在数据库中创建索引时，查询所使用的索引信息存储在索引页中。连续索引页由从一个页到下一个页的指针链接在一起。当对数据的更改影响到索引时，索引中的信息可能会在数据库中分散开来。重建索引可以重新组织索引数据（对于聚集索引还包括表数据)的存储，清除碎片。这可通过减少获得请求数据所需的页读取数来提高磁盘性能。 在 Microsoft SQL Server中，如果要用一个步骤重新创建索引，而不想删除旧索引并重新创建同一索引，则使用

18、 CREATE INDEX 语句的 DROP_EXISTING 子句可以提高效率。这一优点既适用于聚集索引也适用于非聚集索引。7.3 维护索引7.3.5 重建索引 以删除旧索引然后重新创建同一索引的方式重建聚集索引，是一种昂贵的方法，因为所有二级索引都使用聚集键指向数据行。如果只是删除聚集索引然后重新创建，则会使所有非聚集索引都被删除和重新创建两次。一旦删除聚集索引并再次重建该索引，就会发生这种情形。7.3 维护索引7.3.5 重建索引 通过在一个步骤中重新创建索引，可以避免这一昂贵的做法。在一个步骤中重新创建索引时，会告诉 SQL Server 要重新组织现有索引，避免了删除和重新创建非聚集

19、索引这些不必要的工作。该方法的另一个重要优点是可以使用现有索引中的数据排序次序，从而避免了对数据重新排序。这对于聚集索引和非聚集索引都十分有用，可以显著减少重建索引的成本。另外，通过使用 DBCC DBREINDEX 语句，SQL Server 还允许对一个表重建（在一个步骤中）一个或多个索引，而不必单独重建每个索引。7.3 维护索引7.3.5 重建索引索引重建将删除已存在的索引并创建一个新索引。 1. 使用ALTER INDEX REBUILD语句重建索引 ALTER INDEX REBUILD语句的基本语法格式为：ALTER INDEX index_name | ALL ON REBUIL

20、D WITH ( ,.n ) | PARTITION = partition_number WITH ( ,.n ) 例7.4 重新生成 teaching 数据库中 student 表上的 IDX_sname 索引，设置填充索引，将填充因子设置为80%，设置将中间排序结果存储在tempdb中。程序代码如下：USE teachingGOALTER INDEX IDX_sname ON dbo.student REBUILD WITH(PAD_INDEX = ON,FILLFACTOR = 80,SORT_IN_TEMPDB = ON)7.3 维护索引7.3.5 重建索引 2. 使用带DROP_E

21、XISTING子句的CREATE INDEX语句重建索引ALTER INDEX语句不能通过添加或删除键列、更改索引类型、更改列顺序或更改列排序顺序来更改索引定义，如需完成此类操作，可通过带DROP_EXISTING子句的CREATE INDEX语句实现。例7.5 重新生成 teaching 数据库中 student 表上的 C_studentno 索引，将其修改为聚集唯一索引，并指定该索引的叶级页的填充度为70%。程序代码如下：USE teachingGOCREATE UNIQUE CLUSTERED INDEX C_studentnoON dbo.student(studentno)WITH

22、(PAD_INDEX = ON,FILLFACTOR = 70,DROP_EXISTING = ON)PAD_INDEX = ON | OFF 指定索引填充。默认值为 OFF。FILLFACTOR = fillfactor指定一个百分比，指示在创建或更改索引期间，数据库引擎对各索引页的叶级填充的程度。fillfactor 必须为介于 1 至 100 之间的整数值。默认值为 0。7.3 维护索引 7.3.5 重建索引 ALTER INDEX 不能用于对索引重新分区或将索引移到其他文件组。此语句不能用于修改索引定义，如添加或删除列，或更改列的顺序。使用带有 DROP_EXISTING 子句的 CR

23、EATE INDEX 执行这些操作。ALTER INDEX.docx7.3 维护索引7.3.5 重建索引SQLServer2005重建索引前后对比在做维护项目的时，我们经常会遇到索引维护的问题，通过语句，我们就可以判断某个表的索引是否需要重建。执行一下语句：先分析表的索引分析表的索引建立情况：DBCC showcontig(Table)7.3 维护索引7.3.5 重建索引SQLServer2005重建索引前后对比DBCC SHOWCONTIG 正在扫描 Table 表.表: Table (53575229)；索引 ID: 1，数据库 ID: 14已执行 TABLE 级别的扫描。- 扫描页数:

24、228- 扫描区数: 52- 区切换次数: 225- 每个区的平均页数: 4.4- 扫描密度 最佳计数:实际计数: 12.83% 29:226- 逻辑扫描碎片 : 97.37%- 区扫描碎片 : 98.08%- 每页的平均可用字节数: 2686.3- 平均页密度(满): 66.81%7.3 维护索引7.3.5 重建索引SQLServer2005重建索引前后对比当你发现，扫描密度行，最佳计数和实际计数的比例已经严重失调，逻辑扫描碎片占了非常大的百分比，每页平均可用字节数非常大时，就说明你的索引需要重新整理一下了。执行重建索引命令：DBCC DBREINDEX(Table)后分析的情况DBCC S

25、HOWCONTIG 正在扫描 Table 表.表: Table (53575229)；索引 ID: 1，数据库 ID: 14已执行 TABLE 级别的扫描。- 扫描页数: 154- 扫描区数: 20- 区切换次数: 19- 每个区的平均页数: 7.7- 扫描密度 最佳计数:实际计数: 100.00% 20:20- 逻辑扫描碎片 : 0.00%- 区扫描碎片 : 55.00%- 每页的平均可用字节数: 86.8- 平均页密度(满): 98.93%7.3 维护索引7.3.5 重建索引SQLServer2005重建索引前后对比DBCC SHOWCONTIG 正在扫描 Table 表.表: Table

26、 (53575229)；索引 ID: 1，数据库 ID: 14已执行 TABLE 级别的扫描。- 扫描页数: 154- 扫描区数: 20- 区切换次数: 19- 每个区的平均页数: 7.7- 扫描密度 最佳计数:实际计数: 100.00% 20:20- 逻辑扫描碎片 : 0.00%- 区扫描碎片 : 55.00%- 每页的平均可用字节数: 86.8- 平均页密度(满): 98.93%7.3 维护索引7.3.6查看索引信息在SQL Server 中，可以使用系统存储过程查看索引信息。（1）sp_helpindex：用于查看有关表或视图上索引的信息。其基本语法格式如下：sp_helpindex o

27、bjname = name（2）sp_help：用于显示数据库对象或数据类型的基本信息。其基本语法格式如下：sp_help objname = nameSQL Server 2005还提供用于查看用户创建的索引的信息系统视图和系统函数。如系统视图sys.indexes，可以查看索引所属对象ID、索引名称、索引类型、文件组或分区方案ID和索引选项的当前设置。系统函数sys.dm_db_index_physical_stats可以查看索引大小和碎片统计信息7.3 维护索引7.3.7 删除索引1. 使用SQL Server Management Studio删除索引删除索引的操作步骤如下：（1） 启

28、动SQL Server Management Studio，展开“资源管理器”|“数据库”|teaching数据库|“表”|student 节点。（2）选择并展开“索引”项，右击索引UQ_sname，在快捷菜单中选择“删除”命令。（3）在弹出的“删除对象”对话框中，其中显示要删除的索引，单击“确定”按钮即可完成删除操作。7.3 维护索引7.3.7 删除索引2. 使用Transact-SQL语句删除索引使用DROP INDEX语句索引的语法格式如下：DROP VIEW schema_name. table_or_view_name.index_name .,n ; 例 7.6 删除 teachi

29、ng 数据库中 student 表上的聚集索引 C_studentno 和非聚集索引UC_classno。程序代码如下：USE teachingGODROP INDEX student.C_studentno,student.UC_classno7.4 统计信息及应用 SQL Server 2005收集存储在数据库中关于索引和列数据的统计信息，查询优化器使用这些统计信息来选择用于数据检索和更新操作的最有效执行计划。 当系统执行查询语句时，查询优化器将根据收集的统计信息决定在执行时是否使用索引，能够以最小的执行成本来完成操作获得结果。7.4 统计信息及应用7.4.1统计信息的收集1. 统计信息自

30、动创建和更新功能SQL Server 2005能够自动创建和更新统计信息，这项功能有助于查询优化器生成一致且有效的查询计划。展开“资源管理器”|“数据库”，右击teaching数据库，在快捷菜单中选择“属性”命令，出现“数据库属性”对话框，切换至“选项”选项卡，可以看到“自动创建统计信息”和“自动更新统计信息”组合框的默认设置均为True。自动创建的统计信息分两种情况：在数据表的某个列或列组合上创建索引后，系统自动创建一个同名的统计信息。对于数据表中未曾创建索引的单个列，当使用该列执行 操纵 语句时，系统会在评估最佳查询计划前，创建一个该列的统计信息。7.4 统计信息及应用7.4.1统计信息的

31、收集2. SQL Server 2005收集的信息SQL Server 2005为了进行查询成本估算而收集关于表中列的下述统计信息，并存储在一个统计信息对象中（statblob）：表或索引的行数（sys.sysindexes表的rows列）表或索引占用的页面数（sys.sysindexes表的dpages列）统计信息收集的时间。用于生成直方图和密度信息的行数。平均键的长度和包含了步数的单列直方图。字符串摘要（如果某一列含有字符串信息）。统计信息存储在sysindexes系统表的statblob列中。statblob列本身存储在一张内部目录表中。Statblo用于存储二进制大对象统计信息。7.4

32、 统计信息及应用7.4.2 统计信息的创建创建统计信息的Transact-SQL语句是CREATE STATISTICS，其语法格式如下：CREATE STATISTICS statistics_name ON table | view ( column ,.n ) WITH FULLSCAN | SAMPLE number PERCENT | ROWS | STATS_STREAM = stats_stream , NORECOMPUTE 例7.7 编程在 student 表的 studentno 和 classno 上创建一个统计组 studentclass，要求对所有记录计算统计信息。程

33、序代码如下：USE teachingGOCREATE STATISTICS studentclass ON teaching.dbo.student (studentno,classno) WITH FULLSCAN7.4 统计信息及应用7.4.3 查看统计信息1. 使用SQL Server Management Studio查看统计信息 使用SQL Server Management Studio查看统计信息的操作步骤如下：（1）启动SQL Server Management Studio，展开“资源管理器”|“数据库”|teaching数据库|“表”|student 。（2）选中并展开“统

34、计信息”项，右击IDX_sc，在快捷菜单中选择“属性”命令。（3）弹出如图7.5所示的“统计信息属性”对话框。在“常规”选项卡中显示如下信息：表名、统计信息名称、统计信息列、上次更新了这些列的统计信息、更新这些列的统计信息7.4 统计信息及应用7.4.3 查看统计信息1. 使用SQL Server Management Studio查看统计信息使用SQL Server Management Studio查看统计信息的操作步骤如下：（4）在“统计信息属性”对话框中切换至“详细信息”选项卡，如图所示。这些统计信息包括以下3部分内容：标题信息主要包括表中的行数、统计的抽样行数、所有索引列的平均长度等

35、信息。密度信息主要包括索引列前缀集的选择性、平均长度等信息。直方图信息则制定显示直方图时的信息。7.4 统计信息及应用7.4.3 查看统计信息2. 使用DBCC SHOW STATISTICS命令查看统计信息SQL Server 2005提供了DBCC SHOW STATISTICS命令用于显示指定表上的指定目标的当前分发统计信息。基本语法格式为：DBCC SHOW_STATISTICS ( table_name | view_name , target ) WITH NO_INFOMSGS , n : =STAT_HEADER | DENSITY_VECTOR | HISTOGRAM例7.8

36、 通过 DBCC SHOW STATISTICS 命令显示 student 表 IDX_sc 索引的统计信息。程序代码如下：USE teachingGODBCC SHOW_STATISTICS (student, IDX_sc)7.4 统计信息及应用7.4.4 统计信息的更新更新统计信息的UPDATE STATISTICS语句的语法结构如下：UPDATE STATISTICS table | view index | statistics_name | ( index |statistics_name ,.n ) WITH FULLSCAN | SAMPLE number PERCENT |

37、ROWS | RESAMPLE ,.n , ALL | COLUMNS | INDEX , NORECOMPUTE ;例7.9 编程更新 student 表 IDX_sc 索引的统计信息。程序代码如下：USE teachingGOUPDATE STATISTICS student IDX_sc例7.10 编程更新student表上所有索引的分布统计信息。程序代码如下：USE teachingGOUPDATE STATISTICS student7.5 视图的定义7.5.1 视图概念视图是从一个或者多个表及其他视图中通过SELECT语句导出的虚拟表。视图与数据表一样包含一些列带有名称的列和行的数

38、据，但是，视图所对应数据的行和列数据来自定义视图的查询所引用的表，并且在引用视图时动态生成。视图的行为和数据表类似，可以对其进行查看、修改和删除，也可通过视图实现对基表数据的查询与修改。视图为数据库用户提供了很多的便利，主要包括以下几个方面。简化数据查询和处理。屏蔽数据库的复杂性。安全性。可以只授予用户访问视图的权限，而不授予访问表的权限，这样就提高了数据库的安全性。7.5 视图的定义7.5.2 创建视图1. 使用SQL Server Management Studio创建视图使用SQL Server Management Studio创建视图的操作步骤如下：（1）在“对象资源管理器”中展开“

39、数据库”| teaching （2）右击“视图”选项，在快捷菜单中选择“新建视图”命令，进入视图设计界面。（3）同时在弹出的“添加表”对话框中，可以选择创建视图所需的表、视图或者函数等，如分别选择student和score两个表。单击“添加”按钮，即可将其添加到视图的查询中。如图7.7所示7.5 视图的定义7.5.2 创建视图1. 使用SQL Server Management Studio创建视图使用SQL Server Management Studio创建视图的操作步骤如下：（4）单击对话框中的“关闭”按钮，返回到SQL Server Management Studio的视图设计界面，如

40、图7.8所示。在该图中窗口右侧的“视图设计器”中包括以下4个窗格：关系图窗格：以图形方式显示正在查询的表和其他表结构化对象，同时也显示它们之间的关联关系。网格窗格：用户可以在其中指定视图的选项。SQL 窗格：显示视图所要存储的查询语句。结果窗格：显示最近执行的选择查询的结果。7.5 视图的定义7.5.2 创建视图1. 使用SQL Server Management Studio创建视图使用SQL Server Management Studio创建视图的操作步骤如下：（5）为视图选择包含的数据列。可通过“关系图窗格”、“网格窗格”和“SQL窗格”3种方式实现，并且在任何一个窗格中做出修改，另外

41、两个会自动更新以保持一致。 （6）指定查询条件。可通过“网格窗格”和“SQL窗格”两种方式实现。（7）指定分组依据和条件。可通过“网格窗格”和“SQL窗格”两种方式实现 。（8）设置排序。可通过“关系图窗格”、“网格窗格”和“SQL窗格”3种方式实现。执行完成上述操作后，在SQL窗格中显示自动生成的SELECT语句为：SELECT TOP (100) PERCENT dbo.student.sname, AVG(dbo.score.final) AS averageFROM dbo.score INNER JOIN dbo.student ON dbo.score.studentno = db

42、o.student.studentnoWHERE (dbo.student.classno = N090501) AND (dbo.score.final IS NOT NULL)GROUP BY dbo.student.snameHAVING (AVG(dbo.score.final) 60)ORDER BY average DESC7.5 视图的定义7.5.2 创建视图1. 使用SQL Server Management Studio创建视图使用SQL Server Management Studio创建视图的操作步骤如下：（9）设置完成后，单击“保存”按钮。在弹出的对话框中输入视图的名称

43、v_avg后，单击“确定”按钮，即完成了创建视图的操作。（10）在完成对于视图v_avg的创建后，可以单击工具栏的“！”按钮执行SELECT查询，其查询的结果集显示在结果窗格中。7.5 视图的定义7.5.2 创建视图2. 使用Transact-SQL语句创建视图创建视图的CREATE VIEW语句的语法格式如下：CREATE VIEW database_name. schema_name. view_name (column ,n) WITH view_attribute ,n AS select_statement WITH CHECK OPTION其中view_attribute定义为：

44、ENCRYPTION | SCHEMABINDING | VIEW_METADATA 例7.11 编程在 teaching 数据库中创建一个名称为 v_course 的视图，包含所有类别为“必修”的课程信息。程序代码如下：USE teachingGOCREATE VIEW v_courseAS SELECT * FROM course WHERE type = 必修例7.12 编程在 teaching 数据库中创建一个名称为 v_final 的视图，包含学生学号、姓名、课程号、课程名和期末成绩，按学号升序排序，相同学号的记录按课程号升序排序。程序代码如下：USE teaching CREATE

45、 VIEW v_final AS SELECT TOP(100) PERCENT student.studentno, student.sname,course.courseno, ame,score.final FROM student,course,score WHERE student.studentno = score.studentno AND course.courseno = score.courseno ORDER BY student.studentno,course.courseno例7.13 编程在 teaching 数据中创建一个名称为 v_max 的视图，查询每个班最

46、高分的课程名和分数，按班级号升序排序。程序代码如下：USE teachingCREATE VIEW v_max AS SELECT top 10 classno,cname,MAX(final) AS max FROM student s,score sc,course c where sc.courseno = c.courseno and s.studentno = sc.studentno and final IS NOT NULL GROUP BY classno,cname ORDER BY classno7.5 视图的定义7.5.2 创建视图3. 通过视图查看数据查询视图数据既可以

47、使用SQL Server Management Studio，也可以使用SELECT语句。使用SQL Server Management Studio查询视图数据的步骤如下：（1）展开“资源管理器”|“数据库”|teaching数据库。（2） 选中并展开“视图”项，右击v_course视图，在快捷菜单中选择“打开视图”命令。然后进入数据浏览窗口，可和查看表数据一样查看v_course视图中的数据。使用SELECT语句查询视图的数据与查询表的语法一致。如： SELECT * FROM v_course例7.14 通过 v_final 和 v_course 视图查询所有学生的学号、姓名和必修课的总

48、学分。程序代码如下：USE teachingSELECT studentno AS 学号,sname AS 姓名,SUM(credit) AS 必修课总学分FROM v_final,v_courseWHERE v_final.courseno=v_course.coursenoGROUP BY studentno,sname7.5 视图的定义7.5.3 查看视图信息1. 使用SQL Server Management Studio查看视图信息查看视图信息的操作步骤如下：（1）展开“资源管理器”|“数据库”|teaching数据库|“视图”。（2）查看视图的列信息。选中并展开v_avg视图|“列

49、”节点，在其下面显示视图的列信息，包括列名称、数据类型、长度精度和是否为空的约束信息。（3）查看视图的依赖关系。右击v_avg视图，在快捷菜单中选择“查看依赖关系”命令。（4）查看视图定义信息。右击v_avg视图，在快捷菜单中选择“编写视图脚本为”|“CREATE到”|“新查询编辑器窗口”命令，在右边的编辑器窗口中可查看v_avg视图的定义信息。7.5 视图的定义7.5.3 查看视图信息2. 使用系统存储过程查看视图信息用系统存储过程查看用户创建的视图的信息的方法：（1） sp_help：用于显示数据库对象或数据类型的基本信息。语法格式如下： sp_help objname = name（2）

50、sp_helptext：用于显示用户定义规则、默认值、未加密的存储过程、触发器、视图等数据对象的定义信息。语法格式如下： sp_helptext objname = name（3）sp_depends：用于显示有关数据库对象依赖关系的信息。语法格式如下： sp_depends objname = object7.5 视图的定义7.5.3 查看视图信息3. 使用系统表查看视图信息当用户创建的一个视图被存储到SQL Server 2005系统中后，视图的名称等基本信息存储在sysobjects系统表中，对应的存储对象类型type为“V”。有关视图中所定义的列的相关信息存储在syscolumns系统

51、表中有关视图与其他数据库对象之间的依赖关系信息存储在sysdepends系统表中创建视图的Transact-SQL定义语句的文本存储在syscomments系统表中。例7.15 利用 sysobjects 和 syscomments 两个系统表查看 v_avg 视图的名称、ID和定义视图的文本信息。程序代码如下：USE teachingGO SELECT ,sysobjects.id,syscomments.textFROM sysobjects,syscommentsWHERE =v_avg AND sysobjects.type=

52、V AND sysobjects.id=syscomments.id7.6 视图的修改7.6.1 在SQL Server Management Studio中修改视图修改视图的操作步骤如下：（1）展开“资源管理器”|“数据库”|teaching数据库。（2）选中并展开“视图”项，右击v_final视图，在快捷菜单中选择“修改”命令。（3）进入“视图设计器”，可在其中对视图进行修改，其中的操作与创建视图类似。（4）修改完成后，可以单击工具栏的“！”按钮执行新的v_final视图的SELECT查询，其查询的结果集显示在结果窗格中。 7.6 视图的修改7.6.2 利用ALTER VIEW命令修改视图

53、使用ALTER VIEW命令可修改视图，其中包括索引视图。ALTER VIEW不影响相关的存储过程或触发器，并且不会更改权限。ALTER VIEW的语法结构如下：ALTER VIEW database_name. schema_name. view_name (column ,.n) WITH view_attribute ,n AS select_statement WITH CHECK OPTION其中view_attribute定义为： ENCRYPTION | SCHEMABINDING | VIEW_METADATA 例7.16 使用ALTER VIEW语句修改 v_final 视图

54、，使其包含所有学生姓名、课程名和期末成绩，按姓名升序排序。程序代码如下：USE teachingGOALTER VIEW v_final ASSELECT TOP(100) PERCENT student.studentno,sname,cname,finalFROM student,course,scoreWHERE student.studentno = score.studentno AND course.courseno = score.coursenoORDER BY student.studentno例7.17 使用ALTER VIEW语句修改v_avg视图，将其改为加密方式，以确

55、保视图的安全性。程序代码如下：-在“查询编辑器”中输入以下程序，修改v_avg视图为加密方式。USE teachingGOALTER VIEW v_avg WITH ENCRYPTION ASSELECT TOP (100) PERCENT student.sname, AVG(score.final) AS average FROM score INNER JOIN student ON score.studentno = student.studentnoWHERE student.classno = 090501 AND score.final IS NOT NULLGROUP BY s

56、tudent.sname HAVING AVG(score.final) 60ORDER BY average DESC-使用系统存储过程sp_helptext查看已加密视图的定义信息，执行如下程序：EXEC sp_helptext v_avg7.6 视图的修改7.6.3 视图重命名重命名视图可以在SQL Server Management Studio中，右击需要重命名的视图，在快捷菜单中选择“重命名”命令，然后输入新的视图名称即可。利用系统存储过程sp_rename重命名视图。系统存储过程sp_rename更改当前数据库中用户创建对象的名称，此对象可以是表、列、索引、视图或用户定义数据类型

57、等，其语法格式如下：sp_rename objname = object_name , newname = new_name ,objtype = object_type 例如使用存储过程将数据库teaching中的视图v_成绩重命名为v_final。 EXEC sp_rename v_成绩,v_final需要注意的是：更改对象名的任一部分都可能破坏脚本和存储过程，使其不可用。因此建议不要使用此语句来重命名存储过程、触发器、用户定义函数或视图等数据库对象，而是将其删除，然后使用新名称重新创建。7.6 视图的修改7.6.2 删除视图2. 使用Transact-SQL删除视图使用DROP VIEW

58、语句可从当前数据库中删除一个或多个视图，其语法格式为：DROP VIEW schema_name. view_name .,n ; 例如使用Transact-SQL语句删除teaching数据库中v_max视图的命令如下：DROP VIEW v_max7.7 通过视图修改数据1. 通过视图向基表中插入数据在视图上用INSERT语句添加数据要符合以下规则：（1）使用INSERT语句向数据表中插入数据时，用户必须具备插入数据的相关权限。（2）进行插入操作的视图只能引用一个基表的列。（3） 视图中包含的列必须直接引用表列中的基础数据，不能通过计算或聚合函数等方式派生。（4）INSERT语句须为不允许

59、为空值，且没有DEFAULT定义的基表中的所有列指定值。这将确保基表中所有需要值的列都可以获取值。7.7 通过视图修改数据1. 通过视图向基表中插入数据在视图上用INSERT语句添加数据要符合以下规则：（5）在基表中插入的数据必须符合在相关列上定义的约束条件，如是否为空、约束及默认值定义等。（6）视图中不能包含DISTINCT、GROUP BY或HAVING子句。（7）如果在视图定义中使用了WITH CHECK OPTION子句，则该子句将检查插入的数据是否符合视图定义中SELECT语句所设置的条件，如果插入的数据不符合该条件，SQL Server会拒绝插入数据，并显示错误。例7.18 通过视图 v_course 向基表 course 中插入数据 (c05129, 数据库编程, 必修, 64, 4)。程序代码如下：USE teachingGOINSERT INTO v_courseVALUES(c05129,数据库编程,必修,64,4)GOSELECT * FROM course7.7 通过视图修改数据1. 通过视图向基表中插入数据例7.19 编程在teaching数据中创建一个名称为v