第03章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言

計(jì)算機(jī)學(xué)院陳豐

chenfeng@

數(shù)據(jù)庫(kù)系統(tǒng)概論1第三章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖第3章小結(jié)21SQL的發(fā)展2SQL的性質(zhì)3SQL的特點(diǎn)4SQL對(duì)關(guān)系數(shù)據(jù)庫(kù)的支持5SQL的功能3.1SQL概述31.SQL的發(fā)展SQL:StructuredQueryLanguage，結(jié)構(gòu)化查詢語(yǔ)言1974年，由Boyce和Chamber提出1975-1979年，在SystemR上實(shí)現(xiàn)，由IBM的SanJose研究室研制，稱為Sequel(色扣、C扣等)有關(guān)標(biāo)準(zhǔn)SQL-86：“數(shù)據(jù)庫(kù)語(yǔ)言SQL”SQL-89：“具有完整性增強(qiáng)的數(shù)據(jù)庫(kù)語(yǔ)言SQL”，增加了對(duì)完整性約束的支持SQL-92：“數(shù)據(jù)庫(kù)語(yǔ)言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強(qiáng)的完整性、安全性支持等。SQL-1999：增加了對(duì)象關(guān)系特征（使用比較廣的標(biāo)準(zhǔn)）3.1SQL概述42.SQL的性質(zhì)SQL是關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言，提供數(shù)據(jù)的定義、查詢、更新和控制等功能。已成為數(shù)據(jù)庫(kù)領(lǐng)域中的一個(gè)主流語(yǔ)言。SQL語(yǔ)言不是一個(gè)應(yīng)用程序開發(fā)語(yǔ)言，只提供對(duì)數(shù)據(jù)庫(kù)的操作能力，不能完成屏幕控制，菜單管理，報(bào)表生成等功能，但可成為應(yīng)用開發(fā)語(yǔ)言的一部分。大多數(shù)數(shù)據(jù)庫(kù)均用SQL作為共同的數(shù)據(jù)存儲(chǔ)語(yǔ)言和標(biāo)準(zhǔn)接口，使不同的數(shù)據(jù)庫(kù)系統(tǒng)之間的互操作有了共同的基礎(chǔ)有人把確立SQL為關(guān)系數(shù)據(jù)庫(kù)語(yǔ)言標(biāo)準(zhǔn)及其后的發(fā)展稱為

一場(chǎng)革命SQL對(duì)數(shù)據(jù)庫(kù)以外的領(lǐng)域也產(chǎn)生了很大影響3.1SQL概述53.SQL的特點(diǎn)綜合統(tǒng)一集DDL，DML，DCL于一體，語(yǔ)言風(fēng)格統(tǒng)一，操作符統(tǒng)一高度非過程化用戶只需提出“做什么”，無須告訴“怎么做”，不必了解存取路徑面向集合的操作方式一次一集合，操作方式、操作對(duì)象、結(jié)果均為集合一種語(yǔ)法結(jié)構(gòu)，兩種使用方式SQL既是自含式語(yǔ)言（用戶使用），又是嵌入式語(yǔ)言（程序員使用）語(yǔ)言簡(jiǎn)潔，易學(xué)易用：在語(yǔ)言上接近英語(yǔ)3.1SQL概述64.SQL對(duì)關(guān)系數(shù)據(jù)庫(kù)的支持

即SQL對(duì)象與三級(jí)模式結(jié)構(gòu)的對(duì)應(yīng)關(guān)系3.1SQL概述存儲(chǔ)文件1存儲(chǔ)文件2基本表1基本表2基本表3SQL視圖1視圖2外模式模式內(nèi)模式關(guān)系模型SQL-------------------------------------------------外模式----視圖(VIEW)模式-------基本表(TABLE)內(nèi)模式----存儲(chǔ)文件_索引基本表是本身獨(dú)立存在的表，一個(gè)表帶若干索引。

一個(gè)或多個(gè)基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，它的物理結(jié)構(gòu)對(duì)用戶來說是透明的。視圖是從一個(gè)或多個(gè)基本表導(dǎo)出的表，不存放數(shù)據(jù)，是一個(gè)虛表（數(shù)據(jù)庫(kù)中只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表）。視圖上可以再定義視圖。73.1SQL概述5.SQL的功能SQL設(shè)計(jì)巧妙，核心功能只需9個(gè)動(dòng)詞。在語(yǔ)言上接近英語(yǔ)SQL功能操作符操作數(shù)據(jù)查詢DMLSELECT對(duì)基本表、視圖進(jìn)行查詢數(shù)據(jù)定義DDLCREATEALTERDROP創(chuàng)建數(shù)據(jù)庫(kù)、基本表、視圖、索引修改數(shù)據(jù)庫(kù)、基本表、視圖刪除數(shù)據(jù)庫(kù)、基本表、視圖、索引數(shù)據(jù)操縱DMLINSERTUPDATEDELETE在指定表中插入數(shù)據(jù)（元組）在指定表中修改數(shù)據(jù)在指定表中刪除數(shù)據(jù)數(shù)據(jù)控制DCLGRANTREVOKE將指定操作權(quán)限授予指定用戶收回權(quán)限8第三章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖第3章小結(jié)93.2數(shù)據(jù)定義操作對(duì)象操作方式創(chuàng)建刪除修改表CREATETABLEDROPTABLEAlterTable視圖CREATEVIEWDROPVIEWAlterView索引CREATEINDEXDROPINDEXSQL的數(shù)據(jù)定義功能包括定義表、定義視圖和定義索引。表3.2SQL的數(shù)據(jù)定義語(yǔ)句

