VisualFoxpro6.0程序設計基礎SQL查詢與多表程序

1、Visual Foxpro 6.0程序設計基礎 SQL查詢與多表程序要點： 1.掌握SQL查詢 2.掌握基于單表、多表的表單程序 SQL查詢與多表程序SQL語言的應用SQL語言簡介 SQL（Structured Query Language）語言是一種十分重要的標準數(shù)據(jù)庫語言。SQL語言至1986年成為國際標準語言以后，各數(shù)據(jù)庫軟件開發(fā)商紛紛推出SQL的關系數(shù)據(jù)庫管理系統(tǒng)產(chǎn)品或接口軟件，許多應用廣泛的關系數(shù)據(jù)庫管理系統(tǒng)也都增加了DBMS功能，支持標準SQL語言，從而使未來的數(shù)據(jù)庫環(huán)境連接為一個統(tǒng)一的整體。 它不是數(shù)據(jù)庫管理系統(tǒng)，也不是應用軟件開發(fā)語言，它只能用于對數(shù)據(jù)庫中數(shù)據(jù)的操作。 SQL語

2、言有兩種使用方式，即可以作為自含式語言，在數(shù)據(jù)庫管理系統(tǒng)中獨立使用，又可以作為嵌入式語言，嵌入許多高級語言（如C、FORTRAN、COBOL）中使用。 SQL語言的特點1SQL語言是一種一體化的語言 SQL語言集數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)查詢和數(shù)據(jù)控制的功能于一體，語言風格統(tǒng)一，可以獨立完成數(shù)據(jù)庫的全部操作。 2SQL語言是一種高度非過程化的語言。 它沒有必要一步步告訴計算機“如何”去做，而只需要描述清楚用戶要“做什么”，SQL語言就可以將要求交給系統(tǒng)，自動完成全部工作。它的大多數(shù)語句都是獨立執(zhí)行、與上下文無關。3語言簡潔，易學易用SQL的命令動詞SQL功能 命令動詞數(shù)據(jù)查詢 SELECT數(shù)據(jù)定

3、義 CREATE、DROP、ALTER數(shù)據(jù)操縱 INSERT、UPDATE、DELETE數(shù)據(jù)控制 GRANT、REVOKE1、數(shù)據(jù)定義命令 CREATE TABLE-SQL（建立表結構） ALTER TABLE-SQL（修改表結構） DROP TABLE-SQL（刪除表）刪除表的SQL語句： DROP TABLE 表名（見教科書99頁）2、數(shù)據(jù)操縱命令 INSERT-SQL（插入數(shù)據(jù)） DELETE-SQL（刪除數(shù)據(jù)） UPDATE-SQL（更新數(shù)據(jù)）（見教科書124、128、129-130頁）3、SQL數(shù)據(jù)查詢 在數(shù)據(jù)庫中對數(shù)據(jù)的操作，很多時侯是查詢，因此，數(shù)據(jù)查詢是數(shù)據(jù)庫的核心操作。而在S

4、QL語言中，查詢語言中只有一條查詢命令，即SELECT語句。 （見教科書160-176頁） 1)基本的SELECT語句 格式： SELECT , FROM 數(shù)據(jù)庫名稱！ 功能：從指定表或視圖中查詢全部記錄的指定字段值。（見教科書166頁）【例6-14】查詢顯示讀者表中所有的讀者信息。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=”select * from 讀者” 【例6-15】查詢顯示讀者表中所有讀者的讀者編號、讀者姓名、性別，并將結果保存在表文件“中。表單Form1的Init事

5、件代碼如下：select 讀者編號,讀者姓名,性別 ; from 讀者 into table dzxx thisform.grid1.recordsourcetype=1thisform.grid1.recordsource=dzxx使用INTO DBF|TABLE 將查詢結果存放到永久表中。 2)唯一性查詢 唯一性查詢指對于要顯示的結果，若有重復出現(xiàn)時，則只顯示重復行中的一行。這可通過在基本SELECT語句中加入DISTINCT關鍵字的方法實現(xiàn)。若不加此關鍵字，則顯示全部行。【例6-16】查詢圖書表中所有的出版社，相同的出版社只顯示一個。表單Form1的Init事件代碼如下：thisform

