数据库原理与SQL Server 第6章 全面掌握SQL Server 2000

第6章 全面掌握SQL Server 2000 6.1 提高查询速度—索引 6.2 定制数据—视图 6.3 定制功能—存储过程 6.4 自动数据处理—触发器 6.5 数据库原理(三) —数据库系统系统体系结构 实训 创建索引、视图、存储过程和触发器

6.1 提高查询速度—索引 6.1.1 索引概述 1. 索引+的概念 利用索引可以快速地访问数据库表中的特定信息。 2. 索引的种类 6.1 提高查询速度—索引 6.1.1 索引概述 1. 索引+的概念 利用索引可以快速地访问数据库表中的特定信息。 2. 索引的种类 聚集(簇)索引：索引顺序与数据物理顺序相同。表只能包含一个聚集索引。 非聚集(簇)索引：索引顺序与数据物理顺序不同。默认最多249个。

6.1.1 索引概述 3. 索引规则 (1)索引是非显示的，查询时自动调用。 (2)创建主键将自动创建惟一性聚集索引。 6.1.1 索引概述 3. 索引规则 (1)索引是非显示的，查询时自动调用。 (2)创建主键将自动创建惟一性聚集索引。 (3)创建惟一性键时，自动创建惟一性非聚集索引。 (4)索引可以提高查询数据的速度，但维护索引要占一定的时间和空间。 (5)常用查询字段应建立索引，域小字段不应建立索引。

6.1.2 创建索引 使用SQL语句 2. 使用SQL-EM

1. 使用SQL语句 CREATE [UNIQUE] [Clustered] INDEX <索引名>ON [<表名>](<列名>[DESC][,…]) 例6-1 使用SQL语句，对表C创建列cname惟一性非聚集索引。 脚本： CREATE UNIQUE INDEX ix_c ON c(cname) 例6-2 略。

2. 使用SQL-EM (1)启动SQL-EM，单击数据库中的“表”结点，指向右侧指定表，单击右键，选择“所有任务”→“管理索引”命令。 (2)单击“新建”按钮，打开“新建索引”对话框。 (3)在"列名"下选择要创建索引的列。可以选择多达 16 列。为获得最佳性能，最好只选择一列或两列。对所选的每一列，可指出索引是按升序还是降序组织列值。 (4)为索引指定其他需要的设置，然后单击“确定”按钮。

2. 使用SQL-EM 例6-3 使用SQL-EM创建表s列sname的非聚集索引。 (1)启动SQL-EM，单击左侧窗口数据库student中的“表”结点，指向右侧窗口中的表s，单击右键，选择“所有任务”→“管理索引”命令。 (2)单击“新建”按钮，打开“新建索引”对话框。 (3)在“索引名称”输入框中输入索引名称index_sname”,在列名框中选择需要创建索引的列“sname”,设置索引的其他选项。 (4)单击“确定”按钮，返回“管理索引”对话框。单击“关闭”按钮，完成创建索引。

6.1.3 删除索引 1.使用SQL语句 2.使用SQL-EM

1. 使用SQL语句 DROP INDEX <表名>.<索引名>[,…] 例6-4 删除对表c列cname的惟一性非聚集索引ix_c。 脚本： DROP index c.ix_c

2. 使用SQL-EM (1)启动SQL-EM，单击左侧窗口数据库中的“表”结点，指向右侧窗口中指定的表，单击右键，选择“所有任务”→“管理索引”命令。 (2)单击选中需要删除的索引，单击“删除”按钮。 (3)单击“是”按钮，指定索引将被删除。

2. 使用SQL-EM 例6-5 使用SQL-EM删除表s列sname的非聚集索引index_sname。 (1)启动SQL-EM，单击左侧窗口数据库student 中的“表”结点，指向右则窗口中的表“s”，单击 右键，选择“所有任务”→“管理索引”命令。 (2)单击选中需要删除的索引“index_sname”，单 击“删除”按钮。 (3)单击“是”按钮，指定索引将被删除。

6.1.4 查看所有索引 1.使用SQL语句 例6-6 查看表s的所有索引。 sp_helpindex [@objname=]<表名> 例6-6 查看表s的所有索引。 脚本： sp_helpindex ‘s’

2. 使用SQL-EM (1)启动SQL-EM，单击左侧窗口指定数据库结点。单击右键，选择“查看”→“任务板”命令。 (2)单击“表信息”选项卡，显示指定数据库所有表的所有索引信息。

6.1.5 全文索引 全文索引为在字符串数据中进行复杂的词搜索提供有效支持。 6.1.5 全文索引 全文索引为在字符串数据中进行复杂的词搜索提供有效支持。 全文索引包含在全文目录中。每个数据库可以包含多个全文目录。一个目录不能属于多个数据库，而每个目录可以包含一个或多个表的全文索引。一个表只能有一个全文索引，因此每个有全文索引的表只属于一个全文目录。

