第七章結(jié)構(gòu)化查詢語言SQLSQL語言功能包括數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)操縱、和數(shù)據(jù)控制四個(gè)方面。7.1SQL的數(shù)據(jù)定義功能SQL的數(shù)據(jù)定義功能用于定義數(shù)據(jù)表的結(jié)構(gòu)，如創(chuàng)建、修改、刪除數(shù)據(jù)表。一創(chuàng)建數(shù)據(jù)表命令：createtable<表名>(<字段名1><字段類型>[(<字段寬度>[,<小數(shù)位數(shù)>])][,<字段名2>…])功能：創(chuàng)建數(shù)據(jù)表結(jié)構(gòu)例7-1：createtablestud(學(xué)號(hào)c(8),姓名c(8),性別c(2),出生日期d,;專業(yè)號(hào)c(2),入?？偡謓(6,2),團(tuán)員l,簡(jiǎn)歷m,照片g)1.二修改數(shù)據(jù)表命令1：altertable<表名>[add/alter[column]<字段名><字段類型>[<字段寬度>[,小數(shù)位]]命令2：altertable<表名>[drop[column]<字段名>][rename[column]<舊字段名>to<新字段名>]功能：修改表的結(jié)構(gòu)例7-2：在stud中增加一個(gè)“電話”字段altertablestudaddcolumn電話c(8)例7-3：將stud中電話字段的寬度該為10altertablestudalter電話c(10)例7-4：刪除stud中的電話字段altertablestuddrop電話例7-5：將stud中“入?？偡帧弊侄卧撁麨椤案呖汲煽?jī)”altertablestudrename入?？偡謙o高考成績(jī)2.7.2SQL的數(shù)據(jù)查詢功能一select命令的格式格式：select[all/distinct][top<表達(dá)式>[percent]][<別名>.]<列表達(dá)式>[as<攔名>][,<別名.>]<列表達(dá)式>[as<攔名>…]from<表名>[,<表名>…][inner/left/right/fulljoin<表名>[on<連接條件>…]][[into<新表名>/[tofile<文件名>/toprinter/toscreen]][where<連接條件>[and<連接條件>…][and/or<篩選條件>[and/or<篩選條件>…]][groupby<列名>[,<列名>…]][having<篩選條件>][orderby<列名>[asc/desc][,<列名>[asc/desc]…]]功能：實(shí)現(xiàn)數(shù)據(jù)查詢。select語句的執(zhí)行過程為：根據(jù)where子句的連接和檢索條件，從from子句指定的基本表或視圖中選取滿足條件的元組，再按照select子句中指定的列表達(dá)式，選出元組中的屬性值形成結(jié)果表。3.說明：from<表名>[,<表名>…]:指明要查詢的所有數(shù)據(jù)表(2)[<別名>.]<列表達(dá)式>[as<欄名>]：[<別名.>]<列表達(dá)式>可以是字段名,也可是表達(dá)式，而<別名>是字段所在的表名；[as<欄名>]表示可以給輸出時(shí)顯示的列標(biāo)題重新命名二投影查詢

例7-7：查詢stud表中前5列的信息

select學(xué)號(hào),姓名,入?？偡?性別,出生日期fromstud

例7-8：查詢stud表中入?？偡肿畹偷奈迕麑W(xué)生基本情況，按入?？偡钟傻偷礁哌M(jìn)行排列，同時(shí)指定表中的字段在查詢結(jié)果中的列標(biāo)題

seletop5學(xué)號(hào)as學(xué)生編號(hào),姓名as學(xué)生姓名,性別as學(xué)生性別,;

入?？偡謅s高考分?jǐn)?shù)fromstudorderby入?？偡?/p>

例7-9：在xk中查詢選修了課程的學(xué)號(hào)

seledistinct學(xué)號(hào)fromxk4.三條件查詢當(dāng)要在數(shù)據(jù)表中找出滿足某些條件的行時(shí)，則需使用where子句指定查詢條件(格式：where<連接條件>)1.比較大小

