《數(shù)據(jù)庫(kù)原理及應(yīng)用》課件第5章

第5章數(shù)據(jù)查詢5.1SQL5.2SELECT語(yǔ)句5.3單表查詢5.4連接查詢5.5子查詢

【技能目標(biāo)】

學(xué)會(huì)根據(jù)實(shí)際問(wèn)題的要求，靈活、快速地查詢相關(guān)信息。

【知識(shí)目標(biāo)】

掌握SELECT語(yǔ)句的語(yǔ)法格式；掌握單表查詢技術(shù)；掌握聚合函數(shù)的使用方法；掌握連接查詢技術(shù)；掌握嵌套查詢技術(shù)。

5.1.1SQL概述

SQL的全稱為StructuredQueryLanguage(結(jié)構(gòu)化查詢語(yǔ)言)。SQL是在20世紀(jì)70年代末由IBM公司開發(fā)的一套程序語(yǔ)言。由于其功能豐富，語(yǔ)言簡(jiǎn)捷，因此備受歡迎。經(jīng)不斷修改、擴(kuò)充和完善，SQL語(yǔ)言最終發(fā)展成為關(guān)系數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)語(yǔ)言。5.1SQL1986年10月，美國(guó)國(guó)家標(biāo)準(zhǔn)局(AmericanNationalStandardInstitute，ANSI)數(shù)據(jù)庫(kù)委員會(huì)批準(zhǔn)了SQL作為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言的美國(guó)標(biāo)準(zhǔn)。1987年，國(guó)際標(biāo)準(zhǔn)化組織(InternationalOrganizationforStandardization，ISO)正式通過(guò)SQL作為關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言。

SQL成為關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言后，大多數(shù)數(shù)據(jù)庫(kù)均以SQL作為共同的數(shù)據(jù)存取語(yǔ)言和標(biāo)準(zhǔn)接口。SQL是應(yīng)用于數(shù)據(jù)庫(kù)的語(yǔ)言，本身不能獨(dú)立存在。它是一種非過(guò)程性語(yǔ)言。使用SQL時(shí)，只需告訴數(shù)據(jù)庫(kù)需要什么數(shù)據(jù)，怎么顯示就可以了，具體內(nèi)部操作則由數(shù)據(jù)庫(kù)系統(tǒng)來(lái)完成。各個(gè)DBMS產(chǎn)品在具體實(shí)現(xiàn)標(biāo)準(zhǔn)SQL時(shí)都有一些擴(kuò)充。例如，Sybase與Microsoft公司使用Transact-SQL，而Oracle公司使用PL/SQL。5.1.2SQL的特點(diǎn)

SQL主要有以下特點(diǎn)：

(1)綜合統(tǒng)一。SQL語(yǔ)言集數(shù)據(jù)定義語(yǔ)言、數(shù)據(jù)處理語(yǔ)言、數(shù)據(jù)控制語(yǔ)言為一體，具有統(tǒng)一風(fēng)格，是綜合統(tǒng)一的語(yǔ)言。

(2)高度非過(guò)程化。SQL語(yǔ)言只需告訴數(shù)據(jù)庫(kù)需要什么數(shù)據(jù)，怎么顯示，具體的內(nèi)部操作則由數(shù)據(jù)庫(kù)系統(tǒng)自動(dòng)完成，是一種高度非過(guò)程化的語(yǔ)言。

(3)能以多種方式使用。SQL語(yǔ)言能以多種方式使用，可直接以命令的方式交互使用，也可嵌入到程序設(shè)計(jì)語(yǔ)言中使用，非常靈活。

(4)簡(jiǎn)捷易學(xué)。SQL語(yǔ)言功能強(qiáng)大，但由于語(yǔ)法比較簡(jiǎn)單，接近自然語(yǔ)言，所以容易學(xué)習(xí)，易于掌握。表5-1所列是SQL語(yǔ)言中完成核心功能的九個(gè)動(dòng)詞。表5-1SQL語(yǔ)言的動(dòng)詞5.1.3SQL的分類

SQL按照用途可以分為如下三大類。

1.數(shù)據(jù)定義語(yǔ)言

在SQL中定義數(shù)據(jù)庫(kù)、表、視圖和索引等數(shù)據(jù)庫(kù)對(duì)象的語(yǔ)言，稱為數(shù)據(jù)定義語(yǔ)言(DataDefinitionLanguage，DDL)。例如，創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)句(CREATEDATABASE)、創(chuàng)建數(shù)據(jù)表語(yǔ)句(CREATETABLE)和創(chuàng)建視圖語(yǔ)句(CREATEVIEW)等。

2.數(shù)據(jù)處理語(yǔ)言

在SQL中處理數(shù)據(jù)的語(yǔ)言稱為數(shù)據(jù)處理語(yǔ)言(DataManipulationLanguage，DML)。例如，使用SELECT(數(shù)據(jù)查詢語(yǔ)句)查詢表的內(nèi)容，或者使用INSERT(插入語(yǔ)句)、UPDATE(更新語(yǔ)句)和DELETE(刪除語(yǔ)句)插入、修改和刪除表中的數(shù)據(jù)記錄。

3.數(shù)據(jù)控制語(yǔ)言

在SQL中提供并發(fā)控制及恢復(fù)功能，向用戶授權(quán)，支持事務(wù)提交、回滾等功能的語(yǔ)言，稱為數(shù)據(jù)控制語(yǔ)言(DataControlLanguage，DCL)。例如，有時(shí)可能需要一次處理幾條SQL語(yǔ)句，希望它們必須全部執(zhí)行成功，如果其中一條執(zhí)行失敗，則這幾條語(yǔ)句都不要執(zhí)行，而且已經(jīng)執(zhí)行的應(yīng)該恢復(fù)到開始的狀態(tài)，這時(shí)就需要用到數(shù)據(jù)控制語(yǔ)言。

