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

第四章

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

數(shù)據(jù)庫程序設(shè)計蘇州科技學院電子與信息工程學院USTS計算機基礎(chǔ)教學部目錄4.1

SQL概述14.2查詢功能24.3操作功能34.4定義功能44.1SQL概述ANSI提出SQL1986年1987年ISO采納SQL1989年ISO提出SQL891992年ISO提出SQL92SQL功能命令動詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE、DROP、ALTER數(shù)據(jù)操縱INSERT、UPDATE、DELETE數(shù)據(jù)控制GRANT、REVOKE目錄4.2.1簡單查詢4.2.2簡單的連接查詢4.2.3嵌套查詢4.2.4幾個特殊的運算符4.2.5排序4.2.6簡單的計算查詢4.2.7分組與計算查詢4.2.8利用空值查詢4.2.9別名與自連接查詢內(nèi)外層相關(guān)嵌套查詢使用量詞和謂詞的查詢超連接查詢集合的并運算中SELECT的幾個特殊選項4.1

SQL概述14.2查詢功能24.3操作功能34.4定義功能4SELECTSQLSELECT[ALL|DISTINCT][TOPnExpr[PERCENT]]

[Alias.]Select_Item[ASColumn_Name]

[,[Alias.]Select_Item[ASColumn_Name]...]FROM[FORCE]

[DatabaseName!]Table[[AS]Local_Alias]

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

DatabaseName!]Table[[AS]Local_Alias]

[ONJoinCondition…][[INTODestination]

|[TOFILEFileName[ADDITIVE]|TOPRINTER[PROMPT]

|TOSCREEN]][PREFERENCEPreferenceName][NOCONSOLE][PLAIN][NOWAIT][WHEREJoinCondition[ANDJoinCondition...]

[AND|ORFilterCondition[AND|ORFilterCondition...]]][GROUPBYGroupColumn[,GroupColumn...]][HAVINGFilterCondition][UNION[ALL]SELECT命令][ORDERBYOrder_Item[ASC|DESC][,Order_Item[ASC|DESC]...]]問題需要訪問的數(shù)據(jù)涉及到多張表查詢學生名、課程名、成績xs.xmkc.kcmcj.cj需要訪問的只是局部數(shù)據(jù)工商管理系的學生xh,xm需要訪問計算或統(tǒng)計數(shù)據(jù)每個學生的平均分xs.xm平均(cj.cj)分組xs.xm每門課程80分以上的人數(shù)

kc.kcm個數(shù)()條件cj.cj>=80分組cj.kcdh解決方法查詢和視圖！共同點從表中檢索或統(tǒng)計所需的數(shù)據(jù)區(qū)別查詢是只讀的；視圖可以被修改，并且可以將更新結(jié)果送回源表查詢以獨立的文件存儲；視圖不以獨立文件存儲，系統(tǒng)將其名稱及定義存儲在數(shù)據(jù)庫中SELECTSQL定義數(shù)據(jù)源指定數(shù)據(jù)源表FROM

子句確定源表間的聯(lián)接…JOIN…ON…子句定義結(jié)果篩選源表記錄WHERE子句指定輸出字段字段、函數(shù)和表達式的列表或*指定輸出類型INTO子句和TO子句定義記錄的分組GROUPBY子句指定結(jié)果順序ORDERBY子句篩選結(jié)果記錄HAVING子句指定有無重復(fù)記錄ALL/DISTINCT指定結(jié)果的范圍TOPnExpr[PERCENT]簡單查詢例4.1：從職工表中檢索所有工資值。例4.2：檢索倉庫關(guān)系中的所有元組。例4.3：檢索工資多于1230的職工號。例4.4：檢索哪些倉庫有工資多于1210的職工。例4.5：給出在倉庫“WH1”或“WH2”工作并且工資少于1250員的職工號。簡單連接查詢例4.7：找出工作在面積大于400的倉庫的職工號以及這些職工工作的城市。Select職工號,城市from倉庫，職工;

where(面積>400)and(職工.倉庫號=倉庫.倉庫號)Select職工號,城市from倉庫innerjoin職工;

