| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1747 人关注过本帖, 1 人收藏
标题:VFP 学习、开发漫谈 (25)
取消只看楼主 加入收藏
liuxingang28
Rank: 11Rank: 11Rank: 11Rank: 11
来 自:山东济南
等 级:贵宾
威 望:47
帖 子:657
专家分:2167
注 册:2014-2-7
结帖率:96.88%
收藏(1)
已结贴  问题点数:20 回复次数:4 
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 编辑 ]
搜索更多相关主题的帖子: 开发 数据库 影响 
2014-07-02 16:34
liuxingang28
Rank: 11Rank: 11Rank: 11Rank: 11
来 自:山东济南
等 级:贵宾
威 望:47
帖 子:657
专家分:2167
注 册:2014-2-7
收藏
得分:0 
回复 5 楼 taifu945
在写这篇短文时,我确实没有对 Varchar 类型的字段进行测试。因为我从来没有用过 Varchar 数据类型,对这种字段类型关注不够。对于 Varchar类型,开始时,我的理解是:它在存储时会过滤掉尾部空格,但经过测试,根本不是这么一回事。它既不会节约磁盘空间,在字符串比较时,又不能代替TRIM()函数,所以从未使用过。

泉城飞狐
2014-07-03 16:11
liuxingang28
Rank: 11Rank: 11Rank: 11Rank: 11
来 自:山东济南
等 级:贵宾
威 望:47
帖 子:657
专家分:2167
注 册:2014-2-7
收藏
得分:0 
对于Varchar字段类型,再探讨一下。
Varchar字段类型只是不添加满足字段长度的额外空格,若添加的字符串本身就带有尾部空格,则还是要存储的。
可以设想一下,如果要在字符串比较时不使用TRIM()函数,必须在存储时使用TRIM()函数对要保存的字符串进行处理。
如果是Char字段类型,在字符串比较时需使用TRIM()函数,但在存储时无需使用TRIM()函数。
由此可以看出:两种数据类型在字符串比较时所花费的精力相差无几,一种是在存储时过滤空格,一种是在比较时过滤空格。

泉城飞狐
2014-07-03 19:13
liuxingang28
Rank: 11Rank: 11Rank: 11Rank: 11
来 自:山东济南
等 级:贵宾
威 望:47
帖 子:657
专家分:2167
注 册:2014-2-7
收藏
得分:0 
我仔细拜读过您的《VFP入门指南》,我前后仔仔细细读过 3 篇,对其中关于Varchar数据类型的论述印象深刻。我在 9 楼表达的意思您可能理解上有偏差。我的意思,不是说系统在存储时会自动使用TRIM()函数过滤字符串,而是说,若要实现对字符串比较时不再使用TRIM()函数,对Varchar字段类型也必须人工使用TRIM()函数对字符串进行过滤。

泉城飞狐
2014-07-04 10:22
liuxingang28
Rank: 11Rank: 11Rank: 11Rank: 11
来 自:山东济南
等 级:贵宾
威 望:47
帖 子:657
专家分:2167
注 册:2014-2-7
收藏
得分:0 
比如,表单上有一个txtName文本框,用于输入或修改“姓名”字段,该字段是 Varchar类型,则在保存记录时,需执行 replace with trim(thisform.txtName.Value)。

泉城飞狐
2014-07-04 19:53
快速回复:VFP 学习、开发漫谈 (25)
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.018938 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved