第1章 一点背景知识

在我们卷起袖子开始学习之前，先介绍一些基本的数据库概念，了解一下计算机化数据存储与检索的发展历程，这可能会有所帮助。

数据库简介

数据库不过是一组相关信息的集合。例如，电话簿就是一个包含特定地区所有居民姓名、电话号码和地址的数据库。虽然电话簿无疑是一种无处不在且经常使用的数据库，但它存在以下问题：

• 查找某人的电话号码可能会很耗时，尤其是当电话簿包含大量条目时。
• 电话簿仅按姓氏/名字进行索引，所以从理论上讲，查找住在特定地址的人的姓名是可行的，但实际上这个数据库并不适合这样使用。
• 从电话簿印刷出来的那一刻起，随着人们迁入或迁出该地区、更改电话号码，或者在同一地区内搬到其他地方，其中的信息就会越来越不准确。

电话簿存在的这些缺点也适用于任何手动数据存储系统，比如存放在文件柜中的患者病历。由于纸质数据库使用起来很不方便，早期开发的一些计算机应用程序便是数据库系统，即计算机化的数据存储和检索机制。因为数据库系统以电子方式而非纸质形式存储数据，所以它能够更快地检索数据，以多种方式对数据进行索引，并为用户提供最新信息。

早期的数据库系统管理存储在磁带中的数据。由于磁带的数量通常远远多于磁带读取器，技术人员需要根据特定数据请求来装卸磁带。由于那个时代的计算机内存非常小，对相同数据的多次请求通常需要从磁带中多次读取数据。虽然这些数据库系统相比纸质数据库有了很大的改进，但与如今的技术所能实现的相比，仍有很大差距。（现代数据库系统可以管理分布在许多快速访问磁盘驱动器上的数TB数据，并将其中几十GB的数据存储在高速内存中，不过这有点超前了。）

非关系型数据库系统

本节包含一些关于关系型数据库系统出现之前的数据库系统的背景信息。对于那些渴望深入学习SQL的读者，可以直接跳到下一部分，往后翻几页即可。

在计算机化数据库系统发展的最初几十年里，数据以各种方式存储并呈现给用户。例如，在层次数据库系统中，数据以一个或多个树状结构呈现。图1-1展示了乔治·布莱克（George Blake）和苏·史密斯（Sue Smith）的银行账户相关数据如何通过树状结构呈现。

图1-1. 账户数据的层次视图

乔治和苏各自有自己的树状结构，包含他们的账户以及这些账户上的交易记录。层次数据库系统提供了用于定位特定客户树状结构的工具，然后遍历该树状结构以查找所需的账户和/或交易记录。树中的每个节点可能有零个或一个父节点，以及零个、一个或多个子节点。这种配置被称为单父层次结构。

另一种常见的方法是网络数据库系统，它展示了一组记录和一组定义不同记录之间关系的链接。图1-2展示了乔治和苏的相同账户在这样一个系统中的呈现方式。

图1-2. 账户数据的网络视图

为了查找苏的货币市场账户的交易记录，你需要执行以下步骤：

1. 找到苏·史密斯的客户记录。
2. 从苏·史密斯的客户记录跟随链接找到她的账户列表。
3. 遍历账户链，直到找到货币市场账户。
4. 从货币市场记录跟随链接找到其交易记录列表。

网络数据库系统有一个有趣的特性，在图1-2最右侧的产品记录中得以体现。注意，每个产品记录（支票、储蓄等）都指向属于该产品类型的账户记录列表。因此，账户记录可以从多个地方访问（既可以从客户记录访问，也可以从产品记录访问），这使得网络数据库可以作为一种多父层次结构。

层次数据库系统和网络数据库系统如今仍然存在并且应用广泛，不过通常是在大型机领域。此外，层次数据库系统在目录服务领域重新兴起，比如微软的Active Directory、红帽目录服务器，以及在可扩展标记语言（XML）中也有应用。然而，从20世纪70年代开始，一种新的数据表示方式开始扎根，这种方式更加严谨，同时又易于理解和实现。

