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;