《MS SQL Server 2000管理员手册》系列——14. 使用 T-SQL 检索数据

14. 使用 T-SQL 检索数据
SELECT 陈述式
使用 T-SQL 函数
SELECT 的其它用法
本章总结
在本章中，您将学习如何使用 Transcat-SQL（T-SQL）的 SELECT 陈述式来检索资料，本章同时涵盖许多在 SELECT 陈述式中使用的选择性子句、搜寻条件和函数。这些元素在您提出查询时有助于您找到真正需要的数据。
SELECT 陈述式

尽管 SELECT 陈述式主要用于检索特定的数据，它同时也可以用来分配值给本域变量或呼叫函数，这部分内容会在本章最后的〈SELECT 的其它用途〉一节介绍。SELECT 陈述式可以很简单，也可以很复杂－当然能不复杂最好。当您还要再继续检索结果时，尽量使您的 SELECT 陈述式简单化。例如，如果您只需要一个数据表中的两个数据行的数据，那么只须把这两个数据行包括在 SELECT 陈述式中，以减少传回的数据量。
当您决定哪些数据要从哪些数据表回传后，您可以加入其它任何有必要的选项。这些选项包括：使用索引时，指定 WHERE 子句中要包括哪些数据行、指定传回的数据是否需要排序、指定是否只需要回传不同的数据。关于查询最佳化的相关信息，请参阅第35章。
让我们从检视 SELECT 陈述式中的不同选项和每个子句的检索范例讲起。本章范例中使用的数据库是 pubs 和 Northwind，这两个数据库在您安装 Microsoft SQL Server 2000 时即已自动建立。您可以使用 SQL Server Enterprise Manager 来检视 pubs 和 Northwind 数据库的数据表，以熟悉这两个数据库。
SELECT 陈述式的语法由几个选择性的子句组成。在大多数的情况下，一个SELECT 陈述式至少包括一条 SELECT 子句和 FROM 子句。这两条子句分别判断哪一个数据行或哪几个数据行的数据需要检索，以及从哪个数据表中检索数据。例如，对于 pubs 数据库的 authors 数据表，一条简单的 SELECT 陈述式如下：
SELECT au_fname, au_lname
FROM authors
如果您使用 OSQL 命令数据列 ( 第13章中介绍的 )，不要忘记使用 GO 命令执行陈述式。要使用 OSQL，SELECT 陈述式的完整的 T-SQL 语法如下：
USE pubs
SELECT au_fname, au_lname
FROM authors
GO

说明
由于关键词并不分大小写，您可以随意使用，但最好尽量保持一致，以便您的语法便于阅读。因此，本书中关键词均采用大写字母。

当您互动地使用 SELECT 陈述式时 (例如使用 OSQL 或 SQL Server 查询 Analyzer )，结果会显示在各数据行中，同时每一个数据行都以标题指明。 (有关 T-SQL 简介、OSQL 和查询 Analyzer 的介绍，请参考第13章 )
SELECT 子句

SELECT 子句由选择性的自变量和您所要求的选取清单组成。选取清单（select list）是一个包含表达式（expression）或数据行的清单，用以在 SELECT 子句中指明哪些数据行需要回传。下面介绍选择性的自变量和选取清单。
自变量

SELECT 子句使用下列两个自变量控制传回的数据数据列：
• DISTINCT 只传回唯一的资料列。如果选择的清单包括数个数据行，当至少有一数据行的数据值不同时，数据列会被视为是唯一的。如果有两数据列数据相同，它们必定在每一数据行中的值都相同。

• TOP n [PERCENT] 传回结果集合的前 n 数据列。如果指定了PERCENT，那么将只传回前 n 个百分比的资料列数。当使用 PERCENT 时，n 必须是介于 0 到 100 之间的数。如果查询中包括一条 ORDER BY 子句 (ORDER BY 子句将在本章后面〈ORDER BY子句〉一节中细述)，数据列会先按顺序排数据列，然后从排好的结果中传回前 n 资料列或前百分之 n 资料列。

下面的 T-SQL 语法显示了 SELECT 子句范例，它执行了三次，每次使用不同的自变量。第一次查询时使用 DISTINCT 自变量，第二次使用 TOP 50 PERCENT 自变量，第三次使用 TOP 5 自变量。
SELECT DISTINCT au_fname, au_lname
FROM authors
GO

SELECT TOP 50 PERCENT au_fname, au_lname
FROM authors
GO

SELECT TOP 5 au_fname, au_lname
FROM authors
GO
第一次查询传回 23 数据列，每一数据列均为唯一的。第二次传回 12 数据列 (大约为 50%，无条件进入 )，第三次传回 5 资料列。
选取清单

如上所述，选取清单是关于表达式或数据行的清单，用于在 SELECT 子句中指定哪些数据行的数据需要回传。表达式可以是数据行名称、函数或常数的清单。选取清单可以包括好几个表达式或数据行名称，彼此之间用逗号隔开。上面的例子使用了以下的选取清单：
au_fname, au_lname
星号或万用符号您可以使用星号 (* )或万用符号在选取清单中指定需要传回FROM 子句中指定数据表和检视表的所有数据和数据行。例如，传回 sales 数据表的所有数据行的数据列，可以使用下列查询：
SELECT *
FROM sales
GO
本章后面〈交叉联结〉一节将讲述当 SELECT * 陈述式的 FROM 子句资料列出了不只一个资料表的情形。
IDENTITYCOL和ROWGUIDCOL 要从数据表中的识别数据行（Identity Column）中检索数值，可以简单地在选取清单中使用 IDENTITYCOL 表达式。下面是查询 Northwind 数据库的范例，其中 Employess 数据表中定义了识别数据行：
USE Northwind
GO
SELECT IDENTITYCOL
FROM Employees
GO
结果集将如下面所示：
EmployeeID
----------
3
4
8
1
2
6
7
5
9
(影响9个数据列)
请您注意结果集中的数据行标题和数据库中具有 IDENTITY 属性的数据行的名称符合，在本例中是 EmployeeID。
同样的，您可以在选取清单中使用 ROWGUIDCOL 表达式检索资料列的通用唯一识别码（GUID）数据行，即具有 ROWGUIDCOL 属性的数据行。必须是 uniqueidentifier 数据型别的数据行才有 ROWGUIDCOL 属性。
数据行别名使用数据行别名可以使结果集中显示的数据行标题换成您希望的样子。使用别名可让输出数据行中的数据意义更清楚，可指派标题给函数中使用的数据行，亦可在 ORDER BY 子句中引用这些别名。
当在多个不同的资料表中拥有同名的数据行，为了易于分辨起见，您可能需要在输出的数据行标题中包含数据表的名称。举一个使用别名的例子。让我们看看 pubs 数据库中的 employee 数据表中的lname数据行。如果您执行以下的查询：
USE pubs
GO
SELECT lname
FROM employee
GO
将会得到如下的结果：
lname
--------
Cruz
Roulet
Devon
...
O'Rourke
Ashworth
Latimer

