综合案例
ddl&dml
有一个商品数据库
1.数据表的创建 ddl
先编写数据库脚本
--删除数据表
drop table purcase purge;
drop table product purge;
drop table customer purge;
--创建数据表
create table product(
product_id varchar2(5),
product_name varchar2(30) not null,
unit_price number,
category varchar2(50),
provider varchar2(50),
constraint pk_product_id primary key(product_id),
constraint ck_unit_price check(unit_price>0)
);
create table customer(
customer_id varchar2(5),
customer_name varchar2(30) not null,
location varchar2(50),
constraint pk_customer_id primary key(customer_id)
);
create table purcase(
customer_id varchar2(5),
product_id varchar2(5),
quantity number,
constraint fk_customer_id foreign key(customer_id) references customer(customer_id) on delete cascade,
constraint fk_product_id foreign key(product_id) references product(product_id) on delete cascade,
constraint ck_quantity check(quantity)
);
--增加测试数据
--增加产品信息
insert into product(product_id,product_name,unit_price,category,provider)
values('M01','jjs',8.00,'yg','bj');
insert into product(product_id,product_name,unit_price,category,provider)
values('M02','glj',6.50,'yg','glj');
insert into product(product_id,product_name,unit_price,category,provider)
values('M03','jl',5.00,'yg','lhlh');
insert into product(product_id,product_name,unit_price,category,provider)
values('M04','sfj',3.00,'xz','bj');
insert into product(product_id,product_name,unit_price,category,provider)
values('M05','xsl',5.00,'xz','lhlh');
insert into product(product_id,product_name,unit_price,category,provider)
values('M06','dp',2.50,'xyf','nas');
insert into product(product_id,product_name,unit_price,category,provider)
values('M07','zh',3.50,'yg','lhlh');
insert into product(product_id,product_name,unit_price,category,provider)
values('M08','tz',3.00,'xyf','bj');
insert into product(product_id,product_name,unit_price,category,provider)
values('M09','bl',4.00,'xyf','bj');
--增加客户信息
insert into customer(customer_id,customer_name,location)
values('C01','Dennis','hd');
insert into customer(customer_id,customer_name,location)
values('C02','John','cy');
insert into customer(customer_id,customer_name,location)
values('C03','Tom','dc');
insert into customer(customer_id,customer_name,location)
values('C04','Jenny','dc');
insert into customer(customer_id,customer_name,location)
values('C05','Rick','xc');
--增加购买记录
insert into purcase(customer_id,product_id,quantity)
values('C01','M01',3);
insert into purcase(customer_id,product_id,quantity)
values('C01','M05',2);
insert into purcase(customer_id,product_id,quantity)
values('C01','M08',2);
insert into purcase(customer_id,product_id,quantity)
values('C02','M02',5);
insert into purcase(customer_id,product_id,quantity)
values('C02','M06',4);
insert into purcase(customer_id,product_id,quantity)
values('C03','M01',1);
insert into purcase(customer_id,product_id,quantity)
values('C03','M05',1);
insert into purcase(customer_id,product_id,quantity)
values('C03','M06',3);
insert into purcase(customer_id,product_id,quantity)
values('C03','M08',1);
insert into purcase(customer_id,product_id,quantity)
values('C04','M03',7);
insert into purcase(customer_id,product_id,quantity)
values('C04','M04',3);
insert into purcase(customer_id,product_id,quantity)
values('C05','M06',2);
insert into purcase(customer_id,product_id,quantity)
values('C05','M07',8);
commit;