数据库实验四上机实验.docx
《数据库实验四上机实验.docx》由会员分享,可在线阅读,更多相关《数据库实验四上机实验.docx(14页珍藏版)》请在冰豆网上搜索。
![数据库实验四上机实验.docx](https://file1.bdocx.com/fileroot1/2023-2/8/477bf188-2fb4-4497-9796-c41d08ac75d9/477bf188-2fb4-4497-9796-c41d08ac75d91.gif)
数据库实验四上机实验
实验四:
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