关于oracle的学习记录:
四十二、综合实战:dml&ddl(建表、增加数据)
自己建立数据表(约束)、并且实现数据的增加、删除、查询、删除
现有一个商店的数据库,记录顾客以及其购物情况,由下面3个表组成:
**商品 product(商品号productid,商品名productname,单价unitprice,商品类别category,供应商provider);
**顾客 customer(顾客号customerid,姓名name,住址location);
**购买purchase(顾客号customerid,商品号productid,购买数量quantity);
每个顾客可以购买多个商品,每件商品可以被多个顾客购买,属于多对多的关系
使用sql语句完成下列功能:
1.建表,在定义中要求声明如下约束:
(1)、每个表的主外键;
(2)、顾客的姓名和商品名不能为空;
(3)、单价必须大于0,购买数量必须在0-20之间;
–删除数据表
drop table purchase purge;
drop table product purge;
drop table customer purge;
–创建数据表
–1、创建顾客表
create tabel customer(
customerid varchar2(3),
name varchar2(20) not null,
location varchar2(50),
constraint pk_customerid primary key(customerid)
);
–2、创建商品表
create tabel product(
productid varchar2(3),
productname varchar2(20) not null,
unitprice number,
category varchar2(20),
provider varchar2(20),
constraint pk_productid primary key(productid),
constraint ck_unitprice check(unitprice>0)
);
–3、创建购买记录表
–2、创建商品表
create tabel purchase(
customerid varchar2(3),
productid varchar2(3),
quantity number,
constraint fk_customerid foreign key(customerid) references customer(customerid) on delete cascade,
constraint fk_productid foreign key(productid) references product(productid) on delete cascade,
constraint ck_quantity check(quantity between 0 and 20)
);
–测试数据
–事务提交
2.往表中插入数据:
商品( m01,佳洁士,8.00,牙膏,宝洁;
m02,高露洁,6.05,牙膏,高露洁;
m03,洁诺,5.00,牙膏,联合利华;
m04,舒肤佳,3.00,香皂,宝洁;
m05,夏士莲,5.00,香皂,联合利华;
m06,雕牌,2.50,洗衣粉,纳爱斯;
m07,中华,3.50,牙膏,联合利华;
m08,汰渍,3.00,洗衣粉,宝洁;
m09,碧浪,4.00,洗衣粉,宝洁;
)
顾客( c01,dennis,海淀;
c02,john,朝阳;
c03,tom,东城;
c05,jenny,东城;
c06,rick,西城
)
购买( c01,m01,3;
c01,m05,2;
c01,m08,2;
c02,m02,5;
c02,m06,4;
c03,m01,1;
c03,m05,1;
c03,m06,3;
c03,m08,1;
c04,m03,7;
c04,m04,3;
c05,m06,2;
c05,m07,8;
)
商店有9条记录,顾客有5条记录,购买有13条记录
–测试数据
–1、增加商品数据
insert into product(productid,productname,unitprice,category,provider) values(‘m01′,’佳洁士’,8.00,’牙膏’,’宝洁’);
insert into product(productid,productname,unitprice,category,provider) values(‘m02′,’高露洁’,6.50,’牙膏’,’高露洁’);
insert into product(productid,productname,unitprice,category,provider) values(‘m03′,’洁诺’,5.00,’牙膏’,’联合利华’);
insert into product(productid,productname,unitprice,category,provider) values(‘m04′,’舒肤佳’,3.00,’香皂’,’宝洁’);
insert into product(productid,productname,unitprice,category,provider) values(‘m05′,’夏士莲’,5.00,’香皂’,’联合利华’);
insert into product(productid,productname,unitprice,category,provider) values(‘m06′,’雕牌’,2.50,’洗衣粉’,’纳爱斯’);
insert into product(productid,productname,unitprice,category,provider) values(‘m07′,’中华’,3.50,’牙膏’,’联合利华’);
insert into product(productid,productname,unitprice,category,provider) values(‘m08′,’汰渍’,3.00,’洗衣粉’,’宝洁’);
insert into product(productid,productname,unitprice,category,provider) values(‘m09′,’碧浪’,4.00,’洗衣粉’,’宝洁’);
–2、增加顾客数据
insert into customer(customerid,name,location) values(‘c01′,’dennis’,’海淀’);
insert into customer(customerid,name,location) values(‘c02′,’john’,’朝阳’);
insert into customer(customerid,name,location) values(‘c03′,’tom’,’东城’);
insert into customer(customerid,name,location) values(‘c04′,’jenny’,’东城’);
insert into customer(customerid,name,location) values(‘c05′,’rick’,’西城’);
–3、增加购买记录数据
insert into purchase(customerid,productid,quantity) values(‘c01′,’m01’,3);
insert into purchase(customerid,productid,quantity) values(‘c01′,’m05’,2);
insert into purchase(customerid,productid,quantity) values(‘c01′,’m08’,2);
insert into purchase(customerid,productid,quantity) values(‘c02′,’m02’,5);
insert into purchase(customerid,productid,quantity) values(‘c02′,’m06’,4);
insert into purchase(customerid,productid,quantity) values(‘c03′,’m01’,1);
insert into purchase(customerid,productid,quantity) values(‘c03′,’m05’,1);
insert into purchase(customerid,productid,quantity) values(‘c03′,’m06’,3);
insert into purchase(customerid,productid,quantity) values(‘c03′,’m08’,1);
insert into purchase(customerid,productid,quantity) values(‘c04′,’m03’,7);
insert into purchase(customerid,productid,quantity) values(‘c04′,’m04’,3);
insert into purchase(customerid,productid,quantity) values(‘c05′,’m06’,2);
insert into purchase(customerid,productid,quantity) values(‘c05′,’m07’,8);
–事务提交
commit;