1、第6章 SQL語言以及查詢和視圖設計,6.1 概述 1SQL語言簡介 SQL：Structured Query Language的縮寫，即結構化查詢語言，是關系數(shù)據(jù)庫的標準語言。所有的關系數(shù)據(jù)庫管理系統(tǒng)都支持SQL。 SQL語言的主要特點： SQL語言非常簡潔，功能很強。 SQL是一種一體化的語言。 SQL語言是高度非過程化的語言。 SQL語言既可以直接以命令方式交互使用,也可以嵌入到程序設計語言中使用。 VFP支持SQL的數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操縱功能。,SQL命令動詞,2本章用例說明 “教學管理” 數(shù)據(jù)庫和 “職工培訓”數(shù)據(jù)庫。 “職工培訓”數(shù)據(jù)庫包含如下四個表： 單位（單位號 C(6)

2、，單位名 C(10)，學分指標 I） 職工（職工號 C(4)，單位號 C(6)，性別 C(2)，工齡 N(3,0)） 成績1（課程號 C(4)，職工號 C(4)，成績 N(4,0)，考核時間 D） 課程1（課程號 C(4)，課程名 C(16)，學分 I） 四個表相互之間的聯(lián)系是：單位-職工(1：N) 、職工-成績1 (1：N)、課程1-成績1 (1：N) 各表主關鍵字。,6.2.1 表的定義 較完整的SQL CREATE TABLE命令格式是： CREATE TABLE | DBF NAME FREE ( 類型(字段寬度，小數(shù)位數(shù))NULL|NOT NULL CHECK ERROR DEFAU

3、LT PRIMARY KEY | UNIQUE REFERENCES TAG NOCPTRANS ,字段名2 ,PRIMARY KEY TAG ,UNIQUE TAG ，F(xiàn)OREIGN KEY TAG NODUP REFERENCES TAG ，CHECK ERROR ) |FROM ARRAY ,6.2 SQL的數(shù)據(jù)定義功能,例6-1 用命令建立“職工培訓”數(shù)據(jù)庫,然后用SQL CREATE命令建立“單位”表。 CREATE DATABASE 職工培訓 CREATE TABLE 單位(單位號 C(6) PRIMARY KEY,單位名 C(10),; 學分指標 I CHECK(學分指標0) E

4、RROR 學分必須大于0!) PRIMARY KEY：說明“單位號”是主關鍵字(主索引)； CHECK：說明“學分指標”字段的有效性規(guī)則(學分指標0)； ERROR：指定當違反該有效性規(guī)則時的出錯提示信息。,例6-2 用SQL CREATE命令建立“職工”表。 CREATE TABLE 職工( 職工號 C(4) PRIMARY KEY,單位號 C(6),; 性別 C(2) CHECK(性別=男 OR 性別=女) ERROR 性別只能是男或女! DEFAULT 男,工齡 N(3,0) CHECK(工齡=0) ERROR 工齡必須大于0!,; FOREIGN KEY 單位號 TAG 單位號 REF

5、ERENCES 單位) 其中：FOREIGN KEY 單位號 TAG 單位號 REFERENCES 單位表示要建立本表（職工表）與單位表的聯(lián)系；其中“FOREIGN KEY 單位號”表示在本表單位號字段上建立普通索引，同時說明該字段是聯(lián)接字段，“TAG 單位號 REFERENCES 單位”表示引用單位表的主索引“單位號”與單位表建立聯(lián)系。,例6-3 用SQL CREATE命令建立“課程1”表和“成績1”表。 創(chuàng)建“課程1”表： CREATE TABLE 課程1(課程號 C(4) PRIMARY KEY,課程名 C(16),學分 I) 創(chuàng)建“成績1”表： CREATE TABLE 成績1(課程號

6、 C(4),職工號 C(4),; 成績 N(4,0) NULL DEFAULT .NULL.,考核時間D NULL DEFAULT .NULL.,; FOREIGN KEY 職工號 TAG 職工號 REFERENCES 職工,; FOREIGN KEY 課程號 TAG 課程號 REFERENCES 課程1) 小結：通過SQL CREATE命令不僅可以建立表，也可以建立起表之間的聯(lián)系。在此基礎上，可以利用第5章介紹的方法進行參照完整性設置，從而進一步完善數(shù)據(jù)庫的設計。,SQL命令格式：DROP TABLE 功能：該命令直接從磁盤上刪除指定的dbf文件。 說明：要刪除數(shù)據(jù)庫表時,最好應使所屬數(shù)據(jù)庫

7、是當前打開的數(shù)據(jù)庫。才能在從磁盤上刪除dbf文件的同時，從數(shù)據(jù)庫中刪除表。如果所屬數(shù)據(jù)庫不是當前數(shù)據(jù)庫，則雖然從磁盤上刪除了dbf文件，但記錄在數(shù)據(jù)庫dbc文件中的信息沒有刪除，以后會出現(xiàn)錯誤提示。,6.2.2 表的刪除,6.2.3 表結構的修改,ADD 新字段名及屬性,ALTER舊字名段,新屬性 Set(drop) check Set(drop) default,DROP 字段名,ADD(DROP) PRIMARY KEY 主關鍵字 TAG 標識名 ADD(DROP) UNIQUE 候選關鍵字 TAG 標識名 ADD(DROP) FOREIGN KEY 外部關鍵字 RENAME 舊字段名 T

