《数据库原理与设计》课程实验报告1Word文件下载.docx
《《数据库原理与设计》课程实验报告1Word文件下载.docx》由会员分享,可在线阅读,更多相关《《数据库原理与设计》课程实验报告1Word文件下载.docx(12页珍藏版)》请在冰豆网上搜索。
(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)
onupdatecascade
)
(2)将salary表中的P_no设为外键,并使其参照表person中的P_no
altertablesalary20082333
addconstraintp_no_FKforeignkey(p_no)
referencesperson20082333(p_no)
实验二:
SQL更新语句
update、delete、insert语句的练习。
11-6~8。
以11-7、11-8作为实验二报告的典型。
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查询语句
select语句中各种查询条件的实验。
11-12~18。
以11-13、11-14作为实验三报告的典型。
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='
上海'
实验四:
视图及索引的建立和维护
创建表的视图,修改和删除表的视图,并利用视图完成表的查询,创建表的索引、修改和删除表的索引。
11-3~5、11-9~11。
以11-3、11-4、11-9作为实验四报告的典型。
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='
刘菁'
实验五:
存储过程的建立和维护
创建用户的存储过程,修改和删除存储过程、执行存储过程。
11-22~24。
以11-24作为实验五报告的典型。
创建使用游标的存储过程
要求:
根据各员工在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)
if@@order_total<
=100000
set@add=@add+20
elseset@add=@add+@order_total/100000*30
fetchcur_addbonus_checksinto@order_total
return
实验六:
触发器的建立和维护
创建触发器,修改和删除触发器,测试触发器的效果。
11-34。
以11-34作为实验六的报告典型。
创建DELETE类型、UPDATE类型及INSERT类型的触发器并测试其作用
(1)在person表上创建一个触发器,并删除表person中的员工信息时,级联删除表salary中该员工的信息
创建触发器
createtriggerdelete20082333
onperson20082333
afterdelete
as
if@@rowcount=0return
deletesalary20082333
fromsalary20082333t,deletedd
wheret.p_no=d.p_no
删除person表中的员工信息
deletefromperson20082333wherep_no=000003
此时salary表中显示为
(2)在salary表上创建一个触发器,检查在修改该表时是否有不存在于person表中的职工代码出现
createtriggerupdate20082333
onsalary20082333
forupdate,insert
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
end
满足条件时,成功插入
insertintosalary20082333values('
2100'
300'
不满足条件时,提示出错(下面两句分别是插入、修改)
2800'
280'
updatesalary20082333setp_no=011wherep_no=01
(3)在salary表上创建一个触发器,向该表插入数据时必须参考表person中的P_no
创建触发器
实验七:
函数创建与调用
创建函数,调用函数。
11-20、11-21。
以11-20、11-21作为实验七报告的典型。
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('
2200,280)
插入的P_no在person表中存在,在salary表中不存在的时候,就会在salary表中添加进数据