1. 启动Microsoft Search服务 (1)启动SQL-EM，单击展开左侧窗口指定的数据库服务器“支持服务”文件夹。 (2)指向右侧窗口中的“全文检索”图标，单击右键，打开快捷菜单，选择“启动”命令。

2. 建立全文目录 (1)启动SQL-EM，展开左侧窗口中要建立全文目录的数据库，单击“全文目录”结点。 (2)指向右侧窗口中的空白区域，单击右键，选择“新建全文目录”命令。 (3)在“名称”文本框中为全文索引目录指定一个名称，在“位置”文本框中输入存放全文目录的完整路径。单击“调度”选项卡。 (4)单击“新建目录调度”按钮，在“名称”文本框中指定调度的名称并设置其他选项。单击“确定”按钮。 (5)查看新建调度的信息，单击“确定”按钮，开始建立全文目录。

3. 建立全文索引 (1)启动SQL-EM，展开左侧窗口中指定的数据库，单击“表”结点。 (2)单击“下一步”按钮，从“惟一索引”下拉列表中选择一个基于单个字段所建立的惟一索引，单击“下一步”按钮。 (3)选择希望符合于全文查询条件的、基于字符类型的一个或多个字段。单击“下一步”按钮 。 (4)为该数据库选择一个已经存在的全文目录，用于存储全文索引。也可以选中“创建新目录”复选框新建一个全文目录来存储全文索引。单击“下一步”按钮 。 (5)选择一个已经存在的填充调度，也可以单击“新建表调度”按钮来新建一个填充调度。单击“下一步”按钮，完成全文索引的创建过程 。

4. 管理全文索引 修改： SQL-EM→指定表→单击右键→全文索引表→编辑全文索引 删除： 填充： SQL-EM→指定数据库→全文目录→单击右键→启动

5. 使用全文索引 1.使用CONTAINS SELECT <字段列表> FROM <表名> WHERE CONTAINS(<字段名>|*, '<搜索条件>') 2.使用FREETEXT WHERE FREETEXT(<字段名>|*,'<自由文本>')

5. 使用全文索引 例6-7 检索表s列address中包含“西安”的学生。 脚本：select * from s where contains(address, '西安') 例6-8 检索表s列address中包含“西安”或“北京”的学生。 脚本：select * from s where contains(address, '“西安“ or “北京“') 例6-9 检索表s列email中包含“net”或“com”的学生 。 脚本：select sname,sex,email from s where freetext(email, 'net com')

6.2 定制数据—视图 视图是数据库中非常重要的一种数据库对象。 6.2 定制数据—视图 视图是数据库中非常重要的一种数据库对象。 在SQL Server 2000的三层体系结构中，外模式对应于视图，模式对应于表。 SQL Server 2000的逻辑数据独立性正是通过视图技术实现的。

6.2.1 视图概述 1.概念 视图是若干表、视图上构造的虚拟表。 视图只存在结构，数据在运行时从表中生成。 2.作用 6.2.1 视图概述 1.概念 视图是若干表、视图上构造的虚拟表。 视图只存在结构，数据在运行时从表中生成。 2.作用 集中数据：将多个表中的列连接起来，使它们 看起来像一个表。 限制访问：将用户限定在表中的特定行上。

6.2.2 创建视图 1.使用SQL语句 2.使用SQL-EM

1. 使用SQL语句 例6-10 创建一个包含列sno、sname、cno、cname、score的视图。 CREATE VIEW <视图名>[<列名>[,…]] AS <SELECT语句> 例6-10 创建一个包含列sno、sname、cno、cname、score的视图。 脚本： CREATE VIEW v_view1 AS SELECT sc.sno,sname,sc.cno,cname,score FROM s,c,sc WHERE sc.sno=s.sno and sc.sno=c.cno

2. 使用SQL-EM (1)启动SQL-EM，指向左侧窗口数据库中的“视图”结点，单击右键，选择“新建视图”命令。 (2)指向窗口上部图表区域，单击右键，选择“添加表”命令 ，选中基表，单击“添加”按钮。 (3)单击选中基表中列前的复选框，可以定义视图输出列。在字段网格中可以在字段的“准则”框中输入检索条件。 (4)构造SELECT其他子句，单击“保存”。

2. 使用SQL-EM 例6-11 创建一个包含列sno、sname、cno、cname、score的所有选修了“数据库应用”学生的视图。 (1)启动SQL-EM，指向左侧窗口数据库student中的“视图”结点，单击右键，选择“新建视图”命令。 (2)指向窗口上部图表区域，单击右键，选择“添加表”命令，选中表s、c和sc，单击“添加”按钮。 (3)单击“关闭”按钮。单击选中表s列sno、sname，表c列cno、cname，表sc列score。单击选中并在列cname的准则框中输入“=‘数据库应用’”。 (4)单击工具栏上的“保存”图标。