8、O 新字段名,ALTER TABLE 表名,例6-4 為“職工”表增加一個年齡字段。 ALTER TABLE 職工 ADD 年齡 I ; CHECK 年齡18 AND 年齡16 AND 年齡=70 ERROR 年齡超范圍（16年齡70）！ 例6-7 刪除“職工”表年齡字段的有效性規(guī)則。 ALTER TABLE 職工 ALTER 年齡 DROP CHECK,表結構的修改舉例,例6-8 將“成績1”表中的成績字段名改為分數(shù)。 ALTER TABLE 成績1 RENAME COLUMN 成績 TO 分數(shù) 例6-9 刪除“職工”表中的年齡字段。 ALTER TABLE 職工 DROP COLUMN 年

9、齡 例6-10 將“成績1”表的課程號和職工號定義為候選索引(候選關鍵字),并使用索引標識名“職工課程”。 ALTER TABLE 成績1 ADD UNIQUE 職工號+課程號 TAG 職工課程 如果定義為主索引，只需要把UNIQUE 改為PRIMARY KEY即可。 例6-11 刪除“成績1”表的表的候選索引“職工課程”。 ALTER TABLE 成績1 DROP UNIQUE TAG 職工課程,主要包括數(shù)據(jù)的插入、更新和刪除三個方面的操作。 6.3.1 插入 VFP支持兩種格式的插入命令，格式1是標準格式，格式2是VFP的專有格式。 格式1：INSERT INTO (,) VALUES(,

10、) 格式2：INSERT INTO FROM ARRAY |FROM MEMVAR 其中： ：指定要插入記錄的表。VALUES(,)給出具體插入的記錄值。VALUES（）是必須的。若插入的不是完整的記錄,應在表名字后用（）指定要插入值的字段。 格式2中，F(xiàn)ROM ARRAY 表示要插入的記錄值來自指定的數(shù)組；FROM MEMVAR表示要插入的記錄值來自于與表中字段同名的內存變量，如果同名的變量不存在，那么相應的字段為默認值或空。,6.3 SQL的操縱功能,例6-12 用SQL的INSERT命令，在“單位”表中插入以下兩個元組：(”100001”,”物理學院”,300)，(”100002”,50

11、0) 命令如下： INSERT INTO 單位 VALUES (100001, 物理學院,300) INSERT INTO 單位(單位號,學分指標) VALUES (100002, 500) 其中，插入的第二條記錄必須指定要插入的字段名。,SQL命令格式： UPDATE SET =,字段名2=WHERE 一般使用WHERE子句指定條件，即滿足條件的記錄才被更新。一次可以更新多個字段。如果不使用WHERE子句,則更新全部記錄。 例6-15 給“職工”表中所有職工增加2年工齡。 命令如下：UPDATE 職工 SET 工齡=工齡+2 如果僅給單位號是“100001”的職工加2年工齡，則命令為： UP

12、DATE 職工 SET 工齡=工齡+2 WHERE 單位號=100001,6.3.2 更新,SQL命令格式： DELETE FROM WHERE 這里FROM指定從哪個表中刪除記錄，WHERE指定被刪除的記錄應滿足的條件，如果不使用WHERE子句，則刪除該表中的全部記錄。 例6-16 用 SQL的DELETE命令刪除“職工”表中職工號是1001的元組。 命令如下： DELETE FROM 職工 WHERE 職工號=1001 注意：在VFP中SQL DELETE命令同樣是邏輯刪除記錄，如果要物理刪除記錄需要使用PACK命令。,6.3.3 刪除,6.4.1 SQL SELECT命令的格式 查詢功能

13、是SQL語言的核心功能，其基本形式由SELECT-FROM-WHERE查詢塊組成，多個查詢塊可以嵌套執(zhí)行。 VFP中SQL SELECT命令的格式如下： SELECT ALL|DISTINCTTOP PERCENT 別名.AS 列名1,別名. AS 列名2 FROM FORCE數(shù)據(jù)庫名!AS INNER|LEFTOUTER|RIGHTOUTER|FULLOUTER JOIN 數(shù)據(jù)庫名!AS ON INTO |TO FILE ADDITIVE|TO PRINTER PROMPT|TO SCREEN PREFERENCE NOCONSOLEPLAINNOWAIT WHERE AND AND | O

14、R AND | OR GROUP BY , HAVING UNION ALL ORDER BY ASC|DESC, ASC|DESC,6.4 SQL的查詢功能,SQL SELECT命令格式中主要短語的含義如下： SELECT短語：說明查詢的結果中要包含的數(shù)據(jù)。用“*”表示所有字段或指定具體字段數(shù)據(jù)；用ALL選項表示結果中可包括重復值的記錄，DISTINCT選項表示結果中不能包括重復值的記錄。缺省為ALL。 FROM短語：FROM短語與SELECT短語要同時使用，用來指定提供數(shù)據(jù)的表的名稱?？梢灾付ㄒ粋€或多個表，多個表時用逗號分隔。數(shù)據(jù)庫名和表名之間用！分隔。 WHERE短語：說明查詢條件,即選

