D11 Postgres / SQL

标准（必讲）。公司的"钱账本"全在数据库里（用户余额、流水、定价表）。计费系统的正确性=SQL 的正确性。

教学目标（学完能做什么）

会写基础 SQL：增删改查、WHERE、JOIN、GROUP BY、ORDER BY
理解表设计三要素：主键、外键、索引
知道事务（transaction）与 ACID，能说清"扣费为什么必须用事务"
能用 psql / 驱动连数据库做只读查询，能给 D10 的 API 接上数据库

前置要求

D10（FastAPI）、D9（Python）

本模块在业务中的位置

公司每个业务都有 Postgres（见 CLAUDE.md DB index：core_api、newapi、push_gateway、account_vault 等）。计费 = 数据库里扣余额 + 记流水，这一步错一分钱都是事故。D15 计费、第 4 周项目 B（计费引擎）全靠本模块。

内容分段

1. 为什么需要数据库

内存 dict（D10 练习用）进程一重启就丢；数据库持久化 + 并发安全 + 查询能力。
公司统一用 PostgreSQL。

2. SQL 增删改查（CRUD）

-- 建表
CREATE TABLE users (
  id      BIGSERIAL PRIMARY KEY,      -- 自增主键
  name    TEXT NOT NULL,
  balance NUMERIC(20, 8) NOT NULL DEFAULT 0   -- 金额/余额，用 NUMERIC 别用 float！
);

-- 插入
INSERT INTO users (name, balance) VALUES ('alice', 100.0);

-- 查询
SELECT id, name, balance FROM users WHERE balance > 0 ORDER BY balance DESC;

-- 更新
UPDATE users SET balance = balance - 10 WHERE id = 1;

-- 删除
DELETE FROM users WHERE id = 1;

金额永远用 NUMERIC，绝不用 float（二进制浮点会有 0.1+0.2≠0.3 问题，钱账必须精确）。

3. 联表与聚合（业务常用）

-- JOIN：订单关联用户
SELECT o.id, u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'paid';

-- GROUP BY：按模型汇总消耗
SELECT model, SUM(tokens) AS total_tokens, COUNT(*) AS calls
FROM usage_log
GROUP BY model
ORDER BY total_tokens DESC;

记忆：JOIN 是把两张表"接"起来，GROUP BY 是"分堆"后统计。

4. 索引

没有索引 = 全表扫（数据多了巨慢）；有索引 = 走目录。
高频 WHERE/JOIN 的列要加索引：

CREATE INDEX idx_usage_user ON usage_log(user_id);
CREATE INDEX idx_usage_model ON usage_log(model);

不是越多越好：索引占空间、拖慢写；只给高频查询列加。

5. 事务与 ACID（计费的生命线）

BEGIN;
UPDATE users SET balance = balance - 10 WHERE id = 1;   -- 扣费
INSERT INTO bills (user_id, amount, type) VALUES (1, 10, 'chat');  -- 记流水
COMMIT;   -- 要么都成功
-- 任何一步失败就 ROLLBACK，余额不会扣了流水没记

ACID：原子性(Atomicity)/一致性(Consistency)/隔离性(Isolation)/持久性(Durability)。理解第一个最重要：一坨操作要么全成、要么全不成。
为什么扣费必须用事务：否则"余额扣了但流水没记"= 对不上账。

6. 从 FastAPI 连数据库（给 D10 接库）

pip install asyncpg            # 或 sqlalchemy 等 ORM

import asyncpg

async def get_balance(uid: int):
    conn = await asyncpg.connect("postgresql://user:pass@localhost:5432/db")
    try:
        row = await conn.fetchrow("SELECT balance FROM users WHERE id=$1", uid)
        return row["balance"] if row else None
    finally:
        await conn.close()

生产不把密码写代码里，走环境变量/Secret（D8 提过）。

7. psql 只读连接（公司练习口径）

psql "postgresql://用户名:密码@localhost:5432/库名"   # 本地练习
# 生产库密码在 Secret 里，新人只读需负责人授权；只跑 SELECT

讲解节奏建议（约 90 分钟）

时段	内容
09:00-09:10	引入：公司的钱账本全在 Postgres，计费=SQ L 正确
09:10-09:40	CRUD + NUMERIC（强调金额不用 float）
09:40-10:05	JOIN / GROUP BY
10:05-10:25	索引
10:25-10:50	事务与 ACID（计费生命线，多花时间）
10:50-11:30	学员实操：本地库建表/查询 + 给 API 接库（对应作业）
11:30-11:50	常见误区 + 小结
11:50-12:00	布置作业

常见误区汇总

误区	正确理解
金额用 float	用 NUMERIC；float 精度问题会算错钱
全表扫没什么	数据量大就慢到不可用，加索引
索引越多越好	占空间拖写，只加高频查询列
扣费不用事务	可能扣了钱流水没记，对不上账=事故
DELETE 了数据还能找回	默认不可逆；删前先 SELECT 确认（Class A）
密码写代码里	走环境变量/Secret，绝不硬编码

D11 Postgres / SQL

On this page