第七章結(jié)構(gòu)化查詢語言-SQL

第七章結(jié)構(gòu)化查詢語言—SQL

第一節(jié)SQL概述

第二節(jié)SQL定義功能

第三節(jié)SQL操縱功能約

第四節(jié)SQL查詢功能1第一節(jié)SQL概述

SQL是結(jié)構(gòu)化查詢語言(StructuredQueryLanguage)的縮寫。首先由美國ANSI(美國國家標(biāo)準(zhǔn)協(xié)會)提出，后被ISO采納為國際標(biāo)準(zhǔn)。一、

SQL語言發(fā)展簡史二、SQL語言的特點(diǎn)及分類是一種一體化的語言。它包括了數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱和數(shù)據(jù)控制等功能。數(shù)據(jù)定義

CREATE、ALTER、DROP等數(shù)據(jù)查詢

SELECT

數(shù)據(jù)操縱

INSERT、UPTATE、DELETE等由于VFP在數(shù)據(jù)安全性方面的欠缺，不支持SQL的數(shù)據(jù)控制功能。(2)是一種高度過程化的語言。(3)非常簡潔，但功能很強(qiáng)。(4)可以直接以命令方式交互使用，又可以嵌入程序設(shè)計(jì)語言中以程序方式使用。

返回2第二節(jié)SQL的定義功能

一、定義表的結(jié)構(gòu)格式：

CREATETABLE<表名>(字段屬性描述)

該命令直接創(chuàng)建指定名稱的表的結(jié)構(gòu)，各字段的名稱、類型、寬度等與括號中的描述相同。如果此時有打開的數(shù)據(jù)庫，則建立的是數(shù)據(jù)庫表，否則是自由表。【例7.1】用SQL命令創(chuàng)建倉庫表（CKB.DBF）的結(jié)構(gòu)，要求如下。

CREATETABLECkB(CKHC(6),CKMC(10),CHFL,MJN(3,0))

返回字段名數(shù)據(jù)類型寬度小數(shù)位備注CKH字符型6倉庫號CKM字符型10倉庫名CHF邏輯型存貨否MJ數(shù)值型30面積3

二、修改表的結(jié)構(gòu)

用ALTERTABLE命令可以修改表的結(jié)構(gòu)并且可以指定數(shù)據(jù)的完整性控制信息。該命令包括四種具體格式，分別可以完成不同的修改操作。（1）追加新字段

ALTER

TABLE<表名>ADD<字段名及屬性信息>（2）修改原字段的屬性

ALTER

TABLE<表名>ALTER<字段名及屬性信息>（3）刪除字段

ALTER

TABLE<表名>DROP<字段名>（4）字段更名

ALTER

TABLE<表名>RENAME<原字段名>TO<新字段名>【例7.2】為CKB表添加一個管理員（GLY）字段，類型為字符型，寬度位為8。ALTERTABLECKBADDGLYC(8)返回4【例7.3】將CKB表的管理員（GLY）字段的寬度修改為10。ALTERTABLECKBALTERGLYC(10)【例7.4】將CKB表的管理員（GLY）字段名更改為“負(fù)責(zé)人（FZR）”。ALTERTABLECKBRENAMEGLYTOFZR三、表的刪除

格式：DROPTABLE<表名>說明：直接從磁盤上刪除指定的表。若是數(shù)據(jù)庫表，則必須先打開數(shù)據(jù)庫。

【例7.5】刪除CKB表DROPTABLECKB返回5主要包括數(shù)據(jù)的插入（INSERT）、刪除（DELETE）、更新（UPDATE）三種功能。一、數(shù)據(jù)插入SQL的INSERT命令用于向指定的表中追加一條記錄，無論該表是否已經(jīng)打開，也不必從鍵盤輸入數(shù)據(jù)，而是直接把數(shù)據(jù)寫在命令中。該命令動詞是INSERT，因此也叫“插入命令”。格式：

