数据库设计-网上书店.ppt

《数据库设计-网上书店.ppt》由会员分享，可在线阅读，更多相关《数据库设计-网上书店.ppt（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库设计数据库设计网上书店网上书店一、需求分析一、需求分析二、系统功能与流程设计二、系统功能与流程设计三、数据库概念结构设计三、数据库概念结构设计四、数据库逻辑结构设计四、数据库逻辑结构设计五、数据库物理结构设计五、数据库物理结构设计六、提高数据库效率的设计六、提高数据库效率的设计七、数据库的实施七、数据库的实施一、需求分析一、需求分析l1.能通过互联网能通过互联网(Internet)访问网上书店访问网上书店lB/S结构结构 选择合适的开发工具选择合适的开发工具l2.能在网页中浏览图书目录，可按图书类别分能在网页中浏览图书目录，可按图书类别分类浏览。类浏览。l(1)图书应当分类，按何种标准分

2、类图书应当分类，按何种标准分类?分为几类分为几类?是否还有其他分类标准是否还有其他分类标准?又如何分类又如何分类?l(2)哪些信息构成图书目录哪些信息构成图书目录?l(3)是否需要图书搜索功能是否需要图书搜索功能?能按哪些关键词搜索能按哪些关键词搜索?l图书名称、图书号、出版社、单价图书名称、图书号、出版社、单价一、需求分析一、需求分析l3.可浏览所选图书的详细信息。可浏览所选图书的详细信息。l具有包括哪些信息具有包括哪些信息?lISBN，书名，作者，出版社，出版日期，简介，封面，书名，作者，出版社，出版日期，简介，封面，库存数量，单价库存数量，单价l4.浏览图书时可订购图书，生成并提交订单浏

3、览图书时可订购图书，生成并提交订单l(1)在浏览的哪些环节可以订购在浏览的哪些环节可以订购?l浏览图书目录、详细信息时浏览图书目录、详细信息时l(2)一个一个“订单订单”应包含哪些信息应包含哪些信息?l订单号，书号，顾客号，数量，金额，订购日期订单号，书号，顾客号，数量，金额，订购日期l订单管理：创建、增加、修改、删除、确认、结算订单管理：创建、增加、修改、删除、确认、结算一、需求分析一、需求分析l5.根据订单和支付信息发出图书，形成发货信根据订单和支付信息发出图书，形成发货信息（息（“支付信息支付信息”暂不考虑）暂不考虑）l“发货信息发货信息”应当包括哪些信息应当包括哪些信息?l是否需要独立

4、的是否需要独立的“发货单发货单”?l“发货信息发货信息”如何与如何与“订单信息订单信息”联动联动?一、需求分析一、需求分析l6.顾客收到图书之后，对收货予以确认，形成顾客收到图书之后，对收货予以确认，形成收货信息收货信息l“收货信息收货信息”应当包括哪些信息应当包括哪些信息?l是否需要独立的是否需要独立的“收货单收货单”?l“收货信息收货信息”如何与如何与“发货信息发货信息”联动联动?一、需求分析一、需求分析l7.顾客必须注册后才能订购图书顾客必须注册后才能订购图书l(1)顾客登录用的信息顾客登录用的信息l注册账号，密码注册账号，密码l(2)顾客自身的基本信息顾客自身的基本信息l姓名，性别，年

5、龄，地址，邮编，电话，邮箱姓名，性别，年龄，地址，邮编，电话，邮箱l8.应当提供管理员对图书、顾客、订单、发货、应当提供管理员对图书、顾客、订单、发货、收货等信息进行定期维护。收货等信息进行定期维护。l“管理员管理员”信息：管理员信息：管理员ID、姓名、类型、姓名、类型l“管理员管理员”分类：分类：l系统预设的管理员、自定义的管理员系统预设的管理员、自定义的管理员二、系统功能与流程设计二、系统功能与流程设计l1.系统功能系统功能网上网上书店书店前台前台浏览浏览模块模块后台后台管理管理模块模块匿名匿名用户用户注册注册用户用户浏览浏览/查找图书查找图书用户注册用户注册浏览浏览/查找图书查找图书订购

