《Visual FoxPro數(shù)據(jù)庫程序設(shè)計教程》課件第4章

第4章關(guān)系數(shù)據(jù)庫標準語言SQL4.1SQL概述

4.2查詢功能

4.3定義功能

4.4操作功能

4.1SQL概述

SQL語言來源于20世紀70年代IBM的一個被稱為SEQUEL(StructuredEnglishQueryLanguage)的研究項目。20世紀80年代，SQL由ANSI進行了標準化，它包括了定義和操作數(shù)據(jù)的指令。隨后，由于它具有功能豐富，使用方式靈活，語言簡潔易學(xué)等突出特點，在計算機界深受廣大用戶歡迎，許多數(shù)據(jù)庫生產(chǎn)廠家都相繼推出各自支持的SQL標準。1998年4月，ISO提出了具有完整性特征的SQL，并將其定為國際標準，推薦它為標準關(guān)系數(shù)據(jù)庫語言。1990年，我國也頒布了《信息處理系統(tǒng)數(shù)據(jù)庫語言SQL》，將其定為中國國家標準。其特點如下：

(1)?SQL語言是一種一體化的語言。盡管設(shè)計SQL的最初目的是查詢，并且數(shù)據(jù)查詢也是其最重要的功能之一，但SQL決不僅僅是一個查詢工具，它集數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制功能于一體，可以獨立完成數(shù)據(jù)庫的全部操作。

(2)?SQL語言是一種高度非過程化的語言。SQL語言不必一步步地告訴計算機“如何”去做，而只需要描述清楚用戶要“做什么”，SQL語言就可以將要求交給系統(tǒng)，自動完成全部工作。

(3)?SQL語言非常簡潔。雖然SQL語言功能很強，但它只有為數(shù)不多的9條命令：CREATE、DROP、ALTER、SELECT、INSERT、UPDATE、DELETE、GRANT和REVOKE。

(4)?SQL語言可以直接以命令方式交互使用，也可以嵌入到程序設(shè)計語言中以程序方式使用?，F(xiàn)在很多數(shù)據(jù)庫應(yīng)用開發(fā)工具都將SQL語言直接融入到自身的語言之中，使用起來更方便，VisualFoxPro就是如此。這些使用方式為用戶提供了靈活的選擇余地。

VisualFoxPro在SQL方面支持數(shù)據(jù)定義、數(shù)據(jù)查詢和數(shù)據(jù)操縱功能，但在具體實現(xiàn)方面仍存在一些差異。另外，由于VisualFoxPro自身在安全控制方面的缺陷，因此它沒有提供數(shù)據(jù)控制功能。

SQL雖然在各種數(shù)據(jù)庫產(chǎn)品中得到了廣泛的支持，但迄今為止，它只是一種建議標準，各種數(shù)據(jù)庫產(chǎn)品中所實現(xiàn)的SQL在語法、功能等方面均略有差異，本章則講述在VisualFoxPro中SQL的語法、功能與應(yīng)用。 4.2查詢功能

4.2.1基本查詢

基本查詢的命令格式為

SELECT

[ALL|DISTINCT]字段表達式列表[AS列名]FROM表文件名

該命令的作用是將指定表中查詢出的信息顯示在瀏覽窗口中。

ALL表示選出的記錄中包括重復(fù)記錄。

DISTINCT表示選出的記錄中不包括重復(fù)記錄，將重復(fù)記錄只保留一條。缺省時默認為ALL。

字段表達式列表可以是一組用“，”分開的字段名，也可以是函數(shù)或表達式。

AS列名為查詢結(jié)果重新指定一個列標題。

FROM表文件名設(shè)定查詢的數(shù)據(jù)來源。

注意：①書寫應(yīng)注意各個子句之間以空格分隔，子句內(nèi)部各個項目之間使用逗號分隔。

②通常每個子句寫一行，每行后要用“；”續(xù)行。

下列用一些例子說明SELECT命令的使用，例子中所用到的數(shù)據(jù)表——職工基本情況表(jbqk.dbf)，具體內(nèi)容如表4.1所示。表4.1職工基本情況表

例4.1

在jbqk表中，檢索所有記錄的所有字段。

SELECT*FROM

jbqk

命令中的*表示顯示所有的字段，數(shù)據(jù)來源是jbqk表，結(jié)果如圖4.1所示。圖4.1例4.1查詢結(jié)果

例4.2

在jbqk表中，檢索所有職稱名稱。

SELECT

DISTINCT職稱FROM

jbqk

查詢結(jié)果如圖4.2所示。圖4.2例4.2查詢結(jié)果

例4.3

檢索出每個人的姓名和實發(fā)工資(實發(fā)工資=基本工資+獎金)。

SELECT姓名，基本工資+獎金AS實發(fā)工資FROM

jbqk

數(shù)據(jù)表中沒有實發(fā)工資字段，所以使用子句AS，實發(fā)工資為實發(fā)工資列添加一個列標題，查詢結(jié)果如圖4.3所示。圖4.3例4.3查詢結(jié)果

例4.4

統(tǒng)計表中的職工人數(shù)，并計算支付的基本工資總數(shù)和獎金總數(shù)。

SELECTCOUNT(*),SUM(基本工資)，SUM(獎金)FROMjbqk

查詢結(jié)果如圖4.4所示。圖4.4例4.4查詢結(jié)果4.2.2條件(WHERE)查詢

條件查詢的命令格式為

SELECT

[ALL|DISTINC]字段列表FROM表文件名WHERE條件

該命令的作用是檢索出滿足條件記錄中的指定信息。

從命令格式可以看出此命令的格式比基本查詢增加了WHERE條件子句。其中的條件除了可以使用VFP語言中的關(guān)系表達式以及邏輯表達外，還可以使用幾個特殊運算符：

