Tony.Dong的专栏

通过简单易懂的示意图和案例分析透彻讲解每个 SQL 知识点，从 SQL 基础查询到高级分析，从数据库设计到查询优化等，提供 Oracle、MySQL、SQL Server 以及 PostgreSQL 实现代码。

关系数据库是指基于关系模型的数据库。关系模型由关系数据结构、关系操作集合、关系完整性约束三部分组成。在关系模型中，用于存储数据的逻辑结构称为关系；对于使用者而言，关系就是二维表。常见的数据操作包括增加、查询、更新以及删除，或者统称为增删改查（CRUD）。其中，使用最多、也最复杂的操作就是查询，具体来说包括选择、投影、并集、交集、差集以及笛卡儿积等。关系模型中定义了三种完整性约束：实体完整性、参照完整性以及用户定义完整性。SQL是一种面向集合的编程语言，对于 SQL 而言，一切都是关系（表）。

本篇介绍如何使用 SELECT 和 FROM 查询表中的数据，通过投影操作获取指定的字段信息。SQL 不仅仅能够查询表中的数据，还可以返回算术运算、函数和表达式的结果。在许多数据库中，不包含 FROM 子句的无表查询可以用于快速获取信息。另外，别名和注释都可以让我们编写的 SQL 语句更易阅读和理解。

在 SQL 中使用 WHERE 子句指定一个或者多个过滤条件，可以查找满足要求的数据。SQL 查询条件中支持各种比较运算符、逻辑运算符以及空值判断等。另外，DISITINCT 关键字可以去除查询结果中的重复记录。

SQL 支持使用模式匹配对文本内容进行模糊查找，主要的方式有两种：LIKE 运算符和正则表达式函数或运算符。其中，LIKE 运算符通用性更好，但是需要注意区分大小写的问题；正则表达式函数功能更加强大，但是依赖于不同数据库的实现。

ORDER BY 子句可以将查询的结果按照某种规则进行排序。排序方式分为升序和降序；可以基于单列或表达式排序，也可以基于多列或多个表达式排序。中文排序需要字符集和排序规则的支持，不同数据库的实现各不相同。另外，还需要注意空值的排序问题。

查询语句中的 FETCH 和 OFFSET 子句可以限定返回结果的数量和偏移量，从而实现排行榜和分页查询效果。LIMIT 和 OFFSET 子句也是实现该功能的一种常见的用法。另外，某些数据库还实现了其他的替代方式。

掌握常见的 SQL 函数将会方便我们进行数据的处理和分析，避免重复实现已有的功能。本篇主要介绍了常见的数值函数，大多数函数都可以在不同的数据库之间通用，但是也存在一些数据库专有的函数实现。

字符函数用于字符数据的处理，例如字符串的拼接、大小写转换、子串的查找和替换等。本篇介绍了常见的 SQL 字符函数，掌握这些函数可以方便我们对文本数据进行清洗和转换等处理。

本篇我们介绍了日期和时间数据类型以及相关的函数，同时了解了数据类型之间的显式转换和隐式转换。

本篇我们介绍一种为 SQL 语句增加逻辑处理功能的方法：CASE 表达式。SQL 中的 CASE 表达式可以根据不同条件产生不同的结果，实现类似于编程语言中的 IF-THEN-ELSE 逻辑功能。CASE 表达式支持两种形式：简单 CASE 表达式和搜索 CASE 表达式。

聚合函数可以用于数据的汇总分析，本篇介绍了如何使用 SQL 聚合函数统计行数、平均值、总和、最大值、最小值以及统计学中的方差和标准差。

上一篇我们学习了如何利用 SQL 聚合函数对数据进行汇总分析。聚合函数在单独使用时，会将所有的数据作为一个整体（分组）进行统计；因此上一篇中的示例都只返回了一个结果。但是在实际应用中，我们通常需要将数据按照某些规则进行分组，然后分别进行汇总统计。例如，按照部门计算员工的平均月薪，按照不同的产品和渠道统计销售金额等。为了实现这种分组统计的功能，需要将聚合函数与分组操作（GROUP BY）一起使用。...

