SQL 编程思想 - 董旭阳

本书基于作者十多年的工作经验和分享，全面覆盖了从 SQL 基础查询到高级分析、从数据库设计到查询优化等内容，采用了最新的 SQL:2019 标准。

关于作者

董旭阳 是数据库领域的资深专家：

• 数据库架构师：拥有十多年数据库设计和优化经验
• 技术讲师：在各大技术会议分享 SQL 和数据库相关主题
• SQL 标准追随者：致力于推广最新 SQL 标准的应用

作者结合多年实际工作经验，将 SQL 编程的核心思想和最佳实践融入本书，帮助读者建立正确的 SQL 思维方式。

核心内容

1. SQL 编程范式

-- 集合思维 vs 过程思维

-- 过程思维（不推荐）：逐行处理
DECLARE cursor CURSOR FOR SELECT id FROM users;
OPEN cursor;
FETCH NEXT FROM cursor INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    -- 处理每一行
    FETCH NEXT FROM cursor INTO @id;
END

-- 集合思维（推荐）：一次性处理
UPDATE users
SET status = 'active'
WHERE last_login > DATEADD(day, -30, GETDATE());

2. 连接 (JOIN) 详解

-- INNER JOIN: 只返回匹配的行
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

-- LEFT JOIN: 返回左表所有行，右表不匹配则为 NULL
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- RIGHT JOIN: 返回右表所有行，左表不匹配则为 NULL
SELECT o.order_id, c.customer_name
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.customer_id;

-- FULL OUTER JOIN: 返回两表所有行
SELECT a.customer_id AS customer_a, b.customer_id AS customer_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

-- CROSS JOIN: 笛卡尔积
SELECT * FROM colors CROSS JOIN sizes;

-- SELF JOIN: 表与自身连接
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

3. 子查询与 CTE

-- 子查询
SELECT customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);

-- 相关子查询
SELECT c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
    AND o.order_date > '2023-01-01'
);

-- CTE (公用表表达式)
WITH HighValueCustomers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE total_spent > 10000
),
RecentOrders AS (
    SELECT customer_id, order_id, order_date
    FROM orders
    WHERE order_date > '2023-01-01'
)
SELECT h.customer_name, r.order_id
FROM HighValueCustomers h
JOIN RecentOrders r ON h.customer_id = r.customer_id;

-- 递归 CTE
WITH RECURSIVE OrgHierarchy AS (
    -- 基础情况：顶级节点
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 递归情况：子节点
    SELECT e.id, e.name, e.manager_id, oh.level + 1
    FROM employees e
    JOIN OrgHierarchy oh ON e.manager_id = oh.id
)
SELECT * FROM OrgHierarchy;

4. 窗口函数

-- ROW_NUMBER: 行号
SELECT
    customer_id,
    order_id,
    order_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_amount DESC) AS rn
FROM orders;

-- RANK / DENSE_RANK: 排名
SELECT
    customer_id,
    order_amount,
    RANK() OVER (ORDER BY order_amount DESC) AS rank,
    DENSE_RANK() OVER (ORDER BY order_amount DESC) AS dense_rank
FROM orders;

-- LAG / LEAD: 前后行比较
SELECT
    order_date,
    order_amount,
    LAG(order_amount, 1) OVER (ORDER BY order_date) AS prev_amount,
    LEAD(order_amount, 1) OVER (ORDER BY order_date) AS next_amount,
    order_amount - LAG(order_amount, 1) OVER (ORDER BY order_date) AS diff
FROM orders;