前面已經(jīng)學(xué)習(xí)了部分?jǐn)?shù)據(jù)定義語(yǔ)句，本章重點(diǎn)學(xué)習(xí)數(shù)據(jù)處理語(yǔ)言中的SELECT查詢語(yǔ)句。查詢是SQL的核心功能，也是數(shù)據(jù)庫(kù)中使用最多的操作。其他SQL語(yǔ)句在后續(xù)章節(jié)中將陸續(xù)講述。本章以SCMS數(shù)據(jù)庫(kù)查詢?yōu)槔齺?lái)說(shuō)明。

5.2.1SELECT語(yǔ)句的語(yǔ)法格式

SELECT語(yǔ)句的語(yǔ)法格式如下：

SELECT[ALL|DISTINCT]<字段表達(dá)式1>[,<字段表達(dá)式2>[,...n]]

[INTO<新表名>]

5.2SELECT語(yǔ)句

FROM<表名1>[,<表名2>[,...n]]

[WHERE<篩選條件表達(dá)式>]

[GROUPBY<分組表達(dá)式>[HAVING<分組條件表達(dá)式>]]

[ORDERBY<排序表達(dá)式>[ASC|DESC]]

各個(gè)子句說(shuō)明如下：

●

SELECT子句：指定查詢返回的列。

●

INTO子句：將查詢結(jié)果存儲(chǔ)到新表中。

●

FROM子句：用于指定查詢列所在的表和視圖。

●

WHERE子句：指定用于限制返回的行的篩選條件?！?/p>

GROUP子句：將結(jié)果按<分組表達(dá)式>的值進(jìn)行分組，該值相等的記錄為一個(gè)組。如果GROUP子句帶有HAVING短語(yǔ)，則只有滿足<分組條件表達(dá)式>條件的組才會(huì)顯示輸出。

SELECT語(yǔ)句的基本格式是由SELECT子句、FROM子句和WHERE子句組成的查詢塊。

整個(gè)SELECT語(yǔ)句的含義是：根據(jù)WHERE子句指定的條件，從FROM子句指定的表或視圖中查找滿足條件的數(shù)據(jù)，將查詢結(jié)果按GROUPBY子句指定的條件分組，按ORDERBY子句指定的順序排序，按SELECT子句指定的字段表達(dá)式及次序構(gòu)造一個(gè)結(jié)果表。5.2.2SELECT語(yǔ)句的執(zhí)行方式

SQLServer2005中的查詢編輯器用于編輯和運(yùn)行查詢代碼。

【例5-1】查詢所有教師的信息。

具體操作步驟如下：

(1)啟動(dòng)SQLServerManagementStudio。

(2)單擊工具欄中的“新建查詢”按鈕，打開查詢編輯器。

(3)單擊工具欄中的“可用數(shù)據(jù)庫(kù)”下拉列表框，選擇SCMS數(shù)據(jù)庫(kù)，或使用USESCMS語(yǔ)句，將當(dāng)前數(shù)據(jù)庫(kù)修改為SCMS數(shù)據(jù)庫(kù)。

(4)在查詢編輯器窗口中輸入如下查詢語(yǔ)句：

SELECT*FROMteacher

(5)單擊工具欄上的“分析”按鈕，進(jìn)行語(yǔ)法分析，保證語(yǔ)句語(yǔ)法正確。

(6)單擊工具欄上的“執(zhí)行”按鈕，執(zhí)行查詢語(yǔ)句。執(zhí)行結(jié)果如圖5-1所示。圖5-1例5-1執(zhí)行結(jié)果

單表查詢是指在一個(gè)表或一個(gè)視圖中進(jìn)行的查詢。

5.3.1SELECT子句

SELECT子句用于指定查詢返回的列。

1.查詢指定的列

通常，用戶只對(duì)部分列感興趣，用SELECT子句指定所需的列。SELECT子句中字段的先后順序可以與表中的順序不一致，結(jié)果表按照SELECT子句中的字段順序顯示。5.3單表查詢

【例5-2】查詢學(xué)生表中所有學(xué)生的學(xué)號(hào)、姓名和家庭住址。

SELECTsno,sname,saddressFROMstudent

執(zhí)行結(jié)果如圖5-2所示。圖5-2例5-2執(zhí)行結(jié)果

2.查詢所有的列

在SELECT子句中通常用通配符“*”表示指定表或視圖中的所有列。

【例5-3】查詢所有學(xué)生的信息。

SELECT*FROMstudent

執(zhí)行結(jié)果如圖5-3所示。圖5-3例5-3執(zhí)行結(jié)果

3.使用DISTINCT消除重復(fù)記錄

在查詢結(jié)果中往往會(huì)出現(xiàn)重復(fù)記錄，使用DISTINCT短語(yǔ)可以去掉查詢結(jié)果中重復(fù)出現(xiàn)的記錄。如果不使用DISTINCT短語(yǔ)，默認(rèn)使用ALL短語(yǔ)，表示允許重復(fù)記錄出現(xiàn)。

【例5-4】查詢所有學(xué)生所屬班級(jí)的班級(jí)號(hào)。

SELECTDISTINCTclassno

FROMstudent

執(zhí)行結(jié)果如圖5-4所示。圖5-4例5-4執(zhí)行結(jié)果

4.加入字符串列

在顯示查詢結(jié)果時(shí)，為了增強(qiáng)結(jié)果的可讀性，可以在某列前面加入字符串列，對(duì)該列起到說(shuō)明作用。

