第一篇练习：企业级订单系统

本练习以 Enterprise Order System 为背景，分 9 个任务，涵盖 库表设计、CRUD、外键约束、索引优化、多表联查、DDL 进阶 等核心操作。

✅ 场景背景

公司经营一个商品销售平台，涉及四张核心表：

customers（客户）
products（商品）
employees（员工）
orders（订单）

🧱 第一步：数据库 & 表设计

任务 1：创建数据库

-- 新建 enterprise_db 并切换
create database enterprise_db charset=utf8mb4;
use enterprise_db;

任务 2：按顺序创建表

-- customers 表
create table customers (
  customer_id int primary key auto_increment,
  name        varchar(50) not null,
  email       varchar(50) unique,
  signup_date date
) engine=innodb default charset=utf8mb4;

-- products 表
create table products (
  product_id int primary key auto_increment,
  name       varchar(50) not null,
  price      decimal(10,2) not null,
  stock      int not null
) engine=innodb default charset=utf8mb4;

-- employees 表
create table employees (
  employee_id int primary key auto_increment,
  name        varchar(50) not null,
  hire_date   date,
  department  varchar(50)
) engine=innodb default charset=utf8mb4;

-- orders 表（含外键）
create table orders (
  order_id    int primary key auto_increment,
  customer_id int not null,
  product_id  int not null,
  employee_id int not null,
  quantity    int not null,
  order_date  date not null,
  foreign key (customer_id)
    references customers(customer_id)
    on delete cascade,
  foreign key (product_id)
    references products(product_id)
    on delete restrict,
  foreign key (employee_id)
    references employees(employee_id)
    on delete set null
) engine=innodb default charset=utf8mb4;

点评

on delete cascade：删客户时自动删除相关订单
on delete restrict：禁止删有订单的商品
on delete set null：删员工时置空订单中 employee_id

✏️ 第二步：数据操作（CRUD）

任务 3：插入数据

-- customers
insert into customers(name,email,signup_date) values
  ('alice','alice@example.com','2025-05-10'),
  ('bob','bob@example.com','2025-05-12'),
  ('carol','carol@example.com','2025-05-14');

-- products
insert into products(name,price,stock) values
  ('pen',1.50,100),
  ('notebook',3.00,50),
  ('eraser',0.80,200);

-- employees
insert into employees(name,hire_date,department) values
  ('emp1','2025-01-05','sales'),
  ('emp2','2025-02-10','support'),
  ('emp3','2025-03-15','marketing');

-- orders（注意外键，插入之前已经插入过的数据）
insert into orders(customer_id,product_id,employee_id,quantity,order_date) values
  (1,1,1,10,'2025-05-15'),
  (2,2,2,5,'2025-05-16'),
  (3,3,3,20,'2025-05-17');

任务 4：基础查询

-- 4.1 按价格降序查询所有商品
select * from products
order by price desc;

-- 4.2 联表查询订单详情
方法1
方法1

SELECT
	a.order_id,
	a.order_date,(
	SELECT
		b.NAME 
	FROM
		customers b 
	WHERE
		b.customer_id = a.customer_id 
	) name01,
	( SELECT d.NAME FROM products d WHERE d.product_id = a.product_id ) name002,(
	SELECT
		c.NAME 
	FROM
		employees c 
	WHERE
		c.employee_id = a.employee_id 
	) 
FROM
	orders a


select
  o.order_id,
  c.name   as customer,
  p.name   as product,
  e.name   as employee,
  o.quantity,
  o.order_date
from orders o
inner join customers c on o.customer_id = c.customer_id
inner join products p  on o.product_id  = p.product_id
inner join employees e on o.employee_id = e.employee_id;

-- 4.3 查询近 7 天的订单
select * from orders
where order_date >= current_date - interval 7 day;

🔄 第三步：更新与删除

任务 5：更新操作

-- 更新某客户邮箱
update customers
set email = 'alice@new.com'
where customer_id = 1;

-- 商品涨价 10%
update products
set price = price * 1.10
where name = 'pen';

-- 员工调岗
update employees
set department = 'hr'
where name = 'emp2';

任务 6：删除操作

-- 删除离职员工（需先处理 orders.employee_id）
delete from orders
where employee_id = 3;

delete from employees
where employee_id = 3;

-- 删除库存为 0 的商品
delete from products
where stock = 0;

-- 删除客户及其订单（cascade）
delete from customers
where customer_id = 2;

🛠 第四步：表结构变更（ALTER TABLE）

任务 7：结构调整

-- 新增 vip_level 字段，默认 normal
alter table customers
  add column vip_level enum('normal','vip') not null default 'normal';

-- 修改 price 精度
alter table products
  modify column price decimal(12,2) not null;

-- 删除员工表 phone 列（假设存在）
alter table employees
  drop column phone;

⚡ 第五步：索引实战

任务 8：创建索引

-- 复合索引：(customer_id, order_date)
create index idx_ord_customer_date
  on orders(customer_id, order_date);

-- 对比 explain analyze
explain analyze
select * from orders
where customer_id = 1
  and order_date = '2025-05-15';

点评

复合索引最左前缀匹配可加速联合查询
使用 explain analyze 观察 index lookup vs table scan

🎯 第六步：复杂统计查询

任务 9：聚合与分组

-- 每位客户的订单数与总消费
select
  c.name,
  count(o.order_id)        as order_count,
  sum(p.price * o.quantity) as total_spent
from customers c
left join orders o  on c.customer_id = o.customer_id
left join products p on o.product_id  = p.product_id
group by c.customer_id, c.name;

-- 每位员工负责的订单数 > 1
select
  e.name,
  count(o.order_id) as handled_orders
from employees e
join orders o on e.employee_id = o.employee_id
group by e.employee_id, e.name
having handled_orders > 1;

🎉 练习到此结束，涵盖企业级库表设计、CRUD、外键完整性、索引优化、多表统计、DDL 进阶等核心技能。建议：每执行一步都使用 desc / select / explain analyze 验证效果，加深理解。

xxxxxxxxxx – 新建 enterprise_db 并切换create database enterprise_db charset=utf8mb4;use enterprise_db;sql

作于2025年5月20日

企业级订单系统练习