例7-11：在stud.dbf中查詢專業(yè)號(hào)為“01”的學(xué)生記錄例7-12：在stud.dbf中查詢?nèi)雽W(xué)總分大于等于550分的學(xué)生學(xué)號(hào)、姓名、性別、出生日期和入學(xué)總分sele學(xué)號(hào),姓名,性別,出生日期,入?？偡謋romstudwhere;入?？偡?gt;=550sele*fromstudwhere專業(yè)號(hào)='01'2.多重條件查詢當(dāng)where子句需要指定一個(gè)以上的查詢條件時(shí)，則需要使用邏輯運(yùn)算符and，or和not將其連接成復(fù)合的邏輯表達(dá)式。例7-13：在stud.dbf中查詢男團(tuán)員的學(xué)號(hào)、姓名、性別和入?？偡?。sele學(xué)號(hào),姓名,性別,入校總分fromstudwhere性別='男'and團(tuán)員5.3.確定范圍

例7-14：在js中查詢工資在1500至2000之間的教師的教師號(hào)、姓名及職稱。sele教師號(hào),姓名,職稱fromjswhere工資>=1500and工資<=2000sele教師號(hào),姓名,職稱fromjswhere工資between1500and20004.確定集合利用“in”操作可以查詢屬性值屬于指定集合的元組

例7-16：在xk中查詢選修了課程“c140”或“c160”并且成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)sele學(xué)號(hào),課程號(hào),成績(jī)fromxkwhere(課程號(hào)='c140'or課程號(hào);='c160')and成績(jī)>=80sele學(xué)號(hào),課程號(hào),成績(jī)fromxkwhere課程號(hào)in('c140','c160');and成績(jī)>=806.例7-17：在xk中查詢沒有選修'c140'，也沒有選修'c160'的課程并且成績(jī)?cè)?5和90之間的學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)sele學(xué)號(hào),課程號(hào),成績(jī)fromxkwhere課程號(hào)notin;('c140','c160')and成績(jī)between85and905.部分匹配查詢也稱模糊查詢，使用like或notlike子句定義，格式：<屬性名>like<字符串常量>%：表示任意長(zhǎng)度的字符串_：表示任意一個(gè)字符或漢字例7-18：查詢stud表中所有姓“劉”的學(xué)生的學(xué)號(hào)和姓名

例7-19：查詢stud表中第二個(gè)漢字是“敏”的學(xué)生的學(xué)號(hào)和姓名

sele學(xué)號(hào),姓名fromstudwhere姓名like'劉%'sele學(xué)號(hào),姓名fromstudwhere姓名like'_敏%'7.四.統(tǒng)計(jì)查詢常用函數(shù)及功能：AVGSUMCOUNTMAXMIN說明：在以上函數(shù)中，可使用ALL或DISTINCTDISTINCT：表示在計(jì)算時(shí)取消指定列中的重復(fù)值A(chǔ)LL(默認(rèn)值)：表示不取消重復(fù)值例7-20：求學(xué)號(hào)為“s0401109”學(xué)生的總分和平均分并顯示如圖

例7-21：求選修課程號(hào)為'c140'學(xué)生的最高分、最低分及之間相差的分?jǐn)?shù)(顯示課程號(hào))

sele學(xué)號(hào),sum(成績(jī))as總分,avg(成績(jī))as平均分fromxk;where學(xué)號(hào)='s0401109'sele課程號(hào),max(成績(jī))as最高分,min(成績(jī))as最低分,max(成;績(jī))-min(成績(jī))as相差分?jǐn)?shù)fromxkwhere課程號(hào)='c140'8.例7-22：求入?？偡衷?80分以上的學(xué)生的人數(shù)selecount(學(xué)號(hào))as入校總分在580分以上的學(xué)生的人數(shù)from;studwhere入?？偡?gt;=580例7-23：統(tǒng)計(jì)xk表中有多少門課selecount(distinct課程號(hào))as選課表中課程數(shù)fromxk例7-24：利用特殊函數(shù)count(*)求js表中“教授”和“副教授”的人數(shù)selecount(*)as教授和副教授的人數(shù)fromjs;where職稱in(“教授”,“副教授”)五.分組查詢將查詢結(jié)果按照指定字段相同的值進(jìn)行分組，使用groupby子句例7-25：查詢各位教師的教師號(hào)及其任課的門數(shù)

sele教師號(hào),count(*)as任課門數(shù)fromskgroupby教師號(hào)9.例7-26：查詢選修兩門以上課程的學(xué)生學(xué)號(hào)和選課門數(shù)

