2022年西南交通大学数据库原理实验课程设计 .pdf

2014-2015学年第一学期数据库原理课程实验报告学号：20122617 学生姓名：徐玉松班级：软件工程 2012教师：陶宏才辅导老师：王泽洲赵红芳2014年 12 月名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 25 页 -第 2 页 共 25 页实验一：表及约束的创建1.1 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。内容：11-2、11-2633。注：实验内容编号均取自数据库原理及设计（第2 版）第 11章的实验！即：实验内容以第2 版教材为准！报告：以11-31 作为实验一的报告。1.2 实验代码及结果1.2.1 实验代码createtable person20122617 -创建新表(P_no char(6)primarykey,P_name varchar(10)notnull,Sex char(2)notnull,Birthdate datetimenull,Date_hired datetimenotnull,Deptname varchar(10)notnullDEFAULT 培训部 ,P_boss char(6)null,constraint birth_hire_check -为约束创建一个名称check(Birthdate=0.5 and Discount=GETDATE()-365*2)-getdate获取当前日期2.2.1.2 11-8 实验代码deletefromperson20122617wherep_no=000010-删除号员工信息2.2.2 实验结果2.2.2.1 11-7 实验结果名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 25 页 -第 8 页 共 25 页将 salary20122617 表中共哈维000006 的员工工资增加为1800，奖金增加为160 下调成功将两年内没有签订单的员工奖金下调25%2.2.2.2 11-8 实验结果由于 person20122617 表中没有000010 号员工，故有0 行受到影响名师资料总结-精品资料欢迎下载-名师精心整理-第 8 页，共 25 页 -第 9 页 共 25 页实验三：SQL 查询语句3.1 实验目的与内容目的：select 语句中各种查询条件的实验。内容：11-1218。报告：以11-13、11-14 作为实验三的报告。3.2 实验代码及结果3.2.1 实验代码3.2.1.1 11-13 实验代码selectdistinctDeptnamefromperson20122617-查询 person表中的不同部门select*fromperson20122617whereSex=女andP_boss=null-is null -查询女经理的数据select*fromperson20122617whereP_namein(林峰 ,谢志文 ,罗向东 )-查询姓名为林峰谢志文罗向东的信息select*fromsalary20122617whereP_nobetween000003and000008orderbyFactasc-把员工号为000003到 0000008的员工按薪水排序selectP_no工号,2*Base+1.5*Bonus收入fromsalary20122617whereP_no=000002-查询工号为000002的员工的基本工资加倍,奖金加.5倍后的实际收入3.2.1.2 11-14 实验代码selectDeptname部门,avg(Bonus)平均奖金fromsalary20122617a joinperson20122617bona.P_no=b.P_no groupbyDeptnamehavingavg(Bonus)200 orderbyavg(Bonus)desc-查询平均奖金在元以上的部门并排序join 的用法selectcount(*)订单数,sum(Order_total)订单总额fromorderdetail20122617,customer20122617 whereorderdetail20122617.Cust_no=customer20122617.Cust_noandCity=上 海-查询上海顾客的订单数和订单总额3.2.2 实验结果3.2.2.1 11-13 实验结果名师资料总结-精品资料欢迎下载-名师精心整理-第 9 页，共 25 页 -第 10 页 共 25 页Person20122617 表中确实只有培训部和销售部女经理确实只有梁玉琼一位，上述输出正确。查询姓名为林峰谢志文罗向东的信息查询结果正确。名师资料总结-精品资料欢迎下载-名师精心整理-第 10 页，共 25 页 -第 11 页 共 25 页把员工号为000003 到 0000008 的员工按薪水排序。从结果看出排序正确。查询工号为000002 的员工的基本工资加倍,奖金加.5 倍后的实际收入经计算上述输出正确3.2.2.2 11-14 实验结果查询平均奖金在元以上的部门并排序因为没有奖金平均数大于200 元的部门存在，故没有输出。名师资料总结-精品资料欢迎下载-名师精心整理-第 11 页，共 25 页 -第 12 页 共 25 页查询上海顾客的订单数和订单总额上海顾客的总订单数和订单金额均正确名师资料总结-精品资料欢迎下载-名师精心整理-第 12 页，共 25 页 -第 13 页 共 25 页实验四：视图及索引的建立和维护4.1 实验目的与内容目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删除表的索引。内容：11-35、11-911。报告：以11-3、11-4、11-9 作为实验四的报告。4.2 实验代码及结果4.2.1 实验代码4.2.1.1 11-3 实验代码createviewCustomerViewasselectCust_no,Cust_name,Sex,Discountfromcustomer20122617whereCity=北京-建立北京顾客的视图createviewTrainingViewasselectperson20122617.P_no,P_name,Sex,Deptname,SUM(Order_total)asAchievement fromperson20122617,orderdetail20122617 whereperson20122617.P_no=orderdetail20122617.P_noandDeptname=培训部 andP_boss!=nullandOrder_date=getdate()-365 groupbyperson20122617.P_no,P_name,Sex,Deptname-建立培训员工的视图4.2.1.2 11-4 实验代码createindexname_sortonperson20122617(P_name)-在人员表上的姓名列上创建一个单列索引name_port createindexbirth_nameonperson20122617(Birthdate,P_name)-在人员表上创建一个组合索引birth_name createuniqueindexu_name_sortonperson20122617(P_name)-在人员表“姓名列上创建一个唯一索引”u_name_sortcreateclusteredindexfact_idxonsalary20122617(FactDESC)-在月薪表上创建一个聚簇缩影fact _idx，并使其按降序索引4.2.1.3 11-9 实验代码updateCustomerView setDiscount=0.85 whereCust_name=王云 名师资料总结-精品资料欢迎下载-名师精心整理-第 13 页，共 25 页 -第 14 页 共 25 页-将视图 CustomerView中的姓名为“王云”的顾客的购买折扣改为.854.2.2 实验结果4.2.2.1 11-3 实验结果建立北京顾客视图成功创建培训员工视图成功名师资料总结-精品资料欢迎下载-名师精心整理-第 14 页，共 25 页 -第 15 页 共 25 页4.2.2.2 11-4 实验结果在人员表上的姓名列上创建一个单列索引name_port 指令：select*fromperson20122617whereP_name=林峰 创建成功后我们能通过索引P_name 查找数据了。在人员表上创建一个组合索引birth_name名师资料总结-精品资料欢迎下载-名师精心整理-第 15 页，共 25 页 -第 16 页 共 25 页指令：select*fromperson20122617whereBirthdate=1975-02-14andP_name=谢志文 创建成功后我们能通过组合索引查询到我们想要的信息了。在人员表“姓名列上创建一个唯一索引”u_name_sort创建索引失败，因为创建的是一个唯一索引，而在姓名列中有两个“林浩然”的存在，故创建唯一索引失败。名师资料总结-精品资料欢迎下载-名师精心整理-第 16 页，共 25 页 -第 17 页 共 25 页在月薪表上创建一个聚簇缩影fact_idx，并使其按降序索引创建索引失败，因为在 salary20122617 表中我们已经定义了一个主键，主键将自动默认为一个聚簇索引，而一个表中只能有一个聚簇索引故会出现错误。4.2.2.3 11-9 实验结果将视图 CustomerView 中的姓名为“王云”的顾客的购买折扣改为.85 因为在视图CustomerView 中不存在“王云”顾客 故受到影响的行数为0 行名师资料总结-精品资料欢迎下载-名师精心整理-第 17 页，共 25 页 -第 18 页 共 25 页实验五：存储过程的建立和维护5.1 实验目的与内容目的：创建用户的存储过程，修改和删除存储过程、执行存储过程。内容：11-2224。报告：以11-24 作为实验五的报告。5.2 实验代码及结果5.2.1 实验代码createprocproc_addbonus(P_no char(6),add dec(5,1)output)as declareOrder_totalint declarecur_addbonus_checkscursorfor selectOrder_total fromorderdetail20122617 whereP_no=P_no selectadd=0 opencur_addbonus_checksfetchcur_addbonus_checksintoOrder_total if(fetch_status0)begin Closecur_addbonus_checks deallocatecur_addbonus_checks return end setnocounton while(fetch_status=0)beginifOrder_total=100000 setadd=add+20 elsesetadd=add+Order_total/100000*30 fetchcur_addbonus_checksintoOrder_total end closecur_addbonus_checksreturn/*-根据各员工在噢仁derdetail表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加若订单高于,则奖金加 add=add+Order_total/100000*30*/名师资料总结-精品资料欢迎下载-名师精心整理-第 18 页，共 25 页 -第 19 页 共 25 页5.2.2 实验结果根据各员工在噢仁derdetail 表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加若订单高于,则奖金加add=add+Order_total/100000*30 存储过程建立成功我们能成功的查询到工号为000002 的员工的奖金总额为50.0。名师资料总结-精品资料欢迎下载-名师精心整理-第 19 页，共 25 页 -第 20 页 共 25 页实验六：触发器的建立和维护6.1 实验目的与内容目的：创建触发器，修改和删除触发器，测试触发器的效果。内容：11-34。报告：以11-34 作为实验六的报告。6.2 实验代码及结果6.2.1 实验代码createtriggerdelect_p_pno onperson20122617 afterdelete as ifrowcount=0 return deletesalary20122617 fromtitlest,deletedd wheret.P_no=d.P_no return-构建一个person20122617上的能级联删除salary20122617表中员工信息的触发器createtriggerupdate_salary onsalary20122617 forupdate as declarenum_rowsint selectnum_rows=rowcount ifnum_rows=0 return if(selectcount(*)fromperson20122617p,insertedi wherep.P_no=i.P_no)!=num_rows begin raiserror 53334 试图修改非法的P_no 值到 person20122617表中 rollbacktransaction return end return-建立一个在salary20122617表上，能在修改表时检查是否有不存在于person20122617表中的职工代码出现名师资料总结-精品资料欢迎下载-名师精心整理-第 20 页，共 25 页 -第 21 页 共 25 页createtriggerinsert_salary onsalary20122617 forinsert as declarenum_rowsint selectnum_rows=rowcount ifnum_rows=0 return if(selectcount(*)fromperson20122617p,insertedi wherep.P_no=i.P_no)!=num_rows begin raiserror 53334 试图插入非法的P_no 值到 person20122617表中 rollbacktransaction return end return insertintosalary20122617values(000110,1640,160)-建立一个在salary20122617表上的触发器，当向该表插入数据时必须参考person20122617表中的P_no。6.2.2 实验结果声明：要想让触发器工作我们必须将我们的所有与触发器想冲突的外键约束删除，因为外键约束的优先级是高于触发器的。构建一个 person20122617 上的能级联删除salary20122617 表中员工信息的触发器名师资料总结-精品资料欢迎下载-名师精心整理-第 21 页，共 25 页 -第 22 页 共 25 页触发器构建成功我们将工号为000002 的员工删除，从结果看来已经删除成功。我们可以看到员工号为000002 的员工在person20122617 表中已经被删除名师资料总结-精品资料欢迎下载-名师精心整理-第 22 页，共 25 页 -第 23 页 共 25 页我们可以看到在salary20122617 表中员工号为000002 的员工信息也被删除掉了在 salary20122617 表上构建触发器成功我们以updatesalary20122617setP_no=123whereP_no=000001 指令来检查我们的触发器是否工作名师资料总结-精品资料欢迎下载-名师精心整理-第 23 页，共 25 页 -第 24 页 共 25 页从结果中我们可以看到，当我们想要把员工号为000001 的员工的工号改为123 时，触发器阻止我们修改，原因是由于我们修改的工号没有在person20122617 表中存在。从上面的结果来看我们的触发器已经处于工作状态。在 salary20122617 表上触发器建立成功。我们想要检测我们的触发器的工作情况所以我们以下这条指令来检测我们的触发器的工作情况。insertintosalary20122617values(000110,1640,160)名师资料总结-精品资料欢迎下载-名师精心整理-第 24 页，共 25 页 -第 25 页 共 25 页我们执行时报错，因为我们想要将person20122617 表中不存在的工号0001100 插入 salary20122617 表中，触发器当然不能允许这样的操作，所以报错啦。以上证明了我们的触发器工作状态良好。名师资料总结-精品资料欢迎下载-名师精心整理-第 25 页，共 25 页 -