数据库其它对象的创建与管理课程课件

学习情景5：数据库其它对象

的创建与管理学习情景5：数据库其它对象

1单元描述数据库和数据表创建完毕，并不表示数据库的设计就结束了，用户往往会对数据库有一些特殊的要求，比如：数据库是否具备数据完整性关系？如何提高数据的检索速度？当需要频繁执行某一特殊任务时，如何避免重复编写程序代码？在SQLServer2005中提供了一系列数据库对象来解决类似以上的问题。本学习情景将重点介绍在SCDB数据库中创建和管理这些数据库对象（约束、索引、视图、存储过程、触发器等）来解决实际问题的过程。单元描述数据库和数据表创建完毕，并不表示2学习目标学会数据完整性的设计;学会索引的创建与管理;学会视图的创建与管理;学会存储过程的创建与管理;学会触发器的创建与管理;学会游标的创建与使用;了解游标的使用方法。学习目标学会数据完整性的设计;3项目1:SCDB中数据完整性的设计【任务描述】数据库中的数据是从外界输入的，而数据的输入由于种种原因，会输入无效或错误的信息。那么保证数据正确性、一致性和可靠性，就成了数据库系统关注的重要问题。SQLServer提供了数据完整性的设计来解决以上问题，具体可以通过创建约束、默认、规则、用户自定义函数来解决。项目1:SCDB中数据完整性的设计【任务描述】4项目1:SCDB中数据完整性的设计【任务目标】了解数据完整性的基本概念；掌握创建和使用约束来保证数据的完整性；掌握创建、绑定、解绑定和删除默认值的方法；掌握创建、绑定、解绑定和删除规则的方法；掌握创建、绑定、使用和删除用户自定义完整性的方法。项目1:SCDB中数据完整性的设计【任务目标】5任务1了解数据完整性的基础知识数据的完整性是指存储在数据库中数据的正确性、一致性和可靠性。根据数据的完整性所作用的数据库对象和范围的不同，数据的完整性分为实体完整性、域完整性、参照完整性和用户定义完整性4种。任务1了解数据完整性的基础知识数据的完整性6任务1了解数据完整性的基础知识1、实体完整性实体完整性也可称表的完整性。它用于保证数据库中数据表的每一个特定实体都是唯一的，可以通过主键约束（PRIMARYKEY）、唯一键约束（UNIQUE）、索引或标识属性（IDENTITY）来实现。任务1了解数据完整性的基础知识1、实体完整性7任务1了解数据完整性的基础知识2、域完整性域完整性也可称列完整性，用以指定列的数据输入是否具有正确的数据类型、格式以及有效的数据范围。任务1了解数据完整性的基础知识2、域完整性8任务1了解数据完整性的基础知识3、参照完整性参照完整性是保证参照与被参照表中数据的一致性。例如，在学生表Student中有学生的学号StudentID且在选课表SC中也有学号StudentID，而且两个表的学号StudentID值必须一致，如果在输入过程中出现错误且又没有被系统检查出来，那么数据之间将会造成混乱。任务1了解数据完整性的基础知识3、参照完整性9任务1了解数据完整性的基础知识4、用户定义完整性用户定义完整性允许用户定义不属于其他任何完整性分类的特定规则。所有的完整性类型都支持用户定义完整性。用户定义的完整性主要通过使用触发器和存储过程来强制实施完整性。存储过程和触发器的相关知识在后续任务中介绍。任务1了解数据完整性的基础知识4、用户定义完整性10任务2：

约束的实现1.约束的类型约束是SQLServer强制实行的应用规则，它通过限制列、行和表中的数据来保证数据的完整性。当删除表时，表所带的约束也随之被删除。

常用的约束包括CHECK约束、PRIMARYKEY约束、FOREIGNKEY约束、UNIQUE约束和DEFAULT约束。任务2：约束的实现1.约束的类型11任务2：

约束的实现（1）CHECK约束CHECK约束用于限制输入一列或多列的值的范围，通过逻辑表达式来判断数据的有效性，也就是一个列的输入内容必须满足CHECK约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。任务2：约束的实现（1）CHECK约束12任务2：

约束的实现（2）DEFAULT约束

若在表中某列定义了DEFAULT约束，用户在插入新的数据行时，如果该列没有指定数据，那么系统将默认值赋给该列，当然该默认值也可以是空值（NULL）。任务2：约束的实现（2）DEFAULT约束13任务2：

约束的实现（3）PRIMARYKEY约束在表中经常有一列或多列的组合，其值能唯一标识表中的每一行。这样的一列或多列成为表的主键(PrimaryKey)，通过它可以强制表的实体完整性。一个表只能有一个主键，而且主键约束中的列不能为空值。任务2：约束的实现（3）PRIMARYKEY约束14任务2：

约束的实现（4）FOREIGNKEY约束外健(ForeignKey)是用于建立和加强两个表(主表与从表)的一列或多列数据之间的连接，当添加、修改或删除数据时，通过参照完整性来保证它们之间的数据的一致性。任务2：约束的实现（4）FOREIGNKEY约束15任务2：

约束的实现（5）UNIQUE约束UNIQUE约束用于确保表中的两个数据行在非主键中没有相同的列值。与PRIMARYKEY'约束类似，UNIQUE约束也强制唯一性，为表中的一列或多列提供实体完整性。但UNIQUE约束用于非主健的一列或多列组合，且一个表可以定义多个UNIQUE约束。另外，UNIQUE约束可以用于定义多列组合，且一个表可以定义多个UNIQUE约束，UNIQUE约束可以用于定义允许空值的列；而PRIMAYRKEY约束只能用在唯一列上，且不能为空值。任务2：约束的实现（5）UNIQUE约束16任务2：

约束的实现2.约束的创建、查看与删除约束的创建、查看与删除等操作均可在SQLServerManagementStudio的【对象资源管理器】面板中进行，也可使用Transact-SQL语句进行。任务2：约束的实现2.约束的创建、查看与删除17任务2：

约束的实现（1）CHECK约束的创建、查看和删除【例】在学生表(Student)中定义学生的性别Sex列只能是“男”或“女”，从而避免用户输入其他的值。要解决此问题，需要用到CHECK约束，使学生性别列的值只有“男”或“女”两种可能，如果用户输入其他值，系统均提示用户输入无效。任务2：约束的实现（1）CHECK约束的创建、查看和删除18任务2：

