(转)：MS Jet SQL for Access 2000中级篇(天极网)

zhuyiwen

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



--------------------------------------------------------------------------------



    该系列的文章解释了什么是Microsoft® Jet SQL，并说明了它如何在Access 2000的应用程序中使用。本文是基础、中级和高级三篇中的第二篇。这些文章循序渐进的说明了使用Jet SQL的语法和一些方法，并示范了对于Access 2000来说较新的Jet SQL的特征。最后，本文的所有SQL参考都是针对Microsoft Jet 4.0 数据引擎所用的。

中级SQL说明
    通过了解中级结构化查询语言的一些概念，用户可以增强控制数据库的结构和对象的能力，并且可以通过许多有趣的和强有力的途径操纵这些机构中包含的数据。结合使用诸如DAO和ADO之类的数据访问方法，中级SQL 可以很大程度的提高用户应用程序的灵活性及其表现。

基础、中级和高级SQL的不同点
    在基础、中级和高级SQL之间划分一条明显的界限并不是一件容易的事情，在很多情况下，决定其所属都只是简单的决断。但是对于该系列在Access 2000中使用SQL的文章来说，还考虑了如下的一些方面：

    首先一点在于SQL语句本身的复杂级别。在前面的文章中，我们努力使用那些常用的语句，而且是最简单的形式。本文则在前面基础篇的基础上介绍了更复杂的语句。
    其次是在Access 2000中已经包含了一些新的SQL语句、子句和关键词。尽管在本文中仍将使用Access上一版本中所包含的SQL语句，但是一定会出现一些在Access中第一次出现的SQL语句，在高级SQL一文中也是如此。

    最后，那些关于安全性和多用户解决方案的SQL语句将在高级篇中介绍，因为这些语句常常是用于更复杂的应用程序中的 。通过使用中级SQL，用户可以为你的Access应用程序增加更强的灵活性和能力。尽管简单的和直接的SQL语句能够完成很多的事情，但是使用更复杂的语句将扩展在数据库中访问和处理信息的方法范围。使用中级SQL也将使得用户可以更好的控制你的数据库使用和维护工作 。

增强SQL
    在Access 2000中，为了支持Access的新特性，为了和ANSI-92的标准更加一致，并且允许在Access 和 Microsoft® SQL Server™间的更好的兼容性，包含在Microsoft Jet 4.0数据引擎中的SQL作了许多增强。Jet数据引擎现在有两种格式的SQL语法：一种是支持以前使用的SQL语法，另一种是支持新的SQL语法规范。需要格外注意的是，只有在用户使用ActiveX® Data Objects (ADO)和Jet OLE DB provider时新的语法才是可用的，而当前通过Access SQL View接口或者DAO是无法使用新的语法的。本文指出只有通过Jet OLE DB provider 和 ADO才能够使用某个特定的SQL命令。

    在Access的上一版本中，数据访问对象(DAO)是主要的数据访问方法。而现在发生了改变，尽管DAO 仍旧被支持，但新的数据访问方法是使用ADO。ADO是微软的通用数据访问战略（Microsoft's Universal Data Access strategy）的一部分，其最基本的假设是无论数据存在那里都是可以访问的，不管是数据库、目录结构还是某重用户自定义的数据库。

    在讨论Microsoft Jet SQL 时，ADO是非常重要的，正如在前面提到的，一些新的SQL语句只有在使用ADO 和Jet OLE DB provider时才是可用的。在本文和与本文相配套的示范数据库中，所有的代码都是使用ADO些的。那些没有特意指明为只有通过ADO才可用的SQL语句都可以通过Access SQL View 用户接口或 DAO来执行。详尽的关于ADO的讨论超出了本文的范围，用户可以在下面的网站中找到最近的信息 ：http://www.microsoft.com/data/ado/.



SQL代码规范
    本文使用一致性的SQL代码规范。与所有的代码规范相同，目的是使用易读和易于理解的方式来显示代码。这就要通过空格、换行和大写关键字的结合使用来实现。通常来说，要使用大写字母来打印SQL的关键字，如果SQL语句必须换行，尽量使SQL语句的主要部分一起换行。看过一些例子之后，相信读者会对此有较好的感觉。

不合格式的SQL代码
CREATE TABLE tblCustomers (CustomerID INTEGER NOT NULL, [Last Name] TEXT(50) NOT NULL, [First Name] TEXT(50) NOT NULL, Phone TEXT(10), Email TEXT(50))

良好格式的SQL代码

CREATE TABLE tblCustomers (
   CustomerID INTEGER NOT NULL,
   [Last Name] TEXT(5) NOT NULL,
   [First Name] TEXT(50) NOT NULL,
   Phone TEXT(10),
   Email TEXT(50))


[em26]

zhuyiwen

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



--------------------------------------------------------------------------------