(1)?[NOT]IN：表示[不]在…之中。

(2)?[NOT]BETWEEN...AND...：表示[不]在…之間。

(3)?[NOT]LIKE：表示[不]與…匹配。匹配符“%”表示0個或多個任意字符；匹配符“_”表示一個任意字符或一個任意漢字。

例4.5

在jbqk表中，檢索性別是女的記錄。

SELECT編號,姓名,性別FROMjbqkWHERE性別="女"

查詢結(jié)果如圖4.5所示。圖4.5例4.5查詢結(jié)果

例4.6

在jbqk表中，檢索編號前兩位是“11”的記錄。

SELECT編號，姓名，性別FROMjbqkWHERE編號LIKE

"11%"

在命令中，“11%”表示頭兩個字符是11，其后可以用任意個字符，查詢結(jié)果如圖4.6所示。圖4.6例4.6查詢結(jié)果

例4.7

在jbqk表中，檢索職稱中第二個字是“工”的記錄。

SELECT姓名，職稱FROMjbqkWHERE職稱LIKE

"_工"

命令中“_”表示“工”前有一個漢字或一個字符，查詢結(jié)果如圖4.7所示。圖4.7例4.7查詢結(jié)果

例4.8

在jbqk表中，檢索職工的基本工資在350元和600元之間的職工信息。

SELECT姓名，基本工資FROMjbqkWHERE基本工資BETWEEN350AND600

這里BETWEEN…AND表示在…之間，該命令等效于：

SELECT姓名,基本工資FROMjbqk；

WHERE基本工資>=350AND基本工資<=600

查詢結(jié)果如圖4.8所示。

在BETWEEN...AND之前也可以使用NOT，如要檢索職工的基本工資不在350元和600元之間的職工信息，可以使用命令：

SELECT姓名，基本工資FROMjbqk；

WHERE基本工資NOTBETWEEN

350

AND

600圖4.8例4.8查詢結(jié)果

例4.9

在jbqk表中，檢索所有姓陳和呂的記錄。

SELECT編號,姓名FROMjbqkWHERE姓名IN("陳","呂")

該命令等效于：

SELECT編號，姓名FROM

jbqk；

WHERE姓名="陳"OR姓名="呂"

IN(...)的運算對象還可以是一個SELECT的查詢結(jié)果，將這種查詢稱為嵌套查詢(參見章節(jié)4.2.6)，如圖4.9所示。圖4.9例4.9查詢結(jié)果4.2.3排序查詢

使用SQLSELECT可以將查詢結(jié)果排序，而且可以通過TOP子句選出排列在最前面的若干條記錄。命令格式為

SELECT

[ALL?|?DISTINC]字段列表[TOP數(shù)值表達式[PERCENT]]

FROM表文件名

ORDERBY關(guān)鍵字表達式1

[ASC|DESC][,關(guān)鍵字表達式2

[ASC|DESC]...]

其中TOP要與ORDERBY同時使用才有效。

TOP數(shù)值表達式表示在符合條件的記錄中，選取排在最前的指定數(shù)量的記錄。這里的表達式指要查詢的記錄的條數(shù)。

TOP數(shù)值表達式[PERCENT]這里的數(shù)值表達式PERCENT表示的百分比，是指選取指定百分比的記錄。

ASC為升序(默認為升序)，DESC為降序。允許按一列或多列排序。

在排序查詢中主要使用了TOP數(shù)值表達式[PERCENT]子句和ORDERBY關(guān)鍵字表達式1

[ASC|DESC][,關(guān)鍵字表達式2

[ASC|DESC]…]子句。

例4.10

在jbqk表中，檢索出工資最高的三位職工記錄。

SELECT

TOP3姓名FROM

jbqk

ORDER

BY基本工資DESC

命令中DESC表示降序排列，即按基本工資的值由高到低排列。TOP3表示選取排列在最前面的3條記錄，即工資最高的3條記錄。查詢結(jié)果如圖4.10所示。圖4.10例4.10查詢結(jié)果

例4.11

在jbqk表中，按基本工資從高到低的順序，檢索出20%的職工記錄。

SELECTTOP20PERC姓名，基本工資FROMjbqkORDERBY基本工資DESC

其中TOP20PERC表示從最前面選取20%的記錄，表中共有10條記錄，所以查詢結(jié)果中有2條記錄，查詢結(jié)果如圖4.11所示。圖4.11例4.11的查詢結(jié)果

例4.12

對jbqk表中1970年以后出生的職工，按基本工資從高到低的順序，檢索20%的職工記錄。

SELECTTOP20PERC姓名，基本工資FROM

jbqk；

ORDER

BY基本工資DESC；

WHERE出生年月>={^1970-01-01}

該命令等效于：

SELECTTOP20PERC姓名，基本工資FROM

jbqk；

WHERE出生年月>={^1970-01-01}；

ORDER

BY基本工資DESC

由于滿足查詢條件1970年以后出生的職工只有5條記錄，所以選取20%只有1條記錄。查詢結(jié)果如圖4.12所示。圖4.12例4.12查詢結(jié)果

例4.13

先按性別排序，再按基本工資排序，并輸出全部職工的編號、姓名、性別和基本工資。

SELECT編號，姓名，基本工資FROM

jbqk

ORDERBY性別，基本工資這是一個按多列排序的例子，查詢結(jié)果如圖4.13所示。圖4.13例4.13查詢結(jié)果4.2.4分組計算查詢

分組就是將一組類似(根據(jù)分組字段的值)的記錄壓縮成一個結(jié)果記錄，這樣就可以完成基于一組記錄的計算。命令格式為