约束的实现①在SQLServerManagementStudio的【对象资源管理器】中选取【数据库】选项下的SCDB数据库。②展开数据库SCDB，并展开数据库SCDB目录下的【表】，右击“dbo.Student”选项，在弹出的快捷菜单中，选择【设计(G)】命令，打开【设计表】窗口，选中“Sex”，然后，单击【菜单栏】中的【表设计器】窗口工具栏上的【CHECK约束】命令，如图所示；或者将鼠标放在列“Sex”上，右击鼠标，在弹出的快捷菜单中，选择【CHECK约束】命令，如图所示。任务2：约束的实现①在SQLServerManagem19任务2：

约束的实现③在弹出的【CHECK约束】窗口中单击【添加】按钮，如图所示。④单击“表达式”后面的

按钮，进入如图所示的CHECK约束表达式的界面，在“表达式”文本框中输入约束表达式“Sex='男'ORSex='女'，如图所示。然后，单击“确定”按钮。任务2：约束的实现③在弹出的【CHECK约束】窗口中单击【20任务2：

约束的实现⑤在【设计表】窗口单击

（【保存】），即完成了创建并保存CHECK约束的操作。以后用户输入数据时，若输入性别不是“男”或“女”，系统将报告输入无效。

要想删除上面创建的CHECK约束，选择该约束，右击鼠标，在弹出的菜单中选择【删除】命令，如图所示，然后单击“关闭”按钮，即可删除CHECK约束。任务2：约束的实现⑤在【设计表】窗口单击（【保存】），即21任务2：

约束的实现【例】使用Transact-SQL语句为学生表Student创建CHECK约束。在SQLServerManagementStudio查询编辑器中运行以下代码：USESCDBGOALTERTABLEStudentADDCONSTRAINTCK_StudentCHECK(sex='男'orsex='女')GO任务2：约束的实现【例】使用Transact-SQL语句为22任务2：

约束的实现删除CHECK约束的语句格式如下：DROPCONSTRAINTCHECKconstraint_name【例】删除创建的约束CK_Student。

USESCDBGOALTERTABLEStudentDROPCONSTRAINTCK_StudentGO任务2：约束的实现删除CHECK约束的语句格式如下：23任务3：默认值的实现1.默认值的概念与在约束中介绍的DEFAULT约束类似，使用默认值也可以实现当用户在向数据库表中插入新纪录时，如果没有给出某列的输入值，则有系统自动为该列输入默认值的功能。默认值可以是常量、内置函数或数学表达式。任务3：默认值的实现1.默认值的概念24任务3：默认值的实现2.创建默认值

通常创建并使用默认值的步骤为：

①创建一个默认值对象。

创建默认的命令如下：CREATEDEFAULTdefault_nameASconstraint_expression任务3：默认值的实现2.创建默认值25任务3：默认值的实现②将其捆绑到列或用户自定义数据类型上。绑定默认值的命令如下：EXECsp_bindefaultdefault_name,'table_name.[column_name]'任务3：默认值的实现②将其捆绑到列或用户自定义数据类型上。26任务3：默认值的实现3.删除默认值通常删除默认值的步骤为：①解除默认值捆绑的列或用户自定义数据类型。解除绑定默认值的命令如下：EXECsp_unbindefault'table_name.column_name'②删除该默认值。具体的命令语句如下：DROPDEFAULTdefault_name任务3：默认值的实现3.删除默认值27任务4：规则的实现1.规则的概念规则也是实现数据完整性的方法之一，其作用与CHECK约束的部分功能相同。规则可以被绑定到一个列或者用户定义数据类型上，它提供了一种加强列或用户定义数据类型域约束的机制。当其被绑定到列或用户定义的数据类型上时，用来指定允许输入到列中的数据，即当用户向表中插入数据时，用来指定该列接受数据值的范围。同时，规则与默认一样在数据库中只需要定义一次，就可以被多次应用。任务4：规则的实现1.规则的概念28任务4：规则的实现2.创建规则和默认值类似，规则创建后，需要将其捆绑到列上或用户自定义数据类型上。创建规则的命令如下：CREATERULErule_nameASconstraint_expression捆绑规则的命令语句如下：EXECsp_bindrulerule_name，'

table_name.[column_name]'任务4：规则的实现2.创建规则29任务4：规则的实现如果在列或数据类型上已经捆绑了规则，那么当再次向它们捆绑规则时，旧规则将自动被新规则覆盖，而不会捆绑多条规则。捆绑规则可以使用系统存储过程sp_bindrule，解除规则的相关绑定可以使用系统存储过程sp_unbindrule。任务4：规则的实现如果在列或数据类30任务4：规则的实现3.删除规则①解除规则捆绑的列或用户自定义数据类型。解除绑定默认值的命令如下：EXECsp_unbindrule'table_name.column_name'②删除该规则。具体的命令语句如下：DROPRulerule_name任务4：规则的实现3.删除规则31任务5：用户自定义函数的实现1.用户自定义函数为了扩展T-SQL的编程能力，SQLServer2005除了提供的内部函数外，还允许用户自定义函数。用户可以使用CREATEFUNCTION语句编写自已的函数，以满足特殊需要。用户自定义函数可用传递一个或多个参数，并返回一个简单的数值。任务5：用户自定义函数的实现1.用户自定义函数32任务5：用户自定义函数的实现（1）创建标量用户自定义函数标量用户自定义函数返回一个简单的数值，如int、char、decimal等，但禁止使用text、ntext、image、cursor和timestamp作为返回的参数。

