120802031016李泳志-实验9查询存储过程触发器完整性

数据库系统课程设计学院：商学院专业班级：12信管３班学号：120802031002学生姓名：李泳志指导教师：高集荣2015年5月12日实验9：查询、存储过程、触发器、完整性实验目的：1熟练掌握局部变量、游标的定义和使用。2熟练掌握存储过程、触发器的创建和使用。3掌握数据库完整性的约束的定义和使用。二、实验内容第一部分：T-SQL程序设计。第二部分：存储过程第三部分：触发器第四部分：数据库完整性三、实验记录第一部分：T-SQL程序设计。（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息(4).利用游标逐行显示student表中的记录。(5).用自定义函数计算全体男生的平均年龄(6).显示course表中课程名的前2个字符。(7).在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。(8).在选课表中显示学号、课程号，并根据成绩：0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。”第二部分：存储过程1）创建一个为worker表添加职工记录的存储过程.2）创建一个存储过程Delworker删除worker表中指定职工号的记录3）显示存储过程Delworker的定义信息。4）删除存储过程Addworker和Delworker。不要截图5）创建并执行以下存储过程：a.从数据库表中查询，返回学生学号、姓名、课程名、成绩b.从数据库表中查询指定学号的学生学号，姓名、班级，该存储过程接受与传递参数，精确匹配的值第三部分：触发器(1)在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。(3)删除触发器(4)删除触发器(5)在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。第四部分：数据库完整性1、实施worker表的“性别”字段默认值为“男”的约束2实施salary表的“工资”字段值在0~9999的约束3实施depart表的“部门号”字段值唯一的非聚集索引的约束4、为worker表建立外键“部门号”，参考表depart的“部门号”列。5、建立一个规则sex：@性别=’男’OR@性别=’女’,将其绑定到worker表的“性别”列上。6、删除1小题所建立的约束7、删除2小题所建立的约束。8、删除3小题所建立的约束9、删除4小题所建立的约束10解除5小题所建立的绑定并删除规则sex四、实验结果及分析（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”if(selectavg(成绩)from选课where课程号='3')80beginprint'3号成绩良好'endelsebeginprint'3号成绩一般'end（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.declare@messagevarchar(200)ifexists(select学号from选课where学号='95003')beginselectavg(成绩)from选课where学号='95003'endelsebeginset@message='该生未选课'print@messageend（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息declare@msgvarchar(200)ifexists(select成绩from选课where成绩=90)select学号,课程号,成绩from选课where成绩=90elseset@msg='没有学生的成绩在90以上'print@msg(4).利用游标逐行显示student表中的记录。declare@cursorvarcursorset@cursorvar=cursorscrolldynamicforselect*from学生open@cursorvarfetchnextfrom@cursorvarwhile@@fetch_status=0beginfetchnextfrom@cursorvarendclose@cursorvardeallocate@cursorvar(5).用自定义函数计算全体男生的平均年龄CREATEFUNCTIONaverage(@cnumchar(2))RETURNSintasbegindeclare@averintselect@aver=(selectavg(sage)fromstudentwheressex=@cnumgroupbyssex)return@averendgodeclare@aver1int,@cnum1char(2)set@cnum1='男'select@aver1=dbo.average(@cnum1)select@aver1as'全体男生的平均年龄'(6).显示course表中课程名的前2个字符。selectleft(课程名,2)from课程orderby课程号(7).在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。SELECTSUBSTRING(学号,1,2)'年级',SUBSTRING(学号,3,LEN(学号)-1)'序号'FROM学生ORDERBY学号(8).在选课表中显示学号、课程号，并根据成绩：0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。”select学号、课程号、成绩=casewhen成绩=0and成绩60then'不合格'when成绩=60and成绩80then'合格'when成绩=80and成绩90then'良好'when成绩=90and成绩=100then'优秀'endfrom选课第二部分：存储过程（1）创建一个为worker表添加职工记录的存储过程AddworkerCREATEprocAddworker@职工号char(4),@名姓char(8),@性别char(2),@出生日期datetime,@党员否char(2),@参加工作datetime,@部门号char(4)asinsertintoworkervalues(@职工号,@名姓,@性别,@出生日期,@党员否,@参加工作,@部门号);（2）创建一个存储过程Delworker删除worker表中指定职工号的记录createprocDelworker@zhigonghaochar(4)asdeletefromworkerwhere职工号=@zhigonghao;go（3）显示存储过程Delworker的定义信息。（4）删除存储过程Addworker和Delworker。不要截图dropprocedureAddworkerdropprocedureDelworker（5）创建并执行以下存储过程：a.从数据库表中查询，返回学生学号、姓名、课程名、成绩createprocs_backasselect学生.学号,姓名,课程名,成绩from学生,选课,课程where学生.学号=选课.学号and选课.课程号=课程.课程号goexecs_backb.从数据库表中查询指定学号的学生学号，姓名、班级，该存储过程接受与传递参数，精确匹配的值createprocr_back@achar(10)asselect学号,姓名from学生where学号=@agoexecr_back's1'第三部分：触发器(1)在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号createtriggerdepart_updateondepartforupdateasupdateworkerset部门号=(select部门号frominserted)whereworker.部门号=(select部门号fromdeleted)。(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。createtriggerworker_deleteonworkerfordeleteasdeletefromsalarywhere职工号=(select职工号fromdeleted)(3)删除触发器depart_updatedroptriggerdepart_update(4)删除触发器worker_deletedroptriggerworker_delete(5)在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。例如：向选课表中添加一条记录('95013','2',99)，说明S12不在student表中createtriggersc_inserton选课forinsertasifexists(select1from学生where学号in(select学号frominserted))orexists(select1from选课where课程号in(select课程号frominserted))beginrollbacktransactionprint'违反数据一致性'endinsertinto选课values('95013','2',99)第四部分：数据库完整性1、实施worker表的“性别”字段默认值为“男”的约束altertableworkeraddconstraintcon1default'男'for性别goinsertworker(职工号)values(111)select*fromworker2实施salary表的“工资”字段值在0~9999的约束createtablesalary(职工号char(4),姓名char(8),日期datetime,工资decimalnotnullcheck(工资=0and工资=9999),primarykey(职工号,日期))3实施depart表的“部门号”字段值唯一的非聚集索引的约束createtabledepart(部门号char(4)unique,部门名char(10))Go4、为worker表建立外键“部门号”，参考表depart的“部门号”列。Createtableworker(职工号char(4)primarykey,姓名char(8),性别char(2),出生日期datetime,党员否char(2),参加工作datetime,部门号char(4),Foreignkey(部门号)referencesdepart(部门号))5，建立一个规则sex：@性别=’男’OR@性别=’女’,将其绑定到worker表的“性别”列上。createruleworker_ruleas@性别like'男'or@性别like'女'goexecsp_bindrule'worker_rule','worker.性别'Go6、删除1小题所建立的约束Altertableworkerdropconstraintcon17删除2小题所建立的约束。Altertablesalarydrop工资check8删除3小题所建立的约束Altertabledepartdropunique9、删除4小题所建立的约束Alterta