8.4 对SELECT-sql语言的改进与增强
为了更加符合ANSI-92 SQL标准，Visual foxpro的每个版本几乎都会对SQL命令做一些改进与增强。例如，Visual FoxPro 8.0对含有LIKE、GROUP BY和HAVING子句的SELECT-SQL命令进行了改进，并对SELECT-SQL...UNION等命令进行了增强。Visual FoxPro 9.0解除了对子查询的数量限制、对ORDER BY和UNION进行了增强，等等。
8.4.1 Visual FoxPro的SQL语言兼容性
可以使用SET ENGINEBEHAVIOR命令启用对各个Visual FoxPro版本的SQL语言支持，其语法格式如下：

SET ENGINEBEHAVIOR 70 | 80 | 90

其中，70指定Visual FoxPro按照8.0以前版本的方式来处理SQL命令，80指定按照Visual FoxPro 8.0的行为来处理SQL命令，90指定按照Visual FoxPro 9.0的行为来处理SQL命令，默认值为90。

此外，Visual FoxPro还提供了SYS(3099[,70|80|90])函数，该函数与SET ENGINEBEHAVIOR命令的功能完全相同。例如，SYS(3099,70)等同于SET ENGINEBEHAVIOR 70命令。如果不包含70、80和90参数，该函数将返回当前Visual FoxPro数据引擎的支持方式。例如，如果当前是按照8.0以前版本的方式来处理SELECT-SQL命令，则SYS(3099)返回数值型值70。

8.4.2 Visual FoxPro 8.0的SELECT-SQL语言行为
下面是Visaul FoxPro 8.0对SELECT-SQL语言行为的改进与增强，这些行为在Visual FoxPro 9.0中同样被遵守。
5．从SELECT-SQL命令添加记录
现在使用INSERT-SQL命令时可以从一个SELECT-SQL中添加记录，可以复制指定列或全部列到一个记录中，但是要确认复制数据与复制到记录中的列数据的兼容性。参考下列代码：

CREATE CURSOR t1 (f1 c(10))          &&建立表

INSERT INTO t1 VALUES ('1111111111') &&添加记录

CREATE CURSOR t2 (f1 c(10))

INSERT INTO t2 VALUES ('222222')

INSERT INTO t2 VALUES ('333333')

INSERT INTO t1 (f1) SELECT f1 FROM t2 WHERE f1='222222' &&将t2表中f1等于222222的记录添加到t1中

8.4.3 Visual FoxPro 9.0的SELECT-SQL语言行为
SELECT–SQL命令和其他SQL命令在Visual FoxPro 9.0中进行了相当大的增强。主要包括以下方面：

1．容量扩充
在Visual FoxPro 9.0中，移除或放宽了对几个SELECT-SQL命令的限制，包括：

l 移除了JOIN子句和SELECT-SQL命令中子查询的数量限制（先前限制为9）。

l 移除了SELECT-SQL语句中UNION子句的数量限制（先前限制为9）。

l 移除了SELECT-SQL语句中所引用表和别名的数量限制（先前限制为30）。

l 移除了IN (Value_Set)子句（应用于WHERE子句）的24个值限制，但是，值的数目仍受SYS(3055)函数的限制。SYS(3055)函数用于设置FOR和WHERE子句的复杂程度。

2．子查询增强
Visual FoxPro 9.0为子查询提供了更大的灵活性，例如，现在支持多重子查询。

（1）使用多重子查询
Visual FoxPro 9.0支持多层嵌套子查询，嵌套深度没有限制。在Visual FoxPro 8.0中，当子查询嵌套层次超过一层时发生1842错误（子查询嵌套过深）。下面是多重子查询的语法格式：

SELECT … WHERE … (SELECT … WHERE … (SELECT …) …) …

参考下列代码：

CREATE CURSOR MyCursor (field1 I)

INSERT INTO MyCursor VALUES (0)

INSERT INTO MyCursor VALUES (1)

CREATE CURSOR MyCursor1 (field1 I)

INSERT INTO MyCursor1 VALUES (1)

INSERT INTO MyCursor1 VALUES (2)

