「SQL数据分析系列」1. SQL背景知识介绍-CSDN博客

本文链接：https://blog.csdn.net/qq_43045873/article/details/115499796

来源 | Learning SQL Generate, Manipulate, and Retrieve Data， Third Edition

作者 | Alan Beaulieu

译者 | Liangchu

校对 | gongyouliu

编辑 | auroral-L

全文共8325字，预计阅读时间50分钟。

第一章 SQL背景知识

一、数据库简介

1.非关系型数据库

2.关系模型

3.一些术语

二、什么是SQL

1.SQL语句的类别

2.SQL：非过程化语句

3.SQL示例

三、什么是MySQL

四、不只是SQL

五、内容提要

在我们开始学习本书的内容之前，先回顾一下数据库技术的历史，以便更好地了解关系数据库和SQL语言是如何演变的。因此，我会先介绍一些基本的数据库概念，看看计算机数据存储和检索的发展史。

注意：对于那些急于开始写查询语句的读者，可以直接跳到第三章，但我建议在此之后还是再回到前两章看看，以便更好地了解SQL语言的历史和实用性。

一、数据库简介

数据库指的是一组相关信息的集合。例如，电话簿可以被看做一个数据库，其中包含某地区所有居民的姓名、电话号码和地址等信息。虽然电话簿是一个十分普及和常用的数据库，但是它存在以下问题：

• 查找一个人的电话号码可能很费时，特别是电话簿包含大量条目时；

• 电话簿只按姓/名来索引，因此查找居住在特定地址的人的姓名的时候，虽然理论上可行，但对该数据库来说并不实用；

• 从打印电话簿的那一刻起，随着居民在该地区的流动、更改电话号码或迁移到同一地区内的另一个地方等动作的发生，电话簿上的信息变得越来越不准确。

电话簿的上述缺点也在任何手动编制的数据存储系统上存在，例如存储在文件柜中的病历等。由于这些纸质数据库很繁琐，所以最初开发的一些计算机应用程序就是数据库系统，它通过计算机来存储和检索数据。因为数据库系统以电子方式而不是纸质方式存储数据，所以它能够更快速地检索数据、以多种方式索引数据并向用户社区提供最新信息。

早期的数据库系统管理存储在磁带上的数据。由于磁带的数量通常要远远超过磁带读取器，所以技术人员的任务就是在需要特定数据时手动装卸磁带。而且由于那个时代的计算机内存非常小，通常情况下对于同一数据的并发请求需要多次读取磁带上的数据。尽管这些数据库系统比纸质数据库有了显著的改进，但它们与当今技术所能实现的相差甚远。（现代数据库系统可以管理数PB（拍字节（Petabytes），计算机存储容量单位，也常用PB来表示。1PB=1024TB==2^50字节）的数据，由服务器集群访问，每个服务器在高速内存中缓存数万GB（gb也叫吉字节（GB、Gigabyte，在中国又被称为吉咖字节或京字节或十亿字节或戟），常简写为G，是一种十进制的信息计量单位。）的数据，不过这些内容可能有些超前了）

1.非关系型数据库

注意：本节包含一些早期非关系型数据库系统的背景信息。对于渴望深入学习SQL的读者，可以跳过这几页进入下一部分。

在计算机化数据库系统的前几十年中，数据以各种方式存储和呈现给用户。例如，在层次数据库系统中，数据表示为一个或多个树结构。如图（1-1）显示了George Blake和Sue Smith银行账户的相关数据，它们通过树结构表示：

George和Sue都有自己的树结构，包含他们的账户以及交易信息。层次数据库系统提供了定位特定客户树的工具，并能遍历该树以找到所需的帐户或交易数据。树中的每个节点可以有零个或一个父节点，以及零个、一个或多个子节点。这种配置被称作单根层次结构（single-parent hierarchy）。

另一种管理数据的方法称为网状数据库系统，它表现为多个记录集合，集合之间通过连接定义不同记录之间的关系。如图（1-2）显示了该系统中George和Sue的账户信息：

为了找到转移到Sue的货币市场账户（money market account，MoneyMkt）的交易记录，需要执行以下步骤：

1.查找Sue Smith的客户记录；

2.根据Sue Smith的客户记录链接到她的账户列表；

3.遍历账户链，直到找到货币市场账户；

4.通过链接从货币市场记录找到其交易列表。

上图（1-2）最右边的一组产品记录（Products）展示了网状数据库系统的一个有趣特性。请注意，每个产品记录（Checking、Savings等）都指向该产品类型的帐户记录列表。因此，可以从多个位置（客户记录和产品记录）访问帐户记录，这使得网状数据库具备多层次结构。