改变数据表
    在建立或倒入一个数据表之后，用户可能需要修改表的设计。这时就可以使用ALTER TABLE语句。但是注意，改变现存的表的结构可能会导致用户丢失一些数据。比如，改变一个域的数据类型将导致数据丢失或舍入错误，这取决于用户现在使用的数据类型。改变数据表也可能会破坏用户的应用程序中涉及到所改变的域的部分。所以用户在修改现有表的结构之前一定要格外小心。

    使用ALTER TABLE 语句，用户可以增加，删除或改变列或域，也可以增加或删除一个约束。还可以为某个域设定缺省值，但是一次只能修改一个域。假设我们有一个记账单的数据库，而我们想在顾客数据表中增加一个域，这时可以使用ALTER TABLE 语句，在其ADD COLUMN 子句后写上域的名称、数据类型和数据的大小（如果需要的话）。

ALTER TABLE tblCustomers
ADD COLUMN Address TEXT(30)
    要改变域的数据类型或大小，可以使用ALTER COLUMN子句，在后面加上期望的数据类型和数据的大小。

ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40)
    如果需要改变域的名称，则必须删除该域并重新创建。删除一个域要使用DROP COLUMN 子句，在其后跟上域的名称。

ALTER TABLE tblCustomers
DROP COLUMN Address
    注意使用这种方法将会删除该域的现存数据。如果需要保存这些数据，则用户需要在Access的用户界面的设计模式中改变该域的名称，或者编写代码将现存的数据保存在一个临时的表中然后将其添加到改名后的表中。

    缺省值是指在表中增加新纪录并且没有为该列赋值时自动填充到该域中的值。为某域设置缺省值，要在定义域的类型后使用使用DEFAULT关键字，不管是使用ADD COLUMN或 ALTER COLUMN 子句。

ALTER TABLE tblCustomers
ALTER COLUMN Address TEXT(40) DEFAULT Unknown
    注意缺省值并不使用单引号包含，如果用了单引号，则引号也会插入到记录中。在CREATE TABLE语句中也可以使用DEFAULT关键字。

CREATE TABLE tblCustomers (
CustomerID INTEGER CONSTRAINT PK_tblCustomers
PRIMARY KEY,
[Last Name] TEXT(50) NOT NULL,
[First Name] TEXT(50) NOT NULL,
Phone TEXT(10),
Email TEXT(50),
Address TEXT(40) DEFAULT Unknown)

    注意： DEFAULT 语句只有在Jet OLE DB provider和ADO中可以执行，在Access SQL View的用户界面中使用将会返回错误信息。

    下面的部分将讨论如何在ALTER TABLE语句中使用约束。要获得更详细的有关ALTER TABLE的说明，请在Office 助手中或在Microsoft Access 帮助的回答向导的标签页中输入ALTER TABLE ，然后单击查找。

约束
    在《Access 2000的基础Microsoft Jet SQL》一文中，我们讨论了建立表之间的联系的约束方法。约束也能用于建立主键和参考完整性，来限制插入到一个域中的数据值。通常，约束可以用于保持用户数据库中的数据完整性和一致性。

    共有两种类型的约束：单数据域（或称域级的）的约束和多数据域（或称表级的）的约束。两种约束都可以用在CREATE TABLE 或 ALTER TABLE 语句中。

    单域的约束，也就是通常所说的列级的约束，是在域及其数据类型定义后针对该域定义的。下面我们使用用户表，在CustomerID域生成一个单域的主键。增加约束时，在域名后使用CONSTRAINT关键字。

ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER
CONSTRAINT PK_tblCustomers PRIMARY KEY

    注意这里给出了约束的名称。用户还可以在定义主键时使用简称而省略CONSTRAINT子句。

ALTER TABLE tblCustomers
ALTER COLUMN CustomerID INTEGER PRIMARY KEY

    然而，使用简称的方法将导致Access随机的生成约束的名称，从而使得在代码中难以引用。所以，最好给约束制定名称。

    要删除一个约束，可以在ALTER TABLE 语句中使用DROP CONSTRAINT 子句，并给出约束的名称。

ALTER TABLE tblCustomers
DROP CONSTRAINT PK_tblCustomers


    约束还可以用来给域限制允许值。用户可以将限制值设为非空（NOT NULL）或唯一（ UNIQUE），或者定义一个检验性的约束，该约束指一种可以应用于某个域的规则。比如用户希望限制姓和名的域是唯一的，就意味着在表中永远不会有两个相同姓名的记录存在。这是因为这种约束是多域的性的，是在表的级别定义的，而非域的级别。使用ADD CONSTRAINT子句可以定义一个多域的列表。

ALTER TABLE tblCustomers
ADD CONSTRAINT CustomerNames UNIQUE
([Last Name], [First Name])

    注意： 我们在这里只是示范一下如何使用约束，而在实际的应用程序中，用户可能并不希望彻底限制姓名的唯一性。

    检验性约束是一种新的强有力的SQL特性，它通过一个表达式从而允许用户在表中添加数据合法性检验，该表达式可以指向一个单域，也可以指向跨越一个或多个表的多个域。

zhuyiwen

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

zhuyiwen

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



--------------------------------------------------------------------------------



现在我们已经讨论了中级SQL的语法，那么让我们看看在一个Access应用程序中我们可以使用它的一些途径。

