gitgoodordietrying

SQL Toolkit

開發程式設計v1.0.0
作者:gitgoodordietrying更新於 2026-05-11sql-toolkit@clawhub_gitgoodordietrying/sql-toolkit
程式碼生成後端開發#SQL#Backup#MySQL#Sqlite#Debugging

據 AlphaMoat 統計,截至 2026-10,SQL Toolkit 累計下載 35,345 次、獲 56 個星標,按累計下載量在本次參與排名的 10,914 個 AI 技能中排名第 755 位,在「開發程式設計」分類(1,069 個)中排名第 47 位。

列表預設按累計下載量排序,也可按星標排序;詳情頁排名按累計下載量計算。

累計下載
3.5萬
星標
56
全站下載量排名
#755
全站前 7% · 「開發程式設計」前 5%
平台累計下載星標評論
ClawHub ↗2.1萬41-
SkillHub ↗3.5萬56—

各平台的計數口徑不同,統計時間不一致。

這個技能是做什麼的

覆蓋 SQLite、PostgreSQL 與 MySQL:庫表設計、複雜查詢(連線、聚合、視窗函式、CTE)、遷移、索引與備份恢復。執行環境只需 sqlite3、psql 或 mysql 中的一個可用。 執行遷移或還原前,先確認目標資料庫並備份,也要驗證遷移檔案;優先採用交易式或工具管理的遷移方式。

資料釋出於 2026-10-27

可以用來做什麼

  • 給現有表設計索引並最佳化慢查詢
  • 編寫跨表聚合與視窗函式查詢

什麼時候用

  • 需要改表結構或寫遷移指令碼時
  • 查詢變慢需要看執行計劃時

示例提示詞

以下是根據技能文件生成的示例,不代表實際執行結果。

這條查詢很慢,幫我加上合適的索引並解釋原因
把這次的表結構改動寫成一份遷移指令碼

月度資料趨勢

累計下載3.5萬
1.4萬2.5萬3.7萬2026-03 · 累計下載 1.5萬2026-05 · 累計下載 1.9萬2026-06 · 累計下載 2萬2026-07 · 累計下載 2.1萬2026-08 · 累計下載 2.2萬2026-09 · 累計下載 2.4萬2026-10 · 累計下載 3.5萬26-0326-0926-10
星標56
3648592026-03 · 星標 382026-05 · 星標 452026-06 · 星標 482026-07 · 星標 562026-08 · 星標 572026-09 · 星標 572026-10 · 星標 5626-0326-0926-10

2026-09 起統計方法調整:同名技能按作者分別統計,並覆蓋全部技能。圖中虛線為調整前的月份,與之後的資料不直接比較,也不計算跨越調整的增長。

月份累計下載環比星標
2026-103.5萬—56
2026-092.4萬—57
2026-082.2萬+4.8%57
2026-072.1萬+6.4%56
2026-062萬+5.4%48
2026-051.9萬—45
2026-031.5萬—38

SKILL.md 原文

以下是作者釋出的原始檔案,按純文字顯示,本站未做修改或稽核;其中的連結不可點選,命令請核實後再執行。抓取於 2026-10-23。

12 KB · ClawHub v1.0.0
---
name: sql-toolkit
description: Query, design, migrate, and optimize SQL databases. Use when working with SQLite, PostgreSQL, or MySQL — schema design, writing queries, creating migrations, indexing, backup/restore, and debugging slow queries. No ORMs required.
metadata: {"clawdbot":{"emoji":"🗄️","requires":{"anyBins":["sqlite3","psql","mysql"]},"os":["linux","darwin","win32"]}}
---

# SQL Toolkit

Work with relational databases directly from the command line. Covers SQLite, PostgreSQL, and MySQL with patterns for schema design, querying, migrations, indexing, and operations.
展開全部(共 435 行)
## When to Use

- Creating or modifying database schemas
- Writing complex queries (joins, aggregations, window functions, CTEs)
- Building migration scripts
- Optimizing slow queries with indexes and EXPLAIN
- Backing up and restoring databases
- Quick data exploration with SQLite (zero setup)

## SQLite (Zero Setup)

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

### Quick Start

```bash
# Create/open a database
sqlite3 mydb.sqlite

# Import CSV directly
sqlite3 mydb.sqlite ".mode csv" ".import data.csv mytable" "SELECT COUNT(*) FROM mytable;"

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

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

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

### Schema Operations

```sql
-- Create table
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 with foreign key
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'))
);

-- Add column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Create index
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- View schema
.schema users
.tables
```

## PostgreSQL

### Connection

```bash
# Connect
psql -h localhost -U myuser -d mydb

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

# Run single query
psql -h localhost -U myuser -d mydb -c "SELECT NOW();"

# Run SQL file
psql -h localhost -U myuser -d mydb -f migration.sql

