MS Jet SQL for Access 2000中级篇 (III)

中级数据操纵语言
《基础Microsoft Jet SQL for access 2000》一文告诉我们如何使用SQL来检索和管理存储在数据库中的信息。本文的后面部分将讨论中级数据操纵语言（DML）语句，这将使得用户可以更好的控制信息检索和处理的方式。

谓词
谓词指限定一个SELECT 语句的子句，同WHERE 子句类似，但是谓词是在书写列的列表之前定义的。谓词还可以进一步的限制用户所提取的记录集，在某些情况下，过滤出任何可能存在的重复值。

ALL关键字
在SQL语句中，如果没有定义任何谓词的话，将使用缺省的ALL关键字。它意味着所有的符合SQL语句所设定的条件的记录都将被提取出来。回到我们的发票数据库中，从顾客表中提取所域的记录如下：

SELECT *
FROM tblCustomers


注意尽管这里ALL关键词并没有定义，但它是缺省值。我们也可以如下书写该语句：

SELECT ALL *
FROM tblCustomers


DISTINCT关键字

DISTINCT关键字用来控制结果集中重复的值如何进行处理，那些对于指定的列来说用户相同值的行将被过滤掉。如果多于指定的列大于一，则所有指定的列的结合将作为过滤条件。例如，如果用户查询Customers表中姓氏不同的记录，则返回的值都将是唯一的，任何重复姓氏的名字都将以结果集中的一个记录作为其结果。

SELECT DISTINCT [Last Name]
FROM tblCustomers


尤其要注意的是，使用DISTINCT关键字的查询所返回的结果集不能更新，即是只读的。

DISTINCTROW 关键字
DISTINCTROW 关键字和DISTINCT关键字类似，但前者是基于整行而非个别的域的。他只有在处理多个表时，并且只有在用户从某几个但非全部的表中选择数据域时才是有用的。如果用户的查询是基于一个表的，或者要从所有的表中选择数据域，则DISTINCTROW 关键字本质上和ALL关键字相同。

例如，在我们的发票数据库中，每个顾客都可能没有、有一个或多个发票记录。假设我们希望找出有多少拥有多于一张发票的顾客，这时可以使用DISTINCTROW关键字来进行选择。

SELECT DISTINCTROW [Last Name], [First Name]
FROM tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID = tblInvoices.CustomerID


如果我们不使用DISTINCTROW 关键字，得到的将是每个顾客的所有发票记录的行。（这里的 INNER JOIN 语句将在后面的部分讲解）。

TOP关键字
TOP关键字用来返回通过ORDER BY子句所指定的数据行中顶部或底部的某些行。ORDER BY 子句用来指定这些数据列是用升序还是降序排列。如果存在相等值，则TOP关键字将返回所有值相等的行。比如我们希望确定我们的发票数据库中最高额的三条发票记录，可以如下书写SQL语句：

SELECT TOP 3 InvoiceDate, Amount
FROM tblInvoices
ORDER BY Amount DESC


我们也可以将PERCENT关键字和TOP关键字同时使用，来返回由ORDER BY子句所指定的数据行中顶部或底部的一定比例的行，如下所示：

SELECT TOP 25 PERCENT InvoiceDate, Amount
FROM tblInvoices
ORDER BY Amount DESC


注意如果用户没有定义ORDER BY 子句，则TOP关键字将毫无意义，返回的是随机采样的一些行。

要了解有关谓词的更多信息，请在Office 助手中或在Microsoft access 帮助的回答向导的标签页中输入ALL、DISTINCT等谓词，然后单击查找。

SQL 表达式
一个SQL表达式就是作为SQL语句的一部分的一个字符串，并可以得到一个值。你可以任意组合运算符、常数、文字值、函数、域名、控制和属性来建立你的SQL表达式。而" Microsoft Jet SQL for access 2000高级版"一文就向你描述了如何在WHERE子句中使用表达式来限制SQL语句；而且在本文随后的部分，我们将学习各种能够用于表达式的SQL操作符。

IN操作
IN操作是用来判断一个表达式的值是否属于一个指定列表中的值。如果这个表达式等于列表中的一个值，IN操作的返回值为True。而当没有找到，IN操作返回值为False。让我们假设我们想找到所有住在华盛顿州或乔治亚州的销售部成员。我们可以写一个带着长长的WHERE 子句，并使用AND逻辑操作符的SQL语句，而使用IN操作符会缩短我们写的语句。