(影响43个数据列)
要使显示结果中的数据行标题 lname 换成「 Employee Last Name」，以强调lname的实质意义，可利用 AS 关键词：
SELECT lname AS "Employee Last Name"
FROM employee
GO
此命令显示的结果为：
Employee Last Name
------------------
Cruz
Roulet
Devon
...
O'Rourke
Ashworth
Latimer

(影响43个数据列)
您也可以在选取清单中使用其它类型的表达式和数据行别名，并作为 ORDER BY 子句的引用资料行。假设在选取清单中有一个函数被呼叫，您可以使用 AS 关键词来指定一个描述此函数输出的数据行别名。如果函数不用数据行别名，则根本就没有资料行标题。例如，下面的陈述式为 MAX 函数的输出指定了标题 Maximum Job ID：
SELECT MAX (job_id ) AS "Maximum Job ID"
FROM employee
GO
此别名上有引号，是因为它由多个字组成，而且之中有空格。如果别名中不包含空格，就可以不用引号。
您可以引用在 SELECT 子句中指定的数据行别名，作为 ORDER BY 子句的自变量。当选取清单中包含一个运算结果需要排序的函数时，这个技巧会十分有用。例如，以下的命令将检索每一间书店的销售量，并且将输出结果依量的大小排序。在选取清单中指派的别名将被应用在 ORDER BY 子句。
SELECT SUM (qty ) AS Quantity_of_Books, stor_id
FROM sales
GROUP BY stor_id
ORDER BY Quantity_of_Books
GO
本例中，别名不含空格，因此我们不需为别名加上引号。
如果我们并未替查询的 SUM (qty )数据行指定别名，下面的例子将输出同样的结果，但 sum 数据行则没有标题：
SELECT SUM (qty ), stor_id FROM sales
GROUP BY stor_id
ORDER BY SUM (qty )
GO
记住，数据行别名可让您分派标题给输出数据行，但是仅止于名称的改变；无论它们为何都不会影响查询的结果。
FROM 子句

FROM 子句可内含数据表的名称和用来选择数据的检视表。每个 SELECT 子句都会要求使用 FROM 子句，除非是选取清单内不含数据行名称，而且只有常数、变量、数学表达式。您已经看到一些使用 FROM 子句的例子，不过 FROM 子句也可以内含衍生的数据表、联结（Join）和别名。
衍生数据表

衍生数据表（derived table）是在 FROM 子句中内含另一个 SELECT 子句的结果集。被内含的 SELECT 子句的结果集被视为一个数据表，在该数据表以外的 SELECT 子句可以选择其数据。下面的查询使用一个衍生数据表寻找商店名称，此商店提供至少一种可能的折扣方式。
USE pubs
GO
SELECT s.stor_name
FROM stores AS s,(SELECT stor_id, COUNT (DISTINCT discounttype )
AS d_count
FROM discounts
GROUP BY stor_id ) AS d
WHERE s.stor_id = d.stor_id AND
d.d_count >= 1
GO
如果执行这个命令，会看到某一数据列被选取，表示数据库中仅有一家商店─Bookbeat－提供至少一种折扣。
注意此查询使用速记法来代表数据表名称（s代表stores资料表；d代表discounts资料表）。这种速记法称作数据表别名（Table Aliases），本章后面的〈资料表别名〉部分将会讨论这点。

说明
在 WHERE 子句中不能有衍生数据表。WHERE 子句中的 SELECT 陈述式是当成搜寻条件。本章后面的〈WHERE子句及搜寻条件〉部分将详细介绍这点。

联结资料表