CREATE CURSOR MyCursor2 (field1 I)

INSERT INTO MyCursor2 VALUES (2)

SELECT * FROM MyCursor T1 WHERE T1.field1 IN ;

   (SELECT * from MyCursor1 T2 WHERE T2.field1 NOT IN ;

   (SELECT * FROM MyCursor2 T3))

上面SELECT语句首先从MyCursor2取出全部记录，然后取出MyCursor1中的全部记录，并检索出其在MyCursor2不存在的记录，所得到的结果集合中只包含值为1的记录。最后取出MyCursor中的全部记录，与前面生成的结果集合进行比较，检索出二者相同的记录，最终的结果集合仍旧是只有包含值为1的记录，

（2）在相互关联子查询中使用GROUP BY
在Visual FoxPro 9.0中，子查询可以被重复执行，即会被外部查询的每条记录调用一次。

Visual FoxPro 8.0不允许在相互关联子查询中使用GROUP BY子句，否则将产生1828错误（子查询中出现非法的GROUP BY子句），Visual FoxPro 9.0移除了此限制。

下面是相互关联子查询中GROUP BY子句的语法格式。

SELECT … WHERE … (SELECT … WHERE … GROUP BY …) …

参考下列代码。

CLOSE DATABASES ALL

CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor1 VALUES(1,2,3)

INSERT INTO MyCursor1 VALUES(4,9,3)

CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor2 VALUES(1,2,3)

INSERT INTO MyCursor2 VALUES(2,2,3)

INSERT INTO MyCursor2 VALUES(2,9,3)

INSERT INTO MyCursor2 VALUES(4,9,3)

SELECT * from MyCursor1 T1 WHERE field1;

   IN (SELECT MAX(field1) FROM MyCursor2 T2 ;

   WHERE T2.field2=T1.field2 GROUP BY field3)

上面的子查询对于MyCursor1中的每条记录都要执行一次。在子查询中，每次取出MyCursor2中T2.field2=T1.field2的所有记录，并按field3进行分组后取出每组中field1的最大值记录。最后查找MyCursor1中的field1是否包含在子查询结果中。然后再依次执行MyCursor1中的下一条记录。最终得到的结果是MyCursor1中的第2条记录，如图8-27所示。

（3）在非相互关联子查询中使用TOP N
Visual FoxPro 9.0支持在一个非相互关联子查询中使用TOP N子句，在使用了TOP N子句的情况下，子查询中也必须包含ORDER BY子句。并且，ORDER BY仅允许出现在子查询中。

下面是在一个非相互关联子查询中TOP N子句的语法格式。

SELECT … WHERE … (SELECT TOP nExpr [PERCENT] … FROM … ORDER BY …) …

参考下列代码：

CLOSE DATABASES ALL

CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor1 VALUES(1,2,3)

CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor2 VALUES(1,2,3)

SELECT * FROM MyCursor1 WHERE field1 ;

   IN (SELECT TOP 5 field1 FROM MyCursor2 ORDER BY field1)

上面的查询首先从MyCursor2中得到前5条记录，然后与MyCursor1中的field1进行比较，得到一个二者的交集，如图8-28所示。

（4）在SELECT的查询项列表中使用子查询
Visual FoxPro 9.0允许把子查询作为一个查询项或是作为表达式的一部分。如果子查询未返回任何记录，则使用NULL值。在Visual FoxPro 8.0中，这样的查询会发生1810错误（子查询不能在此处使用）。

下面是在SELECT的查询项列表中使用子查询时的语法格式。

SELECT … (SELECT …) … FROM …

参考下列代码：

CREATE CURSOR MyCursor1 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor1 VALUES(1,2,8)

INSERT INTO MyCursor1 VALUES(1,2,9)

CREATE CURSOR MyCursor2 (field1 I, field2 I, field3 I)

INSERT INTO MyCursor2 VALUES(1,4,3)

INSERT INTO MyCursor2 VALUES(2,3,6)

SELECT T1.field1, (SELECT field2 FROM MyCursor2 T2;

   WHERE T2.field1=T1.field1), T1.field3 FROM MyCursor1 T1

