建表与增删改（DDL/DML）

本章目标：能写带主键、约束和注释的建表语句，能做插入、更新、删除，理解事务和幂等写入。 前置知识：SQL 基础、聚合与分组。 预计用时：60 分钟。

1. 为什么需要它

前几章都在“查”：SELECT 从已有的表里取数。但数据开发实习的第一周，你更可能接到这些任务：

• 给新指标建一张结果表，字段、类型、注释都要写清楚。
• 把清洗后的数据写进 DWD 表。
• 修一条写错的数据，或者重跑某天的分区。
• 同事问：“这个任务重跑会不会把数据写重？”

这些都不是 SELECT 能解决的，需要 DDL（建表、改表） 和 DML（增删改数据）。面试也会问 DELETE、TRUNCATE、DROP 的区别，以及事务是什么。

边界：数据开发以查询和批量写入为主，不会像后端那样天天写 UPDATE。但建表、INSERT SELECT、分区覆盖写是每天都要用的，必须会。

本章继续用玩具库 project/sql_demo.duckdb，只新增一张练习用的归档表 demo_order_archive，不涉及电商项目数据。

2. 核心概念

2.1 表结构由字段、类型和约束组成

建表就是声明“这张表有哪些列、每列存什么、哪些值不允许为空”。

金额不要用 FLOAT。浮点数存在精度误差，0.1 + 0.2 不一定等于 0.3；对账时差几分钱，排查起来很痛苦。

2.2 常用约束

主键选择要谨慎：订单明细表的主键通常是 (order_id, item_id) 组合，而不是单独一个 order_id，因为一个订单有多条明细。

2.3 建表语句

MySQL 写法（实习最常遇到）：