6、图书订购图书/维护订单维护订单支付货款支付货款用户资料维护用户资料维护(管理员管理员)图书管理图书管理用户管理用户管理订单管理订单管理思考：初步设计网思考：初步设计网站首页布局站首页布局?二、系统功能与流程设计二、系统功能与流程设计l2.操作流程操作流程总体流程总体流程网上网上书店书店首页首页网上书店概况网上书店概况浏览浏览/搜索图书搜索图书订购图书订购图书用户登录用户登录用户注册用户注册导航导航返回返回首页首页二、系统功能与流程设计二、系统功能与流程设计l3.操作流程操作流程购书流程购书流程网上网上书店书店首页首页浏览图书浏览图书订购图书订购图书用户管理用户管理登录登录收货收货确认确认支付支

7、付发货发货三、数据库概念结构设计三、数据库概念结构设计(E-R图图)l主要主要E-R图图管理员管理员图书图书客户客户管理管理管理管理订购订购账号账号密码密码书号书号书名书名作者作者单价单价数量数量日期日期账号账号密码密码数量数量姓名姓名地址地址电话电话1nn1nm类别类别姓名姓名四、数据库逻辑结构设计四、数据库逻辑结构设计(关系关系)lE-R图转化为关系模式的方法图转化为关系模式的方法l注：注：“关系模式关系模式”是指一个未确定各个属性的数据类型、是指一个未确定各个属性的数据类型、长度等参数的二维表，称为一个长度等参数的二维表，称为一个“关系关系”。l例：管理员例：管理员(账号账号，姓名，密码

8、，姓名，密码)l(1)每一个实体对应一个关系每一个实体对应一个关系l(2)一对多的联系一对多的联系l当当“联系联系”并无独有的并无独有的“属性属性”时，通常不单独转换为时，通常不单独转换为一个关系模式，而是将一个关系模式，而是将“一方一方”的主码增加到的主码增加到“多方多方”关系模式中，构成其外码。关系模式中，构成其外码。l例如：客户例如：客户(账号账号，密码，姓名，密码，姓名，管理员管理员ID)四、数据库逻辑结构设计四、数据库逻辑结构设计(关系关系)l(3)多对多的联系：转换为一个关系模式多对多的联系：转换为一个关系模式l关系的属性：关系的属性：n方的主键、方的主键、m方的主键、方的主键、“

9、联系联系”的属的属性性l关系的主键：组合关系的主键：组合(n方的主键、方的主键、m方的主键方的主键)l例如：例如：订单订单(客户号，书号客户号，书号，订购数量，订购日期，订购数量，订购日期)l(4)三个以上的多元联系：转换为一个关系模式三个以上的多元联系：转换为一个关系模式l类似于多对多联系的转换方法类似于多对多联系的转换方法l例如：例如：供应商供应商项目项目零件零件供应供应数量数量mpn供应供应(供应商号供应商号,项目号项目号,零件号零件号,数量数量)五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l包括：包括：l(一一)物理结构物理结构l是指含有字段类型、字段长度等参数的二

10、维表结构是指含有字段类型、字段长度等参数的二维表结构(包包括主键、外键括主键、外键)l(二二)字段约束字段约束l主键主键(primary key)、外键、外键(foreign key)约束约束l空空/非空约束非空约束(null,not null)l默认值默认值(default)约束约束l唯一唯一(unique)约束约束l自动增长自动增长(identify)约束约束l检查检查(CHECK)约束约束五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l(一一)表结构定义表结构定义l1.管理员表管理员表(admins)l2.顾客表顾客表(customers)l3.图书表图书表(books

11、)l4.图书分类表图书分类表(booktypes)l5.订单表订单表(orders)写出各个关系表写出各个关系表的结构定义的结构定义五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l(二二)表的约束表的约束l1.SQL Server约束的分类约束的分类l(1)实体完整性约束实体完整性约束主要通过主要通过“主键约束主键约束”来实现，以确保表内任意行记录数据来实现，以确保表内任意行记录数据是可区分的，是不相同的。是可区分的，是不相同的。主键约束主键约束(Primary Key):不重复、不为空不重复、不为空l(2)引用完整性约束引用完整性约束主要是通过主要是通过“外键约束外键约束”

