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

第4章結(jié)構(gòu)化查詢語言第2頁4.1SQL概述4.2數(shù)據(jù)查詢（3學(xué)時）4.3數(shù)據(jù)定義

（2學(xué)時）4.4數(shù)據(jù)操縱（1學(xué)時）本章主要內(nèi)容及課時安排

（6學(xué)時）第3頁4.1SQL概述SQL是關(guān)系數(shù)據(jù)庫的標準語言，VisualFoxPro中最強大的SQL命令是SELECT命令，VisualFoxPro只支持SQL其中的語句子集，如沒有提供數(shù)據(jù)控制功能。但這些SQL語句對于數(shù)據(jù)庫查詢和維護是十分重要的。SQL（StructureQuerylanguage）是結(jié)構(gòu)化查詢語言的英文縮寫。SQL語言是一種標準的關(guān)系數(shù)據(jù)庫查詢語言，充分體現(xiàn)了關(guān)系數(shù)據(jù)語言的特點和優(yōu)點。第4頁

關(guān)系數(shù)據(jù)庫標準語言SQL

SQL命令動詞

SQL功能命令動詞數(shù)據(jù)查詢SELECT

數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE第5頁4.2數(shù)據(jù)查詢第6頁命令格式：

SELECT<輸出項列表>FROM<數(shù)據(jù)表名>

[WHERE<篩選條件>][GROUPBY<分組項>[HAVING<過濾條件>]]

[ORDERBY<排序項>[ASC|DESC]][INTODBF<目標>]|[TOFILE<文件名>]……第7頁考試表部分記錄的瀏覽窗口

第8頁SELExm,bs,js

FROM考試;ORDEBYbsDESC,jsDESC例3：將考試表中所有考生成績按筆試成績降序，再按機試成績降序。3.排序查詢例2：從考試表中查詢筆試成績≥90分的考生。2.簡單條件查詢例1:顯示所有考生信息。1.無條件查詢SELE*FROM考試WHEREbs>=90SELE*FROM考試第9頁4．分組與計算查詢例4：在考試表中，按語言類別分組查詢：語言類別、考生人數(shù)、筆試成績的最高分、筆試成績的最低分及筆試成績的平均分。查詢結(jié)果保存在“成績統(tǒng)計.dbf”數(shù)據(jù)表中。Selectyylb語言類別,count(*)考生人數(shù),max(bs)筆試最高分,;min(bs)筆試最低分,avg(bs)筆試平均分;from考試groupbyyylbintotable成績統(tǒng)計常用函數(shù)：COUNT----計數(shù)&&對數(shù)據(jù)表中的記錄進行計數(shù)COUNT(*)，一般COUNT函數(shù)應(yīng)該使用DISTINCT（DISTINCT短語表示去掉重復(fù)記錄）SUM----求和AVG----計算平均值MAX----求最大值MIN----求最小值這些函數(shù)可以用在SELECT短語中對查詢結(jié)果進行計算。（參加考試人數(shù)在20人以上的語言類別的成績情況）第10頁例5：在圖書表中，查詢所藏圖書中，有三種及三種以上的圖書出版社所出版圖書的最高單價和平均單價。SELEcbdw出版單位,MAX(dj)最高單價,AVG(dj)平均單價;FROM圖書GROUPBYcbdw;HAVINGCOUNT(DISTINCTzbh)>=3第11頁例6：查找與“胡娟”同齡的考生姓名、年齡和成績。SELExm,nl,bs,jsFROM考試WHEREnl=;(SELEnlFROM考試WHERExm="胡娟")

