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

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) prima

2、ry 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<Date_hired)create table customer20122617(Cust_no char(6) prima

3、ry key,Cust_name varchar(10) not null,Sex char(2) not null,BirthDate datetime null,City varchar(10) null,Discount Dec(4,2) not null,constraint Discount_check-检查约束 的名称check (Discount>=0.5 and Discount<=1 )-检查约束)-create rule d as state between 0 and 1-创建规则-sp_bindrule d,'customer20122617.Dis

4、count' -这种方法也可以给Discount约束 绑定规则create table orderdetail20122617(Order_no char(6) primary key,constraint Order_no_constraintCHeck(Order_no LIKE'A-ZA-Z0-90-90-90-9'),Cust_no char(6) not null,P_no char(6) not null,Order_total int not null,Order_date datetime not null,constraint person201226

5、17_contrFOREIGN KEY(P_no)-定义外键为P_noREFERENCES person20122617(P_no)-外键参照主表person20122617中的P_noon delete NO Action-参照定义为不许删除on update cascade,-定义为可随着主表跟新constraint cusrtomer20122617_contrforeign key (Cust_no)REFERENCES customer20122617(Cust_no)-参考on delete NO Action on update cascade)create table sala

6、ry20122617(P_no Char(6) primary key, Base Dec(8,2) not null,Bonus Dec(8,2) not null,Fact AS Base+Bonus ,constraint person2_contrFOREIGN KEY(P_no) REFERENCES person20122617(P_no)on delete NO Actionon update cascade) -建表完成1.2.2 实验结果注：仅附有实际意义的结果。运行代码得到结果后拷屏，用Windows画图工具切下有意义的部分，然后粘贴到此处。Person20122617表的

7、创建Customer20122617表的创建Orderdetail20122617表的创建Salary20122617表的创建实验二：SQL更新语句2.1 实验目的与内容目的：update、delete、insert 语句的练习。内容：11-68。报告：以11-7、11-8作为实验二的报告。2.2 实验代码及结果2.2.1 实验代码2.2.1.1实验数据准备：insert into person20122617 -插入person表的数据values('000001','林峰','男','1975-04-07','2003

8、-08-03','销售部','000007' )insert into person20122617values('000002','谢志文','男','1975-02-14','2003-12-07','培训部','000005' )insert into person20122617values('000003','李浩然','男','1970-08-25','2000

9、-05-16','销售部','000007' )insert into person20122617values('000004','廖小玲','女','1979-08-06','2004-05-06','培训部','000005' )insert into person20122617values('000005','梁玉琼','女','1970-08-25','2001

10、-03-13','培训部','NULL' )insert into person20122617values('000006','罗向东','男','1979-05-11','2000-07-09','销售部','000007' )insert into person20122617values('000007','肖佳庆','男','1963-07-14','1988-0

11、6-06','销售部','NULL' )insert into person20122617values('000008','李浩然','男','1975-01-30','2002-04-12','培训部','000005' )insert into person20122617values('000009','赵文龙','男','1969-01-20','1996-08-

12、12','培训部','000007' )INSERT INTO customer20122617 -为customer表插入数据 VALUES ('000001','王云','男' ,'1972-01-30','成都','1.00') INSERT INTO customer20122617 VALUES ('000002','林国平','男' ,'1985-08-14','成都'

13、,'0.85') INSERT INTO customer20122617 VALUES ('000003','郑洋','女' ,'1973-04-07','成都','1.00') INSERT INTO customer20122617 VALUES ('000004','张雨洁','女' ,'1983-09-06','北京','1.00') INSERT INTO customer20

14、122617 VALUES ('000005','刘菁','女' ,'1971-08-20','北京','0.95') INSERT INTO customer20122617 VALUES ('000006','李宇中','男' ,'1979-08-06','上海','1.00') INSERT INTO customer20122617 VALUES ('000007','顾培

15、铭','男' ,'1973-07-23','上海','1.00') INSERT INTO orderdetail20122617-为orderdetail表插入数据 VALUES ('AS0058','000006','000002','150000','2006-04-05') INSERT INTO orderdetail20122617 VALUES ('AS0043','000005','000

16、005','90000','2006-03-25') INSERT INTO orderdetail20122617 VALUES ('AS0030','000003','000001','70000','2006-02-14') INSERT INTO orderdetail20122617 VALUES ('AS0012','000002','000005','85000','2005-11-11&#

17、39;) INSERT INTO orderdetail20122617 VALUES ('AS0011','000007','000009','130000','2005-08-13') INSERT INTO orderdetail20122617VALUES ('AS0008','000001','000007','43000','2006-06-06') INSERT INTO orderdetail20122617 VALUE