12、来实现，即将一个表的主键添加到来实现，即将一个表的主键添加到另一个表中，称为外键另一个表中，称为外键(Foreign Key)，使两个表联合操作时，使两个表联合操作时能准确识别表内各条记录。能准确识别表内各条记录。外键外键(Foreign Key):五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l(2)域完整性约束域完整性约束:给定列的输入正确性和有效性。给定列的输入正确性和有效性。A.唯一性约束唯一性约束(UNIQUE):要求某列任意两行的值不能相同要求某列任意两行的值不能相同B.标识约束标识约束(IDENTIFY):能自动产生唯一的标识值，一般用能自动产生唯一的标识值，一

13、般用于主键。于主键。C.非空约束：非空约束：Not NullD.默认值约束默认值约束:Default，为字段规定默认值，为字段规定默认值E.检查约束：检查约束：CHECK，为字段的值规定检查机制，如范围，为字段的值规定检查机制，如范围F.触发器、存储过程中定义的约束机制触发器、存储过程中定义的约束机制l说明：说明：l除触发器、存储过程外，有些约束除触发器、存储过程外，有些约束在定义字段时给出在定义字段时给出在定义字段时给出在定义字段时给出，当某个字段具有多个约束，可紧跟其后，排列不分次序。当某个字段具有多个约束，可紧跟其后，排列不分次序。有些有些也可在表结构定义的末尾单独给出约束定义也可在表结

14、构定义的末尾单独给出约束定义也可在表结构定义的末尾单独给出约束定义也可在表结构定义的末尾单独给出约束定义。五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l2.常用约束的书写方法常用约束的书写方法l(1)主键约束主键约束l用法用法1：独立书写：独立书写constraint primary key(字段字段)l用法用法2：紧跟字段定义语句：紧跟字段定义语句字段名字段名 类型类型 constraint primary keyl(2)外键约束外键约束l用法用法1：独立书写：独立书写constraint foreign key(字段字段)references 表名表名(字段名字段名)l

15、用法用法2：类似于：类似于primary key.五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l(3)唯一性约束唯一性约束(Unique)l置于字段之后，用法：置于字段之后，用法：Uniquel主键约束自动拥有唯一性；一个表只能有一个主键约束，但主键约束自动拥有唯一性；一个表只能有一个主键约束，但可以有多个唯一性约束。可以有多个唯一性约束。l(4)空或非空约束空或非空约束l置于字段定义之后，用法：置于字段定义之后，用法：Null，Not Nulll(5)唯一标识值约束唯一标识值约束(Identify)l置于字段定义之后，用法：置于字段定义之后，用法：Identify(初值初

16、值,步长步长)l(6)默认值约束默认值约束(Default)l置于字段定义之后，用法：置于字段定义之后，用法：Default 常量常量/函数函数l例如：例如：bq int null default 1 -字段字段bq:购买数量购买数量 orderdate date default GetDate()五、数据库物理结构设计五、数据库物理结构设计(存储结构存储结构)l(7)检查约束检查约束(check)l用法用法:check(表达式表达式)l例：例：ID_P int not null check(ID_P0)age int check(age=10 and age=20)六、提高数据库效率的设计六

17、、提高数据库效率的设计l包括：包括：l(一一)表的索引表的索引l(二二)表的视图表的视图l(三三)存储过程存储过程l(四四)触发器触发器六、提高数据库效率的设计六、提高数据库效率的设计l(一一)表的索引表的索引l1.索引的分类索引的分类l(1)唯一索引唯一索引以原表为基础，按给定字段创建任意两行值不重复的索引。以原表为基础，按给定字段创建任意两行值不重复的索引。说明：创建了唯一约束，将自动创建唯一索引。尽管唯一索说明：创建了唯一约束，将自动创建唯一索引。尽管唯一索引有助于提高查找信息的速度，但为了获得最佳性能，建议引有助于提高查找信息的速度，但为了获得最佳性能，建议使用主键约束或唯一约束。使用

