《数据库原理》课程实验报告2014-2015.doc
2014-2015学年第一学期数据库原理课程实验报告学 号： 20122712 学生姓名： 魏仁斌 班 级： 软件工程2012-4 教 师： 陶宏才 辅导老师： 王泽洲 赵红芳 2014年12月实验一：表及约束的创建1.1 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。内容：11-2、11-2633。 注：实验内容编号均取自数据库原理及设计（第2版）第11章的实验！即：实验内容以第2版教材为准！报告：以11-31作为实验一的报告。1.2 实验代码及结果1.2.1 实验代码CREATE TABLE person_20122712(P_nochar(6)PRIMARY KEY,P_namevarchar(10)NOT NULL,Sexchar(2)NOT NULL,Birthdatedatetime NULL,Date_hireddatetime NOT NULL,Deptnamevarchar(10)NOT NULL DEFAULT '培训部',P_bosschar(6)NULL,CONSTRAINT birth_hire_checkCHECK (Birthdate< Date_hired)CREATE TABLE salary_20122712(P_nochar(6)PRIMARY KEY,BaseDec(8,2)NOT NULL,BonusDec(7,2)NULL,Fact AS Base+Bonus, CONSTRAINT person_contr FOREIGN KEY (P_no) REFERENCES person_20122712(P_no) ON DELETE No Action ON UPDATE CASCADE)CREATE TABLE customer_20122712(Cust_nochar(6)PRIMARY KEY,Cust_namevarchar(10)NOT NULL,Sexchar(2)NOT NULL,BirthDatedatetime NULL,Cityvarchar(10)NULL,DiscountDec(4,2) NOT NULL DEFAULT 1.00,CONSTRAINT Discount_checkCHECK (Discount<=1.00 AND Discount>=0.50)CREATE TABLE orderdetail_20122712(Order_nochar(6)PRIMARY KEY,CONSTRAINTOrder_no_constraintCHECK (Order_no LIKE 'A-ZA-Z0-90-90-90-9'),Cust_nochar(6)NOT NULL,P_nochar(6)NOT NULL,Order_totalintNOT NULL,Order_datedatetimeNOT NULL,CONSTRAINTperson_contrFOREIGN KEY (P_no)REFERENCES person_20122712 (P_no)ON DELETE No ActionON UPDATE CASCADE,CONSTRAINT customer_contrFOREIGN KEY (Cust_no)REFERENCES customer_20122712 (Cust_no)ON DELETE No ActionON UPDATE CASCADE)ALTER TABLE salary_20122712ADD CONSTRAINT salary_P_no_FK FOREIGN KEY (P_no)REFERENCES person_20122712(P_no)1.2.2 实验结果注：仅附有实际意义的结果。运行代码得到结果后拷屏，用Windows画图工具切下有意义的部分，然后粘贴到此处。实验二：SQL更新语句2.1 实验目的与内容目的：update、delete、insert 语句的练习。内容：11-68。报告：以11-7、11-8作为实验二的报告。2.2 实验代码及结果2.2.1 实验代码2.2.1.1 11-7实验代码update salary_20122712 set Base = 1800 ,Bonus = 160 where P_no = '000006'-select * from salary_20122712 where P_no = '000006'update salary_20122712 set Bonus = Bonus * 0.75 where not exists (select * from orderdetail_20122712 where salary_20122712.P_no = orderdetail_20122712.P_no and Order_date >= GETDATE() - 365 * 2)-select * from salary_201227122.2.1.2 11-8实验代码2.2.2 实验结果2.2.2.1 11-7实验结果(1)(2)执行前表orderdetail的数据执行前表salary的数据执行后表salary的数据2.2.2.2 11-8实验结果实验三：SQL查询语句3.1 实验目的与内容目的：select语句中各种查询条件的实验。内容：11-1218。报告：以11-13、11-14作为实验三的报告。3.2 实验代码及结果3.2.1 实验代码3.2.1.1 11-13实验代码select distinct Deptname from person_20122712select * from person_20122712 where P_boss is null and Sex='女'select *from person_20122712 where P_name in ('林峰','谢志文','罗向东')select *from salary_20122712 where P_no between '000003' and '000008' order by Fact ascselect P_no 工号,2*base+1.5*bonus 实际收入from salary_20122712 where P_no='0000023.2.1.2 11-14实验代码select Deptname 部门from salary_20122712 A JOIN person_20122712 B ON A.p_no=B.p_noGROUP BY DeptnameHAVING AVG(Bonus)>200ORDER BY AVG(Bonus) DESCselect COUNT(*) 订单总数,SUM (Order_total) 订单总额from orderdetail_20122712,customer_20122712where orderdetail_20122712.Cust_no=customer_20122712.Cust_no and City='上海'3.2.2 实验结果3.2.2.1 11-13实验结果(1)(2)(3)(4)(5)3.2.2.2 11-14实验结果(1)(2)实验四：视图及索引的建立和维护4.1 实验目的与内容目的：创建表的视图，修改和删除表的视图，并利用视图完成表的查询，创建表的索引、修改和删除表的索引。内容：11-35、11-911。报告：以11-3、11-4、11-9作为实验四的报告。4.2 实验代码及结果4.2.1 实验代码4.2.1.1 11-3实验代码CREATE VIEW CustomerView_20122712 AS select Cust_no,Cust_name,Sex,Discount from customer_20122712 where City ='北京'create view TrainingView_20122712 asselect p2.P_no,P_name,Sex,Deptname,Achievementfromperson_20122712 as p2 left outer join(select p1.P_no,SUM(order_total) as Achievementfrom person_20122712 as p1,orderdetail_20122712 as o where p1.P_no = o.P_no and P_boss is not null andOrder_date >= getdate()-365group by p1.P_no) as p3on p2.P_no = p3.P_nowhere deptname = '培训部'4.2.1.2 11-4实验代码create index name_sorton person_20122712(P_name)create index birth_nameon person_20122712(BirthDate,P_name)create unique index u_name_sorton person_20122712(P_name)create clustered index fact_inxon salary_20122712(Fact desc)4.2.1.3 11-9实验代码update CustomerView_20122712set Discount = 0.85where Cust_name = '王云'4.2.2 实验结果4.2.2.1 11-3实验结果（1）（2）4.2.2.2 11-4实验结果（1）（2）（3）（4）4.2.2.3 11-9实验结果实验五：存储过程的建立和维护5.1 实验目的与内容目的：创建用户的存储过程，修改和删除存储过程、执行存储过程。内容：11-2224。报告：以11-24作为实验五的报告。5.2 实验代码及结果5.2.1 实验代码CREATE PROC proc_addbonus_20122712(P_no CHAR(6),Add DEC(5,1) OUTPUT)ASDECLARE Order_total INTDECLARE cur_addbonus_checks CURSOR FORSELECT order_totalFROM orderdetail_20122712WHERE P_no=P_noSELECT add=0OPEN cur_addbonus_checksFETCH cur_addbouns_checks INTO Order_totalIF(fetch_status<>0)BEGINCLOSE cur_addbouns_checksDEALLOCATE cur_addbouns_checksRETURNENDSET NOCOUNT ONWHILE(fetch_status=0)BEGINIF Order_total<=100000SET add=add+20ELSE SET add=add+Order_total/100000*30FETCH cur_addbouns_checks INTO Order_totalENDCLOSE cur_addbouns_checksDEALLOCATE cur_addbouns_checksRETURN/*调用使用游标的存储过程*/declare ret dec(5,1)exec proc_addbouns_20122712 '000002',add=ret outputselect ret5.2.2 实验结果实验六：触发器的建立和维护6.1 实验目的与内容目的：创建触发器，修改和删除触发器，测试触发器的效果。内容：11-34。报告：以11-34作为实验六的报告。6.2 实验代码及结果6.2.1 实验代码create trigger pubdel_20122712on person_20122712after deleteas if rowcount = 0 return delete salary_20122712 from salary_20122712 s,deleted d where d.P_no = s.P_no returnCREATE TRIGGER salaryup20122712ON salary_20122712AFTER UPDATEASDECLARE num_rows INTSELECT num_rows=rowcountIF num_rows=0 RETURNIF(SELECT count(*)FROM inserted i,person_20122712 pWHERE i.P_no=p.P_no)!=num_rowsBEGINRAISERROR 53334 'error'ROLLBACK TRANSACTIONRETURNENDRETURNCREATE TRIGGER salaryin20122712ON salary_20122712FOR INSERTASDECLARE num_rows INTSELECT num_rows=rowcountIF num_rows=0 RETURNIF(SELECT count(*)FROM inserted i,person_20122712 pWHERE i.P_no=p.P_no)!=num_rowsBEGINRAISERROR 53334 'error'ROLLBACK TRANSACTIONRETURNENDRETURN6.2.2 实验结果（1）（2）（3）第 15 页 共 16 页
