第三章SQL數(shù)據(jù)查詢單表查詢

數(shù)據(jù)庫系統(tǒng)概論Friday,

July

10,

2020

1第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL主要內(nèi)容Friday,

July

10,

2020

23.1

SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)更新視圖3.4數(shù)據(jù)查詢語句格式SELECT

[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[，<目標(biāo)列表達(dá)式>]…

FROM

<表名或視圖名>[，<表名或視圖名>]…[WHERE

<條件表達(dá)式>][GROUP

BY

<列名1>[HAVING

<條件表達(dá)式>]]

[ORDER

BY

<列名2>[ASC|DESC]]；分類：單表查詢連接查詢嵌套查詢集合查詢Friday,

July

10,

2020

33.4數(shù)據(jù)查詢Friday,

July

10,

20204單表查詢查詢僅涉及一個(gè)表，是一種最簡單的查詢操作一、選擇表中的若干列二、選擇表中的若干元組三、對查詢結(jié)果排序四、使用聚集函數(shù)五、對查詢結(jié)果分組一、選擇表中的若干列

1.查詢指定列[例1]查詢?nèi)w學(xué)生的學(xué)號與姓名。SELECT

Sno,SnameFROM

Student;[例2]查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系。SELECT

Sname

,

Sno

,

SdeptFROM

Student;列的先后順序可以與表中定義的順序不一致Friday,

July

10,

202052.查詢?nèi)苛衃例3]

查詢?nèi)w學(xué)生的詳細(xì)記錄。SELECT

Sno

,

Sname

,

Ssex

,

Sage

,

SdeptFROM

Student

;或SELECT

*列的先后順序與表中Friday,

July

10,

20206FROM

Student

定;義的順序一致3.查詢經(jīng)過計(jì)算的值Friday,

July

10,

20207SELECT子句的<目標(biāo)列表達(dá)式>為表達(dá)式算術(shù)表達(dá)式字符串常量函數(shù)列別名等3.查詢經(jīng)過計(jì)算的值[例4] 查全體學(xué)生的姓名及其出生年份。SELECT

Sname

,

2010-SageFROM

Student

;輸出結(jié)果：經(jīng)過計(jì)算的列無列名Friday,

July

10,

20208[例5]

查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECT

Sname

,

"Year

of

Birth:

"

,

2010-Sage

,