-- 累计聚合
SELECT
    order_date,
    order_amount,
    SUM(order_amount) OVER (ORDER BY order_date) AS running_total,
    AVG(order_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM orders;

-- NTILE: 分组
SELECT
    customer_id,
    order_amount,
    NTILE(4) OVER (ORDER BY order_amount DESC) AS quartile
FROM orders;

5. 索引与查询优化

-- 索引类型
-- 1. B-Tree 索引：最常用，适合范围查询
CREATE INDEX idx_customer_name ON customers(name);

-- 2. 哈希索引：适合等值查询
CREATE INDEX idx_user_email ON users USING HASH(email);

-- 3. 复合索引：多列组合
CREATE INDEX idx_order ON orders(customer_id, order_date);

-- 4. 覆盖索引：包含查询所需所有列
CREATE INDEX idx_covering ON orders(customer_id) INCLUDE (order_amount, order_date);

-- 5. 唯一索引
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- 查询优化原则
-- 1. 避免 SELECT *
SELECT id, name, email FROM users;  -- 推荐
SELECT * FROM users;                -- 不推荐

-- 2. 避免在索引列上使用函数
WHERE YEAR(order_date) = 2023;                 -- 不推荐，索引失效
WHERE order_date >= '2023-01-01'
  AND order_date < '2024-01-01';               -- 推荐，索引有效

-- 3. 使用 EXISTS 替代 IN (子查询)
WHERE customer_id IN (SELECT customer_id FROM vip_customers);   -- 可能较慢
WHERE EXISTS (SELECT 1 FROM vip_customers v WHERE v.customer_id = customers.customer_id);  -- 通常更快

-- 4. 避免隐式类型转换
WHERE customer_id = '123';   -- 如果 customer_id 是 INT，会导致类型转换
WHERE customer_id = 123;     -- 推荐

-- 5. 使用 EXPLAIN 分析执行计划
EXPLAIN SELECT * FROM orders WHERE customer_id = 123;

6. 事务与锁

-- 事务 ACID 特性
-- Atomicity (原子性): 事务要么全部成功，要么全部失败
-- Consistency (一致性): 事务执行前后数据保持一致
-- Isolation (隔离性): 并发事务互不干扰
-- Durability (持久性): 事务提交后持久保存

-- 事务控制
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;

COMMIT;  -- 或 ROLLBACK

-- 隔离级别
-- 1. READ UNCOMMITTED: 允许脏读
-- 2. READ COMMITTED: 避免脏读，允许不可重复读
-- 3. REPEATABLE READ: 避免不可重复读，允许幻读
-- 4. SERIALIZABLE: 完全隔离，性能最低

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 锁类型
-- 共享锁 (S): 读锁，多个事务可同时持有
-- 排他锁 (X): 写锁，同一时间只有一个事务可持有
-- 意向锁 (IS/IX): 表明事务想要获取更低级别的锁

-- 显式锁
SELECT * FROM accounts WITH (HOLDLOCK) WHERE account_id = 1;
SELECT * FROM accounts WITH (UPDLOCK) WHERE account_id = 1;
SELECT * FROM accounts WITH (XLOCK) WHERE account_id = 1;

7. 数据库设计范式

第一范式 (1NF): 列不可再分
-- 不符合：address 列包含"省市区街道"
-- 符合：拆分为 province, city, district, street 列

第二范式 (2NF): 满足 1NF + 非主键列完全依赖于主键
-- 不符合：订单明细表中包含商品信息（商品名、价格）
-- 符合：商品信息单独建表，订单明细表只保留商品 ID

第三范式 (3NF): 满足 2NF + 非主键列直接依赖于主键
-- 不符合：员工表中包含"部门名称"（依赖于部门 ID）
-- 符合：部门信息单独建表，员工表只保留部门 ID

BCNF: 满足 3NF + 主键列之间也互不依赖

范式化的优缺点：
+ 减少数据冗余
+ 避免更新异常
+ 数据一致性好
- 查询时需要 JOIN，可能影响性能

反范式化 (Denormalization):
+ 减少 JOIN，查询性能好
+ 适合读多写少的场景
- 数据冗余
- 可能存在更新异常

经典摘录

SQL 是声明式语言，告诉数据库"要什么"，而不是"怎么做"。

集合思维是 SQL 编程的核心。学会用集合的方式思考问题，是写好 SQL 的关键。

** premature optimization is the root of all evil. 先写出正确的 SQL，再根据执行计划优化。**

索引不是越多越好。合适的索引 + 正确的查询写法 = 高性能。

理解事务和锁机制，是编写高并发应用的基础。

读书心得

《SQL 编程思想》是一本注重实战的 SQL 进阶书籍。作者结合多年工作经验，将 SQL 编程的核心思想和最佳实践娓娓道来。

书中对我影响最深的是集合思维的概念。从过程式思维转向集合式思维，是写好 SQL 的关键。书中通过大量对比示例，展示了如何用集合的方式思考和解决问题。

窗口函数部分也非常实用。ROW_NUMBER、RANK、LAG/LEAD 等函数，让很多复杂的分析查询变得简单优雅。在实际工作中，我曾使用窗口函数优化了多个报表查询，性能提升显著。

查询优化部分的讲解也很到位。EXPLAIN 工具的使用、索引失效的常见场景、子查询 vs JOIN 的选择等，都是日常开发中经常遇到的问题。

对于后端开发者来说，SQL 是必备技能。这本书不仅能帮助你写出正确的 SQL，更能帮助你写出高性能、可维护的 SQL。

强烈推荐给需要与数据库打交道的每一位开发者。