该函数的函数体被封装在以BEGIN语句开始，END语句结束的范围内。任务5：用户自定义函数的实现（1）创建标量用户自定义函数33任务5：用户自定义函数的实现其语法格式如下：CREATEFUNCTION[owner_name].function_name(@parameter_namescalar_parameter_data_type)RETURNscalar_return_data_type[AS]BEGINFUNCTION_bodyRETURNscalar_expressionEND任务5：用户自定义函数的实现其语法格式如下：34任务5：用户自定义函数的实现【例】创建一个自定义函数，返回特定课程的平均成绩。USESCDBGOCREATEFUNCTIONAvgGrade_SC(@CourseIDVarchar(20))RETURNSFLOATASBEGINDECLARE@AVG_GradeFLOATSET@AVG_Grade=(SELECTAVG(Grade)FROMSCWHERECourseID=@CourseID)RETURN@AVG_GradeEND任务5：用户自定义函数的实现【例】创建一个自定义函数，返回35任务5：用户自定义函数的实现（2）创建直接表值用户定义函数表值函数返回一个Table型数据，对直接表值用户定义函数而言，返回的结果只是一系列表值，没有明确的函数体。该表是SELECT语句的结果集。其语法格式为：CREATEFUNCTION[owner_name].function_name(@parameter_namescalar_parameter_data_type)RETURNTABLE[AS]RETURN[(select_statement)]任务5：用户自定义函数的实现（2）创建直接表值用户定义函数36任务5：用户自定义函数的实现【例】创建一个函数，要求返回属于同一个班级的学生的基本信息。在SQLServerManagementStudio查询编辑器中运行以下代码：USESCDBGOCREATEFUNCTION学生信息(@班级号Varchar(20))RETURNSTABLEASRETURN(SELECT*FROMStudentWHEREClassID=@班级号)任务5：用户自定义函数的实现【例】创建一个函数，要求返回属37项目2:SCDB中索引的创建与管理【任务描述】数据库中的索引与书籍中的目录类似，在一本书中，利用目录可以快速查找到所需要的信息，无须阅读整本书，在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需要的数据。当创建数据库并优化其性能时，应该为数据查询所使用表的列创建索引，建立索引后，SQLServer2005会根据索引的有序排列，通过高效的查找算法找到相关数据。因此，对表建立索引，可以加快数据的查询速度和减少系统的响应时间。项目2:SCDB中索引的创建与管理【任务描述】38项目2:SCDB中索引的创建与管理【任务目标】

了解索引的基础知识；掌握创建索引的方法；学会管理和维护索引。项目2:SCDB中索引的创建与管理【任务目标】39任务1:了解索引的基础知识1.索引概述索引与目录类似，如果想快速查找而不是逐页查找指定的内容，可以通过目录中章节的页号找到其对应的内容。类似地，索引通过记录表中的关键值指向表中的记录，这样数据库引擎就不用扫描整个表而定位到相关的记录。任务1:了解索引的基础知识1.索引概述40任务1:了解索引的基础知识1.索引概述

SQLServer中一个表的存储是由数据页和索引页两个部分组成的。数据页用来存放除了文本和图像数据以外的所有与表的某一行相关的数据，索引页包含组成特定索引的列中的数据。任务1:了解索引的基础知识1.索引概述41任务1:了解索引的基础知识2.索引的作用索引是以表列为基础的数据库对象，它保存着表中排序的索引列，并且记录了索引列在数据表中的物理存储位置，实现了表中数据的逻辑排序，其主要目的是提高SQLServer系统的性能，加快数据的查询速度和减少系统的响应时间。任务1:了解索引的基础知识2.索引的作用42任务1:了解索引的基础知识3.建立索引的原则一般来说，适合在这些列上创建索引：(1)在经常需要搜索的列上创建索引，可以加快搜索的速度。(2)在作为主键的列上创建索引，强制该列的唯一性和组织表中数据的排列结构。(3)在经常用在连接的列上创建索引，这些列主要是一些外键，可以加快连接的速度。(4)在经常使用在WHERE子句中的列上创建索引，加快条件的判断速度。(5)在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。(6)在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的。任务1:了解索引的基础知识3.建立索引的原则43任务1:了解索引的基础知识4.索引的分类在MicrosoftSQLServer2005系统中，有两种基本类型的索引：聚集索引和非聚集索引。除此之外，还有唯一索引、包含索引、索引视图、全文索引、XML索引等。任务1:了解索引的基础知识4.索引的分类44任务1:了解索引的基础知识（1）聚集索引索引的结构是树状结构，树的顶部称为叶级，树的其他部分称为非叶级，树的根部在非叶级中。同样，聚集索引的叶级和非叶级构成了一个树状结构。在聚集索引中，表中的数据所在的数据页就是聚集索引的叶级，在叶级之外的索引页是非叶级,如图所示。任务1:了解索引的基础知识（1）聚集索引45任务1:了解索引的基础知识聚集索引在使用中具有以下特点：①每一个表只能有一个聚集索引，因为表中数据的物理顺序只有一个。②表中行的物理顺序和索引中行的物理顺序是相同的，在创建任何非聚集索引之前创建聚集索引，这是因为聚集索引改变了表中行的物理顺序，数据行按照一定的顺序排列，并目自动维护这个顺序。③聚集索引的平均大小大约是数据表的百分之五，但是，实际的聚集索引的大小常常根据索引列的大小变化而变化。④在索引的创建过程中，SQLServer临时使用当前数据库的磁盘空间，当创建聚集索引时，需要120%的表空间的大小，因此，一定要保证有足够的空间来创建聚集索引。任务1:了解索引的基础知识聚集索引在使用中具有以下特点：46任务1:了解索引的基础知识（2）非聚集索引非聚集索引具有与表的数据完全分离的结构。使用非聚集索引不用将物理数据页中的数据按列排序。

非聚集索引表示行的逻辑顺序。在非聚集索引中，叶级没有包含数据行，如图所示。任务1:了解索引的基础知识（2）非聚集索引47任务1:了解索引的基础知识（3）唯一索引唯一索引确保索引键不包含重复的值，因此，表或视图中的每一行在某种程度上是唯一的。例如，如果在表中的“姓名”字段上创建了唯一索引，则以后输入的姓名将不能同名。创建PRIMARYKEY或UNIQUE约束会在表中指定的列上自动创建唯一索引。聚集索引和非聚集索引都可以是唯一索引。任务1:了解索引的基础知识（3）唯一索引48任务2：创建索引1、在【对象资源管理器】中创建索引【例】在SCDB数据库中的Student表上创建基于Name列，名为Student_index的不唯一、非聚集索引。（1）在SQLServerManagementStudio的【对象资源管理器】面板中，选择要创建索引的表Student，然后展开Student表前面的“+”号，选中【索引】选项右击，在弹出的快捷菜单中选择“新建索引”命令，如图所示。（2）选择【新建索引】命令，进入如图5.34所示的【新建索引】窗口，在该窗口中列出了Student表上要建立的索引，包含其名称、是不是聚集索引、是否设置唯一索引等。输入索引名称为“Student_index”，选择【非聚集】选项。任务2：创建索引1、在【对象资源管理器】中创建索引49任务2：创建索引（3）单击【添加】按钮进入如图所示的界面，在列表中选择需要创建索引的Name列（对于复合索引，可以选择多个组合列）。（4）单击【确定】按钮，SQLServer将完成索引的创建。任务2：创建索引（3）单击【添加】按钮进入50任务2：创建索引2.使用Transact-SQL语句创建索引创建索引使用CREATEINDEX语句。其语法如下：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON｛table_name|view_name｝([column1[ASC|DESC],column2[ASC|DESC],…]|[express])[TABLESPACEtablespace_name][PCTFREEn1][STORAGE(INITIALn2)][NOLOGGING][NOLINE][NOSORT];任务2：创建索引2.使用Transact-SQL语句创51任务3：

