↓跳到主要内容
  1. Skills/
  2. thinking in SQL | 废话SQL，思维与实践/

什么是SQL

··字数 7179·15 分钟
sql 概念 有悟方法
目录

SQL，全称是 structure query language，结构化查询语言。注意，它并不一种编程语言，只是经常跟编程语言混合在一起使用。

文章有点长，可能会失去耐性，烦请静心看下去，一遍没看懂，多看几遍。你所已懂的，并不一定运用得好。

SQL是一种DSL #

SQL本身是一种 DSL（领域描述语言），它由数学家（早期计算机技术大都是由领域应用中的数学专家主导的）发明用来表达数据查询的描述性语言。一般地，大学计算机课程中，都设有数据库基础、数据库原理这类课程，其中会有非常长的篇幅介绍SQL语言，介绍 select、update、insert、delete，都是非常标准的通用语法，只是大学老师通常不会跟你说，实际工程中使用到的SQL要比教程中的复杂得多。这不是本文要介绍的重点。

为什么只是 select、update、insert、delete，因为我们在使用数据时，从数据表中查询数据、修改删除表中数据、往表中添加数据等操作。数学家很早以前就把帮我们把数据使用的操作需求归纳好了。

DML
Data Manipulate language，数据操作语言。上面提到的都是，比较标准化。市面上的多个不同的数据库产品上运行DML，语法都比较接近，有时还通用（mssql的语法特殊符号比较多）。
DDL
Data Define language，数据定义语言。上面提到了数据操作语言，其中 数据 是被操作的对象。这些对象的属性（表结构、字段的数据类型、物理特性等）使用 DDL 来定义。所以，使用数据前，需要在数据库中使用 DDL ，创建相关的环境（表），然后才可能使用 DML（DML所操作的表、视图和字段都需要实际上已经存在的）。不同的数据库还提供了不同的环境DDL，如表空间维护等，视具体特定的数据库而定。

本文中，数据集与表几乎都是指一个具有多行数据，每行数据又包含多个信息项的数据表。只是为了表达需求，在不同的上下文环境下，会分别使用数据集或者表。

SQL是过程 #

某种定义上，SQL 与 javascript、python等脚本语言的性质是一致的，需要运行环境把它解释并编译成更低层次的代码来执行逻辑。运行SQL的环境（数据库）本身就可以看成是一个带有大量数据环境变量的超级 runtime，这些数据环境变量（表）的值（表中的数据）是事先预置（从其它地方迁移过来或者不断积累更新（CRUD）所形成的状态）。

关系型数据库，有一种叫 execute plan（执行计划）的东西，有些数据库还提供了select * from ... for explain 的语法，来直接得到它。它可以把一个完整的SQL分解多个级联嵌套的步骤，使用一个多于2个表或者视图关联的查询，就可以非常较明显的看到执行计划中的顺序。

执行计划并不只是数据操作过程的可视化，它还是数据库将SQL编译成低层次数据访问代码的高级可视化视图。故而，这个执行计划是数据库专用的（不同的数据库，所生成的执行计划不同，即使同一个SQL，在表数据大小不同时所产生的执行计划都可能不一样。这与数据库内部引擎的实现有关）。也因此，执行计划在SQL调优时非常有用，相当于一个逻辑过程的步骤化分解。通过改变这个执行计划，或者拆解SQL来得到其他想要的执行计划（需要非常丰富的经验积累）。如果有前端基础的同学，浏览器有DOM inspector，执行计划就类似于SQL的 inspector，用来观察SQL最终执行的过程。

有悟认为，oracle SQL编译器所生成的执行计划是非常透明、聪明的。可能是因为oracle市占率高、研发投入高有关。它还支持使用黑科技（sql hint）在不修改原有SQL语句的情况下来改变执行计划，达到调优目的。其他数据库产品并没有这种隐藏的功能。也许涉及到数据库内部实现以及非常难以理解的细节，一直没有官方版本的 oracle sql hint 完整手册。