例7-27：在課程c120、c140、c150、c160中查詢學(xué)生平均成績(jī)?cè)?0分以上課程的學(xué)生的平均分（顯示課程號(hào)）select課程號(hào),avg(成績(jī))as平均分fromxkgroupby課程號(hào);having課程號(hào)in('c120','c140','c150','c160')andavg(成績(jī))>=80六.查詢的排序當(dāng)需要對(duì)查詢結(jié)果排序時(shí)，可以使用orderby子句對(duì)查詢的結(jié)果按一個(gè)或多個(gè)屬性列進(jìn)行升序(asc)或降序(desc)排列例7-28：查詢選修了課程“c140”的學(xué)生學(xué)號(hào)和成績(jī)，并按成績(jī)降序排列select學(xué)號(hào)as選課門數(shù)在兩門以上學(xué)生的學(xué)號(hào),count(*)as;選課門數(shù)fromxkgroupby學(xué)號(hào)havingcount(*)>=210.select學(xué)號(hào),成績(jī)fromxkwhere課程號(hào)='c140'orderby成績(jī)desc例7-29：查詢選修c120、c130和c150課程學(xué)生的學(xué)號(hào)、課程號(hào)和成績(jī)，查詢結(jié)果按課程號(hào)升序排列，課程號(hào)相同再按成績(jī)降序排列select學(xué)號(hào),課程號(hào),成績(jī)fromxkwhere課程號(hào);in('c120','c130','c150')orderby課程號(hào),成績(jī)desc例7-30：求選課在三門以上且各門課程均及格的學(xué)生的學(xué)號(hào)及平均成績(jī)，查詢結(jié)果按學(xué)號(hào)降序列出select學(xué)號(hào),avg(成績(jī))as平均成績(jī)fromxkgroupby學(xué)號(hào)having;count(*)>=3and成績(jī)>=60orderby學(xué)號(hào)desc七.連接查詢通過加在連接字段上的條件將多個(gè)表連接起來，以便從多個(gè)表中查詢數(shù)據(jù)11.1.等值連接與非等值連接例7-31：查詢陳靜老師所講授課程的課程號(hào)，教師號(hào)和姓名

innerjoin:內(nèi)連接，顯示符合條件的記錄例7-32：查詢選修了課程“數(shù)據(jù)庫”或“電子商務(wù)”學(xué)生的學(xué)號(hào)、姓名選課名和成績(jī)selectstud.學(xué)號(hào),姓名,課程名,成績(jī)fromstud,xk,kcwherestud.學(xué)號(hào);=xk.學(xué)號(hào)andxk.課程號(hào)=kc.課程號(hào)and課程名in('數(shù)據(jù)庫',;'電子商務(wù)')selectjs.教師號(hào),js.姓名,sk.課程號(hào)fromjsinnerjoinskonjs.教師號(hào);=sk.教師號(hào)and姓名='陳靜'selectjs.教師號(hào),js.姓名,sk.課程號(hào)fromjs,sk;wherejs.姓名='陳靜'andjs.教師號(hào)=sk.教師號(hào)12.2.自身連接例7-33：查詢所有比“陳靜”工資高的教師姓名、職稱、工資和“陳靜”工資

例7-34:查詢與“周密”職稱相同的教師姓名、性別和職稱selectx.姓名,x.性別,x.職稱fromjsasx,jsasy;wherex.職稱=y.職稱andy.姓名='周密'selectx.姓名,x.工資,x.職稱,y.工資as陳靜工資fromjsasx,jsas;ywherex.工資>y.工資andy.姓名='陳靜'xy3.外連接例7-35：查詢教師姓名、職稱及所授課程的課程名select姓名,職稱,課程名fromjs,sk,kcwherejs.教師號(hào)=sk.教師號(hào);andsk.課程號(hào)=kc.課程號(hào)13.說明：外連接中，參與連接的表有主從之分，以主表中的每行數(shù)據(jù)去匹配從表中的數(shù)據(jù)列，符合連接條件的數(shù)據(jù)將直接顯示，對(duì)于那些不符合條件的列，將填上null值顯示。左連接的主表在關(guān)鍵字leftjoin的左方，右連接的主表在關(guān)鍵字rightjoin的右方例7-36：在js表中添加一條記錄，以外連接的方式查詢教師姓名、職稱及所授課程的課程號(hào)select姓名,職稱,課程號(hào)fromjsleftjoinskonjs.教師號(hào)=sk.教師號(hào)select姓名,職稱,課程號(hào)fromskleftjoinjsonjs.教師號(hào)=sk.教師號(hào)select姓名,職稱,課程號(hào)fromjsrightjoinskonjs.教師號(hào)=sk.教師號(hào)select姓名,職稱,課程號(hào)fromskrightjoinjsonjs.教師號(hào)=sk.教師號(hào)select姓名,職稱,課程號(hào)fromjsfulljoinskonjs.教師號(hào)=sk.教師號(hào)select姓名,職稱,課程號(hào)fromskfulljoinjsonjs.教師號(hào)=sk.教師號(hào)14.八.嵌套查詢?cè)趙here子句中包含一個(gè)形如select-from-where的查詢塊，此查詢塊稱為嵌套查詢或子查詢，包含子查詢的語句稱為外部查詢或父查詢。1.返回一個(gè)值的子查詢當(dāng)子查詢的返回值只有一個(gè)時(shí)，可以使用比較運(yùn)算符將父查詢和子查詢連接起來