15、擇元組的條件。 GROUP BY短語：用于對查詢結果進行分組, 通常利用它進行分組求和。 HAVING短語：必須跟隨GROUP BY使用，用于限定分組必須滿足的條件。 ORDER BY短語：用來對查詢的結果進行排序。缺省ORDER BY短語則按升序。,簡單查詢基于單個表，僅由SELECT和FROM短語構成無條件查詢，或由SELECT、FROM和WHERE短語構成條件查詢。 例6-17 從“職工”表中檢索全體職工的工齡情況有多少種。命令為： SELECT DISTINCT 工齡 FROM 職工 其中，DISTINCT短語的作用是去掉查詢結果中的重復值。 例6-18 檢索“單位”表中的所有元組。

16、SELECT * FROM 單位 其中“*”是通配符，表示所有字段。,6.4.2 簡單查詢,例6-19 檢索工齡高于14年的職工號及其工齡。 SELECT 職工號, 工齡 FROM 職工 WHERE 工齡14 其中，WHERE短語指定了查詢條件。 例6-20 找出在“100001”或“100002”單位工作，并且工齡低于15年的職工號。 SELECT 職工號,工齡 FROM 職工; WHERE 工齡15 AND (單位號=100001 OR 單位號=100002),在WHERE子句所表達的條件中，除了可以使用關系運算符和邏輯運算符之外，還可以使用一些特殊運算符號，如BETWEENAND 、IN

17、和LIKE等。 例6-21 檢索出學分指標在260300分范圍的單位信息。 要查詢值在什么范圍之內，可以使用BETWEENAND,命令如下： SELECT * FROM 單位 WHERE 學分指標 BETWEEN 260 AND 300 例6-22 從“單位”表中檢索出所有系的信息，不要學院或其它單位的信息。 命令如下：SELECT * FROM 單位 WHERE 單位名 LIKE %系 這里的LIKE是字符串匹配運算符，通配符“%”表示0個或多個字符,通配符“_”表示一個任意字符。,6.4.3 帶特殊運算符的條件查詢,例6-23 檢索出除“數(shù)學學院”以外的其它單位的職工信息。 命令如下：SE

18、LECT * FROM 職工 WHERE 單位號!=100002 在SQL中,“不等于”用“!=”表示。在VFP中還可以用“”和“#”表示。另外還可以用否定運算符NOT寫出與上述命令等價的命令： SELECT * FROM 職工 WHERE NOT(單位號=100002) NOT的應用范圍很廣，比如，NOT IN、NOT BETWEEN等。例如檢索出學分指標不在260300分范圍的單位信息： SELECT * FROM 單位 WHERE 學分指標 NOT BETWEEN 260 AND 300,用于指定排序的短語ORDER BY的格式如下： ORDER BY ASC|DESC, ASC|DES

19、C 可以按一列或多列排序，用ASC指定升序，DESC指定降序。默認為升序(ASC)。 例6-24 按“職工”表的工齡值的降序檢索出全部職工信息。 SELECT * FROM 職工 ORDER BY 工齡 DESC 例6-25 檢索出全部職工的信息，先按工齡升序排序，工齡相同則按職工號降序排序。 SELECT * FROM 職工 ORDER BY 工齡 , 職工號 DESC 注意：ORDER BY是對最終的查詢結果進行排序，嵌套查詢中的子查詢不能使用該短語。,6.4.4 排序,聯(lián)接查詢是基于多個表的查詢。 例6-26 找出工齡大于等于15年的職工以及他們所在的單位。 分析：要檢索的信息分別出自職

20、工表和單位表，這是基于多個關系的查詢，可以用聯(lián)接查詢來實現(xiàn)。命令如下： SELECT 職工號,工齡,單位名 FROM 職工,單位; WHERE (工齡=15) AND (職工.單位號=單位.單位號) 其中“職工.單位號=單位.單位號”是聯(lián)接條件?！肮g=15”是選擇條件。 說明： 1)當FROM之后有兩個關系或更多關系時，各個關系相互之間肯定有一種聯(lián)系，否則無法構成檢索表達式。單位表和職工表之間是一對多的聯(lián)系。 2)當FORM之后的多個關系中含有相同的屬性名時，這時必須加表名作為屬性名的前綴，以區(qū)別屬性所屬的關系。例如 “職工.單位號”，“單位.單位號”。,6.4.5 簡單的聯(lián)接查詢,3)在一

21、個SELECT查詢命令中，這種表名前綴出現(xiàn)較多時就會顯得很麻煩，因此，SQL允許在FORM短語中為每個關系名或表名定義一個別名。上述命令可以寫為： SELECT 職工號,工齡,單位名 FROM 職工 AS A,單位 AS B; WHERE (工齡=15) AND (A.單位號=B.單位號) 其中AS可以省略。 例6-27 找出“數(shù)學學院”工齡高于15年的職工。 SELECT 職工號,工齡,單位.單位號,單位名 FROM 職工,單位; WHERE (工齡15)AND(單位名=數(shù)學學院)AND(職工.單位號=單位.單位號) 注意：這里WHERE子句中， “職工.單位號=單位.單位號”是聯(lián)接條件，其