查看索引信息1.在【对象资源管理器】中查看索引信息在SQLServerManagementStudio的【对象资源管理器】面板中，使用与创建索引同样的方法，打开如下图所示的快捷菜单，选择“属性”命令，即可看到该索引对应的信息。任务3：查看索引信息1.在【对象资源管理器】中查看索引信52任务3：

查看索引信息2.使用系统存储过程sp_helpindex查看指定表的索引信息【例】使用系统存储过程sp_helpindex查看SCDB数据库中Student表的索引信息。在SQLServerManagementStudio查询编辑器中运行如下命令：USESCDBGOEXECsp_helpindexStudentGO任务3：查看索引信息2.使用系统存储过程sp_helpin53任务4：重命名索引1.在【对象资源管理器】中重命名索引在SQLServerManagementStudio的【对象资源管理器】面板中，使用与创建索引同样的方法，打开如下图所示的快捷菜单，选择“重命名”命令，然后直接输入新名即可。任务4：重命名索引1.在【对象资源管理器】中重命名索引54任务4：重命名索引2.通过Transact-SQL语句来实现，更改索引名称的命令格式如下：EXECsp_renametable_nameold_index_name,new_index_name其中：Table_name：索引所在的表名称。Old_Index_name：要重新命名的索引的名称。New_Index_name：新的索引名称。任务4：重命名索引2.通过Transact-SQL语句来55任务5：删除索引1.在【对象资源管理器】中删除索引在SQLServerManagementStudio的【对象资源管理器】面板中展开SCDB数据库，单击【表】选项展开Student表，再展开【索引】前面的“+”号，选中索引名为Student_index的索引，在弹出的快捷菜单中选择【删除】命令，进入如图所示的窗口，单击【确定】按钮，即可删除该索引。任务5：删除索引1.在【对象资源管理器】中删除索引562.使用Transact-SQL语句删除索引使用Transact-SQL语句删除索引的语法格式如下：DROPINDEXTable_name,index_name[,Table_name,index_name…]其中：Table_name：索引所在的表名称。Index_name：要删除的索引的名称。2.使用Transact-SQL语句删除索引57项目3：SCDB中视图的创建与管理【任务描述】视图(View)作为一种数据库对象，为用户提供了一个可以检索数据表中的数据的方式。用户通过视图来浏览数据表中感兴趣的部分或全部数据，而数据的物理存储位置仍然在表中。【任务目标】了解视图的基本概念、作用和特点；掌握创建、修改和删除视图的方法并能灵活运用视图来简化表，及简化数据的查询。项目3：SCDB中视图的创建与管理【任务描述】58任务1：了解视图的基础知识1.视图的概念视图是一个虚拟表，并不表示任何物理数据，只是用来查看数据的窗口而已。视图与真正的表很类似，也是由一组命名的列和数据行所组成，其内容由查询所定义。但是视图并不是以一组数据的形式存储在数据库中，数据库中只存储视图的定义，而不存储视图对应的数据，这些数据仍存储在导出视图的基本表中。当基本表中的数据发生变化时，从视图中查询出来的数据也随之改变。任务1：了解视图的基础知识1.视图的概念59任务1：了解视图的基础知识2.使用视图的优点和缺点

（1）使用视图的优点数据保密。对不同的用户定义不同的视图，使用户只能看到与自己有关的数据。简化查询操作。为复杂的查询建立一个视图，用户不必输入复杂的查询语句，只需针对此视图做简单的查询即可。保证数据的逻辑独立性。对于视图的操作，例如，查询只依赖于视图的定义，当构成视图的基本表需要修改时，只需要修改视图定义中的子查询部分，而基于视图的查询不用改变。任务1：了解视图的基础知识2.使用视图的优点和缺点60任务1：了解视图的基础知识（2）使用视图的缺点

当更新视图中的数据时，实际上是对基本表的数据进行更新。事实上，当从视图中插入或者删除时，情况也是这样。然而，某些视图是不能更新数据的，这些视图有如下的特征：有UNION等集合操作符的视图。有GROUPBY子句的视图。有诸如AVG，SUM或者MAX等函数的视图。使用DISTINCT关键字的视图。连接表的视图(其中有一些例外)。任务1：了解视图的基础知识（2）使用视图的缺点61任务2：创建视图1.在【对象资源管理器】中创建视图【例】利用【对象资源管理器】在SCDB数据库中创建一个名为V_Student的视图，该视图仅查看Student表中来自“荆门”的学生的基本信息。（1）启动SQLServerManagementStudio，在【对象资源管理器】的树型目录中，找到SCDB，展开该数据库。（2）选择【视图】，按一下鼠标右键，在弹出的快捷菜单中选择【新建视图】命令，出现如图所示。任务2：创建视图1.在【对象资源管理器】中创建视图62任务2：创建视图（3）在弹出的【添加表】对话框选择Student表，点击【添加】按钮，然后点击【关闭】按钮关闭【添加表】对话框，如图所示。（4）在如图所示的对话框的代码编辑窗格编辑代码。（5）点击工具栏的