6、.grid1.recordsourcetype=4thisform.grid1.recordsource=select distinct; 出版社 from 圖書3)帶條件的查詢 在SELECT語句中，可以通過WHERE子句為查詢規(guī)定條件WHERE子句的格式：WHERE AND 功能：查詢指定表或視圖中滿足查詢條件的記錄。其中可以是關系表達式，也可以是邏輯表達式，下表列出了組成常用的運算符。運算符類別運算符實例關系運算符=、=、=、成績=60邏輯運算符NOT、 AND、 OR成績=60 AND成績70字符串匹配運算符LIKE姓名 LIKE “王% 范圍運算符BETWEEN AND成績BETWE

7、EN 60 AND 70空值運算符IS NULL成績 IS NULL集合運算符IN、NOT IN專業(yè)班級 IN (“計算機”,”英語”) LIKE子句中可以用通配符：下劃線“_”，表示任何一個字符；百分號符“%”，表示一串字符?！纠?-17】查詢圖書表中書名包含“程序”二字的圖書信息。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 圖書 where 書名 like %程序% 【例6-18】查詢圖書表中圖書的價格在28到30之間的圖書。表單Form1的

8、Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 圖書 where 價格 between 28 and 30【例6-19】在借閱表中查詢借閱了書號為bj080001、bj060022的讀者編號、書號及借閱日期。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； 讀者編號,書號,借閱日期 from 借閱； where 書號 in(bj08000

9、1,bj060022)” 4)排序查詢 通過ORDER BY子句可以實現(xiàn)查詢結果的排序輸出，允許按一列或多列排序。 格式： ORDER BY ASC|DESC,ASC|DESC 其中，ASC表示升序排序（缺省方式），DESC表示降序排序。【例6-20】按辦證日期先后順序顯示讀者表中的讀者信息。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select * ； from 讀者 order by 辦證日期【例6-21】在借閱表中按讀者編號從大到小的順序，讀者編號相同時按借閱日期的先后

10、順序顯示讀者編號、書號及借閱日期。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； 讀者編號,書號,借閱日期 from 借閱； order by 讀者編號 desc,借閱日期 asc【例6-22】顯示圖書表中數(shù)量最多的兩種圖書信息。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select top 2； * from 圖書 order by 數(shù)量 de

11、sc使用TOP PERCENT短語顯示部分結果。注意：TOP短語要與ORDER BY短語同時使用才有效。 【例】顯示價格最低的20%圖書的信息。SELECT * TOP 20 PERCENT FROM 圖書 ORDER BY 價格 5)帶計算函數(shù)的查詢 在SELECT語句中，使用SQL語言提供的一些查詢計算函數(shù)，可以增強查詢功能?；镜牟樵冇嬎愫瘮?shù)的格式及功能如下表所示。函數(shù)的格式函數(shù)功能COUNT(*)計算記錄條數(shù)SUM(字段名)求字段名所指定字段值的總和AVG(字段名)求字段名所指定字段的平均值MAX(字段名)求字段名所指定字段的最大值MIN(字段名)求字段名所指定字段的最小值【例6-23

12、】計算圖書表中圖書的最高價格和最低價格。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； max(價格),min(價格) from 圖書【例6-24】統(tǒng)計圖書表中圖書的總數(shù)量。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ； sum(數(shù)量) from 圖書【例6-25】計算圖書表中圖書的平均價格，保存在數(shù)組DJ中。表單Form1的Init

13、事件代碼如下：select avg(價格) from 圖書 into array djthisform.label1.caption=”圖書的平均價格； 為:”+ str(dj(1),5,2)+元使用INTO ARRAY 將查詢結果存放到數(shù)組中。 6)用別名輸出列標題（字段名） 將列名用含義更明確的別名輸出，便于理解，可以通過AS關鍵字實現(xiàn)。 格式： AS 功能：將查詢輸出的列名以“列別名”輸出。將【例6-23】中顯示結果的列標題改為最高單價和最低單價,運行結果如圖所示,其SQL語句改為:select max(價格) as 最高單價,min(價格) as 最低單價 from 圖書 7)分組查詢

14、 通過GROUP BY子句可以實現(xiàn)分組查詢。 格式： GROUP BY , HAVING 其中，可以是表的字段名、字段函數(shù)名或標識列的數(shù)值型表達式；HAVING 子句進一步限定分組的條件。 在select語句中，where子句是對記錄的限定，而Having短語是對分組的限定，where、group by及Having可以同時出現(xiàn)在一個select語句中。三者在select中出現(xiàn)的順序為：WhereGroup by-Having。記錄1記錄2記錄3記錄4記錄5記錄6記錄2記錄4記錄5記錄6記錄2記錄5記錄4記錄6記錄2記錄5where限定Group by分組Having限定【例6-26】計算圖書

