4.2 在DML语句中使用子查询

学习目标：

1.熟悉update、insert、delete语句的语法

2.在update语句中使用子查询

3.在insert语句中使用子查询

4.在delete语句中使用子查询

5.养成责任意识，做好数据备份

一、update、insert、delete语句的语法

— 1. update语句

UPDATE 表

SET 列1 = 表达式1 ,列2 = 表达式2 ……

WHERE 条件

— 2. INSERT 语句

INSERT INTO 表[(字段名列表)]

SELECT 查询结果

— 3. DELETE 语句

DELETE FROM 表

WHERE 条件

1. 维护数据前先备份

1. 实例：

— 在换季时节，美淘网为促销，决定将所有火锅类商品的团购价上调百分之十。

— 查询所有火锅类商品的团购价

-- 查询所有火锅类商品的团购价
-- 1.1 连接查询
SELECT p.title , p.currentPrice
FROM category c
NATURAL JOIN product p
WHERE c.categoryName = '火锅';
-- 1.2 非相关子查询
SELECT p.title ,p.currentPrice
FROM product p
WHERE p.categoryID in
        (SELECT c.categoryID
        FROM category c
        WHERE c.categoryName = '火锅');
-- 1.3 相关子查询
SELECT p.title , p.currentPrice
FROM product p
WHERE '火锅' = # 该商品的类型名
      (SELECT c.categoryName FROM category c 
             WHERE p.categoryID = categoryID
            );
-- 1.4 EXISTS子查询
SELECT p.title , p.currentPrice
FROM product p
WHERE EXISTS # 该商品的类型名
      (SELECT * FROM category c 
             WHERE p.categoryID = categoryID AND '火锅' = c.categoryName
            );

— 更新价格

UPDATE product p   -- 1 打开表   
SET p.currentPrice = p.currentPrice * 1.1  -- 4
WHERE '火锅' =                 -- 3
     (SELECT categoryName FROM category 
      WHERE p.categoryID = categoryID); -- 2

2.统计所有商品的总订购数量，并用此数量更新相应商品的“销售数量”字段。

SELECT p.title ,p.salesCount
FROM product p;
-- 统计各商品的销售数量
SELECT od.productID ,SUM(quantity)
FROM ordersdetail od
GROUP BY od.productID;
-- 以上数据更新product表的salesCount字段
-- 1.表product_copy的salesCount字段清零
UPDATE product_copy p
SET p.salesCount = 0;
-- 更新，set子句中使用相关子查询，思路清晰
UPDATE product_copy p
SET  p.salesCount = #该商品的销售量
        (SELECT SUM(quantity) FROM ordersdetail
     WHERE p.productID = productID
    );

-- 将非相关子查询作虚拟表，用在update子句中，进行表连接
UPDATE product p,
            -- 用t.销售数量更新p.salesCount
            -- 方法一：用where进行表连接
            (SELECT od.productID , SUM(od.quantity) 销售数量
            FROM ordersdetail od
            GROUP BY od.productID) t
SET  p.salesCount = t.`销售数量` 
WHERE t.productID = p.productID; -- 连接两个表

-- update中使用子查询作表连接
UPDATE product p
            -- 用t.销售数量更新p.salesCount
      -- 方法二：用join进行表连接 
JOIN            
            (SELECT od.productID , SUM(od.quantity) 销售数量
            FROM ordersdetail od
            GROUP BY od.productID) t
USING(productID)
SET  p.salesCount = t.`销售数量` ;

小结：update中使用子查询情况

1.update子句中一般使用非相关子查询作虚拟表，通过表之间连接更新数据。

2.set子句中一般使用相关子查询来计算同类的数据。

3.where子句中使用子查询与select子句中相同。

-- 借用连接查询的思维来更新表
UPDATE ordersdetail o
JOIN ordersdetail_copy oc USING(ordersID,productID)
SET oc.quantity = o.quantity;

思考：

1.如何更新orders表中订单金额字段。

提示：一个订单有多种商品，每个商品的金额累计就是订单金额。

2.如何使用ordersDetail表的quanqity字段来更新ordersDetail_copy表的quantity字段。

小结：

1.update语句中使用子查询，一般在set子句中使用相关子查询。

where子句中使用非相关效率高。

2.update语句如果使用连接数据更新，易于实现。

三、delete语句中使用子查询

-- 删除ordersdetail表对应的“雷亚波”所有的订单
-- 创建ordersdetail表的备份temp

CREATE TABLE temp SELECT * FROM ordersdetail;
-- 删除temp表中雷亚波的订单
DELETE FROM temp # 此处不能用别名
WHERE ordersID in
    (SELECT ordersID
        FROM orders o
        WHERE '雷亚波' = 
            (SELECT c.customerName from customer c
             WHERE o.customerID = customerID));

四、insert语句使用子查询解决唯一约束检查

• 添加新商品类型

SELECT * FROM category c;

# 先检查是否存在相同类型名，没有就添加
INSERT INTO category(category.categoryName,category.p_categoryID)
SELECT '新类型',4
FROM   DUAL  # 此方法适合5.5以上版本  
WHERE NOT EXISTS(
   SELECT * FROM category  WHERE categoryName = '新类型'
);