SQL 学习笔记

SQL 是用于定义、查询和操作关系型数据的声明式语言。编写 SQL 时，应先明确目标结果集，再考虑连接、过滤、聚合和执行计划。不同数据库在函数、过程语法和事务行为上存在差异，本文以通用 SQL 为主，并在流程控制部分说明 MySQL 风格语法。

SQL 语句分类

DDL：定义数据库对象，例如 CREATE、ALTER 和 DROP。
DML：写入或修改数据，例如 INSERT、UPDATE 和 DELETE。
DQL：查询数据，主要指 SELECT。
DCL：控制权限，例如 GRANT 和 REVOKE。
TCL：控制事务，例如 COMMIT 和 ROLLBACK。

flowchart LR
    A[业务问题] --> B[确定目标列]
    B --> C[确定数据来源与连接]
    C --> D[使用 WHERE 过滤行]
    D --> E[分组与聚合]
    E --> F[使用 HAVING 过滤分组]
    F --> G[排序与限制结果]
    G --> H[检查执行计划]

基础查询顺序

SELECT department_id, COUNT(*) AS employee_count
FROM employees
WHERE status = 'ACTIVE'
GROUP BY department_id
HAVING COUNT(*) >= 5
ORDER BY employee_count DESC
LIMIT 10;

逻辑处理顺序通常可理解为 FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY 和 LIMIT。该顺序解释了为何大多数数据库不能在同一层的 WHERE 中直接引用 SELECT 定义的别名。

CASE WHEN 与 IF THEN

CASE 是表达式，用于在查询或更新语句中根据条件返回一个值。它可以出现在 SELECT、ORDER BY、UPDATE 和聚合表达式中。

SELECT
    book_name,
    CASE
        WHEN sale_price > price THEN '高于原价'
        WHEN sale_price = price THEN '等于原价'
        ELSE '低于原价'
    END AS price_comparison
FROM books;

条件聚合是 CASE 的常见用途：

SELECT
    department_id,
    SUM(CASE WHEN status = 'ACTIVE' THEN 1 ELSE 0 END) AS active_count
FROM employees
GROUP BY department_id;

IF ... THEN 是存储过程、函数或触发器中的流程控制语句，不能作为标准 SQL 表达式直接放入普通查询。以下为 MySQL 风格示例：

IF total_amount >= 1000 THEN
    SET discount_rate = 0.10;
ELSEIF total_amount >= 500 THEN
    SET discount_rate = 0.05;
ELSE
    SET discount_rate = 0;
END IF;

部分数据库提供 IF 函数，例如 MySQL 的 IF(condition, value1, value2)，但它属于方言功能。需要跨数据库迁移时，应优先使用标准 CASE。

表结构与元数据

MySQL 可使用以下命令查看表结构：

1 2	DESCRIBE table_name; SHOW CREATE TABLE table_name;

DESCRIBE 适合快速查看字段、类型、空值与索引信息。SHOW CREATE TABLE 能看到完整建表语句，更适合核对默认值、约束、字符集和存储引擎。跨数据库场景可查询 information_schema.columns。

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema = 'app_db'
  AND table_name = 'users'
ORDER BY ordinal_position;

连接查询

INNER JOIN 只返回两侧满足连接条件的记录。
LEFT JOIN 保留左表全部记录，右表无匹配时返回空值。
FULL OUTER JOIN 保留两侧全部记录，但并非所有数据库都直接支持。
CROSS JOIN 返回笛卡尔积，使用时应明确结果规模。

SELECT o.id, o.created_at, u.name
FROM orders AS o
JOIN users AS u ON u.id = o.user_id
WHERE o.created_at >= '2026-01-01';

连接条件应写在 ON 中，结果过滤通常写在 WHERE 中。对 LEFT JOIN 的右表字段在 WHERE 中进行非空过滤，会使查询效果接近 INNER JOIN，这是常见逻辑错误。

事务

事务的核心目标是让一组相关修改整体成功或整体失败。

START TRANSACTION;

UPDATE accounts
SET balance = balance - 100
WHERE id = 1 AND balance >= 100;

UPDATE accounts
SET balance = balance + 100
WHERE id = 2;

COMMIT;

实际转账还必须检查第一条更新影响的行数，并对账户行使用适当锁策略。事务应保持简短，避免在事务中等待外部接口或用户输入。

索引与性能

复合索引的列顺序应根据等值过滤、范围过滤、排序和选择性综合确定。索引可以减少扫描量，但会增加写入成本和存储占用。

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

EXPLAIN
SELECT id, created_at
FROM orders
WHERE user_id = 42
  AND created_at >= '2026-01-01';

优化时应依据执行计划和真实数据分布，重点观察扫描行数、连接方式、临时表和排序操作。避免对索引列进行不必要的函数计算，也要避免使用 SELECT * 读取无关大字段。

安全要求

应用程序必须使用参数化查询，不能通过字符串拼接构造用户输入。

String sql = "SELECT id, name FROM users WHERE email = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, email);
    try (ResultSet result = statement.executeQuery()) {
        // Process the result set.
    }
}