oracle-建表-增加数据

1.建表：

-----------------------------------------------------------------

a.每个表的主外键
b.顾客的姓名和商品名不能为空
c.单价必须大于0，购买数量必须0到20之间

-----------------------------------------------------------------

2.表的意义

-------------------------------------

顾客表--customer

customerid 【顾客号】
name 【姓名】
loaction 【地址】

商品表--product

productid 【商品编号】
productname【商品名称】
unitprice 【商品价格】
category 【商品种类】

provider   【供应商】

购买记录表  -- purcase
customerid 【顾客号】
productid 【商品号】
quantity 【购买的数量】
------------------------------------------

删除数据表

-------------------------------------------------

DROE TABLE purcase PURGE;
DROE TABLE product PURGE;

DROE TABLE customer PURGE;

-----------------------------------------

创建数据表

----------------------------------------

--1.创建顾客表；

----------------------------------------------------------

CREATE TABLE customer(
customerid VARCHAR2(3), 【顾客号】
name VARCHAR2(20) NOT NULL,【姓名】
loaction VARCHAR2(50), 【地址】
CONSTRAINT pk_customerid PRIMARY KEY(customerid)

); 

---------------------------------------

--2.创建商品表；

--------------------------------------

CREATE TABLE 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.创建购买记录表

------------------------------------------------------------------------------------------

CREATE TABLE  purcase(
customerid VARCHAR(3),【顾客号】
productid VARCHAR(20), 【商品号】
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)
);
---------------------------------------------------------------------------------------------------------------------

测试数据

--------------------------------------------------------------------------------------------------------

1.增加商品数据

------------------------------------------------------------------------------------------------------------

INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO1','佳洁士',8.00,'牙膏','宝洁');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO2','高露洁',6.50,'牙膏','高露洁');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO3','洁诺',5.00,'牙膏','联合利华');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO4','舒肤佳',3.00,'香皂','宝洁');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO5','夏士莲',5.00,'香皂','联合利华');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO6','雕牌',3.00,'洗衣粉','纳爱斯');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO7','中华',3.50,'牙膏','联合利华');
INSERT INTO product(productid,productname,unitprice,category,provider)
VALUES('MO8','汰渍',3.00,'洗衣粉','宝洁');
INSERT INTO product(productid,productname,unitprice,category,provider)

VALUES('MO9','碧浪',4.00,'洗衣粉','宝洁');

-----------------------------------------------------------------------------------------------------------------

2.增加顾客表

------------------------------------------------------------------------------------------------------------------

INSERT INTO customer(customerid,name,loaction)
VALUES('CO1','Dennis','海淀');
INSERT INTO customer(customerid,name,loaction)
VALUES('CO2','John','朝阳');
INSERT INTO customer(customerid,name,loaction)
VALUES('CO3','Tom','东城');
INSERT INTO customer(customerid,name,loaction)
VALUES('CO4','Jenny','东城');
INSERT INTO customer(customerid,name,loaction)
VALUES('CO5','Rick','西城');
-------------------------------------------------------------------------------

3.增加购买记录表

---------------------------------------------------------------------------------------

INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO1','M01',3);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO1','M05',2);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO1','M08',2);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO2','M02',5);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO2','M06',4);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO3','M01',1);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO3','M05',1);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO3','M06',3);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO3','M08',1);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO4','M03',7);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO4','M04',3);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO5','M06',2);
INSERT INTO purcase(customerid,productid,quantity)
VALUES('CO5','M07',8);
---------------

-----事务提交

COMMIT;
-------------------------------------------------------------

3.用SQL语句完成查询：

1.求购买了供应商“宝洁”产品的所有顾客

·确定要使用的数据表
  |— customer表：顾客信息
  |— product表：供应商的信息：
  |— purcase表：需要连接商品和顾客信息

第一步：找的供应商“宝洁”提供的商品编号

SELECT  productid
FROM  product
WHERE provider = '宝洁'；

第二步：根据商品编号查询顾客信息

SELECT customerid
FROM purcase
WHERE productid IN(
SELECT  productid
FROM  product
WHERE provider = '宝洁');
第三步 根据顾客编号 查询顾客信息（最终结果）
=========================================
 DISTINCT   除去重复的数据
=========================================
SELECT  DISTINCT * 
FROM customer
WHERE customerid IN(
SELECT customerid
FROM purcase
WHERE productid IN(
SELECT  productid
FROM  product
WHERE provider = '宝洁')
);
-------------------------------------------------------------

2.求牙膏卖出数量最多的供应商：

·确定要使用的数据表

 |-product;找到牙膏类型的商品编号
 |-purcase  找到所有的购买记录
·确定已知的关联字段
 |-product.productid = purcase.productid

第一步 将商品表与购买表记录做多表查询

SELECT pro.provider,p.quantity
FROM purcase p, product pro
WHERE  p.productid = pro.productid
AND pro.category ='牙膏';

第二步 统计每个供应商的销售总量

SELECT pro1.provider,p1.quantity,SUM(p1.quantity) sum
FROM purcase p, product pro1
WHERE p1.productid = pro1.productid
AND pro1.category ='牙膏'
GROUP BY pro1.provider

第三步：

SELECT pro1.provider,SUM(p1.quantity) sum
FROM purcase p1, product pro1
WHERE p1.productid = pro1.productid
AND pro1.category ='牙膏'
GROUP BY pro1.provider
HAVING SUM(p1.quantity) =(
SELECT MAX(SUM(p1.quantity))
FROM purcase p1,product pro1
WHERE p.productid = pro1.productid
AND pro1.category ='牙膏'
GROUP BY pro1.provider);
-----------------------------------------------------------------------------------------

4.将所有的牙膏商品单价增加10%

UPDATE product SET unitprice = unitprice * 1.1 

WHERE category = '牙膏';

--------------------------------------------------------------------------------------

5.删除从来没有购买过得商品记录

DELETE FROM product WHERE productid NOT IN(
SELECT productid FROM purcase
);