SQL

从命令行掌握关系型数据库。涵盖 SQLite、PostgreSQL、MySQL 和 SQL Server，提供经过实战检验的模式设计、查询、迁移和运维方案。

何时使用

当您需要使用关系型数据库时——设计模式、编写查询、构建迁移、优化性能或管理备份。适用于 SQLite、PostgreSQL、MySQL 和 SQL Server。

快速参考

核心规则

1. 选择正确的数据库

2. 始终参数化查询

# ❌ NEVER
cursor.execute(f"SELECT * FROM users WHERE id = {user_id}")

# ✅ ALWAYS
cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))

3. 为过滤条件建立索引

大表中 WHERE、JOIN ON 或 ORDER BY 中使用的任何列都需要建立索引。

4. 使用事务

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

5. 优先使用 EXISTS 而非 IN

-- ✅ Faster (stops at first match)
SELECT * FROM orders o WHERE EXISTS (
  SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active
);

快速开始

SQLite

sqlite3 mydb.sqlite                              # Create/open
sqlite3 mydb.sqlite "SELECT * FROM users;"       # Query
sqlite3 -header -csv mydb.sqlite "SELECT *..." > out.csv
sqlite3 mydb.sqlite "PRAGMA journal_mode=WAL;"   # Better concurrency

PostgreSQL

psql -h localhost -U myuser -d mydb              # Connect
psql -c "SELECT NOW();" mydb                     # Query
psql -f migration.sql mydb                       # Run file
\dt  \d+ users  \di+                             # List tables/indexes

MySQL

mysql -h localhost -u root -p mydb               # Connect
mysql -e "SELECT NOW();" mydb                    # Query

SQL Server

sqlcmd -S localhost -U myuser -d mydb            # Connect
sqlcmd -Q "SELECT GETDATE()"                     # Query
sqlcmd -S localhost -d mydb -E                   # Windows auth

常见陷阱

NULL 陷阱

• 如果子查询包含 NULL，NOT IN (子查询) 会返回空结果 → 使用 NOT EXISTS
• NULL = NULL 结果是 NULL，而非 TRUE → 使用 IS NULL
• COUNT(列名) 排除 NULL，COUNT(*) 统计所有行

索引杀手

• 对列使用函数 → WHERE YEAR(date) = 2024 会全表扫描
• 类型转换 → WHERE varchar_col = 123 会跳过索引
• LIKE '%term' 无法使用索引 → 仅 LIKE 'term%' 有效
• 复合索引 (a, b) 对仅基于 b 的过滤无帮助

JOIN 陷阱

• 在右表上使用 WHERE 条件的 LEFT JOIN 会变成 INNER JOIN
• 缺少 JOIN 条件会导致笛卡尔积
• 多个 LEFT JOIN 可能会使行数倍增

EXPLAIN

-- PostgreSQL
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE user_id = 5;

-- SQLite
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;

危险信号：

• 大表上的 Seq Scan → 需要索引
• Rows Removed by Filter 过高 → 索引未覆盖过滤条件
• 实际行数与估计行数相差较大 → 运行 ANALYZE tablename;

索引策略

-- Composite index (equality first, range last)
CREATE INDEX idx_orders ON orders(user_id, status);

-- Covering index (avoids table lookup)
CREATE INDEX idx_orders ON orders(user_id) INCLUDE (total);

-- Partial index (smaller, faster)
CREATE INDEX idx_pending ON orders(user_id) WHERE status = 'pending';

可移植性

相关技能

如果用户确认，使用 clawhub install <slug> 安装：

• prisma — Node.js ORM
• sqlite — SQLite 特定模式
• analytics — 数据分析查询

反馈

• 如果觉得有用：clawhub star sql
• 保持更新：clawhub sync