【例5-5】查詢所有學(xué)生的姓名和家庭住址。

SELECTsname,'家庭住址',saddress

FROMstudent

執(zhí)行結(jié)果如圖5-5所示。圖5-5例5-5執(zhí)行結(jié)果

5.使用別名

在顯示查詢結(jié)果時(shí)，為了增強(qiáng)結(jié)果的可讀性，也可以指定別名代替原來(lái)列的名稱。指定別名的方法有以下三種：

(1)采用“列名AS別名”的格式。

(2)采用“列名別名”的格式。

(3)采用“別名=列名”的格式。

這里別名可以用單引號(hào)引起來(lái)，也可以不用。

【例5-6】查詢所有學(xué)生的姓名和家庭住址，并在標(biāo)題欄顯示“姓名”、“家庭住址”。

以下三句的執(zhí)行效果相同：

SELECTsnameAS姓名,saddressAS家庭住址FROMstudent

SELECTsname姓名,saddress家庭住址FROMstudent

SELECT姓名=sname,家庭住址=saddressFROMstudent

執(zhí)行結(jié)果如圖5-6所示。圖5-6例5-6執(zhí)行結(jié)果

6.顯示表達(dá)式的值

SELECT子句中的字段表達(dá)式不僅可以是字段，也可以是表達(dá)式。表達(dá)式中也可使用函數(shù)。

【例5-7】查詢所有學(xué)生的姓名和年齡。

SELECTsname姓名,year(getdate())-year(borndate)年齡

FROMstudent

執(zhí)行結(jié)果如圖5-7所示。圖5-7例5-7執(zhí)行結(jié)果5.3.2WHERE子句

WHERE子句的目的是從表或視圖中過(guò)濾出符合條件的記錄。WHERE子句中的篩選條件表達(dá)式可以是關(guān)系表達(dá)式、邏輯表達(dá)式和特殊表達(dá)式。

1.關(guān)系表達(dá)式

用關(guān)系運(yùn)算符將兩個(gè)表達(dá)式連接在一起的式子即為關(guān)系表達(dá)式，關(guān)系表達(dá)式的返回值為邏輯值(TRUE，F(xiàn)ALSE)。關(guān)系表達(dá)式的格式如下：

<表達(dá)式1><關(guān)系運(yùn)算符><表達(dá)式2>

WHERE子句中的常用關(guān)系運(yùn)算符如表5-2所示。表5-2關(guān)系運(yùn)算符

說(shuō)明

①字符型數(shù)據(jù)之間的比較是對(duì)字符的ASCII值進(jìn)行比較。

②字符串的比較是從左向右依次進(jìn)行的。

【例5-8】查詢所有男生的姓名和家庭住址。

SELECTsname,saddress

FROMstudent

WHEREssex='男'

執(zhí)行結(jié)果如圖5-8所示。

【例5-9】查詢1988年以后出生的學(xué)生的基本情況。

SELECT*

FROMstudent

WHEREborndate>'1988-12-31'

執(zhí)行結(jié)果如圖5-9所示。

圖5-8例5-8執(zhí)行結(jié)果

圖5-9例5-9執(zhí)行結(jié)果

2.邏輯表達(dá)式

用邏輯運(yùn)算符將兩個(gè)表達(dá)式連接在一起的式子即為邏輯表達(dá)式，邏輯表達(dá)式的返回值為邏輯值(TRUE，F(xiàn)ALSE)。邏輯表達(dá)式的格式如下：

[<關(guān)系表達(dá)式1>]<邏輯運(yùn)算符><關(guān)系表達(dá)式2>

WHERE子句中常用的邏輯運(yùn)算符如表5-3所示。表5-3邏輯運(yùn)算符【例5-10】查詢所有年齡大于20歲的女生的姓名和出生日期。

SELECTsname,borndate

FROMstudent

WHEREyear(getdate())-year(borndate)>20ANDssex='女'

執(zhí)行結(jié)果如圖5-10所示。

【例5-11】查詢09031012班甘肅籍的學(xué)生的姓名、性別和家庭住址。

SELECTsname,ssex,saddress

FROMstudent

WHEREclassno='09031012'ANDsubstring(saddress,1,2)='甘肅'

執(zhí)行結(jié)果如圖5-11所示。

圖5-10例5-10執(zhí)行結(jié)果

圖5-11例5-11執(zhí)行結(jié)果

3.特殊表達(dá)式

特殊表達(dá)式在比較運(yùn)算中有一些特殊的用途，如用于指定范圍、模式匹配、測(cè)試字段值是否為空、檢查一個(gè)字段值是否屬于一組值之中等。

WHERE子句中特殊表達(dá)式常用的特殊運(yùn)算符如表5-4所示。特殊表達(dá)式的具體格式在使用時(shí)給出。表5-4特殊運(yùn)算符

說(shuō)明使用通配符時(shí)，一個(gè)漢字算一個(gè)字符。

1)

BETWEEN…AND

使用BETWEEN…AND可實(shí)現(xiàn)一個(gè)區(qū)間范圍的篩選。格式如下：

<表達(dá)式>[NOT]BETWEEN<表達(dá)式1>AND<表達(dá)式2>

【例5-12】查詢所有年齡為20～21歲的學(xué)生的基本情況。

SELECT*

FROMstudent

WHEREyear(getdate())-year(borndate)BETWEEN20AND21

執(zhí)行結(jié)果如圖5-12所示。

【例5-13】查詢所有年齡不在20～21之間的學(xué)生的基本情況。

SELECT*

FROMstudent

WHEREyear(getdate())-year(borndate)NOTBETWEEN20AND21

執(zhí)行結(jié)果如圖5-13所示。

