數(shù)據(jù)庫原理與應(yīng)用：第9章 單表查詢

數(shù)據(jù)庫原理與應(yīng)用

第9章查詢

9.1單表查詢

9.2連接查詢

9.3嵌套查詢查詢：從數(shù)據(jù)庫中獲得所需要的數(shù)據(jù)。查詢利用SELECT語句實現(xiàn)。語句格式SELECT[ALL|DISTINCT]<目標列表達式>[,<目標列表達式>]…[INTO新表名]FROM<表名>[，<表名>]…[WHERE<條件表達式>][GROUPBY<列名1>[,<列名2>]][HAVING<條件表達式>][ORDERBY<列名3>[ASC|DESC][,<列名4>[ASC|DESC]]]SELECT子句：指定要顯示的屬性列INTO子句：將查詢到的結(jié)果集形成一個新表FROM子句：指定查詢對象(表)WHERE子句：指定查詢條件GROUPBY子句：對查詢結(jié)果按指定列進行分組，該屬性列值相等的元組為一個組。HAVING子句：篩選出只有滿足指定條件的組ORDERBY子句：對查詢結(jié)果表按指定列值的升序或降序排序主要內(nèi)容數(shù)據(jù)庫的查詢包括：單表查詢連接查詢嵌套查詢school數(shù)據(jù)庫student(sno,sname,ssex,sbirthday,class)teacher(tno,tname,tsex,tbirthday,prof,depart)course(cno,cname,tno)score(sno,cno,degree)示例數(shù)據(jù)庫

9.1

單表查詢

查詢僅涉及一個表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干行三、對查詢結(jié)果排序四、使用集函數(shù)五、對查詢結(jié)果分組六、HAVING子句一、選擇表中的若干列1查詢指定列2查詢?nèi)苛?修改查詢結(jié)果中的列標題4替換查詢結(jié)果中的數(shù)據(jù)5查詢經(jīng)過計算的值1.查詢指定列[例1]查詢?nèi)w學生的學號與姓名。SELECTsno,snameFROMstudent

[例2]查詢?nèi)w學生的姓名、學號、班號。SELECTsname,sno,classFROMstudent2.查詢?nèi)苛衃例3]查詢?nèi)w學生的詳細記錄。SELECTsno，sname，ssex，sage，sdept

FROMstudent

或SELECT*FROMstudent3.修改查詢結(jié)果中的列標題【例4】查詢student表中所有記錄，結(jié)果中各列的標題分別指定為學號、姓名、性別、出生日期、班號。SELECTsnoAS'學號',snameAS'姓名',ssexAS'性別',

sbirthdayAS'出生日期',classAS'班號'FROMstudent'學號'=snosno'學號'4.替換查詢結(jié)果中的數(shù)據(jù)CASEWHEN條件1THEN表達式1WHEN條件2THEN表達式2...ELSE表達式nEND【例5】查詢score表sno,sname,degree列，對degree列按以下規(guī)則進行轉(zhuǎn)換；若degree為90～100，替換為“優(yōu)秀”，若degree為80～

89，替換為“良好”，若degree在70～

79之間，替換為“中等”，若degree為60～

69之間，替換為“及格”，若degree為0～59之間，替換為“不及格”，列標題更改為“evaluation”。SELECTsno,cno,evaluation=CASEWHENdegree>=90ANDdegree<=100THEN'優(yōu)秀'WHENdegree>=80anddegree<=89THEN'良好'WHENdegree>=70anddegree<=79THEN'中等'WHENdegree>=60anddegree<=69THEN'及格'ELSE'不及格'ENDFROMscore5.查詢經(jīng)過計算的值

SELECT子句的<目標列表達式>為表達式算術(shù)表達式字符串常量函數(shù)[例6]查全體學生的姓名及其年齡。SELECTsname,'age:',2000-DATEPART(yy,sbirthday)FROMstudent二、選擇表中的若干行1消除結(jié)果集中的重復(fù)2限制結(jié)果集的返回行數(shù)3查詢滿足條件的行

(1)表達式比較

(2)指定范圍

(3)確定集合

(4)字符匹配

(5)空值比較

(6)多重條件查詢

1.消除結(jié)果集中的重復(fù)行[例7]查詢選修了課程的學生學號。(1)SELECTsnoFROMscore或(默認ALL)SELECTALLsnoFROMscoreALL與DISTINCT(2)SELECTDISTINCTsnoFROMscore2.限制結(jié)果集的返回行數(shù)可使用TOP選項限制查詢結(jié)果集返回的行數(shù)。其基本格式為：TOPn[PERCENT][例8]SELECTTOP4*FROMscoreSELECTTOP40PERCENT*FROMscore3.查詢滿足條件的行WHERE子句常用的查詢條件查

詢

條

件謂

詞比較=，>，<，>=，<=，!=，<>，!>，!<；

確定范圍BETWEEN…AND…，NOTBETWEEN…AND…確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL多重條件AND，OR(1)比較大小在WHERE子句的<比較條件>中使用比較運算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運算符NOT+比較運算符[例9]查詢所有在’1975-01-01’后出生的學生學號及其姓名。

SELECTsno,snameFROMstudentWHEREsbirthday>’1975-01-01’或SELECTsno,snameFROMstudentWHERENOTsbirthday<='1975-01-01'(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例10]查詢成績在60～80（包括60分和80分）之間的所有記錄。SELECT*FROMscoreWHEREdegreeBETWEEN60AND80例題（續(xù)）[例11]查詢成績不在60～80之間的所有記錄。SELECT*FROMscoreWHEREdegreeNOTBETWEEN60AND80(3)確定集合IN<取值表>,NOTIN<取值表>