6.2.3 修改视图 1.使用SQL语句 2.使用SQL-EM

1. 使用SQL语句 ALTER VIEW <视图名>[<列名>[,…]] AS <SELECT语句>

2. 使用SQL-EM (1)启动SQL-EM，单击左侧窗口要修改的视图所在数据库中的“视图”结点，指向右侧窗口中要修改的视图，单击右键，选择“设计视图”命令。该窗口与使用SQL-EM创建该视图时的窗口完全相同。 (2)用创建视图相同的方法修改视图，单击“关闭”按钮完成视图修改。

6.2.4 删除视图 1.使用SQL语句 2.使用SQL-EM

1. 使用SQL语句 例6-12 删除视图v_view2。 脚本： drop view v_view2

2. 使用SQL-EM (1)启动SQL-EM，单击左侧窗口要删除的 视图所在数据库中的“视图”结点，指向 右侧窗口中要删除的视图，单击右键， 选择“删除”命令 。 (2)单击“全部除去”按钮，指定视图将被删 除。

6.2.5 使用视图 视图可以和表一样在SELECT语句中使用。 1.检索数据 6.2.5 使用视图 视图可以和表一样在SELECT语句中使用。 1.检索数据 例6-13 查询选修了数据库应用或VB程序设计课程的学生的学号、姓名、课程名、成绩。 脚本： select sno,sname,cname,score from v_view1 where cname=‘数据库应用’ or cname=‘VB程序设计’ 2.编辑数据 视图一般作为查询手段，任一SELECT的结果均可构造一视图，视图同时可以作为表使用，但更新存在许多限制。

6.3 定制功能—存储过程 6.3.1 存储过程概述 存储过程是存储在服务器上的一组预先定义并编译好的，用来实现某种特定功能的SQL语句，可以减轻网络流量，并可提高SQL语句的执行效率。 SQL Server 2000提供了许多系统存储过程，用户也可以自己创建存储过程。

6.3.2 创建存储过程 1.使用SQL语句 2.使用SQL-EM

1. 使用SQL语句 CREATE PROCEDURE <存储过程名> AS <SQL语句> … 例6-14 创建一个新表n，包含列class、num、man、woman，用于统计表s中各班的总人数、男生人数以及女生人数。编写一个存储过程total，用于由表s统计出数据插入表n。 脚本：

2. 使用SQL-EM (1)启动SQL-EM，指向左侧窗口数据库中的“存储过程”结点，单击右键，选择“新建存储过程”命令 。 (2)输入存储过程名，并输入存储过程程序。

2. 使用SQL-EM 例6-15 创建例6-14所要求的存储过程，并命名 为total1。 例6-15 创建例6-14所要求的存储过程，并命名 为total1。 (1)启动SQL-EM，指向左侧窗口数据库student中的“存储过程”结点，单击右键，选择“新建存储过程”命令 。 (2)输入存储过程名，即将“[OWNER]. [PROCEDURE NAME]”改为“total1”，并输入存储过程程序。

6.3.3 执行存储过程 exec[ute] <存储过程名> 例6-16 执行存储过程total1，由表s统计出 数据插入表n。 脚本： execute total1

6.3.4 查看和修改存储过程 例6-17 查看存储过程total1，并删除其 中的begin及end语句。 (1)启动SQL-EM，展开左侧窗口的数据库student，单击“存储过程”结点，指向右侧窗口中的存储过程名“total1”，单击右键，选择“属性”命令，可以查看该存储过程。 (2)删除begin及end语句，单击“确定”按钮，完成存储过程的修改。

6.3.5 删除存储过程 1.使用 SQL语句 2.使用 SQL-EM

6.3.5 删除存储过程 1. 使用SQL语句 DROP PROCEDURE <存储过程名>[,…] 6.3.5 删除存储过程 1. 使用SQL语句 DROP PROCEDURE <存储过程名>[,…] 例6-18 删除存储过程total1。 脚本： drop procedure total1

6.3.5 删除存储过程 2. 使用SQL-EM (1)启动SQL-EM，展开左侧窗口的指定数据库，单击“存储过程”结点，指向右侧窗口中要删除的存储过程，单击右键，选择“删除”命令。 (2)单击“全部除去”按钮，指定存储过程将被删除。

6.3.6 存储过程和游标 存储过程可以输出游标，也就是说，一个存储过程被调用后，会得到一个游标，调用者可以对这个游标进行操作，如查询处理数据等。 例6-19 创建一个输出游标的存储过程。 脚本： 例6-20 嵌套的游标。

