格式化 SQL

SQL（结构化查询语言）是地球上几乎每个重要的关系数据库都使用的通用语言——从 PostgreSQL、 MySQL和 SQLite 到 SQL Server 和 Oracle 等商业系统。它允许您定义数据结构、存储数据、高效查询并强制执行有关该数据行为的规则。

下面是对 SQL 的深入介绍，大约 2,500 字：它是什么、如何工作以及如何在真实系统中很好地使用它。

1. SQL 是什么（以及为什么它仍然重要）

SQL 是一种声明式语言：您描述什么 数据您想要，数据库决定如何获取它。 W3Schools 上的 SQL 指南 和 W3Schools.in SQL 系列 等教程将 SQL 介绍为跨多个数据库引擎存储、操作和检索数据的标准语言。

因为 SQL 是标准化的且得到广泛支持，您可以在以下环境中使用相同的心理模型：

• MySQL 或 PostgreSQL 上的 Web 应用程序
• 使用 SQLite 的嵌入式应用程序
• SQL Server 或 Oracle 等企业系统

大多数日常业务数据——订单、用户、支付、日志、分析——最终存储在您可以用 SQL 查询的表中。

2. SQL 标准和方言

SQL 由 ISO/IEC 9075 标准系列标准化，该系列定义了核心语言和许多可选扩展。 标准已通过 SQL-86、SQL-92、 SQL:1999、SQL:2003 到 SQL:2023 等版本演变。ANSI 的 SQL 标准 ISO/IEC 9075:2023 摘要等文章解释了 ANSI 和 ISO 标准如何随时间对齐。

标准本身分为多个部分，例如：

• 第 1 部分（框架）和第 2 部分（基础）
• API 的第 3 部分（调用级接口）
• 存储过程的第 4 部分（持久存储模块）
• 后续部分，如SQL/XML、SQL/MED和 甚至用于属性图查询的SQL/PGQ

您可以在 SQL 的 ISO 目录页面 和 Modern SQL 的标准部分分解 等资源中查看完整列表。

在实践中，每个数据库都实现自己的方言：

• PostgreSQL 添加了扩展，如高级数据类型和全文搜索， 在 PostgreSQL 手册 中记录。
• MySQL 有自己的 SQL 语句 和 函数 风格。
• SQLite 支持 SQL 的重要子集，具有一些独特的行为， 在其 SELECT 文档 中详细说明。

核心概念——表、行、列、连接、聚合——是可移植的，但 任何非平凡的系统都需要学习特定方言的怪癖和特性。

3. 关系基础：表、行、键

在核心，SQL 围绕关系模型设计： 数据存在于表（关系）中，其中每一行 代表一个事实，每一列描述一个属性。

典型元素包括：

• 表：例如 customers、orders
• 列：例如 id、name、 order_date、total_amount
• 主键：每行的唯一标识符（通常是 id）
• 外键：引用另一个表的主键的列 （例如 orders.customer_id → customers.id）

规范化指南——如 DigitalOcean 的规范化教程 或 freeCodeCamp 关于 1NF、2NF、3NF 的解释——将良好的关系设计框架为通过将数据分解为结构良好的表并用键链接它们来最小化冗余并防止更新异常。

当您使用 SQL 查询时，您基本上是在问数据库："从这些表中， 在这些条件下，我应该看到哪些行和列？"

4. 核心查询：SELECT

SELECT 语句是 SQL 的主力，可能是 最复杂的命令。 SQLite SELECT 参考 称其为"SQL 语言中最复杂的命令"，而 MySQL 的 SELECT 文档 和 SQLite Tutorial 的 SELECT 指南 等教程都逐步介绍了它的许多选项。

SELECT id, name
FROM customers;

关键部分：

• SELECT 列出列（或所有列的 *， 尽管通常最好明确）。
• FROM 选择一个或多个表。
• WHERE 过滤行。
• ORDER BY 排序结果。
• LIMIT 限制您看到的行数。

SELECT id, name, created_at
FROM customers
WHERE active = TRUE
ORDER BY created_at DESC
LIMIT 50;

W3Schools SQL 教程 和 W3Schools MySQL 对常见命令的概述 等入门教程使用 SELECT 来展示如何在单个、可读的表达式中从表中提取数据。

5. 过滤、排序和表达式

当您在查询中直接组合过滤和表达式时，SQL 表现出色：

SELECT
  id,
  total_amount,
  total_amount * 0.1 AS tax_estimate
FROM orders
WHERE status = 'paid'
  AND total_amount >= 100
ORDER BY total_amount DESC;

WHERE 子句可以使用比较运算符（=、<>、>、 <）、逻辑运算符（AND、OR、 NOT）、模式匹配（LIKE、 ILIKE）等。MySQL 等数据库记录了丰富的 内置函数和运算符 用于数字、字符串、日期/时间、JSON 和其他操作。

您还可以：