SELECT *
FROM tblShipping
WHERE State IN ('WA','GA')


通过使用NOT逻辑操作符，我们可以检索出IN操作的反操作结果，这个语句会返回所有不住在华盛顿州的销售部成员。

SELECT *
FROM tblShipping
WHERE State NOT IN ('WA')


BETWEEN操作

BETWEEN操作用于判断一个表达式的值是否介于一个特定的范围之间。如果这个表达式的值介于这个特定范围之间，包括范围开始和结束的值，这个BETWEEN操作返回True。如果这个表达式得值不属于这个范围，则BETWEEN操作返回值为False。假设我们想找到所以金额介于50美圆到100美圆之间的所有发票。我们最好在WHERE 子句使用BETWEEN 操作以及关键字AND设定范围。

SELECT *
FROM tblInvoices
WHERE Amount BETWEEN 50 and 100


通过使用NOT逻辑操作符，我们可以检索出BETWEEN操作的反操作结果，找到不在范围中的所有发票数量。

SELECT *
FROM tblInvoices
WHERE Amount NOT BETWEEN 50 and 100


注意这个范围可以设定为相反的顺序并依旧得到相同的结果 (BETWEEN 100 和 50)，但许多的适用于ODBC的数据库要求这个范围遵从从头到尾的顺序。如果你设计你的应用程序可以兼容或升级为适用于ODBC的数据库，你就应该总是按照从头到尾的方法使用。

LIKE操作
LIKE 操作operator is used to determine if the value of an expression compares to that of a pattern. 一个样式就是就是一个完全的字符串或是一个包含有一个或多个通配符的部分字符串。通过使用LIKE 操作，你可以在一个结果集里查找一个域并找到所有符合特殊的样式的值。

SELECT *
FROM tblCustomers
WHERE [Last Name] LIKE 'Johnson'


为了返回所有的名字以字母J开头的顾客，我们使用星号通配符。

SELECT *
FROM tblCustomers
WHERE [Last Name] LIKE 'J*'


通过使用NOT逻辑操作符，我们可以检索出LIKE操作的反操作，并在列表中过滤掉所有的 Johnsons。

SELECT *
FROM tblCustomers
WHERE [Last Name] NOT LIKE 'Johnson'


你在LIKE 操作样式里可以使用多种通配符，如下表所示：

通配符
描述

*（星号）
匹配所有字符并可以被用在结构字符串的任何位置。

%（百分号）
批评任何字符并可以被用在结构字符串的任何位置。(只适用于ADO 和 the Jet OLE DB provider)

?（问号）
匹配任何单个字符并可以被用在结构字符串的任何位置。

_（下划线）
匹配任何单个字符并可以被用在结构字符串的任何位置。(只适用于ADO 和 the Jet OLE DB provider)

#（数字符号）
匹配任何单个数字并可以被用在结构字符串的任何位置。

[]（方括号）
匹配任何被包括在方括号里面的单个字符，并可以被用在结构字符串的任何位置。

!（感叹号）
匹配任何不属于被方括号所包含的字符列表中的单个字符。Matches any single character not in the list that is enclosed within the square brackets.

-（连字符）
匹配任何一个在方括号中的字符。


注意: 上表所述的“%”和“_”通配符只能通过Jet OLE DB provider 或 ADO运行。如果通过. access SQL View 用户界面运行它们将获得一个空的结果集。

如果你想得到更多的通配符的信息，在Office 助理或微软access帮助窗体的Answer Wizard标志中输入wildcard characters，并单击Search。

IS NULL 操作符
空值就是指无值或不可知值。IS NULL操作符被用于判断一个表达式的值是否和一个空值相等。

SELECT *
FROM tblInvoices
WHERE Amount IS NULL


通过添加NOT逻辑操作符，我们可以检索IS NULL操作符的反操作。在这个例子里，SQL语句将会除掉所有包含丢失的或未知值的发票记录。

SELECT *
FROM tblInvoices
WHERE Amount IS NOT NULL