22、它是選擇條件。,例6-28 檢索出“物理學院”全部職工選修課程的情況。 分析：該檢索涉及到單位表、職工表、成績1表和課程1表四個關系，表之間相互的聯(lián)系必須用聯(lián)接條件來表達，命令如下： SELECT A.單位號,A.單位名,B.職工號,C.課程號,D.課程名,C.成績 FROM 單位 A,職工 B,成績1 C,課程1 D;WHERE A.單位名=物理學院 AND A.單位號=B.單位號; AND B.職工號=C.職工號 AND C.課程號=D.課程號 當查詢涉及多個關系時，聯(lián)接條件的表達最好按表之間的聯(lián)系順序來書寫。這里，可以理解成以單位表的單位號聯(lián)系確定職工表中的單位號，得到職工號；再以職工表

23、的職工號聯(lián)系確定成績1表的職工號，得到課程號和成績；再以成績1表的課程號聯(lián)系確定課程1表的課程號，并得到課程名。,1簡單的嵌套查詢 在一個SELECT命令的WHERE子句中，如果又出現(xiàn)另一個SELECT命令，則這樣的查詢稱為嵌套查詢。嵌套查詢也是基于多個關系的查詢。其查詢的結果出自一個關系，但查詢相關的條件卻可能涉及多個關系。 例6-29 檢索哪些單位有工齡高于14年的職工。 分析：這里要求查詢單位表中的單位信息，而查詢條件是職工表的工齡字段值。命令如下： SELECT 單位名 FROM 單位 WHERE 單位號 IN; (SELECT 單位號 FROM 職工 WHERE 工齡14) 可以看到

24、,該命令中含有兩個SELECT-FROM-WHERE查詢塊，即內層查詢（或子查詢）塊和外層查詢塊，內層查詢檢索到的單位號是100002和100004，因此可以寫出等價的命令： SELECT 單位名 FROM 單位 WHERE 單位號 IN(100002,100004) 本例也可以用聯(lián)接查詢實現(xiàn)，命令如下： SELECT DISTINCT 單位名 FROM 職工,單位; WHERE (工齡14) AND (職工.單位號=單位.單位號),6.4.6 嵌套查詢,例6-30 檢索哪些單位所有職工的工齡均高于14年。 分析：本例的檢索要求與前一例稍有區(qū)別，即要找出沒有一個職工的工齡是低于或等于14年的單

25、位。命令如下： SELECT * FROM 單位 WHERE 單位號 NOT IN; (SELECT 單位號 FROM 職工 WHERE 工齡=14) 這里，內層查詢塊從職工表中找出所有職工的工齡低于或等于14年的單位號集合；然后從單位表中檢索元組的單位號屬性值不在該集合中的所有元組。 但是，這里的檢索出現(xiàn)了錯誤，盡管在職工表中還沒有“經(jīng)濟管理系”（單位號100005）的職工，但結果中出現(xiàn)了“經(jīng)濟管理系”的信息。 如果要排除那些在職工表中還沒有職工信息的單位，命令如下： SELECT * FROM 單位 WHERE 單位號 NOT IN; (SELECT 單位號 FROM 職工 WHERE 工

26、齡=14); AND 單位號 IN (SELECT 單位號 FROM 職工),前面幾個嵌套查詢的例子都是外層查詢依賴于內層查詢的結果，內層查詢與外層查詢無關。但有時，內層查詢的條件需要外層查詢提供值，而外層查詢的條件需要內層查詢的結果。這就是所謂內、外層互相關的查詢。 例6-31 列出每個職工所選修課程的最好成績。 即每個職工可能學習了多門課程，要把每個職工的最高成績找出來。命令如下： SELECT out.職工號,out.課程號,out.成績,out.考核時間 FROM 成績1 out WHERE out.成績=; (SELECT MAX(inside.成績) FROM 成績1 inside

27、; WHERE out.職工號=inside.職工號) 該查詢中，外層查詢和內層查詢使用同一個表，為了區(qū)別，在FROM子句中給它們分別指定別名out和inside。外層查詢提供out關系中每個元組的職工號值給內層查詢使用；內層查詢利用一個職工號值，確定該職工所選學課程的最好成績分數(shù)；隨后外層查詢再將out關系的同一元組的成績值與該最好成績值進行比較，如果相等，則該元組被選擇。 MAX()函數(shù)將在下一小節(jié)介紹。,2內、外層互相關的嵌套查詢,6.4.7 使用函數(shù)計算與分組查詢,SQL SELECT命令還有計算方式的檢索，利用系統(tǒng)提供的計算檢索函數(shù)，可以對滿足條件的記錄進行計算和統(tǒng)計匯總等。 用于計

28、算檢索的常用函數(shù)： MAX(表達式)求最大值 MIN(表達式)求最小值 AVG(表達式)計算平均值 COUNT(表達式)計數(shù) SUM(表達式)求和 1簡單計算查詢 例6-32 找出在“100002”單位工作的職工的最高工齡。 SELECT MAX(工齡) FROM 職工 WHERE 單位號=100002 如果找該單位職工的最低工齡，則命令為： SELECT MIN(工齡) FROM 職工 WHERE 單位號=100002 例6-33 找出在“100002”單位工作的職工的平均工齡。 SELECT AVG(工齡) FROM 職工 WHERE 單位號=100002,例6-34 檢索顯示教學管理數(shù)據(jù)