103.2數(shù)據(jù)定義（續(xù)）3.2.1域定義3.2.2定義基本表3.2.3修改基本表3.2.4刪除基本表3.2.5建立與刪除索引11SQLServer數(shù)據(jù)類型說明char（n）固定長(zhǎng)度的字符串類型，n表示字符串的最大長(zhǎng)度，取值范圍為1～8000varchar（n）可變長(zhǎng)度的字符串類型，n表示字符串的最大長(zhǎng)度，取值范圍為1～8000Int4字節(jié)，存儲(chǔ)從–231(–2,147,483,648)到231-1(2,147,483,647)范圍的整數(shù)smallint2字節(jié)，存儲(chǔ)從–215(–32,768)到215-1(32,767)范圍的整數(shù)numeric（p,q）或decimal（p,q）定點(diǎn)精度和小數(shù)位數(shù)。使用最大精度時(shí)，有效值從–1038+1到1038-1。其中，p為精度，指定小數(shù)點(diǎn)左邊和右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)。q為小數(shù)位數(shù)，指定小數(shù)點(diǎn)右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)，0<=q<=p。q的默認(rèn)值為0float8字節(jié)，存儲(chǔ)從–1.79E+308到1.79E+308范圍的浮點(diǎn)型數(shù)real4字節(jié)，存儲(chǔ)從–3.40E+38到3.40E+38范圍的浮點(diǎn)型數(shù)datetime占用8字節(jié)空間，存儲(chǔ)從1753年1月1日到9999年12月31日的日期和時(shí)間數(shù)據(jù)，精確到百分之三秒（或3.33毫秒）3.2.1域定義121.定義語(yǔ)句格式3.2.2定義基本表CREATETABLE<表名>

（<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>][，<列名><數(shù)據(jù)類型>[<列級(jí)完整性約束條件>]]…[，<表級(jí)完整性約束條件>]）<表名>：所要定義的基本表的名字（在整個(gè)數(shù)據(jù)庫(kù)中必須唯一）<列名>：組成該表的各個(gè)屬性（列）（列名在表中必須唯一）CREATETABLEstudent(

Snochar(9)PRIMARYKEY,--列級(jí)完整性約束條件，Sno是主碼

Snamevarchar(20)UNIQUE,--Sname取唯一值

Ssexchar(2), Sageint,

Sdeptchar(20))SQLserver中的注釋問題：Sname中能保存多少個(gè)漢字或英文字母?[]表示內(nèi)容為可選的133.2.2定義基本表（續(xù)）2.定義完整性約束為了維護(hù)數(shù)據(jù)完整性，DBMS必須提供一種機(jī)制，檢查DB

中的數(shù)據(jù)是否滿足語(yǔ)義規(guī)定的條件。這些加在DB的數(shù)據(jù)之上的語(yǔ)義約束條件就是數(shù)據(jù)完整性

約束條件，這些條件作為表定義的一部分存儲(chǔ)在數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中。用戶操作數(shù)據(jù)庫(kù)中的數(shù)據(jù)時(shí)由DBMS自動(dòng)檢查數(shù)據(jù)是否滿足完整性約束條件。143.2.2定義基本表（續(xù)）2.定義完整性約束定義表的同時(shí)，可定義與該表有關(guān)的完整性約束條件。<列級(jí)完整性約束條件>：涉及表中一個(gè)屬性列的完整性約束條件，用于限制該屬性列的取值，如（NOTNULL/UNIQCE/DEFAULT等）。在定義列時(shí)定義。<表級(jí)完整性約束條件>：涉及表中多個(gè)屬性列的完整性約束條件，用于限制元組中各屬性列之間的聯(lián)系，如（開始日期<結(jié)束日期、發(fā)貨數(shù)量<訂貨數(shù)量等）。在表

定義的最后定義。153.2.2定義基本表（續(xù)）3.常用完整性約束唯一性約束：UNIQUE非空值約束：NOTNULL默認(rèn)值約束：DEFAULT主碼約束：PRIMARYKEY外碼約束：FORIGNKEYCHECK約束：限制列的取值范圍下列情況時(shí)，只能表級(jí)約束處定義

1)主碼由多個(gè)屬性組成時(shí)，主屬性用括號(hào)

2)CHECK約束若限制多列之間的取值約束時(shí)只能用于列級(jí)約束可用于列級(jí)、表級(jí)約束16例：定義Student、Course和SC關(guān)系3.2.2定義基本表CREATETABLEstudent(

Snochar(9)PRIMARYKEY,

Snamevarchar(20)NOTNULL,

Ssexchar(2), Sageint,

Sdeptchar(20))CREATETABLECourse(cnochar(10)PRIMARYKEY,

cnamechar(20)NOTNULL,

pcnochar(4),creditsmallint

CHECK(credit>0)

)CREATETABLESC(

Snochar(9),

Cnochar(10), gradesmallint,

PRIMARYKEY(Sno,Cno), FOREIGNKEY(Sno)REFERENCESstudent(Sno), FOREIGNKEY(Cno)REFERENCEScourse(Cno),CHECK(grade>=0andgrade<=100))應(yīng)注意完整性約束的定義1)主碼：PRIMARYKEY關(guān)鍵字。2)外碼：FOREIGNKEY關(guān)鍵字。3)用戶定義：CHECK關(guān)鍵字等。列級(jí)完整性約束表級(jí)完整性約束173.2.2定義基本表（續(xù)）4.實(shí)體完整性約束：用主碼約束（PRIMARYKEY）來保證每個(gè)表只有一個(gè)PRIMARYKEY約束PRIMARYKEY約束的列值集合必須是唯一的且不允許有空值CREATETABLEstudent(

Snochar(9)PRIMARYKEY,--主碼只有1個(gè)屬性

……)CREATETABLESC(

Snochar(9),

Cnochar(10), ……

PRIMARYKEY(Sno,Cno),--主碼由多個(gè)屬性構(gòu)成

……)183.2.2定義基本表（續(xù)）5.參照完整性約束：用外碼約束（FOREIGNKEY）來保證FOREIGNKEY(外碼)REFERENCES<外碼所屬主表名>(列名)外碼必須引用有PRIMARYKEY或UNIQUE約束的列CREATETABLESC(

Snochar(9),

Cnochar(10),…… FOREIGNKEY(Sno)REFERENCESstudent(Sno), FOREIGNKEY(Cno)REFERENCEScourse(Cno),……)SnoCnoGrade95011929501286SCsnoSnameSsexSageSdept9501李勇男20CS9502劉晨女19ISStudentCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)2Course193.2.2定義基本表（續(xù)）6.用戶定義完整性約束唯一性約束：UNIQUE非空值約束：NOTNULL默認(rèn)值約束：DEFAULTCHECK約束：用于限制列的取值在指定的范圍內(nèi)，或約束同一個(gè)表某個(gè)元組中多個(gè)列之間的取值*一個(gè)表中可以在一個(gè)列或多個(gè)列上定義UNIQUE*每個(gè)列只有一個(gè)DEFAULT約束，允許使用系統(tǒng)提供的值作為默認(rèn)值*執(zhí)行INSERT語(yǔ)句或UPDATE語(yǔ)句，系統(tǒng)自動(dòng)檢查

