如何成为一名合格的 CRUD 工程师？

一九七零年，那是一个夏天。

有一位来自 IBM 圣约瑟研究实验室的高级研究员 Edgar Frank Codd 在 Communications of ACM 上发表了名为 A Relational Model of Data for Large Shared Data Banks 的文章，从而创建了关系数据模型。时至今日，基于该模型的关系数据库仍然是企业存储和处理数据的主要方式。甚至可以说，绝大多数 IT 系统都是围绕着数据库执行数据增删改查操作的。

目前主流的关系型数据库包括 MySQL、Oracle、Microsoft SQL Server、PostgreSQL 以及 SQLite 等。虽然这些数据库管理系统的具体实现有所不同，但它们都使用 SQL（Structured Query Language，结构化查询语言）作为访问和操作数据库的标准语言。

1

SQL1974 年，同样是来自 IBM 的 Donald D. Chamberlin 和 Raymond F. Boyce 基于关系模型开发了 SQL 的初始版本：SEQUEL（Structured English Query Language）。SEQUEL 被设计用于 IBM 最初的准关系数据库管理系统 SystemR。

1986 年，美国国家标准学会（ANSI）首先发布了 SQL 标准；随后 ISO 标准组织于 1987 年创建了“数据库语言 SQL”标准。在经历了 1989、1992、1996、1999、2003、2006、2008、2011、2016 以及 2019 年的多次修订之后，如今的 SQL 标准包含了大量的功能，内容多达数千页。以下是 SQL 发展过程中的一些关键节点：

SQL 是关系模式的第一个商业实现，同时也是最成功的一个实现。SQL 是访问和操作关系型数据库的标准语言，所有的关系型数据库都可以使用 SQL 语句进行数据访问和控制，许多大数据平台（包括 Flink、Spark、Hive 等）也都提供的 SQL 支持。

SQL 语句非常接近自然语言（英语），我们只需要掌握几个简单英文单词的作用，例如 SELECT、INSERT、UPDATE、DELETE 等，就可以完成绝大部分的数据库操作。例如，以下是一个简单的查询语句：

SELECT emp_id, emp_name, salaryFROM employeeWHERE salary >= 10000ORDER BY emp_id;即便对于没有学过 SQL 的初学者，我们只要知道几个英文单词的意思就不难理解该语句的作用。该语句查找员工表（employee）中月薪（salary）大于等于 10000 的员工，返回了员工的工号（emp_id）、姓名（emp_name）以及月薪（salary），并且按照工号进行排序显示。

可以看出，SQL 语句非常简单直观，全部都是由简单的英语单词组成，因为它在设计之初就考虑了非技术人员的使用需求。主要的 SQL 语句只有几个，很多时候甚至只需要使用一个 SELECT 语句。

也许正是由于它的简单易用，很多人都认为 SQL 就是简单的增删改查。但实际上，早在 1999 年 SQL 就支持了通用表表达式（WITH 语句）和递归查询、用户定义类型以及许多在线分析功能，随后它又增加了窗口函数、MERGE 语句、XML 数据类型、JSON 文档存储（SQL/JSON）、复杂事件和流数据处理（MATCH_RECOGNIZE 子句）以及多维数组（SQL/MDA）等，最新的 SQL 标准正在定制图形存储（SQL/PGQ）相关的功能。

2

通用表表达式我们以通用表表达式（WITH 语句）为例，介绍如何使用 SQL 语句分析社交网络（微信、Facebook 等）中的好友关系。以下是一个简单的好友关系网络：

在接下来的案例分析中，我们使用 t_user 表存储用户信息：

  1|刘一         2|陈二         3|张三

...其中，user_id 是用户编号，user_name 是用户姓名。

t_friend 表中存储了好友关系，每个好友关系存储两条记录。例如：

  1|        2  2|        1  4|        1

...其中，user_id 是用户编号，friend_id 是好友的用户编号。

我们首先介绍如何查看共同好友，以下语句查找“张三”和“李四”的共同好友：

WITH f1(friend_id) AS (SELECT f.friend_idFROM t_user uJOIN t_friend f ON (u.user_id = f.friend_id AND f.user_id = 3)),f2(friend_id) AS (SELECT f.friend_idFROM t_user uJOIN t_friend f ON (u.user_id = f.friend_id AND f.user_id = 4))SELECT u.user_id AS "好友编号", u.user_name AS "好友姓名"FROM t_user uJOIN f1 ON (u.user_id = f1.friend_id)JOIN f2 ON (u.user_id = f2.friend_id);我们在查询中定义了两个 CTE，f1 代表“张三”的好友，f2 代表“李四”的好友，主查询语句通过连接这两个结果集返回了他们的共同好友。查询返回的结果如下：

1|刘一

社交软件通常实现了推荐好友的功能。一方面它们可能是读取了用户的手机通讯录，找到已经在系统中注册但不属于该用户好友的用户进行推荐。另一方面系统可以找出和用户不是好友，但是有共同好友的用户进行推荐。

