《数据库原理与设计》课程实验报告1.docx
《《数据库原理与设计》课程实验报告1.docx》由会员分享，可在线阅读，更多相关《《数据库原理与设计》课程实验报告1.docx（12页珍藏版）》请在冰豆网上搜索。
《数据库原理与设计》课程实验报告1
2010-2011学年第二学期
《数据库原理与设计》
课程实验报告
学号：
学生姓名：
班级：
教师：
辅导老师：
2011年6月
实验一：
表及约束的创建
1.实验目的与内容
目的：
创建数据表、添加和删除列、实现所创建表的完整性约束。
内容：
11-2、11-26~33。
报告：
以11-31作为实验一报告的典型。
2.实验代码及结果
11-31
（1）创建表时将orderdetail的Cust_no列和P_no列定义为外键，并分别参考表customer的列Cust_no和表person的列P_no
createtableorderdetail20082333
（
Order_nochar（6）notnullprimarykey
constraintOrder_no_constraint
check（Order_nolike'[A-Z][A-Z][0-9][0-9]'）,
Cust_nochar（6）notnull,
P_nochar（6）notnull,
Order_totalintnotnull,
Order_datedatetimenotnull,
constraintperson_contr
foreignkey（P_no）
referencesperson20082333（P_no）
ondeletecascade
onupdatecascade,
constraintcustomer_contr
foreignkey（Cust_no）
referencescustomer20082333（Cust_no）
ondeletecascade
onupdatecascade
）
（2）将salary表中的P_no设为外键，并使其参照表person中的P_no
altertablesalary20082333
addconstraintp_no_FKforeignkey（p_no）
referencesperson20082333（p_no）
实验二：
SQL更新语句
1.实验目的与内容
目的：
update、delete、insert语句的练习。
内容：
11-6~8。
报告：
以11-7、11-8作为实验二报告的典型。
2.实验代码及结果
11-7
（1）将salary表中工号为000006的员工工资增加为1800，奖金增加为160
UPDATEsalary20082333setBase=1800,Bonus=160wherep_no='000006'
（2）利用SQL语句将两年内没有签订单的员工奖金下调25%。
UPDATEsalary20082333setBonus=bonus*.75wherenotexists（select*fromorderdetail20082333
wheresalary20082333.p_no=orderdetail20082333.p_noandorder_date>=getdate（）-730）
11-8
删除person表中工号为000001的员工数据。
deletefromperson20082333wherep_no='000001'
实验三：
SQL查询语句
1.实验目的与内容
目的：
select语句中各种查询条件的实验。
内容：
11-12~18。
报告：
以11-13、11-14作为实验三报告的典型。
2.实验代码及结果
11-13
（1）查询person表中所有不重复的部门
selectdistinctDeptnamefromperson20082333
（2）查询person表中部门女经理的数据
select*fromperson20082333wherep_bossisnullandsex='女'
（3）查询person表中姓名为林峰、谢志文和罗向东的员工数据
select*fromperson20082333wherep_namein（'林峰','谢志文','罗向东'）
（4）利用SQL语句将工号为000003~000008的员工的月收入按实发工资升序排序
select*fromsalary20082333wherep_nobetween'000003'and'000008'
orderbyFactASC
（5）查询工号为000002的员工基本工资增加2倍，奖金增加1.5倍后的实际收入。
selectp_no工号,2*base+1.5*bonus实际收入fromsalary20082333wherep_no='000002'
11-14
（1）利用SQL语句查询一月份发放奖金平均数大于180元的部门，并从高到低排序。
selectDeptname部门,avg（bonus）平均奖金
FROMsalary20082333Ajoinperson20082333BonA.p_no=B.p_no
groupbydeptnamehavingavg（bonus）>180orderbyavg（bonus）desc
（2）查询居住城市在上海的顾客订单总数和订单总额。
selectcount（*）订单总数,sum（order_total）订单总额
fromorderdetail20082333,customer20082333
whereorderdetail20082333.Cust_no=customer20082333.Cust_noandCity='上海'
实验四：
视图及索引的建立和维护
1.实验目的与内容
目的：
创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删除表的索引。
内容：
11-3~5、11-9~11。
报告：
以11-3、11-4、11-9作为实验四报告的典型。
2.实验代码及结果
11-3
（1）在表customer上创建“顾客”视图CustomerView，其中包括居住在北京的顾客的基本信息，并显示“顾客号”、“姓名”、“性别”和“购买折扣”等字段
createviewCustomerViewAS
selectCust_no,Cust_name,Sex,Discount
fromcustomer20082333whereCity='北京'
（2）基于表customer和表orderdetail创建“培训员工”视图TrainingView，其中包括培训部所有员工（不含部门经理）的员工号、姓名、性别、所属部门和最近一年内的总销售业绩
createviewTrainingViewAS
selectperson20082333.p_no,p_name,Sex,Deptname,SUM（order_total）ASAchievement
fromperson20082333,orderdetail20082333
whereperson20082333.p_no=orderdetail20082333.p_noanddeptname='培训部'
andp_bossisnotnullandorder_date>=getdate（）-365
groupbyperson20082333.p_no,p_name,Sex,Deptname
11-4
（1）在“人员”表的“姓名”列上创建一个单列索引name_sort
createindexname_sortonperson20082333（p_name）
（2）在“人员”表的“出生日期”列和姓名列上创建一个组合索引birth_name
createindexbirth_nameonperson20082333（birthdate,p_name）
（3）在“人员”表的“姓名”列上创建一个唯一索引u_name_sort
createuniqueindexu_name_sortonperson20082333（p_name）
（4）“月薪”表的“实发”列上创建一个聚簇索引fact_idx，并使系统按降序索引
createclusteredindexfact_idxonsalary20082333（FactDESC）
11-9
将“顾客”视图CustomerView中姓名为“刘菁”的顾客的购买折扣改为0.85
updateCustomerViewsetdiscount=0.85wherecust_name='刘菁'
实验五：
存储过程的建立和维护
1.实验目的与内容
目的：
创建用户的存储过程，修改和删除存储过程、执行存储过程。
内容：
11-22~24。
报告：
以11-24作为实验五报告的典型。
2.实验代码及结果
创建使用游标的存储过程
要求：
根据各员工在orderdetail表中的销售业绩计算其总的奖金增额；员工每签订一份订单额小于100000的订单，其奖金增额为20；若订单额高于100000，则奖金增额为（order_total/100000*30）
createprocprocproc_addbonus
（@p_nochar（6）,@adddec（5,1）output）
AS
declare@order_totalint
declarecur_addbonus_checks
fromorderdetail20082333
wherep_no=@p_noselect@add=0
opencur_addbonus_checks
fetchcur_addbonus_checksinto@order_tatal
if（@@fetch_status<>0）
begin
closecur_addbonus_checks
deallocatecur_addbonus_checks
return
end
setnocounton
while（@@fetch_status=0）
begin
if@@order_total<=100000
set@add=@add+20
elseset@add=@add+@order_total/100000*30
fetchcur_addbonus_checksinto@order_total
end
closecur_addbonus_checks
deallocatecur_addbonus_checks
return
实验六：
触发器的建立和维护
1.实验目的与内容
目的：
创建触发器，修改和删除触发器，测试触发器的效果。
内容：
11-34。
报告：
以11-34作为实验六的报告典型。
2.实验代码及结果
创建DELETE类型、UPDATE类型及INSERT类型的触发器并测试其作用
要求：
（1）在person表上创建一个触发器，并删除表person中的员工信息时，级联删除表salary中该员工的信息
创建触发器
createtriggerdelete20082333
onperson20082333
afterdelete
as
if@@rowcount=0return
deletesalary20082333
fromsalary20082333t,deletedd
wheret.p_no=d.p_no
return
删除person表中的员工信息
deletefromperson20082333wherep_no=000003
此时salary表中显示为
（2）在salary表上创建一个触发器，检查在修改该表时是否有不存在于person表中的职工代码出现
创建触发器
createtriggerupdate20082333
onsalary20082333
forupdate,insert
as
declare@num_rowsint
select@num_rows=@@rowcount
if@num_rows=0return
if（selectcount（*）
fromperson20082333p,insertedi
wherep.p_no=i.p_no）!
=@num_rows
begin
raiserror53334'试图插入或修改非法的p_no值到salary表中'
rollbacktransaction
return
end
return
满足条件时，成功插入
insertintosalary20082333values（'000001','2100','300'）
不满足条件时，提示出错（下面两句分别是插入、修改）
insertintosalary20082333values（'000003','2800','280'）
updatesalary20082333setp_no=011wherep_no=01
（3）在salary表上创建一个触发器，向该表插入数据时必须参考表person中的P_no
创建触发器
createtriggerupdate20082333
onsalary20082333
forupdate,insert
as
declare@num_rowsint
select@num_rows=@@rowcount
if@num_rows=0return
if（selectcount（*）
fromperson20082333p,insertedi
wherep.p_no=i.p_no）!
=@num_rows
begin
raiserror53334'试图插入或修改非法的p_no值到salary表中'
rollbacktransaction
return
end
return
满足条件时，成功插入
insertintosalary20082333values（'000001','2100','300'）
不满足条件时，提示出错（下面两句分别是插入、修改）
insertintosalary20082333values（'000003','2800','280'）
updatesalary20082333setp_no=011wherep_no=01
实验七：
函数创建与调用
1.实验目的与内容
目的：
创建函数，调用函数。
内容：
11-20、11-21。
报告：
以11-20、11-21作为实验七报告的典型。
2.实验代码及结果
11-20创建函数
要求：
创建一个函数Check_Pno，检测给定的员工号是否存在，如果存在返回0，否则返回-1
createfunctionCheck_Pno20082333（@p_nochar（6））
returnsintegeras
begin
declare@numint
ifexists（selectp_nofromperson20082333where@p_no=p_no）
select@num=0
else
select@num=-1
return@num
end
11-21调用函数
要求：
调用函数Check_Pno，如果返回0，则向salary表中插入一行该员工的工资记录
declare@numint
exec@num=Check_Pno20082333'000007'
if@num=0
insertsalary20082333values（'000007',2200,280）
插入的P_no在person表中存在，在salary表中不存在的时候，就会在salary表中添加进数据
升级会员 