第6章 创建和管理数据库对象

第6章创建(chuàngjiàn)和管理数据库对象计算机系胡骏共八十五页数据库的对象(duìxiàng)数据库的对象包括索引、视图、触发器、存储过程、关系图、用户定义的数据类型、用户定义的函数。索引加快查询速度视图控制用户对数据的访问触发器对表进行插入(chārù)、更改、删除操作时自动运行的命令存储过程完成特定功能的命令。用户定义的数据类型丰富数据类型的种类，方面用户使用用户定义的函数灵活的为各种计算提供函数支持。共八十五页6.1创建和管理(guǎnlǐ)索引SQL访问(fǎngwèn)表中数据的方式：①表扫描方式②遍历索引共八十五页SQL访问表中数据(shùjù)的方式共八十五页使用(shǐyòng)索引的优缺点优点：加快查询搜索数据的速度缺点：若对索引列上数据进行删除、更新、插入操作所耗费的时间比没有(méiyǒu)索引时要长。结论：小心设定索引。共八十五页6.1.1索引的设计原则(yuánzé)和索引类型1.索引设计原则：⑴创建索引对精确查询、范围查询、以及外键约束都是有利的。⑵一个表中有较多索引，影响数据的添加、更新(gēngxīn)、删除时的性能。⑶小型表不需要索引，因为遍历表所花费的时间会比遍历索引短。共八十五页6.1.1索引的设计原则(yuánzé)和索引类型⑷在频繁搜索的字段上建立(jiànlì)索引：①主键②外键③经常进行范围搜索、精确搜索的字段④按关键字排序的字段共八十五页6.1.1索引(suǒyǐn)的设计原则和索引(suǒyǐn)类型⑸不需要索引的字段①查询中很少涉及(shèjí)的字段②有大量重复值的字段③更新性能比查询性能更重要的列④定义TEXT、NTEXT或IMAGE数据类型的字段⑹对外键创建索引时先创建聚集索引在创建非聚集索引。共八十五页6.1.1索引(suǒyǐn)的设计原则和索引(suǒyǐn)类型2.索引(suǒyǐn)的类型聚集索引：基于数据行的键值在表内排序和存储这些数据行。非聚集索引：具有完全独立于数据行的结构。非聚集索引的最低行包含非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。共八十五页共八十五页共八十五页何时使用(shǐyòng)聚集索引或非聚集索引动作描述使用聚集索引使用非聚集索引列经常被分组排序应应返回某范围内的数据应不应一个或极少不同值不应不应小数目的不同值应不应大数目的不同值不应应频繁更新的列不应应外键列应应主键列应应频繁修改索引列不应应共八十五页测试聚集(jùjí)索引和非聚集(jùjí)索引性能现搭建(dājiàn)好测试平台：学生基本情况表中有100万条记录。运行程序：select*from学生基本情况表where出生日期>‘1980-3-20’①当在出生日期上建立非聚集索引时用时：53763毫秒（54秒）

②当在出生日期上建立聚集索引时用时：2423毫秒（2秒）想一想为什么？共八十五页怎样测量查询(cháxún)时间？declare@ddatetimeset@d=getdate()select*from学生基本(jīběn)情况表where出生日期>‘1980-3-20‘select[执行时间(毫秒)]=Datediff(ms,@d,getdate())共八十五页6.1.2创建(chuàngjiàn)索引1索引向导2企业(qǐyè)管理器3查询分析器4企业管理器中索引优化向导共八十五页利用T-SQL语言(yǔyán)创建、查看、修改索引创建索引语法格式(géshi)：CREATE[UNIQUE][CLUSTERED|NONCLUSTERED]INDEXindex_nameON{table|view}查看索引语法格式：sp_helpindextable_name修改索引名称sp_renameold_index_name,new_index_name,‘index’删除索引dropindexindex_name

共八十五页利用(lìyòng)T-SQL语言创建、查看、修改索引在学生基本情况表中对学号建立聚集(jùjí)索引Createclusteredindexxs_聚集on学生基本情况（学号）把上述索引名称改为学生索引sp_rename‘学生基本情况表.xs_聚集’,’学生索引’,‘index’查看学生基本情况表中索引信息sp_helpindex学生基本情况表删除学生索引dropindex学生基本情况表.学生索引

共八十五页6.2创建(chuàngjiàn)和管理视图视图作为一种基本的数据库对象，是查询一个表或多个表的另一种方法，它是通过把预先定义的查询存储在数据库中，然后就可以再查询语句中调用(diàoyòng)它。提示：①对于查询的表我们把它叫做基表

