《数据库原理与设计》课程实验报告1.docx

1、数据库原理与设计课程实验报告12010-2011学年第二学期数据库原理与设计课程实验报告学 号： 学生姓名： 班 级： 教 师： 辅导老师： 2011年6月实验一：表及约束的创建1. 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。内容：11-2、11-2633。报告：以11-31作为实验一报告的典型。2. 实验代码及结果11-31（1）创建表时将orderdetail的Cust_no列和P_no列定义为外键，并分别参考表customer的列Cust_no和表person的列P_nocreate table orderdetail20082333(Order_no ch

2、ar(6) not null primary keyconstraint Order_no_constraintcheck (Order_no like A-ZA-Z0-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 person_contrforeign key(P_no)references person20082333(P_no)on delete cascadeon update cascade,co

3、nstraint customer_contrforeign key(Cust_no)references customer20082333(Cust_no)on delete cascadeon update cascade)（2）将salary表中的P_no设为外键，并使其参照表person中的P_noalter table salary20082333add constraint p_no_FK foreign key(p_no) references person20082333(p_no)实验二：SQL更新语句1. 实验目的与内容目的：update、delete、insert 语句的

4、练习。内容：11-68。报告：以11-7、11-8作为实验二报告的典型。2. 实验代码及结果11-7（1）将salary表中工号为000006的员工工资增加为1800，奖金增加为160UPDATE salary20082333 set Base=1800,Bonus=160 where p_no=000006（2）利用SQL语句将两年内没有签订单的员工奖金下调25%。UPDATE salary20082333 set Bonus=bonus*.75 where not exists (select * from orderdetail20082333 where salary20082333.

5、p_no=orderdetail20082333.p_no and order_date=getdate()-730)11-8删除person表中工号为000001的员工数据。delete from person20082333 where p_no=000001实验三：SQL查询语句1. 实验目的与内容目的：select语句中各种查询条件的实验。内容：11-1218。报告：以11-13、11-14作为实验三报告的典型。2. 实验代码及结果11-13（1）查询person表中所有不重复的部门select distinct Deptname from person20082333（2）查询per

6、son表中部门女经理的数据select * from person20082333 where p_boss is null and sex=女（3）查询person表中姓名为林峰、谢志文和罗向东的员工数据select * from person20082333 where p_name in(林峰,谢志文,罗向东)（4）利用SQL语句将工号为000003000008的员工的月收入按实发工资升序排序select * from salary20082333 where p_no between 000003 and 000008order by Fact ASC（5）查询工号为000002的员工

7、基本工资增加2倍，奖金增加1.5倍后的实际收入。select p_no 工号,2*base+1.5*bonus 实际收入 from salary20082333 where p_no=00000211-14（1）利用SQL语句查询一月份发放奖金平均数大于180元的部门，并从高到低排序。select Deptname 部门,avg(bonus) 平均奖金FROM salary20082333 A join person20082333 B on A.p_no=B.p_nogroup by deptname having avg(bonus)180 order by avg(bonus) desc

8、（2）查询居住城市在上海的顾客订单总数和订单总额。select count(*) 订单总数,sum(order_total) 订单总额from orderdetail20082333,customer20082333where orderdetail20082333.Cust_no=customer20082333.Cust_no and City=上海实验四：视图及索引的建立和维护1. 实验目的与内容目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删除表的索引。内容：11-35、11-911。报告：以11-3、11-4、11-9作为实验四报告的典型。2.

9、实验代码及结果11-3 （1）在表customer上创建“顾客”视图CustomerView，其中包括居住在北京的顾客的基本信息，并显示“顾客号”、“姓名”、“性别”和“购买折扣”等字段create view CustomerView AS select Cust_no,Cust_name,Sex,Discount from customer20082333 where City=北京 （2）基于表customer和表orderdetail创建“培训员工”视图TrainingView，其中包括培训部所有员工（不含部门经理）的员工号、姓名、性别、所属部门和最近一年内的总销售业绩create vi

10、ew TrainingView AS select person20082333.p_no,p_name,Sex,Deptname,SUM(order_total) AS Achievement from person20082333,orderdetail20082333 where person20082333.p_no=orderdetail20082333.p_no and deptname=培训部 and p_boss is not null and order_date=getdate()-365 group by person20082333.p_no,p_name,Sex,De

11、ptname11-4 （1）在“人员”表的“姓名”列上创建一个单列索引name_sortcreate index name_sort on person20082333(p_name) （2）在“人员”表的“出生日期”列和姓名列上创建一个组合索引birth_name create index birth_name on person20082333(birthdate,p_name) （3）在“人员”表的“姓名”列上创建一个唯一索引u_name_sort create unique index u_name_sort on person20082333(p_name) （4）“月薪”表的“实发

12、”列上创建一个聚簇索引fact_idx，并使系统按降序索引create clustered index fact_idx on salary20082333(Fact DESC)11-9 将“顾客”视图CustomerView中姓名为“刘菁”的顾客的购买折扣改为0.85update CustomerView set discount=0.85 where cust_name=刘菁实验五：存储过程的建立和维护1. 实验目的与内容目的：创建用户的存储过程，修改和删除存储过程、执行存储过程。内容：11-2224。报告：以11-24作为实验五报告的典型。2. 实验代码及结果创建使用游标的存储过程要求：

13、根据各员工在orderdetail表中的销售业绩计算其总的奖金增额；员工每签订一份订单额小于100000的订单，其奖金增额为20；若订单额高于100000，则奖金增额为（order_total/100000*30）create proc proc proc_addbonus(p_no char(6),add dec(5,1) output)AS declare order_total int declare cur_addbonus_checks from orderdetail20082333 where p_no=p_no select add=0 open cur_addbonus_ch

14、ecks fetch cur_addbonus_checks into order_tatal if(fetch_status0) begin close cur_addbonus_checks deallocate cur_addbonus_checks return end set nocount on while(fetch_status=0) begin if order_total=100000 set add=add+20 else set add=add+order_total/100000*30 fetch cur_addbonus_checks into order_tota

15、l end close cur_addbonus_checks deallocate cur_addbonus_checksreturn实验六：触发器的建立和维护1. 实验目的与内容目的：创建触发器，修改和删除触发器，测试触发器的效果。内容：11-34。报告：以11-34作为实验六的报告典型。2. 实验代码及结果创建DELETE类型、UPDATE类型及INSERT类型的触发器并测试其作用要求：（1）在person表上创建一个触发器，并删除表person中的员工信息时，级联删除表salary中该员工的信息 创建触发器create trigger delete20082333on person20

16、082333after deleteasif rowcount=0 return delete salary20082333 from salary20082333 t,deleted d where t.p_no=d.p_noreturn 删除person表中的员工信息delete from person20082333 where p_no=000003 此时salary表中显示为 (2)在salary表上创建一个触发器，检查在修改该表时是否有不存在于person表中的职工代码出现 创建触发器create trigger update20082333on salary20082333for

17、 update,insertasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif (select count (*) from person20082333 p,inserted i where p.p_no=i.p_no)!=num_rowsbegin raiserror 53334 试图插入或修改非法的p_no值到salary表中 rollback transaction return end return 满足条件时，成功插入insert into salary20082333 values(000001

18、,2100,300) 不满足条件时，提示出错（下面两句分别是插入、修改）insert into salary20082333 values(000003,2800,280)update salary20082333 set p_no=011 where p_no=01 （3）在salary表上创建一个触发器，向该表插入数据时必须参考表person中的P_no创建触发器create trigger update20082333on salary20082333for update,insertasdeclare num_rows intselect num_rows=rowcountif num

19、_rows=0 returnif (select count (*) from person20082333 p,inserted i where p.p_no=i.p_no)!=num_rowsbegin raiserror 53334 试图插入或修改非法的p_no值到salary表中 rollback transaction return end return 满足条件时，成功插入insert into salary20082333 values(000001,2100,300) 不满足条件时，提示出错（下面两句分别是插入、修改）insert into salary20082333 val

20、ues(000003,2800,280)update salary20082333 set p_no=011 where p_no=01实验七：函数创建与调用1. 实验目的与内容目的：创建函数，调用函数。内容：11-20、11-21。报告：以11-20、11-21作为实验七报告的典型。2. 实验代码及结果11-20创建函数要求：创建一个函数Check_Pno，检测给定的员工号是否存在，如果存在返回0，否则返回-1create function Check_Pno20082333(p_no char(6)returns integer asbegin declare num int if exi

21、sts (select p_no from person20082333 where p_no=p_no) select num=0 else select num=-1 return numend11-21调用函数要求：调用函数Check_Pno，如果返回0，则向salary表中插入一行该员工的工资记录declare num intexec num=Check_Pno20082333 000007if num=0 insert salary20082333 values(000007,2200,280)插入的P_no在person表中存在，在salary表中不存在的时候，就会在salary表中添加进数据