圖5-12例5-12執(zhí)行結(jié)果

圖5-13例5-13執(zhí)行結(jié)果

2)

ISNULL

當(dāng)需要判斷一個(gè)表達(dá)式是否為空值時(shí)，可使用ISNULL。格式如下：

<表達(dá)式>IS[NOT]NULL

【例5-14】查詢所有電子郵件為空的學(xué)生姓名。

SELECTsname

FROMstudent

WHEREsemailISNULL

執(zhí)行結(jié)果如圖5-14所示。

【例5-15】查詢有電子郵件的學(xué)生的姓名和電子郵件。

SELECTsname,semail

FROMstudent

WHEREsemailISNOTNULL

執(zhí)行結(jié)果如圖5-15所示。

圖5-14例5-14執(zhí)行結(jié)果

圖5-15例5-15執(zhí)行結(jié)果

3)

LIKE

LIKE用于判斷字符串表達(dá)式是否與一個(gè)指定的模式字符串相匹配。格式如下：

<字符串表達(dá)式>[NOT]LIKE<模式字符串>

【例5-16】查詢所有姓張，且姓名為兩個(gè)漢字的學(xué)生的學(xué)號(hào)和姓名。

SELECTsno,snameFROMstudentWHEREsnameLIKE'張_'

執(zhí)行結(jié)果如圖5-16所示。

【例5-17】查詢所有姓張和姓陳的學(xué)生的學(xué)號(hào)和姓名。

SELECTsno,snameFROMstudentWHEREsnameLIKE'[張,陳]%'

執(zhí)行結(jié)果如圖5-17所示。

圖5-16例5-16執(zhí)行結(jié)果

圖5-17例5-17執(zhí)行結(jié)果

4)

IN

IN可以指定一個(gè)值表，當(dāng)表達(dá)式的值與值表中的任意一個(gè)匹配時(shí)，返回TRUE，否則返回FALSE。IN的作用類似于邏輯“或”。格式如下：

<表達(dá)式>[NOT]IN(表達(dá)式1[,...n])

【例5-18】查詢所有09011011班和09011012班的學(xué)生的學(xué)號(hào)、姓名和性別。

SELECTsno,sname,ssex

FROMstudent

WHEREclassnoIN('09011011','09011012')

執(zhí)行結(jié)果如圖5-18所示。

【例5-19】查詢既不是09011011班，也不是09011012班的學(xué)生的學(xué)號(hào)、姓名和性別。

SELECTsno,sname,ssex

FROMstudent

WHEREclassnoNOTIN('09011011','09011012')

執(zhí)行結(jié)果如圖5-19所示。

圖5-18例5-18執(zhí)行結(jié)果

圖5-19例5-19執(zhí)行結(jié)果5.3.3ORDERBY子句

在SELECT語(yǔ)句中，使用ORDERBY子句可以對(duì)查詢結(jié)果進(jìn)行升序或降序排列。其中“排序表達(dá)式”可以是一個(gè)列名、列的別名、表達(dá)式或非零的整數(shù)值，而非零的整數(shù)值則表示列名、列的別名、表達(dá)式在選擇列表中的位置。ASC表示升序，為默認(rèn)值；DESC表示降序。排序時(shí)空值(NULL)被認(rèn)為是最小值。

【例5-20】按照出生日期順序列出所有學(xué)生的學(xué)號(hào)、姓名、性別和出生日期。

SELECTsno,sname,ssex,borndate

FROMstudent

ORDERBYborndate

執(zhí)行結(jié)果如圖5-20所示。

【例5-21】查詢所有09011011班和09011012班學(xué)生的學(xué)號(hào)、姓名、性別和出生日期，要求按照班級(jí)升序排序，同一班級(jí)按出生日期降序排序。

SELECTsno,sname,ssex,borndate

FROMstudent

WHEREclassnoIN('09011011','09011012')

ORDERBYclassno,borndateDESC

執(zhí)行結(jié)果如圖5-21所示。

圖5-20例5-20執(zhí)行結(jié)果

圖5-21例5-21執(zhí)行結(jié)果5.3.4聚合函數(shù)

SQLServer提供的聚合函數(shù)用來(lái)完成一定的統(tǒng)計(jì)功能。SQL語(yǔ)言中常用的聚合函數(shù)如表5-5所示。表5-5SQL語(yǔ)言中常用的聚合函數(shù)

說(shuō)明如果指定DISTINCT，則表示在計(jì)算時(shí)要取消指定字段中的重復(fù)值。如果不指定DISTINCT，則表示為ALL(ALL為缺省)，即在計(jì)算時(shí)不取消重復(fù)值。

【例5-22】統(tǒng)計(jì)學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)AS學(xué)生人數(shù)

FROMstudent

執(zhí)行結(jié)果如圖5-22所示。

【例5-23】查詢年齡最大和最小的學(xué)生的出生日期。

SELECTMIN(borndate)AS年齡最大學(xué)生生日,MAX(borndate)AS年齡最小學(xué)生生日

FROMstudent

執(zhí)行結(jié)果如圖5-23所示。

圖5-22例5-22執(zhí)行結(jié)果

圖5-23例5-23執(zhí)行結(jié)果

【例5-24】統(tǒng)計(jì)03201號(hào)課程的平均分。

SELECTAVG(result)AS平均成績(jī)

FROMsc

WHEREcno='03201'

執(zhí)行結(jié)果如圖5-24所示。

【例5-25】統(tǒng)計(jì)第一學(xué)期所開課程的總課時(shí)和總學(xué)分。

SELECTSUM(hours)AS總課時(shí),SUM(credit)AS總學(xué)分

FROMcourse

WHEREterm='1'

