《SQL Server数据库系统基础》课件007

第7章视图与索引7.1视图简介7.2视图的创建7.3视图的修改和删除7.4视图的利用7.5索引简介7.6索引的创建和使用7.7实战训练小结思考题

对于数据表来说，SQLServer2000的视图与索引也是不可缺少的伴侣。视图使数据表的操作更加方便，索引使数据表的查询更加快速。本章任务： 学会创建和使用视图，利用视图更新数据，学会创建索引。7.1视图简介7.1.1视图的概念视图是一种数据库对象，是从一个或者多个数据表或视图中导出的虚表。视图所对应的数据并不真正地存储在视图中，而是存储在所引用的数据表中。视图的结构和数据是对数据表进行查询的结果。视图被定义后便存储在数据库中。和真实的表一样，视图在显示时也包括几个被定义的数据列和多个数据行，但通过视图看到的数据只是存放在数据表中的数据。对视图中数据的操作像对表那样，可以进行查询、修改和删除，但需要满足一定的条件。当对通过视图看到的数据进行修改时，相应的数据表的数据也会发生变化；同时，如果数据表的数据发生变化，则在相关的视图中会立刻体现出来。

根据创建视图时给定的条件，视图可以是一个数据表的一部分，也可以是多个数据表的联合，它存储了要执行检索的查询语句的定义，以便在引用该视图时使用。在SQLServer2000中，视图里最多可以定义一个或者多个数据表的1024个字段，能定义的记录数只受表中记录数的限制。视图可以用来访问整个表、表的一部分或者多个表的联接，这取决于视图中数据表的定义。数据表的定义可以是数据表中字段的子集或者记录的子集、两个或者多个数据表的联合或者联接、数据表的统计汇总、视图的视图以及视图和数据表的混合。7.1.2视图的优点在SQLServer2000中，可以根据用户的实际需要创建视图。使用视图的主要优点如下：

(1)视图可以屏蔽数据的复杂性，简化用户对数据库的操作。使用视图时，用户不必了解数据表的结构，就可以方便地使用和管理数据。因为在定义视图时，可以把经常使用的联接、投影和查询语句定义为视图，所以在每一次执行相同的查询时，不必重新编写这些复杂的查询语句，只要一条简单的查询视图语句就可以实现相同的功能。可见，视图向用户隐藏了表与表之间复杂的联接操作，简化了对用户操作数据的要求。(2)视图是为用户定制的，视图可以只反映用户感兴趣的某些特定数据，完成他们的特定任务，而无关的数据则不在视图中显示出来。视图可以让不同的用户以不同的方式看到不同或者相同的数据集。当数据表随某个用户应用的变化而增减字段时，数据表结构需要变化，但与这些增减字段无关的用户视图却可以保持稳定。

(3)可以使用视图重新组织数据，视图中的字段名称也可根据需要重新定义。在某些情况下，由于表中数据量太大，因此需要对表中的数据进行水平或者垂直分割，如果直接分割数据表，则可能会引起应用程序的执行错误。可以使用视图对数据表中的数据进行分块显示，从而使原有的应用程序仍可以通过视图来重载数据。(4)视图提供了一个简单而有效的安全机制，可以定制不同用户对数据的访问权限。通过视图，用户只能查看和修改他们所能看到的数据，其他数据库或者表既不可见也不可访问。如果某一用户想要访问视图的结果集，则必须获得其访问权限。视图所引用表的访问权限与视图权限的设置互不影响。7.2视 图 的 创 建在SQLServer2000中，创建视图有三种方法：用企业管理器创建视图、用Transact-SQL语句中的CREATEVIEW命令创建视图，用企业管理器的创建视图向导创建视图。创建视图的要求如下：

(1)视图的名称必须满足SQLServer2000中规定的标识符的命名规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的数据表名称相同。

(2)只能在当前数据库中创建视图。

(3)一个视图中最多只能引用1024个列，视图中记录的数目限制只由其数据表中的记录数决定。(4)如果视图中某一列是函数、数学表达式、常量，或者来自多个表的列名相同，则必须为列定义名称。

(5)如果视图引用的数据表或者视图被删除，则该视图不能再被使用，直到创建新的数据表或者视图为止。

(6)不能在视图上创建索引，不能在规则和默认的定义中引用视图。

(7)当通过视图查询数据时，SQLServer要进行检查，以确保语句中涉及的所有数据库对象存在，每个数据库对象在语句的上下文中有效，而且数据修改语句不能违反数据完整性规则。7.2.1使用企业管理器创建视图使用企业管理器创建视图的具体操作步骤如下：

(1)打开“企业管理器”窗口，打开“新建视图”对话框。其操作方法有两种：方法一：在企业管理器左窗格的树型选项卡中选择指定的SQLServer组，展开指定的服务器，打开要创建视图的数据库文件夹，选中指定的数据库，右击该数据库图标，从弹出的快捷菜单中依次选择“新建”→“视图”选项，如图7-1所示，打开“新建视图”对话框。方法二：在数据库文件夹中，用鼠标右击下一层的“视图”选项，在弹出的快捷菜单中选择“新建视图”选项，如图7-2所示。图7-1新建视图方法一图7-2新建视图方法二(2)在出现的新建视图操作界面可以键入建立视图的SQL语句，如图7-3所示。在图7-3中，右击窗口上部的空白部分，从弹出的快捷菜单中选择“添加表”选项，或者单击工具栏中的按钮，出现如图7-4所示的“添加表”对话框，在该对话框中可以选择需添加的数据表。