例7-37：查詢與“陳靜”教師職稱相同的教師姓名和職稱

2.返回一組值的子查詢當(dāng)子查詢的返回值不止一個(gè)時(shí)，則不能直接使用比較運(yùn)算符，可以在比較運(yùn)算符和子查詢之間插入any或allselect姓名,職稱fromjswhere職稱=;(select職稱fromjswhere姓名='陳靜')15.(1)使用any（any的含義為任意一個(gè)）例7-38：查詢講授課程號(hào)為“c140”的教師姓名

例7-39：查詢比男生入校總分最低分高的女生姓名和入?？偡謘elect姓名,入?？偡謋romstudwhere性別='女'and入?？偡?gt;any;(sele入校總分fromstudwhere性別='男')說明：可以使用in代替“=any”select姓名fromjswhere教師號(hào)=any;(sele教師號(hào)fromskwhere課程號(hào)='c140')16.(2)使用all（all含義為全部）例7-41：查詢比女生入?？偡肿罡叻指叩哪猩彰腿胄？偡謘elect姓名,入?？偡謋romstudwhere性別='男'and入?？偡?gt;all;(sele入校總分fromstudwhere性別='女')17.(4)使用existsexists用于判斷查詢結(jié)果是否存在。當(dāng)exists的子查詢結(jié)果存在即非空時(shí)，則外層的where子句返回真值，否則返回假值例7-42：查詢講授課程號(hào)為“c140”的教師姓名select姓名fromjswhereexists;(select*fromsk;wherejs.教師號(hào)=sk.教師號(hào)and課程號(hào)='c140')18.九查詢結(jié)果的輸出intotable<表名>：輸出到數(shù)據(jù)表；intocursor<表名>：輸出到臨時(shí)表。臨時(shí)表只存儲(chǔ)在內(nèi)存中，不作為永久的數(shù)據(jù)表存儲(chǔ)在磁盤中例7-43：查詢陳靜老師所講授課程的課程號(hào)，教師號(hào)和姓名，輸出到臨時(shí)表,然后顯示該表的內(nèi)容

7.3SQL的數(shù)據(jù)更新功能一插入數(shù)據(jù)記錄1.插入一行新記錄：insertinto表名[(字段名1[,字段名2,…])]values(表達(dá)式1[,表達(dá)式2…])selectjs.教師號(hào),js.姓名,sk.課程號(hào)fromjs,skwherejs.姓名='陳靜';andjs.教師號(hào)=sk.教師號(hào)intocursorcx1brow19.2.插入多行記錄將一個(gè)表中的數(shù)據(jù)抽取數(shù)行插入另一表中，可以通過子查詢來實(shí)現(xiàn)例7-45：求出各學(xué)生選修課的平均成績(jī)，把結(jié)果存放在新表學(xué)生平均成績(jī)中createtable學(xué)生平均成績(jī)(姓名c(8),平均成績(jī)n(3))insertinto學(xué)生平均成績(jī)sele姓名,avg(成績(jī))fromstud,xk;wherestud.學(xué)號(hào)=xk.學(xué)號(hào)groupbyxk.學(xué)號(hào)二修改數(shù)據(jù)記錄命令：update<表名>