层次数据库系统和网状数据库系统如今仍然存在，不过通常在大型机领域中使用。此外，层次数据库系统在目录服务领域有了新的应用，比如微软的Active Directory和开源Apache目录服务器。然而，从20世纪70年代开始，一种新的表示数据的方法开始生根发芽，这种方法更加严谨，并且易于理解和实现。

2.关系模型

1970年，IBM研究实验室的E.F.Codd博士发表了一篇题为“大型共享数据库的数据关系模型（A Relational Model of Data for Large Shared Data Banks）”的论文，提出将数据表示为一组表。冗余数据用于链接不同表中的记录，而不是使用指针在相关实体之间导航。如图（1-3）显示了George和Sue的账户信息在这种情况下的表示方式：

上图（1-3）中的四个表代表了迄今为止讨论的四个实体：Customer、Product、Account和Transaction。纵观上图（1-3）Customer表的顶部，你可以看到三列：cust_id（包含客户的ID号）、fname（包含客户的名字）和lname（包含客户的姓氏）。继续向下看Customer表，可以看到两个记录行，一行包含George Blake的数据，另一行包含Sue Smith的数据。一个表能包含的最大列数因服务器而异，但这个数据通常足够大，所以不用担心（比如，Microsoft 的SQL Server允许每个表最多包含1024列）。一个表可能包含的行数与其说是受到数据库服务器的限制，不如说是受到物理限制（即有多少磁盘驱动器空间可用）和可维护性（即在表中记录数量达到怎样的规模后仍然可以保持易用性）的问题。

关系数据库中的每个表都包含一项作为每行唯一标识的信息（称为主键），它与其他信息一起完整描述该条目。再看Customer表，cust_id列为每个客户保存了不同的编号；例如，George Blake可以由顾客ID 1来唯一标识，这个标识符永远不会被分配给其他客户，所以定位Customer表中George Blake的数据时并不需要其他的信息。

注意：每个数据库服务器都提供了一种机制，用于生成唯一的数字以用作主键值，因此你不必操心哪些数字已经被赋予为主键。

虽然我也可能选择使用fname和lname列的组合作为主键（由两个或更多列组成的主键称为复合主键），但实际上很容易出现两个或多个姓名都相同的人都在银行拥有帐户的情况。因此，选择Customer表中的cust_id列作为主键是更合适的。

注意：在本例中，选择fname/lname作为主键将被称为自然主键，而选择cust_id将被称为代理键（ surrogate key）。到底选哪一种键取决于数据库设计者，但在这种本例中的选择是显而易见的，因为一个人的姓可能会更改（例如，当一个人使用配偶的姓时），但是主键一旦分配了值就不能更改。

有些表还包含用于导航到另一个表的信息，这就是前面提到的“冗余数据”。例如，Account表包括一个名为cust_id的列，该列包含使用该帐户的客户的唯一标识，以及一个名为product_id的列，该列包含帐户所关联产品的唯一标识，这些列称为外键，它们的作用与账户信息网络结构中各实体之间的连线相同。如果你正在查看一个特定的帐户记录，并且希望了解有关该客户的更多信息，你可以获取cust_id列的值，并使用它在Customer表中查找相应的行（在关系数据库术语中，这个过程称为连接（join），会在第三章介绍，并在第五章和第十章进行深入探讨）。

多次存储相同的数据似乎很浪费，但是某些情况下使用冗余数据可以更清楚地体现关系模型。例如，Account表包含一列作为开户客户的唯一标识符是合适的，但是如果在Account表中包含该客户的名字和姓氏就不合适了。如果客户要更改其姓名，你需要确保数据库中只有一个地方保存客户的姓名；否则，数据可能会在一个地方更改，而不会在另一个地方更改，从而导致数据库中数据的不可靠（数据的不一致）。此数据的适当位置是Customer表，其他表中只应包括cust_id值。一个列包含多条信息也是不合适的，例如使用name列同时包含用户的姓和名，或者使用address列包含街道、城市、州和邮政编码信息。优化数据库设计以确保每个独立信息只存放在一个位置（外键除外）的过程称为规范化。

让我们回到上图（1-3）中的四个表，你可能想知道如何使用这些表来查找George Blake在checking上的账户交易。首先，在Customer表中找到George Blake的唯一标识符（主键）。然后，在Account表中找到一行，该行的cust_id列包含George的唯一标识符，其product_id列与Product表中name列等于“Checking”的行匹配。最后，通过匹配Account表的唯一标识account_id列来定位Transaction表中对应的行。这听起来可能很复杂，但其实在SQL语言中，用一个命令就可以完成这些任务了，稍后你将看到这一点。