关系模型

1970年，IBM研究实验室的E. F. Codd博士发表了一篇题为《大型共享数据库的数据关系模型》的论文，提出将数据表示为一组表。与使用指针在相关实体之间导航不同，关系模型使用冗余数据来链接不同表中的记录。图1-3展示了乔治和苏的账户信息在这种情况下的呈现方式。

图1-3. 账户数据的关系视图

图1-3中有四个表，分别代表到目前为止讨论的四个实体：客户、产品、账户和交易。看一下图1-3中客户表的顶部，你可以看到三列：cust_id（包含客户的ID号）、fname（包含客户的名字）和lname（包含客户的姓氏）。再看客户表的侧面，你可以看到两行，一行包含乔治·布莱克的数据，另一行包含苏·史密斯的数据。不同数据库服务器中表的列数可能不同，但通常都足够多，不会成为问题（例如，Microsoft SQL Server允许每个表最多有1024列）。表的行数更多地取决于物理限制（即有多少磁盘驱动器空间可用）和可维护性（即表在变得难以处理之前能达到多大规模），而不是数据库服务器的限制。

关系数据库中的每个表都包含用于唯一标识表中一行的信息（称为主键），以及完整描述该实体所需的其他信息。再看一下客户表，cust_id列给每个客户分配了不同的编号；例如，乔治·布莱克可以通过客户ID为1来唯一标识。不会有其他客户被分配这个标识符，并且在客户表中查找乔治·布莱克的数据也不需要其他信息。

每个数据库服务器都提供了一种生成唯一编号集以用作主键值的机制，所以你不必担心跟踪哪些编号已被分配。

虽然我本可以选择使用fname和lname列的组合作为主键（由两个或更多列组成的主键称为复合键），但很容易出现两个或更多人姓名相同且都在银行有账户的情况。因此，我特意在客户表中添加了cust_id列，专门用作主键列。

在这个例子中，选择fname/lname作为主键被称为自然键，而选择cust_id则被称为代理键。关于是采用自然键还是代理键的问题存在广泛的争论，但在这种特定情况下，选择很明确，因为一个人的姓氏可能会改变（比如某人随配偶改姓），而主键列一旦赋值就不应再更改。

有些表还包含用于导航到另一个表的信息；这就是前面提到的“冗余数据”的用武之地。例如，账户表包含一个名为cust_id的列，其中包含开设该账户的客户的唯一标识符，以及一个名为product_cd的列，其中包含该账户所对应的产品的唯一标识符。这些列被称为外键，它们的作用与层次结构和网络结构版本的账户信息中连接实体的线条相同。如果你查看某个特定的账户记录，并且想了解更多关于开设该账户的客户的信息，你可以获取cust_id列的值，并使用它在客户表中找到相应的行（在关系数据库术语中，这个过程称为连接；连接将在第3章中介绍，并在第5章和第10章中深入探讨）。

多次存储相同的数据可能看起来很浪费，但关系模型对于哪些冗余数据可以存储有明确的规定。例如，在账户表中包含开设账户的客户的唯一标识符的列是合理的，但在账户表中同时包含客户的名字和姓氏就不合理了。例如，如果一个客户改了名字，你需要确保数据库中只有一个地方存储客户的姓名；否则，数据可能在一个地方被更改，而在另一个地方却没有更改，导致数据库中的数据不可靠。存储这些数据的合适位置是客户表，其他表中应只包含cust_id值。同样，一个列也不应该包含多条信息，比如一个名字列既包含一个人的名字又包含姓氏，或者一个地址列包含街道、城市、州和邮政编码信息。优化数据库设计，确保每个独立的信息片段只存储在一个地方（外键除外）的过程称为规范化。