29、庫的“成績”表中所有同學全部課程的平時、期中和期末成績總分的平均分以及所選修的課程數(shù)。 SELECT AVG(平時成績+期中成績+期末成績),; COUNT(DISTINCT 課程代號) FROM 成績 說明： 1)函數(shù)參數(shù)不一定是單個屬性，也可以是復雜的表達式。 2)在SELECT語句中，不但可以為關系指定別名,還可以使用AS（或省略AS）為查詢結果中的各列單獨指定顯示的標題，使得顯示標題含義更明了。 例如:SELECT AVG(平時成績+期中成績+期末成績) AS 總平均,; COUNT(DISTINCT 課程代號) AS 課程數(shù) FROM 成績,例6-35 求所有職工的工齡都高于12年的

30、單位的平均學分指標。 SELECT AVG(學分指標) 平均指標 FROM 單位 WHERE 單位號 NOT IN (SELECT 單位號 FROM 職工 WHERE 工齡=12) 其中，“平均指標”是為顯示列指定的標題，省略了AS。另外，該命令的計算結果包含了在職工表中還沒有職工信息的單位，因此結果不準確。要排除這些單位，命令應改為： SELECT AVG(學分指標) 平均指標 FROM 單位 WHERE 單位號 NOT IN (SELECT 單位號 FROM 職工 WHERE 工齡=12) AND 單位號 IN (SELECT 單位號 FROM 職工),例6-36 找出已經(jīng)被選課程的門數(shù)和

31、選修課程的人數(shù)。 SELECT COUNT(DISTINCT 課程號) 課程數(shù),COUNT(*) 人次數(shù) FROM 成績1 注意，除非對關系中的元組個數(shù)進行計數(shù)，一般COUNT函數(shù)應該使用DISTINCT。 例6-37 求選修“辦公自動化”這一課程的職工的成績之和。 SELECT SUM(成績) FROM 成績1 WHERE 課程號 IN; (SELECT 課程號 FROM 課程1 WHERE 課程名=辦公自動化),2分組與計算查詢,如果需要對查詢結果進行分組，而分組又通常要求有計算，這時利用GROUP BY子句。 GROUP BY短語的格式如下： GROUP BY , HAVING 可以按一

32、列或多列分組，還可以用HAVING短語進一步限定查詢結果中的分組必須滿足的條件。,例6-38 求各單位職工的平均工齡。 命令：SELECT 單位號,AVG(工齡) FROM 職工 GROUP BY 單位號 GROUP BY子句一般放在WHERE子句 或 放在FROM子句之后。 如果要檢索滿足一定條件的分組，就應使用HAVING子句。 例6-39 求至少有兩個職工的單位的平均工齡。 SELECT 單位號,COUNT(*),AVG(工齡) FROM 職工; GROUP BY 單位號 HAVING COUNT(*)=2 HAVING子句總是跟在GROUP BY子句之后,不可以單獨使用。,6.4.8

33、使用量詞和謂詞的查詢,除與子查詢有關的IN和NOT IN等運算符外還有兩類和子查詢有關的運算符，ANY、SOME、ALL與EXISTS、NOT EXISTS。它們有以下兩種形式： ANY|ALL|SOME(子查詢) NOT EXISTS(子查詢) 其中：ANY、SOME和ALL是量詞。而ANY和SOME是同義詞，表示在進行比較運算時只要子查詢中有一行能使結果為真，則結果就為真；ALL則要求子查詢中的所有行都使結果為真時，結果才為真。 EXISTS是謂詞。EXISTS或NOT EXISTS是用來檢查在子查詢中是否有結果返回，即存在元組或不存在元組。,例6-40 檢索出那些在職工表中還沒有職工的單

34、位信息。 要查詢的是沒有職工或不存在職工的單位，所以可以使用謂詞NOT EXISTS： SELECT * FROM 單位 WHERE NOT EXISTS; (SELECT * FROM 職工 WHERE 單位號=單位.單位號) 以上的查詢等價于： SELECT * FROM 單位 WHERE 單位號 NOT IN(SELECT 單位號 FROM 職工) 例6-41 檢索那些至少已經(jīng)有一個職工的單位信息。 SELECT * FROM 單位 WHERE EXISTS; (SELECT * FROM 職工 WHERE 單位號=單位.單位號) 它等價于： SELECT * FROM 單位 WHERE

35、 單位號 IN(SELECT 單位號 FROM 職工) 注意：NOT EXISTS只是判斷子查詢中是否有或沒有結果返回，它本身并沒有任何運算或比較。,例6-42 找出有職工工齡大于或等于“100002”單位中任何一名職工工齡的單位。 這個查詢可以使用ANY或SOME量詞。 SELECT DISTINCT 單位號 FROM 職工 WHERE 工齡=ANY; (SELECT 工齡 FROM 職工 WHERE 單位號=”100002”) 它等價于： SELECT DISTINCT 單位號 FROM 職工 WHERE 工齡=; (SELECT MIN(工齡) FROM 職工 WHERE 單位號=100

36、002) 例6-43 檢索有職工的工齡大于或等于“100002”單位中所有職工工齡的單位號。 這個查詢使用ALL量詞。 SELECT DISTINCT 單位號 FROM 職工 WHERE 工齡=ALL; (SELECT 工齡 FROM 職工 WHERE 單位號=100002) 它等價于： SELECT DISTINCT 單位號 FROM 職工 WHERE 工齡=; (SELECT MAX(工齡) FROM 職工 WHERE 單位號=100002),6.4.9 自聯(lián)接和超聯(lián)接查詢（選學）,1自聯(lián)接查詢 SQL不僅可以對多個關系實行聯(lián)接操作，也可以將單個關系與其自身進行聯(lián)接，這種聯(lián)接稱為自聯(lián)接。