15、表中每個出版社的圖書數(shù)量。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“ select ； 出版社,sum(數(shù)量) as 數(shù)量 from 圖書; group by 出版社【例6-27】在借閱表中統(tǒng)計每本圖書的借閱次數(shù)。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“ select ； 書號,count(*) as 借閱次數(shù) from 借閱; group by 書號 8

16、)多表聯(lián)接查詢 SELECT語句可以實現(xiàn)對的4種聯(lián)接類型的查詢：內部聯(lián)接（Inner Join）:只有2個表的字段都滿足聯(lián)接條件時，才將此記錄選入查詢結果中。左聯(lián)接(Left Outer Join)：聯(lián)接條件左邊表中的記錄都包含在查詢結果中，而右邊表中的記錄只有滿足聯(lián)接條件時，才選入查詢結果中。右聯(lián)接（Right Outer Join）：與左聯(lián)接正好相反。完全聯(lián)接（Full Join）：無論2個表中的記錄是否滿足聯(lián)接條件，都將全部記錄選入查詢結果中。掌握該種聯(lián)接 通常有3種方法實現(xiàn)多表聯(lián)接查詢，一是在查詢命令中指定聯(lián)接類型；二是在查詢命令中使用WHERE子句；三是使用嵌套查詢。 （1）指定聯(lián)接

17、類型實現(xiàn)多表間的聯(lián)接查詢 格式：SELECT FROM INNER JOIN JOIN ON ON WHERE 功能：通過指定的聯(lián)接類型建立多表間的聯(lián)接 注意：若SELECT后要查詢的列名（字段名）在2個表中都有，則必須采用“表名.字段名”，若字段名唯一，則可僅寫出字段名。（2）用WHERE子句實現(xiàn)多表間的聯(lián)接查詢：用WHERE子句實現(xiàn)多表間的聯(lián)接查詢時，無須直接指明聯(lián)接類型（隱含是內部聯(lián)接），只需把聯(lián)接條件直接寫入WHERE子句即可。（3）使用嵌套查詢實現(xiàn)多表間的聯(lián)接查詢：在SQL語言中，由SELECT、FROM、WHERE語句組成一個查詢塊。嵌套查詢就是將第2個查詢塊放在第1個查詢塊的WH

18、ERE條件中，形成外層(第1個)查詢包含內層(第2個)查詢的嵌套查詢。 外層查詢也稱為主查詢、父查詢，內層查詢也稱為下層查詢、子查詢。系統(tǒng)對嵌套查詢的處理過程是先做子查詢，在子查詢的基礎上再做主查詢。 在子查詢中還可以再包含更下層的查詢，從而形成層嵌套查詢。【例6-28】查詢至少被借閱了2次的圖書的書號及書名。方法1:表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select ; 圖書.書號,書名 from 圖書,借閱 ; where 圖書.書號=借閱.書號 group ； by

19、圖書.書號 having count(*)=2select 圖書.書號,書名 from 圖書 a,借閱 b; where a.書號=b.書號 group ； by a.書號 having count(*)=2方法2:表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=“select; 圖書.書號,書名 from 圖書 join 借閱 ; on 圖書.書號=借閱.書號 group by; 圖書.書號 having count(*)=2方法3:表單Form1的Init事件代碼如下：thisf

20、orm.grid1.recordsourcetype=4thisform.grid1.recordsource=“select 書號,； 書名 from 圖書 where 書號 in ; (select 書號 from 借閱 group by ； 書號 having count(*)=2)【例6-29】輸入圖書編號，查詢借閱該圖書的讀者姓名、單位、聯(lián)系電話。方法1:“查詢”按鈕的Click事件代碼如下：bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource= s

