SQL 综合练习
– 创建 customers 表，用于存储客户信息
CREATE TABLE IF NOT EXISTS customers (id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘客户ID，自增长’,name varchar(255) NOT NULL COMMENT ‘客户姓名，非空’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’客户信息表’;
– 创建 orders 表，用于存储订单信息
CREATE TABLE IF NOT EXISTS orders (id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘订单ID，自增长’,customer_id int(11) NOT NULL COMMENT ‘客户ID，非空’,order_date date NOT NULL COMMENT ‘订单日期，非空’,total_amount decimal(10,2) NOT NULL COMMENT ‘订单总金额，非空’,
PRIMARY KEY (id),
FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单信息表’;
– 创建 order_items 表，用于存储订单商品信息
CREATE TABLE IF NOT EXISTS order_items (id int(11) NOT NULL AUTO_INCREMENT COMMENT ‘商品ID，自增长’,order_id int(11) NOT NULL COMMENT ‘订单ID，非空’,product_name varchar(255) NOT NULL COMMENT ‘商品名称，非空’,quantity int(11) NOT NULL COMMENT ‘商品数量，非空’,price decimal(10,2) NOT NULL COMMENT ‘商品单价，非空’,
PRIMARY KEY (id),
FOREIGN KEY (order_id) REFERENCES orders (id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=’订单商品信息表’;
– 向 customers 表插入数据
INSERT INTO customers (name)
VALUES
(‘张丽娜’),(‘李明’),(‘王磊’),(‘赵静’),(‘钱伟’),
(‘孙芳’),(‘周涛’),(‘吴洋’),(‘郑红’),(‘刘华’),
(‘陈明’),(‘杨丽’),(‘王磊’),(‘张伟’),(‘李娜’),
(‘刘洋’),(‘陈静’),(‘杨阳’),(‘王丽’),(‘张强’);
SELECT * FROM customers;
– 向 orders 表插入数据
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES
(1, ‘2022-01-01’,100.00),(1, ‘2022-01-02’,200.00),
(2, ‘2022-01-03’,300.00),(2, ‘2022-01-04’,400.00),
(3, ‘2022-01-05’,500.00),(3, ‘2022-01-06’,600.00),
(4, ‘2022-01-07’,700.00),(4, ‘2022-01-08’,800.00),
(5, ‘2022-01-09’,900.00),(5, ‘2022-01-10’,1000.00);
SELECT * FROM orders;
– 向 order_items 表插入数据
INSERT INTO order_items (order_id, product_name, quantity, price)
VALUES
(1, ‘耐克篮球鞋’,1,100.00),
(1, ‘阿迪达斯跑步鞋’,2,50.00),
(2, ‘匡威帆布鞋’,3,100.00),
(2, ‘万斯板鞋’,4,50.00),
(3, ‘新百伦运动鞋’,5,100.00),
(3, ‘彪马休闲鞋’,6,50.00),
(4, ‘锐步经典鞋’,7,100.00),
(5, ‘亚瑟士运动鞋’,10,50.00),
(5, ‘帆布鞋’,1,100.00),
(1, ‘苹果手写笔’,2,50.00),
(2, ‘电脑包’,3,100.00),
(3, ‘苹果手机’,4,50.00),
(4, ‘苹果耳机’,5,100.00),
(5, ‘苹果平板’,7,100.00);
SELECT * FROM order_items;
– 需求1：查询每个客户的订单总金额
– 分析：客户的订单存在订单表里，可能有多个订单，这里需求 JOIN ON 关联2个表，然后用 GROUP BY 根据客户id 分组，再通过 SUM 函数计算价格总和。
SELECT
customers.NAME,
SUM(orders.total_amount) AS total_amount
FROM
customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY
customers.id
ORDER BY
total_amount DESC
LIMIT 0,
3;
– 需求2：查询每个客户的订单总金额，并计算其占比
– 分析：每个客户的总金额的需求上面已经实现了，这里需求算占比，就需要通过一个子查询来计算全部订单的总金额，然后相除
SELECT
customers.NAME,
SUM(orders.total_amount) AS total_amount,
SUM(orders.total_amount) / (SELECT SUM(total_amount) FROM orders) AS percentage
FROM
customers
INNER JOIN orders ON customers.id = orders.customer_id
GROUP BY
customers.id;
– 需求3：查询每个客户的订单总金额，并列出每个订单的商品清单
– 分析：这里在总金额的基础上，多了订单项的查询，需求多关联一个表
SELECT
customers.NAME,
orders.order_date,
orders.total_amount,
order_items.product_name,
order_items.quantity,
order_items.price
FROM
customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
ORDER BY
customers.name,
orders.order_date;
– 需求4:查询每个客户的订单总金额，并列出每个订单的商品清单，同时只显示客户名字姓 “张” 的客户记录
– 分析：总金额和商品清单的需求前面实现过了，现在只需要加一个 WHERE 来过滤只查询姓张的客户
SELECT
customers.NAME,
orders.order_date,
orders.total_amount,
order_items.product_name,
order_items.quantity,
order_items.price
FROM
customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
WHERE
name LIKE ‘张%’
ORDER BY
customers.NAME,
orders.order_date;
– 需求5：查询每个客户的订单总金额，并列出每个订单的商品清单，同时只显示订单日期在 2022-01-01 至 2022-01-03 之间的记录。
– 分析：通过 BETWEEN AND 来过滤时间范围。
SELECT
customers.NAME,
orders.order_date,
orders.total_amount,
order_items.product_name,
order_items.quantity,
order_items.price
FROM
customers
JOIN orders ON customers.id = orders.customer_id
JOIN order_items ON orders.id = order_items.order_id
WHERE
orders.order_date BETWEEN ‘2022-01-01’
AND ‘2022-01-03’
ORDER BY
customers.NAME,
orders.order_date;
– 需求6：查询每个客户的订单总金额，并计算商品数量，只包含商品名称包含 “鞋” 的商品，商品名用 - 连接，显示前3条记录
– 分析：查询订单总金额和商品总数量都需要用 GROUP BY 根据 customer.id 分组，过滤出只包含鞋的商品 %鞋%，把分组的多条商品名连接需要用 GROUP_CONCAT 函数，SEPARATOR 只能用于 GROUP_CONCAT函数，指定拼接字符串之间的分隔符
SELECT
c.NAME AS cutomer_name,
SUM(o.total_amount) total_amount,
COUNT(oi.id) total_quantity,
GROUP_CONCAT(oi.product_name SEPARATOR ‘-‘) product_names
FROM
customers c
JOIN orders o ON c.id = o.customer_id
JOIN order_items oi ON o.id = oi.order_id
WHERE
oi.product_name LIKE ‘%鞋%’
GROUP BY
c.id
ORDER BY
total_amount DESC
LIMIT 3;
– 查询存在订单的客户
– 分析：使用子查询和 EXISTS 实现
SELECT
*
FROM
customers
WHERE
EXISTS (SELECT 1 FROM orders WHERE orders.customer_id = customers.id);
– 查询没有下单过的客户
SELECT
*
FROM
customers c
WHERE
NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
– 需求8：将王磊的订单总金额打九折
– 分析：update 更新订单金额，子查询查 王磊的订单
– 查询王磊的订单
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE customers.name = ‘王磊’;
– 订单可能有多条，使用 in 指定一个集合
UPDATE orders o SET o.total_amount = o.total_amount * 0.9
WHERE o.customer_id IN (SELECT id FROM customers WHERE name = ‘王磊’);
– 还原订单价格
UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.total_amount = o.total_amount / 0.9
WHERE c.name = ‘王磊’;