– 纯属猜测🤔

在大数据产品，上面所说的执行计划同样存在，SQL被直白的编译成 java程序。可能因为oracle的高市占率原因，hivesql与oracle的SQL实现兼容性非常高，如果有基于oracle实现的OLAP型分析应用，存储过程迁移成本会低很多。

对SQL工作原理有兴趣的，可看oracle关于 《SQL如何工作》 中的描述。

SQL的思维方式 #

前面谈到SQL是DSL、也是过程，那么使用SQL来访问数据时，很多情况下可以跳过操作步骤这种过程式思维惯势，而是直接面向业务问题作答（特别是经验丰富的应用程序开发程序员，SQL的使用大多只停留在CRUD，会把一个SQL可以实现的逻辑分解成多个小SQL）。

本节所要谈的思维方式，更适合于长期从事数据开发的SQL BOY。

多年前，有悟刚入行没多久，有一个1年工作经验的JAVA工程师同事，我们在一起编写一个数据处理程序的 PL/SQL 存储过程。居然把一个批量修改数据的 update 语句，按一个 for 循环并逐条update数据的方式来实现，而且相当自豪地使用了游标。简直是把 SQL 当 JAVA 程序来编写了。在我们的嘲笑下，他默默的接触了教育（实属不该，年轻不懂事，要谦虚🤫）。

通常，我们喜欢使用可视化图解的方式来帮助我们理解问题，SQL也如此。有悟经常使用的是数据集分析法，确实涉及到一些数学概念，但并不复杂。如果数学概念让人生畏，那么你可以认为这是一种日常工作生活中常见的分类分析方法。

以下语法，从标准中精简而来，仅保留核心部分，以减少复杂性，便于陈述。

select ... from ... [where ...]
从某数据源（这里指具体的数据集）获取目标信息（具体的属性），当然也可以添加过滤条件
update ... set ... [where] ...]
对目标数据进行修改。这里隐藏了一个概念，目标数据集。经验初浅的，关注 set …（要如何修改）；经验丰富，一般更习惯于先关注 update … [where …] (要修改什么，为什么修改) 更高级别的问题。
delete from ... [where ...]
与 update 的逻辑类似。
insert into ... (values(...) | select ... from [where ...])
insert into 有两个重要的但非常不同的场景。在功能应用系统中，最常见的是 insert into ... values(...)，实现向系统里添加业务数据（非常重要的数据生产方式）；而在数据开发领域，通常所需要的数据已经存在系统中的某个位置，这时往往使用的是 insert into ... select ... from ...[where ...]。
merge into
insert into 与 update 的结合体。除 merge 外，还有诸如 update set [where …] [delete …] 这种变种。

先抛开容易搞混乱的变种，它对简化问题理解没有帮助。我们的目的是从上面的语法来提炼帮助理解、从而形成容易理解、记忆、使用的模式，这才是主要矛盾。

select、update、delete、insert，可以分为 数据状态发生变化（修改、删除、插入）、数据状态不会发生变化（查询）两大类。可以进一步的抽象成下图的形式。

查询 是确定数据源、目标集的过程，增删改为对目标集的二次加工。有时可能会觉得二次加工时非常花费精力，其实查询才是重点，在这个确定数据源与目标集的过程中，往往涉及业务问题的理解、分析，加工只是附带的操作需求。本节所说的思维方式，以及后文所要谈的内容，都是围绕这个部分。

熟悉数据开发的人，可能都会有这种经历，SQL不管再怎么长，它的代码量其实非常少的。若业务问题的理解不准确，编写的查询缺漏条件，那么查错并编写出正确的SQL所花费的时间，往往比编写出原来那个为了任务而凑数的SQL所花时间要多很多。一些死要面子的人始终不肯承认这一点。

如何编写SQL - SQL 查询的构建过程 #

