第5講 單表查詢《數(shù)據(jù)庫原理及應(yīng)用》教學(xué)課件

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

列名[AS]列的別名如果別名中有特殊字符、空格、關(guān)鍵字等原樣輸出時(shí)要用引號(hào);選擇列（投影運(yùn)算）SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>，···]FROM<表名>例1：查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。例2：求全體學(xué)生的所有信息。例3：求學(xué)生學(xué)號(hào)和年齡。例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;無條件查詢消除重復(fù)行在SELECT語句中使用關(guān)鍵字DISTINCT去掉結(jié)果表中的重復(fù)行例：求選修了課程的學(xué)生學(xué)號(hào)(去掉重復(fù)行)。SELECTDISTINCTsnoFROMscore無條件查詢<條件表達(dá)式>中的運(yùn)算符如下表所示。SELECT<目標(biāo)列表達(dá)式>

FROM<表名>WHERE<條件表達(dá)式>條件查詢條件查詢—比較運(yùn)算符條件表達(dá)式的形式如下：

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

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

【例】求學(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)算符例1：求學(xué)分在40與49之間（包括40和49）的學(xué)生學(xué)號(hào)和學(xué)分。例2：求學(xué)分不在40與49之間的學(xué)生學(xué)號(hào)和學(xué)分。SELECTsno,totalcreditFROMstudentWHEREtotalcreditBETWEEN40AND49;SELECTsno,totalcreditFROMstudentWHEREtotalcreditNOTBETWEEN40AND49;條件查詢—集合運(yùn)算符謂詞IN可以用來查找屬性值屬于指定集合的元組。

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

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

NOTIN表示與IN完全相反的含義。條件查詢—集合運(yùn)算符例1：求考試成績?yōu)?0、85或90的學(xué)生學(xué)號(hào)。例2：求不是通信工程系、計(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)）：代表任意長（長度為0）字符串。

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

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

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

例1:在course表中插入新記錄。

例2:查詢以"DB_"開頭的課程的詳細(xì)情況。SELECT*FROMcourseWHEREcnameLIKE'DB\_%';INSERTINTOcourse(cno,cname)VALUES('401','DBDESIGN'),('402','DB_DESIGN');條件查詢—空值運(yùn)算符NULL表示空值。空值是一種不存在的或者不知道、不可用的數(shù)據(jù)。格式：列名

IS[NOT]

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

數(shù)據(jù)庫表的行中，未被賦值的字段自動(dòng)被認(rèn)為是空值。0長度的字符串自動(dòng)解釋為空值?？罩档馁x值：把連續(xù)兩個(gè)單引號(hào)賦值給它；把空值常量NULL賦值給它?？罩挡荒軈⒓尤魏斡?jì)算，如要計(jì)算用函數(shù)：IFNULL(expr1，expr2)。

若expr1非空，返回expr1；若expr1為NULL，返回expr2。條件查詢—空值運(yùn)算符例1：查詢course表中學(xué)分為空的記錄。例2：

將course表中學(xué)分為空的記錄的學(xué)分加10SELECT*FROMcourseWHEREcreditISNULL;注意：在定義表時(shí)可以用default定義一個(gè)默認(rèn)值來替代空值。UPDATEcourseSETcredit=IFNULL(credit,0)+10WHEREcreditISNULL;條件查詢—多重條件組合查詢當(dāng)查詢條件涉及到多個(gè)時(shí)，可將若干條件通過邏輯運(yùn)算符構(gòu)成一個(gè)更復(fù)雜的條件進(jìn)行查詢。

運(yùn)算符的優(yōu)先順序如下：條件查詢—多重條件組合查詢例1：求計(jì)算機(jī)系或通信工程系，學(xué)分大于50的學(xué)生姓名、系和學(xué)分。例2：求選修課程(‘101’)或課程(‘102’)，成績?cè)?5和95之間的學(xué)生的學(xué)號(hào)、課程與成績。

SELECTsname,dept,totalcreditFROMstudentWHEREdeptIN('計(jì)算機(jī)','通信工程')ANDtotalcredit>50;SELECTsno,cno,gradeFROMscoreWHEREcnoIN('101','102')ANDgradeBETWEEN85AND95;組函數(shù)SQL提供了組函數(shù)（聚合函數(shù)），用于對(duì)一組值執(zhí)行統(tǒng)計(jì)組函數(shù)例1：求學(xué)生總?cè)藬?shù)。例2：求選修了課程的學(xué)生人數(shù)。例3：求計(jì)算機(jī)系學(xué)生的平均學(xué)分。例4：求選修了課程‘101’的最高、最低與平均成績。

