Oracle数据库学习之DML&DDL(建表、增加数据)实战

关于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;

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