VisualFoxpro6.0程序設(shè)計(jì)基礎(chǔ)SQL查詢與多表程序

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

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

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

4、QL語(yǔ)言中，查詢語(yǔ)言中只有一條查詢命令，即SELECT語(yǔ)句。 （見(jiàn)教科書(shū)160-176頁(yè)） 1)基本的SELECT語(yǔ)句 格式： SELECT , FROM 數(shù)據(jù)庫(kù)名稱！ 功能：從指定表或視圖中查詢?nèi)坑涗浀闹付ㄗ侄沃?。（?jiàn)教科書(shū)166頁(yè)）【例6-14】查詢顯示讀者表中所有的讀者信息。表單Form1的Init事件代碼如下：thisform.grid1.recordsourcetype=4thisform.grid1.recordsource=”select * from 讀者” 【例6-15】查詢顯示讀者表中所有讀者的讀者編號(hào)、讀者姓名、性別，并將結(jié)果保存在表文件“中。表單Form1的Init事

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

25、thisform.label2.caption=請(qǐng)輸入要查詢的+lx“查詢”按鈕的Click事件代碼如下：xx=alltrim(thisform.text1.value)DO CASE CASE lx=讀者編號(hào) select * from 讀者 where 讀者編號(hào)=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(請(qǐng)選擇查詢類(lèi)型)ENDCASEthisform.grid1.recordsourcetype=1thisform.grid1.recordsource=cx使用INTO CURSOR 將查詢結(jié)果存放到臨時(shí)表中。ex1、有如下表所示的數(shù)據(jù)表：外幣名稱外幣代碼現(xiàn)鈔買(mǎi)入價(jià)基準(zhǔn)價(jià)現(xiàn)鈔賣(mài)出價(jià)美元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）計(jì)算出劉劍所持有的全部外幣相當(dāng)于人民幣的價(jià)值數(shù)量，寫(xiě)出正確的SQL命令。注意：某種外幣相當(dāng)于人民幣數(shù)量的計(jì)算公式：人民幣價(jià)值數(shù)量= 該種外幣的現(xiàn)鈔買(mǎi)入價(jià)* 該種外幣持有數(shù)量SELECT SUM(現(xiàn)鈔買(mǎi)入價(jià)*持有數(shù)量) AS ; 人民幣價(jià)值 FROM 持有者,兌換 ; WHERE 兌換.外幣代碼=持有者.外幣代碼 ; AND 姓名=劉劍外幣名稱外幣代碼現(xiàn)鈔買(mǎi)入價(jià)基準(zhǔn)價(jià)現(xiàn)鈔賣(mài)出價(jià)美元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）將兌換表中，美元和英鎊的基準(zhǔn)價(jià)上調(diào)0.05%，寫(xiě)出正確的SQL命令。UPDATE 兌換SET 基準(zhǔn)價(jià)=基準(zhǔn)價(jià)*1.05 ; WHERE 外幣名稱=美元 ; OR外幣名稱=英鎊外幣名稱外幣代碼現(xiàn)鈔買(mǎi)入價(jià)基準(zhǔn)價(jià)現(xiàn)鈔賣(mài)出價(jià)美元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）刪除“持有者”表中所有外幣名稱為“歐元”的記錄，寫(xiě)出正確的SQL命令。DELETE FROM 持有者 WHERE 外幣代碼 IN; (SELECT 外幣代碼 FROM 兌換 ; WHERE 外幣名稱=歐元)外幣名稱外幣代碼現(xiàn)鈔買(mǎi)入價(jià)基準(zhǔn)價(jià)現(xiàn)鈔賣(mài)出價(jià)美元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）查詢持有外幣種類(lèi)在3種以上