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

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

列名[AS]列的別名如果別名中有特殊字符、空格、關鍵字等原樣輸出時要用引號;選擇列（投影運算）SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>，···]FROM<表名>例1：查詢?nèi)w學生的姓名、學號、所在系。例2：求全體學生的所有信息。例3：求學生學號和年齡。例4：求學生的學號，姓名和性別，顯示時使用別名“學號”、“姓名”和“性別(男,女)”。SELECTsno,sname,deptFROMstudent;SELECTsno,YEAR(SYSDATE())-YEAR(birthday)FROMstudent;SELECT*FROMstudent;SELECTsno學號,sname姓名,sexAS'性別(男,女)'FROMstudent;無條件查詢消除重復行在SELECT語句中使用關鍵字DISTINCT去掉結(jié)果表中的重復行例：求選修了課程的學生學號(去掉重復行)。SELECTDISTINCTsnoFROMscore無條件查詢<條件表達式>中的運算符如下表所示。SELECT<目標列表達式>

FROM<表名>WHERE<條件表達式>條件查詢條件查詢—比較運算符條件表達式的形式如下：

<屬性列>比較運算符{列名|常量|表達式}

其中：字符串常量和日期常量要用一對單引號括起來。

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

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

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

選出的元組包括邊界<a>與<b>,要求a<=b，等價于a=<屬性列<=b。條件查詢—范圍運算符例1：求學分在40與49之間（包括40和49）的學生學號和學分。例2：求學分不在40與49之間的學生學號和學分。SELECTsno,totalcreditFROMstudentWHEREtotalcreditBETWEEN40AND49;SELECTsno,totalcreditFROMstudentWHEREtotalcreditNOTBETWEEN40AND49;條件查詢—集合運算符謂詞IN可以用來查找屬性值屬于指定集合的元組。

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

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

NOTIN表示與IN完全相反的含義。條件查詢—集合運算符例1：求考試成績?yōu)?0、85或90的學生學號。例2：求不是通信工程系、計算機系、網(wǎng)絡工程系的學生的信息。SELECT*FROMscoreWHEREgradeIN(80,85,90);SELECT*FROMstudentWHEREdeptNOTIN('計算機','通信工程','網(wǎng)絡工程');條件查詢—模糊查詢運算符謂詞LIKE的一般格式為：

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

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

%（百分號）：代表任意長（長度為0）字符串。

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

SELECT*FROMstudentWHEREsnameLIKE'林%';SELECT*FROMstudentWHEREsnameLIKE'%林%';SELECT*FROMstudentWHEREsnameLIKE'%林_';條件查詢—模糊查詢運算符

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

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

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

IS[NOT]

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

數(shù)據(jù)庫表的行中，未被賦值的字段自動被認為是空值。0長度的字符串自動解釋為空值?？罩档馁x值：把連續(xù)兩個單引號賦值給它；把空值常量NULL賦值給它?？罩挡荒軈⒓尤魏斡嬎悖缫嬎阌煤瘮?shù)：IFNULL(expr1，expr2)。

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

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

運算符的優(yōu)先順序如下：條件查詢—多重條件組合查詢例1：求計算機系或通信工程系，學分大于50的學生姓名、系和學分。例2：求選修課程(‘101’)或課程(‘102’)，成績在85和95之間的學生的學號、課程與成績。

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

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

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

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

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