数据库教案 项目四 数据库的管理

项目四数据库的管理

一、教学目标

1.掌握学习索引、视图的使用方法

2.掌握学习存储过程的使用方法

3.掌握学习触发器的使用方法

二、课时分配4项目共4个任务，安排8课时

三、教学重点通过本项目的学习，让学生了解索引和视图的使用方法，掌握创建、修改索引和视图的方法；掌握存储过程的创建以及如何查看、修改和删除存储过程；学习触发器的创建和应用

四、教学难点

1.掌握索引和视图的创建和修改

2.掌握存储过程的创建以及如何查看、修改和删除存储过程

3.了解触发器的创建和应用

五、教学内容任务十六索引

一、索引索引是根据表中一列或多列的值按照一定的顺序建立的列值与记录之间的对应关系，以表列为基础建立的数据库对象索引加快检索数据行的速度，当查阅图书的某一章节内容时，为了方便查找，可以选择目录索引，快速找到页码在“学籍管理”数据库中，为加快查询速度，给经常使用的表创建索引，并设置相关属性如果列中有几个不同的值，或者表中仅包含几行值，则不推荐为其创建索引因为，索引在搜索数据所花的时间比在表中逐行搜索话的时间更长sp_stored_procedures,用于返回当前数据库中的存储过程列表，其语法为sp_stored_procedures name,其中name用于指定返回目录信息的过程名

2.修改存储过程使用ALTER PROCEDURE语句语法与CREATE PROCEDURE语句类似ALTER PROCEDUREprocedure_name[WITH ENCRYPTION]ASsql语句

3.重命名和删除存储过程1重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename,其语法形式如下sp_rename原存储过程名称，新存储过程名称通过SQL Server管理平台也可以修改存储过程的名称在SQL Server管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称2删除存储过程删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下drop proceduresp_name sp_name为存储过程名利用SQL Server管理平台也可以很方便地删除存储过程在SQL Server管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，则会弹出除去对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作单击“显示相关性”按钮，则可以在删除前查看与该存储过程有依赖关系的其他数据库对象名称任务

十九、触发器

一、触发器触发器是一种特殊的存储过程，可以分为DML触发器和DDL触发器两大类DML触发器在数据库中发生数据操作语言DML事件时将启用，DDL触发器在服务器或数据库中发生数据定义语言DDL事件时将调用这些触发器DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句在表中插入记录、更新记录或者删除记录时，DML触发器被自动激活而DDL触发器是为了响应各种数据定义语言DDL事件DDL事件主要与以关键字CREATE、ALTER和DROP开始的语句,执行DDL式操作的系统存储过程也可以激发DDL触发器触发器可以用来实现对表实施复杂的完整性约束

二、DML触发器的优点1DML触发器可通过数据库中的相关表实现级联更改2DML触发器可以防止恶意或错误的INSERTUPDATE以及DELETE操作与CHECK约束不同，DML触发器可以引用其他表中的列

三、DML触发器创建创建触发器时要指定触发器名称、定义触发器的表名；触发器将何时激发；激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE,多个数据修改语句可激活同一个触发器；执行触发操作的编程语句DML触发器使用deleted和inserted逻辑表它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表Deleted表用于存储delete,update语句所影响的行的副本Inserted表用于存储Insert或update语句所影响的行的副本触发器可以用TSQL在“新建查询”里创建，也可以在SSMS中先确定当前操作的数据库，再展开表节点，在“触发器”节点点击鼠标右键,再选择“新建触发器”来创建1使用SQL Server管理平台创建触发器的过程如下在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图19-1所示图19-1新建触发器对话框右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图19-2所示最后单击“执行”按钮，即可成功创建触发器，，匕Tr.W*fMtrtHQgomeOS.1•«・・■.IMHUKSMJ“8L«UHE・ti0Tria«rT«itntoeloveaPTk，・btoctiatcoBMa«vailnob»lacXu^Bdint-W ottiesoar*0MV・ft39^1COKCwti.SQBVtflttwQiWF»*d同函》w43S4rwtt«Br«Horavaa口划出■■警XVi0mum■ca«9工SU.sawrt»ypie»ltcwVIC.EATtTWIOOCBWbeMjlMe6y3mteletaeMjmve、丫noRvejMaae,Trwec_ttareC