On(職工.倉庫號=倉庫.倉庫號)where面積>400嵌套查詢例4.9：查詢所有職工的工資都多于1210元的倉庫的信息。沒有一個職工的工資少于或等于1210元的倉庫信息。Select*from倉庫where倉庫號notin;(select倉庫號from職工where工資<=1210);

and倉庫號in(select倉庫號from職工)幾個特殊運算符例4.11:檢索出工資在1220元到1240元范圍內(nèi)的職工信息。Select*from職工;where工資between1220and1240例4.12:從供給商關(guān)系中檢索出全部公司的信息〔不要工廠或其他供給商的信息〕。Select*from供給商;where供給商名like“%公司”4.2.5排序例4.15先按倉庫號排序，再按工資排序輸出全部職工信息。Select*from職工orderby倉庫號,工資分組計算查詢Count()、sum()、avg()、max()、min()例：查詢cj表中每個學生的考試門數(shù)、最高分、最低分、平均分及總分，結(jié)果按學號升序排序。Selectcj.xh,count(*)as考試門數(shù),;max(cj.cj)as最高分,min(cj.cj)as最低分,;avg(cj.cj)as平均分,sum(cj.cj)as總分;fromcj;groupbycj.xh;orderby1利用空值查詢例4.23：找出尚未確定供給商的訂購單。Select*from訂購單;where供給商號isnull別名與自連接查詢在FROM子句中使用：<關(guān)系名><別名>雇員關(guān)系：雇員〔雇員號，雇員姓名，經(jīng)理〕雇員號雇員姓名經(jīng)理E3趙勇E4錢潮E3E6孫潔E3E8李淥E6Selects.雇員姓名,“領(lǐng)導(dǎo)”,e.雇員姓名;from雇員s,雇員ewheres.雇員號=e.經(jīng)理內(nèi)外層互相關(guān)嵌套查詢例：列出每個職工經(jīng)手的具有最高金額的訂購單信息。Selectout.職工號,out.供給商號,out.訂購單號,out.訂購日期,out.總金額;from訂購單outwhere總金額=;(selectmax(總金額)from訂購單inner1;whereout.職工號=inner1.職工號)使用量詞和謂詞查詢<表達式><比較運算符>[ANY|ALL|SOME](子查詢)ANY與SOME是同義詞，在進行比較運算時只要子查詢中有一行能使結(jié)果為真，那么結(jié)果為真。ALL要求子查詢中的所有行都能使結(jié)果為真時，結(jié)果才為真。[NOT]EXISTS(子查詢)[not]Exists是謂詞，用來檢查在子查詢中是否有結(jié)果返回〔即存在元組或不存在元組〕例：查詢那些倉庫中還沒有職工的倉庫信息。SELECT*FROM倉庫WHERENOTEXISTS;(SELECT*FROM職工where倉庫號=倉庫.倉庫號)<==>select*from倉庫where倉庫號notin;(select倉庫號from職工)例：檢索有職工的工資大于或等于WH1倉庫中任何一名職工的工資的倉庫號。Selectdistinct倉庫號from職工;where工資>=any;(select工資from職工where倉庫號=“WH1”)超連接查詢SELECT……from

tableinner|left|right|fulljointableOnjoinconditionWhere……例：普通連接Select倉庫.倉庫號,城市,面積,職工號,工資;from倉庫[inner]join職工on倉庫.倉庫號=職工.倉庫號例：左連接Select倉庫.倉庫號,城市,面積,職工號,工資;from倉庫leftjoin

職工on倉庫.倉庫號=職工.倉庫號例：右連接Select倉庫.倉庫號,城市,面積,職工號,工資;from倉庫rightjoin

職工on倉庫.倉庫號=職工.倉庫號例：全連接Select倉庫.倉庫號,城市,面積,職工號,工資;from倉庫fulljoin

職工on倉庫.倉庫號=職工.倉庫號集合的并運算UNION：兩個查詢結(jié)果具有相同結(jié)構(gòu)的關(guān)系并在一起。查詢?nèi)＝處熍c學生的姓名和類別信息。Selectjs.xmas姓名,"教師"as類別