要写好SQL，就要对SQL中重要的组成部分-查询语句有更深刻的认识。语法是为了让数据库能够理解、编译、执行的规范，只是一种工具，并不是方法。

首先看看 select 语法的结构。

查询: select SELECTED_COLUMNS
     from   TABLE | 查询 | 视图 [ | join expression]
     where  
     [group by [having]]

可分解为几个部分:

  1. TABLE | 查询 | 视图
  2. join expression
  3. where statement
  4. selected columns
  5. group by

因历史发展问题，关联表达式有两种写法。如使用带有关键字的 [inner|left|right|full] join 写法，不过通常情况下，更多的是等值连接、内连接，把多个表罗列在 from 之后，把关联条件做为过滤写在 where 来加以约束结果数据集。虽然结果是等效的，但在学习SQL的时候，最好还是把它视为一个变种写法，因为二者的思维过程还是不完全相同的。

理解上述四个部分，不同思维习惯的可能不同，有悟建议按照上述所列顺序：从大到小到，从整体到局部。最终编写SQL查询，就会变成这样的一种模式。

从哪里（TABLE、视图）获取想要(where filter) 的信息（selected columns)，是否需要特别的加工（group by 汇总）。

当你有足够长时间的编写SQL经验后，会发现自己就像一个雕刻工匠，拿到一块木料或者石料，先用锤子大胆砸掉边角乃至于成形，后使用刻刀雕琢丰富局部细节。

SQL 查询的构建 - TABLE、视图、子查询 #

确定数据来源的步骤。这个步骤需要关注的内容是回答领域问题时，需要从哪个地方获取数据，把表、视图、子查询当成是具体数据集的表达形式，这些数据集本身具有一定的格式。数据集所提供的领域范围，是否能够覆盖目标结果。如果范围不足，那么需要引入更广的范围。若范围已覆盖，则看数据集中的信息项（字段）是否能够满足目标（与表的范式设计有关，而往往这些表都是其他人设计的），将范围中多个有关联关系的表收集起来，并确定每个数据集的唯一性标识（业务主键或者ID之类）

  1. 在关系型数据库中，数据集中的每一个元素的格式都是相同的，即每一行数据，都有相同的字段属性，字段个数与数据类型。

  2. 子查询，本身也是完整的查询。通常实践中，当从数据表提取信息时，需要非常多的步骤或者转换时，会这个中间临时结果封装成一个查询（可以避免创建物理表），隐藏中间细节只是为了更好的理解全局问题。

这里所说的 表，通常是数据源的数据模型；视图，为了更好的访问数据所创建的虚拟表；子查询，通常是为了减少SQL复杂性而编写的临时查询。无论是表、视图、子查询，都指向同一个方向，则数据源的数据模型。如果是从事数据开发领域的读者，就非常明白有悟所指。所以，为了编写出正确的SQL查询，对数据源模型的清晰认识就成为了解SQL需求之后接着开展的工作。通常我们从ER图（反映数据集间关系）、数据字典（表的字段说明）来了解数据源。

SQL 查询的构建 - JOIN expression #

在确定数据来源时，多数情况下时会涉及到一次性从多个表中提取数据。通过多个数据集关联得到一个新的数据集，从新的数据集上提取所需的信息项。这一环节可以回答这类的问题：

  1. 关系分析：存在、不存在、同时存在
  2. 关联信息提取：从附属表中提取关联信息

故，构建关联表达式时，需要确定两个重要的问题：

  1. 每个数据集均可以确定每一行数据的唯一标识
  2. 这些数据集间存在的关联关系条件（主从、一对多、多对一、表间引用字段等等）

若上述的关键性问题无法确定，那么无法编写出正确的SQL，或者得到的查询结果是不准确的。有悟称之为数据集不可用，目标不可达。