Insertinto<表名>[(字段1，字段2，…)];Values(表達(dá)式1，表達(dá)式2，…)

若只插入部分字段，必須在表名后用括號依次指明所插入的字段名，且與VALUES后括號中表達(dá)式的值一一對應(yīng)。當(dāng)插入整條記錄時，則不必指出字段名，但VALUES后括號中表達(dá)式的值，必須依次為表內(nèi)所有字段的值。返回第三節(jié)SQL的操縱功能6【例7.7】向XSQK表插入一條記錄，學(xué)號、姓名、班級分別是“0801100204”，“王一”和“流體力學(xué)”這三個值。

INSERTINTOXSQK(xh,xm,bj)VALUES("0801100204","王一","流體力學(xué)")【例7.8】向CJ表插入一條記錄，“xh”、“kcdm”、“cj”分別為“0801100204”、“150104”、86。

INSERTINTOCJVALUES("0801100204"，"150104",86)

可以“瀏覽”方式查看插入結(jié)果。

返回7二、刪除數(shù)據(jù)格式：

DELETEFROM<表名>[WHERE<條件>]

功能：對指定表中滿足條件的記錄做刪除標(biāo)記。如果不指定條件，則對所有記錄添加刪除標(biāo)記。說明：此命令無須事先打開表，注意條件子句用WHERE?！纠?.9】將XSQK表中年齡大于25歲的女生進(jìn)行邏輯刪除。解：

DELETEFROMXSQK;WHERE(YEAR(DATE())-YEAR(CSRQ)>25)ANDXB='女'返回8三、更新命令格式：

UPDATE<表名>SET字段名1=<表達(dá)式1>[,字段名2=<表達(dá)式2>…][WHERE<條件>]

功能：對指定表中滿足條件的記錄，用指定表達(dá)式的值更新指定字段的值。說明：如果不指定條件,該命令默認(rèn)的范圍為所有記錄?！纠?.10】將XSQK表中所有學(xué)生的入學(xué)成績都提高5分。UPDATEXSQKSETRXCJ=RXCJ+5【例7.11】將XSQK表中1990年及其以后出生的所有學(xué)生的入學(xué)成績都提高10分。UPDATEXSQKSETRXCJ=RXCJ+10；WHEREYEAR(CSRQ)>=1990四、SQL與VFP命令對比1、VFP需先打開表，SQL不用。2、SQL中要出現(xiàn)表名，VFP除USE和SETRELATION外都不出表名。返回9

查詢功能是SQL的核心，SQL的查詢命令又稱為SELECT語句。一、SELECT命令的格式SELECT[ALL│DISTINCT][TOPn[PERCENT]];[別名.]<字段１>[AS]<別名>[,[別名2]<字段２>[AS]<別名>]>…];FROM[數(shù)據(jù)庫名!]<表名１>[[AS]<別名>][,[數(shù)據(jù)庫名2！]<表名２…];[[INNER│LEFT[OUTER]│RIGHT[OUTER]│FULL[OUTER]JOIN<數(shù)據(jù)庫名>!]<表名>[WHERE<篩選條件>|<聯(lián)接條件>];[GROUPBY<分組字段1>[，<分組字段2>…]]［HAVING<分組條件>］];[ORDERBY<排序字段1>[ASC|DESC][<排序字段2>[ASC|DESC]…]];[INTOTABLE<表名>]│[INTOCURSOR<表名>]|[INTOARRAY<數(shù)組名>][TOFILE<文件名>]

這條命令看起來很復(fù)雜，但其基本形式由SELECT-FROM模塊構(gòu)成，其余多為可選項(xiàng)，要根據(jù)查詢的需求去選用。第四節(jié)SQL的數(shù)據(jù)查詢返回10二、基本查詢格式：SELECT[ALL|DISTINCT]<字段列表>FROM<表名>1、查詢指定字段【例7.12】