SELECT[ALL|DISTINCT]字段列表FROM表文件名

[GROUPBY分組字段列表…][HAVING過濾條件]

分組計算查詢就是在基本查詢的基礎(chǔ)上增加了GROUPBY…HAVING過濾條件子句。在此命令格式中也可以使用條件子句和排序子句。

過濾條件對分組的結(jié)果根據(jù)條件(可以是來自于字段列表項中的選項，也可以是一個統(tǒng)計函數(shù))進行記錄組的過濾。

例4.14

在jbqk表中，檢索出每個部門的職工人數(shù)。

SELECT部門，COUNT(*)AS總?cè)藬?shù)FROMjbqk

GROUP

BY部門

在該查詢中，先按部門進行分組再按組進行記錄的個數(shù)計數(shù)，查詢結(jié)果如圖4.14所示。圖4.14例4.14查詢結(jié)果

例4.15

在jbqk表中，檢索出至少有三個職工的部門的平均工資。

SELECT部門，COUNT(*),AVG(工資)FROMjbqk；

GROUPBY倉庫號HAVINGCOUNT(*)>2

查詢結(jié)果如圖4.15所示。圖4.15例4.15查詢結(jié)果4.2.5聯(lián)接查詢

在一個數(shù)據(jù)庫中的多個表之間一般都存在著某些聯(lián)系，當(dāng)一個查詢語句中同時涉及到兩個或兩個以上的表時，這種查詢稱之為聯(lián)接查詢(也稱為多表查詢)。在多表之間查詢必須處理表與表之間的聯(lián)接關(guān)系。

在多表查詢中引用的例表如表4.2(職工表zg.dbf)、表4.3(倉庫表ck.dbf)、表4.4(供應(yīng)商gys.dbf)和表4.5(訂購單dgd.dbf)所示。表4.2職工表表4.3倉庫表表4.4供應(yīng)商表表4.5訂購單表

1.簡單聯(lián)接

例4.16

找出工資多于1230元的職工號和他們所在的城市。

SELECT職工號，城市FROMzg，ckWHERE(工資>1230)AND(zg.倉庫號=ck.倉庫號)

倉庫表和職工表之間存在一種一對多的聯(lián)系，查詢的結(jié)果如圖4.16所示。

查詢結(jié)果中的職工號和城市分別出自職工表和倉庫表，這兩個表之間是依據(jù)“職工.倉庫號=倉庫.倉庫號”作為聯(lián)接條件的。圖4.16例4.16查詢結(jié)果

例4.17

找出工作在面積大于400的倉庫的職工號以及這些職工所在的城市。

SELECT職工號,城市FROMzg,ck；

WHERE(面積>400)AND(zg.倉庫號=ck.倉庫號)

查詢的結(jié)果如圖4.17所示。圖4.17例4.17查詢結(jié)果

2.別名的使用

在聯(lián)接操作中，經(jīng)常需要使用關(guān)系名作前綴，有時這樣顯得很麻煩。因此，SQL允許在FROM短語中為關(guān)系名定義別名。

格式：關(guān)系名別名

如果使用別名，例4.17的SELECT命令就可以寫成：

SELECT職工號，城市FROMzga，ckb；

WHERE(面積>400)AND(a.倉庫號=b.倉庫號)

在FROM子句中設(shè)定了表zg的別名為a，表ck的別名為b。有了這樣的定義，在命令的其它部分引用該表時，就可以直接使用別名。

3.自然聯(lián)接

SQL不僅可以對多個表實行聯(lián)接操作，也可以將同一個表與其自身進行聯(lián)接，這種聯(lián)接就稱為自然聯(lián)接或自聯(lián)接。在可以進行這種自然聯(lián)接的表中，實際存在著一種特殊的遞歸關(guān)系，即表中的一些記錄，根據(jù)出自同一值域的兩個不同的字段，可以與另外的一些記錄有一種對應(yīng)關(guān)系(一對多聯(lián)系)。

例如，雇員和對應(yīng)上級領(lǐng)導(dǎo)的關(guān)系如表4.6所示。表4.6雇員和對應(yīng)上級領(lǐng)導(dǎo)表

例4.18

根據(jù)雇員和上級領(lǐng)導(dǎo)的關(guān)系表，列出上一級經(jīng)理及其所領(lǐng)導(dǎo)的職員清單。

SELECTS.雇員姓名，"領(lǐng)導(dǎo)"，E.雇員姓名FROM雇員S,雇員E;

WHERES.雇員號=E.經(jīng)理

查詢結(jié)果如圖4.18所示。圖4.18例4.18查詢結(jié)果這個查詢中通過別名定義了兩個邏輯表，一個是經(jīng)理表S，一個是雇員表E，它們中的內(nèi)容是相同的，如表4.7和表4.8所示。查詢是在這兩個虛表之間建立聯(lián)接并進行查詢的。表4.7虛表S表4.8虛表E

4.超聯(lián)接查詢

在新的SQL標準中還支持兩個新的關(guān)系聯(lián)接運算符，它們與我們原來所了解的簡單聯(lián)接和自然聯(lián)接不同。原來的聯(lián)接是只有滿足聯(lián)接條件，相應(yīng)的結(jié)果才會出現(xiàn)在結(jié)果表中，這種聯(lián)接也稱為內(nèi)聯(lián)接。兩種新的聯(lián)接是指SQL中的“*=”和“=*”的聯(lián)接運算，它們不滿足內(nèi)聯(lián)接這種特性。

“*=”稱為左聯(lián)接，左聯(lián)接操作是在結(jié)果集中，除了包含有滿足聯(lián)接條件的記錄外，還保留聯(lián)接表達式左表中的非匹配記錄，沒有匹配的字段用?.NULL.?表示。

