結(jié)構(gòu)化查詢語言SQL

1、結(jié)構(gòu)化查詢語言（SQL）SQL概述 SQL的內(nèi)容在筆試和上機考試中均占到大約30%的比例，此外它還是查詢和視圖的基礎(chǔ)，因此是學(xué)習(xí)的重點也是難點。SQL是結(jié)構(gòu)化查詢語言Structure Query Language的縮寫。SQL包含了查詢功能、數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制功能，在VFP中沒有提供數(shù)據(jù)控制功能。SQL主要特點1 SQL是一種一體化語言。2 SQL是一種高度非過程化的語言。3 SQL語言非常簡潔。4 SQL語言可直接以命令方式交互使用，也可嵌入到程序設(shè)計語言中以程序方式使用。查詢功能SELECT命令的特點：1 可以自動打開數(shù)據(jù)庫、表文件加以查詢，而不需要事先用OPEN DATABA

2、SE或USE命令打開。2 可以直接選取數(shù)據(jù)表中的數(shù)據(jù)，而不需要事先用SET RELATION命令建立關(guān)聯(lián)。3 當需要的索引文件不存在時，會自動建立暫存索引文件，以支持快速搜索技術(shù)（Rushmore）來查詢。4 其查詢結(jié)果可輸出到文件、表、屏幕或報表上，還可以轉(zhuǎn)換成統(tǒng)計圖表。命令格式：SELECT FROM WHERE 可與 LIST FIELDS FOR 對照學(xué)習(xí)。關(guān)系操作：投影，選擇，聯(lián)接。說明：功能強大，語法靈活；要處理的數(shù)據(jù)表無須事先打開，通過FROM子句指明并打開。1 SELECT 短語：說明要查詢的數(shù)據(jù)；對應(yīng)的關(guān)系操作為投影，類似于FIELDS子句。2 FROM 短語：說明要查詢的數(shù)

3、據(jù)來自哪個或哪些表，可對單個表或多個表進行查詢；3 WHERE 短語：說明查詢條件；對應(yīng)的關(guān)系操作為選擇，類似于FOR子句。如是多表查詢還可能過該子句指明聯(lián)接條件，進行聯(lián)接。4 GROUP BY 短語：用于對查詢結(jié)果進行分組，可利用它進行分組匯總；類似于TOTAL命令。5 HAVING 短語：跟隨GROUP BY 使用，它用來限定分組必須滿足的條件；6 ORDER BY 短語：用于對查詢的結(jié)果進行排序；類似于SORT命令。示例數(shù)據(jù)庫：學(xué)生管理，包括三個表：學(xué)生，課程，選課（通過該表反映“學(xué)生”與“課程”之間“多對多”的聯(lián)系。問題：請同學(xué)們分別指出三個表的主關(guān)鍵字。一、 簡單查詢 簡單查詢基于單

4、個表。例：1查詢學(xué)生信息SELE * FROM 學(xué)生 &&“*”號代表所有列。類似于：USE 學(xué)生LIST2查詢男生學(xué)生的學(xué)號、姓名及生日SELE 學(xué)號,姓名,生日 FROM 學(xué)生 WHERE 性別=”男”類似于USE 學(xué)生LIST學(xué)號,姓名,生日 FOR性別=”男”3.查詢學(xué)分大于5的課程的信息SELE * FROM 課程 WHERE 學(xué)分>5類似于USE 學(xué)生LIST FOR 學(xué)分>5 4、查詢學(xué)分不在6到9之間的Sele * from 課程 where 學(xué)分 not between 6 and 9二、 簡單聯(lián)接查詢 聯(lián)接是關(guān)系的基本操作之一，聯(lián)接查詢基于多個關(guān)

5、系的查詢。例：查詢男生學(xué)生的選課信息，包括姓名，學(xué)號及成績。分析：本例的查詢結(jié)果包括兩個表“學(xué)生”與“選課”的屬性，適用于聯(lián)接查詢。SELE 姓名,選課.學(xué)號,成績; FROM 學(xué)生,選課;WHERE 學(xué)生.學(xué)號=選課.學(xué)號AND 性別=”男” 說明：如果命令太長一行寫不下可在行末加分號“：”表續(xù)行選課.學(xué)號： 因為兩個表中均有“學(xué)號”字段，所以必須指明所屬表。學(xué)生.學(xué)號=選課.學(xué)號：連接條件。還可使用如下SQL語句：SELE 姓名,選課.學(xué)號,成績;FROM 學(xué)生 JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號;WHERE 性別=”男”說明：JOIN ON ：建立表與表之間的聯(lián)接。自連接：se

