SQL Toolkit — SQL 工具kit

SQL 工具kit

Work with relational databases directly from the command line. Covers SQLite, PostgreSQL, and MySQL with patterns for 模式 de签名, 查询ing, 迁移s, 索引ing, and operations.

When to Use Creating or modifying database 模式s Writing complex queries (joins, aggregations, window functions, CTEs) Building 迁移 scripts Optimizing slow queries with 索引es and EXPLAIN Backing up and restoring databases Quick data exploration with SQLite (zero 设置up) SQLite (Zero 设置up)

SQLite is included with Python and avAIlable on every 系统. Use it for local data, prototyping, and single-file databases.

Quick 启动 # 创建/open a database sqlite3 mydb.sqlite

# 导入 CSV directly sqlite3 mydb.sqlite ".mode csv" ".导入 data.csv mytable" "SELECT COUNT() FROM mytable;"

# One-liner queries sqlite3 mydb.sqlite "SELECT FROM users WHERE 创建d_at > '2026-01-01' LIMIT 10;"

# 导出 to CSV sqlite3 -header -csv mydb.sqlite "SELECT FROM orders;" > orders.csv

# Interactive mode with headers and columns sqlite3 -header -column mydb.sqlite

模式 Operations -- 创建 table 创建 TABLE users ( id INTEGER PRIMARY KEY AUTOINCREMENT, emAIl TEXT NOT NULL UNIQUE, name TEXT NOT NULL, 创建d_at TEXT DEFAULT (datetime('now')), 更新d_at TEXT DEFAULT (datetime('now')) );

-- 创建 with foreign key 创建 TABLE orders ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES users(id) ON 删除 CASCADE, total REAL NOT NULL 检查(total >= 0), 状态 TEXT NOT NULL DEFAULT 'pending' 检查(状态 IN ('pending','pAId','shipped','cancelled')), 创建d_at TEXT DEFAULT (datetime('now')) );

-- 添加 column ALTER TABLE users 添加 COLUMN phone TEXT;

-- 创建 索引 创建 索引 idx_orders_user_id ON orders(user_id); 创建 UNIQUE 索引 idx_users_emAIl ON users(emAIl);

-- View 模式 .模式 users .tables

PostgreSQL Connection # Connect psql -h localhost -U myuser -d mydb

# Connection string psql "postgresql://user:pass@localhost:5432/mydb?sslmode=require"

# 运行 single 查询 psql -h localhost -U myuser -d mydb -c "SELECT NOW();"

# 运行 SQL file psql -h localhost -U myuser -d mydb -f 迁移.sql

# 列出 databases psql -l

模式 De签名 Patterns -- Use UUIDs for distributed-friendly primary keys 创建 扩展 IF NOT EXISTS "uuid-ossp";

创建 TABLE users ( id UUID PRIMARY KEY DEFAULT uuid_生成_v4(), emAIl TEXT NOT NULL, name TEXT NOT NULL, password_哈希 TEXT NOT NULL, 角色 TEXT NOT NULL DEFAULT 'user' 检查(角色 IN ('user','admin','moderator')), 创建d_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), 更新d_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), CONSTRAINT users_emAIl_unique UNIQUE(emAIl) );

-- Auto-更新 更新d_at 创建 OR REPLACE FUNCTION 更新_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.更新d_at = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;

创建 TRIGGER 更新_users_modtime BEFORE 更新 ON users FOR EACH ROW 执行 FUNCTION 更新_modified_column();

-- Enum type (PostgreSQL-specific) 创建 TYPE order_状态 AS ENUM ('pending', 'pAId', 'shipped', 'delivered', 'cancelled');

创建 TABLE orders ( id UUID PRIMARY KEY DEFAULT uuid_生成_v4(), user_id UUID NOT NULL REFERENCES users(id) ON 删除 CASCADE, 状态 order_状态 NOT NULL DEFAULT 'pending', total NUMERIC(10,2) NOT NULL 检查(total >= 0), metadata JSONB DEFAULT '{}', 创建d_at TIMESTAMPTZ NOT NULL DEFAULT NOW() );

-- Partial 索引 (only 索引 active orders — smaller, faster) 创建 索引 idx_orders_active ON orders(user_id, 创建d_at) WHERE 状态 NOT IN ('delivered', 'cancelled');

-- GIN 索引 for JSONB queries 创建 索引 idx_orders_metadata ON orders USING GIN(metadata);

JSONB Queries (PostgreSQL) -- Store JSON INSERT INTO orders (user_id, total, metadata) VALUES ('...', 99.99, '{"source": "网页", "coupon": "SAVE10", "items": [{"sku": "A1", "qty": 2}]}');

-- 查询 JSON fields SELECT FROM orders WHERE metadata->>'source' = '网页'; SELECT FROM orders WHERE metadata->'items' @> '[{"sku": "A1"}]'; SELECT metadata->>'coupon' AS coupon, COUNT() FROM orders GROUP BY 1;

-- 更新 JSON field 更新 orders 设置 metadata = jsonb_设置(metadata, '{source}', '"移动"') WHERE id = '...';

MySQL Connection mysql -h localhost -u root -p mydb mysql -h localhost -u root -p -e "SELECT NOW();" mydb

Key Differences from PostgreSQL -- Auto-increment (not SERIAL) 创建 TABLE users ( id BIGINT UN签名ED AUTO_INCREMENT PRIMARY KEY, emAIl VARCHAR(255) NOT NULL UNIQUE, name VARCHAR(255) NOT NULL, 创建d_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 更新d_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON 更新 CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHAR设置=utf8mb4;

-- JSON type (MySQL 5.7+) 创建 TABLE orders ( id BIGINT UN签名ED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UN签名ED NOT NULL, metadata JSON, FOREIGN KEY (user_id) REFERENCES users(id) ON 删除 CASCADE );

-- 查询 JSON