VFP第5章SQL語言的應用

《VFP第5章SQL語言的應用》由會員分享，可在線閱讀，更多相關(guān)《VFP第5章SQL語言的應用（35頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、合肥工業(yè)大學 1isualFV oxPro 合肥工業(yè)大學 2 按照美國國家標準協(xié)會（ANSI）的規(guī)定SQL是關(guān)系數(shù)據(jù)庫的標準語言。目前流行的關(guān)系數(shù)據(jù)庫管理系統(tǒng)，如Qracle、Sybase、SQL Server、Visual FoxPro等都支持SQL語言標準。SQL雖然在各種數(shù)據(jù)庫產(chǎn)品中得到了廣泛的支持，但迄今為止，它只是一種建議標準，各種數(shù)據(jù)庫產(chǎn)品中所實現(xiàn)的SQL在語法、功能等方面均略有差異。Visual FoxPro在SQL方面支持數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操縱功能。5.1 SQL語 言 概 述 合肥工業(yè)大學 3 SQL語言具有如下特點：（1）SQL是一種一體化的語言。它集數(shù)據(jù)定義、數(shù)據(jù)查

2、詢、數(shù)據(jù)操縱和數(shù)據(jù)控制功能于一體，可以獨立完成數(shù)據(jù)庫的全部操作。（2）SQL語言是一種高度非過程化的語言。只需要描述清楚用戶要“做什么”，不必指明“怎么做”，SQL語言就可以將要求交給系統(tǒng)，自動完成全部工作。（3）SQL語言非常簡潔。雖然SQL語言功能很強，但它只有9條命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT、REVOKE。另外SQL的語法也非常簡單，因此容易學習和掌握。 （4）SQL語言可以直接以命令方式交互使用，也可以嵌入到程序設(shè)計語言中以程序方式使用。 合肥工業(yè)大學 4 Visual FoxPro的SQL SELECT命

3、令的語法格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 數(shù)據(jù)庫名!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是一個命令的集合，對它進行分解。 5.2 數(shù)

4、據(jù) 查 詢 合肥工業(yè)大學 5 SQL查詢語句格式：SelectAll|Distinct|,FromLeftRightJoinOnWhereGroup ByHavingOrder ByAscDesc 其中： All：查詢結(jié)果是表的全部記錄。 Distinct：查詢結(jié)果是不包含重復行的記錄集。 From：查詢結(jié)果來源。 LeftRightJoinOn：查詢結(jié)果是多表組和的記錄集。 合肥工業(yè)大學 6 Where：查詢結(jié)果是表中滿足的記錄集 Group By：查詢結(jié)果是表按分組的記錄集。 Having：是將指定表滿足,并且按表結(jié)果組成的記錄集。 Order By：查詢結(jié)果是否按某一字段值排列。 Asc

5、：查詢結(jié)果按某一字段值升序排列。 Desc：查詢結(jié)果按某一字段值降序排列。 ：進行查詢計算函數(shù)。 ：可以是關(guān)系表達式。也可以是邏輯表達式 合肥工業(yè)大學 7 查詢計算函數(shù)的格式及功能 函數(shù)格式函數(shù)功能COUNT（*）計算記錄個數(shù)SUM（字段名）求字段名和所指定字段的值的總和AVG（字段名）求字段名所指定的平均值MAX（字段名）求字段名所指定的最大值MIN（字段名）求字段名所指定字段的最小值 合肥工業(yè)大學 8 查詢條件中常用的運算符 運算符實例=、=、=、工資現(xiàn)狀3000NOT、AND、OR工資現(xiàn)狀3000LIKE性別 LIKE “男”BETWEEN AND工資現(xiàn)狀 BETWEEN 3000 AN

6、D 5000IN職稱 IN (教授，副教授） 合肥工業(yè)大學 9 5.2.1 簡單查詢查詢僅涉及一個表，是最簡單的查詢操作 1. 查詢指定列【例4-1】查詢?nèi)w學生的學號與姓名.(1)在命令窗口下輸入以下命令：SELECT 學號,姓名;FROM 學生 合肥工業(yè)大學 10 2. 查詢?nèi)苛小纠?-3】查詢?nèi)w學生的詳細記錄. 在命令窗口下輸入以下命令：SELECT *;FROM 學生 合肥工業(yè)大學 11 基本查詢所謂基本查詢是指無條件查詢，其格式是：SELECT ALL|DISTINCT.AS ,.AS FROM 別名1,別名2其中ALL表示輸出所有記錄，包括重復記錄。DISTINCT表示輸出無重

7、復結(jié)果的記錄。當選擇多個數(shù)據(jù)庫表中的字段時，可使用別名來區(qū)分不同的表。的作用是在輸出結(jié)果中的列標題，可以不同于字段名?？梢允亲侄蚊⒈磉_式或函數(shù)。表名代表要查詢的表。要輸出全部字段用“*” 表示。 合肥工業(yè)大學 12 例 列出學生名單。OPEN DATABASE D:學生管理SELECT * FROM 學生 FROM 成績;WHERE成績60 合肥工業(yè)大學 14 4. 查詢滿足條件的元組(2)得到的運行結(jié)果如圖4-5所示。圖4-5 合肥工業(yè)大學 15 帶條件查詢WHERE是條件語句關(guān)鍵字，是可選項，格式：WHERE 條件表達式是指查詢的結(jié)果集合應滿足的條件，如果某行條件為真就包括該行記錄?？梢?/p>