6、le S.雇員姓名,"領(lǐng)導(dǎo)",E.雇員姓名 from 雇員 S,雇員 E where S.雇員號=E.經(jīng)理三、 嵌套查詢嵌套查詢是另一類基于多個關(guān)系的查詢，此類查詢所要求的結(jié)果出自一個關(guān)系，但相關(guān)的條件卻涉及多個關(guān)系。1、例：查詢女生學(xué)生選課信息，包括學(xué)號，課程號，成績。SELE *;FROM 選課;WHERE 學(xué)號 IN (SELE 學(xué)號 FROM 學(xué)生 WHERE 性別=”女”)說明：IN：相當于集合運算符。還可使用如下SQL語句：簡單聯(lián)接查詢SELE 選課.學(xué)號,課程號,成績;FROM 選課 JOIN 學(xué)生 ON 選課.學(xué)號=學(xué)生.學(xué)號;WHERE 性別=”女”2、查

7、詢有“硬盤”訂購需求的訂購單明細記錄：這個例子要求查詢訂購單明細表中的記錄，而查詢條件是產(chǎn)品標的名稱，Sele * from 訂購單明細 where 產(chǎn)品號 IN；（sele 產(chǎn)品號 from 產(chǎn)品 where 名稱=“硬盤”）3、排除法查詢的例子：查詢所有訂購數(shù)量（即訂購單中每個訂購項目的數(shù)量）都在50以上的訂購單的信息Sele * from 訂購單 where 訂單號 not in；（sele 訂單號 from 訂購單明細 where 數(shù)量=50）Sele * from 訂購單 where 訂單號 not in；（sele 訂單號 from 訂購單明細 where 數(shù)量=50） and 訂

8、單號 in （sele 訂單號 from 訂購單明細）4、找出和某某產(chǎn)品同等價位的所有產(chǎn)品信息：內(nèi)外層查詢的同一個表，內(nèi)層查詢查處單價，外層查詢根據(jù)這個單價查詢產(chǎn)品信息Sele * from 產(chǎn)品 where 單價=；（sele 單價 from 產(chǎn)品 where 規(guī)格說明=“某某產(chǎn)品”） 5、內(nèi)外層互相查詢嵌套查詢四、 特殊運算符 1 BETWEEN AND 意為“和之間”例：查詢成績在80分到90分之間的選課信息。SELE * FROM 選課 WHERE 成績 BETWEEN 80 AND 90等價于：SELE * FROM 選課 WHERE 成績>=80 AND 成績<=90例

9、：查詢成績不在80分到90分之間的選課信息。SELE * FROM 選課 WHERE 成績 NOT BETWEEN 80 AND 902 LIKE 是字符串匹配運算符，通配符“%”表示0個或多個字符，“_”表示一個字符。注意：不是“*”和“？”，不正確！例：從學(xué)生關(guān)系中查詢姓“黃”的學(xué)生信息。SELE * FROM 學(xué)生 WHERE 姓名 LIKE “黃%”不能寫成：SELE * FROM 學(xué)生 WHERE 姓名="黃%" 例：從學(xué)生關(guān)系中查詢不姓“黃”的學(xué)生信息。SELE * FROM 學(xué)生 WHERE 姓名 NOT LIKE “黃%”例：SELE * FROM 學(xué)生 W

10、HERE 姓名 LIKE "黃_"SELE * FROM 學(xué)生 WHERE 姓名 LIKE "黃_"五、 排序SQL SELECT 可將查詢結(jié)果排序。排序的短語格式如下：ORDER BY Order_Item ASC|DESC, Order_Item ASC|DESC說明：可按升序或降序排序，允許按一列或多列排序。例：按學(xué)分進行升序查詢課程信息。SELE * FROM 課程 ORDER BY 學(xué)分 ASC例：先按學(xué)分進行升序排序，學(xué)分相同的再按課程名進行降序排序并輸出全部課程信息。SELE * FROM 課程 ORDER BY 學(xué)分,課程名 DESC注：

11、ORDER BY 對最終的查詢（外查詢）結(jié)果進行排序，不能在子查詢（內(nèi)查詢）中使用此短語。六、 簡單計算查詢 SQL不但具有一般的檢索能力，而且還有計算方式的檢索。用于計算檢索的函數(shù)有：1 COUNT（*）：計數(shù) 自變量通常為數(shù)值型2 SUM（）：求和3 AVG（）：算平均值4 MAX（）：求最大值5 MIN（）：求最小值上面的函數(shù)可用在SELECT短語中對查詢結(jié)果進行計算。例：查詢學(xué)號為“03”的學(xué)生選修課程的考試成績總分和平均分。SELE SUM(成績),AVG(成績) FROM 選課 WHERE 學(xué)號=”03”SELE SUM(成績) 總分,AVG(成績) 平均分 FROM 選課 WHE