# List databases
psql -l
```

### Schema Design Patterns

```sql
-- Use UUIDs for distributed-friendly primary keys
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    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 users_email_unique UNIQUE(email)
);

-- Auto-update updated_at
CREATE OR REPLACE FUNCTION update_modified_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_modtime
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_modified_column();

-- Enum type (PostgreSQL-specific)
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    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()
);

-- Partial index (only index active orders — smaller, faster)
CREATE INDEX idx_orders_active ON orders(user_id, created_at)
    WHERE status NOT IN ('delivered', 'cancelled');

-- GIN index for JSONB queries
CREATE INDEX idx_orders_metadata ON orders USING GIN(metadata);
```

### JSONB Queries (PostgreSQL)

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

-- Query JSON fields
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;

-- Update JSON field
UPDATE orders SET metadata = jsonb_set(metadata, '{source}', '"mobile"') WHERE id = '...';
```

## MySQL

### Connection

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

### Key Differences from PostgreSQL

```sql
-- Auto-increment (not SERIAL)
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- JSON type (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
);

-- Query JSON
SELECT * FROM orders WHERE JSON_EXTRACT(metadata, '$.source') = 'web';
-- Or shorthand:
SELECT * FROM orders WHERE metadata->>'$.source' = 'web';
```

## Query Patterns

### Joins

```sql
-- Inner join (only matching rows)
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';

-- Left join (all users, even without orders)
SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Self-join (find users with same email domain)
SELECT a.name, b.name, SPLIT_PART(a.email, '@', 2) AS domain
FROM users a
JOIN users b ON SPLIT_PART(a.email, '@', 2) = SPLIT_PART(b.email, '@', 2)
WHERE a.id < b.id;
```

### Aggregations

```sql
-- Group by with having
SELECT status, COUNT(*) AS cnt, SUM(total) AS revenue
FROM orders
GROUP BY status
HAVING COUNT(*) > 10
ORDER BY revenue DESC;

-- Running total (window function)
SELECT date, revenue,
    SUM(revenue) OVER (ORDER BY date) AS cumulative_revenue
FROM daily_sales;

-- Rank within groups
SELECT user_id, total,
    RANK() OVER (PARTITION BY user_id ORDER BY total DESC) AS rank
FROM orders;

-- Moving average (last 7 entries)
SELECT date, revenue,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM daily_sales;
```

### Common Table Expressions (CTEs)

```sql
-- Readable multi-step queries
WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month', created_at) 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;

-- Recursive CTE (org chart / tree traversal)
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
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT REPEAT('  ', depth) || name AS org_chart FROM org_tree ORDER BY depth, name;
```

## Migrations

### Manual Migration Script Pattern

```bash
#!/bin/bash
# migrate.sh - Run numbered SQL migration files
DB_URL="${1:?Usage: migrate.sh <db-url>}"
MIGRATIONS_DIR="./migrations"

# Create tracking table
psql "$DB_URL" -c "CREATE TABLE IF NOT EXISTS schema_migrations (
    version TEXT PRIMARY KEY,
    applied_at TIMESTAMPTZ DEFAULT NOW()
);"

# Run pending migrations in order
for file in $(ls "$MIGRATIONS_DIR"/*.sql | sort); do
    version=$(basename "$file" .sql)
    already=$(psql "$DB_URL" -tAc "SELECT 1 FROM schema_migrations WHERE version='$version';")
    if [ "$already" = "1" ]; then
        echo "SKIP: $version (already applied)"
        continue
    fi
    echo "APPLY: $version"
    psql "$DB_URL" -f "$file" && \
    psql "$DB_URL" -c "INSERT INTO schema_migrations (version) VALUES ('$version');" || {
        echo "FAILED: $version"
        exit 1
    }
done
echo "All migrations applied."
```

### Migration File Convention

```
migrations/
  001_create_users.sql
  002_create_orders.sql
  003_add_users_phone.sql
  004_add_orders_metadata_index.sql
```

Each file:
```sql
-- 003_add_users_phone.sql
-- Up
ALTER TABLE users ADD COLUMN phone TEXT;

-- To reverse: ALTER TABLE users DROP COLUMN phone;
```

## Query Optimization

### EXPLAIN (PostgreSQL)

```sql
-- Show query plan
EXPLAIN SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';

-- Show actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = '...' AND status = 'paid';
```

**What to look for:**
- `Seq Scan` on large tables → needs an index
- `Nested Loop` with large row counts → consider `Hash Join` (may need more `work_mem`)
- `Rows Removed by Filter` being high → index doesn't cover the filter
- Actual rows far from estimated → run `ANALYZE tablename;` to update statistics

### Index Strategy