SELECT INTO 语句
SELECT INTO 语句，也可以理解为一个表单创建查询，可以用来从一个或多个已存在工作表中创建一个新的工作表。它所创建的工作表可以基于任何有效的SELECT语句。SELECT INTO 语句可以用来存储记录、创建备份表单或在一个外部数据库里创建新的工作表。

当你用SELECT INTO 语句创建一个新工作表时，所有的新工作表里的域都继承于原始工作表。然而，不包括其他的工作表属性，如主关键字或索引都是在新工作表中被创建。一旦新的工作表被创建，你当然可以使用ALTER TABLE语句添加这些属性。

如果你要创建一个新的工作表，可以使用一个带有你希望在工作表种包含的列的域列表和你新工作表的名称的SELECT INTO 语句，并在FROM子句里提供数据资源。

SELECT *
INTO tblNewCustomers
FROM tblCustomers


为了为新的工作表指定确定的域，把域名列表里的原始工作表的域名用星号代替，并用AS关键字来命名新的工作表中的各列。

SELECT [First Name] & ' ' & [Last Name] AS FullName
INTO tblNewCustomerNames
FROM tblCustomers


如果要在一个已经存在的外部数据库里创建新的工作表，你可以用IN关键字。如果外部数据库不存在，SELECT INTO 语句将会返回一个错误信息。

SELECT *
INTO tblNewCustomers
IN 'C:\Customers.mdb'
FROM tblCustomers


子查询
子查询就是在用在另一个SELECT、SELECT INTO、INSETT INTO 、DELETE 或UPDATE语句内部的SELECT语句。它可以帮助你对基于另一个结果集的结果进行进一步的限制。这叫做嵌入，并且因为一个子查询就是一个SELECT语句，你也可以把一个子查询嵌入到另一个子查询里面。当你在一个SQL语句中使用一个子查询的时候，它可以作为一个域列表、WHERE子句或者HAVING子句的一部分。

这里由三种基本的子查询形式，并且每种都使用不同种类的谓词。

IN子查询
IN 子查询用于检索这样的一组值，即其中记录的某一列的值都为另一个工作表或查询中的一列的值包含。它从其它工作表中只能返回一列，这是一个限制条件。如果返回的多于一列就会产生一个错误。使用发票数据库例子，我们可以写出一个返回所有拥有发票的顾客的列表的ＳＱＬ语句。

SELECT *
FROM tblCustomers
WHERE CustomerID
IN (SELECT CustomerID FROM tblInvoices)


通过使用NOT逻辑操作符，我们可以检索和ＩＮ子查询相反的记录，从而可以获得所有没有任何发票的顾客列表。

SELECT *
FROM tblCustomers
WHERE CustomerID
NOT IN (SELECT CustomerID FROM tblInvoices)


ANY/SOME/ALL子查询

ANY、 SOME和ALL子查询谓词被用于比较主查询的记录和子查询的多个输出记录。ANY 和 SOME谓词是同义词并可以被替换使用。

当你需要从主查询中检索任何符合在子查询中满足比较条件的记录时可以使用ANY或 SOME谓词。谓词应该恰好放在子查询开始的括号前面。

SELECT *
FROM tblCustomers
WHERE CustomerID = ANY
(SELECT CustomerID FROM tblInvoices)


注意由上面SQL语句所返回的结果集和IN子查询例子所返回的那个相同。而与ANY和SOME谓词的不同之处就在于它们都可以使用等于（=）以外的操作符，比如大于（>）和小于（<）。

SELECT *
FROM tblCustomers
WHERE CustomerID > ANY
(SELECT CustomerID FROM tblInvoices)


当我们想在主查询中检索满足子查询比较条件的所有记录时使用谓词ALL。

SELECT *
FROM tblCustomers
WHERE CustomerID > ALL
(SELECT CustomerID FROM tblInvoices)


EXISTS子查询

EXISTS谓词是用于子查询来在一个结果集中检查所以存在值的记录。换句话说，就是如果子查询没有返回任何行，这个比较就False。而如果它返回了一行或多行，这个比较就为True。

SELECT *
FROM tblCustomers AS A
WHERE EXISTS
(SELECT * FROM tblInvoices
WHERE A.CustomerID = tblInvoices.CustomerID)