執(zhí)行結(jié)果如圖5-25所示。

圖5-24例5-24執(zhí)行結(jié)果

圖5-25例5-25執(zhí)行結(jié)果5.3.5GROUPBY子句

利用GROUPBY子句能夠?qū)⒉樵兘Y(jié)果按照指定的字段值進(jìn)行分組，實(shí)現(xiàn)分組統(tǒng)計(jì)。HAVING子句對(duì)分組后的結(jié)果進(jìn)行條件篩選，控制只輸出分組條件表達(dá)式值為TRUE的組。

【例5-26】統(tǒng)計(jì)各班學(xué)生人數(shù)。

SELECTclassnoAS班級(jí),COUNT(*)AS人數(shù)

FROMstudent

GROUPBYclassno

執(zhí)行結(jié)果如圖5-26所示。

【例5-27】統(tǒng)計(jì)各班學(xué)生人數(shù)和平均年齡。

SELECTclassno班級(jí),COUNT(sno)人數(shù),

AVG(year(getdate())-year(borndate))平均年齡

FROMstudent

GROUPBYclassno

執(zhí)行結(jié)果如圖5-27所示。

圖5-26例5-26執(zhí)行結(jié)果

圖5-27例5-27執(zhí)行結(jié)果圖5-28例5-28執(zhí)行結(jié)果

【例5-28】查詢?nèi)藬?shù)大于2的班級(jí)。

SELECTclassnoAS班級(jí),COUNT(*)AS人數(shù)

FROMstudent

GROUPBYclassnoHAVINGCOUNT(*)>2

執(zhí)行結(jié)果如圖5-28所示。

說(shuō)明WHERE子句與HAVING子句的區(qū)別在于作用的對(duì)象不同。WHERE子句作用于表或視圖，從表或視圖中選擇滿足篩選條件的記錄；HAVING子句作用在組上，選擇滿足篩選條件的組。

單表查詢只在一個(gè)表或視圖中進(jìn)行查詢。在實(shí)際查詢中，往往涉及多個(gè)表或視圖。例如，查詢某學(xué)生選修課程的情況，包括學(xué)生姓名、課程名、課程成績(jī)、取得學(xué)分等信息，就需要在student表、sc表和course表中進(jìn)行查詢。將同時(shí)涉及兩個(gè)或兩個(gè)以上的表或視圖的查詢稱為連接查詢。

在Transact-SQL中，連接查詢有兩類表示形式：一類是符合SQL標(biāo)準(zhǔn)的連接謂詞的表示形式，一類是Transact-SQL擴(kuò)展的JOIN連接的表示形式。5.4連接查詢5.4.1連接謂詞

在SELECT語(yǔ)句的WHERE子句中使用比較運(yùn)算符，給出連接條件對(duì)表進(jìn)行連接，將這種表示形式稱為連接謂詞的表示形式?；靖袷饺缦拢?/p>

[<表名1>.]<字段名1><運(yùn)算符><表名2.><字段名2>

以上連接謂詞中的兩個(gè)字段稱為連接字段，它們必須是可比的。連接謂詞中的比較運(yùn)算符可以是<、<=、=、>、>=、!=、<>等。當(dāng)比較運(yùn)算符為“=”時(shí)，就是等值連接；若在等值連接中去除結(jié)果表中相同的字段名，則為自然連接；若有多個(gè)連接條件，則為復(fù)合條件連接；若一個(gè)表與自身連接，則稱為自身連接。

【例5-29】查詢每個(gè)學(xué)生及其班級(jí)的詳細(xì)情況。

SELECTstudent.*,class.*

FROMstudent,class

WHEREstudent.classno=class.classno

這是一種等值連接的方法，結(jié)果表中有兩個(gè)“classno”字段。執(zhí)行結(jié)果如圖5-29所示。圖5-29例5-29執(zhí)行結(jié)果

說(shuō)明在查詢所引用的兩個(gè)或多個(gè)表之間，任何重復(fù)的字段都必須用表名限定。如果某字段在查詢用到的兩個(gè)或多個(gè)表中不重復(fù)，則不必用表名去限定。但是如果所有的字段都用表名限定，則可提高查詢的可讀性。

【例5-30】查詢每個(gè)學(xué)生及其班級(jí)的詳細(xì)情況。

SELECTstudent.*,class.classname,class.leader,class.dno

FROMstudent,class

WHEREstudent.classno=class.classno

這是一種自然連接的方法，結(jié)果表中只有一個(gè)“classno”字段。執(zhí)行結(jié)果如圖5-30所示。圖5-30例5-30執(zhí)行結(jié)果

【例5-31】查詢選修了“計(jì)算機(jī)導(dǎo)論”課程且成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)、姓名、課程名及成績(jī)。

SELECTstudent.sno,student.sname,ame,sc.result

FROMstudent,course,sc

WHEREstudent.sno=sc.snoANDo=o

ANDame='計(jì)算機(jī)導(dǎo)論'ANDsc.result>80

這是一種復(fù)合條件連接。執(zhí)行結(jié)果如圖5-31所示。

【例5-32】查詢所有課程的先修課的先修課。

SELECTame,ame

FROMcoursea,courseb,coursec

WHEREa.pno=oANDb.pno=o

這是一種自身連接。執(zhí)行結(jié)果如圖5-32所示。

圖5-31例5-31執(zhí)行結(jié)果

圖5-32例5-32執(zhí)行結(jié)果5.4.2JOIN連接

Transact-SQL擴(kuò)展的JOIN連接的表示形式可將多個(gè)表連接起來(lái)。FROM后面JOIN連接的格式如下：

<first_table><join_type><second_table>ON<search_condition>

|<first_table>CROSSJOIN<second_table>