例如，職工表和倉庫表依據(jù)倉庫號進行左聯(lián)接的結(jié)果如表4.9所示(職工表為左表，倉庫表為右表)。表4.9左聯(lián)接的查詢結(jié)果兩個表中共有的倉庫號WH1、WH2和WH3，所對應(yīng)的記錄進行聯(lián)接，而只有左表中才有的倉庫號WH5也會出現(xiàn)在結(jié)果中，但是由于右表中沒有該字段則，對應(yīng)的該字段的值為?.NULL.。右表中有而左表中沒有的倉庫號WH4則不出現(xiàn)在結(jié)果中。

“=*”稱為右聯(lián)接，右聯(lián)接的操作是在結(jié)果集中，除了包含有滿足聯(lián)接條件的記錄外，還保留聯(lián)接表達式右表中的非匹配記錄，沒有匹配的字段用?.NULL.?表示。

在結(jié)果表中，包含了第二個表中滿足條件的所有記錄；如果有在聯(lián)接條件上匹配的元組，則第一個表返回相應(yīng)值，否則返回空值。

上例中的右聯(lián)接結(jié)果如表4.10所示。表4.10右聯(lián)接的查詢結(jié)果

例4.19

內(nèi)部聯(lián)接。

SELECTck.倉庫號，城市，面積，職工號，工資FROMckJOINzgONck.倉庫號=zg.倉庫號

如下兩種命令格式也是等價的：

SELECTck.倉庫號，城市，面積，職工號，工資；

FROM倉庫INNERJOINzgONck.倉庫號=zg.倉庫號

和SELECTck.倉庫號，城市，面積，職工號，工資；

FROMck，zgWHEREck.倉庫號=zg.倉庫號

查詢結(jié)果如圖4.19所示。圖4.19例4.19查詢結(jié)果

例4.20

左聯(lián)接。

SELECTzg.倉庫號，城市，面積，職工號，工資；

FROM?zg?LEFTJOINckONck.倉庫號=zg.倉庫號

查詢結(jié)果參看表4.9。

例4.21

右聯(lián)接。

SELECTzg.倉庫號，城市，面積，職工號，工資；

FROM

zg

RIGHTJOIN

ck

ON

ck.倉庫號=zg.倉庫號

查詢結(jié)果參看表4.10。

例4.22

全聯(lián)接。

SELECTck.倉庫號，城市，面積，職工號，工資；

FROM

ck

FULL

JOIN

zg

ON

ck.倉庫號=zg.倉庫號

查詢結(jié)果如圖4.20所示。圖4.20例4.22查詢結(jié)果4.2.6嵌套查詢

嵌套查詢是基于多個關(guān)系的查詢，這類查詢所要求的結(jié)果出自一個關(guān)系，但相關(guān)條件卻涉及多個關(guān)系，這時就需要使用SQL的嵌套查詢功能。

所謂查詢的嵌套，就是在條件篩選，即WHERE子句中又包括了SELECT查詢。嵌套查詢常用的WHERE格式為

格式一：WHERE條件關(guān)系運算符[ANY|ALL|SOME](子查詢)

格式二：[NOT]EXISTS(子查詢)

關(guān)系運算符：除了可以使用在第二章介紹的關(guān)系運算符之外，還有本章介紹到的特殊運算符。

ANY，ALL，SOME是量詞，其中ANY和SOME是同義詞，在進行比較運算時，只要子查詢中有一條記錄能使結(jié)果為真。則結(jié)果就為真。而ALL則要求子查詢中的所有行都為真，結(jié)果才為真。

EXISTS是謂詞。EXISTS或NOTEXISTS是用來檢查子查詢中是否有結(jié)果返回，即子查詢的結(jié)果中存在記錄或不存在記錄。

1.使用IN操作符的嵌套查詢

例4.23

查詢哪些城市至少有一個倉庫的職工工資為1250元。

SELECT城市FROM倉庫；

WHERE倉庫號IN；

(SELECT倉庫號FROM職工；

WHERE工資=1250)

此例查詢結(jié)果中的城市信息屬于倉庫表，而查詢條件是職工表中的工資字段值。查詢中用到了倉庫和職工兩個表，查詢時先是子查詢在職工表中查詢工資為1250元職工所在倉庫的倉庫號，然后在倉庫表中用IN判斷出倉庫號包含在子查詢的結(jié)果中的倉庫所在的城市。我們把子查詢稱為內(nèi)層查詢塊，查詢結(jié)果如圖4.21所示。圖4.21例4.23查詢結(jié)果

例4.24

查詢所有的職工工資都多于1210元的倉庫的信息。

此要求也可描述為沒有一個職工的工資少于或等于1210元的倉庫的信息。依據(jù)此描述通常會把查詢命令寫為

SELECT*FROMckWHERE倉庫號NOTIN；

(SELECT倉庫號FROMzgWHERE工資<=1210)

查詢結(jié)果如圖4.22所示。觀察查詢的結(jié)果不難看出：“武漢”的“WH4”倉庫還沒有職工(查看表4.2職工表)，但該倉庫的信息也被檢索出來了。排除那些還沒有職工的倉庫，檢索要求描述為查詢所有的職工工資都多于1210元的倉庫的信息，并且該倉庫至少要有一名職工。正確的查詢命令應(yīng)是：

SELECT*FROMckWHERE倉庫號NOTIN;

(SELECT倉庫號FROMzgWHERE工資<=1210)AND倉庫號IN;

(SELECT倉庫號FROMzg)

正確的查詢結(jié)果如圖4.23所示。圖4.22例4.24錯誤的查詢結(jié)果圖4.23例4.24正確的查詢結(jié)果

2.使用比較符的嵌套查詢