fromjs;Union;Selectxs.xmas姓名,"學生"as類別

fromxs4.2.14VisualFoxProSQLSELECT的幾個特殊選項顯示局部數(shù)據(jù)：1.顯示工資最高的3位職工信息Select*top3from職工orderby工資desc2.顯示工資最低的那30%職工的信息Select*top30percentfrom職工orderby工資查詢結(jié)果的去處：INTOARRAYArrayNameINTOCURSORCursorNameINTOTABLE|DBFTableNameTOFILEFileName[ADDITIVE]TOPRINTER[PROMPT]目錄4.3.1插入數(shù)據(jù)4.3.2更新數(shù)據(jù)4.3.3刪除4.1

SQL概述14.2查詢功能24.3操作功能34.4定義功能4插入數(shù)據(jù)INSERTINTO

dbf_name[(fname1[,fname2,…])]VALUES(eExpression1[,eExpression2,…])Insertinto訂購單values("E7","S4","OR01",{^2001-05-25},5000)更新數(shù)據(jù)UPDATETableName

SET

Column_Name1=eExpression1

[,Column_Name2=eExpression2...]

WHERE

Condition為js表添加一個年齡字段，并計算所有男教師的年齡。Altertablejsaddcolumnnln(3,0)Updatejssetjs.nl=year(date())-year(js.csrq);wherejs.xb=“男”刪除數(shù)據(jù)DELETEFROMTableName[WHERECondition]

刪除所有考試不及格的成績信息。DELETEFROMcjWHEREcj.cj<60目錄4.4.1表的定義4.4.2表的刪除4.4.3表結(jié)構(gòu)的修改4.4.4視圖的定義4.1

SQL概述14.2查詢功能24.3操作功能34.4定義功能4表的定義CREATETABLE|DBFTableName1[NAMELongTableName][FREE]

(FieldName1FieldType[(nFieldWidth[,nPrecision])]

[NULL|NOTNULL]

[CHECKlExpression1[ERRORcMessageText1]]

[DEFAULTeExpression1]

[PRIMARYKEY|UNIQUE]

[REFERENCESTableName2[TAGTagName1]]

[NOCPTRANS]

[,FieldName2...]

[,PRIMARYKEYeExpression2TAGTagName2

|,UNIQUEeExpression3TAGTagName3]

[,FOREIGNKEYeExpression4TAGTagName4[NODUP]

REFERENCESTableName3[TAGTagName5]]

[,CHECKlExpression2[ERRORcMessageText2]])

|FROMARRAYArrayName倉庫1CREATETABLE倉庫1(倉庫號C(5)PRIMARYKEY,城市C(10),面積ICHECK(面積>0)ERROR“面積應(yīng)該大于0”)職工1CREATETABLE職工1(倉庫號C(5),職工號C(5)PRIMARYKEY,工資ICHECK(工資>=1000and工資<=5000)ERROR“工資值的范圍在1000-5000！”DEFAULT1200,FOREIGNKEY倉庫號TAG倉庫號REFERENCES倉庫1)表的刪除DROPTABLEtable_name表結(jié)構(gòu)的修改格式1：(定義或修改字段定義)ALTERTABLE

TableName1

ADD|ALTER[COLUMN]FieldName1

FieldType[(nFieldWidth[,nPrecision])]

[NULL|NOTNULL]

[CHECK

lExpression1[ERRORcMessageText1]]

[DEFAULT

eExpression1]

[PRIMARYKEY|UNIQUE]

[REFERENCES

TableName2[TAGTagName1]]AltertablexsaddrxrqDdefaultdate()格式2：〔修改字段的屬性設(shè)置〕ALTERTABLETableName1

ALTER[COLUMN]FieldName2

[NULL|NOTNULL]

[SETDEFAULTeExpression2]

[SETCHECKlExpression2[ERRORcMessageText2]]

[DROPDEFAULT]

[DROPCHECK]

給xb字段設(shè)置check格式3：〔修改表級別的屬性〕ALTERTABLETableName1

[DROP[COLUMN]FieldName3]

[SETCHECKlExpression3[ERRORcMes