```sql
-- Single column (most common)
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Composite (for queries filtering on both columns)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- Column ORDER matters: put equality filters first, range filters last

-- Covering index (includes data columns to avoid table lookup)
CREATE INDEX idx_orders_covering ON orders(user_id, status) INCLUDE (total, created_at);

-- Partial index (smaller, faster — only index what you query)
CREATE INDEX idx_orders_pending ON orders(user_id) WHERE status = 'pending';

-- Check unused indexes
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;
```

### SQLite EXPLAIN

```sql
EXPLAIN QUERY PLAN SELECT * FROM orders WHERE user_id = 5;
-- Look for: SCAN (bad) vs SEARCH USING INDEX (good)
```

## Backup & Restore

### PostgreSQL

```bash
# Full dump (custom format, compressed)
pg_dump -Fc -h localhost -U myuser mydb > backup.dump

# Restore
pg_restore -h localhost -U myuser -d mydb --clean --if-exists backup.dump

# SQL dump (portable, readable)
pg_dump -h localhost -U myuser mydb > backup.sql

# Dump specific tables
pg_dump -h localhost -U myuser -t users -t orders mydb > partial.sql

# Copy table to CSV
psql -c "\copy (SELECT * FROM users) TO 'users.csv' CSV HEADER"
```

### SQLite

```bash
# Backup (just copy the file, but use .backup for consistency)
sqlite3 mydb.sqlite ".backup backup.sqlite"

# Dump to SQL
sqlite3 mydb.sqlite .dump > backup.sql

# Restore from SQL
sqlite3 newdb.sqlite < backup.sql
```

### MySQL

```bash
# Dump
mysqldump -h localhost -u root -p mydb > backup.sql

# Restore
mysql -h localhost -u root -p mydb < backup.sql
```

## Tips

- Always use parameterized queries in application code — never concatenate user input into SQL
- Use `TIMESTAMPTZ` (not `TIMESTAMP`) in PostgreSQL for timezone-aware dates
- Set `PRAGMA journal_mode=WAL;` in SQLite for concurrent read performance
- Use `EXPLAIN` before deploying any query that runs on large tables
- PostgreSQL: `\d+ tablename` shows columns, indexes, and size. `\di+` lists all indexes with sizes
- For quick data exploration, import any CSV into SQLite: `sqlite3 :memory: ".mode csv" ".import file.csv t" "SELECT ..."`

「開發程式設計」分類下的熱門技能

程式設計大神.Skill開發程式設計

【全棧能力】程式設計大神.Skill:面向複雜真實專案的高階全棧程式設計助手。不止生成程式碼,而是從一句目標接手陌生程式碼庫,拆解需求、補齊實現、追到根因、完成重構、聯調前後端,並把結果推進到能執行、能驗證、能交付。覆蓋 Web、後端、指令碼、iOS、安卓、鴻蒙 ArkUI、微信小程式及其他技術棧,支援中英文工程、疑難 Bug、跨模組任務和長任務續做;用構建、日誌、真實入口、裝置或介面證據說明結果。僅問概念時直接回答,要求落地時持續推進到驗收或明確阻塞。

↓ 1125.9萬★ 82u_42bbb65f
程式設計專家Pro.Skill開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 626.9萬★ 32user_47d655f0
程式設計專家.Skill開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 600.7萬★ 378user_741dc82b
dev-expert開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 528.1萬★ 32user_814dbe54
dev-expert-pro開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 104.7萬★ -user_814dbe54
程式設計專家 Pro開發程式設計

程式設計專家 Pro:融合程式設計專家 2.0.3 工程專題與程式設計大神的鴻蒙 ArkTS/ArkUI、微信小程式和小遊戲開發資料。用於專案實現、根因除錯、審查、重構、測試、架構效能分析及多端開發驗證。支援自然語言和 @專題標識,按任務讀取包內知識;只問概念時直接回答。

↓ 98.2萬★ 1user_28275a78
程式設計專家.Skill開發程式設計

程式設計專家.Skill P8級程式設計助手,25年實戰經驗技能Skill,全棧:Java/Php網站/軟體專案、API設計、Bug診斷、程式碼生成、程式碼審查、重構、測試用例、效能基準、技術選型、文件生成、任務拆解、Spec驅動、Karpathy規範、CMS二次開發、前端設計、MySQL/Mariadb、專案知識圖譜,以及工程紀律層(根因除錯硬閉環、階段導航、事故復盤/SLO、威脅建模/供應鏈安全、生產就緒/漸進式釋出、AI編碼治理、面向Agent程式碼可讀性、LLM應用安全)。支援 @標識 顯式呼叫跳過路由。

↓ 70.1萬★ 6user_613b33ea
Github開發程式設計

用 gh 命令列工具檢視 GitHub 上的 PR 檢查、工作流執行與 API 查詢的技能。

↓ 48萬★ 714Peter Steinberger

gitgoodordietrying 的其他技能

資料月份:2026-10 · 下載/星標數聚合自公開技能註冊庫(ClawHub、SkillHub),按月更新。口徑說明見資料方法。