數(shù)據(jù)庫(kù)虛擬教研室制作數(shù)據(jù)庫(kù)《數(shù)據(jù)庫(kù)原理及應(yīng)用》第5章單表查詢03分組查詢01無(wú)條件查詢02條件查詢04排序05限制查詢結(jié)果數(shù)量目錄單表查詢語(yǔ)句—SELECTSELECT<目標(biāo)列表達(dá)式>[FROM<表名>][WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]][LIMITn]；一般格式：無(wú)條件查詢可以選擇表中的指定列或所有列；可以更改結(jié)果列的顯示順序；可以使用列表達(dá)式進(jìn)行計(jì)算列；使用的列的別名:

列名[AS]列的別名如果別名中有特殊字符、空格、關(guān)鍵字等原樣輸出時(shí)要用引號(hào);選擇列（投影運(yùn)算）SELECT[*|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>，···]FROM<表名>【例5.1】查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系?！纠?.2】查詢?nèi)w學(xué)生的所有信息?！?.3】查詢學(xué)生學(xué)號(hào)和年齡。【例5.4】求學(xué)生的學(xué)號(hào)，姓名和性別，顯示時(shí)使用別名“學(xué)號(hào)”、“姓名”和“性別(男,女)”。 SELECTsno,sname,deptFROMstudent; SELECTsno,YEAR(SYSDATE())-YEAR(birthday)FROMstudent; SELECT*FROMstudent; SELECTsno學(xué)號(hào),sname姓名,sexAS'性別(男,女)'FROMstudent;無(wú)條件查詢消除重復(fù)行原本不完全相同的行，經(jīng)過向某些列投影后，可能變成相同的行。在SELECT語(yǔ)句中使用關(guān)鍵字DISTINCT去掉結(jié)果表中的重復(fù)行【例5.5】查詢選修了課程的學(xué)生學(xué)號(hào)(去掉重復(fù)行)。無(wú)條件查詢

SELECTDISTINCTsnoFROMscore條件查詢需要使用WHERE子句指定查詢條件。條件表達(dá)式中通常要使用運(yùn)算符進(jìn)行運(yùn)算，常用的運(yùn)算符如表所示。條件查詢條件查詢—比較運(yùn)算符條件表達(dá)式的形式如下：

<屬性列>比較運(yùn)算符{列名|常量|表達(dá)式}

其中：字符串常量和日期常量要用一對(duì)單引號(hào)括起來(lái)。

【例5.6】求學(xué)分大于等于50的學(xué)生姓名和學(xué)分。SELECTsname,totalcreditFROMstudentWHEREtotalcredit>50;條件查詢—范圍運(yùn)算符BETWEENAND的一般格式為：

<屬性列>[NOT]BETWEEN<a>AND<b>

查詢屬性值在指定范圍內(nèi)的元組其中：<a>為范圍的下限（低值），<b>為范圍的上限（高值）。

選出的元組包括邊界<a>與<b>,要求a<=b，等價(jià)于a=<屬性列<=b。條件查詢—范圍運(yùn)算符【例5.8】求學(xué)分在40與49之間（包括40和49）的學(xué)生學(xué)號(hào)和學(xué)分。【例5.9】求學(xué)分不在40與49之間的學(xué)生學(xué)號(hào)和學(xué)分。 SELECTsno,totalcreditFROMstudentWHEREtotalcreditBETWEEN40AND49;SELECTsno,totalcreditFROMstudentWHEREtotalcreditNOTBETWEEN40AND49;條件查詢—集合運(yùn)算符謂詞IN可以用來(lái)查找屬性值屬于指定集合的元組。

<屬性列>[NOT]IN<值表>

謂詞IN實(shí)際上是一系列謂詞‘OR’的縮寫。所起的作用就是檢查列值是否等于它后面括弧內(nèi)的一組值中某一個(gè)。如果等于其中某一個(gè)值，則其結(jié)果為‘真’，否則其結(jié)果為‘假’。

NOTIN表示與IN完全相反的含義。條件查詢—集合運(yùn)算符【例5.10】查詢考試成績(jī)?yōu)?0、85或90的學(xué)生學(xué)號(hào)和成績(jī)?！纠?.11】查詢不是通信工程系、計(jì)算機(jī)系、網(wǎng)絡(luò)工程系的學(xué)生的信息。 SELECT*FROMscoreWHEREgradeIN(80,85,90);SELECT*FROMstudentWHEREdeptNOTIN('計(jì)算機(jī)','通信工程','網(wǎng)絡(luò)工程');條件查詢—模糊查詢運(yùn)算符謂詞LIKE的一般格式為：

<屬性列>[NOT]LIKE‘<匹配串>’