上面的代码首先使用子查询从MyCursor2中取出field2字段值作为外部查询结果的一个项，然后从MyCursor1中取出field1和field3字段，最终结果集合如图8-29所示。

 

图8-29 在SELECT的查询项列表中使用子查询时的结果集合

如果子查询的返回结果超出了一行，将发生1860错误（子查询返回了多条记录）。

（5）在子查询的SELECT查询项列表中使用聚合函数
在Visual FoxPro 9.0中，支持在子查询的SELECT查询项列表中使用聚合函数，通过在比较运算符（<、<=、>、>=）后面跟着ALL、ANY或SOME来进行比较。

参考下列代码：

CLOSE DATABASES ALL

CREATE CURSOR MyCursor (FIELD1 i)

INSERT INTO MyCursor VALUES (6)

INSERT INTO MyCursor VALUES (0)

INSERT INTO MyCursor VALUES (1)

INSERT INTO MyCursor VALUES (2)

INSERT INTO MyCursor VALUES (3)

INSERT INTO MyCursor VALUES (4)

INSERT INTO MyCursor VALUES (5)

INSERT INTO MyCursor VALUES (-1)

CREATE CURSOR MyCursor2 (FIELD2 i)

INSERT INTO MyCursor2 VALUES (1)

INSERT INTO MyCursor2 VALUES (2)

INSERT INTO MyCursor2 VALUES (2)

INSERT INTO MyCursor2 VALUES (3)

INSERT INTO MyCursor2 VALUES (3)

INSERT INTO MyCursor2 VALUES (3)

INSERT INTO MyCursor2 VALUES (4)

INSERT INTO MyCursor2 VALUES (4)

INSERT INTO MyCursor2 VALUES (4)

INSERT INTO MyCursor2 VALUES (4)

SELECT * FROM MyCursor WHERE field1;

   < ALL (SELECT COUNT(*) FROM MyCursor2 GROUP BY field2) ;

   INTO CURSOR MyCursor3

BROWSE

上面的子查询中使用COUNT()函数来计算每组中值的数目，得到一个结果集合。然后把MyCursor中的field1字段与这个子查询结果集合进行比较，检索出MyCursor中小于子查询结果集合中所有行的记录，如图8-41所示。

 

图8-30 在子查询的SELECT查询项列表中使用聚合函数时得到的结果集合

（6）在相互关联子查询中允许使用复杂表达式与相互关联字段进行比较
在Visual FoxPro 8.0中，相互关联字段仅能按下面格式引用：

相互关联字段 <比较运算符> 本表字段

或者是下面的格式：

本表字段 <比较运算符> 相关联字段

在Visual FoxPro 9.0中，支持相关联字段与本表字段表达式进行比较，格式如下：

相互关联字段 <比较运算符> 本表字段表达式

或者是下面的格式：

本表字段表达式 <比较运算符> 相互关联字段

一个本表字段表达式至少要包含一个本表字段，并且不允许引用外部字段（相互关联字段）。

参考下列代码：

SELECT * FROM MyCursor ;

   WHERE EXISTS(SELECT * FROM MyCursor2 ;

   WHERE MyCursor2.field2 / 2 > MyCursor.field1)

在上面的子查询中，“MyCursor2.field2 / 2”是本表字段表达式，“MyCursor.field1”是相互关联字段。

（7）对表达式与子查询比较的修改
在Visual FoxPro 8.0中，比较的左边部分和子查询应当引用一个且仅能是一个来自FROM子句的表。如果是一个具有相互关联子查询的比较，则表也应当是相互关联表。

在Visual FoxPro 9.0中，按照下面的方式进行比较：

l 一个IN比较左边部分的表达式应当至少引用一个来自FROM子句的表。

l =、==、<>、!=后跟着ALL、SOME或ANY的条件的左侧应当至少引用一个来自FROM子句的表。

l >、>=、<、<=后跟着ALL、SOME或ANY (SELECT TOP…)的条件的左侧应当至少引用一个来自FROM子句的表。

l >、>=、<、<=后跟着ALL、SOME或ANY (SELECT <聚合函数>…)的条件的左侧应当至少引用一个来自FROM子句的表。