• 使用 IN 匹配列表： WHERE status IN ('paid', 'refunded')
• 使用 BETWEEN 表示范围： WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31'
• 使用 IS NULL / IS NOT NULL 处理缺失值

好的教程和手册强调表达式出现在许多 子句中——WHERE、ORDER BY、HAVING和 甚至 SELECT 本身。MySQL 文档在函数和运算符章节中的表达式求值部分强调了这一点。

6. 连接表

真实的数据库很少将所有内容保存在单个表中。相反，您 将数据规范化为多个表，并在查询时连接它们。 连接在 W3Schools 关于 SQL 连接的页面、 GeeksforGeeks 的连接教程、 TutorialsPoint 的连接概述 和 SQL Practice Online 上的 SQL 连接教程 等资源中得到了广泛介绍。

SELECT
  o.id,
  c.name,
  o.order_date,
  o.total_amount
FROM orders AS o
JOIN customers AS c
  ON c.id = o.customer_id;

常见的连接类型：

• INNER JOIN – 仅在两个表中都有匹配的行
• LEFT JOIN – 左侧表中的所有行，加上右侧的匹配
• RIGHT JOIN – 与左连接相反（在某些引擎如 SQLite 中不支持）
• FULL OUTER JOIN – 来自两侧的行，即使不匹配

DbSchema 的"SQL 连接解释"文章 或 LearnSQL.com 的连接示例 等可视化和代码密集的解释是建立对连接如何工作的强烈直觉的好方法。

7. 聚合、分组和窗口函数

要汇总数据——总和、平均值、计数——您使用 聚合函数（COUNT、SUM、 AVG、MIN、MAX）与 GROUP BY：

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(total_amount) >= 1000
ORDER BY total_spent DESC;

MySQL 的函数和运算符章节 和 PostgreSQL 的聚合和窗口函数文档 等参考资料列出了您可以在这些表达式中使用的内置函数。

现代 SQL 还支持窗口函数，允许您在仍返回 单个行的同时在可滑动的行"窗口"上计算聚合——例如，运行总和或排名。 窗口函数通过 SQL:1999 的修改进入 SQL，随后集成到 SQL:2003 中，如 SQL:1999 和 SQL:2003 标准 的注释中总结的那样。

SELECT
  customer_id,
  order_date,
  total_amount,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS running_total
FROM orders;

这种类型的查询对于在数据库中直接进行分析非常强大。

8. 数据定义：创建和发展模式

SQL 不仅用于查询；它还通过数据定义语言 (DDL)语句定义 数据的形状，在 PostgreSQL 的数据定义指南 和 MySQL 的 SQL 语句章节 等部分中得到了很好的记录。

常见的 DDL 命令：

• CREATE DATABASE my_app; – 创建数据库
• CREATE TABLE customers (...); – 定义表
• ALTER TABLE customers ADD COLUMN phone TEXT; – 更改表结构
• DROP TABLE customers; – 删除表

CREATE TABLE customers (
  id           SERIAL PRIMARY KEY,
  email        VARCHAR(255) UNIQUE NOT NULL,
  name         TEXT NOT NULL,
  created_at   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  active       BOOLEAN NOT NULL DEFAULT TRUE
);

需要注意的事项：

• 数据类型：整数、字符串、日期、布尔值、JSON 等
• 约束：PRIMARY KEY、 UNIQUE、NOT NULL、CHECK 表达式
• 默认值和生成的列

每个主要的 RDBMS 手册——PostgreSQL 的 SQL 语言部分、MySQL 的 参考手册和 SQLite 的 语言参考——强调仔细思考 DDL，因为模式决策以后很难更改。

9. 事务、并发和约束

SQL 数据库通常通过事务实现ACID属性—— 原子性、一致性、隔离性、持久性：

BEGIN;

UPDATE accounts
SET balance = balance - 200
WHERE id = 1;

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

COMMIT;

如果 BEGIN 和 COMMIT 之间有任何失败，事务 可以回滚以防止中间更改泄漏到数据库中。

MySQL 关于 事务和锁定语句 的部分涵盖 START TRANSACTION、COMMIT、 ROLLBACK 和保存点，而 PostgreSQL 关于 并发控制 和 事务管理 的章节解释了隔离级别和 MVCC 在底层如何工作。

约束——如外键或检查约束——是数据库 执行业务规则的方式：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers (id);

如果使用得当，它们将您的数据库变成不变量的守护者， 在源头防止无效数据，而不是仅仅依赖应用程序逻辑。

10. 模式设计和规范化

获得正确的模式通常比智能查询更重要。 规范化是通过结构化表来 减少冗余和提高一致性的过程。教程如：

• "SQL 中的数据库规范化——解释 1NF、2NF、3NF"
• DigitalOcean 的规范化指南
• GeeksforGeeks 关于 DBMS 中的范式
• Guru99 的 1NF–6NF 概述
• Visual Paradigm 的规范化级别指南

典型的进展：

