企业级 MySQL 实战（三）：SQL 语言开发与应用实战（电商订单系统）

本文以一个完整的电商订单系统为案例，从建库建表到增删改查、多表关联、函数、存储过程、视图索引，完整演示 MySQL SQL 语言开发。所有命令均在 MySQL 8.4 上真实执行。

一、数据库与表管理

1.1 创建数据库

CREATE DATABASE shop DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

生产环境务必使用 utf8mb4（完整支持 emoji 和生僻字），排序规则推荐 utf8mb4_0900_ai_ci（8.0+ 默认）。

1.2 创建表（覆盖各数据类型）

CREATE TABLE customers (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '客户ID',
  name VARCHAR(50) NOT NULL COMMENT '姓名',
  email VARCHAR(100) UNIQUE COMMENT '邮箱',
  phone CHAR(11) COMMENT '手机号',
  age TINYINT UNSIGNED COMMENT '年龄',
  balance DECIMAL(10,2) DEFAULT 0.00 COMMENT '余额',
  vip_level ENUM('普通','银卡','金卡','钻石') DEFAULT '普通' COMMENT 'VIP等级',
  birthday DATE COMMENT '生日',
  reg_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间',
  bio TEXT COMMENT '简介',
  is_active TINYINT(1) DEFAULT 1 COMMENT '是否激活',
  INDEX idx_name(name)
) ENGINE=InnoDB COMMENT='客户表';

数据类型速查：

类型	用途	示例
INT UNSIGNED	无符号整数主键	id
VARCHAR(n)	变长字符串	name
CHAR(n)	定长字符串（手机号）	phone
DECIMAL(m,d)	精确小数（金额）	balance
ENUM	枚举	vip_level
DATE/DATETIME	日期/时间	birthday/reg_time
TEXT	长文本	bio

⚠️ 金额必须用 DECIMAL，绝不能用 FLOAT/DOUBLE（有精度损失）。

1.3 生成列（8.0 特性）

订单明细表的小计用生成列自动计算：

subtotal DECIMAL(12,2) GENERATED ALWAYS AS (quantity * unit_price) STORED COMMENT '小计(生成列)'

Field      Type
subtotal   decimal(12,2)  STORED GENERATED

二、数据插入 INSERT

INSERT INTO customers (name,email,phone,age,balance,vip_level,birthday) VALUES
('张三','zhangsan@qq.com','13800000001',28,5000.00,'金卡','1998-05-20'),
('李四','lisi@qq.com','13800000002',32,2000.00,'银卡','1994-11-02');

插入后各表行数：

customers    5
products     7
orders       5
order_items  7

三、查询语句 SELECT

3.1 基础查询 + WHERE

SELECT id,name,email,vip_level,balance FROM customers WHERE balance > 1000 ORDER BY balance DESC;

4	赵六	zhaoliu@qq.com	钻石	15000.00
1	张三	zhangsan@qq.com	金卡	5000.00
2	李四	lisi@qq.com	银卡	2000.00

3.2 聚合 + GROUP BY + HAVING

SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price, MAX(price) AS max_price
FROM products GROUP BY category HAVING AVG(price) > 1000 ORDER BY avg_price DESC;

category	cnt	avg_price	max_price
笔记本	2	14499.000000	15999.00
手机	2	7999.000000	8999.00

WHERE 过滤原始行，HAVING 过滤聚合后的组，顺序：WHERE → GROUP BY → HAVING → ORDER BY。

3.3 多表 JOIN

SELECT o.order_no, p.name AS product, oi.quantity, oi.unit_price, oi.subtotal
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
WHERE o.order_no='ORD20260831001';

order_no	        product	        quantity  unit_price  subtotal
ORD20260831001	iPhone 15 Pro	1	        8999.00     8999.00
ORD20260831001	罗技鼠标	    1	        299.00      299.00

3.4 子查询与 UNION

-- 子查询：余额高于平均值的客户
SELECT name,balance FROM customers WHERE balance > (SELECT AVG(balance) FROM customers);
-- UNION：合并两个结果集
SELECT name AS item,'客户' AS type FROM customers WHERE balance>5000
UNION
SELECT name,'商品' FROM products WHERE price>5000;

四、更新与删除

UPDATE customers SET balance = balance + 100 WHERE vip_level='钻石';
UPDATE products SET price = price * 0.9 WHERE category='外设';
DELETE FROM order_items WHERE order_id IN (SELECT id FROM orders WHERE order_no='ORD20260831004');

⚠️ UPDATE/DELETE 务必带 WHERE，否则全表操作。生产环境建议先 SELECT 确认影响行数。

五、内置函数

SELECT
  CONCAT(name,'-',vip_level) AS 客户标签,
  UPPER(email) AS 邮箱大写,
  ROUND(balance,0) AS 余额取整,
  DATEDIFF(NOW(), birthday) AS 出生天数
FROM customers LIMIT 3;

客户标签	  邮箱大写	        余额取整  出生天数
张三-金卡	  ZHANGSAN@QQ.COM	5000	    10330
李四-银卡	  LISI@QQ.COM	    2000	    11625

CASE 条件表达式：

SELECT order_no,
  IF(status='已完成','✅完成','⏳未完成') AS 状态标记,
  CASE WHEN total_amount>10000 THEN '大额订单'
       WHEN total_amount>5000 THEN '中额订单'
       ELSE '小额订单' END AS 订单分级
FROM orders;

六、自定义函数与存储过程

6.1 自定义函数

DELIMITER //
CREATE FUNCTION get_customer_level(p_balance DECIMAL(10,2)) RETURNS VARCHAR(20) DETERMINISTIC
BEGIN
  DECLARE lvl VARCHAR(20);
  IF p_balance >= 10000 THEN SET lvl = '高价值客户';
  ELSEIF p_balance >= 5000 THEN SET lvl = '中价值客户';
  ELSE SET lvl = '普通客户'; END IF;
  RETURN lvl;
END//
DELIMITER ;

SELECT name, balance, get_customer_level(balance) AS 客户等级 FROM customers;

张三	5000.00	中价值客户
赵六	15100.00	高价值客户

6.2 存储过程

CREATE PROCEDURE sp_order_summary()
BEGIN
  SELECT status, COUNT(*) AS cnt, SUM(total_amount) AS amount FROM orders GROUP BY status;
END

CALL sp_order_summary();

已完成	2	25596.00
待发货	1	15999.00
待支付	1	6999.00

DELIMITER // 用于修改语句分隔符，避免存储过程内部的 ; 提前结束定义。

七、视图与索引

CREATE OR REPLACE VIEW v_order_detail AS
SELECT o.order_no, c.name AS customer, p.name AS product, oi.subtotal, o.status
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN customers c ON o.customer_id = c.id
JOIN products p ON oi.product_id = p.id;

八、约束验证（外键/唯一）

外键约束：插入不存在的客户 ID 会报错：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
  (`shop`.`orders`, CONSTRAINT `fk_orders_customer` ...)

唯一约束：重复邮箱会报错：

ERROR 1062 (23000): Duplicate entry 'zhangsan@qq.com' for key 'customers.email'

九、总结

本文通过电商订单系统，完整实战了 MySQL SQL 语言：建库建表 → 数据类型 → 增删改查 → 聚合/JOIN/子查询/UNION → 函数 → 存储过程 → 视图索引 → 约束。这些是数据库开发最核心、最常用的技能。

下一篇：深入 MySQL 的 InnoDB 存储引擎、事务 ACID 与锁机制。