上一篇我们学习了如何利用 GROUP BY 子句与聚合函数实现数据的分组汇总。到此为止，我们已经完成了基础篇的学习。本篇我们以世界银行公布的全球 GDP 和人口数据为案例，介绍使用 SQL 进行数据分析的常用方法。同时也作为对基础篇所学内容的回顾和复习。 GDP（国内生产总值）是指按市场价格计算的一个国家/地区所有常住单位在一定时期内生产活动的所有最终产品和服务的市场价格。GDP 通常被公...

上一篇我们以分析世界银行全球 GDP 和人口数据为案例，回顾了基础篇所学的各种 SQL 基本查询和分析功能。从本篇开始我们将会学习进阶篇的内容。进阶篇将会介绍更加复杂的多表查询、子查询、集合运算以及各种高级的数据分析技术，能够让我们真正体会到 SQL 数据处理和分析的强大。作为进阶篇的开始，我们先来讨论一下 SQL 中的空值问题，因为空值的特殊性导致我们很容易出现一些错误和问题。空值与三值逻...

上一篇我们讨论了 SQL 中的空值，以及空值可能带来的问题和解决方法。到目前为止，我们的查询都是从单个表中获取数据。本篇开始我们探讨一下如何从多个表中获取相关的数据。因为在关系数据库中，通常将不同的信息和它们之间的联系存储到多个表中。比如产品表、用户表、用户订单表、以及关联的订单明细表等。当我们想要查看某个订单时，需要同时从这几个表中查找关于订单的全部信息。在 SQL 中，我们可以使用多表连接...

上一篇我们介绍了如何在 SQL 语句中使用连接查询（JOIN）获取多个表中的关联数据，具体讨论了内连接、左/右/全外连接、交叉连接、自然连接以及自连接的原理和使用方法。除了连接查询，SQL 还提供了另一种同时查询多个表的方法：子查询（Subquery）。本篇我们就来了解一下各种类型的子查询和相关的运算符。什么是子查询？我们先来考虑一个问题，哪些员工的月薪大于所有员工的平均月薪？可以先使用 A...

上一篇我们介绍了 SQL 中各种形式的子查询，以及与子查询相关的 IN、ALL、ANY/SOME、EXISTS 运算符。我们已经学习了两种涉及多个表的查询语句，今天我们来讨论另一种从多个查询中返回组合结果的方法：集合运算。集合运算数据库中的表与集合理论中的集合非常类似，表是由行组成的集合。因此， SQL 支持基于行的各种集合操作：并集运算（UNION）、交集运算（INTERSECT）和差集运...

上一篇我们讨论了如何利用 SQL 集合运算符（UNION [ALL]、INTERSECT 以及 EXCEPT）将多个查询结果合并成一个结果。接下来我们介绍 SQL 中一个非常强大的功能：通用表表达式（Common Table Expression）。表即变量在编程语言中，通常会定义一些变量和函数（方法）；变量可以被重复使用，函数（方法）可以将代码模块化并且提高程序的可读性与可维护性。与此类...

上一篇我们学习了如何利用 SQL 通用表表达式（CTE）简化复杂的查询语句，实现数据的遍历和递归处理。我们在第 13 篇学习了基础的数据分组汇总操作，例如按照部门和职位统计员工的数量和平均月薪。现在，让我们讨论一些更高级的分组统计分析功能，也就是 GROUP BY 子句的扩展选项。销售示例数据本篇我们将会使用一个新的销售数据集（sales_data），它包含了 2019 年 1 月 1 日到...

上一篇我们学习了利用 GROUP BY 子句的扩展选项（ROLLUP、CUBE 以及 GROUPING SETS）实现数据的层次统计、交叉汇总以及自定义维度分析等高级功能。不过，产品和业务对于复杂报表的需求并不仅仅止步于此。例如，如何分析员工在部门内的薪酬排名、计算产品每个月的累计销量以及与去年同期相比的增长率等。这些分析功能通过分组汇总操作通常很难或者无法实现，因此我们需要了解更加强大的 SQ...

上一篇我们介绍了窗口函数的概念和语法，以及聚合窗口函数的使用。今天我们继续讨论 SQL 中的排名窗口函数和取值窗口函数，它们分别可以用于统计产品的分类排名和数据的环比/同比分析。排名窗口函数排名窗口函数用于对数据进行分组排名。常见的排名窗口函数包括：ROW_NUMBER，为分区中的每行数据分配一个序列号，序列号从 1 开始分配。RANK，计算每行数据在其分区中的名次；如果存在名次相同的...

