DML练习

下面是一个基于 MySQL 的商品表（products）和商品明细表（product_details）的设计，并提供了100条示例数据。

1. 商品表（products）：

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(10, 2),
  category_id INT
);

2. 商品明细表（product_details）：

CREATE TABLE product_details (
  id INT PRIMARY KEY,
  product_id INT,
  color VARCHAR(20),
  size VARCHAR(10),
  stock INT,
  FOREIGN KEY (product_id) REFERENCES products (id)
);

3. 插入示例数据：

-- 插入商品数据
INSERT INTO products (id, name, price, category_id) VALUES
  (1, '商品1', 19.99, 1),
  (2, '商品2', 29.99, 1),
  (3, '商品3', 9.99, 2),
  ...
  (100, '商品100', 39.99, 3);

-- 插入商品明细数据
INSERT INTO product_details (id, product_id, color, size, stock) VALUES
  (1, 1, '红色', 'S', 10),
  (2, 1, '红色', 'M', 20),
  (3, 2, '蓝色', 'S', 15),
  ...
  (100, 50, '黑色', 'L', 5);

现在，让我们来进行一些练习题目，包括插入、更新和删除操作，难度中等，需要使用关联子查询。

1. 练习题目1：插入一条新的商品记录，并插入该商品的明细记录。

INSERT INTO products (id, name, price, category_id) VALUES
  (101, '新商品', 49.99, 4);

INSERT INTO product_details (id, product_id, color, size, stock) VALUES
  (101, 101, '绿色', 'M', 10);

2. 练习题目2：将商品名为’商品10’的价格更新为39.99。

UPDATE products
SET price = 39.99
WHERE name = '商品10';

3. 练习题目3：删除所有库存小于等于5的商品明细记录。

DELETE FROM product_details
WHERE stock <= 5;

4. 练习题目4：将商品名为’商品5’的价格更新为59.99，并将该商品明细的颜色更新为’黄色’。

UPDATE products
SET price = 59.99
WHERE name = '商品5';

UPDATE product_details
SET color = '黄色'
WHERE product_id = (SELECT id FROM products WHERE name = '商品5');

下面给出了20个难度较高的增删改题目，基于上述表结构和数据。

1. 将商品名为’商品5’的价格更新为商品名为’商品6’的价格的两倍。

   UPDATE products
   SET price = (SELECT price * 2 FROM products WHERE name = '商品6')
   WHERE name = '商品5';

2. 将所有商品的价格都增加10%。

   UPDATE products
   SET price = price * 1.1;

3. 删除所有库存小于等于10的商品明细记录，并且对应的商品名以’商品’开头。

   DELETE FROM product_details
   WHERE stock <= 10
   AND product_id IN (SELECT id FROM products WHERE name LIKE '商品%');

4. 将所有商品的价格按照价格从高到低的顺序重新编号，并将编号存储在新的列中。

   SET @row_number = 0;
   UPDATE products
   SET new_id = (@row_number := @row_number + 1)
   ORDER BY price DESC;

5. 将所有商品明细的库存数量设置为对应商品的平均库存数量。

   UPDATE product_details
   SET stock = (
   SELECT AVG(stock) 
   FROM product_details pd 
   INNER JOIN products p ON pd.product_id = p.id
   WHERE p.id = product_details.product_id
   );

6. 将所有商品明细的颜色修改为与商品名相同的最后一个字符。

   UPDATE product_details pd
   INNER JOIN products p ON pd.product_id = p.id
   SET pd.color = RIGHT(p.name, 1);

7. 删除所有没有对应商品的商品明细记录。

   DELETE FROM product_details
   WHERE product_id NOT IN (SELECT id FROM products);

8. 将商品名为’商品1’的价格设置为与商品名为’商品2’的价格相同。

   UPDATE products p1
   INNER JOIN products p2 ON p1.name = '商品1' AND p2.name = '商品2'
   SET p1.price = p2.price;

9. 删除所有库存大于平均库存的商品明细记录。

   DELETE FROM product_details
   WHERE stock > (SELECT AVG(stock) FROM product_details);

10. 将商品名为’商品1’的价格按照商品明细的库存数量进行递增排序，并更新价格。

    SET @row_number = 0;
    UPDATE products p
    INNER JOIN (
    SELECT product_id, stock, (@row_number := @row_number + 1) AS row_num
    FROM product_details
    WHERE product_id = (SELECT id FROM products WHERE name = '商品1')
    ORDER BY stock ASC
    ) pd ON p.id = pd.product_id
    SET p.price = pd.row_num;

以上是一些难度较高的增删改题目，涵盖了复杂的查询和更新操作，可以帮助你进一步提升对 MySQL 数据库操作的理解和应用能力。