数据查询与统计.ppt

上一次课讲到创建数据库、使用命令显示与操作记录、创建与使用可视类、使用表格显示与操作记录、使用TreeView控件显示记录、表单之间传递数据。请同学们回顾这样两个问题：,数据库表结构中项目组成？,2.如何添加、编辑自定义方法？,1.字段名称、类型、宽度、小数位数、索引方式； 2.字段格式、输入掩码、标题； 3.字段有效性规则； 4.字段注释。,添加方法：菜单：表单/新建属性/输入上面的属性名/添加 建立以后，可以通过“菜单：表单/编辑属性/方法程序”进行编辑。 对表单对象新建方法程序内容的编辑与对象事件过程编辑相同。,上一次课主要学习的内容,本章主要介绍VFP中的数据查询命令，SQL查询语句，在表单中定位记录，过滤记录以及数据统计。,第2章.数据查询与统计,VFP中数据查询命令分类： VFP中数据查询命令可以分为顺序查询、索引查询和过滤记录3种。,2.1数据查询命令,格式: LOCATE FOR 格式: CONTINUE 功能: 定位到第一个符合条件的记录位置, CONTINUE定位下一个符合条件的记录 注意：该命令只能在当前工作区查询。,2.1.1顺序查询,在下列示例中，定位德国顾客的记录，然后显示总数。 CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE customer & 打开 customer 表 SET TALK OFF STORE 0 TO gnCount LOCATE FOR ALLTRIM(UPPER(customer.country) = GERMANY DO WHILE FOUND() gnCount = gnCount + 1 ? company CONTINUE ENDDO ? 总的德国客户数: + LTRIM(STR(gnCount),.Locate例,命令格式：SEEK 功能：SEEK 在一个表中搜索首次出现的一个记录，这个记录的索引关键字必须与指定的表达式匹配。,2.1.2索引查询,完整的命令格式：SEEK eExpression ORDER nIndexNumber | IDXIndexFileName | TAG TagName OF CDXFileName ASCENDING | DESCENDING IN nWorkArea | cTableAlias 说明：只能在索引过的表中使用 seek 命令，并且只能搜索索引关键字。除非 SET EXACT 的设置为 OFF ，否则匹配指的是完全匹配。 如果 SEEK 找到了与索引关键字相匹配的记录，则 RECNO( ) 返回匹配记录的记录号；FOUND( ) 返回“真”(.T.)；EOF( ) 返回“假”(.F.)。 如果找不到相匹配的关键字，则 RECNO( ) 将表中记录的个数加 1，然后返回；FOUND( ) 返回“假”(.F.)；EOF( ) 返回“真”(.T.)。 如果 SET NEAR 设置为 ON ，则记录指针指向与索引关键字最相匹配的那个记录的后面一个记录。如果 SET NEAR 设置为 OFF，则记录指针指向文件的结尾。在这两种情况下，RECNO(0) 都返回与关键字最匹配的记录号。,CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE customer ORDER company & 打开 Customer 表 SET EXACT OFF STORE B TO gcSeekVal SEEK gcSeekVal IF FOUND() DISPLAY FIELDS company, contact ENDIF 示例中，先打开 customer 表，并按 company 字段建立索引。然后，用 seek 命令查找与内存变量 gcseekval 的值相匹配的索引关键字表达式。,.Seek例,2.1.3记录过滤,CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE customer & 打开 Customer 表 SET TALK ON SET FILTER TO SUBSTR(cust_id,1) = B CLEAR ? FILTER() & 显示 filter expression STORE FILTER(customer) TO gcOldFilter & Save filter expression SET FILTER TO country = USA ? FILTER() & 显示 filter expression SET FILTER TO &gcOldFilter & Restore filter expression ? FILTER() & 显示 filter expression LIST FIELDS cust_id, contact & Demonstrate filter condition,.Set Filter例,VFP支持SQL语句，可以从一个或多个表中检索数据。SELECT SQL 命令是与其它 VFP 一样的内置的 VFP 命令。当你使用 SELECT 来生成查询时, VFP翻译查询并从表中获取指定数据。你可以从以下地方创建 SELECT 查询: “命令”窗口中 带有其它任何 VFP 命令的 VFP 程序中 查询设计器中,2.2查询操作,SELECT ALL|DISTINCT & ALL默认设置,结果包含所有行 | 剔除重复的行。 TOP PERCENT&符合条件记录中，取指定数量或百分比的记录。该子句要求先ORDER BY。包含 PERCENT 关键字时，取值范围0.01到99.99 AS , AS &限定匹配项的名称, 检索项应该是FROM 子句所包含的表中的字段名称 或 一个常量，查询结果中每一行都出现这个常量值 或 一个表达式，可以是用户自定义函数名。指定各项均生成一列。如果多项同名, 在项名前包含表别名和一个句点来避免列重复。”,.Select-SQL格式-1/6,“AS ”为查询输出中的列指定显示名, 下列字段函数可以与选定项一起使用，选定项可以是一个字段或包含字段的表达式: AVG(检索项), 计算列中数值的平均值。 COUNT(检索项), 计算列中选定项的数目。计算查询输出的行数。COUNT(*) 计算查询输出中的行数。 MIN(检索项), 确定列中检索项的最小值。 MAX(检索项), 确定列中检索项的最大值。 SUM(检索项), 计算列中数值的和。,.Select-SQL格式-2/6,FROM ! AS&列出所有从中检索数据的表。AS ,为表指定一个临时名称。 INTO |TO FILE|TO PRINTER|TO SCREEN& 指定在何处保存查询结果。:ARRAY 数组名 | CURSOR 临时表名 | DBF/TABLE 表名 缺省输出到浏览窗口。,.Select-SQL格式-3/6,WHERE AND AND|OR AND|OR & 指定查询条件/表间联接条件,可以是关系表达式/逻辑 表达式,也可以是: FieldName NOT BETWEEN Start_Range AND End_Range 检查是否字段值在指定的范围内。 FieldName NOT LIKE cExpression 搜索匹配 cExpression 的各字段。可以用百分号 (%) 和下划线 (_) 通配符作为 cExpression 的一部分。百分号代表串中任何长度的不知道的字符。下划线代表串中的单个的不知道的字符。,.Select-SQL格式-4/6,FieldName NOT IN (Subquery) 在记录出现在查询结果中之前字段必须包含一个由子查询返回的值。 GROUP BY ,HAVING &指定分组输出及输出条件HAVING应出现在INTO前。,.Select-SQL格式-5/6,UNION ALL SELECT语句 &把一个 SELECT 语句的最后查询结果同另一个 SELECT 语句最后查询结果组合起来。默认情况下，UNION 检查组合的结果并排除重复的行。要组合多个UNION 子句，可使用括号。可以用 UNION 子句模拟一个外部联接。ALL 防止 UNION 删除组合结果中重复的行。 ORDER BY ASC|DESC,ORDER BY ASC|DESC&查询结果排序输出,.Select-SQL格式-6/6,显示customer 表中所有的公司名称(来自一个表的一个字段)。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT pany ; FROM customer,.Select-SQL示例1,显示两个表中的三个字段的内容并根据 cust_id 字段连接表。它使用两个表的本地别名。 CLOSE ALL CLOSE DATABASES OPEN 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-SQL示例2,显示在指定字段只有唯一数据的记录。 CLOSE ALL CLOSE DATABASES OPEN 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示例3,以升序次序显示 country、postalcode 和 company 字段。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT country, postalcode, company ; FROM customer ; Order By country, postalcode, company,.Select-SQL示例4,将来自两个表的字段内容存储在第三个表中。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT pany, y.order_date, y.shipped_on ; FROM customer x, orders y ; WHERE x.cust_id = y.cust_id ; INTO TABLE custship.dbf BROWSE,.Select-SQL示例5,显示订单 (order) 日期早于 2007 年 9 月 16 日的记录。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT pany, y.order_date, y.shipped_on ; FROM customer x, orders y ; WHERE x.cust_id = y.cust_id ; AND y.order_date 2007-09-16,.Select-SQL示例6,显示订货日期早于 09/16/2007 的所有客户。 CLOSE ALL CLOSE DATABASES OPEN 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.order_date 2007-09-16,.Select-SQL示例7,显示 customer 表中邮政编码与 orders 表的邮政编码相匹配的所有公司名。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT company FROM customer x WHERE ; EXISTS ; (SELECT * FROM orders y ; WHERE x.postalcode = y.postalcode),.Select-SQL示例8,在VFP中，可以使用Loacte命令查找表中任意字段（备注型和通用型除外）并进行定位。 下面以“输入查询条件”为例介绍VFP如何查找并定位记录。,2.3定位记录,在项目管理器中建立“输入查询条件”表单,表单名称为:trainprjser，在表单中添加2个标签、1个组合框、1个文本框、2个MyCmd类对象。该表单被用于其他表单中,所以不需要单独定义数据环境.,2.3.1创建表单,AutoCenter=.T. BorderStyle=2-固定对话框 Caption=“输入查询条件“ Closable=.F. Height=90 Width=250 Icon=people.ico MDIForm=.T. MaxButton=.F. MinButton=.F. ShowTips=.T. ShowWindow=1-在顶层表单中 WindowType=1-模式 Name=“TrainPrjSer“ 组合框FindType.RowSource=“项目编号,项目主题,培训人“ RowSourceType=1-值 Style=2-下拉列表框 文本框FindKey.FontSize=12 Height=25 按钮CmdOk.Caption=“确定“ Default=.T. ToolTipText=“开始查找定位“ 按钮CmdCcl. Caption=“取消“ Cancel=.T. ToolTipText=“取消查找“,.表单及控件属性,*声明本地变量 LOCAL FindStr, oldRecord *保存记录指针位置 oldRecord = RECNO() *模糊比较 SET EXACT OFF *根据查询类型生成查询条件 DO CASE *以项目编号查询 CASE THISFORM.FindType.Value = 1 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 项目编号“,2.3.2添加代码 确定钮 CmdOk.Click-1/5,CASE THISFORM.FindType.Value = 2 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 培训主题“ *以培训人查询 CASE THISFORM.FindType.Value = 3 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 培训人“ ENDCASE *进行查询 SELECT TrainPrj *过滤记录 SET FILTER TO &FindStr GO TOP,CmdOk.Click-2/5,*循环询问用户 DO WHILE .NOT. EOF() *更新主表单画面 TrainPrj.REFRESH *将本表单最小化 THISFORM.WINDOWSTATE=1 *询问用户 IF MESSAGEBOX(“是本笔记录吗“,4+32,“人力资源管理系统“) = 6 *退出循环 EXIT,CmdOk.Click-3/5,*如果用户选择“否” ELSE *下移记录指针并刷新表单 SKIP TrainPrj.REFRESH ENDIF *将本表单还原 THISFORM.WINDOWSTATE = 0 ENDDO,CmdOk.Click-4/5,*到了表的末尾并且未找到记录 IF EOF() MESSAGEBOX(“未找到指定的记录“, 16, “人力资源管理系统“) *回到原记录 GO oldRecord ENDIF *清除过滤条件 SET FILTER TO TrainPrj.REFRESH THISFORM.RELEASE,CmdOk.Click-5/5,ThisForm.Release,取消钮CmdCcl.Click,VFP提供了简单的统计命令，也可以根据统计创建图表。,2.4数据统计,VFP提供了5个统计命令： Count、Sum、Average、CalCulate、Total。,2.4.1数据统计命令,命令格式： COUNT FOR/WHILE TO 功能： 统计表中记录数目。 说明：COUNT 在 FOR 或 WHILE 条件为“真”时，对一定范围内的记录进行计数。如果 SET TALK 是 ON，则显示记录的数目。如果 SET DELETE 是 OFF，则带有删除标记的记录也包括在计数中。,1.计数命令,下面的示例统计并显示在巴黎的顾客数目。 CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE customer & 打开 Customer 表 CLEAR COUNT FOR UPPER(city) = PARIS DISPLAY FIELDS company, contact FOR UPPER(city) = PARIS,.Count示例,格式： SUM 表达式列表 范围 FOR/ WHILE 条件 TO 内存变量表 | TO ARRAY 数组名 功能： 对当前选定表的指定数值字段或全部数值字段进行求和。,2.求和命令,下面的示例显示 products 表中的 in_stock 字段和 on_order 字段的总计，以及这两个总计的和。 CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) USE products TO gnInStock, gnOnOrder, gnUnits CLEAR ? ? 库存总数 : , gnInStock & 显示 3119.00 ? 订货总数 : , gnOnOrder & 显示 780.00 ? 总单位 : , gnUnits & 显示 3899.00,.Sum示例,格式： AVERAGE 表达式列表 范围 FOR/WHILE 条件 TO 变量列表 | TO ARRAY 数组名 功能： 计算数值表达式或字段的算术平均值。 说明： 除非包含可选的表达式列表，否则选定表的所有字段都将参与求平均值的运算。如果 SET TALK 为 ON，结果显示在屏幕上。如果 SET HEADINGS 为 ON，字段名或包括字段名的表达式将显示在结果的上面。,3.求平均值,CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE orders & 打开 order 表 CLEAR AVERAGE Order_Amt & 计算所有订单的平均值 AVERAGE Order_Amt TO gnAvg & 保存平均值到内存变量 ? 平均订货数: ? gnAvg & 再次显示平均值,.Average示例,格式: TOTAL ON TO FOR/WHILE FIELDS 功能: 计算当前选定表中数值字段的总和。,4.分组汇总命令,要使用此命令，当前工作区中的表必须经过排序或索引。对于具有相同字段值或索引关键字值的各组记录，将分别计算其总计值。总计结果放入另一个表的记录中，同时在此表中还将对这些字段值或索引关键字值创建一条记录。 如果第二个表中数值字段的宽度不足以放置总计值，将会发生数值溢出错误。当发生数值溢出错误时，Visual FoxPro 保存总计值最主要的部分: *小数位被截断，即对总计值余下小数位进行圆整。 *如果总计值仍然不能放下，如包含七位以上的数字，这时将采用科学计数法表示。 *最后，用星号代替字段的内容。,.Total命令使用说明,AcademyCount视图对员工信息表中各学历人数进行了分组统计，其统计结果是以表的形式显示的，在VFP中可以使用MicroSoft Graph将表形式的统计结果变为图表形式显示。 MicroSoft Graph包含2个对象，数据表DadaSheet和图表Chart，Chart结果基于DadaSheet。 以下介绍以图表形式显示员工信息表中各学历人数统计结果。,2.4.2创建统计图表,新建1个表，命名为Chart,该表中包含1个通用型字段。,.创建表,新建1个表单：ChartGraph，将表Chart添加到数据环境中。 在表单中添加1个Active绑定控件：ChartGraph 调整其大小，使其能显示整个图表。,.创建表单,*回车换行符 #DEFINE CRLF CHR(13) + CHR(10) *TAB键 #DEFINE TAB CHR(9) *运行查询 DO AGStat.qpr,.表单Init代码-1/6,LOCAl lcData *读取学历名称作为横坐标 lcData = “ + TAB + “男“ + TAB + “女“ + CRLF SELECT AGStat GO TOP SCAN lcData = lcData + ALLTRIM(学历) + TAB +; ALLTRIM(STR(男) + TAB + ALLTRIM(STR(女) + CRLF ENDSCAN,.表单Init代码-2/6,*将字符串生成的Chart对象保存通用型字段中 IF !USED(“Chart“) USE DATA/Chart ENDIF SELECT Chart APPEND GENERAL 学历统计 DATA lcData CLASS “MSGRAPH.CHART“ *设置OLE绑定控件的数据源为通用型字段 THISFORM.ChartGraph.CONTROLSOURCE = “Chart.学历统计“,.