②视图来自于一个基表还是几个基表对视图中的操作有绝对的影响共八十五页6.2.1视图(shìtú)1.视图的概念是一种虚拟的表或存储查询，它只包含表的一部分，其内容有查询需求定义。2.视图的作用①返回用户需要的数据②是数据库查询简便直观③方便数据的导出④实现(shíxiàn)对创建视图的内部表进行数据修改共八十五页6.2.2创建(chuàngjiàn)视图1.使用(shǐyòng)企业管理器创建视图见录像2.使用T-SQL语言创建视图略共八十五页6.2.3查看(chákàn)视图1.使用企业管理器查看(chákàn)视图共八十五页6.2.3查看(chákàn)视图2.使用存储过程(guòchéng)查看视图共八十五页6.2.4修改、删除(shānchú)及重命名视图1.修改视图ALTERVIEW班级视图ASSELECT专业代码(dàimǎ),系部名称,专业名称FROMdbo.班级2.重命名视图SP_RENAME‘班级视图’,‘班级视图列表’3.删除视图DROPVIEW班级视图列表共八十五页6.2.5使用视图操作(cāozuò)表数据1.使用视图检索(jiǎnsuǒ)数据可以在视图中查询数据SELECT班级名称FROM班级视图WHERE班级名称=‘计算机网络’共八十五页6.2.5使用(shǐyòng)视图操作表数据讨论在视图中添加、修改、删除数据的时候(shíhou)，分为两种情况：1视图来自于一个基表2视图来自于几个不同的基表共八十五页视图(shìtú)与单个基表之间的关系①基表数据更改后，视图数据会更改吗？见录像实验结果：基表数据更改后，视图数据也更改。②视图中数据更改后，基表数据会更改吗？见录像实验结果：视图数据更改后，基表数据也会更改。③视图更改后，其他(qítā)引用这个视图的视图会更改吗？实验结果：会（自己试验）共八十五页视图(shìtú)与单个基表之间的关系④修改视图中的数据，基表中的数据跟着进行修改吗？会（自己试验(shìyàn)）。⑤删除视图中的数据，基表中的数据会跟着删除吗？会（自己试验）。共八十五页视图(shìtú)与多个基表之间的关系共八十五页视图与多个(duōɡè)基表之间的关系共八十五页视图(shìtú)与多个基表之间的关系①基表数据更改后，视图数据会更改吗？实验结果(jiēguǒ)：基表数据更改后，视图数据也更改。②视图更改后，其他引用这个视图的视图会更改吗？实验结果：会（自己试验）共八十五页视图与多个(duōɡè)基表之间的关系③视图中数据(shùjù)更改后，基表数据(shùjù)会更改吗？实验结果：视图数据更改后，多个基表数据也会更改。共八十五页视图(shìtú)与多个基表之间的关系④向视图(shìtú)中插入数据，基表中的数据跟着进行修改吗？不会（自己试验）。共八十五页视图与多个(duōɡè)基表之间的关系⑤删除视图(shìtú)中的数据，基表中的数据会跟着删除吗？不会（自己试验）。共八十五页总结(zǒngjié)查询插入删除更新视图与单个基表能查询视图中插入数据影响到基表视图中删除数据影响到基表视图中更新数据影响到基表视图与多个基表能查询视图中插入数据不成立视图中删除不成功视图中更新数据影响到基表共八十五页总结(zǒngjié)不能对视图某些列进行数据操作。不允许改变是某个计算的结果的列，例如包括计算值、内建函数或行聚合函数的列

若修改影响了在视图中未引用的列，也有可能引起错误。例如往视图中插入行的时候，其他(qítā)未被引用的列不允许空值且没有默认值共八十五页6.3创建和管理(guǎnlǐ)存储过程存储过程的定义：将固定的操作使用控制流语句编写(biānxiě)出来经过预编译存储在数据库中，在需要运行时进行调用，以实现某个特定的任务。优点：①允许模块化的程序设计②更快的执行速度③有效降低网络流量④较好的安全机制共八十五页6.3创建(chuàngjiàn)和管理存储过程存储过程的分类：系统存储过程：sp_rename，sp_help，……用户自定义存储过程：自己编写

扩展(kuòzhǎn)存储过程：仅存在于MASTER数据库中。用于外部程序编写好放在SQL服务器上共八十五页6.3.1创建存储(cúnchǔ)过程1.使用T-SQL语言(yǔyán)创建存储过程CREATEPROCEDUREprocedure_nameASsql_statement[…n]

共八十五页使用(shǐyòng)T-SQL语言创建存储过程简单应用：createprocedure打印(dǎyìn)

asdeclare@varchar(10)set@var='你好'select@vargo共八十五页注意(zhùyì)①不能将CREATEPROCEDURE语句与其他(qítā)SQL语句组合到单个批处理中。