6.4 自动数据处理—触发器 6.4.1 触发器概述 1.概念 触发器是建立在表上的特殊的存储过程，当对表进行操作时，触发器将自动执行。

2. 执行原理 insert(delete)：对表插入(删除)记录时，触发器执行。首先将插入(删除)的记录放入inserted(deleted)表中，该表为一逻辑表(结构与原表相同)，保存插入(删除)的记录，然后执行触发器指定的操作。 update：相当于先delete，再insert。

6.4.2 创建触发器 1. 使用SQL语句 CREATE TRIGGER <触发器名> ON <表名> FOR INSERT|UPDATE|DELETE AS <SQL语句> …

6.4.2 创建触发器 例6-21 在表s上创建一个INSERT 触发器，实现当表s插入一个学生时，自动调整表n中相应班级的数据，即实现表s插入数据时与表n间数据的一致性。 脚本： 例6-22 在表s上创建一个DELETE 触发器，实现当表s删除一个学生时，自动调整表n中相应班级的数据，即实现表s删除数据时与表n间数据的一致性。

2. 使用SQL-EM (1)启动SQL-EM，单击左侧窗口数据库中的“表”结点，指向右侧窗口中指定表，单击右键，选择“所有任务”→“管理触发器”命令。 (2)输入触发器名，并选择触发器的类型，并输入触发器程序。 (3)单击“确定”按钮，完成触发器的创建。

2. 使用SQL-EM 例6-23 在表s上创建一个UPDATE触发器，实现当s 修改一个学生的信息时，自动调整表n中相应班级的 (1)启动SQL-EM，单击左侧窗口数据库student中的“表”结点，指向右侧窗口中的表“s”，单击右键，选择“所有任务”→“管理触发器”命令。 (2)将触发器名“[TRIGGER NAME]”改为“s_update”，并选择触发器的类型为“UPDATE”，输入触发器程序。 脚本： (3)单击“确定”按钮，完成触发器的创建。

6.4.3 查看和修改触发器 (1)启动SQL-EM，单击左侧窗口数据库中的“表”结点，指向右侧窗口中的表，单击右键，选择“所有任务”→“管理触发器”命令。 (2)单击“名称”文本框右侧的下拉按钮，选择触发器，可以查看和修改触发器。

6.4.3 查看和修改触发器 例6-24 查看表s的UPDATE触发器，并删除其中 的注释语句及begin、end语句。 (1)启动SQL-EM，单击左侧窗口数据库student中的“表”结点，指向右侧窗口中的表“s”，单击右键，选择“所有任务”→“管理触发器”命令。 (2)单击“名称”文本框右侧的下拉按钮，选择触发器“s_update”，查看触发器并删除注释语句及begin、end语句 。

6.4.4 删除触发器 1.使用SQL语句 DROP TRIGGER <触发器名>[,…] 2.使用SQL-EM (2)单击“名称”文本框右侧的下拉按钮，选择需要删除的触发器，单击“删除”按钮。

6.5 数据库原理(三) —数据库系统体系结构 6.5.1 三级模式 1.外模式(子模式) 用户使用的数据视图的描述。 2.概念模式(模式) 6.5 数据库原理(三) —数据库系统体系结构 6.5.1 三级模式 1.外模式(子模式) 用户使用的数据视图的描述。 2.概念模式(模式) 全局数据视图的描述。 3.内模式(物理模式) 物理存储数据视图的描述。

6.5.2 两级映像 1.外模式/概念模式映像 用于定义外模式和概念模式间数据的对应关系 。 2.概念模式/内模式映像 用于定义概念模式和内模式间数据的对应关系。

6.5.3 两级数据独立性 1.物理数据独立性 对内模式的修改尽量不影响概念模式。 2.逻辑数据独立性 对概念模式的修改尽量不影响外模式和应用程序。

实训 创建索引、视图、存储过程和触发器 实验名称：创建索引、视图、存储过程和触发器 目的要求：掌握SQL Server 2000创建索引、视图、存储过程和触发器的方法 操作步骤： (1)使用SQL-EM在数据库student中学生表上创建列sname的非聚集索引。 (2)使用SQL-EM在数据库student中创建视图,视图名为：v_<班级>_<学号>_1，包含列：sno、class、sname、sex、cno、cname、score。 (3)使用SQL-EM在数据库student中创建学生表的统计表，表名要求为： <班级>_<学号>_n,包含列:class、char(20)，num、smallint，man、smallint，woman、smallint。其中，class为主键。 (4)使用SQL-EM编写一个存储过程，存储过程名为：<班级>_<学号>_total，用于由学生表统计出数据插入统计表，并执行该存储过程。 (5)使用SQL-EM在学生表上创建INSERT、DELETE和UPDATE触发器，用于维护学生表与统计表间数据的一致性。