例4.25

查詢出和職工E4掙同樣工資的所有職工。

SELECT職工號FROMzg

WHERE工資=；

(SELECT工資FROMzgWHERE職工號="E4")

查詢結(jié)果如圖4.24所示。用此方式查詢時，子查詢的結(jié)果只能有一個結(jié)果。圖4.24例4.25查詢結(jié)果

3.使用量詞的嵌套查詢

例4.26

在jbqk表中，檢索其他部門中比培訓(xùn)中心某一職工基本工資少的職工信息。

SELECT編號，姓名，部門，基本工資FROMd:\jbqk

WHERE基本工資<ANY

(SELECT基本工資FROMjbqkWHERE部門="培訓(xùn)中心")AND部門<>"培訓(xùn)中心"

該命令中的量詞可以用ANY，也可以使用SOME，查詢結(jié)果如圖4.25所示。此命令等效于

SELECT編號，姓名，部門，基本工資FROMd:\jbqk

WHERE基本工資<=；

(SELECTMAX(基本工資)FROMjbqkWHERE部門="培訓(xùn)中心")；

AND部門<>"培訓(xùn)中心"圖4.25例4.26查詢結(jié)果

例4.27

在jbqk表中，檢索其他部門中比培訓(xùn)中心所有職工基本工資都少的職工信息。

SELECT編號,姓名,部門,基本工資FROMd:\jbqkWHERE基本工資<ALL；

(SELECT基本工資FROMjbqkWHERE部門="培訓(xùn)中心")

AND部門<>"培訓(xùn)中心"

查詢結(jié)果如圖4.26所示。此命令等效于

SELECT編號，姓名，部門，基本工資FROMd:\jbqk

WHERE基本工資<=；

(SELECTMIN(基本工資)FROMjbqkWHERE部門="培訓(xùn)中心")；

AND部門<>"培訓(xùn)中心"圖4.26例4.27查詢結(jié)果

4.使用謂詞EXISTS的嵌套查詢

例4.28

檢索那些倉庫中還沒有職工信息的倉庫。

SELECT*FROMck;

WHERENOTEXISTS(SELECT*FROMzgWHERE倉庫號=ck.倉庫號)

注意：這里的內(nèi)層查詢引用了外層查詢的表，只有這樣使用謂詞EXISTS或NOTEXISTS才有意義。所以這類查詢都是內(nèi)外層相關(guān)的嵌套查詢。以上查詢等價于

SELECT*FROMckWHERE倉庫號NOTIN(SELECT倉庫號FROMzg)

查詢結(jié)果如圖4.27所示。

例4.29

檢索那些至少已經(jīng)有一個職工的倉庫的信息。

SELECT*FROMckWHEREEXISTS;

(SELECT*FROMzgWHERE倉庫號=ck.倉庫號)

以上查詢等價于

SELECT*FROMckWHERE倉庫號IN(SELECT倉庫號FROMzg)

查詢結(jié)果如圖4.28所示。圖4.27例4.28查詢結(jié)果

5.內(nèi)外層相關(guān)的嵌套查詢

前面所討論的嵌套查詢(使用謂詞EXISTS查詢除外)都是外層查詢依賴于內(nèi)層查詢的結(jié)果，而內(nèi)外層查詢無關(guān)。有時內(nèi)層查詢的條件需要外層查詢提供值，而外層查詢的條件需要內(nèi)層查詢的結(jié)果，這樣的嵌套查詢稱為內(nèi)外層相關(guān)的嵌套查詢。圖4.28例4.29查詢結(jié)果

例4.30

列出每個職工經(jīng)手的具有最高總金額的訂購單信息。

SELECTout.職工號，out.供應(yīng)商號，out.訂購單號，out.訂購日期，out.總金額；

FROMd:\dgdoutWHERE總金額=；

(SELECT

MAX(總金額)

FROM

dgd

inner1

WHEREout.職工號=inner1.職工號)

查詢結(jié)果如圖4.29所示。圖4.29例4.30查詢結(jié)果

例4.31

在訂購單表中，檢索只與供應(yīng)商S4有訂單而與供應(yīng)商S3無訂單的職工。

SELECT職工號FROMd:\dgdqWHERE供應(yīng)商號='S4'ANDNOTEXISTS；

(SELECT職工號FROMd:\dgdwWHEREq.職工號=w.職工號ANDw.供應(yīng)商號='S3')

查詢結(jié)果如圖4.30所示。圖4.30例4.31查詢結(jié)果4.2.7利用空值查詢

例4.32

查詢出尚未確定供應(yīng)商的訂購單。

在訂購單表中，還沒有確定的供應(yīng)商訂購單的供應(yīng)商號和訂購日期字段為空值?.NULL.。

SELECT*FROMdgdWHERE供應(yīng)商號ISNULL

查詢結(jié)果如圖4.31所示。圖4.31例4.32查詢結(jié)果

例4.33

列出已經(jīng)確定了供應(yīng)商的訂購單信息。

SELECT*FROM訂購單WHERE供應(yīng)商號ISNOTNULL

查詢結(jié)果如圖4.32所示。圖4.32例4.33查詢結(jié)果4.2.8集合的并運算

SQL支持集合的并(UNION)運算，即可以將兩個SELECT語句的查詢結(jié)果合并成一個查詢結(jié)果。當(dāng)然，要求進行并運算的兩個查詢結(jié)果具有相同的字段個數(shù)，并且對應(yīng)字段的值要具有相同的數(shù)據(jù)類型和取值范圍。

命令格式：SELECT命令1

UNION

[ALL]

SELCCT命令2

例4.34

在jbqk表中查詢在培訓(xùn)中心和軟件中心工作員工的信息。

