⚡

SQL数据库专家 sql-expert

SQL数据库专家技能提供全面的SQL查询编写、优化和数据库设计指导。涵盖PostgreSQL、MySQL、SQLite、SQL Server等主流数据库，支持复杂查询编写、性能优化、模式设计、索引创建、安全迁移和错误调试。关键词：SQL查询优化、数据库设计、索引策略、性能调优、SQL迁移、数据库管理、PostgreSQL、MySQL、SQL Server、数据查询。

后端开发 0 次安装 0 次浏览 更新于 3/2/2026

name: sql-expert description: “SQL查询编写、优化和数据库模式设计专家，支持PostgreSQL、MySQL、SQLite和SQL Server。适用于以下数据库工作：(1) 编写包含连接、子查询和窗口函数的复杂SQL查询，(2) 优化慢查询和分析执行计划，(3) 设计符合规范化的数据库模式，(4) 创建索引和改进查询性能，(5) 编写迁移和处理模式变更，(6) 调试SQL错误和查询问题”

SQL专家技能

为PostgreSQL、MySQL、SQLite和SQL Server提供SQL编写、优化和管理的专家指导。

核心能力

此技能使您能够：

  • 编写复杂SQL查询，包含JOIN、子查询、CTE和窗口函数
  • 优化慢查询，使用EXPLAIN计划和索引建议
  • 设计数据库模式，符合规范化（1NF、2NF、3NF、BCNF）
  • 创建有效索引以提升查询性能
  • 编写数据库迁移，支持安全回滚
  • 调试SQL错误并理解错误消息
  • 处理事务，使用适当的隔离级别
  • 处理JSON/JSONB数据类型
  • 生成测试数据用于测试
  • 转换数据库方言（PostgreSQL ↔ MySQL ↔ SQLite）

支持的数据库系统

PostgreSQL

最适合：复杂查询、JSON数据、高级功能、ACID合规

pip install psycopg2-binary sqlalchemy

MySQL/MariaDB

最适合：Web应用、WordPress、高读取负载

pip install mysql-connector-python sqlalchemy

SQLite

最适合：本地开发、嵌入式数据库、测试

pip install sqlite3  # Python内置

SQL Server

最适合：企业应用、Windows环境

pip install pyodbc sqlalchemy

查询编写

基础SELECT与JOIN

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

子查询与CTE

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

-- 分析查询性能
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 索引扫描（好）
-- - 高成本数字
-- - 处理的大行数

快速优化技巧

-- 差：在索引列上使用函数
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';

索引指南

何时创建索引：

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

何时不创建索引：

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

详细索引策略见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。

高级模式

UPSERT（插入或更新）

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

递归CTE

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

最佳实践

关键指南

  1. 始终使用参数化查询以防止SQL注入
  2. 相关操作使用事务以确保原子性
  3. 添加适当的约束（主键、外键、非空、检查）
  4. 包含时间戳（created_at、updated_at）在表中
  5. 使用有意义的名称用于表和列
  6. **避免SELECT *** - 仅指定所需列
  7. 索引外键以提升连接性能
  8. 使用VARCHAR而非CHAR用于变长字符串
  9. 正确处理NULL值使用IS NULL / IS NOT NULL
  10. 使用适当的数据类型（DECIMAL用于货币，非FLOAT）

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

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。

常见陷阱

注意这些常见问题：

  1. N+1查询问题 - 使用JOIN而非循环查询
  2. 未使用LIMIT用于大表的探索性查询
  3. 隐式类型转换阻止索引使用
  4. 使用COUNT(*)而EXISTS已足够
  5. 未正确处理NULL（NULL = NULL总是NULL，非TRUE）
  6. 使用SELECT DISTINCT作为权宜之计而非修复查询
  7. 忘记事务用于相关操作
  8. 在索引列上使用函数阻止索引使用

示例 - 避免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 - 常见错误及如何避免

快速开始

  1. 基础查询使用上述模式
  2. 优化从EXPLAIN开始并查看references/query-optimization.md
  3. 模式设计查看规范化模式和examples/schema_examples.sql
  4. 复杂场景查看references/advanced-patterns.md
  5. 实用工具使用scripts/sql_helper.py

工作流程

处理SQL数据库时：

  1. 理解需求 - 需要查询或存储什么数据？
  2. 设计模式 - 应用规范化，选择适当数据类型
  3. 创建索引 - 索引外键和频繁查询的列
  4. 编写查询 - 从简单开始，根据需要增加复杂性
  5. 优化 - 使用EXPLAIN识别瓶颈
  6. 测试 - 使用样本数据和边界情况验证
  7. 文档 - 为复杂查询添加注释

迁移时：

  1. 计划变更 - 识别受影响表和依赖关系
  2. 编写迁移 - 创建向上和向下迁移
  3. 在副本上测试 - 先在开发数据库上测试
  4. 备份 - 运行迁移前始终备份
  5. 执行 - 在低流量期间运行迁移
  6. 验证 - 迁移后检查数据完整性