(3)在“添加表”对话框中有3个选项卡，可以分别选中它们来选择表、视图和函数。在“表”选项卡的列表框中列出了所有可用的表，选择相应的表作为创建视图的数据表，单击“添加”按钮，就可以添加进去，也可以双击某个表名来添加表。使用同样的方法可以切换到“视图”或“函数”选项卡，从中选择需要的视图或函数，并依次创建新的视图。图7-3新建视图操作界面图7-4“添加表”对话框(4)这里利用Ctrl键和鼠标配合，同时选中前面建立的3个表(即学生表、成绩表和班级表)，并单击“添加”按钮，即可将这3个表添加到“创建视图”对话框中。然后通过单击字段左边的复选框选择需要的字段。这里选择学生表中的学号和姓名字段、班级表中的班级名称字段以及成绩表中的总评成绩字段，如图7-5所示。其他选项的说明如下：●选中“输出”复选框，可以在输出结果中显示该字段。●在“准则”复选框中输入限制条件，可以限制输出的记录。在定义视图的查询语句中该限制条件对应WHERE子句。图7-5选择视图字段操作界面(5)可以点中某一表(或视图)中的字段，按住不放，拖到另一表(或视图)中，建立表间联接；也可以用鼠标右击两个表(或视图)之间的连线，删除联接。图7-5中学生表与成绩表没有连线时是crossjoin，将两表的“学号”字段连线后，即变成内联接。如果要用外联接，则可以先建内联接，然后在表间连线上右击并选中属性，在该属性框中选中一个或多个表，以决定左(右)外联接或全外联接。

(6)右击“字段定义”对话框，从弹出的快捷菜单中选择“属牲”选项，出现“属性”对话框，如图7-6所示。在该对话框中，“DISTINCT值”可以选择不输出重复的记录，“加密浏览”可以实现对视图定义加密，选中“顶端”复选框可以限制视图最多输出的记录条数。图7-6“属性”对话框(7)要运行并输出该视图结果，可以在视图设计窗口中单击工具栏中的“！”按钮，或者右击窗口空白区域，在弹出的快捷菜单中选择“运行”选项，则可根据设置的查询语句，在本窗口最下面的数据结果区显示出生成的视图内容。

(8)如果想保存视图的定义，则可单击工具栏中的“保存”按钮，或者在窗口上部显示数据表的窗格内单击鼠标右键，从弹出的快捷菜单中选择“保存”选项保存视图。这里输入“v_学生成绩”作为视图名，单击“确定”按钮，即可完成本例中视图的创建。

(9)如果不想保存视图的定义，则可直接复制全部代码。这种方法用于多表联接的代码设计，这样的设计快速、简便，不易出错。7.2.2使用Transact-SQL语句创建视图利用企业管理器创建视图是很方便的，除此以外，还可以使用Transact-SQL语句中的CREATEVIEW命令创建视图，其语法形式如下：

CREATEVIEW[<database_name>.][<owner>.]view_name[(column[,...n])][WITH<view_attribute>[,...n]]ASselect_statement[WITHCHECKOPTION]<view_attribute>::={ENCRYPTION|SCHEMABINDING|VIEW_METADATA}其中，各参数的说明如下：● database_name：用于指定创建视图的数据库名称。database_name必须是现有数据库的名称。如果不指定数据库，则database_name默认为当前数据库。● owner：用于指定创建视图所有者的用户名，owner必须是database_name所指定的数据库中的现有用户名。owner默认为database_name所指定的数据库中与当前联接相关联的用户名。● view_name：用于指定视图的名称。● column：用于指定视图中的字段名称。● WITHENCRYPTION：表示SQLServer加密包含CREATEVIEW语句文本在内的系统表列。WITHENCRYPTION主要用于将存储在系统表syscomments中的语句加密。● select_statement：用于创建视图的SELECT命令，利用SELECT命令可以从多个表或者视图中选择列，构成新视图的列，也可以使用UNION关键字联合多个SELECT语句。但是，在SELECT语句中，不能使用ORDERBY、COMPUTE、COMPUTEBY语句和INTO关键字以及临时表。● WITHCHECKOPTION：用于强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。通过视图修改数据行时，WITHCHECKOPTION确保提交修改后，仍可通过视图看到修改后的数据。● SCHEMABINDING：表示在select_statement语句中如果包含表、视图或者引用用户自定义函数，则表名、视图名或者函数名前必须包含所有者的前缀。● VIEW_METADATA：表示如果某一查询中引用该视图且要求返回浏览模式的元数据，那么SQLServer将向DBLIB和OLEDBAPIS返回视图的元数据信息。

【例7-1】使用Transact-SQL语句创建一个新视图，命名其为学生成绩。要求数据表的来源为：学生表、成绩表和课程表；选择的字段为：学生表中的学号和姓名字段、课程表中的课程名称字段及成绩表中的总评成绩字段；查询的数据为052090班学生的考试成绩。

程序清单如下：

USEcollegeGOCREATEVIEW学生成绩

ASSELECT学生表.学号,学生表.姓名,

课程表.课程名称,

成绩表.总评成绩

FROM

学生表,课程表,成绩表

WHERE学生表.学号=成绩表.学号

AND课程表.课程号=成绩表.课程号

ANDLEFT(学生表.学号,6)='052090'

在查询分析器中执行上面的程序，将会生成视图学生成绩。为了查看视图中的数据，在查询分析器中输入下面的SQL语句：

select*from学生成绩程序的执行结果如下：学号姓名 课程名称 总评成绩

