数据库对象表的操作.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 addtype typename 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命令创建表 其语法形式如下 CREATETABLE database name owner owner table name column nameAScomputed column expression n ON filegroup DEFAULT 创建表的各参数的说明如下 database name 用于指定在其中创建表的数据库名称 owner 用于指定新建表的所有者的用户名 table name 用于指定新建的表的名称 column name 用于指定新建表的列的名称 computed column expression 用于指定计算列的列值的表达式 ON filegroup 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 DROP ROWGUIDCOL 用于指定在某列上添加或删除ROWGUIDCOL属性 ADD 用于指定要添加一个或多个列定义 计算列定义或者表约束 computed column expression 用于指定一个计算列的计算表达式 WITHCHECK WITHNOCHECK 用于指定已经存在于表中的数据是否需要使用新添加的或者刚启用的FOREIGNKEY或CHECK约束进行验证 DROP CONSTRAINT constraint name COLUMNcolumn name 用于指定从表中删除的约束或者列的名称 CHECK NOCHECK CONSTRAINT 用于指定启用或禁用FOREIGNKEY或者CHECK约束 ALL 用于指定使用NOCHECK选项禁用所有的约束 或者使用CHECK选项启用所有约束 ENABLE DISABLE TRIGGER 用于指定启用或禁用触发器 当一个触发器被禁用时 它对表的定义依然存在 然而 当在表上执行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 namePRIMARYKEY CLUSTERED 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 nameUNIQUE CLUSTERED NONCLUSTERED column name n 3 检查约束 一个列级检查约束只能与限制的字段有关 一个表级检查约束只能与限制的表中字段有关 一个表中可以定义多个检查约束 每个CREATETABLE语句中每个字段只能定义一个检查约束 在多个字段上定义检查约束 则必须将检查约束定义为表级约束 当执行INSERT语句或者UPDATE语句时 检查约束将验证数据 检查约束中不能包含子查询 创建检查约束常用的操作方法有两种 1 企业管理器操作法 使用企业管理器创建检查约束 与创建唯一性约束类似 2 Transact SQL语句操作法 检查约束的Transact SQL语句操作法 其语法形式如下 CONSTRAINTconstraint nameCHECK NOTFORREPLICATION 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 9 0 9 0 9 0 9 0 9 0 9 0 9 0 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 n ASselect statement WITHCHECKOPTION 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 WITHENCRYPTION ASselect statement WITHCHECKOPTION 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删除视图的语法形式如下 DROPVIEW view 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命令创建索引 其语法形式如下 CREATE UNIQUE CLUSTERED NONCLUSTERED INDEXindex nameON table view column ASC DESC n with PAD INDEX FILLFACTOR fillfactor IGNORE DUP KEY DROP EXISTING STATISTICS NORECOMPUTE SORT IN TEMPDB ONfilegroup 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 helpindex objname name 其中 objname name 参数用于指定当前数据库中的表的名称 另外 系统存储过程sp rename可以用来更改索引的名称 其语法形式如下 sp rename objname object name newname new name objtype object type 例子5 23 更改employees表中的索引employees name index名称为employees name ind 其程序清单如下 Execsp rename employees employees name index employees name ind index 3 使用Transact SQL语句中的DROPINDEX命令删除索引 当不再需要某个索引时 可以将其删除 DROPINDEX命令可以删除一个或者多个当前数据库中的索引 其语法形式如下 DROPINDEX table 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创建存储过程的语法形式如下 CREATEPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql 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命令来执行 其语法形式如下 EXEC UTE return status procedure name number procedure name var parameter value variable OUTPUT DEFAULT n WITHRECOMPILE 例子5 27 使用EXECUTE命令传递单个参数 它执行showind存储过程 以titles为参数值 showind存储过程需要参数 tabname 它是一个表的名称 其程序清单如下 EXECshowindtitles当然 在执行过程中变量可以显式命名 EXECshowind tabname titles如果这是isql脚本或批处理中第一个语句 则EXEC语句可以省略 showindtitles或者showind tabname titles 5 5 3查看和修改存储过程 查看存储过程存储过程被创建之后 它的名字就存储在系统表sysobjects中 它的源代码存放在系统表syscomments中 可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程 1 使用企业管理器查看用户创建的存储过程 在企业管理器中 打开指定的服务器和数据库项 选择要创建存储过程的数据库 单击存储过程文件夹 此时在右边的页框中显示该数据库的所有存储过程 用右键单击要查看的存储过程 从弹出的快捷菜单中选择属性选项 此时便可以看到存储过程的源代码 2 使用系统存储过程来查看用户创建的存储过程 可供使用的系统存储过程及其语法形式如下 sp help 用于显示存储过程的参数及其数据类型sp help objname name 参数name为要查看的存储过程的名称 sp helptext 用于显示存储过程的源代码sp helptext objname name 参数name为要查看的存储过程的名称 sp depends 用于显示和存储过程相关的数据库对象sp depends objname object 参数object为要查看依赖关系的存储过程的名称 sp stored procedures 用于返回当前数据库中的存储过程列表 2 修改存储过程 存储过程可以根据用户的要求或者基表定义的改变而改变 使用ALTERPROCEDURE语句可以更改先前通过执行CREATEPROCEDURE语句创建的过程 但不会更改权限 也不影响相关的存储过程或触发器 其语法形式如下 ALTERPROC EDURE procedure name number parameterdata type VARYING default OUTPUT n WITH RECOMPILE ENCRYPTION RECOMPILE ENCRYPTION FORREPLICATION ASsql statement n 例子5 29 创建了一个名为Oakland authors的过程 默认情况下 该过程包含所有来自加利福尼亚州奥克兰市的作者 随后授予了权限 然后 当该过程需更改为能够检索所有来自加利福尼亚州的作者时 用ALTERPROCEDURE重新定义了该存储过程 其程序清单如下 5 5 4重命名和删除存储过程 1 重命名存储过程修改存储过程的名称可以使用系统存储过程sp rename 其语法形式如下 sp rename原存储过程名称 新存储过程名称另外 通过企业管理器也可以修改存储过程的名称 2 删除存储过程 删除存储过程可以使用DROP命令 DROP命令可以将一个或者多个存储过程或者存储过程组从当前数据库中删除 其语法形式如下 dropprocedure procedure n 当然 利用企业管理器也可以很方便地删除存储过程 5 6触发器 5 6 1创建触发器5 6 2查看 修改和删除触发器5 6 3触发器的应用 触发器 触发器是一种特