數(shù)據(jù)查詢與統(tǒng)計.ppt

上一次課講到創(chuàng)建數(shù)據(jù)庫、使用命令顯示與操作記錄、創(chuàng)建與使用可視類、使用表格顯示與操作記錄、使用TreeView控件顯示記錄、表單之間傳遞數(shù)據(jù)。請同學(xué)們回顧這樣兩個問題：,數(shù)據(jù)庫表結(jié)構(gòu)中項(xiàng)目組成？,2.如何添加、編輯自定義方法？,1.字段名稱、類型、寬度、小數(shù)位數(shù)、索引方式； 2.字段格式、輸入掩碼、標(biāo)題； 3.字段有效性規(guī)則； 4.字段注釋。,添加方法：菜單：表單/新建屬性/輸入上面的屬性名/添加 建立以后，可以通過“菜單：表單/編輯屬性/方法程序”進(jìn)行編輯。 對表單對象新建方法程序內(nèi)容的編輯與對象事件過程編輯相同。,上一次課主要學(xué)習(xí)的內(nèi)容,本章主要介紹VFP中的數(shù)據(jù)查詢命令，SQL查詢語句，在表單中定位記錄，過濾記錄以及數(shù)據(jù)統(tǒng)計。,第2章.數(shù)據(jù)查詢與統(tǒng)計,VFP中數(shù)據(jù)查詢命令分類： VFP中數(shù)據(jù)查詢命令可以分為順序查詢、索引查詢和過濾記錄3種。,2.1數(shù)據(jù)查詢命令,格式: LOCATE FOR 格式: CONTINUE 功能: 定位到第一個符合條件的記錄位置, CONTINUE定位下一個符合條件的記錄 注意：該命令只能在當(dāng)前工作區(qū)查詢。,2.1.1順序查詢,在下列示例中，定位德國顧客的記錄，然后顯示總數(shù)。 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 ? 總的德國客戶數(shù): + LTRIM(STR(gnCount),.Locate例,命令格式：SEEK 功能：SEEK 在一個表中搜索首次出現(xiàn)的一個記錄，這個記錄的索引關(guān)鍵字必須與指定的表達(dá)式匹配。,2.1.2索引查詢,完整的命令格式：SEEK eExpression ORDER nIndexNumber | IDXIndexFileName | TAG TagName OF CDXFileName ASCENDING | DESCENDING IN nWorkArea | cTableAlias 說明：只能在索引過的表中使用 seek 命令，并且只能搜索索引關(guān)鍵字。除非 SET EXACT 的設(shè)置為 OFF ，否則匹配指的是完全匹配。 如果 SEEK 找到了與索引關(guān)鍵字相匹配的記錄，則 RECNO( ) 返回匹配記錄的記錄號；FOUND( ) 返回“真”(.T.)；EOF( ) 返回“假”(.F.)。 如果找不到相匹配的關(guān)鍵字，則 RECNO( ) 將表中記錄的個數(shù)加 1，然后返回；FOUND( ) 返回“假”(.F.)；EOF( ) 返回“真”(.T.)。 如果 SET NEAR 設(shè)置為 ON ，則記錄指針指向與索引關(guān)鍵字最相匹配的那個記錄的后面一個記錄。如果 SET NEAR 設(shè)置為 OFF，則記錄指針指向文件的結(jié)尾。在這兩種情況下，RECNO(0) 都返回與關(guān)鍵字最匹配的記錄號。,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 命令查找與內(nèi)存變量 gcseekval 的值相匹配的索引關(guān)鍵字表達(dá)式。,.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語句，可以從一個或多個表中檢索數(shù)據(jù)。SELECT SQL 命令是與其它 VFP 一樣的內(nèi)置的 VFP 命令。當(dāng)你使用 SELECT 來生成查詢時, VFP翻譯查詢并從表中獲取指定數(shù)據(jù)。你可以從以下地方創(chuàng)建 SELECT 查詢: “命令”窗口中 帶有其它任何 VFP 命令的 VFP 程序中 查詢設(shè)計器中,2.2查詢操作,SELECT ALL|DISTINCT & ALL默認(rèn)設(shè)置,結(jié)果包含所有行 | 剔除重復(fù)的行。 TOP PERCENT&符合條件記錄中，取指定數(shù)量或百分比的記錄。該子句要求先ORDER BY。包含 PERCENT 關(guān)鍵字時，取值范圍0.01到99.99 AS , AS &限定匹配項(xiàng)的名稱, 檢索項(xiàng)應(yīng)該是FROM 子句所包含的表中的字段名稱 或 一個常量，查詢結(jié)果中每一行都出現(xiàn)這個常量值 或 一個表達(dá)式，可以是用戶自定義函數(shù)名。指定各項(xiàng)均生成一列。如果多項(xiàng)同名, 在項(xiàng)名前包含表別名和一個句點(diǎn)來避免列重復(fù)。”,.Select-SQL格式-1/6,“AS ”為查詢輸出中的列指定顯示名, 下列字段函數(shù)可以與選定項(xiàng)一起使用，選定項(xiàng)可以是一個字段或包含字段的表達(dá)式: AVG(檢索項(xiàng)), 計算列中數(shù)值的平均值。 COUNT(檢索項(xiàng)), 計算列中選定項(xiàng)的數(shù)目。計算查詢輸出的行數(shù)。COUNT(*) 計算查詢輸出中的行數(shù)。 MIN(檢索項(xiàng)), 確定列中檢索項(xiàng)的最小值。 MAX(檢索項(xiàng)), 確定列中檢索項(xiàng)的最大值。 SUM(檢索項(xiàng)), 計算列中數(shù)值的和。,.Select-SQL格式-2/6,FROM ! AS&列出所有從中檢索數(shù)據(jù)的表。AS ,為表指定一個臨時名稱。 INTO |TO FILE|TO PRINTER|TO SCREEN& 指定在何處保存查詢結(jié)果。:ARRAY 數(shù)組名 | CURSOR 臨時表名 | DBF/TABLE 表名 缺省輸出到瀏覽窗口。,.Select-SQL格式-3/6,WHERE AND AND|OR AND|OR & 指定查詢條件/表間聯(lián)接條件,可以是關(guān)系表達(dá)式/邏輯 表達(dá)式,也可以是: FieldName NOT BETWEEN Start_Range AND End_Range 檢查是否字段值在指定的范圍內(nèi)。 FieldName NOT LIKE cExpression 搜索匹配 cExpression 的各字段?？梢杂冒俜痔?(%) 和下劃線 (_) 通配符作為 cExpression 的一部分。百分號代表串中任何長度的不知道的字符。下劃線代表串中的單個的不知道的字符。,.Select-SQL格式-4/6,FieldName NOT IN (Subquery) 在記錄出現(xiàn)在查詢結(jié)果中之前字段必須包含一個由子查詢返回的值。 GROUP BY ,HAVING &指定分組輸出及輸出條件HAVING應(yīng)出現(xiàn)在INTO前。,.Select-SQL格式-5/6,UNION ALL SELECT語句 &把一個 SELECT 語句的最后查詢結(jié)果同另一個 SELECT 語句最后查詢結(jié)果組合起來。默認(rèn)情況下，UNION 檢查組合的結(jié)果并排除重復(fù)的行。要組合多個UNION 子句，可使用括號?？梢杂?UNION 子句模擬一個外部聯(lián)接。ALL 防止 UNION 刪除組合結(jié)果中重復(fù)的行。 ORDER BY ASC|DESC,ORDER BY ASC|DESC&查詢結(jié)果排序輸出,.Select-SQL格式-6/6,顯示customer 表中所有的公司名稱(來自一個表的一個字段)。 CLOSE ALL CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) SELECT pany ; FROM customer,.Select-SQL示例1,顯示兩個表中的三個字段的內(nèi)容并根據(jù) 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,顯示在指定字段只有唯一數(shù)據(jù)的記錄。 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,將來自兩個表的字段內(nèi)容存儲在第三個表中。 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命令查找表中任意字段（備注型和通用型除外）并進(jìn)行定位。 下面以“輸入查詢條件”為例介紹VFP如何查找并定位記錄。,2.3定位記錄,在項(xiàng)目管理器中建立“輸入查詢條件”表單,表單名稱為:trainprjser，在表單中添加2個標(biāo)簽、1個組合框、1個文本框、2個MyCmd類對象。該表單被用于其他表單中,所以不需要單獨(dú)定義數(shù)據(jù)環(huán)境.,2.3.1創(chuàng)建表單,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=“項(xiàng)目編號,項(xiàng)目主題,培訓(xùn)人“ 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 *根據(jù)查詢類型生成查詢條件 DO CASE *以項(xiàng)目編號查詢 CASE THISFORM.FindType.Value = 1 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 項(xiàng)目編號“,2.3.2添加代碼 確定鈕 CmdOk.Click-1/5,CASE THISFORM.FindType.Value = 2 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 培訓(xùn)主題“ *以培訓(xùn)人查詢 CASE THISFORM.FindType.Value = 3 FindStr=“+ALLTRIM(THISFORM.FindKey.Value)+“$ 培訓(xùn)人“ ENDCASE *進(jìn)行查詢 SELECT TrainPrj *過濾記錄 SET FILTER TO &FindStr GO TOP,CmdOk.Click-2/5,*循環(huán)詢問用戶 DO WHILE .NOT. EOF() *更新主表單畫面 TrainPrj.REFRESH *將本表單最小化 THISFORM.WINDOWSTATE=1 *詢問用戶 IF MESSAGEBOX(“是本筆記錄嗎“,4+32,“人力資源管理系統(tǒng)“) = 6 *退出循環(huán) EXIT,CmdOk.Click-3/5,*如果用戶選擇“否” ELSE *下移記錄指針并刷新表單 SKIP TrainPrj.REFRESH ENDIF *將本表單還原 THISFORM.WINDOWSTATE = 0 ENDDO,CmdOk.Click-4/5,*到了表的末尾并且未找到記錄 IF EOF() MESSAGEBOX(“未找到指定的記錄“, 16, “人力資源管理系統(tǒng)“) *回到原記錄 GO oldRecord ENDIF *清除過濾條件 SET FILTER TO TrainPrj.REFRESH THISFORM.RELEASE,CmdOk.Click-5/5,ThisForm.Release,取消鈕CmdCcl.Click,VFP提供了簡單的統(tǒng)計命令，也可以根據(jù)統(tǒng)計創(chuàng)建圖表。,2.4數(shù)據(jù)統(tǒng)計,VFP提供了5個統(tǒng)計命令： Count、Sum、Average、CalCulate、Total。,2.4.1數(shù)據(jù)統(tǒng)計命令,命令格式： COUNT FOR/WHILE TO 功能： 統(tǒng)計表中記錄數(shù)目。 說明：COUNT 在 FOR 或 WHILE 條件為“真”時，對一定范圍內(nèi)的記錄進(jìn)行計數(shù)。如果 SET TALK 是 ON，則顯示記錄的數(shù)目。如果 SET DELETE 是 OFF，則帶有刪除標(biāo)記的記錄也包括在計數(shù)中。,1.計數(shù)命令,下面的示例統(tǒng)計并顯示在巴黎的顧客數(shù)目。 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 表達(dá)式列表 范圍 FOR/ WHILE 條件 TO 內(nèi)存變量表 | TO ARRAY 數(shù)組名 功能： 對當(dāng)前選定表的指定數(shù)值字段或全部數(shù)值字段進(jìn)行求和。,2.求和命令,下面的示例顯示 products 表中的 in_stock 字段和 on_order 字段的總計，以及這兩個總計的和。 CLOSE DATABASES OPEN DATABASE (HOME(2) + datatestdata) USE products TO gnInStock, gnOnOrder, gnUnits CLEAR ? ? 庫存總數(shù) : , gnInStock & 顯示 3119.00 ? 訂貨總數(shù) : , gnOnOrder & 顯示 780.00 ? 總單位 : , gnUnits & 顯示 3899.00,.Sum示例,格式： AVERAGE 表達(dá)式列表 范圍 FOR/WHILE 條件 TO 變量列表 | TO ARRAY 數(shù)組名 功能： 計算數(shù)值表達(dá)式或字段的算術(shù)平均值。 說明： 除非包含可選的表達(dá)式列表，否則選定表的所有字段都將參與求平均值的運(yùn)算。如果 SET TALK 為 ON，結(jié)果顯示在屏幕上。如果 SET HEADINGS 為 ON，字段名或包括字段名的表達(dá)式將顯示在結(jié)果的上面。,3.求平均值,CLOSE DATABASES OPEN DATABASE (HOME(2) + Datatestdata) USE orders & 打開 order 表 CLEAR AVERAGE Order_Amt & 計算所有訂單的平均值 AVERAGE Order_Amt TO gnAvg & 保存平均值到內(nèi)存變量 ? 平均訂貨數(shù): ? gnAvg & 再次顯示平均值,.Average示例,格式: TOTAL ON TO FOR/WHILE FIELDS 功能: 計算當(dāng)前選定表中數(shù)值字段的總和。,4.分組匯總命令,要使用此命令，當(dāng)前工作區(qū)中的表必須經(jīng)過排序或索引。對于具有相同字段值或索引關(guān)鍵字值的各組記錄，將分別計算其總計值?？傆嫿Y(jié)果放入另一個表的記錄中，同時在此表中還將對這些字段值或索引關(guān)鍵字值創(chuàng)建一條記錄。 如果第二個表中數(shù)值字段的寬度不足以放置總計值，將會發(fā)生數(shù)值溢出錯誤。當(dāng)發(fā)生數(shù)值溢出錯誤時，Visual FoxPro 保存總計值最主要的部分: *小數(shù)位被截斷，即對總計值余下小數(shù)位進(jìn)行圓整。 *如果總計值仍然不能放下，如包含七位以上的數(shù)字，這時將采用科學(xué)計數(shù)法表示。 *最后，用星號代替字段的內(nèi)容。,.Total命令使用說明,AcademyCount視圖對員工信息表中各學(xué)歷人數(shù)進(jìn)行了分組統(tǒng)計，其統(tǒng)計結(jié)果是以表的形式顯示的，在VFP中可以使用MicroSoft Graph將表形式的統(tǒng)計結(jié)果變?yōu)閳D表形式顯示。 MicroSoft Graph包含2個對象，數(shù)據(jù)表DadaSheet和圖表Chart，Chart結(jié)果基于DadaSheet。 以下介紹以圖表形式顯示員工信息表中各學(xué)歷人數(shù)統(tǒng)計結(jié)果。,2.4.2創(chuàng)建統(tǒng)計圖表,新建1個表，命名為Chart,該表中包含1個通用型字段。,.創(chuàng)建表,新建1個表單：ChartGraph，將表Chart添加到數(shù)據(jù)環(huán)境中。 在表單中添加1個Active綁定控件：ChartGraph 調(diào)整其大小，使其能顯示整個圖表。,.創(chuàng)建表單,*回車換行符 #DEFINE CRLF CHR(13) + CHR(10) *TAB鍵 #DEFINE TAB CHR(9) *運(yùn)行查詢 DO AGStat.qpr,.表單Init代碼-1/6,LOCAl lcData *讀取學(xué)歷名稱作為橫坐標(biāo) lcData = “ + TAB + “男“ + TAB + “女“ + CRLF SELECT AGStat GO TOP SCAN lcData = lcData + ALLTRIM(學(xué)歷) + TAB +; ALLTRIM(STR(男) + TAB + ALLTRIM(STR(女) + CRLF ENDSCAN,.表單Init代碼-2/6,*將字符串生成的Chart對象保存通用型字段中 IF !USED(“Chart“) USE DATA/Chart ENDIF SELECT Chart APPEND GENERAL 學(xué)歷統(tǒng)計 DATA lcData CLASS “MSGRAPH.CHART“ *設(shè)置OLE綁定控件的數(shù)據(jù)源為通用型字段 THISFORM.ChartGraph.CONTROLSOURCE = “Chart.學(xué)歷統(tǒng)計“,.