12、RE 學(xué)號=”03”SELE 姓名,SUM(成績) AS 總分,AVG(成績) AS 平均分;FROM 學(xué)生 JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號;WHERE 學(xué)生.學(xué)號=”03”七、 分組與計算查詢 利用GROUP BY 子句進行分組計算查詢使用得更加廣泛。GROUP BY短語格式如下：GROUP BY GroupColumn,GroupColunmHAVING FilterCondition說明：1 可按一列或多列分組，還可以用HAVING進一步限定分組的條件。2 GROUP BY子句一般跟在WHERE子句之后，沒有WHERE子句時，跟在FROM子句之后；HAVING子句必須跟在G

13、ROUP BY 之后，不能單獨使用。在查詢中是先用WHERE子句限定元組，然后進行分組，最后再用HAVING子句限定分組。例：求每個學(xué)生選課的考試成績平均分。SELE 學(xué)號,AVG(成績) FROM 選課 GROUP BY 學(xué)號說明：在此查詢中，選按學(xué)號屬性進行分組，然后再計算每個學(xué)號的平均成績。例：在選課表中求每個選課門數(shù)為4門的學(xué)生的總分和平均分。SELE 學(xué)號,SUM(成績) AS 總分,AVG(成績) AS 平均分;FROM 選課;GROUP BY 學(xué)號 HAVING COUNT(*)=4SELE 姓名,SUM(成績) AS 總分,AVG(成績) AS 平均分;FROM 學(xué)生 JOIN

14、 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號;WHERE 性別=”女”;GROUP BY 選課.學(xué)號 HAVING COUNT(*)=4例：求平均成績在80分以上的各課程的課程號與平均成績。SELE 課程號,AVG(成績) FROM 選課; GROUP BY 課程號 HAVING AVG(成績)>80八、使用空值進行查詢 空值的概念：空值就是缺值或還沒有確定值，不能把它理解為任何意義的數(shù)據(jù)。比如表示價格的一個字段值，空值表示沒有定價，而數(shù)值0可能表示免費?？罩蹬c空（或空白）字符串、數(shù)值0等具有不同的含義。例：假設(shè)在選課中有些學(xué)生某門課程還沒有考試，則成績?yōu)榭?。試找出尚未考試的選課信息。SELE

15、* FROM 選課 WHERE 成績 IS NULL注：不能寫成“=NULL”例：試找出成績不為空的選課信息。SELE * FROM 選課 WHERE 成績 IS NOT NULL九、別名與自聯(lián)接查詢 在聯(lián)接操作中，要使用關(guān)系名作前綴，為簡單起見，SQL允許在FROM短語中為關(guān)系名定義別名。格式為：<關(guān)系名><別名>例：查詢選課信息中的姓名，課程名，成績。SELE 姓名,課程名,成績;FROM 學(xué)生 S,課程 C,選課 SC;WHERE S.學(xué)號=SC.學(xué)號 AND C.課程號=SC.課程號說明：在上面的例子中，別名并不是必須的，但是在關(guān)系的自聯(lián)接操作中，別名則是必不可

16、少的。SQL不僅可以對多個關(guān)系實行聯(lián)接操作，也可將同一關(guān)系與其自身進行聯(lián)接，這種聯(lián)接就稱為自聯(lián)接。在這種自聯(lián)接操作關(guān)系上，本質(zhì)上存在著一種特殊的遞歸聯(lián)系，也就是關(guān)系中的一些元組，根據(jù)出自同一值域的兩個不同的屬性，可以與另外一些元組有一種對應(yīng)關(guān)系（一對多的聯(lián)系）。注：元組即記錄。例：試查詢先修課的課程名在本例中，先修課號與課程號出自同一值域，會涉及自聯(lián)接查詢。SELE DISTINCT C2.先修課號 C1.課程名;FROM 課程 C1,課程 C2;WHERE C1.課程號=C2.先修課號十、內(nèi)外層相關(guān)嵌套查詢 前面討論的嵌套查詢是外層查詢依賴于內(nèi)層查詢的結(jié)果，而內(nèi)層查詢與外層查詢無關(guān)。但有時也