联结数据表（join table）是两个更多数据表联结操作后产生的结果集。资料表之间可以执行的联结方式有：内部联结、完全外部联结、左外部联结、右外部联结及交叉联结等。下面简述了每一种联结。
内部联结内部联结（inner join）为预设的联结形式；它指定只有符合 ON 条件数据表中的数据列才能被包含在结果集中，而不符合的资料列都被排除在外。要指定一个联结，可以使用 JOIN 关键词。使用 ON 关键词是用来定义联结基本的搜寻条件。下面的查询联结了 stores 和 discounts 两个数据表，以显示哪些商店提供了折扣以及折扣的种类（预设为内部联结，表示只有符合 ON 搜寻条件的资料列会被传回）。
SELECT s.stor_id, d.discounttype
FROM stores s JOIN discounts d
ON s.stor_id = d.stor_id
GO
结果集如下：
stor_id discounttype
------- -------------------
8042 Customer Discount
您可以看到，只有一家商店提供折扣，而且只有一种折扣。在传回的唯一数据数据列里，它从 stores 数据表中得到的 stor_id 与从 discounts 数据表中得到的 sotr_id 相符合，所以此 stor_id 及它的 discounttype 被传回。
完全外部联结完全外部联结（full outer join）指定了不论是符合的数据列（满足 ON 搜寻条件的资料列）或不符合的资料列（不满足 ON 条件的资料列）都被包含在结果集中。对于不符合的数据列，NULL 将显示在不符合的数据行中。在本例中，NULL 即表示商店不提供任何折扣，这样它在 stores 数据表中有 sotr_id 值，但在 discounts 资料表中则没有值。NULL 也代表在 discounts 数据表中此种形式的折扣任何商店都不提供。下面的查询用了与前面内部联结相同的查询，但这次我们要指定完全外部联结：
SELECT s.stor_id, d.discounttype
FROM stores s FULL OUTER JOIN discounts d
ON s.stor_id = d.stor_id
GO
结果集如下：
stor_id discounttype
------- ------------------
NULL Initial Customer
NULL Volume Discount
6380 NULL
7066 NULL
7067 NULL
7131 NULL
7896 NULL
8042 Customer Discount
结果集显示只有一数据列完全符合。其它数据列则会在某一栏中具有 NULL 值。
左外部联结左外部联结（left outer join）传回符合的数据列及指定于 JOIN 关键词左边之数据表的所有数据列。使用与上述相同的查询，在此我们指定左外部联结，如下所示：
SELECT s.stor_id, d.discounttype
FROM stores s LEFT OUTER JOIN discounts d
ON s.stor_id = d.stor_id
GO
结果集如下：
stor_id discounttype
------- ----------------------------------------
6380 NULL
7066 NULL
7067 NULL
7131 NULL
7896 NULL
8042 Customer Discount
结果集中包含符合 ON 条件的一数据列和 stores 数据表中的其它数据列，这些数据列在 discounts 数据表中并没有与之符合的 stor_id（这些资料列的 discounttype 栏为 NULL）。
右外部联结右外部联结（right outer join）与左外部联结相反，它传回符合的数据列及指定于 JOIN 关键词右边的数据表中所有的数据列。下面使用同一个查询说明右外部联结：
SELECT s.stor_id, d.discounttype
FROM stores s RIGHT OUTER JOIN discounts d
ON s.stor_id = d.stor_id
GO
结果集如下：
stor_id discounttype
------- -------------------
NULL Initial Customer
NULL Volume Discount
8042 Customer Discount
结果集显示了符合 ON 条件的数据列和在 discounts 数据表中的其它数据列，这些数据列在 stores 数据表中没有与之符合的 stor_id（其 stor_id 栏为 NULL）。
交叉联结交叉联结（cross join）是没有指定 WHERE 子句时，两个数据表的交叉产物。如果存在 WHERE 子句，则交叉联结就与内部联结类似。当没有 WHERE 子句时，所有的栏和数据列将从这两个数据表中以下列方式传回：第一个数据表的每一数据列与第二个数据表的每一数据列相符，所以结果集的数目为第一个数据表的数据列数乘以第二个数据表的数据列数。
为了理解交叉联结，下面将举几个新的例子。先让我们看一个没有 WHERE 子句的交叉联结的情况，然后举三个包含 WHERE 子句的交叉联结的例子。下面的查询是一个简单的范例。执行这三个查询，并且注意每个查询输出的数据列数。
SELECT *
FROM stores
GO

SELECT *
FROM sales
GO

SELECT *
FROM stores CROSS JOIN sales
GO

说明
如果 FROM 子句中包含两个数据表，其作用相当于指定了一个 CROSS JOIN，例如：
SELECT *
FROM stores, sales
GO

要避免出现大量杂乱的信息（如果远超过了我们需要的部分），便需要加一个WHERE 子句来缩小查询的范围，如下所述：
SELECT *
FROM sales CROSS JOIN stores
WHERE sales.stor_id = stores.stor_id
GO
该陈述式只传回符合 WHERE 子句中搜寻条件的资料列，将结果缩减到只有 21资料列。WHERE 子句使交叉联结与内部联结的作用相同（即只有满足搜寻条件的数据列被传回）。该查询将传回 sales 数据表中的数据数据列与 stores 数据表中有相同 stor_id 的数据列，这些数据表将集合在一起。不符合的数据列不会被传回。
为了进一步缩减结果集，可以在星号（）前面加上数据表名称，指定将选择该数据表的所有数据列和数据行，如下面的例子所示。您也可以透过在数据表名称和数据行名称之间插入一点（.）来选定数据表的某一数据行。
SELECT sales., stores.city
FROM sales CROSS JOIN stores
WHERE sales.stor_id = stores.stor_id
GO
这个查询传回 sales 数据表中的所有数据行，并附加上 stores 数据表数据列中有同样 stor_id 的 city 资料行。实际上，结果集包含 sales 数据表中符合 stor_id 的数据列以及所附加的商店所在的城市。
以下是不含星号（*）的同一个查询，sales 数据表中仅有 stor_id 数据行被选取。
SELECT sales.stor_id, stores.city
FROM sales CROSS JOIN stores
WHERE sales.stor_id = stores.stor_id
GO
资料表别名

我们已经看过了几个使用数据表别名的例子。AS 关键词是选择性的（FROM tablename AS alias 的结果与 FROM tablename alias 是相同的）。让我们再看一下〈右外部联结〉部分的查询，这个查询用到了别名：
SELECT s.stor_id, d.discounttype
FROM stores s RIGHT OUTER JOIN discounts d
ON s.stor_id = d.stor_id
GO
这两个数据表中都含有 stor_id 数据行。要区分在查询中所引用的是哪一个数据表中的 stor_id 数据行，必须提供数据表名称或在数据表别名后插入一点（.）再加上数据行名称。在本例中，s 代表 stores 资料表，d代表 discounts 资料表。在指定某一栏时，s或d加在资料行名称前以表示此资料行属于哪一个资料表。包含 AS 关键词的同一个查询如下：
SELECT s.stor_id, d.discounttype
FROM stores AS s RIGHT OUTER JOIN discounts AS d
ON s.stor_id = d.stor_id
GO
INTO 子句

