第三章SQL4數(shù)據(jù)查詢 嵌套查詢

數(shù)據(jù)庫系統(tǒng)概論2018年9月27日1第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL主要內(nèi)容2018年9月27日2SQL概述學(xué)生-課程數(shù)據(jù)庫數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)更新視圖3.4數(shù)據(jù)查詢2018年9月27日3?語句格式SELECT

[ALL|DISTINCT]

<目標(biāo)列表達式>[，<目標(biāo)列表達式>]…FROM[

WHERE<表名或視圖名>[，<表名或視圖名>]…<條件表達式>][

GROUP

BY

<

列名1>

[

HAVING

<

條件表達式>

]

][

ORDER

BY

<

列名2>

[

ASC|DESC

]

]

；分類：單表查詢連接查詢嵌套查詢集合查詢3.4數(shù)據(jù)查詢3.4.3嵌套查詢2018年9月27日4?嵌套查詢概述?一個SELECT-FROM-WHERE語句稱為一個查詢塊?

將一個查詢塊嵌套在另一個查詢塊的WHERE

子句或HAVING

短語的條件中的查詢稱為嵌套查詢引例2018年9月27日5SELECT

Sname外層查詢/父查詢FROM

StudentWHERE

Sno

IN（SELECT

Sno內(nèi)層查詢/子查詢FROM

SCWHERE

Cno=

"

2

"

）；?子查詢的限制2018年9月27日6?

不能使用ORDER

BY

子句?

層層嵌套方式反映了

SQL

語言的結(jié)構(gòu)化?有些嵌套查詢可以用連接運算替代嵌套查詢分類2018年9月27日7?不相關(guān)子查詢子查詢的查詢條件不依賴于父查詢?相關(guān)子查詢子查詢的查詢條件依賴于父查詢引出子查詢的謂詞2018年9月27日8?

帶有IN謂詞的子查詢?

帶有比較運算符的子查詢?

帶有ANY或ALL謂詞的子查詢?

帶有EXISTS謂詞的子查詢一、帶有IN謂詞的子查詢2018年9月27日9[例39]查詢與“劉晨”在同一個系學(xué)習(xí)的學(xué)生。此查詢要求可以分步來完成①確定“劉晨”所在系名

SELECT

SdeptFROM

StudentWHERE

Sname="

劉晨"；結(jié)果為[例40]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號和姓2018年9月27日13名SELECT

Sno

，Sname③最后在Student關(guān)系中取出Sno和SnameFROM

StudentWHERE

Sno

IN(SELECT

Sno②然后在SC關(guān)系中找出選修了3號課程的學(xué)生學(xué)號FROM

SCWHERE

Cno

IN(SELECT

Cno①首先在Course關(guān)系中找出“信FROM

Course息系統(tǒng)”的課程號，結(jié)果為3號WHERE

Cname=

‘信息系統(tǒng)"

));?用連接查詢2018年9月27日14SELECT

Student

.Sno,SnameFROM

Student,SC,CourseWHERE

Student.Sno

=

SC.Sno

ANDSC.Cno=Course.Cno

ANDCourse.Cname="

信息系統(tǒng)"；二、帶有比較運算符的子查詢2018年9月27日15?當(dāng)理論上內(nèi)層查詢返回單值時，可用比較運算符（>

，<

，=

，>=

，<=

，!=或<

>

）。?

與ANY

或ALL

謂詞配合使用例：假設(shè)一個學(xué)生只可能在一個系學(xué)習(xí)，并且必須屬于一個系，則在[例39]可以用

=

代替IN

：SELECT

Sno,Sname,SdeptFROM

StudentWHERE

Sdept

=(SELECT

SdeptFROM

Student2018年9月27日16WHERE

Sname=

"劉晨")；劉晨"

)

=

Sdept

；2018年9月27日17子查詢一定要跟在比較符之后錯誤的例子：SELECT

Sno

，Sname

，SdeptFROM

StudentWHERE