l >、>=、<、<=后跟着ALL、SOME或ANY (带有GROUP BY和/或HAVING的子查询)的条件的左侧应当至少引用一个来自FROM子句的表。

l 在Visual FoxPro 9.0中，如果比较项的左侧不是来自查询项列表（如未包含ALL、SOME或ANY），则左侧的项不一定要引用来自FROM子句的表。

总之，比较项的左侧允许引用来自FROM子句的多个表，对于一个相互关联的子查询项，比较项的左侧不能引用相互关联表。

（8）在UPDATE–SQL命令的SET列表中使用子查询项。
在Visual FoxPro 9.0中，UPDATE-SQL命令支持在SET子句中使用子查询项。

在SET子句中的子查询和在比较运算符中使用的子查询具有同样的要求，如果子查询没有返回任何记录，则使用NULL值。

在一个SET子句中仅允许有一个子查询，如果在SET子句中包含了子查询，则不允许在WHERE子句中使用子查询。

下面是SET子句中子查询的语法格式：

UPDATE … SET … (SELECT …) …

参考下列代码：

CREATE CURSOR MyCursor1 (field1 I , field2 I NULL)

INSERT INTO MyCursor1 VALUES (1,1)

INSERT INTO MyCursor1 VALUES (2,2)

INSERT INTO MyCursor1 VALUES (5,5)

INSERT INTO MyCursor1 VALUES (6,6)

INSERT INTO MyCursor1 VALUES (7,7)

INSERT INTO MyCursor1 VALUES (8,8)

INSERT INTO MyCursor1 VALUES (9,9)

CREATE CURSOR MyCursor2 (field1 I , field2 I)

INSERT INTO MyCursor2 VALUES (1,10)

INSERT INTO MyCursor2 VALUES (2,20)

INSERT INTO MyCursor2 VALUES (3,30)

INSERT INTO MyCursor2 VALUES (4,40)

INSERT INTO MyCursor2 VALUES (5,50)

INSERT INTO MyCursor2 VALUES (6,60)

INSERT INTO MyCursor2 VALUES (7,70)

INSERT INTO MyCursor2 VALUES (8,80)

UPDATE MyCursor1 SET field2=100+(SELECT field2 FROM MyCursor2 ;

   WHERE MyCursor2.field1=MyCursor1.field1) WHERE field1>5

SELECT MyCursor1

BROWSE

上面的子查询首先从MyCursor2中根据MyCursor2.field1=MyCursor1.field1条件取出field2的字段值，然后再将该值加100对MyCursor1的field2字段赋值，结果集合如图8-31所示。

如果子查询的返回结果超出了一行，将发生1860错误（子查询返回了多条记录）。

 
 
 

7．对本地缓冲数据的支持
有时对于能够从还未进行发送更新的本地缓冲临时表中使用SELECT-SQL来获取缓冲数据是非常有用的。但是，通常情况下，SQL语句都是通过已提交到磁盘中的内容来获取数据的。Visual FoxPro 9.0进行了这方面的增强，在使用SELECT-SQL时，允许指定是从缓冲还是从磁盘中获取数据。

SELECT-SQL语句现在支持一个WITH (BUFFERING = lExpr)子句，在lExpr为.T.时，指定从缓冲中获取数据，在lExpr为.F.时，则指定从磁盘中获取数据。

下面的代码将分别从缓冲和磁盘中获取数据，结果分别保存在BufferTable和DiskTable表中，如图8-34所示。

SET MULTILOCKS ON

CREATE TABLE Table1 (Field1 N(10), Field2 N(10))

CURSORSETPROP("Buffering", 5, "Table1") &&使用表缓冲

INSERT INTO Table1 VALUES (1, 1) &&插入记录

TABLEUPDATE(.T.)            &&提交记录到磁盘

INSERT INTO Table1 VALUES (2, 2) &&插入记录，该记录将保存在缓冲中

SELECT * FROM Table1 WITH (BUFFERING=.T.) INTO CURSOR BufferTable &&获取缓冲中的数据