18、主键约束或唯一约束。l(2)主键索引主键索引为表定义一个主键将自动创建主键索引，主键索引是唯一索为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。引的特殊类型。六、提高数据库效率的设计六、提高数据库效率的设计l(3)聚集索引聚集索引l在聚集索引中，表中各行的物理顺序与键值的逻辑（索在聚集索引中，表中各行的物理顺序与键值的逻辑（索引）顺序相同。表只能包含一个聚集索引。引）顺序相同。表只能包含一个聚集索引。l例如：汉语字（词）典例如：汉语字（词）典默认按拼音排序编排字典中的每页页码。拼音字母默认按拼音排序编排字典中的每页页码。拼音字母a，b，c，dx，y，z就是索引的逻辑顺序，而页

19、码就是索引的逻辑顺序，而页码1，2，3就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑就是物理顺序。默认按拼音排序的字典，其索引顺序和逻辑顺序是一致的。顺序是一致的。六、提高数据库效率的设计六、提高数据库效率的设计l(4)非聚集索引非聚集索引l对非聚集索引，表中各行的物理顺序与键值的逻辑顺序对非聚集索引，表中各行的物理顺序与键值的逻辑顺序不匹配。不匹配。l聚集索引比非聚集索引（聚集索引比非聚集索引（nonclustered index）有更快）有更快的数据访问速度。的数据访问速度。l例如，按笔画排序的索引就是非聚集索引，例如，按笔画排序的索引就是非聚集索引，“1”画的画的字（词）对应的页码

20、可能比字（词）对应的页码可能比“3”画的字（词）对应的画的字（词）对应的页码大（靠后）。页码大（靠后）。l说明：说明：lSQL Server中，一个表只能创建中，一个表只能创建1个聚集索引，多个非个聚集索引，多个非聚集索引。设置某列为主键，该列就默认为聚集索引。聚集索引。设置某列为主键，该列就默认为聚集索引。六、提高数据库效率的设计六、提高数据库效率的设计l2.索引的创建方法索引的创建方法lCREATE UNIQUE CLUSTERED|NONCLUSTEREDINDEX 索引名索引名 ON 表名表名(字段名字段名)WITH FILLFACTOR=nl说明：说明：lUNIQUE表示唯一索引，可

21、选表示唯一索引，可选lCLUSTERED、NONCLUSTERED表示聚集索引还是表示聚集索引还是非聚集索引，可选非聚集索引，可选lFILLFACTOR表示填充因子，指定一个表示填充因子，指定一个0到到100之间的之间的值，该值指示索引页填满的空间所占的百分比值，该值指示索引页填满的空间所占的百分比六、提高数据库效率的设计六、提高数据库效率的设计l3.使用索引的几条原则使用索引的几条原则l(1)可以创建索引的情形可以创建索引的情形该列用于频繁搜索该列用于频繁搜索该列用于对数据进行排序该列用于对数据进行排序l(2)不要创建索引的情形不要创建索引的情形列中仅包含几个不同的值。列中仅包含几个不同的值

22、。表中仅包含几行。为小型表创建索引可能不太划算，因为表中仅包含几行。为小型表创建索引可能不太划算，因为SQL Server在索引中搜索数据所花的时间比在表中逐行搜索在索引中搜索数据所花的时间比在表中逐行搜索所花的时间更长所花的时间更长 六、提高数据库效率的设计六、提高数据库效率的设计l(二二)视图的使用视图的使用l1.什么是视图？什么是视图？l视图是一种数据库对象，为用户提供了一种查询数据表视图是一种数据库对象，为用户提供了一种查询数据表中数据的方式。中数据的方式。视图的定义视图的定义视图的定义视图的定义是由是由select语句构成，并存语句构成，并存储在数据库中。储在数据库中。l l引用视图

23、引用视图引用视图引用视图实质上是引用实质上是引用select语句的查询结果。语句的查询结果。l l视图是虚拟表视图是虚拟表视图是虚拟表视图是虚拟表，具有数据表的一些特性，可以对视图进，具有数据表的一些特性，可以对视图进行查询、修改、删除等操作，还能成为另一个视图所引行查询、修改、删除等操作，还能成为另一个视图所引用的表。用的表。对视图的数据进行修改时，基表的数据也会发生变化，反之对视图的数据进行修改时，基表的数据也会发生变化，反之亦然。亦然。六、提高数据库效率的设计六、提高数据库效率的设计l2.使用视图的优点使用视图的优点l(1)简化查询语句简化查询语句可以把经常使用的联接、投影和查询语句定义