现在进入 SELECT 陈述式的第一个真正的选择性子句：INTO 子句。利用 SELECT

  1<select list=""> INTO<new_tablename> 语法可以从一个或多个数据表中检索数据并将结果数据列输出到一个新的数据表。当执行 SELECT...INTO 陈述式时，新数据表会自动产生并按照选取清单中的数据行定义。新数据表中的每一个数据行与来源数据行有相同的数据型别，并且和选取清单中的数据行名称相同。使用者必须在目标数据库中有 CREAT TABLE 权限，才能执行 SELECT...INTO。在 第30章 中将介绍如何设定权限。   
  2您可以使用 SELECT...INTO 将结果存入暂存性或永久性的资料表中。对于一个本域暂存数据表（只有目前的联机或使用者能看见），必须在数据表明前加上 ＃ 号。对于一个全域暂存数据表（对任何使用者都能看见），必须在数据表前加上两个 ＃ 号（＃＃）。当所有正在使用同一个暂存数据表的使用者都与 SQL 服务器断线时，该暂存数据表将会自动被删除。要选择存入永久性数据表，您无需为数据表名称加前置字，但是目标数据库的 Select Into/Bulk Copy 选项必须选取。要为 pubs 数据库选取这个选项，可以执行以下 SQL 查询：   
  3sp_dboption pubs, "select into/bulkcopy", true   
  4GO   
  5您也可以用 SQL Server Enterprise Manager 来选择这个选项，如下所示：   
  61\. 在 Enterprise Manager 窗口中点选 pubs 数据库并且按右钮，出现快捷菜单后选择 内容 ，可显示 pubs 数据库的属性，如图14-1（这个画面曾在 第九章 出现过，当时我们建立了一个数据库并指定其档案成长选项）。   
  72\. 按一下 选项 卷标页，如图14-2，并在 模型 的下拉式选单中选择 大量登入 。其它选项保持原先的设定。按一下 确定 。   
  8下面的查询使用 SELECT...INTO 建立一个名为 emp_info 的永久数据表，其中包含所有员工的姓名，以及它们的职务说明（在 pubs 数据库内）：   
  9SELECT employee.fname, employee.lname, jobs.job_desc   
 10INTO emp_info   
 11FROM employee, jobs   
 12WHERE employee.job_id = jobs.job_id   
 13GO 
 14
 15  
 16  
 17图14-1 数据库属性窗口的「一般」卷标页 
 18
 19  
 20  
 21图14-2 数据库属性窗口的「选项」卷标页   
 22emp_info数据表包含了三个数据行名称－fname、lname以及 job_desc，其数据型别与原数据表（employee与 jobs数据表）的定义的数据型别相同。若要使新数据表成为一个本域暂存数据表，必须在数据表名称前加上 ＃ 符号，如 #emp_info；若要成为全域暂存数据表，则使用 ＃＃ 符号，如 ##emp_info。   
 23WHERE 子句及搜寻条件   
 24  
 25WHERE 子句依据搜寻条件来限定查询所传回的数据列。本节中我们将说月许多可用在搜寻条件的操作。   
 26________________________________________   
 27说明   
 28搜寻条件不只可用在 SELECT 陈述式中的 WHERE 子句，也可用于 UPDATE 以及 DELETE 陈述式。（UPDATE 与 DELETE 陈述式将在 20章 讨论）   
 29________________________________________   
 30首先让我们先复习一些术语。搜寻条件可以透过结合逻辑运算子 AND、OR 和NOT 而包含无限多个述词。 述词 （predicate）是指传回 TRUE、FALSE 和 UNKNOWN 值的表达式。一个 表达式 （expression）可以是数据行名称、常数、纯量函数（传回一个值的函数）、变量、纯量子查询（传回一个数据行的子查询）或是透过运算子结合这些元素所形成的总体。本节中，述词或表达式均以 表达式 这个术语来表达。   
 31比较运算子   
 32  
 33表达式中可使用的等式与不等式运算子数据列于表14-1。   
 34表14-1 比较运算子   
 35运算子 检验的条件   
 36= 检验两个表达式是否相等   
 37&lt;&gt; 检验两个表达式是否不等   
 38!= 检验两个表达式是否不等（同&lt;&gt;）   
 39&gt; 检验前一个表达式是否大于后一个表达式   
 40&gt;= 检验前一个表达式是否大于或等于后一个表达式   
 41!&gt; 检验前一个表达式是否不大于后一个表达式   
 42&lt; 检验前一个表达式是否小于后一个表达式   
 43&lt;= 检验前一个表达式是否小于或等于后一个表达式   
 44!&lt; 检验前一个表达式是否不小于后一个表达式   
 45一个简单的 WHERE 子句可能利用等于运算子（＝）来比较两个表达式。例如，下面的 SELECT 陈述式可用来检验每数据列 lname 数据行中数据型别为 char 的值，如果为 Latimer，则传回 TRUE（传回 TRUE 的资料列将被包含在结果集）。   
 46SELECT *   
 47FROM employee   
 48WHERE lname = "Latimer"   
 49GO   
 50本例的查询将传回一数据列。Latimer必须加上引号，因为它是字符串。   
 51________________________________________   
 52说明   
 53SQL Server 预设接受单引号（'）与双引号（"），例如 'Latimer'或 "Latimer"。本书的范例只使用双引号来避免混淆。您可以使用 SET QUOTED_IDENTIFIER 选项来允许保留关键词当作对象名称，并只使用单引号。将该选项设定为 ON（OFF为默认值）。   
 54________________________________________   
 55下面的查询使用不等于运算子（&lt;&gt;），数据行为 integer 数据型别的 job_id。   
 56SELECT job_desc   
 57FROM jobs   
 58WHERE job_id &lt;&gt; 1   
 59GO   
 60这个查询将传回在jobs 数据表中 job_id 不等于1的数据列中的说明文字。本例中有13资料列被传回。如果某数据列中有 NULL 值，它不等于1也不等于其它值，故此数据列也将被传回。   
 61逻辑运算子   
 62  
 63逻辑运算子 AND 和 OR 测试两个表达式，并根据结果传回一个 TRUE、FALSE 或 UNKNOWN 的布尔值。NOT 逻辑运算子会传回与表达式相反的结果。图14-3中数据列出了 AND、OR 与 NOT 运算子传回的每种可能值。AND 与 OR 表格中，左边的数据行是第一个表达式的值，最上面的数据列是第二个表达式的值，两者交叉的表格中即是结果的布尔值。NOT 表格则更直接一些。UNKNOWN 是表达式中有 NULL 值所传回的结果。 
 64
 65  
 66  
 67图14-3 AND、OR 与 NOT 的运算结果   
 68下面的查询是在 WHERE 子句中对两个表达式运用 AND 逻辑运算子例子：   
 69SELECT job_desc, min_lvl,max_lvl   
 70FROM jobs   
 71WHERE min_lvl &gt;= 100 AND   
 72max_lvl &lt;= 225   
 73GO   
 74如图14-3，要让 AND 运算传回 TRUE，必须两个条件均为 TRUE。在这个查询中，有 4 数据列被传回。   
 75下一个查询中，一个 OR 运算将检测出版商是否位于华盛顿 D.C.或位于麻州。若两个检验中只要有一个值为 TRUE，则此数据列将被传回。   
 76SELECT p.pub_name, p.state, t.title   
 77FROM publishers p, titles t   
 78WHERE p.state = "DC" OR   
 79p.state = "MA" AND   
 80t.pub_id = p.pub_id   
 81GO   
 82此查询传回 23 数据列。   
 83NOT 运算单纯的传回其后布尔表达式的相反值。例如，要查询所有的作者版税不少于 20% 的书籍名称，您可以下列方法来运用 NOT 运算子：   
 84SELECT t.title, r.royalty   
 85FROM titles t, roysched r   
 86WHERE t.title_id = r.title_id AND NOT   
 87r.royalty &lt; 20   
 88GO   
 89其它关键词   
 90  
 91除了以上这些运算子，还有多种 T-SQL 的关键词可用于搜寻条件。本节将解释最常用的一些关键词，也会给予范例说明。   
 92LIKE LIKE 关键词在搜寻条件中指定模式比对。 模式比对 （Pattern matching）是在一个比对表达式与搜寻条件中指定的模式之间进行的比对检测，语法如下：   
 93<match_expression> LIKE <pattern>   
 94若比对表达式符合模式，则传回 TRUE 的布尔值。反之则传回 FALSE。对比运算是必须是字符串数据型别。如果不是的话，SQL Server 会尽可能将其自动转换为字符串数据型别。   
 95模式是真正的字符串表达式。 字符串表达式 定义为一个由字符或万用字符组成的字符串。 万用字符 是在字符串表达式中代表特殊意义的字符。表14-2数据列出了模式中可用的万用字符：   
 96表14-2 T-SQL的万用字符   
 97万用字符 说明   
 98% 百分比符号；与零个或若干个字符比对   
 99_ 底线；与任何一个单一字符比对   