8、是單表的條件表達式，也可以是多表之間的條件表達。例 求出女學生入學成績平均分。SELECT 性別,AVG(入學成績) AS 入學成績平均分 FROM 學生 WHERE 性別=女例 列出非少數(shù)民族的學生名單。SELECT 學號,姓名,性別；FROM 學生 WHERE 少數(shù)民族=.F.SELECT 學號,姓名,性別 FROM 學生 ;WHERE NOT 少數(shù)民族 合肥工業(yè)大學 16 例 列出入學成績在560分到650分之間的學生名單。SELECT 學號,姓名,入學成績; FROM 學生; WHERE 入學成績 BETWEEN 560 AND 650SELECT 學號,姓名,入學成績; FROM 學

9、生;WHERE 入學成績=560 AND 入學成績90 合肥工業(yè)大學 21 5.2.3 嵌套查詢有時候一個SELECT命令無法完成查詢?nèi)蝿?，需要在一個SELECT命令的WHERE子句中出現(xiàn)另一個SELECT命令，這種查詢稱為嵌套查詢。Visual FoxPro只支持單層嵌套查詢。例 列出選修“大學計算機基礎(chǔ)”的所有學生的學號。SELECT 學號 FROM 選課 WHERE 課程號=;(SELECT 課程號 FROM 課程 WHERE 課程名=數(shù)據(jù)庫原理) 上述SQL語句執(zhí)行的是兩個過程，首先在課程表中找出“大學計算機基礎(chǔ)”的課程號（比如“021001”），然后再在選課表中找出課程號等于“021

10、001”的記錄，列出這些記錄的學號。 合肥工業(yè)大學 22 【例4-11】查詢選修021001號課程的所有學生的姓名。(1)在命令窗口下輸入以下命令：SELECT 姓名;FROM 學生;WHERE 學號 IN（SELECT學號;FROM 成績;WHERE 課程號=021001） 合肥工業(yè)大學 23 【例4-12】查詢?nèi)雽W成績比20050002高的所有學生的姓名、入學成績。(1)在命令窗口下輸入以下命令：SELECT 姓名,入學成績;FROM 學生;WHERE 入學成績 （SELECT入學成績; FROM學生 ;WHERE學號 = 20050002） 合肥工業(yè)大學 24 5.2.4 使用庫函數(shù)查詢

11、 【例4-13】查詢學生總?cè)藬?shù)。(1)在命令窗口下輸入以下命令：SELECT COUNT（*）;FROM 學生 (2)得到的運行結(jié)果如圖4-13所示。 圖4-13 合肥工業(yè)大學 25 【例4-14】查詢學習021001號課程的學生最高成績。(1)在命令窗口下輸入以下命令：SELECT MAX（成績）;FROM 成績 ;WHERE 課程號=021001 合肥工業(yè)大學 26 5.2.5 查詢結(jié)果處理1. 排序輸出（ORDER）ORDER BY ASC | DESC ,ASC | DESC例 按性別順序列出學生的學號、姓名、性別、課程名及成績，性別相同的先按課程名,再按成績由高到低排序。SELECT

12、 a.學號,a.姓名,a.性別,c.課程名,b.成績;FROM 學生 a,選課 b,課程 c;WHERE a.學號=b.學號 AND b.課程號=c.課程號; ORDER BY a.性別,c.課程名,b.成績 DESC注意 如果沒有指定查詢結(jié)果的顯示順序，將按物理順序輸出查詢結(jié)果。可以用ORDER BY子句指定按照一個或多個屬性列的升序（ASC）或降序（DESC）重新排列查詢結(jié)果，ASC為缺省值。 合肥工業(yè)大學 27 【例4-8】查詢選修了021002號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)的降序排列 (1)在命令窗口下輸入以下命令：SELECT學號,成績;FROM成績;WHERE課程號=0

13、21002;ORDER BY 成績 DESC 合肥工業(yè)大學 28 2. 分組統(tǒng)計（GROUP）GROUP BY ,其中可以是字段名，SQL函數(shù)表達式，也可以是列序號。例 分別統(tǒng)計男女人數(shù)。SELECT 性別,COUNT(性別) FROM 學生 ;GROUP BY 性別3. 篩選（HAVING）篩選條件格式是：HAVING HAVING子句與WHERE功能一樣，只不過是與GROUP BY子句連用，用來指定每一分組內(nèi)應滿足的條件。例 列出成績平均分大于80分的課程號。SELECT 課程號 FROM 選課 GROUP BY 課程號 HAVING AVG(成績)=80 合肥工業(yè)大學 29 5.3.1

14、建立表結(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è)計器完成的所有功能。簡化：CREATE TABLE ( (,) ，(,) ) 5.3 數(shù) 據(jù) 定 義 合肥工業(yè)大學 30 例 利用SQL命令建立學生管理數(shù)據(jù)庫，其中包含