3.一些术语

我在前面的章节中介绍了一些新的术语，所以也许是时候给出一些正式的定义了。下表（1-1）显示了本书余下部分使用术语的定义：

二、什么是SQL

根据Codd对关系模型的定义，他提出了一种称为DSL/Alpha的语言来处理关系表中的数据。Codd的论文发表后不久，IBM建立一个小组根据Codd的想法构建了一个原型。这个小组创建了一个简化的DSL/Alpha版本，他们称之为SQUARE。对SQUARE的改进产生了一种称为SEQUEL的语言，它最终被缩短为SQL。虽然SQL最初是一种用于操控关系数据库中的数据的语言，但在本书的最后你会知道，它已经发展成了一种跨各种数据库技术操作数据的语言。

距今为止，SQL已经有40多年的历史了，在这一期间它发生了巨大的变化。在20世纪80年代中期，美国国家标准协会（American National Standards Institute，ANSI）开始为SQL语言制定第一个标准，该标准于1986年发布，随后对其不断改进，在1989年、1992年、1999年、2003年、2006年、2008年、2011年和2016年发布了一系列新的SQL标准。除了对核心语言的改进之外，SQL语言还添加了新的特性，以结合面向对象等其他功能。后来的标准侧重于相关技术的集成，如可扩展标记语言（ extensible markup language，XML）和JavaScript对象表示法（JavaScript object notation，JSON）。

SQL与关系模型密切相关，因为SQL查询的结果是一个表（在本文中也称为结果集）。因此，只需存储查询的结果集，就可以在关系数据库中创建一个新的固定表。同样地，一个查询可以使用固定表和来自其他查询的结果集作为输入（我们将在第九章对此进行详细探讨）。

最后要注意一点：SQL不是任何短语的缩写（尽管许多人坚持认为它代表“结构化查询语言Structured Query Language”）。当提到该语言时，可以使用单独的字母（即S.Q.L）或使用sequel一词。

1.SQL语句的类别

SQL语言分为几个不同的部分：我们在本书中探讨的部分包括SQL案例（schema）语句，用于定义存储在数据库中的数据结构；SQL数据（data）语句，用于操作SQL案例语句所定义的数据结构；以及SQL事务（transaction）语句，用于开始、结束和回滚事务（将在第十二章介绍）。比如，要在数据库中创建一个新表，可以使用SQL 案例语句create table，而要在新表中插入数据则需要SQL数据语句insert。

为了让你了解这些句子是怎样的，下面给出了一个SQL案例语句，它创建了一个名为corporation的表：

CREATE TABLE corporation 
(corp_id SMALLINT, 
 name VARCHAR(30), 
 CONSTRAINT pk_corporation PRIMARY KEY (corp_id) 
);

该语句创建一个包含两列的表，列为corp_id和name，其中corp_id列被设置为表的主键。在第二章中，我们将探讨此语句更多的细节，比如MySQL提供的各种不同的数据类型。下面是一条SQL数据语句，它将向corporation表插入一行关于Acme Paper Corporation的数据：

INSERT INTO corporation (corp_id, name) 
VALUES (27, 'Acme Paper Corporation');

此语句向corporation表中添加一行数据，其中corp_id列的值为27，name列的值为Acme Paper Corporation。

最后，这里给出一条简单的select语句来检索刚刚创建的数据：

mysql< SELECT name
 -> FROM corporation
 -> WHERE corp_id = 27;
+------------------------+
| name |
+------------------------+
| Acme Paper Corporation |
+------------------------+

通过SQL案例语句创建的所有数据库元素都存储在一组称为数据字典的特殊表中。这种“关于数据库的数据”统称为元数据，我们将在第十五章中讨论它。与用户自己创建的表一样，数据字典表也可以通过select语句来查询，从而允许在运行时查看部署在数据库中的当前数据结构。例如，如果你被要求写一份报告来显示上个月创建的新帐户，那么你可以硬编码account表中的各个列名，也可以查询数据字典以确定当前列集，并在每次运行的时候动态生成报表。

本书的大部分内容涉及SQL语言的数据部分，其中包括select、update、insert和delete命令。SQL案例语句在第二章中演示，它将引导你完成一些简单表的设计和创建。一般来说，除了语法之外，SQL案例语句不需要太多的研究，而SQL数据语句虽然数量很少，却是非常值得研究的。因此，尽管我试图介绍很多SQL案例语句，但本书的大部分章节都会侧重于SQL数据语句。