在互联网上学习SQL关联的语法时，经常会看到使用维恩图(venn)来分析两个数据集关系的文章。它可以非常准确的表达两个集之间的包含关系，用来帮助确定主从关系、覆盖包含关系时比较有用。但它并不能帮助确定数据集之间的关联条件。所以就有人提出了维恩图不够好、要使用关联图来表达更多的细节。其实仔细思考一下，没有这个必要，当关联字段非常多时，由于要精确表达左、右联接，图形就会变得复杂无比，反而忽略重点。

根据有悟的经验，使用维恩图可以快速划分主从，确定需要的关联结果，特别是用来快速沟通覆盖包含关系。至于关联关系的细节，可以作为附加说明的形式来加以补充，突出重点，有的放矢。

SQL 查询的构建 - where filter #

where 表达式，既可以认为是数据选择条件，也可以认为是过滤器，它都是对整个输出的结果集进行约束。选择一种更适合你思维习惯的表达即可。

看到这里，就明白有悟为什么建议把 from table1, table2 where 关联条件 看成是一个变种，编写关联时尽量使用标准写法。虽然可以钻下牛角，关联条件就是对数据集 table1、table2的约束。如果你是在构建一个通用化的SQL查询生成器，那么这个牛角会让你付出编写更多、更复杂代码的代价。

where 过滤的作用范围（约束整个输出结果集），与一些函数作用范围级别相同，导致一些函数无法在where条件中直接使用，如分析函数。但有时需要先计算分析函数结果，再对计算结果进行过滤选择，就需要使用子查询（通常所说的套一层）的方式来解决(比如在oracle数据库，为了获取排序top 1的那一行数据，需要先row_number排序产生序号后，在外层选择序号=1的数据行)。你们也不需要在这个问题上浪费时间，前文提前，SQL是描述性的语言，表达的是逻辑，并隐藏了过程步骤，SQL标准并没有针对上面两个相同作用域级别的操作定义其先后顺序，而是把这个选择权交到用户手里。实际上这个优先级的标准是无法制定的，孰先孰后，皆有可能。

SQL 查询的构建 - selected columns - 非汇总 #

上面 数据集、关联、约束等环节，已经非常接近所要的结果。把所有的源数据集，根据关联关系所到的结果，看成一个完整的新数据集（子查询），这个数据集上的信息项，包含了所有数据源数据集上的所有信息项。实际上，我们通常只需要这个数据集上的部分信息项，或者根据数据集上的信息项，衍生出新的信息项（如转换）。

这个环节就是雕琢。选出符合目标需求的信息项（字段），使用 case when ... end、标量函数(scalar function) 转换、四则运算等来添加衍生信息。

标量函数(scalar function) ，是计算机计算科学中，指根据输入参数来计算函数输出值、输入参数与结果值的维度相同的计算函数。在数据库领域中，这类函数是一个统称，它代表只根据输入的参数来计算结果，这个结果所归属的业务标识与输入的参数相同（不改变数据粒度，与多行汇总计算为一行相区别）。

另外，像分析函数(analystic function。也叫窗口函数，windowing function)这类作用于整个结果集的函数，也可以放在 selected columns 语句来定义。

SQL 查询的构建 - group by 和 selected columns #

汇总，它在实际应用中，非常重要，也非常普遍。比如我们常见的excel电子表格，根据所列销售明细计算销售量，并按照区域、或销量人员、或分公司等不同的维度进行统计。

汇总时，可以把 from 数据集 [join expression] where 看成一个新结果集的子查询。汇总是针对这个数据集所进行的计算。用来统计的维度和计算的汇总项，只能来自这个数据集，通常是字符型表示分类或者个体标识符字段，用来表示维度，如区域代码、币种、销售员编号等；可以汇总计算的字段，通常是数字型的，如金额、个数等。编写group by sql，首先确定统计维度、统计项，构建可以获取这些信息项的数据集查询，把维度信息项写在 group by 后，而 selected columns 可以出现的是维度项与汇总函数，统计项做为汇总函数的输入。

需要明确一点的时，使用 group by 之后，结果数据集的维度发生变化，则代表每一行数据的标识发生了变化，结果集被重新定义。

