数据库对象表的操作.ppt

第5章数据库对象的操作,5.1数据类型5.2表操作5.3视图操作5.4索引操作5.5存储过程5.6触发器5.7图表,5.1数据类型,5.1.1系统数据类型5.1.2自定义数据类型,5.1.1系统数据类型,1.整型数据类型2.浮点数据类型3.字符数据类型4.日期和时间数据类型5.文本和图形数据类型,6.货币数据类型7.位数据类型8.二进制数据类型9.特殊数据类型10.新增数据类型,1.整型数据类型,整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。int（integer）：int（或integer）数据类型可以存储从-231（-2，147，483，648）到231-1（2，147，483，647）范围之间的所有正负整数。Smallint：可以存储从-215（-32，768）到215-1范围之间的所有正负整数。Tinyint：可以存储从0到255范围之间的所有正整数。,2.浮点数据类型,浮点数据类型用于存储十进制小数。浮点数值的数据在SQLServer中采用只入不舍的方式进行存储。Real：可以存储正的或者负的十进制数值，最大可以有7位精确位数。Float：可以精确到第15位小数，其范围从-1.79E-308到1.79E+308。Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从-1038-1到1038-1之间的数值。,3.字符数据类型,字符数据类型可以用来存储各种字母、数字符号和特殊符号。Char：其定义形式为char（n），每个字符和符号占用一个字节的存储空间。Varchar：其定义形式为varchar（n）。用char数据类型可以存储长达255个字符的可变长度字符串。Nchar：其定义形式为nchar（n）。Nvarchar：其定义形式为nvarchar（n）。,4.日期和时间数据类型,Datetime：用于存储日期和时间的结合体。它可以存储从公元1753年1月1日零时起到公元9999年12月31日23时59分59秒之间的所有日期和时间。Smalldatetime：与datetime数据类型类似，但其日期时间范围较小，它存储从1900年1月1日到2079年6月6日内的日期。,5.文本和图形数据类型,Text：用于存储大量文本数据，其容量理论上为1到231-1（2，147，483，647）个字节，但实际应用时要根据硬盘的存储空间而定。Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。Image：用于存储照片、目录图片或者图画，其理论容量为231-1（2，147，483，647）个字节。,6.货币数据类型,Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为-922337213685477.5808到922337213685477.5808，精度为货币单位的万分之一。Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为-214748.3468到214748.3467。,7.位数据类型,Bit：称为位数据类型，其数据有两种取值：0和1，长度为1字节。,8.二进制数据类型,Binary：其定义形式为binary(n)，数据的存储长度是固定的，即n+4字节，当输入的二进制数据长度小于n时，余下部分填充0。Varbinary：其定义形式为varbinary(n)，数据的存储长度是变化的，它为实际所输入数据的长度加上4字节。其它含义同binary。,9.特殊数据类型,Timestamp：亦称时间戳数据类型，它提供数据库范围内的唯一值，反应数据库中数据修改的相对顺序，相当于一个单调上升的计数器。Uniqueidentifier：用于存储一个16字节长的二进制数据类型，它是SQLServer根据计算机网络适配器地址和CPU时钟产生的唯一号码而生成的全局唯一标识符代码（GloballyUniqueIdentifier，简写为GUID）。,10.新增数据类型,Bigint：用于存储从-263（-9，223，372，036，854，775，807）到263-1（9，223，372，036，854，775，807）之间的所有正负整数。sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQLServer数据。table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。,5.1.2自定义数据类型,1.使用企业管理器（EnterpriseManager）创建用户自定义数据类型。2.利用系统存储过程创建用户自定义数据类型。系统存储过程sp_addtype为用户提供了T_SQL语句创建自定义数据类型的途径，其语法形式如下：sp_addtypetypename=type,phystype=system_data_type,nulltype=null_type,owner=owner_name,例子5-1：自定义一个地址数据类型。,execsp_addtypeaddress,varchar(80),notnull其运行结果如下：（1row(s)affected）typeadded.,例子5-2：删除自定义的生日数据类型。,execsp_droptypebirthday其运行结果如下：（1row(s)affected）（0row(s)affected）Typehasbeendropped.,5.2表操作,5.2.1创建表5.2.2增加、删除和修改字段5.2.3创建、删除和修改约束5.2.4查看表格,5.2.1创建表,在SQLServer2000中，每个数据库中最多可以创建200万个表，用户创建数据库表时，最多可以定义1024列，也就是可以定义1024个字段。SQLServer2000提供了两种方法创建数据库表，第一种方法是利用企业管理器（EnterpriseManager）创建表；另一种方法是利用Transact-SQL语句中的create命令创建表。,1.利用EnterpriseManager创建表,在EnterpriseManager中，展开指定的服务器和数据库，打开想要创建新表的数据库，用右键单击表对象，从弹出的快捷菜单中选择新建表选项，或者在工具栏中选择图标，就会出现新建表对话框，在该对话框中，可以定义列的以下属性：列名称、数据类型、长度、精度、小数位数、是否允许为空、缺省值、标识列、标识列的初始值、标识列的增量值和是否有行的标识。然后根据提示进行设置。,2.利用create命令创建表,其语法形式如下：CREATETABLEdatabase_name.owner.|owner.table_name(|column_nameAScomputed_column_expression|，n)ONfilegroup|DEFAULT,创建表的各参数的说明如下：,database_name：用于指定在其中创建表的数据库名称。owner：用于指定新建表的所有者的用户名。table_name：用于指定新建的表的名称。column_name：用于指定新建表的列的名称。computed_column_expression：用于指定计算列的列值的表达式。ONfilegroup|DEFAULT：用于指定存储表的文件组名。TEXTIMAGE_ON：用于指定text、ntext和image列的数据存储的文件组。data_type：用于指定列的数据类型。DEFAULT：用于指定列的缺省值。,constant_expression：用于指定列的缺省值的常量表达式。IDENTITY：用于指定列为标识列。Seed：用于指定标识列的初始值。Increment：用于指定标识列的增量值。NOTFORREPLICATION：用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用，即不足的生成列值，使得复制的数据行保持原来的列值。ROWGUIDCOL：用于指定列为全球唯一鉴别行号列。COLLATE：用于指定表使用的校验方式。column_constraint和table_constraint：用于指定列约束和表约束。,例子5-3：创建了一个雇员信息表,其SQL语句的程序清单如下：CREATETABLEemployee(numberintnotnull,namevarchar(20)NOTNULL,sexchar(2)NULL,birthdaydatetimenull,hire_datedatetimeNOTNULLDEFAULT(getdate()professional_titlevarchar(10)null,salarymoneynull,memontextnull),5.2.2增加、删除和修改字段,利用企业管理器增加、删除和修改字段。在企业管理器中，打开指定的服务器中要修改表的数据库，用右键单击要进行修改的表，从弹出的快捷菜单中选择设计表选项，则会出现设计表对话框，在该对话框中，可以利用图形化工具完成增加、删除和修改字段的操作。利用Transact-SQL语言中的altertable命令增加、删除和修改字段。,利用Transact-SQL语言中的altertable命令增加、删除和修改字段的各参数的说明如下：,table：用于指定要修改的表的名称。ALTERCOLUMN：用于指定要变更或者修改数据类型的列。column_name：用于指定要更改、添加或删除的列的名称。new_data_type：用于指定新的数据类型的名称。precision：用于指定新的数据类型的精度。scale：用于指定新的数据类型的小数位数。NULL|NOTNULL：用于指定该列是否可以接受空值。ADD|DROPROWGUIDCOL：用于指定在某列上添加或删除ROWGUIDCOL属性。,ADD：用于指定要添加一个或多个列定义、计算列定义或者表约束。computed_column_expression：用于指定一个计算列的计算表达式。WITHCHECK|WITHNOCHECK：用于指定已经存在于表中的数据是否需要使用新添加的或者刚启用的FOREIGNKEY或CHECK约束进行验证。DROPCONSTRAINTconstraint_name|COLUMNcolumn_name：用于指定从表中删除的约束或者列的名称。,CHECK|NOCHECKCONSTRAINT：用于指定启用或禁用FOREIGNKEY或者CHECK约束。ALL：用于指定使用NOCHECK选项禁用所有的约束，或者使用CHECK选项启用所有约束。ENABLE|DISABLETRIGGER：用于指定启用或禁用触发器。当一个触发器被禁用时，它对表的定义依然存在；然而，当在表上执行INSERT、UPDATE或DELETE语句时，触发器中的操作将不执行，除非重新启用该触发器。ALL：用于指定启用或禁用表中所有的触发器。trigger_name：指定要启用或禁用的触发器名称。,例子5-4：创建了一个雇员信息表,其SQL语句的程序清单如下：createtableemployees(idchar(8)primarykeynamechar(20)notnull,departmentchar(20)null,memochar(30)nullageintnull,)altertableemployeesaddsalaryintnulldropcolumnagealtercolumnmemovarchar(200)null,5.2.3创建、删除和修改约束,在SQLServer2000中有5种约束：主键约束（primarykeyconstraint）唯一性约束（uniqueconstraint）检查约束（checkconstraint）缺省约束（defaultconstraint）外部键约束（foreignkeyconstraint）,1.主键约束,主键的添加、删除和修改操作方法有两种：（一）企业管理器操作法，在企业管理器中，用右键单击要操作的数据库表，从弹出的快捷菜单中选择设计表选项，然后根据提示操作。（二）Transact-SQL语句操作法。其语法形式如下：CONSTRAINTconstraint_namePRIMARYKEYCLUSTERED|NONCLUSTERED（column_name,n）,主键约束各参数的说明如下,constraint_name：用于指定约束的名称，约束的名称在数据库中应该是唯一的。如果不指定，则系统会自动生成一个约束名。LUSTERED|NONCLUSTERED：用于指定索引的类型，即聚簇索引或者非聚簇索引，CLUSTERED为默认值。column_name：用于指定主键的列名。主键最多由16个列组成。,例子5-5,在执行创建产品信息表的操作时，指定产品编号为主键值，并且创建一个聚簇索引。createtableproducts(idchar(10)notnull,namechar(20)notnullpricemoneydefault0.01quantitysmallintnullconstraintpk_idprimarykeyclustered(id),2.唯一性约束,唯一性约束用于指定一个或者多个列的组合的值具有唯一性，以防止在列中输入重复的值。当使用唯一性约束时，需要考虑以下几个因素：使用唯一性约束的字段允许为空值。一个表中可以允许有多个唯一性约束。可以把唯一性约束定义在多个字段上。唯一性约束用于强制在指定字段上创建一个唯一性索引。缺省情况下，创建的索引类型为非聚簇索引。,创建和修改唯一性约束的操作方法,创建和修改唯一性约束的操作方法有两种：（一）企业管理器操作法，通过企业管理器可以完成创建和修改唯一性约束的操作。（二）Transact-SQL语句操作法。CONSTRAINTconstraint_nameUNIQUECLUSTERED|NONCLUSTERED（column_name,n）,3.检查约束,一个列级检查约束只能与限制的字段有关；一个表级检查约束只能与限制的表中字段有关。一个表中可以定义多个检查约束。每个CREATETABLE语句中每个字段只能定义一个检查约束。在多个字段上定义检查约束，则必须将检查约束定义为表级约束。当执行INSERT语句或者UPDATE语句时，检查约束将验证数据。检查约束中不能包含子查询。,创建检查约束常用的操作方法有两种：,1.企业管理器操作法。使用企业管理器创建检查约束，与创建唯一性约束类似。2.Transact-SQL语句操作法。检查约束的Transact-SQL语句操作法，其语法形式如下：CONSTRAINTconstraint_nameCHECKNOTFORREPLICATION(logical_expression),例子5-7：,创建了一个学生信息表，其中输入性别字段值时，只能接受“F”或者“M”，并且为phonenum字段创建检查约束，限制只能输入类似01080798654之类的数据，而不能随意输入其他数据。createtablestudent(Idchar(8)namechar(8)sexchar(2)phonenumintconstraintchk_sexcheck（sexin（F,M）Constraintchk_phonenumcheck（phonenumlike(010)0-90-90-90-90-90-90-90-9）,4.缺省约束,使用缺省约束时，应该注意以下几点：每个字段只能定义一个缺省约束。如果定义的缺省值长于其对应字段的允许长度，那么输入到表中的缺省值将被截断。不能加入到带有IDENTITY属性或者数据类型为timestamp的字段上。如果字段定义为用户定义的数据类型，而且有一个缺省绑定到这个数据类型上，则不允许该字段有缺省约束。,5.外部键约束,外部键约束用于强制参照完整性，提供单个字段或者多个字段的参照完整性。当使用外部键约束时，应该考虑以下几个因素：外部键约束提供了字段参照完整性。外部键从句中的字段数目和每个字段指定的数据类型必须和REFERENCES从句中的字段相匹配。外部键约束不能自动创建索引，需要用户手动创建。,用户想要修改外部键约束的数据，必须有对外部键约束所参考表的SELECT权限或者REFERENCES权限。参考同一表中的字段时，必须只使用REFERENCES子句，不能使用外部键子句。一个表中最多可以有31个外部键约束。在临时表中，不能使用外部键约束。主键和外部键的数据类型必须严格匹配。,5.2.4查看表格,查看表格的定义2.查看表格中的数据3.查看表格与其它数据库对象的依赖关系4.利用系统存储过程Sp_help查看表的信息,例子5-10：,显示当前数据库中所有对象的信息；显示表word的信息。在查询分析器（QueryAnalyzer）中它们对应的语句和运行结果如图5-24(a)和图5-24(b)所示。,图5-24(a)当前数据库对象显示窗口,图5-24(b)表word显示窗口,5.2.5删除表,1.利用企业管理器删除表在企业管理器中，展开指定的数据库和表格项，用右键单击要删除的表，从快捷菜单中选择“除去表”选项，则会出现除去对象对话框。单击“全部删除”按钮，即可删除表。2.利用DROPTABLE语句删除表DROPTABLE语句可以删除一个表和表中的数据及其与表有关的所有索引、触发器、约束、许可对象。DROPTABLE语句的语法形式如下：DROPTABLEtable_name,5.3.1创建视图5.3.2修改和重命名视图5.3.3查看视图信息、删除视图5.3.4通过视图修改记录,5.3视图操作,视图是从一个或者多个表或视图中导出的表，其结构和数据是建立在对表的查询基础上的。和真实的表一样，视图也包括几个被定义的数据列和多个数据行，但从本质上讲，这些数据列和数据行来源于其所引用的表。因此，视图不是真实存在的基础表而是一个虚拟表，视图所对应的数据并不实际地以视图结构存储在数据库中，而是存储在视图所引用的表中。,视图的概念：,使用视图的优点和作用,可以使视图集中数据、简化和定制不同用户对数据库的不同数据要求。使用视图可以屏蔽数据的复杂性，用户不必了解数据库的结构，就可以方便地使用和管理数据，简化数据权限管理和重新组织数据以便输出到其他应用程序中。视图可以使用户只关心他感兴趣的某些特定数据和他们所负责的特定任务，而那些不需要的或者无用的数据则不在视图中显示。视图大大地简化了用户对数据的操作。,视图可以让不同的用户以不同的方式看到不同或者相同的数据集。在某些情况下，由于表中数据量太大，因此在表的设计时常将表进行水平或者垂直分割，但表的结构的变化对应用程序产生不良的影响。视图提供了一个简单而有效的安全机制。,5.3.1创建视图,创建视图时应该注意以下情况：只能在当前数据库中创建视图。如果视图引用的基表或者视图被删除，则该视图不能再被使用，直到创建新的基表或者视图。如果视图中某一列是函数、数学表达式、常量或者来自多个表的列名相同，则必须为列定义名称。不能在视图上创建索引，不能在规则、缺省、触发器的定义中引用视图。当通过视图查询数据时，SQLServer要检查以确保语句中涉及的所有数据库对象存在，而且数据修改语句不能违反数据完整性规则。视图的名称必须遵循标识符的规则，且对每个用户必须是唯一的。此外，该名称不得与该用户拥有的任何表的名称相同。,1.利用企业管理器创建视图,图5-26，图5-27(a)，5-27(b)，图5-28，图5-29，图5-30，是利用企业管理器创建视图的具体操作步骤，用户可根据图中提示创建视图。,图5-26选择新建视图选项对话框,图5-27(a)新建视图对话框,图5-27(b)添加表菜单,图5-28添加表对话框,图5-29选择视图字段对话框,图5-30视图属性对话框,2.利用Transact-SQL语句中的CREATEVIEW命令创建视图,使用Transact-SQL语句中的CREATEVIEW创建视图的语法形式如下：CREATEVIEW.view_name(column,.n)WITH,.nASselect_statementWITHCHECKOPTION:=ENCRYPTION|SCHEMABINDING|VIEW_METADATA,使用Transact-SQL语句中的CREATEVIEW创建视图的参数说明如下：,view_name用于指定视图的名称，column用于指定视图中的字段名称。WITHENCRYPTION表示SQLServer加密包含CREATEVIEW语句文本在内的系统表列。select_statement用于创建视图的SELECT语句，利用SELECT命令可以从表中或者视图中选择列构成新视图的列。WITHCHECKOPTION用于强制视图上执行的所有数据修改语句都必须符合由select_statement设置的准则。,SCHEMABINDING表示在select_statement语句中如果包含表、视图或者引用用户自定义函数，则表名、视图名或者函数名前必须有所有者前缀。VIEW_METADATA表示如果某一查询中引用该视图且要求返回浏览模式的元数据时，那么SQLServer将向DBLIB和OLEDBAPIS返回视图的元数据信息。,例子5-12：,选择表student和teacher中的部分字段和记录创建视图，并且限制表student中的记录只能是名称为“张三”的记录集合，视图定义为view_part，其程序清单如下：createviewview_partasSelectstudent.name,student.age,student.sex,teacher.name,age,salaryfromstudent,teacherwherestudent.name=张三,3.利用向导创建视图,图5-31-图5-38是利用向导创建视图的具体步骤。,图5-31选择工具菜单中的向导命令,图5-32欢迎使用创建视图向导对话框,图5-33选择数据库对话框,图5-34选择对象对话框,图5-35选择字段对话框,图5-36定义限制对话框,图5-37命名视图对话框,图5-38完成创建视图向导对话框,5.3.2修改和重命名视图,1.修改视图（1）利用企业管理器修改视图。（2）使用ALTERVIEW语句修改视图。ALTERVIEWview_name(column,.n)WITHENCRYPTIONASselect_statementWITHCHECKOPTION,2.重命名视图,（1）利用企业管理器重命名视图。（2）可以使用系统存储过程sp_rename修改视图的名称，该过程的语法形式如下：sp_renameold_name,new_name例子5-14：把视图v_all重命名为v_part，其程序清单如下：sp_renamev_all,v_part,5.3.3查看视图信息、删除视图,1.查看视图信息每当创建了一个新的视图后，则在系统说明的系统表中就定义了该视图的存储，因此，可以使用系统存储过程sp_help显示视图特征，使用sp_helptext显示视图在系统表中的定义，使用sp_depends显示该视图所依赖的对象。它们的语法形式分别如下：（1）sp_help数据库对象名称（2）sp_helptext视图（触发器、存储过程）（3）sp_depends数据库对象名称,使用SQLServer查询分析器和企业管理器来显示视图,使用SQLServer查询分析器（QueryAnalyzer）可以方便地显示视图信息，如图5-42所示，该对话框显示了使用sp_helptext存储过程显示视图v_employee的特征。另外，也可以使用企业管理器来显示视图的定义，如图5-43所示。利用企业管理器也可以查看视图的输出数据，如图5-44所示。,图5-42显示视图信息对话框,图5-43视图属性对话框,图5-44视图输出数据窗口,2.删除视图,（1）使用企业管理器删除视图的操作方法为如图5-45和图5-46所示。（2）使用Transact-SQL语句DROPVIEW删除视图的语法形式如下：DROPVIEWview_name,n可以使用该命令同时删除多个视图，只需在要删除的视图名称之间用逗号隔开即可。例子5-15：同时删除视图v_student和v_teacher，其程序清单如下：dropviewv_student,v_teacher,图5-45除去对象对话框,图5-46显示相关性对话框,5.3.4通过视图修改记录,使用视图修改数据时，需要注意以下几点：修改视图中的数据时，不能同时修改两个或者多个基表，可以对基于两个或多个基表或者视图的视图进行修改，但是每次修改都只能影响一个基表。不能修改那些通过计算得到的字段。如果在创建视图时指定了WITHCHECKOPTION选项，那么所有使用视图修改数据库信息时，必须保证修改后的数据满足视图定义的范围。执行UPDATE、DELETE命令时，所删除与更新的数据必须包含在视图的结果集中。如果视图引用多个表时，无法用DELETE命令删除数据。,1.插入数据记录,例子5-16：首先创建了一个新的视图v_employees，该视图基于表employees创建。createviewv_employees(number,name,age,sex,salary)asselectnumber,name,age,sex,salaryfromemployeeswherename=张三然后，通过执行以下语句使用该视图向表employees中添加一条新的数据记录。Insertintov_employeesValues(001,李力,22,m,2000),2.更新数据记录,使用视图可以更新数据记录，但应该注意的是，更新的只是数据库中的基表。例子5-19：创建了一个基于表employees的视图v_employees，然后通过该视图修改表employees中的记录。其程序清单如下：createviewv_employeesasselect*fromemployeesupdatev_employeessetname=张然wherename=张三,3.删除数据记录,使用视图删除记录，可以删除任何基表中的记录，直接利用DELETE语句删除记录即可。但应该注意，必须指定在视图中定义过的字段来删除记录。例子5-20：利用视图v_employees删除表employees中姓名为张然的记录。其程序清单为：deletefromv_employeeswherename=张然,5.4索引操作,5.4.1创建索引5.4.2查看、修改和删除索引,一、什么叫索引,数据库中的索引与书籍中的索引类似，在一本书中，利用索引可以快速查找所需信息，无须阅读整本书。在数据库中，索引使数据库程序无须对整个表进行扫描，就可以在其中找到所需数据。书中的索引是一个词语列表，其中注明了包含各个词的页码。而数据库中的索引是某个表中一列或者若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。,二、索引的作用,通过创建唯一索引，可以保证数据记录的唯一性。可以大大加快数据检索速度。可以加速表与表之间的连接，这一点在实现数据的参照完整性方面有特别的意义。在使用ORDERBY和GROUPBY子句中进行检索数据时，可以显著减少查询中分组和排序的时间。使用索引可以在检索数据的过程中使用优化隐藏器，提高系统性能。,聚集索引与非聚集索引,聚集索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即聚集索引与数据是混为一体的，它的叶节点中存储的是实际的数据。非聚集索引具有完全独立于数据行的结构，使用非聚集索引不用将物理数据页中的数据按列排序。非聚集索引的叶节点存储了组成非聚集索引的关键字值和行定位器。,5.4.1创建索引,SQLServer2000创建索引的方法（1）利用企业管理器中的索引向导创建索引。（2）利用企业管理器直接创建索引。（3）利用Transact-SQL语句中的CREATEINDEX命令创建索引。（4）利用企业管理器中的索引优化向导创建索引。,1.利用索引向导创建索引,如图5-47-图5-52所示。,图5-47欢迎使用创建索引向导对话框,图5-48选择数据库和对象对话框,图5-49显示当前索引信息对话框,图5-50选择创建索引的列对话框,图5-51指定索引选项对话框,图5-52指定索引名称对话框,2.利用企业管理器直接创建索引,其具体步骤如下：（）在企业管理器中，展开指定的服务器和数据库，选择要创建索引的表，用右键单击该表，从弹出的快捷菜单中选择所有任务项的管理索引选项（如图5-53所示），就会出现管理索引对话框，如图5-54所示。在该对话框中，可以选择要处理的数据库和表。（）单击“新建”按钮，则出现新建索引对话框，如图5-55所示。（）选择完成后单击“确定”按钮，即可生成新的索引；单击“取消”按钮，则取消新建索引的操作。,图5-53选择管理索引选项对话框,图5-54管理索引对话框,图5-55新建索引对话框,3.利用Transact-SQL语句中的CREATEINDEX命令创建索引,其语法形式如下：CREATEUNIQUECLUSTERED|NONCLUSTEREDINDEXindex_nameONtable|view(columnASC|DESC,.n)withPAD_INDEX,FILLFACTOR=fillfactor,IGNORE_DUP_KEY,DROP_EXISTING,STATISTICS_NORECOMPUTE,SORT_IN_TEMPDBONfilegroup,CREATEINDEX命令创建索引各参数说明如下：,UNIQUE：用于指定为表或视图创建唯一索引，即不允许存在索引值相同的两行。CLUSTERED：用于指定创建的索引为聚集索引。NONCLUSTERED：用于指定创建的索引为非聚集索引。index_name：用于指定所创建的索引的名称。table：用于指定创建索引的表的名称。view：用于指定创建索引的视图的名称。ASC|DESC：用于指定具体某个索引列的升序或降序排序方向。Column：用于指定被索引的列。PAD_INDEX：用于指定索引中间级中每个页（节点）上保持开放的空间。,FILLFACTOR=fillfactor：用于指定在创建索引时，每个索引页的数据占索引页大小的百分比，fillfactor的值为1到100。IGNORE_DUP_KEY：用于控制当往包含于一个唯一聚集索引中的列中插入重复数据时SQLServer所作的反应。DROP_EXISTING：用于指定应删除并重新创建已命名的先前存在的聚集索引或者非聚集索引。STATISTICS_NORECOMPUTE：用于指定过期的索引统计不会自动重新计算。SORT_IN_TEMPDB：用于指定创建索引时的中间排序结果将存储在tempdb数据库中。ONfilegroup：用于指定存放索引的文件组。,例子5-21：,例子5-21：为表employees创建了一个唯一聚集索引，其程序清单如下：CREATEUNIQUECLUSTEREDINDEXnumber_indONemployees(number)withpad_index,fillfactor=20,ignore_dup_key,drop_existing,statistics_norecompute,例子5-22：,为表employees创建了一个复合索引，其程序清单如下：createindexemployees_cpl_indonemployees(name,age)withpad_index,fillfactor=50,4.利用索引优化向导创建索引,索引优化向导可以完成以下几方面的工作：（1）根据给定的工作负荷，通过使用查询优化器分析该工作负荷中的查询，为数据库推荐最佳索引组合。（2）分析所建议的更改将会产生的影响，包括索引的使用，查询在表之间的分布，以及查询在工作负荷中的性能。（3）推荐为执行一个小型的问题查询集而对数据库进行优化的方法。（4）通过设定高级选项如磁盘空间约束、最大查询语句数和每个索引最多对应字段数等，允许定制推荐方式。,利用索引优化向导创建和优化索引的具体步骤如下：,如图5-56-图5-65所示。,图5-56欢迎使用索引优化向导对话框,图5-57选择服务器和数据库对话框,图5-58连接服务器对话框,图5-59选择服务器对话框,图5-61修改缺省优化参数对话框,图5-62选择要优化的表对话框,图5-63索引建议对话框,图5-64索引分析对话框,图5-65完成索引优化向导对话框,5.4.2查看、修改和删除索引,1.利用企业管理器查看、修改和删除索引要查看和修改索引的详细信息，可以在企业管理器中，展开指定的服务器和数据库项，用右键单击要查看的表，从弹出的快捷菜单中选择所有任务子菜单中的“管理索引”选项，则会出现管理索引对话框。选择要查看或者修改的索引，单击“编辑”按钮，就会出现修改索引对话框。在该对话框中，可以修改索引的大部分设置，还可以直接修改其SQL脚本，只需按下“编辑SQL”按钮，即可出现编辑SQL脚本对话框，其中可以编辑、测试和运行索引的SQL脚本。要删除索引，可以在企业管理器中，从管理索引对话框中或者表的属性对话框中，选择要删除的索引，单击“删除”按钮，即可删除索引。,2.用系统存储过程查看和更改索引名称,系统存储过程sp_helpindex可以返回表的所有索引信息，其语法形式如下：sp_helpindexobjname=name其中objname=name参数用于指定当前数据库中的表的名称。另外，系统存储过程sp_rename可以用来更改索引的名称，其语法形式如下：sp_renameobjname=object_name,newname=new_name,objtype=object_type,例子5-23：,更改employees表中的索引employees_name_index名称为employees_name_ind，其程序清单如下：Execsp_renameemployees.employees_name_index,employees_name_ind,index,3.使用Transact-SQL语句中的DROPINDEX命令删除索引,当不再需要某个索引时，可以将其删除，DROPINDEX命令可以删除一个或者多个当前数据库中的索引，其语法形式如下：DROPINDEXtable.index|view.index,.n其中，table|view用于指定索引列所在的表或索引视图；index用于指定要删除的索引名称。注意，DROPINDEX命令不能删除由CREATETABLE或者ALTERTABLE命令创建的主键或者唯一性约束索引，也不能删除系统表中的索引。,5.5存储过程,5.5.1创建存储过程5.5.2执行存储过程5.5.3查看和修改存储过程5.5.4重命名和删除存储过程,存储过程的概念,SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。在SQLServer中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。,5.5.1创建存储过程,在SQLServer中，可以使用三种方法创建存储过程：使用创建存储过程向导创建存储过程。利用SQLServer企业管理器创建存储过程。使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程。,创建存储过程时，需要确定存储过程的三个组成部分：,所有的输入参数以及传给调用者的输出参数。被执行的针对数据库的操作语句，包括调用其它存储过程的语句。返回给调用者的状态值，以指明调用是成功还是失败。,1.使用创建存储过程向导创建存储过程,在企业管理器中，选择工具菜单中的向导选项，选择“创建存储过程向导”（如图3-1所示），则出现欢迎使用创建存储过程向导对话框，如图5-71所示。根据图5-71-图5-76提示可完成创建存储过程。,图3-1新建SQLServer组,图5-71欢迎使用创建存储过程向导对话框,图5-72选择数据库对话框,图5-73选择数据库对象对话框,图5-74完成创建存储过程向导对话框,图5-75编辑存储过程属性对话框,图5-76编辑存储过程SQL对话框,2.使用SQLServer企业管理器创建存储过程,（）在SQLServer企业管理器中，选择指定的服务器和数据库，用右键单击要创建存储过程的数据库，在弹出的快捷菜单中选择“新建”选项，再选择下一级菜单中的“存储过程”选项，如图5-77所示；或者用右键单击存储过程图标，从弹出的快捷菜单中选择“新建存储过程”选项，如图5-78所示。均会出现创建存储过程对话框，如图5-79所示。（）在文本框中可以输入创建存储过程的T_SQL语句，单击“检查语法”，则可以检查语法是否正确；单击“确定”按钮，即可保存该存储过程。如果要设置权限，单击“权限”按钮，如图5-80所示。,图5-77选择新建存储过程对话框（1）,图5-78选择新建存储过程对话框（2）,图5-79新建存储过程对话框,图5-80设置权限对话框,3.使用Transact-SQL语句中的CREATEPROCEDURE命令创建存储过程,创建存储过程前，应该考虑下列几个事项：不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。存储过程是数据库对象，其名称必须遵守标识符规则。只能在当前数据库中创建存储过程。一个存储过程的最大尺寸为128M。,使用CREATEPROCEDURE创建存储过程的语法形式如下：,CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,用CREATEPROCEDURE创建存储过程的语法参数的意义如下：,procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。parameter：过程中的参数。在CREATEPROCEDURE语句中可以声明一个或多个参数。data_type：用于指定参数的数据类型。VARYING：用于指定作为输出OUTPUT参数支持的结果集。Default：用于指定参数的默认值。OUTPUT：表明该参数是一个返回参数。,RECOMPILE：表明SQLServer不会保存该存储过程的执行计划。ENCRYPTION：表示SQLServer加密了syscomments表，该表的text字段是包含CREATEPROCEDURE语句的存储过程文本。FORREPLICATION：用于指定不能在订阅服务器上执行为复制创建的存储过程。AS：用于指定该存储过程要执行的操作。sql_statement：是存储过程中要包含的任意数目和类型的Transact-SQL语句。,5.5.2执行存储过程,直接执行存储过程可以使用EXECUTE命令来执行，其语法形式如下：EXECUTEreturn_status=procedure_name;number|procedure_name_varparameter=value|variableOUTPUT|DEFAULT,.nWITHRECOMPILE,例子5-27：,使用EXECUTE命令传递单个参数，它执行showind存储过程，以titles为参数值。showind存储过程需要参数(tabname)，它是一个表的名称。其程序清单如下：EXECshowindtitles当然，在执行过程中变量可以显式命名：EXECshowindtabname=titles如果这是isql脚本或批处理中第一个语句，则EXEC语句可以省略：showindtitles或者showindtabname=titles,5.5.3查看和修改存储过程,查看存储过程存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。,（1）使用企业管理器查看用户创建的存储过程,在企业管理器中，打开指定的服务器和数据库项，选择要创建存储过程的数据库，单击存储过程文件夹，此时在右边的页框中显示该数据库的所有存储过程。用右键单击要查看的存储过程，从弹出的快捷菜单中选择属性选项，此时便可以看到存储过程的源代码。,（2）使用系统存储过程来查看用户创建的存储过程,可供使用的系统存储过程及其语法形式如下：sp_help：用于显示存储过程的参数及其数据类型sp_helpobjname=name参数name为要查看的存储过程的名称。sp_helptext：用于显示存储过程的源代码sp_helptextobjname=name参数name为要查看的存储过程的名称。sp_depends：用于显示和存储过程相关的数据库对象sp_dependsobjname=object参数object为要查看依赖关系的存储过程的名称。sp_stored_procedures：用于返回当前数据库中的存储过程列表,2.修改存储过程,存储过程可以根据用户的要求或者基表定义的改变而改变。使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程，但不会更改权限，也不影响相关的存储过程或触发器。其语法形式如下：ALTERPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,例子5-29：,创建了一个名为Oakland_authors的过程，默认情况下，该过程包含所有来自加利福尼亚州奥克兰市的作者。随后授予了权限。然后，当该过程需更改为能够检索所有来自加利福尼亚州的作者时，用ALTERPROCEDURE重新定义了该存储过程。其程序清单如下：,5.5.4重命名和删除存储过程,1.重命名存储过程修改存储过程的名称可以使用系统存储过程sp_rename，其语法形式如下：sp_rename原存储过程名称，新存储过程名称另外，通过企业管理器也可以修改存储过程的名称。,2.删除存储过程,删除存储过程可以使用DROP命令，DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除，其语法形式如下：dropprocedureprocedure,n当然，利用企业管理器也可以很方便地删除存储过程。,5.6触发器,5.6.1创建触发器5.6.2查看、修改和删除触发器5.6.3触发器的应用,触发器,触发器是一种特殊类型的存储过程，它不同于前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名称而被直接调用。触发器是一个功能强大的工具，它使每个站点可以在有数据修改时自动强制执行其业务规则。触发器可以用于SQLServer约束、默认值和规则的完整性检查。,触发器主要优点如下：,触发器是自动的：当对表中的数据作了任何修改（比如手工输入或者应用程序采取的操作）之后立即被激活。触发器可以通过数据库中的相关表进行层叠更改。触发器可以强制限制，这些限制比用CHECK约束所定义的更复杂。,5.6.1创建触发器,创建触发器应该考虑以下几个问题：CREATETRIGGER语句必须是批处理中的第一个语句。创建触发器的权限默认分配给表的所有者，且不能将该权限转给其他用户。触发器为数据库对象，其名称必须遵循标识符的命名规则。虽然触发器可以引用当前数据库以外的对象，但只能在当前数据库中创建触发器。虽然不能在临时表或系统表上创建触发器，但是触发器可以引用临时表。,在含有用DELETE或UPDATE操作定义的外键的表中，不能定义INSTEADOF和INSTEADOFUPDATE触发器。虽然TRUNCATETABLE语句类似于没有WHERE子句（用于删除行）的DELETE语句，但它并不会引发DELETE触发器，因为TRUNCATETABLE语句没有记录。WRITETEXT语句不会引发INSERT或UPDATE触发器。当创建一个触发器时必须指定：名称；在其上定义触发器的表；触发器将何时激发；激活触发器的数据修改语句。,1.使用企业管理器创建触发器,在企业管理器中，展开指定的服务器和数据库项，然后展开要在其上创建触发器的表所在的数据库，用右键单击该表，从弹出的快捷菜单中选择所有任务子菜单下的管理触发器选项，则会出现触发器属性对话框。在该对话框中，在名称文本框中选择新建，然后在文本框中输入触发器文本。单击“检查语法”按钮，则可以检查语法是否正确。单击“应用”按钮，则在名称下拉列表中会有新创建的触发器名称。最后，单击“确定”按钮，即可关闭该对话框，成功创建触发器。,2.使用CREATETRIGGER命令创建触发器,其语法形式如下：CREATETRIGGERtrigger_nameONtable|viewWITHENCRYPTIONFOR|AFTER|INSTEADOFDELETE,INSERT,UPDATEWITHAPPENDNOTFORREPLICATIONAS,IFUPDATE(column)AND|ORUPDATE(column).n|IF(COLUMNS_UPDATED()bitwise_operatorupdated_bitmask)comparison_operatorcolumn_bitmask.nsql_statement.n,例子5-30：,创建了一个触发器，在titles表上创建一个插入、更新类型的触发器，其程序清单如下：USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=reminderANDtype=TR)DROPTRIGGERreminderGOCREATETRIGGERreminderONtitlesFORINSERT,UPDATEASsql_statementsGO,5.6.2查看、修改和删除触发器,1.查看触发器（）使用企业管理