21、elect ； 讀者姓名,單位,聯(lián)系電話 from 讀者,； 借閱 where 讀者.讀者編號=； 借閱.讀者編號 and 書號=bh方法2:“查詢”按鈕的Click事件代碼如下：bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 讀者姓名,單位,聯(lián)系電話 from 讀者 ; join 借閱 on 讀者.讀者編號=； 借閱.讀者編號 where 書號=bh方法3:“查詢”按鈕的Click事件代碼如下：bh=alltrim(thisform

22、.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource=“select ； 讀者姓名,單位,聯(lián)系電話 from 讀者 ; where 讀者編號 in ； (select 讀者編號 from 借閱 where； 書號=bh)【例6-30】輸入讀者編號，查詢該讀者的姓名、單位、借閱的圖書的書名及借閱日期。方法1:“查詢”按鈕的Click事件代碼如下：bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.gr

23、id1.recordsource=“select ； 讀者姓名,單位,書名,借閱日期 from ； 讀者,借閱, 圖書 where 讀者.讀者編號=； 借閱.讀者編號 and 借閱.書號=； 圖書.書號 and 讀者.讀者編號=bh方法2:“查詢”按鈕的Click事件代碼如下：bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4 thisform.grid1.recordsource= select ； 讀者姓名,單位,書名,借閱日期 from ； 讀者 join 借閱 join 圖書 on ； 借閱.書號=圖書.

24、書號 on ； 讀者.讀者編號=借閱.讀者編號 ； where 讀者.讀者編號=bh【例6-10】按圖書編號查詢圖書信息?！安樵儭卑粹o的Click事件代碼如下：bh=alltrim(thisform.text1.value) thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=select * from 圖書 where 書號=bh【例6-11】以多種方式查詢讀者信息，若有多條記錄，按性別降序排列。表單Form1的Load事件代碼如下： public lx組合框Combo1的InteractiveChange事件代碼如下：

25、thisform.label2.caption=請輸入要查詢的+lx“查詢”按鈕的Click事件代碼如下：xx=alltrim(thisform.text1.value)DO CASE CASE lx=讀者編號 select * from 讀者 where 讀者編號=xx into cursor cx CASE lx=讀者姓名 select * from 讀者 where 讀者姓名=xx into cursor cx CASE lx=性別 select * from 讀者 where 性別=xx order by 性別 desc； into cursor cx OTHERWISE messag

26、ebox(請選擇查詢類型)ENDCASEthisform.grid1.recordsourcetype=1thisform.grid1.recordsource=cx使用INTO CURSOR 將查詢結果存放到臨時表中。ex1、有如下表所示的數(shù)據(jù)表：外幣名稱外幣代碼現(xiàn)鈔買入價基準價現(xiàn)鈔賣出價美元12821.6555827.4500825.9500英鎊141171.43001176.50001204.0500歐元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外幣代碼持有數(shù)量張三121000張三141300張三151500李芳142

27、000李芳181500王林141800王林151200劉劍122000劉劍151200劉劍181500外幣表.dbf持有者.dbf（1）計算出劉劍所持有的全部外幣相當于人民幣的價值數(shù)量，寫出正確的SQL命令。注意：某種外幣相當于人民幣數(shù)量的計算公式：人民幣價值數(shù)量= 該種外幣的現(xiàn)鈔買入價* 該種外幣持有數(shù)量SELECT SUM(現(xiàn)鈔買入價*持有數(shù)量) AS ; 人民幣價值 FROM 持有者,兌換 ; WHERE 兌換.外幣代碼=持有者.外幣代碼 ; AND 姓名=劉劍外幣名稱外幣代碼現(xiàn)鈔買入價基準價現(xiàn)鈔賣出價美元12821.6555827.4500825.9500英鎊141171.430011

28、76.50001204.0500歐元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外幣代碼持有數(shù)量張三121000張三141300張三151500李芳142000李芳181500王林141800王林151200劉劍122000劉劍151200劉劍181500外幣表.dbf持有者.dbf（2）將兌換表中，美元和英鎊的基準價上調0.05%，寫出正確的SQL命令。UPDATE 兌換SET 基準價=基準價*1.05 ; WHERE 外幣名稱=美元 ; OR外幣名稱=英鎊外幣名稱外幣代碼現(xiàn)鈔買入價基準價現(xiàn)鈔賣出價美元12821.65558

29、27.4500825.9500英鎊141171.43001176.50001204.0500歐元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外幣代碼持有數(shù)量張三121000張三141300張三151500李芳142000李芳181500王林141800王林151200劉劍122000劉劍151200劉劍181500外幣表.dbf持有者.dbf（3）刪除“持有者”表中所有外幣名稱為“歐元”的記錄，寫出正確的SQL命令。DELETE FROM 持有者 WHERE 外幣代碼 IN; (SELECT 外幣代碼 FROM 兌換 ; WHERE 外幣名稱=歐元)外幣名稱外幣代碼現(xiàn)鈔買入價基準價現(xiàn)鈔賣出價美元12821.6555827.4500825.9500英鎊141171.43001176.50001204.0500歐元15877.5325895.5600886.2686法郎18585.5500600.4888604.6500姓名外幣代碼持有數(shù)量張三121000張三141300張三151500李芳142000李芳181500王林141800王林151200劉劍122000劉劍151200劉劍181500外幣表.dbf持有者.dbf（4）查詢持有外幣種類在3種以上