西北工业大学数据库实验报告.docx
《西北工业大学数据库实验报告.docx》由会员分享，可在线阅读，更多相关《西北工业大学数据库实验报告.docx（13页珍藏版）》请在冰豆网上搜索。
西北工业大学数据库实验报告
《数据库原理》实验报告
题目：
实验一
数据库和表的创建与管理
学号
姓名
班级
日期
1.实验内容、步骤以及结果
1.使用系统存储过程（sp_rename）将视图“V_SPJ”更名为“V_SPJ_三建”。
（5分）
execsp_renamev_spj,v_spj_三建
2.针对SPJ数据库，创建并执行如下的存储过程：
（共计35分）
（1）创建一个带参数的存储过程—jsearch。
该存储过程的作用是：
当任意输入一个工程代号时，将返回供应该工程零件的供应商的名称（SNAME）和零件的名称（PNAME）以及工程的名称（JNAME）。
执行jsearch存储过程，查询“J1”对应的信息。
（10分）
创建：
createprocedurejsearch（@search_jnonchar（20））
as
begin
selectj.jname,s.sname,p.pname
froms,p,j,spj
where
spj.jno=@search_jnoandspj.jno=j.jnoandspj.sno=s.snoandspj.pno=p.pno
end
执行：
EXECjsearch@search_jno='J1'
（2）使用S表，为其创建一个加密的存储过程—jmsearch。
该存储过程的作用是：
当执行该存储过程时，将返回北京供应商的所有信息。
（10分）
创建：
createprocedurejmsearch
withencryption
as
begin
select*fromS
wherecity='北京'
end
使用系统存储过程sp_helptext查看jsearch,jmsearch的文本信息。
（5分）
execsp_helptext'jsearch'
execsp_helptext'jmsearch'
（3）执行jmsearch存储过程，查看北京供应商的情况。
（5分）
执行：
execjmsearch
（4）删除jmsearch存储过程。
（5分）
dropprocedurejmsearch
3.针对Student数据库，创建和执行如下的触发器：
（共计40分）
（1）删除SC表上的外键约束，针对SC表创建一个名为insert_s的INSERT触发器。
该触发器的功能：
当用户向SC表中插入记录时，如果插入的cno值不是C表中Cno的已有值，则提示用户“不能插入记录这样的纪录”，否则提示“记录插入成功”。
触发器创建成功之后，向SC表插入记录，验证触发器是否正常工作。
（5分）
createtriggerinsert_s
onSC
insteadofinsert
as
if（exists（select*frominsertedwherecnonotin（selectcnofromC）））
begin
print'不能插入这样的记录！
！
'
rollbacktransaction
end
else
print'记录插入成功！
！
'
验证：
insertintoscvalues（95009,8,98）
更改数据：
insertintoscvalues（95009,5,98）
（2）为S表创建一个名为dele_s1的DELETE触发器，该触发器的作用是禁止删除S表中的记录。
触发器创建成功之后，删除S表中的记录，验证触发器是否正常工作。
（5分）
创建：
createtriggerdele_s1
ons
insteadofdelete
as
rollbacktransaction
print'禁止删除'
验证：
deletefromswheresno='95001'
（3）为S表创建一个名为dele_s2的DELETE触发器，该触发器的作用是删除S表中的记录时删除SC表中该学生的选课纪录。
触发器创建成功之后，删除S表中的记录删除S表中的记录，验证触发器是否正常工作（SC表中的数据被正常删除）。
（5分）
创建：
createtriggerdele_s2
ons
afterdelete
as
deletefromsc
wheresnoin（selectsnofromdeleted）
验证：
deletefromswheresno='95001'
S表中：
Sc表中：
（4）为S表创建一个名为update_s的UPDATE触发器，该触发器的作用是禁止更新S表中“sdept”字段的内容。
触发器创建成功之后，更新S表中“sdept”字段的内容，验证触发器是否正常工作。
（5分）
创建:
createtriggerupdate_s
ons
insteadofupdate
as
ifupdate（sdept）
begin
raiserror（'sdept不能被修改',10,1）
end
验证：
（5）禁用update_s触发器。
禁用之后，更新S表中的“sdept”字段的内容，验证触发器是否还继续正常工作。
（5分）
禁用触发器：
disabletriggerupdate_sons
验证：
updates
setsdept='CS1'
wheresno='95002'
（6）删除update_s触发器。
（5分）
droptriggerupdate_s
（7）创建一个新的课程成绩统计表CAvgGrade（Cno,Snum,examSNum,avgGrade），分别表示课号，选该课程的学生人数，参加考试人数，该门课程的平均成绩。
利用触发器实现如下的功能：
当SC表中有记录插入、删除或者某个人的成绩更新时，自动更新表CAvgGrade。
注意SC表中的grade为NULL时表明该学生还未参加考试，计算平均成绩时不需要计算该成绩，但是grade为0即考试成绩为0时，需要计算该成绩。
（10分）
创建表：
创建触发器：
usestudent
go
createtriggerupdate_sc_cavggrade
onsc
forinsert,delete,update
as
begin
declare@cnochar（10）
declare@ssumint
declare@examssumint
declare@avggradeint
select@cnofrominserted
select@cnofromdeleted
select@ssum=count（*）fromscwhere@cno=cno
select@examssum=count（*）fromscwhere@cno=cnoandcgrade>=0
select@avggrade=AVG（cgrade）fromscwhere@cno=cnoandcgrade>=0
updateCAvgGrade
setSsum=@ssum,examSsum=@examssum,avgGrade=@avggrade
wherecno=@cno
end
验证触发器：
初始的CAvgGrade表
插入数据时：
insertintoscvalues（'95004','1',65）
Sc表：
CAvgGrade表：
删除数据时：
deletefromscwheresno='95001'
sc表的变化：
CAvgGrade表变化：
更新数据时：
updatesc
setcgrade=99wheresno='95004'
SC表的变化：
CAvgGrade表变化：
4.创建一个works数据库，其中包含员工表empoyee（eID,eName,salary），假设该表中有1000条员工数据，完成下列要求（总计20分，每题10分）。
（1）为了协助本题自动生成1000条员工数据，创建一个自动生成员工ID的用户自定义函数generateEID。
其中员工ID要求是一个8位的数字，前四位表示插入员工数据的当前年份，后四位按照从0001到9999的顺序增长。
例如2015年插入的第一条数据是20050001，所有1000条员工ID分别是20150001-20151000。
调用该函数实现自动插入1000条数据。
（注意插入数据的时候员工姓名可以为任意值，工资是2000-5000之间的数字）
创建了一个存储过程来完成：
usework
go
createproceduregenerateEID
as
begin
declare@iint
set@i=0
while（@i<1000）
begin
insertintodbo.employeevalues（20160001+@i,'name'+CAST（@iasnchar（20））,2000+CAST（FLOOR（rand（）*3001）asint））
set@i=@i+1
return
end
end
（2）该公司计划为员工按照一定的规则涨工资，请使用游标创建一个存储过程，执行该存储过程完成本次工资调整：
工资增长规则如下：
●工资在3000元以下，每月涨300元；
●工资在3000-4000元之间，每月涨200元；
●工资大于或者等于4000元，每月涨50元；
usework
go
declaremycursorCURSORfor
selectsalaryfromemployee
openmycursor
declare@salaryint
fetchnextfrommycursorinto@salary
while@@FETCH_STATUS=0
begin
if（@salary<3000）
begin
updateemployeesetsalary=@salary+300wherecurrentofmycursor
end
elseif（@salary<4000）
begin
updateemployeesetsalary=@salary+200wherecurrentofmycursor
end
else
begin
updateemployeesetsalary=@salary+50wherecurrentofmycursor
end
fetchnextfrommycursorinto@salary
end
closemycursor
deallocatemycursor
未改变时：
改变后：
2.实验中出现的问题以及解决方案（对于未解决问题请将问题列出来）
除了标题内容以外，该部分内容中还可以写对于实验的一些感受，建议，意见等。
触发器是对表进行插入、更新、删除的时候会自动执行的特殊存储过程，本章学习的触发器类型是after触发器，即只有当执行update,delete,insert的时候这个触发器才会被使用到，触发器的使用为数据库操作真的提供了很多便利
批阅者：
批阅日期：
实验成绩：
批注：
升级会员 