江蘇省計算機等級考試VFP第六章考點歸納

第七講SQL語言考點1：數(shù)據(jù)查詢SQL：StructuredQueryLanguage，結(jié)構(gòu)化查詢語言，包括數(shù)據(jù)查詢、數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制4部分，但VFP不提供數(shù)據(jù)控制功能。SQL的核心是數(shù)據(jù)查詢，基本形式由SELECT-FROM-WHERE查詢塊組成。SQL語句:是一個整體，一行寫不完用；續(xù)行字段---select消除重復記錄-àdistinct，全部記錄-à*字段別名-àas重復字段--à表名.字段分組函數(shù)：Avg(),Count(),Min(),Max(),Sum()特殊函數(shù)：IIF前幾、前百分比項-àTOPN/TOPNPERCENT字段，與ORDERBY配合使用分組計算函數(shù):計算平均值avg(cj)as平均成績計算總分:sum(cj)as總成績求最高值:max(cj)as最高分求最小值:min(cj)as最低分求人數(shù),門數(shù),課程數(shù):count(*)ócount(字段名)求不合格門數(shù):sum(IIF(cj.cj,1,0))as不合格門數(shù)注意不要把count()和sum()相混淆.前者是求記錄數(shù),后者是求字段值加起來的總和.count(distinct字段名)：計數(shù)時，去掉結(jié)果中重復的記錄，即結(jié)果中重復的記錄只算一條。selectcount(xdh)fromxsselectcount(distinctxdh)fromxs表-----FROM連接查詢：是一種基于多個關(guān)系的查詢；格式 1：Select字段列表from表1,表2Where表1.字段名=表2.相同字段名字段列表中的某字段若為兩表的公共字段，則需在該字段前加表名進行限定例1：計算劉明同學選修的所有課程的平均成績，正確的SQL語句是

A．SELECT

AVG(成績)

FROM選課WHERE姓名=”劉明”

B．SELECT

AVG(成績)

FROM學生,選課WHERE姓名=”劉明”

C．SELECT

AVG(成績)FROM學生,選課WHERE學生.姓名=”劉明”

D．SELECT

AVG(成績)FROM學生,選課WHERE學生.學號=選課.學號AND姓名=”劉明”格式2：SELECT字段列表FROM表1INNER|LEFT|RIGHT|FULLJOIN表2INNER|LEFT|RIGHT|FULLJOIN表3ON表2.字段A=表3.字段AON表1.字段B=表2.字段BWHERE<篩選條件>例2：設(shè)學生表S(學號,姓名,性別,年齡),課程表C(課程號,課程名,學分)和學生選課表SC(學號,課程號,成績),檢索學號,姓名和學生所選課程名和成績,正確的SQL命令是：

A．SELECT

學號，姓名，課程名，成績

FROM

S，SC，CWHERE

S.學號

=SC.學號

AND

SC.學號=C.學號B．SELECT

學號，姓名，課程名，成績FROM

（S

JOIN

SC

ON

S.學號=SC.學號）JOIN

C

ON

SC.課程號

=C.

課程號C．SELECT

S.

學號，姓名，課程名，成績FROM

S

JOIN

SC

JOIN

C

ON

S.學號=SC.學號

ON

SC.課程號=C.

課程號D．SELECT

S.

學號，姓名，課程名，成績FROM

S

JOIN

SC

JOIN

C

ON

SC.課程號=C.課程號

ON

S.學號=SC.

學號嵌套查詢：所要求的結(jié)果出自一個關(guān)系，但關(guān)系的條件卻涉及多個關(guān)系。其內(nèi)層基本上也是一個SELECT-FROM-WHERE查詢語句，可使用謂詞IN或NOTIN來判斷在外層的查詢條件中是否包含內(nèi)層查詢的結(jié)果。格式：Select字段列表from表1where字段Ain(select字段Afrom表2where篩選條件)例1：Select*fromxswherexhin(selectxhfromcj)&&表示只顯示選修過課程的XS信息例2：Select*fromxswherexhnotin(selectxhfromcj)&&顯示沒有選過課的學生信息。例3：顯示沒有簽訂任何訂單的職員信息（職員號和姓名），正確的SQL語句是

A．SELECT

職員.職員號,姓名

FROM

職員

JOIN

訂單ON