17、需要內(nèi)、外層互相關(guān)的查詢，這時內(nèi)層查詢的條件需要外層查詢提供值，而外層查詢的條件需要內(nèi)層查詢的結(jié)果。例：查詢只有一個學(xué)生選修的課程的學(xué)號，課程號及成績。SELE *;FROM 選課 SC1;WHERE SC1.課程號 NOT IN； (SELE 課程號 FROM 選課 SC2 WHERE SC1.學(xué)號<>SC2.學(xué)號)SELE * FROM 選課 SC1;WHERE NOT EXIST (SELE * FROM 選課 SC2 ;WHERE SC1.學(xué)號<>SC2.學(xué)號 AND SC2.課程號=SC1.課程號)十一、用量詞和謂詞的查詢前面已學(xué)過NOT IN運算符，此處還有

18、兩種與子查詢相關(guān)的運算符。格式1：<表達式><比較運算符>ANY|ALL|SOME(子查詢)格式2：NOT EXISTS (子查詢)說明：1ANY、ALL和SOME為量詞，ANY和SOME是同義詞，在進行比較運算時只要子查詢中有一行能使結(jié)果為真，則結(jié)果為真；ALL則要求子查詢中的所有行都使結(jié)果為真時，結(jié)果才為真。2EXISTS為謂詞，EXISTS和NOT EXISTS是用來檢查在子查詢中是否有結(jié)果返回，也就是存在元組或不存在元組。例：查詢選修有課程的學(xué)生的學(xué)號，姓名，性別及生日。（看懂）SELE * FROM 學(xué)生 WHERE EXIST (SELE * FROM 選課

19、 WHERE 學(xué)生.學(xué)號=選課.學(xué)號)注：本例中內(nèi)層查詢引用了外層查詢的表，只有這樣使用謂詞EXISTS或NOT EXISTS才有意義。例：確定哪些客戶目前沒有訂購單：這里的查詢是客戶沒有訂購單子記錄或不存在訂購單子記錄Sele * from 客戶 where not exists ；（sele * from 訂購單 where 客戶號=客戶.客戶號） ：此查詢等價于 sele * from 客戶 where 客戶號 not in（sele 客戶號 from 訂購單）例：查詢學(xué)分最高的課程信息。SELE * FROM 課程 WHERE 學(xué)分>=ALL (SELE 學(xué)分 FROM 課程)S

20、ELE * FROM 課程 WHERE NOT 學(xué)分<ANY (SELE 學(xué)分 FROM 課程)SELE * FROM 課程 WHERE NOT 學(xué)分<SOME (SELE 學(xué)分 FROM 課程)SELE * FROM 課程 WHERE 學(xué)分=(SELE MAX(學(xué)分) FROM 課程)例：查詢學(xué)分不是最高的課程信息。SELE * FROM 課程 WHERE 學(xué)分<ANY (SELE 學(xué)分 FROM 課程)SELE * FROM 課程 WHERE 學(xué)分<SOME (SELE 學(xué)分 FROM 課程)十二、超聯(lián)接查詢 在兩個表的超聯(lián)接查詢中，首先保證一個表中滿足條件的元組都

21、在結(jié)果中，然后將滿足聯(lián)接條件的元組與另一個表中的元組進行聯(lián)接，不滿足聯(lián)接條件的則將應(yīng)來自另一表的屬性置為空值。語法：SELE FROM Talbel INNER|LEFT|RIGHT|FULL JOIN Table ON Join ConditionWHERE 說明：1 INNER JOIN：等價于JOIN，為普通聯(lián)接。2 ON Join Condition：用于指定聯(lián)接條件。例：比較幾種聯(lián)接方式。SELE 姓名,課程號,成績 FROM 學(xué)生 JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號SELE 姓名,課程號,成績 FROM 學(xué)生 LEFT JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號SELE

22、 姓名,課程號,成績 FROM 學(xué)生 RIGHT JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號SELE 姓名,課程號,成績 FROM 學(xué)生 FULL JOIN 選課 ON 學(xué)生.學(xué)號=選課.學(xué)號十三、集合并運算 可將兩個SELECT語句的查詢結(jié)果通過并運算合并成一個查詢結(jié)果。為進行并運算，要求兩個查詢結(jié)果具有相同的字段個數(shù)，并且對應(yīng)字段的值要出自同一值域，也就是具有相同的數(shù)據(jù)類型和取值范圍。例：查詢學(xué)號為01與02的選課信息。SELE * FROM 選課 WHERE 學(xué)號=”01”;UNION;SELE * FROM 選課 WHERE 學(xué)號=”02”十四、VFP中有關(guān)SQL SELECT的幾個