BROWSE NOWAIT

SELECT * FROM Table1 WITH (BUFFERING=.F.) INTO CURSOR DiskTable    &&获取磁盘中的数据

BROWSE NOWAIT

 

有关表缓冲方面的内容，请参考第19章中的介绍。

如果未使用BUFFERING子句，则数据检索受SET SQLBUFFERING命令控制。如果设置为ON，则从缓冲中获取数据；如果设置为OFF（默认值），则从磁盘中获取数据。

8．其他SQL命令增强
（1）在INSERT-SQL命令中使用UNION子句
在Visual FoxPro 9.0中，支持在INSERT-SQL命令中使用UNION子句。语法格式如下：

INSERT INTO … SELECT … FROM … [UNION SELECT … [UNION …]]

参考下列代码：

CREATE CURSOR MyCursor (field1 I,field2 I)

CREATE CURSOR MyCursor1 (field1 I,field2 I)

CREATE CURSOR MyCursor2 (field1 I,field2 I)

INSERT INTO MyCursor1 VALUES (1,1)

INSERT INTO MyCursor2 VALUES (2,2)

INSERT INTO MyCursor SELECT * FROM MyCursor1 UNION SELECT * FROM MyCursor2

SELECT MyCursor

BROWSE

上面的INSERT语句首先将UNION两侧通过SELECT得到的结果集合进行合并，然后把合并后的结果集合插入到MyCursor中，如图8-35所示。

 

（2）相互关联的UPDATE-SQL命令
Visual FoxPro 9.0现在支持通过UPDATE-SQL命令进行相互关联更新。如果一个FROM子句包含在UPDATE-SQL命令中，则UPDATE后面的名称是进行更新操作的目标，该名称可以是一个表名称、别名，或是一个文件名称。下面的逻辑用于选择目标表：

l 如果名称与一个FROM子句中表的隐式或显式别名匹配，则该表作为更新操作的目标表。

l 如果名称与一个当前数据环境中临时表的别名匹配，则该临时表作为目标表。

l 一个表或具有相同名称的文件可以被作为目标表。

UPDATE-SQL命令的FROM子句与SELECT-SQL命令中的FROM子句具有相同的语法限制：

l 目标表或临时表不能被包在OUTER JOIN中作为第二个表。

l 目标临时表不能是一个子查询结果。

l 在连接目标表前，所有其他的JOIN能够被计算。

下面是相互关联UPDATE命令的语法格式：

UPDATE … SET … FROM … WHERE …

参考下列代码：

CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)

INSERT INTO MyCursor1 VALUES (1,1,0)

INSERT INTO MyCursor1 VALUES (2,2,0)

INSERT INTO MyCursor1 VALUES (5,5,0)

INSERT INTO MyCursor1 VALUES (6,6,0)

INSERT INTO MyCursor1 VALUES (7,7,0)

INSERT INTO MyCursor1 VALUES (8,8,0)

INSERT INTO MyCursor1 VALUES (9,9,0)

CREATE CURSOR MyCursor2 (field1 I , field2 I)

INSERT INTO MyCursor2 VALUES (1,10)

INSERT INTO MyCursor2 VALUES (2,20)

INSERT INTO MyCursor2 VALUES (3,30)

INSERT INTO MyCursor2 VALUES (4,40)

INSERT INTO MyCursor2 VALUES (5,50)

INSERT INTO MyCursor2 VALUES (6,60)

INSERT INTO MyCursor2 VALUES (7,70)

INSERT INTO MyCursor2 VALUES (8,80)

CREATE CURSOR MyCursor3 (field1 I , field2 I)

INSERT INTO MyCursor3 VALUES (6,600)

INSERT INTO MyCursor3 VALUES (7,700)

UPDATE MyCursor1 SET MyCursor1.field2=MyCursor2.field2, field3=MyCursor2.field2*10 FROM MyCursor2 ;

WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1

SELECT MyCursor1

LIST OFF

UPDATE MyCursor1 SET MyCursor1.field2=MyCursor3.field2 FROM MyCursor2, MyCursor3 ;

WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND MyCursor2.field1=MyCursor3.field1

