数据库实验报告.doc

实验一：表及约束的创建1.1 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。内容：11-2、11-2633。报告：以11-31作为实验一的报告。1.2 实验代码及结果1.2.1 实验代码 (1)CONSTRAINT person_contr FOREIGN KEy (P_no) REFERENCES person20122675 (P_no) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT customers_contr FOREIGN KEy (Cust_no) REFERENCES customers20122675(Cust_no) ON DELETE CASCADE ON UPDATE CASCADE (2)alter table salary20122675add constraint deptno_fk foreign key(deptno)references person20122675(p_no)1.2.2 实验结果（1）（2）实验二：SQL更新语句2.1 实验目的与内容目的：update、delete、insert 语句的练习。内容：11-68。报告：以11-7、11-8作为实验二的报告。2.2 实验代码及结果2.2.1 实验代码2.2.1.1 11-7实验代码（1）update salary20122675set Base=1800,Bonus=160where P_no=000006（2）update salary20122675set Bonus=Bonus*.75where not exists (select*from orderdetails20122675where salary20122675.P_no=orderdetails20122675.P_no and order_date=GETDATE()-365*2)2.2.1.2 11-8实验代码delete from person20122675 where p_no=0000102.2.2 实验结果2.2.2.1 11-7实验结果（1）（2）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实验代码(1)select distinct Deptname from person20122675(2)select*from person20122675 where P_boss=null and Sex=女 (3)select*from person20122675 where P_name in(林峰,谢志文,罗向东)(4)select*from salary20122675 where P_no between 000003 and 000008order by Fact asc(5)select P_no,2*Base+1.5*Bonus from salary20122675where P_no=0000023.2.1.2 11-14实验代码(1)select Deptname 部门, AVG(Bonus) 平均奖金 from salary20122675 A JOIN person20122675 B ON A.P_no=B.P_no GROUP BY Deptname HAVING AVG(Bonus)200 ORDER BY AVG(Bonus) DESC (2)select count(*) 订单总数,SUM(Order_total) 订单总额 from orderdetails20122675,customers20122675 where orderdetails20122675.cust_no=customers20122675.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实验代码（1）create view Customers20122675View asselect Cust_no,Cust_name,Sex,Discount from customers20122675 where City=北京（2）create view TrainingView as select p2.P_no,P_name,Sex,Deptname,Achievementfrom person20122675 as p2 left outer join(select p1.P_no,sum(order_total) as Achievementfrom person20122675 as p1,orderdetails20122675 as owhere p1.P_no=o.P_no and P_boss =null andorder_date=getdate()-365group by p1.P_no)as p3on p2.P_no=p3.P_nowhere deptname=培训部4.2.1.2 11-4实验代码(1)create index name_sort on person20122675(P_name)(2)create index birth_name on person20122675(Birthdate ,P_name)(3)create unique index u_name_sort on person20122675(P_name)(4)CREATE CLUSTERED INDEX fact_idx on salary20122675(Fact desc)4.2.1.3 11-9实验代码update Customers20122675Viewset 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(P_no char(6),Add dec(5,1) output)asdeclare Order_total intdeclare cur_addbonus_checks cursor forselect Order_totalfrom orderdetails20122675where P_no=P_noselect add=0open cur_addbonus_checks if (fetch_status0)beginclose cur_addbonus_checksdeallocate cur_addbonus_checksreturnendset nocount onwhile (fetch_status=0)beginif Order_total1beginraiserror 53333 不支持多个P_no值得修改 rollback transaction return end update person20122675 set P_no=i.P_no from person20122675 p,deleted d,inserted i where p.p_no=d.p_no end returnupdate salary20122675 set P_no=20000 where P_no=000009 （3）create trigger salary20122675_inson salary20122675for update,insertasdeclare num_rows intselect num_rows=rowcountif num_rows=0 returnif (select count (*) from person20122675 p,inserted i where p.