2.SQL：非过程化语句

如果你以前使用过编程语言，那么你会习惯于定义变量和数据结构、使用条件逻辑（即if-then-else）和循环结构（即do-while-end），并将代码分解为可重用的小片段（即对象、函数、过程）。你的代码会被交付给编译器，给出的可执行文件能够精确地（但其实不是总是精确的）符合你编程的预期。无论你使用的是Java、Python、Scala还是其他一些过程化语言，你都可以完全控制程序的功能。

注意：过程化语言定义了期望的结果和生成结果的机制或过程。非过程化语言也定义了期望的结果，但是生成结果的过程留给了外部代理来定义。

然而使用SQL需要放弃一些对过程的控制，因为SQL语句定义了必要的输入和输出，而语句的执行方式交付给数据库引擎的一个组件——优化器（optimizer）。优化器的任务是查看SQL语句，并考虑表的配置方式和有无索引可用等，以确定最有效的执行路径（当然，并不总是最有效的）。大多数数据库引擎都允许通过指定优化器选项来影响优化器的决策，例如建议使用特定的索引等。然而，大多数SQL用户永远都不需要考虑得这么复杂，而是会将它留给数据库管理员或性能调优专家来处理。

因此只使用SQL是无法编写完整的应用程序的，除非是一些操作某些数据的简单脚本，否则一般都需要将SQL与你喜欢用的编程语言相集成。一些数据库供应商已经做到了这一点，例如Oracle的PL/SQL语言、MySQL的存储过程语言，还有Microsoft的Transact-SQL语言。使用这些语言的时候，SQL数据语句是该语言语法的一部分，能够将数据库查询与过程化命令无缝集成。但是，如果你使用的是非特定于数据库的语言（如Java或Python），则需要使用一些由数据库供应商提供的工具包/API来执行SQL语句。有些工具包是由数据库厂商提供的，而另一些则是由第三方厂商或开放源代码提供者所创建。下表（1-2）显示了将SQL集成到特定语言中的一些可用选项：

倘若你只需要以交互方式执行SQL命令，那么每个数据库开发商至少都会提供一个简单的命令行工具，用于将SQL命令提交到数据库引擎并检查结果。大多数开发商也提供了图形化工具，包括一个显示SQL命令的窗口和另一个显示SQL命令结果的窗口。此外，还有第三方工具，如SQuirrel，它通过JDBC连接到许多不同的数据库服务器。由于本书中的示例是针对MySQL数据库执行的，因此我使用MySQL安装文件中包含的mysql命令行工具来运行示例并格式化结果。

3.SQL示例

在本章前面，我承诺过会展示一个返回George Blake的checking账户上所有交易的SQL语句，废话少说，语句和查询结果如下：

SELECT t.txn_id, t.txn_type_cd, t.txn_date, t.amount 
FROM individual i 
 INNER JOIN account a ON i.cust_id = a.cust_id 
 INNER JOIN product p ON p.product_cd = a.product_cd 
 INNER JOIN transaction t ON t.account_id = a.account_id 
WHERE i.fname = 'George' AND i.lname = 'Blake' 
 AND p.name = 'checking account';
+--------+-------------+---------------------+--------+
| txn_id | txn_type_cd | txn_date | amount |
+--------+-------------+---------------------+--------+
| 11 | DBT | 2008-01-05 00:00:00 | 100.00 |
+--------+-------------+---------------------+--------+
1 row in set (0.00 sec)

这里就简单地分析一下该语句：该查询在individual表中查找姓名为George Blake的行，以及查询在product表中账户名为checking account的行，并通过account表将它们关联起来，然后返回transaction表中所有提交到该账户上面的交易信息内容，分为四列显示。

如果你碰巧知道George Blake的客户ID为8，并且checking账户的指定代码为“CHK”，那么你只需在account表中根据客户ID查找George Blake的checking账户，并使用账户ID查找相应的交易：

SELECT t.txn_id, t.txn_type_cd, t.txn_date, t.amount 
FROM account a 
 INNER JOIN transaction t ON t.account_id = a.account_id 
WHERE a.cust_id = 8 AND a.product_cd = 'CHK';

在接下来的章节中，我将介绍这些查询中的所有概念（并且还会涉及更多概念），但我至少在这里想展示一下它们的大致结构。

前面的查询包含三个不同的子句：select、from和where。几乎你遇到的每个查询都将至少包含这三个子句，当然还有几个子句可以用于更加特定的查询。三个字句的作用如下：

SELECT /* one or more things */ ...
FROM /* one or more places */ ...
WHERE /* one or more conditions apply */ ...

