sql-expert by questfortech-investments/claude-code-skills
npx skills add https://github.com/questfortech-investments/claude-code-skills --skill sql-expert为 PostgreSQL、MySQL、SQLite 和 SQL Server 数据库提供编写、优化和管理的专家指导。
此技能使您能够:
最适合:复杂查询、JSON 数据、高级功能、ACID 合规性
pip install psycopg2-binary sqlalchemy
最适合:Web 应用程序、WordPress、高读取负载
pip install mysql-connector-python sqlalchemy
广告位招租
在这里展示您的产品或服务
触达数万 AI 开发者,精准高效
最适合:本地开发、嵌入式数据库、测试
pip install sqlite3 # Built into Python
最适合:企业应用程序、Windows 环境
pip install pyodbc sqlalchemy
-- 带过滤的简单 SELECT
SELECT
column1,
column2,
column3
FROM
table_name
WHERE
condition = 'value'
AND another_condition > 100
ORDER BY
column1 DESC
LIMIT 10;
-- INNER JOIN
SELECT
users.name,
orders.order_date,
orders.total_amount
FROM
users
INNER JOIN
orders ON users.id = orders.user_id
WHERE
orders.status = 'completed';
-- LEFT JOIN (包含所有用户,即使没有订单)
SELECT
users.name,
COUNT(orders.id) as order_count,
COALESCE(SUM(orders.total_amount), 0) as total_spent
FROM
users
LEFT JOIN
orders ON users.id = orders.user_id
GROUP BY
users.id, users.name;
-- WHERE 子句中的子查询
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- 公共表表达式 (CTE)
WITH high_value_customers AS (
SELECT
user_id,
SUM(total_amount) as lifetime_value
FROM orders
GROUP BY user_id
HAVING SUM(total_amount) > 1000
)
SELECT
users.name,
users.email,
hvc.lifetime_value
FROM users
INNER JOIN high_value_customers hvc ON users.id = hvc.user_id;
-- 组内排名
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
employees;
-- 累计总和
SELECT
order_date,
total_amount,
SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM
orders;
-- 移动平均
SELECT
order_date,
total_amount,
AVG(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7days
FROM
daily_sales;
更多高级查询模式请参见 examples/complex_queries.sql。
-- 分析查询性能
EXPLAIN ANALYZE
SELECT
users.name,
COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;
-- 关注点:
-- - Seq Scan (差) vs Index Scan (好)
-- - 高成本数值
-- - 处理的大量行数
-- 差:对索引列使用函数
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- 好:保持索引列"干净"
SELECT * FROM users WHERE email = LOWER('user@example.com');
-- 差:SELECT *
SELECT * FROM large_table WHERE id = 123;
-- 好:仅选择需要的列
SELECT id, name, email FROM large_table WHERE id = 123;
全面的优化技术,请参见 references/query-optimization.md。
第一范式 (1NF):消除重复组,使用原子值
-- 好:为订单项使用单独的表
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_name VARCHAR(100)
);
第二范式 (2NF):所有非键属性完全依赖于整个主键
-- 好:分离产品信息
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_price DECIMAL(10, 2)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
第三范式 (3NF):无传递依赖
一对多:
CREATE TABLE authors (
author_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT NOT NULL,
published_date DATE,
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
多对多:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
-- 联结表
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id),
UNIQUE (student_id, course_id)
);
更多模式模式请参见 examples/schema_examples.sql。
-- 单列索引
CREATE INDEX idx_users_email ON users(email);
-- 复合索引(顺序很重要!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
-- 唯一索引
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- 部分索引 (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
何时创建索引:
何时不创建索引:
详细的索引策略,请参见 references/indexes-performance.md。
-- 步骤 1:添加可为空的列
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- 步骤 2:填充现有行
UPDATE users SET status = 'active' WHERE status IS NULL;
-- 步骤 3:设为 NOT NULL
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
-- 步骤 4:为新行添加默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- 回滚计划
ALTER TABLE users DROP COLUMN status;
-- 好:先添加可为空的列,然后回填数据
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);
-- 分批回填
UPDATE large_table SET new_column = 'value' WHERE new_column IS NULL LIMIT 1000;
-- 重复直到完成
-- 然后设为 NOT NULL
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;
更多迁移模式请参见 examples/migrations.sql。
-- PostgreSQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email,
updated_at = NOW();
-- MySQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email),
updated_at = NOW();
-- 分层数据遍历
WITH RECURSIVE employee_hierarchy AS (
-- 锚点:顶级员工
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 递归:向上一级汇报的员工
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;
更多高级模式,包括数据透视表、JSON 操作和批量操作,请参见 references/advanced-patterns.md。
包含多项最佳实践的示例:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
status VARCHAR(20) CHECK (status IN ('pending', 'completed', 'cancelled')),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
全面的最佳实践,请参见 references/best-practices.md。
注意这些常见问题:
示例 - 避免 N+1:
# 差:N+1 查询
users = db.query("SELECT * FROM users")
for user in users:
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
# 好:带 JOIN 的单一查询
result = db.query("""
SELECT users.*, orders.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
""")
完整的陷阱列表及解决方案,请参见 references/common-pitfalls.md。
辅助脚本 (scripts/):
sql_helper.py - 用于查询构建、模式内省、索引分析和迁移辅助的实用函数示例 (examples/):
complex_queries.sql - 使用 CTE、窗口函数和子查询的高级查询模式schema_examples.sql - 各种用例的完整模式设计示例migrations.sql - 安全迁移模式和零停机技术参考资料 (references/):
query-optimization.md - 全面的查询优化技术和 EXPLAIN 分析indexes-performance.md - 详细的索引策略、维护和监控advanced-patterns.md - UPSERT、批量操作、数据透视表、JSON 操作、递归查询best-practices.md - 完整的 SQL 最佳实践指南common-pitfalls.md - 常见错误及如何避免references/query-optimization.mdexamples/schema_examples.sqlreferences/advanced-patterns.mdscripts/sql_helper.py处理 SQL 数据库时:
对于迁移:
每周安装次数
102
仓库
GitHub 星标数
4
首次出现
2026年1月22日
安全审计
安装于
opencode75
codex71
claude-code68
gemini-cli67
github-copilot63
cursor63
Expert guidance for writing, optimizing, and managing SQL databases across PostgreSQL, MySQL, SQLite, and SQL Server.
This skill enables you to:
Best for : Complex queries, JSON data, advanced features, ACID compliance
pip install psycopg2-binary sqlalchemy
Best for : Web applications, WordPress, high-read workloads
pip install mysql-connector-python sqlalchemy
Best for : Local development, embedded databases, testing
pip install sqlite3 # Built into Python
Best for : Enterprise applications, Windows environments
pip install pyodbc sqlalchemy
-- Simple SELECT with filtering
SELECT
column1,
column2,
column3
FROM
table_name
WHERE
condition = 'value'
AND another_condition > 100
ORDER BY
column1 DESC
LIMIT 10;
-- INNER JOIN
SELECT
users.name,
orders.order_date,
orders.total_amount
FROM
users
INNER JOIN
orders ON users.id = orders.user_id
WHERE
orders.status = 'completed';
-- LEFT JOIN (include all users, even without orders)
SELECT
users.name,
COUNT(orders.id) as order_count,
COALESCE(SUM(orders.total_amount), 0) as total_spent
FROM
users
LEFT JOIN
orders ON users.id = orders.user_id
GROUP BY
users.id, users.name;
-- Subquery in WHERE clause
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Common Table Expression (CTE)
WITH high_value_customers AS (
SELECT
user_id,
SUM(total_amount) as lifetime_value
FROM orders
GROUP BY user_id
HAVING SUM(total_amount) > 1000
)
SELECT
users.name,
users.email,
hvc.lifetime_value
FROM users
INNER JOIN high_value_customers hvc ON users.id = hvc.user_id;
-- Ranking within groups
SELECT
name,
department,
salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM
employees;
-- Running totals
SELECT
order_date,
total_amount,
SUM(total_amount) OVER (ORDER BY order_date) as running_total
FROM
orders;
-- Moving averages
SELECT
order_date,
total_amount,
AVG(total_amount) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as moving_avg_7days
FROM
daily_sales;
See examples/complex_queries.sql for more advanced query patterns.
-- Analyze query performance
EXPLAIN ANALYZE
SELECT
users.name,
COUNT(orders.id) as order_count
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;
-- Look for:
-- - Seq Scan (bad) vs Index Scan (good)
-- - High cost numbers
-- - Large row counts being processed
-- BAD: Function on indexed column
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
-- GOOD: Keep indexed column clean
SELECT * FROM users WHERE email = LOWER('user@example.com');
-- BAD: SELECT *
SELECT * FROM large_table WHERE id = 123;
-- GOOD: Select only needed columns
SELECT id, name, email FROM large_table WHERE id = 123;
For comprehensive optimization techniques, see references/query-optimization.md.
First Normal Form (1NF) : Eliminate repeating groups, use atomic values
-- GOOD: Separate table for order items
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_name VARCHAR(100)
);
CREATE TABLE order_items (
order_item_id INT PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_name VARCHAR(100)
);
Second Normal Form (2NF) : All non-key attributes depend on entire primary key
-- GOOD: Separate product information
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
product_price DECIMAL(10, 2)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
Third Normal Form (3NF) : No transitive dependencies
One-to-Many:
CREATE TABLE authors (
author_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);
CREATE TABLE books (
book_id INT PRIMARY KEY,
title VARCHAR(200),
author_id INT NOT NULL,
published_date DATE,
FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
Many-to-Many:
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(100)
);
CREATE TABLE courses (
course_id INT PRIMARY KEY,
course_name VARCHAR(100)
);
-- Junction table
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY,
student_id INT NOT NULL,
course_id INT NOT NULL,
enrollment_date DATE,
grade CHAR(2),
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id),
UNIQUE (student_id, course_id)
);
See examples/schema_examples.sql for more schema patterns.
-- Single column index
CREATE INDEX idx_users_email ON users(email);
-- Composite index (order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';
When to create indexes:
When NOT to create indexes:
For detailed index strategies, see references/indexes-performance.md.
-- Step 1: Add column as nullable
ALTER TABLE users ADD COLUMN status VARCHAR(20);
-- Step 2: Populate existing rows
UPDATE users SET status = 'active' WHERE status IS NULL;
-- Step 3: Make it NOT NULL
ALTER TABLE users ALTER COLUMN status SET NOT NULL;
-- Step 4: Add default for new rows
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';
-- Rollback plan
ALTER TABLE users DROP COLUMN status;
-- GOOD: Add column as nullable first, then backfill
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);
-- Backfill in batches
UPDATE large_table SET new_column = 'value' WHERE new_column IS NULL LIMIT 1000;
-- Repeat until complete
-- Then make it NOT NULL
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;
See examples/migrations.sql for more migration patterns.
-- PostgreSQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON CONFLICT (user_id)
DO UPDATE SET
name = EXCLUDED.name,
email = EXCLUDED.email,
updated_at = NOW();
-- MySQL
INSERT INTO users (user_id, name, email, updated_at)
VALUES (1, 'John Doe', 'john@example.com', NOW())
ON DUPLICATE KEY UPDATE
name = VALUES(name),
email = VALUES(email),
updated_at = NOW();
-- Hierarchical data traversal
WITH RECURSIVE employee_hierarchy AS (
-- Anchor: top-level employees
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: employees reporting to previous level
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;
For more advanced patterns including pivot tables, JSON operations, and bulk operations, see references/advanced-patterns.md.
Example with multiple best practices:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT NOT NULL,
order_date DATE NOT NULL DEFAULT CURRENT_DATE,
total_amount DECIMAL(10, 2) CHECK (total_amount >= 0),
status VARCHAR(20) CHECK (status IN ('pending', 'completed', 'cancelled')),
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (user_id) REFERENCES users(user_id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
For comprehensive best practices, see references/best-practices.md.
Watch out for these frequent issues:
Example - Avoiding N+1:
# BAD: N+1 queries
users = db.query("SELECT * FROM users")
for user in users:
orders = db.query("SELECT * FROM orders WHERE user_id = ?", user.id)
# GOOD: Single query with JOIN
result = db.query("""
SELECT users.*, orders.*
FROM users
LEFT JOIN orders ON users.id = orders.user_id
""")
For a complete list of pitfalls and solutions, see references/common-pitfalls.md.
Helper Scripts (scripts/):
sql_helper.py - Utility functions for query building, schema introspection, index analysis, and migration helpersExamples (examples/):
complex_queries.sql - Advanced query patterns with CTEs, window functions, and subqueriesschema_examples.sql - Complete schema design examples for various use casesmigrations.sql - Safe migration patterns and zero-downtime techniquesReferences (references/):
query-optimization.md - Comprehensive query optimization techniques and EXPLAIN analysisindexes-performance.md - Detailed index strategies, maintenance, and monitoringadvanced-patterns.md - UPSERT, bulk operations, pivot tables, JSON operations, recursive queriesbest-practices.md - Complete SQL best practices guidecommon-pitfalls.md - Common mistakes and how to avoid themreferences/query-optimization.mdexamples/schema_examples.sqlreferences/advanced-patterns.mdscripts/sql_helper.pyWhen working with SQL databases:
For migrations:
Weekly Installs
102
Repository
GitHub Stars
4
First Seen
Jan 22, 2026
Security Audits
Gen Agent Trust HubFailSocketPassSnykPass
Installed on
opencode75
codex71
claude-code68
gemini-cli67
github-copilot63
cursor63
React 组合模式指南:Vercel 组件架构最佳实践,提升代码可维护性
120,000 周安装