例如，以下语句返回了可以推荐给“陈二”的用户：

WITH friend(id) AS (SELECT f.friend_idFROM t_user uJOIN t_friend f ON (u.user_id = f.friend_id AND f.user_id = 2)),fof(id) AS (SELECT f.friend_idFROM t_user uJOIN t_friend f ON (u.user_id = f.friend_id)JOIN friend ON (f.user_id = friend.id AND f.friend_id != 2))SELECT u.user_id AS "用户编号", u.user_name AS "用户姓名",count(*) AS "共同好友"FROM t_user uJOIN fof ON (u.user_id = fof.id)WHERE fof.id NOT IN (SELECT id FROM friend)GROUP BY u.user_id, u.user_name;我们在查询中定义了两个 CTE，friend 代表了“陈二”的好友，fof 代表了“陈二”好友的好友（排除了“陈二”自己）。主查询语句通过 WHERE 条件排除了 fof 中已经是“陈二”好友的用户，并且统计了被推荐的用户和“陈二”的共同好友数量。查询返回的结果如下：

  4|李四    |   2  7|孙七    |   1  8|周八    |   2

基于查询结果，我们可以向“陈二”推 3 个可能认识的人，并且告诉他和这些用户有几位共同好友。

在社会学中存在一个六度关系理论（Six Degrees of Separation），指地球上任何两个人都可以通过六层以内的关系链联系起来。2011 年 Facebook 以一个月内访问的 7.21 亿活跃用户为研究对象，计算出其中任何两个独立的用户之间平均间隔的人数为 4.74。

我们以“赵六”和“孙七”为例，查找他们之间的好友关系链：

-- MySQLWITH RECURSIVE relation(uid, fid, hops, path) AS (SELECT user_id, friend_id, 0, CONCAT(',', user_id , ',', friend_id)FROM t_friendWHERE user_id = 6UNION ALLSELECT r.uid, f.friend_id, hops+1, CONCAT(r.path, ',', f.friend_id)FROM relation rJOIN t_friend fON (r.fid = f.user_id)AND (INSTR(r.path, CONCAT(',',f.friend_id,',')) = 0)AND hops < 6)SELECT uid, fid, hops, substr(path, 2) AS pathFROM relationWHERE fid = 7ORDER BY hops;其中，relation 是一个递归 CTE。初始化语句用于查找“赵六”的好友，第 1 次递归返回了“赵六”好友的好友，然后以此类推。我们将关系层数 hops 限制为小于 6，path 字段中存储了使用逗号分隔的关系链，INSTR 函数用于防止形成 A->B->A 的环路。

查询返回的结果如下。

“赵六”和“孙七”之间最近的关系是通过“李四”和“刘一”两个人进行联系。

另外，我们也可以统计任何两个用户之间平均最少间隔的人数：

-- MySQLWITH RECURSIVE relation(uid, fid, hops, path) AS (SELECT user_id, friend_id, 0, CONCAT(',', user_id , ',', friend_id)FROM t_friendUNION ALLSELECT r.uid, f.friend_id, hops+1, CONCAT(r.path, ',', f.friend_id)FROM relation rJOIN t_friend fON (r.fid = f.user_id)AND (INSTR(r.PATH, CONCAT(',',f.friend_id,',')) = 0))SELECT AVG(min_hops)FROM (SELECT uid, fid, MIN(hops) min_hopsFROM relationGROUP BY uid, fid) mh;查询返回的结果如下。

avg(min_hops)

   0.8214

我们提供的测试数据集很小，任何两个人之间平均间隔 0.8 个人。

除了好友关系之外，通用表表达式也可以用于分析微博、知乎等软件中的粉丝关注关系。其他常用的案例包括生成数字序列、遍历组织关系图以及查询地铁、航班换乘路线图等。

3

SQL 编程思想如果你认为 SQL 就是简单的增删改查，那已经是 40 年前的概念了。虽然 SQL 是基于关系模型开发的语言，但是在经过几十年的发展之后，它早就不再局限于关系模型了。为了能够帮助大家了解并学习现代化的 SQL 语言和编程思想，而不仅仅局限于传统 SQL 提供的简单功能，《SQL 编程思想》这本书应运而生。

本书基于作者十多年的工作经验和知识分享，全面覆盖了从 SQL 基础查询到高级分析、从数据库设计到查询优化等内容，通过循序渐进的方式和简单易懂的案例分析，透彻讲解了每个 SQL 知识点。本书采用了全新的 SQL:2019 标准，紧跟产业发展趋势，帮助读者解锁最前沿的 SQL 技能，同时提供了 5 种主流数据库的实现和差异。最后，本书还介绍了全新的 SQL:2019 标准对文档存储（JSON）、行模式识别（MATCH_RECOGNIZE）、多维数组（SQL/MDA）以及图形存储（SQL/PGQ）的支持。

本书适合需要在日常工作中完成数据处理的 IT 从业人员，包括 SQL 初学者、拥有一定基础的中高级工程师，甚至精通某种数据库产品的专家阅读。