，弹出如图所示的视图保存对话框，输入视图的名称“V_Student”，点击【确定】按钮，即完成视图的创建。任务2：创建视图（3）在弹出的【添加表】对话框选择Stude63任务2：创建视图2.使用Transact-SQL语句创建视图基本语法如下：CREATEVIEWview_name[WITHENCRYPTION]ASselect_statement其中，WITHENCRYPTION子句表示对视图加密。任务2：创建视图2.使用Transact-SQL语句创建视图64任务3：显示视图的信息1.在【对象资源管理器】中显示视图的信息【例5.29】在SQLServerManagementStudio窗口中查看和修改视图V_Student的定义信息。（1）在【对象资源管理器】面板中展开【数据库】选项，然后展开【SCDB】选项。（2）展开【视图】选项，在视图列表中可以见到名为V_Student的视图。如果没有看到，单击“刷新”按钮，刷新一次。（3）右击V_Student视图，在弹出的快捷菜单中选择【设计】命令打开如图所示窗口，可以在该对话框中直接对视图的定义进行修改。任务3：显示视图的信息1.在【对象资源管理器】中显示视图的65任务3：显示视图的信息2.通过执行系统存储过程sp_helptext查看视图的信息【例】通过执行系统存储过程sp_helptext查看视图V_Student2的定义信息。在SQLServerManagementStudio查询编辑器中运行如下命令：USESCDBGOsp_helptext'V_Student2'任务3：显示视图的信息2.通过执行系统存储过程sp_help66任务4：重命名视图【例】将视图V_Student2重新命名为V_Stu2。（1）在【对象资源管理器】面板中展开【数据库】选项，然后展开【SCDB】选项。（2）展开【视图】选项，在视图列表中选择名为V_Student2的视图，右击鼠标在弹出的菜单中选择【重命名】，如图所示，然后将视图V_Student2重新命名为V_Stu2。任务4：重命名视图【例】将视图V_Student2重新命名为67任务4：重命名视图【例】将视图V_Stu2重新命名为V_Student2。在SQLServerManagementStudio查询编辑器中运行如下命令：USESCDBGOEXECsp_rename'V_Stu2','V_Student2'任务4：重命名视图【例】将视图V_Stu2重新命名为V_St68任务5：视图的修改和删除1.视图的修改视图的修改是由ALTER语句来完成的，基本语法如下：ALTERVIEWview_name[WITHENCRYPTION]ASSelect_statement任务5：视图的修改和删除1.视图的修改69任务5：视图的修改和删除2.视图的删除

视图的删除是通过DROP语句来实现的。【例】使用Transact-SQL语句删除视图V_Student。

在SQLServerManagementStudio查询编辑器中运行如下命令：USESCDBGODROPV_StudentGO任务5：视图的修改和删除2.视图的删除70项目：4SCDB中存储过程的创建与管理【任务描述】当用户使用一串Transact-SQL语句访问服务器上的数据时，首先将Transact-SQL语句发送到服务器，由服务器编译Transact-SQL语句，并进行优化产生查询的执行计划，之后数据库引擎执行查询计划，最终将执行结果发回客户程序。每当执行一段Transact-SQL语句时，都要重复以上操作。是否可以免去以上重复操作，而是将用户经常执行的可以实现某种特殊功能的代码看成一个集合，当用户需要使用这段代码时直接调用呢？SQLServer提供了存储过程这一数据库对象来解决以上问题。项目：4SCDB中存储过程的创建与管理【任务描述】71项目：4SCDB中存储过程的创建与管理【任务目标】了解存储过程的基本概念和作用；了解存储过程的分类；学会创建和执行存储过程的方法；学会管理和维护存储过程；掌握存储过程的重编译处理；学会系统存储过程和扩展存储过程的调用。项目：4SCDB中存储过程的创建与管理【任务目标】72任务1：了解存储过程的基础知识1.存储过程的基本概念存储过程是在数据库服务器端执行的一组Transact-SQL语句的集合，经编译后存放在数据库服务器中。它能够向用户返返回数据、向数据库表中写入和修改数据，还可以执行系统函数和管理操作。用户在编程过程中只需要给出存储过程的名称和必需的参数，就可以方便地调用它们。任务1：了解存储过程的基础知识1.存储过程的基本概念73任务1：了解存储过程的基础知识2.存储过程的特点可以将存储过程的特点归纳如下：（1）能够在单个存储过程中执行一系列的Transact-SQL语句，也能够在一个存储过程中调用其他的存储过程。（2）存储过程是保存在服务器端的已经编译的Transact-SQL语句，因此比一般的Transact-SQL语句执行速度快，同时减少了网络流量，节省大量时间和数据量。（3）存储过程可以使用控制流语句和变量，大大增强了SQL的功能。（4）存储过程在提交前会自动检查语法，避免了一些不必要错误的出现。（5）存储过程是管理员放在服务器端的Transact-SQL语句，可以设置用户对存储过程的使用权限，从而保证了数据库访问的安全性。任务1：了解存储过程的基础知识2.存储过程的特点74任务1：了解存储过程的基础知识3.存储过程的分类