5.嵌套查詢第12頁6.多表內(nèi)聯(lián)查詢例7：在圖書管理數(shù)據(jù)庫中，查詢已借出書籍讀者的借書證號、姓名和單位以及借出圖書的書名，單價，借書日期。SELEa.jszh,a.xm,a.dw,b.sm,b.dj,c.jsrq;FROM讀者A,圖書B,借閱C;WHERE(a.jszh=c.jszh)AND(b.zbh=c.zbh)SELECT讀者.jszh,讀者.xm,讀者.dw,圖書.sm,圖書.dj,借閱.jsrq;FROM圖書管理!讀者INNERJOIN圖書管理!借閱;INNERJOIN圖書管理!圖書;ON借閱.zbh=圖書.zbh;ON讀者.jszh=借閱.jszh在“查詢設(shè)計器”中自動生成的SQL命令是：內(nèi)部聯(lián)接：INNERJOIN等價于JOIN普通聯(lián)接，只有滿足聯(lián)接條件的記錄才出現(xiàn)在查詢結(jié)果中。例8：在圖書管理數(shù)據(jù)庫中，求會計學(xué)院借閱圖書的讀者的人數(shù)。SELEdw單位,COUNT(DISTINCT借閱.jszh)借書人數(shù);FROM借閱,讀者;WHERE借閱.jszh=讀者.jszhAND讀者.jszhIN;(SELECT讀者.jszhFROM讀者WHEREdw="會計學(xué)院")SELExm姓名,dw單位FROM讀者WHERENOTEXISTS;(SELE*FROM借閱WHERE借閱.jszh=讀者.jszh);ORDERBYdw例9：對于圖書管理數(shù)據(jù)庫，檢索沒有借閱任何圖書的讀者的姓名和所在單位。SELEdw單位,COUNT(DISTINCT借閱.jszh)借書人數(shù);FROM借閱,讀者;WHERE借閱.jszh=讀者.jszhANDdw="會計學(xué)院"7.利用BETWEEN、IN、LIKE的查詢語句例10：顯示所有筆試成績在(60～80)分考生信息。SELE*FROM考試;WHEREbsBETW60AND80例11：顯示所有參加“二級VB”和“二級VF”的考生。SELE*FROM考試WHEREyylbIN;("二級VB","二級VF")等價于bs>=60ANDbs<=80

等價于yylb="二級VB“ORyylb="二級VF"第15頁例13：顯示所有名字中有“麗”的考生成績。SELExm,bs,jsFROM考試;WHERExmLIKE"%麗%"例12：顯示所有姓“李”的考生成績。SELE*FROM考試WHERExmLIKE"李%"SELE*FROM考試WHERExmLIKE"李_"SELE*FROM考試WHERExmLIKE"李__"等價于xm="李"

等價于"麗"$xm

8.顯示部分記錄例14:顯示筆試成績最高的三位考生的記錄。SELE*TOP3FROM考試ORDERBYbsDESC例15:顯示筆試成績最低的那30%考生的記錄。SELE*TOP30PERCENTFROM考試;ORDERBYbs第17頁4.3數(shù)據(jù)定義第18頁

4.3.1建立數(shù)據(jù)表

CREATTABLE|DBF<表名>[FREE](<字段名1><字段類型>(<字段寬度>[,<小數(shù)位數(shù)>]),<字段名2>……)[NULL|NOTNULL][CHECK<邏輯表達式>[ERROR字符表達式]][DEFAULT<表達式>][PRIMARYKEY|UNIQUE][REFERECES<表名2>[TAG<標記名>]]

