综合案例
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;
oracle数据库从入门到精通之三
转载本文章为转载内容,我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题,欢迎原作者联系我们进行内容更正或删除文章。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
Oracle数据库教程(从入门介绍安装、到精通使用)
Oracle数据库教程(从入门介绍安装、到精通使用)
服务器 关系型数据库 SQL -
MySQL数据库应用从入门到精通
作者:崔洋 贺亚茹ISBN: 978-7-113-21101-1出版时间:2016年1月第三版页数:431阅读时间:2018-05-05推荐指数:★★★★☆
mysql 数据库 读书笔记 mysql数据库 零基础 -
ActiveMQ从入门到精通(三)
ActiveMQ从入门到精通
Active MQ -
Oracle 从入门到精通系列 - 前言
Oracle 从入门到精通系列 - 前言
oracle 数据库 database sql -
学习Oracle数据库入门到精通教程资料合集
学习Oracle数据库入门到精通教程资料合集
学习Oracle数据库入门到精通教程资料 -
mysql从入门到精通之数据库安装图解
Mysql是基于C/S架构的。客户端/服务器Clinent /Server &
服务器 操作系统 开源软件 windows7 数据库安装 -
数据库版本管理框架-Flyway(从入门到精通)
数据库版本管理框架-Flyway(从入门到精通)
数据库 sql SQL Flyway