第4章關系數據庫標準語言SQL

第4章關系數據庫標準語言SQL4.1SQL語言概述4.2SQL數據查詢功能4.3SQL數據操作功能4.4SQL數據定義功能4.1概述

SQL是結構化查詢語言StructuredQueryLanguage的縮寫。可以說查詢是SQL語言的重要組成部分，但不是全部，SQL還包含數據定義、數據操縱和數據控制功能等部分。

SQL已經成為關系數據庫的標準數據語言，所以現在所有的關系數據庫管理系統(tǒng)都支持SQL.

SQL語言具有如下主要特點：①SQL是一種一體化的語言，它包括了數據定義、數據查詢、數據操縱和數據控制等方面的功能，它可以完成數據庫活動中的全部工作。②SQL語言是一種高度非過程化的語言③SQL語言非常簡潔。雖然SQL語言功能很強，但它只有為數不多的幾條命令。④SQL語言可在命令窗口或程序中交互使用。4.1概述表4.1SQL命令動詞SQL功能作用命令動詞數據查詢查詢SELECT數據操縱插入INSERT更新UPDATE刪除DELETE數據定義新建CREATE刪除DROP修改ALTER4.2查詢功能

回顧:專門關系運算p13選擇：從關系中找出滿足給定條件的元組的操作稱為選擇。投影：從關系模式中指定若干個屬性組成新的關系稱為投影。連接：將兩個表按給定的連接條件，將第一個關系中的所有記錄逐個與第二個關系的所有記錄按條件進行連接(連接)，即選擇兩個關系在連接屬性上滿足條件的元組拼接成一個新的關系的運算。自然聯接：當連接屬性具有相同屬性名，連接條件取相等條件時，去掉重復屬性的等值連接稱為自然連接。

SQL的核心是查詢。

SQL的查詢命令也稱作SELECT命令，它的基本形式由SELECT-FROM-WHERE查詢塊組成，多個查詢塊可以嵌套執(zhí)行。即：select查詢什么from從哪來where查詢條件

VFP的SQLSELECT命令的語法格式如下：(見書p128)Select字段名1,字段名2,…from表名 where條件GROUP

BY短語用于對查詢結果進行分組，可以利用它進行分組匯總；HAVING短語必須跟隨GROUP

BY使用，它用來限定分組必須滿足的條件；ORDER

BY短語用來對查詢的結果進行排序。4.2.1簡單查詢基于單個表的查詢，可以有簡單的查詢條件。這樣的查詢由SELECT和FROM短語構成無條件查詢，或由SELECT、from、WHERE短語組成條件查詢。例4.1從職工表中檢索所有工資值。SELECT工資FROM職工SELECT職工號,工資FROM職工要顯示的字段名從此表名中找可以看到在結果中有重復值，如果要去掉重復值只需要指定DISTINCT短語：

SELECTDISTINCT

工資FROM職工

DISTINCT

短語的作用是去掉查詢結果中的重復值。例4.2檢索倉庫關系中的所有元組。

select*from倉庫*代表要顯示的所有字段例4.3檢索工資多于1230元的職工號。Select職工號from職工where工資＞1230WHERE短語指定了查詢條件，查詢條件可以是任意復雜的邏輯表達式。例4.4檢索哪些倉庫號有工資多于1210元的職工。SELECTDISTINCT倉庫號from職工WHERE工資＞1210例4.5給出在倉庫“WHl”或“WH2”工作，并且工資少于1250元的職工號。SELECT職工號FROM職工；WHERE工資<1250AND(倉庫號=“WH1”O(jiān)R倉庫號=“WH2”)

注意：這里的分號是續(xù)行符號，下同。4.2.2簡單的連接查詢

聯接是關系的基本操作之一，聯接查詢是一種基于多個關系的查詢。例4.6找出工資多于1230元的職工號和他們所在的城市。select職工號,城市