24、为视图，当查可以把经常使用的联接、投影和查询语句定义为视图，当查询时，只需简单地查询视图而隐藏对基表的复杂查询操作。询时，只需简单地查询视图而隐藏对基表的复杂查询操作。l(2)增加可读性增加可读性视图可以定制数据内容、可以使用字段别名。视图可以定制数据内容、可以使用字段别名。l(3)保证数据逻辑独立性保证数据逻辑独立性当数据表结构改变时，只需更改视图定义的查询语句，不需当数据表结构改变时，只需更改视图定义的查询语句，不需更改应用程序代码，保证了数据的逻辑独立性。更改应用程序代码，保证了数据的逻辑独立性。l(4)增加数据的安全性和保密性增加数据的安全性和保密性针对不同用户可以创建不同视图，真正的

25、数据表是不可见、针对不同用户可以创建不同视图，真正的数据表是不可见、不可访问的。不可访问的。六、提高数据库效率的设计六、提高数据库效率的设计l3.视图的创建视图的创建l lCreate ViewCreate View 数据库名数据库名.视图名视图名(列名列名1,列名列名2,)WITH ENCRYPTION ASAS SELECTSELECT语句语句语句语句 WITH CHECK OPTION l参数说明参数说明l“列名列名1,列名列名2,”:是视图的列名，若省略则取数据表的列是视图的列名，若省略则取数据表的列名名l“with encryption”:加密视图加密视图l“with check o

26、ption”：强制对视图执行的所有数据修改语：强制对视图执行的所有数据修改语句都必须符合条件。句都必须符合条件。六、提高数据库效率的设计六、提高数据库效率的设计l例：创建网上图书的简要信息视图例：创建网上图书的简要信息视图l简要信息：书名、作者、简要信息：书名、作者、ISBN、出版社、出版日期、出版社、出版日期l创建视图创建视图lCreate view bSimpInfo(书名书名,作者作者,ISBN,出版社出版社,出版出版日期日期)AS select bName,bAuthor,bISBN,bPub,bDateselect bName,bAuthor,bISBN,bPub,bDate fro

27、m booksfrom booksl引用视图引用视图l在视图在视图bSimpInfo中查询书名含有中查询书名含有“数据库数据库”的图书的图书lselect*from bSimpInfo where 书名书名 like%数据库数据库%六、提高数据库效率的设计六、提高数据库效率的设计l4.视图的管理视图的管理l(1)修改视图定义修改视图定义:Alter viewl(2)更改视图名更改视图名:利用利用SQL Server提供的系统存储过程提供的系统存储过程lExec sp_rename ,l(3)删除视图删除视图lDrop view l(4)对视图插入、更新、删除记录数据对视图插入、更新、删除记录数

28、据(insert,update,delete)l基表数据会立即改变，仍然要满足基表对数据的操作要求基表数据会立即改变，仍然要满足基表对数据的操作要求.六、提高数据库效率的设计六、提高数据库效率的设计l(三三)存储过程存储过程l1.存储过程的概念和分类存储过程的概念和分类l存储过程是由一系列对数据库进行复杂操作的存储过程是由一系列对数据库进行复杂操作的SQL语句、语句、流程控制语句和函数组成的。经过编译后，可以象系统流程控制语句和函数组成的。经过编译后，可以象系统类函数一样作为一个独立的数据库对象进行管理，提供类函数一样作为一个独立的数据库对象进行管理，提供给应用程序调用。给应用程序调用。存储过