CHECK約束，只有在向表中插入數(shù)據(jù)時(shí)才檢查

DEFAULT約束206.用戶定義完整性約束—例子說明Student表的Ssex只能取“男”、“女”。

Ssexchar(2)CHECK(SsexIN(‘男’,’女’))說明SC表的Grade在0到100分之間

CHECK(Grade>=0andGrade<=100)涉及多個(gè)列值之間的比較

CREATETABLEt1( IDchar(4)PRIMARYKEY,

oldvalue

int,

newvalue

int,

CHECK(newvalue>oldvalue) )3.2.2定義基本表（續(xù)）列級(jí)完整性約束表級(jí)完整性約束213.2.3修改基本表ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>|<列名>][ALTER<列名><數(shù)據(jù)類型>]<表名>：要修改的基本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的完整性約束條件或列ALTER子句：用于修改列名和數(shù)據(jù)類型22[例1]

向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型

ALTERTABLEStudentADDSentranceDATETIME不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。

[例2]

刪除列

ALTERTABLEStudentDropColumnSentrance

[例3]

將年齡的數(shù)據(jù)類型由整數(shù)改為短整數(shù)。

ALTERTABLEStudentALTERSageSmallInt注：修改原有的列定義有可能會(huì)破壞已有數(shù)據(jù)

[例4]

增加課程名稱必須取唯一值的約束條件。

ALTERTABLECourseADDUNIQUE(Cname)3.2.3修改基本表（續(xù)）233.2.4刪除基本表DROPTABLE<表名>刪除基本表時(shí)，系統(tǒng)會(huì)將與該表有關(guān)的所有對(duì)象一并刪除。即表中的數(shù)據(jù)、表上的索引都刪除，表上的視

圖往往仍然保留，但無法引用。[例11]

刪除Student表

DROPTABLEStudent執(zhí)行刪除基本表的操作一定要格外小心！243.2.5建立與刪除索引1索引2建立索引3刪除索引251.索引?建立索引的目的：加快查詢速度，提高數(shù)據(jù)文件訪問效率。

?缺點(diǎn)：索引是有代價(jià)的（時(shí)、空）。為了維護(hù)索引，對(duì)數(shù)據(jù)進(jìn)行插入、更新、刪除操作所花費(fèi)的時(shí)間會(huì)更長(zhǎng)。?在設(shè)計(jì)和創(chuàng)建索引時(shí)，應(yīng)確保對(duì)性能的提高程度大于在存儲(chǔ)空間和處理資源方面的代價(jià)。3.2.5建立與刪除索引（續(xù)）26?

MicrosoftSQLServer支持在表中任何列（包括計(jì)算列）上定義的索引。?如果一個(gè)表沒有創(chuàng)建索引，則數(shù)據(jù)行不按任何特定的順序存儲(chǔ)。這種結(jié)構(gòu)稱為堆集。SQLServer索引的兩種類型為：

聚集索引、非聚集索引?維護(hù)索引

DBMS自動(dòng)完成?使用索引

DBMS自動(dòng)選擇是否使用索引以及使用哪些索引3.2.5建立與刪除索引（續(xù)）272.建立索引語(yǔ)句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 用<表名>指定要建索引的基本表名字索引可以建立在該表的一列或多列上，各列名之間用逗號(hào)分隔用<次序>指定索引值的排列次序，升序：ASC，降序：DESC。缺省值：ASC3.2.5建立與刪除索引（續(xù)）[例13]CREATEINDEXstu_snameONStudent(Sname)在Student表的Sname（姓名）列上建立索引。28CLUSTERED(聚集索引)

：數(shù)據(jù)文件中的記錄按照索引鍵指定的順序排序，使得具有相同索引鍵值的記錄在物理上聚集在一起（即構(gòu)成一簇［cù］）。即索引項(xiàng)的順序與表中記錄的物理順序一致。一個(gè)表只能建立一個(gè)聚集索引。3.2.5建立與刪除索引（續(xù)）29UNIQUE(單一索引)：非聚集索引中的特例

唯一索引，不允許存在索引值相同的兩行（相當(dāng)于增加了一個(gè)UNIQUE約束）3.2.5建立與刪除索引（續(xù)）303.刪除索引DROPINDEX<索引名>刪除索引時(shí)，系統(tǒng)會(huì)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例7]

刪除Student表的stu_sname索引。

DROPINDEXstu_sname3.2.5建立與刪除索引（續(xù)）31第三章

關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖第3章小結(jié)323.3查詢3.3.1概述3.3.2單表查詢3.3.3連接查詢3.3.4嵌套查詢3.3.5集合查詢3.3.6小結(jié)33查詢功能是SQL語(yǔ)言的核心，是數(shù)據(jù)庫(kù)中用得最多的操作。SQL語(yǔ)言的所有查詢都是利用SELECT語(yǔ)句完成的。SELECT語(yǔ)句的功能：用于檢索和統(tǒng)計(jì)數(shù)據(jù)作用對(duì)象：關(guān)系（表）結(jié)果：產(chǎn)生一個(gè)新的關(guān)系（表）使用方式：交互式、嵌入式使用3.3.1概述343.3.1概述（續(xù)）1.語(yǔ)句格式：SELECT

[ALL|DISTINCT]<目標(biāo)列表達(dá)式>…--需要哪些列FROM<表名或視圖名>[，<表名或視圖名>]…--來自哪些表[WHERE<檢索條件表達(dá)式>]--根據(jù)什么條件[GROUPBY<分組依據(jù)列>[HAVING<組提取條件>]][ORDERBY<排序依據(jù)列>[ASC|DESC]]DISTINCT表示在查詢結(jié)果中去掉重復(fù)行，ALL缺省

[例1]

