MySQL 数据基础：关系、约束与订单查询

课前先保留判断：课前预习；课后用你的结果复验：课后复习。

今天从哪里开始

上一周能把订单保存到 JSON，今天运营带来商品 CSV，要先逐行校验并导出复核清单；客服还要按客户查历史订单，找出从未下单的客户。数据不再只是读回一整个文件，商品、客户、订单与明细之间的关系需要被反复查询和检查。

先完成商品文件的惰性读取与复核，再把这几类对象画成表与关系，连接 MySQL 建表、放入虚构样本、运行查询。最后故意制造一个无效关联，看看数据库能替我们挡住什么。

关系查询在本周项目中的位置

图中保留的是数据库主线；新增文件处理在它前面完成商品资料复核，MySQL 是否已写入仍以真实查询结果判断。

今天要学会什么

商品文件读取、迭代器、生成器与分批处理
MySQL 安装连接
PK、FK、约束
DECIMAL 和价格快照
CRUD、JOIN 与参数绑定

课堂结束时，用自己的项目检查：

能做出来：生成商品 JSONL 复核清单、解释一次性消费和尾批，再查询没有订单的客户并解释 LEFT JOIN。
能查出问题：针对“字符串拼 SQL、外键顺序错误、金额精度变化”中的风险，说明输入、预期与实际差异；失败后核对数据没有被误改。
能解释修改：提交代码、测试和修改前后结果，讲清自己接受或拒绝了哪一处 AI 建议。

先帮运营处理一份商品表

我接到一份商品 CSV：编号、名称、单价。运营要先检查坏行，再按小批次处理；客服随后要查询订单。今天把两条工作连起来：文件负责收进资料，Python 负责逐行校验，MySQL 负责保存关系和查询事实。

我继续使用上周自己的金额、函数和异常规则。当天日包提供 sql/schema.sql、sql/seed.sql 和 CSV 样本；我补 commerce_ops/import_products.py，不是从空目录重写商城框架。

运营 CSV → 一行文字 → Decimal 校验 → 一批商品 → JSONL 复核清单
                                                    ↓ 人工核对字段
                       当天 SQL 样本 → MySQL 关系表 → 客服订单查询

导出工具实际读写本地文件；完成复核后，可通过日包提供的 MySQL 接入支架向专用实验库新增商品。两步分别运行、分别检查，生成文件本身不代表数据库写入。

打开文件时先确定路径、编码和关闭时机

为什么学：同一份商品表换个启动目录就读不到，中文变乱码，或读取出错后文件一直占用，都会阻止运营导入。商城中的使用位置是导入器入口。

Path 表示路径；相对路径以当前工作目录为起点。open 返回文件对象；with 在退出代码块时关闭它，发生异常也会退出。encoding 约定字节怎样转成文字。CSV 用 newline="" 让 csv 模块处理换行；utf-8-sig 同时兼容常见的 UTF-8 BOM 表头。

在自己的累计项目根运行这个小例，先确认那里已有日包的 data/products.csv：

from pathlib import Path
import csv
 
source = Path("data/products.csv")
print("工作目录", Path.cwd())
print("目标文件", source.resolve())
with source.open(encoding="utf-8-sig", newline="") as stream:
    reader = csv.DictReader(stream)
    first = next(reader)
    print(first["name"], type(first["unit_price_yuan"]).__name__)
print(stream.closed)

样本首行输出“轻羽无线鼠标 str”，离开 with 后输出 True。CSV 单价读入后仍是文本，继续使用 Decimal(原始文本)；不要先转 float。文件不存在报 FileNotFoundError，乱码不能靠吞异常变成空清单。

我把文件改名再运行，先看解析后的路径，再改回正确文件名。w 会截断已有输出，r 只读；输入与输出必须不同。D02 再解释 with 如何调用对象协议。

列表可以反复看，迭代器会向前走

为什么学：商城运营可能要处理很多商品。我希望一条一条检查，而不是先把所有内容装进列表。可迭代对象能提供迭代器，例如列表；iter(x) 取得迭代器；next(it) 取得下一项；没有下一项时抛 StopIteration。

