Skip to main content
  1. 经验技巧分享/
  2. thinking in SQL | 废话SQL,思维与实践/

什么是SQL

··431 字
sql 概念 有悟方法

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

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

SQL是一种DSL #

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

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

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 intoupdate 的结合体。除 merge 外,还有诸如 update set [where …] [delete …] 这种变种。

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

selectupdatedeleteinsert,可以分为 数据状态发生变化(修改、删除、插入)、数据状态不会发生变化(查询)两大类。可以进一步的抽象成下图的形式。

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

熟悉数据开发的人,可能都会有这种经历,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,以不变应万变。