從學(xué)生情況表（XSQK）中查詢所有學(xué)生的學(xué)號（XH）、姓名（XM）、班級（BJ）和入學(xué)成績（RXCJ）信息。SELECTXH,XM,BJ,RXCJFROMXSQK

【例7.13】查詢成績表(CJ)中的所有學(xué)生成績信息。SELECT*FROMCJ&&“*”為通配符，表示所有字段。該命令等價于：SELECTXH,KCDM,CJFROMCJ

2、查詢經(jīng)過計(jì)算的列

【例7.14】查詢學(xué)生情況表（XSQK）中所有學(xué)生的學(xué)號（XH）、姓名（XM）和年齡。SELECTXH,XM,YEAR(DATE())-YEAR(CSRQ)AS年齡FROMXSQK3、去掉重復(fù)值【例7.15】查詢學(xué)生情況表（XSQK）中所有學(xué)生的班級（BJ），要求去掉重復(fù)信息。SELECTDISTINCTBJFROMXSQK返回11三、條件查詢

在SELECT語句中，查詢條件用WHERE子句來描述。主要有以下幾種情況：1．比較大小用于比較的運(yùn)算符包括：=，>，<，>=，<=，#、!=或<>?！纠?.16】查詢學(xué)生情況表(XSQK)中所有男生的學(xué)號(XH)、姓名(XM)和性別(XB)。SELECTXH,XM,XBFROMXSQKWHERExb="男"【例7.17】查詢學(xué)生情況表(XSQK)中入學(xué)成績(RXCJ)多于565分（含）的學(xué)生的學(xué)號(XH)、姓名(XM)和入學(xué)成績(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHERERXCJ>=5652．多重條件使用邏輯運(yùn)算符AND、NOT和OR可以進(jìn)行復(fù)合條件查詢?！纠?.18】查詢學(xué)生情況表(XSQK)中所有男生且入學(xué)成績(RXCJ)高于500分的學(xué)生的學(xué)號(XH)、姓名(XM)和入學(xué)成績(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHEREXB="男"ANDRXCJ>500返回12【例7.19】查詢學(xué)生情況表(XSQK)中金融091班和政法092班的所有學(xué)生信息。SELECT*FROMXSQKWHEREBJ="金融091"ORBJ="政法092"3.確定范圍BETWEEN…AND…和NOTBETWEEN…AND…分別用來描述宇段值在或不在指定范圍的條件。其中，AND的左端給出查詢范圍的下限，AND的右端給出的是查詢范圍的上限?！纠?.20】查詢學(xué)生情況表(XSQK)中入學(xué)成績(RXCJ)在550到600分之間的學(xué)生的學(xué)號(XH)、姓名(XM)和入學(xué)成績(RXCJ)。SELECTXH,XM,RXCJFROMXSQKWHERERXCJBETWEEN550AND6004．確定集合這里所說的集合是相同類型的常量所組成的集合。謂詞IN用來描述字段的值屬于指定的集合，NOTIN則描述字段值不屬于指定的集合。【例7.21】查詢學(xué)生情況表(XSQK)中金融091班和政法092班的所有學(xué)生信息。SELECT*FROMXSQKWHEREBJIN("金融091","政法092")返回135．部分匹配查詢LIKE是字符匹配運(yùn)算符，進(jìn)行匹配運(yùn)算時可以使用通配符“％”和下劃線“_”，其中，“％”表示0個或多個任意字符，“_”表示1個任意字符。例如，第2個字符為B的字符串可以表示為“_B％”。LIKE的格式為：<字符型字段名>LIKE<含通配符的字符串常量>【例7.22】查詢XSQK表中所有姓“張”的學(xué)號（XH）和姓名（XM）。SELECTXH,XMFROMXSQKWHEREXMLIKE"張%"四、統(tǒng)計(jì)查詢SQL提供了許多庫函數(shù)，可以進(jìn)一步增強(qiáng)檢索功能，這些函數(shù)主要有：COUNT(列名|*)按列統(tǒng)計(jì)記錄個數(shù)SUM(<列名>)計(jì)算一列值的總和AVG(<列名>)計(jì)算一列值的平均值MAX(<列名>)求一列值中的最大值MIN(<列名>)求一列值中的最小值返回14如果指定DISTINCT，則表示在計(jì)算時要取消指定行或列中的重復(fù)值。使用SUM，AVG，MAX，MIN函數(shù)時，指定列必須是數(shù)值型。另外，在標(biāo)準(zhǔn)SQL中只能在SELECT子句和HAVING短語中使用庫函數(shù)?！纠?.23】查詢成績表（CJ）中學(xué)號為“0901100103”的學(xué)生的學(xué)號（XH），總分和平均分。SELECTXH,SUM(CJ)AS總分,AVG(CJ)AS平均分FROMCJ;WHEREXH="0901100103"【例7.24】查詢成績表（CJ）中課程代碼（KCDM）為“150205”的課程代碼，最高分和最低分。SELECTKCDM,MAX(CJ)AS最高分,MIN(CJ)AS最低分;FROMCJWHEREKCDM="150205"【例7.25】統(tǒng)計(jì)成績表（CJ）中有多少門課。SELECTCOUNT(DISTINCTKCDM)AS課程數(shù)FROMCJ【例7.26】統(tǒng)計(jì)XSQK表中學(xué)生人數(shù)。SELECTCOUNT(*)AS學(xué)生人數(shù)FROMXSQK

