SQL 基础与表设计

Phase 04 — Database 涵盖：SQL · Table · Primary Key · Foreign Key · Index · JOIN · Transaction

1. 学习目标

完成本知识点后，你应该能够：

理解关系型数据库的核心概念：表、行、列、主键、外键
编写基础 SQL：SELECT、INSERT、UPDATE、DELETE
使用 JOIN 查询关联表数据
理解索引的作用与适用场景
使用事务保证多步操作的原子性
为数字孪生场景设计设备与遥测数据的表结构

2. 为什么需要

数字孪生后端需要持久化存储：设备档案、历史轨迹、告警记录、工单状态……这些数据不能只在内存里——服务重启就会丢失。

关系型数据库（以 PostgreSQL 为代表）用 SQL 作为标准查询语言，通过表组织结构化数据，通过主键 / 外键表达实体关系，通过事务保证数据一致性。这是后端开发中最基础、最通用的数据持久化方案。

3. 核心概念

3.1 SQL（Structured Query Language）

操作关系型数据库的标准语言，分为四类：

类型	关键字	作用
DDL	`CREATE` / `ALTER` / `DROP`	定义表结构
DML	`SELECT` / `INSERT` / `UPDATE` / `DELETE`	增删改查
DCL	`GRANT` / `REVOKE`	权限管理
TCL	`BEGIN` / `COMMIT` / `ROLLBACK`	事务控制

3.2 Table（表）

表是数据的二维结构：每行（row） 是一条记录，每列（column） 是一个字段。

sql

CREATE TABLE devices (
    id          VARCHAR(32) PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    device_type VARCHAR(50)  NOT NULL,
    status      VARCHAR(20)  DEFAULT 'idle',
    created_at  TIMESTAMPTZ  DEFAULT NOW()
);

3.3 Primary Key（主键）

唯一标识一行记录的列（或列组合）：

不能为 NULL
值必须唯一
每张表通常有一个主键

sql

-- 单字段主键
id VARCHAR(32) PRIMARY KEY

-- 自增整数主键（PostgreSQL 用 SERIAL 或 GENERATED）
id SERIAL PRIMARY KEY

3.4 Foreign Key（外键）

引用另一张表主键的列，建立表间关联：

sql