回到图1-3中的四个表，你可能想知道如何使用这些表来查找乔治·布莱克支票账户的交易记录。首先，你要在客户表中找到乔治·布莱克的唯一标识符。然后，在账户表中找到cust_id列包含乔治唯一标识符，且product_cd列与产品表中name列等于“Checking”的行相匹配的行。最后，在交易表中找到account_id列与账户表中的唯一标识符相匹配的行。这听起来可能很复杂，但正如你很快会看到的，使用SQL语言，你可以用一条命令完成这个操作。

一些术语

在前面的章节中我介绍了一些新术语，或许现在是时候给出一些正式定义了。表1-1展示了本书后面会用到的术语及其定义。

术语	定义
实体	数据库用户群体感兴趣的事物。例如客户、零件、地理位置等。
列	存储在表中的单个数据项。
行	一组列，共同完整描述一个实体或对一个实体的某种操作。也称为记录。
表	一组行，存储在内存中（非持久化）或永久存储设备上（持久化）。
结果集	非持久化表的另一个名称，通常是SQL查询的结果。
主键	可用于唯一标识表中每一行的一个或多个列。
外键	可共同用于标识另一个表中单个行的一个或多个列。

什么是SQL？

除了定义关系模型，Codd还提出了一种名为DSL/Alpha的语言，用于操作关系表中的数据。Codd的论文发表后不久，IBM委托一个团队基于他的理念构建一个原型。这个团队创建了DSL/Alpha的简化版本，并将其命名为SQUARE。对SQUARE进行改进后，产生了一种名为SEQUEL的语言，最终该语言更名为SQL。

如今，SQL已经“人到中年”（可惜笔者也是），在此过程中经历了诸多变化。20世纪80年代中期，美国国家标准协会（ANSI）开始制定SQL语言的首个标准，并于1986年发布。随后的改进使得SQL标准在1989年、1992年、1999年、2003年和2006年都有新版本发布。除了对核心语言进行改进，SQL语言还增加了新功能，以融入面向对象的功能等。最新的标准SQL:2006聚焦于SQL与XML的集成，并定义了一种名为XQuery的语言，用于查询XML文档中的数据。

SQL与关系模型紧密相关，因为SQL查询的结果是一个表（在这种情况下也称为结果集）。因此，在关系数据库中，只需存储查询的结果集就可以创建一个新的永久表。同样，一个查询可以将永久表和其他查询的结果集都作为输入（我们将在第9章详细探讨这一点）。

最后要说明一点：SQL并不是任何事物的首字母缩写（尽管很多人坚持认为它代表“结构化查询语言”）。在提及这门语言时，逐个说出字母（即S. Q. L.）或使用“sequel”这个单词都是可以接受的。

SQL语句分类

SQL语言分为几个不同部分：本书中我们探讨的部分包括SQL模式语句（用于定义存储在数据库中的数据结构）、SQL数据语句（用于操作之前通过SQL模式语句定义的数据结构）以及SQL事务语句（用于开始、结束和回滚事务，将在第12章介绍）。例如，要在数据库中创建一个新表，你会使用SQL模式语句CREATE TABLE；而向新表中填充数据的过程则需要使用SQL数据语句INSERT。

为了让你了解这些语句的样子，下面是一条创建名为corporation表的SQL模式语句：

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

这条语句创建了一个包含两列（corp_id和name）的表，并将corp_id列指定为该表的主键。我们将在第2章深入探讨这条语句的细节，比如MySQL中可用的不同数据类型。接下来，这是一条向corporation表中插入一行数据（代表Acme Paper Corporation公司）的SQL数据语句：

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模式语句创建的所有数据库元素都存储在一组名为数据字典（data dictionary）的特殊表中。这些 “关于数据库的数据” 统称为元数据（metadata），我们将在第15章进行探讨。就像你自己创建的表一样，数据字典表也可以通过SELECT语句进行查询，这样你就能在运行时发现数据库中当前部署的数据结构。例如，如果你被要求编写一份报告，展示上个月创建的新账户，你既可以在编写报告时硬编码已知的账户表列名，也可以查询数据字典来确定当前的列集，并在每次执行报告时动态生成报告。