[,FOREIGNKEY<索引表達式>TAG<索引標識>REFERENCE<數(shù)據(jù)表>[TAG<索引標識>] ……第19頁例：新建一個學(xué)生管理數(shù)據(jù)庫，在該數(shù)據(jù)庫中建立學(xué)生表stud.dbf(學(xué)號、姓名、性別、出生日期、籍貫、地址、家庭月收入、系別代號)和成績表cj.dbf(學(xué)號、計算機、英語、會計、法學(xué)、總分、平均分)，并且將兩個表按“學(xué)號”建立一對多的聯(lián)系。CREATDATA學(xué)生管理第20頁&&學(xué)號為主索引且不能為空值，對家庭月收入字段說明了字段有效性規(guī)則及出錯信息。CREATTABLEstud(學(xué)號c(4)PRIMKEYNOT;NULL,姓名c(6),性別c(2),出生日期d,籍貫c(8),地址;c(40),家庭月收入n(8,2)CHECK家庭月收入>=0;ERROR"家庭月收入應(yīng)大于等于零",系別代號c(2))第21頁

CREATTABLEcj(學(xué)號c(10),計算機n(5,1),;英語n(5,1),會計n(5,1),法學(xué)n(5,1),總分n(3),;平均分n(5,1),FOREIGNKEY學(xué)號;

TAG學(xué)號REFERENCESstud)&&FOREIGNKEY學(xué)號

短語表明在該表的學(xué)號字段上建立一個普通索引。&&

TAG學(xué)號REFERENCESstud短語表明學(xué)號字段是連接字段，通過引用stud的主索引“學(xué)號”與stud建立聯(lián)系。第22頁學(xué)生表和成績表的一對多的聯(lián)系第23頁4.3.2表的刪除數(shù)據(jù)庫在實際使用過程中，往往有些表不再需要了，對于這樣的表可以刪除以節(jié)省存儲空間。要刪除數(shù)據(jù)庫中的表時，最好在當前打開的數(shù)據(jù)庫中進行操作。

命令格式：

DROPTABLE<表名>|？[RECYCLE]

命令功能：刪除指定的表。?參數(shù)用于在“刪除”對話框中選擇要刪除的表名。RECYCLE：用于將刪除的表放入回收站。第24頁例1：增加電話字段

ALTERTABLEstudADD電話c(10)

例2：修改姓名字段的寬度

ALTERTABLEstudALTER姓名c(10)4.3.3修改數(shù)據(jù)表

ALTERTABLE<表名>ADD|ALTER<字段名><字段類型>[(<字段寬度>[,<小數(shù)位數(shù)>])]ALTERTABLE<表名>DROP<字段名>ALTERTABLE<表名>RENAMECOLUMN<字段名1>

TO<字段名2>第25頁例3：刪除電話字段ALTERTABLEstudDROP電話例4：將字段名由地址改為家庭地址ALTERTABLEstudRENAMECOLUMN地址TO;家庭地址

例5：修改或定義成績表的英語字段的有效性規(guī)則。ALTERTABLEcjALTER英語SETCHECK;

英語>=0ERROR“成績不應(yīng)小于零！”第26頁例6：刪除學(xué)生表的家庭月收入字段的有效性規(guī)則。ALTERTABLEstudALTER家庭月收入;DROPCHECK例7：將學(xué)生表中姓名和出生日期定義為候選索引，索引標記名為“xmrqsy”。ALTERTABLEstudADDUNIQUE;姓名+DTOC(出生日期)TAGxmrqsy例8：刪除學(xué)生表中索引標記為“xmrqsy”的候選索引。

ALTERTABLEstudDROPUNIQUETAGxmrqsy

第27頁例9：刪除成績表與學(xué)生表兩個表之間的永久關(guān)系。

ALTERTABLEcj

DROPFOREIGNKEYTAG學(xué)號例10：重新建立成績表與學(xué)生表兩個表之間一對多的永久關(guān)系。

ALTERTABLEcj

ADDFOREIGNKEY學(xué)號;TAG學(xué)號REFERENCESstud

第28頁4.4數(shù)據(jù)操縱第29頁

例：為stud表添加記錄的全部字段數(shù)據(jù)。

INSERTINTOstudVALU("0110","李建國","男",;{^1981.02.06},"湖南","湖大北校區(qū)39棟",2345.00,"01")

&&數(shù)據(jù)的個數(shù)和順序和表結(jié)構(gòu)的字段個數(shù)和順序必須一致。例：為stud表添加記錄的部分字段數(shù)據(jù)。

INSERTINTOstud(學(xué)號,籍貫,性別,姓名)VALU;("0111","湖北",

"女","王麗麗")&&數(shù)據(jù)的順序和字段名表中指定的順序必須一致。4.4.1插入記錄INSEINTO<表名>[(<字段名表>)]VALUE(<表達式表>)第30頁

4.4.2更新記錄

UPDATE<表名>SET<字段名1>=<表達式1>[，<字段名2>=<表達式2>[,...]][WHERE<條件>]例：將“考試.dbf”表中的“yylb”字段的“二級VF”改為“二級VisualFoxPro”。

UPDATE考試SETyylb="二級VisualFoxPro";WHEREyylb="二級VF"例：將“考試.dbf”的jl字段按分數(shù)添入優(yōu)秀、及格、補考和不及格結(jié)論。

UPDATE考試SETJL="優(yōu)秀"

WHEREbs>=90;ANDjs>=90第31頁

4.4.3刪除記錄

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

例：刪除不及格考生記錄。

DELEFROM考試WHEREbs<60andjs<60

例：從借閱表中的刪除科學(xué)出版社出版的“數(shù)據(jù)庫導(dǎo)論”的借閱記錄。（注意，不同出版單位所出版的相同書名“數(shù)據(jù)庫導(dǎo)論”在借閱表中都有借閱記錄）

DELETEFROM借閱WHEREzbh;IN(SELECT借閱.zbhFROM圖書,借閱;WHERE借閱.zbh=圖書.zbhANDsm="數(shù)據(jù)庫導(dǎo)論";ANDcbdw="科學(xué)出版社")本章練習(xí)第33頁“教師”表職工號姓名職稱年齡工資系號11020001肖天海副教授352000.000111020002王巖鹽教授403000.000211020003劉星魂講師251500.000111020004張月新講師301500.000311020005李明玉教授342000.000111020006孫民山教授472100.000211020007錢無名教授492200.0003“學(xué)院”表系號系名01英語02會計03工商管理第34頁(1)使用SQL語句將一條新的記錄插入學(xué)院表。INSERT______學(xué)院(系號,系名)______(“04”,“計算機”)(2)使用SQL語句求“工商管理”系的所有職工的工資總和SELE_____(工資)FROM教師WHERE系號IN;(SELE系號FROM_____WHERE系名=“工商管理”)(3)使用SQL語句完成將所有教授的工資提高5%的操作。_____教師SET工資=工資*1.05______職稱=“教授”INTOVALU學(xué)院SUMUPDAWHERE第35頁數(shù)據(jù)庫表stock.dbf股票代碼股票名稱

單價

交易所600600青島啤酒7.48上海600601方正科技

15.20

上海600602廣電電子

10.40上海600603興業(yè)房產(chǎn)

12.76上海600604二紡機

9.96上海600605輕工機械

14.59上海000001深發(fā)展

7.48

深圳000002深萬科

12.50

深圳第36頁(1)執(zhí)行如下SQL語言后

SELE*FROMstockINTODBFstockORDERBY單價

A)系統(tǒng)會提示出錯信息