CREATE TABLE demo_order_archive (
    order_id      VARCHAR(32)  NOT NULL COMMENT '订单ID',
    customer_id   VARCHAR(32)  DEFAULT NULL COMMENT '客户ID，可为空',
    status        VARCHAR(20)  NOT NULL COMMENT '订单状态',
    order_date    DATE         NOT NULL COMMENT '下单日期',
    total_amount  DECIMAL(12, 2) NOT NULL DEFAULT 0.00 COMMENT '订单金额',
    created_at    TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '写入时间',
    PRIMARY KEY (order_id),
    KEY idx_customer (customer_id),
    KEY idx_date (order_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单归档表';

Hive 写法（离线数仓）：

CREATE EXTERNAL TABLE IF NOT EXISTS ods_demo_orders (
    order_id      STRING COMMENT '订单ID',
    customer_id   STRING COMMENT '客户ID',
    status        STRING COMMENT '订单状态',
    created_at    STRING COMMENT '下单时间，原始字符串',
    paid_at       STRING COMMENT '支付时间',
    delivered_at  STRING COMMENT '签收时间'
)
COMMENT 'demo 订单原始表'
PARTITIONED BY (dt STRING COMMENT '分区日期')
STORED AS PARQUET;

两处关键差异：

• Hive 3.x 可以声明 PRIMARY KEY / UNIQUE 约束，但只作为元数据记录，默认不强制校验；重复数据仍靠写入逻辑保证。
• Hive 的 PARTITIONED BY 把分区字段放在表字段之外，查询时作为目录过滤条件。

2.4 插入数据

单行插入：

INSERT INTO demo_order_archive
    (order_id, customer_id, status, order_date, total_amount)
VALUES
    ('A1001', 'C001', 'paid', DATE '2024-03-01', 499.00);

多行插入：

INSERT INTO demo_order_archive
    (order_id, customer_id, status, order_date, total_amount)
VALUES
    ('A1002', 'C002', 'paid', DATE '2024-03-01', 258.00),
    ('A1003', NULL,   'paid', DATE '2024-03-02', 59.70);

从查询结果插入（数仓最常用）：

INSERT INTO demo_order_archive
    (order_id, customer_id, status, order_date, total_amount)
SELECT o.order_id,
       CAST(o.customer_id AS VARCHAR),
       o.status,
       CAST(o.created_at AS DATE),
       SUM(i.quantity * i.unit_price) AS total_amount
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.created_at >= TIMESTAMP '2024-03-01 00:00:00'
  AND o.created_at <  TIMESTAMP '2024-04-01 00:00:00'
GROUP BY o.order_id, o.customer_id, o.status, o.created_at;

INSERT INTO ... SELECT 是数据开发的主武器：清洗、汇总、写结果表都靠它。

2.5 更新与删除

-- 先查：确认要改的是哪几行
SELECT order_id, total_amount
FROM demo_order_archive
WHERE order_id IN ('A1002', 'A1003');

-- 再改：只改确认过的行
UPDATE demo_order_archive
SET total_amount = total_amount + 10
WHERE order_id IN ('A1002', 'A1003');

-- 删除同样先查再删
DELETE FROM demo_order_archive
WHERE order_date < DATE '2024-03-01';

铁律：UPDATE 和 DELETE 必须先写 SELECT 验证范围。 把 SELECT * 换成 DELETE 之前，先看返回多少行；没有 WHERE 的 DELETE 会清空整张表。

三个容易混的语句：

2.6 事务：一组操作要么全成功，要么全失败

转账是经典例子：A 扣钱和 B 加钱必须同时成功，否则钱会凭空消失。

BEGIN;
UPDATE demo_account SET balance = balance - 100 WHERE user_id = 'A';
UPDATE demo_account SET balance = balance + 100 WHERE user_id = 'B';
COMMIT;

如果中间出错，执行 ROLLBACK，两条更新都撤销。

事务的四个特性（ACID）：

隔离级别是面试高频，先记住问题本身：

MySQL InnoDB 默认是 REPEATABLE READ。数据开发不需要背实现细节，但要能说清“脏读是读到了别人未提交的数据”。

Hive 的边界：Hive 面向批量写入，默认不支持行级 UPDATE/DELETE，也不支持完整事务。离线数仓修数据的方式通常是重跑分区、覆盖写，而不是改某一行。这个差异是面试常问点。

2.7 幂等写入：重跑一次和重跑十次结果一样

调度任务失败后要补数，必须保证重跑安全。

错误写法：

-- 每跑一次就多一份，指标翻倍
INSERT INTO demo_daily_sales
SELECT CAST(created_at AS DATE) AS dt, SUM(i.quantity * i.unit_price)
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
GROUP BY CAST(created_at AS DATE);

正确写法一：覆盖分区

INSERT OVERWRITE TABLE demo_daily_sales PARTITION (dt = '2024-03-01')
SELECT COUNT(DISTINCT order_id) AS order_cnt,
       SUM(quantity * unit_price) AS gmv
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid' AND CAST(created_at AS DATE) = DATE '2024-03-01';

正确写法二：先删后插（同一批次）

DELETE FROM demo_daily_sales WHERE dt = DATE '2024-03-01';
INSERT INTO demo_daily_sales
SELECT CAST(o.created_at AS DATE),
       COUNT(DISTINCT o.order_id),
       SUM(i.quantity * i.unit_price)
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid' AND CAST(o.created_at AS DATE) = DATE '2024-03-01'
GROUP BY CAST(o.created_at AS DATE);

正确写法三：按唯一键 upsert（MySQL）

INSERT INTO demo_daily_sales (dt, order_cnt, gmv)
VALUES ('2024-03-01', 4, 2115.70)
ON DUPLICATE KEY UPDATE
    order_cnt = VALUES(order_cnt),
    gmv = VALUES(gmv);

幂等的判断标准：同一数据日期，跑 1 次和跑 10 次，结果完全一致。 依赖 now()、随机数、当前日期的逻辑都会破坏幂等。

3. 动手示例

以下示例在玩具库里执行。DuckDB 的建表语法与 MySQL 接近，但注释和自增写法不同；写数据时要连接可写模式（去掉 read_only=True）：

python - <<'PY'
import duckdb
con = duckdb.connect('project/sql_demo.duckdb')
con.execute("SELECT 1")
print("connected")
PY

3.1 建一张归档表

CREATE TABLE demo_order_archive (
    order_id      VARCHAR PRIMARY KEY,
    customer_id   VARCHAR,
    status        VARCHAR NOT NULL,
    order_date    DATE NOT NULL,
    total_amount  DECIMAL(12, 2) DEFAULT 0
);

查看结构：

DESCRIBE demo_order_archive;

3.2 从订单表写入归档表

只取 2024 年 3 月的有效订单，每单汇总金额：

INSERT INTO demo_order_archive
    (order_id, customer_id, status, order_date, total_amount)
SELECT o.order_id,
       CAST(o.customer_id AS VARCHAR),
       o.status,
       CAST(o.created_at AS DATE),
       SUM(i.quantity * i.unit_price) AS total_amount
FROM demo_orders o
JOIN demo_order_items i ON i.order_id = o.order_id
WHERE o.status = 'paid'
  AND o.created_at >= TIMESTAMP '2024-03-01 00:00:00'
  AND o.created_at <  TIMESTAMP '2024-04-01 00:00:00'
GROUP BY o.order_id, o.customer_id, o.status, o.created_at;

SELECT COUNT(*) AS 归档行数, ROUND(SUM(total_amount), 2) AS 归档金额
FROM demo_order_archive;

结果应为 6 行、2,393.40 元，和 3 月有效订单的销售额对得上。

3.3 更新前先查

-- 金额不合理的行：预期 0 行
SELECT *
FROM demo_order_archive
WHERE total_amount <= 0
LIMIT 10;

如果上面的查询返回了行，先确认范围，再执行更新。例如把某个订单的金额修正为明细重算值：

UPDATE demo_order_archive
SET total_amount = 499.00
WHERE order_id = 'A1001';

真实业务里即使遇到空客户也不能随便填一个默认 ID，否则会把不同订单错误归到同一个人；正确做法是保留空值，用户维度分析时排除。

3.4 事务回滚

BEGIN;
DELETE FROM demo_order_archive WHERE order_date = DATE '2024-03-05';
SELECT COUNT(*) AS 事务内行数 FROM demo_order_archive;
ROLLBACK;
SELECT COUNT(*) AS 回滚后行数 FROM demo_order_archive;

3 月 5 日有 1 个有效订单，所以事务内行数会比原来少 1，回滚后恢复。

两次行数应该相同，说明删除被回滚了。把 ROLLBACK 换成 COMMIT，删除才会真正生效。

3.5 用 JOIN 重算金额

-- 先看差异
SELECT a.order_id, a.total_amount,
       ROUND(SUM(i.quantity * i.unit_price), 2) AS 重算金额
FROM demo_order_archive a
JOIN demo_order_items i ON a.order_id = i.order_id
GROUP BY a.order_id, a.total_amount
HAVING ABS(a.total_amount - SUM(i.quantity * i.unit_price)) > 0.01
LIMIT 10;

差异为 0 行说明写入逻辑正确。生产上重算历史数据，优先“重跑分区覆盖”，而不是逐行 UPDATE。

4. 常见坑

5. 在线练习

DDL 没有完全等价的在线题，改为“在线 DML + 本地建表任务”。

本地任务：

• 为 demo_orders 写一份 Hive 分区 DDL，dt 放在 PARTITIONED BY 里。
• 用 CREATE TABLE ... AS SELECT 从 demo_orders 建一张按状态汇总的表。
• 用 INSERT OVERWRITE 重写一个分区，并说明为什么不用 INSERT INTO。

6. 面试延伸

面试题：DELETE、TRUNCATE、DROP 有什么区别？

是什么：DELETE 按条件删除行，可以回滚（事务内），会触发行级日志；TRUNCATE 清空整张表，不能带条件，通常隐式提交、不能回滚；DROP 删除表结构和数据，表本身消失。

解决什么问题：修少量数据用 DELETE；快速重置整表用 TRUNCATE；下线废弃表用 DROP。生产环境执行前都要确认范围、备份或走审批。

我的实操边界：我在本地 DuckDB 用 DELETE 和事务做过数据修正与回滚，用过 TRUNCATE 重置测试表；没有在生产库执行过 DROP。

面试题：事务的 ACID 和隔离级别是什么？

是什么：ACID 是原子性、一致性、隔离性、持久性。隔离级别从低到高是读未提交、读已提交、可重复读、串行化，分别解决脏读、不可重复读、幻读。

解决什么问题：并发写同一批数据时，隔离级别决定一个事务能否看到另一个事务未提交的修改。MySQL InnoDB 默认可重复读，用 MVCC 和间隙锁减少并发冲突。

我的实操边界：我理解四个隔离级别和三类并发问题，在本地用 BEGIN/COMMIT/ROLLBACK 验证过原子性；没有在生产环境调过隔离级别或排查过死锁。

面试题：数仓任务怎么保证重跑幂等？

是什么：同一数据日期重复执行，结果不变。常用做法是分区覆盖写、先删后插、按唯一键 upsert，并固定输入数据区间。

解决什么问题：调度失败、上游延迟、逻辑修复都需要重跑；如果任务用 INSERT INTO 追加，重跑会让指标翻倍。幂等保证补数不会污染结果。

我的实操边界：我在本地用全量重跑和固定数据区间保证可复现，理解 Hive INSERT OVERWRITE 的分区覆盖；没有在生产调度系统里做过跨天补数。

7. 本章验收

• 能写出带主键、NOT NULL、DEFAULT 和注释的建表语句。
• 能说清金额为什么用 DECIMAL 而不是 FLOAT。
• 能写出 INSERT INTO ... SELECT，并知道它和 INSERT VALUES 的区别。
• 能说出 DELETE、TRUNCATE、DROP 的区别和适用场景。
• 能用 BEGIN/COMMIT/ROLLBACK 演示一次事务回滚。
• 能写出至少两种幂等写入方案，并解释为什么 INSERT INTO 重跑会翻倍。
• 能说清 MySQL 和 Hive 在更新、事务上的差异。

上一章：聚合与分组。下一章：多表查询与 JOIN。