05209001 张三 C语言 8005209001 张三 高等数学 8105209001 张三 邓小平理论 7305209002 李四 C语言 7505209002 李四 高等数学 8105209002 李四 邓小平理论 8505209003 唐宝家 C语言 7705209003 唐宝家 高等数学 8005209003 唐宝家 邓小平理论 9005209004 顾叶 C语言 7705209004 顾叶 高等数学 7905209004 顾叶 邓小平理论 8205209006 王安 C语言 6505209006 王安 高等数学 6905209006 王安 邓小平理论 8405209007 李静 C语言 7305209007 李静 高等数学 7605209007 李静 邓小平理论 7205209008 李朋 高等数学 54(所影响的行数为19行)

【例7-2】使用Transact-SQL语句创建新视图“不及格学生表”。对数据表和字段的选择同上，但要求只显示各班不及格的学生学号、姓名、课程名及成绩，并加密视图的定义。程序清单如下：

USEcollegeGOCREATEVIEW不及格学生表

WITHENCRYPTIONASSELECT学生表.学号,学生表.姓名,

课程表.课程名称,

成绩表.总评成绩

FROM

学生表,课程表,成绩表

WHERE学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号

AND总评成绩<60

在查询分析器中执行上面的程序，将会生成视图“不及格学生表”。在查询分析器中输入下面的SQL语句：

SELECT*FROM不及格学生表程序的执行结果如下：学号姓名课程名称总评成绩

05209008李朋高等数学54(所影响的行数为1行)

如果在定义视图时对不及格学生表视图进行了加密，那么在企业管理器中右击该视图的名称，并在弹出的快捷菜单中选择“设计视图”选项查看视图的定义信息时，系统将会弹出错误提示信息，如图7-7所示。图7-7查看加密视图时的错误提示信息【例7-3】使用Transact-SQL语句创建新视图“总分”和“平均分”。要求计算各门课程的总分及平均分。程序清单如下：

USEcollegeGOCREATEVIEW总分和平均分

ASSELECT课程表.课程名称AS课程名称,SUM(考试成绩)AS总分,AVG(考试成绩)AS平均分

FROM

学生表,课程表,成绩表

WHERE学生表.学号=成绩表.学号AND课程表.课程号=成绩表.课程号

GROUPBY

课程表.课程名称在查询分析器中执行上面的程序，将生成新视图“总分”和“平均分”。输入以下程序代码查看视图中的数据记录：

SELECT*FROM总分和平均分程序的执行结果如下：课程名称 总分 平均分

C语言 542 77

邓小平理论 533 76

高等数学 613 76(所影响的行数为3行)7.2.3使用向导创建视图除了可以使用前面介绍的两种方法创建视图以外，使用视图向导也可以很容易地创建视图，其具体操作步骤如下：

(1)在企业管理器中打开视图向导。打开视图向导的方法有以下两种：方法一：在企业管理器中选中某个数据库，这里选中college数据库，单击“工具”菜单，选择其中的“向导”选项，如图7-8所示。在出现的“选择向导”对话框中单击“数据库”选项左边的加号，在下一级选项中会看到“创建视图向导”选项，如图7-9所示。方法二：在企业管理器中选择工具栏中的图标，可直接打开如图7-9所示的“选择向导”对话框。图7-8选择“工具”菜单中的“向导”选项图7-9“选择向导”对话框(2)在“选择向导”对话框中双击“创建视图向导”选项，或者先选中“创建视图向导”选项，再单击“确定”按钮，将会出现“欢迎使用创建视图向导”对话框，如图7-10所示。

(3)单击“下一步”按钮，出现“选择数据库”对话框，其下拉列表框中列出了选定服务器的所有可用数据库，如图7-11所示。

(4)这里选择数据库college，然后单击“下一步”按钮，将出现选择视图对应的数据表的对话框，如图7-12所示。在该对话框中，列出了所选数据库college中的所有表，这些表右边的复选框标志表明该表是否已经被选择。这里将窗口中列出的学生表、成绩表和课程表3个表全部选中。图7-10“欢迎使用创建视图向导”对话框图7-11“选择数据库”对话框图7-12“选择对象”对话框(5)选择表后，单击“下一步”按钮，将会出现选择创建视图所使用的数据表中的字段对话框，如图7-13所示。在该对话框中，每个字段右端有一个复选框，可以选中该复选框，使该字段包含在创建的视图中。这里选择学生表中的学号和姓名字段、课程表中的课程名称字段及成绩表中的成绩字段。

(6)选择需要的字段后，单击“下一步”按钮，会出现“定义限制”对话框，如图7-14所示。可以在其文本框中输入查询语句的限制条件，即SELECT语句中的WHERE子句的内容，用来限制视图中包含的记录信息。默认时，视图将显示数据表中所选字段的所有信息。这里在文本框中输入如下限制条件：

WHERE学生表.学号=成绩表.学号

AND课程表.课程号=成绩表.课程号图7-13“选择列”对话框图7-14“定义限制”对话框(7)单击“下一步”按钮，出现“命名视图”对话框，系统会给出一个默认的视图名称，用户也可以自己输入新的视图名称，这里输入“v_学生成绩_052090”，如图7-15所示。

(8)单击“下一步”按钮，出现一个确认对话框，如图7-16所示。系统根据前面所做的设置，在该对话框的文本框中自动给出了完成相应功能的Transact-SQL语句。在本实例中，相应的Transact-SQL语句如下：

