数据库实验四上机实验.docx
《数据库实验四上机实验.docx》由会员分享，可在线阅读，更多相关《数据库实验四上机实验.docx（14页珍藏版）》请在冰豆网上搜索。
数据库实验四上机实验
实验四：
T-SQL语句及存储过程
徐瑞福20082603信管1班
1.实验特点:
类型：
验证类别：
专业基础计划学时：
4学时每组人数：
1人
2.实验目的与要求：
1）掌握T-SQL语句的基本语法格式，熟练使用查询语句，熟练使用流程控制语句
2）掌握在查询分析器中创建存储过程，掌握利用企业管理器创建用户存储过程
3.主要仪器设备：
安装SQLServer2000中文版的微型计算机。
4.实验内容提要:
1）变量的定义、赋值、输出
2）IF…..ELSE语句
3）WHILE语句
4）常用系统标量函数和用户定义函数
5）游标的使用
6）存储过程的创建、调用、删除
7）触发器的创建、执行、删除
8）数据库完整性中默认值、规则对象的创建、绑定、删除
完成包含以上内容的课上例题及补充作业题
5.上机操作要点：
上机前要认真阅读教材的有关例题，要将上机操作的语句事先写在提纲上。
6.注意事项：
操作命令在查询分析器中完成，注意保存SQL文件，上机结束时一定要将数据库和表与系统有效“分离”后，才能存盘带走。
T-SQL查询、存储过程、触发器、完整性上机作业题
第一部分：
T-SQL程序设计
（1）.如果3号课程的平均成绩在80分以上，则输出“3号课程成绩良好”，否则输出“3号成绩一般”
declare@avgfloatset@avg=（selectavg（grade）fromscwherecno='3'）if@avg>80print'3号课程成绩良好'elseprint'3号成绩一般'
（2）计算并输出95003号学生的平均成绩，若无该生信息，则显示“该生未选课”,提示信息.
declare@avgfloatif（selectcount（*）fromscwheresno='95003'）=0print'该生未选课'elsebeginselect@avg=avg（grade）fromscwheresno='95003'print'95003号学生平均成绩'print@avgend
（3）.如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息
declare@textchar（10）ifexists（selectgradefromSCwheregrade>90）selectSno,Cno,GradefromSCwhereGrade>90elsebeginset@text='没有学生的课程成绩在90分以上'print@textend
（4）.利用游标逐行显示student表中的记录。
declarestucursorforselect*fromstudentopenstufetchnextfromstuwhile@@fetch_status=0fetchnextfromstuclosestudeallocatestu
（5）.用自定义函数计算全体男生的平均年龄
createfunctionavg_age（@sexchar
（2））returnsint
asbegindeclare@averintselect@aver=（selectavg（Sage）fromStudentwhereSsex=@sex）return@averend
go
declare@aver1int,@sexchar
（2）set@sex='男'select@aver1=dbo.avg_age（@sex）
select@aver1as'全体男生的平均年龄'
go
（6）.显示course表中课程名的前2个字符。
selectsubstring（Cname,1,2）fromCourse
（7）.在一列中显示student中各元组的学号中的年级，列名显示为“年级”；另一列中显示学号中的学生序列号，列名显示为“序号”。
selectsubstring（Sno,1,2）年级,substring（Sno,3,len（Sno）-1）序号fromStudentorderbySno
（8）.在选课表中显示学号、课程号，并根据成绩：
0-59显示“不合格”；60-79显示“合格”；80-89显示“良好”；90-100显示“优秀。
”
selectSnoas'学号',Cnoas'课程号',grade=casewhenGrade<=59then'不合格'whenGrade>=60andGrade<=79then'合格'whenGrade>=80andGrade<=89then'良好'else'优秀'endfromSC
第二部分：
存储过程
（1）创建一个为worker表添加职工记录的存储过程Addworker
go
ifexists（selectnamefromsysobjectswherename='Addworker'andtype='P'）dropprocedureAddworker
go
createprocAddworker@职工号char（4）,@姓名char（8）,@性别char
（2）,@出生日期datetime,@党员否char
（2）,@参加工作datetime,@部门号char（4）
as
insertintoworker（职工号,姓名,性别,出生日期,党员否,参加工作,部门号）values（@职工号,@姓名,@性别,@出生日期,@党员否,@参加工作,@部门号）
go
execAddworker'16','王璐','女','1988-11-20','否','2010-08-21','11'
（2）创建一个存储过程Delworker删除worker表中指定职工号的记录
go
ifexists（selectnamefromsysobjectswherename='Delworker'andtype='P'）dropprocedureDelworker
go
createprocedureDelworker@职工号char（4）asdeletefromworkerwhere职工号=@职工号
go
execDelworker'16'
（3）显示存储过程Delworker的定义信息。
Sp_helptextDelworker
（4）删除存储过程Addworker和Delworker。
dropprocedureAddworker,Delworker
（5）创建并执行以下存储过程：
a.从数据库表中查询，返回学生学号、姓名、课程名、成绩
use徐瑞福200826031
go
ifexists（selectnamefromsysobjectswherename='select_stu'andtype='P'）dropprocedureselect_stu
go
createprocedureselect_stu
asselectSC.Sno,Sname,Cname,GradefromStudent,SC,CoursewhereSC.Sno=Student.SnoandSC.Cno=Course.Cno
go
execselect_stu
b.从数据库表中查询指定学号的学生学号，姓名，该存储过程接受与传递参数，精确匹配的值
use徐瑞福200826031
go
ifexists（selectnamefromsysobjectswherename='select_sno'andtype='P'）dropprocedureselect_sno
go
createprocedureselect_sno@Snochar（5）
asselectSno,SnamefromStudentwhereSno=@Sno
go
execselect_sno'95002'
第三部分：
触发器
（1）在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号。
建立触发器命令：
Ifexists（selectnamefromsysobjectswherename='depart_update'andtype='tr'）droptriggerdepart_update
go
Createtriggerdepart_updateondepartforupdate
as
declare@no1int,@no2int
select@no1=部门号ftominserted
select@no2=部门号fromdeleted
updateworkerset部门号=@no1where部门号=@no2
运行命令：
updatedepartset部门号=103部门号=101
（2）在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。
创建触发器的命令：
go
Ifexists（selectnamefromsysobjectswherename='worker_delete'andtype='tr'）droptriggerworker_delete
go
createtriggerworker_deleteonworkerfordeleteasdeletefromsalarywheresalary.职工号=（select职工号fromdeleted）
更新命令：
deletefromworkerwhere职工号=2
Ifexists（selectnamefromsysobjectswherename='trig'andtype='tr'）droptriggerworker_delete
gocreatetriggertrigonworker
fordeleteas
select’deleted表：
’asdeleted表，*fromdeleted
（3）删除触发器depart_update
Ifexists（selectnamefromsysobjectswherename=’depart_update’andtype=’tr’）droptriggerdepart_update
（4）删除触发器worker_delete
Ifexists（selectnamefromsysobjectswherename=’worker_delete’andtype=’tr’）droptriggerworker_delete
（5）在数据库中创建一个触发器，向选课表添加一条纪录时，检查该纪录的学号在学生表中是否存在，检查该纪录的课程号在课程表中是否存在，若其中有一项为否，则拒绝添加操作，并显示“违反数据一致性”提示信息。
例如：
向选课表中添加一条记录（'95009','5',89），说明95009不在student表中
createtriggersc_insertonscforinsertas
declare@xuehaochar（10）,@kechenghaochar（10）,@chengjismallint,@a,@b
select@xuehao=sno,@kechenghao=cnofrominserted
set@a=selectsnofromstudentwheresno=@xuehao
set@b=selectcnofromcoursewherecno=@kechenghao
ifexists（@aand@b）
beign
insertintoscvalues（@xuehao,@kechenghao,@chengji）
end
rollbacktransactionprint'违反数据一致性'
insertintoscvalues（'95009','5',89）
第四部分：
数据库完整性
1、实施worker表的“性别”字段默认值为“男”的约束
altertableworker
addconstraintcon1default'男'for性别
go
例如：
insertworker（职工号）values（111）
select*fromworker
2实施salary表的“工资”字段值在0~9999的约束
createtablesalary
（职工号char（4）,
姓名char（8）,
日期datetime,
工资decimalnotnullcheck（工资>=0and工资<=9999）,
primarykey（职工号,日期））
3实施depart表的“部门号”字段值唯一的非聚集索引的约束
createtabledepart
（部门号char（4）unique,
部门名char（10））
Go
4、为worker表建立外键“部门号”，参考表depart的“部门号”列。
Createtableworker
（职工号char（4）primarykey,
姓名char（8）,
性别char
（2）,
出生日期datetime,
党员否char
（2）,
参加工作datetime,
部门号char（4）,
Foreignkey（部门号）referencesdepart（部门号））
5、建立一个规则sex：
@性别=’男’OR@性别=’女’,将其绑定到worker表的“性别”列上。
createruleworker_rule
as@性别like'男'or@性别like'女'
go
execsp_bindrule'worker_rule','worker.性别'
Go
6、删除1小题所建立的约束
Altertableworkerdropconstraintcon1
7、删除2小题所建立的约束。
Altertablesalarydrop工资check
8、删除3小题所建立的约束
Altertabledepartdropunique
9、删除4小题所建立的约束
Altertableworkerdropforeignkey
10.解除5小题所建立的绑定并删除规则sex
ifexists（selectnamefromsysobjects
wherename='worker_rule'andtype='R'）
begin
execsp_unbindrule'worker.性别'
dropruleworker_rule
升级会员 