)
CREATETABLEorderdetail20112736
(
order_nochar(6)NOTNULLPRIMARYKEY
CONSTRAINTorder_no20112736
CHECK(order_noLIKE'[A-Z][A-Z][0-9][0-9]'),
cust_nochar(6)NOTNULL
CONSTRAINTcust_no20112736
REFERENCEScustomer20112736(cust_no)
ONDELETECASCADE
ONUPDATECASCADE,
p_nochar(6)NOTNULL
CONSTRAINTp_no20112736
REFERENCESperson20112736(p_no),
order_totalintNOTNULL,
order_datedatetimeNOTNULL
)
CREATETABLEsalary20112736
(
p_nochar(6)NOTNULLPRIMARYKEY
CONSTRAINTwaijian20112736
REFERENCESperson20112736(p_no)
ONDELETECASCADE
ONUPDATECASCADE,
basedec(5)NULL,
bonusdec(5)NULL,
factASbase+bonus,
)
2.实验结果
3.遇到的问题及解决方法
在最后一个’(‘的前边使用了逗号,删除后解决问题
实验二SQL操纵子语言
一、实验目的、内容、原理
利用SQL向表person、salary、customer及orderdetail中插入数据。
二、实验代码、结果与问题
1.实验代码
INSERTINTOcustomer20112736VALUES('000001','王云','男','1972-01-30','成都',1.00)
INSERTINTOcustomer20112736VALUES('000002','林国平','男','1985-08-14','成都',0.85)
INSERTINTOcustomer20112736VALUES('000003','郑洋','女','1973-04-07','成都',1.00)
INSERTINTOcustomer20112736VALUES('000004','张雨洁','女','1983-09-06','北京',1.00)
INSERTINTOcustomer20112736VALUES('000005','郑箐','女','1971-08-20','北京',0.95)
INSERTINTOcustomer20112736VALUES('000006','李宇中','男','1979-08-06','上海',1.00)
INSERTINTOcustomer20112736VALUES('000007','顾培铭','男','1973-07-23','上海',1.00)
INSERTINTOorderdetail20112736VALUES('AS58','000006','000002',150000,'2006-04-05')
INSERTINTOorderdetail20112736VALUES('AS43','000005','000005',90000,'2006-03-25')
INSERTINTOorderdetail20112736VALUES('AS30','000003','000001',70000,'2006-02-14')
INSERTINTOorderdetail20112736VALUES('AS12','000002','000005',85000,'2005-11-11')
INSERTINTOorderdetail20112736VALUES('AS11','000007','000009',130000,'2005-08-13')
INSERTINTOorderdetail20112736VALUES('AS08','000001','000007',43000,'2005-06-06')
INSERTINTOorderdetail20112736VALUES('AS05','000001','000007',72000,'2005-05-12')
INSERTINTOorderdetail20112736VALUES('BU67','000007','000003',110000,'2005-03-08')
INSERTINTOorderdetail20112736VALUES('BU43','000004','000008',70000,'2004-12-25')
INSERTINTOorderdetail20112736VALUES('BU39','000002','000005',90000,'2004-10-12')
INSERTINTOorderdetail20112736VALUES('BU32','000006','000002',32000,'2004-08-08')
INSERTINTOorderdetail20112736VALUES('BU21','000004','000006',66000,'2004-04-01')
INSERTINTOorderdetail20112736VALUES('CX44','000007','000009',80000,'2003-12-12')
INSERTINTOorderdetail20112736VALUES('CX32','000003','000001',35000,'2003-09-18')
INSERTINTOorderdetail20112736VALUES('CX25','000002','000003',90000,'2003-05-02')
INSERTINTOorderdetail20112736VALUES('CX22','000001','000007',66000,'2002-12-04')
INSERTINTOperson20112736VALUES('000001','林峰','男','1973-04-07','2003-08-03','销售部','000007')
INSERTINTOperson20112736VALUES('000002','谢志文','男','1975-02-14','2003-12-07','培训部','000005')
INSERTINTOperson20112736VALUES('000003','李浩然','男','1970-08-25','2000-05-16','销售部','000007')
INSERTINTOperson20112736VALUES('000004','廖小玲','女','1979-08-06','2004-05-06','培训部','000005')
INSERTINTOperson20112736VALUES('000005','梁玉琼','女','1970-08-25','2001-03-13','培训部',NULL)
INSERTINTOperson20112736VALUES('000006','罗向东','男','1979-05-21','2000-07-09','销售部','000007')
INSERTINTOperson20112736VALUES('000007','肖家庆','男','1963-07-14','1998-06-06','销售部',NULL)
INSERTINTOperson20112736VALUES('000008','李浩然','男','1975-01-30','2002-04-12','培训部','000007')
INSERTINTOperson20112736VALUES('000009','赵文龙','男','1969-04-20','1996-08-12','销售部','000007')
INSERTINTOsalary20112736VALUES('000001',2100,300)
INSERTINTOsalary20112736VALUES('000002',1800,300)
INSERTINTOsalary20112736VALUES('000003',2800,280)
INSERTINTOsalary20112736VALUES('000004',2500,250)
INSERTINTOsalary20112736VALUES('000005',2300,275)
INSERTINTOsalary20112736VALUES('000006',1750,130)
INSERTINTOsalary20112736VALUES('000007',2400,210)
INSERTINTOsalary20112736VALUES('000008',1800,235)
INSERTINTOsalary20112736VALUES('000009',2150,210)
2.实验结果
3.遇到的问题及解决方法
插入order_no时因为格式不对而不能插入,修改其格式,使其满足题目要求。
实验三用SQL语句修改表中的数据
一、实验目的、内容、原理
(1)将salary表中工号为000006的员工工资增加为1800,奖金增加为160
(2)利用SQL语句将两年内没有签订单的员工工资下调25%
二、实验代码、结果与问题
1.实验代码
UPDATEsalary20112736SETbase=1800,bonus=160WHEREp_no=’000006’