<取值表>：用逗號分隔的一組取值[例12]查詢成績?yōu)?5、86或88的記錄。SELECT*FROMscoreWHEREdegreeIN(85,86,88)(3)確定集合[例13]查詢成績既不是85、86，也不是88的記錄。SELECT*FROMscoreWHEREdegreeNOTIN(85,86,88)(4)字符串匹配[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：固定字符串或含通配符的字符串當匹配串為固定字符串時：可以用=運算符取代LIKE謂詞用!=或<>運算符取代NOTLIKE謂詞例題1)匹配串為固定字符串[例14]查詢學號為101的學生的詳細情況。

SELECT*FROMstudentWHEREsnoLIKE‘101'等價于：

SELECT*FROMstudentWHEREsno=‘101'通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab

等都滿足該匹配串_(下劃線)代表任意單個字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串例題（續(xù)）2)匹配串為含通配符的字符串[例15]查詢所有姓王學生的姓名、學號和性別。

SELECTsname,sno,ssexFROMstudentWHEREsnameLIKE'王%'例題（續(xù)）[例16]查詢姓“李”且全名為二個漢字的學生的姓名。

SELECTsnameFROMstudentWHEREsnameLIKE'李_'例題（續(xù)）[例17]查詢名字中第2個字為"陽"字的學生的姓名和學號。

SELECTsname,snoFROMstudentWHEREsnameLIKE'_陽%'例題（續(xù)）[例18]查詢所有不姓李的學生姓名。

SELECTsnameFROMstudentWHEREsnameNOTLIKE‘李%'ESCAPE短語：當用戶要查詢的字符串本身就含有%或_時，要使用ESCAPE'<換碼字符>'短語對通配符進行轉(zhuǎn)義。例題（續(xù)）3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

[例19]查詢課程名為DB_Design課程的課程號和任課教師。

SELECTcno,tnoFROMcourseWHEREcnameLIKE'DB\_Design'

ESCAPE'\'例題（續(xù)）使用換碼字符將通配符轉(zhuǎn)義為普通字符(續(xù))[例20]查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細情況。

SELECT*FROMcourseWHEREcnameLIKE'DB\_%i__'ESCAPE'\'(5)涉及空值的查詢

使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替[例21]某些學生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W生的學號和相應(yīng)的課程號。

SELECTsno,CnoFROMscoreWHEREdegreeISNULL例題(續(xù))

[例22]查所有有成績的學生學號和課程號。

SELECTsno,cnoFROMscoreWHEREdegreeISNOTNULL(6)多重條件查詢用邏輯運算符AND和OR來聯(lián)結(jié)多個查詢條件

AND的優(yōu)先級高于OR

可以用括號改變優(yōu)先級例題[例23]查詢95033班且在’1975-01-01’后出生的學生姓名。

SELECTsnameFROMstudentWHEREclass='95033'ANDsbirthday>'1975-01-01'改寫[例10][例10]查詢成績在60～80（包括60分和80分）之間的所有記錄。SELECT*FROMscoreWHEREdegreeBETWEEN60AND80可改寫為：SELECT*FROMscoreWHEREdegree>=60ANDdegree<=80改寫[例12][例12]查詢成績?yōu)?5、86或88的記錄。SELECT*FROMscoreWHEREdegreeIN(85,86,88)可改寫為：SELECT*FROMscoreWHEREdegree=85ORdegree=86ORdegree=88三、對查詢結(jié)果排序

使用ORDERBY子句可以按一個或多個屬性列排序升序：ASC降序：DESC缺省值為升序當排序列含空值時ASC：排序列為空值的元組最先顯示DESC：排序列為空值的元組最后顯示對查詢結(jié)果排序（續(xù)）

[例24]查詢選修了3-105號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)降序排列。

SELECTsno,degreeFROMscoreWHEREcno='3-105'ORDERBYdegreeDESC對查詢結(jié)果排序（續(xù)）

[例25]查詢?nèi)w學生情況，查詢結(jié)果按所在班的班號升序排列，同一班中的學生按出生先后順序排列。

SELECT*FROMstudentORDERBYclass,sbirthday四、使用集函數(shù)5類主要集函數(shù)計數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計算總和SUM（[DISTINCT|ALL]<列名>） 計算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）使用集函數(shù)（續(xù)）求最大值MAX（[DISTINCT|ALL]<列名>）

求最小值MIN（[DISTINCT|ALL]<列名>） DISTINCT短語：在計算時要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值A(chǔ)LL為缺省值使用集函數(shù)（續(xù)）[例26]查詢學生總?cè)藬?shù)。

SELECTCOUNT(*)FROMstudent

[例27]查詢選修了課程的學生人數(shù)。

SELECTCOUNT(DISTINCTsno)FROMscore注：用DISTINCT以避免重復(fù)計算學生人數(shù)使用集函數(shù)（續(xù)）[例28]計算3-105號課程的學生平均成績。

SELECTAVG(degree)FROMscoreWHEREcno='3-105'

[例29]查詢選修3-105號課程的學生最高分數(shù)。

SELECTMAX(degree)FROMscoreWHERcno='3-105'五、對查詢結(jié)果分組使用GROUPBY子句分組 細化集函數(shù)的作用對象未對查詢結(jié)果分組，集函數(shù)將作用于整個查詢結(jié)果對查詢結(jié)果分組后，集函數(shù)將分別作用于每個組使用GROUPBY子句分組[例30]求各個課程號及相應(yīng)的選課人數(shù)。SELECTcno,COUNT(*)'選課人數(shù)'FROMscoreGROUPBYcno

結(jié)果