ids = ["P1001", "P1002"]
cursor = iter(ids)
print(next(cursor))  # P1001
print(next(cursor))  # P1002
try:
    print(next(cursor))
except StopIteration:
    print("这次读取结束")
print(list(cursor))  # []，并不会回到开头
print(list(ids))     # 原列表仍可再次取得迭代器

for 会自动取得迭代器并在 StopIteration 时结束，正常写业务通常不必手动捕获它。我手动运行一次，是为了知道为什么“第二次遍历没有结果”。

用 yield 把商品一条一条交给下一步

为什么学：先打印日志再处理同一个读取器，可能已经把商品消耗完了。生成器让处理流程按需求推进，也要求我清楚谁在消费数据。

函数体含 yield，调用后得到生成器。第一次 next 才执行到第一个 yield，暂停并保留局部状态；下一次继续向后执行。return 结束生成器；不要在生成器里手动 raise StopIteration。

def product_ids():
    print("准备第一件")
    yield "P1001"
    print("准备第二件")
    yield "P1002"
 
rows = product_ids()     # 还没有打印准备消息
print(next(rows))
print(list(rows))        # 只剩第二件
print(list(rows))        # []

生成器表达式 (row["name"] for row in rows) 也按需产生结果；列表推导式 [row["name"] for row in rows] 会立即消费并保存全部结果。两者都可能耗尽同一个 rows。

我现在补商品读取器：逐行检查编号、名称和 Decimal 单价，合格一行 yield 一行；错误必须带 CSV 行号。即使调用成功，坏行也可能在后面的 next/for 才暴露，异常捕获要包住实际遍历。

一批三条不代表最后两条可以丢掉

商城的批量处理器接受任意可迭代对象，每批至多 size 条。调用 batches(rows, 2) 处理五件商品，批大小应为 2、2、1；空输入为零批；批大小零和负数应拒绝。

我先在纸上推演缓冲列表什么时候清空，再补 batches。每次交出一批后建立新列表，不能交出同一列表又把它清空，否则已经交出去的结果也会改变。这复用 W01 的可变对象知识。

导出时使用 json.dumps 把字典变成 JSON 文本，Decimal 先转两位小数字符串。json.loads 做反向转换。今天用 JSON Lines（.jsonl）：一行一个 JSON 对象，不是一个大 JSON 数组，不能对整个多行文件直接 json.load。

import json
from decimal import Decimal
 
record = {"product_id": "P1001", "unit_price_yuan": format(Decimal("129.00"), ".2f")}
line = json.dumps(record, ensure_ascii=False)
print(line)
print(json.loads(line)["unit_price_yuan"])

读取、校验、分批和导出连起来时，只保留当前行和当前批；若再调用 list(rows)，仍会一次装入全部数据。生成器不自动解决数据库分页、重复商品、事务或持久任务问题。

本日约定完整消费生成器。提前 break 时不要假设文件立即关闭；D02 会用 contextlib.closing 给提前停止的读取器明确收口。导出遇坏行可能已有部分 JSONL，必须标记失败、修复原始数据后重新生成，不能汇报“全部导入成功”。

我的第一轮交付与数据库任务如何接上

从本人项目根运行：

python check_d01_python.py

首次应在 STUDENT_TODO 处失败。补完后，再用三件、五件、空 CSV、错误单价各验一次。独立变化：把批大小改为 3，解释为什么最后一批仍存在；先消费一条再导出，说明为什么少了一件。

接下来沿用同一批商品编号看关系表。今天数据库操作复用提供的建表与样本脚本，我主要补查询和约束反例；完整入库框架不要求从零编写；我使用下方提供的接入支架完成受限入库，再用 SQL 验证。

从已校验清单接到现有 MySQL 表

商品文件清单是输入准备，持久化还要真正写库。主样本保持已有商品合同：P1001 鼠标129.00、P1002 键盘299.00、P1003 保温杯89.00；不得给同一编号换含义。我先使用日包 data/new_products.csv 的 P2001/P2002 生成完整成功的 JSONL，人工核对数量和单价，再调用提供的 commerce_ops/mysql_catalog_import.py。这是连接与 SQL 支架，今天先解释参数绑定和表字段，D02 拆解事务退出。