数据库范例
作为这篇文章的附带，这里有一个叫acIntSQL.mdb的数据库范例。

在acIntSQL中的任一处都是基于本文所提到的所有主题，并且它通过查询和范例的代码演示了我们所讨论的不同SQL语句。

在acIntSQL中所使用到的许多部分查询都是基于特定工作表中存在和包含的数据，或者是基于其它已经存在的数据库对象。如果你由于丢失数据而在运行一个查询产生故障，打开工作表重置窗体并单击工作表重置按键。这将会重新生产工作表和其中原始缺省数据。如果要手动通过工作表重置过程，你需要按照下面的顺序执行这些查询过程：

Drop Table Shipping
Drop Table Invoices
Drop Table Customers
Drop Table CreditLimit
Create Table Customers
Create Table Invoices
Create Table Shipping
Create Table CreditLimit
Populate Customers
Populate Invoices
Populate Shipping
Populate CreditLimit

查询
查询就是指存储在Access数据库中并可以随时调用的SQL 语句，也可以直接被Access 用户界面或Visual Basic® for Applications (VBA)编程语言调用。查询可以使用Access Query Designer来建立，Access Query Designer时一个可以很容易建立SQL语句的强大的可视化工具。或者你也可以通过直接在SQL视图窗口输入SQL语句来建立查询。

如同在"Microsoft Jet SQL for Access 2000基础篇"一文中所述， Access把数据库中所有面向数据的任务转化为SQL 语句。要演示这一点，让我们使用Access Query Designer来建立一个查询。

打开acIntSQL数据库。
(单击此处拷贝acIntSQL.mdb 数据库例子。)

确保tblCustomers 和 tblInvoices这两个表单已经创建并且其中包含有数据。
在数据库窗口中从Objects条中选择Queries选项。
在数据库窗口工具条里单击按键 New。
在New Query对话框中选择Design View并单击OK。
在Show Table对话框中选择tblCustomers并单击Add；接着选择tblInvoices 并单击Add；接着单击Close按键。
在tblCustomers 域名列表中选择Last Name 域并把它拖到设计表格中的第一个域中。
在tblInvoices 域名列表中选择InvoiceDate 和 Amount域并把它们拖到设计表格中。
在设计表格中InvoicwDate域的Sort属性里选择Ascending。
从Access菜单条中选择View并单击SQL View。这样就打开了SQL View 窗口和显示了在查询中Access正在使用的SQL语法。
注意 这个查询类似于存储在acIntSQL 数据库中的"Join - Inner"查询。

嵌入语句
嵌入SQL 语句就是指你在Visual Basic for Applications (VBA) 编程语言中使用SQL 语句。虽然深入讨论如何使用VBA超出了本文的范围，但如何使用程序来运行SQL 语句却是一件简单的工作。

在acIntSQL数据库中，有两个使用内部SQL语句的窗体需要通过Jet OLE DB provider 和 ADO来运行：就是演示数据定义语句的 Intermediate DDL 窗体，以及演示数据处理语句的Intermediate DML窗体。

中级DDL 语句
这个acIntSQL数据库有许多关于你可以用来管理你的数据库结构的SQL 语句的范例。有一部分数据定义语言 (DDL) 语句被以数据定义查询的方式存储。而其它的则在程序设计代码的内部作为内联SQL使用。如果你要使用这些DDL例子，你需要在运行它之前删除一些数据库对象。例如，如果你想运行创建当前数据类型的查询，你就先要确定当前数据表单不存在。如果不是，那么，这个查询就会返回一个信息告诉你这个表单已经存在。在运行任何DDL 例子之前，检查它所要创建或改变的数据库对象确定其已经被设定好，这样程序才能完全的运行。

在内联DDL 语句范例中，同样的建议仍然是适用的：检查并设定它们会影响的数据库对象以确保DDL 语句的顺利运行。

一般来说，内联DDL 语句只是通过简单设定一个ADO Connection对象来运行，并将SQL 语句传送给 Connection对象的运行程序。下面是来源于在中级DDL语句窗体的二进制数据类型的输入输出。

Private Sub cmdBinary_Click() Dim conDatabase As ADODB.Connection
Dim SQL As String
On Error GoTo Error_Handler
Set conDatabase = Application.CurrentProject.Connection
'注意: Fields 1 through 4 can be created through both
'SQL View and the Jet OLEDB Provider.
'Fields 5 and 6 can only be created through the
'Jet OLE DB provider.

SQL = "CREATE TABLE tblCodeBinaryDataTypes (" & _
"Field1_BINARY BINARY, " & _
"Field2_BINARY250 BINARY(250), " & _
"Field3_VARBINARY VARBINARY, " & _
"Field4_VARBINARY250 VARBINARY(250), " & _
"Field5_BVARYING BINARY VARYING, " & _
"Field6_BVARYING250 BINARY VARYING(250))"
conDatabase.Execute SQL
MsgBox "The BINARY dat

大熊

这的确是篇新手入门的好文章！