訂單.職員號=職員.職員號

GROUP

BY

職員.職員號HAVING

COUNT(*)=0

B．SELECT

職員.職員號,姓名

FROM

職員

LEFT

JOIN

訂單

ON

訂單.職員號=職員.職員號

GROUP

BY

職員.職員號HAVING

COUNT(*)=0

C．SELECT

職員號,姓名

FROM

職員

WHERE

職員號

NOT

IN

(SELECT

職員號

FROM

訂單)

D．SELECT

職員.職員號,姓名

FROM

職員

WHERE

職員.職員號

<>

(SELECT

訂單.職員號

FROM

訂單)

例4：設(shè)XDH為XS表中的一個字段（類型為字符型，寬度為2個字節(jié)），則下列條件表達式中，與條件表達式XDHNOTIN(“01”,”04”)功能等價的是：AXs.xdh!=”01”ANDXs.xdh!=”04”BXs.xdh!<>”01”O(jiān)RXs.xdh!<>”04”C!(Xs.xdh>”01”O(jiān)RXs.xdh>”04”)DXs.xdh=”01”O(jiān)RXs.xdh=”04”篩選記錄---WHEREWHERE與HAVING的區(qū)別：WHERE篩選原表記錄，HAVING篩選結(jié)果記錄，進一步限定分組的條件WHERE后只能是原始字段，不能是計算函數(shù)HAVING后一般使用別名和計算函數(shù)HAVING必須和GROUPBY同時使用，不能單獨使用，必須跟在GROUPBY子句之后，與WHERE子句并不矛盾。篩選要滿足的條件:針對普通字段的條件，分組要滿足的條件:針對計算字段的條件例：條件Avg(cj)>90放在HAVING后面作為分組要滿足的條件條件cj>90放在WHERE后面作為篩選條件幾個特殊選項字段between值1and值2，等價于：字段值>=值1and字段值<=值2例：成績notBetween20and100等價于：成績<20or成績>100字段LIKE通配表達式通配表達式中可使用以下2個通配符：%：表示任意0或多個字符_（下劃線）：表示任意1個字符例：select…where姓名like“李%”select…where學號like“%2%”空值查詢（isnull）例：查詢有選課記錄，但沒有考試成績的學生的學號和課程號，正確的SQL語句是ASELECT學號,課程號FROMSCWHERE成績=””BSELECT學號,課程號FROMSCWHERE成績=NULLCSELECT學號,課程號FROMSCWHERE成績ISNULLDSELECT學號,課程號FROMSCWHERE成績查詢尚未確定供應商的訂購單的信息：Select*from訂購單where供應商號isnull查詢已經(jīng)確定了供應商的訂購單的信息：Select*from訂購單where供應商號isnotnull例：如下命令查詢雇員表中“部門號“字段為空值的記錄：SELECT*FROM雇員WHERE部門號ISNULL