(

SELECT

SdeptFROM

StudentWHERE

Sname=

‘(SQL

Server

2005

支持)三、帶有ANY(SOME)或ALL謂詞的子查詢2018年9月27日18謂詞語義：任意一個值?

ANY/SOME?ALL：所有值需要配合使用比較運算符2018年9月27日19ANYALL<

ANY<

ALL>=

ANY>=

ALL<=

ANY<=

ALL=

ANY=ALL大于子查詢結(jié)果中的某個值大于子查詢結(jié)果中的所有值小于子查詢結(jié)果中的某個值小于子查詢結(jié)果中的所有值大于等于子查詢結(jié)果中的某個值大于等于子查詢結(jié)果中的所有值小于等于子查詢結(jié)果中的某個值小于等于子查詢結(jié)果中的所有值等于子查詢結(jié)果中的某個值等于子查詢結(jié)果中的所有值（通常沒有實際意義）!=（或<>

）ANY!=（或<>

）ALL不等于子查詢結(jié)果中的某個值（通常沒有實際意義）不等于子查詢結(jié)果中的任何一個值[例42]

查詢其他系中比計算機系任意一個(其中某一個)學(xué)生年2018年9月27日20齡小的學(xué)生姓名和年齡SELECTFROMSname,

SageStudentWHERE

Sdept

<>

"CS"AND

Sage

<

ANY(SELECTSageFROM

StudentWHERE

Sdept=

"CS")執(zhí)行過程?1.DBMS

執(zhí)行此查詢時，首先處理子查詢，找出CS系中所有學(xué)生的年齡，構(gòu)成一個集合(20，19)?2.

處理父查詢，找所有不是CS

系且年齡小于20

或19

的學(xué)生用集函數(shù)實現(xiàn)[例42]SELECT

Sname,

Sage2018年9月27日21FROM

WHEREStudentSage

<(SELECT

MAX(Sage)FROM

StudentWHERE

Sdept=

"CS")AND

Sdept

<>

"CS";[例43]

查詢其他系中比計算機系所有學(xué)生年齡都小

的學(xué)生姓名及年齡。方法一：用ALL謂詞SELECT

Sname,SageFROM

StudentWHERE

Sage

<

ALL(SELECT

SageFROM

StudentWHERE

Sdept=

"CS")AND

Sdept

<>

"CS"2018年9月27日22方法二：用集函數(shù)SELECT

Sname,SageFROM

StudentWHERE

Sage

<

(SELECT2018年9月27日23MIN

(Sage)FROM

StudentWHERE

Sdept=

"IS")AND

Sdept

<>"CS";?

ANY 和ALL謂詞有時可以用集函數(shù)實現(xiàn)?

ANY 與ALL與集函數(shù)的對應(yīng)關(guān)系2018年9月27日24=INANY<>或!=--<<MAX<=<=MAX>>MIN>=>=

MIN>=

MAXALL--NOT

IN<MIN<=

MIN>MAX?

用集函數(shù)實現(xiàn)子查詢通常比直接用ANY

或ALL

查詢效率要高，因為前者通常能夠減少比較次數(shù).找出在1號課程的考試中，成績最高的學(xué)生的學(xué)號和姓名。SELECT

sno,snameFROM

studentWHERE

sno

in(SELECT

snoFROM

scWHERE

cno="01"

and

grade=(

SELECT

max(grade)FROM

scWHERE

cno="01"))2018年9月27日25查詢學(xué)生年齡平均值最大的院系SELECTsdeptFROM

studentGROUP

BY

sdeptHAVING

AVG(sage)>=all(SELECT

avg(sage)FROM

studentGROUP

BY

sdept

)2018年9月27日26四、帶有EXISTS謂詞的子查詢2018年9月27日27EXISTS謂詞NOT

EXISTS

謂詞用EXISTS/NOT

EXISTS實現(xiàn)全稱量詞4.不同形式的查詢間的替換[例44]

查詢所有選修了1號課程的學(xué)生姓名。用嵌套查詢SELECT

SnameFROM

StudentWHERE

EXISTS2018年9月27日28(SELECT

*/*相關(guān)子查詢*/FROM

SCWHERESno=Student.Sno

AND

Cno=

"1")；1.

EXISTS謂詞–帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真值“true”或邏輯假值“false”。若內(nèi)層查詢結(jié)果非空，則返回真值若內(nèi)層查詢結(jié)果為空，則返回假值–由EXISTS引出的子查詢，其目標(biāo)列表達式通常都用*

，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義2.

NOT

EXISTS謂詞2018年9月27日29?相關(guān)子查詢求解過程?首先取外層查詢中表的第一個元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值處理內(nèi)層查詢，然后根據(jù)內(nèi)層查詢結(jié)果集是否空決定WHERE

子句的取值，若WHERE

子句為真，則取外層表的此元組放入結(jié)果表；?然后再取外層表的下一個元組；?重復(fù)這一過程，直至外層表全部檢查完為止。2018年9月27日30思路分析：2018年9月27日31若SC中存在這樣的元組，其Sno值等于此Student.Sno值，并且其Cno=

‘1"

，則取此Student.Sname送入結(jié)果關(guān)系。本查詢涉及Student和SC關(guān)系。在Student中依次取每個元組的Sno值，用此值去檢查SC關(guān)系?！駥W(xué)號

Sno姓名

Sname性別

Ssex年齡

Sage所在系

Sdept200215121李勇男20CS200215122劉晨女19CS200215123王敏女18MA200515125張立男19IS學(xué)號Sno課程號Cno成績Grade200215121192200215121285200215121388200215122290200215122380?

用連接運算SELECT

SnameFROM

Student,

SCWHERE

Student.Sno=SC.Sno

AND

SC.Cno=‘1"

;2018年9月27日32[例45]

查詢沒有選修1

號課程的學(xué)生姓名。SELECT

SnameFROM

Student2018年9月27日33Student.Sno

ANDWHERE

NOT

EXISTS(SELECT

*FROM

SCWHERE

Sno

=Cno=‘1"

)；思考：此例能否用連接查詢實現(xiàn)？［例］找出每個學(xué)生超過他選修課程平均成績的課程號。SELECT

Sno ，

CnoFROM

SC

xWHERE

Grade

>=(SELECT

AVG(Grade)FROM

SC

yWHERE

y.Sno=x.Sno);2018年9月27日34用EXISTS/NOT

EXISTS

實現(xiàn)全稱量詞(難點)2018年9月27日35[例46]查詢選修了全部課程的學(xué)生姓名。

SELECT

SnameFROM

StudentWHERE

NOT

EXISTS(SELECT

*FROM

CourseWHERE

NOT

EXISTS(SELECT

*FROM

SCWHERE

Sno=

Student.SnoAND

Cno=Course.Cno))；

[例47]

查詢至少選修了學(xué)生

200215122選修的全部課程的學(xué)生號碼。用NOT

EXISTS

謂詞表示：SELECT

SnoFROM

StudentWHERE

NOT

EXISTS(SELECT

*FROM

CourseWHERE

cno

in(select

cno

from

scwhere

sno="200215122"

)and

NOT

EXISTS(SELECT

*FROM

SCWHERE

Sno=

Student.SnoAND

Cno=

Course.Cno))2018年9