上一篇我们学习了如何利用排名窗口函数实现产品的分类排名，以及利用取值窗口函数进行销量的环比/同比分析。本篇我们介绍 SQL:2016 标准新增的一个功能：行模式识别（Row Pattern Recognition）。行模式识别行模式识别用于查找多行数据之间的规律。行模式识别通过指定一个模式（正则表达式），找到匹配该模式的一组数据行；并且可以对这些匹配的一组数据进行过滤、分组和聚合操作。行模式...

在进阶篇中我们学习了许多 SQL 高级功能，包括空值的处理、连接查询、子查询、集合运算、通用表表达式、高级分组选项、窗口函数以及上一篇的行模式识别。这些特性可以帮助我们实现各种复杂的数据分析和报表功能。从今天开始我们将会进入开发篇的学习，了解如何设计数据库的模式、管理表和操作表中的数据、理解数据库事务、索引、视图以及编写服务器端程序。首先，让我们来看看如何设计数据库的结构。数据库设计流程数据...

上一篇我们讨论了如何进行数据库的结构设计，并具体介绍了实体关系图和规范化的技术。在设计 ERD 时，首先需要定义实体以及实体的属性，也就是定义表的结构。定义表结构时，首先需要确认表中包含哪些字段以及字段的数据类型。今天我们就来了解一下如何为表中的字段选择合适的数据类型。常见数据类型字段的数据类型定义了该字段能够存储的数据值，以及允许执行的操作。下图列出了 SQL 中常见的数据类型，包括字符串...

上一篇我们讨论了如何为字段选择合适的数据类型。选定了字段的数据类型之后，我们就可以开始创建和管理数据库中的表了。数据库对象数据库（Database）由一组相关的对象组成，主要包括表、索引、视图、存储过程等。为了方便对象的管理和访问，数据库通常使用模式（Schema）来组织这些对象；模式是一个逻辑单元，或者一个存储对象的容器；它们之间的关系如下图所示：一个数据库由多个模式组成，一个模式由许多...

上一篇我们学习了如何创建和维护数据库中的表。有了表之后，我们就可以执行一些数据操作了。今天我们就来学习如何对表中的数据进行增加、删除、修改以及合并操作。增加数据SQL 主要提供了两种增加数据的方法：INSERT INTO … VALUES … ;INSERT INTO … SELECT … ;首先，第一种形式的语法如下：INSERT INTO t(col1, col2, ...)...

上一篇我们学习了如何利用 DML 语句对数据进行各种增删改合操作。当我们在操作数据的同时，其他人或者应用程序可能也在操作相同的数据；此时数据库必须保证多个用户之间不会产生影响，数据不会出现不一致性。这就涉及到一个重要的概念：数据库事务（Transaction）。什么是数据库事务在企业应用中，数据库通常需要支持多用户并发访问；并且保证多个用户并发访问相同的数据时，不会造成数据的不一致性和不完整...

上一篇我们介绍了数据库事务的概念和重要性、事务的 ACID 属性，以及并发事务的控制与隔离级别。今天我们讨论与性能相关的一个重要对象：索引（Index）。你一定听说过：索引可以提高查询的性能。那么，索引的原理是什么呢？有了索引就一定可以查询的更快吗？索引只是为了优化查询速度吗？接下来我们就一一进行解答。索引的原理以下是一个简单的查询，查找工号为 5 的员工：SELECT * FROM ...

上一篇我们介绍了索引的概念、索引提高查询性能的原理，以及索引需要付出的代价。今天我们来讨论另一个重要的数据库对象：视图（View），学习如何利用视图简化查询语句、实现业务规则以及提高数据的安全性。视图不是表简单来说，视图就是一个预定义的查询语句。视图在许多情况下可以当作表来使用，因此也被称为虚拟表（Virtual Table）。视图与表最大的区别在于它不包含数据，数据库中只存储视图的定义语句...

