sql-expert

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

SQL Expert Skill

SQL专家技能

Expert guidance for writing, optimizing, and managing SQL databases across PostgreSQL, MySQL, SQLite, and SQL Server.

为PostgreSQL、MySQL、SQLite和SQL Server等数据库提供专业的查询编写、优化及管理指导。

Core Capabilities

核心能力

This skill enables you to:

Write complex SQL queries with JOINs, subqueries, CTEs, and window functions
Optimize slow queries using EXPLAIN plans and index recommendations
Design database schemas with proper normalization (1NF, 2NF, 3NF, BCNF)
Create effective indexes for query performance
Write database migrations safely with rollback support
Debug SQL errors and understand error messages
Handle transactions with proper isolation levels
Work with JSON/JSONB data types
Generate sample data for testing
Convert between database dialects (PostgreSQL ↔ MySQL ↔ SQLite)

本技能可帮助您：

编写复杂SQL查询，包含JOIN、子查询、CTE和窗口函数
优化慢查询，使用EXPLAIN执行计划和索引建议
设计数据库架构，遵循规范化原则（1NF、2NF、3NF、BCNF）
创建高效索引，提升查询性能
编写数据库迁移脚本，支持安全回滚
调试SQL错误，理解错误信息
处理事务，设置合适的隔离级别
操作JSON/JSONB数据类型
生成测试用示例数据
在不同数据库方言间转换（PostgreSQL ↔ MySQL ↔ SQLite）

Supported Database Systems

支持的数据库系统

PostgreSQL

Best for: Complex queries, JSON data, advanced features, ACID compliance

bash

pip install psycopg2-binary sqlalchemy

适用场景：复杂查询、JSON数据、高级功能、ACID合规性

bash

pip install psycopg2-binary sqlalchemy

MySQL/MariaDB

Best for: Web applications, WordPress, high-read workloads

bash

pip install mysql-connector-python sqlalchemy

适用场景：Web应用、WordPress、高读负载场景

bash

pip install mysql-connector-python sqlalchemy

SQLite

Best for: Local development, embedded databases, testing

bash

pip install sqlite3  # Built into Python

适用场景：本地开发、嵌入式数据库、测试环境

bash

pip install sqlite3  # 已内置到Python中

SQL Server

Best for: Enterprise applications, Windows environments

bash

pip install pyodbc sqlalchemy

适用场景：企业级应用、Windows环境

bash

pip install pyodbc sqlalchemy

Query Writing

查询编写

Basic SELECT with JOINs

基础SELECT与JOIN查询

sql

-- 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;

sql

-- 带过滤条件的简单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;

Subqueries and CTEs

子查询与CTE

sql

-- 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;

sql

-- 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;

Window Functions

窗口函数

sql

-- 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.

sql

-- 分组内排名
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

。

Query Optimization

查询优化

Using EXPLAIN

使用EXPLAIN分析

sql

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

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;

-- 重点关注：
-- - 顺序扫描（性能差） vs 索引扫描（性能好）
-- - 高成本数值
-- - 处理的大量行数

Quick Optimization Tips

快速优化技巧

sql

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

sql

-- 不良写法：对索引列使用函数
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

。

Schema Design

架构设计

Normalization Principles

规范化原则

First Normal Form (1NF): Eliminate repeating groups, use atomic values

sql

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

sql

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

第一范式（1NF）：消除重复组，使用原子值

sql

-- 推荐：将订单项拆分到单独表
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）：所有非键属性完全依赖于主键

sql

-- 推荐：拆分产品信息到单独表
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）：消除传递依赖

Common Schema Patterns

常见架构模式

One-to-Many:

sql

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:

sql

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.

一对多关系：

sql

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)
);

多对多关系：

sql

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

。

Indexes and Performance

索引与性能

Creating Indexes

创建索引

sql

-- 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';

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';

Index Guidelines

索引使用指南

When to create indexes:

✅ Columns used in WHERE clauses
✅ Columns used in JOIN conditions
✅ Columns used in ORDER BY
✅ Foreign key columns

When NOT to create indexes:

❌ Small tables (< 1000 rows)
❌ Columns with low selectivity (boolean fields)
❌ Columns frequently updated

For detailed index strategies, see

references/indexes-performance.md

适合创建索引的场景：

✅ WHERE子句中使用的列
✅ JOIN条件中使用的列
✅ ORDER BY中使用的列
✅ 外键列

不适合创建索引的场景：

❌ 小表（少于1000行）
❌ 低选择性列（如布尔字段）
❌ 频繁更新的列

详细的索引策略请查看

references/indexes-performance.md

。

Migrations

迁移脚本

Safe Migration Pattern

安全迁移模式

sql

-- 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;

sql

-- 步骤1：添加可空列
ALTER TABLE users ADD COLUMN status VARCHAR(20);

-- 步骤2：填充现有行数据
UPDATE users SET status = 'active' WHERE status IS NULL;

-- 步骤3：设置为非空
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;

Zero-Downtime Migrations

零停机迁移

sql

-- 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.

sql

-- 推荐：先添加可空列，再回填数据
ALTER TABLE large_table ADD COLUMN new_column VARCHAR(100);

-- 分批回填数据
UPDATE large_table SET new_column = 'value' WHERE new_column IS NULL LIMIT 1000;
-- 重复执行直到完成

-- 然后设置为非空
ALTER TABLE large_table ALTER COLUMN new_column SET NOT NULL;

