大型数据库管理系统技术、应用与实例分析SQL-Server-2005(第2版)孟宪虎-电子工业出版社

实验参考答案实验四(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工资=4000