LOFTER-网易轻博

实验3：数据操作和完整性约束的作用

1．INSERT INTO wcp.仓库VALUES('AA1111','广州',51)

INSERT INTO wcp.仓库VALUES('AA1112','深圳',51)

INSERT INTO wcp.仓库VALUES('AA1113','上海',51)

INSERT INTO wcp.仓库VALUES('AA1114','北京',51)

INSERT INTO wcp.仓库VALUES('AA1115','汕头',51)

INSERT INTO wcp.职工VALUES('AA111111','AA1111',1001)

INSERT INTO wcp.职工VALUES('AA111112','AA1112',1002)

INSERT INTO wcp.职工VALUES('AA111113','AA1113',1003)

INSERT INTO wcp.职工VALUES('AA111114','AA1114',1004)

INSERT INTO wcp.职工VALUES('AA111115','AA1115',1005)

INSERT INTO wcp.职工VALUES('AA111116','AA1111',1006)

INSERT INTO wcp.职工VALUES('AA111117','AA1112',1007)

INSERT INTO wcp.职工VALUES('AA111118','AA1113',1008)

INSERT INTO wcp.职工VALUES('AA111119','AA1114',1009)

INSERT INTO wcp.职工VALUES('AA111110','AA1115',1000)

INSERT INTO wcp.供应商VALUES('S111','广工','广州')

INSERT INTO wcp.供应商VALUES('S112','广工','广州')

INSERT INTO wcp.供应商VALUES('S113','广工','广州')

INSERT INTO wcp.供应商VALUES('S114','广工','广州')

INSERT INTO wcp.供应商VALUES('S115','广工','广州')

INSERT INTO wcp.订购单VALUES('AA111111','S111','OR1111','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1112','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S113','OR1113','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S114','OR1114','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S115','OR1115','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S111','OR1116','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1117','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S113','OR1118','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S114','OR1119','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S115','OR1110','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S111','OR1101','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S111','OR1111','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1121','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1131','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S113','OR1141','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1151','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S115','OR1161','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S114','OR1171','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S112','OR1181','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订购单VALUES('AA111111','S111','OR1191','2019-5-28',1000,'2019-5-29')

INSERT INTO wcp.订单明细VALUES('OR1110','01','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1111','01','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1112','02','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1113','04','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1114','05','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1115','06','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1116','07','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1117','08','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1118','09','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1119','10','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1110','11','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1111','12','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1112','13','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1113','14','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1114','15','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1115','16','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1116','17','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1117','18','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1118','19','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1119','20','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1110','21','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1112','22','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1111','23','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1113','24','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1115','25','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1114','26','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1116','27','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1117','28','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1118','29','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1119','30','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1110','32','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1111','31','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1112','33','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1113','34','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1114','35','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1115','36','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1116','37','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1117','38','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1118','39','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1119','40','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1110','41','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1111','42','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1112','43','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1113','44','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1114','45','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1115','46','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1117','47','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1118','48','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1119','49','洗洁精',10,10)

INSERT INTO wcp.订单明细VALUES('OR1110','50','洗洁精',10,10)

 

2．

违反实体完整性：INSERT INTO wcp.仓库VALUES('AA1111','广州',51)

原因：违反了PRIMARY KEY 约束'PK__仓库__4830B400'。不能在对象'wcp.仓库' 中插入重复键。

违反参照完整性：INSERT INTO wcp.职工VALUES('AA111122','AA1122',1001)

原因：职工表的仓库号参照仓库表的仓库号，此时仓库表中没有仓库号为'AA1122'的元组，INSERT 语句与FOREIGN KEY 约束"FK__职工__仓库号__4EDDB18F"冲突。该冲突发生于数据库"master"，表"wcp.仓库", column '仓库号'。

违反用户定义完整：

INSERT INTO wcp.仓库VALUES('AA1123','珠海',49)

原因：仓库表中的面积要大于50。INSERT 语句与CHECK 约束"CK__仓库__面积__4B0D20AB"冲突。该冲突发生于数据库"master"，表"wcp.仓库", column '面积'。

 

3．

DELETE wcp.仓库WHERE 城市='广州'

操作失败,因为职工表参照了该记录

 

空值删除：

DELETE wcp.供应商WHERE 供应商号='S116'

 

4.

UPDATE wcp.职工SET 仓库号='aa2222' WHERE 职工号='AA111111'

违反参照完整性：仓库表中没有仓库号为'aa2222'的元组

 

UPDATE wcp.仓库set 城市=null where 仓库号='AA1111'  

违反实体完整性，城市不为空值

 

UPDATE wcp.仓库SET 面积=49 WHERE 仓库号='AA1111'

违反用户定义完整性规则，因为面积被定义约束大于或等于50

 

5. UPDATE wcp.职工SET 工资=(工资*1.1)

 

6.

UPDATE wcp.订购单SET 金额=(SELECT SUM(单价*数量)FROM wcp.订单明细

WHERE 订购单.订购单号=订单明细.订购单号)

 

7. DELETE wcp.职工WHERE 仓库号IN (SELECT 仓库号FROM wcp.仓库WHERE 城市='深圳') 

DELETE wcp.订购单WHERE 供应商号IN(SELECT 供应商号FROM wcp.供应商WHERE 地址='广工')