LOWER(SdeFROM

Student

;輸出結(jié)果：Friday,

July

10,

20209使用列別名改變查詢結(jié)果的列標(biāo)題SELECT

Sname

NAME

,

"Year

of

Birth:

"

BIRTH

,2010-Sage

BIRTHDAY

,

LOWER(Sdept)DEPARTMENTFROM

Student

;輸出結(jié)果：Friday,

July

10,

2020

10二、選擇表中的若干元組

1.消除取值重復(fù)的行ALL

與DISTINCT[例6]

查詢選修了課程的學(xué)生學(xué)號。(1)

SELECT

SnoFROM

SC;或(默認(rèn)ALL)SELECT

ALL

SnoFROM

SC;輸出結(jié)果：Friday,

July

10,

2020

11使用DISTINCT使得重復(fù)行只出現(xiàn)一次SELECT

DISTINCT

SnoFROM

SC;輸出結(jié)果：說明：即便查詢結(jié)果包括多列，DISTINCT都要置于SELECT和第一列之間。例如：SELECT

DISTINCT

Cno,GradeFROM

SC;Friday,

July

10,

2020

122.查詢滿足條件的元組通過WHERE子句可以實(shí)現(xiàn)查詢滿足指定條件的元組。Friday,

July

10,

202013(1)比較大小Friday,

July

10,

202014在WHERE子句的<條件表達(dá)式>中使用比較運(yùn)算符?

=，>，<，>=，<=，!=或<>，!>，!<，[例7]查詢計(jì)算機(jī)科學(xué)系全體學(xué)生的名單。SELECT

SnameFROM

StudentWHERE

Sdept="CS";[例8]查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECT

Sname,SageFROM

StudentWHERE

Sage

<

20;(2)確定范圍Friday,

July

10,

2020

15使用謂詞BETWEEN…AND…NOT

BETWEEN

…

AND

…[例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。SELECT

Sname,Sdept,SageFROM

StudentWHERE

Sage

BETWEEN

20

AND

23;[例11]查詢年齡不在20~23歲之間的學(xué)生的姓名、系別和年齡。SELECT

Sname,Sdept,SageFROM

StudentWHERE

Sage

NOT

BETWEEN

20

AND

23;(3)確定集合Friday,

July

10,

2020

16使用謂詞IN<值表>NOT

IN<值表>說明：<值表>是用逗號分隔的一組取值[例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECT

Sname,SsexFROM

StudentWHERE

Sdept

IN

(

"IS","MA","CS"

);[例13]查詢既不是信息系、數(shù)學(xué)系，也不是計(jì)算機(jī)科學(xué)系Friday,

July

10,

2020

17的學(xué)生的姓名和性別。SELECT

Sname,SsexFROM

StudentWHERE

Sdept

NOT

IN

(

"IS","MA","CS"

);(4)字符串匹配Friday,

July

10,

2020

18[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]<匹配串>：指定匹配模板匹配模板：固定字符串或含通配符的字符串說明：當(dāng)匹配模板為固定字符串時(shí)，可以用=運(yùn)算符取代LIKE謂詞，用!=或<>運(yùn)算符取代NOT

LIKE謂詞。通配符%(百分號)代表任意長度（長度可以為0）的字符串例：a%b表示以a開頭，以b結(jié)尾的任意長度的字符串。如acb，addgb，ab等都滿足該匹配串。不包括空字符一個(gè)字母或漢字_(下橫線)代表任意單個(gè)字符例：a_b表示以a開頭，以b結(jié)尾的長度為3的任意字符串。如acb，afb等都滿足該匹配串。Friday,

July

10,

202019匹配模板為固定字符串[例14]查詢學(xué)號為200215121的學(xué)生的詳細(xì)情況。SELECT

*FROM

StudentWHERE

Sno

LIKE

"200215121";等價(jià)于：SELECT

*FROM

StudentWHERE

Sno

=

"200215121";Friday,

July

10,

202020匹配模板為含通配符的字符串[例15]查詢所有姓劉的學(xué)生的姓名、學(xué)號和性別。SELECT

Sname,Sno,SsexFROM

StudentWHERE

rtrim(Sname)LIKE"劉%";[例16]查詢姓劉的且全名為3個(gè)漢字的學(xué)生的姓名。SELECT

SnameFROM

StudentWHERE

rtrim(Sname)

LIKE

"劉

";討論：rtrim的作用Friday,

July

10,

202021[例17]查詢名字中第2個(gè)字為"陽"字的學(xué)生的姓名和學(xué)號。SELECT

Sname,SnoFROM

StudentWHERE

Sname

LIKE

"_陽%";[例18]查詢所有不姓劉的學(xué)生姓名。SELECT

Sname,Sno,SsexFROM

StudentWHERE

Sname

NOT

LIKE"劉%";Friday,

July

10,

202022ESCAPE短語：Friday,

July

10,

202023當(dāng)用戶要查詢的字符串本身就含有%或_時(shí)，要使用

ESCAPE"<換碼字符>"短語對通配符進(jìn)行轉(zhuǎn)義。[例19]查詢DB_Design課程的課程號和學(xué)分。SELECT

Cno,CcreditFROM

CourseWHERE

Cname

LIKE

"DB\_Design"

ESCAPE

"\";說明：ESCAPE

‘＼’表示“＼”為換碼字符，將緊隨其后的通配符轉(zhuǎn)義為普通的字符。[例20]查詢以"DB_"開頭，且倒數(shù)第3個(gè)字符為i的課程的詳細(xì)情況。SELECT

*FROM

CourseWHERE

Cname

LIKE

"DB\

%i

"

ESCAPE

"\";說明：更多通配符參加聯(lián)機(jī)叢書Friday,

July

10,

202024(5)涉及空值的查詢Friday,

July

10,

2020

25使用謂詞IS

NULL或IS

NOT

NULL[例21]某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。SELECT

Sno,CnoFROM

SCWHERE

Grade

IS

NULL;[例22]查所有有成績的學(xué)生學(xué)號和課程號。SELECT

Sno，CnoFROM

SCWHERE

Grade

IS

NOT

NULL；當(dāng)ANSI_NULLS為ON時(shí)(默認(rèn)值)，則所有對空值的比較均取值為UNKNOWN，因此：IS

NULL不能用=NULL代替IS

NOT

NULL不能用<>NULL代替當(dāng)ANSI_NULLS為OFF時(shí)：IS

NULL能用=NULL代替IS

NOT

NULL能用<>NULL代替Friday,

July

10,

2020

26在SC表中新增一條選課記錄，Sno為‘200215123’，

Cno為‘3’，Grade字段為空值，執(zhí)行下面的SQL語句，然后將OFF改為ON并執(zhí)行，比較兩次結(jié)果。SET

ANSI_NULLS

OFFSELECT

*Friday,

July

10,

2020

27FROMWHERESCGrade=null;SELECT

*FROMWHERESCGrade<>null;執(zhí)行下面的SQL語句，然后將OFF改為ON并執(zhí)行，比較兩次結(jié)果。SET

ANSI_NULLS

OFFSELECT

*FROMWHERESCGrade<>92SELECT

*FROM

SCWHERE

NOT

Grade

Between

85

and

100;思考：這些否定查詢的結(jié)果中會(huì)包括NULL值嗎？Friday,

July

10,

2020

28(6)多重條件查詢Friday,

July

10,

2020

29用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個(gè)查詢條件AND的優(yōu)先級高于OR可以用括號改變優(yōu)先級邏輯運(yùn)算符NOT[NOT]

IN[NOT]

BETWEEN

…[NOT]

LIKENOT+比較運(yùn)算符AND

…[例23]查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。SELECT

SnameFROM

StudentWHERE

Sdept=

"CS"

AND

Sage<20

;Friday,

July

10,

2020

30[例12]查詢信息系（IS）、數(shù)學(xué)系（MA）和計(jì)算機(jī)科學(xué)系（CS）學(xué)生的姓名和性別。SELECT

Sname，SsexFROM

StudentWHERE

Sdept

IN

(

"IS"，"MA"，"CS"

)

;可改寫為：SELECT

Sname，SsexFROM

StudentWHERE

Sdept=

"

IS

"

OR

Sdept=

"

MA"

OR

Sdept=

"

CS

"

;Friday,

July

10,

2020

31[例10]查詢年齡在20~23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。SELECT

Sname，Sdept，SageFriday,

July

10,

2020

32FROMWHEREStudentSage

BETWEEN

20

AND

23；可改寫為：SELECT

Sname，Sdept，SageFROM

StudentWHERE

Sage>=20

AND

Sage<=23；三、對查詢結(jié)果排序Friday,

July

10,

2020

33使用ORDER

BY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時(shí)將如何處理？[例24]查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分?jǐn)?shù)降序排列。SELECT

Sno,GradeFROMWHERESCCno=

"3"ORDER

BY

Grade

DESC;查詢結(jié)果:分析：如何處理空值Friday,

July

10,

202034[例25]查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。SELECT

*FROM

StudentORDER

BY

Sdept，Sage

DESC；Friday,

July

10,

202035四、使用集函數(shù)Friday,

July

10,

2020

36主要聚集函數(shù)計(jì)數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計(jì)算總和：SUM（[DISTINCT|ALL]<列名>）計(jì)算平均值：AVG（[DISTINCT|ALL]<列名>）求最大值：MAX（[DISTINCT|ALL]<列名>）求最小值：MIN（[DISTINCT|ALL]<列名>）說明：DISTINCT短語：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語：不取消重復(fù)值，為缺省值[例26]查詢學(xué)生總?cè)藬?shù)。SELECT

