一、实验大纲
实验目的：
1. 熟悉索引、存储过程的概念
2. 熟练掌握索引和存储过程的操作方法 实验要求：
1. 为职工表employee创建唯一索引取名为ix_name，要求姓名不能完全相同。 2. 使用唯一聚集索引ix_id，为salary表的eno列创建索引，并强调唯一性。 3. 删除索引ix_name。
4. 利用存储过程，给employee表添加一条财务科员工的信息。
5. 利用存储过程从employee、salary、dept表的连接中返回所有职工的姓名、科室、基本工资。
6. 利用存储过程查找“刘刚”的员工编号、所在科室。
7. 利用存储过程查找姓“李”的员工的员工编号、科室名称、基本工资。 8. 删除以上建立的所有存储过程 实验环境： SQL Server 2000 二、实验指导
说明：本示例用例的表结构如下所示：
Student (sno, sname, sex，sage，dept, oldgrade, advisor) Teacher (tno, tname, dept, salary, title) Course (cno, cname, descry, dept, credit) SC (sno, cno, grade)
以上数据库表的含义为： Student—学生：sno（学号），sname（学生姓名），sex（性别），sage(年龄)，dept（系），oldgrade（高考成绩），advisor（导师）
Teacher—教师：tno（教师编号），tname（教师姓名），dept（所在系），salary（工资），title（职称）
Course—课程：cno（课程号），cname（课程名），descry（课程说明），dept（开课系），credits（学分）
SC—成绩：sno（学号），cno（课程号），grade（成绩） 1．利用企业管理器创建和删除索引 （1）创建索引
在数据库关系图中选择要创建索引的表，右击该表，然后从快捷菜单中选择“索引/键”命令。 或为要创建索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择“索
图1 图2
引/键”命令。或在工具栏中选择
，弹出对话框如图1所示。
选择“新建”命令。“选定的索引”框显示系统分配给新索引的名称。如图2所示。在“列名”下选择要创建索引的列。可以选择多达 16 列。为获得最佳性能，最好只选择一列或两列。对所选的每一列，可指出索引是按升序还是降序组织列值。为索引指定任何其它需要的设置，然后单击“确定”按钮。当保存表或关系图时，索引即创建在数据库中。
（2）删除索引
在数据库关系图中选择要删除索引的表，右击该表，然后从快捷菜单中选择“属性”命令。
或为要删除索引的表打开表设计器，在表设计器中右击，然后从快捷菜单中选择\"属性\"命令。
选择“索引/键”选项卡。从“选定的索引”列表中选择要删除的索引。 选择“删除”按钮。
注意：选择“删除”按钮将导致一个无法撤消的操作，而且不保存对数据库关系图所做的所有其它更改。若要撤消该操作，请不保存更改即关闭当前的数据库关系图和所有其它打开的数据库关系图以及表设计器窗口。当保存表或关系图时，索引即从数据库中被删除。利用向导创建和删除索引
（1）创建索引
展开一个服务器组，再展开要在其中创建视图的服务器。展开“数据库”文件夹。选择需要建立索引的表，在工具栏上单击“运行向导”
命令。弹出如图3所示向导界面。选
择“创建索引向导”，“确定”进入创建索引欢迎界面如图4所示。
图3 图4
单击“下一步”进入选择表或视图界面，为索引选择对象如图5所示。选择数据库student中的course表。
图5 图6 单击“下一步”进入显示当前索引信息界面，如图6所示。
图7 图8
单击“下一步”进入选择列界面如图7所示。在相应列的□中选择是否包含在索引中。 单击“下一步”进入指定索引选项界面，如图8，为索引选择相应的属性。若要建立唯一索引，则选中“使其成为唯一索引”选项。
单击“下一步”进入索引创建正在完成界面，如图9所示。 单击“完成”，进入向导完成界面，提示创建索引成功，如图10所示。
图9 图10 （2）删除索引
打开已经创建索引表设计器，在表设计器中右击，然后从快捷菜单中选择“索引/键”命令。或在工具栏中选择
，弹出对话框删除对话框。选择要删除的索引，单击“删除”
按钮即可。
3.存储过程
（1）利用企业管理器创建存储过程
展开服务器组，然后展开服务器。展开“数据库”文件夹，再展开要在其中创建过程的数据库。右击“存储过程”，然后单击“新建存储过程”命令。或者，单击“存储过程”，选择菜单“操作”，然后单击“新建存储过程”，如图11所示。然后弹出新建存储过程对话框
如图12所示。
图11 图12 输入存储过程的文本。按 TAB 键可以缩进存储过程的文本。按下 CTRL+TAB 键或单击合适的按钮来退出文本框。
若要检查语法，单击“检查语法”命令。 若要设置权限，单击“权限”命令。 （2）用创建存储过程向导创建存储过程
展开一个服务器组，再展开要在其中创建视图的服务器。
在工具栏上单击“运行向导”
命令。展开“数据库”文件夹，如图13所示。
图13
选择“创建存储过程向导”命令。单击“确定”按钮。 按提示完成向导中的步骤。
4.利用T-SQL语言创建、删除索引和存储过程 （1）创建索引
创建索引的语法结构： 语法
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name ON { table | view } ( column [ ASC | DESC ] [ ,...n ] ) [ WITH < index_option > [ ,...n] ] [ ON filegroup ]
< index_option > ::= { PAD_INDEX |
FILLFACTOR = fillfactor | IGNORE_DUP_KEY | DROP_EXISTING |
STATISTICS_NORECOMPUTE | SORT_IN_TEMPDB }
参数说明：
UNIQUE：为表或视图创建唯一索引（不允许存在索引值相同的两行）。视图上的聚集索引必须是 UNIQUE 索引。
CLUSTERED：创建一个对象，其中行的物理排序与索引排序相同，并且聚集索引的最低一级（叶级）包含实际的数据行。一个表或视图只允许同时有一个聚集索引。
具有聚集索引的视图称为索引视图。必须先为视图创建唯一聚集索引，然后才能为该视图定义其它索引。
在创建任何非聚集索引之前创建聚集索引。创建聚集索引时重建表上现有的非聚集索引。
如果没有指定 CLUSTERED，则创建非聚集索引。
NONCLUSTERED：创建一个指定表的逻辑排序的对象。对于非聚集索引，行的物理排序独立于索引排序。非聚集索引的叶级包含索引行。每个索引行均包含非聚集键值和一个或多个行定位器（指向包含该值的行）。如果表没有聚集索引，行定位器就是行的磁盘地址。如果表有聚集索引，行定位器就是该行的聚集索引键。
其他参数说明祥见SQL Server联机丛书。 （2）删除索引
从当前数据库中删除一个或多个索引。
DROP INDEX 语句不适用于通过定义 PRIMARY KEY 或 UNIQUE 约束创建的索引（通过分别使用 CREATE TABLE 或 ALTER TABLE 语句的 PRIMARY KEY 或 UNIQUE 选项创建）。
删除索引的语法结构：
DROP INDEX 'table.index | view.index' [ ,...n ] 参数说明：
table | view：是索引列所在的表或索引视图。若要查看在表或视图上存在的索引列表，请使用 sp_helpindex 并指定表名或视图名称。表名和视图名称必须符合标识符规则。有关更多信息，请参见使用标识符。可以选择是否指定表或视图所有者名称。
index：是要除去的索引名称。索引名必须符合标识符的规则。 n：是表示可以指定多个索引的占位符。 [例1]创建唯一索引。
CREATE TABLE student_temp ( sno int NOT NULL, sname char(10), oldgrade int )；
CREATE UNIQUE CLUSTERED INDEX IX_sid ON student_temp(sno)；
CREATE UNIQUE INDEX IX_sname WITH PAD_INDEX,
FILLFACTOR = 80, IGNORE_DUP_KEY ON student_temp(sname)；
EXEC sp_helpindex student_temp；
[例2] 创建简单索引。 SET NOCOUNT OFF USE student
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'ix_sno') DROP INDEX student.ix_sno GO
USE student
CREATE INDEX ix_sno ON student(sno) GO
[例3] 使用唯一聚集索引。
该例为 teacher 表的 tno 列创建索引，并且强制唯一性。因为指定了 CLUSTERED 子句，所以该索引将对磁盘上的数据进行物理排序。
SET NOCOUNT ON USE student
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'teacher') DROP TABLE teacher GO
USE student
IF EXISTS (SELECT name FROM sysindexes WHERE name = 'ix_tno') DROP INDEX teacher.ix_tno GO
USE student GO
CREATE TABLE teacher (
tno int NOT NULL,
base_pay money NOT NULL,
commission decimal(2, 2) NOT NULL )
INSERT teacher
VALUES (1, 500, .10) INSERT teacher
VALUES (2, 1000, .05) INSERT teacher
VALUES (3, 800, .07) INSERT teacher
VALUES (5, 1500, .03) INSERT teacher
VALUES (9, 750, .06) GO
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEX ix_tno ON teacher (tno) GO
（3）创建存储过程
创建存储过程语法结构：
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]
参数说明祥见SQL Server联机丛书。 （4）执行存储过程
执行存储过程的语法结构： [EXEC[UTE]]
{[@return_status=] procedure_name [; number]}
[[@parameter =] {value | @variable [ OUTPUT] | [DEFAULT]} ][, . . . n] [WITH RECOMPILE] （5）删除存储过程
删除存储过程的语法结构：
DROP PROCEDURE { procedure } [ ,...n ]
[例4]创建存储过程。如果存在同名的存储过程，先删除之。
IF EXISTS(SELECT NAME FROM sysobjects WHERE name = 'Pinfo10000' AND type = 'P') DROP PROCEDURE Pinfo10000 GO
CREATE PROCEDURE Pinfo10000 AS SELECT * FROM teacher WHERE salary >=10000 ORDER BY salary DESC GO
//执行存储过程 EXEC Pinfo10000 GO
[例5]利用存储过程实现模糊查询,查找姓王同学的情况。 create procedure sp_stuname @s_name varchar(10) as select sno,sname,dept from student
where sname like @s_name go
//执行存储过程
exec sp_stuname '王%'
[例6]利用存储过程计算出’计算机系’学生的高考平均成绩。 create procedure sp_avgoldgrade @d_no char(20),@p_tot int output as select @p_tot=avg(oldgrade) from student
where dept=@d_no go
declare @avg_grade int
exec sp_avgoldgrade 计算机系, @avg_grade output select @avg_grade 三、实验报告模板
列出创建、删除索引；创建、删除关系图；创建、删除存储过程的实现方式； 写出创建和删除索引；创建和删除存储过程的命令语句； 显示实验数据和结果；
列出实验过程中遇到的问题及其解决方法。 declare @Subject Nvarchar(100);
declare @Subject1 Nvarchar(100);
select Subject,[From] from ServerSideGridTest inner join
CONTAINSTABLE(ServerSideGridTest,[Subject],@Subject,500) as k on OID=k.[key] union all
select Subject,[From] from ServerSideGridTest inner join
CONTAINSTABLE(ServerSideGridTest,[Subject],@Subject1,500) as k on OID=k.[key]
ALTER proc [dbo].[searchTest]
@Content1 nvarchar(50), @Content2 nvarchar(50) as
declare @Subject nvarchar(50) declare @from nvarchar(50) set @subject=@Content1; set @from=@Content2; declare @sql nvarchar(500)
set @sql='select * from ServerSideGridTest inner join
CONTAINSTABLE(ServerSideGridTest,[Subject],''(\"'+@Subject+'\") OR
(\"'+@from+'\")'',500) as k on OID=k.[key]
order by k.RANK DESC;' exec sp_executesql @sql
加强版
declare @Content1 nvarchar(50) declare @Content2 nvarchar(50) declare @ID int set @Content1='web' set @Content2='Page' set @ID=6
declare @Subject nvarchar(50) declare @from nvarchar(50) set @subject=@Content1; set @from=@Content2; declare @sql nvarchar(500)
set @sql='select * from ServerSideGridTest inner join
CONTAINSTABLE(ServerSideGridTest,([Subject]),''(\"'+@Subject+'\") OR (\"'+@from+'\")'',500) as k on OID=k.[key]
where OID='+convert(nvarchar,@ID)+' order by k.RANK DESC' exec sp_executesql @sql
因篇幅问题不能全部显示，请点此查看更多更全内容