注意在前面的SQL 语句里， tblCustomers 工作表使用了一个别名。这就是为何我们可以在后来的子查询中引用它的原因。当一个子查询以这种格式与一个主查询联接时就称相关查询。

通过使用NOT逻辑操作符，我们可以检索和EXISTS子查询相反的记录，从而可以得到所有没有任何发票的顾客的结果集。

SELECT *
FROM tblCustomers AS A
WHERE NOT EXISTS
(SELECT * FROM tblInvoices
WHERE A.CustomerID = tblInvoices.CustomerID)


如果你想得到更多的关于子查询的信息，在Office 助理或微软access帮助窗体的Answer Wizard标志中输入SQL subqueries ，并单击Search。

连接
在一个如同access的相关数据库系统中，你会常常需要同时从多个工作表中摘出信息记录。这可以通过使用一个SQL JOIN语句来实现。JOIN语句可以让你从已经定义了相互关系的工作表中检索记录，而不用管记录和工作表之间的关系是一对一、一对多还是多对多。

内部连接
内部连接，也被理解为对等连接，就是被使用的连接里最一般的形式。这种连接通过匹配一个各个工作表中共有的域值来从两个或更多的工作表中检索记录。你所连接的域必须具有类似的数据类型，但你就不能对MOMO和OLEOBJECT数据类型进行连接。为了建立一个INNER JOIN语句，在SELECT语句的FROM子句里使用INNER JOIN关键字。让我们使用INNER JOIN 建立所有拥有发票的顾客的结果集，并带上那些发票的时间和金额。

SELECT [Last Name], InvoiceDate, Amount
FROM tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
ORDER BY InvoiceDate


注意工作表名被INNER JOIN关键字所分开，并且相关的比较是在ON关键字的后面。对于相关的比较，你也可以使用<、 >、 <=、 >=或 <> 操作符，并且你也可以使用BETWEEN关键字。同时注意各个工作表只在比较关系中使用的ID域，它们都不是最后结果集的组成。

如果要进一步的限制SELECT 语句我们可以在ON子句中的比较连接后面使用WHERE子句。在下面的例子中我们通过只包括1998年1月1日以后的发票来缩小结果集。

SELECT [Last Name], InvoiceDate, Amount
FROM tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
WHERE tblInvoices.InvoiceDate > #01/01/1998#
ORDER BY InvoiceDate


在希望连接多个工作表的案例中，你可以嵌入INNER JOIN子句。在这个例子里，我们将在过去的一个SELECT语句的基础上产生我们的结果集，但我们也将通过为tblShipping工作表添加INNER JOIN使结果包括每个顾客的所在城市和国家。

SELECT [Last Name], InvoiceDate, Amount, City, State
FROM (tblCustomers INNER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID)
INNER JOIN tblShipping
ON tblCustomers.CustomerID=tblShipping.CustomerID
ORDER BY InvoiceDate


注意第一个JOIN子句为圆括号所包含以使之从逻辑上和第二个JOIN子句分开。而在FROM子句中使用一个第二个工作表的别名把一个工作表连接到自身也是可能的。让我们假设我们想找到所有具有相同的名的顾客记录。我们可以通过为第二个工作表创建一个别名“A”并查找其姓氏不同的记录来实现。

SELECT tblCustomers.[Last Name],
tblCustomers.[First Name]
FROM tblCustomers INNER JOIN tblCustomers AS A
ON tblCustomers.[Last Name]=A.[Last Name]
WHERE tblCustomers.[First Name]<>A.[First Name]
ORDER BY tblCustomers.[Last Name]


外部连接

外部连接是在当记录保存在某一个工作表中时用于在多个工作表进行记录检索，即使在其它的工作表中没有匹配的记录也行。Jet 数据库引擎共支持两种类型的外部连接。考虑两个互相相近的工作表，一个在左边，另一个在右边。左外部连接将在右工作表中选择所有匹配比较关系标准的所有行和左工作表中的所有行，即使在右工作表中没有匹配记录存在。而右外部连接则是左外部连接的简单反转；即所有在右工作表中的行将被保存。

作为一个例子，让我们假设我们想测定每个顾客的所有发票数量，但如果一个顾客没有发票，我们希望通过消息“NONE”来显示其信息。