23、特殊選項 1 顯示部分結(jié)果格式：TOP n PERCENT功能：只需要滿足條件的前幾個記錄。說明：（1） n是1至32767之間的整數(shù)，說明顯示前幾個記錄。（2） 當使用PERCENT時，說明顯示結(jié)果中前百分之幾的記錄。例：顯示學(xué)分最低的前2項課程記錄。SELE * TOP 2 FROM 課程 ORDER BY 學(xué)分 DESC &&通常與ORDER BY子句連用2 將結(jié)果存放在數(shù)組中 格式：INTO ARRAY ArrayName3 將結(jié)果存放在臨時文件中格式：INTO CURSOR CursorName說明：臨時表中一個只讀的DBF文件，當查詢結(jié)束后該臨時文件是當前文件，可像

24、一般的DBF文件一樣使用，當關(guān)閉文件時該文件將自動刪除。4 將結(jié)果存放在永久表中，通過該子句可實現(xiàn)表的復(fù)制。格式：INTO TABLE|DBF TableName5 將結(jié)果存放到文本文件中格式：TO FILE FileName ADDITIVE6 將結(jié)果直接輸出到打印機格式：TO PRINTER PROMPT習(xí)題：P131/ 2 P132/ 4,5,6第三節(jié) 數(shù)據(jù)操作功能 一、 插入 命令格式1：INSERT INTO 數(shù)據(jù)表名 (字段名1,字段名2,.) VALUES (表達式1,表達式2.)功能：在指定表尾添加一條新記錄，其值為VALUES后面的表達式的值。命令格式2：INSERT INT

25、O 數(shù)據(jù)表名 FROM ARRAY ArrayName|FROM MEMVAR功能：向指定表中添加一條新記錄，其值來自于數(shù)組或?qū)?yīng)的同名內(nèi)存變量。例：向課程表中插入記錄：（”05”,”數(shù)理邏輯”,”6”,”03”）INSERT INTO 課程 VALUES(”05”,”數(shù)理邏輯”,6,”03”)說明：1 如未使用字段名指明，則VALUES中的表達式數(shù)目必須與表中的字段數(shù)相同，而且相應(yīng)的數(shù)據(jù)類型必須一致。2 如果表中設(shè)定了主索引，則插入記錄時只能用該SQL INSERT 命令而不能使用APPEND或INSERT命令。3 注意插入數(shù)據(jù)的數(shù)據(jù)類型必須與表設(shè)計時的類型一致。二、 更新 命令格式：UPD

26、ATE TableName SET 字段名1=表達式1，字段名2=表達式2. WHERE Condition類似于REPLACE命令。通常嵌入在循環(huán)結(jié)構(gòu)內(nèi)，用另一個表的數(shù)據(jù)來修改本表。例：將課程關(guān)系中課程號為“01”的課程的學(xué)分加1分。UPDATE 課程 SET 學(xué)分=學(xué)分+1 WHERE 課程號=”01”習(xí)題：P148/2 P150/6三、刪除 命令格式：DELETE FROM TableName WHERE Condition說明：類似于VFP的DELE . FOR . 命令。1 若無WHERE子句會刪除表中的全部記錄。2 此命令為邏輯刪除。用PACK命令進行物理刪除。例：刪除課程關(guān)系中課

27、程號為“05”的元組。DELE FROM課程 WHERE 課程號=”05”習(xí)題：P156/25第二節(jié) 數(shù)據(jù)定義功能 一、表的定義 命令格式：CREATE TABLE 數(shù)據(jù)表名(字段名1 字段類型(字段寬度,小數(shù)位數(shù)) NULL|NOT NULLCHECK ERRORDEFAULTPRIMARY KEY|UNIQUE字段名2. .)說明：例：用SQL CREATE命令建立學(xué)生2數(shù)據(jù)表CREATE TABLE 學(xué)生2(學(xué)號C(2) NOT NULL PRIMARY KEY ,姓名 C(8),;性別 C(2) CHECK 性別=”男” OR 性別=”女” ERROR “性別只能是男或女” DEFAULT “男”,生日 D)習(xí)題：P157/2二、表的刪除命令格式：DROP TABLE TableName功能：直接從磁盤上刪除指定的表。例：刪除上例中的表“學(xué)生2”DROP TABLE 學(xué)生2習(xí)題：P148/1三、 表結(jié)構(gòu)的修改命令格式1：ALTER TABLE TableName ADD|ALTER COLUMN 字段名1. 說明：此格式可添加（ADD）新的字段或