mysql实验的好处_数据库（SQL）：实验三 数据操作和完整性约束的作用

1、自行设计各个表的记录

insert into test.仓库 values ('QW0001','广州',500)

insert into test.仓库 values ('QW0002','上海',900)

insert into test.仓库 values ('QW0003','北京',2500)

insert into test.仓库 values ('QW0004','纽约',1000)

insert into test.仓库 values ('QW0005','深圳',650)

insert into test.职工 values ('ZG000001','QW0005',3000)

insert into test.职工 values ('ZG000002','QW0001',5600)

insert into test.职工 values ('ZG000003','QW0004',6600)

insert into test.职工 values ('ZG000004','QW0002',3800)

insert into test.职工 values ('ZG000005','QW0005',4900)

insert into test.职工 values ('ZG000006','QW0003',5700)

insert into test.职工 values ('ZG000007','QW0001',2300)

insert into test.职工 values ('ZG000008','QW0003',4800)

insert into test.职工 values ('ZG000009','QW0002',4400)

insert into test.职工 values ('ZG000010','QW0004',5900)

insert into test.供应商 values('S001','华广','深圳')

insert into test.供应商 values('S002','广工','广州')

insert into test.供应商 values('S003','美的','茂名')

insert into test.供应商 values('S004','乐视','深圳')

insert into test.供应商 values('S005','康佳','广州')

insert into test.订购单

values('ZG000007','S005','OR0001','2015-1-6',5000,'2015-2-5')

insert into test.订购单

values('ZG000002','S001','OR0002','2015-2-6',10000,'2015-3-8')

insert into test.订购单

values('ZG000003','S004','OR0003','2015-1-16',3000,'2015-3-5')

insert into test.订购单

values('ZG000006','S005','OR0004','2015-5-6',9000,'2015-7-5')

insert into test.订购单

values('ZG000004','S003','OR0005','2015-4-6',7000,'2015-6-1')

insert into test.订购单

values('ZG000005','S004','OR0006','2015-2-6',6000,'2015-7-15')

insert into test.订购单

values('ZG000009','S001','OR0007','2015-10-6',8000,'2015-12-25')

insert into test.订购单

values('ZG000010','S002','OR0008','2015-1-26',7500,'2015-12-5')

insert into test.订购单

values('ZG000008','S003','OR0009','2015-11-6',6900,'2015-11-11')

insert into test.订购单

values('ZG000007','S001','OR0010','2015-4-1',8600,'2015-4-28')

insert into test.订购单

values('ZG000003','S005','OR0011','2015-10-1',9100,'2015-12-5')

insert into test.订购单

values('ZG000004','S003','OR0012','2015-2-6',12000,'2015-6-22')

insert into test.订购单

values('ZG000008','S001','OR0013','2015-3-4',6000,'2015-4-1')

insert into test.订购单

values('ZG000006','S005','OR0014','2015-2-28',20000,'2015-3-24')

insert into test.订购单

values('ZG000009','S003','OR0015','2015-6-6',7700,'2015-12-15')

insert into test.订购单

values('ZG000010','S004','OR0016','2015-7-6',4500,'2015-11-22')

insert into test.订购单

values('ZG000001','S002','OR0017','2015-8-7',6800,'2015-12-2')

insert into test.订购单

values('ZG000002','S002','OR0018','2015-9-10',9900,'2015-12-25')

insert into test.订购单

values('ZG000005','S004','OR0019','2015-5-4',11500,'2015-6-1')

insert into test.订购单

values('ZG000001','S003','OR0020','2015-10-6',6700,'2015-5-25')

insert into test.订购单明细 values('OR0012','01','洗衣液',39,59)

insert into test.订购单明细 values('OR0014','02','香皂',14,99)

insert into test.订购单明细 values('OR0004','03','油',32,84)

insert into test.订购单明细 values('OR0005','04','米',30,125)

insert into test.订购单明细 values('OR0007','05','盐',14,55)

insert into test.订购单明细 values('OR0006','06','鸡蛋',26,24)

insert into test.订购单明细 values('OR0009','07','牛奶',37,68)

insert into test.订购单明细 values('OR0008','08','面包',54,67)

insert into test.订购单明细 values('OR0010','09','面膜',14,65)

