,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,*,Click to edit Master title style,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Click to edit Master title style,查看數(shù)據(jù)庫(kù),倉(cāng)庫(kù)管理,Click to edit Master text styles,Second level,Third level,Fourth level,Fifth level,Click to edit Master title style,*,第四章,關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言,SQL,Visual FoxPro,數(shù)據(jù)庫(kù)程序設(shè)計(jì),蘇州科技學(xué)院電子與信息工程學(xué)院,USTS,計(jì)算機(jī),基礎(chǔ)教學(xué)部,目 錄,4.1,SQL,概述,1,4.2,查詢功能,2,4.3,操作功能,3,4.4,定義功能,4,4.1 SQL,概述,ANSI,提出,SQL,1986,年,1987,年,ISO,采納,SQL,1989,年,ISO,提出,SQL89,1992,年,ISO,提出,SQL92,SQL,功能,命令動(dòng)詞,數(shù)據(jù)查詢,SELECT,數(shù)據(jù)定義,CREATE,、,DROP,、,ALTER,數(shù)據(jù)操縱,INSERT,、,UPDATE,、,DELETE,數(shù)據(jù)控制,GRANT,、,REVOKE,目 錄,4.2.1,簡(jiǎn)單查詢,4.2.2,簡(jiǎn)單的連接查詢,4.2.3,嵌套查詢,4.2.4,幾個(gè)特殊的運(yùn)算符,4.2.5,排序,4.2.6,簡(jiǎn)單的計(jì)算查詢,4.2.7,分組與計(jì)算查詢,4.2.8,利用空值查詢,4.2.9,別名與自連接查詢,內(nèi)外層相關(guān)嵌套查詢,使用量詞和謂詞的查詢,超連接查詢,集合的并運(yùn)算,中,SELECT,的幾個(gè)特殊選項(xiàng),4.1,SQL,概述,1,4.2,查詢功能,2,4.3,操作功能,3,4.4,定義功能,4,SELECT SQL,SELECT ALL|DISTINCT TOP,nExpr,PERCENT,Alias,.,Select_Item,AS,Column_Name,Alias,.,Select_Item,AS,Column_Name,.,FROM FORCE,DatabaseName,!,Table,AS,Local_Alias,INNER,|LEFT OUTER|RIGHT OUTER|FULL OUTER,JOIN,DatabaseName,!,Table,AS,Local_Alias,ON,JoinCondition,INTO,Destination,|TO FILE,FileName,ADDITIVE,|TO PRINTER PROMPT|TO SCREEN,PREFERENCE,PreferenceName,NOCONSOLE,PLAIN,NOWAIT,WHERE,JoinCondition,AND,JoinCondition,.AND|OR,FilterCondition,AND|OR,FilterCondition,.,GROUP BY,GroupColumn,GroupColumn,.,HAVING,FilterCondition,UNION ALL,SELECT,命令,ORDER BY,Order_Item,ASC|DESC,Order_Item,ASC|DESC.,問(wèn)題,需要訪問(wèn)的數(shù)據(jù)涉及到多張表,查詢學(xué)生名、課程名、成績(jī),xs.xm kc.kcm cj.cj,需要訪問(wèn)的只是部分?jǐn)?shù)據(jù),工商管理系的學(xué)生,xh,xm,需要訪問(wèn)計(jì)算或統(tǒng)計(jì)數(shù)據(jù),每個(gè)學(xué)生的平均分,xs.xm,平均,(cj.cj),分組,xs.xm,每門課程,80,分以上的人數(shù),kc.kcm,個(gè)數(shù),(),條件,cj.cj=80,分組,cj.kcdh,解決辦法,查詢和視圖！,共同點(diǎn),從表中檢索或統(tǒng)計(jì)所需的數(shù)據(jù),區(qū)別,查詢是只讀的；視圖可以被修改，并且可以將更新結(jié)果送回源表,查詢以獨(dú)立的文件存儲(chǔ)；視圖不以獨(dú)立文件存儲(chǔ)，系統(tǒng)將其名稱及定義存儲(chǔ)在數(shù)據(jù)庫(kù)中,SELECT SQL,定義,數(shù)據(jù)源,指定數(shù)據(jù)源表,FROM,子句,確定源表間的聯(lián)接,JOINON,子句,定義,結(jié)果,篩選源表記錄,WHERE,子句,指定輸出字段,字段、函數(shù)和表達(dá)式的列表或*,指定輸出類型,INTO,子句和,TO,子句,定義記錄的分組,GROUP BY,子句,指定結(jié)果順序,ORDER BY,子句,篩選結(jié)果記錄,HAVING,子句,指定有無(wú)重復(fù)記錄,ALL/DISTINCT,指定結(jié)果的范圍,TOP nExprPERCENT,簡(jiǎn)單查詢,例,4.1,：從職工表中檢索所有工資值。,例,4.2,：檢索倉(cāng)庫(kù)關(guān)系中的所有元組。,例,4.3,：檢索工資多于,1230,的職工號(hào)。,例,4.4,：檢索哪些倉(cāng)庫(kù)有工資多于,1210,的職工。,例,4.5,：給出在倉(cāng)庫(kù)“,WH1”,或“,WH2”,工作并且工資少于,1250,員的職工號(hào)。,簡(jiǎn)單連接查詢,例,4.7,：找出工作在面積大于,400,的倉(cāng)庫(kù)的職工號(hào)以及這些職工工作的城市。,Select,職工號(hào),城市,from,倉(cāng)庫(kù)，職工,;where(,面積,400)and(,職工,.,倉(cāng)庫(kù)號(hào),=,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),),Select,職工號(hào),城市,from,倉(cāng)庫(kù),inner join,職工,;On(,職工,.,倉(cāng)庫(kù)號(hào),=,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),)where,面積,400,嵌套查詢,例,4.9,：查詢所有職工的工資都多于,1210,元的倉(cāng)庫(kù)的信息。,沒(méi)有一個(gè)職工的工資少于或等于,1210,元的倉(cāng)庫(kù)信息。,Select*from,倉(cāng)庫(kù),where,倉(cāng)庫(kù)號(hào),not in;,(select,倉(cāng)庫(kù)號(hào),from,職工,where,工資,=1210);,and,倉(cāng)庫(kù)號(hào),in(select,倉(cāng)庫(kù)號(hào),from,職工,),幾個(gè)特殊運(yùn)算符,例,4.11:,檢索出工資在,1220,元到,1240,元范圍內(nèi)的職工信息。,Select*from,職工,;,where,工資,between,1220,and,1240,例,4.12:,從供應(yīng)商關(guān)系中檢索出全部公司的信息（不要工廠或其他供應(yīng)商的信息）。,Select*from,供應(yīng)商,;,where,供應(yīng)商名,like,“%,公司”,4.2.5,排序,例,4.15,先按倉(cāng)庫(kù)號(hào)排序，再按工資排序輸出全部職工信息。,Select*from,職工,order by,倉(cāng)庫(kù)號(hào),工資,分組計(jì)算查詢,Count(),、,sum(),、,avg(),、,max(),、,min(),例：查詢,cj,表中每個(gè)學(xué)生的考試門數(shù)、最高分、最低分、平均分及總分，結(jié)果按學(xué)號(hào)升序排序。,Select,cj.xh,count,(*)as,考試門數(shù),;,max,(,cj.cj)as,最高分,min,(cj.cj)as,最低分,;,avg,(cj.cj)as,平均分,sum,(cj.cj)as,總分,;,from,cj;,group by,cj.xh;,order by,1,利用空值查詢,例,4.23,：找出尚未確定供應(yīng)商的訂購(gòu)單。,Select*from,訂購(gòu)單,;,where,供應(yīng)商號(hào),is null,別名與自連接查詢,在,FROM,子句中使用：,雇員關(guān)系：雇員（雇員號(hào)，雇員姓名，經(jīng)理）,雇員號(hào),雇員姓名,經(jīng)理,E3,趙勇,E4,錢潮,E3,E6,孫潔,E3,E8,李淥,E6,Select s.,雇員姓名,“,領(lǐng)導(dǎo),”,e.,雇員姓名,;,from,雇員,s,雇員,e,where,s.,雇員號(hào),=e.,經(jīng)理,內(nèi)外層互相關(guān)嵌套查詢,例：列出每個(gè)職工經(jīng)手的具有最高金額的訂購(gòu)單信息。,Select,out.,職工號(hào),out.,供應(yīng)商號(hào),out.,訂購(gòu)單號(hào),out.,訂購(gòu)日期,out.,總金額,;,from,訂購(gòu)單,out where,總金額,=;,(select max(,總金額,)from,訂購(gòu)單,inner1;,where,out.,職工號(hào),=inner1.,職工號(hào),),使用量詞和謂詞查詢,ANY|ALL|SOME(,子查詢,),ANY,與,SOME,是同義詞，在進(jìn)行比較運(yùn)算時(shí)只要子查詢中有一行能使結(jié)果為真，則結(jié)果為真。,ALL,要求子查詢中的所有行都能使結(jié)果為真時(shí)，結(jié)果才為真。,NOT EXISTS(,子查詢,),not Exists,是謂詞，用來(lái)檢查在子查詢中是否有結(jié)果返回（即存在元組或不存在元組）,例：查詢那些倉(cāng)庫(kù)中還沒(méi)有職工的倉(cāng)庫(kù)信息。,SELECT*FROM,倉(cāng)庫(kù),WHERE NOT EXISTS;,(SELECT*FROM,職工,where,倉(cāng)庫(kù)號(hào),=,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),),select*from,倉(cāng)庫(kù),where,倉(cāng)庫(kù)號(hào),not in;,(select,倉(cāng)庫(kù)號(hào),from,職工,),例：檢索有職工的工資大于或等于,WH1,倉(cāng)庫(kù)中任何一名職工的工資的倉(cāng)庫(kù)號(hào)。,Select distinct,倉(cāng)庫(kù)號(hào),from,職工,;,where,工資,=any;,(select,工資,from,職工,where,倉(cāng)庫(kù)號(hào),=“WH1”),超連接查詢,SELECT,from,table,inner|left|right|full join,table,On,joincondition,Where,例：普通連接,Select,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),城市,面積,職工號(hào),工資,;,from,倉(cāng)庫(kù),inner join,職工,on,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),=,職工,.,倉(cāng)庫(kù)號(hào),例：左連接,Select,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),城市,面積,職工號(hào),工資,;,from,倉(cāng)庫(kù),left join,職工,on,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),=,職工,.,倉(cāng)庫(kù)號(hào),例：右連接,Select,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),城市,面積,職工號(hào),工資,;,from,倉(cāng)庫(kù),right join,職工,on,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),=,職工,.,倉(cāng)庫(kù)號(hào),例：全連接,Select,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),城市,面積,職工號(hào),工資,;,from,倉(cāng)庫(kù),full join,職工,on,倉(cāng)庫(kù),.,倉(cāng)庫(kù)號(hào),=,職工,.,倉(cāng)庫(kù)號(hào),集合的并運(yùn)算,UNION,：兩個(gè)查詢結(jié)果具有相同結(jié)構(gòu)的關(guān)系并在一起。,查詢?nèi)＝處熍c學(xué)生的姓名和類別信息。,Select js.xm as,姓名,教師,as,類別,from js;,Union,;,Select xs.xm as,姓名,學(xué)生,as,類別,from xs,4.2.14Visual FoxPro SQL SELECT,的幾個(gè)特殊選項(xiàng),顯示部分?jǐn)?shù)據(jù)：,1.,顯示工資最高的,3,位職工信息,Select*top 3 from,職工,order by,工資,desc,2.,顯示工資最低的那,30%,職工的信息,Select*top 30 percent from,職工,order by,工資,查詢結(jié)果的去處：,INTO ARRAY,ArrayName,INTO CURSOR,CursorName,INTO TABLE|DBF,TableName,TO FILE,FileName,ADDITIVE,TO PRINTER PROMPT,目 錄,4.3.1,插入數(shù)據(jù),4.3.2,更新數(shù)據(jù),4.3.3,刪除,4.1,SQL,概述,1,4.2,查詢功能,2,4.3,操作功能,3,4.4,定義功能,4,插入數(shù)據(jù),INSERT INTO,dbf_name,(,fname1,fname2,),VALUES,(,eExpression1,eExpression2,),Insert into,訂購(gòu)單,values(E7,S4,OR01,2001-05-25,5000),更新數(shù)據(jù),UPDATE,TableName,SET,Column_Name1=eExpression1,Column_Name2=eExpression2,.,WHERE,Condition,為,js,表添加一個(gè)年齡字段，并計(jì)算所有男教師的年齡。,A