更新于：2023-12-31

SQL 入门

当前期刊数: 231

本系列是 SQL 系列的开篇，介绍一些宏观与基础的内容。

SQL 是什么？

SQL 是一种结构化查询语言，用于管理关系型数据库，我们 90% 接触的都是查询语法，但其实它包含完整的增删改查和事物处理功能。

声明式特性

SQL 属于声明式编程语言，而现代通用编程语言一般都是命令式的。但是不要盲目崇拜声明式语言，比如说它未来会代替低级的命令式语言，因为声明式本身也有它的缺点，它与命令式语言也有相通的地方。

为什么我们觉得声明式编程语言更高级？因为声明式语言抽象程度更高，比如 select * from table1 仅描述了要从 table1 查询数据，但查询的具体步骤的完全没提，这背后可能存在复杂的索引优化与锁机制，但我们都无需关心，这简直是编程的最高境界。

那为什么现在所有通用业务代码都是命令式呢？因为 命令式给了我们描述具体实现的机会 ，而通用领域的编程正需要建立在严谨的实现细节上。比如校验用户权限这件事，即便 AI 编程提供了将 “登陆用户仅能访问有权限的资源” 转化为代码的能力，我们也不清楚资源具体指哪些，以及在权限转移过程中的资源所有权属于谁。

SQL 之所以能保留声明式特性，完全因为锁定了关系型数据管理这个特定领域，而恰恰对这个领域的需求是标准化且可枚举的，才使声明式成为可能。

基于命令式语言也完全可拓展出声明式能力，比如许多 ORM 提供了类似 select({}).from({}).where({}) 之类的语法，甚至一个 login() 函数也是声明式编程的体现，因为调用者无需关心是如何登陆的，总之调用一下就完成了登陆，这不就是声明式的全部精髓吗？

语法分类

作为关系型数据库管理工具，SQL 需要定义、操纵与控制数据。

数据定义即修改数据库与表级别结构，这些是数据结构，或者是数据元信息，它不代表具体数据，但描述数据的属性。

数据操纵即修改一行行具体数据，增删改查。

数据控制即对事务、用户权限的管理与控制。

数据定义

DDL（Data Definition Language）数据定义，包括 CREATE DROP ALTER 方法。

数据操纵

DML（Data Manipulation Language）数据操纵，包括 SELECT INSERT UPDATE DELETE 方法。

数据控制

DCL（Data Control Language）数据控制，包括 COMMIT、ROLLBACK 等。

所有 SQL 操作都围绕这三种类型，其中数据操纵几乎占了 90% 的代码量，毕竟数据查询的诉求远大于写，数据写入对应数据采集，而数据查询对应数据分析，数据分析领域能玩出的花样远比数据采集要多。

PS：有些情况下，会把最重要的 SELECT 提到 DQL（Data Query Language）分类下，这样分类就变成了四个。

集合运算

SQL 世界的第一公民是集合，就像 JAVA 世界第一公民是对象。我们只有以集合的视角看待 SQL，才能更好的理解它。

何为集合视角，即所有的查询、操作都是二维数据结构中进行的，而非小学算术里的单个数字间加减乘除关系。

集合的运算一般有 UNION 并集、EXCEPT 差集、INTERSECT 交集，这些都是以行为单位的操作，而各种 JOIN 语句则是以列为单位的集合运算，也是后面提到的连接查询。

只要站在二维数据结构中进行思考，运算无非是横向或纵向的操作。

数据范式

数据范式分为五层，每层要求都比上一层更严苛，因此是一个可以逐步遵循的范式。数据范式要求数据越来越解耦，减少冗余。

比如第一范式要求每列都具有原子性，即都是不可分割的最小数据单元。如果数据采集时，某一列作为字符串存储，并且以 “|” 分割表示省市区，那么它就不具有原子性。

当然实际生产过程往往不都遵循这种标准，因为表不是孤立的，在数据处理流中，可能在某个环节再把列原子化，而原始数据为了压缩体积，进行列合并处理。

希望违反范式的还不仅是底层表，现在大数据处理场景下，越来越多的业务采用大宽表结构，甚至故意进行数据冗余以提升查询效率，列存储引擎就是针对这种场景设计的，所以数据范式在大数据场景下是可以变通的，但依然值得学习。

聚合

当采用 GROUP BY 分组聚合数据时，如希望针对聚合值筛选，就不能用 WHERE 限定条件了，因为 WHERE 是基于行的筛选，而不是针对组合的。（GROUP BY 对数据进行分组，我们称这些组为 “组合”），所以需要使用针对组合的筛选语句 HAVING：

1
2
3

SELECT SUM(pv) FROM table
GROUP BY city
HAVING AVG(uv) > 100

这个例子中，如果 HAVING 换成 WHERE 就没有意义，因为 WHERE 加聚合条件时，需要对所有数据进行合并，不符合当前视图的详细级别。（关于视图详细级别，在我之前写的精读《什么是 LOD 表达式》有详细说明）。