USE[college]GOCREATEVIEW[v_学生成绩_052090]ASSELECT[dbo].[成绩表].[总评成绩],[dbo].[课程表].[课程名称],[dbo].[学生表].[学号],[dbo].[学生表]..[姓名]FROM[dbo].[成绩表],[dbo].[课程表],[dbo].[学生表]WHERE学生表.学号=成绩表.学号

AND课程表.课程号=成绩表.课程号如果需要修改前面的设置，则可以单击“上一步”按钮。除了上述方法外，也可以直接在文本框中修改显示的SQL语句，单击“完成”按钮即可创建一个新视图。图7-15“命名视图”对话框图7-16“向导已完成”对话框7.3视图的修改和删除可以使用企业管理器或Transact-SQL语句修改视图的定义，也可以对现有的视图重新命名。删除视图再重建视图，不影响视图所依赖的表数据。7.3.1修改视图

1.使用企业管理器修改视图在企业管理器中，右击要修改的视图名称，从弹出的快捷菜单中选择“设计视图”选项，将会出现视图的设计窗口。该窗口与创建视图时的窗口相同，可以按照创建视图的方法修改视图的定义。2.使用Transact-SQL语句修改视图可以使用Transact-SQL语言中的ALTERVIEW语句修改视图，但首先必须拥有使用视图的权限，然后才能使用ALTERVIEW语句。该语句的语法形式如下：

ALTERVIEWview_name((column[,...n])][WITHENCRYPTION]ASselect_statement[WITHCHECKOPTION]

其中，各参数的说明如下：● view_name：用于指定要修改的视图。● column：用于指定视图中包含的一个或者多个字段的名称，用逗号分开，它们将成为给定视图的一部分。● select_statement:用于指定定义视图的SELECT语句。● WITHENCRYPTION：用于加密syscomments表中包含ALTERVIEW语句文本的条目。使用WITHENCRYPTION可防止将视图作为SQLServer复制的一部分发布。● WITHCHECKOPTION：用于强制视图上执行的所有数据修改语句都必须符合由定义视图的select_statement设置的准则。注意：如果原来的视图定义是用WITHENCRYPTION或WITHCHECKOPTION创建的，那么只有当ALTERVIEW中也包含这些选项时，这些选项才有效。

【例7-4】修改视图“不及格学生表”，在该视图中增加一个新的条件，要求只显示高等数学不及格的学生信息。程序清单如下：

USEcollegeGOALTERVIEW不及格学生表

AsSELECT学生表.学号,

学生表.姓名,课程表.课程名称,成绩表.总评成绩

FROM学生表,课程表,成绩表

WHERE学生表.学号=成绩表.学号

AND课程表.课程号=成绩表.课程号

AND课程表.课程名称='高等数学'--本行为新增加代码

AND总评成绩<60

在查询分析器中执行上面的程序，将会修改已创建的视图“不及格学生表”。为了查看修改后的视图包含的数据记录，在查询分析器中输入下面的程序语句：

SELECT*FROM不及格学生表程序的执行结果如下：学号 姓名 课程名称 总评成绩

05209008 李朋 高等数学 54(所影响的行数为1行)

由以上程序的执行结果可以看出，修改后的视图包含的数据记录只为高等数学考试不及格的学生学号、姓名及考试成绩。7.3.2删除视图对于不再使用的视图，可以使用企业管理器或者Transact-SQL语句中的DROPVIEW命令将其删除。

1.使用企业管理器删除视图使用企业管理器删除视图的具体操作步骤如下：

(1)打开“企业管理器”窗口，选择要删除的视图，右击该视图的名称，从弹出的快捷菜单中选择“删除”选项，出现“除去对象”对话框。这里展开college数据库，并选中“视图”选项，在视图显示窗口中选择视图“v_学生成绩_052090”进行删除，出现“除去对象”对话框，如图7-17所示。图7-17“除去对象”对话框(2)在“除去对象”对话框中单击“显示相关性”按钮，将显示和视图有关的表格和视图。单击“全部除去”按钮，即可删除该视图。

2.使用Transact-SQL语句删除视图可以使用Transact-SQL语句中的DROPVIEW命令删除视图，其语法形式如下：

DROPVIEW{view_name}[,...n]

若要使用该命令同时删除多个视图，只需在要删除的各视图名称之间用逗号隔开即可。【例7-5】删除视图“v_学生成绩_052090”和视图“不及格学生表”。程序清单如下：

DROPVIEWv_学生成绩_052090,不及格学生表在查询分析器中执行上面的语句，即可同时删除视图“v_学生成绩_052090”和“不及格学生表”。打开企业管理器的视图窗口，将会发现上述两个视图已从数据库中删除了。注意：在确认删除之前，应该查看视图的相关性窗口，查看是否有数据库对象依赖于将被删除的视图。另外，数据是保存在数据表中的，删除视图对数据没有影响。7.4视 图 的 利 用 通过视图可以方便地检索到任何需要的数据信息。但是视图的作用并不仅仅局限于检索记录，还可以利用视图对创建视图的内部表进行数据修改，比如插入新的记录、更新记录以及删除记录等。使用视图修改数据时，需要注意以下几点：

(1)修改视图中的数据时，不能同时修改两个或者多个数据表。可以对基于两个或多个数据表或者视图的视图进行修改，但是每次修改都只能影响一个数据表。

(2)不能修改那些通过计算得到的字段，例如包含计算值或者合计函数的字段。(3)如果在创建视图时指定了WITHCHECKOPTION选项，那么使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。

(4)执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。