查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECTSno，SnameFROMStudent352.SELECT查詢語(yǔ)句，可看成是關(guān)系代數(shù)運(yùn)算表達(dá)式SELECT子句：指定要顯示的屬性列，作投影運(yùn)算FROM子句：指定查詢對(duì)象(基本表或視圖)，多個(gè)關(guān)系時(shí)則表示要做笛卡爾積運(yùn)算WHERE子句：指定查詢條件，做選擇運(yùn)算GROUPBY子句：對(duì)查詢結(jié)果按指定列的值分組，該屬性列值相等的元組為一個(gè)組。通常會(huì)在每組中作用集函數(shù)。HAVING短語(yǔ)：篩選出只有滿足指定條件的組ORDERBY子句：對(duì)查詢結(jié)果表按指定列值的升序或降序排序3.3.1概述（續(xù)）363.3.2單表查詢查詢僅涉及一個(gè)表，是一種最簡(jiǎn)單的查詢操作1.選擇表中的若干列2.查詢滿足條件的元組3.對(duì)查詢結(jié)果排序4.使用集函數(shù)5.對(duì)查詢結(jié)果分組371.選擇表中若干列[例1]

查詢?nèi)w學(xué)生的學(xué)號(hào)與姓名。SELECTSno，SnameFROMStudent

[例2]

查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECTSname，Sno，SdeptFROMStudent[例3]

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

SELECTSno,Sname,Ssex,Sage,Sdept

FROMStudent

或

SELECT*FROMStudent；3.3.2單表查詢（續(xù)）*表示取所有列的信息列名要用逗號(hào)分開38(1)目標(biāo)列表達(dá)式<目標(biāo)列表達(dá)式>不僅可以使用列名，還可以使用算術(shù)表達(dá)

式、字符串常量和函數(shù)等。例

查全體學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。

SELECT

Sname,‘YearofBirth:’,2005－Sage,LOWER(Sdept)

FROMStudent3.3.2單表查詢列表達(dá)式的顯示結(jié)果均沒有列標(biāo)題如何為空白列提供一個(gè)列標(biāo)題??表達(dá)式函數(shù)常量39使用列別名改變查詢結(jié)果的列標(biāo)題別名要用空格或AS分開例：

SELECT

Sname

NAME,‘YearofBirth:’

ASBIRTH,2005-SageASBIRTHDAY,

LOWER(Sdept)AS

DEPARTMENT

FROMStudent3.3.2單表查詢列的別名40(2)消除取值重復(fù)的行在SELECT子句中使用DISTINCT短語(yǔ)例：查詢選修了課程的學(xué)生學(xué)號(hào)。1)SELECTSnoFROMSC2)SELECTDISTINCT

SnoFROMSC3.3.2單表查詢SC去掉重復(fù)元組41注意：DISTINCT短語(yǔ)的作用范圍是所有目標(biāo)列例：查詢選修課程的各種成績(jī)錯(cuò)誤的寫法SELECTDISTINCT

Cno，DISTINCTGradeFROMSC正確的寫法

SELECTDISTINCT

Cno，GradeFROMSC

3.3.2單表查詢422.查詢滿足條件的元組WHERE子句常用的查詢條件查詢條件謂詞比較=，>，<，>=，<=，!=，<>，!>，!<，NOT+上述比較運(yùn)算符確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空值ISNULL，ISNOTNULL邏輯運(yùn)算AND，OR，NOT3.3.2單表查詢（續(xù)）43(1)比較大小在WHERE子句的<比較條件>中使用比較運(yùn)算符=，>，<，>=，<=，!=或<>，!>，!<，邏輯運(yùn)算符NOT+比較運(yùn)算符[例8]

查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECTSname,SageFROMStudentWHERESage<20

或（等價(jià)于）SELECTSname,SageFROMStudentWHERENOTSage>=20*取反操作的執(zhí)行效率比較低，盡量少用44(2)確定范圍使用謂詞BETWEEN…AND…NOTBETWEEN…AND…[例10]

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

SELECTSname，Sdept，SageFROMStudent

WHERESageBETWEEN20AND23該查詢等價(jià)于下面的查詢

SELECTSname,Sdept,SageFROMStudent

WHERESage>=20ANDSage<=2345(3)確定集合IN是表示某元素是否屬于某集合的謂詞語(yǔ)法格式

WHERE<列名>[NOT]IN<值表>

<值表>：用逗號(hào)分隔的一組取值

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

SELECTSname，Ssex

FROMStudent

WHERESdeptIN('IS'，'MA'，'CS')snoSnameSsexSageSdept9501李勇男20CS9502劉晨女19IS9503王敏女18MA9504張立男19IS9505趙陽(yáng)女21GL46(4)字符串匹配格式1：[NOT]LIKE’<匹配串>’含義：查找指定列值與<匹配串>相匹配的元組。 其中<匹配串>可含：％(百分號(hào))

：代表任意長(zhǎng)度(可為0)的字符串。_(下橫線)

：代表任意單個(gè)字符。

相當(dāng)于Windows、dos等中查文件名的*和？例：查所有姓劉或姓王的學(xué)生姓名、學(xué)號(hào)和性別。

SELECTSname,Sno,Ssex FROMStudent WHERESnameLIKE‘劉％’ORSnameLIKE‘王％’新的問題：如何表示%和_本身？用ESCAPE換碼轉(zhuǎn)義邏輯運(yùn)算符47格式2：

LIKE’<匹配串>’

ESCAPE’<換碼字符>’

若要查的串本身含％或_，用ESCAPE‘<換碼字符>’對(duì)通配符進(jìn)行轉(zhuǎn)義。

[例20]：查“DB_”開頭，且倒數(shù)第2個(gè)字符為i的課程情況

SELECT* FROMCourse WHEREcnameLIKE‘DB\_

％i_’ESCAPE‘\’\_表示要找內(nèi)容為_表示任意單個(gè)字符48書上用\換碼轉(zhuǎn)義不幸，postgres恰好不能用\實(shí)際上可以更靈活（常用#,&,!等，甚至用字母都可以！）。如下面的！轉(zhuǎn)義：

SELECTc1FROMtb

WHEREc1LIKE'%10-15!%off%'ESCAPE'!'!%進(jìn)行換碼轉(zhuǎn)義49(5)涉及空值的查詢使用謂詞ISNULL或ISNOTNULL“ISNULL”不能用“=NULL”代替

(注意：有的系統(tǒng)允許NULL比較，可以使用＝和<>，如MSSQLServer。有的不允許，如Oracle。SQL標(biāo)準(zhǔn)不允許)[例21]

某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績(jī)。查詢?nèi)鄙俪煽?jī)的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)。

