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

1、.2014-2015学年第一学期数据库原理课程实验报告学 号： 20122617 学生姓名： 徐玉松 班 级： 软件工程2012 教 师： 陶宏才 辅导老师： 王泽洲 赵红芳 2014年12月实验一：表及约束的创建1.1 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。内容：11-2、11-2633。 注：实验内容编号均取自数据库原理及设计（第2版）第11章的实验！即：实验内容以第2版教材为准！报告：以11-31作为实验一的报告。1.2 实验代码及结果1.2.1 实验代码create table person20122617-创建新表(P_no char(6) prim

2、ary key,P_name varchar(10) not null,Sex char(2) not null,Birthdate datetime null,Date_hired datetime not null,Deptname varchar(10) not null DEFAULT培训部,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实验代码delete fro

3、m person20122617 where p_no=000010-删除号员工信息2.2.2 实验结果2.2.2.1 11-7实验结果将salary20122617表中共哈维000006的员工工资增加为1800，奖金增加为160下调成功将两年内没有签订单的员工奖金下调25%2.2.2.2 11-8实验结果由于person20122617表中没有000010号员工，故有0行受到影响实验三：SQL查询语句3.1 实验目的与内容目的：select语句中各种查询条件的实验。内容：11-1218。报告：以11-13、11-14作为实验三的报告。3.2 实验代码及结果3.2.1 实验代码3.2.1.1

4、11-13实验代码select distinct Deptname from person20122617-查询person表中的不同部门select * from person20122617 where Sex=女 and P_boss=null- is null -查询女经理的数据select * from person20122617 whereP_name in (林峰,谢志文,罗向东) -查询姓名为林峰 谢志文罗向东的信息select * from salary20122617 where P_no between 000003 and 000008 order by Fact a

5、sc -把员工号为000003到0000008的员工按薪水排序select P_no 工号 ,2*Base+1.5*Bonus 收入 from salary20122617 where P_no=000002 -查询工号为000002的员工的基本工资加倍,奖金加.5倍后的实际收入3.2.1.2 11-14实验代码select Deptname 部门 ,avg(Bonus) 平均奖金 from salary20122617 a join person20122617 b on a.P_no=b.P_nogroup by Deptname having avg(Bonus)200 order by

6、 avg (Bonus) desc -查询平均奖金在元以上的部门并排序join 的用法select count (*) 订单数,sum(Order_total)订单总额from orderdetail20122617 , customer20122617where orderdetail20122617.Cust_no=customer20122617.Cust_no and City=上海 -查询上海顾客的订单数和订单总额3.2.2 实验结果3.2.2.1 11-13实验结果Person20122617表中确实只有培训部和销售部女经理确实只有梁玉琼一位，上述输出正确。查询姓名为林峰 谢志文罗

7、向东的信息查询结果正确。把员工号为000003到0000008的员工按薪水排序。从结果看出排序正确。查询工号为000002的员工的基本工资加倍,奖金加.5倍后的实际收入经计算上述输出正确3.2.2.2 11-14实验结果查询平均奖金在元以上的部门并排序因为没有奖金平均数大于200元的部门存在，故没有输出。查询上海顾客的订单数和订单总额上海顾客的总订单数和订单金额均正确实验四：视图及索引的建立和维护4.1 实验目的与内容目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删除表的索引。内容：11-35、11-911。报告：以11-3、11-4、11-9作为实验四的

8、报告。4.2 实验代码及结果4.2.1 实验代码4.2.1.1 11-3实验代码create view CustomerView as select Cust_no, Cust_name,Sex,Discount from customer20122617 where City=北京 -建立北京顾客的视图create view TrainingView as select person20122617.P_no,P_name,Sex,Deptname,SUM(Order_total) as Achievementfrom person20122617,orderdetail20122617wh

9、ere person20122617.P_no=orderdetail20122617.P_no and Deptname=培训部 and P_boss!=null and Order_date=getdate()-365group by person20122617.P_no,P_name,Sex,Deptname -建立培训员工的视图4.2.1.2 11-4实验代码create index name_sort on person20122617(P_name) -在人员表上的姓名列上创建一个单列索引name_portcreate index birth_name on person2012

10、2617(Birthdate,P_name) -在人员表上创建一个组合索引birth_namecreate unique index u_name_sort on person20122617(P_name) -在人员表“姓名列上创建一个唯一索引”u_name_sortcreate clustered index fact_idx on salary20122617(Fact DESC) -在月薪表上创建一个聚簇缩影fact_idx，并使其按降序索引4.2.1.3 11-9实验代码update CustomerViewset Discount=0.85where Cust_name=王云 -将

11、视图CustomerView中的姓名为“王云”的顾客的购买折扣改为.854.2.2 实验结果4.2.2.1 11-3实验结果建立北京顾客视图成功创建培训员工视图成功4.2.2.2 11-4实验结果在人员表上的姓名列上创建一个单列索引name_port指令：select *from person20122617 where P_name=林峰创建成功后我们能通过索引P_name查找数据了。在人员表上创建一个组合索引birth_name指令：select *from person20122617 where Birthdate=1975-02-14and P_name=谢志文创建成功后我们能通过组

12、合索引查询到我们想要的信息了。在人员表“姓名列上创建一个唯一索引”u_name_sort创建索引失败，因为创建的是一个唯一索引，而在姓名列中有两个“林浩然”的存在，故创建唯一索引失败。在月薪表上创建一个聚簇缩影fact_idx，并使其按降序索引创建索引失败，因为在salary20122617表中我们已经定义了一个主键，主键将自动默认为一个聚簇索引，而一个表中只能有一个聚簇索引故会出现错误。4.2.2.3 11-9实验结果将视图CustomerView中的姓名为“王云”的顾客的购买折扣改为.85因为在视图CustomerView中不存在“王云”顾客 故受到影响的行数为0行实验五：存储过程的建立和

13、维护5.1 实验目的与内容目的：创建用户的存储过程，修改和删除存储过程、执行存储过程。内容：11-2224。报告：以11-24作为实验五的报告。5.2 实验代码及结果5.2.1 实验代码create proc proc_addbonus( P_no char(6),add dec(5,1)output)asdeclare Order_total intdeclare cur_addbonus_checks cursor forselect Order_totalfrom orderdetail20122617where P_no=P_noselect add=0open cur_addbonu

14、s_checks fetch cur_addbonus_checks into Order_totalif(fetch_status0)beginClose cur_addbonus_checksdeallocate cur_addbonus_checksreturnendset nocount onwhile (fetch_status=0)begin if Order_total=100000set add=add+20else set add=add+Order_total/100000*30fetch cur_addbonus_checks into Order_totalendclo

15、se cur_addbonus_checks return/*-根据各员工在噢仁derdetail表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加 若订单高于,则奖金加 add=add+Order_total/100000*30*/5.2.2 实验结果根据各员工在噢仁derdetail表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加 若订单高于,则奖金加 add=add+Order_total/100000*30存储过程建立成功我们能成功的查询到工号为000002的员工的奖金总额为50.0 。实验六：触发器的建立和维护6.1 实验目的与内容目的：创建触发器，

16、修改和删除触发器，测试触发器的效果。内容：11-34。报告：以11-34作为实验六的报告。6.2 实验代码及结果6.2.1 实验代码create trigger delect_p_pnoon person20122617after deleteasif rowcount=0 returndelete salary20122617from titles t,deleted dwhere t.P_no=d.P_noreturn-构建一个person20122617上的能级联删除salary20122617表中员工信息的触发器create trigger update_salaryon salary

17、20122617for updateasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif(select count(*)from person20122617 p,inserted iwhere p.P_no=i.P_no)!=num_rowsbeginraiserror 53334 试图修改非法的P_no值到person20122617表中rollback transactionreturnendreturn -建立一个在salary20122617表上，能在修改表时检查是否有不存在于person201226

18、17表中的职工代码出现create trigger insert_salaryon salary20122617for insertasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif(select count(*)from person20122617 p,inserted iwhere p.P_no=i.P_no)!=num_rowsbeginraiserror 53334 试图插入非法的P_no值到person20122617表中rollback transactionreturnendreturninsert into salary20122617 values(000110,1640,160)-建立一个在salary20122617表上的触发器，当向该表插入数据时必须参考person20122617表中的P_no。6.2.2 实验结果声明：要想让触发器工作我们必须将我们的所有与触发器想冲突的外键约