共八十五页注意(zhùyì)②创建存储过程的权限默认属于数据库所有者，该所有者可以将此权限授予其他用户(yònghù)。在某数据库下有两个用户a和b在a用户下创建了一个存储过程名称叫PROC现在a用户下把使用此存储过程的权利授予bgrantexecuteonctob共八十五页注意(zhùyì)可对存储过程进行(jìnxíng)加密，这样别人无论用什么方法也看不到你的脚本（程序）createprocedure打印withencryptionasdeclare@varchar(10)set@var='你好'select@vargo共八十五页创建带输入(shūrù)参数的存储过程输入参数是指由调用程序(chéngxù)向存储过程传递的参数。它们在创建存储过程语句中被定义，而在执行该存储过程中给出相应的变量值。语法:{@parameter

data_type}[=default]参数：@parameter：参数名，必须以@符号为前缀。data_type：参数的数据类型说明。default：如果执行存储过程时未提供该参数值，则使用默认值。共八十五页创建带输入参数(cānshù)的存储过程例：创建一个(yīɡè)指定姓名学生成绩单的存储过程。createprocedure成绩存储过程

@namechar(10)asselect*from学生成绩视图

where姓名=@name执行存储过程：EXEC成绩存储过程‘张三’或者：EXEC成绩存储过程@name=‘张三’共八十五页创建带输出参数(cānshù)的存储过程从存储过程(guòchéng)中返回一个或多个值。语法：

@参数名数据类型[=默认值]OUTPUT@参数名：存储过程的输出参数名，必须以@符号为前缀。数据类型：该参数的数据类型说明。OUTPUT：指明输出参数。注意，输出参数必须位于所有输入参数说明之后。共八十五页创建(chuàngjiàn)带输出参数的存储过程例：从下图所示的视图(shìtú)中查询指定姓名同学的各科平均分。共八十五页创建带输出参数(cānshù)的存储过程CreateprocedurePROC_查询成绩(@NAMECHAR(10),@平均分intoutput)Asset@平均分=(selectavg(成绩)from学生(xuésheng)成绩视图where姓名=@name)执行：共八十五页思考题创建一个存储过程(guòchéng)能向下面的表中添加一条记录，并写出执行语句共八十五页6.3.2查看(chákàn)修改删除存储过程1.查看存储过程sp_helpsp_helptext2.修改(xiūgǎi)存储过程ALTERPROCEDUREprocedure_nameas3.删除存储过程dropprocedureprocedure_name4.重命名存储过程sp_renameold_name,new_name共八十五页6.4创建(chuàngjiàn)和管理触发器触发器是什么?触发器是一类特殊(tèshū)的存储过程，它们之间的主要区别在于，触发器不允许用户调用，当对表进行插入、删除、修改操作时由系统自动调用并执行，而存储过程必须通过用户的调用才能执行。共八十五页6.4.1触发器作用(zuòyòng)触发器设计的初衷是：实现数据库中相关表的级联修改；但是级联引用完整性中的级联更新选项很好的完成了这项任务。虽然设计的初衷被另外的方法很好很方便的实现，并不表示触发器不具备(jùbèi)应用的价值，相反在数据库中触发器应用越来越广泛。共八十五页6.4.1触发器作用(zuòyòng)其他作用：①检查数据输入的正确性：说明：在数据库中检查数据输入的正确性，可以使用CHECK约束，或者其他约束；但是CHECK约束有其本身的缺陷(quēxiàn)：不能参照其他表中的数据。共八十五页说明(shuōmíng)这里只能用触发器不能用CHECK约束(yuēshù)，因为CHECK约束(yuēshù)只能规定商品销售数量在某个确定值之内，而这里这里销售数量的最大值是随着库存量变化而变化的此时要规定销售数量不大于库存数量怎么办？商品库存数量随时在跟新共八十五页6.4.1触发器作用(zuòyòng)②检查数据修改的正确性：当对表中受触发器保护的数据修改时，触发器不但会自动更新其他表与其相关的数据，还可以自动检查这些数据，只要有一个不符合条件，则修改数据失败。如对于工资表中工资数据的更改规定不可以超过40%，使用触发器可以检查变更前后的数据如果幅度超过40%，那么回滚该操作(cāozuò)事务。共八十五页6.4.2创建(chuàngjiàn)触发器CREATETRIGGER触发器名ON{表|视图}{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}AS[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)][...n]|IF(COLUMNS_UPDATED(){位操作符}更新位标志){比较(bǐjiào)运算符}列的位标志[,…n]}]SQL语句[…n]共八十五页6.4.2创建(chuàngjiàn)触发器触发器的类型：