(5)如果视图引用多个表，则无法用DELETE命令删除数据。若使用UPDATE命令，则应与INSERT操作一样，被更新的列必须属于同一个表。下面通过具体的例子来讲述如何通过视图来插入、更新和删除数据表中的数据。7.4.1利用视图插入新的数据使用视图可以插入新的数据记录，但应该注意的是，新插入的数据实际上存放在与视图相关的数据表中。

1.使用企业管理器在视图中插入记录具体方法是：在企业管理器中打开要插入记录的数据表对应的视图，在返回的数据记录的最下面一行中直接插入新记录即可。

2.使用Transact-SQL语句通过视图插入记录

【例7-6】创建一个基于班级表的新视图“v_班级表_网络”，要求包含网络专业的所有班级。

程序清单如下：

USEcollegeGOCREATEVIEWv_班级表_网络

ASSELECT班级编号,班级名称,专业

FROM班级表

WHERE专业like'网络'

在查询分析器中执行上面的程序，将会生成新的视图“v_学生表_052090”，此视图包含的数据记录如下：班级编号 班级名称 专业

052001 05网络1班 网络

052010 05网络2班 网络

(所影响的行数为2行)

在查询分析器中执行以下语句：

Insertintov_班级表_网络(班级编号,班级名称,专业)Values('052011','06网络1班','网络')

上述语句执行成功后，向班级表中添加一条新的数据记录。在企业管理器中打开视图“v_班级表_网络”，将会显示插入数据后的信息，如图7-18所示。此外，也可以使用SELECT语句在视图和表中查到该条记录。例如，在查询分析器中输入以下查询语句，然后从视图中查询数据。

SELECT班级编号,班级名称,专业

FROMv_班级表_网络显示结果如图7-19所示。图7-18企业管理器中的视图显示结果图7-19在查询分析器中的显示结果

同时，也可以直接从班级表中查询记录，查询语句如下：

SELECT班级编号,班级名称,专业

FROM班级表

WHERE专业1ike'网络'

执行上面的程序，显示的结果与使用视图显示的结果相同。

注意：如果在创建视图时定义了限制条件或者数据表的列允许空值或有默认值，而插入的记录不满足该条件，则此时仍然可以向表中插入记录，只是用视图检索时不会显示出新插入的记录。如果不想让这种情况发生，则可以使用WITHCHECKOPTION选项限制插入不符合视图规则的视图。这样在插入记录时，如果记录不符合限制条件，则不能插入。【例7-7】首先创建一个包含限制条件的视图“软件男生信息”，限制条件为显示05软件班的性别为“男”的学生信息，然后插入一条不满足限制条件的记录，再用SELECT语句检索视图和表。.

程序清单如下：

USEcollegeGOCREATEVIEW软件男生信息

ASSELECT*FROM学生表

