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

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

第9章查詢(xún)

9.1單表查詢(xún)

9.2連接查詢(xún)

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

9.1

單表查詢(xún)

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

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

FROMstudent

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

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

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

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

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

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

(1)表達(dá)式比較

(2)指定范圍

(3)確定集合

(4)字符匹配

(5)空值比較

(6)多重條件查詢(xún)

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

詢(xún)

條

件謂

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

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

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

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

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

SELECT*FROMstudentWHEREsnoLIKE‘101'等價(jià)于：

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

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

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

SELECTsnameFROMstudentWHEREsnameLIKE'李_'例題（續(xù)）[例17]查詢(xún)名字中第2個(gè)字為"陽(yáng)"字的學(xué)生的姓名和學(xué)號(hào)。

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

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

[例19]查詢(xún)課程名為DB_Design課程的課程號(hào)和任課教師。

SELECTcno,tnoFROMcourseWHEREcnameLIKE'DB\_Design'

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

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

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

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

[例22]查所有有成績(jī)的學(xué)生學(xué)號(hào)和課程號(hào)。

SELECTsno,cnoFROMscoreWHEREdegreeISNOTNULL(6)多重條件查詢(xún)用邏輯運(yùn)算符AND和OR來(lái)聯(lián)結(jié)多個(gè)查詢(xún)條件

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

可以用括號(hào)改變優(yōu)先級(jí)例題[例23]查詢(xún)95033班且在’1975-01-01’后出生的學(xué)生姓名。

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

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

[例24]查詢(xún)選修了3-105號(hào)課程的學(xué)生的學(xué)號(hào)及其成績(jī)，查詢(xún)結(jié)果按分?jǐn)?shù)降序排列。

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

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

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

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

SELECTCOUNT(*)FROMstudent

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

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

SELECTAVG(degree)FROMscoreWHEREcno='3-105'

[例29]查詢(xún)選修3-105號(hào)課程的學(xué)生最高分?jǐn)?shù)。

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

結(jié)果