CREATE TABLE telemetry (
    id         BIGSERIAL PRIMARY KEY,
    device_id  VARCHAR(32) NOT NULL REFERENCES devices(id),
    x          DOUBLE PRECISION NOT NULL,
    y          DOUBLE PRECISION NOT NULL,
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

device_id 必须是 devices.id 中已存在的值，否则插入失败——这保证了引用完整性。

3.5 Index（索引）

加速查询的数据结构，类似书的目录：

sql

-- 为高频查询字段建索引
CREATE INDEX idx_telemetry_device_time
    ON telemetry (device_id, recorded_at DESC);

场景	是否建索引
WHERE / JOIN 常用列	是
唯一约束列	自动有索引
小表、低频写入	通常不需要
频繁 UPDATE 的列	谨慎，索引有维护成本

3.6 JOIN（表连接）

将多张表的数据合并查询：

JOIN 类型	说明
`INNER JOIN`	只返回两表都匹配的行
`LEFT JOIN`	左表全保留，右表无匹配则为 NULL
`RIGHT JOIN`	右表全保留
`FULL JOIN`	两表全保留

sql

SELECT d.id, d.name, t.x, t.y, t.recorded_at
FROM devices d
INNER JOIN telemetry t ON d.id = t.device_id
WHERE d.status = 'moving'
ORDER BY t.recorded_at DESC
LIMIT 10;

3.7 Transaction（事务）

一组 SQL 操作要么全部成功，要么全部回滚：

sql

BEGIN;

UPDATE devices SET status = 'maintenance' WHERE id = 'AGV-001';
INSERT INTO maintenance_logs (device_id, reason) VALUES ('AGV-001', 'scheduled check');

COMMIT;   -- 全部生效
-- ROLLBACK;  -- 全部撤销

事务遵循 ACID：

属性	含义
Atomicity	原子性，全成功或全失败
Consistency	一致性，约束始终满足
Isolation	隔离性，并发事务互不干扰
Durability	持久性，提交后数据不丢

4. 基础语法

4.1 数字孪生表结构设计

sql

-- 设备表
CREATE TABLE devices (
    id          VARCHAR(32) PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    device_type VARCHAR(50)  NOT NULL,  -- agv, sensor, robot_arm
    status      VARCHAR(20)  DEFAULT 'idle',
    created_at  TIMESTAMPTZ  DEFAULT NOW()
);

-- 遥测数据表（外键关联设备）
CREATE TABLE telemetry (
    id          BIGSERIAL PRIMARY KEY,
    device_id   VARCHAR(32) NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
    x           DOUBLE PRECISION NOT NULL,
    y           DOUBLE PRECISION NOT NULL,
    speed       DOUBLE PRECISION DEFAULT 0,
    battery     INT CHECK (battery >= 0 AND battery <= 100),
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_telemetry_device_time ON telemetry (device_id, recorded_at DESC);

4.2 CRUD 操作

sql

-- 插入
INSERT INTO devices (id, name, device_type, status)
VALUES ('AGV-001', 'Alpha AGV', 'agv', 'idle');

INSERT INTO telemetry (device_id, x, y, speed, battery)
VALUES ('AGV-001', 12.5, 3.8, 1.2, 85);

-- 查询
SELECT * FROM devices WHERE device_type = 'agv';
SELECT id, name, status FROM devices WHERE status = 'moving';

-- 更新
UPDATE devices SET status = 'moving' WHERE id = 'AGV-001';

-- 删除
DELETE FROM telemetry WHERE recorded_at < NOW() - INTERVAL '30 days';

4.3 JOIN 查询

sql

-- 查询每台 AGV 的最新位置
SELECT DISTINCT ON (d.id)
    d.id, d.name, d.status, t.x, t.y, t.battery, t.recorded_at
FROM devices d
LEFT JOIN telemetry t ON d.id = t.device_id
WHERE d.device_type = 'agv'
ORDER BY d.id, t.recorded_at DESC;

4.4 事务示例

sql

BEGIN;

-- 设备进入维护状态，同时记录日志
UPDATE devices SET status = 'maintenance' WHERE id = 'AGV-002';

INSERT INTO maintenance_logs (device_id, reason, started_at)
VALUES ('AGV-002', 'battery replacement', NOW());

-- 若任一步失败，执行 ROLLBACK
COMMIT;

5. 代码解析

sql

device_id VARCHAR(32) NOT NULL REFERENCES devices(id) ON DELETE CASCADE

NOT NULL：该列不能为空
REFERENCES devices(id)：外键，值必须存在于 devices.id
ON DELETE CASCADE：删除设备时，关联的遥测记录一并删除

sql

CREATE INDEX idx_telemetry_device_time ON telemetry (device_id, recorded_at DESC);

复合索引 (device_id, recorded_at DESC) 适合「按设备查最近 N 条遥测」这类查询，PostgreSQL 可以利用索引避免全表扫描。

sql

SELECT DISTINCT ON (d.id) ...
ORDER BY d.id, t.recorded_at DESC;

PostgreSQL 特有的 DISTINCT ON：每组（这里是每个 d.id）只取排序后的第一行，即最新一条遥测。

6. JavaScript / TypeScript 对比

概念	SQL / 关系型数据库	JavaScript / 前端
数据存储	持久化表，服务端	内存对象、localStorage（有限）
查询语言	声明式 SQL	命令式 filter/map/reduce
关联数据	JOIN 多表	手动嵌套对象或多次请求
事务	原生支持 ACID	无（IndexedDB 有部分支持）
Schema	强类型，先定义表结构	动态，对象随意增删字段
并发安全	数据库引擎处理	单线程，通常不涉及

关键差异：

SQL 是声明式的——你描述「要什么数据」，数据库决定怎么取
关系型数据库强调范式和约束，数据完整性由数据库保证
前端 Array.filter() 类比 WHERE，但 SQL 在服务端处理百万行更高效

7. 常见错误

错误 1：忘记主键

sql

-- ❌ 无唯一标识，难以更新/删除特定行
CREATE TABLE devices (name VARCHAR(100));

-- ✅ 每表应有主键
CREATE TABLE devices (id VARCHAR(32) PRIMARY KEY, name VARCHAR(100));

错误 2：外键引用不存在的记录

sql

-- ❌ AGV-999 不存在于 devices 表
INSERT INTO telemetry (device_id, x, y) VALUES ('AGV-999', 1.0, 2.0);
-- ERROR: insert or update on table "telemetry" violates foreign key constraint

错误 3：SELECT * 在生产环境滥用

sql

-- ❌ 返回所有列，含不需要的大字段
SELECT * FROM telemetry WHERE device_id = 'AGV-001';

-- ✅ 只取需要的列
SELECT x, y, speed, recorded_at FROM telemetry WHERE device_id = 'AGV-001';

错误 4：无索引的大表查询

sql

-- ❌ 百万行全表扫描
SELECT * FROM telemetry WHERE device_id = 'AGV-001' ORDER BY recorded_at DESC;

-- ✅ 在 (device_id, recorded_at) 上建索引

错误 5：事务未正确处理失败

sql

BEGIN;
UPDATE devices SET status = 'error' WHERE id = 'AGV-001';
-- 中间某步失败但未 ROLLBACK，可能导致部分提交
COMMIT;

在 Go 中应使用 tx.Rollback() 配合 defer 处理（见 Phase 04 下一文档）。

8. 实际应用

数字孪生 — 设备数据持久化

Three.js 前端展示实时 AGV 位置，后端需要：

devices 表：设备档案（ID、类型、状态）
telemetry 表：历史轨迹（坐标、速度、电量、时间戳）
alerts 表：告警记录（外键关联设备）

典型查询场景：

场景	SQL 模式
设备列表页	`SELECT * FROM devices WHERE device_type = 'agv'`
实时位置（最新一条）	`DISTINCT ON` + `ORDER BY recorded_at DESC`
历史轨迹回放	`WHERE device_id = ? AND recorded_at BETWEEN ? AND ?`
低电量告警统计	`JOIN` devices + 子查询过滤 `battery < 20`

数据流：

AGV 上报 → Go Handler → INSERT telemetry → PostgreSQL 持久化
                                    ↓
                          Three.js 查询历史 / WebSocket 推送实时

9. 深入理解

9.1 范式简述

范式	要求	数字孪生示例
1NF	列不可再分	坐标拆成 `x`、`y` 两列，而非 `"12.5,3.8"` 字符串
2NF	非主键列完全依赖主键	遥测表的 `speed` 依赖 `telemetry.id`，而非部分依赖
3NF	消除传递依赖	设备类型名不冗余存储在遥测表，通过 `device_id` 关联

实际项目中不必追求最高范式，适度冗余换查询性能是常见做法。

9.2 索引类型（PostgreSQL）

B-tree（默认）：等值、范围查询
Hash：仅等值查询
GIN / GiST：全文搜索、地理数据

9.3 隔离级别

PostgreSQL 默认 READ COMMITTED。高并发场景需了解：

级别	脏读	不可重复读	幻读
READ COMMITTED	否	是	是
REPEATABLE READ	否	否	否（PG 实现）
SERIALIZABLE	否	否	否

9.4 JOIN vs 应用层组装

数据量小、关联简单：JOIN 一次取出，减少往返
微服务拆分后：可能无法跨库 JOIN，改在应用层组装

10. 练习

请独立完成，不要查看答案。完成后说「检查答案」并提交你的 SQL。

Level 1 — 基础

练习 1.1：编写 CREATE TABLE 语句，创建 warehouses 表，包含 id（主键）、name、width、height。

练习 1.2：向 devices 表插入 3 台 AGV 记录（自行定义 ID 和名称）。

练习 1.3：编写 SELECT 查询所有 status = 'idle' 的设备。

练习 1.4：编写 UPDATE 将 AGV-001 的状态改为 'moving'。

Level 2 — 应用

练习 2.1：创建 alerts 表，包含 id（自增主键）、device_id（外键引用 devices）、level（warning/critical）、message、created_at。

练习 2.2：编写 INNER JOIN 查询，返回告警及其对应设备名称。

练习 2.3：为 telemetry 表添加索引，优化「按设备 ID 查最近数据」的查询。

练习 2.4：用事务实现：设备状态改为 error，同时插入一条 critical 级别告警。

Level 3 — 综合

练习 3.1：设计并创建完整的数字孪生 schema：warehouses、devices（含 warehouse_id 外键）、telemetry、alerts 四表。

练习 3.2：编写查询：返回每个仓库中 battery < 20 的 AGV 列表（含仓库名、设备名、最新电量）。

练习 3.3：编写查询：统计每台设备过去 24 小时的遥测记录数，按记录数降序排列。

Level 4 — 项目实践

练习 4.1：在本地 PostgreSQL 中执行完整 schema 搭建，插入至少 5 台设备、20 条遥测、3 条告警，编写 5 个业务查询（设备列表、最新位置、历史轨迹、低电量设备、告警列表）并验证结果。

11. 学习检查

完成练习后，确认你能回答：

主键和外键分别解决什么问题？
INNER JOIN 和 LEFT JOIN 有什么区别？
什么情况下应该建索引？什么情况下不该建？
事务的 ACID 四个属性分别是什么意思？
为什么数字孪生场景需要 telemetry 独立表而非把所有字段放 devices？
ON DELETE CASCADE 的行为是什么？

12. 下一步

已完成	下一知识点	关系
SQL, Table, PK, FK, Index, JOIN, Transaction	PostgreSQL	在真实数据库中执行 SQL
	database/sql	用 Go 代码操作数据库
	Connection Pool	生产环境连接管理

建议顺序：先在 PostgreSQL 中手动执行本节的 SQL，再学习 Go 的 database/sql 包进行程序化访问。

前置知识提醒

已完成 Phase 03（Go Web / REST API），能编写 Handler 处理 HTTP 请求
本地或 Docker 中已安装 PostgreSQL（下一文档会详细说明）
可使用 psql 或 pgAdmin 执行 SQL

学习导航

上一篇：Gin、JWT、API Design · 对应练习 · 下一篇：PostgreSQL 与 database/sql

SQL 基础与表设计 ​

1. 学习目标 ​

2. 为什么需要 ​

3. 核心概念 ​

3.1 SQL（Structured Query Language） ​

3.2 Table（表） ​

3.3 Primary Key（主键） ​

3.4 Foreign Key（外键） ​

3.5 Index（索引） ​

3.6 JOIN（表连接） ​

3.7 Transaction（事务） ​

4. 基础语法 ​

4.1 数字孪生表结构设计 ​

4.2 CRUD 操作 ​

4.3 JOIN 查询 ​

4.4 事务示例 ​

5. 代码解析 ​

6. JavaScript / TypeScript 对比 ​

7. 常见错误 ​

错误 1：忘记主键 ​

错误 2：外键引用不存在的记录 ​

错误 3：SELECT * 在生产环境滥用 ​

错误 4：无索引的大表查询 ​

错误 5：事务未正确处理失败 ​

8. 实际应用 ​

数字孪生 — 设备数据持久化 ​

9. 深入理解 ​

9.1 范式简述 ​

9.2 索引类型（PostgreSQL） ​

9.3 隔离级别 ​

9.4 JOIN vs 应用层组装 ​

10. 练习 ​

Level 1 — 基础 ​

Level 2 — 应用 ​

Level 3 — 综合 ​

Level 4 — 项目实践 ​

11. 学习检查 ​

12. 下一步 ​

前置知识提醒 ​

学习导航 ​