SELECT [Last Name] & ', ' & [First Name] AS Name,
IIF(Sum(Amount) IS NULL,'NONE',Sum(Amount)) AS Total
FROM tblCustomers LEFT OUTER JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
GROUP BY [Last Name] & ', ' & [First Name]


在前面的SQL语句中仍然有几个问题。第一个是对字符串连接操作符“&”的使用，这个操作符允许你把两个或更多的域连接到一起组成一个字符串。第二个是 immediate if(IIF)语句，它会检查合并后的字符串是否为空。如果为空，这个语句将返回消息“NONE”，而如果组合不是空，将返回组合后的值。最后一点是外部连接子句。使用左外部连接保存左工作表的行从而让我们可以看到所有的顾客，包括那些没有发票在帐目中的。

在一个多工作表的连接中外部连接可以被嵌套在内部连接里，但内部连接不可以被嵌套在外部连接中。

笛卡儿乘积
当我们讨论联接时常常遇到的一个术语是笛卡儿乘积。笛卡儿乘积的定义为“把所有表单的所有行完全合并”。例如，如果你想不用任何约束把两个工作表联合在一起，你就完成了一个笛卡儿乘积。

SELECT *
FROM tblCustomers, tblInvoices


这不是一个好东西，特别当你要处理的工作表中包含有成百上千行数据时。所以你应该通过约束你的连接来避免笛卡儿乘积。

The UNION operator
虽然UNION 的操作也可以视为一个合并查询，但我们不可以技术性地把它看作是一个联接，它之所以被提到是因为它能把从多个来源获得的数据合成一个结果表单中，而这一点和某些类型的联接是类似的。UNION 操作一般被用来把来自表单、SELECT语句或查询的数据结合，并省略掉任何重复的行。所有的数据源必须有相同数目的域，不过这些域不一定要是相同的数据类型。让我们假设我们有一个雇员表单，其中具有和顾客工作表单相同的结构，那么我们希望合并这两个工作表得到一个姓名和电子邮件地址信息的列表。

SELECT [Last Name], [First Name], Email
FROM tblCustomers
UNION
SELECT [Last Name], [First Name], Email
FROM tblEmployees


如果你希望找到这些表中的所有域，我们可以使用TABLE关键字，如同下面一样：

TABLE tblCustomers
UNION
TABLE tblEmployees


UNION操作不会显示任何在两个表单中重复出现的记录，但它可以通过在UNION关键字后使用谓词ALL来覆盖重复信息，如下所示：

SELECT [Last Name], [First Name], Email
FROM tblCustomers
UNION ALL
SELECT [Last Name], [First Name], Email
FROM tblEmployees


转换语句

虽然转换语句也可以视为一个交叉表查询，但我们不可以技术性地把它看作是一个联接，它之所以被提到是因为它能把从多个来源获得的数据合成一个结果表单中，而这一点和某些类型的联接是类似的。

TRANSFORM 语句通常用于计算总数、平均值、数目以及其它对记录进行总体统计的算法。并在计算后把结果信息显示在一个格子或数据表中，其中的数据分别按照行和列排列。一个TRANSFORM 语句的一般形式如下：

TRANSFORM aggregating function
SELECT Statement
PIVOT column heading field


我们假设我们可以建立一个按照每一年为基础显示每个顾客的所有发票的数据表。这个列名应该是顾客的姓名，而行名则将是年份。让我们修改原来的SQL语句以符合转变后的语句.

TRANSFORM
IIF(Sum([Amount]) IS NULL,'NONE',Sum([Amount]))
AS Total
SELECT [Last Name] & ', ' & [First Name] AS Name
FROM tblCustomers LEFT JOIN tblInvoices
ON tblCustomers.CustomerID=tblInvoices.CustomerID
GROUP BY [Last Name] & ', ' & [First Name]
PIVOT Format(InvoiceDate, 'yyyy')
IN ('1996','1997','1998','1999','2000')


注意SUM函数是合计函数，组里的列的题头用在SELECT 语句的子句里，而行的名字由PIVOT关键字后所列出的域名决定。

如果你想知道关于连接的更多信息，在Office 助理或微软access帮助窗体得Answer Wizard标志中输入SQL joins，并单击Search。