本书的大部分内容都围绕SQL语言的数据部分展开，其中包括SELECT、UPDATE、INSERT和DELETE命令。SQL模式语句将在第2章进行演示，本书中使用的示例数据库就是在这一章生成的。一般来说，除了语法之外，SQL模式语句不需要太多讨论；而SQL数据语句虽然数量不多，但有很多深入研究的空间。因此，虽然我会向你介绍许多SQL模式语句，但本书的大多数章节还是集中讨论SQL数据语句。

SQL：一种非过程化语言

如果你以前使用过编程语言，那么你一定习惯了定义变量和数据结构、使用条件逻辑（即if-then-else）和循环结构（即do while...end），以及将代码分解为小型的、可复用的部分（即对象、函数、过程）。你的代码会被交给编译器处理，生成的可执行文件会（嗯，并不总是完全）按照你的编程意图执行。无论你使用的是Java、C#、C、Visual Basic还是其他过程化语言，你都能完全控制程序的行为。

过程化语言既定义了期望的结果，也定义了生成结果的机制或过程。非过程化语言同样定义了期望的结果，但生成结果的过程则交给外部代理处理。

然而，使用SQL时，你需要放弃一些你习惯的控制权，因为SQL语句只定义了必要的输入和输出，而语句的执行方式则由数据库引擎中的一个名为优化器（optimizer）的组件决定。优化器的工作是分析你的SQL语句，同时考虑表的配置方式和可用的索引，从而确定最有效的执行路径（嗯，并不总是最有效的）。大多数数据库引擎允许你通过指定优化器提示（optimizer hints）来影响优化器的决策，比如建议使用某个特定的索引；不过，大多数SQL用户永远不会深入到这个层面，而是将这类调整工作交给数据库管理员或性能专家。

因此，仅靠SQL你无法编写完整的应用程序。除非你只是编写一个简单的脚本来处理某些数据，否则你需要将SQL与你喜欢的编程语言集成。一些数据库供应商已经为你完成了这种集成，比如甲骨文（Oracle）的PL/SQL语言、MySQL的存储过程语言以及微软（Microsoft）的Transact-SQL语言。在这些语言中，SQL数据语句是语言语法的一部分，这使你能够将数据库查询与过程化命令无缝集成。然而，如果你使用的是像Java这样的非特定于数据库的语言，你就需要使用工具包或应用程序编程接口（API）从代码中执行SQL语句。其中一些工具包由数据库供应商提供，而另一些则由第三方供应商或开源提供者创建。表1 - 2展示了将SQL集成到特定语言中的一些可用选项。

语言	工具包
Java	JDBC（Java数据库连接，JavaSoft）
C++	Rogue Wave SourceProDB（用于连接Oracle、SQL Server、MySQL、Informix、DB2、Sybase和PostgreSQL数据库的第三方工具）
C/C++	Pro*C（Oracle）、MySQL CAPI（开源）和DB2调用级接口（IBM）
C#	ADO.NET（Microsoft）
Perl	Perl DBI
Python	Python DB
Visual Basic	ADO.NET（Microsoft）

如果你只需要交互式地执行SQL命令，每个数据库供应商至少会提供一个简单的命令行工具，用于向数据库引擎提交SQL命令并查看结果。大多数供应商还会提供一个图形化工具，其中一个窗口显示你的SQL命令，另一个窗口显示SQL命令的执行结果。由于本书中的示例都是在MySQL数据库上执行的，所以我使用MySQL安装包中自带的mysql命令行工具来运行示例并格式化结果。

SQL示例