29、程具有参数传递、判断和声明变量、返回信息、扩充存储过程具有参数传递、判断和声明变量、返回信息、扩充标准标准SQL语言、可以嵌套调用等特点。语言、可以嵌套调用等特点。l存储过程可以分为系统存储过程和用户自定义存储过程存储过程可以分为系统存储过程和用户自定义存储过程系统存储过程：由系统提供，可直接使用系统存储过程：由系统提供，可直接使用用户自定义存储过程：由用户编写，需先定义后使用用户自定义存储过程：由用户编写，需先定义后使用六、提高数据库效率的设计六、提高数据库效率的设计l2.使用存储过程的优缺点使用存储过程的优缺点l优点：优点：执行速度快执行速度快(创建时经过了语法检查和编译创建时经过了语法检

30、查和编译)有利于模块化程序设计有利于模块化程序设计便于应用程序的维护管理便于应用程序的维护管理减少网络通信量减少网络通信量(在在SQL Server服务器范围内执行服务器范围内执行)保证系统的安全性保证系统的安全性l缺点缺点不能实现复杂的逻辑操作不能实现复杂的逻辑操作(SQL语言所限制语言所限制)并不能实现全部或某些特殊的功能并不能实现全部或某些特殊的功能不宜太多，不然难于记忆和使用。不宜太多，不然难于记忆和使用。六、提高数据库效率的设计六、提高数据库效率的设计l3.创建存储过程创建存储过程l lCreate procedureCreate procedure 形参变量形参变量 数据类型数据类

31、型=默认值默认值 output with recompile|encryptionASAS select语句语句l“过程名过程名”：最多：最多128个字符，必须唯一个字符，必须唯一l“形参变量形参变量”：必须以：必须以开头，最多开头，最多1024个参量个参量l“默认值默认值”：只能是常量或空值，不能是表名：只能是常量或空值，不能是表名/列名等列名等l“output”：规定该变量是用以将执行结果返回的：规定该变量是用以将执行结果返回的lRecomplie:重编译重编译,encryption:加密加密六、提高数据库效率的设计六、提高数据库效率的设计l例：创建存储过程例：创建存储过程pr_sb，使其

32、按书名查找该，使其按书名查找该书的作者书的作者,出版社出版社,出版日期。出版日期。lCreate procedure pr_sb(bnm varchar(50),bau varchar(20)output,bpb varchar(50)output,bdt date output)AS select bau=bName,bpb=bPub,bdt=bDate from books where bName=bnm六、提高数据库效率的设计六、提高数据库效率的设计l4.存储过程的执行存储过程的执行lEXECUTE l例：例：lUse bookslexecute pr_sb 数据库技术数据库技术l说明：

33、说明：l执行存储过程前应当先打开某个数据表执行存储过程前应当先打开某个数据表六、提高数据库效率的设计六、提高数据库效率的设计l5.存储过程的管理存储过程的管理l(1)修改存储过程：修改存储过程：alter procedure，用法同创建，用法同创建l(2)存储过程重命名：存储过程重命名：lexecute sp_rename,l(3)删除存储过程：删除存储过程：drop procedure 六、提高数据库效率的设计六、提高数据库效率的设计l(四四)触发器触发器l1.触发器的概念与特点触发器的概念与特点l它是一段能自动执行的程序，是特殊的存储过程。它是一段能自动执行的程序，是特殊的存储过程。l其其

34、特殊性特殊性特殊性特殊性在于：在于：不允许使用参数，没有返回值。不允许使用参数，没有返回值。不允许用户调用，当对表进行插入、删除、修改等操作时由不允许用户调用，当对表进行插入、删除、修改等操作时由系统自动调用并执行。系统自动调用并执行。六、提高数据库效率的设计六、提高数据库效率的设计l2.触发器的主要用途：实现更复杂的完整性约束触发器的主要用途：实现更复杂的完整性约束l(1)实现数据库中多个表的级联修改实现数据库中多个表的级联修改当修改删除某个表的数据时，其他表的相应数据能自动修改当修改删除某个表的数据时，其他表的相应数据能自动修改外键约束也具有这种作用，且效率更高。外键约束也具有这种作用，且