查詢指定的屬性列與<匹配串>相匹配的元組。<匹配串>可以是一個(gè)完整的字符串，也可以含有通配符的字符串。通配符包括：

%（百分號(hào)）：代表任意長(zhǎng)（長(zhǎng)度為0）字符串。

_（下劃線）：代表任意單個(gè)字符。條件查詢—模糊查詢運(yùn)算符【例5.12】查詢姓名是以漢字“林”打頭的學(xué)生信息【例5.13】查詢姓名中含有“林”的學(xué)生信息。【例5.14】查詢姓名長(zhǎng)度至少是兩個(gè)字符且倒數(shù)第二個(gè)字符必須是漢字“林”的學(xué)生信息。

SELECT*FROMstudentWHEREsnameLIKE'林%';SELECT*FROMstudentWHEREsnameLIKE'%林%';SELECT*FROMstudentWHEREsnameLIKE'%林_';條件查詢—模糊查詢運(yùn)算符

使用換碼字符'＼'將通配符轉(zhuǎn)義為普通字符

【例5.15】在course表中插入新記錄，查詢以"DB_"開頭的課程的詳細(xì)情況。

SELECT*FROMcourseWHEREcnameLIKE'DB\_%';INSERTINTOcourse(cno,cname)VALUES('401','DBDESIGN'),('402','DB_DESIGN');條件查詢—空值運(yùn)算符NULL表示空值?？罩凳且环N不存在的或者不知道、不可用的數(shù)據(jù)。格式：列名

IS[NOT]

NULL這里的IS不能用=替代。

數(shù)據(jù)庫(kù)表的行中，未被賦值的字段自動(dòng)被認(rèn)為是空值。0長(zhǎng)度的字符串自動(dòng)解釋為空值?？罩挡荒軈⒓尤魏斡?jì)算，如要計(jì)算用空值轉(zhuǎn)換函數(shù)：COALESCE()可以接受多個(gè)參數(shù)，返回第一個(gè)非NULL參數(shù)的值。（任何數(shù)據(jù)庫(kù)中都可以使用）IFNULL(expr1，expr2)。若expr1非空，返回expr1；若expr1為NULL，返回expr2。（MySQL使用）條件查詢—空值運(yùn)算符【例5.16】查詢course表中學(xué)分為空的記錄?！纠?.17】

將course表中學(xué)分為空的記錄的學(xué)分加10SELECT*FROMcourseWHEREcreditISNULL;（1）UPDATEcourseSETcredit=COALESCE(credit,0)+10WHEREcreditISNULL;（2）UPDATEcourseSETcredit=IFNULL(credit,0)+10WHEREcreditISNULL;條件查詢—多重條件組合查詢當(dāng)查詢條件涉及到多個(gè)時(shí)，可將若干條件通過邏輯運(yùn)算符構(gòu)成一個(gè)更復(fù)雜的條件進(jìn)行查詢。

運(yùn)算符的優(yōu)先順序如下：條件查詢—多重條件組合查詢【例5.18】查詢計(jì)算機(jī)系或通信工程系，學(xué)分大于50的學(xué)生姓名、系和學(xué)分?！纠?.19】查詢選修課程(‘101’)或課程(‘102’)，成績(jī)?cè)?5和95之間的學(xué)生的學(xué)號(hào)、課程與成績(jī)。SELECTsname,dept,totalcreditFROMstudentWHEREdeptIN('計(jì)算機(jī)','通信工程')ANDtotalcredit>50;SELECTsno,cno,gradeFROMscoreWHEREcnoIN('101','102')ANDgradeBETWEEN85AND95;組函數(shù)SQL提供了組函數(shù)（聚合函數(shù)），用于對(duì)一組值執(zhí)行統(tǒng)計(jì)組函數(shù)【例5.20】查詢學(xué)生總?cè)藬?shù)?！纠?.21】查詢選修了課程的學(xué)生人數(shù)?！纠?.22】查詢計(jì)算機(jī)系學(xué)生的平均學(xué)分?！纠?.23】查詢選修了課程號(hào)為101的課程的學(xué)生的最高、最低與平均成績(jī)。SELECTCOUNT(*)AS總?cè)藬?shù)FROMstudent;SELECTCOUNT(DISTINCTsno)FROMscore;SELECTAVG(totalcredit)FROMstudentWHEREdept='計(jì)算機(jī)';SELECTMAX(grade),MIN(grade),AVG(grade)FROMscoreWHEREcno='101';分組查詢分組查詢的語(yǔ)法格式為：GROUPBY<用于分組的列名>[HAVING<條件表達(dá)式>]將查詢結(jié)果表按某一列或多列值分組，值相等的為一組；當(dāng)對(duì)多列進(jìn)行分組時(shí)，所有的組函數(shù)統(tǒng)計(jì)都是對(duì)最后的分組列進(jìn)行的；分組查詢【例5.24】查詢各門課程的平均成績(jī)與總成績(jī)?！纠?.25】查詢各系的人數(shù)。SELECTcno,AVG(grade),SUM(grade)FROMscoreGROUPBYcno;SELECTdept,COUNT(*)人數(shù)FROMstudentGROUPBYdept;分組查詢-HAVING子句如果分組后還要按一定的條件對(duì)這些分組進(jìn)行篩選，只輸出滿足條件的組，則應(yīng)該使用HAVING短語(yǔ)指定篩選條件。【例5.26】查詢?nèi)藬?shù)在10人以上的系及人數(shù)。SELECTdept,COUNT(*)人數(shù)FROMstudentGROUPBYdeptHAVING人數(shù)>10;分組查詢-HAVING子句WHERE與HAVING的區(qū)別：WHERE作用于基本表或視圖，從中選擇滿足條件的元組；HAVING短語(yǔ)作用于組，從中選擇滿足條件的組。【例5.27】查詢女生人數(shù)小于等于3的系和女生數(shù)。SELECTdept,COUNT(*)AS女生數(shù)FROMstudentWHEREsex='女'GROUPBYdeptHAVING女生數(shù)<=3;注意：在包含GROUPBY子句的查詢語(yǔ)句中，SELECT子句后面的所有字段列表（除組函數(shù)外），均應(yīng)該包含在GROUPBY子句中，即選項(xiàng)與分組應(yīng)具有一致性。排序查詢排序的語(yǔ)法格式為：