返回15五、分組查詢

GROUPBY子句可以將查詢結(jié)果按一列或多列值分組，列值相等的為一組，還可以利用HAVING短語按一定的條件對分組后的數(shù)據(jù)進(jìn)行篩選。GROUPBY子句一般應(yīng)跟在WHERE子句之后，沒有WHERE子句時，跟在FROM子句后。如果對分組有要求的話，則可以用HAVING短語指定篩選條件。該短語必須與GROUPBY配合使用。需要強(qiáng)調(diào)的是，HAVING短語與WHERE子句之間并不矛盾，WHERE子句用于表的選擇運(yùn)算，HAVING短語用于設(shè)置分組的篩選條件，即滿足該條件的分組數(shù)據(jù)才被輸出。【例7.27】查詢成績表（CJ）中每門課程的課程代碼（KCDM）、最高分和最低分。SELECTKCDM,MAX(CJ)AS最高分,MIN(CJ)AS最低分;FROMCJGROUPBYKCDM【例7.28】查詢成績表（CJ）中每名學(xué)生的學(xué)號（XH）、最高分和最低分。SELECTXH,MAX(CJ)AS最高分,MIN(CJ)AS最低分FROMCJ;GROUPBYXH返回16【例7.29】統(tǒng)計(jì)輸出班級人數(shù)超過18（含）人的各班級的入學(xué)成績平均分。SELECTBJ,AVG(RXCJ)AS平均分FROMXSQK;GROUPBYBJHAVINGCOUNT(*)>=18【例7.30】在成績表（CJ）里查詢選修了“150104”和“150205”的學(xué)生平均成績在80分及以上的學(xué)生的學(xué)號和平均成績。SELECTXH,KCDM,AVG(CJ)AS平均成績FROMCJ;WHEREKCDMIN("150104","150205")GROUPBYXH;HAVINGAVG(CJ)>=80六、查詢的排序利用ORDERBY子句可以對查詢的結(jié)果按指定字段進(jìn)行排序。其中，ASC表示升序，DESC表示降序，缺省時表示升序。另外，利用該子句還可以實(shí)現(xiàn)多重排序?！纠?.31】查詢學(xué)生情況表(XSQK)中的學(xué)生信息，要求按入學(xué)成績由高到低順序排序。SELECT*FROMXSQKORDERBYRXCJDESC返回17【例7.32】查詢學(xué)生情況表(XSQK)中的學(xué)生學(xué)號（XH）、姓名（XM）、班級（BJ）和入學(xué)成績（RXCJ）。要求查詢結(jié)果按班級升序排列，班級相同再按入學(xué)成績降序排列。SELECTXH,XM,BJ,RXCJFROMXSQKORDERBYBJ,RXCJDESC【例7.33】統(tǒng)計(jì)輸出班級人數(shù)超過18（含）人的各班級的入學(xué)成績平均分,并按平均分降序排列。SELECTBJ,AVG(RXCJ)AS平均分FROMXSQK;GROUPBYBJHAVINGCOUNT(*)>=18ORDERBY2DESC如果用戶需要輸出滿足條件的前幾個記錄，可以使用以下短語：