0.”MW9ctaM_fikm3K_ffeve,tytMM.TttHjMara1FTTPX•二加dW-JSt■£««•1；%IKEXPT.TCLKTKWDATTASBKCXS—SCTWDCOWTWaddedcopc«v«ntoxcreresult■■“frcw-i”ux“r»TvitM8KUCT•MTMXOQWTCNlXAP,口的9GqptweetstMeMMe8criMPtrMt«jJsr»______________________________________________________________________________________________________________________[______________JJ■“卜m・SHUITKUAiE.yCSbdz»00831♦开总]].ji||^-tiCT幽,««rv«JC«!・=•1”g4，・|EwSt«1-I-，”，图19-2新建触发器窗口2使用CREATE TRIGGER命令创建DML触发器的简单语法形式如下CREATE TRIGGER触发器名称ON表FOR INSERTUPDATE或DELETEASTSQL语句

①insert触发器insert触发器用于在表中增加数据时使用，该触发器会在完成数据变动的基础上做额外的动作，完成业务操作

②update触发器，当在一个有update触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作

③delete触发器delete触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除；第二种情况是执行可删除主记录的子记录的级联删除操作

四、DDL触发器的创建和应用DDL触发器一般用于以下目的

①防止对数据库架构进行某些更改2希望数据库中发生某种情况以响应数据库架构中的更改3要记录数据库架构中的更改或事件仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发DDL触发器无法作为INSTEAD OF触发器使用DDL触发器有两种，一种是作用在当前SQL Server服务器上的，一种是作用在当前数据库中的这两种DDL触发器在Management Studio中所在的位置是不同的，作用在当前SQL Server服务器上的DDL触发器所在位置是“对象资源管理器”，选择所在SQL Server服务器，定位到“服务器对象”一“触发器”，在“摘要”对话框里就可以看到所有的作用在当前SQL Server服务器上的DDL触发器，如图19-3所示图19-3SQL Server服务器上的DDL触发器W*；£作用在当前数据库中的DDL触发器所在位置是“对象资源管理OK.••iwe-JNoDropC6f.u*VIfCOJOTM*■Ja3♦普艮dlFiMQBHi.Ilan-器”，选择所在SQL Server服务器“数据库”，所在数据库，定位到“可编程性”数据库触发器”，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器右击触发器，在弹出的快捷菜单中选择“编写数据库触发器脚本为-CREATE至『一新查询编辑器对话框”，然后在新打开的“查询编辑器”对话框里可以看到该触发器的内容，如图19-4所示JJj为X3I^STWeWT^SGUf«rv«9IE7l»««，—Ktcmia£U*»*■与旦更回心圆阴」®13|£|刈*剧对心国建13fOOUriT«««a59S»*^E■Qi«««a«sim，aBtJrTJID®ax也g«t»-iSLAMDsd一«■・—.BQIBMWa=wmmvttjf r、Qg©二AB二I尔*11阳，UHW」DU3STtBiwriKiiuowvrixl__丁mer3“Mc4M9rrajamt3…八.,.•UMMLTriT«t]««a，*«””•，《*aa••••••茴复；MiTrio^c【ar」M.rofic”l|03^日・:IUU/2GUio$osrrusiNULLScttGO.Wj瓷n“TOWTtPIDDHITICP■GO・Eu9多改■rZSy・“agg*[i*_gL_a“_・Ai”]otitet4*a»e，QiaidCor尔勘_12—3川:_21,、^«W«4N2】“,不除Wt泉,也不能，饺袅i•rollteotuaMyxlonGOg/aOff7/lat»fic«O«4«r*twjj»9U.s««w代直兀6!代/n«Jti三工卸甲MlHS^I»lir1«1«UIttEQR*11zlsrh I上LHMIWfHWU.r,W.nu*n\noQE;snnmAA»I•iu«i«w64to.TriT«it8aomaQata•I%«”-at$“•・13c34|3c.[.[[Way=Mra♦，”.，:皿r-|，•7s%1-3u】osu图19-4数据库中的DDL触发器也可以用TSQL语句创建DDL触发器，创建DDL触发器与创建DML触发器类似，但要注意创建DDL触发器和DML触发器的语句区别建立DDL触发器的简单语句如下CREATE TRIGGER触发器名ON ALLSERVER或DATABASEFOR或AFTER激活DDL触发器的事件AS要执行的SQL语句

二、索引的类型1唯一索引不允许两行具有相同的索引值也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同若创建了唯一约束，将自动创建唯一索引2主键索引为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型主键索引要求主键中的每个值都是唯一的，并且不能为空3聚集索引Clustered Index表中各行的物理顺序与键值的逻辑顺序索引顺序相同，每个表只能有一个聚集索引聚集索引经常创建在表中经常被搜索到的列或按顺序访问的列上，在默认情况下，主键约束自动创建聚集索引例如汉语字典默认按拼音排序，拼音顺序较后的字对应的页码也较大4非聚集索引Non clusteredIndex表的物理顺序与索引顺序不相同的索引数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针例如，汉语字典按笔画排序的索引就是非聚集索引，一画的字如“乙”对应的页码却比三画的字如“口”对应的页码大聚集索引和非聚集索引是从索引数据存储的角度来区分的；而唯一索引和非唯一索引是从索引值来区分的，所以唯一索引和非唯一索引既可以是聚集索引，也可以是非聚集索引，只要列中的数据是唯一的，就可以在一张表中创建一个唯一索引和多个非聚集索引

三、创建索引使用TSQL语句创建索引的简单语法:CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex nameONtable namecolumn name---[WITH FILLFACTOR=x]其中UNIQUE表示唯一索引，CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比，都是可选的FILLFACTOR的作用是当系统新建或重建索引时，在每一个索引页上预先留出一部分空间，使得系统在新增索引信息时能够保持索引内容在索引页上尽量连续值为100时表示页将填满，所留出的存储空间量最小，一般用于表数据不变的情况下值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间，一般用于表数据容易发生变化的情况下使用create index方法，可以指定索引类型、唯一性等，可以创建聚集索引，也可以创建非聚集索引，既可以在一个列上创建索引，也可以在两个或两个以上的列上创建索引

四、修改索引

1.索引重命名语法格式sp_rename OldName,NewName其中old name是原索引名称；new name是新索引名称sp_rename OldName,NewName[,object_type]必须在索引前面加上表名前缀

2.重新生成索引ALTER INDEXindex_name ONtabie_or_view_name REBUILD

3.禁用索引ALTER INDEXindex_name ONtabie_or_view_name DISABLE

4.删除索引使用TSQL语句中的DROP INDEX命令可以删除表中的索引，其语法格式如下DROP INDEX表名.索引名在删除索引时，要注意不能使用DROP INDEX语句删除由主键约束或唯一性约束创建的索引要想删除这些索引，必须先删除这些约束当删除表时，该表全部索引也将被删除当删除一个聚集索引时，该表的全部非聚集索引重新自动创建任务

十七、

一、视图视图是一种数据库对象，是一个虚拟的数据表，该数据表中的数据记录是从一个或多个表中进行查询筛选后的结果当对视图中的数据进行修改时，相应的基表数据也被修改；若基表的数据被修改，视图中的对应数据也会自动修改视图可以是一个数据表的一部分，也可以由多个基表的联合；视图也可以由一个或多个其他视图产生视图上的操作和基表类似，但是DBMS对视图的更新操作等往往存在一定的限制视图简化了操作，也提供了数据库的安全机制，可以只允许用户通过视图访问数据，而不允许用户直接访问基础表简单地说，学生信息的数据库中有多个表，学校各个部分所关注的学生数据内容是不同的，可以根据不同的要求，创建用户所需要的视图在“学籍管理”数据库中，为简化数据操作，将经常使用的查询定义为视图，并对视图进行相应的编辑

二、视图操作视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过SQL Server企业管理器和TransactSQL语句来实现以删除视图为例

①在SSMS中删除视图的方法，与修改视图类似,右击需要删除视图名，在弹出的快捷菜单中选择“删除”命令，则在打开的窗口单击“确定”按钮，即可完成删除操作

②使用TSQL语句删除视图，可以使用DROP VIEW语句来删除视图，其语法格式为DROP VIEW视图名【例1】删除Score_View视图DROP VIEWScore_View删除一个视图后，虽然它所基于的表和数据不会受到任何影响，但是依赖于该视图的其他对象或查询将会在执行时出现错误

三、视图的应用通过视图可以完成某些和基础表相同的一些数据操作，如数据的查询、添加、修改和删除

1.通过视图插入表数据使用视图插入数据与在基表中插入数据一样，都可以通过INSERT语句来实现插入数据的操作是针对视图中的列的插入操作，而不是针对基表中的所有的列的插入操作使用INSERT语句进行插入操作的视图必须能够在基表中插入数据，否则插入操作会失败对于由多个基表连接而成的视图来说，一个插入操作只能作用于一个基表上

2.通过视图修改表数据语法格式UPDATE视图名SET列1二列值1列2二列值2列n二列值nWHERE逻辑表达式若通过视图修改数据，视图必须定义在一个表上，并且不包括统计函数，SELECT语句中不包括GROUP BY子句在视图中更新数据也与在基表中更新数据一样，但是当视图基于多个基表中的数据时，与插入操作一样，每次更新操作只能更新一个基表中的数据

3.通过视图删除表数据通过视图删除数据与通过基表删除数据的方式一样，在视图中删除的数据同时在基表中也被删除当一个视图连接了两个以上的基表时，对数据的删除操作则是不允许的任务

十八、存储过程

一、存储过程存储过程是使用TSQL语言编写的为完成特定的功能的程序，经编译后存储在数据库中存储过程也是数据库对象，通过使用存储过程提高数据库的安全性和减少网络通信的数据量

二、存储过程优点1允许模块化程序设计2执行效率高3减少网络通信的数据量4提高数据库的安全性

三、存储过程分类1系统存储过程一般以“sp_”开头，物理意义上系统存储过程在Resource数据库中，逻辑意义上存储过程出现在系统数据库和用户定义数据库的sys架构中2扩展存储过程一般以“xp_”开头，允许用其他语言创建自己的外部存储过程，以DLL形式存在3用户自定义存储过程，可以输入参数、向客户端返回结果、消息等

四、创建存储过程存储过程的创建的简单语法如下create procedure过程名[©parameter data_type]/*定义参数的类型*/[VARYING][二default][OUTPUT]/*定义参数的属性等*/AS执行的SQL语句创建存储过程

1.创建的简单存储过程1在SQL Server管理平台中，选择“资源资源管理器”一“服务器”一“数据库例如ProDB”-可编程性”一“存储过程”一“新建存储过程”选项，如图18-1所示JEMfi«aiMBs!#Q8©XA»g母U£cMQp，・“品⑺J曰0三a G9-2」龄才属q皿-si■jraMsrxm*BOLs««wiotne-ironn-评上HJNdl£3AJ1UE•ua，03glHttMSXxvtusor■:4VXC$・•・”.身■A.d」，-ax.£•»・・*w*s»**tt•W.-|jCS-»«4，・«r«Jigrtr,图18-1在SSMS中创建存储过程T«wLa^»9raM・tWfx*Cx^locwciwiav5wFcoGeMte，*•BemM.fM-VM*IUKSwc2iyValwloxTea^Laco-lnt—M coflIJ1»CM9erflB»e^T1M1C«.2在文本框中可以输入创建存储过程的TSQL语句，单击“执行”TharoXockalCCMMAIAvlli加・ibcXjdadUtM4eCinic-iwvttWeMooector*X・幽得irrgMto.igmmM按钮，即可创建该存储过程HC1A«JCbor..K«wCr«eete««rz«1•〃》oel|rttost・^urteg8oti..・3存储过程的执行exec过程名W皿_F3_，OJ，391X，,u1Iu_r，rar3sirNocoimcn»

五、查看、修改和删除存储过程-rMBCt toe内QCgMC・b»CB3n4CTePMRFdFT-epi.”•一

1.查看存储过程5tm“冬U5MIa.^TTXCWRVMiifticlfSI1在SSMS中查看存储过程，展开指定的服务器和数据库，选择并依次展开“可编程性一存储过程”，然后右击要查看的存储过程名称，从弹出的快捷菜单中，选择“编写存储过程脚本为一CREATE到一新查询编辑器窗口”，则可以看到存储过程的源代码如图18-2所示■电汽4…s；•jr E[，zDBGO嬴=30X-mOBfWVuirEM.JMtrfftf/对•ScocelFrooedure*o.{BnAMINULLSCH into]u/aa/aou图a00srrovoTtB_iK»om，xavGOce«，・rooctwc«Idkro]lel・3_*ntoj^cXavaidmtsans-aeieetid.cias9MMpe.otelfsoaiSQ|IMt宓，el«99vWre*d・0ul”・149a|tQd5jr・rQ QI32cr・UE*QlW^«8QBMl•Knxjd0缄SJW

9.XI£QB，WIrw■w*ntim霍拿住StsxeQiKMlW»w一mQ|ftu£ie«U«*SvricuAau“pt•zarfflrrrall一.J图18-2查看存储过程2使用系统存储过程来查看用户创建的存储过程可供使用的系统存储过程及其语法形式如下sp_help,用于显示存储过程的参数及其数据类型，其语法为sp_help name,参数name为要查看的存储过程的名称sp_helptext,用于显示存储过程的源代码，其语法为sp_helptext name,参数name为要查看的存储过程的名称。