与非group by sql类似，group by sql也有作用域为整个结果数据集的语句或函数。

  • group by: 符合汇总要求的维度
  • selected columns: 汇总维度，汇总函数，分析函数
  • 过滤：在 group by 汇总维度 语句后，使用 having 条件 来过滤选择汇总后符合目标的结果

需要特别说明的，group by 产生的数据集与源数据集的业务标识维度不同，分析函数的输入参数应该是汇总维度或者与group by 汇总后相同级别的信息项。比如可以是金额字段，也可以是sum(金额) ，后者使用了聚合函数(aggregate function)，数据库可以确保输入分析函数时的值，为汇总后的结果，从而确认分析函数的作用域是结果数据集。

SQL查询的构建 - 多表关联时的顺序 #

select
from   表1
inner  join 表2
on     表1... = 表2...
inner   join 表3
on     表2... = 表3...
where  表1.某某列 in (...)
and    表2.某某列 in (...)

select
from   表1
inner  join 表2
on     表1... = 表2...
and    表1.某某列 in (...)
and    表2.某某列 in (...)
inner   join 表3
on     表2... = 表3...

上面两个SQL的执行结果是相同的，即等价。前文提到SQL是过程，但写SQL可以忽略过程。刚开始比较难理解其中的意思。

  • 过程，因为SQL最终会被编译成数据库内核可执行的程序
  • 忽略过程，是写SQL时减少心智负担

当你日常编写比较多SQL时，还要注意：

  1. 虽然数据库的SQL优化器可以非常智能的生成它认识最优的执行计划，早期的数据库没那么先进，而且并不是每一个数据库产品都很一样先进。一定有人跟你说过，要先装小表放在前面做多表关联的主表，这种说法在很多年前的SQL优化是有效的，但当下并不一定，技术并没有停止发展。以后再有人强制你这么做，可以大胆反驳。（这一项，涉及到SQL优化，不是本文内容）
  2. 我们编写SQL时，首先是确保结果的正确性，再考虑可读性、优化问题。上面两段SQL，有悟习惯于将两个表间的关联条件、单表的字段过滤统一写在 on 之后。一来不容易缺漏，二来表达清楚。（当查询有4个表，关联条件、过滤字段多达4个以上，并且无序罗列在 where 语句之后时，会发现需要更多的时间来验证语句的逻辑正确性）

因此，有悟建议采用第二个SQL的方式，既易读，也能形成固定模式。前两个表关联得到一个结果集，再与后续第三个表关联，以此类推。虽然头脑里要多花点精力来逻辑思考这个先后过程步骤，但它却非常符合我们的思维习惯。当习惯养成，自然也就不会变成负担。

SQL查询的构建 - 其他联想 #

本文中详细介绍的select 查询语句，是有悟多年来用得最多的部分，但SQL 查询的功能并不止于此。当你已经熟悉前面所谈的内容后，建议你翻看 oracle sql 手册，其中有 select 的完整语法图，仔细研究每一个环节，可以挖掘出非常多的其它新鲜内容，比如：

  • cte（ common table expression, with 表达式）
  • 表函数
  • …

诸多功能，都从select 语句被引入。

编写 SQL 语句的境界 #

  1. 当你在编写SQL时，心里一直在嘀咕左联接、右联接，那么你是个新手
  2. 当你在编写SQL时，头脑可以对SQL语句进行逻辑运行（调试），不会被左联接、右联接、where中的关联条件混淆，并可一次性生产出正确性非常高的SQL语句，那么你应该是已经经过多年培训的数据开发工程师
  3. 当你在编写SQL时，不需要回忆语法，而是满脑的数据集、关联关系。恭喜你，基础已经成为肌肉记忆，让你可以专注于领域问题。这需要很多年的训练。

别着急，循序渐进，总有一天，你也可以写出优秀的SQL，以不变应万变。

↑