大型数据库管理系统技术、应用与实例分析-基于SQL Server（第3版）单元实验答案

大型数据库管理系统技术、应用与实例分析——基于SQLServer（第3版）实验参考答案实验一1）根据现实世界的组织和工作过程将其转化成E-R图描述。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。确定实体和实体的属性。员工员工性别所在部门号工资出生年月技术职称员工号姓名部门部门部门领导部门名部门地址部门号部门电话部门人数项目项目项目主管项目名称完工日期项目编号所在地方开工日期确定员工和部门的联系、员工和项目间的联系，给联系命名并指出联系的类型。员工员工项目参与nn员工员工部门所属n1确定多对多联系本身的属性。参与参与员工号项目编号职责④画出员工、部门、项目组成的E-R图。员工员工项目参与nn参与n1员工号姓名性别技术职称出生年月所在部门号性别部门部门电话部门领导部门地址部门名部门号开工日期完工日期所在地方项目编号项目名称项目主管部门人数2）将E-R图转换为关系表。①将实体转化为关系表。员工表列名数据类型数据长度可否为空员工号Varchar4NOTNULL姓名Varchar8NULL性别Varchar2NULL出生年月Datetime8NULL所在部门号Varchar4NULL技术职称Varchar10NULL工资Numeric8NULL部门表列名数据类型数据长度可否为空部门号Varchar4NOTNULL部门名Varchar10NULL部门电话Varchar13NULL部门地址Varchar30NULL部门领导Varchar10NULL部门人数Int4NULL项目表列名数据类型数据长度可否为空项目编号Varchar5NOTNULL项目名称Varchar20NULL所在地方Varchar30NULL项目主管Varchar10NULL开工日期Datetime8NULL完工日期Datetime8NULL②将联系转化为关系表。员工表和部门表联系列名数据类型数据长度可否为空员工号Varchar4NOTNULL姓名Varchar8NULL性别Varchar2NULL出生年月Datetime8NULL所在部门号Varchar4NULL技术职称Varchar10NULL工资Numeric8NULL部门号Varchar4NOTNULL员工参与项目联系列名数据类型数据长度可否为空员工号Varchar4NOTNULL项目号Varchar5NOTNULL职责Varchar10NULL③写出表的关系模式并标明各自的主码和外码。员工（员工号，姓名，性别，出生年月，所在部门号，技术职称，工资，所在部门号）员工号是主码；所在部门号是外码，参照部门表中的部门号。部门（部门号，部门名，部门电话，部门领导，部门地址，部门人数）部门号是主码。项目（项目编号，项目名称，所在地方，开工日期，完工日期）项目编号是主码。员工参与项目（员工号，项目编号，职责）员工号，项目编号是主码；员工号是外码，参照员工表中的员工号；项目编号是外码，参照项目表中的项目编号。④确定主要属性的约束条件。员工号由4位数字组成；部门号由4位数字组成；项目编号由字母J和最多两位数字组成；3）设计关系表中的模拟数据。实体转化的表不少于8条记录，联系转化的表不少于15条记录。4）设计对上述关系表的基本操作任务实验二1）安装SQLServer2005。2）查看安装SQLServer2005的目录结构。3）注册服务器并和数据库连接。4）查看设置安全认证模式①打开对象资源管理器。②用右键单击要设置认证模式的服务器，从快捷菜单中选择“属性”选项，则出现SQLServer属性对话框。5）查询编辑器的使用。①选择要执行的数据库“master”。②在编辑器中输入以下语句，注意观察录入文本的颜色。SELECT*FROMsysobjectsWHEREname='sysrowsets'③使用查询菜单的“执行”命令，执行SQL脚本。④将SQL脚本以文件名SQL02-01.SQL保存。④将SQL脚本以文件名SQL02-01.SQL保存。6）创建和使用链接服务器。①在d:\samples上建立mysheet.xls文件，输入数据。②在查询编辑器中输入【例2-3】的例子③用Windows本地用户定义登录映射sp_addlinkedserverN'Myexcel',N'Jet4.0',N'Microsoft.Jet.4.0',N'd:\samples\mysheet.xls',NULL,N'excel5.0'GOsp_addlinkedsrvloginN'Myexcel',false,N'sa',N'soft008',NULLGO④执行查询select*fromMyexcel...sheet1$⑤用SQLServer本地用户定义登录映射。sp_addlinkedserverN'Myexcel',N'Jet4.0',N'Microsoft.Jet.OLEDB.4.0',N'd:\samples\mysheet.xls',NULL,N'excel5.0'GOsp_addlinkedsrvloginN'Myexcel',false,N'sa',N'soft008',NULLGO⑥执行查询。select*fromMyexcel...sheet1$7）分离master数据库，然后在附加进系统。Master应改为用户数据库。实验三1）用对象资源管理器创建数据库数据库名：xmgl数据文件1的逻辑名为：xmgl1，物理名为：xmgl1.mdf,存放在“D:\xmgl”目录下，初始大小为：1MB，增长方式为自动增长，每次增加1MB。数据文件2的逻辑名为：xmgl2，物理名为：xmgl2.ndf,存放在与主数据文件相同的目录下；文件大小为3MB；增长方式为自动增长，每次增加10%。日志文件1的逻辑名为：xmrz1，物理名为：xmrz1.ldf,存放在“D:\xmrz”目录下，初始大小为：1MB，增长方式为自动增长，每次增加10%。右击对象资源管理器中的“数据库”项；在弹出的快捷菜单中选择“新建数据库”。首先在D盘上创建xmgl和xmrz这两个文件夹2）用对象资源管理器查看和修改数据库①在对象资源管理器里展开“数据库”；②选定“xmgl”数据库，右击即可进入“xmgl”数据库属性界面进行查看和修改。3）用SQL命令创建数据库，数据库要求同上。①进入查询编辑器输入以下程序--使用master数据库USEmasterGO--删除xmgl数据库IFEXISTS(SELECT*FROMsysdatabasesWHEREname='xmgl')DROPdatabasexmglGO--创建xmgl数据库createdatabasexmglonprimary(name=xmgl1,filename='d:\xmgl\xmgl1.mdf',size=1,filegrowth=1),(name=xmgl2,filename='d:\xmgl\xmgl2.ndf',size=3,filegrowth=10%)logon(name=xmrz1,filename='d:\xmrz\xmrz1.ldf',size=1,filegrowth=10%)4）用SQL命令修改数据库。在xmgl增加一个名为“xmgl3”的数据文件，在“F:\xmgl”目录下增加，文件名为“xmgl3.ndf”,初始大小为3MB，增长方式为自动增长，每次增加15%。删除“xmgl2”。①进入查询编辑器②输入以下程序--在xmgl增加一个名为“xmgl3”的数据文件在“F:\xmgl”目录下增加，--文件名为“xmgl3.ndf”,初始大小为3MB，增长方式为自动增长，每次增加15%。alterdatabasexmgladdfile(name=xmgl3,filename='F:\xmgl\xmgl3.ndf',size=3,filegrowth=15%)将数据库“xmgl”中的文件xmgl2和刚增加的xmgl3删掉。①进入查询编辑器②输入以下程序--将数据库“xmgl”中的文件xmgl2和刚增加的xmgl3删掉。alterdatabasexmglremovefilexmgl2alterdatabasexmglremovefilexmgl35）删除数据库创建一个数据库”aa”，用企业管理器和SQL命令“DROPdatabaseaa”。方法一：用企业管理器删除方法二：用SQL语句删除实验四(1)创建表①使用对象资源管理器创建②使用DDL语言定义基本表.有一个项目管理（xmgl）数据库,现有四张表，分别是部门表(部门号，部门名，部门电话，部门地址);员工表(员工号，姓名，性别，出生年月，所在部门号）；项目表(项目编号，项目名称，所在地方）以及员工参与项目(员工号，项目编号,职责）。其中一个员工属于一个部门，一个部门有多个员工；一个员工可同时参加多个项目，一个项目有多个员工一起开发。用SQL语言中的DDL语言建立这四张表,设计并定义表的主码和外码，各字段的数据类型自己设计。部门表的创建：createtable部门表(部门号char(4),部门名char(10),部门电话char(13),部门地址char(30),部门人数char(4),primarykey(部门号))员工表的创建：createtable员工表(员工号char(4),姓名char(8),性别char(2),出生年月datetime,所在部门号char(4),primarykey(员工号),foreignkey(所在部门号)references部门表(部门号))项目表的创建：createtable项目表(项目编号char(5),项目名称char(20),所在地方char(30),项目类型char(10),primarykey(项目编号))员工参与项目表的创建：createtable员工参与项目表(员工号char(4),项目编号char(5),职责char(10),primarykey(员工号,项目编号),foreignkey(员工号)references员工表(员工号),foreignkey(项目编号)references项目表(项目编号))所得结果为：(2)从sysobjects、sysindexes和syscolumns，sysrefrences表中查看关于上述创建的表的信息。sysobjects表：sysindexes表：Syscolumns表：Sysrefrences表：（3）使用对象资源管理器定义下面的约束。①员工号是四位数字串，其中第一位和最后一位是取1-9之间的数字，其他是0-9。②项目编号是以字母J开始的其它是数字的4位字符串。③约束性别的取值为‘男’、‘女’，且非空，缺省是男。④约束电话号码的格式：以8892开始的，第5位取234中的一个，其它是数字的8位数字串。（4）使用DDL语言增加修改部分表及字段①在部门表中增加部门领导字段(注意和员工号同域)；在项目表中增加项目主管字段(注意和员工号同域)；②在员工表中增加技术职称和工资字段，其中工资为数字类型；③在项目表中增加开工日期和完工日期字段，类型为日期类型；altertable项目表add开工日期datetimealtertable项目表add完工日期datetime④增加约束工程起始日期小于计划完成日期。altertable项目表addcheck(开工日期<完工日期)⑤修改部门人数字段类型为整型。altertable部门表altercolumn部门人数int⑥删除项目表中的项目类型字段。altertable项目表dropcolumn项目类型实验五（1）调出第4章实验创建的四个表。方法：附加XMGL数据库或运行创建4个表的SQL语句。（2）用SQL语句增加数据语句输入四个表中数据。其中部门表不得少于5个，员工表不得少于10个，项目表不得少于10个，员工参与项目的情况表不得少于20个。（3）设计查询语句并在查询编辑器中进行查询。①求参加'J3'项目的员工姓名--请参加了‘J3’项目的员工姓名select姓名from员工参与项目表,员工表where项目编号='J3'and员工表.员工号=员工参与项目表.员工号②查询'张明'这个职工所参加的项目的项目号，项目名称以及项目所在地方。select项目表.项目编号,项目名称,所在地方from员工参与项目表,员工表,项目表where员工表.员工号=员工参与项目表.员工号and员工参与项目表.项目编号=项目表.项目编号and姓名='张明'③查询参与了所有项目的员工姓名和员工所在的部门。select姓名,所在部门号from员工表wherenotexists(select*from项目表wherenotexists(select*from员工参与项目表where员工号=员工表.员工号and项目编号=项目表.项目编号))④查询没有参与任何一个项目的员工姓名和所在部门。select姓名,所在部门号from员工表wherenotexists(select*from项目表whereexists(select*from员工参与项目表where员工号=员工表.员工号and项目编号=项目表.项目编号))⑤查询所有部门都有员工参与的项目。select项目编号from项目表wherenotexists(select*from员工表wherenotexists(select*from员工参与项目表where员工号=员工表.员工号and项目编号=项目表.项目编号))⑥查询参加了在上海的项目的所有职工的编号、姓名和所在部门。select员工表.员工号,姓名,所在部门号from员工参与项目表,员工表,项目表where员工表.员工号=员工参与项目表.员工号and员工参与项目表.项目编号=项目表.项目编号and所在地方like'%杭州%'⑦列出每个部门职工的工资，部门的平均工资、最高工资、最低工资，工资合计，以及整个单位职工的平均工资总计。select所在部门号,avg(工资)as平均工资,max(工资)as最高工资,min(工资)as最低工资,sum(工资)as工资合计from员工表groupby所在部门号selectavg(工资)as平均工资总计from员工表⑧对所有项目主管的工资增加10%。update员工表set工资=工资*1.1whereexists(select*from项目表where项目表.项目主管=员工表.员工号)查询工资改变情况改变前：改变后：实验六先删除员工表和员工参与项目表上面的所有索引.1）使用对象资源管理器创建、管理索引①为员工表创建一个索引名为“emp_id”的唯一性非聚集索引，索引关键字是“员工号”，填充因子80%。⑵重命名索引，将索引emp_id重命名为员工表_员工号。⑶删除索引员工表_员工号。二、使用T-SQL语句创建、管理索引①为员工表创建一个索引名为emp_id的唯一性非聚集索引，索引关键字是员工号，填充因子80%。createuniquenonclusteredindexemp_idon员工表(员工号asc)withfillfactor=80回到企业管理器查看：②重命名索引，将索引emp_id重命名为员工表_员工号。sp_rename'员工表.emp_id','员工表_员工号'回到企业管理器中查看：③为员工参与项目表创建一个索引名为“员工_项目_index”的非聚集复合索引，索引关键字为“员工号”，升序，项目编号，降序，填充因子50%。createnonclusteredindex员工_项目_indexon员工参与项目表(员工号asc,项目编号desc)withfillfactor=50④删除索引“员工表_员工号”和“员工_项目_index”。dropindex员工参与项目表.员工_项目_index回到企业管理器查看3）索引前后的执行计划①删除员工表中员工号上的主键。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表和员工参与项目表中的员工号都没有索引）select姓名,项目名称,职责from员工表,项目表,员工参与项目表where员工表.员工号=员工参与项目表.员工号and项目表.项目编号=员工参与项目表.项目编号观察执行计划I/O=0.00625+0.0375+0.00632+0.00632=0.05639Cpu开销=0.000105+0.000028+0.000105+0.000106+0.00008+0.00008=0.001944②为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序；按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号没索引，员工参与项目表中的员工号有非聚集索引）createnonclusteredindex员工参与项目表_员工号on员工参与项目表(员工号asc)withfillfactor=80select姓名,项目名称,职责from员工表,员工参与项目表,项目表where员工表.员工号=员工参与项目表.员工号and项目表.项目编号=员工参与项目表.项目编号观察执行计划：I/O=0.00625+0.0375+0.000081+0.00632=0.050151Cpu开销=0.000105+0.000028+0.000105+0.000089+0.000081+0.00008=0.000737③重建员工表中员工号上的主键，删除“员工参与项目_员工号”的非聚集索引。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号没有非聚集索引）dropindex员工参与项目表.员工参与项目表_员工号select姓名,项目名称,职责from员工表,员工参与项目表,项目表where员工表.员工号=员工参与项目表.员工号and项目表.项目编号=员工参与项目表.项目编号观察执行计划：I/O=0.00625+0.000105+0.0375+0.0000080+0.000080=.0006515Cpu开销=0.000105+0.000028+0.000105+0.00375+0.000008+0.000008=0.004148④为员工参与项目表创建一个索引名为“员工参与项目_员工号”的非聚集索引，索引关键字为“员工号”，升序。按员工姓名和项目名称查询对应的职责，然后观察执行计划信息，计算总的I/O和CPU开销。（员工表中员工号有聚集索引，员工参与项目表中的员工号有非聚集索引）createclusteredindex员工参与项目表_员工号on员工参与项目表(员工号asc)withfillfactor=80select姓名,项目名称,职责from员工表,员工参与项目表,项目表where员工表.员工号=员工参与项目表.员工号and项目表.项目编号=员工参与项目表.项目编号I/O=0.00375+0.000105+0.0375+0.000008+0.0000632=0.014005Cpu开销=0.000105+0.000105+0.00375+0.0000089+0.0000080+0.000089=0.00421实验七1）比较自动事务模式和显式事务模式执行SQL的不同。selecttimes=0,*from员工表①以自动事务模式执行下面SQL语句insertinto员工表values('2011','杨阳','男','1990-07-20','1004','销售员',3800)selecttimes=1,*from员工表update员工表set工资=4000where员工号='2011'selecttimes=2,*from员工表deletefrom员工表where员工号='2011'selecttimes=3,*from员工表②以显式事务模式执行SQL语句--进入显式事务模式begintransactioninsertinto员工表values('2011','杨阳','男','1990-07-20','1004','销售员',3800)selecttimes=4,*from员工表--执行提交操作committransactiongoselecttimes=5,*from员工表begintransactionbegintransaction--修改数据update员工表set工资=4000where员工号='2011'selecttimes=6,*from员工表--执行回退操作rollbacktransactiongoselecttimes=7,*from员工表begintransaction--删除数据deletefrom员工表where员工号='2011'selecttimes=8,*from员工表--执行回退操作rollbacktransactiongoselecttimes=9,*from员工表2）对员工表结构进行修改，增加最高学历和毕业院校字段，如果成功提交，否则取消。（用显式事务，如果语句执行成功，则系统变量@@ERROR是0。可以在sysmessages系统表中查看与@@ERROR错误代码相关的文本信息）。altertable员工表add最高学历char,毕业学校char3）仿照【例7-8】执行系统存储过程sp_lock，观察程序执行过程中锁的使用状况。实验八1）变量的使用声明两个字符变量：@i1和@i2,然后将它们转换为整形变量，对@i1赋初值：10，@i2的值为：@i1的值乘以5，再显示@i2的结果。执行程序显示结果。declare@i1char(100),@i2char(100)set@i1='10'set@i2=cast((cast(@i1asint)*5)aschar(100))print@i2go2）分支结构的使用查询某个部门员工参与的项目，如果该部门没有人参与任何项目，就在员工项目表中增加该部门最少一人去参与项目。否则不在该表中增加该部门人员。同时在员工表中对参与项目的员工工资增加200，以上增加和修改要求通过显式事务实现，如果成功提交，否则回退。执行程序显示结果。declare@error1int,@error2intbegintransactionifnotexists(select* from员工参与项目表 where员工号in (select员工号 from员工表 where所在部门号in (select部门号 from部门表 where部门名='销售部')))begininsertinto员工参与项目表values('2010','J4','家电销售')endselect@error1=@@errorupdate员工表set工资=工资+200where员工号in (selectdistinct员工号 from员工参与项目表)select@error2=@@errorif@error1=0and@error2=0begincommittransactionendelsebeginif@error1<>0print'错误发生在查询语句'if@error2<>0print'错误发生在修改语句'rollbacktransactionendselect*from员工表3）循环结构的使用查询员工的基本信息，要求列出员工的职工号，姓名，部门编号，部门，工资，而对工资不是直接显示具体数值，而是进行替换，显示工资级别。1000到1200的，为一级工资，1200到1500的是二级工资，1500到2000的是三级工资，2000到2700的是四级别工资，2800到3700的是五级别工资，高于3700的为高级。要求按部门编号循环分批处理显示。执行程序显示结果。declare@maxNumchar(4),@minNumchar(4)select@maxNum=(selectmax(部门号))from部门表select@minNum=(selectmin(部门号))from部门表while(cast(@minNumasint)<=cast(@maxNumasint))beginselect员工号,姓名,所在部门号,部门名,工资=casewhen工资>=3700then'高级工资'when工资>=2800then'五级工资'when工资>=2000then'四级工资'when工资>=1500then'三级工资'when工资>=1200then'二级工资'when工资>=1000then'一级工资'endfrom员工表,部门表where员工表.所在部门号=部门表.部门号and员工表.所在部门号=@minNumset@minNum=cast((cast(@minNumasint)+1)aschar(4))end4）常用函数的使用①计算从1980年01月01号到当前日期的天数、月数及年数。declare@startdatetime,@enddatetimeset@start=cast('1980-01-01'asdatetime)select@end=getdate()selectdatediff(dd,@start,@end)as'天数',datediff(mm,@start,@end)as'月数',datediff(yy,@start,@end)as'年数'②计算当前日期加上100天之后的日期。declare@olddatetime,@newdatetimeselect@old=getdate(),@new=dateadd(dd,100,@old)select@oldas'当前日期',@newas'100天之后的日期'③将日期2009/10/26转换为2009年10月26日的字符串。declare@datedatetimeset@date=cast('2009-10-26'asdatetime)selectcast((year(@date))aschar(4))+'年'+cast((month(@date))aschar(2))+'月'+cast((day(@date))aschar(2))+'日'④用函数计算字符串‘Iamateacher.’的长度，并使用函数将“student”替换为“teacher”。printlen('Iamateacher.')⑤用函数求“Youareastudent”字符串中，从11开始，长度为7的子串。实验九1）针对员工表创建一个视图,取员工表的前4个属性，要求带WITHENCRYPTION。使用sp_helptext和在syscomments表中分别观察定义的文本。最后利用定义的视图进行查询。ifexists(selecttable_namefrominformation_schema.views wheretable_name='员工表_视图') dropview员工表_视图gocreateview员工表_视图withencryption --加密asselect员工号,姓名,性别,出生年月from员工表go--查询sysobjects表中'员工表_视图'的id号select*fromsysobjectswherename='员工表_视图'--查询syscomments表中text项select*fromsyscommentswhereid='1269579561'--使用sp_helptext观察定义的文本sp_helptext员工表_视图

2）创建一个查询参加所有项目的员工视图“V1_视图”，包括员工号、姓名，所在部门名。并进行查询。ifexists(selecttable_namefrominformation_schema.viewswheretable_name='V1_视图')dropviewV1_视图gocreateviewV1_视图(员工号,姓名,所在部门名)asselect员工号,姓名,部门名from员工表,部门表where员工表.所在部门号=部门表.部门号and员工号in(select员工号from员工表wherenotexists(select*from项目表wherenotexists(select*from员工参与项目表where员工号=员工表.员工号and项目编号=项目表.项目编号)))select*fromV1_视图3）创建只包含部门名是“人事处”的显示部门信息的视图“V2_视图”，不带WITHCHECKOPTION。ifexists(selecttable_namefrominformation_schema.viewswheretable_name='V2_视图')dropviewV2_视图gocreateviewV2_视图asselect*from部门表where部门名='人事处'①在该视图上分别插入部门是“办公室”和“人事处”观察执行结果。insertintoV2_视图values('1006','办公室',88922666,'XX苑XX幢206室',2012)insertintoV2_视图values('1007','人事处',88922777,'XX苑XX幢207室',2013)②分别修改该视图针对部门是“办公室”和“人事处”的其他属性数据，观察执行结果。updateV2_视图set部门电话=88933666where部门名='办公室'updateV2_视图set部门电话=88933777where部门名='人事处'③分别删除部门是“办公室”和“人事处”的记录，分别观察执行情况。deletefromV2_视图where部门名='办公室'deletefromV2_视图where部门名='人事处'4）创建只包含部门名是“人事处”的显示部门信息的视图“V3_视图”，带WITHCHECKOPTION。ifexists(selecttable_namefrominformation_schema.viewswheretable_name='V3_视图')dropviewV3_视图go createviewV3_视图asselect*from部门表where部门名='人事处'withcheckoption①在该视图上分别插入部门是“办公室”和“人事处”的部门数据，观察执行结果。insertintoV3_视图values('1008','办公室',88922888,'XX苑XX幢208室',2014)insertintoV3_视图values('1009','人事处',88922999,'XX苑XX幢209室',2015)②分别修改该视图针对部门是“办公室”和“人事处”的其他属性数据，观察执行结果。updateV3_视图set部门电话=88993322where部门号='1008'updateV3_视图set部门电话=88993355where部门号='1009'③分别删除部门是“办公室”和“人事处”的记录，分别观察执行情况。deletefromV3_视图where部门名='办公室'deletefromV3_视图where部门名='人事处'5）创建查询员工叫“张三”是哪个部门的视图“V4_视图”，然后在该视图里删除“张三”的所有信息，观察执行情况。为什么是这样？ifexists(selecttable_namefrominformation_schema.viewswheretable_name='V4_视图')dropviewV4_视图gocreateviewV4_视图asselect姓名,部门名from员工表,部门表where员工表.所在部门号=部门表.部门号and姓名='张三'deletefromV4_视图where姓名='张三'实验101）定义及使用游标针对员工表定义一个只读游标“CUR1_游标”，逐行显示员工的所有信息。declare@员工号char(4),@姓名char(8),@性别char(8),@技术职称char(10),@工资money,@部门号char(4)declareCUR1_游标cursorforselect员工号,姓名,性别,技术职称,所在部门号,工资from员工表forreadonlyopenCUR1_游标fetchnextfromCUR1_游标into@员工号,@姓名,@性别,@技术职称,@部门号,@工资while@@fetch_status=0beginselect@员工号as员工号,@姓名as姓名,@部门号as部门号,@工资as工资fetchnextfromCUR1_游标into@员工号,@姓名,@性别,@技术职称,@部门号,@工资endcloseCUR1_游标deallocateCUR1_游标2）使用游标修改数据针对员工表定义一个游标“CUR2_游标”，将游标中绝对位置为3的员工姓名改为“杜兰特”，性别改为“男”。declare@员工号char(4),@姓名char(8),@性别char(10),@部门号char(4),@工资moneydeclareCUR2_游标scrollcursorforselect员工号,姓名,所在部门号,工资,性别from员工表forupdateof工资openCUR2_游标fetchnextfromCUR2_游标into@员工号,@姓名,@部门号,@工资,@性别while@@fetch_status=0beginselect@员工号as员工号,@姓名as姓名,@部门号as部门号,@工资as工资,@性别as性别update员工表set姓名='杜兰特'where员工号=2003update员工表set性别='男'where员工号=2003fetchnextfromCUR2_游标into@员工号,@姓名,@部门号,@工资,@性别endcloseCUR2_游标deallocateCUR2_游标3）使用游标删除数据定义一个游标“CUR3_游标”，将员工表中名为“杜兰特”的员工删掉。declare@员工号char(4),@姓名char(8),@性别char(10),@部门号char(4),@工资moneydeclareCUR3_游标scrollcursorforselect员工号,姓名,所在部门号,工资,性别from员工表openCUR3_游标fetchnextfromCUR3_游标into@员工号,@姓名,@部门号,@工资,@性别while@@fetch_status=0beginselect@员工号as员工号,@姓名as姓名,@部门号as部门号,@工资as工资,@性别as性别delete员工表where姓名='杜兰特'fetchnextfromCUR3_游标into@员工号,@姓名,@部门号,@工资,@性别endcloseCUR3_游标deallocateCUR3_游标4）针对项目管理数据库，设计嵌套游标，外层游标“CUR41_游标”显示每个员工的员工号、员工姓名、技术职称，所在部门，内层游标“CUR42_游标”逐个显示当前员工所参加的项目的情况，包括项目号、项目名称、承担职责。declare@员工号char(4),@员工姓名char(8),@技术职称char(10),@所在部门char(10)declare@项目编号char(4),@项目名称char(20),@项目起始日期char(10),@终止日期char(10),@承担职责char(10)declare@messagechar(80)--定义显示每个员工的员工号、员工姓名、技术职称，所在部门declareCUR41_游标cursorforselect员工号,姓名,技术职称,部门名from员工表,部门表where员工表.所在部门号=部门表.部门号--打开游标openCUR41_游标fetchnextfromCUR41_游标into@员工号,@员工姓名,@技术职称,@所在部门while@@fetch_status=0begin--显示员工号、员工姓名、技术职称，所在部门select@message=@员工号+''+@员工姓名+''+@技术职称+''+@所在部门print@message--定义然后逐个显示当前员工所参加的项目的情况declareCUR42_游标cursorforselect项目表.项目编号,项目名称,开工日期,完工日期,职责from项目表,员工参与项目表where项目表.项目编号=员工参与项目表.项目编号and员工号=@员工号openCUR42_游标--打开游标fetchnextfromCUR42_游标into@项目编号,@项目名称,@项目起始日期,@终止日期,@承担职责while@@fetch_status=0begin--显示项目号、项目名称、项目起始日期、终止日期，承担职责select@message=@项目编号+''+@项目名称+''+@项目起始日期+''+@终止日期+''+@承担职责print@messagefetchnextfromCUR42_游标into@项目编号,@项目名称,@项目起始日期,@终止日期,@承担职责endcloseCUR42_游标--关闭游标deallocateCUR42_游标--释放游标fetchnextfromCUR41_游标into@员工号,@员工姓名,@技术职称,@所在部门endcloseCUR41_游标--关闭游标deallocateCUR41_游标--释放游标5）在员工表中增加一列“参加的项目总数”。创建游标“CUR5_游标”，利用游标在员工参与项目表中统计员工参加的项目数，然后将参加的数目填入员工表中的参加的项目总数列中。declare@员工号char(4),@员工参加的项目数intdeclare@项目编号char(4)declare@参加的项目总数intdeclareCUR5_游标cursorforselect员工号,参加的项目总数from员工表forupdateof参加的项目总数openCUR5_游标fetchnextfromCUR5_游标into@员工号,@员工参加的项目数while@@fetch_status=0begindeclareCUR5_游标_1cursorforselect项目编号from员工参与项目表where员工号=@员工号openCUR5_游标_1fetchnextfromCUR5_游标_1into@项目编号select@参加的项目总数=0while@@fetch_status=0beginselect@参加的项目总数=@参加的项目总数+1fetchnextfromCUR5_游标_1into@项目编号endcloseCUR5_游标_1deallocateCUR5_游标_1update员工表set参加的项目总数=@参加的项目总数wherecurrentofCUR5_游标fetchnextfromCUR5_游标into@员工号,@参加的项目总数endcloseCUR5_游标deallocateCUR5_游标实验11execsp_grantdbaccess'dong'1）创建标量型自定义函数①建立一个求阶乘的函数“F1_自定义函数”。createfunctionF1_自定义函数(@nbigint)returnsbigintasbegindeclare@numbigintif(@n<0)set@num=0elseif(@n<2)set@num=1elsebeginset@num=@nwhile(@n-1>0)beginset@num=@num*(@n-1)set@n=@n-1endendreturn(@num)endselectdbo.F1_自定义函数(5)*dbo.F1_自定义函数(3)-dbo.F1_自定义函数(6)as计算结果2）创建内联表值型函数①创建函数“F2_自定义函数”，通过员工号查询员工姓名、年龄、性别和所在部门（注意不是部门编号）。createfunctionF2_自定义函数(@员工号char(4))returnstableasreturn(select姓名as员工姓名,,datediff(yy,cast(出生年月asdatetime),'2011')as年龄,性别,部门名as所在部门from员工表,部门表where员工表.所在部门号=部门表.部门号and员工号=@员工号)select*fromdbo.F2_自定义函数('2001')3）设计多语句表值函数①创建函数“F3_自定义函数”，显示指定部门的每个职工参与的项目数，并在最后一行显示该职工参与项目的总数。createfunctionF3_自定义函数(@部门名char(10))returns@员工参与项目情况表table(员工号char(4),姓名char(8),项目名称char(20),参加的项目总数int)asbegindeclare@员工号char(4),@姓名char(8),@参加的项目总数int--定义游标declare员工参与项目_游标cursorforselect员工号,姓名,参加的项目总数from员工表,部门表where员工表.所在部门号=部门表.部门号and部门名=@部门名open员工参与项目_游标fetchnextfrom员工参与项目_游标into@员工号,@姓名,@参加的项目总数while@@fetch_status=0begin--将当前职工参与的项目情况返回表insert@员工参与项目情况表select员工表.员工号,姓名,项目名称,参加的项目总数from员工表,项目表,员工参与项目表where员工表.员工号=员工参与项目表.员工号and员工参与项目表.项目编号=项目表.项目编号and员工表.员工号=@员工号--插入该职工参与项目的总数insertinto@员工参与项目情况表(员工号,姓名,参加的项目总数)values(@员工号,@姓名,@参加的项目总数)fetchnextfrom员工参与项目_游标into@员工号,@姓名,@参加的项目总数endclose员工参与项目_游标deallocate员工参与项目_游标returnendselect*fromF3_自定义函数('销售部')4）创建一个用户自定义函数“F4_自定义函数”，返回参加某个项目（例如‘J3’）的所有员工的姓名、职称、所在部门的名称，以及每个员工在该项目中的职责和任务。利用该函数进行查询。createfunctionF4_自定义函数(@项目编号char(5))returnstableasreturn(select员工表.员工号,姓名,技术职称,部门号,部门名,项目编号,职责from员工表,员工参与项目表,部门表where部门号=所在部门号and员工表.员工号=员工参与项目表.员工号and项目编号=@项目编号)/*利用该函数进行查询*/declare@项目编号char(5)set@项目编号='J3'select*fromF4_自定义函数(@项目编号)实验121）创建并执行不带参数的存储过程①打开SQL查询编辑器。②针对项目表创建名为“P1_存储过程”的存储过程，要求每页显示5条记录，按任意键显示下一页。③执行“P1_存储过”存储过程进行数据浏览。/*执行储存过程*/usexmglifexists(select*fromsysobjectswherename='P1_存储过程'andtype='p')begindropprocedureP1_存储过程endgocreateprocedureP1_存储过程asselect*from项目表/*执行储存过程*/usexmglifexists(select*fromsysobjectswherename='P1_存储过程'andtype='p'）execP1_存储过程go2）创建并执行带输入参数的存储过程①打开SQL查询编辑器。②部门人数应该等于员工表中对应部门实际员工数，由于有员工调入调出，可能存在不等的情况。编写存储过程“P2_存储过程”，检查指定部门人数的正确性，如果不正确，则进行修改。③显示部门表和员工表数据；然后执行存储过程；再显示部门表和员工表数据，比较数据是否变化。selectcount(员工表.员工号)总人数from部门表,员工表where部门表.部门号=员工表.所在部门号groupby部门号altertable部门表add总人数intusexmglifexists(select*fromsysobjectswherename='P2_存储过程'andtype='p')begindropprocedureP2_存储过程endgocreateprocedureP2_存储过程asselectcount(所在部门号)as总人数,所在部门号into#tempfrom员工表groupby所在部门号declare@maxNumchar(4),@minNumchar(4),@nintselect@maxNum=(selectmax(部门号))from部门表select@minNum=(selectmin(部门号))from部门表while(cast(@minNumasint)<=cast(@maxNumasint))beginfrom#tempwhere所在部门号=@minNumupdate部门表set部门人数=@nwhere部门号=@minNumset@minNum=cast((cast(@minNumasint)+1)aschar(4))endselect*from部门表3）创建带OUTPUT输出参数的存储过程①打开SQL查询编辑器。②设计存储过程“P3_存储过程”，从员工表计算某部门人员平均工资。要求输入参数为部门号，输出参数是该部门的平均工资。③编写主程序，调用存储过程，在主程序中显示指定部门的平均工资。ifexists(select*fromsysobjectswherename='P3_存储过程'andtype='p')begindropprocedureP3_存储过程endgocreateprocedureP3_存储过程(@所在部门号char(4)output,@部门平均工资floatoutput)asselect@所在部门号=所在部门号,@部门平均工资=avg(工资)from员工表groupby所在部门号//执行储存declare@部门平均工资floatexecP3_存储过程'1001',@部门平均工资outputselect'平均分'=@部门平均工资go4）创建并执行带输入参数和返回状态的存储过程①打开SQL查询编辑器。②设计存储过程“P4_存储过程”，完成对员工表的元组插入工作。要求使用输入参数。插入操作成功返出状态值0，失败返出状态值-1。③执行存储过程，如果返回状态值为0，输出“数据插入成功”，否则输出“数据插入失败”。ifexists(select*fromsysobjectswherename='P4_存储过程'andtype='p')begindropprocedureP4_存储过程endgocreateprocedureP4_存储过程(@员工号char(4),@姓名char(8),@性别char(2),@出生年月varchar(60),@所在部门号char(4),@技术职称char(10),@工资money,@参加的项目总数int)asbegintraninsertinto员工表values(@员工号,@姓名,@性别,cast(@出生年月asdatetime),@所在部门号,@技术职称,@工资,@参加的项目总数)if@@error<>0beginrollbacktranreturn-1endelsebegincommittranreturn0enddeclare@statusintexec@status=P4_存储过程'2001','小张','男','1988-03-15','1003','采购部长',4232,12if@status=0print'插入成功'elseprint'插入失败'5）修改和删除存储过程①修改“P1_存储过程”存储过程，要求指定项目编号作为输入参数，并增加WITHENCRYPTION选项。alterprocedureP1_存储过程(@项目编号char(5))withencryptionasselect*from项目表go②查看修改后的“P1_存储过程”存储过程文本。usexmglgoexecsp_helptextP1_存储过程go③执行“P1_存储过程”存储过程declare@项目编号char(5)execP1_存储过程@项目编号go④删除“P1_存储过程”存储过程。dropprocedureP1_存储过程实验131）创建事后触发器①打开SQL查询编辑器。②设计一个名为“T1_触发器”，当员工表插入和删除后触发器，当做插入操作时，实现在部门表中相应部门人数的增加，当做删除操作时，实现相应部门人数的减少。（其中要使用事务，即在插入员工数据的同时，相应部门的人数要增加，如果两者都成功，则提交，如果有一个失败则两者均撤消。删除也一样。）插入触发器CREATETRIGGERT1_触发器ON员工表FORINSERTASbeginupdate部门表set总人数=总人数+1from部门表,insertedwhere部门表.部门号=inserted.所在部门号if@@error=0committransactionelserollbacktransactionend删除后触发器CREATETRIGGERT11_触发器ON员工表FORDELETEASbeginupdate部门表set总人数=总人数-1from部门表,deletedwhere部门表.部门号=deleted.所在部门号if@@error=0committransactionelserollbacktransactionend③在员工表中插入记录，查看部门表人数是否增加select部门号,总人数from部门表where部门号='1002'insertinto员工表(员工号,所在部门号)values('2011','1002')select部门号,总人数from部门表where部门号='1002'④在员工表中删除记录，查看部门表人数是否减少。select部门号,总人数from部门表where部门号='1002'dele