第2章据查询与统计

1、上一次课讲到创建数据库、使用命令显示与操作记录、创建与使用可视类、使用表格显示与操作记录、使用treeview控件显示记录、表单之间传递数据。请同学们回顾这样两个问题：1. 数据库表结构中项目组成？2.如何添加、编辑自定义方法？1.字段名称、类型、宽度、小数位数、索引方式；2.字段格式、输入掩码、标题；3.字段有效性规则；4.字段注释。添加方法：菜单：表单/新建属性/输入上面的属性名/添加建立以后，可以通过“菜单：表单/编辑属性/方法程序”进行编辑。对表单对象新建方法程序内容的编辑与对象事件过程编辑相同。上一次课主要学习的内容本章主要介绍vfp中的数据查询命令，sql查询语句，在表单中定位记录

2、，过滤记录以及数据统计。第第2 2章章. .数据查询与统计数据查询与统计vfpvfp中数据查询命令分类中数据查询命令分类：vfp中数据查询命令可以分为顺序查询、索引查询和过滤记录3种。2.12.1数据查询命令数据查询命令格式:locate for 格式:continue功能: 定位到第一个符合条件的记录位置, continue定位下一个符合条件的记录 注意：该命令只能在当前工作区查询。2.1.12.1.1顺序查询顺序查询在下列示例中，定位德国顾客的记录，然后显示总数。close databasesopen database (home(2) + datatestdata)use custome

