Oracle从入门到总裁:​​​​​​https://blog.csdn.net/weixin_67859959/article/details/135209645

现有一个商店的数据库，记录顾客及其购物情况。根据要求完成任务

此数据库由下面 3 个表组成。

商品 product（商品号 productid，商品名 productname，单价 unitprice，商品类别 category，供应商 provider）

顾客 customer（顾客号 customerid，姓名 name，住址 location）

购买 purcase（顾客号 customerid，商品号 productid，购买数量 quantity）

每个顾客可以购买多件商品，每件商品可以被多个顾客购买。属于多对多的关系。

数据表的创建将在下一章介绍。

假设这 3 个数据表已经创建，现在需要完成下面任务 

（1）往表中插入数据。

商品（M01，佳洁士，8.00，牙膏，宝洁；

M02，高露洁，6.50，牙膏，高露洁；

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，东城；

C04，Jenny，东城；

C05，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；）。

（2）用 SQL 语句完成下列查询。

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

② 求购买的商品包含了顾客“Dennis”所购买的所有商品的顾客（姓名）。

③ 求牙膏卖出数量最多的供应商。

（3）将所有的牙膏商品单价增加 10%

（4）删除从未被购买的商品记录

下面我们就来看看如何实现

（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,'洗衣粉','宝洁') ;

增加用户信息

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','西城') ;

增加购买记录

INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M01',3) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M05',2) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C01','M08',2) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M02',5) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C02','M06',6) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M01',1) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M05',1) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M06',3) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C03','M08',1) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M03',7) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C04','M04',3) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M06',2) ;
INSERT INTO purcase (customerid,productid,quantity) VALUES ('C05','M07',8) ;

最后一定要提交事务

commit

如果事务不提交，那么 session 一旦关闭数据就消失了

（2）用 SQL 语句完成下列查询

与之前的部门和员工不同的是，本次的查询属于多对多的查询应用，这一点在某种程度上决定了查询的复杂度

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

确定要使用的数据表

customer 表：可以取得顾客信息。

product 表：商品表中可以找到供应商信息。

purcase 表：保存顾客购买商品的记录。

第一步：找到供应商“宝洁”的所有商品编号，因为有了商品编号才可以查找到购买记录

select productid
from product 
where provider='宝洁';

 第二步：以上的查询返回多行单列，按照要求，应该在 where子句之中出现，现在又属于一个范围的匹配，那么可以使用 IN 进行判断，找出购买记录是为了找到顾客信息

select customerid
from purcase
where productid IN (select productidfrom product where provider='宝洁') ;

第三步：以上返回了顾客的编号，直接利用 where子句过滤

select *
from customer
where customerid IN (select customeridfrom purcasewhere productid IN (select productidfrom product where provider='宝洁') ) ;

② 求购买的商品包含了顾客“Dennis”所购买的所有商品的顾客（姓名）。

确定要使用的数据表

customer 表：顾客信息。

purcase 表：购买的商品记录。

首先需要知道“Dennis”购买了那些商品。所有的购买记录保存在了 purcase 表之中，而要想查购买记录，只需要知道顾客的编号即可。

顾客编号通过 customer 表查询

select productid
from purcase
where customerid=(select customeridfrom customerwhere name='Dennis' );

可以发现，此人购买了 M01、M05、M08，而其他用户必须包含这些内容才可以算是购买过此商品。

现在先不嵌套子查询，假设已经知道了购买的顾客编号为“C01”。

SELECT productid FROM purcase WHERE customerid='C01' MINUS
SELECT productid FROM purcase WHERE customerid='C03' ;

“C01”的购买记录：M01、M05、M08

依次内推

SELECT productid FROM purcase WHERE customerid='C01' ;
SELECT productid FROM purcase WHERE customerid='C02' ;
SELECT productid FROM purcase WHERE customerid='C03' ;
SELECT productid FROM purcase WHERE customerid='C04' ;
SELECT productid FROM purcase WHERE customerid='C05' ;

那么现在的问题就在于如何可以将 C03 保留，其他编号删除。可以借助集合操作。通过差集的计算可以找到规律

C01 和 C02 顾客做差运算（M01、M05、M08）

SELECT productid FROM purcase WHERE customerid='C01' MINUS
SELECT productid FROM purcase WHERE customerid='C02' ;

C01 和 C03 顾客做差运算（null）

SELECT productid FROM purcase WHERE customerid='C01' MINUS
SELECT productid FROM purcase WHERE customerid='C03' ;

如果包含有 C01 的全部内容差的结果是 null，可以利用学习过一个运算符补充，这个运算符的特点是如果有数据则查询，如果没有数据则不查询

SELECT * 
FROM customer ca
WHERE NOT EXISTS(SELECT p1.productid FROM purcase p1WHERE customerid=(SELECT customeridFROM customer WHERE name='Dennis') MINUSSELECT p2.productid FROM purcase p2WHERE customerid=ca.customerid ) 
AND ca.name<>'Dennis' ;

③ 求牙膏卖出数量最多的供应商

确定要使用的数据表

product 表：供应商信息

product 表：商品分类以及出售的数量

purcase 表：销售记录

第一步：查找出牙膏的商品编号，如果没有编号就不可能知道购买记录。

SELECT productid FROM product WHERE category='牙膏' ;

第二步：以上查询返回多行单列，把返回的结果在 WHERE 子句嵌套使用。根据 purcase 表找到所有牙膏的销售数量

SELECT productid,SUM(quantity)
FROM purcase 
WHERE productid IN (SELECT productid FROM product WHERE category='牙膏')
GROUP BY productid;

第三步：因为要找牙膏的最高销售数量，所以需要进行统计函数嵌套，而一旦嵌套之后，统计查询的 SELECT 子句里面不允许出现其他任何字段

SELECT productid,SUM(quantity)
FROM purcase 
WHERE productid IN (SELECT productid FROM product WHERE category='牙膏')
GROUP BY productid
HAVING SUM(quantity)=(SELECT MAX(SUM(quantity))FROM purcase WHERE productid IN (SELECT productid FROM product WHERE category='牙膏')GROUP BY productid);

第四步：由于最后只是需要一个供应商的信息，只需要根据商品编号查找到供应商信息即可

SELECT provider
FROM product
WHERE productid=(SELECT productidFROM purcase WHERE productid IN (SELECT productid FROM product WHERE category='牙膏')GROUP BY productidHAVING SUM(quantity)=(SELECT MAX(SUM(quantity))FROM purcase WHERE productid IN (SELECT productid FROM product WHERE category='牙膏')GROUP BY productid));

（3）将所有的牙膏商品单价增加 10%

UPDATE product SET unitprice=unitprice*1.1 WHERE category='牙膏' ;

（4）删除从未被购买的商品记录

第一步：找出所有购买过的商品信息

SELECT productid FROM purcase ;

第二步：使用 not in 就可以表示未购买过的商品记录

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