SELECTSno，CnoFROMSC

WHEREGradeISNULL50(6)復(fù)合條件查詢用邏輯運(yùn)算符AND和OR來聯(lián)結(jié)多個(gè)查詢條件AND的優(yōu)先級(jí)高于OR可以用括號(hào)改變優(yōu)先級(jí)[例23]

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

SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20513.3.2單表查詢（續(xù)）3.對(duì)查詢結(jié)果排序使用ORDERBY子句可以按一個(gè)或多個(gè)屬性列排序升序：ASC；降序：DESC；當(dāng)排序列含空值（NULL）時(shí)ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示默認(rèn)為升序(ASC)，NULL值最大（這里說“NULL值最大”，僅僅針對(duì)NULL值排序的情況。

SQLserver中相反為最小）52[例24]

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

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC

3.3.2單表查詢（續(xù)）53注：如果取“NULL值”的字段出現(xiàn)在條件表達(dá)式中，將使條件計(jì)算為NULL，進(jìn)而被排除于結(jié)果外。例如，查找成績(jī)?cè)?0分以上(含)的學(xué)生的學(xué)號(hào)：

SELECTDISTINCTSno FROMSC WHEREGrade>=90

成績(jī)?yōu)镹ULL的學(xué)生的學(xué)號(hào)并不出現(xiàn)在結(jié)果中。3.3.2單表查詢（續(xù)）544.使用集函數(shù)為了進(jìn)一步方便用戶，增強(qiáng)檢索功能，SQL提供了許多集函數(shù)，主要包括：COUNT([DISTINCT|ALL]*)

統(tǒng)計(jì)元組個(gè)數(shù)

COUNT([DISTINCT|ALL]<列名>)

統(tǒng)計(jì)指定列非空的記錄個(gè)數(shù)。SUM([DISTINCT|ALL]<列名>)

計(jì)算一列值的總和（此列必須是數(shù)值型）AVG([DISTINCT|ALL]<列名>)

計(jì)算一列值的平均值（此列必須是數(shù)值型）MAX([DISTINCT|ALL]<列名>)

求一列值中的最大值MIN([DISTINCT|ALL]<列名>)

求一列值中的最小值

3.3.2單表查詢（續(xù)）55使用集函數(shù)（續(xù)）DISTINCT短語(yǔ)：在計(jì)算時(shí)要取消指定列中的重復(fù)值A(chǔ)LL短語(yǔ)：不取消重復(fù)值A(chǔ)LL為缺省值[例26]

查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMStudent；

[例27]

查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCT

Sno)FROMSC；注：學(xué)生每選修一門課，在SC中都有一條相應(yīng)的記錄，而一個(gè)學(xué)生一般都要選修多門課程，為避免重復(fù)計(jì)算學(xué)生人數(shù)，必須在COUNT函數(shù)中用DISTINCT短語(yǔ)。56使用集函數(shù)（續(xù)）[例28]

計(jì)算1號(hào)課程的學(xué)生平均成績(jī)。

SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]

查詢選修1號(hào)課程的學(xué)生最高分?jǐn)?shù)。

SELECTMAX(Grade)FROMSCWHERCno=‘1’；除count(*)外，NULL值均被聚集函數(shù)所忽略。575.對(duì)查詢結(jié)果分組GROUPBY子句可以將查詢結(jié)果表的各行按一列或多列取值相等的原則進(jìn)行分組。對(duì)查詢結(jié)果分組的目的是為了細(xì)化集函數(shù)的作用對(duì)象。如果未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果，即整個(gè)查詢結(jié)果只有一個(gè)函數(shù)值。否則，集函數(shù)將作用于每一個(gè)組，即每一組都有一個(gè)函數(shù)值。如例28中SELECTAVG(Grade)FROMSCWHERECno=‘1’表示課程1的平均分，若要同時(shí)查每門課程的平均分？3.3.2單表查詢（續(xù)）58使用GROUPBY子句分組注：SQL規(guī)定，所有帶有NULL值的記錄在分組時(shí)被作為一組。[例30]

求各門課程的平均分?jǐn)?shù)。

SELECTCno，AVG(Grade)asAvg_GrageFROMSCGROUPBYCno；

一般來說，分組查詢的SELECT目標(biāo)列中只允許出現(xiàn)聚集函數(shù)和GROUPBY子句中出現(xiàn)過的列59注：使用HAVING短語(yǔ)篩選最終輸出結(jié)果[例31]

查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

HAVING條件針對(duì)的是GROUP分組，WHERE針對(duì)的是元組，兩者并不矛盾。

使用GROUPBY子句分組只有滿足HAVING短語(yǔ)指定條件的組才輸出60[例32]

查詢有3門以上課程是90分以上的學(xué)生的學(xué)號(hào)及（90分以上的）課程數(shù)

SELECTSno,COUNT(*)FROMSCWHEREGrade>=90--成績(jī)?yōu)?0分以上的課程

GROUPBYSnoHAVINGCOUNT(*)>=3;--有3門這樣的課程HAVING短語(yǔ)與WHERE子句的區(qū)別：作用對(duì)象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組。HAVING短語(yǔ)作用于組，從中選擇滿足條件的組。使用GROUPBY子句分組613.3.3連接查詢同時(shí)涉及多個(gè)表的查詢稱為連接查詢學(xué)號(hào)課程號(hào)成績(jī)9501192950128695021729503290學(xué)號(hào)學(xué)生性別年齡班號(hào)9501李勇男241036019502劉晨女251036019503王名男231037019504張立女24103702學(xué)號(hào)學(xué)生性別課程名成績(jī)9501李勇男數(shù)據(jù)庫(kù)929501李勇男高數(shù)869502劉晨女?dāng)?shù)據(jù)庫(kù)729503王名男高數(shù)90SCStudentCourse連接查詢CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言6462格式1（SQL-89）：

SELECT列表達(dá)式1，列表達(dá)式2，……，列表達(dá)式nFROM表1，表2WHERE<連接條件>AND<查詢條件>3.3.3連接查詢（續(xù)）格式2（SQL-92）：

SELECT列表達(dá)式1，列表達(dá)式2，……，列表達(dá)式nFROM表1[INNER/OUTER/CROSS]JOIN