• 1NF：没有重复组；每列包含原子值。
• 2NF：每个非键属性完全依赖于主键（对于复合键）。
• 3NF：没有传递依赖（非键属性不依赖于其他非键属性）。

过度规范化可能会损害性能并使查询复杂化；实用的 团队通常规范化到 3NF，然后在性能需要的地方选择性地反规范化 （例如，缓存聚合）。

11. 索引和查询性能

索引是使 SQL 查询快速的主要工具。它们是辅助 数据结构，允许数据库直接跳转到相关行，而不是扫描整个表。

一个备受推崇的索引资源是 Markus Winand 的 Use The Index, Luke!，这是一本完全专注于 SQL 性能和索引策略的免费在线书籍。网站的主页， UseTheIndexLuke.com，以及 "索引 LIKE 过滤器性能调优" 和 "更多索引，更慢的 INSERT" 等文章解释了索引何时有帮助以及何时有害。

关键思想：

• B-tree 索引加速相等和范围搜索（WHERE email = ...、 WHERE created_at >= ...）。
• 复合索引考虑列顺序——例如， (customer_id, created_at) 对于 WHERE customer_id = ? AND created_at >= ? 效果很好。
• 索引有助于带过滤器的 SELECT、UPDATE和 DELETE，但会减慢插入，因为每次插入都必须更新所有相关索引。

MSSQLTips 关于设计 SQL Server 索引的文章 等指南展示了索引中列的选择和顺序如何影响实际查询。

实用的经验法则：

• 索引您的主键和频繁的连接/过滤列。
• 避免索引每一列——过多的索引会杀死写入性能。
• 使用数据库的EXPLAIN/EXPLAIN ANALYZE 来查看查询的执行方式。

12. 现代 SQL 特性：CTE、JSON 等

虽然基础保持稳定，但现代 SQL 已经显著增长：

• 通过 WITH 的公共表表达式 (CTE) 允许更可读、更模块化的查询：

  WITH recent_orders AS (
    SELECT *
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '7 days'
  )
  SELECT customer_id, COUNT(*) 
  FROM recent_orders
  GROUP BY customer_id;

• JSON 支持允许您将半结构化数据与 关系表混合。PostgreSQL、MySQL 和其他系统在其手册中记录了广泛的 JSON 函数，例如 MySQL 的 JSON 特性列在 JSON 函数 下。
• 高级数据类型（数组、hstore、几何、 全文搜索）在 PostgreSQL 的数据类型和索引章节中 PostgreSQL 手册 中描述。

SQL 标准本身已添加了 XML、多维数组 和属性图查询的部分，如 ISO SQL 标准目录 和 ANSI SQL 标准文章 等关于 SQL 演变的文章中所记录的那样。

现代 SQL 足够强大，许多应用程序可以将复杂的 逻辑——层次结构、分析、事件处理——推送到数据库层。

13. 有效学习 SQL

因为 SQL 仍然是数据工作的基础，有一个丰富的 学习资源生态系统：

• 交互式教程，如 W3Schools SQL 教程 和 W3Schools.in SQL 课程索引 涵盖语法、查询和示例，带有浏览器内编辑器。
• 结构化培训，如 W3Schools SQL 培训 和 SQL Bootcamp 提供练习、自动评分和认证。
• 实践导向的网站，如 SQL Practice Online 的连接教程 和许多其他网站提供实际问题来解决。
• 专业资源，如 Use The Index, Luke! 专注于性能和索引，在基础 SQL 和生产级调优之间架起桥梁。

实用的学习策略：

1. 掌握 SELECT 和基本过滤。从简单的 查询开始，逐步添加 WHERE、ORDER BY和 LIMIT 层。
2. 熟悉连接。练习所有连接类型 直到您可以自信地预测结果。
3. 添加分组和聚合。学习使用 GROUP BY回答 "每月收入"或"前 10 名客户"等问题。
4. 学习模式设计。研究规范化并练习 建模真实系统——例如商店、预订系统、社交网络。
5. 探索您最喜欢的引擎的特性。阅读数据库的官方文档——PostgreSQL 的 SQL 语言部分、MySQL 的 参考手册或 SQLite 的 语言参考——以发现引擎特定的工具。
6. 最后，学习性能。使用 Use The Index, Luke! 和类似资源中的概念进行索引实验，并使用 EXPLAIN 分析查询。

如果您能够舒适地读写 SQL，您可以直接在生产中检查 数据，无需导出到电子表格即可构建报告，通过查看底层表来调试 应用程序逻辑，并与工程师和分析师更有效地协作。

14. 大局

SQL 处于一个独特的交叉点：它已有 40 多年的历史，但在当今云原生、分析密集的系统中仍然 深度相关。 ISO/IEC 9075 标准 及其许多部分继续发展，而 PostgreSQL、 MySQL和 SQLite 等开源数据库推动实用功能和性能改进。

如果您处理数据——开发者、分析师、数据科学家或产品经理——SQL 是让您直接向数据库提问的共享语言。值得学习一次，它会在您职业生涯的剩余时间里回报。