。使用謂詞查詢：格式：Select字段列表1from表1where[not]exists(Select字段列表2from表2where表1.字段A=表2.字段A)EXISTS或NOTEXISTS是用來檢查在子查詢中是否有結(jié)果返回，即存在元組或不存在元組。其本身并沒有進行任何運算或比較，只用來返回子查詢結(jié)果。例1：設(shè)有s(學號,姓名,性別)和sc(學號,課程號,成績)兩個表,如下SQL語句檢索選修的每門課程的成績都高于或等于85分的學生的學號、姓名和性別,正確的是：A．SELECT學號,姓名,性別FROMsWHEREEXISTS(SELECT*FROMSCWHERESC.學號=s.學號AND成績<=85)B．SELECT學號,姓名,性別FROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.學號=s.學號AND成績<=85)C．SELECT學號,姓名,性別FROMSWHEREEXISTS(SELECT*FROMSCWHERESC.學號=S.學號AND成績>85)D．SELECT學號,姓名,性別FROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.學號=S.學號AND成績<85)例2：查詢選修C2課程號的學生姓名，下列SQL語句錯誤的是：ASELECT姓名FROMSWHEREEXISTS(SELECT*FROMSCWHERE學號=S.學號AND課程號=’C2’)BSELECT姓名FROMSWHERE學號IN(SELECT學號FROMSCWHERE課程號=’C2’)CSELECT姓名FROMSJOINSCONS.學號=SC.學號WHERE課程號=’C2’DSELECT姓名FROMSWHERE學號=(SELECT學號FROMSCWHERE課程號=’C2’)使用量詞查詢where字段A<比較運算符>[ANYISOME|ALL](子查詢)ANY或SOME在進行比較運算時，只要子查詢中有一行能使結(jié)果為真，結(jié)果就為真，往往對應于MIN()；ALL則要求子查詢中所有行都使結(jié)果為真時，結(jié)果才為真，往往對應于MAX()。例1：Select…..from…..where工資＞＝all(select工資from……)意義：all后面篩選是一個范圍：因此：工資＞＝all,就是＞＝這個范圍里的所有工資，反過來，意思就是：比這個范圍里的最高值要大.即：select…..from…where工資＞＝（selectmax(工資）from….)例2：Select….from….whereshengao>=any(selectshengao…from..)等價于：select…from…whereshengao>=(selectmin(shengao)….from…)例3：查詢選修課程號為”101”課程得分最高的同學，正確的SQL語句是：A．SELECT學生.學號,姓名FROM學生,選課WHERE學生.學號=選課.學號AND課程號=”101”AND成績>=ALL(SELECT成績FROM選課)B．SELECT學生.學號,姓名FROM學生,選課WHERE學生.學號=選課.學號AND成績>=ALL(SELECT成績FROM選課WHERE課程號=”101”)C．SELECT學生.學號,姓名FROM學生,選課WHERE學生.學號=選課.學號AND成績>=ANY(SELECT成績FROM選課WHERE課程號=”101”)D．SELECT學生.學號,姓名FROM學生,選課WHERE學生.學號=選課.學號AND課程號=”101”AND成績>=ALL(SELECT成績FROM選課WHERE課程號=”101”)例4：有SQL語句：SELECT

DISTINCT系號FROM教師WHERE工資>=;

ALL

(SELECT

工資FROM教師WHERE系號=“02”)

與如上語句等價的SQL語句是

：A．SELECT

DISTINCT系號FROM教師WHERE工資>=(SELECT

MAX(工資)FROM教師WHERE

系號="02")

B．SELECT

DISTINCT系號FROM教師WHERE工資>=(SELECT

MIN(工資)FROM教師WHERE

系號="02")

C．SELECT