18、S ('AS0005','000001','000007','72000','2006-05-12') INSERT INTO orderdetail20122617 VALUES ('BU0067','000007','000003','110000','2006-03-08') INSERT INTO orderdetail20122617 VALUES ('BU0043','000004','

19、;000008','70000','2006-12-25') INSERT INTO orderdetail20122617 VALUES ('BU0039','000002','000005','90000','2006-10-12') INSERT INTO orderdetail20122617 VALUES ('BU0032','000006','000002','32000','2006-08-

20、08') INSERT INTO orderdetail20122617 VALUES ('BU0021','000004','000006','66000','2006-04-01') INSERT INTO orderdetail20122617 VALUES ('CX0044','000007','000009','80000','2006-12-12') INSERT INTO orderdetail20122617 V

21、ALUES ('CX0032','000003','000001','35000','2006-09-18') INSERT INTO orderdetail20122617 VALUES ('CX0025','000002','000003','90000','2006-05-02') INSERT INTO orderdetail20122617 VALUES ('CX0022','000001',&

22、#39;000007','66000','2006-12-04')insert into salary20122617-为salary表插入数据values('000001','2100','300' )insert into salary20122617values('000002','1800','300' )insert into salary20122617values('000003','2800','280&

23、#39; )insert into salary20122617values('000004','2500','250' )insert into salary20122617values('000005','2300','275' )insert into salary20122617values('000006','1750','130' )insert into salary20122617values('000007','

24、;2400','210' )insert into salary20122617values('000008','1800','235' )insert into salary20122617values('000009','2150','210' )-数据插入均完成2.2.1.1 11-7实验代码update salary20122617 set Base=1800 where P_no=000006 -跟新号员工的工资和奖金update salary20122617 se

25、t Bonus='160' where P_no=000006update salary20122617 -将两年内没有订单的员工奖金下调25%set Bonus=Bonus*.75where not exists(select *from orderdetail20122617where salary20122617.P_no=orderdetail20122617.P_no and Order_date>=GETDATE()-365*2) -getdate获取当前日期2.2.1.2 11-8实验代码delete from person20122617 where p_

26、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 11-13实验代码select distinct

27、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 '000

28、003' and '000008' order by Fact asc -把员工号为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_n

29、o=b.P_nogroup by Deptname having avg(Bonus)>200 order by 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

30、 11-13实验结果Person20122617表中确实只有培训部和销售部女经理确实只有梁玉琼一位，上述输出正确。查询姓名为林峰 谢志文罗向东的信息查询结果正确。把员工号为000003到0000008的员工按薪水排序。从结果看出排序正确。查询工号为000002的员工的基本工资加倍,奖金加.5倍后的实际收入经计算上述输出正确3.2.2.2 11-14实验结果查询平均奖金在元以上的部门并排序因为没有奖金平均数大于200元的部门存在，故没有输出。查询上海顾客的订单数和订单总额上海顾客的总订单数和订单金额均正确实验四：视图及索引的建立和维护4.1 实验目的与内容目的：创建表的视图，修改和删除表的视图，

31、并利用视图完成表的查询，创建表的索引、修改和删除表的索引。内容：11-35、11-911。报告：以11-3、11-4、11-9作为实验四的报告。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,De

32、ptname,SUM(Order_total) as Achievementfrom person20122617,orderdetail20122617where 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 inde

33、x name_sort on person20122617(P_name) -在人员表上的姓名列上创建一个单列索引name_portcreate index birth_name on person20122617(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) -

34、在月薪表上创建一个聚簇缩影fact_idx，并使其按降序索引4.2.1.3 11-9实验代码update CustomerViewset Discount=0.85where Cust_name='王云' -将视图CustomerView中的姓名为“王云”的顾客的购买折扣改为.854.2.2 实验结果4.2.2.1 11-3实验结果建立北京顾客视图成功创建培训员工视图成功4.2.2.2 11-4实验结果在人员表上的姓名列上创建一个单列索引name_port指令：select *from person20122617 where P_name='林峰'创建成功后我

35、们能通过索引P_name查找数据了。在人员表上创建一个组合索引birth_name指令：select *from person20122617 where Birthdate='1975-02-14'and P_name='谢志文'创建成功后我们能通过组合索引查询到我们想要的信息了。在人员表“姓名列上创建一个唯一索引”u_name_sort创建索引失败，因为创建的是一个唯一索引，而在姓名列中有两个“林浩然”的存在，故创建唯一索引失败。在月薪表上创建一个聚簇缩影fact_idx，并使其按降序索引创建索引失败，因为在salary20122617表中我们已经定义了一个

36、主键，主键将自动默认为一个聚簇索引，而一个表中只能有一个聚簇索引故会出现错误。4.2.2.3 11-9实验结果将视图CustomerView中的姓名为“王云”的顾客的购买折扣改为.85因为在视图CustomerView中不存在“王云”顾客 故受到影响的行数为0行实验五：存储过程的建立和维护5.1 实验目的与内容目的：创建用户的存储过程，修改和删除存储过程、执行存储过程。内容：11-2224。报告：以11-24作为实验五的报告。5.2 实验代码及结果5.2.1 实验代码create proc proc_addbonus( P_no char(6),add dec(5,1)output)asdec

37、lare Order_total intdeclare cur_addbonus_checks cursor forselect Order_totalfrom orderdetail20122617where P_no=P_noselect add=0open cur_addbonus_checks fetch cur_addbonus_checks into Order_totalif(fetch_status<>0)beginClose cur_addbonus_checksdeallocate cur_addbonus_checksreturnendset nocount

38、onwhile (fetch_status=0)begin if Order_total<=100000set add=add+20else set add=add+Order_total/100000*30fetch cur_addbonus_checks into Order_totalendclose cur_addbonus_checks return/*-根据各员工在噢仁derdetail表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加 若订单高于,则奖金加 add=add+Order_total/100000*30*/5.2.2 实验结果根据各员工在噢仁de

39、rdetail表中的销售业绩计算其总的奖金金额,员工每签定一份小雨的订单其奖金加 若订单高于,则奖金加 add=add+Order_total/100000*30存储过程建立成功我们能成功的查询到工号为000002的员工的奖金总额为50.0 。实验六：触发器的建立和维护6.1 实验目的与内容目的：创建触发器，修改和删除触发器，测试触发器的效果。内容：11-34。报告：以11-34作为实验六的报告。6.2 实验代码及结果6.2.1 实验代码create trigger delect_p_pnoon person20122617after deleteasif rowcount=0 returnd

40、elete salary20122617from titles t,deleted dwhere t.P_no=d.P_noreturn-构建一个person20122617上的能级联删除salary20122617表中员工信息的触发器create trigger update_salaryon salary20122617for updateasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif(select count(*)from person20122617 p,inserted iwhere p.P_n