Visual FoxPro在SQL方面支持數據定義、數據查詢和數據操縱功能,

  SQL語言的核心是查詢。SQL語言的查詢命令也稱作SELECT命令,它的基本形式由

SELECT-FROM-WHERE查詢塊組成,多個查詢塊可以嵌套執行。Visual FoxPro的SQL-SELECT

命令的語法格式如下:

    SELECT[ALL | DISTINCT][TOP nExpr[PERCENT]]

    [Alias]Select_Item[AS Column_Name][,[Alias]Select_Item[AS Column-Name]…]

    FROM  [FORCE][DatabaseName!] Table [[As]  Local_Alias]

    [[INNER | LEFT[OUTER]| RIGHT[OUTER]| FULL[OUTER] JOIN

    DatabaseName!]Table[[AS]Local_Alias]

    [ON JoinCondition…]

-    [[INTO Destination]

    |[TO FILE FileName[ADDITIVE] |  TO PRINTER[PROMPT]|  TO SCREEN]]

    [WHERE JoinCondition [AND JoinCondition…]

    [AND | OR FilterCondition [AND | OR FilterCondition…]]]

    [GROUP BY GroupColumn[,GroupColumn…]]

    [HAVING FilterCondition]

    [UNION [ALL] SELECT Command]

    [ORDER BY Order_Item [ASC | DESC][,Order_Item [ASC | DESC]…]]

從SELECT的命令格式來看似乎非常復雜,實際上只要理解了命令中各個短語的含義,SQL

SELECT還是很容易掌握的,其中主要短語的含義如下:

  ·SELECT說明要查詢的數據；

  ·FROM說明要查詢的數據來自哪個(些)表,可以基于單個表或多個表進行查詢；

  ·WHERE說明查詢條件,即選擇元組的條件；

  ·GROUP BY短語用于對查詢結果進行分組,可以利用它進行分組匯總；

  ·HAVING短語必須跟隨GROUP BY使用,它用來限定分組必須滿足的條件；

  ·ORDER BY短語用來對查詢的結果進行排序。

  以上短語是學習和理解SQL SELECT命令必須要掌握的,還有一些短語是Visual FoxPro特有的。

  SELECT查詢命令的使用非常靈活,用它可以構造各種各樣的查詢。本節將通過大量的實

例來介紹SELECT命令的使用,在例子中再具體解釋各個短語的含義。

以下為訂貨倉庫數據庫:

倉庫表:

職工表:

訂購表

注:Null是空值,這里的意思是還沒有確定供應商,自然也沒有確定訂購日期

供應商表:

5.2.1簡單查詢

    首先從幾個最簡單的查詢開始,這些查詢基于單個表,查詢由SELECT和FROM短語構成

(無條件查詢)或由SELECT、FROM和WHERE短語構成(條件查詢)。

  例5.1從職工關系中檢索所有工資值。

    SELECT 工資 FROM 職工

  結果是:

    1220

    1210

    1250

    1230

    1250

  可以看到,在結果中有重復值,如果要去掉重復值只需要指定DISTINCT短語:

    SELECT DISTINCT 工資 FROM 職工

  DISTINCT短語的作用是去掉查詢結果中的重復值。

  例5.2檢索倉庫關系中的所有元組。

    SELECT  *  FROM 倉庫

  結果是:

    WH1  北京  370

    WH2  上海  500

    WH3  廣州  200

    WH4  武漢  400

  其中"*"是通配符,表示所有屬性(字段),這里的命令等同于:

    SELECT 倉庫號,城市,面積 FROM 倉庫

  例5.3 檢索工資多于1230元的職工號。

    SELECT 職工號 FROM 職工 WHERE 工資>1230

  結果是:

    E4

    E7

  這里用WHERE短語指定了查詢條件,查詢條件可以是任意復雜的邏輯表達式。

  例5.4檢索哪些倉庫有工資多于1210元的職工。

SELECT DISTINCT 倉庫號 FROM 職工 WHERE 工資>1210

  結果是:

    WH1 

    WH2

    WH3

  在這個例子中,顯然我們只對惟一的倉庫號感興趣。但在例5.1中我們也許對所有的工資值都感興趣,因此不管是否有重復值。決定是否消除重復值是有一定實際意義的,也是SQL的重要一面。再回到例5.1,假設我們對所有職工的平均工資感興趣,那么指定DISTINCT顯然是錯誤的,我們將在計算檢索中再來討論這個問題。

  例5.5 給出在倉庫"WHl"或"WH2"工作并且工資少于1250元的職工號。

    SELECT 職工號 FROM 職工;

      WHERE 工資<1250  AND  (倉庫號="WHl" OR 倉庫號="WH2")

  結果是:  

    SELECT 職工號,城市 FROM 職工,倉厙;

      WHERE  (工資>1230)  AND (職工.倉庫號=倉庫.倉庫號)

  結果是:

    E4  上海

    E7  北京

  這里的"職工.倉庫號=倉庫.倉庫號"是連接條件。

  如果在檢索命令的FROM之后有兩個關系,那么這兩個關系之間肯定有一種聯系(否則無法構成檢索表達式)。從前面的討論已經知道,倉庫關系和職工關系之間存在著一個一對多的聯系。

  當FROM之后的多個關系中含有相同的屬性名時,這時必須用關系前綴指明屬性所屬的關系,如職工.倉庫號,"."前面是關系名,后面是屬性名

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

    select 職工號,城市 from 倉庫,職工;

      Where  (面積>400) and  (職工.倉庫號=倉庫.倉庫號)

  結果是:

    E1  上海

    E4  上海

5.2.3嵌套查詢

    接著來討論另一類基于多個關系的查詢,這類查詢所要求的結果出自一個關系,但相關的條件卻涉及多個關系。在5.2.2節的例子中,WHERE之后是一個相對獨立的參件,這個條件或者為真,或者為假。但是,有時我們需要用另外的方式來表達檢索要求,例如,當檢索關系x中的元組時,它的條件依賴于相關的關系y中的元組的屬性值,這時使用SQL的嵌套查詢功能將非常方便。下面就來看幾個例子。

    例5.8哪些城市至少有一個倉庫的職工的工資為1250元?

    這個例子要求查詢倉庫表中的城市信息,而查詢條件是職工表的工資字段值,為此可以使用

如下的嵌套查詢:

    SELECT 城市 FROM 倉庫 WHERE 倉庫號 IN;

      (SELECT 倉庫號 FROM 職工WHERE 工資=1250)

.  結果是:

  .    北京

       上海

    我們看到,在這個命令中含有兩個SELECT—FROM—WHERE查詢塊,即內層查詢塊和外層

查詢塊,內層查詢塊檢索到的倉庫號值是WH1和WH2,這樣就可以寫出等價的命令:

    SELECT 城市 FROM 倉庫 WHERE 倉庫號 IN ("WH1","WH2")

    這里IN相當于集合運算符∈。 

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

    這個檢索要求也可以描述為:沒有一個職工的工資少于或等于1210元的倉庫的信息

    這樣可以有SQL命令:

    SELECT  *  FROM 倉庫 WHERE 倉庫號 NOT IN;

       (SELECT 倉庫號 FROM 職工 WHERE 工資<=1210)

    結果是:

    WH2  上海  500

    WH3  廣州  200

    WH4  武漢  400

    內層SELECT—FROM—WHERE查詢塊指出所有職工的工資少于或等于1210元的倉庫的

倉庫號值的集合,在這里該集合只有一個值"WH1";然后再從倉庫關系中檢索元組的倉庫號屬性值不在該集合中的每個元組。

    有的讀者也許已經注意到剛才的檢索出現了錯誤,盡管在"武漢"的"WH4"倉庫還沒有職工,但該倉庫的信息也被檢索出來了。所以我們必須認真分析檢索要求,寫出正確的

    如果我們的檢索要求要排除那些還沒有職工的倉庫,檢索要求可以敘述為:檢索所有職工的工資都多于1210元的倉庫的信息,并且該倉庫至少要有一名職工。這樣描述就很清楚了,因為我們對沒有職工的倉庫不感興趣。這樣,寫出的SQL命令也就復雜一些了:

    SELECT * FROM 倉庫 WHERE 倉庫號 NOT IN;

     (SELECT 倉庫號 FROM 職工 WHERE 工資<=1210);

      AND 倉庫號 IN (SELECT 倉庫號 FROM 職工)

  這樣,內層是兩個并列的查詢,在結果中將不包含沒有職工的倉庫信息。

  例5.10找出和職工E4掙同樣工資的所有職工。

    SELECT 職工號 FROM 職工 WHERE工資=;

    (SELECT 工資 FROM 職工 WHERE 職工號="E4")

  結果是:

    E4   

    E7   

5.2.4幾個特殊的運算符

  下面有幾個特殊的運算符,它們是Between... And和Like等,下面通過例子來解釋

  例5.11 檢索出工資在1220元到1240元范圍內的職工信息

  這個查詢的條件是在什么范圍之內,顯然可以使用Between...And,謂詞有如下查詢語句

    Select * from 職工 where 工資 Between 1220 and 1240

  結果是:

    WH2  E1  1220

    WH3  E6  1230

  這里Between...And的意思是在"...和...之間",這個查詢的條件等價于:

    (工資>=1220) And(工資<=1240)

  顯然使用Between...And表達條件更清晰、更簡潔

  例5.12從供應商關系中檢索出全部公司的信息(不要工廠或其他供應商的信息)。

  這是一個字符串匹配的查詢,顯然應該使用LIKE運算符:

    SELECT * FROM 供應商 WHERE 供應商名 LIKE "%公司"

  結果是: 

    s4  華通電子公司  北京

  這里的LIKE是字符串匹配運算符,通配符"%"表示0個或多個字符,另外還有一個通配符

"-"(下劃線)表示一個字符。

  例5.13找出不在北京的全部供應商信息。

    SELECT * FROM 供應商 WHERE 地址!="北京"

  結果是:

    s3  振華電子廠  西安

    S6  607廠       鄭州 

    在SQL中,"不等于"用"!="表示。另外還可以用否定運算符NOT寫出等價的命令:

    SELECT * FROM 供應商 WHERE NOT(地址="北京")

    NOT的應用范圍很廣,例如,可以有NOT IN(見例5.9)、NOT  BETWEEN等。假如提出

和例5.11相反的請求,找出工資不在1220元和1240元之間的全部職工信息,可以用命令

    SELECT * FROM 職工 WHERE 工資 NOT BETWEEN 1220 AND 1240

    結果是:

    WH1  E3  1210

    WH2  E4  1250

    WH1  E7  1250   

5.2.5排序

    使用SQL SELECT可以將查詢結果排序,排序的短語是ORDER BY,格式如下:

    ORDER BY Order_Item [ASC | DESC][,Order_Item [ASC | DESC]...]

    可以看出,可以按升序(ASC)或降序(DESC)排序,可以按一列或多列排序。下面是兩個查

詢結果排序的例子。

    例5.14按職工的工資值升序檢索出全部職工信息。

    SELECT * FROM 職工 ORDER BY 工資

    結果是:

    WH1    E3    1210

    WH2    E1    1220

    WH3    E6    1230

    WH2    E4    1250

    WHl    E7    1250

    這里ORDER BY是排序子句,如果要將結果按降序排列,只要加上DESC:

    SELECT  *  FROM 職工 ORDER BY 工資 DESC

    例5.15先按倉庫號排序,再按工資排序輸出全部職工信息。

    SELECT  *  FROM 職工 ORDER BY 倉庫號,工資

    結果是:

    WH1    E3    1210

    WH1    E7    1250

    WH2    E1    1220

    WH2    E4    1250

    WH3    E6    1230

    這是一個按多列排序的例子。

    注意:ORDER BY是對最終的查詢結果進行排序,不可以在子查詢中使用該短語。

5.2.6簡單的計算查詢

    SQL語言是完備的,也就是說,只要數據是按關系方式存人數據庫的,就能構造合適的SQL

命令把它檢索出來。事實上,SQL不僅具有一般的檢索能力,而且還有計算方式的檢索,例如檢索職工的平均工資、檢索某個倉庫中職工的最高工資值等。用于計算檢索的函數有:COUNT(計數)、SUM(求和)、AVG(計算平均值)、MAX(求最大值)、MIN(求最小值)。

  這些函數可以用在SELECT短語中對查詢結果進行計算。

  例5.16找出供應商所在地的數目。    .

    SELECT COUNT(DISTINCT 地址)  FROM 供應商

  參見前面給出的供應商的記錄值,其中共有3個地址:北京、西安和鄭州,所以結果為3。注意,除非對關系中的元組個數進行計數,一般COUNT函數應該使用DISTINCT。

    SELECT COUNT(*) FROM 供應商

將給出供應商關系中的記錄數。

  例5.17求支付的工資總數。

    SELECT SUM(工資) FROM 職工

  結果是:

      6160

 這個結果是職工關系中的工資值的總和,它并不管是否有重復值,這時若使用命令:

    Select Sum(Distinct 工資) From 職工

將得出錯誤的結果是4910

例5.18  求北京和上海的倉庫職工的工資總和

    Select Sum(工資) From 職工 Where 倉庫號 in;

      (select 倉庫號 from 倉庫 where 城市="北京" or 城市="上海")

結果是:

   4930

例5.19 求所有職工的工資都多于1210元的倉庫的平均面積

    Select Avg(面積) From 倉庫 Where 倉庫號 Not In;

       (Select 倉庫號 From 職工 Where 工資<=1210)

    結果是:

     366.67

    這里要注意,以上結果的運算包含了尚沒有職工WH4倉庫,如果要排除沒有職工的倉庫,以上語句應該改為:

    SELECT AVG(面積) FROM 倉庫 WHERE 倉庫號 NOT IN;

      (SELECT 倉庫號 FROM 職工 WHERE 工資<=1210);

    AND 倉庫號 IN (SELECT 倉庫號 FROM 職工)

  結果是:

    350

例5.20 求在WH2倉庫工作的職工的最高工資值。

    SELECT MAX(工資) FROM 職工 WHERE 倉庫號="WH2"

  結果是:

    1250

  與MAX函數相對應的是MIN函數(求最小值)。比如,求最低工資值可以有如下命令:

    SELECT MIN(工資) FROM 職工 WHERE 倉庫號="WH2"

5.2.7分組與計算查詢

    5.2.6節的幾個例子是針對整個關系的計算查詢,而利用GROUP BY子句進行分組計算查

詢使用的更加廣泛。GROUP BY短語的格式如下:

    GROUP BY GroupColumn[,GroupGolumn…][HAVING FilterCondition]

    可以按一列或多列分組,還可以用HAVING進一步限定分組的條件。下面是幾個分組計算

查詢的例子。

    例5.21求每個倉庫的職工的平均工資。

    SELECT 倉庫號,AVG(工資) FROM 職工;

      GROUP BY倉庫號

    結果是:

    WH1    1230

    WH2    1235

    WH3    1230

    在這個查詢中,首先按倉庫號屬性進行分組,然后再計算每個倉庫的平均工資。Group By

子句一般跟在WHERE子句之后,沒有WHERE子句時,跟在FROM子句之后。另外,還可以根

據多個屬性進行分組。

   在分組查詢時,有時要求分組滿足某個條件時才檢索,這時可以用HAVING子句來限定

分組。

    例5.22 求至少有兩個職工的每個倉庫的平均工資。

    SELECT 倉庫號,COUNT(*),AVG(工資) FROM 職工;

      GROUP BY 倉庫號 HAVING COUNT(*)>=2

    結果是:

    WH1  2    1230

    WH2  2    1235

    HAVING子句總是跟在GROUP BY子句之后,而不可以單獨使用。HAVING子句和

WHERE子句不矛盾,在查詢中是先用WHERE子句限定元組,然后進行分組,最后再用Having

子句限定分組。

5.2.8利用空值查詢

    SQL支持空值,當然也可以利用空值進行查詢。

    假設在訂購單關系中,一名職工正在準備訂購單,但尚未選定供應商,這樣若把信息

據庫,則供應商號和訂購日期兩個屬性均為空值,在前面給出的訂購單記錄中有3條這樣的

記錄。

    例5.23  找出尚未確定供應商的訂購單。

    SELECT * FROM 訂購單 WHERE 供應商號 IS NULL

    結果是:

    E6    NULL    OR77    NULL

    E1    NULL    OR80    NULL

E3    NULL    OR90    NULL

注意:查詢空值時要使用IS NULL,而=NULL是無效的,因為空值不是一個確定的值,所以不要用”=”這樣的運算符進行比較

例:5.24列出已經確定了供應商的訂購單的信息

Select * From 訂購單 Where 供應商號 Is Not Null

    在新的SQL標準中還支持兩個新的關系連接運算符,它們與原來我們所了解的等值連接和

自然連接不同。原來的連接是只有滿足連接條件,相應的結果才會出現在結果表中；而這兩個新

的連接運算是,首先保證一個表中滿足條件的元組都在結果表中,然后將滿足連接條件的元組與

另一個表的元組進行連接,不滿足連接條件的則應將來自另一表的屬性值置為空值。

5.2超連接查詢

  Visual FoxPro有專門的連接運算語法格式,它支持超連接查詢,我們從本節開始給出的SQL SELECT語句的完整語法格式中抽出的與連接運算有關的語法格式如下:

    SELECT…

    FROM Table INNER | LEFT | RIGHT | FULL JOIN Table

      ON JoinCondition

    WHERE ...

其中:

  •INNER JOIN等價于JOIN,為普通連接;

   Left   join為左連接

   Right  join為右連接

   Full   join為完全連接,即兩個表中的記錄不管是否滿足連接條件將都在目標表或

查詢結果中出現,不滿足連接條件的記錄的對應部分為Full

   On  指定連接條件

例5.31  普通連接(即只有滿足連接條件的記錄才出現在查詢結果中)

     Select 倉庫.倉庫號,城市,面積,職工號,工資;

       From 倉庫 Join 職工;

        ON倉庫.倉庫號=職工.倉庫號

 和

    SELECT 倉庫.倉庫號,城市,面積,職工號,工資;

    FROM倉庫,職工 WHERE 倉庫.倉庫號=職工.倉庫號

  以上三種連接語句的結果都是:

    WH2  上海  500  E1  1220

    WH1  北京  370  E3  1210

    WH2  上海  500  E4  1250

    WH3  廣州  200  E6  1230

    WH1  北京  370  E7  1250

  例5.32左連接(即除滿足連接條件的記錄出現在查詢結果中外,第一個表中不滿足連接

條件的記錄也出現在查詢結果中)。

    SELECT 倉庫.倉庫號,城市,面積,職工號,工資;

      FROM  倉庫  LEFT JOIN  職工;

       ON 倉庫.倉庫號=職工.倉庫號

  結果是:

    WH1  北京  370  E3  1210

    WH1  北京  370  E7  1250

    WH2  上海  500  E1  1220

    WH2  上海  500  E4  1250

    WH3  廣州  200  E6  1230

    WH4  武漢  400  NULL NULL

    為了看到右連接和全連接的效果,假設在職工表中插入了如下一條記錄:

    "WH8","E8",1200

    例5.33  右連接(即除滿足連接條件的記錄出現在查詢結果中外,第二個表中不滿足

條件的記錄也出現在查詢結果中)。

    SELECT 倉庫.倉庫號,城市,面積,職工號,工資;

    FROM 倉庫 RIGHT JOIN 職工;

      ON倉庫.倉庫號=職工.倉庫號

    結果是:

    WH2  上海  500  E1  1220

    WH1  北京  370  E3  1210  

    WH2  上海  500  E4  1250

    WH3  廣州  200  E6  1230

    WH1  北京  370  E7  1250

    NULL NULL  NULL E8  1200

    實際上職工"E8"所在的倉庫并不存在,這在實際應用中是不允許的。

    例5.34全連接(即除滿足連接條件的記錄出現在查詢結果中外,兩個表中不滿足連接條

件的記錄也出現在查詢結果中)。

    SELECT 倉庫.倉庫號,城市,面積,職工號,工資;

    FROM 倉庫 FULL JOIN 職工;

      ON  倉庫.倉庫號=職工.倉庫號

    結果是:

    WH1  北京  370  E3  1210

    WHl  北京  370  E7  1250

    WH2  上海  500  E1  1220

    WH2  上海  500  E4  1250

    WH3  廣州  200  E6  1230

    WH4  武漢  400  NULL NULL

    NULL NULL  NULL E8  1200

    注意:JOIN連接格式在連接多個表時的書寫方法要特別注意,在這種格式中JOIN

 ON的順序是很重要的,特別要注意JOIN的順序要和ON的順序(相應的連接條件)正好相反

5.2.13集合的并運算

  SQL支持集合的并(UNION)運算，即可以將兩個SELECT語句的查詢結果通過并運算合并

成一個查詢結果。為了進行并運算，要求兩個查詢結果具有相同的字段個數，并且對應字段的值

要出自同一個值域(相同的數據類型和取值范圍)

  例如,如下語句的結果是城市為北京和上海的倉庫信息:

    Select * from 倉庫 Where 城市="北京";

    Union;

    Select * from 倉庫 Where 城市="上海"

5.2.14 Visual Foxpro 的幾個特殊選項

1.只顯示前幾項紀錄

  有時人們只需要滿足條件的前幾個記錄,這時使用Top短語比較合適

例4.35  顯示工資最高的3位職工的信息

   Select * Top 3 From 職工 Order by 工資 DESC

 結果是:

    WH2  E4  1250

    WH1  E7  1250

    WH3  E6  1230

例5.36顯示工資最低的那30%職工的信息。

    SELECT * TOP 30 PERCENT FROM 職工 ORDER BY 工資

結果是：

    WH5  E8    1200

    WH1  E3    1210

2.將查詢結果存放到數組中

可以使用INTO ARRAY ArrayName短語將查詢結果存放到數組中，ArrayName可以是任意的

數組變量名。一般將存放查詢結果的數組作為二維數組來使用，每行一條記錄，每列對應于查詢

結果的一列。查詢結果存放在數組中，可以非常方便地在程序中使用。

如下語句將查詢到的職工信息存放在數組tmp中:

    Select * from 職工 Into Array tmp

   tmp(1,1)存放的是第1條記錄的倉庫號字段值,tmp(1,3)存放的是第1條記錄的工資字段

值等。 

    3.將查詢結果存放在臨時文件中

    使用短語INTO CURSOR CursName可以將查詢結果存放到臨時數據庫文件,其中

CursorName是臨時文件名,該短語產生的臨時文件是一個只讀的.dbf文件,當查詢結束后該臨時

文件是當前文件,可以像一般的.dbf文件一樣使用(當然是只讀),當關閉文件時該文件將自動

刪除。   

    如下語句將查詢到的職工信息存放在臨時.dbf文件tmp中:

    SELECT * FROM 職工 INTO CURSOR tmp

    一般利用INTO CURSOR短語存放一些臨時結果,比如一些復雜的匯總可能需要分階段完

成,需要根據幾個中間結果再匯總等,這時利用該短語存放中間結果就非常合適,使用完后這些

臨時文件會自動刪除。

    4.將查詢結果存放到永久表中

    使用短語INTO DBF | TABLE TableName可以將查詢結果存放到永久表中(.dbf文件),例

如將例5.35的查詢結果存放在表highsal中可以使用如下語句:

    SELECT * TOP 3 FROM 職工 INTO TABLE highsal ORDER BY 工資 DESC

  5.將查詢結果存放到文本文件中

    使用短語TO FILE FileName [ADDITIVE]可以將查詢結果存放到文本文件中,其中

FileName給出了文本文件名(默認擴展名是.TXT),如果使用ADDITIVE則結果將追加在原文件

的尾部,否則將覆蓋原有文件。

    如下語句將查詢結果以文本的形式存儲在文本文件tmp.txt中:

    SELECT * TOP 3  FROM 職工 TO FILE tmp ORDER BY工資 DESC

    如果TO短語和INTO短語同時使用,則TO短語將會被忽略。

    6.將查詢結果直接輸出到打印機

    使用短語TO PRINTER[PROMPT]可以直接將查詢結果輸出到打印機,如界使用了

PROMPT選項,在開始打印之前會打開打印機設置對話框。

    以上用大量的實例介紹了SQL SELECT語句的使用方法,這些實例都可以在Visual Foxpro下執行。掌握SQL SELECT語句不僅對學好、用好Visual FoxPro至關重要,也是以后使用其他數據庫或開發數據庫應用程序的基礎。