SELECTCOUNT(*)AS總?cè)藬?shù)FROMstudent;SELECTCOUNT(DISTINCTsno)FROMscore;SELECTAVG(totalcredit)FROMstudentWHEREdept='計(jì)算機(jī)';SELECTMAX(grade),MIN(grade),AVG(grade)FROMscoreWHEREcno='101';分組查詢分組查詢的語法格式為：GROUPBY<用于分組的列名>[HAVING<條件表達(dá)式>]將查詢結(jié)果表按某一列或多列值分組，值相等的為一組；當(dāng)對(duì)多列進(jìn)行分組時(shí)，所有的組函數(shù)統(tǒng)計(jì)都是對(duì)最后的分組列進(jìn)行的；在包含GROUPBY子句的查詢語句中，SELECT子句后面的所有字段列表（除組函數(shù)外），均應(yīng)該包含在GROUPBY子句中，即選項(xiàng)與分組應(yīng)具有一致性。分組查詢例1：求各門課程的平均成績與總成績。

例2：求各專業(yè)的人數(shù)SELECTcno,AVG(grade),SUM(grade)FROMscoreGROUPBYcno;SELECTdept,COUNT(*)FROMstudentGROUPBYdept;分組查詢-HAVING子句如果分組后還要按一定的條件對(duì)這些分組進(jìn)行篩選，只輸出滿足條件的組，則應(yīng)該使用HAVING短語指定篩選條件。例：求人數(shù)在10人以上專業(yè)及人數(shù)SELECTdept,COUNT(*)FROMstudentGROUPBYdeptHAVINGCOUNT(*)>10;分組查詢-HAVING子句WHERE與HAVING的區(qū)別：WHERE作用于基本表或視圖，從中選擇滿足條件的元組；HAVING短語作用于組，從中選擇滿足條件的組。例：求計(jì)算機(jī)專業(yè)的男女生人數(shù)SELECTdept,sex,COUNT(*)FROMstudentWHEREdept='計(jì)算機(jī)'GROUPBYdept,sex;排序查詢排序的語法格式為：

ORDERBY<用于排序的列名>[ASC|DESC]，…ASC升序排序（默認(rèn)值），DESC降序排序；可以選擇多列進(jìn)行排序；如果排序列含有空值時(shí)，ASC排序時(shí)，含有空值的行位于最前面。例：求選修課程(‘101’)或課程(‘102’)學(xué)生的學(xué)號(hào)、課程號(hào)與成績，結(jié)果按學(xué)號(hào)升序、課程號(hào)降序排序。SELECTsno,cno,gradeFROMscoreORDERBYsno,gradeDESC;排序查詢1.限制返回結(jié)果的記錄數(shù)

對(duì)于一次性查詢出的大量記錄，不僅不便于閱讀查看，還會(huì)浪費(fèi)系統(tǒng)效率。為此，MySQL中提供了一個(gè)關(guān)鍵字LIMIT，可以限定查詢結(jié)果的數(shù)量，也可以指定查詢從哪一條記錄開始。基本語法格式如下：LIMIT[OFFSET,]記錄數(shù)；“記錄數(shù)”表示限定獲取的最大記錄數(shù)量；當(dāng)LIMT后僅含有此參數(shù)時(shí)，表示從第1條記錄開始獲取；可選項(xiàng)OFFSET表示偏移量，用于設(shè)置從哪條記錄開始，默認(rèn)第1條記錄的偏移量值為0，第2條記錄的偏移量值為1，依此類推。限制查詢結(jié)果數(shù)量例1：求101課程的成績排前三名學(xué)生的學(xué)號(hào)、課程號(hào)、成績。SELECTsno,cno,gradeFROMscoreWHEREcno='101'ORDERBYgradeDESCLIMIT3;限制查詢結(jié)果數(shù)量例2：求101課程的成績排前6-10名學(xué)生的學(xué)號(hào)、課程號(hào)、成績。SELECTsno,cno,gradeFROMscoreWHEREcno='101'ORDERBYgradeDESCLIMIT5,5;2.分頁顯示使用limit實(shí)現(xiàn)數(shù)據(jù)的分頁顯示，每頁顯示pageSize條記錄，此時(shí)顯示第pageNo頁的公式為：LIMIT(pageNo-1)*pageSize,pageSize;限制查詢結(jié)果數(shù)量例：分頁顯示學(xué)生表的所有記錄，每頁顯示10條記錄。--第1頁顯示的記錄SELECT*FROMstudentLIMIT0,10;--第2頁顯示的記錄SELECT*FROMstudentLIMIT10,10;--第3頁顯示的記錄SELECT*FROMstudentLIMIT20,10;限制查詢結(jié)果數(shù)量導(dǎo)入某省某年高校質(zhì)量工程項(xiàng)目數(shù)據(jù)（excel表）到當(dāng)前數(shù)據(jù)