①after触发器（后触发）这种触发器将在数据(shùjù)操作（insert,delete,update）之后才触发，对变动的数据进行检查，如果发现错误则取消或回滚数据更改。②insteadof触发器（替代触发）这种触发器将在数据操作（insert,delete,update）之前触发，转而去执行触发器所定义的操作共八十五页6.4.2创建(chuàngjiàn)触发器③for触发器（一般触发器）这种触发器就是after触发器。如果在书写程序的时候(shíhou)只有for参数那么系统默认是后触发器。共八十五页注意(zhùyì)触发器使用限制CREATETRIGGER必须是批处理中的第一条语句，并且只能应用到一个表中。触发器只能在当前的数据库中创建(chuàngjiàn)，不过触发器可以引用当前数据库的外部对象。如果一个表的外键在DELETE/UPDATE操作上定义了级联，则不能在该表上定义INSTEADOFDELETE/UPDATE触发器。共八十五页触发器结构(jiégòu)事件(shìjiàn)UPDATEINSERTDELETE条件AFTERINSTEADOF动作各种Transact-SQL语句共八十五页触发器示意图共八十五页共八十五页共八十五页修改(xiūgǎi)触发器名触发器语句(yǔjù)共八十五页AFTER触发器例题(lìtí)此时要规定(guīdìng)销售数量不大于库存数量，怎么办？商品库存数量随时在跟新共八十五页解答(jiědá)共八十五页解答(jiědá)CREATETRIGGER销售触发器ON[dbo].[销售]AFTERUPDATEASdeclare@xsslchar(10),@kcslchar(10)select@xssl=销售数量from销售select@kcsl=商品(shāngpǐn)数量from库存IF@xssl>=@kcslraiserror('销售数量不能大于库存数量',16,1)rollbacktransaction共八十五页结果(jiēguǒ)验证图共八十五页INSTEADOF触发器例题(lìtí)设置替代触发器，不允许库存表进行修改和删除(shānchú)操作。CREATETRIGGER库存触发器ON[dbo].[库存]INSTEADOFUPDATE,DELETEASraiserror(‘不允许更新、删除库存表中记录',16,10)共八十五页结果(jiēguǒ)验证图共八十五页附加内容(nèiróng)：触发器使用的临时表在创建触发器的过程中，数据库系统会自动创建deleted,inserted两个临时表供触发器使用。

①deleted表用于存储被update,delete语句影响的行的副本，当系统对某个附加了触发器的表执delete语句的时候，被删除的记录(jìlù)从原表中消失，但会存储在deleted表中。而执行update语句的时候，更新后的记录(jìlù)会替代原来旧的记录(jìlù)，而旧的记录(jìlù)会传输到deleted表中存储。共八十五页附加内容(nèiróng)：触发器使用的临时表②inserted表用来存储insert,update命令所影响到行的副本。当对一个带有触发器的表进行insert操作(cāozuò)的时候，新的记录不但添加进表中而且会复制到inserted表中。而进行update操作(cāozuò)的时候，表中原有记录被更新，而且更新后的记录会被存储在inserted表中。共八十五页怎样(zěnyàng)看到inserted,deleted表？CREATETRIGGERtr1ON销售(xiāoshòu)FORINSERT,UPDATE,DELETEASSelect*frominsertedSelect*fromdeletedGo共八十五页临时(línshí)表的应用请使用触发器实现(shíxiàn)库存表中商品名称修改，销售表中上品名称也跟着修改。修改前修改后共八十五页临时(línshí)表的应用CREATETRIGGER修改姓名ON库存afterupdateASDECLARE@spmc1varchar(10),@spmc2varchar(10)SELECT@spmc1=商品名称FROMdeleted--从deleted表得到被删除的原商品名称SELECT@spmc2=商品名称FROMinserted--从inserted表得到被更新(gēngxīn)的新商品名称UPDATE库存SET商品名称=@spmc2WHERE商品名称=@spmc1UPDATE销售SET商品名称=@spmc2WHERE商品名称=@spmc1共八十五页练习(liànxí)请使用触发器实现(shíxiàn)库存表和销售表之间的级联删除操作。共八十五页解答(jiědá)Createtrigger级联删除on库存(kùcún)AfterdeleteAsDeclare@商品名称char(10)Select@商品名称=商品名称fromdeletedDeletefrom销售Where商品名称=@商品名称Go共八十五页IFUPDATE(column)参数(cānshù)的使用

IFUPDATE(column)测试在指定的列上进行(jìnxíng)的INSERT或UPDATE操作，不能用于DELETE操作。

例：创建触