Oracle-----DML&DDL的综合案例(数据查询以及数据更新实战)

上一篇👉:Oracle-----DML&DDL的综合案例(创建数据表、增加测试数据)

---

总目录👉震惊！史上最菜的Oracle 11g教程(大佬勿进)

---

🚴大家好！我是近视的脚踏实地，这篇文章主要是继续来完成一个DML&DDL的综合案例，这篇主要是数据查询以及数据更新实战
     唯有行动  才能解除你所有的不安

1、数据查询

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

1.1 求购买了供应商"宝洁"产品的所有顾客

(1)、求购买了供应商"宝洁"产品的所有顾客;
     ※ 确定要使用的数据表：
          ※ customer表：可以取得顾客信息；
          ※product表：可以找到供应商的信息；
          ※purcase表：保存顾客购买商品的记录。

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

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

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

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

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

select *
from customer
where customerid in(
    select customerid
    from purcase
    where productid in (
        select productid
        from product
        where provider='宝洁'));

1.2 求购买的商品包含了顾客“Dennis"所购买的所有商品的顾客(姓名)

(2)、求购买的商品包含了顾客“Dennis"所购买的所有商品的顾客(姓名);
     ※ 确定要使用的数据表：
          ※ customer表：顾客信息；
          ※purcase表：顾客购买的商品记录。

第一步： 至少需要知道“Dennis"购买了哪些商品？所有的购买记录保存在了purcase表中，而要想查购买记录，只需要知道顾客的编号即可。顾客编号又可以通过customer表查询

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

可以发现此人购买了M01、M05、M08的信息，而其他用户必须包含这些内容才可以算是购买过次商品。
第二步： 现在先不嵌套子查询，就假设已经知道购买的顾客编号为"C01",再来分析👇:

select productid
from    purcase
where customerid='C01';

再来看"C02"的购买记录👇:M02、M06

select productid
from    purcase
where customerid='C02';

再来看"C03"的购买记录👇:M01、M05、M06、M08

select productid
from    purcase
where customerid='C03';

再来看"C04"的购买记录👇:M03、M04

select productid
from    purcase
where customerid='C04';

再来看"C05"的购买记录👇:M03、M04

select productid
from    purcase
where customerid='C05';

第三步： 那么经过上面分析，可以看到只有C01和C03购买的包含了M01、M05、M08，那么现在的问题就在于如何可以将M03号，其他编号删除呢？于是我们可以借助集合操作
通过差集计算可以发现，能找到规律。
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和C04顾客做差运算👇:(M01、M05、M08)

select productid
from    purcase
where customerid='C01'
        minus
select productid
from    purcase
where customerid='C04';

那么就可以发现规律了，如果包含C01的全部内容，他的结果是null，曾经学习过一个运算符(exists)，这个运算符的特点是如果有数据则查询，如果没有数据则不查询。

select *
from customer ca
where not exists(
   select p1.productid
   from    purcase p1
   where customerid=(
	      select customerid
          from customer
          where name='Dennis')
      minus
   select p2.productid
   from    purcase p2
   where customerid=ca.customerid
and ca.name<>'Dennis'
);

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

(3)、求牙膏卖出数量最多的供应商。
     ※ 确定要使用的数据表：
          ※product表：可以找到供应商的信息；
          ※product表：商品分类、以及出售的数量；
          ※purcase表：销售记录。

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

select productid
from    product
where category='牙膏';

第二步： 以上查询返回多行单列，想也不用想，在where子句后使用。(忘记的可以回去再看看👉:Oracle-----子查询简介&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 productid
    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
));

以上就是多对多的查询分析

2、数据更新

4、数据更新

(1)、将所有的牙膏商品单价增加10%。

update product set unitprice=unitprice*1.1 where category='牙膏';

(2)、删除从未被购买的商品记录。
**第一步：**找出所有购买过的商品信息

select productid from purcase;

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

delete from product where productid not in(select productid from purcase);

---

下一篇👉Oracle-----定义序列&定义特殊序列

本篇博客到这就完啦，非常感谢您的阅读🙏，如果对您有帮助，可以帮忙点个赞或者来波关注鼓励一下喔😬 ，嘿嘿👀