在本章前面，我承诺会给你展示一条SQL语句，它能返回George Blake支票账户的所有交易记录。话不多说，这条语句如下：

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表中找到 “支票账户” 产品的记录行，然后在account表中找到该个人与产品组合对应的记录行，最后从transaction表中返回该账户所有交易记录的四列数据。如果你恰好知道George Blake的客户ID是8，并且支票账户的代码是CHK，那么你可以直接根据客户ID在account表中找到George Blake的支票账户，然后使用账户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 /* 一个或多个内容 */...
FROM /* 一个或多个来源 */...
WHERE /* 一个或多个条件适用 */...

大多数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数据语句时，数据库引擎都会反馈该语句影响了多少行数据。如果你使用的是像前面提到的mysql命令行工具这样的交互式工具，那么你会收到关于以下内容的反馈：

• SELECT语句返回的行数
• INSERT语句创建的行数
• UPDATE语句修改的行数
• DELETE语句删除的行数

如果你使用的是前面提到的某个工具包与过程化语言相结合的方式，那么在执行完SQL数据语句后，该工具包会提供一个调用方法来获取这些信息。一般来说，检查这些信息是个好习惯，这样可以确保你的语句没有产生意外的结果（比如你忘记在DELETE语句中添加WHERE子句，结果删除了表中的所有行！）。

什么是MySQL？

关系型数据库在商业领域已经存在了二十多年。一些最为成熟且受欢迎的商业产品包括：

• 甲骨文公司（Oracle Corporation）的Oracle数据库
• 微软（Microsoft）的SQL Server
• IBM的DB2通用数据库
• 赛贝斯（Sybase）的自适应服务器（Adaptive Server）

所有这些数据库服务器的功能大致相同，不过有些更适合运行超大型或高吞吐量的数据库；有些则在处理对象、超大型文件或XML文档等方面表现更出色，诸如此类。此外，所有这些服务器在遵循最新的ANSI SQL标准方面都做得相当不错。这是一件好事，我会着重向你展示如何编写在这些平台上几乎无需修改就能运行的SQL语句。

除了商业数据库服务器，在过去五年里，开源社区也十分活跃，旨在创建商业数据库服务器的可行替代品。两个最常用的开源数据库服务器是PostgreSQL和MySQL。MySQL官方网站（http://www.mysql.com）目前宣称其安装量已超过1000万，而且其服务器是免费提供的。我发现它的服务器下载和安装都非常简单。基于这些原因，我决定本书中的所有示例都在MySQL（6.0版本）数据库上运行，并使用mysql命令行工具格式化查询结果。即便你已经在使用其他数据库服务器，并且从未打算使用MySQL，我还是建议你安装最新的MySQL服务器，加载示例模式和数据，用本书中的数据和示例进行试验。

不过，请记住以下注意事项：

本书并非关于MySQL的SQL实现。

相反，本书旨在教你如何编写在MySQL上无需修改即可运行的SQL语句，并且这些语句在Oracle数据库、赛贝斯自适应服务器和SQL Server的最新版本上只需进行很少的修改或无需修改就能运行。

为了使本书中的代码尽可能独立于数据库供应商，我不会展示MySQL的SQL语言实现中一些有趣但在其他数据库实现中无法做到的特性。相反，附录B会为打算继续使用MySQL的读者介绍其中的一些特性。

后续内容

接下来四章的总体目标是介绍SQL数据语句，特别着重讲解SELECT语句的三个主要子句。此外，你会看到许多使用银行模式（将在下一章介绍）的示例，本书中的所有示例都将使用这个模式。我希望通过熟悉单个数据库，你能够抓住示例的关键，而无需每次都停下来研究使用的表。如果一直使用同一组表让你感到有些乏味，你可以随意向示例数据库中添加更多的表，或者创建自己的数据库来进行试验。

在扎实掌握基础知识之后，后续章节将深入探讨更多的概念，其中大部分概念相互独立。因此，如果你感到困惑，可以先继续往后学习，之后再回过头来重新学习某一章。当你学完本书并完成所有示例后，你就离成为一名经验丰富的SQL从业者不远了。