COUNT(*)FROM

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

COUNT(DISTINCT

Sno)FROM

SC；注：用DISTINCT以避免重復(fù)計(jì)算學(xué)生人數(shù)思考：查詢結(jié)果有幾行？為什么？Friday,

July

10,

2020

37COUNT與空值：Friday,

July

10,

202038NumName陳明50張力70董慶基于T1分析下面的查詢的結(jié)果：1)Select

count(*)From

T1結(jié)果：32)Select

count(num)From

T1結(jié)果：2T1[例28]計(jì)算1號課程的學(xué)生平均成績。SELECT

AVG(Grade)Friday,

July

10,

202039FROM

SCWHERE

Cno=

"

1

"；[例29]查詢選修1號課程的學(xué)生最高分?jǐn)?shù)。SELECT

MAX(Grade)FROM

SCWHER

Cno=

"

1

"；說明：除了COUNT(*)，聚集函數(shù)忽略空值。查詢選修3號課程的最低分?jǐn)?shù)、最高分?jǐn)?shù)和平均分?jǐn)?shù)。SELECT

min(Grade),MAX(Grade),AVG(Grade)FROM

SCWHERE

Cno="3";Friday,

July

10,

202040下例是錯(cuò)誤的：SELECTFROMsno，MIN(grade)sc說明：聚集查詢反映的是集合特性在查詢語句中，當(dāng)使用聚集函數(shù)后，在沒有分組的情況下不能直接使用表中字段。Friday,

