2014-2015学年第一学期《数据库原理》课程实验报告学号：20122617学生姓名：徐玉松班级：软件工程2012 教师：陶宏才辅导老师：王泽洲赵红芳2014年12月实验一：表及约束的创建1.1 实验目的与内容目的：创建数据表、添加和删除列、实现所创建表的完整性约束。
内容：11-2、11-26~33。
注：实验内容编号均取自《数据库原理及设计（第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<Date_hired))create table customer20122617(Cust_no char(6)primary 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.Discount' --这种方法也可以给Discount约束绑定规则create table orderdetail20122617(Order_no char(6)primary key,constraint Order_no_constraintCHeck(Order_no LIKE'[A-Z][A-Z][0-9][0-9][0-9][0-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_no on delete NO Action --参照定义为不许删除on update cascade,--定义为可随着主表跟新constraint cusrtomer20122617_contrforeign key(Cust_no)REFERENCES customer20122617(Cust_no)--参考on delete NO Actionon 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-6~8。
报告：以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 customer20122617VALUES ('000002','林国平','男','1985-08-14','成都','0.85')INSERT INTO customer20122617VALUES ('000003','郑洋','女','1973-04-07','成都','1.00')INSERT INTO customer20122617VALUES ('000004','张雨洁','女','1983-09-06','北京','1.00')INSERT INTO customer20122617VALUES ('000005','刘菁','女','1971-08-20','北京','0.95')INSERT INTO customer20122617VALUES ('000006','李宇中','男','1979-08-06','上海','1.00')INSERT INTO customer20122617VALUES ('000007','顾培铭','男','1973-07-23','上海','1.00')INSERT INTO orderdetail20122617--为orderdetail 表插入数据VALUES ('AS0058','000006','000002','150000','2006-04-05')INSERT INTO orderdetail20122617VALUES ('AS0043','000005','000005','90000','2006-03-25')INSERT INTO orderdetail20122617VALUES ('AS0030','000003','000001','70000','2006-02-14')INSERT INTO orderdetail20122617VALUES ('AS0012','000002','000005','85000','2005-11-11')INSERT INTO orderdetail20122617VALUES ('AS0011','000007','000009','130000','2005-08-13')INSERT INTO orderdetail20122617VALUES ('AS0008','000001','000007','43000','2006-06-06')INSERT INTO orderdetail20122617VALUES ('AS0005','000001','000007','72000','2006-05-12')INSERT INTO orderdetail20122617VALUES ('BU0067','000007','000003','110000','2006-03-08')INSERT INTO orderdetail20122617VALUES ('BU0043','000004','000008','70000','2006-12-25')INSERT INTO orderdetail20122617VALUES ('BU0039','000002','000005','90000','2006-10-12')INSERT INTO orderdetail20122617VALUES ('BU0032','000006','000002','32000','2006-08-08')INSERT INTO orderdetail20122617VALUES ('BU0021','000004','000006','66000','2006-04-01')INSERT INTO orderdetail20122617VALUES ('CX0044','000007','000009','80000','2006-12-12')INSERT INTO orderdetail20122617VALUES ('CX0032','000003','000001','35000','2006-09-18')INSERT INTO orderdetail20122617VALUES ('CX0025','000002','000003','90000','2006-05-02')INSERT INTO orderdetail20122617VALUES ('CX0022','000001','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')insert into salary20122617values('000004','2500','250')insert into salary20122617values('000005','2300','275')insert into salary20122617values('000006','1750','130')insert into salary20122617values('000007','2400','210')insert into salary20122617values('000008','1800','235')insert into salary20122617values('000009','2150','210')--数据插入均完成2.2.1.1 11-7实验代码update salary20122617set Base=1800 where P_no=000006 --跟新号员工的工资和奖金update salary20122617set Bonus='160'where P_no=000006update salary20122617--将两年内没有订单的员工奖金下调25% set Bonus=Bonus*.75where not exists(select*from orderdetail20122617wheresalary20122617.P_no=orderdetail20122617.P_no and Order_date>=GETDATE()-365*2)--getdate获取当前日期2.2.1.2 11-8实验代码delete from person20122617where p_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语句中各种查询条件的实验。