3、r & 打开 customer 表set talk offstore 0 to gncountlocate for alltrim(upper(customer.country) = germany do while found() gncount = gncount + 1 ? company continueenddo? 总的德国客户数: + ltrim(str(gncount).locate.locate例例 命令格式命令格式：seek 功能功能：seek 在一个表中搜索首次出现的一个记录，这个记录的索引关键字必须与指定的表达式匹配。2.1.22.1.2索引查询索引查询完整的命令

4、格式完整的命令格式：seek eexpression order nindexnumber | idxindexfilename | tag tagname of cdxfilename ascending | descending in nworkarea | ctablealias说明说明：只能在索引过的表中使用 seek 命令，并且只能搜索索引关键字。除非 set exact 的设置为 off ，否则匹配指的是完全匹配。如果 seek 找到了与索引关键字相匹配的记录，则 recno( ) 返回匹配记录的记录号；found( ) 返回“真”(.t.)；eof( ) 返回“假”(.f.)。如

5、果找不到相匹配的关键字，则 recno( ) 将表中记录的个数加 1，然后返回；found( ) 返回“假”(.f.)；eof( ) 返回“真”(.t.)。如果 set near 设置为 on ，则记录指针指向与索引关键字最相匹配的那个记录的后面一个记录。如果 set near 设置为 off，则记录指针指向文件的结尾。在这两种情况下，recno(0) 都返回与关键字最匹配的记录号。close databasesopen database (home(2) + datatestdata)use customer order company & 打开 customer 表set exac

6、t offstore b to gcseekvalseek gcseekvalif found() display fields company, contactendif示例中，先打开 customer 表，并按 company 字段建立索引。然后，用 seek 命令查找与内存变量 gcseekval 的值相匹配的索引关键字表达式。.seek.seek例例 vfp记录过滤是从逻辑上过滤掉不符合条件的记录，不真正删除。记录过滤可以将不需要的记录屏蔽掉。命令格式命令格式：set filter to 表达式 in 区号 | 别名功能功能：指定访问当前表中记录时必须满足的条件。 表达式表达式 -指定

7、记录必须满足的条件。 in in 区号区号 | | 别名别名 指定受 set filter 命令影响的工作区或表别名。用该子句来指定一个当前工作区以外的工作区或表。关闭过滤功能命令关闭过滤功能命令：set filter to说明说明：该命令对select-sql不起作用。函数filter(in 区号 | 别名)返回 set filter 命令中指定的表筛选表达式。2.1.32.1.3记录过滤记录过滤close databasesopen database (home(2) + datatestdata)use customer & 打开 customer 表set talk onset

8、 filter to substr(cust_id,1) = bclear? filter() & 显示 filter expressionstore filter(customer) to gcoldfilter & save filter expressionset filter to country = usa? filter() & 显示 filter expressionset filter to &gcoldfilter & restore filter expression? filter() & 显示 filter express

9、ionlist fields cust_id, contact & demonstrate filter condition.set filter.set filter例例vfp支持sql语句，可以从一个或多个表中检索数据。select sql 命令是与其它 vfp 一样的内置的 vfp 命令。当你使用 select 来生成查询时, vfp翻译查询并从表中获取指定数据。你可以从以下地方创建 select 查询: “命令”窗口中 带有其它任何 vfp 命令的 vfp 程序中 查询设计器中 2.22.2查询操作查询操作 selectselectall|distinctall|distinc

10、t & all默认设置,结果包含所有行 | 剔除重复的行。top top percent percent&符合条件记录中，取指定数量或百分比的记录。该子句要求先order by。包含 percent 关键字时，取值范围0.01到99.99 as as , as as &限定匹配项的名称, 检索项应该是from 子句所包含的表中的字段名称 或 一个常量，查询结果中每一行都出现这个常量值 或 一个表达式，可以是用户自定义函数名。指定各项均生成一列。如果多项同名, 在项名前包含表别名和一个句点来避免列重复。” .select-sql.select-sql格式格式-1/6-1/

11、6“as ”为查询输出中的列指定显示名, 下列字段函数可以与选定项一起使用，选定项可以是一个字段或包含字段的表达式: avg(检索项检索项), 计算列中数值的平均值。 count(检索项检索项), 计算列中选定项的数目。计算查询输出的行数。count(*) 计算查询输出中的行数。 min(检索项检索项), 确定列中检索项的最小值。 max(检索项检索项), 确定列中检索项的最大值。 sum(检索项检索项), 计算列中数值的和。.select-sql.select-sql格式格式-2/6-2/6from from ! as as&列出所有从中检索数据的表。as ,为表指定一个临时名称。i

12、nto into |to file|to file|to |to printer|to screenprinter|to screen& 指定在何处保存查询结果。:array 数组名数组名 | cursor 临时表名临时表名 | dbf/table 表名表名 缺省输出到浏览窗口。.select-sql.select-sql格式格式-3/6-3/6where where and and and|or and|or and|or and|or & 指定查询条件/表间联接条件,可以是关系表达式/逻辑 表达式,也可以是:fieldname not between start_range

13、 and end_rangefieldname not between start_range and end_range检查是否字段值在指定的范围内。fieldname not like cexpressionfieldname not like cexpression搜索匹配 cexpressioncexpression 的各字段。可以用百分号 (%)(%) 和下划线 (_)(_) 通配符作为 cexpressioncexpression 的一部分。百分号代表串中任何长度的不知道的字符。下划线代表串中的单个的不知道的字符。.select-sql.select-sql格式格式-4/6-4/6

14、fieldname not in (subquery)fieldname not in (subquery)在记录出现在查询结果中之前字段必须包含一个由子查询返回的值。group by group by , , having having &指定分组输出及输出条件havinghaving应出现在intointo前。.select-sql.select-sql格式格式-5/6-5/6union all selectunion all select语句语句 &把一个 selectselect 语句的最后查询结果同另一个 selectselect 语句最后查询结果组合起来。默认情况下

15、，unionunion 检查组合的结果并排除重复的行。要组合多个unionunion 子句，可使用括号。可以用 unionunion 子句模拟一个外部联接。allall 防止 unionunion 删除组合结果中重复的行。order by order by asc|desc,order asc|desc,order by by asc|desc asc|desc&查询结果排序输出.select-sql.select-sql格式格式-6/6-6/6显示customer 表中所有的公司名称(来自一个表的一个字段)。close allclose databasesopen database

16、(home(2) + datatestdata)select pany ; from customer.select-sql.select-sql示例示例1 1显示两个表中的三个字段的内容并根据 cust_id 字段连接表。它使用两个表的本地别名。close allclose databasesopen database (home(2) + datatestdata)select pany, y.order_date, y.shipped_on ; from customer x ; inner join orders y on x.cust_id = y.cust_id.select-sq

17、l.select-sql示例示例2 2显示在指定字段只有唯一数据的记录。close allclose databasesopen database (home(2) + datatestdata)select distinct pany, y.order_date,; y.shipped_on ; from customer x, orders y ; where x.cust_id = y.cust_id.select-sql.select-sql示例示例3 3以升序次序显示 country、postalcode 和 company 字段。close all close databases

18、open database (home(2) + datatestdata) selectselect country, postalcode, company ; fromfrom customer ; order byorder by country, postalcode, company.select-sql.select-sql示例示例4 4将来自两个表的字段内容存储在第三个表中。close allclose databasesopen database (home(2) + datatestdata)select pany, y.order_date, y.shipped_on ;

19、 from customer x, orders y ; where x.cust_id = y.cust_id ; into table custship.dbfbrowse.select-sql.select-sql示例示例5 5显示订单 (order) 日期早于 2007 年 9 月 16 日的记录。close all close databases open database (home(2) + datatestdata) selectselect pany, y.order_date, y.shipped_on ; from from customer x, orders y ;

20、where where x.cust_id = y.cust_id ; and and y.order_date 2007-09-16.select-sql.select-sql示例示例6 6显示订货日期早于 09/16/2007 的所有客户。close allclose databasesopen database (home(2) + datatestdata)select pany, y.order_date, y.shipped_on ; from customer x ; left join orders y ; on x.cust_id = y.cust_id ; and y.or

21、der_date 2007-09-16.select-sql.select-sql示例示例7 7显示 customer 表中邮政编码与 orders 表的邮政编码相匹配的所有公司名。close allclose databasesopen database (home(2) + datatestdata)selectselect company fromfrom customer x wherewhere ; existsexists ; (selectselect * fromfrom orders y ; wherewhere x.postalcode = y.postalcode).se

22、lect-sql.select-sql示例示例8 8在vfp中，可以使用loacte命令查找表中任意字段（备注型和通用型除外）并进行定位。 下面以“输入查询条件”为例介绍vfp如何查找并定位记录。2.32.3定位记录定位记录在项目管理器中建立“输入查询条件”表单,表单名称为:trainprjser，在表单中添加2个标签、1个组合框、1个文本框、2个mycmd类对象。该表单被用于其他表单中,所以不需要单独定义数据环境.2.3.12.3.1创建表单创建表单autocenter=.t.borderstyle=2-固定对话框caption=输入查询条件closable=.f.height=90widt

23、h=250icon=people.icomdiform=.t.maxbutton=.f.minbutton=.f.showtips=.t.showwindow=1-在顶层表单中windowtype=1-模式 name=trainprjser组合框findtype.rowsource=项目编号,项目主题,培训人rowsourcetype=1-值style=2-下拉列表框文本框findkey.fontsize=12height=25按钮cmdok.caption=确定default=.t.tooltiptext=开始查找定位按钮cmdccl. caption=取消cancel=.t.tooltip

24、text=取消查找. .表单及控件属性表单及控件属性*声明本地变量local findstr, oldrecord*保存记录指针位置oldrecord = recno()*模糊比较set exact off*根据查询类型生成查询条件do case *以项目编号查询 case thisform.findtype.value = 1 findstr=+alltrim(thisform.findkey.value)+$ 项目编号2.3.22.3.2添加代码添加代码确定钮确定钮cmdok.click-1/5cmdok.click-1/5 case thisform.findtype.value = 2

25、findstr=+alltrim(thisform.findkey.value)+$ 培训主题*以培训人查询 case thisform.findtype.value = 3 findstr=+alltrim(thisform.findkey.value)+$ 培训人endcase*进行查询select trainprj*过滤记录set filter to &findstrgo topcmdok.click-2/5cmdok.click-2/5*循环询问用户do while .not. eof() *更新主表单画面 trainprj.refresh *将本表单最小化 thisform.

26、windowstate=1 *询问用户 if messagebox(是本笔记录吗,4+32,人力资源管理系统) = 6 *退出循环exitcmdok.click-3/5cmdok.click-3/5*如果用户选择“否” else *下移记录指针并刷新表单skiptrainprj.refresh endif *将本表单还原 thisform.windowstate = 0enddocmdok.click-4/5cmdok.click-4/5*到了表的末尾并且未找到记录if eof()messagebox(未找到指定的记录, 16, 人力资源管理系统)*回到原记录go oldrecordendif

27、*清除过滤条件set filter totrainprj.refreshthisform.releasecmdok.click-5/5cmdok.click-5/5thisform.release取消钮取消钮cmdccl.clickcmdccl.clickvfp提供了简单的统计命令，也可以根据统计创建图表。2.42.4数据统计数据统计vfp提供了5个统计命令：count、sum、average、calculate、total。2.4.12.4.1数据统计命令数据统计命令命令格式命令格式：count for/while to 功能功能：统计表中记录数目。说明说明：count 在 for 或 wh

28、ile 条件为“真”时，对一定范围内的记录进行计数。如果 set talk 是 on，则显示记录的数目。如果 set delete 是 off，则带有删除标记的记录也包括在计数中。1.1.计数命令计数命令下面的示例统计并显示在巴黎的顾客数目。close databasesopen database (home(2) + datatestdata)use customer & 打开 customer 表clearcount for upper(city) = parisdisplay fields company, contact for upper(city) = paris.coun

29、t.count示例示例格式格式：sum 表达式列表 范围 for/ while 条件 to 内存变量表 | to array 数组名功能功能：对当前选定表的指定数值字段或全部数值字段进行求和。2.2.求和命令求和命令下面的示例显示 products 表中的 in_stock 字段和 on_order 字段的总计，以及这两个总计的和。close databasesopen database (home(2) + datatestdata)use products & 打开 products 表sum in_stock, on_order, in_stock+on_order ; to g

30、ninstock, gnonorder, gnunitsclear? 库存总数 : , gninstock & 显示 3119.00? 订货总数 : , gnonorder & 显示 780.00? 总单位 : , gnunits & 显示 3899.00.sum.sum示例示例 格式格式：average 表达式列表 范围 for/while 条件 to 变量列表 | to array 数组名功能功能：计算数值表达式或字段的算术平均值。说明说明：除非包含可选的表达式列表，否则选定表的所有字段都将参与求平均值的运算。如果 set talk 为 on，结果显示在屏幕上。如果

31、 set headings 为 on，字段名或包括字段名的表达式将显示在结果的上面。3.3.求平均值求平均值close databasesopen database (home(2) + datatestdata)use orders & 打开 order 表clearaverage order_amt & 计算所有订单的平均值average order_amt to gnavg & 保存平均值到内存变量? 平均订货数: ? gnavg & 再次显示平均值.average.average示例示例格式格式:total on to for/while fields

32、功能功能:计算当前选定表中数值字段的总和。4.4.分组汇总命令分组汇总命令要使用此命令，当前工作区中的表必须经过排序或索引。对于具有相同字段值或索引关键字值的各组记录，将分别计算其总计值。总计结果放入另一个表的记录中，同时在此表中还将对这些字段值或索引关键字值创建一条记录。如果第二个表中数值字段的宽度不足以放置总计值，将会发生数值溢出错误。当发生数值溢出错误时，visual foxpro 保存总计值最主要的部分: *小数位被截断，即对总计值余下小数位进行圆整。 *如果总计值仍然不能放下，如包含七位以上的数字，这时将采用科学计数法表示。 *最后，用星号代替字段的内容。.total.total命令

33、使用说明命令使用说明academycount视图对员工信息表中各学历人数进行了分组统计，其统计结果是以表的形式显示的，在vfp中可以使用microsoft graph将表形式的统计结果变为图表形式显示。microsoft graph包含2个对象，数据表dadasheet和图表chart，chart结果基于dadasheet。 以下介绍以图表形式显示员工信息表中各学历人数统计结果。2.4.22.4.2创建统计图表创建统计图表新建1个表，命名为chart,该表中包含1个通用型字段。. .创建表创建表新建1个表单：chartgraph，将表chart添加到数据环境中。在表单中添加1个active绑定

34、控件：chartgraph调整其大小，使其能显示整个图表。. .创建表单创建表单*回车换行符#define crlf chr(13) + chr(10)*tab键#define tab chr(9)*运行查询do agstat.qpr. .表单表单initinit代码代码-1/6-1/6local lcdata*读取学历名称作为横坐标lcdata = + tab + 男 + tab + 女 + crlfselect agstatgo topscan lcdata = lcdata + alltrim(学历) + tab +; alltrim(str(男) + tab + alltrim(str(女) + crlfendscan. .表单表单initinit代码代码-2/6-2/6*将字符串生成的chart对象保存通用型字段中if !used(chart)use data/chartendifselect chartappend general 学历统计 data lcdata clas