35、效率更高。l(2)扩展约束、默认对象和规则对象的完整性检查扩展约束、默认对象和规则对象的完整性检查例如例如“check约束约束”在限制数据输入时不能参照其他表中的数在限制数据输入时不能参照其他表中的数据，如在计算据，如在计算“销售金额销售金额=数量数量*单价单价”时，时，“数量数量”不应超不应超过过“库存数量库存数量”，此时用，此时用check约束不能实现这种约束。约束不能实现这种约束。l(3)自动生成数据自动生成数据l(4)自定义复杂的安全权限。自定义复杂的安全权限。六、提高数据库效率的设计六、提高数据库效率的设计l3.触发器的触发方式触发器的触发方式l为数据表的某个字段设置触发器后，当该字

36、段的数据被为数据表的某个字段设置触发器后，当该字段的数据被insert(插入插入),delete(删除删除),update(修改修改)时，触发器时，触发器被激活并执行。被激活并执行。l按激活的时机分为按激活的时机分为“后触发后触发后触发后触发”和和“替代触发替代触发替代触发替代触发”两种方式。两种方式。六、提高数据库效率的设计六、提高数据库效率的设计l(1)后触发后触发l若引发触发器执行的语句通过了各种约束检查，成功执若引发触发器执行的语句通过了各种约束检查，成功执行后才激活并执行触发器程序，称为后触发。行后才激活并执行触发器程序，称为后触发。l特点：特点：若引发触发器执行的语句违反了某种约束

37、，该语句不会执行，若引发触发器执行的语句违反了某种约束，该语句不会执行，则后触发方式的触发器也不被激活。则后触发方式的触发器也不被激活。后触发方式只能创建在数据表上，不能创建在视图上。后触发方式只能创建在数据表上，不能创建在视图上。一个表可以有多个后触发器。一个表可以有多个后触发器。六、提高数据库效率的设计六、提高数据库效率的设计l(2)替代触发替代触发l若激活触发器的语句仅仅起到激活触发器的作用，一旦若激活触发器的语句仅仅起到激活触发器的作用，一旦激活触发器后该语句即停止执行，立即转去执行触发器激活触发器后该语句即停止执行，立即转去执行触发器的程序，激活触发器的语句并不被执行，相当于禁止某的

38、程序，激活触发器的语句并不被执行，相当于禁止某种操作，这种方式称为替代触发。种操作，这种方式称为替代触发。l特点：特点：可以创建在表上，也可以创建在视图上。可以创建在表上，也可以创建在视图上。一个表只能有一个替代触发器。一个表只能有一个替代触发器。六、提高数据库效率的设计六、提高数据库效率的设计l4.SQL Server系统对触发器的管理系统对触发器的管理l(1)触发器作为一种数据库对象，在触发器作为一种数据库对象，在syscommentsyscomment系统表系统表系统表系统表中存储有中存储有其完整的定义信息，在其完整的定义信息，在sysobjectsysobject系统表系统表系统表系统

39、表中有该对象的记录。中有该对象的记录。l(2)触发器被激活时会创建两个临时表：触发器被激活时会创建两个临时表：insertedinserted表和表和表和表和deleteddeleted表表表表，以确保对数据的安全操作。以确保对数据的安全操作。l两个表的结构与激活触发器的原数据表相同。两个表的结构与激活触发器的原数据表相同。l用用insertinsert语句语句语句语句插入记录激活触发器时，系统在原表插入记录的同时，也插入记录激活触发器时，系统在原表插入记录的同时，也会自动把记录插入到会自动把记录插入到inserted表中。表中。l用用deletedelete语句语句语句语句删除记录激活触发器

40、时，系统在原表删除记录的同时，删除记录激活触发器时，系统在原表删除记录的同时，会把删除的记录添加到会把删除的记录添加到deleted表中。表中。l用用updateupdate语句语句语句语句修改记录激活触发器时，系统先在原表删除原有记录，修改记录激活触发器时，系统先在原表删除原有记录，删除的记录被添加到删除的记录被添加到deleted表中，再在原表插入新的记录，并同时插表中，再在原表插入新的记录，并同时插入到入到inserted表。表。l用户可以用用户可以用select语句查询这两个表，但不允许修改。语句查询这两个表，但不允许修改。l触发器执行完后，这两个表将自动删除。触发器执行完后，这两个表