July

10,

2020

41五、對查詢結(jié)果分組Friday,

July

10,

2020

42使用GROUP

BY子句分組細(xì)化聚集函數(shù)的作用對象未對查詢結(jié)果分組，聚集函數(shù)將作用于整個(gè)查詢結(jié)果對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個(gè)組GROUP

BY子句:SELECTFROMSdeptStudentGROUP

BY

SdeptFriday,

July

10,

2020

43SnoSdept…200215121CS…......對于P82

Student表的實(shí)例而言，涉及‘CS’、‘MA’、‘IS’三個(gè)系SnoSdept……CS…...SnoSdept……IS…...SnoSdept……MA…...Friday,

July

10,

202044執(zhí)行結(jié)果：觀察：查詢結(jié)果有幾行？為什么？Friday,

July

10,

202045GROUP

BY與聚集函數(shù)：Friday,

July

10,

2020

46[例31]求各個(gè)課程號及相應(yīng)的選課人數(shù)。SELECT

Cno,COUNT(Sno)FROM

SCGROUP

BY

Cno;說明:集合查詢中出現(xiàn)的屬性（列）必須符合以下兩種條件之一：對列使用了聚集函數(shù)；列出現(xiàn)在GROUP

BY子句中GROUP

BY與WHERE：SELECT

sdept,COUNT(*)FROM

StudentWHERE

ssex="女"

GROUP

BY

sdept;二者關(guān)系：先執(zhí)行WHERE，再執(zhí)行GROUP

BY。分析：對于P82

Student表的實(shí)例而言，查詢結(jié)果有幾行？聚集查詢Fr結(jié)ida果y,July

10,2020

47GROUP

BY與ORDER

BY：SELECT

sdept,COUNT(*)FROM

StudentWHERE

ssex="女"

GROUP

BY

sdeptORDER

BY

sdept

DESC;說明：排序字段是某一集合特性聚集查詢Fr結(jié)ida果y,July

10,2020

48GROUP

BY與空值：假設(shè)有庫存表以及下列一組數(shù)據(jù)：結(jié)果：FROM

庫存表GROUP

BY品名Friday,

July

10,

202049執(zhí)行下面的查詢：SELECT品名,sum(數(shù)量)結(jié)論：分組列為空值的記錄構(gòu)成一個(gè)分組次級分組：可以有多個(gè)分組字段假設(shè)有庫存表以及下列一組數(shù)據(jù)：SELECT品名,顏色,sum(數(shù)量)AS