B)會生成一個按“單價”升序排列的表文件，將原來的stock.dbf文件覆蓋

C)會生成一個按“單價”降序排列的表文件，將原來的stock.dbf文件覆蓋

D)不會生成排序文件，只在屏幕上顯示一個按“單價”升序排序的結(jié)果(2)在當前盤當前目錄下刪除表stock的命令是

A)DROPstock

B)DELETABLstockC)DROPTABLstockD)DELEstock第37頁(3)執(zhí)行如下SQL語言后

SELEmax(單價)INTOARRAYaFROMstock

A)a(1)的內(nèi)容為15.20B)a(1)的內(nèi)容為7.48

C)a(0)的內(nèi)容為15.20D)a(0)的內(nèi)容為7.48(4)執(zhí)行如下SQL語言后，temp表中第二條記錄的“均價”字段的內(nèi)容是

SELE股票代碼,avg(單價)as均價FROMstock;GROUPBY交易所INTODBFtempA)7.48B)9.99C)11.73D)15.20(5)執(zhí)行如下SQL語言后產(chǎn)生的視圖包含的記錄個數(shù)是

CREAVIEWstock_viewASSELE*FROMstock;WHERE交易所=“深圳”

A)1B)2C)3D)4第38頁(6)將stock表的股票名稱字段的寬度由８改為１０，應(yīng)使用SQL語句

A)ALTERTABLEstock股票名稱WITHc(10)B)ALTERTABLEstock股票名稱c(10)C)ALTERTABLEstockALTER股票名稱c(10)D)ALTERstockALTER股票名稱c(10)(7)執(zhí)行如下SQL語句后產(chǎn)生的視圖含有的字段名是

CREAVIEWview_stockASSELE股票名稱AS名稱,單價FROMstockA)股票名稱、單價B)名稱、單價C)名稱、單價、交易所D)股票名稱、單價、交易所第39頁(8)執(zhí)行如下SQL語句后表stock1中的記錄個數(shù)是

SELEDISTINCT單價FROMstockWHERE單價=(SELEmin(單價)FROMstock)INTODBFstock1A)1B)2C)3D)4(9)求每個交易所的平均單價的SQL語句是