SQLServer2005提供了三种存储过程：用户自定义存储过程、系统存储过程和扩展存储过程。（1）用户自定义存储过程用户自定义存储过程也就是用户自行创建并存储在用户数据库中的存储过程，它用于完成用户指定的某一特定功能(如查询用户所需的数据信息)。任务1：了解存储过程的基础知识3.存储过程的分类75任务1：了解存储过程的基础知识（2）系统存储过程SQLServer2005不仅提供用户自定义存储过程的功能，而且也提供许多可作为工具使用的系统存储过程。系统存储过程通常使用“sp_”为前缀，主要用于管理SQLServer和显示有关数据库及用户的信息。（3）扩展存储过程扩展存储过程(ExtendedStoredProcedures)是用户可以使用外部程序语言编写的存储过程。任务1：了解存储过程的基础知识（2）系统存储过程76任务2：创建和执行存储过程1.创建和执行简单存储过程（1）创建存储过程的SQL语法格式如下：CREATEPROCEDUREprocedure_name[WITHENCRYPTION][WITHRECOMPILE]ASSq1_statement任务2：创建和执行存储过程1.创建和执行简单存储过程77任务2：创建和执行存储过程（2）执行存储过程在存储过程创建成功后，用户可以执行存储过程来检查存储过程的返回结果。执行存储过程主要有两种方法，一是在SQLServerManagementStudio的查询编辑器中运用Transact-SQL语句执行；二是在SQLServerManagementStudio的对象资源管理器中直接用鼠标操作执行存储过程。任务2：创建和执行存储过程（2）执行存储过程78任务2：创建和执行存储过程在SQLServerManagementStudio查询编辑器中执行存储过程的操作步骤如下：打开SQLServerManagementStudio查询编辑器；在SQLServerManagementStudio查询编辑器中输入执行存储过程的Transact-SQL语句，然后单击执行。执行存储过程的Transact-SQL语句基本语法如下:EXECprocedure_name任务2：创建和执行存储过程在SQLSer79任务3:修改存储过程1.使用Transact-SQL语句修改存储过程修改存储过程是由ALTER语句来完成的，其语法如下：ALTERPROCEDUREprocedure_name[WITHENCRYPTION][WITHRECOMPILE]ASSql_statement任务3:修改存储过程1.使用Transact-SQL语句修80任务3:修改存储过程2.在【对象资源管理器】面板中修改存储过程通过SQLServerManagementStudio中的【对象资源管理器】来修改存储过程的具体步骤如下：⑴展开SQLServerManagementStudio【对象资源管理器】中的【数据库】选项，然后展开【可编程性】选项，如图所示。⑵展开【存储过程】选项，选中要进行修改的存储过程，右击鼠标，在弹出的菜单中选择【修改】命令，如图所示。⑶在弹出的修改存储过程窗口中，直接修改该存储过程，修改完毕，保存即可。任务3:修改存储过程2.在【对象资源管理器】面板中修改存储81任务4:重命名存储过程重命名存储过程的具体步骤：①在SQLServerManagementStudio窗口中打开【对象资源管理器】面板，并展开数据库“SCDB”选项。②展开【可编程性】选项，选择【存储过程】选项。在存储过程详细列表中，选中存储过程db.p_Student2，右击鼠标，在弹出的快捷菜单中，选择【重命名】命令，如图所示。③输入存储过程的新名称即可。任务4:重命名存储过程重命名存储过程的具体步骤：82任务5:删除存储过程存储过程的删除常用的方法有两种，一种是使用Transact-SQL语句来删除；另一种是使用SQLServerManagementStudio中的【对象资源管理器】来进行删除。1.通过Transact-SQL语句删除存储过程存储过程的删除是通过DROP语句来实现的。任务5:删除存储过程存储过程的删除常用的83任务5:删除存储过程2.使用【对象资源管理器】删除存储过程以使用SQLServerManagementStudio窗口来删除存储过程p_StudentNum为例，讲述在【对象资源管理器】中删除存储过程的步骤如下：(1)在SQLServerManagementStudio窗口中打开【对象资源管理器】面板，展开SCDB选项。(2)展开【可编程性】选项，右击【存储过程】选项，展开【存储过程】，选中dbo.p_StudentNum，单击鼠标右键，在弹出的快捷菜单中，选择【删除】命令即可，如图所示。任务5:删除存储过程2.使用【对象资源管理器】删除存储过程84项目5SCDB中触发器的创建与管理【任务描述】触发器是一种特殊的存储过程，在满足某种特定条件时，触发器可以自动执行，完成各种复杂的任务。触发器通常用于实现强制业务规则和数据完整性。【任务目标】了解触发器的概念；学会创建触发器的方法；掌握触发器的管理和维护。项目5SCDB中触发器的创建与管理【任务描述】85任务1:了解触发器的基础知识触发器是一种特殊类型的存储过程。存储过程是通过存储过程名被调用执行的，而触发器主要是通过事件触发而被执行的。触发器(Trigger)不仅能实现完整性规则，而且能保证一些较复杂业务规则的实施。所谓触发器就是一类由事件驱动的特殊过程，一旦由某个用户定义，任何用户对该触发器指定的数据进行增加、删除或修改操作时，系统将自动激活相应的触发器，在核心层进行集中的完整性控制。任务1:了解触发器的基础知识触发器是86任务1:了解触发器的基础知识2.触发器的优点（1）强制比CHECK约束更复杂的数据的完整性;（2）使用自定义的错误提示信息;（3）实现数据库中多张表的级联修改;（4）比较数据库修改前后数据的状态;（5）维护规范化数据。任务1:了解触发器的基础知识2.触发器的优点87任务2：

创建触发器使用CREATETRIGGER命令创建触发器，其基本语法如下:CREATETRIGGERtrigger_nameON{table|view}{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHENCRYPTION]ASIFUPDATE(column_name)[{and|or}UPDATE(column_name)…]sql_statement任务2：创建触发器使用CREATETRIGGER命令创建88任务2：

创建触发器【例】在SCDB数据库的Student表上创建一个Student_trigger1的触发器，当执行INSERT操作时，将显示一条“数据插入成功！”的消息。在SQLServerManagementStudio查询编辑器中运行如下命令:USESCDBGOCREATETRIGGERStudent_trigger1ONStudentFORINSERTASPRINT'数据插入成功！'GO任务2：创建触发器【例】在SCDB数据库的Student表89任务3：管理触发器1.查看触发器信息（1）使用系统存储过程查看触发器信息

