创建 customers 表，用于存储客户信息；创建 orders 表，用于存储订单信息；创建 order_items 表，用于存储订单商品信息。

-- 创建 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
        ('张丽娜'),('李明'),('王磊'),('赵静'),('钱伟'),
        ('孙芳'),('周涛'),('吴洋'),('郑红'),('刘华'),
        ('陈明'),('杨丽'),('王磊'),('张伟'),('李娜'),
        ('刘洋'),('陈静'),('杨阳'),('王丽'),('张强');

-- 向 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);

-- 向 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 customers;
select * from orders;
select * from order_items;

1: 查询每个客户的订单总金额

select customers.name, sum(orders.total_amount) as '订单总金额'
	from customers
	join orders on customers.id = orders.customer_id
	group by customers.id
	ORDER BY total_amount DESC;

2: 查询每个客户的订单总金额，并计算其占比

select customers.name, sum(orders.total_amount) as order_total,
sum(orders.total_amount) / (select sum(total_amount) from orders) as Percentage
from customers
join orders on customers.id = orders.customer_id
group by customers.id order by '订单总金额';

3: 查询每个客户的订单总金额，并列出每个订单的商品清单

select customers.name, orders.total_amount, orders.order_date, order_items.product_name, order_items.quantity
from customers
join orders on customers.id = orders.customer_id
join order_items on orders.id = order_items.order_id
order by orders.order_date, customers.name;

4: 查询每个客户的订单总金额，并计算其占比

select customers.name, sum(orders.total_amount) as order_total,
sum(orders.total_amount) / (select sum(total_amount) from orders) as Percentage
from customers
join orders on customers.id = orders.customer_id
group by customers.id order by '订单总金额';

5: 查询每个客户的订单总金额，并列出每个订单的商品清单，同时只显示客户名字姓“张”的客户的记录

select customers.name, orders.total_amount, orders.order_date, order_items.product_name, order_items.quantity
from customers
join orders on customers.id = orders.customer_id
join order_items on orders.id = order_items.order_id
where customers.name like '张%'
order by orders.order_date, customers.name;

6: 查询每个客户的订单总金额，并计算商品数量, 只包含商品名称包含“鞋”的商品，商品名用-连接，显示前 3 条记录

select customers.name,
	sum(orders.total_amount) as orders_total,
    count(order_items.id) as quantity_total,
    group_concat(order_items.product_name SEPARATOR '-') as product_names
from customers
join orders on customers.id = orders.customer_id
join order_items on orders.id = order_items.order_id
where order_items.product_name like '%鞋'
group by customers.id
order by orders_total
limit 3;

7:查询存在/不存在订单的客户

select customers.name from customers where exists (select * from orders where orders.customer_id = customers.id);
select customers.name from customers where not exists (select * from orders where orders.customer_id = customers.id);

8:将王磊的订单总金额打九折

update orders
	set orders.total_amount = orders.total_amount * 0.9
    where orders.customer_id in (select id from customers where name = '王磊');

select * from orders
    join customers on orders.customer_id = customers.id
    where customers.name = '王磊';