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 陷阱

• NOT IN (子查询)如果子查询包含 NULL 则返回空结果 → 请使用NOT EXISTS
• NULL = NULL是NULL，而非真 → 应使用IS NULL
• COUNT(column)排除NULL值，COUNT(*)统计所有行

索引杀手

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

进行过滤时无帮助

• 连接陷阱
• 对右表使用WHERE条件的LEFT JOIN会变成INNER JOIN
• 多次LEFT JOIN可能导致行数倍增

执行计划分析

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

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

危险信号：

• 顺序扫描在大表上执行 → 需要建立索引
• 被过滤器移除的行数数值过高 → 索引未覆盖过滤条件
• 实际行数与预估行数差异 → 执行ANALYZE 表名；

索引策略

-- 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特定模式
• 分析— 数据分析查询

反馈

• 如有帮助：clawhub star sql
• 保持更新：clawhub sync