表2ON<連接條件>WHERE<查詢條件>63SQL中連接查詢中的主要類型：1.內(nèi)連接

1)等值連接

2)非等值連接

3)自身連接2.外連接3.廣義笛卡爾積4.多表連接查詢3.3.3連接查詢（續(xù)）643.3.3連接查詢（續(xù)）1.內(nèi)連接是一種常用的連接類型。如果兩個(gè)表的相關(guān)字段滿足連接條件，則從兩個(gè)表中提取數(shù)據(jù)并組合成新的記錄。用來連接兩個(gè)表的條件稱為連接條件或連接謂詞，其一般格式為：

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>

比較運(yùn)算符為“=”的連接操作稱為等值連接，反之稱為

非等值連接（比較運(yùn)算符：>、<、>=、<=、!=

）。65[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>連接條件中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一個(gè)是整型，另一個(gè)是實(shí)型，整型和實(shí)型都是數(shù)值型，因此是可比的。但若一個(gè)是字符型，另一個(gè)是整數(shù)型就不允許了，因?yàn)樗鼈兪遣豢杀鹊念愋汀Ｈ暨B接條件中引用表名1和表名2的同名屬性時(shí)，都必

須加上表名前綴。若引用唯一屬性名時(shí)也可省略表名前綴。3.3.3連接查詢（續(xù)）66DBMS執(zhí)行連接操作的過程：首先在表1中找到第一個(gè)元組，然后從頭開始順序掃描或按索引掃描表2，查找滿足連接條件的元組，每找到一個(gè)元組,就將表1中的第一個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。表2全部掃描完畢后，再到表1中找第二個(gè)元組，然后再?gòu)念^開始順序掃描或按索引掃描表2，查找滿足連接條件的元組，每找到一個(gè)元組,就將表1中的第二個(gè)元組與該元組拼接起來，形成結(jié)果表中一個(gè)元組。重復(fù)上述操作，直到表1全部元組都處理完畢為止。（優(yōu)化）3.3.3連接查詢（續(xù)）學(xué)號(hào)課程號(hào)成績(jī)9501C01929501C02869502C01729503C0290學(xué)號(hào)學(xué)生性別年齡班號(hào)9501李勇男241036019502劉晨女251036019503王名男23103701SCStudent67[例33]

查詢每個(gè)學(xué)生及其選修課程的情況。學(xué)生情況存放在Student表中，學(xué)生選課情況存放在SC表中，所以本查詢要把Student與SC表的數(shù)據(jù)通過兩個(gè)表都具有的屬性Sno(外碼連接)實(shí)現(xiàn)的。這是一個(gè)等值連接。完成本查詢的SQL語(yǔ)句為：

SELECTs.*,

SC.*

FROMStudentassJOINSCON

s.Sno=SC.Sno

--連接操作

3.3.3連接查詢（續(xù)）Sno重復(fù)出現(xiàn)，第1列為student表的，后一個(gè)Sno為SC表的68若在等值連接中把目標(biāo)列中重復(fù)的屬性列去掉則為自然連接（等值連接的特殊情況）。[例34]查詢學(xué)生9501及其選修課程的情況。

SELECT

s.Sno,Sname,Ssex,Sage,

Sdept,Cno,Grade--投影操作

FROMStudentassJOINSCON

s.Sno=SC.Sno

--連接操作

WHERE

s.Sno='9501'--選擇操作3.3.3連接查詢（續(xù)）693.3.3連接查詢（續(xù)）自身連接連接操作不僅可以在兩個(gè)表之間進(jìn)行，也可以是一個(gè)表

與其自己進(jìn)行連接，這種連接稱為表的自身連接。例35查詢每一門課的間接先修課（只求兩層即先行課的先行課），只查課程號(hào)CnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64FIRSTCnoCnameCpnoCcredit1數(shù)據(jù)庫(kù)542數(shù)學(xué)23信息系統(tǒng)144操作系統(tǒng)635數(shù)據(jù)結(jié)構(gòu)746數(shù)據(jù)處理27C語(yǔ)言64SECOND70書上的作法是：

SELECTFIRST.Cno,SECOND.Cpno

FROMCourseFIRST,CourseSECOND

WHEREFIRST.Cpno=SECOND.Cno;這一作法實(shí)際上有問題：沒有先行課的先行課的課程（即某門課的先行課的先行課為NULL）也出現(xiàn)在結(jié)果中。請(qǐng)思考后，自己修改正確。3.3.3連接查詢（續(xù)）713.3.3連接查詢（續(xù)）2.外連接外連接的特殊性在于外連接會(huì)保留那些在另一關(guān)系中沒

有可連接元組的剩余元組（懸浮元組）。由于外連接得到的關(guān)系的屬性集是參與連接的兩個(gè)關(guān)系的屬性集的并集，因此懸浮元組需要用NULL來填充缺值的屬性。外連接操作以指定表為連接主體，將主體表中不滿足連接條件的元組一并輸出。左外連接：LEFT[OUTER]JOIN,保留左關(guān)系所有元組右外連接：RIGHT[OUTER]JOIN,保留右關(guān)系所有元組72[例36]

查詢每個(gè)學(xué)生及其選修課程的情況包括沒有選修課程的學(xué)生----用外連接操作

SELECT

s.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade

FROMStudentassLEFTJOINSCON

s.Sno=SC.Sno；

3.3.3連接查詢（續(xù)）OUTER可以省略懸浮元組，沒有選課用NULL來填充缺值的屬性保留左關(guān)系（即Student）所有元組73[例37]

查詢每門課程及其選修課程的情況包括沒有學(xué)生選修過的課程----用外連接操作

SELECT

sc.Sno,sc.Cno,sc.Grade,c.CName,c.Credit

FROMSCRIGHTJOINCoursecON

sc.Cno=c.Cno；

3.3.3連接查詢（續(xù)）保留右關(guān)系（即Course）所有元組743.廣義笛卡爾積不帶連接謂詞的連接很少使用例：

SELECT

Student.*,SC.*