该支架会向既有 products 与 inventory 表新增记录：分类“课程导入”、默认未上架、可用和预留库存均为 0；不会覆盖原商品。已有编号交给主键约束拒绝，一批写入失败会退出事务。对真实 MySQL 的回滚结论必须实测，不能用阅读代码替代。

在已按项目手册私下配置数据库连接、创建专用实验表后，从本人项目根执行：

# COMMERCE_DATABASE_URL 使用本人私有配置，不把带密码的值粘到记录里。
$env:COMMERCE_IMPORT_JSONL='data/new_products.review.jsonl'
python -m commerce_ops.mysql_catalog_import

支架只接受 student_ 或 codex_w02_test_ 开头的课程专用库。先通过 D01 自己完成的 write_jsonl 生成上述文件；不得直接拿任意部分输出入库。随后真实查询：

SELECT p.product_id, p.unit_price_yuan, p.is_active,
       i.available_qty, i.reserved_qty
FROM products p JOIN inventory i ON p.product_id=i.product_id
WHERE p.product_id IN ('P2001','P2002');

两件商品、准确单价、未上架和零库存都要对应。再次提交同一清单应该被拒绝，原记录保持不变；不能擅自删表来“让它通过”。没有真实库时先完成文件与 SQL 绑定检查，把入库、重复拒绝和回滚明确留待补验。

为什么不继续用一个 JSON 文件

JSON 与关系数据库

JSON（结构化数据交换格式）很适合表达一个完整文档。

W01 已用它保存和恢复订单。现在新增的是关系查询与数据库约束，不需要把上一周的金额规则重写一遍。

当五个对象频繁互相引用时，问题变了：

customer_id 是否真的存在；
order_id 是否唯一；
一张订单是否有多条明细；
product_id 能否指向不存在的商品；
多次写入怎样一起回滚；
两个请求怎样避免互相覆盖。

所以今天引入关系数据库。

MySQL 在系统里的位置

MySQL Server（服务端）负责：

保存表和索引；
执行 SQL（结构化查询语言）；
管理事务与并发；
返回结果或错误。

MySQL Client（客户端）是连接和操作服务器的工具。

Python Driver 把 Python 调用转换成数据库协议。

SQLAlchemy（Python数据库工具库）在 Driver 之上提供 SQL 构造、映射和 Session（会话）。

不要把这四层混成“数据库”一个词。

MySQL 第一次使用：安装、配置、连接、再写 SQL

如果你电脑还没有 MySQL，先完成 MySQL 8.4 安装与课程数据库验收手册。课堂不是从“假设数据库已经装好”开始，必须逐层留下：

Get-Service -Name 'MySQL*' -ErrorAction SilentlyContinue
where.exe mysql
mysql --version
Get-NetTCPConnection -State Listen -LocalPort 3306 -ErrorAction SilentlyContinue

随后使用你课程账号连接专用实验库。今天先在 MySQL 客户端执行 W02-D01 日包中的 sql/schema.sql 和 sql/seed.sql，再完成自己的 queries.sql。执行前核对当前数据库，不能把样本导入已有业务库。

第一次先读 SQL、运行 SQL，再认识 Python 怎样提交 SQL。后面的参数绑定片段用于解释调用方式；它依赖已建立的连接，不是复制到空文件就能运行的完整程序。SQLAlchemy（数据库工具库）与 Session（工作会话）的完整接入在明天展开。

安装器完成、服务存在、端口监听、SQL 能执行、Python 能连接是五层不同证据。密码不进入命令截图、代码或 Git；真实 MySQL 暂时失败时可以用 Fixture（固定测试数据或测试装置）继续学习事务机制，但真实数据库 Evidence（验证证据）保持 Pending（待验证）。

本周数据库基线

本周数据库实验使用：

MySQL 8.4 LTS 系列；
InnoDB（MySQL事务存储引擎）存储引擎；
utf8mb4 字符集；
人民币金额 DECIMAL(10,2)；
课程专用数据库与最小权限账号；
连接信息来自环境配置；
密码不进入 Git。

补丁版本和 Python 驱动按当天依赖清单核对，并实际验证连接。

未完成真实连接与读写验证时，先标记“待验证”，不要把安装截图当成数据库运行结果。