WHERE班级编号like'052005'AND性别='男'GOInsertinto软件男生信息values(‘05209009’,‘王利’,‘女’,‘1986-11-5’,‘052005’,‘65562193’,‘国和二村34号604室','31011019870315154x')GOSELECT*FROM软件男生信息

GOSELECT*FROM学生表

WHERE班级编号1ike'052005'GO

当插入了性别为“女”的学生王利后，查询学生表，显示结果如图7-20所示。但由于此条记录不满足创建视图“软件男生信息”的条件，因此当查询视图“软件男生信息”中的记录时，此条记录不会显示出来，如图7-21所示。图7-20表的输出结果窗口图7-21视图输出结果窗口【例7-8】在例7-7的基础上添加WITHCHECKOPTION选项。程序清单如下：

USEcollegeGOCREATEVIEW软件男生信息_onlyASSELECT*FROM学生表

WHERE班级编号like'052005'AND性别='男'WITHCHECKOPTIONGOInsertinto软件男生信息_Onlyvalues(‘052090010’,‘李茹’,‘女’,‘1986-1-15’,‘052005’,‘65050213’,‘上农新村4号204室','31011019860115104x')GOSELECT*FROM软件男生信息_OnlyGO

运行该程序将显示如下出错信息：服务器：消息550，级别16，状态1，行1

插入失败的原因是：目标视图或者目标视图所跨越的某一视图指定了WITHCHECKOPTION，而该操作的一个或多个结果行又不符合CHECKOPTION约束条件。由此可见，利用视图插入数据可以限制某些错误数据进入数据表。7.4.2利用视图更新数据利用视图可以更新数据记录，但应该注意，更新的只是数据库的数据表中的数据记录。

1.使用企业管理器在视图中更新记录具体方法是：在企业管理器中打开要更新记录的数据表对应的视图，在返回的数据记录窗口中直接修改。

2.使用Transact-SQL语句通过视图更新记录

【例7-9】创建一个基于学生表的视图“v_学生表_052005”，条件是显示05软件班的学生学号、姓名和性别，然后通过该视图修改学生表中的记录。

程序清单如下：

USEcollegeGOCREATEVIEWv_学生表_052005(学号,姓名,性别)ASSELECT学号,姓名,性别

FROM学生表

WHERE班级编号like'052005'GOupdatev_学生表_052005set姓名='王利'WHERE性别='男'

如果上面的程序执行成功，则系统会返回以下信息：

(所影响的行数为1行)

在上面的程序中，首先创建了新的视图“v_学生表_052005”，然后将性别为“男”的学生姓名成功修改为“王利”。在企业管理器中打开该视图，显示结果如图7-22所示。图7-22通过视图更新数据后的输出结果窗口7.4.3利用视图删除数据利用视图可以删除数据记录，但应该注意的是，删除的只是数据库的数据表中的数据记录。

1.利用企业管理器在视图中删除记录具体方法是：在企业管理器中打开要删除记录的数据表对应的视图，在返回的数据记录窗口中直接删除。

2.利用Transact-SQL语句通过视图删除记录利用视图删除记录时可以直接利用Transact-SQL语言的DELETE命令删除视图中的记录。但应注意，必须用指定的视图中定义过的字段删除记录。【例7-10】利用视图“v_学生表_052005”先插入一条记录，然后删除此条记录。程序清单如下：

USEcollegeGOInsertinto软件男生信息

Values(‘05209031’,‘刘法’,‘男’,‘1987-04-15’,‘052005’,‘55621451’,‘周家嘴路556弄33号304室',)GODELETEFROMv_学生表_052005WHERE学号='05209031'

在查询分析器中执行上面的程序，程序会先插入一条学号为“05209031”的新记录，然后通过DELETE语句从视图中删除这条记录，即删除数据表中的相应记录。7.4.4利用视图简化数据查询

1.减少字段名的输入如果我们经常要显示一个数据表中的部分列，则可以使用：

select字段1，字段2，字段3，…，字段nfrom表但是，每次输入很多字段名会很不方便，很容易出错，特别是字段很多时更加麻烦，为了节省时间简化操作，可以把select语句一次正确地输入到视图语句中，以后只要使用下列简单语句即可。

select*from视图

2.控制数据的显示范围在大量的数据中，如果用户要显示具有某种特征的数据，则可以使用视图来简化查询。比如，在学生成绩表中，要看哪些学生需要补考，要查不及格的比率等，就需要建一个“不及格成绩”的视图，在这个视图中显示的全是不及格的成绩记录。如果考试及格或补考及格，则这些记录就不会在视图中显示。7.4.5利用视图简化编程当我们编写多表联接查询的Transact-SQL语句时，由于表多、字段多，因此常常写错字段名或者虽然写对字段名但放错了表名，造成多行语句的调试出错。如果我们借用企业管理器中创建视图的工具，那么就会发现可以避免麻烦，从而可以很方便地编写程序。具体的方法如下：

(1)从企业管理器打开创建视图的界面，如图7-23所示。

(2)点击工具栏上的“添加表”按钮，选中要用的表或视图，再按“添加”按钮，如图7-24所示。选中多个表或视图后，关闭“添加表”对话框，如图7-25所示。图7-23创建视图的界面图7-24“添加类”对话框图7-25选择了多个表或视图的界面(3)如果是有主键外键关系的表，就已经有了中间的联接，从代码框来看，就有了“INNERJOIN”和“ON”表达式；如果两个表中间没有联接，代码框显示“CROSSJOIN”，则需要用鼠标点中一个表的某个字段拖到另一表的某个字段，建立联系的两个字段必须类型相同，内容相同，这样就建立了“INNERJOIN”和“ON”表达式。如果是外联接，则需要将鼠标右击两表之间的连线，显示属性页后，选择左外联接、右外联接或全外联接。

(4)再到两个表中按顺序点中各字段，就得到了代码框的全部代码，如图7-26所示。

(5)不用保存视图就可复制代码，这样就完成了多表联接查询的编程。这样，我们编写和调试多表联接的Transact-SQL语句时，可以避免使用键盘输入，只要点击鼠标就能一次成功。图7-26产生联接的全部程序代码7.4.6利用视图保证数据安全

1.设置“只读”视图在创建视图时，只给一般用户设置SELECT权限，不设置增、删、改的权限(具体设置在以后的数据库安全章节中再详细介绍)。这样，一般用户就只能从视图读数据，而不能写数据了。

2.使用加密并修改字段名在创建视图时，使用WITHENCRYPTION选项，并且使用与数据表中不同的字段名，用户就无法知道视图来源于什么表，字段名分别是什么。这样，可以使用数据，但找不到数据表。3.取数据表的子集通过取数据表的子集来创建视图，可以把需要保密的数据字段和数据行隐藏起来。加上禁止一般用户使用数据表，而只允许使用视图，这样可以使一般用户局限于了解不重要的字段信息和数据行。取数据表的子集的方法是：在SELECT后面去除要保密的字段，在WHERE后面加上限制条件。比如，在订单表中限制订购数量小于500，这样订购数量上万的订单数据行在视图中就找不到了。7.5索引简介7.5.1索引的概念数据库中的索引是一个列表，这个列表中包含了某个表一列或者若干列值的集合，以及这些值的记录在数据表中存储的物理地址。数据库的索引与书籍的目录类似。如果想在一本书中快速查找所需的信息，则可以利用书籍的目录快速定位，而无需阅读整本书。类似地，在数据库中，如果想在某个表中快速查找满足条件的记录，则可以创建索引。索引使数据库程序无需对整个表进行扫描，就可以在其中找到所需数据。

当SQLServer进行数据查询时，查询优化器会自动计算现有的几种执行查询方案，看哪种方案的开销最小，速度最快，SQLServer就会按照该方案查询。如果没有建立索引，则在数据库表中查询符合某种条件的记录时，系统会从第一条记录开始，对表中的所有记录进行逐条扫描。如果有索引存在，则可以通过索引快速地找到查询的结果。扫描整个表格从存储表格的起始地址开始，依次比较记录，直至找到位置。通过索引查找时，因为索引是有序排列的，所以可以通过高效的有序查找算法(如折半查找等)找到索引项，再根据索引项中记录的物理地址找到查询结果的存储位置。7.5.2索引的优点使用索引可以大大提高系统的性能，其具体表现如下：

(1)可以大大加快数据检索速度。

(2)通过创建唯一索引，可以保证数据记录的唯一性。

(3)在使用ORDERBY和GROUPBY子句检索数据时，可以显著减少查询中分组和排序的时间。

(4)使用索引可以在检索数据的过程中使用优化隐藏器，从而提高了系统的性能。

(5)可以加速表与表之间的联接，这一点在实现数据的参照完整性方面有特别的意义。7.5.3索引的分类

1.聚集索引

聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引是数据的物理排序。一个表只能有一个聚集索引。

2.非聚集索引非聚集索引具有与表的数据完全分离的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引中存储了组成非聚集索引的关键字的值和行定位器。3.聚集索引和非聚集索引的性能比较

(1)每个表只能有一个聚集索引，因为一个表中的记录只能以一种物理顺序存放。但是，一个表可以有不止一个非聚集索引。

(2)从建立了聚集索引的表中取出数据要比建立了非聚聚集索引的表快。

(3)非聚集索引需要大量的硬盘空间和内存。

(4)非聚集索引可以提高从表中取数据的速度，也会降低向表中插入和更新数据的速度。7.5.4索引的应用条件不是在任何查询中都需要建立索引。索引带来的查找效率提高是有代价的，因为索引也要占用存储空间，而且为了维护索引的有效性，在向表中插入新的数据或者更新数据时，数据库还要执行额外的操作来维护索引。所以，过多的索引不一定能提高数据库性能，必须科学地设计索引，才能带来数据库性能的提高。建立索引的一般原则如下：

(1)对数据记录多的表要建索引，而数据记录少的表不需要建索引。

(2)对经常用来检索的字段要建立索引。

(3)对数据表中的主键要建立索引。

(4)对数据表中的外键要建立索引。

(5)对经常用于联接的字段要建立索引。7.6索引的创建和使用7.6.1系统自动创建索引在SQLServer2000中，索引可以由系统自动创建，也可以由用户手工创建。系统在创建表中对象时可以附带地创建新索引，例如新建表时，可以创建主键或唯一性约束，同时就创建了索引。在查询分析器中，可以使用系统存储过程sp_helpindex来查看索引。对数据表中的某个字段设置主键约束时，系统会在该字段上自动创建唯一索引，该索引可以是聚集的，也可以是非聚集的。系统自动创建的索引名也会因为创建主键的场所和方法不同而有所不同。

如果在企业管理器中设置主键，则系统会自动创建一个唯一的非聚集索引，索引名为“PK_表名”。如果在查询分析器中使用Transact-SQL语句添加主键约束，则也会创建一个唯一索引，但索引名为“PK_表名_XXXXXXXX”，其中X是系统自动生成的数字或英文字母。这个索引可以是聚集的，也可以是非聚集的，取决于在PRIMARYKEY后面使用的关键字。如果使用NONCLUSTERED关键字，则会生成非聚集的唯一索引；如果使用CLUSTERED关键字，则会生成聚集的唯一索引。不使用关键字时，如果此表存在聚集索引，则生成非聚集的唯一索引，否则生成聚集的唯一索引。在查询分析器中执行上面的程序会创建新的数据表，系统同时自动创建了唯一聚集索引。

除了系统自动生成的索引外，用户也可以根据实际需要使用以下几种方法创建索引。

(1)利用企业管理器直接创建索引。

(2)利用Transact-SQL语句中的CREATEINDEX命令创建索引。

(3)利用企业管理器中的索引向导创建索引。

(4)利用企业管理器中的索引优化向导创建索引。注意：只有表或视图的所有者才能为表创建索引，并且可以随时创建索引，无论表中是否有数据。此外，还可以通过指定限定的数据库名称为另一个数据库中的表或视图来创建索引。7.6.2通过企业管理器创建索引使用企业管理器直接创建索引的具体操作步骤如下：

(1)在企业管理器中展开指定的服务器和数据库，选择要创建索引的表，选中数据库服务器下的college数据库，并右击其中的某一个数据表，从弹出的快捷菜单中依次选择“所有任务”→“管理索引”选项，如图7-27所示。

(2)在出现的“管理索引”对话框中，可以选择要处理的数据库和表，显示在“现有索引”区域中的是现存的索引名称、是否聚集以及对应的字段信息。另外，在该对话框中还可以执行编辑索引属性以及删除索引的操作，如图7-28所示。图7-27选择“管理索引”选项图7-28“管理索引”对话框(3)选择college数据库中的成绩表数据表，然后单击“新建”按钮，出现“新建索引”对话框，如图7-29所示。

(4)在“索引名称”文本框中输入新建索引的名称，在下面的复选框中选择用于创建索引的字段。在该对话框中可以设定索引的属性，例如是否聚集、是否唯一；还可以建立复合索引，指定填充度属性。这里写入索引名称为“成绩索引”，选中“总评成绩”字段前面的复选框，并选中“排序次序”列中对应“总评成绩”字段的复选框，使成绩按降序进行排序。

(5)选择完成后单击“确定”按钮，即可生成新的索引。单击“取消”按钮，则取消本次新建索引的操作。创建索引后的窗口如图7-30所示。图7-29“新建索引”对话框图7-30创建索引后的窗口7.6.3通过查询分析器创建索引利用Transact-SQL语句中的CREATEINDEX命令可以创建索引，既可以创建一个可改变表的物理顺序的聚集索引，也可以创建提高查询性能的非聚集索引。其语法形式如下：

CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view)(column[ASC|DESC][,...n])[WITH[PAD_INDEX][[,]FILLFACTOR=fillfacfor][[,]IGNORE_DUP_KEY][[,]DROP_EXISTING][[,]STATISTICS_NORECOMPUTE][[,]SORT_IN_TEMPDB]][ONfilegroup]

其中，各参数的说明如下：● UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。当列包含重复值时，不能创建唯一索引。如要使用此选项，则应确定索引所包含的列均不允许为NULL值，否则在使用时会经常出错。● CLUSTERED：用于指定创建的索引为聚集索引。如果此选项默认，则创建的索引为非聚集索引。● NONCLUSTERED：用于指定创建的索引为非聚集索引。其索引数据页包含了指向数据库中实际的表数据页的指针。● index_name：用于指定所创建的索引名称。索引名称在一个表或视图中必须唯一，但在数据库中不必唯一。索引名必须遵循SQLServer2000中标识符的命名规则。● table：用于指定创建索引的表名称。必要时可以选择指定的数据库名称和所有者名称。● view：用于指定创建索引的视图名称。必须使用SCHEMABINDING选项定义视图，才能在视图上创建索引。● ASC|DESC：用于指定某个具体索引列的升序或降序排序方向。默认值为升序(ASC)。● column：用于指定被索引的列。指定两个或者多个列名组成一个索引时，可以为指定列的组合值创建组合索引，在table后的圆括号中列出组合索引中要包括的列(按排序优先级排列)，这种索引称为复合索引。一个索引中最多可以指定16个列，但列的数据类型的长度之和不能超过900个字节。● PAD_INDEX：用于指定索引中间级每个页(节点)上保持开放的空间。无论FILLFACTOR的值有多小，中间级索引页上的行数永远都不会小于两行。PAD_INDEX选项只有在指定了FILLFACTOR时才有用，因为PAD_INDEX使用由FILLFACTOR所指定的百分比。默认情况下，给定中间级页上的键集，SQLServer将确保每个索引页上的可用空间至少可以容纳一个索引允许的最大行。如果FILLFACTOR指定的百分比不够大，则无法容纳一行，SQLServer将在内部使用允许的最小值替代该百分比。● FILLFACTOR=fillfactor：在创建索引时用于指定每个索引页的数据占索引页大小的百分比，fillfactor的值为1～100，它同时指出了索引页保留的自由空间占索引页大小的百分比。对于那些频繁进行大量数据插入或者删除的表，在建立索引时应该为将来生成的索引数据预留较大的空间，即将fillfactor设得较小，否则，索引页会因数据的插入而很快填满，并产生分页，而分页会大大增加系统的开销。如果设得过小，则又会浪费大量的磁盘空间，降低查询性能。因此，对于此类表，通常设一个大约为10的fillfactor。对数据不更改、高并发、只读的表，fillfactor可以设到95以上，甚至100。如果没有指定此选项，则SQLServer默认值为0。0是个特殊值，与fillfactor中的其他小值的意义不同，其叶节点被完全填满，而在索引页中还有一些空间。可以用存储过程sp_configue改变默认的fillfactor值。● IGNORE_DUP_KEY：当向包含于一个唯一的聚集索引中的列中插入重复数据时，用于控制SQLServer所作的反应。如果为索引指定了IGNORE_DUP_KEY选项，并且执行了创建重复键的INSERT语句，则SQLServer将发出警告消息，并跳过此行数据的插入，继续执行下面的插入数据的操作。如果没有为索引指定IGNORE_DUP_KEY，则SQLServer会发出一条警告消息，并回滚整个INSERT语句。● DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。删除聚集索引会导致所有的非聚集索引被重建，因为需要用行指针来替换聚集索引键。如果再重建聚集索引，那么非聚集索引又会重建一次，以便用聚集索引键来替换行指针。使用DROP_EXISTING选项可以使非聚集索引只重建一次。● STATISTICS_NORECOMPUTE：用于指定过期的索引统计不自动重新计算。若要恢复自动更新统计，则可以手动执行没有NORECOMPUTE子句的UPDATESTATISTICS命令。● SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果存储在tempdb数据库中。如果tempdb数据库与用户数据库位于不同的磁盘设备上，则使用此选项可以减少创建索引所需的时间，但会增加创建索引时使用的磁盘空间。● ONfilegroup：用于指定存放索引的文件组。该文件组必须已经通过执行CREATEDATABASE或ALTERDATABASE创建。【例7-11】使用CREATEINDEX语句为“学生表”创建一个非聚集索引，索引字段为“姓名”，索引名为“i_学生姓名”。程序清单如下：

USEcollegeGOCREATEINDEXi_学生姓名

ON学生表(姓名)【例7-12】新建一个数据表，名称为“电脑设备”，为此表创建一个唯一的聚集索引，索字段为“设备编号”，索引名为“i_电脑设备”。程序清单如下：

USEcollegeGOCREATETABLE电脑设备(编号int,设备名称char(10),使用时间smalldatetime)GOCREATEUNIQUECLUSTEREDINDEXi_电脑设备ON电脑设备(编号)WITHPAD_INDEX,FILLFACTOR=40,IGNORE_DUP_KEY,STATISTICS_NORECOMPUTE【例7-13】使用性别字段和出生年月字段，为学生表创建一个复合索引。程序清单如下：

USEcollegeGOCREATEINDEXI_学生表

on学生表(性别,出生年月)WITHPAD_INDEX，

FILLFACTOR=