37、在可以進行自聯(lián)接操作的關系中，具有來自同一值域的兩個不同的屬性，使該關系中的一些元組與該關系中另外一些元組有對應關系，也就是實體集內部存在聯(lián)系，這種關系通常是一對多的聯(lián)系。 例如，假設學員關系：學員(學號,姓名,組長學號)，則存在一個組長管理多個學員；又例如，假設零部件關系：零部件(零件號，名稱，所屬部件號) ，則存在一個部件包含多個組成零件。以零部件關系為例，其中零件號和所屬部件號兩個屬性出自同一個值域，這兩個屬性值是包含關系，其中一個所屬部件號對應多個零件號。 例6-44 根據(jù)零部件關系列出部件所包含的零件清單。 SELECT A.名稱,包含,B.名稱 FROM 零部件 A,零部件 B;

38、WHERE A.零件號=B.所屬部件,2超聯(lián)接查詢,超聯(lián)接運算符有“*=”和“=*”。 “*=”稱為左聯(lián)接，左聯(lián)接的含義是檢索第一個表中的所有記錄和第二個表中與之對應的記錄。對于第一個表中的每一個記錄，如果第二個表中沒有與之相匹配的記錄，則在查詢結果中第二個表相應行的屬性值以Null代之； “=*”稱為右聯(lián)接右聯(lián)接的含義是檢索第二個表中的所有記錄和第一個表中與之對應的記錄。對于第二個表中的每一個記錄，如果第一個表中沒有與之相匹配的記錄，則在查詢結果中第一個表相應行的屬性值以Null代之。 注意：VFP雖然支持超聯(lián)接運算，但不能使用運算符“*=”和“=*”。VFP支持超聯(lián)接查詢有專有的聯(lián)接運算語

39、法格式。在SELECT語句格式中，VFP用于聯(lián)接運算有關的語法格式為： SELECT . FROM INNER |LEFT|RIGHT|FULL JOIN ON WHERE . 說明： 和分別指定第一個表和第二個表。, FROM短語中給出聯(lián)接類型。有四種聯(lián)接類型： INNER JOIN等價于JOIN，為普通的等值聯(lián)接，只有滿足聯(lián)接條件的記錄才出現(xiàn)在查詢結果中。在VFP中稱為內部聯(lián)接。 LEFT JOIN為左聯(lián)接。 RIGHT JOIN為右聯(lián)接。 FULL JOIN稱為全聯(lián)接。相當左聯(lián)接與右聯(lián)接的合成。即兩個表中的記錄不管是否滿足聯(lián)接條件將都在查詢結果中出現(xiàn)，相互不滿足聯(lián)接條件的記錄相應部分以N

40、ULL代之。 ON 短語指定聯(lián)接條件。 WHERE短語給出篩選條件。 例6-45 檢索出單位及其所屬職工的職工號和工齡信息。 用內部聯(lián)接實現(xiàn)，即只有滿足聯(lián)接條件的記錄才出現(xiàn)在查詢結果中。 SELECT 單位.單位號,單位名,職工號,工齡; FROM 單位 INNER JOIN 職工; ON 單位.單位號=職工.單位號 注意：在超聯(lián)接中，聯(lián)接條件在ON短語中給出。,6.4.10 用空值查詢（自學）,所謂空值是指NULL值。SQL支持空值，允許利用空值進行查詢。 例6-48 找出“成績1”表中選修了課程但還沒有參加考核或者沒有成績值的記錄。 SELECT * FROM 成績1 WHERE 成績 I

41、S NULL 例6-49 找出“成績1”表中有成績的記錄。命令如下： SELECT * FROM 成績1 WHERE 成績 IS NOT NULL 注意：利用空值查詢時要使用“IS NULL”或“IS NOT NULL”，若使用“=NULL”和“NULL”是無效的,因為空值不是一個確定的值,所以不能用“=”，“”等運算符進行比較。 例6-50 找出那些所有課程都在65分以上的職工的平均工齡。 SELECT AVG(工齡) FROM 職工 WHERE 職工號 NOT IN; (SELECT 職工號 FROM 成績1 WHERE 成績65 OR 成績 IS NULL),6.4.11 集合的并運算（

42、自學）,SQL支持集合的并(UNION)運算，可以將兩個SELECT語句的查詢結果用UNION合并成一個查詢結果。要進行并運算，兩個查詢的結果必須具有相同的字段個數(shù)，并且對應字段的值要出自同一個值域，即具有相同的數(shù)據(jù)類型和取值范圍。 例如，檢索出單位表中物理學院和數(shù)學學院的信息： SELECT * FROM 單位 WHERE 單位名=物理學院; UNION; SELECT * FROM 單位 WHERE 單位名=數(shù)學學院 顯然，對于出自同一個表的數(shù)據(jù)，用以下命令更簡單，等價的命令為： SELECT * FROM 單位 WHERE 單位名=物理學院 OR 單位名=數(shù)學學院,6.4.12 VFP的