參數(shù)說(shuō)明如下：

●

first_table，second_table：需要連接的表。

●

join_type：連接類型。其格式如下：

[INNER]|{LEFT|RIGHT|FULL}[OUTER]JOIN

●

ON：用于指定連接條件。

以JOIN指定的連接有三種類型：INNERJOIN為內(nèi)連接，OUTERJOIN為外連接，CROSSJOIN為交叉連接。

1.內(nèi)連接

內(nèi)連接按照ON所指定的連接條件合并兩個(gè)表，返回滿足條件的行。內(nèi)連接是系統(tǒng)默認(rèn)的，可以省略INNER。使用內(nèi)連接后，仍可使用WHERE子句指定其他條件。

【例5-33】查詢所有學(xué)生的基本情況和選修課程的情況。

SELECT*FROMstudentINNERJOINscONstudent.sno=sc.sno

結(jié)果表中包含了student和sc表的所有字段，其中sno是重復(fù)字段。執(zhí)行結(jié)果如圖5-33所示。若要去除重復(fù)字段，可將以上語(yǔ)句改為如下語(yǔ)句：

SELECTstudent.*,o,sc.result

FROMstudentINNERJOINscONstudent.sno=sc.sno

【例5-34】查詢選修了03105號(hào)課程且成績(jī)?cè)?5分以上的學(xué)生的姓名和成績(jī)。

SELECTstudent.sname,sc.result

FROMstudentJOINscONstudent.sno=sc.sno

WHEREo='03105'ANDsc.result>85

執(zhí)行結(jié)果如圖5-34所示。

圖5-33例5-33執(zhí)行結(jié)果

圖5-34例5-34執(zhí)行結(jié)果

【例5-35】查詢選修了“計(jì)算機(jī)導(dǎo)論”課程且成績(jī)?cè)?0分以上的學(xué)生的學(xué)號(hào)、姓名、課程名及成績(jī)。

SELECTstudent.sno,student.sname,ame,sc.result

FROMstudentJOINscONstudent.sno=sc.sno

JOINcourseONo=o

WHEREame='計(jì)算機(jī)導(dǎo)論'ANDsc.result>80

這是通過(guò)student表、sc表、course表之間的內(nèi)連接實(shí)現(xiàn)的。執(zhí)行結(jié)果如圖5-35所示。圖5-35例5-35執(zhí)行結(jié)果

2.外連接

外連接的結(jié)果表不但包含ON所指定的連接條件的行，還包含相應(yīng)表中的所有行。外連接包括三種：左外連接、右外連接和完全外連接。外連接中OUTER可以省略。

1)左外連接(LEFTOUTERJOIN或LEFTJOIN)

結(jié)果表中除了滿足連接條件的行以外，還包含左表中的所有行，但不包括右表中的不匹配行。

【例5-36】查詢所有學(xué)生的選修課程情況，列出學(xué)號(hào)、姓名、選修課程號(hào)、成績(jī)。沒(méi)有選課的學(xué)生也一并列出。

SELECTstudent.sno,student.sname,o,sc.result

FROMstudentLEFTOUTERJOINscONstudent.sno=sc.sno

結(jié)果表中包含了student表中所有的學(xué)生，沒(méi)有選課的學(xué)生cno和result字段值為空值。執(zhí)行結(jié)果如圖5-36所示。圖5-36例5-36執(zhí)行結(jié)果

2)右外連接(RIGHTOUTERJOIN或RIGHTJOIN)

結(jié)果表中除了滿足連接條件的行以外，還包含右表中的所有行，但不包括左表中的不匹配行。

【例5-37】采用右外連接查詢09031011班所有學(xué)生的選修課程情況，列出學(xué)號(hào)、姓名、選修課程號(hào)、成績(jī)。沒(méi)有選課的學(xué)生也一并列出。

SELECTstudent.sno,student.sname,o,sc.result

FROMscRIGHTOUTERJOINstudentONstudent.sno=sc.sno

WHEREstudent.classno='09031011'

結(jié)果表中包含了student表中所有的學(xué)生，沒(méi)有選課的學(xué)生cno和result字段值為空值。執(zhí)行結(jié)果如圖5-37所示。圖5-37例5-37執(zhí)行結(jié)果

3)完全外連接(FULLOUTERJOIN或FULLJOIN)

結(jié)果表中除了滿足連接條件的行以外，還包含兩表中的所有行。若要通過(guò)在連接結(jié)果中包括不匹配信息，可以使用完全外連接。

3.交叉連接

交叉連接的結(jié)果表是由第一個(gè)表的每行與第二個(gè)表的每行拼接后形成的表。交叉連接就是兩個(gè)表的笛卡爾積。第一個(gè)表的行數(shù)乘以第二個(gè)表的行數(shù)等于交叉連接結(jié)果表的行數(shù)。交叉連接不使用WHERE子句。

【例5-38】查詢所有可能的選課情況，列出學(xué)號(hào)、姓名、課程號(hào)、課程名。

SELECTstudent.sno,student.sname,o,ame

FROMstudentCROSSJOINcourse

執(zhí)行結(jié)果如圖5-38所示。圖5-38例5-38執(zhí)行結(jié)果

在SQL語(yǔ)句中，將一條SELECT查詢語(yǔ)句嵌套在SELECT、INSERT、DELETE、UPDATE等語(yǔ)句中，該SELECT查詢就是子查詢。

5.5子查詢當(dāng)一條SELECT語(yǔ)句嵌套在SELECT語(yǔ)句中時(shí)，外層的SELECT語(yǔ)句稱為外部查詢或父查詢，內(nèi)層的查詢就稱為內(nèi)部查詢或子查詢。子查詢可以嵌套，利用子查詢嵌套可以將比較復(fù)雜的查詢分解成幾個(gè)簡(jiǎn)單查詢的嵌套來(lái)實(shí)現(xiàn)，使得條理清晰。