SELECT MyCursor1

LIST OFF

上面代码中的两个UPDATE语句对MyCursor1的更新结果如图8-36所示。读者如果对UPDATE命令中的FROM子句理解起来比较困难，可以将FROM子句转换到相应的SELECT语句中，查看查询结果。例如，上面两个FROM子句转换到SELECT中的格式如下，查询结果如图8-37所示。从图中可以看出，上面的第一个UPDATE语句中实际上是使用MyUpdate1中的Field2_b字段值和Field2_b*10来分别更新MyCursor1的Field2和Field3字段。

SELECT * FROM MyCursor1, MyCursor2 ;

   WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 ;

   INTO CURSOR MyUpdate1

SELECT * FROM MyCursor1, MyCursor2, MyCursor3 ;

   WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND MyCursor2.field1=MyCursor3.field1 ;

   INTO CURSOR MyUpdate2
（3）相互关联的DELETE-SQL命令
Visual FoxPro 9.0现在支持使用DELETE-SQL进行相互关联删除。

如果一个FROM子句中包含有多个表，则DELETE后面的名称是必需的，并且被指定为删除操作的目标，该名称可以是一个表名称、别名或是一个文件名称。DELETE-SQL命令选择目标表的逻辑和FROM子句的语法限制与UPDATE-SQL命令相同，见前面的介绍。

下面是相互关联DELETE命令的语法格式：

DELETE [alias] FROM alias1 [, alias2 … ] … WHERE …

参考下列代码：

CREATE CURSOR MyCursor1 (field1 I , field2 I NULL,field3 I NULL)

INSERT INTO MyCursor1 VALUES (1,1,0)

INSERT INTO MyCursor1 VALUES (2,2,0)

INSERT INTO MyCursor1 VALUES (5,5,0)

INSERT INTO MyCursor1 VALUES (6,6,0)

INSERT INTO MyCursor1 VALUES (7,7,0)

INSERT INTO MyCursor1 VALUES (8,8,0)

INSERT INTO MyCursor1 VALUES (9,9,0)

CREATE CURSOR MyCursor2 (field1 I , field2 I)

INSERT INTO MyCursor2 VALUES (1,10)

INSERT INTO MyCursor2 VALUES (2,20)

INSERT INTO MyCursor2 VALUES (3,30)

INSERT INTO MyCursor2 VALUES (4,40)

INSERT INTO MyCursor2 VALUES (5,50)

INSERT INTO MyCursor2 VALUES (6,60)

INSERT INTO MyCursor2 VALUES (7,70)

INSERT INTO MyCursor2 VALUES (8,80)

CREATE CURSOR MyCursor3 (field1 I , field2 I)

INSERT INTO MyCursor3 VALUES (6,600)

INSERT INTO MyCursor3 VALUES (7,700)

DELETE MyCursor1 FROM MyCursor2 ;

WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1

SELECT MyCursor1

LIST OFF

RECALL ALL &&撤消删除

DELETE MyCursor1 FROM MyCursor2, MyCursor3 ;

WHERE MyCursor1.field1>5 AND MyCursor2.field1=MyCursor1.field1 AND MyCursor2.field1=MyCursor3.field1

SELECT MyCursor1

LIST OFF

RECALL ALL

DELETE FROM MyCursor1 WHERE MyCursor1.field1>5

SELECT MyCursor1

LIST OFF

RECALL ALL

DELETE MyCursor1 from MyCursor1 WHERE MyCursor1.field1>5

RECALL ALL IN MyCursor1

DELETE T1 ;

FROM MyCursor1 T1 JOIN MyCursor2 ON T1.field1>5 AND MyCursor2.field1=T1.field1, MyCursor3 ;

WHERE MyCursor2.field1=MyCursor3.field1

RECALL ALL IN MyCursor1

执行上述代码，几次删除的结果如图8-38所示（记录前的“*”表示该记录被添加了删除标记）。读者如果对DELETE命令中的FROM子句理解起来比较困难，也可以参考前面在UPDATE命令中介绍的方法，把FROM子句转换到SELECT中来获取查询结果，进行分析。