ORDERBY<用于排序的列名>[ASC|DESC]，…ASC升序排序（默認(rèn)值），DESC降序排序；可以選擇多列進(jìn)行排序，當(dāng)有多列時(shí)，所有記錄按第一列順序排列，當(dāng)?shù)谝涣械闹迪嗟葧r(shí)，再按第二列的順列，以此類推；如果排序列含有空值時(shí)，ASC排序時(shí)，含有空值的行位于最前面?！纠?.28】查詢選修課程的學(xué)生的學(xué)號(hào)、課程號(hào)與成績(jī)，結(jié)果按學(xué)號(hào)升序、課程號(hào)降序排序。SELECTsno,cno,gradeFROMscoreORDERBYsno,cnoDESC;排序查詢1.限制返回結(jié)果的記錄數(shù)

對(duì)于一次性查詢出的大量記錄，不僅不便于閱讀查看，還會(huì)浪費(fèi)系統(tǒng)效率。為此，MySQL中提供了一個(gè)關(guān)鍵字LIMIT，可以限定查詢結(jié)果的數(shù)量，也可以指定查詢從哪一條記錄開始?；菊Z(yǔ)法格式如下： LIMIT[OFFSET,]記錄數(shù)； LIMIT記錄數(shù)[OFFSET<OFFSET>]；LIMIT

記錄數(shù)；表示從第1條記錄開始返回限定的最大記錄數(shù)量；LIMIT[OFFSET,]記錄數(shù)；可選項(xiàng)OFFSET表示偏移量，用于設(shè)置從哪條記錄開始，默認(rèn)第1條記錄的偏移量值為0，第2條記錄的偏移量值為1，以此類推。LIMIT記錄數(shù)[OFFSET<OFFSET>]；OFFEST<OFFSET>表示略過<OFFSET>條記錄，從<OFFSET>+1條記錄開始獲取要顯示的記錄數(shù)。限制查詢結(jié)果數(shù)量【例5.29】查詢課程號(hào)為101的課程的成績(jī)排前3名的學(xué)生的學(xué)號(hào)、課程號(hào)、成績(jī)。SELECTsno,cno,gradeFROMscoreWHEREcno='101'ORDERBYgradeDESCLIMIT3;限制查詢結(jié)果數(shù)量【例5.30】查詢101課程成績(jī)排列在5～10名的學(xué)生的學(xué)號(hào)、課程號(hào)、成績(jī)。--格式1SELECTsno,cno,gradeFROMscoreWHEREcno=101ORDERBYgradeDESCLIMIT4,6;--格式2SELECT*FROMscoreLIMIT6OFFSET4;2.分頁(yè)顯示使用limit實(shí)現(xiàn)數(shù)據(jù)的分頁(yè)顯示，每頁(yè)顯示pageSize條記錄，此時(shí)顯示第pageNo頁(yè)的公式為：LIMIT(pageNo-1)*pageSize,pageSize;(pageNo-1)*pageSize：從哪條記錄開始顯示；

pageSize：每頁(yè)顯示的行數(shù)。限制查詢結(jié)果數(shù)量【例5.31】分頁(yè)顯示student表的所有記錄，每頁(yè)顯示10條