SELECT*FROMckWHERE部門="培訓(xùn)中心";

UNION;

SELECT*FROMckWHERE部門="軟件中心"

查詢結(jié)果如圖4.33所示。圖4.33例4.34查詢結(jié)果4.2.9查詢輸出去向

在VFP中查詢的結(jié)果默認在瀏覽窗口中輸出，在實際應(yīng)用中我們通常需要將查詢的結(jié)果從其他途徑輸出。VFP提供了多種查詢結(jié)果的輸出方式，指定輸出方式可在SELECT命令中使用子句INTO，格式如下：

[INTO查詢?nèi)ハ騶TOFILE文件名[ADDITIVE]|TOPRINTER[PROMPT]|TOSCREEN]

查詢?nèi)ハ蚩梢允牵?/p>

(1)?ARRAY數(shù)組名：將查詢結(jié)果放在數(shù)組中。

(2)?INTOCURSOR文件名：將查詢結(jié)果放在臨時表文件中，該表為只讀?.dbf文件，當(dāng)關(guān)閉文件時該文件將自動刪除。

(3)?INTODBF|TABLE表文件名：將查詢結(jié)果放在永久表中。

(4)?TO

FILE文件名[ADDITIVE]：將查詢結(jié)果放在文本文件中，ADDITIVE選項使結(jié)果追加到原文件的尾部，否則將覆蓋原有文件。

(5)?TO

PRINTER

[PROMPT]：將查詢結(jié)果直接輸出到打印機，PROMPT選項將打開打印機設(shè)置對話框。

(6)?TOSCREEN：將結(jié)果在瀏覽窗口輸出。

下面通過實例說明如何指定查詢?nèi)ハ颉?/p>

1.將查詢結(jié)果存放在數(shù)組中

一般將存放查詢結(jié)果的數(shù)組作為二維數(shù)組使用，每一行對應(yīng)一條記錄，每列對應(yīng)查詢結(jié)果的一列。查詢結(jié)果放在數(shù)組中，可以很方便地在程序中使用。

例4.35

將下面的查詢結(jié)果存放到數(shù)組aa中。

SELECT*

FROMzgINTOARRAY

aa

2.將查詢結(jié)果存放在臨時文件中

臨時文件是一個只讀的?.dbf表文件，當(dāng)查詢結(jié)束后該臨時文件為當(dāng)前文件，可以像一般的?.dbf文件一樣使用但僅是只讀，關(guān)閉該文件將自動刪除。一般的臨時文件通常用于存放中間結(jié)果，使用完畢后這些臨時文件會自動刪除。如一些比較復(fù)雜的匯總可能需要分階段完成，需要根據(jù)幾個中間結(jié)果再匯總，利用此去向就非常合適。

例4.36

將下面的查詢結(jié)果存放到臨時文件ls中。

SELECT*FROM職工INTOCURSOR

ls

3.將查詢結(jié)果存放在永久表中

利用此去向可以創(chuàng)建一個新表，其內(nèi)容就是查詢的結(jié)果。

例4.37

將下面查詢結(jié)果存放到臨時文件ls中。

SELECT*TOP

3

FROMzg；

INTOCURSORlsORDERBY工資DESC

在此命令中同時使用了ORDERBY短語。

4.將查詢結(jié)果存放在文本文件中

使用命令SELECT*FROM

zgTO

FILEwb可以將查詢的結(jié)果寫入文本文件wb中。

5.將查詢結(jié)果直接輸出到打印機

使用命令SELECT*FROMzgTOPRINTER可以將查詢結(jié)果直接輸出到打印機。

SELECT命令的格式包括三個基本子句：SELECT子句、FROM子句和WHERE子句，還包括操作子句：ORDER子句、GROUP子句、UNION子句及其他一些選項，各子句的含義如表4.11所示。表4.11SELECT命令中各子句的含義 4.3定義功能

標準SQL的數(shù)據(jù)定義功能非常廣泛，一般包括數(shù)據(jù)庫的定義、表的定義、視圖的定義、存儲過程的定義、規(guī)則的定義和索引的定義等若干部分。在本節(jié)將主要介紹VisualFoxPro支持的表定義功能和視圖定義功能。

1.表結(jié)構(gòu)的定義

在第三章中已介紹了多種方法建立表，在VisualFoxPro中也可以通過SQL的CREATETABLE命令建立表。命令格式如下：表名為新建表指定表名。

NAME長表名為新建表指定一個長表名。

FREE建立的表是自由表，不加入到打開的數(shù)據(jù)庫中。當(dāng)沒有打開數(shù)據(jù)庫時，建立的表都是自由表。

字段名1類型[(字段寬度[，小數(shù)位數(shù)])]指定表中所含的字段名、字段類型、字段寬度及小數(shù)位數(shù)。字段類型可以用一個字符表示。

NULL允許該字段值為空。

NOTNULL該字段值不能為空。缺省值為NOTNULL。

CHECK邏輯表達式1指定該字段的字段有效性的規(guī)則。

ERROR字符型文本信息1指定在瀏覽或編輯窗口中該字段輸入的值不符合CHECK子句的合法值時，VFP顯示的錯誤信息。

DEFAULT表達式為該字段指定一個缺省值，表達式的數(shù)據(jù)類型與該字段的數(shù)據(jù)類型要一致。

PRIMARY

KEY為該字段創(chuàng)建一個主索引，索引標識名與字段名相同。

UNIQUE為該字段創(chuàng)建一個候選索引，索引標識名與字段名相同。注意這里的候選索引不是惟一索引。