A)SELE交易所,avg(單價)FROMstock;GROUPBY單價B)SELE交易所,avg(單價)FROMstock;ORDERBY單價C)SELE交易所,avg(單價)FROMstock;GROUPBY交易所D)SELE交易所,avg(單價)FROMstock;ORDERBY交易所第40頁商品表:部門號商品號商品名稱單價數(shù)量產(chǎn)地400101A牌電風(fēng)扇200.0010廣東400104A牌微波爐350.0010廣東400105B牌微波爐600.0010廣東201032C牌傳真機1000.0020上海400107D牌微波爐_A420.0010北京200110A牌電話機200.0050廣東200112B牌手機2000.0010廣東400202A牌電冰箱3000.002廣東301041B牌計算機6000.0010廣東300204C牌計算機10000.0010上海第41頁部門表:部門號部門名稱40家用電器部10電視錄攝象機部20電話手機部30計算機部SQL語句SELE部門號,MAX(單價*數(shù)量)FROM商品表;GROUPBY部門號查詢結(jié)果有幾條記錄?

A)1B)4C)3D)10第42頁(2)SQL語句SELE產(chǎn)地,COUNT(*)提供的商品種類數(shù)FROM商品表

WHERE單價>200GROUPBY產(chǎn)地;HAVINGCOUNT(*)>=2ORDERBY2DESC

查詢結(jié)果的第一條記錄的產(chǎn)地和提供的商品種類數(shù)是

A)北京,1B)上海,2C)廣東,5D)廣東,7(3)SQL語句SELE部門表.部門號,部門名稱,SUM(單價*數(shù)量)FROM部門表,商品表WHERE部門表.部門號=商品表.部門號GROUPBY部門表.部門號各部門商品數(shù)量合計B)各部門商品金額合計C)所有商品金額合計D)各部門商品金額平均值第43頁(4)SQL語句SELE部門表.部門號,部門名稱,商品號,商品名稱,單價FROM部門表,商品表WHERE部門表.部門號=商品表.部門號ORDERBY部門表.部門號DESC,單價查詢結(jié)果的第一條記錄的商品號是

A)0101B)0202C)0110D)0112(5)SQL語句SELE部門名稱FROM部門表WHERE部門號IN;(SELE部門號FROM商品表WHERE單價BETW;420AND1000)家用電器部、電話手機部B)家用電器部、計算機C)電話手機部、電視錄攝象機部D)家用電器部、電視錄攝象機部第44頁例:在標準SQL中，建立視圖的命令是CREATESCHEMA(B)CREATETABLE(C)CREATEVIEW(D)CREATEINDEX例：在SQL語言中，修改基本表的語句是

(A)CREATETABLE(B)DROPTABLE(C)ALTERTABLE(D)UPDATETABLE例：SQL語言是(A)高級語言(B)結(jié)構(gòu)化查詢語言(C)第三代語言(D)宿主語言第45頁例：SQL實現(xiàn)分組查詢的短語是(A)ORDERBY(B)GROUPBY(C)HAVING(D)ASC例：在SQL的計算查詢中，用于求平均值的函數(shù)是(A)AVG(B)AVERAGE(C)average(D)AVE例：SQL語句中的SELECT命令建立表之間聯(lián)系的短語為(A)UNIQE(B)GROUPBY(C)JOIN(D)FROM例:下面所列條目中，錯誤的SQL語句是(A)ALTERTABLE(B)ALTERVIEW(C)CREATETABLE(D)CREATEVIEW例：SQL語句中修改表結(jié)構(gòu)的命令是(A)MODIFYTABLE(B)MODIFYSTRUCTURE(C)ALTERTABLE(D)ALTERSTRUCTURE第46頁例：用于顯示部分查詢結(jié)果的TOP短語，必須同時使用的短語是：(A)ORDERBY(B)FROM(C)WHERE(D)GROUPBY例：用SQL語句建立表時將屬性定義為主關(guān)鍵字，應(yīng)使用短語(A)CHECK(B)PRIMARYKEY(C)FREE(D)UNIQUE例：HAVING短語不能單獨使用，必須接在下列哪個短語之后(A)ORDERBY(B)FROM(C)WHER