(数据库应用技术)第5章

1、第5章 数据库对象的操作5.1 数据类型 5.2 表操作 5.3 视图操作 5.4 索引操作 5.5 存储过程 5.6 触发器 5.7 图 表 上一章返回目录5.1 数据类型 5.1.1 系统数据类型5.1.2 自定义数据类型 5.1.1 系统数据类型 1. 整型数据类型2. 浮点数据类型 3. 字符数据类型 4. 日期和时间数据类型 5. 文本和图形数据类型 6. 货币数据类型 7. 位数据类型 8. 二进制数据类型 9. 特殊数据类型 10. 新增数据类型1. 整型数据类型整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。 int（integ

2、er）：int（或integer）数据类型可以存储从-231（-2，147，483，648）到231-1（2，147，483，647）范围之间的所有正负整数。 Smallint：可以存储从-215（-32，768）到215-1范围之间的所有正负整数 。Tinyint：可以存储从0到255范围之间的所有正整数。 2. 浮点数据类型 浮点数据类型用于存储十进制小数。浮点数值的数据在SQL Server中采用只入不舍的方式进行存储 。Real：可以存储正的或者负的十进制数值，最大可以有7位精确位数。 Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308。 Decima

3、l和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。 3. 字符数据类型 字符数据类型可以用来存储各种字母、数字符号和特殊符号。 Char：其定义形式为char（n），每个字符和符号占用一个字节的存储空间。 Varchar：其定义形式为varchar（n）。用char数据类型可以存储长达255个字符的可变长度字符串 。Nchar：其定义形式为nchar（n）。 Nvarchar：其定义形式为nvarchar（n）。 4. 日期和时间数据类型 Dat

4、etime：用于存储日期和时间的结合体 。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间 。Smalldatetime：与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年6月6日内的日期。 5. 文本和图形数据类型 Text：用于存储大量文本数据，其容量理论上为1到231-1（2，147，483，647）个字节，但实际应用时要根据硬盘的存储空间而定。 Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。Image：用于存

5、储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。6. 货币数据类型 Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808到922337213685477.5808，精度为货币单位的万分之一。Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。7. 位数据类型 Bit：称为位数据类型，其数据有两种取值：0和1，长度为1字节。 8. 二进制数据类

6、型Binary：其定义形式为binary(n)，数据的存储长度是固定的，即n+4字节，当输入的二进制数据长度小于n时，余下部分填充0。 Varbinary：其定义形式为varbinary(n)，数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。其它含义同binary。9. 特殊数据类型 Timestamp：亦称时间戳数据类型，它提供数据库范围内的唯一值，反应数据库中数据修改的相对顺序，相当于一个单调上升的计数器。 Uniqueidentifier：用于存储一个16字节长的二进制数据类型，它是SQL Server根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代

7、码（Globally Unique Identifier，简写为GUID）。10. 新增数据类型 Bigint：用于存储从-263（-9，223，372，036，854，775，807）到263-1（9，223，372，036，854，775，807）之间的所有正负整数。 sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据。table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。5.1.2 自定义数据类型 1. 使用企业管理器（Enterprise M

8、anager）创建用户自定义数据类型。2. 利用系统存储过程创建用户自定义数据类型 。系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径，其语法形式如下：sp_addtype typename= type,phystype= system_data_type, nulltype= null_type, owner= owner_name例子5-1：自定义一个地址数据类型。 exec sp_addtype address, varchar(80), not null其运行结果如下：（1 row(s) affected）type added.例子5-2：删除自定义的

9、生日数据类型。 exec sp_droptype birthday其运行结果如下：（1 row(s) affected）（0 row(s) affected）Type has been dropped.5.2 表操作 5.2.1 创建表5.2.2 增加、删除和修改字段 5.2.3 创建、删除和修改约束 5.2.4 查看表格 5.2.1 创建表在SQL Server 2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。 SQL Server 2000提供了两种方法创建数据库表，第一种方法是利用企业管理器（Enterprise

10、 Manager）创建表；另一种方法是利用Transact-SQL语句中的create命令创建表。1. 利用Enterprise Manager创建表 在Enterprise Manager中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择新建表选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。 然后根据提示进行设置。2. 利用create命令创建表 其语法形式如下：CREATE TABLE d

11、atabase_name. owner .| owner. table_name( | column_name AS computed_column_expression| ，n) ON filegroup | DEFAULT 创建表的各参数的说明如下： database_name：用于指定在其中创建表的数据库名称。 owner：用于指定新建表的所有者的用户名 。table_name：用于指定新建的表的名称。 column_name：用于指定新建表的列的名称。 computed_column_expression：用于指定计算列的列值的表达式。 ON filegroup | DEFAULT：用