DISTINCT系號FROM教師WHERE工資>=(ANY(SELECT(工資)FROM教師WHERE

系號="02")

D．SELECT

DISTINCT系號FROM教師WHERE工資>=(SOME

(SELECT(工資)FROM教師WHERE

系號="02")查詢?nèi)ハ颉狪NTO/TO將查詢結(jié)果存放到數(shù)組中:INTOARRAY數(shù)組名將查詢結(jié)果存到臨時表：INTOCURSOR表名查詢結(jié)束后該表為當前表將查詢結(jié)果存到永久表：INTODBF|TABLE表名將查詢結(jié)果存到文本文件中：tofile文本文件名additive表示追加在原文件后，否則覆蓋原文件將查詢結(jié)果直接輸出到打印機：toprinter將查詢結(jié)果直接輸出到屏幕：toscreen分組---GROUPBY常規(guī)表字段，可以對一個字段或多個字段分組包含SQL字段函數(shù)的字段名數(shù)值，最左邊列為1與分組函數(shù)配合使用排序---ORDERBY必須是查詢結(jié)果字段在子查詢中，必須是外層SELECT語句中的字段不可以在子查詢中使用該短語可以是字段名，也可以是數(shù)值升序為ASC，默認；降序為DESC可對一個或多個字段排序例：Selectcsrq,xh,xmfromxsorderbyxmDESC,xhdesc等價于：selectxh,xm,csrqfromxsorderby2desc,3desc因為，xm在SELECT后面的字段排第二位,xm排第一位聯(lián)結(jié)查詢：UNION將第一個SELECT語句最后查詢結(jié)果與另一個SELECT語句最后查詢結(jié)果排除重復行后組合起來兩個SELECT語句查詢結(jié)果列數(shù)必須相同兩個SELECT語句查詢結(jié)果對應列必須有相同的數(shù)據(jù)類型和寬度ORDERBY只能出現(xiàn)在最后一個SELECT語句中，而且需按編號指出例：已知借閱（ＪＹ）表中含讀者類型(lx)，借閱日期（jyrq）和還書日期（hsrq）等字段．下列ＳＱＬ命令用來統(tǒng)計教師、學生借書過期罰款人次和罰款金額．其中罰款金額的算法如下：(1)對于學生類讀者（lx字段值為"Ｘ"）來說，借閱期限為30天．每超過一天，罰款金額以每本書每天0.05元計算；(2)對于教師類讀者（lx字段值為"Ｊ"）來說，借閱期限為60天．每超過一天，罰款金額以每本書每天0.05元計算；SELECT“教師”AS類型,COUNT（*）AS罰款人次；SUM（0.05*(hsrq-jyrq-60)）AS罰款金額；FROMsjk!jy；WHEREjy.lx="J"ANDhsrq-jyrq>60;UNION；SELECT"學生“AS類型,COUNT(*)AS罰款人次；

SUM（0.05*(hsrq-jyrq-30)）AS罰款金額；FROMsjk!jy；WHEREjy.lx="X"ANDhsrq-jyrq>30創(chuàng)建表---CreateTableCreateTable表名(字段1數(shù)據(jù)類型(寬度)null|notnull,字段2數(shù)據(jù)類型(寬度)null,……）說明：可在表名后通過NAME子句指定長表名可在表名后通過FREE子句表明建立自由表數(shù)據(jù)類型是大寫字母，若寬度固定則寬度應省略此命令可以建立滿足實體完整性的主關(guān)鍵字(主索引)PRIMARYKEY、候選索引UNIQUE、定義域完整性的CHECK約束及出錯提示信息ERROR、定義默認值DEFAULT等，另外還有描述表之間聯(lián)系的FOREIGNKEY和REFERENCES等。如果建立自由表，則NAME、PRIMARYKEY、CHECK、DEFAULT、FOREIGNKEY、REFERENCES均不能使用。例1：某銀行存款表(ck.dbf)的表結(jié)果如下所示：創(chuàng)建ck表的表結(jié)構(gòu)，可以使用下列命令CREATETABLEck(zhC(15),crrqD,cqN(3,0),jeY)例2：利用SQL語句的定義功能建立一個課程表，并且為課程號建立建立主索引，語句格式為：CREATETABLE課程表(課程號C(5)PrimaryKey,課程名C(30))刪除表DROPTABLE表名直接從磁盤上刪除表文件修改表結(jié)構(gòu)---ALTERTABLE（1）ALTERTABLE表名ADD|ALTERCOLUMN字段名字段類型(寬度)null|notnullCheck邏輯表達式ERROR出錯信息DEFAULT默認值PRIMARYKEY|UNIQUEADD是增加一字段，ALTER修改字段類型和寬度例1：為“歌手”表增加一個字段“最后得分”的SQL語句是：A．ALTERTABLE歌手ADD最后得分F(6,2)B．ALTERDBF歌手ADD最后得分F6,2C．CHANGETABLE歌手ADD最后得分F(6,2)D．CHANGETABLE學院INSERT最后得分F6,2例2：為表“金牌榜”增加一個字段“獎牌總數(shù)”，同時為該字段設(shè)置有效性規(guī)則：獎牌總數(shù)>=0，應使用SQL語句：ALTER

TABLE

金牌榜

(ADD)獎牌總數(shù)I（CHECK）獎牌總數(shù)>=0例3：將stock表的股票名稱字段的寬度由8改為10，應使用SQL語句A)ALTERTABLEstock股票名稱WITHc(10)B)ALTERTABLEstock股票名稱c(10)C)ALTERTABLEstockALTER股票名稱c(10)D)ALTERstockALTER股票名稱c(10)（2）ALTERTABLE表名ALTERCOLUMN字段名NULL|NOTNULLSETCHECK邏輯表達式ERROR出錯信息SETDEFAULT