15、3個表：學生表、選課表和課程表。操作步驟如下：（1）用CREATE命令建立數(shù)據(jù)庫。CREATE DATABASE D: 學生管理（2）用CREATE命令建立學生表。CREATE TABLE 學生(學號 C(5) PRIMARY KEY,姓名 C(8), 入學成績 N(5,1) CHECK(入學成績0) ERROR “成績應該大于0！”)（3）建立課程表。CREATE TABLE 課程(課程號 C(5) PRIMARY KEY,課程名 C(20), 學分 N(1)（4）建立選課表。CREATE TABLE 選課(學號C（5），課程號 C(5)，成績 I CHECK(成績=0 AND 成績=100

16、)ERROR成績值的范圍0100！ DEFAULT 60, FOREIGN KEY 學號 TAG 學號 REFERENCES 學生，F(xiàn)OREIGN KEY 課程號 TAG 課程號 REFERENCES 課程 合肥工業(yè)大學 31注意：用SQL CREATE命令新建的表自動在最小可用工作區(qū)打開，并可以通過別名引用，新表的打開方式為獨占方式。如果建立自由表（當前沒有打開的數(shù)據(jù)庫或使用了FREE），則很多選項在命令中不能使用，如NAME、CHECK、DEFAULT、FOREIGN KEY、PRIMARY KEY和REFERENCES等。 合肥工業(yè)大學 32 5.3.2 刪除表刪除表的SQL命令是：DR

17、OP TABLE DROP TABLE命令直接從磁盤上刪除所指定的表文件。5.3.3 修改表結(jié)構(gòu)修改表結(jié)構(gòu)的命令是ALTER TABLE，該命令有3種格式。格式1：增加字段ALTER TABLE ADD(,) ADD(,)例 為課程表增加一個整數(shù)類型的學時字段。OPEN DATABASE D:學生管理ALTER TABLE 課程 ADD 學時 I CHECK(學時16 ) ERROR “學時應該大于16！” 合肥工業(yè)大學 33 格式2：修改字段屬性 ALTER TABLE ALTER (,) ALTER (,) DROP DEFAULTDROP CHECK該格式命令主要用于定義、修改和刪除有效

18、性規(guī)則以及默認值定義。例 修改學分字段并刪除有效性規(guī)則。ALTER TABLE 課程 ALTER學分N(3,1) DROP CHECK格式3：刪除字段與修改字段名ALTER TABLE DROP ， DROP RENAME COLUMN TO 該格式的命令可以刪除指定字段（DROP COLUMN）、修改字段名(RENAME COLUMN)、還可以修改指定表的完整性規(guī)則，包括主索引、外關(guān)鍵字、候選索引及表的合法值限定的添加與刪除。 例 刪除課程表中的學時字段。ALTER TABLE 課程 DROP COLUMN 學時 合肥工業(yè)大學 34 5.4.1 插入記錄Visual FoxPro支持兩種SQ

19、L插入命令，其格式是：格式1：INSERT INTO (字段名1,) VALUES(,)該命令在指定的表尾添加一條新記錄，其值為VALUES后面表達式的值。插入數(shù)據(jù)的格式及順序必須與表的結(jié)構(gòu)完全吻合。 例 向?qū)W生表中添加記錄。INSERT INTO 學生 VALUES(231002,陽雨光,男,1988-09-10,.T.,上海,610,，)INSERT INTO 學生(學號,姓名) VALUES(231109,李成功)格式2：INSERT INTO FROM ARRAY |FROM MEMVAR該命令在指定的表尾添加一條新記錄，其值來自數(shù)組或?qū)耐麅?nèi)存變量。 例 已經(jīng)定義了數(shù)組A（5），

20、A中各元素的值分別是：A（1）=231013，A（2）=張陽，A（3）=女，A（4）=1988-09-10，A（5）=.T.。利用該數(shù)組向?qū)W生表中添加記錄。INSERT INTO 學生 FROM ARRAY A 5.4 數(shù) 據(jù) 操 縱 合肥工業(yè)大學 35 5.4.2 刪除記錄DELETE FROM ! WHERE 該命令從指定表中，根據(jù)指定的條件邏輯刪除記錄。用PACK命令將邏輯刪除的記錄從物理上刪除掉。例 將“學生”表所有男生的記錄邏輯刪除。DELETE FROM 學生 WHERE 性別=男5.4.3 更新記錄UPDATE !SET= ,= WHERE該命令用指定的新值更新記錄，對存儲在表中的記錄進行修改。例 將“學生”表中胡敏杰學生的籍貫改為廣東。UPDATE 學生 SET 籍貫=廣東 WHERE 姓名=胡敏杰例 所有男生的各科成績加20分UPDATE 選課 SET 成績=成績+20 WHERE學號IN（SELECT 學號 FROM 學生 WHERE 性別=男）以上命令中，用到了WHERE條件運算符“IN”和對用SELECT語句 選擇出的記錄進行數(shù)據(jù)更新。注意UPDATE一次只能在單一的表中更新記錄。