注意：大多数SQL实现都将/*和*/标记之间的所有文本视为注释。

构造查询时，你首先要做的通常是确定需要哪些表，然后将它们添加到from子句中。接下来，你需要向where子句添加查询条件，以便从这些表中筛选出你不感兴趣的数据。最后，你要确定需要检索不同表中的哪些列，并将它们添加到select子句中。下面是一个简单的示例，该例子展示了如何找到所有姓为“Smith”的客户：

SELECT cust_id, fname 
FROM individual 
WHERE lname = 'Smith';

此查询在individual表中搜索lname列与字符串“Smith”匹配的所有行，并从这些行返回cust_id和fname列。

除了查询数据库外，可能还需要插入和更新数据库中的数据。下面是一个简单的示例，说明如何在product表中插入新行：

INSERT INTO product (product_cd, name) 
VALUES ('CD', 'Certificate of Depysit')

啊咧，看来你把“Deposit”拼错了，不过没关系，你可以使用update语句：

UPDATE product 
SET name = 'Certificate of Deposit' 
WHERE product_cd = 'CD';

注意，update语句和select语句一样包含where子句，这是因为update语句必须识别要修改的行。在本例中，指定只修改product_cd列与字符串“CD”匹配的行。由于product_cd列是product表的主键，因此应该期望update语句只修改一行（如果表中不存在该值，则为零行）。每当你执行SQL data语句时，你都会收到来自数据库引擎的反馈，显示其中有多少行受本次执行语句的影响。如果你使用的是交互式工具，如前面提到的mysql命令行工具，那么你将收到以下关于操作影响行数的反馈：

• 由select语句返回

• 由insert语句创建

• 由update语句修改

• 由delete语句删除

如果你将过程化语言与前面提到的某个工具箱一起使用，那么该工具箱将包含一个调用，以便在执行SQL数据语句后获得此信息。一般来说，最好是检查此信息以确保语句没有执行意外操作（比如忘记在delete语句中编写where子句，导致删除表中的所有行）。

三、什么是MySQL

关系数据库已经商业化三十多年了，几种最成熟和流行的商业产品包括：

• Oracle公司的Oracle Database

• Microsoft的SQL Server

• IBM提供的DB2 Universal Database

所有这些数据库服务器都执行大致相同的操作，尽管其中的一些更适合运行大容量和高吞吐量的数据库，而另一些更擅长处理对象、大文件或XML文档等。此外，所有这些服务器都遵从最新的ANSI SQL标准。这是一件好事，我将重点向你展示如何编写标准SQL语句，以便不进行或者只进行少量修改就可以在这些平台上运行。

随着商业数据库服务器的出现，在过去的二十年中，开源社区正在为创建一个可行的替代案例而努力。最常用的两个开源数据库服务器是PostgreSQL和MySQL。MySQL服务器是免费的，并且非常便于下载和安装。基于这些原因，本书的所有示例都将在MySQL（8.0版）运行，并使用mysql命令行工具格式化查询结果。即便你使用的是另外一种数据库并且也没打算使用MySQL，我还是建议你安装最新的MySQL服务器，加载示例模式和数据，并尝试使用本书中的数据和示例。

但是，还请牢牢记住以下告诫：

本书并不是一本关于MySQL的SQL实现教程。

相反，本书的目的是教你如何编写SQL语句，这些语句将在MySQL上运行而不做任何修改，并且在做很少或根本不做任何修改的情况下移植到Oracle Database、DB2和SQL Server上。

四、不只是SQL

在本书出版第二版和第三版的十年间，数据库领域内发生了很多事情。尽管关系型数据库现在还在大量使用，并且也将持续一段时间，但是新的数据库技术已经出现并满足了亚马逊和谷歌等公司的需求。这些技术包括Hadoop、Spark、NoSQL和NewSQL，它们是分布式、可扩展的系统，通常部署在商品服务器集群上。虽然详细探讨这些技术并不在本书的范围之内，但是它们却和关系数据库有一些共同点：SQL。

由于组织经常使用多种技术存储数据，因此需要从特定的数据库服务器中“拔出”SQL，并提供一种可以跨多个数据库的服务。例如，一份报表可能需要将存储在Oracle、Hadoop、JSON文件、CSV文件和Unix日志文件中的数据合并在一起。新一代的工具已经被用来应对这类挑战，其中最有前途的是Apache Drill，它是一个开源的查询引擎，允许用户编写查询来访问存储在大多数数据库或文件系统中的数据。我们将在第十八章中探讨Apache Drill。