100[ ] 范围万用字符，与指定范围或集合中的单一字符比对，例如 [m-p] 或 [mnop] 表示m、n、o、p中的任一字符。   
101[^] 不在范围内万用字符；与不包括在该范围或集合中的任一个字符比对，例如 [^m-p] 或 [^mnop] 表示除了m、n、o、p以外的其它任一字符。   
102要进一步了解 LIKE 关键词与万用字符，请看面几个例子。若要在 authors资料表中寻找以 S 为开头的作者姓氏，您可以使用以下含有 % 万用字符的查询：   
103SELECT au_lname   
104FROM authors   
105WHERE au_lname LIKE "S%"   
106GO   
107结果将为：   
108au_lname   
109\-----   
110Smith   
111Straight   
112Stringer   
113这个查询中，S% 表示传回以 S 开头，后接任何字符的姓氏。   
114________________________________________   
115说明   
116本范例假设您使用的是预设的按字典顺序排序，且不区分大小写。若指定其它的排序方法，您的结果可能不同，但使用 LIKE 关键词的原理是相同的。   
117________________________________________   
118要搜寻 ID 以 724 开头的作者信息，若已知每个 ID 的格式就像社会安全号码（3个数字－2个数字－4个数字），您可使用底线（_）万用字符，如下所示：   
119SELECT *   
120FROM authors   
121WHERE au_id LIKE "724-_ _-_ _ _ _"   
122GO   
123结果集将包含两资料列，au_id 值为 724-08-9931 与 724-80-9391。   
124现在让我们看一个使用 [ ] 万用字符的例子。要搜寻开头为 A 到 M 之间的任一字母的作者姓氏，您可以组合 [ ] 及 % 万用字符：   
125SELECT au_lname   
126FROM authors   
127WHERE au_lname LIKE "[A-M]%"   
128GO   
129结果集将包含有以 A-M 开头的名字的 14 数据列。(如果使用区分大小写的排序，则得到 13 数据列。 )   
130如果用 [^] 万用字符取代 [ ] 万用字符执行类似的查询，将得到除了 A 到 M以外字母为开头的姓氏，如下所示：   
131SELECT au_lname   
132FROM authors   
133WHERE au_lname LIKE "[^A-M]%"   
134GO   
135此查询传回 9 数据列。   
136如果使用区分大小写的排序，但要得到符合条件但不区分大小写的所有名称，可以使用一个检验第一个字母为小写或大写的查询，如下所示：   
137SELECT au_lname   
138FROM authors   
139WHERE au_lname LIKE "[A-M]%" OR   
140au_lname LIKE "[a-m]%"   
141GO   
142结果集包含名称 del Castillo，如果仅用区分大小写的查询方式，则得不到这个结果。   
143LIKE 关键词也可放在 NOT 运算子之后。NOT LIKE 传回不符合搜寻条件的资料列。例如，要找出不以 The 开头的书名，您可以使用 NOT LIKE，如下列查询所示：   
144SELECT title   
145FROM titles   
146WHERE title NOT LIKE "The %"   
147GO   
148此查询将传回 15 数据列。   
149您可以很有创意的使用 LIKE 关键词。不过要小心的测试您的查询以便确定传回的数据是您真正需要的部分。如果遗漏了原本应该包含的 NOT 或 ^ 字符，得到的结果集会刚好与您期望的值相反。遗漏了原本必须的 % 字符也会得到错误的结果，您还必须注意前后空格的使用。   
150ESCAPE ESCAPE 关键词使您能为万用字符本身进行模式比对，例如 ^、%、[ 以及 _。在 ESCAPE 关键词之后接着您指定要避开的字符，代表字符串表达式中的这些字符必须按字面意义逐字比对。例如，在 titles数据表中要找出 title 数据行中有下底线的所有资料列，您可以使用下列查询：   
151SELECT title   
152FROM titles   
153WHERE title LIKE "%e_%" ESCAPE "e"   
154GO   
155此查询将没有数据列被传回，因为数据库中并没有任何一本书名含有下底线。   
156BETWEEN BETWEEN 关键词通常与 AND 一起使用来为搜寻条件指定包括的范围。语法如下：   
157<test_expression> BETWEEN <begin_ expression=""> AND <end_ expression="">   
158当 test_ expression 表达式大于或等于 begin_ expression 表达式并且小于或等于 end_ expression表达式 时，搜寻条件的结果为 TRUE 布尔值；否则结果为 FALSE。   
159下面的查询使用 BETWEEN 关键词来找出价格在 5 到 25 美元之间的书名：   
160SELECT price, title   
161FROM titles   
162WHERE price BETWEEN 5.00 AND 25.00   
163GO   
164此查询将传回 14 数据列。   
165您也可以将 NOT 与 BETWEEN 一起使用，找出不在指定范围内的数据列。例如，寻找价格不在 20 到 30 美元的书名（亦即价格小于 20 美元或大于 30 美元），您可以使用下列查询：   
166SELECT price, title   
167FROM titles   
168WHERE price NOT BETWEEN 20.00 AND 30.00   
169GO   
170当您使用 BETWEEN 关键词，test_expression 表达式必须与begin_expression 表达式及 end_expression 表达式有相同的数据型别。   
171在刚刚的范例中，price数据行的数据型别为 money，因此 begin_ expression 表达式与 end_ expression 表达式必须是一个可以被与之比较或被 隐含转换 （implicit conversion）为 money 数据型别的数字。您不可在 test_ expression 表达式使用价格却在 begin_ expression 表达式与 end_ expression 表达式使用字符串或 char 数据型别；否则 SQL Server 将传回一个错误讯息。   
172________________________________________   
173说明   
174如果隐含转换是可行的话，SQL Server 会在需要时自动的转换数据型别。隐含转换能自动的将数据型别转换为另一个能兼容的数据型别。转换后便可进行比较。例如，把一个 smallint 数据型别的数据行与一个 int 数据型别的数据行作比较时，SQL Server 会在比较前将 smallint 隐含转换为 int 数据型别。如果没有提供隐含转换，您可以使用 CAST 或 CONVERT 函数来明确的指定转换数据行。关于 SQL Server 中哪些数据型别可隐含转换、哪些数据型别需外显转换，可于 SQL Server《在线丛书》索引「CAST」，并在 找到的主题 对话框中选择 CAST与CONVERT 。   
175________________________________________   
176最后一个与 BETWEEN 关键词相关的例子，是在搜寻条件中使用字符串。要找出从 Bennet 到 McBadden 之间按字母顺序排数据列的作者姓氏，您可以使用下列查询：   
177SELECT au_lname   
178FROM authors   
179WHERE au_lname BETWEEN "Bennet" AND "McBadden"   
180GO   
181BETWEEN 的范围会包含起始值和结束值，因此存在于数据表中的 Bennet 与 McBadden 也会包括在查询结果之内。   
182IS NULL IS NULL 关键词用来在搜寻条件中选择在指定数据行中有 NULL 值的数据列。例如，在 titles数据表寻找 notes数据行没有数据（亦即 notes值为 NULL）的书名，您可以使用下列查询：   
183SELECT title, notes   
184FROM titles   
185WHERE notes IS NULL   
186GO   
187结果将为：   
188title notes   
189\------------------------------------ ------   
190The Psychology of Computer Cooking NULL   
191如您所见，notes 资料行的 NULL 值在结果集中显示为 NULL。NULL 并不是资料行中的实际值－它仅指出资料行中存在 NULL 值。   
192要找到 notes数据行有数据的书名 (notes 值不为 null 的书名 )，使用 IS NOT NULL，如下所示：   
193SELECT title, notes   
194FROM titles   
195WHERE notes IS NOT NULL   
196GO   
197结果集显示有 17 数据列其 notes数据行内至少有一个字符，所以其 notes数据行中没有 null 值。   
198IN IN 关键词在搜寻条件中用来判断给定的测试表达式是否符合子查询或值清单中的任一值。如果找到一个相符的情况， TRUE 将被传回。   
199NOT IN 传回 IN 的相反结果，因此，如果测试表达式在子查询数值清单中没有找到，TRUE 将被传回。语法如下：   
200<test_expression> IN (<subquery>)   
201或   
202<test_expression> IN (<list of="" values="">)   
203子查询 （subquery）是一个结果集中只传回一个资料行的 SELECT 陈述式。子查询必须被包含在小括号内。 值清单 也是如此，这些清单被包含在小括号内，并以逗号分开。从子查询或值清单所得的数据行，其数据型别必须与 test_expression 表达式相同。必要时 SQL Server 会执行隐含转换。   
204您可以使用 IN 关键词和值清单找到3个特定工作说明的 job_id 数字，查询如下：   
205SELECT job_id   
206FROM jobs   
207WHERE job_desc IN ("Operations Manager",   
208"Marketing Manager",   
209"Designer" )   
210GO   
211此查询的值清单为：("Operations Manager"、"Marketing Manager"、"Designer" )，为传回 job ID 的条件，必须是数据列中 job_desc 数据行中的值为前述三个之一。比起使用两个 OR 运算子来完成查询，IN 关键词可以让您的查询易读易懂，如下所示：   
212SELECT job_id   
213FROM jobs   
214WHERE job_desc = "Operations Manager" OR   
215job_desc = "Marketing Manager" OR   
216job_desc = "Designer"   
217GO   
218下面的查询在一个陈述式中使用 IN 关键词两次－一次针对子查询，另一次针对子查询中的值清单：   
219SELECT fname, lname --Outer query   
220FROM employee   
221WHERE job_id IN ( SELECT job_id --Inner query, or subquery   
222FROM jobs   
223WHERE job_desc IN ("Operations Manager",   
224"Marketing Manager",   
225"Designer"))   
226GO   
227子查询的结果集将先被找到－本例中，它是一个 job_id 值的集合。此子查询产生的 job_id 值的集合并不会传回到屏幕上，外层的查询将把它当作表达式一般运用在自己的 IN 搜寻条件。最后的结果集将包含所有职业头衔为 Operations Manager、Marketing Manager 或 Designer 的员工姓名。结果集如下：   
228fname lname   
229\-------------------- ----------------------   
230Pedro Afonso   
231Lesley Brown   
232Palle Ibsen   
233Karin Josephs   
234Maria Larsson   
235Elizabeth Lincoln   
236Patricia McKenna   
237Roland Mendel   
238Helvetius Nagy   
239Miguel Paolino   
240Daniel Tonini 
241
242(影响11个数据列)   
243IN 也可与 NOT 运算子一起使用。例如，要传回所有不位在加州、德州或伊利诺伊州的出版商姓名，请执行以下查询：   
244SELECT pub_name   
245FROM publishers   
246WHERE state NOT IN ("CA",   
247"TX",   
248"IL")   
249GO   
250查询将传回 state 数据行值不为值清单所数据列三州之一的五个资料列。如果您的数据库选项 ANSI nulls 设定为 ON ，结果将只传回三数据列。这是因为原始结果集中的五数据列里有两数据列的 state值为 NULL，而当 ANSI nulls 设定为 ON 时 NULL 是不会被选取。   
251要判断 pubs 数据库中您的 ANSI nulls 设定为何，可以执行以下的系统预存程序：   
252sp_dboption "pubs", "ANSI nulls"   
253GO   
254如果 ANSI nulls 设定为 OFF ，可使用以下陈述式将其改为 ON ：   
255sp_dboption "pubs", "ANSI nulls", TRUE   
256GO   
257要将 ON 改为 OFF ，以 FALSE 代替 TRUE 。   
258EXISTS EXISTS 关键词用来检测其后的子查询中的数据数据列的存在性。语法如下：   
259EXISTS (<subquery>)   
260如果有任何数据列满足此一子查询，TRUE 将被传回。   
261要选择曾经出版过书籍的作者姓名，您可使用以下查询：   
262SELECT au_fname, au_lname   
263FROM authors   
264WHERE EXISTS (SELECT au_id   
265FROM titleauthor   
266WHERE titleauthor.au_id = authors.au_id )   
267GO   
268存在于 authors资料表，但没有出版任何书（数据列于 titleauthor 数据表）的作者姓名将不会被选择。如果在子查询中没有任何数据列被选择，交给外层查询使用的结果集为空。（零数据列被选择）   
269CONTAINS 与 FREETEXT CONTAINS 与 FREETEXT 关键词用来在基于字符的数据型别的数据行中进行全文检索。它们比 LIKE 关键词提供更大的弹性。例如，CONTAINS 关键词让您可以搜寻与指定字符或词组不完全相符但类似的文字（是一种 模糊 （fuzzy）比对）。FREETEXT 允许您搜寻与指定字符串部份或全部相符（或模糊比对）的若干文字。这些文字不需要完全符合指定的搜寻字符串，也不需要与字符串中的文字有相同的顺序。这两个关键词有多种用途并对全文检索提供了不少选项，不过这些主题已超过本章的讨论范围。   
270________________________________________   
271相关信息   
272关于 CONTAINS 与 FREETEXT 关键词的相关信息，可于 SQL Server《在线丛书》索引「CONTAINS」与「FREETEXT」。   
273________________________________________   
274GROUP BY 子句   
275  
276GROUP BY 子句用于 WHERE 子句之后，指示结果集中的数据列将按照指定的群组数据行来群组化。如果 SELECT 子句使用到汇总函数，则每个群组都会计算一个总计值并显示于输出结果。（ 汇总函数 （aggregate function）执行计算并传回值；这些函数将在本章稍后 〈汇总函数〉 一节里有详细说明。）   
277________________________________________   
278说明   
279选取清单中的每个数据行－除了汇总函数使用的数据行－必须在 GROUP BY 子句中指定为群组数据行；否则 SQL Server 将传回一个错误讯息。如果不遵循此一规则（指定的 GROUP BY 数据行必须群组选取清单中的每个数据行），输出结果将无法以合理的方式显示。   
280________________________________________   
281当 SELECT 子句含有汇总函数时，GROUP BY 最为有用。让我们看一下用GROUP BY 子句寻找每种书籍销售总量的 SELECT 陈述式：   
282SELECT title_id, SUM (qty )   
283FROM sales   
284GROUP BY title_id   
285GO   
286结果集如下：   
287title_id   
288\-------- ------------   
289BU1032 15   
290BU1111 25   
291BU2075 35   
292BU7832 15   
293MC2222 10   
294MC3021 40   
295PC1035 30   
296PC8888 50   
297PS1372 20   
298PS2091 108   
299PS2106 25   
300PS3333 15   
301PS7777 25   
302TC3218 40   
303TC4203 20   
304TC7777 20 
305
306(影响16个数据列)   
307此查询没有 WHERE 子句－您并不需要。结果集显示了一个 title_id 数据行以及一个没有标题的摘要数据行。对每个不同的 title_id，各个书籍的总销售量显示在摘要数据行。例如，title_id 为 BU1032的书籍在 sales数据表出现了两次－一次在 qty数据行显示销售了 5 本， 第二次在另一个订单中销售了 10 本。SUM 汇总函数总和这两次的销售得出 15 本的总量，并显示于摘要数据行。要为摘要数据行加上一个标题，可使用 AS 关键词：   
308SELECT title_id, SUM(qty) AS "Total Sales"   
309FROM sales   
310GROUP BY title_id   
311GO   
312现在结果集中的摘要资料行标题为 Total Sales：   
313title_id Total Sales   
314\-------- ------------   
315BU1032 15   
316BU1111 25   
317BU2075 35   
318BU7832 15   
319MC2222 10   
320MC3021 40   
321PC1035 30   
322PC8888 50   
323PS1372 20   
324PS2091 108   
325PS2106 25   
326PS3333 15   
327PS7777 25   
328TC3218 40   
329TC4203 20   
330TC7777 20 
331
332(影响16个数据列)   
333您可以在 GROUP BY 子句中包含一个以上的数据行让群组巢状化。 巢状群组 （nesting groups）意味着结果集将以每个群组数据行被指定的顺序来进行群组化的工作。例如，要找出已经依类型及出版商群组化的书籍的平均价格，可执行下列查询：   
334SELECT type, pub_id, AVG(price ) AS "Average Price"   
335FROM titles   
336GROUP BY type, pub_id   
337GO   
338结果集如下：   
339type pub_id Average Price   
340\------------ ------ ------------------------   
341business 0736 2.99   
342psychology 0736 11.48   
343UNDECIDED 0877 NULL   
344mod_cook 0877 11.49   
345psychology 0877 21.59   
346trad_cook 0877 15.96   
347business 1389 17.31   
348popular_comp 1389 21.48 
349
350(影响8个数据列)   
351注意心理学和商业类型的书籍出现了不只一次，因为它们按照不同的出版商 ID分组。UNDECIDED 类型的 NULL 值表示数据表中没有此种类型的价格，因此也没有计算平均值。   
352GROUP BY 子句提供了一个选择性的关键词 ALL，用以指定结果集中将包含所有群组，即使它们可能不符合搜寻条件。没有数据列符合搜寻条件的群组将会在摘要数据行出现 NULL 以便于辨别。例如，要显示版税为 12% 的书籍的平均价格（以及其它条件不符的书籍，其摘要数据行为 NULL），并将这些书籍先以类型再以出版商 ID 来群组化，执行下列查询：   
353SELECT type, pub_id, AVG(price) AS "Average Price"   
354FROM titles   
355WHERE royalty = 12   
356GROUP BY ALL type, pub_id   
357GO   
358结果集如下：   
359type pub_id Average Price   
360\------------ ------ ------------------------   
361business 0736 NULL   
362psychology 0736 10.95   
363UNDECIDED 0877 NULL   
364mod_cook 0877 19.99   
365psychology 0877 NULL   
366trad_cook 0877 NULL   
367business 1389 NULL   
368popular_comp 1389 NULL 
369
370(影响8个数据列)   
371所有类型的书籍都在输出中显示，版税不为 12% 的书籍则出现了 NULL 值。   
372如果我们不使用关键词 ALL，结果集将只包含版税为 12% 的书籍类型：   
373type pub_id Average Price   
374\------------ ------ ------------------------   
375psychology 0736 10.95   
376mod_cook 0877 19.99 
377
378(影响2个数据列)   
379GROUP BY 子句常与 HAVING 子句一起使用，我们将在下面讨论。   
380HAVING 子句   
381  
382HAVING 用来为一个群组或一个汇总函数指定搜寻条件。HAVING 通常使用在GROUP BY 子句之后，主要用途在于对一个已经被群组化的结果指定要检测的搜寻条件。如果搜寻条件是使用在群组化之前，则 WHERE 子句要比 HAVING 更有效率。HAVING 技巧减少了必须群组化的数据列数。如果没有 GROUP BY 子句，HAVING 只能与选取清单的汇总函数一起使用。在这种情况下，HAVING 子句与 WHERE 子句的作用相同。如果 HAVING 不是用在这些情况下，SQL Server 将传回错误信息。   
383HAVING 子句的语法如下：   
384HAVING <search_condition>   
385这里的 search_condition 与在本章前面 〈WHERE子句和搜寻条件〉 部分所讲的搜寻条件具有相同的意义。HAVING 子句和 WHERE 子句不同在于 HAVING 子句可以在搜寻条件中包含汇总函数，而 WHERE 子句不行。   
386________________________________________   
387说明   
388您可以在 SELECT 和 HAVING 子句中使用汇总函数，但在 WHERE 子句不能。   
389________________________________________   
390下面的查询用 HAVING 子句选择平均价格超过 15 美元的出版商的书籍种类：   
391SELECT type, pub_id, AVG (price ) AS "Average Price"   
392FROM titles   
393GROUP BY type,pub_id   
394HAVING AVG(price ) &gt; 15.00   
395GO   
396结果集如下：   
397type pub_id Average Price   
398\------------ ------ ------------------------   
399psychology 0877 21.59   
400trad_cook 0877 15.96   
401business 1389 17.31   
402popular_comp 1389 21.48 
403
404(影响4个数据列)   
405您也可以将逻辑运算子与 HAVING 子句一起使用。加上 AND 运算子的查询如下：   
406SELECT type, pub_id, AVG (price ) AS "Average Price"   
407FROM titles   
408GROUP BY type, pub_id   
409HAVING AVG(price) &gt;= 15.00 AND AVG(price ) &lt;= 20.00   
410GO   
411结果集如下：   
412type pub_id Average Price   
413\------------ ------ ------------------------   
414trad_cook 0877 15.96   
415business 1389 17.31 
416
417(影响2个数据列)   
418您也可以用 BETWEEN 子句代替 AND，得到的结果是相同的：   
419SELECT type, pub_id, AVG(price) AS "Average Price"   
420FROM titles   
421GROUP BY type, pub_id   
422HAVING AVG (price ) BETWEEN 15.00 AND 20.00   
423GO   
424若您在使用 HAVING 时不用 GROUP BY 子句，则在选取清单与 HAVING 子句里必须包含一个汇总函数。例如要选取 mod_cook 类型的总金额大于 20 美元的各种书籍，可执行以下查询：   
425SELECT SUM(price)   
426FROM titles   
427WHERE type = "mod_cook"   
428HAVING SUM(price) &gt; 20   
429GO   
430如果将表达式 SUM(price) &gt; 20 置于 WHERE 子句中，SQL Server 将会传回错误信息（在 WHERE 子句中不允许使用汇总函数）。   
431________________________________________   
432说明   
433记住，使用 HAVING 子句是为检测 GROUP BY 子句所得到的结果集而加上一个搜寻条件，或用来检测一个汇总函数。其它的情况应该用 WHERE 子句指定搜寻条件，那样将更有效率而且避免 SQL Server 发生错误。   
434________________________________________   
435ORDER BY 子句   
436  
437ORDER BY 子句用来指定结果集里数据列的排序方式。您可以用 ASC 或DESC 选项来指定是升幂（从小到大）或是降序（从大到小）。当没有选定时，预设为升幂。您可以在 ORDER BY 子句中指定很多数据行，结果将按第一个数据行来排序，如果第一个数据行有重复值，就按第二个数据行排序，依此类推。在后面的内容中可以看到，如果让 ORDER BY 与 GROUP BY 共同使用，这种排序将更有意义。首先让我们看一个在 ORDER BY 子句使用一个数据行，按作者姓氏来升幂排序的例子：   
438SELECT au_lname, au_fname   
439FROM authors   
440ORDER BY au_lname ASC   
441GO   
442结果集将按姓的字母顺序排列。记住若在安装 SQL Server 时设定 排序顺序区分大小写 ，将会对例如 del Castillo 这样的姓氏产生影响。   
443如果要排序的结果数据行不只一个，只要加上这些数据行的名称，并在 ORDER BY 子句中用逗号区隔即可。下面的查询选择 job_id 和员工的姓名然后将它们先按姓氏再按名字排序：   
444SELECT job_id, lname, fname   
445FROM employee   
446ORDER BY job_ifd, lname, fname   
447GO   
448结果集如下：   
449job_id lname fname   
450\------ ------------------------------ -------------   
4512 Cramer Philip   
4523 Devon Ann   
4534 Chang Francisco   
4545 Henriot Paul   
4555 Hernadez Carlos   
4565 Labrune Janine   
4575 Lebihan Laurence   
4585 Muller Rita   
4595 Ottlieb Sven   
4605 Pontes Maria   
4616 Ashworth Victoria   
4626 Karttunen Matti   
4636 Roel Diego   
4646 Roulet Annette   
4657 Brown Lesley   
4667 Ibsen Palle   
4677 Larsson Maria   
4687 Nagy Helvetius   
469... ...   
47013 Accorti &amp;nb</search_condition></subquery></list></test_expression></subquery></test_expression></end_></begin_></test_expression></pattern></match_expression></new_tablename></select>