在上一篇中，我们学习了视图的概念和作用；视图可以实现查询语句的重用，业务封装以及数据的安全访问。今天我们来讨论另一个用于在数据库中实现业务逻辑封装的对象：存储过程（Stored Procedure）和函数（Stored Function）。什么是存储过程？SQL 是一种声明式的语言，关注的是结果而不是过程。但是在实际开发中为了满足业务处理的需要，编程语言（Java、C++ 等）都会提供各种功...

上一篇我们学习了如何利用存储过程和函数在数据库中实现业务逻辑。今天我们介绍一种特殊的存储过程或者函数：触发器（Trigger）。触发器概述数据库触发器是一种特殊的存储过程或者函数，触发器不能被直接调用，而是当某个事件发生时自动触发并执行预定义的操作。常见的触发事件包括修改数据的 DML 语句、定义数据库对象的 DDL 语句以及系统级别的事件，例如用户的登录操作。以下是 DML 语句触发器的示...

上一篇我们介绍了触发器的概念和作用，演示了如何使用触发器实现自动的数据审计。到目前为止，我们已经学习了许多 SQL 基础查询、高级分析以及数据库设计和开发的实用技能。今天我们开始扩展篇的学习。首先，让我们深入到数据库服务器的内部，探索一下 SQL 查询的执行过程。SQL 查询执行过程不同数据库对于 SQL 语句的执行过程采用了各自的实现方式；我们虽然不能通过一篇文章涵盖这些实现细节，但是可以...

上一篇我们探讨了 SQL 查询的执行过程，学会了如何查看和解读执行计划。今天我们来介绍一些常见的查询优化方法和技巧。首先一点：优化规则千万条，执行计划第一条。不要盲目相信什么规则，包括本文列出的规则；因为数据库优化器在不断改进，许多规则已经或者将来不再适用。不过另一方面，通过执行计划找出性能问题并进行优化的方法不会改变。一般来说，对于 OLTP 应用减少数据库磁盘 IO 是 SQL 优化需要考...

上一篇我们介绍了一些常见的 SQL 优化技巧，包括创建合适的索引和各种查询语句的优化。今天我们来学习如何在关系数据库中存储 NoSQL 数据（JSON 文档），以及使用 SQL 实现数据行与 JSON 文档的相互转换。什么是 JSON？JSON（JavaScript Object Notation、JavaScript 对象表示法）是一种轻量级的数据交换格式，采用完全独立于编程语言的文本格式...

上一篇我们学习了如何在关系数据库中存储 JSON 文档，以及使用 SQL 实现关系数据与 JSON 数据的相互转换。今天我们介绍如何在 Python 程序中访问数据库，执行 SQL 语句进行数据分析操作。Python 数据库连接Python 以其优雅、准确、 简单的语言特性，在云计算、Web 开发、自动化运维、数据科学以及机器学习等人工智能领域获得了广泛应用。如果想要学习 Python，可以...

上一篇我们学习了如何在 Python 应用中通过数据库接口执行 SQL 语句和存储过程。今天我们来了解一下如何在 Java 程序中通过 JDBC 执行数据的增删改查操作。什么是 JDBC？JDBC（Java Database Connectivity）是 Java 语言中用于访问数据库的应用程序接口（API）。JDBC 提供了查询和更新关系数据库的标准方法，属于 Java Standard ...

上一篇我们学习了如何在 Java 程序中通过 JDBC 接口连接和操作数据库。今天我们来讨论一下 SQL 中的动态语句，以及由此带来的 SQL 注入问题。什么是动态 SQL 语句？在我们的专栏中，使用的基本都是静态 SQL 语句（Static SQL），因为这些语句的内容在编译时就已经完全确定。与此相反，数据库还支持另一类语句：动态语句（Dynamic SQL）。动态 SQL 语句是指内容...

上一篇我们介绍了如何使用动态 SQL 创建通用的查询语句以及 SQL 注入问题和预防方法。今天是我们专栏的最后一篇文章，不知不觉我们已经坚持了 2 个多月，完成了从初级查询到高级分析功能、从增删改查到数据库设计、从性能优化到应用开发等相关知识的学习。在考虑本篇文章的内容时，开始我是打算说一说编写专栏的感想和收获；不过最终还是决定利用这篇文章再介绍一些与 SQL 相关的重要概念和总结。其它的让我...