FROMStudent,SC3.3.3連接查詢（續(xù)）754.多表連接查詢：查詢涉及多個(gè)表，與兩個(gè)表的連接本質(zhì)相同首先必須對(duì)查詢進(jìn)行分析，確定涉及幾個(gè)表1)若查詢結(jié)果涉及n個(gè)表的屬性,則這種連接至少包括n個(gè)表2)若查詢必須通過連接表，則連接表必須包括在這n個(gè)表中連接表：反映聯(lián)系的表，如SC,它連接了Student和Course3.3.3連接查詢（續(xù)）SELECT列表達(dá)式1，列表達(dá)式2，……，列表達(dá)式nFROM表1JOIN表2ON<連接條件1>JOIN表3ON<連接條件2>……JOIN表nON<連接條件n-1>WHERE<查詢條件>763.3.3連接查詢（續(xù)）[例38]

查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修的課程名及成績(jī)。SELECT

s.Sno,Sname,Cname,GradeFROMStudentass

JOINSCON

s.Sno=SC.Sno

JOINCourseON

SC.Cno=Course.Cno;77嵌套查詢概述在SQL語(yǔ)言中，一個(gè)SELECT-FROM-WHERE語(yǔ)句稱為一個(gè)查詢塊。將一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或

HAVING短語(yǔ)的條件中的查詢稱為嵌套查詢或子查詢。子查詢一般跟在IN、SOME（ANY）、ALL和EXIST等謂詞后面。3.3.4嵌套查詢

SELECT

Sname

--外層查詢/父查詢

FROM

Student

WHERE

Sno

IN(SELECT

Sno

--內(nèi)層查詢/子查詢

FROM

SC

WHERE

Cno=‘2’

);783.3.4嵌套查詢（續(xù)）子查詢的限制：不能使用ORDERBY子句通常，嵌套查詢的求解方法是由里向外處理。即每個(gè)子查詢?cè)谄渖弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。嵌套查詢使得可以用一系列簡(jiǎn)單查詢構(gòu)成復(fù)雜的查詢，從而明顯地增強(qiáng)了SQL的查詢能力。以層層嵌套的方式來構(gòu)造程序正是SQL(StructuredQueryLanguage)中“結(jié)構(gòu)化”的含義所在。有些嵌套查詢可以用連接運(yùn)算替代79一、帶有IN謂詞的子查詢二、帶有比較運(yùn)算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢3.3.4嵌套查詢（續(xù)）80一、帶有IN謂詞的子查詢》帶有IN謂詞的子查詢是指父查詢與子查詢之間用IN進(jìn)行連接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中?！酚捎谠谇短撞樵冎?，子查詢的結(jié)果往往是一個(gè)集合，所以謂詞IN是嵌套查詢中最經(jīng)常使用的謂詞。[例39]

查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生3.3.4嵌套查詢（續(xù)）81查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生，可以首先確定“劉晨”所在系名，然后再查找所有在該系學(xué)習(xí)的學(xué)生。所以可以分步來完成此查詢：

①確定“劉晨”所在系名

SELECTSdept

FROMStudent

WHERESname='劉晨';

結(jié)果為：

Sdept

----------

IS

3.3.4嵌套查詢（續(xù)）82②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept='IS';結(jié)果為：

Sno

Sname

Sdept

--------------------------

95001

劉晨

IS

95004

張立

IS

3.3.4嵌套查詢（續(xù)）83可表示為嵌套查詢的形式：

SELECTSno,Sname,Sdept

FROMStudent

WHERESdeptIN

(SELECTSdept

FROMStudent

WHERESname=‘劉晨’);本例中的查詢也可以用我們前面學(xué)過的表的自身連接查詢來完成。

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1

JOINStudentS2ONS1.Sdept=S2.SdeptWHERES2.Sname='劉晨'；3.3.4嵌套查詢（續(xù)）84[例40]查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名

SELECTSno,Sname

③最后在Student關(guān)系中

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno

②然后在SC關(guān)系中找出選

FROMSC修了3號(hào)課程的學(xué)生學(xué)號(hào)

WHERECnoIN(SELECTCno

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

FROMCourse息系統(tǒng)”的課程號(hào)，結(jié)果為3號(hào)

WHERECname='信息系統(tǒng)'));3.3.4嵌套查詢（續(xù)）SELECT

s.Sno,Sname

--用連接查詢FROMStudentass

JOINSCON

s.Sno=SC.Sno

JOINCourseON

SC.Cno=Course.CnoWHERECname=‘信息系統(tǒng)’853.3.4嵌套查詢（續(xù)）例39和例40中的各個(gè)子查詢都只執(zhí)行一次，其結(jié)果用于父查詢，子查詢的查詢條件不依賴于父查詢，這類子查詢稱為不相關(guān)子查詢。不相關(guān)子查詢是最簡(jiǎn)單的一類子查詢。相關(guān)子查詢：子查詢的查詢條件依賴于父查詢

863.3.4嵌套查詢（續(xù)）二、帶有比較運(yùn)算符的子查詢帶有比較運(yùn)算符的子查詢是指父查詢與子查詢之間用比較運(yùn)算符進(jìn)行連接。當(dāng)用戶能確切知道內(nèi)層查詢返回的是單值時(shí)，可以用

>、<、=、>=、<=、!=或<>等比較運(yùn)算符。87例：在例39中，由于一個(gè)學(xué)生只可能在一個(gè)系學(xué)習(xí)，也就是說內(nèi)查詢劉晨所在系的結(jié)果是一個(gè)唯一值，因此該查詢也可以用比較運(yùn)算符來實(shí)現(xiàn)，其SQL語(yǔ)句如下：

SELECTSno,Sname,SdeptFROMStudentWHERESdept

=--例39中使用IN(SELECTSdeptFROMStudentWHERESname=‘劉晨’);這一作法可能有問題：如果兩個(gè)或以上系都有叫做“劉晨”的學(xué)生，查詢將出錯(cuò)。3.3.4嵌套查詢（續(xù)）[例39]

查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生88需要注意的是，子查詢一定要跟在比較符之后，下列寫法是錯(cuò)誤的：

SELECTSno,Sname,Sdept

FROMStudent

WHERE(SELECTSdept

FROMStudent

WHERESname='劉晨')=Sdept;