43、SQL SELECT命令中幾個特殊選項,1.顯示部分結果：TOP PERCENT 需要注意的是TOP短語要與ORDER BY短語同時使用才有效。 例6-51 找出工齡最高的三位職工的信息。 SELECT * TOP 3 FROM 職工 ORDER BY 工齡 DESC 例6-52 顯示20%職工的信息，他們的工齡是最短的。 SELECT * TOP 20 PERCENT FROM 職工 ORDER BY 工齡 2.將查詢結果存放到數(shù)組中：INTO ARRAY 例6-53 如果把例6-51的查詢結果存放在數(shù)組temp中，命令為： SELECT * TOP 3 FROM 職工 ORDER BY 工

44、齡 DESC INTO ARRAY temp,3.將查詢結果存放在臨時文件中:INTO CURSOR 例6-54 將查詢到的職工信息存放在臨時dbf文件temp中，并用BROWSE命令查看。 命令如下： SELECT * FROM 職工 INTO CURSOR temp BROWSE 4.將查詢結果存放到永久表中:INTO DBF | TABLE 例6-55 將例6-52的查詢結果存放在表LOWER.DBF文件中，命令如下： SELECT * TOP 20 PERCENT FROM 職工 ORDER BY 工齡 INTO TABLE LOWER,5.將查詢結果存放到文本文件中:TO FILE

45、ADDITIVE 文本文件名的默認擴展名是txt。若使用ADDITIVE，則如果指定的文本文件已經(jīng)存在時，將查詢結果追加在原文件的尾部，否則將覆蓋原有文件。 6.將查詢結果直接輸出到打印機的短語格式:TO PRINTER PROMPT 7.將查詢結果顯示在VFP主窗口或活動的用戶自定義窗口:TO SCREEN 注意：前面2、3、4三個短語都是INTO短語，5、6、7是TO短語。如果TO短語和INTO短語同時使用，則TO短語將會被忽略。,1視圖的概念 視圖（VIEW）是在數(shù)據(jù)庫表（非自由表）的基礎上定義的一種特殊表，是實際并不存在的虛擬表。所謂虛擬，是因為視圖的數(shù)據(jù)是從表或其它視圖中抽取得來的，

46、視圖的數(shù)據(jù)并沒有在數(shù)據(jù)庫中單獨集中存儲，而只是在數(shù)據(jù)庫中保存視圖的定義。視圖一經(jīng)定義，就成為數(shù)據(jù)庫的組成部分，可以像表一樣接受用戶的查詢。另外，沒有數(shù)據(jù)庫就沒有視圖。 視圖又分為本地視圖和遠程視圖。 視圖是可更新的，利用視圖可以修改或更新檢索到的記錄，并把更新結果發(fā)送回到源表中，使源數(shù)據(jù)表中的記錄也隨之修改，這是查詢所沒有的功能。,6.5 視圖及視圖的定義,視圖定義的命令格式如下： CREATE SQL VIEW (,) AS 說明： 視圖定義的核心部分是查詢。其中可以是任意的SELECT查詢語句，該子句決定視圖中的數(shù)據(jù)來源。 當沒有為視圖指定字段名(列名)時，視圖的字段名將與中指定的字段名或

47、表中的字段同名。 SQL表示SQL可以省略。 由于視圖的數(shù)據(jù)是從表派生出來的，常把視圖的源表稱作基本表。 例6-56 定義以下視圖：CREATE VIEW V1 AS; SELECT 職工號, 單位號,性別 FROM 職工 視圖一經(jīng)定義，就可以和基本表一樣進行各種查詢。 例如：SELECT * FROM V1或 SELECT 職工號,單位號,性別 FROM V1,2SQL視圖的定義或創(chuàng)建,例6-57 前面的例6-31，要列出每個職工所選修課程的最好成績，如果要求一般工作人員也鍵入那么復雜的命令，實在有點太強求了，我們可以先定義好視圖：CREATE VIEW V2 AS; SELECT out.

48、職工號,out.課程號,out.成績,out.考核時間; FROM 成績1 out WHERE out.成績=; (SELECT MAX(inside.成績) FROM 成績1 inside; WHERE out.職工號=inside.職工號) 此后，再提出同樣的查詢要求，就只需要輸入以下命令即可： SELECT * FROM V2 例6-58 以下視圖向用戶提供職工號、職工的性別和職工所在單位信息： CREATE VIEW V3 AS; SELECT 職工號,性別,單位名 FROM 職工,單位; WHERE 職工.單位號=單位.單位號 該結果對用戶就好像有一個包含字段職工號、性別和單位名的表

