SQL 工具包

直接从命令行操作关系型数据库。涵盖 SQLite、PostgreSQL 和 MySQL，包含模式设计、查询、迁移、索引和操作模式。

使用场景

• - 创建或修改数据库模式
• 编写复杂查询（连接、聚合、窗口函数、CTE）
• 构建迁移脚本
• 使用索引和 EXPLAIN 优化慢查询
• 备份和恢复数据库
• 使用 SQLite 快速探索数据（零配置）

SQLite（零配置）

SQLite 随 Python 附带，可在每个系统上使用。用于本地数据、原型设计和单文件数据库。

快速开始

bash

创建/打开数据库

sqlite3 mydb.sqlite

直接导入 CSV

sqlite3 mydb.sqlite .mode csv .import data.csv mytable SELECT COUNT(*) FROM mytable;

单行查询

sqlite3 mydb.sqlite SELECT * FROM users WHERE created_at > 2026-01-01 LIMIT 10;

导出为 CSV

sqlite3 -header -csv mydb.sqlite SELECT * FROM orders; > orders.csv

带表头和列的交互模式

sqlite3 -header -column mydb.sqlite

模式操作

sql
-- 创建表
CREATE TABLE users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
created_at TEXT DEFAULT (datetime(now)),
updated_at TEXT DEFAULT (datetime(now))
);

-- 创建带外键的表
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total REAL NOT NULL CHECK(total >= 0),
status TEXT NOT NULL DEFAULT pending CHECK(status IN (pending,paid,shipped,cancelled)),
created_at TEXT DEFAULT (datetime(now))
);

-- 添加列
ALTER TABLE users ADD COLUMN phone TEXT;

-- 创建索引
CREATE INDEX idxordersuserid ON orders(userid);
CREATE UNIQUE INDEX idxusersemail ON users(email);

-- 查看模式
.schema users
.tables

PostgreSQL

连接

bash

连接

psql -h localhost -U myuser -d mydb

连接字符串

psql postgresql://user:pass@localhost:5432/mydb?sslmode=require

运行单个查询

psql -h localhost -U myuser -d mydb -c SELECT NOW();

运行 SQL 文件

psql -h localhost -U myuser -d mydb -f migration.sql

列出数据库

psql -l

模式设计模式

sql
-- 使用 UUID 作为分布式友好的主键
CREATE EXTENSION IF NOT EXISTS uuid-ossp;

CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuidgeneratev4(),
email TEXT NOT NULL,
name TEXT NOT NULL,
password_hash TEXT NOT NULL,
role TEXT NOT NULL DEFAULT user CHECK(role IN (user,admin,moderator)),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
CONSTRAINT usersemailunique UNIQUE(email)
);

-- 自动更新 updated_at
CREATE OR REPLACE FUNCTION updatemodifiedcolumn()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER updateusersmodtime
BEFORE UPDATE ON users
FOR EACH ROW EXECUTE FUNCTION updatemodifiedcolumn();

-- 枚举类型（PostgreSQL 特有）
CREATE TYPE order_status AS ENUM (pending, paid, shipped, delivered, cancelled);

CREATE TABLE orders (
id UUID PRIMARY KEY DEFAULT uuidgeneratev4(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
status order_status NOT NULL DEFAULT pending,
total NUMERIC(10,2) NOT NULL CHECK(total >= 0),
metadata JSONB DEFAULT {},
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 部分索引（仅索引活跃订单 — 更小、更快）
CREATE INDEX idxordersactive ON orders(userid, createdat)
WHERE status NOT IN (delivered, cancelled);

-- JSONB 查询的 GIN 索引
CREATE INDEX idxordersmetadata ON orders USING GIN(metadata);

JSONB 查询（PostgreSQL）

sql
-- 存储 JSON
INSERT INTO orders (user_id, total, metadata)
VALUES (..., 99.99, {source: web, coupon: SAVE10, items: [{sku: A1, qty: 2}]});

-- 查询 JSON 字段
SELECT * FROM orders WHERE metadata->>source = web;
SELECT * FROM orders WHERE metadata->items @> [{sku: A1}];
SELECT metadata->>coupon AS coupon, COUNT(*) FROM orders GROUP BY 1;

-- 更新 JSON 字段
UPDATE orders SET metadata = jsonb_set(metadata, {source}, mobile) WHERE id = ...;

MySQL

连接

bash
mysql -h localhost -u root -p mydb
mysql -h localhost -u root -p -e SELECT NOW(); mydb

与 PostgreSQL 的主要区别

sql
-- 自增（不是 SERIAL）
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
createdat TIMESTAMP DEFAULT CURRENTTIMESTAMP,
updatedat TIMESTAMP DEFAULT CURRENTTIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- JSON 类型（MySQL 5.7+）
CREATE TABLE orders (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
user_id BIGINT UNSIGNED NOT NULL,
metadata JSON,
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 查询 JSON
SELECT * FROM orders WHERE JSON_EXTRACT(metadata, $.source) = web;
-- 或简写：
SELECT * FROM orders WHERE metadata->>$.source = web;

查询模式

连接

sql
-- 内连接（仅匹配行）
SELECT u.name, o.total, o.status
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.created_at > 2026-01-01;

-- 左连接（所有用户，即使没有订单）
SELECT u.name, COUNT(o.id) AS ordercount, COALESCE(SUM(o.total), 0) AS totalspent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- 自连接（查找相同邮箱域名的用户）
SELECT a.name, b.name, SPLIT_PART(a.email, @, 2) AS domain
FROM users a
JOIN users b ON SPLITPART(a.email, @, 2) = SPLITPART(b.email, @, 2)
WHERE a.id < b.id;

聚合

sql
-- 分组并带 HAVING
SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY revenue DESC;

-- 累计总和（窗口函数）
SELECT date, revenue,
SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_sales;

-- 组内排名
SELECT user_id, total,
RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
FROM orders;

-- 移动平均（最近 7 条）
SELECT date, revenue,
AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;

公用表表达式（CTE）

sql
-- 可读的多步骤查询
WITH monthly_revenue AS (
SELECT DATETRUNC(month, createdat) AS month,
SUM(total) AS revenue
FROM orders
WHERE status = paid
GROUP BY 1
),
growth AS (
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) /
NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS growth_pct
FROM monthly_revenue
)
SELECT * FROM growth ORDER BY month;

-- 递归 CTE（组织架构图/树遍历）
WITH RECURSIVE org_tree AS (
SELECT id, name, manager_id, 0 AS depth
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, t.depth + 1