insert into test.订购单明细 values('OR0019','10','鼠标',38,72)

insert into test.订购单明细 values('OR0020','11','键盘',44,55)

insert into test.订购单明细 values('OR0003','12','充电宝',57,245)

insert into test.订购单明细 values('OR0017','13','裤子',44,57)

insert into test.订购单明细 values('OR0015','14','衬衫',74,78)

insert into test.订购单明细 values('OR0016','15','手表',59,177)

insert into test.订购单明细 values('OR0018','16','发带',22,145)

insert into test.订购单明细 values('OR0015','17','耳机',46,78)

insert into test.订购单明细 values('OR0001','18','项链',56,69)

insert into test.订购单明细 values('OR0012','19','沐浴露',47,85)

insert into test.订购单明细 values('OR0004','20','包包',19,37)

insert into test.订购单明细 values('OR0009','21','篮球',89,183)

insert into test.订购单明细 values('OR0018','22','足球',46,37)

insert into test.订购单明细 values('OR0020','23','羽毛球',48,75)

insert into test.订购单明细 values('OR0011','24','网球拍',58,97)

insert into test.订购单明细 values('OR0013','25','乒乓球',37,87)

insert into test.订购单明细 values('OR0017','26','洗洁精',64,78)

insert into test.订购单明细 values('OR0001','27','酱油',23,44)

insert into test.订购单明细 values('OR0015','28','化妆品',21,66)

insert into test.订购单明细 values('OR0007','29','洗面奶',47,99)

insert into test.订购单明细 values('OR0005','30','药膏',66,151)

insert into test.订购单明细 values('OR0013','31','防晒霜',22,45)

insert into test.订购单明细 values('OR0008','32','牙膏',21,32)

insert into test.订购单明细 values('OR0006','33','牙刷',12,45)

insert into test.订购单明细 values('OR0008','34','水杯',16,57)

insert into test.订购单明细 values('OR0014','35','扫把',18,42)

insert into test.订购单明细 values('OR0010','36','拖把',16,37)

insert into test.订购单明细 values('OR0010','37','水桶',11,55)

insert into test.订购单明细 values('OR0010','38','垃圾桶',5,22)

insert into test.订购单明细 values('OR0010','39','相机',78,111)

insert into test.订购单明细 values('OR0010','40','音响',98,125)

insert into test.订购单明细 values('OR0010','41','吸尘器',89,222)

insert into test.订购单明细 values('OR0010','42','书',24,87)

insert into test.订购单明细 values('OR0010','43','笔记本',5500,298)

insert into test.订购单明细 values('OR0010','44','空调',3450,111)

insert into test.订购单明细 values('OR0010','45','洗衣机',1570,572)

insert into test.订购单明细 values('OR0010','46','电视',550,42)

insert into test.订购单明细 values('OR0010','47','油烟机',2000,45)

insert into test.订购单明细 values('OR0010','48','台式电脑',3400,100)

insert into test.订购单明细 values('OR0010','49','显示器',800,50)

insert into test.订购单明细 values('OR0010','50','移动插座',12,70)

2、

1)

insert into test.仓库 values('QW0001','惠州',600)

2)

insert into test.职工 values('ZG000011','QW0009',3000)

3)

insert into test.仓库 values('QW0006','天津',30)

3、

1)

delete test.仓库 where 城市='北京'

2)

delete test.供应商 where 供应商号='S002'

3)

alter table test.职工

drop contraint ck

alter table test.职工

drop column 仓库号

alter table test.职工

add 仓库号 char(6)constraint ck foreign key references test.仓库

on delete cascade

on update cascade

4、

1)

update test.职工 set 仓库号 ='QW0006' where 职工号='ZG000002'

2)

update test.仓库 set 仓库号 =null where 仓库号='QW0001'

3)

update test.仓库 set 面积=30 where 仓库号='QW0001'

5、

update test.职工 set 工资 =(工资+(工资*0.1))

6、

update.test.订购单 set 金额 =(select SUM(单价*数量) form test.订购单明细 where

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

7、

1)

delete test.职工 where 仓库号 in (select 仓库号 from test.仓库 where 城市

='北京')

2)

delete test.订购单 where 供应商号 in (select 供应商号 from test.供应商 where 供应商名

='广工')