嵌套查詢是按照邏輯順序由里向外執(zhí)行的，即先處理子查詢，然后將子查詢的結(jié)果用于父查詢的查詢條件。5.5.1使用IN的子查詢

在嵌套查詢中，子查詢的結(jié)果往往是一個(gè)集合，使用IN的子查詢用于進(jìn)行一個(gè)表達(dá)式的值是否在子查詢結(jié)果集中的判斷。使用IN的子查詢的格式如下：

<表達(dá)式>[NOT]IN(子查詢)

當(dāng)表達(dá)式與子查詢結(jié)果集中的某個(gè)值相等時(shí)，返回TRUE，否則，返回FALSE。若使用NOT，則返回的值正好相反。

【例5-39】查詢所有與“賀迎春”在一個(gè)班學(xué)習(xí)的學(xué)生的學(xué)號(hào)、姓名、班級(jí)號(hào)。

SELECTsno,sname,classno

FROMstudent

WHEREclassnoIN

(SELECTclassnoFROMstudentWHEREsname='賀迎春')

DBMS在實(shí)現(xiàn)該查詢時(shí)，先查找“賀迎春”所在的班級(jí)號(hào)，再查找在該班級(jí)學(xué)習(xí)的學(xué)生。執(zhí)行結(jié)果如圖5-39所示。本例查詢也可以通過(guò)以下的自身連接來(lái)完成，執(zhí)行結(jié)果如圖5-40所示。

SELECTa.sno,a.sname,a.classno

FROMstudenta,studentb

WHEREa.classno=b.classnoANDb.sname='賀迎春'

圖5-39例5-39第1句執(zhí)行結(jié)果

圖5-40例5-39第2句執(zhí)行結(jié)果

【例5-40】查詢選修了03105號(hào)課程的學(xué)生的學(xué)號(hào)和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREsnoIN

(SELECTsnoFROMscWHEREcno='03105')

執(zhí)行結(jié)果如圖5-41所示。

【例5-41】查詢09031011班沒(méi)有選修03105號(hào)課程的學(xué)生的學(xué)號(hào)和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREclassno='09031011'ANDsnoNOTIN

(SELECTsnoFROMscWHEREcno='03105')

執(zhí)行結(jié)果如圖5-42所示。

圖5-41例5-40執(zhí)行結(jié)果

圖5-42例5-41執(zhí)行結(jié)果

【例5-42】查詢選修了“數(shù)據(jù)庫(kù)原理及應(yīng)用”課程的學(xué)生名單。

SELECTstudent.sname

FROMstudent

WHEREsnoIN

(SELECTsnoFROMsc

WHEREcnoIN

(SELECTcnoFROMcourseWHEREcname='數(shù)據(jù)庫(kù)原理及應(yīng)用'))

執(zhí)行結(jié)果如圖5-43所示。

【例5-43】查詢沒(méi)有選修“數(shù)據(jù)庫(kù)原理及應(yīng)用”的學(xué)生名單。

SELECTstudent.sname

FROMstudent

WHEREsnoNOTIN

(SELECTsnoFROMsc

WHEREcnoIN

(SELECTcnoFROMcourseWHEREcname='數(shù)據(jù)庫(kù)原理及應(yīng)用'))

執(zhí)行結(jié)果如圖5-44所示。

圖5-43例5-42執(zhí)行結(jié)果

圖5-44例5-43執(zhí)行結(jié)果5.5.2使用比較運(yùn)算符的子查詢

使用比較運(yùn)算符的子查詢是指父查詢和子查詢之間用比較運(yùn)算符連接的子查詢。使用比較運(yùn)算符的子查詢使表達(dá)式的值與子查詢的結(jié)果進(jìn)行比較運(yùn)算，其格式如下：

<表達(dá)式>{<|<=|=|>|>=|!=|<>|!<|!>}{ANY|ALL}(子查詢)

其中，ANY和ALL說(shuō)明對(duì)比較運(yùn)算符的限制。

ALL表示表達(dá)式要與子查詢結(jié)果集中的每個(gè)值都進(jìn)行比較，當(dāng)表達(dá)式與結(jié)果集中的每個(gè)值都滿足比較運(yùn)算的關(guān)系時(shí)，才返回TRUE，否則，返回FALSE。

ANY表示表達(dá)式只要與子查詢結(jié)果集中的某個(gè)值滿足比較運(yùn)算的關(guān)系時(shí)，就返回TRUE，否則，返回FALSE。例如，“>ALL(0,1,2)”表示表達(dá)式的值要大于2，才返回TRUE。“>ANY(0,1,2)”表示表達(dá)式的值至少大于一個(gè)值，也就是表達(dá)式只要大于0或1或2，就返回TRUE。

因此，要使帶有“>ALL”的子查詢中的某行滿足外部查詢中指定的條件，引入子查詢的列中的值必須大于由子查詢返回的值的列表中的每個(gè)值。

同樣，要使帶有“>ANY”的子查詢中的某行滿足外部查詢中指定的條件，引入子查詢的列中的值必須至少大于由子查詢返回的值的列表中的一個(gè)值。表5-6帶有ALL和ANY的子查詢運(yùn)算【例5-44】查詢比09031011班的所有學(xué)生年齡都小的學(xué)生的學(xué)號(hào)、姓名和出生日期。

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>ALL

(SELECTborndateFROMstudentWHEREclassno='09031011')

執(zhí)行結(jié)果如圖5-45所示。

