数据库教程_从零开始学SQL数据库操作

第 2章 结构化查询语言简介 本章介绍结构化查询语言（SQL）。SQL 语句被分为两类：一类是数据操作语言（DML）语 句，它被用来查询和修改数据，另一类是数据定义语言（DDL）语句，它被用来创建表、联系和 其他的结构。本章只考虑用来查询数据的 DML语句；其余的用来插入、修改和删除数据的 DML 语句将在第 7章介绍。在第 7章中，我们将同时介绍 SQL的 DDL语句。 2.1 SQL的背景 SQL在 20世纪 70年代后期由 IBM公司开发，并于 1992年被美国国家标准化协会（ANSI） 认可为国家标准。本书所介绍的 SQL版本基于这个标准，它有时候被称为 SQL-92。一个以后的 版本，SQL3，结合一些面向对象的概念。这种较新的版本没有获得商业 DBMS厂商的足够重视， 因此对于实际的数据库处理来说不太重要。我们不在本书中讨论它。 不同于 Java或是 C#，SQL不是一种完整的编程语言。相反地，它被称为数据子语言，因为 它只包括那些用来创建和处理数据库数据和元数据的语句。可以通过多种不同的方式来使用 SQL 语句。可以将它们直接提交给 DBMS来处理；可以将 SQL语句嵌入到客户机/服务器应用程序中； 可以将它们嵌入到 Web 页面；可以将它们用于和数据抽取程序；同样也可以直接从 Visual Studio .NET和其他开发工具中执行 SQL语句。 SQL到处存在，因此 SQL编程是一项重要的技能。今天，所有的 DBMS产品都处理 SQL。 像在第 1章中解释的那样，如果读者使用过Microsoft Access，就已经使用过 SQL了，即使没有 意识到。每次处理一个表单，创建一个报表，或是运行一个查询，Access 都生成 SQL 语句，将 其发送给 Access 内部的 DBMS 引擎 Jet。要进行更多的基础数据库处理，读者需要揭示出被 Access所隐藏的 SQL。更进一步地，一旦你了解 SQL，相对于必须使用 Access 的图形化表单、 按钮和其他的工具来创建查询，就会发现以 SQL直接书写查询语句更为方便。 企业级的 DBMS系统比如 Oracle，DB2，SQL Server和MySQL①需要了解 SQL。在这些产品 中，所有的数据操作都是使用 SQL来表示的。 2.2 Cape Codd户外运动 Cape Codd户外运动是一个根据真实的户外零售设备供应商所虚构的公司。Cape Codd在遍及 美国和加拿大的 15个零售店铺中销售娱乐用途的户外设备。它同时通过 Internet上的Web店面应 用和邮件订单的方式销售商品。所有的零售销售都被存储在一个由 Oracle管理的销售数据库中， 如图 2.1所示。 ① MySQL 是一种开发源代码的 DBMS 产品，可以从 www.mysql.com下载。在 Linux 环境下，它相当流行。我们 会在第 14章对其进行一些介绍。 第 2章 结构化查询语言简介 23 图 2.1 Cape Codd零售销售抽取 2.2.1 零售数据抽取 Cape Codd的市场部门打算对于店铺内的销售进行一次分析。相应地，市场分析人员要求信 息服务部分从操作数据库中抽取零售销售数据。为进行市场研究，它们并不需要所有的订单数据。 它们需要的表和列在图 2.2中给出。 表 列 数据类型 OrderNumber Integer StoreNumber Integer StoreZip Character (9) OrderMonth Character (12) OrderYear Integer RETAIL_ORDER OrderTotal Currency OrderNumber Integer SKU Integer Quantity Integer Price Currency ORDER_ITEM ExtendedPrice Currency SKU Integer SKU_Description Character (35) Department Character (30) SKU_DATA Buyer Character (30) 图 2.2 零售销售抽取数据格式 需要三个表：RETAIL_ORDER，ORDER_ITEM和 SKU_DATA。RETAIL_ORDER表包含每 个订单的数据，ORDER_ITEM 表包含订单中每个项目的数据，而 SKU_DATA 包含每个 SKU 的 数据。这里 SKU 是库存单位的意思，读者可以认为 SKU 是每个 Cape Codd 公司所销售物品的 标识。 店铺 1 销售点应用 销售点应用 Oracle销售 数据库 邮购订单销售 销售点应用 零售店销售 数据抽取 销售抽取数 据库 Web店面 Internet 销售 店铺 15 Internet客户 店铺 2 ⋯ 数据库处理——基础、设计与实现 24 2.2.2 RETAIL_ORDER数据 如图 2.2所示，RETAIL_ORDER表有列 OrderNumber，StoreNumber，StoreZip（销售该订单 的商铺邮政编码），OrderMonth，OrderYear和 OrderTotal。我们只抽取有关零售店铺销售的数据。 其他类型的销售（来回票据和其他销售相关的事务）都在抽取过程中被去掉。 数据抽取过程只选择操作数据的几个列。销售点（POS）和其他的应用所处理的数据比这里 所给出的要多得多，同时它们保存数据的格式也不相同。例如，在 Oracle数据库中，原始的订单 数据以数据格式MM/DD/YYYY来存放 OrderDate（例如，10/22/2004代表 2004年 10月 22日）。 抽取程序将 OrderDate转化为市场部门需要的 OrderMonth和 OrderYear的格式。这种数据过滤和 转换是数据抽取过程中常见的。 图 2.3给出用于示例的 RETAIL_ORDER数据。第一行包含 OrderNumber 1000的数据抽取， 第二行包含 OrderNumber 2000的数据抽取，等等。 图 2.3 零售销售抽取的示例数据 2.2.3 ORDER_ITEM数据 ORDER_ITEM 表包含每个订单中所购物品的数据抽取。对应于订单中的每个 SKU，表中都 有相应的一行。要理解这个表，可以考虑一下你从零售商店中获得的销售回执，回执中包含每个 订单的数据。它包含订单的基本数据，比如日期和总额，并且每一行对应你所购买的一种商品。 ORDER_ITEM表中的一行就对应类似订单回执中的条目。 OrderNumber列和 RETAIL_ORDER表中的 OrderNumber列相关。SKU是库存单位的编号， 它和 SKU_DATA表中的 SKU相关（在下一节讨论）。Quantity是该订单中这个 SKU的购买数量。 Price 是每个物品的价格，ExtendedPrice 是由 Quantity 和 Price 相乘得到的。图 2.3 的下部给出 ORDER_ITEM数据。第一行和订单 3000，以及 SKU 100200相关。订单 3000中购买物品 100200， 并且 ExtendedPrice是 300。第二行和订单 2000相关，在该行中，4个物品 101100以每个 50美元 的价格被购买，因此 ExtendedPrice为 4乘 50，即 200。这个结构对于订单中的物品是很典型的， 我们会在第 5章和第 6章中再次看到它，在那里会为整个订单创建数据模型，并为这个数据模型 设计数据库。 顺便提一下，读者可能认为在一个订单中，所有行的 ExtendedPrice 之和应该等于 第 2章 结构化查询语言简介 25 RETAIL_ORDER表中的 OrderTotal列值。但是实际上它们并不相等。例如对于订单 3000， ExtendedPrice之和等于 300 + 100 + 50，即 450。然而订单 3000的OrderTotal值为 480。出 现这个差别的原因在于，OrderTotal中包含税、运输费和其他没有出现在数据抽取中的费用。 2.2.4 SKU_DATA数据 SKU_DATA 表包含列 SKU，SKU_Description，Department 和 Buyer。SKU 是一个整型值， 表示每一样 Cape Codd 出售的物品。SKU_Description 是一个对于每一样物品的简短文字描述。 Department和 Buyer标识负责购买该物品的部门和个人。和其他的表一样，这些列也是操作数据 库中所存储的 SKU数据的一个子集。 2.2.5 数据抽取是普遍的 在继续学习之前，首先请读者注意，这里所介绍的数据抽取过程并不仅仅是一个理论化的练 习。正相反，这种抽取过程是很现实、非常常见和重要的。目前，数以百计的全球商业公司正像 Cape Codd一样在创建抽取数据库。 在本章的下一节，读者会学习如果编写 SQL语句来处理这些抽取数据。这项知识非常有价值， 并且很实用。再重复一下，就在你阅读这段话的时候，数以百计的人们正在编写 SQL来从抽取数 据中获得信息。在本章所学习的 SQL将会是你作为知识工人、应用程序员或数据库管理员的重要 资产。花时间来学习 SQL；这项投资会在你的职业中回报丰厚的利润。 2.3 SQL的 SELECT/FROM/WHERE框架 本节介绍 SQL查询语句的基础语句框架。在我们讨论这个基础框架之后，读者会学习如何将 SQL语句提交给 Access，SQL Server和 Oracle执行。如果愿意，可以像处理本章剩下部分所解释 的 SQL语句那样，随着教材的进程来学习额外的 SQL语句。 2.3.1 从单一表中读取特定的列 从最简单开始。假设我们要获取 SKU_DATA表的 Department和 Buyer列值。读取这些数据 的 SQL语句如下所示： SELECT Department, Buyer FROM SKU_DATA; 当 DBMS为图 2.3中的数据处理这条语句时，结果为： SQL语句转换表从表开始，以某种方式处理表，最后以表的结构提供结果。即使处理的结果 数据库处理——基础、设计与实现 26 仅仅是一个单一的数字，这个数字也会被认为是一个一行一列的表。读者会在本章的后面学习到， 一些 SQL语句处理多个表。然而不管输入表的数目是多少，每条 SQL语句的结果都是一个单一 的输出表。 同时注意 SQL语句以分号结尾，这是 SQL-92标准所要求的。虽然一些 DBMS产品允许忽略 这个分号，但是另外一些有这个要求。因此请养成以分号结束 SQL语句的习惯。 SELECT语句中列名的顺序决定结果表中列的顺序。因此，如果在 SELECT语句中交换 Buyer 和 Department的顺序，它们也会在结果表中同样交换顺序。由此，SQL语句： SELECT Buyer, Department FROM SKU_DATA; 会产生这样的结果表： 注意在结果中有一些行是重复的。例如第一行和第二行中的数据是相同的。下面我们使用 DISTINCT关键字来消除重复： SELECT DISTINCT Buyer, Department FROM SKU_DATA; 这个语句的结果为： 所有的重复行都被消除。 顺便提一下，SQL不自动去除重复行的原因是因为做这件事情很浪费时间。为确定行是 否有重复，每一行都必须和其他行进行比较。如果在表中有 100 000行，检查会占用很 长的时间。因此默认情况下，并不删除重复。然而总是可以使用 DISTINCT关键字来强 迫删除重复行。 假如我们要查看 SKU_DATA表的所有列。为此我们可以在 SELECT语句中指定每个列，如 下所示： SELECT SKU, SKU_Description, Department, Buyer FROM SKU_DATA; 结果将会是包含 SKU_Data中所有行和所有 4个列的表。然而，SQL提供一种简洁的符号来查询 一个表的所有列，这种方式是使用一个星号： 第 2章 结构化查询语言简介 27 SELECT * FROM SKU_DATA; 结果为： 它包含 SKU_DATA表中的所有行和所有列数据。 2.3.2 从单一表中读取特定的行 假如我们要选择 SKU_DATA 表的所有列，而只要与水上运动部门相关的行。可以以如下的 方式来使用WHERE子句： SELECT * FROM SKU_DATA WHERE Department = 'Water Sports'; 这个 SQL语句的结果为： 在一个WHERE子句中，如果列包含字符或是日期类型，用于比较的值必须以单引号（' '） 引起来。如果这个列包含的是数字类型数据，则比较的值不需要在引号内。因此，要寻找所有 SKU 值大于 200 000的行，我们可以这样写： SELECT * FROM SKU_DATA WHERE SKU > 200000; 结果为： 如同前面所说明的那样，对于数字类型列值的比较不需要被括在括号内。同时注意在数字类 型值中，不使用逗号。 数据库处理——基础、设计与实现 28 2.3.3 从单一表中读取特定的列和行 到目前为止，我们已经可以选择特定列和所有行，以及所有列和特定行了。我们可以将这些 操作结合在一起，通过为需要的列命名和使用WHERE子句来选择特定列及特定行。例如，要选 择登山部门中所有产品的 SKU_Description和部门名称，可指定： SELECT SKU_Description, Department FROM SKU_DATA WHERE Department = 'Climbing'; 结果为： SQL并不需要在WHERE子句中使用的列也同样出现在 SELECT的列名列表中。因此，可以 指定： SELECT SKU_Description, Buyer FROM SKU_DATA WHERE Department = 'Climbing'; 在这里，限定列 Department并没有出现在 SELECT的列名列表中。结果为： 顺便提一下，标准练习中我们将 SQL语句的 SELECT，FROM和WHERE都写在单独的 行上。这只是一种编码惯例，事实上 SQL解析器并不要求这样。你可以这样书写最后一 条 SQL语句，SELECT SKU_Description，Buyer FROM SKU_DATA WHERE Department = 'Climbing'；所有的内容都在一行上，任何 DBMS产品仍然可以处理它。然而标准的多 行 SQL编码规则使得 SQL更容易阅读。我们鼓励读者按照这个标准来书写自己的 SQL。 2.3.4 向 DBMS提交 SQL语句 在继续介绍 SQL之前，学习如何向 DBMS提交 SQL语句是很有用的。通过这种方式，读者 可以在学习本书时，一边输入和运行 SQL语句，一边阅读对其的讨论。 顺便提一下，即使不在 DBMS上运行查询，也可以学习 SQL语句。如果出于某种原因， 读者并没有 Access，SQL Server或是 Oracle，不要绝望；你可以在没有它们的情况下学 习 SQL。情况可能是，你的教师和现在实践中的绝大多数人一样，都是在没有 DBMS 的情况下学习 SQL 的。只是如果可以在阅读时同时运行 SQL，则会更容易理解和记住 SQL语句。 具体提交 SQL语句的方法取决于 DBMS。这里我们将介绍在 Access，SQL Server和 Oracle 中提交 SQL的情况。 将 SQL提交给 Access 在可以执行 SQL 语句之前，需要一台已经安装 Access 的计算机，并且需要包含图 2.3 中的 第 2章 结构化查询语言简介 29 表和示例数据的 Access数据库。Access是许多版本的 Microsoft Office中的一部分，因此要找到 一台安装有它的计算机并不困难。 一些菜单选项在不同版本的 Access中有细微的差别。这里的讨论是基于 Access 2003。如果 读者拥有的是不同版本的 Access，命令可能有细微的差别。然而这并不是什么大问题。 读者有两种方式可以获得图 2.3 中的数据库、表和数据。首先，你可以从本书的 Web 站点 www.prenhall.com/kroenke下载名为 Chapter_2.mdb的 Access数据库。此外，你也可以使用附录 A 中所说明的过程来自己创建数据库，并添加图 2.3 中的表和数据。在继续以前，需要采取其中的 一种方式。 为在 Access中处理 SQL语句，打开数据库，并点击 Access窗口左上角的查询标签来创建一 个新的查询窗口。然后在数据库窗口的顶部点击 New选项，如图 2.4所示。点击 OK按钮来选择设 计视图，并点击显示表对话框的关闭（°）按钮（因为我们要自己输入 SQL语句，因此不必在这个 窗口中指定表）。接下来，在 Access菜单中选择 View选项，并点击 SQL View选项，如图 2.5所示。 图 2.4 在 Access中创建一个新的查询 图 2.5 在 Access中选择 SQL View选项 接着，在出现的空白窗口中输入 SQL语句。如图 2.6所示，输入如下的 SQL语句： SELECT * FROM SKU_DATA; 数据库处理——基础、设计与实现 30 在输入 SQL语句后，在 Access菜单中点击 Query选项，然后像图 2.6一样点击 Run选项。结果 如图 2.7所示。 图 2.6 在 Access中运行 SQL代码 图 2.7 在 Access中执行查询的结果 如果要修改查询语句，或是键入新的内容，在 Access菜单中选择 View选项，并和前面一样 选择 SQL View选项。在这里，可以修改 SQL语句，或是键入一条新语句。也可以在查询窗口激 活时，通过选择 File/Save选项来存储这条语句。 顺便提一下，Microsoft Access是一个针对初学者的 DBMS产品。它并不能正确处理本 章所介绍的全部 SQL语句。而这里所展示的都是读者应该掌握的标准 SQL语句。我们不 将讨论仅仅限定于 Access可以处理的语句，而是介绍所有重要的 SQL语句，并标记那些 Access不能处理的东西（在 Access中无效）。Access不能处理某些 SQL语句是很令人烦 心的。如果这让读者感到困扰，写信给Microsoft，告诉他们去修改 Access的 SQL解析器。 将 SQL提交给 SQL Server 在能够为 SQL Server输入 SQL语句之前，读者应该可以访问一台装有 SQL Server的计算机， 并有包含图 2.3 中给出的表和数据的数据库。你的教师可能已经在计算机实验室中安装了 SQL 第 2章 结构化查询语言简介 31 Server，并为你输入数据。如果是这样的话，按照他的指示来访问数据库。 另一种选择是，如果你购买与本书捆绑销售的 SQL Server试用版①，可以将该试用版安装在 任意运行Windows的计算机上，并创建图 2.3中的表和数据。你需要阅读第 11章中关于 SQL Server 的介绍。同样地，本书的Web站点包含可以帮助你在 SQL Server下创建 Cape Codd抽取数据库 的文件和指令。 最简单的在 SQL Server 中练习 SQL 的办法是在查询分析器中输入 SQL 语句，我们将在 第 11章中对此进行说明。图 2.8给出如下 SQL语句在 SQL Server查询分析器中的执行： SELECT * FROM SKU_DATA; 将 SQL提交给 Oracle数据库 在能够为 Oracle输入 SQL语句之前，读者应该可以访问一台装有 Oracle的计算机，并有包 含图 2.3中给出的表和数据的数据库。 图 2.8 使用 SQL Server的查询分析器执行 SQL 你的教师可能已经在计算机实验室中安装了 Oracle，并为你输入数据。如果是这样的话，按 照他的指示来访问数据库。另一种选择是，如果你购买与本书捆绑销售的 Personal Oracle的试用 版，可以将该试用版安装在任意运行Windows的计算机上，并创建图 2.3中的表和数据。我们将 在第 10章开始对 Oracle进行介绍，可以参照那里的指令进行。同样地，本书的Web站点包含可 以帮助你在 Oracle下创建 Cape Codd抽取数据库的文件和指令。 最简单的在 Oracle中练习 SQL的办法是在 Oracle的一个工具 SQL Plus中输入 SQL语句，我 们将在 10章中对此进行介绍。图 2.9给出如下 SQL语句在 SQL Plus的执行情况： SELECT SKU, DEPARTMENT, BUYER FROM SKU_DATA; 图 2.9 使用 Oracle的 SQL Plus执行 SQL ① 仅限于在美国的读者——编者注。 数据库处理——基础、设计与实现 32 读者也可以在 Oracle中使用图形化Windows工具来输入 SQL语句，我们在 10.2.6节对此进 行介绍。然而这样会使读者脱离真正数据库管理员所使用的经典 Oracle 环境。如果你打算为 Oracle工作，请至少使用一段时间的 SQL Plus。 2.4 查询单一表的 SQL 本节介绍更多的处理单一表的 SQL语句。随着本书的进展，读者会发现 SQL对于查询数据 库和从已有的数据中产生的信息是多么强大。作为图示，这里的输出由 SQL Server 生成，其他 DBMS产品的输出也是类似的。 2.4.1 将结果排序 SQL语句产生的行序是任意的，这是由每个 DBMS内部的程序所决定的。图 2.3中给出的 例子结果是由 Access 产生的，其顺序也由 Access 确定。Oracle，SQL Server，DB2 和其他的 DBMS产品会产生不同的顺序。同时注意在 ORDER_ITEM表中，对应订单 3000的行是分散在 结果中的。 如果你要求 DBMS以特定的顺序显示行，可以使用词语 ORDER BY。例如，下面的 SQL语句： SELECT * FROM ORDER_ITEM ORDER BY OrderNumber; 将会有如下的结果： 通过添加第二个列名，我们可以在两个列上排序。例如，为首先使用 OrderNumber排序，然 后在同一个 OrderNumber内使用 Price排序，我们这样写： SELECT * FROM ORDER_ITEM ORDER BY OrderNumber, Price; 结果为： 第 2章 结构化查询语言简介 33 如果希望首先使用 Price，然后再用 OrderNumber对数据排序，应该如下交换 ORDER BY子 句后列的顺序： SELECT * FROM ORDER_ITEM ORDER BY Price, OrderNumber; 对 Access用户的说明：不同于 SQL Server这里的输出情况，Access在现金数据的输出时添加了 美元符号。 默认情况下，行按照升序排列。为按照降序排列，在列名后面添加关键字 DESC。因此，为 首先按照 Price降序排列，再按照 OrderNumber升序排列，可以指定： SELECT * FROM ORDER_ITEM ORDER BY Price DESC, OrderNumber ASC; 结果为： 由于默认的顺序是升序，我们不需要在最后的 SQL语句中指明 ASC。因此，下面的 SQL语 句是等价的： SELECT * FROM ORDER_ITEM ORDER BY Price DESC, OrderNumber; 2.4.2 WHERE子句选项 SQL 包含一组 WHERE 子句的选项，这可以极大地扩展 SQL 的功能和应用。在本节中，我 们考虑三个选项：复合子句、范围和通配符。 复合WHERE子句 SQL的WHERE子句可以使用 AND，OR，IN和 NOT IN运算符来包含多个条件。例如，要 选择 SKU_DATA中所有部门名称为水上运动，而买主为 Nancy Meyers，我们可以写为： SELECT * FROM SKU_DATA WHERE Department = 'Water Sports' AND Buyer = 'Nancy Meyers'; 结果为： 数据库处理——基础、设计与实现 34 类似地，要选择 SKU_DATA中或者属于野营部门，或者属于登山部门的数据，可以编写： SELECT * FROM SKU_DATA WHERE Department = 'Camping' OR Department = 'Climbing'; 结果为： 三个或者更多的 AND和 OR条件可以组合使用，但在这种情况下，使用 IN和 NOT IN运算 符会更容易。例如，假设要获取 SKU_DATA中所有买主为 Nancy Meyers，Cindy Lo或 Jerry Martin 其中之一的行，我们可以构建一个包含两个 AND 的 WHERE子句。但另一种简单的途径是如下 面这样使用 IN关键字： SELECT * FROM SKU_DATA WHERE Buyer IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin'); 在这种格式中，一个值的集合被括在括号里。如果买主等于其中任意一个值，则所在的行被选中。 结果为： 类似地，如果我们要在 SKU_DATA中寻找买主不是 Nancy Meyers，Cindy Lo或 Jerry Martin 中任何一位，可以这样写： SELECT * FROM SKU_DATA WHERE Buyer NOT IN ('Nancy Meyers', 'Cindy Lo', 'Jerry Martin'); 结果为： 注意 IN和 NOT IN的重要区别。一行满足 IN的条件（如果它等于括号内的任意一个值）。而 一行满足 NOT IN的条件（如果它不等于括号中的所有值）。 WHERE子句中的范围 SQL的 WHERE 子句可以使用 BETWEEN 关键字来指定数据值的范围。例如，下面的 SQL 第 2章 结构化查询语言简介 35 语句： SELECT * FROM ORDER_ITEM WHERE ExtendedPrice BETWEEN 100 AND 200; 会产生如下的结果： 注意范围的上界和下界，即 100和 200，也被包含在结果表中。前面的 SQL语句相当于： SELECT * FROM ORDER_ITEM WHERE ExtendedPrice > = 100 AND ExtendedPrice < = 200; 同时注意，ORDER BY关键字可以和任意WHERE子句组合使用。 SELECT * FROM ORDER_ITEM WHERE ExtendedPrice BETWEEN 100 AND 200 ORDER BY OrderNumber DESC; 它将会产生这样的结果： WHERE子句中的通配符 可以在WHERE子句中使用关键字 LIKE来指定对于列值的部分匹配。例如，假设要在 SKU_ DATA表中寻找所有买主名为 Pete的行。为实现这个目的，我们使用如下带有通配符%的关键字 LIKE： SELECT * FROM SKU_DATA WHERE Buyer LIKE 'Pete%'; 百分号（%）是一个代表任意字符序列的通配符。当它和 LIKE 一起使用时，字符串'Pete%'意味 着所有以单词 Pete开头的字符串。这个查询的结果是： 顺便提一下，通配符%和下划线（_）字符是在 SQL-92标准中定义的。它们可以使用在 除Microsoft的 Access以外的所有 DBMS产品中。对于 Access，使用星号（*）代替%， 数据库处理——基础、设计与实现 36 使用问号（？）代替下划线。这个差异之所以存在，是由于 Access 的设计人员选择遵循 Microsoft DOS下的通配符，而不是 SQL-92的通配符。 假设我们要寻找在 SKU_DATA 的 SKU_Description 里，描述中的某个地方包含单词 Tent 的 行。由于 Tent可以出现在开始、结尾或者中间，我们需要在 LIKE短语的两端都加上通配符，如 下所示： SELECT * FROM SKU_DATA WHERE SKU_Description LIKE '%Tent%'; 结果为： 这个查询会找到在 SKU_Description中的任意地方出现单词 Tent的行。 有些时候我们需要在列的某个特定位置寻找某个特定的值。例如，假定在 SKU值的编码 中，从右侧开始的第三个位置上的 2 有特殊的含义，比如表明它是其他商品的变种。不管出 于什么原因，假定要寻找从右侧开始的第三个位置上是 2 的所有 SKU。假如我们使用如下的 SQL语句： SELECT * FROM SKU_DATA WHERE SKU LIKE '%2%'; 结果为： 这不是所要的结果，我们错误地选择了所有在 SKU值中任何地方有 2的行。 为准确地寻找到商品，我们不能使用 SKU LIKE'%2%'的表示。不同的是，我们必须使用下划 线（_）来表示单个、不确定的字符。下面的 SQL 语句会找到所有的 SKU_DATA 行，它在右侧 第三个位置上是 2： SELECT * FROM SKU_DATA WHERE SKU LIKE '%2_ _'; 注意到这里有两个下划线，一个代表右边的第一个位置，而另一个代表右边的第二个位置。结果为： 这就是我们想要的结果。 第 2章 结构化查询语言简介 37 2.5 在 SQL查询中进行计算 可以在 SQL查询语句中进行一些类型的算术运算。一类计算涉及到内置的 SQL函数的使用， 另一类涉及到在 SELECT语句中的列上进行简单的算法操作。我们依次考虑它们。 2.5.1 使用 SQL内建的函数 SQL提供 5个内置的函数用于在表的列上进行算术运算：SUM，AVG，MIN，MAX和 COUNT。 一些 DBMS产品扩展这些标准的内置函数来提供一些额外的函数。这里，我们重点讨论这 5个标 准函数。 假设要知道 RETAIL_ORDER 表中所有订单的 OrderTotal 之和，可以采用如下的方法来获得 这个总数： SELECT SUM (OrderTotal) FROM RETAIL_ORDER; 结果为： 回想一下，SQL 语句的结果总是一个表。在这里例子中，该表只包含一行和一列，其值为 OrderTotal之和。 OrderTotal的和并不是表中的一列，因此 DBMS不能为它提供列名。前述的结果来自于 SQL Server，它将该列命名为'（No column name）'。其他 DBMS产品所存取的举措也是类似的。 这个结果看上去不太好。我们希望有一个有意义的列名，SQL允许我们使用 AS关键字为其 设置一个列名。如果我们指定： SELECT SUM (OrderTotal) AS OrderSum FROM RETAIL_ORDER; 结果为： 这是一个更有意义的标签。这里的名字 OrderSum 是任意的，可以自由地选择我们认为对于用户 来说该结果有意义的名字。可以选择 OrderTotal_Total，OrderTotalSum或者任意其他的什么。 如果将内置函数用于WHERE子句，则可以增强其效用。例如，我们可以书写： SELECT SUM (ExtendedPrice) AS Order3000Sum FROM ORDER_ITEM WHERE OrderNumber = 3000; 结果为： 内置的函数可以在一个语句中混合和匹配，例如，我们可以书写： 数据库处理——基础、设计与实现 38 SELECT SUM (ExtendedPrice) AS OrderItemSum, AVG (ExtendedPrice) AS OrderItemAvg, MIN (ExtendedPrice) AS OrderItemMin, MAX (ExtendedPrice) AS OrderItemMax FROM ORDER_ITEM; 结果为： 函数 COUNT看上去和 SUM类似，但实际上是不同的。COUNT计算行的数目，而 SUM累 加列的值。如果我们书写： SELECT COUNT(*) AS NumRows FROM ORDER_ITEM; 结果为： 这个结果表明表中共有 7行。注意如果要计算行数的话，需要在 COUNT函数后加上一个星 号。COUNT是惟一一个需要星号的内置函数。此外，COUNT可以被应用于任意类型的数据，而 SUM，AVG，MIN和MAX只能被应用于数值型数据。 COUNT产生的结果在某些情况下可能令人意外。例如，假设要计算 SKU_DATA表中部门的 数目。如果写为： SELECT COUNT (Department) AS DeptCount FROM SKU_DATA; 结果为： 这是 SKU_DATA 表中行的数目，而不是不同部门值的数目。如果你要计算不同部门值的数目， 则需要使用如下的 DISTINCT关键字： SELECT COUNT (DISTINCT Department) AS DeptCount FROM SKU_DATA; {在 Access中失效} 结果为： 除分组（在后面定义）以外，不可以将表的列名和一个内置函数结合在一起。如果编写： SELECT Department, COUNT(*) FROM SKU_DATA; 在 SQL Server中的结果为： 第 2章 结构化查询语言简介 39 （这个出错信息是 SQL Server专有的。然而在 Access，Oracle或 DB2中，也会看到相类似 的信息）。 需要了解内置函数的其他限制，它们不可以使用于WHERE子句。因此，不能这样写： SELECT * FROM RETAIL_ORDER WHERE OrderTotal>AVG(OrderTotal); 试图这样编写语句也会导致来自于 DBMS的错误信息。在第 7章中，读者会学习如何使用一 系列的 SQL视图来获取想要的结果。 2.5.2 SELECT语句中的算术运算 可以在 SQL语句中进行基本的算术运算。例如假设为验证ORDER_ITEM表中数据的准确性， 要计算总价的值。我们这样书写 SQL语句来计算总价： SELECT Quantity * Price AS EP FROM ORDER_ITEM; 结果为： 如果我们打算将这个计算得到的值和原来存储的 ExtendedPrice值进行比较，可以这样写： SELECT Quantity * Price AS EP, ExtendedPrice FROM ORDER_ITEM; 结果为： 现在我们可以可视化地比较这两个值，以确保存储的值是准确的。 另一个 SQL 语句中表达式的用途是进行字符串操作。假设我们要将 Buyer 和 Department 列 结合成一个单一的名为 Sponsor的列。如果这样写这个语句： SELECT Buyer + 'in' + Department AS Sponsor 数据库处理——基础、设计与实现 40 FROM SKU_DATA; 结果为： 结果的形式不太好。我们可以使用更高级的函数来去除空白。其相关语法和使用方法在不同 的 DBMS 中是不同的。如果我们在这里对每个 DBMS 产品的不同特性进行说明，则会偏离讨论 的重点。如果读者要了解更多的信息，在所使用的 DBMS文档中搜索有关字符串函数的部分。 仅仅为说明这种可能性，我们这里给出有关 SQL Server 的语句，消除 Buyer 和 Department 右侧结尾的空白： SELECT DISTINCT RTRIM (Buyer) + 'in' + RTRIM (Department) AS Sponsor FROM SKU_DATA; 结果为： 这个结果在视觉上要好一些。 2.6 分组 在 SQL 中，可以使用 GROUP BY 关键字来对行依照相同的值进行分组。例如，假设在 SKU_DATA表上的 SELECT语句上指定 GROUP BY Department，DBMS会首先按照部门把所有 的行排序，然后将所有有相同部门值的行归成一组。针对于每一个不同的部门值，都会有相应的 一个组。 例如，SQL语句： SELECT Department, COUNT(*) AS Dept_SKU_Count FROM SKU_DATA GROUP BY Department; 产生如下的结果： 为获取这个结果，DBMS首先依照部门的值对行进行排序，然后计算具有相同部门值的行数。 另一个使用 GROUP BY的例子是： 第 2章 结构化查询语言简介 41 SELECT SKU, AVG (ExtendedPrice) AS AvgEP FROM ORDER_ITEM GROUP BY SKU; 该语句结果为： 这里行被按照 SKU进行排序和分组，然后计算每组 SKU物品的平均 ExtendedPrice。 可以在 GROUP BY表达式中使用不止一个列。例如，SQL语句： SELECT Department, Buyer, COUNT(*) AS Dept_Buyer_SKU_Count FROM SKU_DATA GROUP BY Department, Buyer; 首先按照部门，再按照买主的值，对行进行分组；然后计算每个部门值和买主值组合所对应的行 数。其结果为： 当使用 GROUP BY时，只有在 GROUP BY表达式中出现的列和 SQL的内置函数可以被应用 于 SELECT表达式。下面的表示会产生错误： SELECT SKU,Department,COUNT(*) AS Dept_SKU_Count FROM SKU_DATA GROUP BY Department; 这样的语句是错误的，因为对应每个部门组，会有多个不同的 SKU 值。DBMS 没有办法将多个 值放置在结果中。如果读者不理解这个问题，可以试着手工来处理这个语句，它是不能进行的。 当然，WHERE和 ORDER BY子句也可以在 SELECT语句中使用，如下所示： SELECT Department, COUNT(*) AS Dept_SKU_Count FROM SKU_DATA WHERE SKU <> 302000 GROUP BY Department ORDER BY Dept_SKU_Count; {在 Access中失效} 结果为： 注意登山部门信息的一行被从记数中删除了，因为它不满足 WHERE 条件。如果不使用 数据库处理——基础、设计与实现 42 ORDER BY子句，则行会以任意的部门顺序显示。当使用它以后，就可以给出顺序。 通常情况下，将WHERE放置在 GROUP BY之前。一些 DBMS产品不要求这个顺序，但另 一些有这个要求。为安全起见，总是将WHERE放在 GROUP BY的前面。 SQL提供另外一个 GROUP BY的特性，以扩展其功能。HAVING运算符限制在结果中出现 的组。我们可以限制前面的查询，只显示包含有超过一行的组，其写法如下： SELECT Department, COUNT(*) AS Dept_SKU_Count FROM SKU_DATA WHERE SKU <> 302000 GROUP BY Department HAVING COUNT (*) > 1 ORDER BY Dept_SKU_Count; {在 Access中无效} 结果为： 将这个结果与前面的结果比较，对应于攀登的一行（数量为 1）被去除。 顺便提一下，任何 SQL 的内置函数都可以用于 HAVING 子句。例如，下面是一个有效 的 SQL： SELECT COUNT(*) AS SKU_Count, SUM(Price) AS TotalRev, SKU FROM ORDER_ITEM GROUP BY SKU HAVING SUM (Price) = 100; 运行这个查询来查看结果。 注意当语句中同时包含WHERE和 HAVING子句时，可能有一些含糊的情况。最终的结果取 决于 WHERE 条件在 HAVING 之前还是之后应用。为消除这种含糊，WHERE 总是在 HAVING 之前被应用。 2.7 在 NASDAQ交易数据中寻找模式 在继续对于 SQL的讨论之前，考虑一个例子，来展示我们刚刚介绍的 SQL的能力。 假设一个朋友告诉你，她怀疑股票市场倾向于在一周的某些特定日子上涨，而在其他的日子 下跌。她请求你去检查过往的交易数据来判断这是否是事实。特别是她希望交易一个称为 NASDAQ 100的指数基金，该基金是一个针对 NASDAQ交易最大的 100家公司的交易所基金。 她提供你过去年份中 NASDAQ 100的交易数据以供分析。假定她提供的数据以关系数据库中一个 名为 NDX表的形式存储（可以在本书的网站 www.prenhall.com/kroenke找到这个表）。 2.7.1 检查数据的特性 假定你决定首先检查数据的一般特性。通过执行下面的查询来查看这个表中存在的列： SELECT * FROM NDX; 第 2章 结构化查询语言简介 43 该查询结果的前 5行数据如下： 假设你知道第一行是该基金在某个交易日休市时的值，而第二行则是前一个交易日休市时的 值，第三行是当天休市值和前一天休市值的差。Volume是所有交易的数目，而其他的数据都和交 易日期相关。 下面，你决定使用这个查询语句来检查交易价格的变动： SELECT AVG (ChangeClose) AS AverageChange, MAX (ChangeClose) AS MaxGain, MIN (ChangeClose) AS MaxLoss FROM NDX; 结果为： 顺便提一下，DBMS提供很多函数来格式化查询结果，以减少小数点后数字的显示位数， 为结果添加现金符号，比如$或£，或者进行其他的格式修改。然而这些函数是和 DBMS 相关的。使用术语 formatting results搜索你所使用 DBMS的文档，来了解更多有关这些 函数的信息。 仅仅是出于好奇，你决定寻找那些有最大和最小变动的日子。为避免在长数字串中寻找位置， 以进行相等的比较，使用大于和小于来对相近的值进行比较： SELECT ChangeClose, TMonth, TDayOfMonth, TYear FROM NDX WHERE ChangeClose > 398 OR ChangeClose < -400; 结果为： 结果是令人吃惊的！有什么理由最大的涨幅和最大的跌幅都出现在 1月 3日吗？你开始怀疑 是否你的朋友有一个有希望的想法。 2.7.2 在一周的日交易中寻找模式 希望确定是否在一周的日平均交易中存在着差异。相应地，创建如下的 SQL语句： SELECT TDayOfWeek, AVG (ChangeClose) AS AvgChange FROM NDX GROUP BY TDayOfWeek; 结果为： 数据库处理——基础、设计与实现 44 确实，在一周的不同交易日上似乎存在着差异。NASDAQ 100似乎在周一和周二下跌，而在 其他的三天中上涨。特别是周四，似乎是一个非常好的交易日。 然而，你开始怀疑，是否该模式在每年都成立呢？为回答这个问题，编写 SQL语句： SELECT TDayOfWeek, TYear, AVG (ChangeClose) AS AvgChange FROM NDX GROUP BY TDayOfWeek, TYear ORDER BY TDayOfWeek, TYear DESC; 因为有 20年的数据，这个查询结果共有 100行。为简化分析，决定将行限定为最近的 5年： SELECT TDayOfWeek, TYear, AVG (ChangeClose) AS AvgChange FROM NDX WHERE TYear > '1999' GROUP BY TDayOfWeek, TYear ORDER BY TDayOfWeek, TYear DESC; 这个