从0开始学SQL：[https://gaor4awyz1u.feishu.cn/docx/Gvdldy856ofvNoxAs05chAQenSd]

入场检查

在终端中保留原始输出：

SELECT VERSION();
SELECT @@default_storage_engine;
SELECT @@character_set_server;
SELECT @@transaction_isolation;

你应该能解释每条在检查什么。

不要在截图或作业里暴露密码。

如果个人 MySQL 暂时失败，按下面的顺序降级，不要直接换成另一种数据库：

先排查本机 MySQL 8.4 的服务、端口、账号和连接串；
本机当天无法恢复时，切换到课程提供的真实 MySQL 8.4 环境，继续完成 SQL、事务和回滚实验；
如果当天没有可用的真实 MySQL，先用虚构样本纸笔推演表关系与连接结果，登记故障；恢复后补做 SQL。明天的事务替身实验不能代替今天的数据库验证。

第三层只用于纸笔推演关系和观察已有测试的机制，不能证明你已经完成真实 MySQL 实验。真实环境证据继续记为 Pending，补验后才能通过对应验收。

SQLite 或其他存储不能替代本周的 MySQL 结论。

五个对象怎样变成表

五对象 ER 图

图中先看三条关系：

Customer 1:N Order；
Order 1:N OrderItem；
Product 1:N OrderItem。

Inventory 和 Product 在本周是一一对应。

OrderItem 同时关联 Order 和 Product。

它还保存下单时的商品名与价格快照。

为什么把客户资料复制到每条订单里，会增加维护风险？

同一客户会出现多份资料，修改容易不一致。客户用稳定编号标识，订单通过关联引用；交易时需要冻结的商品价格另存订单快照。

主键、外键和约束

表已经按对象拆开，新的问题随之出现：订单填了一个不存在的客户编号怎么办？主键帮助定位，外键帮助检查引用，约束把明确规则交给数据库共同保护。

主键 Primary Key，简称 PK：

唯一定位一行；
不能为空；
一张表只有一个主键定义。

外键 Foreign Key，简称 FK：

表达一行引用另一张表；
阻止无效引用；
让业务关系可由数据库检查。

约束不是装饰。

它们把一部分业务规则变成数据库不能轻易绕过的红线。

最小五表设计

products

product_id PK
name
category
unit_price_yuan DECIMAL(10,2)（十进制定点数，精度10位、小数2位）
is_active

customers

customer_id PK
display_name（虚构）

inventory

product_id PK + FK
available_qty
reserved_qty
updated_at

订单头与订单项

orders

order_id PK
customer_id FK
total_amount_yuan
order_status
payment_status
logistics_status
created_at

order_items

order_item_id PK
order_id FK
product_id FK
product_name_snapshot
unit_price_yuan
quantity
line_total_yuan

订单头汇总，订单项保存每一行事实。

为什么 OrderItem 要存价格快照

假设今天商品价格是 129.00 元。

客户下单后，明天运营把 Product 改成 139.00 元。

历史订单应该变成 139.00 元吗？

不应该。

所以 OrderItem 保存下单时单价和商品名。

查询历史订单时，不能拿 Product 当前价格重算。

人民币金额跨层映射

金额类型映射

从左到右看：

用户看到 129.00 元；
API 传 "129.00"；
Python 使用 Decimal("129.00")；
MySQL 使用 DECIMAL(10,2)。

任何一层偷换成 float（浮点数类型），都可能引入不可解释的小数误差。

金额仍然以“元”为业务单位。

Python 的 Decimal、数据库 DECIMAL(10,2) 和页面金额文字怎样衔接？

Python 用十进制金额，数据库用定点小数列保存，展示按两位小数格式化；DECIMAL(10,2) 共十位、其中两位小数。输入、范围和精度仍需校验，不能靠显示格式修正数据。

DECIMAL(10,2) 怎么读

DECIMAL(10,2)：

总共最多 10 位十进制数字；
其中 2 位在小数点后；
整数部分最多 8 位。

它适合本课程的人民币金额范围。

这不是所有支付系统的通用规格。

真实企业会结合金额上限、币种和会计规则决定精度。

DDL、DML、DQL

从数据问题出发选择 SQL、事务和索引工具

不要先背缩写。先看下半部分的三个故障：重复事实、半成品订单、查询变慢；再回到上半部分判断要用结构定义、数据读写、事务还是权限语句。本周 DCL 只认识边界，不扩展账号管理。

DDL：数据定义语言（Data Definition Language），定义结构，例如：

CREATE TABLE products (...);

DML：数据操纵语言（Data Manipulation Language），修改数据，例如：

INSERT INTO products (...) VALUES (...);
UPDATE inventory SET available_qty = ...;

DQL：数据查询语言（Data Query Language），查询数据，例如：

SELECT * FROM products;

今天只学项目需要的子集。

建表顺序为什么有要求

有外键依赖时，先建被引用的表。

一个可行顺序：

products
customers
inventory
orders
order_items

删除时通常反过来。

如果先建 order_items，却引用尚不存在的 products，MySQL 会因外键目标表不存在而拒绝这条 DDL。

先读错误，不要关闭外键检查来绕过。

JOIN：把分开的表重新组合

拆表减少了重复资料，但客服要看完整订单时又需要把信息合起来。JOIN（表连接）按关联键组合记录；要先确定哪些客户或订单不能被遗漏，再选连接方式。

订单详情需要：

订单头；
所有明细；
商品快照。

SELECT
    o.order_id,
    o.customer_id,
    o.total_amount_yuan,
    oi.product_id,
    oi.product_name_snapshot,
    oi.unit_price_yuan,
    oi.quantity,
    oi.line_total_yuan
FROM orders AS o
JOIN order_items AS oi
  ON oi.order_id = o.order_id
WHERE o.order_id = :order_id;

:order_id 表示绑定参数，不是字符串拼接。

INNER JOIN 和 LEFT JOIN

INNER JOIN（表连接）只保留两侧都匹配的行。

LEFT JOIN 保留左表全部行，右侧没有匹配时用 NULL。

订单详情合同要求至少一条 OrderItem。

正常业务查询可以使用 INNER JOIN。

排查“空订单头”脏数据时，LEFT JOIN 更容易暴露异常。

选择 JOIN 要从问题出发。

统计所有客户的订单数时，没有下单的客户应该消失还是显示零？

若需求是所有客户，应保留并显示零。以客户为左表连接订单，计数订单主键而非空连接行；统计范围先由业务确定。

零订单客户为什么在 INNER JOIN 中消失？

INNER JOIN 只保留两侧匹配；从客户表 LEFT JOIN 订单可以保留未匹配客户。计数用订单主键 COUNT(o.order_id)，否则 COUNT(*) 会把那条空连接行算作一个订单。

参数化 SQL 为什么是底线

错误示例：

sql = f"SELECT * FROM orders WHERE order_id = '{order_id}'"

输入被直接拼进 SQL。

正确方向：

session.execute(
    text("SELECT * FROM orders WHERE order_id = :order_id"),
    {"order_id": order_id},
)

参数由 Driver 绑定。

本周不做安全攻防，但不能养成字符串拼 SQL 的习惯。

把输入拼进 SQL 与参数绑定有什么区别？

拼字符串可能让输入变成 SQL 语法；参数绑定把值交给驱动处理。表名和列名不是普通值占位符，动态标识符需要固定白名单。

学生自己动手，AI 只能改你允许改的地方

我先保存当前可运行版本，再完成“查询没有订单的客户并解释 LEFT JOIN”。让 AI 一次只修改负责该行为的文件；修改前预测正常与失败结果，检查差异，再运行回归。每人需要解释一处接受或拒绝的建议。

错误与边界怎样验收

今天重点检查：字符串拼 SQL、外键顺序错误、金额精度变化。保留输入、预期、实际输出和修复后的结果，不能只贴全绿截图。未验证的外部依赖如实记为待验证。

留给下一阶段

今天先证明关系、金额与查询结果正确：尤其是零订单客户仍在统计中，未知关联会被拒绝。明天把多条 SQL 组合成一次下单操作，解决“第一步成功、第二步失败”留下半张订单的问题。

众元AI学习空间

课程目录

02 课堂课件 · D01