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

项目四数据库的管理一、教学目标1.掌握学习索引、视图的使用方法。2.掌握学习存储过程的使用方法。3.掌握学习触发器的使用方法。二、课时分配本项目共4个任务，安排8课时。三、教学重点通过本项目的学习，让学生了解索引和视图的使用方法，掌握创建、修改索引和视图的方法；掌握存储过程的创建以及如何查看、修改和删除存储过程；学习触发器的创建和应用。四、教学难点1.掌握索引和视图的创建和修改。2.掌握存储过程的创建以及如何查看、修改和删除存储过程。3.了解触发器的创建和应用。五、教学内容任务十六索引一、索引索引是根据表中一列或多列的值按照一定的顺序建立的列值与记录之间的对应关系，以表列为基础建立的数据库对象。索引加快检索数据行的速度，当查阅图书的某一章节内容时，为了方便查找，可以选择目录索引，快速找到页码。在“学籍管理”数据库中，为加快查询速度，给经常使用的表创建索引，并设置相关属性。如果列中有几个不同的值，或者表中仅包含几行值，则不推荐为其创建索引。因为，索引在搜索数据所花的时间比在表中逐行搜索话的时间更长。二、索引的类型（1）唯一索引：不允许两行具有相同的索引值。也就是说，对于表中的任何两行记录来说，索引键的值都是各不相同。若创建了唯一约束，将自动创建唯一索引。（2）主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值都是唯一的，并且不能为空。（3）聚集索引(ClusteredIndex)：表中各行的物理顺序与键值的逻辑顺序（索引顺序）相同，每个表只能有一个聚集索引。聚集索引经常创建在表中经常被搜索到的列或按顺序访问的列上，在默认情况下，主键约束自动创建聚集索引。例如汉语字典默认按拼音排序，拼音顺序较后的字对应的页码也较大。（4）非聚集索引(NonclusteredIndex)：表的物理顺序与索引顺序不相同的索引。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。例如，汉语字典按笔画排序的索引就是非聚集索引，一画的字如“乙”对应的页码却比三画的字如“口”对应的页码大。聚集索引和非聚集索引是从索引数据存储的角度来区分的；而唯一索引和非唯一索引是从索引值来区分的，所以唯一索引和非唯一索引既可以是聚集索引，也可以是非聚集索引，只要列中的数据是唯一的，就可以在一张表中创建一个唯一索引和多个非聚集索引。三、创建索引使用TSQL语句创建索引的简单语法：CREATE［UNIQUE］［CLUSTERED|NONCLUSTERED］INDEXindex_nameONtable_name(column_name…)［WITHFILLFACTOR=x］其中UNIQUE表示唯一索引，CLUSTERED、NONCLUSTERED表示聚集索引还是非聚集索引，FILLFACTOR表示填充因子，指定一个0到100之间的值，该值指示索引页填满的空间所占的百分比，都是可选的。FILLFACTOR的作用是当系统新建或重建索引时，在每一个索引页上预先留出一部分空间，使得系统在新增索引信息时能够保持索引内容在索引页上尽量连续。值为100时表示页将填满，所留出的存储空间量最小，一般用于表数据不变的情况下。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间，一般用于表数据容易发生变化的情况下。使用createindex方法，可以指定索引类型、唯一性等，可以创建聚集索引，也可以创建非聚集索引，既可以在一个列上创建索引，也可以在两个或两个以上的列上创建索引。四、修改索引1.索引重命名语法格式：sp_renameOldName,NewName其中：oldname是原索引名称；newname是新索引名称。sp_renameOldName,NewName［,object_type］必须在索引前面加上表名前缀2.重新生成索引ALTERINDEXindex_nameONtable_or_view_nameREBUILD3.禁用索引ALTERINDEXindex_nameONtable_or_view_nameDISABLE4.删除索引使用TSQL语句中的DROPINDEX命令可以删除表中的索引，其语法格式如下：DROPINDEX表名.索引名在删除索引时，要注意不能使用DROPINDEX语句删除由主键约束或唯一性约束创建的索引。要想删除这些索引，必须先删除这些约束。当删除表时，该表全部索引也将被删除。当删除一个聚集索引时，该表的全部非聚集索引重新自动创建。任务十七、视图一、视图视图是一种数据库对象，是一个虚拟的数据表，该数据表中的数据记录是从一个或多个表中进行查询筛选后的结果。当对视图中的数据进行修改时，相应的基表数据也被修改；若基表的数据被修改，视图中的对应数据也会自动修改。视图可以是一个数据表的一部分，也可以由多个基表的联合；视图也可以由一个或多个其他视图产生。视图上的操作和基表类似，但是DBMS对视图的更新操作等往往存在一定的限制。视图简化了操作，也提供了数据库的安全机制，可以只允许用户通过视图访问数据，而不允许用户直接访问基础表。简单地说，学生信息的数据库中有多个表，学校各个部分所关注的学生数据内容是不同的，可以根据不同的要求，创建用户所需要的视图。在“学籍管理”数据库中，为简化数据操作，将经常使用的查询定义为视图，并对视图进行相应的编辑。二、视图操作视图的操作主要包括视图的创建、修改、删除和重命名等，其操作可以通过SQLServer企业管理器和TransactSQL语句来实现。以删除视图为例：①在SSMS中删除视图的方法，与修改视图类似，右击需要删除视图名，在弹出的快捷菜单中选择“删除”命令，则在打开的窗口单击“确定”按钮，即可完成删除操作。②使用TSQL语句删除视图，可以使用DROPVIEW语句来删除视图，其语法格式为：DROPVIEW视图名【例1】删除Score_View视图。DROPVIEWScore_View删除一个视图后，虽然它所基于的表和数据不会受到任何影响，但是依赖于该视图的其他对象或查询将会在执行时出现错误。三、视图的应用通过视图可以完成某些和基础表相同的一些数据操作，如数据的查询、添加、修改和删除。1.通过视图插入表数据使用视图插入数据与在基表中插入数据一样，都可以通过INSERT语句来实现。插入数据的操作是针对视图中的列的插入操作，而不是针对基表中的所有的列的插入操作。使用INSERT语句进行插入操作的视图必须能够在基表中插入数据，否则插入操作会失败。对于由多个基表连接而成的视图来说，一个插入操作只能作用于一个基表上。2.通过视图修改表数据语法格式：UPDATE视图名SET列1=列值1列2=列值2……列n=列值nWHERE逻辑表达式若通过视图修改数据，视图必须定义在一个表上，并且不包括统计函数，SELECT语句中不包括GROUPBY子句。在视图中更新数据也与在基表中更新数据一样，但是当视图基于多个基表中的数据时，与插入操作一样，每次更新操作只能更新一个基表中的数据。3.通过视图删除表数据通过视图删除数据与通过基表删除数据的方式一样，在视图中删除的数据同时在基表中也被删除。当一个视图连接了两个以上的基表时，对数据的删除操作则是不允许的。任务十八、存储过程一、存储过程存储过程是使用TSQL语言编写的为完成特定的功能的程序，经编译后存储在数据库中。存储过程也是数据库对象，通过使用存储过程提高数据库的安全性和减少网络通信的数据量。二、存储过程优点（1）允许模块化程序设计。（2）执行效率高。（3）减少网络通信的数据量。（4）提高数据库的安全性。三、存储过程分类（1）系统存储过程：一般以“sp_”开头，物理意义上系统存储过程在Resource数据库中，逻辑意义上存储过程出现在系统数据库和用户定义数据库的sys架构中。（2）扩展存储过程：一般以“xp_”开头，允许用其他语言创建自己的外部存储过程，以DLL形式存在。（3）用户自定义存储过程，可以输入参数、向客户端返回结果、消息等。四、创建存储过程存储过程的创建的简单语法如下：createprocedure过程名［@parameterdata_type］/*定义参数的类型*/［VARYING］［=default］［OUTPUT］/*定义参数的属性等*/AS执行的SQL语句创建存储过程1.创建的简单存储过程（1）在SQLServer管理平台中，选择“资源资源管理器”→“服务器”→“数据库（例如ProDB）”→“可编程性”→“存储过程”→“新建存储过程”选项，如图18-1所示。（2）在文本框中可以输入创建存储过程的TSQL语句，单击“执行”按钮，即可创建该存储过程。（3）存储过程的执行：exec过程名。五、查看、修改和删除存储过程1.查看存储过程（1）在SSMS中查看存储过程，展开指定的服务器和数据库，选择并依次展开“可编程性→存储过程”，然后右击要查看的存储过程名称，从弹出的快捷菜单中，选择“编写存储过程脚本为→CREATE到→新查询编辑器窗口”，则可以看到存储过程的源代码。如图18-2所示。（2）使用系统存储过程来查看用户创建的存储过程。可供使用的系统存储过程及其语法形式如下：sp_help，用于显示存储过程的参数及其数据类型，其语法为：sp_helpname，参数name为要查看的存储过程的名称。sp_helptext，用于显示存储过程的源代码，其语法为：sp_helptextname，参数name为要查看的存储过程的名称。sp_stored_procedures，用于返回当前数据库中的存储过程列表，其语法为：sp_stored_proceduresname，其中name用于指定返回目录信息的过程名。2.修改存储过程使用ALTERPROCEDURE语句语法与CREATEPROCEDURE语句类似。ALTERPROCEDUREprocedure_name［WITHENCRYPTION］ASsql语句3.重命名和删除存储过程（1）重命名存储过程：修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename原存储过程名称，新存储过程名称通过SQLServer管理平台也可以修改存储过程的名称。在SQLServer管理平台中，右击要操作的存储过程名称，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称。（2）删除存储过程：删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropproceduresp_namesp_name为存储过程名。利用SQLServer管理平台也可以很方便地删除存储过程。在SQLServer管理平台中，右击要删除的存储过程，从弹出的快捷菜单中选择“删除”选项，则会弹出除去对象对话框，在该对话框中，单击“确定”按钮，即可完成删除操作。单击“显示相关性”按钮，则可以在删除前查看与该存储过程有依赖关系的其他数据库对象名称。任务十九、触发器一、触发器触发器是一种特殊的存储过程，可以分为DML触发器和DDL触发器两大类。DML触发器在数据库中发生数据操作语言DML事件时将启用，DDL触发器在服务器或数据库中发生数据定义语言DDL事件时将调用这些触发器。DML事件包括在指定表或视图中修改数据的INSERT语句、UPDATE语句或DELETE语句。在表中插入记录﹑更新记录或者删除记录时，DML触发器被自动激活。而DDL触发器是为了响应各种数据定义语言DDL事件。DDL事件主要与以关键字CREATE、ALTER和DROP开始的语句，执行DDL式操作的系统存储过程也可以激发DDL触发器。触发器可以用来实现对表实施复杂的完整性约束。二、DML触发器的优点（1）DML触发器可通过数据库中的相关表实现级联更改。（2）DML触发器可以防止恶意或错误的INSERT、UPDATE以及DELETE操作。与CHECK约束不同，DML触发器可以引用其他表中的列。三、DML触发器创建创建触发器时要指定触发器名称、定义触发器的表名；触发器将何时激发；激活触发器的数据修改语句，有效选项为INSERT、UPDATE或DELETE，多个数据修改语句可激活同一个触发器；执行触发操作的编程语句。DML触发器使用deleted和inserted逻辑表。它们在结构上和触发器所在的表的结构相同，SQLServer会自动创建和管理这些表。Deleted表用于存储delete，update语句所影响的行的副本。Inserted表用于存储Insert或update语句所影响的行的副本。触发器可以用TSQL在“新建查询”里创建，也可以在SSMS中先确定当前操作的数据库，再展开表节点，在“触发器”节点点击鼠标右键，再选择“新建触发器”来创建。（1）使用SQLServer管理平台创建触发器的过程如下：在SQLServer管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图19-1所示。右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图19-2所示。最后单击“执行”按钮，即可成功创建触发器。（2）使用CREATETRIGGER命令创建DML触发器的简单语法形式如下：CREATETRIGGER触发器名称ON表FORINSERT、UPDATE或DELETEASTSQL语句①insert触发器。insert触发器用于在表中增加数据时使用，该触发器会在完成数据变动的基础上做额外的动作，完成业务操作。②update触发器，当在一个有update触发器的表中修改记录时，表中原来的记录被移动到删除表中，修改过的记录插入到了插入表中，触发器可以参考删除表和插入表以及被修改的表，以确定如何完成数据库操作。③delete触发器。delete触发器通常用于两种情况，第一种情况是为了防止那些确实需要删除但会引起数据一致性问题的记录的删除；第二种情况是执行可删除主记录的子记录的级联删除操作。四、DDL触发器的创建和应用DDL触发器一般用于以下目的：①防止对数据库架构进行某些更改。②希望数据库中发生某种情况以响应数据库架构中的更改。③要记录数据库架构中的更改或事件。仅在运行触发DDL触发器的DDL语句后，DDL触发器才会激发。DDL触发器无法作为INSTEADOF触发器使用。DDL触发器有两种，一种是作用在当前SQLServer服务器上的，一种是作用在当前数据库中的。这两种DDL触发器在ManagementStudio中所在的位置是不同的，作用在当前SQLServer服务器上的DDL触发器所在位置是：“对象资源管理器”，选择所在SQLServer服务器，定位到“服务器对象”→“触发器”，在“摘要”对话框里就可以看到所有的作用在当前SQLServer服务器上的DDL触发器，如图19-3所示。作用在当前数据库中的DDL触发器所在位置是：“对象资源管理器”，选择所在SQLServer服务器“数据库”，所在数据库，定位到“可编程性”→“数据库触发器”，在摘要对话框里就可以看到所有的当前数据库中的DDL触发器。右击触发器，在弹出的快捷菜单中选择“编写数据库触发器脚本为”→“CREATE到”→“新查询编辑器对话框”，然后在新打开的“查询编辑器”对话框里可以看到该触发器的内容，如图19-4所示。也可以用TSQL语句创建DDL触发器，创建DDL触发器与创建DML触发器类似，但要注意创建DDL触发器和DML触发器的语句区别。建立DDL触发器的简单语句如下：CREATETRIGGER触发器名ONALLSERVER或DATABASEFOR或AFTER激活DDL触发器的事件AS要执行的SQL语句其中,ON后面的AllServer是将DDL触发器作用到整个当前的服务器上。如果指定了这个参数，在当前服务器上的任何一个数据库都能激活该触发器。ON后面的Database是将DDL触发器作用到当前数据库，只能在这个数据库上激活该触发器。DDL触发器不能指定的insteadof触发器。激活DDL触发器的事件包括两种，一种在DDL触发器作用在当前数据库情况下可以使用以下事件如：CREATE_TABLE、ALTER_TABLE、DROP_TABLE、CREATE_ROLE、CREATE_USER等。另一种是在DDL触发器作用在当前服务器情况下，可以使用以下事件如：CREATE_DATABASE、ALTER_DATABASE、DROP_DATABASE、CREATE_LOGIN等。五、管理触发器管理DDL触发器与管理DML触发器类似，以DDL触发器为例。1.查看触发器除了在SSMS中查看触发器，还可以用系统存储过程查看触发器。系统存储过程sp_help、sp_helptext和sp_depends分别提供有关触发器的不同信息。其具体用途和语法形式如下。（1）help：用于查看触发器的一般信息，如触发器的名称、属性、类型和创建时间。sp_help‘触发器名称’（2）helptext：用于查看触发器的正文信息。sp_helptext‘触发器名称’（3）depends：用于查看指定触发器所引用的表