business order

1.业务场景

一个电商平台需要记录

  1. 客户信息
  2. 产品信息
  3. 订单信息
  4. 订单明细(每个订单的产品及数量)
    要求:
  • 每个客户可以有多个订单
  • 每个订单可以包含多种产品
  • 需要支持统计每个客户总消费,销量排行等报表

2.表设计

1
2
3
4
#创建库
CREATE DATABASE business DEFAULT CHARETTE utf8;
#选择库
USE business;

Customers表(客户)

字段名 数据类型 约束 说明
customer_id INT PRIMARY 客户唯一编号
name VARCHAR(50) NOT NULL 客户姓名
email VARCHAR(100) UNIQUE 客户邮箱 
1
2
3
4
5
CREATE TABLE Customers(
customer_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE
)

Products表(产品)

字段名 数据类型 约束 说明
product_id INT PRIMARY KEY 产品唯一编号
product_name VARCHAR(100) NOT NULL 产品名称
price DECIMAY NOT NULL 产品单价 
1
2
3
4
5
CREATER TABLE Products(
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

Orders表(订单)

字段名 数据类型 约束 说明
order_id INT PRIMARY 订单编号
customer_id INT FOREIGN KEY->Customers(customer_id) 下单客户
order_date DATE NOT NULL 下单日期 
1
2
3
4
5
6
CREATE TABLE Orders(
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    );

OrderItems表(订单明细)

字段名 数据类型 约束 说明
order_item INT PRIMARY KEY 明细唯一编号
order_id INT FOREIGN KEY->Orders(order_id) 所属订单
product_id INT FOREIGN KEY->Products(product_id) 产品
quantity INT NOT NULL 数量 
1
2
3
4
5
6
7
8
CREATE TABLE OrderItems(
    order_item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES Orders(order_id),
    FOREIGN KEY (product_id) REFERENCES Products(Product_id)
);

3.插入示例数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#--Customers
INSERT INTO Customers VALUES
(1,'Alice','alice@example.com'),
(2,'Bob','bob@example.com');
#--Products
INSERT INTO Products VALUES
(101,'手机',1999.00),
(102,'耳机',299.00),
(103,'充电宝',149.00);
#--Orders
INSERT INTO Orders VALUES
(1001,1,'2025-11-01'),
(1002,2,'2025-11-02');

#OrderItems
INSERT INTO OrderItems VALUES
(1,1001,101,1),
(2,1001,102,2),
(3,1002,103,3);

多表聚合分析示例

统计每个产品销量

1
2
3
4
5
6
7
p.product name,
SUM(oi.quantity) AS total_sold
FROM Products p
JOIN OrderItems oi ON p.product_id = oi.product_id
GROUP BY p.product_id,p.product_name
ORDER BY total_sold DESC;

结果示例:

product_name total_sold
充电宝 3
耳机 2
手机 1