更多迁移模式请查看

examples/migrations.sql

。

Advanced Patterns

高级模式

UPSERT (Insert or Update)

UPSERT（插入或更新）

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

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

Recursive CTEs

递归CTE

sql

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

sql

-- 层级数据遍历
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

。

Best Practices

最佳实践

Critical Guidelines

关键准则

Always use parameterized queries to prevent SQL injection
Use transactions for related operations to ensure atomicity
Add appropriate constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, CHECK)
Include timestamps (created_at, updated_at) on tables
Use meaningful names for tables and columns
**Avoid SELECT *** - specify only needed columns
Index foreign keys for join performance
Use VARCHAR instead of CHAR for variable-length strings
Handle NULL values properly with IS NULL / IS NOT NULL
Use appropriate data types (DECIMAL for money, not FLOAT)

Example with multiple best practices:

sql

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

始终使用参数化查询，防止SQL注入
相关操作使用事务，确保原子性
添加合适的约束（主键、外键、非空、检查约束）
表中包含时间戳字段（created_at、updated_at）
使用有意义的命名（表和列名）
**避免使用SELECT *** - 仅指定需要的列
外键添加索引，提升连接性能
可变长度字符串使用VARCHAR而非CHAR
正确处理NULL值，使用IS NULL / IS NOT NULL
选择合适的数据类型（金额用DECIMAL，而非FLOAT）

包含多项最佳实践的示例：

sql

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

。

Common Pitfalls

常见陷阱

Watch out for these frequent issues:

N+1 Query Problem - Use JOINs instead of loops with queries
Not using LIMIT for exploratory queries on large tables
Implicit type conversions preventing index usage
Using COUNT(*) when EXISTS is sufficient
Not handling NULLs properly (NULL = NULL is always NULL, not TRUE)
Using SELECT DISTINCT as a band-aid instead of fixing the query
Forgetting transactions for related operations
Using functions on indexed columns preventing index usage

Example - Avoiding N+1:

python

undefined

注意以下常见问题：

N+1查询问题 - 使用JOIN而非循环查询
大表探索查询未使用LIMIT
隐式类型转换导致索引失效
可用EXISTS时使用COUNT(*)
NULL值处理不当（NULL = NULL始终为NULL，而非TRUE）
用SELECT DISTINCT掩盖查询问题
相关操作未使用事务
对索引列使用函数导致索引失效

避免N+1问题的示例：

python

-- 不良写法：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

。

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)

辅助脚本（

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
```
- 常见错误及避免方法

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`.

---

基础查询可使用上述示例模式
优化查询请从EXPLAIN开始，并查看
```
references/query-optimization.md
```
架构设计请参考规范化模式，并查看
```
examples/schema_examples.sql
```
复杂场景请查看
```
references/advanced-patterns.md
```
工具类操作请使用
```
scripts/sql_helper.py
```

Helper Scripts and Examples

工作流程

Available Resources

—

Helper Scripts (

scripts/

```
sql_helper.py
```
- Utility functions for query building, schema introspection, index analysis, and migration helpers

Examples (

examples/

```
complex_queries.sql
```
- Advanced query patterns with CTEs, window functions, and subqueries
```
schema_examples.sql
```
- Complete schema design examples for various use cases
```
migrations.sql
```
- Safe migration patterns and zero-downtime techniques

References (

references/

```
query-optimization.md
```
- Comprehensive query optimization techniques and EXPLAIN analysis
```
indexes-performance.md
```
- Detailed index strategies, maintenance, and monitoring
```
advanced-patterns.md
```
- UPSERT, bulk operations, pivot tables, JSON operations, recursive queries
```
best-practices.md
```
- Complete SQL best practices guide
```
common-pitfalls.md
```
- Common mistakes and how to avoid them

处理SQL数据库时的流程：

理解需求 - 需要查询或存储哪些数据？
设计架构 - 应用规范化原则，选择合适的数据类型
创建索引 - 为外键和频繁查询的列添加索引
编写查询 - 从简单开始，逐步增加复杂度
优化查询 - 使用EXPLAIN识别瓶颈
测试验证 - 用示例数据和边缘场景验证
文档注释 - 为复杂查询添加注释

迁移流程：

规划变更 - 识别受影响的表和依赖关系
编写迁移脚本 - 同时创建正向和回滚脚本
副本测试 - 先在开发数据库测试
备份数据 - 执行迁移前务必备份
执行迁移 - 在低流量时段执行
验证结果 - 迁移后检查数据完整性

Quick Start

—

For basic queries, use the patterns shown above
For optimization, start with EXPLAIN and check
```
references/query-optimization.md
```
For schema design, review normalization patterns and see
```
examples/schema_examples.sql
```
For complex scenarios, check
```
references/advanced-patterns.md
```
For utilities, use
```
scripts/sql_helper.py
```

—

Workflow

—

When working with SQL databases:

Understand requirements - What data needs to be queried or stored?
Design schema - Apply normalization, choose appropriate data types
Create indexes - Index foreign keys and frequently queried columns
Write queries - Start simple, add complexity as needed
Optimize - Use EXPLAIN to identify bottlenecks
Test - Verify with sample data and edge cases
Document - Add comments for complex queries

For migrations:

Plan changes - Identify affected tables and dependencies
Write migration - Create both up and down migrations
Test on copy - Test on development database first
Backup - Always backup before running migrations
Execute - Run migrations during low-traffic periods
Verify - Check data integrity after migration

—