REFERENCES表文件名[TAG標識名]指定建立永久關(guān)系的父表，同時以該字段為索引關(guān)鍵字建立外索引，用該字段名作為索引標識名。表名為父表表名，標識名為父表中的索引標識名。如果省略索引標識名，則用父表的主控索引關(guān)鍵字建立關(guān)系，否則不能省略。如果指定了索引標識名，則在父表中存在索引標識字段上建立關(guān)系。父表不能是自由表。

CHECK邏輯表達式2

[ERROR字符型文本信息2]由邏輯表達式指定表的合法值。不合法時，顯示由字符型文本信息指定的錯誤信息。該信息只有在瀏覽或編輯窗口中修改數(shù)據(jù)時顯示。

FROM

ARRAY數(shù)組名由數(shù)組創(chuàng)建表結(jié)構(gòu)。數(shù)組名指定數(shù)組包含表的每一個字段的字段名、字段類型、字段寬度及小數(shù)位數(shù)。

例4.38

在學(xué)生管理數(shù)據(jù)庫中建立：學(xué)生(學(xué)號，姓名，年齡，性別，入學(xué)時間)表。

OPEN

DATABASE學(xué)生管理

CREATE

TABLE學(xué)生(;

學(xué)號C(5)

UNIQUE，;

姓名C(8)

NULL，;

年齡N(2)

CHECK(年齡>10

AND年齡<25)ERROR"年齡應(yīng)大于10，小于25"，性別C(2)，;

入學(xué)時間D

DEFAULT｛^2006-09-01｝)時間型字段的寬度系統(tǒng)設(shè)定為8，因此可以省略

例4.39

建立數(shù)據(jù)庫表成績(學(xué)號，英語，法律，計算機基礎(chǔ))，并建立與例4.38中學(xué)生表的關(guān)聯(lián)。

CREATETABLE成績(;

學(xué)號C(5),;

英語N(6，2),;

法律N(6，2),;

計算機基礎(chǔ)(N6，2),;

FOREIGN

KEY學(xué)號TAG學(xué)號REFERENCES學(xué)生)

2.表的刪除

刪除表的SQL命令格式為

DROP

TABLE表名

DROP

TABLE直接從磁盤上刪除指定的?.dbf文件。如果要刪除的是數(shù)據(jù)庫表，只有其相應(yīng)的數(shù)據(jù)庫是當(dāng)前數(shù)據(jù)庫，才能從數(shù)據(jù)庫中刪除該表。否則即使從磁盤上刪除了?.dbf文件，但是記錄在數(shù)據(jù)庫中的?.dbf文件信息卻沒有刪除，此后會出現(xiàn)錯誤提示。所以要刪除數(shù)據(jù)庫中的表，應(yīng)使數(shù)據(jù)庫是當(dāng)前打開的數(shù)據(jù)庫，并在數(shù)據(jù)庫中進行操作。

3.表結(jié)構(gòu)的修改

用戶使用數(shù)據(jù)時，隨著應(yīng)用要求的改變，往往需要對原來的表格結(jié)構(gòu)進行修改，修改表結(jié)構(gòu)的SQL命令是ALTER

TABLE，該命令格式有三種。

格式一：這種格式的ALTER

TABLE命令可以為指定的表添加字段或修改已有的字段。其格式為

ALTER

TABLE表文件名ADD|ALTER

[COLUMN]

字段名1字段類型[(長度[，小數(shù)位數(shù)])][NULL|NOTNULL]

[CHECK邏輯表達式1

[ERROR字符型文本信息]][DEFAULT表達式1]

[PRIMARYKEY|UNIQUE][REFERENCES表文件名1

[TAG標識名1]]

表文件名指明被修改表的表名。

ADD[COLUMN]該子句指出新增加列的字段名及它們的數(shù)據(jù)類型等信息。

ALTER[COLUMN]該子句指出要修改列的字段名以及它們的數(shù)據(jù)類型等信息。在ALTER子句中，使用CHECK任選項時，需要被修改字段的已有數(shù)據(jù)滿足CHECK規(guī)則；使用PRIMARYKEY、UNIQUE任選項時，需要被修改字段的已有數(shù)據(jù)滿足惟一性，不能有重復(fù)值。

例4.40

為學(xué)生表增加一個數(shù)值型類型的“入學(xué)成績”字段。

ALTER

TABLE學(xué)生;

ADD入學(xué)成績CHECK入學(xué)成績>500

ERROR"入學(xué)成績應(yīng)在500分以上"

例4.41

將學(xué)生表的學(xué)號字段的寬度由原來的5改為6。

ALTER

TABLE學(xué)生ALTER學(xué)號C(6)

從以上格式可以看出，該格式可以修改字段的類型、寬度，有效性規(guī)則，錯誤性信息，默認值，也可以定義主關(guān)鍵字、聯(lián)系等，但是不能修改字段名，不能刪除字段，也不能刪除已經(jīng)定義的規(guī)則等。格式二：這種格式的ALTER

TABLE命令主要用于修改指定表中指定字段的DEFAULT和CHECK約束規(guī)則，不影響原有表的數(shù)據(jù)。此命令格式為

ALTERTABLE表文件名ALTER

[COLUMN]字段名[NULL|NOTNULL]

[SET

DEFAULT表達式]

[SET

CHECK邏輯表達式[ERROR字符型文本信息]]

[DROP

DEFAULT]

[DROP

CHECK]

表文件名指明被修改表的表名。

ALTER[COLUMN]字段名指出要修改列的字段名。

SET

DEFAULT表達式重新設(shè)置字段的缺省值。

SET

CHECK邏輯表達式[ERROR字符型文本信息]重新設(shè)置該字段的有效性規(guī)則，要求該字段的原有數(shù)據(jù)滿足合法值。

DROPDEFAULT刪除缺省值。

DROPCHECK刪除該字段的合法限定。