本查詢也可以用聚合函數(shù)來(lái)實(shí)現(xiàn)，語(yǔ)句如下：

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>

(SELECTMAX(borndate)FROMstudentWHEREclassno='09031011')

【例5-45】查詢比09031011班的某個(gè)學(xué)生年齡小的學(xué)生的學(xué)號(hào)、姓名和出生日期。

SELECTsno,sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>ANY

(SELECTborndateFROMstudent

WHEREclassno='09031011')

執(zhí)行結(jié)果如圖5-46所示。本查詢也可以用聚合函數(shù)來(lái)實(shí)現(xiàn)，語(yǔ)句如下：

SELECTstudent.sno,student.sname,borndate

FROMstudent

WHEREclassno!='09031011'ANDborndate>

(SELECTMIN(borndate)FROMstudent

WHEREclassno='09031011')

圖5-45例5-44執(zhí)行結(jié)果

圖5-46例5-45執(zhí)行結(jié)果5.5.3使用EXISTS的子查詢

使用EXISTS用于測(cè)試子查詢的結(jié)果集是否為空表。若子查詢的結(jié)果集不為空，則EXISTS返回TRUE，否則，返回FALSE。EXISTS還可與NOT結(jié)合，即NOTEXISTS，其返回值與EXISTS剛好相反。使用EXISTS的子查詢格式如下：

[NOT]EXISTS(子查詢)

由EXISTS引出的子查詢，因?yàn)镾ELECT子句指定列無(wú)意義，其SELECT子句格式通常是SELECT*。

【例5-46】查詢選修了03105號(hào)課程的學(xué)生學(xué)號(hào)和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHEREEXISTS

(SELECT*FROMscWHEREcno='03105'ANDsno=student.sno)

執(zhí)行結(jié)果如圖5-47所示。圖5-47例5-46執(zhí)行結(jié)果

【例5-47】查詢沒(méi)有選修03105號(hào)課程的學(xué)生學(xué)號(hào)和姓名。

SELECTstudent.sno,student.sname

FROMstudent

WHERENOTEXISTS

(SELECT*FROMscWHEREcno='03105'ANDsno=student.sno)

執(zhí)行結(jié)果如圖5-48所示。圖5-48例5-47執(zhí)行結(jié)果

【例5-48】查詢選修了全部課程的學(xué)生名單。

SELECTstudent.sname

FROMstudent

WHERENOTEXISTS

(SELECT*FROMcourseWHERENOTEXISTS

(SELECT*FROMscWHEREsno=student.snoANDcno=o))

執(zhí)行結(jié)果如圖5-49所示。圖5-49例5-48執(zhí)行結(jié)果5.5.4相關(guān)子查詢

分析例5-46、例5-47和例5-48,可發(fā)現(xiàn)它們有一個(gè)共同之處，即子查詢的查詢條件中引用了外層父查詢的某個(gè)字段值。

如果子查詢的WHERE子句中引用了外部查詢表，則該查詢稱為相關(guān)子查詢。

相關(guān)子查詢與非相關(guān)子查詢不同。非相關(guān)子查詢的內(nèi)層查詢只處理一次，得到一個(gè)結(jié)果集，依據(jù)子查詢的結(jié)果處理外層查詢；而相關(guān)子查詢的內(nèi)層查詢要處理多次，內(nèi)層查詢與外層查詢的某個(gè)字段有關(guān)，當(dāng)外層查詢表隨著不同行該字段值變化時(shí)，內(nèi)層查詢的結(jié)果集就會(huì)隨之變化，依據(jù)變化的子查詢的結(jié)果處理外層查詢。以例5-46為例，子查詢的查詢條件中引用了外層父查詢的student表的sno字段，父查詢的student表的不同行的sno字段值不同，相對(duì)應(yīng)的子查詢的查詢條件就不同，因而子查詢的查詢結(jié)果集就隨著父查詢的student表的sno字段值的變化而變化。

相關(guān)子查詢的執(zhí)行過(guò)程是：

(1)取外層查詢表的一行，取出與內(nèi)層查詢相關(guān)的字段值。

(2)根據(jù)該行與內(nèi)層查詢相關(guān)的字段值來(lái)處理內(nèi)層查詢，若外層查詢的WHERE子句返回值為真，則取此行記錄放入結(jié)果集中。

(3)返回到第(1)步，直到外層查詢表的所有行處理完為止。

【例5-49】查詢軟件09-1班的學(xué)生名單。

SELECTstudent.sname

FROMstudent

WHERE'軟件09-1'IN

(SELECTclassnameFROMclass

WHEREclassno=student.classno)

執(zhí)行結(jié)果如圖5-50所示。圖5-50例5-49執(zhí)行結(jié)果5.5.5INSERT、DELETE、UPDATE語(yǔ)句中的子查詢

子查詢也可以嵌套在INSERT、DELETE、UPDATE語(yǔ)句中。

1.帶子查詢的INSERT語(yǔ)句

前面介紹的INSERT語(yǔ)句一次只能插入一條記錄。實(shí)際上，帶子查詢的INSERT語(yǔ)句一次可以插入多條記錄。

帶子查詢的INSERT語(yǔ)句的基本格式如下：

INSERTINTO<表名>[(<字段1>[,<字段2>...])]

子查詢

【例5-50】對(duì)每個(gè)班，求學(xué)生的最小年齡、最大年齡、平均年齡，然后新建一個(gè)表，把結(jié)果存入表中。

首先建立一個(gè)新表，有五個(gè)字段：班級(jí)號(hào)、班級(jí)名、最小年齡、最大年齡、平均年齡。

CREATETABLEclass_age

(

classnochar(8),

classnamevarchar(30),