VFP第5章SQL語(yǔ)言的應(yīng)用

合肥工業(yè)大學(xué) 1isualFV oxPro 合肥工業(yè)大學(xué) 2 按照美國(guó)國(guó)家標(biāo)準(zhǔn)協(xié)會(huì)（ANSI）的規(guī)定SQL是關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。目前流行的關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)，如Qracle、Sybase、SQL Server、Visual FoxPro等都支持SQL語(yǔ)言標(biāo)準(zhǔn)。SQL雖然在各種數(shù)據(jù)庫(kù)產(chǎn)品中得到了廣泛的支持，但迄今為止，它只是一種建議標(biāo)準(zhǔn)，各種數(shù)據(jù)庫(kù)產(chǎn)品中所實(shí)現(xiàn)的SQL在語(yǔ)法、功能等方面均略有差異。Visual FoxPro在SQL方面支持?jǐn)?shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操縱功能。5.1 SQL語(yǔ) 言 概 述 合肥工業(yè)大學(xué) 3 SQL語(yǔ)言具有如下特點(diǎn)：（1）SQL是一種一體化的語(yǔ)言。它集數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制功能于一體，可以獨(dú)立完成數(shù)據(jù)庫(kù)的全部操作。（2）SQL語(yǔ)言是一種高度非過(guò)程化的語(yǔ)言。只需要描述清楚用戶要“做什么”，不必指明“怎么做”，SQL語(yǔ)言就可以將要求交給系統(tǒng)，自動(dòng)完成全部工作。（3）SQL語(yǔ)言非常簡(jiǎn)潔。雖然SQL語(yǔ)言功能很強(qiáng)，但它只有9條命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQL的語(yǔ)法也非常簡(jiǎn)單，因此容易學(xué)習(xí)和掌握。 （4）SQL語(yǔ)言可以直接以命令方式交互使用，也可以嵌入到程序設(shè)計(jì)語(yǔ)言中以程序方式使用。 合肥工業(yè)大學(xué) 4 Visual FoxPro的SQL SELECT命令的語(yǔ)法格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 數(shù)據(jù)庫(kù)名!AS INNER | LEFT OUTER | RIGHTOUTER|FULL OUTERJOIN !ASON INTO |TO FILEADDITIVE|TO PRINTER PROMPT|TO SCREENPREFERENCE NOCONSOLEPLAINNOWAITWHERE AND AND|OR AND|OR GROUP BY ,HAVING UNIONALLSELECT命令 ORDER BY ASC|DESC,ASC|DESC SQL SELECT是一個(gè)命令的集合，對(duì)它進(jìn)行分解。 5.2 數(shù) 據(jù) 查 詢 合肥工業(yè)大學(xué) 5 SQL查詢語(yǔ)句格式：SelectAll|Distinct|,FromLeftRightJoinOnWhereGroup ByHavingOrder ByAscDesc 其中： All：查詢結(jié)果是表的全部記錄。 Distinct：查詢結(jié)果是不包含重復(fù)行的記錄集。 From：查詢結(jié)果來(lái)源。 LeftRightJoinOn：查詢結(jié)果是多表組和的記錄集。 合肥工業(yè)大學(xué) 6 Where：查詢結(jié)果是表中滿足的記錄集 Group By：查詢結(jié)果是表按分組的記錄集。 Having：是將指定表滿足,并且按表結(jié)果組成的記錄集。 Order By：查詢結(jié)果是否按某一字段值排列。 Asc：查詢結(jié)果按某一字段值升序排列。 Desc：查詢結(jié)果按某一字段值降序排列。 ：進(jìn)行查詢計(jì)算函數(shù)。 ：可以是關(guān)系表達(dá)式。也可以是邏輯表達(dá)式 合肥工業(yè)大學(xué) 7 查詢計(jì)算函數(shù)的格式及功能 函數(shù)格式函數(shù)功能COUNT（*）計(jì)算記錄個(gè)數(shù)SUM（字段名）求字段名和所指定字段的值的總和AVG（字段名）求字段名所指定的平均值MAX（字段名）求字段名所指定的最大值MIN（字段名）求字段名所指定字段的最小值 合肥工業(yè)大學(xué) 8 查詢條件中常用的運(yùn)算符 運(yùn)算符實(shí)例=、=、=、工資現(xiàn)狀3000NOT、AND、OR工資現(xiàn)狀3000LIKE性別 LIKE “男”BETWEEN AND工資現(xiàn)狀 BETWEEN 3000 AND 5000IN職稱(chēng) IN (教授，副教授） 合肥工業(yè)大學(xué) 9 5.2.1 簡(jiǎn)單查詢查詢僅涉及一個(gè)表，是最簡(jiǎn)單的查詢操作 1. 查詢指定列【例4-1】查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名.(1)在命令窗口下輸入以下命令：SELECT 學(xué)號(hào),姓名;FROM 學(xué)生 合肥工業(yè)大學(xué) 10 2. 查詢?nèi)苛小纠?-3】查詢?nèi)w學(xué)生的詳細(xì)記錄. 在命令窗口下輸入以下命令：SELECT *;FROM 學(xué)生 合肥工業(yè)大學(xué) 11 基本查詢所謂基本查詢是指無(wú)條件查詢，其格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 別名1,別名2其中ALL表示輸出所有記錄，包括重復(fù)記錄。DISTINCT表示輸出無(wú)重復(fù)結(jié)果的記錄。當(dāng)選擇多個(gè)數(shù)據(jù)庫(kù)表中的字段時(shí)，可使用別名來(lái)區(qū)分不同的表。的作用是在輸出結(jié)果中的列標(biāo)題，可以不同于字段名?？梢允亲侄蚊⒈磉_(dá)式或函數(shù)。表名代表要查詢的表。要輸出全部字段用“*” 表示。 合肥工業(yè)大學(xué) 12 例 列出學(xué)生名單。OPEN DATABASE D:學(xué)生管理SELECT * FROM 學(xué)生 FROM 成績(jī);WHERE成績(jī)60 合肥工業(yè)大學(xué) 14 4. 查詢滿足條件的元組(2)得到的運(yùn)行結(jié)果如圖4-5所示。圖4-5 合肥工業(yè)大學(xué) 15 帶條件查詢WHERE是條件語(yǔ)句關(guān)鍵字，是可選項(xiàng)，格式：WHERE 條件表達(dá)式是指查詢的結(jié)果集合應(yīng)滿足的條件，如果某行條件為真就包括該行記錄?？梢允菃伪淼臈l件表達(dá)式，也可以是多表之間的條件表達(dá)。例 求出女學(xué)生入學(xué)成績(jī)平均分。SELECT 性別,AVG(入學(xué)成績(jī)) AS 入學(xué)成績(jī)平均分 FROM 學(xué)生 WHERE 性別=女例 列出非少數(shù)民族的學(xué)生名單。SELECT 學(xué)號(hào),姓名,性別；FROM 學(xué)生 WHERE 少數(shù)民族=.F.SELECT 學(xué)號(hào),姓名,性別 FROM 學(xué)生 ;WHERE NOT 少數(shù)民族 合肥工業(yè)大學(xué) 16 例 列出入學(xué)成績(jī)?cè)?60分到650分之間的學(xué)生名單。SELECT 學(xué)號(hào),姓名,入學(xué)成績(jī); FROM 學(xué)生; WHERE 入學(xué)成績(jī) BETWEEN 560 AND 650SELECT 學(xué)號(hào),姓名,入學(xué)成績(jī); FROM 學(xué)生;WHERE 入學(xué)成績(jī)=560 AND 入學(xué)成績(jī)90 合肥工業(yè)大學(xué) 21 5.2.3 嵌套查詢有時(shí)候一個(gè)SELECT命令無(wú)法完成查詢?nèi)蝿?wù)，需要在一個(gè)SELECT命令的WHERE子句中出現(xiàn)另一個(gè)SELECT命令，這種查詢稱(chēng)為嵌套查詢。Visual FoxPro只支持單層嵌套查詢。例 列出選修“大學(xué)計(jì)算機(jī)基礎(chǔ)”的所有學(xué)生的學(xué)號(hào)。SELECT 學(xué)號(hào) FROM 選課 WHERE 課程號(hào)=;(SELECT 課程號(hào) FROM 課程 WHERE 課程名=數(shù)據(jù)庫(kù)原理) 上述SQL語(yǔ)句執(zhí)行的是兩個(gè)過(guò)程，首先在課程表中找出“大學(xué)計(jì)算機(jī)基礎(chǔ)”的課程號(hào)（比如“021001”），然后再在選課表中找出課程號(hào)等于“021001”的記錄，列出這些記錄的學(xué)號(hào)。 合肥工業(yè)大學(xué) 22 【例4-11】查詢選修021001號(hào)課程的所有學(xué)生的姓名。(1)在命令窗口下輸入以下命令：SELECT 姓名;FROM 學(xué)生;WHERE 學(xué)號(hào) IN（SELECT學(xué)號(hào);FROM 成績(jī);WHERE 課程號(hào)=021001） 合肥工業(yè)大學(xué) 23 【例4-12】查詢?nèi)雽W(xué)成績(jī)比20050002高的所有學(xué)生的姓名、入學(xué)成績(jī)。(1)在命令窗口下輸入以下命令：SELECT 姓名,入學(xué)成績(jī);FROM 學(xué)生;WHERE 入學(xué)成績(jī) （SELECT入學(xué)成績(jī); FROM學(xué)生 ;WHERE學(xué)號(hào) = 20050002） 合肥工業(yè)大學(xué) 24 5.2.4 使用庫(kù)函數(shù)查詢 【例4-13】查詢學(xué)生總?cè)藬?shù)。(1)在命令窗口下輸入以下命令：SELECT COUNT（*）;FROM 學(xué)生 (2)得到的運(yùn)行結(jié)果如圖4-13所示。 圖4-13 合肥工業(yè)大學(xué) 25 【例4-14】查詢學(xué)習(xí)021001號(hào)課程的學(xué)生最高成績(jī)。(1)在命令窗口下輸入以下命令：SELECT MAX（成績(jī)）;FROM 成績(jī) ;WHERE 課程號(hào)=021001 合肥工業(yè)大學(xué) 26 5.2.5 查詢結(jié)果處理1. 排序輸出（ORDER）ORDER BY ASC | DESC ,ASC | DESC例 按性別順序列出學(xué)生的學(xué)號(hào)、姓名、性別、課程名及成績(jī)，性別相同的先按課程名,再按成績(jī)由高到低排序。SELECT a.學(xué)號(hào),a.姓名,a.性別,c.課程名,b.成績(jī);FROM 學(xué)生 a,選課 b,課程 c;WHERE a.學(xué)號(hào)=b.學(xué)號(hào) AND b.課程號(hào)=c.課程號(hào); ORDER BY a.性別,c.課程名,b.成績(jī) DESC注意 如果沒(méi)有指定查詢結(jié)果的顯示順序，將按物理順序輸出查詢結(jié)果?？梢杂肙RDER BY子句指定按照一個(gè)或多個(gè)屬性列的升序（ASC）或降序（DESC）重新排列查詢結(jié)果，ASC為缺省值。 合肥工業(yè)大學(xué) 27 【例4-8】查詢選修了021002號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢結(jié)果按分?jǐn)?shù)的降序排列 (1)在命令窗口下輸入以下命令：SELECT學(xué)號(hào),成績(jī);FROM成績(jī);WHERE課程號(hào)=021002;ORDER BY 成績(jī) DESC 合肥工業(yè)大學(xué) 28 2. 分組統(tǒng)計(jì)（GROUP）GROUP BY ,其中可以是字段名，SQL函數(shù)表達(dá)式，也可以是列序號(hào)。例 分別統(tǒng)計(jì)男女人數(shù)。SELECT 性別,COUNT(性別) FROM 學(xué)生 ;GROUP BY 性別3. 篩選（HAVING）篩選條件格式是：HAVING HAVING子句與WHERE功能一樣，只不過(guò)是與GROUP BY子句連用，用來(lái)指定每一分組內(nèi)應(yīng)滿足的條件。例 列出成績(jī)平均分大于80分的課程號(hào)。SELECT 課程號(hào) FROM 選課 GROUP BY 課程號(hào) HAVING AVG(成績(jī))=80 合肥工業(yè)大學(xué) 29 5.3.1 建立表結(jié)構(gòu)命令格式是：CREATE TABLE|DBF NAME FREE( (,)NULL|NOT NULLCHECK ERRORDEFAULT PRIMARY KEY | UNIQUEREFERENCES TAG (,)NULL|NOT NULLCHECK ERRORDEFAULT PRIMARY KEY | UNIQUE REFERENCES TAG)|FROM ARRAY CREATE TABLE命令建立表可以完成用表設(shè)計(jì)器完成的所有功能。簡(jiǎn)化：CREATE TABLE ( (,) ，(,) ) 5.3 數(shù) 據(jù) 定 義 合肥工業(yè)大學(xué) 30 例 利用SQL命令建立學(xué)生管理數(shù)據(jù)庫(kù)，其中包含3個(gè)表：學(xué)生表、選課表和課程表。操作步驟如下：（1）用CREATE命令建立數(shù)據(jù)庫(kù)。CREATE DATABASE D: 學(xué)生管理（2）用CREATE命令建立學(xué)生表。CREATE TABLE 學(xué)生(學(xué)號(hào) C(5) PRIMARY KEY,姓名 C(8), 入學(xué)成績(jī) N(5,1) CHECK(入學(xué)成績(jī)0) ERROR “成績(jī)應(yīng)該大于0！”)（3）建立課程表。CREATE TABLE 課程(課程號(hào) C(5) PRIMARY KEY,課程名 C(20), 學(xué)分 N(1)（4）建立選課表。CREATE TABLE 選課(學(xué)號(hào)C（5），課程號(hào) C(5)，成績(jī) I CHECK(成績(jī)=0 AND 成績(jī)=100)ERROR成績(jī)值的范圍0100！ DEFAULT 60, FOREIGN KEY 學(xué)號(hào) TAG 學(xué)號(hào) REFERENCES 學(xué)生，F(xiàn)OREIGN KEY 課程號(hào) TAG 課程號(hào) REFERENCES 課程 合肥工業(yè)大學(xué) 31注意：用SQL CREATE命令新建的表自動(dòng)在最小可用工作區(qū)打開(kāi)，并可以通過(guò)別名引用，新表的打開(kāi)方式為獨(dú)占方式。如果建立自由表（當(dāng)前沒(méi)有打開(kāi)的數(shù)據(jù)庫(kù)或使用了FREE），則很多選項(xiàng)在命令中不能使用，如NAME、CHECK、DEFAULT、FOREIGN KEY、PRIMARY KEY和REFERENCES等。 合肥工業(yè)大學(xué) 32 5.3.2 刪除表刪除表的SQL命令是：DROP TABLE DROP TABLE命令直接從磁盤(pán)上刪除所指定的表文件。5.3.3 修改表結(jié)構(gòu)修改表結(jié)構(gòu)的命令是ALTER TABLE，該命令有3種格式。格式1：增加字段ALTER TABLE ADD(,) ADD(,)例 為課程表增加一個(gè)整數(shù)類(lèi)型的學(xué)時(shí)字段。OPEN DATABASE D:學(xué)生管理ALTER TABLE 課程 ADD 學(xué)時(shí) I CHECK(學(xué)時(shí)16 ) ERROR “學(xué)時(shí)應(yīng)該大于16！” 合肥工業(yè)大學(xué) 33 格式2：修改字段屬性 ALTER TABLE ALTER (,) ALTER (,) DROP DEFAULTDROP CHECK該格式命令主要用于定義、修改和刪除有效性規(guī)則以及默認(rèn)值定義。例 修改學(xué)分字段并刪除有效性規(guī)則。ALTER TABLE 課程 ALTER學(xué)分N(3,1) DROP CHECK格式3：刪除字段與修改字段名ALTER TABLE DROP ， DROP RENAME COLUMN TO 該格式的命令可以刪除指定字段（DROP COLUMN）、修改字段名(RENAME COLUMN)、還可以修改指定表的完整性規(guī)則，包括主索引、外關(guān)鍵字、候選索引及表的合法值限定的添加與刪除。 例 刪除課程表中的學(xué)時(shí)字段。ALTER TABLE 課程 DROP COLUMN 學(xué)時(shí) 合肥工業(yè)大學(xué) 34 5.4.1 插入記錄Visual FoxPro支持兩種SQL插入命令，其格式是：格式1：INSERT INTO (字段名1,) VALUES(,)該命令在指定的表尾添加一條新記錄，其值為VALUES后面表達(dá)式的值。插入數(shù)據(jù)的格式及順序必須與表的結(jié)構(gòu)完全吻合。 例 向?qū)W生表中添加記錄。INSERT INTO 學(xué)生 VALUES(231002,陽(yáng)雨光,男,1988-09-10,.T.,上海,610,，)INSERT INTO 學(xué)生(學(xué)號(hào),姓名) VALUES(231109,李成功)格式2：INSERT INTO FROM ARRAY |FROM MEMVAR該命令在指定的表尾添加一條新記錄，其值來(lái)自數(shù)組或?qū)?yīng)的同名內(nèi)存變量。 例 已經(jīng)定義了數(shù)組A（5），A中各元素的值分別是：A（1）=231013，A（2）=張陽(yáng)，A（3）=女，A（4）=1988-09-10，A（5）=.T.。利用該數(shù)組向?qū)W生表中添加記錄。INSERT INTO 學(xué)生 FROM ARRAY A 5.4 數(shù) 據(jù) 操 縱 合肥工業(yè)大學(xué) 35 5.4.2 刪除記錄DELETE FROM ! WHERE 該命令從指定表中，根據(jù)指定的條件邏輯刪除記錄。用PACK命令將邏輯刪除的記錄從物理上刪除掉。例 將“學(xué)生”表所有男生的記錄邏輯刪除。DELETE FROM 學(xué)生 WHERE 性別=男5.4.3 更新記錄UPDATE !SET= ,= WHERE該命令用指定的新值更新記錄，對(duì)存儲(chǔ)在表中的記錄進(jìn)行修改。例 將“學(xué)生”表中胡敏杰學(xué)生的籍貫改為廣東。UPDATE 學(xué)生 SET 籍貫=廣東 WHERE 姓名=胡敏杰例 所有男生的各科成績(jī)加20分UPDATE 選課 SET 成績(jī)=成績(jī)+20 WHERE學(xué)號(hào)IN（SELECT 學(xué)號(hào) FROM 學(xué)生 WHERE 性別=男）以上命令中，用到了WHERE條件運(yùn)算符“IN”和對(duì)用SELECT語(yǔ)句 選擇出的記錄進(jìn)行數(shù)據(jù)更新。注意UPDATE一次只能在單一的表中更新記錄。