3.3.4嵌套查詢（續(xù)）89[例40]相關(guān)子查詢：子查詢的查詢條件依賴于父查詢找出每個(gè)學(xué)生超過他選修課程平均成績(jī)的課程號(hào)。3.3.4嵌套查詢（續(xù)）SCySELECTAVG(Grade)FROMSCyWHEREy.Sno=‘9501’92是否>此平均成績(jī)?若大于，則輸出該行90相關(guān)子查詢的一般求解過程：外層每一元組都被帶入內(nèi)層。內(nèi)層得到結(jié)果。外層WHERE條件判斷。如果為真，當(dāng)前元組被選出；否則，不被選出。重復(fù)，直到外層沒有元組為止。3.3.4嵌套查詢（續(xù)）

SELECTSno,CnoFROMSCx

WHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno);1.第1行，Sno為95012.內(nèi)層計(jì)算出9501選修的平均成績(jī)?yōu)?83.判斷外層的條件92>88，故第1行被選出4.以此類推SC91三、帶有ANY（SOME）或ALL的子查詢比較運(yùn)算符一般不能與集合比較。集合前加以謂詞ANY（SOME）或ALL修飾后可以比較。ANY(SOME)R

意義為

SomeinR。R中的某一個(gè)（任意一個(gè)值）。AllR意義為allinR,或allofR。R中的全部。3.3.4嵌套查詢（續(xù)）92056(5<some)=true05)=false0505(5!=some)=true(注意0

5)(5<some)=true(5=someANY(SOME)：任意一個(gè)值3.3.4嵌套查詢（續(xù)）93056(5<ALL)=false69)=true0546(5!=ALL)=true(5<ALL)=false(5=ALL3.3.4嵌套查詢（續(xù)）ALL：所有值94需要配合使用比較運(yùn)算符>ANY 大于子查詢結(jié)果中的某個(gè)值>ALL 大于子查詢結(jié)果中的所有值<ANY 小于子查詢結(jié)果中的某個(gè)值<ALL 小于子查詢結(jié)果中的所有值>=ANY 大于等于子查詢結(jié)果中的某個(gè)值>=ALL 大于等于子查詢結(jié)果中的所有值<=ANY 小于等于子查詢結(jié)果中的某個(gè)值<=ALL 小于等于子查詢結(jié)果中的所有值=ANY 等于子查詢結(jié)果中的某個(gè)值=ALL 等于子查詢結(jié)果中的所有值（通常沒有實(shí)際意義）!=（或<>）ANY 不等于子查詢結(jié)果中的某個(gè)值!=（或<>）ALL 不等于子查詢結(jié)果中的任何一個(gè)值3.3.4嵌套查詢（續(xù)）95[例42]

查詢其他系中比CS系任一學(xué)生年齡小的學(xué)生名單

SELECTSname,SageFROMStudent

WHERESage<ANY

(SELECTSage

--CS系中學(xué)生的年齡

FROMStudent

WHERESdept='CS')

ANDSdept<>'CS'--注意這是父查詢塊中的條件執(zhí)行過程：1.DBMS執(zhí)行此查詢時(shí)，首先處理子查詢，找出

CS系中所有學(xué)生的年齡，構(gòu)成一個(gè)集合(20，22)2.處理父查詢，找所有不是CS系且年齡小于20或

22的學(xué)生3.3.4嵌套查詢（續(xù)）96[例43]

查詢其他系中比CS系所有學(xué)生年齡都小的學(xué)生名單

SELECTSname,Sage

FROMStudent

WHERESage<ALL

(SELECTSage

FROMStudent

WHERESdept=‘CS’)

ANDSdept<>‘CS‘;

3.3.4嵌套查詢（續(xù)）973.3.4嵌套查詢（續(xù)）四、帶有EXISTS謂詞的子查詢EXISTS代表存在量詞

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

*，因?yàn)閹XISTS的子查詢只返回真值或假值，給出列名無實(shí)際意義NOTEXISTS謂詞--結(jié)果為空，返回真，否則假98[例44]

查詢所有選修了1號(hào)課程的學(xué)生姓名涉及Student關(guān)系和SC關(guān)系，我們可以在Student關(guān)系中依次取每個(gè)元組的Sno值，用此Student.Sno值去檢查SC關(guān)系，若SC中存在這樣的元組，其SC.Sno值等于用來檢查的Student.Sno值，并且其SC.Cno=‘1’，則取此Student.Sname送入結(jié)果關(guān)系。將此想法寫成SQL語(yǔ)句就是：

SELECTSname

FROMStudentass

WHEREEXISTS--相關(guān)子查詢

(SELECT*

FROMSC

WHEREsc.Sno=s.SnoANDCno='1');3.3.4嵌套查詢（續(xù)）99[例45]

查詢沒有選修1號(hào)課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.Sno ANDCno='1')；此例用連接運(yùn)算難于實(shí)現(xiàn)

3.3.4嵌套查詢（續(xù)）100P111[例46]

查詢選修了全部課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.Sno ANDCno=Course.Cno))3.3.4嵌套查詢（續(xù)）相關(guān)子查詢?nèi)舨樵兘Y(jié)果不為空，說明還有課程沒有選，在SC中不存在如果不存在這種情況，即選了所有的課程，則輸出該學(xué)生101P111[例46]

查詢選修了全部課程的學(xué)生姓名。SELECTSnameFROMstudentWHERESnoIN(SELECTSnoFROMSCGROUPBYSno----根據(jù)Sno分組，統(tǒng)計(jì)每個(gè)學(xué)生選

----修了幾門課程。如果等于course----的總數(shù)，就是我們要找的SnoHAVINGcount(*)=(SELECTcount(*)FROMcourse

))

----統(tǒng)計(jì)course中共有幾門課程3.3.4嵌套查詢（續(xù)）1023.3.5集合查詢標(biāo)準(zhǔn)SQL直接支持的集合操作種類并操作(UNION)一般商用數(shù)據(jù)庫(kù)支持的集合操作種類并操作(UNION)交操作(INTERSECT)差操作(MINUS)1031.并操作語(yǔ)句形式

<查詢塊> UNION <查詢塊>參加UNION操作的各結(jié)果表的列數(shù)必須相同；對(duì)應(yīng)項(xiàng)的數(shù)據(jù)類型也必須相同3.3.5集合查詢（繼續(xù)）104[例48]

查詢計(jì)算機(jī)科學(xué)系的學(xué)生及年齡不大于19歲的學(xué)生。方法一：

SELECT*FROMStudentWHERESdept='CS'

UNIONSELECT*FROMStudentWHERESage<=19；方法二：

SELECT*FROMStudentWHERE