12、于指定存储表的文件组名。 TEXTIMAGE_ON：用于指定 text、ntext 和 image 列的数据存储的文件组。 data_type：用于指定列的数据类型 。DEFAULT：用于指定列的缺省值。 constant_expression：用于指定列的缺省值的常量表达式 。IDENTITY：用于指定列为标识列。 Seed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。 NOT FOR REPLICATION：用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用，即不足的生成列值，使得复制的数据行保持原来的列值。ROWGUIDCOL：用于指定

13、列为全球唯一鉴别行号列 。COLLATE：用于指定表使用的校验方式。 column_constraint和table_constraint：用于指定列约束和表约束。 例子5-3：创建了一个雇员信息表 其SQL语句的程序清单如下： CREATE TABLE employee ( number int not null, name varchar(20) NOT NULL, sex char(2) NULL, birthday datetime null, hire_date datetime NOT NULL DEFAULT (getdate() professional_title varch

14、ar(10) null, salary money null, memo ntext null)5.2.2 增加、删除和修改字段 利用企业管理器增加、删除和修改字段 。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择设计表选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。 利用Transact-SQL语言中的alter table命令增加、删除和修改字段。利用Transact-SQL语言中的alter table命令增加、删除和修改字段的各参数的说明如下： table：用于指定要修改的表的名称。 AL

15、TER COLUMN：用于指定要变更或者修改数据类型的列。column_name：用于指定要更改、添加或删除的列的名称。 new_data_type：用于指定新的数据类型的名称。 precision：用于指定新的数据类型的精度。 scale：用于指定新的数据类型的小数位数。 NULL | NOT NULL：用于指定该列是否可以接受空值。 ADD | DROP ROWGUIDCOL ：用于指定在某列上添加或删除 ROWGUIDCOL 属性。 ADD：用于指定要添加一个或多个列定义、计算列定义或者表约束。computed_column_expression：用于指定一个计算列的计算表达式。 WIT

16、H CHECK | WITH NOCHECK：用于指定已经存在于表中的数据是否需要使用新添加的或者刚启用的 FOREIGN KEY 或 CHECK 约束进行验证。 DROP CONSTRAINT constraint_name | COLUMN column_name ：用于指定从表中删除的约束或者列的名称。 CHECK | NOCHECK CONSTRAINT：用于指定启用或禁用FOREIGN KEY或者CHECK约束。ALL ：用于指定使用 NOCHECK 选项禁用所有的约束，或者使用 CHECK 选项启用所有约束。ENABLE | DISABLE TRIGGER：用于指定启用或禁用触发器

17、。当一个触发器被禁用时，它对表的定义依然存在；然而，当在表上执行 INSERT、UPDATE 或 DELETE 语句时，触发器中的操作将不执行，除非重新启用该触发器。ALL ：用于指定启用或禁用表中所有的触发器。trigger_name ：指定要启用或禁用的触发器名称。例子5-4：创建了一个雇员信息表 其SQL语句的程序清单如下：create table employees(id char(8) primary keyname char(20) not null,department char(20) null,memo char(30) nullage int null,)alter tab

18、le employees add salary int null drop column agealter column memo varchar(200) null5.2.3 创建、删除和修改约束 在SQL Server 2000中有5种约束：主键约束（primary key constraint）唯一性约束（unique constraint）检查约束（check constraint）缺省约束（default constraint）外部键约束（foreign key constraint）1. 主键约束 主键的添加、删除和修改操作方法有两种：（一）企业管理器操作法，在企业管理器中，用右键

19、单击要操作的数据库表，从弹出的快捷菜单中选择设计表选项 ，然后根据提示操作。（二）Transact-SQL语句操作法。 其语法形式如下：CONSTRAINT constraint_name PRIMARY KEY CLUSTERED|NONCLUSTERED （column_name,n）主键约束各参数的说明如下 constraint_name：用于指定约束的名称，约束的名称在数据库中应该是唯一的。如果不指定，则系统会自动生成一个约束名。LUSTERED|NONCLUSTERED：用于指定索引的类型，即聚簇索引或者非聚簇索引，CLUSTERED为默认值。column_name：用于指定主键的列

20、名。主键最多由16个列组成。例子5-5 在执行创建产品信息表的操作时，指定产品编号为主键值，并且创建一个聚簇索引。create table products(id char(10) not null,name char(20) not nullprice money default 0.01quantity smallint nullconstraint pk_id primary key clustered (id)2. 唯一性约束唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下几个因素： 使用唯一性约束的字段允许为空值。一个表

21、中可以允许有多个唯一性约束。可以把唯一性约束定义在多个字段上。唯一性约束用于强制在指定字段上创建一个唯一性索引。 缺省情况下，创建的索引类型为非聚簇索引。创建和修改唯一性约束的操作方法创建和修改唯一性约束的操作方法有两种：（一）企业管理器操作法，通过企业管理器可以完成创建和修改唯一性约束的操作。 （二）Transact-SQL语句操作法。CONSTRAINT constraint_name UNIQUE CLUSTERED|NONCLUSTERED（column_name,n）3. 检查约束 一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。一个表中可以定义多个检

22、查约束。每个CREATE TABLE语句中每个字段只能定义一个检查约束。在多个字段上定义检查约束，则必须将检查约束定义为表级约束。当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。检查约束中不能包含子查询。创建检查约束常用的操作方法有两种：1.企业管理器操作法。使用企业管理器创建检查约束，与创建唯一性约束类似 。2.Transact-SQL语句操作法。检查约束的Transact-SQL语句操作法，其语法形式如下：CONSTRAINT constraint_name CHECK NOT FOR REPLICATION (logical_expression)例子5-7： 创建了一

23、个学生信息表，其中输入性别字段值时，只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似01080798654之类的数据，而不能随意输入其他数据。create table student(Id char(8)name char(8)sex char(2)phonenum intconstraint chk_sex check（sex in （F,M）Constraint chk_phonenum check（phonenum like (010)0-90-90-90-9 0-90-90-90-9）4. 缺省约束使用缺省约束时，应该注意以下几点：每个字段只能定义一个

24、缺省约束。如果定义的缺省值长于其对应字段的允许长度，那么输入到表中的缺省值将被截断。不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。如果字段定义为用户定义的数据类型，而且有一个缺省绑定到这个数据类型上，则不允许该字段有缺省约束。5. 外部键约束外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。当使用外部键约束时，应该考虑以下几个因素：外部键约束提供了字段参照完整性。外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。外部键约束不能自动创建索引，需要用户手动创建。 用户想要修改外部键约束的数据，必须有对外部键约

25、束所参考表的SELECT权限或者REFERENCES权限。参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句。一个表中最多可以有31个外部键约束。在临时表中，不能使用外部键约束。主键和外部键的数据类型必须严格匹配。5.2.4 查看表格 查看表格的定义2. 查看表格中的数据 3. 查看表格与其它数据库对象的依赖关系 4. 利用系统存储过程Sp_help查看表的信息 例子5-10： 显示当前数据库中所有对象的信息；显示表word的信息。在查询分析器（Query Analyzer）中它们对应的语句和运行结果如图5-24(a)和图5-24(b)所示。图5-24(a) 当前数据

26、库对象显示窗口 图5-24(b) 表word显示窗口5.2.5 删除表1. 利用企业管理器删除表在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“除去表”选项，则会出现除去对象对话框。单击“全部删除”按钮，即可删除表。 2. 利用DROP TABLE语句删除表DROP TABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。DROP TABLE语句的语法形式如下：DROP TABLE table_name5.3.1 创建视图5.3.2 修改和重命名视图5.3.3 查看视图信息、删除视图5.3.4 通过视图修改记录5.3 视图操作视

27、图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。 视图的概念：使用视图的优点和作用 可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。视图可以使用户只关心他感兴趣的某些特定数据和他们

28、所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。视图大大地简化了用户对数据的操作。 视图可以让不同的用户以不同的方式看到不同或者相同的数据集。在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。 视图提供了一个简单而有效的安全机制。 5.3.1 创建视图创建视图时应该注意以下情况： 只能在当前数据库中创建视图。如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。不能在视图上创建索引，不能在规则、缺

29、省、触发器的定义中引用视图。当通过视图查询数据时，SQL Server要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。1. 利用企业管理器创建视图图5-26 ，图5-27(a) ，5-27(b) ，图5-28 ，图5-29 ，图5-30 ，是利用企业管理器创建视图的具体操作步骤，用户可根据图中提示创建视图。图5-26 选择新建视图选项对话框图5-27 (a) 新建视图对话框 图5-27(b) 添加表菜单 图5-28 添加表对话框图5-29 选择视图字段对

30、话框图5-30 视图属性对话框 2. 利用Transact-SQL语句中的CREATE VIEW命令创建视图使用Transact-SQL语句中的CREATE VIEW创建视图的语法形式如下：CREATE VIEW . . view_name ( column ,.n ) WITH ,.n AS select_statement WITH CHECK OPTION := ENCRYPTION | SCHEMABINDING | VIEW_METADATA 使用Transact-SQL语句中的CREATE VIEW创建视图的参数说明如下：view_name用于指定视图的名称，column用于指定视

31、图中的字段名称。WITH ENCRYPTION表示 SQL Server 加密包含 CREATE VIEW 语句文本在内的系统表列。select_statement用于创建视图的SELECT语句，利用SELECT命令可以从表中或者视图中选择列构成新视图的列。 WITH CHECK OPTION用于强制视图上执行的所有数据修改语句都必须符合由 select_statement 设置的准则。SCHEMABINDING表示在select_statement语句中如果包含表、视图或者引用用户自定义函数，则表名、视图名或者函数名前必须有所有者前缀。VIEW_ METADATA表示如果某一查询中引用该视图

32、且要求返回浏览模式的元数据时，那么SQL Server将向DBLIB和OLE DB APIS返回视图的元数据信息。例子5-12： 选择表student和teacher中的部分字段和记录创建视图，并且限制表student中的记录只能是名称为“张三”的记录集合，视图定义为view_part，其程序清单如下：create view view_partas Select ,student.age,student.sex,,age,salaryfromstudent,teacherwhere =张三3. 利用向导创建视图 图5-31

33、 -图5-38 是利用向导创建视图 的具体步骤。图5-31 选择工具菜单中的向导命令 图5-32 欢迎使用创建视图向导对话框图5-33 选择数据库对话框图5-34 选择对象对话框图5-35 选择字段对话框图5-36 定义限制对话框图5-37 命名视图对话框图5-38 完成创建视图向导对话框5.3.2 修改和重命名视图 1. 修改视图（1）利用企业管理器 修改视图。（2）使用ALTER VIEW语句修改视图 。ALTER VIEW view_name (column,.n) WITHENCRYPTION ASselect_statement WITH CHECK OPTION 2. 重命名视图

34、（1）利用企业管理器重命名视图 。（2）可以使用系统存储过程sp_rename修改视图的名称，该过程的语法形式如下： sp_rename old_name,new_name例子5-14：把视图v_all重命名为v_part，其程序清单如下：sp_rename v_all,v_part5.3.3 查看视图信息、删除视图 1. 查看视图信息每当创建了一个新的视图后，则在系统说明的系统表中就定义了该视图的存储，因此，可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义，使用sp_depends显示该视图所依赖的对象。它们的语法形式分别如下：（1）sp_h

35、elp 数据库对象名称（2）sp_helptext 视图（触发器、存储过程）（3） sp_depends 数据库对象名称使用SQL Server 查询分析器和企业管理器来显示视图使用SQL Server 查询分析器（Query Analyzer）可以方便地显示视图信息，如图5-42所示，该对话框显示了使用sp_helptext存储过程显示视图v_employee的特征。另外，也可以使用企业管理器来显示视图的定义，如图5-43所示。 利用企业管理器也可以查看视图的输出数据，如图5-44所示。 图5-42 显示视图信息对话框 图5-43 视图属性对话框图5-44 视图输出数据窗口2. 删除视图 （

36、1）使用企业管理器删除视图的操作方法为如图5-45和图5-46所示。 （2）使用Transact-SQL语句DROP VIEW删除视图的语法形式如下：DROP VIEW view_name ,n可以使用该命令同时删除多个视图，只需在要删除的视图名称之间用逗号隔开即可。例子5-15：同时删除视图v_student和v_teacher，其程序清单如下：drop view v_student,v_teacher图5-45 除去对象对话框图5-46 显示相关性对话框5.3.4 通过视图修改记录 使用视图修改数据时，需要注意以下几点： 修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多

37、个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段。如果在创建视图时指定了WITH CHECK OPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。如果视图引用多个表时，无法用DELETE命令删除数据。1. 插入数据记录 例子5-16：首先创建了一个新的视图v_employees，该视图基于表employees创建。create view v_employees(number, name, age, sex, salary)asse

38、lect number, name, age, sex, salary from employeeswhere name=张三然后，通过执行以下语句使用该视图向表employees中添加一条新的数据记录。Insert into v_employeesValues(001,李力,22,m,2000)2. 更新数据记录使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。例子5-19：创建了一个基于表employees的视图v_employees，然后通过该视图修改表employees中的记录。其程序清单如下：create view v_employeesas select * fr

39、om employeesupdate v_employeesset name=张然where name=张三3. 删除数据记录 使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。例子5-20：利用视图v_employees删除表employees中姓名为张然的记录。其程序清单为：delete from v_employeeswhere name=张然5.4 索引操作5.4.1 创建索引 5.4.2 查看、修改和删除索引一、什么叫索引 数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无

40、须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 二、索引的作用通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDER BY和GROUP BY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。 聚集索引与非聚集索引聚集索引对表的物

41、理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。 非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。 5.4.1 创建索引 SQL Server 2000创建索引的方法（1）利用企业管理器中的索引向导创建索引。（2）利用企业管理器直接创建索引。（3）利用Transact-SQL语句中的CREATE INDEX命令创建索引。（4）利用企业管理器中的索引优化向导创建索引。 1. 利用索引向导创建索引 如图5-47 -图5-52 所示。图

42、5-47 欢迎使用创建索引向导对话框图5-48 选择数据库和对象对话框图5-49 显示当前索引信息对话框图5-50 选择创建索引的列对话框图5-51 指定索引选项对话框图5-52 指定索引名称对话框2. 利用企业管理器直接创建索引其具体步骤如下：（）在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项（如图5-53所示），就会出现管理索引对话框，如图5-54所示。在该对话框中，可以选择要处理的数据库和表 。（）单击“新建”按钮，则出现新建索引对话框，如图5-55所示。 （）选择完成后单击“确定”按钮，即可生成新的索引；单击

43、“取消”按钮，则取消新建索引的操作。图5-53 选择管理索引选项对话框图5-54 管理索引对话框图5-55 新建索引对话框3. 利用Transact-SQL语句中的CREATE INDEX命令创建索引其语法形式如下：CREATE UNIQUE CLUSTERED| NONCLUSTERED INDEX index_name ON table | view ( column ASC | DESC ,.n ) withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING ,STATISTICS_NORECOMPUTE,SORT_I

44、N_TEMPDB ON filegroup CREATE INDEX命令创建索引各参数说明如下： UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。 CLUSTERED：用于指定创建的索引为聚集索引。 NONCLUSTERED：用于指定创建的索引为非聚集索引。index_name：用于指定所创建的索引的名称。 table：用于指定创建索引的表的名称。 view：用于指定创建索引的视图的名称。 ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。 Column：用于指定被索引的列。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。FILL

45、FACTOR = fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQL Server所作的反应。 DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。 STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。 SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在 tempdb 数据库中。 ON filegroup：用于指定存放索引的文件组。 例子5

46、-21：例子5-21：为表employees创建了一个唯一聚集索引，其程序清单如下：CREATE UNIQUE CLUSTERED INDEX number_ind ON employees (number)with pad_index, fillfactor=20, ignore_dup_key, drop_existing, statistics_norecompute例子5-22：为表employees创建了一个复合索引，其程序清单如下：create index employees_cpl_indon employees(name,age)with pad_index, fillfact

47、or=504. 利用索引优化向导创建索引索引优化向导可以完成以下几方面的工作： （1） 根据给定的工作负荷，通过使用查询优化器分析该工作负荷中的查询，为数据库推荐最佳索引组合。（2）分析所建议的更改将会产生的影响，包括索引的使用，查询在表之间的分布，以及查询在工作负荷中的性能。（3）推荐为执行一个小型的问题查询集而对数据库进行优化的方法。（4）通过设定高级选项如磁盘空间约束、最大查询语句数和每个索引最多对应字段数等，允许定制推荐方式。利用索引优化向导创建和优化索引的具体步骤如下： 如图5-56 -图5-65 所示。图5-56 欢迎使用索引优化向导对话框图5-57 选择服务器和数据库对话框图5-

48、58 连接服务器对话框图5-59 选择服务器对话框图5-61 修改缺省优化参数对话框图5-62 选择要优化的表对话框图5-63 索引建议对话框图5-64 索引分析对话框图5-65 完成索引优化向导对话框5.4.2 查看、修改和删除索引 1. 利用企业管理器查看、修改和删除索引要查看和修改索引的详细信息，可以在企业管理器中，展开指定的服务器和数据库项，用右键单击要查看的表，从弹出的快捷菜单中选择所有任务子菜单中的“管理索引”选项，则会出现管理索引对话框。选择要查看或者修改的索引，单击“编辑”按钮，就会出现修改索引对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需按下

49、“编辑SQL”按钮，即可出现编辑SQL脚本对话框，其中可以编辑、测试和运行索引的SQL脚本。要删除索引，可以在企业管理器中，从管理索引对话框中或者表的属性对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。2. 用系统存储过程查看和更改索引名称系统存储过程sp_helpindex可以返回表的所有索引信息，其语法形式如下：sp_helpindex objname=name其中objname=name参数用于指定当前数据库中的表的名称。另外，系统存储过程sp_rename可以用来更改索引的名称，其语法形式如下：sp_renameobjname=object_name,newname=new

50、_name , objtype = object_type 例子5-23：更改employees表中的索引employees_name_index名称为employees_name_ind，其程序清单如下：Exec sp_rename employees.employees_name_index, employees_name_ind, index3. 使用Transact-SQL语句中的DROP INDEX命令删除索引当不再需要某个索引时，可以将其删除，DROP INDEX命令可以删除一个或者多个当前数据库中的索引，其语法形式如下：DROP INDEX table.index | view.

51、index ,.n 其中，table | view用于指定索引列所在的表或索引视图；index用于指定要删除的索引名称。注意，DROP INDEX命令不能删除由CREATE TABLE或者ALTER TABLE命令创建的主键或者唯一性约束索引，也不能删除系统表中的索引。5.5 存储过程5.5.1 创建存储过程5.5.2 执行存储过程5.5.3 查看和修改存储过程5.5.4 重命名和删除存储过程 存储过程的概念SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。在SQL Server中存储过程分为两类：

52、即系统提供的存储过程和用户自定义的存储过程。 5.5.1 创建存储过程 在SQL Server中，可以使用三种方法创建存储过程 ：使用创建存储过程向导创建存储过程。利用SQL Server 企业管理器创建存储过程。使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 创建存储过程时，需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。1. 使用创建存储过程向导创建存储过程 在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导

53、”（如图3-1所示），则出现欢迎使用创建存储过程向导对话框，如图5-71所示。根据图5-71-图5-76提示可完成创建存储过程。图3-1 新建SQL Server组图5-71 欢迎使用创建存储过程向导对话框图5-72 选择数据库对话框 图5-73 选择数据库对象对话框图5-74 完成创建存储过程向导对话框图5-75 编辑存储过程属性对话框图5-76 编辑存储过程SQL对话框2. 使用SQL Server 企业管理器创建存储过程 （）在SQL Server企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”

54、选项，如图5-77所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图5-78所示。均会出现创建存储过程对话框，如图5-79所示。（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮，如图5-80所示。图5-77 选择新建存储过程对话框（1）图5-78 选择新建存储过程对话框（2）图5-79 新建存储过程对话框图5-80 设置权限对话框3. 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程创建存储过程前，应该考虑下列几

55、个事项： 不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。使用CREATE PROCEDURE创建存储过程的语法形式如下： CREATEPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION

56、 FORREPLICATION AS sql_statement .n 用CREATE PROCEDURE创建存储过程的语法参数的意义如下： procedure_name：用于指定要创建的存储过程的名称。 number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。 parameter：过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。 data_type：用于指定参数的数据类型。 VARYING：用于指定作为输出OUTPUT参数支持的结果集。 Default：用于指定参数的默认值。 OU

57、TPUT：表明该参数是一个返回参数。 RECOMPILE：表明 SQL Server 不会保存该存储过程的执行计划 。ENCRYPTION ：表示 SQL Server 加密了 syscomments 表，该表的text字段是包含 CREATE PROCEDURE 语句的存储过程文本。 FOR REPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。 AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类型的 Transact-SQL 语句。5.5.2 执行存储过程 直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下

58、：EXECUTE return_status=procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT,.n WITH RECOMPILE 例子5-27： 使用 EXECUTE 命令传递单个参数，它执行 showind 存储过程，以 titles 为参数值。showind 存储过程需要参数 (tabname)，它是一个表的名称。其程序清单如下：EXEC showind titles当然，在执行过程中变量可以显式命名：EXEC showind tabname = titles如果这是 isql

59、脚本或批处理中第一个语句，则 EXEC 语句可以省略：showind titles或者showind tabname = titles5.5.3 查看和修改存储过程 查看存储过程 存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。（1）使用企业管理器查看用户创建的存储过程 在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此

60、时便可以看到存储过程的源代码。 （2）使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_help objname= name参数name为要查看的存储过程的名称。 sp_helptext：用于显示存储过程的源代码 sp_helptext objname= name参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_depends objname=object参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前