系统存储过程sp_help、sp_helptext、sp_depends和sp_helptrigger分别提供有关触发器的不同信息。（2）使用系统表查看触发器信息用户可以通过查询系统表sysobjects得到触发器的相关信息。任务3：管理触发器1.查看触发器信息90任务3：管理触发器2.修改触发器（1）重命名触发器使用sp_rename命令修改触发器的名字，其语法格式为：EXECsp_renameoldname.newname其中，oldname：指触发器原来的名称，newname：指触发器的新名称。任务3：管理触发器2.修改触发器91任务3：管理触发器2.修改触发器（2）修改触发器定义修改触发器的具体语法如下：ALTERTRIGGERtrigger_nameON[table|view]{FOR[AFTER|INSTEADOF]}{[INSERT][,][UPDATE][,][DELETE]}[WITHENCRYPTION]ASIFUPDATE(cotumn_name)[{and|or}UPDATE(column_name)…]sql_statesment任务3：管理触发器2.修改触发器92任务3：管理触发器3.禁止和启动触发器禁用和启用触发器的语法如下：ALTERTABLEtable_name{ENABLE|DISABLE}TRIGGER{ALL|trigger_name[,…n]}任务3：管理触发器3.禁止和启动触发器93任务3：管理触发器4.删除触发器（1）使用命令DRDPTRIGGER删除指定的触发器，具体语法形式如下：DROPTRIGGERtrigger_name（2）在【对象资料管理器】面板中删除触发器按照前面介绍的方法找到相应的触发器并右击鼠标，在弹出的快捷菜单中，选择【删除】命令即可。（3）删除触发器所在的表时，SQLserver2005将自动删除与该表相关的触发器。任务3：管理触发器4.删除触发器94项目6SCDB中游标的使用【任务描述】由SELECT语句查询的结果是一个记录集，即由若干条记录组成的一个完整的单元。在实际应用中常常需要从这种记录集中逐行逐条的进行访问。如：在统计某班某课学生成绩分布的查询结果集中，用户往往希望逐行访问记录，以便知道每个同学这门课的成绩是多少，据此判断成绩是优、良、中、及格还是不及格。使用游标便可解决这类问题。项目6SCDB中游标的使用【任务描述】95项目6SCDB中游标的使用【任务目标】了解游标的概念及其操作，了解常用游标的类型、使用游标的步骤，掌握游标的打开方法，学会利用游标提取数据、修改数据和删除数据，掌握游标关闭及释放方法。项目6SCDB中游标的使用【任务目标】96任务1：了解游标的基础知识1.游标的定义游标(Cursor)是允许用户能够从SELECT语句查询的结果集中，逐条逐行地访问记录，用户可以按照自己的意愿逐行地显示、修改或删除这些记录的数据访问处理机制。游标可以理解为数据表记录逐行访问(移动当前记录和在当前记录上进行访问)的位置指针。任务1：了解游标的基础知识1.游标的定义97任务1：了解游标的基础知识1.游标的定义游标是一个与Transact-SQL的SELECT语句相关联的符号名，它使用户可逐行访问由SQLServer返回的结果集。游标包括以下两个部分：（1）游标结果集(CursorResultSet)：由定义该游标的SELECT语句返回的行集合。（2）游标位置(CursorFasitian)：指向这个行集合某一行的当前指针。任务1：了解游标的基础知识1.游标的定义98任务1：了解游标的基础知识2.游标的优点从游标定义上可以得到游标的优点，这些优点使游标在实际应用中发挥了重要的作用：（1）允许程序对由查询语句SELECT返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。（2）提供对基于游标位置的表中的行进行删除和更新的能力。（3）游标实际上是作为面向集合的数据库管理系统(DBMS)和面向行的程序设计之间的桥梁。任务1：了解游标的基础知识2.游标的优点99任务1：了解游标的基础知识3.声明游标声明游标的语法形式为：DECLAREcursor_nameCURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name_list[,…]]}]任务1：了解游标的基础知识3.声明游标100任务2：

使用游标1.打开游标可使用OPEN语句打开游标，语法格式如下：OPEN{[GLOBAL]cursor_name|@cursor_variable_name}任务2：使用游标1.打开游标101任务2：

使用游标2.读取游标中的数据一旦游标被打开，就可以从该游标集合中读取数据了。从游标中得到一行数据的操作称为一个FETCH。该FETCH操作包含如下操作：FETCHFIRST：获取游标中的第一行数据。如果在打开游标之后第一次执行该操作，则将获取游标中的第一行数据。FETCHNEXT：获取下一行数据。如果在打开游标之后第一次执行该操作，则将获取游标中的第一行数据。FETCHPRIOR：获取上一行数据。如果在打开游标之后第一次执行该操作，则得不到任何数据。FETCHLAST：获取游标中最后行数据。如果在打开游标之后第一次执行该操作，则将获取游标中的最后一行数据。FETCHABSOLUTEn：如果n是一个正整数，则该操作会获取游标中从第一行开始的n行数据；如果n是一个负整数，那么该操作会获取游标中从末尾行向前数的n行数据。如果n是0则不会获取数据。FETCHRELATIVEn：相对上一个被获取的行而言，该操作将获取该行前面或者后面的n行数据。如果n是正数，则获取该行之后的n行数据。如果n是负数，则获取该行之前的n行数据。如果n的值是0，则再次获取该行的数据。任务2：使用游标2.读取游标中的数据102任务2：

使用游标3.使用游标修改数据游标不仅能够读取表中的数据，同时还可修改(更新和删除)表中的行。SQLServer提供了WHERECURRENTOFcursor_name子句对游标集合中的当前行进行更新和删除操作，下面用一个实例来说明如何使用该WHERE子句修改表中的内容：【例5.65】利用游标Student_cursor2，删除表Student中的数据。在SQLServerManagementStudio查询编辑器中运行如下代码。任务2：使用游标3.使用游标修改数据103学习情景5：数据库其它对象

的创建与管理学习情景5：数据库其它对象

104单元描述数据库和数据表创建完毕，并不表示数据库的设计就结束了，用户往往会对数据库有一些特殊的要求，比如：数据库是否具备数据完整性关系？如何提高数据的检索速度？当需要频繁执行某一特殊任务时，如何避免重复编写程序代码？在SQLServer2005中提供了一系列数据库对象来解决类似以上的问题。本学习情景将重点介绍在SCDB数据库中创建和管理这些数据库对象（约束、索引、视图、存储过程、触发器等）来解决实际问题的过程。单元描述数据库和数据表创建完毕，并不表示105学习目标学会数据完整性的设计;学会索引的创建与管理;学会视图的创建与管理;学会存储过程的创建与管理;学会触发器的创建与管理;学会游标的创建与使用;了解游标的使用方法。学习目标学会数据完整性的设计;106项目1:SCDB中数据完整性的设计【任务描述】数据库中的数据是从外界输入的，而数据的输入由于种种原因，会输入无效或错误的信息。那么保证数据正确性、一致性和可靠性，就成了数据库系统关注的重要问题。SQLServer提供了数据完整性的设计来解决以上问题，具体可以通过创建约束、默认、规则、用户自定义函数来解决。项目1:SCDB中数据完整性的设计【任务描述】107项目1:SCDB中数据完整性的设计【任务目标】了解数据完整性的基本概念；掌握创建和使用约束来保证数据的完整性；掌握创建、绑定、解绑定和删除默认值的方法；掌握创建、绑定、解绑定和删除规则的方法；掌握创建、绑定、使用和删除用户自定义完整性的方法。项目1:SCDB中数据完整性的设计【任务目标】108任务1了解数据完整性的基础知识数据的完整性是指存储在数据库中数据的正确性、一致性和可靠性。根据数据的完整性所作用的数据库对象和范围的不同，数据的完整性分为实体完整性、域完整性、参照完整性和用户定义完整性4种。任务1了解数据完整性的基础知识数据的完整性109任务1了解数据完整性的基础知识1、实体完整性实体完整性也可称表的完整性。它用于保证数据库中数据表的每一个特定实体都是唯一的，可以通过主键约束（PRIMARYKEY）、唯一键约束（UNIQUE）、索引或标识属性（IDENTITY）来实现。任务1了解数据完整性的基础知识1、实体完整性110任务1了解数据完整性的基础知识2、域完整性域完整性也可称列完整性，用以指定列的数据输入是否具有正确的数据类型、格式以及有效的数据范围。任务1了解数据完整性的基础知识2、域完整性111任务1了解数据完整性的基础知识3、参照完整性参照完整性是保证参照与被参照表中数据的一致性。例如，在学生表Student中有学生的学号StudentID且在选课表SC中也有学号StudentID，而且两个表的学号StudentID值必须一致，如果在输入过程中出现错误且又没有被系统检查出来，那么数据之间将会造成混乱。任务1了解数据完整性的基础知识3、参照完整性112任务1了解数据完整性的基础知识4、用户定义完整性用户定义完整性允许用户定义不属于其他任何完整性分类的特定规则。所有的完整性类型都支持用户定义完整性。用户定义的完整性主要通过使用触发器和存储过程来强制实施完整性。存储过程和触发器的相关知识在后续任务中介绍。任务1了解数据完整性的基础知识4、用户定义完整性113任务2：