例4.42

將學(xué)生表的入學(xué)成績字段的有效性規(guī)則修改成：入學(xué)成績高于450。

ALTERTABLE成績；

ALTER入學(xué)成績SETCHECK入學(xué)成績>=450ERROR"應(yīng)該大于450"

例4.43

刪除學(xué)生表中入學(xué)成績字段的默認值。

ALTERTABLE學(xué)生ALTER入學(xué)成績DROP

DEFAULT格式三：這種格式的ALTER

TABLE命令，可以刪除指定表中的指定字段，修改字段名，修改指定表完整性規(guī)則，包括添加或刪除主索引、候選索引及表的合法值限定。其命令格式為

ALTER

TABLE表文件名

[DROP[COLUMN]字段名1]

[SET

CHECK邏輯表達式1

[ERROR字符型文本信息]]

[DROP

CHECK]

[ADD

PRIMARYKEY表達式1

TAG標識名1

[FOR邏輯表達式2]]

[DROP

PRIMARY

KEY]

[ADD

UNIQUE表達式2

[TAG標識名2

[FOR邏輯表達式3]]]

[DROP

UNIQUE

TAG標識名3]

[ADD

FOREIGNKEY[表達式3][TAG標識名4][FOR邏輯表達式4]

REFERENCES表文件名2[TAG標識名4]]

[DROP

FOREIGN

KEY

TAG標識名5

[SAVE]]

[RENAME

COLUMN原字段名TO新字段名]

[NOVALIDATE]

DROP[COLUMN]字段名從指定表中刪除指定的字段。

SET

CHECK邏輯表達式[ERROR字符型文本信息]為該表指定合法值及錯誤提示信息。

DROPCHECK刪除該表的合法值限定。

ADD

PRIMARY

KEY表達式TAG標識名為該表建立主索引。

DROP

PRIMARY

KEY刪除該表的主索引。

ADD

UNIQUE表達式[TAG標識名]為該表建立候選索引。

DROP

UIQUE

TAG標識名刪除該表的候選索引。

ADD

FOREIGN

KEY為該表建立非主索引，與指定的父表建立關(guān)系。

DROP

FOREIGN

KEYTAG標識名刪除外索引，取消與父表的關(guān)系，SAVE子句將保存該索引。

RENAME

COLUMN原字段名TO新字段名修改字段名。

NOVALIDATE修改表結(jié)構(gòu)時，允許違反該表的數(shù)據(jù)完整性規(guī)則，默認值為禁止違反數(shù)據(jù)完整性規(guī)則。

例4.44

將學(xué)生表中的學(xué)號字段的字段名改為“編號”。

ALTER

TABLE學(xué)生RENAME

COLUMN學(xué)號TO編號

例4.45

刪除學(xué)生表中的年齡字段。

ALTER

TABLE學(xué)生DROP

COLUMN年齡

例4.46

刪除學(xué)生中的候選索引xh。

ALTER

TABLE學(xué)生DROP

UNIQUE

TAGxh

4.視圖的定義

在VisualFoxPro中視圖是一個定制的虛擬表，可以是本地的、遠程的或帶參數(shù)的。視圖可以引用一個或多個表，也可以引用其他視圖。視圖是可更新的，它可以引用遠程表。

在關(guān)系數(shù)據(jù)庫中，視圖也稱作窗口，即視圖是操作表的窗口，可以把它看作是從表中派生出來的虛表。它依賴于表，但不獨立存在。本節(jié)將介紹如何利用SQL語言創(chuàng)建視圖。

視圖是根據(jù)對表的查詢定義的，其命令格式為

CREATE

VIEW視圖名[(字段名1[,字段名2]…)]AS查詢語句

查詢語句可以是任意的SELECT查詢語句，它說明和限定了視圖中的數(shù)據(jù)。如果沒有為視圖指定字段名，視圖中的字段名將與查詢語句中指定的字段名相同。

1)從單個表派生出視圖

例4.47

在職工表中定義視圖，使視圖中包含職工號和所工作的倉庫號字段。

OPEN

DATABASE倉庫管理

CREATEVIEWW1AS

SELECT職工號,倉庫號FROMzg

例4.48

在倉庫表中，查詢北京倉庫的信息，定義如下視圖。

CREATEVIEWW2AS；

SELECT*FROMzkWHERE城市="北京"

執(zhí)行上面兩條命令后的結(jié)果如圖4.34所示。從該圖可以看出，視圖一方面可以限定對數(shù)據(jù)的訪問，另一方面又可以簡化對數(shù)據(jù)的訪問。圖4.34視圖W1和W2

2)從多表派生出視圖

顧名思義，從多表派生視圖即所產(chǎn)生的視圖中的數(shù)據(jù)來自多個表。

例4.49

定義視圖，使視圖向用戶提供職工號、職工的工資和職工工作所在城市的信息。

CREATEVIEWW3AS;

SELECT職工號,工資,城市FROMzg,ck;

WHEREzg.倉庫號=ck.倉庫號

結(jié)果對用戶就好像有一個包含字段職工號、工資和所在城市的表。在數(shù)據(jù)設(shè)計器中打開視圖W3的瀏覽窗口，可以從圖4.35中看到此視圖的內(nèi)容。圖4.35視圖W3的內(nèi)容

例4.50

在訂購單表中，列出每個職工經(jīng)手的具有最高總金額的訂購單信息，定義視圖。

CREATEVIEWW4AS;

SELECTout.職工號,out.供應(yīng)商號,out.訂購單號,out.訂購日期,out.總金額;

FROMd:\dgdoutWHERE總金額=;

(SELECTMAX(總金額)FROMd:\dgdinner1;

WHEREout.職工號=inner1.職