聚合如此重要，是因为我们分析数据必须在高 LEVEL 视角看，明细数据是看不出趋势的。而复杂的需求往往伴随着带有聚合的筛选条件，明白 SQL 是如何支持的非常重要。

CASE 表达式

CASE 表达式分为简单与搜索 CASE 表达式，简单表达式：

1	`SELECT CASE pv WHEN 1 THEN 'low' ELSE 'high' END AS quality`

上面的例子利用 CASE 简单表达式形成了一个新字段，这种模式等于生成了业务自定义临时字段，在对当前表进行数据加工时非常有用。搜索 CASE 表达式能力完全覆盖简单 CASE 表达式：

1	`SELECT CASE WHEN pv < 100 THEN 'low' ELSE 'high' END AS quality`

可以看到，搜索 CASE 表达式可以用 “表达式” 描述条件，可以轻松完成更复杂的任务，甚至可以在表达式里使用子查询、聚合等手段，这些都是高手写 SQL 的惯用技巧，所以 CASE 表达式非常值得深入学习。

复杂查询

SELECT 是 SQL 最复杂的部分，其中就包含三种复杂查询模式，分别是连接查询与子查询。

连接查询

指 JOIN 查询，比如 LEFT JOIN、RIGHT JOIN、INNER JOIN。

在介绍聚合时我们提到了，连接查询本质上就是对列进行拓展，而两个表之间不会无缘无故合成一个，所以必须有一个外键作为关系纽带：

1
2
3

SELECT A.pv, B.uv
FROM table1 as t1 LEFT JOIN table2 AS P t2
ON t1.productId = t2.productId

连接查询不仅拓展了列，还会随之拓展行，而拓展方式与连接的查询的类型有关。除了连接查询别的表，还可以连接查询自己，比如：

1 2	`SELECT t1.pv AS pv1, P2.pv AS pv2 FROM tt t1, tt t2`

这种子连接查询结果就是自己对自己的笛卡尔积，可通过 WHERE 筛选去重，后面会有文章专门介绍。

子查询与视图

子查询就是 SELECT 里套 SELECT，一般来说 SELECT 会从内到外执行，只有在关联子查询模式下，才会从外到内执行。

而如果把子查询保存下来，就是一个视图，这个视图并不是实体表，所以很灵活，且数据会随着原始表数据而变化：

CREATE VIEW countryGDP (country, gdp)
AS
SELECT country, SUM(gdp)
FROM tt
GROUP BY country

之后 countryGDP 这个视图就可以作为临时表来用了。

这种模式其实有点违背 SQL 声明式的特点，因为定义视图类似于定义变量，如果继续写下去，势必会形成一定命令式思维逻辑，但这是无法避免的。

事务

当 SQL 执行一连串操作时，难免遇到不执行完就会出现脏数据的问题，所以事务可以保证操作的原子性。一般来说每个 DML 操作都是一个内置事务，而 SQL 提供的 START TRANSACTION 就是让我们可以自定义事务范围，使一连串业务操作都可以包装在一起，成为一个原子性操作。

对 SQL 来说，原子性操作是非常安全的，即失败了不会留下任何痕迹，成功了会全部成功，不会存在中间态。

OLAP

OLAP（OnLine Analytical Processing）即实时数据分析，是 BI 工具背后计算引擎实现的基础。

现在越来越多的 SQL 数据库支持了窗口函数实现，用于实现业务上的 runningSum 或 runningAvg 等功能，这些都是数据分析中很常见的。

以 runningSum 为例，比如双十一实时表的数据是以分钟为单位的实时 GMV，而我们要做一张累计到当前时间的 GMV 汇总折线图，Y 轴就需要支持 running_sum(GMV) 这样的表达式，而这背后可能就是通过窗口函数实现的。

当然也不是所有业务函数都由 SQL 直接提供，业务层仍需实现大量内存函数，在 JAVA 层计算，这其中一部分是需要下推到 SQL 执行的，只有内存函数与下推函数结合在一起，才能形成我们在 BI 工具看到的复杂计算字段效果。

总结

SQL 是一种声明式语言，一个看似简单的查询语句，在引擎层往往对应着复杂的实现，这就是 SQL 为何如此重要却又如此普及的原因。

虽然 SQL 容易上手，但要系统的理解它，还得从结构化数据与集合的概念开始进行思想转变。

不要小看 CASE 语法，它不仅与容易与编程语言的 CASE 语法产生混淆，本身结合表达式进行条件分支判断，是许多数据分析师在日常工作中最长用的套路。

现在使用简单 SQL 创建应用的场景越来越少了，但 BI 场景下，基于 SQL 的增强表达式场景越来越多了，本系列我就是以理解 BI 场景下查询表达式为目标创建的，希望能够学以致用。

讨论地址是：精读《SQL 入门》· Issue ##398 · ascoders/weekly

如果你想参与讨论，请点击这里，每周都有新的主题，周末或周一发布。前端精读 - 帮你筛选靠谱的内容。

关注 前端精读微信公众号

版权声明：自由转载-非商用-非衍生-保持署名（创意共享 3.0 许可证）

SQL 入门