41、将自动删除。六、提高数据库效率的设计六、提高数据库效率的设计l5.创建触发器创建触发器l lCreate triggerCreate trigger owner.触发器名触发器名onon owner.表名表名/视图名视图名 for/after/instead offor/after/instead of insert,update,delete with encryption nor for replicationASAS set nocount -不返回给变量赋值的结果不返回给变量赋值的结果 SQL语句系列语句系列 rollback transaction -事务回滚事务回滚六、提高数据库效

42、率的设计六、提高数据库效率的设计l说明说明l(1)for/after/instead offor/after/instead of:用于指定触发方式用于指定触发方式lFor与与after相同，是为了与以前版本兼容。相同，是为了与以前版本兼容。l其后可以指定其后可以指定“insert,update,delete”命令中的一个或多命令中的一个或多个个l(2)nor for replication:在复制过程中不激活在复制过程中不激活l(3)set nocount:l触发器一般不能有返回值，所以不应有触发器一般不能有返回值，所以不应有select语句进行语句进行查询或给变量赋值查询或给变量赋值(获取

43、被操作数据的语句除外获取被操作数据的语句除外)l如果必须使用变量赋值语句，应使用此选项来避免返回如果必须使用变量赋值语句，应使用此选项来避免返回结果结果六、提高数据库效率的设计六、提高数据库效率的设计l说明说明l(4)SQL语句系列语句系列l即触发器被激活后要执行的即触发器被激活后要执行的SQL代码，其中可以包含获代码，其中可以包含获得被操作数据的得被操作数据的select语句。语句。l对后触发方式，被操作数据一定在两个临时表中。对后触发方式，被操作数据一定在两个临时表中。l如果被操作数据是多值的，可用如果被操作数据是多值的，可用in判断是否包含其中判断是否包含其中被操作数据被操作数据 in(

44、select 被操作字段被操作字段 from 临时表临时表)l如果被操作数据是单值的，可用以下语句获得如果被操作数据是单值的，可用以下语句获得Select 变量变量=被操作字段被操作字段 from 临时表临时表六、提高数据库效率的设计六、提高数据库效率的设计l说明说明l(5)rollback transaction：事务回滚语句：事务回滚语句l对后触发方式，语句执行完毕才执行触发器，如果发现对后触发方式，语句执行完毕才执行触发器，如果发现操作不符合规则，可用该语句取消操作。操作不符合规则，可用该语句取消操作。六、提高数据库效率的设计六、提高数据库效率的设计l几点注意：几点注意：l(1)触发器不

45、能创建在临时表或系统表上，后触发不能创建触发器不能创建在临时表或系统表上，后触发不能创建在视图上。在视图上。l(2)一个触发器只能创建在一个表上，一个表可以有一个替一个触发器只能创建在一个表上，一个表可以有一个替代触发和多个后触发代触发和多个后触发(可以是同一种操作类型，可以同时触可以是同一种操作类型，可以同时触发发)。l(3)由于由于truncate table(删除全部记录删除全部记录)语句删除记录时不被语句删除记录时不被记入事务日志，所以该语句不能激活记入事务日志，所以该语句不能激活delete删除操作的触发删除操作的触发器。器。l(4)如果外键所引用的父表已经创建了对子表级联修改或删如

46、果外键所引用的父表已经创建了对子表级联修改或删除的触发器，则子表不允许创建具有相同动作的替代触发器。除的触发器，则子表不允许创建具有相同动作的替代触发器。l(5)触发器中不能使用触发器中不能使用create,alter,drop等语句。等语句。六、提高数据库效率的设计六、提高数据库效率的设计l例：设有商品表和合同表，现为商品表创建一例：设有商品表和合同表，现为商品表创建一个个“删除商品删除商品”的触发器，当删除商品表中的的触发器，当删除商品表中的某个商品时，把这些商品在合同表中的销售合某个商品时，把这些商品在合同表中的销售合同同时删除，实现两个表的级联删除。同同时删除，实现两个表的级联删除。lCreate trigger 删除商品删除商品 on 商品表商品表after deleteas delete 合同表合同表 where 货号货号 in(select 货号货号 from deleted)