默認值DROPCHECKDROPDEFAULT修改或設(shè)置已存在字段的有效性規(guī)則、默認值等。例1：為“評分”表的“分數(shù)”字段添加有效性規(guī)則：“分數(shù)必須大于等于0并且小于等于10”，正確的SQL語句是：A．CHANGETABLE評分ALTER分數(shù)SETCHECK分數(shù)＞=0AND分數(shù)＜=10B．ALTERTABLE評分ALTER分數(shù)SETCHECK分數(shù)＞=0AND分數(shù)＜=10C．ALTERTABLE評分ALTER分數(shù)CHECK分數(shù)＞=0AND分數(shù)＜=10D．CHANGETABLE評分ALTER分數(shù)SETCHECK分數(shù)＞=0OR分數(shù)＜=10例2：為“教師”表的職工號字段添加有效性規(guī)則：職工號的最左邊三位字符是110，正確的SQL語句是

A．CHANGE

TABLE教師ALTER職工號SET

CHECK

LEFT(職工號,3)=“110”

B．ALTERTABLE教師ALTER職工號SET

CHECK

LEFT(職工號,3)=“110”

C．ALTER

TABLE教師ALTER職工號CHECK

LEFT(職工號,3)=“110”

D．CHANGE

TABLE教師ALTER職工號SET

CHECK

OCCURS(職工號,3)="110"

（3）Altertable表名dropcolumn字段名刪除一個字段Altertable表名renamecloumn字段名Ato字段名B：字段重命名Altertable表名addprimarykey/unique/foreignkey索引表達式tag索引名創(chuàng)建主索引、候選索引、外關(guān)鍵字Altertable表名dropprimarykey/unique/foreignkey索引表達式tag索引名刪除主索引、候選索引、外關(guān)鍵字例1：已有“歌手”表，將該表中的“歌手號”字段定義為候選索引、索引名是temp，正確的SQL語句是：

alterTABLE

歌手

ADD

UNIQUE

歌手號TAG

temp例2：ALTERTABLExscjDROPCOLUMNbz&&刪除備注（BZ）字段例3：ALTERTABLE訂購單RENAMECOLUMN總金額TO金額例4：ALTERTABLE訂購單DROPUNIQUETAGabcd刪除候選索引abcd圖定義：CREATEVIEW視圖名ASSELECT…AS后的SELECT語句指定了視圖中的數(shù)據(jù)，視圖的字段名和AS后SELECT子句的字段名相同。刪除視圖：DROPVIEW視圖名當一個視圖是由單個表導出時可以進行插入和更新操作，但不能進行刪除操作；當視圖是從多個表導出時，插入、更新和刪除操作都不允許進行。當一個視圖是由單個表導出時可以進行插入和更新操作，但不能進行刪除操作；當視圖是從多個表導出時，插入、更新和刪除操作都不允許進行。例1：根據(jù)“歌手”表建立視圖myview,視圖中含有包括了“歌手號”左邊第一位是“1”的所有記錄，正確的SQL語句是：A．CREATEVIEWmyviewASSELECT*FROM歌手WHERELEFT（歌手號，1）=“1”B．CREATEVIEWmyviewASSELECT*FROM歌手WHERELIKE（“1”歌手號）C．CREATEVIEWmyviewSELECT*FROM歌手WHERELEFT（歌手號，1）=“1”D．CREATEVIEWmyviewSELECT*FROM歌手WHERELIKE（“1”歌手號）例2：有如下SQL語句CREATEVIEWview_stockASSELECT股票名稱AS名稱，單價FROMstock執(zhí)行該語句后產(chǎn)生的視圖含有的字段名是：A)股票名稱、單價B)名稱、單價C)名稱、單價、交易所D)股票名稱、單價、交易所插入數(shù)據(jù)---INSERTINTO格式1：INSERTINTO表名(字段1,字段2,…,字段n)VALUES(表達式1,表達式2,…表達式n)此格式適用于向表中插入一條不完整記錄插入數(shù)據(jù)---INSERTINTO格式1：INSERTINTO表名(字段1,字段2,…,字段n)VALUES(表達式1,表達式2,…表達式n)此格式適用于向表中插入一條不完整記錄格式2：INSERTINTO表名VALUES(表達式1,表達式2,…表達式n)此格式適用于向表中插入一條完整記錄，但表達式1,表達式2,…表達式n的順序和類型要和表完全一致例：插入一條記錄到“選課”表中，選課.DBF：學號（C,8），課程號(C,3)，成績(N,5,1)學號、課程號和成績分別是“02080111”、“103”和80，正確的SQL語句是

A．INSERTINTO選課VALUES(“02080111”，”103”，80)

B．INSERTVALUES(“02080111”，”103”，80)