约束的实现1.约束的类型约束是SQLServer强制实行的应用规则，它通过限制列、行和表中的数据来保证数据的完整性。当删除表时，表所带的约束也随之被删除。

常用的约束包括CHECK约束、PRIMARYKEY约束、FOREIGNKEY约束、UNIQUE约束和DEFAULT约束。任务2：约束的实现1.约束的类型114任务2：

约束的实现（1）CHECK约束CHECK约束用于限制输入一列或多列的值的范围，通过逻辑表达式来判断数据的有效性，也就是一个列的输入内容必须满足CHECK约束的条件，否则，数据无法正常输入，从而强制数据的域完整性。任务2：约束的实现（1）CHECK约束115任务2：

约束的实现（2）DEFAULT约束

若在表中某列定义了DEFAULT约束，用户在插入新的数据行时，如果该列没有指定数据，那么系统将默认值赋给该列，当然该默认值也可以是空值（NULL）。任务2：约束的实现（2）DEFAULT约束116任务2：

约束的实现（3）PRIMARYKEY约束在表中经常有一列或多列的组合，其值能唯一标识表中的每一行。这样的一列或多列成为表的主键(PrimaryKey)，通过它可以强制表的实体完整性。一个表只能有一个主键，而且主键约束中的列不能为空值。任务2：约束的实现（3）PRIMARYKEY约束117任务2：

约束的实现（4）FOREIGNKEY约束外健(ForeignKey)是用于建立和加强两个表(主表与从表)的一列或多列数据之间的连接，当添加、修改或删除数据时，通过参照完整性来保证它们之间的数据的一致性。任务2：约束的实现（4）FOREIGNKEY约束118任务2：

约束的实现（5）UNIQUE约束UNIQUE约束用于确保表中的两个数据行在非主键中没有相同的列值。与PRIMARYKEY'约束类似，UNIQUE约束也强制唯一性，为表中的一列或多列提供实体完整性。但UNIQUE约束用于非主健的一列或多列组合，且一个表可以定义多个UNIQUE约束。另外，UNIQUE约束可以用于定义多列组合，且一个表可以定义多个UNIQUE约束，UNIQUE约束可以用于定义允许空值的列；而PRIMAYRKEY约束只能用在唯一列上，且不能为空值。任务2：约束的实现（5）UNIQUE约束119任务2：

约束的实现2.约束的创建、查看与删除约束的创建、查看与删除等操作均可在SQLServerManagementStudio的【对象资源管理器】面板中进行，也可使用Transact-SQL语句进行。任务2：约束的实现2.约束的创建、查看与删除120任务2：

约束的实现（1）CHECK约束的创建、查看和删除【例】在学生表(Student)中定义学生的性别Sex列只能是“男”或“女”，从而避免用户输入其他的值。要解决此问题，需要用到CHECK约束，使学生性别列的值只有“男”或“女”两种可能，如果用户输入其他值，系统均提示用户输入无效。任务2：约束的实现（1）CHECK约束的创建、查看和删除121任务2：

约束的实现①在SQLServerManagementStudio的【对象资源管理器】中选取【数据库】选项下的SCDB数据库。②展开数据库SCDB，并展开数据库SCDB目录下的【表】，右击“dbo.Student”选项，在弹出的快捷菜单中，选择【设计(G)】命令，打开【设计表】窗口，选中“Sex”，然后，单击【菜单栏】中的【表设计器】窗口工具栏上的【CHECK约束】命令，如图所示；或者将鼠标放在列“Sex”上，右击鼠标，在弹出的快捷菜单中，选择【CHECK约束】命令，如图所示。任务2：约束的实现①在SQLServerManagem122任务2：

约束的实现③在弹出的【CHECK约束】窗口中单击【添加】按钮，如图所示。④单击“表达式”后面的

按钮，进入如图所示的CHECK约束表达式的界面，在“表达式”文本框中输入约束表达式“Sex='男'ORSex='女'，如图所示。然后，单击“确定”按钮。任务2：约束的实现③在弹出的【CHECK约束】窗口中单击【123任务2：

约束的实现⑤在【设计表】窗口单击

（【保存】），即完成了创建并保存CHECK约束的操作。以后用户输入数据时，若输入性别不是“男”或“女”，系统将报告输入无效。

要想删除上面创建的CHECK约束，选择该约束，右击鼠标，在弹出的菜单中选择【删除】命令，如图所示，然后单击“关闭”按钮，即可删除CHECK约束。任务2：约束的实现⑤在【设计表】窗口单击（【保存】），即124任务2：

约束的实现【例】使用Transact-SQL语句为学生表Student创建CHECK约束。在SQLServerManagementStudio查询编辑器中运行以下代码：U