TOP<數(shù)值表達(dá)式>[PERCENT]

其中，<數(shù)值表達(dá)式>表示要顯示前幾個記錄的數(shù)目。若使用PERCENT，則<數(shù)值表達(dá)式>應(yīng)取0.01至99.99間的實(shí)數(shù)，表示要顯示所有查詢結(jié)果中的前百分之幾的記錄。注意，TOP短語要與ORDERBY短語同時使用才有效。【例7.34】查詢?nèi)雽W(xué)成績最高的三位同學(xué)的姓名和入學(xué)成績。SELECTTOP3XM,RXCJFROMXSQKORDERBYRXCJDESC返回18【例7.35】查詢?nèi)雽W(xué)成績最低的30%同學(xué)的姓名和入學(xué)成績。SELECTTOP30PERCENTXM,RXCJFROMXSQK;ORDERBYRXCJDESC七、利用空值查詢在SQL中可以查詢某字段取值為空(NULL)的記錄，空值不同于零和空格，它不占任何存儲空間，中是一個特殊的符號“NULL”。輸入NULL的方法是復(fù)合鍵CTRL+0（零）?！纠?.36】查詢課程表（KCB）中沒有課程名稱的記錄信息。SELECT*FROMKCBWHEREKCMCISNULL注：查詢空值時要使用“ISNULL”，而“=NULL”是無效的，因?yàn)榭罩挡皇且粋€確定的值。八、連接查詢?nèi)绻樵兩婕皟蓚€或兩個以上的表，則需要將相關(guān)的表按某個字段連接起來，這種查詢稱為連接查詢（多表查詢）。返回191、等值連接查詢（1）2個表查詢條件格式

表名1．公共字段名=表名2．公共字段名其中，引用不同表中的字段需在字段名前加表名和“.”?！纠?.37a】查詢“趙剛”同學(xué)的學(xué)號、姓名，性別和成績。SELECTXSQK.XH,XM,XB,CJFROMXSQK,CJ;WHEREXSQK.XH=CJ.XHANDXM="趙剛"（2）3個表查詢查詢條件格式表名1．公共字段名1=表名2．公共字段名1AND表名2．公共字段名2=表名3．公共字段名2【例7.37b】查詢“趙剛”同學(xué)的學(xué)號、姓名，性別、選修的課程名稱和成績。SELECTXSQK.XH,XM,XB,KCMC,CJ;FROMXSQK,CJ,KCB;WHERECJ.KCDM=KCB.KCDMANDXSQK.XH=CJ.XH;ANDXM="趙剛"返回202、超連接查詢超連接查詢包括內(nèi)部連接、左連接、右連接和全連接等幾種情況的查詢。這里只介紹內(nèi)連接。格式如下：（1）2個表查詢條件格式FROM表名1[INNER]JOIN表名2ON表名1.公共字段名=表名2.公共字段名可以看出，超連接查詢是通過FROM短語給出連接類型，用ON短語給出連接條件進(jìn)行的。其中INNER可以省略?！纠?.37c】查詢“趙剛”同學(xué)的學(xué)號、姓名，性別和成績。SELECTXSQK.XH,XM,XB,CJFROMXSQKJOINCJ;ONXSQK.XH=CJ.XHWHEREXM="趙剛"（2）3個表查詢條件格式FROM表名1

