外观
练习 — SQL 基础与表设计
对应知识点：
SQL·Table·Primary Key·Foreign Key·Index·JOIN·Transaction对应文档：docs/phase-04-database/sql-fundamentals.md代码目录：workspace/phase-04/sql/
前置条件：已安装 PostgreSQL，能使用 psql 或 pgAdmin 执行 SQL。
Level 1 — 基础
练习 1.1 · 创建仓库表
编写 CREATE TABLE 语句，创建 warehouses 表：
| 列 | 类型 | 约束 |
|---|---|---|
| id | VARCHAR(32) | PRIMARY KEY |
| name | VARCHAR(100) | NOT NULL |
| width | DOUBLE PRECISION | NOT NULL |
| height | DOUBLE PRECISION | NOT NULL |
练习 1.2 · 插入 AGV 设备
向 devices 表插入 3 台 AGV 记录（需先创建 devices 表或使用文档中的 schema）：
| id | name | device_type | status |
|---|---|---|---|
| AGV-001 | Alpha | agv | idle |
| AGV-002 | Beta | agv | moving |
| AGV-003 | Gamma | agv | idle |
练习 1.3 · 查询空闲设备
编写 SELECT 查询所有 status = 'idle' 的设备，只返回 id 和 name 列。
练习 1.4 · 更新设备状态
编写 UPDATE 语句，将 AGV-001 的状态改为 'moving'，并编写 SELECT 验证结果。
Level 2 — 应用
练习 2.1 · 创建告警表
创建 alerts 表：
| 列 | 类型 | 约束 |
|---|---|---|
| id | BIGSERIAL | PRIMARY KEY |
| device_id | VARCHAR(32) | NOT NULL, REFERENCES devices(id) |
| level | VARCHAR(20) | NOT NULL（warning 或 critical） |
| message | TEXT | NOT NULL |
| created_at | TIMESTAMPTZ | DEFAULT NOW() |
练习 2.2 · JOIN 查询告警
编写 INNER JOIN 查询，返回所有告警的 device_id、device_name（来自 devices 表）、level、message、created_at。
练习 2.3 · 创建索引
为 telemetry 表的 (device_id, recorded_at DESC) 创建复合索引，说明为什么这个索引适合「查某设备最近 N 条遥测」。
练习 2.4 · 事务操作
用事务实现以下操作（任一步失败则全部回滚）：
- 将
AGV-002状态改为'error' - 插入一条
critical级别告警，message 为'motor failure'
Level 3 — 综合
练习 3.1 · 完整 Schema 设计
设计并创建数字孪生完整 schema，包含四张表：
warehouses（id, name, width, height）devices（id, name, device_type, status, warehouse_id → warehouses）telemetry（id, device_id → devices, x, y, speed, battery, recorded_at）alerts（id, device_id → devices, level, message, created_at）
要求：主键、外键、至少一个索引。
练习 3.2 · 低电量 AGV 查询
编写查询：返回每个仓库中最新电量 battery < 20 的 AGV 列表，包含：
- 仓库名
- 设备 ID 和名称
- 最新电量值
提示：需要 JOIN + 子查询或 DISTINCT ON 获取最新遥测。
练习 3.3 · 遥测统计
编写查询：统计每台设备过去 24 小时的遥测记录数，按记录数降序排列。无记录的设备也应出现（记录数为 0）。
Level 4 — 项目实践
练习 4.1 · 数字孪生数据库搭建
在本地 PostgreSQL 中完成：
- 创建数据库
digital_twin - 执行练习 3.1 的完整 schema
- 插入测试数据：2 个仓库、5 台设备（至少 3 台 AGV）、20 条遥测、3 条告警
- 编写并验证 5 个业务查询：
- 所有 AGV 列表
- 每台 AGV 最新位置（x, y, battery）
- AGV-001 过去 1 小时的历史轨迹
- 所有低电量（battery < 20）设备
- 所有 critical 告警及对应设备名
将 schema 和查询 SQL 保存到 workspace/phase-04/sql/schema.sql 和 workspace/phase-04/sql/queries.sql。
学习检查
- [ ] PRIMARY KEY 与 UNIQUE 的区别？
- [ ] INNER JOIN 与 LEFT JOIN 在「无匹配告警」查询中的差异？
- [ ] 复合索引最左前缀原则？
- [ ] 事务 ACID 在「状态+告警」场景中的作用？
提交方式
检查答案 — SQL 基础练习 X.X并附上 SQL 文件或 psql 执行说明。
学习导航
上一篇：Gin、JWT、API Design · 对应知识文档 · 下一篇：PostgreSQL 与 database/sql