49、。使用起來十分方便。,在視圖中還可以使用虛字段 一個視圖的SELECT子句可以包含算術表達式或函數(shù)，這些表達式或函數(shù)與視圖的其他字段看起來一樣，但并不存儲在表內，所以稱為視圖的虛字段。（注：虛字段名就是在查詢語句中為列指定的顯示標題） 例6-59 定義一個視圖，包含教學管理數(shù)據(jù)庫中成績表的學號、課程號、平時、期中和期末成績之外，還包含總評成績?？傇u成績按平時、期中和期末成績的平均計算。 CREATE VIEW V_ZP AS; SELECT *,(平時成績+期中成績+期末成績)/3 AS 總評 FROM 成績 其中“總評”稱為虛字段。然后,可使用查詢命令: SELECT * FROM V_ZP

50、 ORDER BY 學號 4.視圖的刪除 視圖是從表中派生出來的，不存在修改結構的問題，但是視圖可以刪除。 刪除視圖的命令格式是：DROP VIEW 例如，刪除視圖 V3，鍵入命令：DROP VIEW V3,6.6.1查詢和視圖設計的概念 查詢設計（或設計查詢）是指預先定義好一個SQL SELECT語句，并保存為查詢文件（.QPR文件），該命令文件可以被反復執(zhí)行來提取數(shù)據(jù)。 生成查詢文件（.QPR文件）的方法： 1)可使用文本編輯器直接輸入和編輯SQL SELECT語句，并保存為.QPR文件。 2)使用“查詢向導”和“查詢設計器”來設計，讓系統(tǒng)自動生成.QPR查詢文件。,6.6 查詢設計與視圖

51、設計,視圖設計（或設計視圖）則是指使用VFP提供的“視圖向導”和“視圖設計器”來設計，讓系統(tǒng)在數(shù)據(jù)庫中自動生成視圖的定義。 查詢和視圖的主要差別在于： 1)視圖是可更新的，視圖可以更新數(shù)據(jù)并將更新結果發(fā)送回源表，而查詢則不行。 2)查詢文件（QPR文件）獨立存在，該程序文件可以反復執(zhí)行；視圖不是獨立文件，只是保存在數(shù)據(jù)庫中的一種定義，可以作為查詢的對象。 3)對查詢結果可以定向輸出，而視圖不可以定向輸出。查詢的結果可以在窗口瀏覽，生成數(shù)據(jù)表、圖形和報表等，而視圖的結果不能直接實現(xiàn)這樣的功能。,6.6.2 查詢設計,1. 用“查詢向導”設計查詢 1)“查詢向導”的啟動 單擊“文件”菜單，選擇“新

52、建”命令，文件類型選中“查詢”，單擊“向導”按鈕； 打開“教學管理”項目。 單擊“工具”菜單，選擇“向導”子菜單中的“查詢”命令。 2）創(chuàng)建標準查詢的過程 例：設計一個查詢，查找“學1”表中專業(yè)為“計算機”，性別為“女”的所有同學的記錄。注意：在“步驟3篩選記錄”中構成以下兩個條件表達式： 學1.專業(yè)=計算機與 學1.性別=女 兩個條件要求同時滿足是“與”關系，因此選中“與”單選按鈕。,3）運行查詢,在“項目管理器”中運行查詢 如果查詢不在“項目管理器”中，可以利用“項目管理器”的“添加”按鈕，將查詢添加到“項目管理器”中，然后再運行它。 使用“程序”菜單的“運行”命令運行查詢 用DO命令運行

53、查詢文件 可以在命令窗口直接輸入命令來運行查詢文件，例如： DO 查詢成績.QPR 其中，擴展名.QPR不能省略。,2.用“查詢設計器”設計查詢,1）啟動“查詢設計器” 在“項目管理器”“數(shù)據(jù)”選項卡中選擇“查詢”，單擊“新建”按鈕。 在“文件”菜單中選“新建”命令 在命令窗口中，直接輸入以下命令：CREATE QUERY 啟動查詢設計器時，用戶應首先添加查詢的對象，即表或視圖。,查詢設計器窗口組成：,由頂部窗格和下部的選項卡組成，頂部的窗格顯示被查詢的表和表間的聯(lián)接。連線表示表間的聯(lián)接。在數(shù)據(jù)庫中定義的表與表之間的聯(lián)系在“查詢設計器”中將成為默認的聯(lián)接。,2）利用“查詢設計器”設計查詢,“查

54、詢設計器”窗口中的基本操作：添加表、移去表 、“查詢設計器工具欄”的顯示與隱藏。 例6-60：設計一個查詢，用于查看計算機專業(yè)學生選修了哪些課程，并將選修的課程名稱和期末成績顯示出來。 分析：根據(jù)表結構可知，本任務涉及3個表,即“學1”表、“成績”表、“課程”表。在“學1”表中有學生的姓名及專業(yè)信息；在“成績”表中有學生學號和對應的選修課程代碼；在“課程”表中有課程代碼對應的課程名稱。3個表分別以學號和課程代號建立聯(lián)接。,3）查詢文件(SQL語句)內容的查看,在“查詢設計器”中，用戶設置好有關內容后，系統(tǒng)自動生成一條SELECT-SQL命令，并將該命令以查詢文件（.QPR文件）形式存儲起來。要查看SQL語句，方法如下： 在項目管理器“數(shù)據(jù)”選項卡中選中某個查詢； 單擊“修改”按鈕，打開“查詢設計器”； 從“查詢”菜單中選擇“查看S