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) primary 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=1 )-检查约束)-create rule d as state between 0 and 1-创建规则-sp_bindrule d,customer20122617.Discount -这种方法也可以给Discount约束 绑定规则create table orderdetail20122617(Order_no char(6) primary key,constraint Order_no_constraintCHeck(Order_no LIKEA-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 person20122617_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 salary20122617(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表的创建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-08-03,销售部,000007 )insert into person20122617values(000002,谢志文,男,1975-02-14,2003-12-07,培训部,000005 )insert into person20122617values(000003,李浩然,男,1970-08-25,2000-05-16,销售部,000007 )insert into person20122617values(000004,廖小玲,女,1979-08-06,2004-05-06,培训部,000005 )insert into person20122617values(000005,梁玉琼,女,1970-08-25,2001-03-13,培训部,NULL )insert into person20122617values(000006,罗向东,男,1979-05-11,2000-07-09,销售部,000007 )insert into person20122617values(000007,肖佳庆,男,1963-07-14,1988-06-06,销售部,NULL )insert into person20122617values(000008,李浩然,男,1975-01-30,2002-04-12,培训部,000005 )insert into person20122617values(000009,赵文龙,男,1969-01-20,1996-08-12,培训部,000007 )INSERT INTO customer20122617 -为customer表插入数据 VALUES (000001,王云,男 ,1972-01-30,成都,1.00) INSERT INTO customer20122617 VALUES (000002,林国平,男 ,1985-08-14,成都,0.85) INSERT INTO customer20122617 VALUES (000003,郑洋,女 ,1973-04-07,成都,1.00) INSERT INTO customer20122617 VALUES (000004,张雨洁,女 ,1983-09-06,北京,1.00) INSERT INTO customer20122617 VALUES (000005,刘菁,女 ,1971-08-20,北京,0.95) INSERT INTO customer20122617 VALUES (000006,李宇中,男 ,1979-08-06,上海,1.00) INSERT INTO customer20122617 VALUES (000007,顾培铭,男 ,1973-07-23,上海,1.00) INSERT INTO orderdetail20122617-为orderdetail表插入数据 VALUES (AS0058,000006,000002,150000,2006-04-05) INSERT INTO orderdetail20122617 VALUES (AS0043,000005,000005,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) INSERT INTO orderdetail20122617 VALUES (AS0011,000007,000009,130000,2005-08-13) INSERT INTO orderdetail20122617VALUES (AS0008,000001,000007,43000,2006-06-06) INSERT INTO orderdetail20122617 VALUES (AS0005,000001,000007,72000,2006-05-12) INSERT INTO orderdetail20122617 VALUES (BU0067,000007,000003,110000,2006-03-08) INSERT INTO orderdetail20122617 VALUES (BU0043,000004,000