from職工,倉庫;where(工資>1230)and(職工.倉庫號=倉庫.倉庫號)表名表中字段如果在檢索命令的FROM之后有兩個關系，那么這兩個關系之間肯定有一種聯系，否則無法構成檢索表達式。從前面的討論可知，倉庫關系和職工關系之間存在著一個一對多的聯系。當FROM之后的多個關系中含有相同的屬性名時，這時必須用關系前綴直接指明屬性所屬的關系，如職工.倉庫號，“.”前面是關系名（表名），后面是屬性名（字段名）。例4.7找出工作在面積大于400的倉庫的職工號以及這些職工工作所在的城市。select職工號,城市from職工,倉庫;where(面積>400)and(職工.倉庫號=倉庫.倉庫號)4.2.3嵌套查詢基于多個關系的查詢，這類查詢所要求的結果出自一個關系，但相關的條件卻涉及多個關系。例4.8哪些城市至少有一個倉庫的職工工資為1250元?

這個例子要求查詢倉庫表中的城市信息，而查詢條件是職工表的工資字段值，為此可以使用如下的嵌套查詢：SELECT城市FROM倉庫WHERE倉庫號in;(SELECT倉庫號FROM職工WHERE工資=1250)這里IN相當于集合運算符∈例4.9查詢所有職工的工資都多于1210元的倉庫的信息。這個檢索要求也可以描述為：沒有一個職工的工資少于或等于1210元的倉庫的信息。這樣可以有SQL命令：SELECT*FROM倉庫WHERE倉庫號NOTIN;(SELECT倉庫號FROM職工WHERE;工資<=1210)不在以下范圍例4.10找出和職工E4同樣工資的所有職工。Select職工號FROM職工WHERE工資=;(SELECT工資FROM職工WHERE職工號="E4")4.2.4幾個特殊運算符BETWEEN…AND…（…和…之間）LIKE是字符串匹配運算符通配符“%”表示0個或多個字符“_”表示一個字符例4.11檢索工資在1220到1240元之間的職工。Select*from職工where工資between1220and1240例5.12從供應商表中檢索出全部公司的信息，不要工廠或其他供應商的信息。Select*from供應商where供應商名like"%公司"例4.13找出不在北京的全部供應商信息。Select*from供應商where地址!="北京"不等于，也可用not(地址=“北京”）

4.2.5排序使用SQLSELECT可以將查詢結果排序，排序的短語是ORDERBY，可以按升序(ASC)或降序(DESC)排序，允許按一列或多列排序。例4.14按職工的工資值升序檢索出全部職工信息。

SELECT*FROM職工ORDERBY工資字段名例4.15先按倉庫號排序，再按工資排序并輸出全部職工信息。SELECT*FROM職工ORDERBY倉庫號,工資

注意：ORDERBY是對最終的查詢結果進行排序，不可以在子查詢中使用該短語。4.2.6簡單的計算查詢

SQL不僅具有一般的檢索能力，而且還有計算方式的檢索。用于計算檢索的函數有：①COUNT—計數②SUM—求和②AVG—計算平均值④MAX—求最大值⑤MIN—求最小值這些函數可以用在SELECT短語中對查詢結果進行計算。例4.16找出供應商所在地的數目。SELECTCOUNT(DISTINCT地址)FROM供應商注意:除非對關系(表)中的元組(記錄)個數進行計數，一般COUNT函數應該使用DISTINCT。例4.17求支付的工資總數。

SELECTSUM(工資)FROM職工結果是：6160

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

SELECTSUM(DISTINCT工資)FROM職工將得出錯誤的結果4910。例4.18求北京和上海的倉庫職工的工資總和。SELESUM(工資)FROM職工WHERE倉庫號IN;(SELECT倉庫號FROM倉庫WHERE城市="北京"OR城市="上海"例4.19求所有職工的工資都多于1210元的倉庫的平均面積。SELECTAVG(面積)FROM倉庫WHERE倉庫號NOTIN;(SELECT倉庫號FROM職工WHERE工資<=1210)例4.20求在WH2倉庫工作的職工的最高工資值。

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

4.2.7分組與計算查詢利用GROUPBY子句進行分組計算查詢。GROUPBY短語的格式如下：

Groupby字段名having條件 可以按一列或多列分組，還可以用HAVING進一步限定分組的條件。例4.21求每個倉庫的職工的平均工資。SELECT倉庫號,AVG(工資)FROM職工GROUPBY倉庫號結果是：

WHll230WH21235WH31230

在這個查詢中，首先按倉庫號屬性進行分組，然后再計算每個倉庫的平均工資。例4.22求至少有兩個職工的每個倉庫的平均工資。SELECT倉庫號,COUNT(*),AVG(工資)FROM職工;GROUPBY倉庫號HAVINGCOUNT(*)>=2

結果是：

WHl21230WH221235HAVING子句總是跟在GROUPBY子句之后，不可以單獨使用。4.2.8利用空值查詢SQL支持空值，當然也可以利用空值進行查詢。假設在訂購單關系中，一名職工正在準備訂購單，但尚未選定供應商，這樣若把信息存入數據庫，則供應商號和訂購日期兩個屬性均為空值，在前面給出的訂購單記錄中有3個這樣的記錄。例4.23找出尚未確定供應商的訂購單。SELECT*FROM訂購單WHERE供應商號ISNULL

結果是：

E6NUILOR77NULIE1NULLOR80NUIJLE3NUIJLOR90NULL

注意：查詢空值時要使用ISNULL，而＝NULL是無效的，因為空值不是一個確定的值，所以不能用“＝”這樣的運算符進行比較。例4.24列出已經確定了供應商的訂購單信息。SELECT*FROM訂購單WHERE供應商號ISNOTNULL

結果是：

E3S7OR672001／06／23E1S4OR732001／07／28E7S4OR762001／05／25E3S4OR792001／06／13E3S3OR912001／07／134.2.9別名與自連接查詢 在連接操作中，經常需要使用關系名作前綴，有時這樣顯得很麻煩。因此，SQL允許在FROM短語中為關系名定義別名，格式為：＜關系名＞＜別名＞ 比如，如下的聯接語句是一個基于4個關系的聯接查詢，其中必須使用關系名作前綴：select供應商名from供應商,訂購單,職工,倉庫;where地址="北京"AND城市="北京";and供應商.供應商號=訂購單.供應商號;and訂購單.職工號=職工.職工號;and職工.倉庫號=倉庫.倉庫號 在上面的查詢中，如果使用別名就會簡單一些，如下是使用了別名的同樣的聯接查詢語句：select供應商名from供應商S,訂購單P,職工E,倉庫W;where地址="北京"AND城市="北京";andS.供應商號=P.供應商號;andP.職工號=E.職工號;andE.倉庫號=W.倉庫號4.2.10內外層互相關嵌套查詢例4.26列出每個職工經手的具有最高總金額的訂購單信息。SELECTout.職工號,out.供應商號,out.訂購單號,;out.訂購日期,out.總金額;FROM訂購單outWHERE總金額=;(SELECTMAX(總金額)FROM訂購單

innerl;WHEREout.職工號=innerl.職工號)在這個查詢中，外層查詢和內層查詢使用同一個關系，給它們分別指定別名out和innerl。 外層查詢提供out關系中每個元組的職工號值給內層查詢使用； 內層查詢利用這個職工號值，確定該職工經手的具有最高總金額的訂購單的總金額； 隨后外層查詢再根據out關系的同一元組的總金額值與該總金額值進行比較，如果相等，則該元組被選擇。

4.2.11使用量詞和謂詞的查詢子查詢有關的運算符，它們有以下兩種形式：<表達式><比較運算符>[ANY|ALL|SOME](子查詢) [NOT]EXISTS(子查詢)ANY、ALL和SOME是量詞，其中ANY和SOME是同義詞.EXISTS是謂詞，EXISTS或NOTEXISTS是用來檢查在子查詢中是否有結果返回，即存在元組或不存在元組。例4.27檢索那些倉庫中還沒有職工的倉庫的信息。這里的查詢是沒有職工或不存在職工，所以可以使用謂詞NOTEXISTS：SELECT*FROM倉庫WHERENOTEXISTS;(SELECT*FROM職工WHERE倉庫號=倉庫.倉庫號)以上的查詢等價于：SELECT*FROM倉庫WHERE倉庫號NOTIN;(SELECT倉庫號FROM職工)例4.28檢索那些倉庫中至少已經有一個職工的倉庫的信息。SELECT*FROM倉庫WHEREEXISTS;(SELE*FROM職工WHER倉庫號=倉庫.倉庫號)它等價于：SELECT*FROM倉庫WHERE倉庫號IN;(SELECT倉庫號FROM職工)注意：[NOT]EXISTS只是判斷子查詢中是否有或沒有結果返回，它本身并沒有任何運算或比較。例4.29檢索有職工的工資大于或等于WH1倉庫中任何一名職工工資的倉庫號。這個查詢可以使用ANY或SOME量詞。Any表示任何一個;some表示若干.SELECTDISTINCT倉庫號FROM職工WHERE工資>=ANY;(SELECT工資FROM職工WHER倉庫號="WH1")它等價于：SELECTDISTINCT倉庫號FROM職工WHER工資>=;(SELECTMIN(工資)FROM職工WHER倉庫號="WH1")ANY、ALL謂詞與函數及IN謂詞的等價轉換關系=<>或!=<<=>>=ANYIN<MAX<=MAX>MIN>=MINALLNOTIN<MIN<=MIN>MAX>=MAX5．2．12超聯接查詢在一般SQL中超聯接運算符是“*＝”和“=*”。其中“*＝”稱為左聯接，含義是在結果表中包含第一個表中滿足條件的所有記錄；如果有在聯接條件上匹配的元組，則第二個表返回相應值，否則第二個表返回空值；而“＝*”稱為右聯接，含義是在結果表中包含第二個表中滿足條件的所有記錄；如果有在聯接條件上匹配的元組，則第一個表返回相應值，否則第一個表返回空值。注意：VFP不支持超聯接運算符“*＝”和“＝*”VFP有專門的聯接運算語法格式，它支持超聯接查詢，聯接運算有關的語法格式如下：

SELECT……FROM表名INNER|LEFT|RIGHT|FULLJOIN表

ON聯接條件

WHERE…．其中：

INNERJOIN=JOIN,為普通的聯接,在VFP稱內部聯接。

LEFTJOIN為左聯接。

RIGHTJOIN為右聯接。

FULLJOIN可以稱為全聯接，即兩個表中的記錄不管是否滿足聯接條件將都在目標表或查詢結果中出現，不滿足聯接條件的記錄對應部分為NULL。例4.31:普通聯接，即只有滿足聯接條件的記錄才出現在查詢結果中。SELECT倉庫.倉庫號,城市,面積,職工號,工資;FROM倉庫JOIN職工;ON倉庫.倉庫號=職工.倉庫號如下兩種命令格式也是等價的：SELECT倉庫.倉庫號,城市,面積,職工號,工資;FROM倉庫INNERJoin職工;ON倉庫.倉庫號=職工.倉庫號和SELECT倉庫.倉庫號,城市,面積,職工號,工資;FROM倉庫,職工WHERE倉庫.倉庫號=職工.倉庫號例4.32左聯接，即除滿足連接條件的記錄出現在查詢結果中外，第一個表中不滿足聯接條件的記錄也出現在查詢結果中。SELECT倉庫.倉庫號,城市,面積,職工號,工資;FROM倉庫LEFTJOIN職工;ON倉庫.倉庫號=職工.倉庫號右聯接:除滿足連接條件的記錄出現在查詢結果中外，第二個表中不滿足聯接條件的記錄也出現在查詢結果中.全聯接:兩個表中不滿足聯接條件的記錄也出現在查詢結果中。注意：VFP的SQLSELECT語句的連接格式只能實現兩個表的連接，如果要實現多個表的連接，還需要使用標準格式。例如下面是一個基于4個關系的連接查詢：

SELECT倉庫.倉庫號,城市,供應商名,地址;FROM供應商,訂購單,職工,倉庫;WHERE供應商.供應商號=訂購單.供應商號;AND訂購單.職工號=職工.職工號;AND職工.倉庫號=倉庫.倉庫號4.2.13集合的并運算

SQL支持集合的并(UNION)運算，即可以將兩個SELECT語句的查詢結果通過并運算合并成一個查詢結果。為了進行并運算，要求這樣的兩個查詢結果具有相同的字段個數，并且對應字段的值要出自同一個值域，即具有相同的數據類型和取值范圍。

例如，如下語句的結果是城市為北京和上海的倉庫信息：SELECT*FROM倉庫WHERE城市="北京";UNION;SELECT*FROM倉庫WHERE城市="上海"

4.2.14VFP中SQLSELECT的幾個特殊選項1．顯示部分結果 有時只需要滿足條件的前幾個記錄，這時使用TOPnExpr[PERCENT]短語非常有用.

當不使用PERCENT時，nExpr

是l至32767間的整數，說明顯示前幾個記錄；當使用PERCENT時，nExpr是0.01至99.99間的實數，說明顯示結果中前百分之幾的記錄。需要注意的是TOP短語要與ORDERBY短語同時使用才有效。例4.35顯示工資最高的三位職工的信息。SELECT*TOP3FROM職工ORDERBY工資DESC例4.36顯示工資最低的那30％職工的信息。SELECT*TOP30PERCENTFROM職工ORDERBY工資2．將查詢結果存放到數組中可以使用“INTOARRAY數組名”短語將查詢結果存放到數組中。如將查詢到的職工信息存放在數組tmp中：

SELECT*FROM職工INTOARRAYtmptmp(1，1)存放的是第一條記錄的倉庫號字段值，tmp(1，3)存放的是第一條記錄的工資字段值等。3．將查詢結果存放在臨時文件中使用短語INTOCURSORcursorName可以將查詢結果存放到臨時數據表文件中.

其中cursorName是臨時文件名，該短語產生的臨時文件是一個只讀的dbf文件，當查詢結束后該臨時文件是當前文件，可以像一般的dbf文件一樣使用，但僅是只讀。當關閉文件時該文件將自動刪除。如將查詢到的職工信息存放在臨時dbf文件tmp中：。

SELECT*FROM職工INTOCURSORtmp4．將查詢結果存放到永久表中使用短語“INToDBF|TABLE表名”可以將查詢結果存放到永久表中(dbf文件)。比如將例4.36的查詢結果存放在表hh中可以使用如下語句：SELECT*TOP3FROM職工INTOTABLEhh;ORDERBY工資DESC

5．將查詢結果存放到文本文件中使用短語TOFILE文件名[ADDITIVE]可以將查詢結果存放到文本文件中.如將查詢結果以文本的形式存在hnp.txt中：SELECT*TOP3FROM職工TOFILEhnpORDERBY;工資DESC如果TO短語和INTO短語同時使用，則To短語將會被忽略。6．將查詢結果直接輸出到打印機使用短語TOPRINTER[PROMPT]可以直接將查詢結果輸出到打印機.

VFP在支持標準SQL方面還有一些限制和遺憾，例如不支持多層嵌套查詢、不支持COMPUTE短語等。4.3操作功能

SQL的操作功能是指對數據庫中數據的操作功能，主要包括數據的插入、更新和刪除三個方面的內容。4.3.1插入數據

VFP支持兩種SQL插入命令的格式，第一種格式是標準格式，第二種格式是VFP的特殊格式。第一種格式：

INSERTINTO表名[(字段1[，字段2…])]VALUES(記錄值1，記錄值2，…)例如:insertinto學生values(911111,"張三",21,"男")第二種格式：INSERTINTO表名FROMARRAY數組名|FROMMEMVAR其中：

FROMMEMVAR說明根據同名的內存變量來插入記錄值，如果同名的變量不存在，那么相應的字段為默認值或空。例如，往訂購單關系中插入元組：

(”E7”，”S4”，”O(jiān)R01“，2001／05／25)可用如下命令：INSERTINTO訂購單;VALUES("E7","S4","OR01",{^2001-05-25},1111)注意：這里日期型數據的表示方式。如果供應商號尚未確定，則先只能插入兩個屬性的值:

INSERTINTO訂購單(職工號,訂購單號);VALUES("E9","OR01")注意： 當一個表定義了主索引或候選索引后，由于相應的字段具有關鍵字的特性，即不能為空，所以只能用此命令插入記錄。FoxPro以前的插入命令(INSERT或APPEND)是先插入一條空記錄，然后再輸入各字段的值，由于關鍵字字段不允許為空，所以使用以前的方法就不能成功地插入記錄。

4.3.2更新

SQL的數據更新命令格式如下：

UPDATE表名

SET字段1=表達式1，字段2=表達式2，…WHERE條件

一般使用WHERE子句指定條件，以更新滿足條件的一些記錄的字段值，并且一次可以更新多個字段；如果不使用WHERE子句，則更新全部記錄。例如，給WH1倉庫的職工提高10％的工資的命令：

UPDATE職工SET工資=工資*1.10；

WHERE倉庫號=”WH1”

又如，給所有學生的年齡增加1歲：

UPDATE學生SET年齡=年齡+14.3.3刪除數據SQL從表中刪除數據的命令格式如下：

DELETEFROM表名[WHERE條件]這里FROM指定從哪個表中刪除數據，WHERE指定被刪除的記錄所滿足的條件，如果不使用WHERE子句，則刪除該表中的全部記錄。例如，要刪除倉庫關系中倉庫號值是WH2的元組，可用命令：

DELETEFROM倉庫WHERE倉庫號="WH2"

注意： 在VFP中SQLDELETE命令同樣是邏輯刪除記錄，如果要物理刪除記錄需要繼續(xù)使用PACK命令。4.4定義功能標準SQL的數據定義功能非常廣泛，一般包括 數據庫的定義 表的定義 視圖的定義 存儲過程的定義 規(guī)則的定義 索引的定義等若干部分。4.4.1表的定義通過SQL的CREATETABLE命令建立表，相應的命令格式是：CREATETABLE|DBF表名……簡化形式:

CREATETABLE表名

(字段名1,類型(長度),字段名2,類型(長度)....)例4.37用命令建立“訂貨管理1”數據庫。CREATEDATABASE訂貨管理1建立倉庫1表：CREATETABLE倉庫1(;倉庫號C(5)PRIMARYKEY,;城市C(10),;面積ICHECK(面積>0)ERROR"面積應該大于0!")

例4.38用SQLCREATE命令建立職工l表。CREATETABLE職工1(倉庫號C(5),;職工號C(5)PRIMARYKEY,;工資ICHECK(工資>=1000AND工資<=5000);ERROR"工資值的范圍在1000-50001"DEFAULT1200,;FOREIGNKEY倉庫號TAG倉庫號REFERENCES倉庫1)

例4.39用SQLCREATE命令建立供應商1表。CREATETABLE供應商1(;

供應商號C(5)PRIMARYKEY,供應商名c(20),地址c(20))用SQICREATE命令建立訂購單1表。CREATETABLE訂購單1(;職工號C(5),供應商號C(5),訂購單號C(5)PRIMARYKEY,訂購日期D,;FOREIGNKEY職工號TAG職工號REFERENCES職工1,;FOREIGNKEY供應商號TAG供應商號REFERENCES供應商1)4.4.2表的刪除刪除表的SQL命令是：DROPTABLE表名DROPTABLE直接從磁盤上刪除所對應的dbf文件。如果表是數據庫中的表并且相應的數據庫是當前數據庫，則從數據庫中刪除了表；否則雖然從磁盤上刪除了dbf