JOIN表名2JION表名3ON表名2.公共字段名=表名3.公共字段名ON表名1.公共字段名=表名2.公共字段名返回21【例7.37d】查詢“趙剛”同學(xué)的學(xué)號、姓名，性別、選修的課程名稱和成績。SELECTXSQK.XH,XM,XB,KCMC,CJ;FROMXSQKJOINCJJOINKCB;ONCJ.KCDM=KCB.KCDMONXSQK.XH=CJ.XH;WHEREXM="趙剛"九、嵌套查詢所謂嵌套查詢，是指在WHERE子句條件中涉及字段與另一個SELECT查詢結(jié)果的比較，也就是說，嵌套查詢是指在一個SELECT語句中包含另一個SELECT語句的查詢。SQL支持嵌套查詢，這正是SQL結(jié)構(gòu)化的具體體現(xiàn)。

處于內(nèi)層的查詢稱為子查詢，處于外層的查詢稱為父查詢。在子查詢的SELECT語句不能使用ORDERBY子句，即ORDERBY子句只能對最終查詢結(jié)果排序。1、返回一個值的子查詢當(dāng)子查詢的返回值只有一個時，可以使用>，<，>=，<=，!=或<>等比較運(yùn)算符將父查詢和子查詢連接起來。返回22【例7.38】從成績表中查詢“趙剛”同學(xué)的考試成績信息，要求顯示CJ表的所有字段。SELECT*FROMCJ;WHEREXH=(SELECTXHFROMXSQKWHEREXM="趙剛")2、返回一組值的子查詢?nèi)绻硬樵兊姆祷刂挡恢挂粋€，而是一個集合時，則不能直接使用比較運(yùn)算符，可以在運(yùn)算符和子查詢之間插入ANY、SOME、ALL。WHERE子句中的條件書寫格式為：

<字段名><關(guān)系比較運(yùn)算符>[ANY|SOME|ALL](子查詢)其中，ANY和SOME是作用相同的謂詞。ANY代表某個值，ALL代表所有值。從上述格式可以看出，使用謂詞ANY或ALL時必須同時使用比較運(yùn)算符。例如，>ANY的語義為：大于子查詢結(jié)果中的某個值。>ALL的語義為：大于子查詢結(jié)果中的所有值。由于子查詢的結(jié)果是記錄的集合，故也可使用謂詞IN來實(shí)現(xiàn)。返回23【例7.39】查詢選修了課程代碼（KCDM）為“150204”的學(xué)生的姓名（XM）。SELECTXMFROMXSQKWHEREXH=ANY;(SELECTXHFROMCJWHEREKCDM="150205")也可以寫成：SELECTXMFROMXSQKWHEREXHIN;(SELECTXHFROMCJWHEREKCDM="150205")【例7.40】查詢高于男生入學(xué)成績最高分的女生的姓名和入學(xué)成績。SELECTXM,RXCJFROMXSQKWHERERXCJ>ALL;(SELECTRXCJFROMXSQKWHEREXB="男")ANDXB="女"也可以寫成：SELECTXM,RXCJFROMXSQKWHERERXCJ>;(SELECTMAX(RXCJ)FROMXSQKWHEREXB="男")ANDXB="女"返回24EXISTS也稱為存在量詞，WHERE子句中使用量詞EXISTS表示當(dāng)子查詢的結(jié)果非空時，條件為真：反之，則為假。EXISTS前也可以加NOT，表示檢測條件為“不存在”。【例7.41】查詢選修了課程代碼（KCDM）為“140101”的學(xué)生的姓名（XM）。SELECTXMFROMXSQKWHEREEXISTS;(SELECT*FROMCJWHEREXSQK.XH=CJ.XHANDKCDM="140101")十、查詢結(jié)果輸出1、將查詢結(jié)果存入表中

若