VFP 学习、开发漫谈 （25）
这一期与大家聊一聊有关SQL 语句的使用问题。说实话，写这篇短文有点“战战兢兢”的感觉，因为 SQL 语句虽廖廖数条，但却“博大精深”，入门易，精通难。下面，结合我的实战经验，谈一谈学习体会。●. 数据库引擎的版本对 SQL 语句影响很大。在 VFP 9.0下，默认数据库引擎的版本为 90，即：Set EngineBehavior 90。还可以将数据库引擎设置为 80 或 70，以兼容 VFP 8.0或 VFP 7.0之前的版本（当 Set EngineBehavior 70 时，兼容 VFP7.0、VFP 6.0等）。
下面简单总结一下，9.0版本的数据库引擎与其他版本相比对SQL查询的影响：
1. 对 Like 比较符的影响
假设表 T1.dbf有一个字段 Fld1，包含“1”和“12”两条记录。在8.0和9.0版本下，SELECT * FROM t1 WHERE fld1 LIKE '1_'，返回字段值为“1”和“12”两条记录，而在 7.0版本下，SELECT * FROM t1 WHERE fld1 LIKE '1_'，仅返回字段值为“12”的一条记录。也就是说，下划线用于 Like 模板字符的尾部时，在 8.0 和 9.0下，下划线代表的是空格或其他字符，而在 7.0下，下划线代表的是非空格字符。
2. 对 Top 语句的影响
在 9.0版本下，SELECT * TOP 1 FROM employee ORDER BY salary，仅列出员工表中工资最少的其中 1 名员工，也就是说，对于 Top n，返回的记录不会多于 n。但在 8.0 和 7.0 版本下，若拥有最低工资的员工有多名，则 SELECT * TOP 1 FROM employee ORDER BY salary 返回多名员工。
比如，现在我要查询工资最低的员工信息，在 VFP 9.0默认数据引擎下，不能使用 SELECT * TOP 1 FROM employee ORDER BY salary，而应该采用如下语句：SELECT * FROM employee WHERE salary = (SELECT MIN(salary) FROM employee)
3. 对Group By 子句的影响
在 7.0版本下，下列语句可以正常运行：SELECT 编号,姓名,SUM(工资) FROM 工资表 GROUP BY 编号
该命令的字段列表中，除 GROUP BY 中的字段“编号”和聚合函数“SUM(工资)”外，还可以出现其他字段“姓名”。但在 8.0 和 9.0版本下，该语句不能正常运行，必须修改如下：SELECT 编号,姓名,SUM(工资) FROM 工资表 GROUP BY 编号,姓名
也就是说，字段列表中除了 GROUP BY 中的字段和聚合函数外，不能出现其他字段。这也是很多用户碰到的问题：在VFP 6.0下可以正常运行的SQL语句，移植到VFP 9.0下时却不能正常运行。在VFP 9.0下，若不想对语句作任何修改，执行 SET EngineBehavior 70 即可。
●. 对于 SELECT 中的 TOP n，若 n 带小数位，则自动取整，即：舍去小数位，不是四舍五入。如：SELECT * TOP 1.9 FROM employee ORDER BY salary，仅显示1条记录（1.9→1）。
●. 对于 SELECT 中的 TOP n PERCENT，如：SELECT * TOP 10 PERCENT FROM employee ORDER BY salary，假设数据表共有 11 条记录，则 11 * 10% = 1.1，那么系统是显示 1 条记录呢，还是显示 2 条记录？答案是 2 条记录。也就是说，当记录数乘以百分数后的结果是小数时，则系统自动向上取整（不是四舍五入）。
●. 执行一个最简单的 SQL 语句：SELECT * FROM employee INTO CURSOR curTmp，然后我们分析一下运行结果，从中可以得出如下结论：
1. 该命令执行完毕，会在一个未使用的空闲工作区打开 curTmp，相当于 SELECT 0 / USE curTmp
2. 执行该命令时，不会影响原工作区及其他工作区表的打开状态及记录指针。
3. 此命令受 SET DELETED ON/OFF 的影响。若 SET DELETED ON，则忽略逻辑删除的记录；否则，逻辑删除的记录也参与查询，并在查询结果中仍保持逻辑删除状态。
4. 结果游标 curTmp 是只读的，除非在游标名后加上参数 ReadWrite。
●. 将上述SQL语句修改为：SELECT * FROM employee WHERE salary > 5000 INTO ARRAY aRet，这条命令将查询结果输出至数组。需要注意的是：若没有满足条件的记录，则不会创建数组 aRet，即：数组 aRet 不存在，因此，在后续处理中，应该先判断 _Tally 系统变量是否大于0，然后再对 aRet 数组进行处理。否则，可能因 aRet 不存在而出错。
程序代码：SELECT name FROM employee WHERE salary>5000 INTO ARRAY aRet
IF _Tally > 0
FOR EACH oEmp IN aRet
? oEmp
NEXT
ENDIF关于将查询结果输出至临时表还是输出至数组，小结如下：
1. 若查询结果包含很多行，则应该选择输出至临时表，因为数组元素的个数是有限制的，最大为 65000，而临时表的记录数无此限制，仅与内存和硬盘的空间大小有关。
2. 若查询结果包含的行不多，且不对查询结果作进一步的处理，则输出至数组可能更方便一些，因为输出至临时表会改变当前工作区，还要随时关闭临时表，而对于数组来说，一般其作用域是局部的或私有的，在当前过程结束时，会自动释放，且不会影响工作区及记录指针。但是，输出至临时表，对于查询结果的检索和进一步处理，可能更方便一些，可以使用我们熟悉的 Replace、Locate 等表处理命令和函数。
●. 使用 SELECT 语句可以快速建立数据表结构。比如，建立一个与 employee.dbf 结构相同的临时表 curEmp，则可以运行 SELECT * FROM employee INTO CURSOR curEmp READWRITE WHERE .f.。注意 WHERE 条件中的 .f.，以及临时表后面的 READWRITE。本语句可以部分代替 Create Cursor/Create Table/Copy Structure 命令。
●. 当 Select 中的字段是表达式时，最好通过 CAST()函数为表达式指定数据类型和长度，否则可能因字段长度不够造成数据溢出或数据丢失。
大家先随我做一个试验：
先运行 SELECT 0,* FROM employee，然后再执行 MODIFY STRUCTURE 观察一下数据表的结构，可以看出第一个字段 Exp_1 的长度为 1，也就是说，最大只能保存数值 9，超过 9 时数据会溢出，即：显示一串星号。
现在，我们将上述语句改为 SELECT 0000,* FROM employee，运行后再观察一下表结构，你会发现第一个字段的数据类型为 Numric，长度变成了 4。
以下举一个初学者经常犯的错误：
程序代码：*建立一个临时表t1，第一条记录的数量和单价为0
CREATE CURSOR t1 (编号 c(10),数量 i,金额 i)
INSERT INTO t1 VALUES ('1',0,0)
INSERT INTO t1 VALUES ('2',10,200)
*查询各商品的单价，可以发现第二条记录单价溢出。
SELECT 编号,IIF(数量=0,0,ROUND(金额/数量,2)) FROM t1下面再举一个字符串函数的例子：SELECT IIF(EMPTY(班组),部门,TRIM(部门)+'-'+ALLT(班组)) FROM employee
上述命令是想把“部门”和“班组”组合成一个字段，二者之间加分隔线，然后输出。而运行后可以发现，因查询结果中的字段长度不足，字段内容显示不全，部分数据丢失。
可以将上述命令修改为：SELECT PADR(IIF(EMPTY(班组),部门,TRIM(部门)+'-'+ALLT(班组)),50) FROM employee
这种在数值前添加 0 或在字符后面添加空格的做法，虽然可以勉强解决问题，但总之不是正规的处理方法。正规的处理方法是，通过 CAST()函数来重定义表达式。上面列举的三条命令可修改如下：
SELECT CAST(0 AS I),* FROM employee
SELECT 编号,CAST(IIF(数量=0,0,ROUND(金额/数量,2)) AS N(12,2)) FROM t1
SELECT CAST(IIF(EMPTY(班组),部门,TRIM(部门)+'-'+ALLT(班组)) AS C(50)) FROM employee
●. SELECT SQL 语句会使用当前数据环境中的 SET DELETE ON/OFF 设置，但表的 SET FILTER 及 SET ORDER 等设置不影响 SQL 语句的执行。这在判断记录是否重复时十分有用。
比如，在输入发票时，判断当前输入的发票号是否已存在，可使用如下代码：
程序代码：LOCAL cId,aRet[1]
cId = PADR(LTRIM(THISFORM.txt发票号.Value),15)
SELECT * FROM invoice WHERE id = m.cId INTO ARRAY aRet
IF _Tally > 0
MESSAGEBOX(‘发票号(‘+TRIM(m.cId)+’)已经存在！’,48,’提示’)
RETURN
ENDIF使用 SELECT 语句的好处是：不用考虑表的过滤条件和索引的影响，同时又不影响表的记录指针。若使用 Locate/Seek 则要麻烦的多，需要先解除表的过滤条件，再按发票号搜索，最后还要恢复环境。当然，若要完全杜绝发票号重复，必须结合“主索引/候选索引”以及表的缓冲技术来处理。
●. 我们知道，在默认的 SET EXACT OFF 状态下，? "ABCD"="AB" 的执行结果是 .t.，那么在 WHERE 表达式中，这种“模糊”比较方式是否还有效呢？必须提醒你的是：SET EXACT ON/OFF 对 SQL 语句无效，影响SQL语句比较方式的是 SET ANSI ON/OFF。SET ANSI ON 相当于 SET EXACT ON，SET ANSI OFF 相当于 SET EXACT OFF，默认状态下是 SET ANSI OFF。
●. 对于 SELECT 语句的 INTO CURSOR 子句，其后面可以加 NoFilter 选项。VFP 帮助中的解释是：若临时表用于后边的查询，需要加 NoFilter 选项。但是，在我开发的多个应用系统中，从来没有在临时表后面添加过此选项。实践证明，NoFilter 仅是为了兼容老版本（VFP5.0 或 3.0）的 VFP 才设置的。对于 VFP 9.0来说，不用添加该选项。例如：
SELECT * FROM employee WHERE salary > 5000 INTO CURSOR curTmp SELECT dep,SUM(salary) FROM curTmp GROUP BY dep
上述代码中的第二个 SELECT 语句是对第一个 SELECT 语句生成的临时表 curTmp 做进一步处理，但在第一个语句的临时表后面没有添加 NoFilter 选项，程序也可以正常运行。
●. 在 SELECT 语句中使用子查询时，使用 IN (子查询) 的情况最多，但也可以使用其他比较符。例如：显示工资超过平均工资 50% 的员工记录：
SELECT name,salary FROM employee WHERE salary>(SELECT AVG(salary)*1.5 FROM employee)
●. 为了简化命令的书写，在对多个表进行连接时，可使用表别名。需要注意的是：表别名仅对当前SQL语句有效，且仅用于当前 SQL 语句内部，系统并不会以该别名在另一个工作区打开表。例如：
SELECT a.编号,a.姓名,a.部门,b.工资 ;
FROM 员工表 a,工资表 b ;
WHERE a.编号 = b.编号
从上述代码可以看出，使用别名后，代码更为简洁、清晰，可读性更好。执行该语句后，大家可以验证一下，看看系统是不是存在别名为a或b的工作区。
●. 使用 ALTER TABLE 修改表结构时，可以一次性修改多个字段，如：下面这条语句，可为 employee.dbf 新增字段 EmpID I 和 Notes M，删除字段 Sex，将 Salary 修改为 N(10,2)，将 Dep 改名为 Department：
ALTER TABLE employee ADD empid I ADD notes m DROP sex ALTER salary n(10,2) RENAME dep TO department
●. 在使用 Create Cursor 或 Create Table 建立表时，对于整型字段我们一般都使用“I”来定义，如：CREATE CURSOR table1 (fld1 i)，此时 fld1 的最大值可达 21 亿，在表格中，此字段的默认显示宽度为 70。如果已经预见到该值不会那么大，如：销售订单中的变压器台数，不会超过 1 万台，不妨将其定义为 N(4,0)，甚至可简写为 N(4)，即：CREATE CURSOR table1 (fld1 N(4))，则在表格中该列的默认的显示宽度大大缩小，约为 37。
●. 使用 UPDATE 语句更新单个表中的字段时，可以省略 FROM 子句，如：对于工资不足 3000 元的员工，工资上调10%，可输入如下语句：
UPDATE employee SET salary = salary*1.1 WHERE salary < 3000
注意：无需在字段 salary 前加表名 employee
假设有一个表 emp_old.dbf，其表结构与 employee.dbf 完全相同，现在想用 emp_old.dbf 的数据更新 employee.dbf 表中的 salary 字段，可用如下命令：
UPDATE employee SET salary = emp_old.salary FROM emp_old WHERE employee.empid = emp_old.empid
分析以上语句，可以看出：
1. 对于要更新的字段 salary，无需在字段名前加表名，即使两个表存在同名字段。
2. 用一个表的记录去更新另一个表的记录时，必须使用 FROM 选项，且只需在 FROM 中列出来源表，如：本例中的 emp_old。
3. 在 WHERE 中列出两个表的连接条件，且必须在字段名前加表名。
●. 对于左连接（Left Join）来说，字段列表中出现的右表中的字段，有可能是空值（右表中没有与左表匹配的记录），因此应使用 NVL()函数来消除空值的影响。比如：Vendor.dbf 保存的是所有供应商的清单，Order.dbf 保存的是每个供应商的采购金额汇总，下面这条语句可查询每个供应商的采购金额：
SELECT vendor.*,NVL(SUM(order.amount),00000000.00) ;
FROM vendor LEFT JOIN order ON vendor.veid = order.veid
若不使用 NVL()，则对于没有发生业务的供应商，在查询结果的金额字段中将出现空值 .NULL.。空值的显示问题很好解决，只要执行 SET NullDisplay To ""，即可使空值不显示。但是，若要对查询结果作进一步处理，则空值与普通的 DBF表的字段值有很大不同。因为空值与任何值的操作其结果都是空值。更严重的是，默认情况下，我们建立的表或游标，都不支持空值。若将字段值替换为空值，则会报错。所以，应尽可能使用 NVL()函数对空值转换一下。
●. 一般情况下，SELECT SQL 不包含本地缓冲区中的记录。若要统计缓冲区的记录，需在表名后添加 WITH (BUFFERING =.t.)选项。比如：对订单明细表设定了开放式表缓冲，在提交保存之前，需统计一下订单金额，可执行如下命令：
SELECT SUM(金额) FROM 订单明细 WITH (BUFFERING =.t.) WHERE 订单id = 订单表.id INTO ARRAY aRet
●. SQL 中的子查询不仅可以出现在 WHERE 条件中，也可以直接出现在 FROM 子句中，如：
SELECT a.供应商id,a.供应商名称,b.订单金额 ;
FROM vendor AS a,(SELECT 供应商id,SUM(金额) as 订单金额 FROM order GROUP BY 供应商id) AS b ;
WHERE a.veid = b.veid
注意：对于 FROM 子句中的子查询，必须给出别名。
[ 本帖最后由 liuxingang28 于 2014-7-3 08:40 编辑 ]
