第03章 關(guān)系數(shù)據(jù)庫標準語言

計算機學院陳豐

chenfeng@

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

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

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

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

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

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

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）固定長度的字符串類型，n表示字符串的最大長度，取值范圍為1～8000varchar（n）可變長度的字符串類型，n表示字符串的最大長度，取值范圍為1～8000Int4字節(jié)，存儲從–231(–2,147,483,648)到231-1(2,147,483,647)范圍的整數(shù)smallint2字節(jié)，存儲從–215(–32,768)到215-1(32,767)范圍的整數(shù)numeric（p,q）或decimal（p,q）定點精度和小數(shù)位數(shù)。使用最大精度時，有效值從–1038+1到1038-1。其中，p為精度，指定小數(shù)點左邊和右邊可以存儲的十進制數(shù)字的最大個數(shù)。q為小數(shù)位數(shù)，指定小數(shù)點右邊可以存儲的十進制數(shù)字的最大個數(shù)，0<=q<=p。q的默認值為0float8字節(jié)，存儲從–1.79E+308到1.79E+308范圍的浮點型數(shù)real4字節(jié)，存儲從–3.40E+38到3.40E+38范圍的浮點型數(shù)datetime占用8字節(jié)空間，存儲從1753年1月1日到9999年12月31日的日期和時間數(shù)據(jù)，精確到百分之三秒（或3.33毫秒）3.2.1域定義121.定義語句格式3.2.2定義基本表CREATETABLE<表名>

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

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

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

Ssexchar(2), Sageint,

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

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

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

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

1)主碼由多個屬性組成時，主屬性用括號

2)CHECK約束若限制多列之間的取值約束時只能用于列級約束可用于列級、表級約束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)鍵字等。列級完整性約束表級完整性約束173.2.2定義基本表（續(xù)）4.實體完整性約束：用主碼約束（PRIMARYKEY）來保證每個表只有一個PRIMARYKEY約束PRIMARYKEY約束的列值集合必須是唯一的且不允許有空值CREATETABLEstudent(

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

……)CREATETABLESC(

Snochar(9),

Cnochar(10), ……

PRIMARYKEY(Sno,Cno),--主碼由多個屬性構(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ù)庫542數(shù)學2Course193.2.2定義基本表（續(xù)）6.用戶定義完整性約束唯一性約束：UNIQUE非空值約束：NOTNULL默認值約束：DEFAULTCHECK約束：用于限制列的取值在指定的范圍內(nèi)，或約束同一個表某個元組中多個列之間的取值*一個表中可以在一個列或多個列上定義UNIQUE*每個列只有一個DEFAULT約束，允許使用系統(tǒng)提供的值作為默認值*執(zhí)行INSERT語句或UPDATE語句，系統(tǒng)自動檢查

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

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

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

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

CREATETABLEt1( IDchar(4)PRIMARYKEY,

oldvalue

int,

newvalue

int,

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

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

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

[例2]

刪除列

ALTERTABLEStudentDropColumnSentrance

[例3]

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

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

[例4]

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

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

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

刪除Student表

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

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

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

聚集索引、非聚集索引?維護索引

DBMS自動完成?使用索引

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

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

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

刪除Student表的stu_sname索引。

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

關(guān)系數(shù)據(jù)庫標準語言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語言的核心，是數(shù)據(jù)庫中用得最多的操作。SQL語言的所有查詢都是利用SELECT語句完成的。SELECT語句的功能：用于檢索和統(tǒng)計數(shù)據(jù)作用對象：關(guān)系（表）結(jié)果：產(chǎn)生一個新的關(guān)系（表）使用方式：交互式、嵌入式使用3.3.1概述343.3.1概述（續(xù)）1.語句格式：SELECT

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

[例1]

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

查詢?nèi)w學生的學號與姓名。SELECTSno，SnameFROMStudent

[例2]

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

查詢?nèi)w學生的詳細記錄。

SELECTSno,Sname,Ssex,Sage,Sdept

FROMStudent

或

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

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

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

SELECT

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

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

SELECT

Sname

NAME,‘YearofBirth:’

ASBIRTH,2005-SageASBIRTHDAY,

LOWER(Sdept)AS

DEPARTMENT

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

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

Cno，DISTINCTGradeFROMSC正確的寫法

SELECTDISTINCT

Cno，GradeFROMSC

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

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

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

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

SELECTSname，Sdept，SageFROMStudent

WHERESageBETWEEN20AND23該查詢等價于下面的查詢

SELECTSname,Sdept,SageFROMStudent

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

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

<值表>：用逗號分隔的一組取值

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

SELECTSname，Ssex

FROMStudent

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

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

：代表任意單個字符。

相當于Windows、dos等中查文件名的*和？例：查所有姓劉或姓王的學生姓名、學號和性別。

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

LIKE’<匹配串>’

ESCAPE’<換碼字符>’

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

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

SELECT* FROMCourse WHEREcnameLIKE‘DB\_

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

SELECTc1FROMtb

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

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

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

SELECTSno，CnoFROMSC

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

查詢計算機系年齡在20歲以下的學生姓名。

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

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

查詢選修了3號課程的學生的學號及其成績，查詢結(jié)果按分數(shù)降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC

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

SELECTDISTINCTSno FROMSC WHEREGrade>=90

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

統(tǒng)計元組個數(shù)

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

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

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

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

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

求一列值中的最小值

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

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

SELECTCOUNT(*)FROMStudent；

[例27]

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

SELECTCOUNT(DISTINCT

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

計算1號課程的學生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；

[例29]

查詢選修1號課程的學生最高分數(shù)。

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

求各門課程的平均分數(shù)。

SELECTCno，AVG(Grade)asAvg_GrageFROMSCGROUPBYCno；

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

查詢選修了3門以上課程的學生學號。

SELECTSnoFROMSCGROUPBYSnoHAVINGCOUNT(*)>3；

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

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

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

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

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

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

SELECT列表達式1，列表達式2，……，列表達式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)連接是一種常用的連接類型。如果兩個表的相關(guān)字段滿足連接條件，則從兩個表中提取數(shù)據(jù)并組合成新的記錄。用來連接兩個表的條件稱為連接條件或連接謂詞，其一般格式為：

[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>

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

非等值連接（比較運算符：>、<、>=、<=、!=

）。65[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>連接條件中的列名稱為連接字段。連接條件中的各連接字段類型必須是可比的，但不必是相同的。例如，可以都是字符型，或都是日期型；也可以一個是整型，另一個是實型，整型和實型都是數(shù)值型，因此是可比的。但若一個是字符型，另一個是整數(shù)型就不允許了，因為它們是不可比的類型。若連接條件中引用表名1和表名2的同名屬性時，都必

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

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

SELECTs.*,

SC.*

FROMStudentassJOINSCON

s.Sno=SC.Sno

--連接操作

3.3.3連接查詢（續(xù)）Sno重復(fù)出現(xiàn)，第1列為student表的，后一個Sno為SC表的68若在等值連接中把目標列中重復(fù)的屬性列去掉則為自然連接（等值連接的特殊情況）。[例34]查詢學生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ù)）自身連接連接操作不僅可以在兩個表之間進行，也可以是一個表

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

SELECTFIRST.Cno,SECOND.Cpno

FROMCourseFIRST,CourseSECOND

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

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

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

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]

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

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

查詢每個學生的學號、姓名、選修的課程名及成績。SELECT

s.Sno,Sname,Cname,GradeFROMStudentass

JOINSCON

s.Sno=SC.Sno

JOINCourseON

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

HAVING短語的條件中的查詢稱為嵌套查詢或子查詢。子查詢一般跟在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子句通常，嵌套查詢的求解方法是由里向外處理。即每個子查詢在其上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。嵌套查詢使得可以用一系列簡單查詢構(gòu)成復(fù)雜的查詢，從而明顯地增強了SQL的查詢能力。以層層嵌套的方式來構(gòu)造程序正是SQL(StructuredQueryLanguage)中“結(jié)構(gòu)化”的含義所在。有些嵌套查詢可以用連接運算替代79一、帶有IN謂詞的子查詢二、帶有比較運算符的子查詢?nèi)?、帶有ANY（SOME）或ALL謂詞的子查詢四、帶有EXISTS謂詞的子查詢3.3.4嵌套查詢（續(xù)）80一、帶有IN謂詞的子查詢》帶有IN謂詞的子查詢是指父查詢與子查詢之間用IN進行連接，判斷某個屬性列值是否在子查詢的結(jié)果中?！酚捎谠谇短撞樵冎校硬樵兊慕Y(jié)果往往是一個集合，所以謂詞IN是嵌套查詢中最經(jīng)常使用的謂詞。[例39]

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

①確定“劉晨”所在系名

SELECTSdept

FROMStudent

WHERESname='劉晨';

結(jié)果為：

Sdept

----------

IS

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

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=‘劉晨’);本例中的查詢也可以用我們前面學過的表的自身連接查詢來完成。

SELECTS1.Sno，S1.Sname，S1.SdeptFROMStudentS1

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

SELECTSno,Sname

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

FROMStudent取出Sno和SnameWHERESnoIN(SELECTSno

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

FROMSC修了3號課程的學生學號

WHERECnoIN(SELECTCno

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

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

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中的各個子查詢都只執(zhí)行一次，其結(jié)果用于父查詢，子查詢的查詢條件不依賴于父查詢，這類子查詢稱為不相關(guān)子查詢。不相關(guān)子查詢是最簡單的一類子查詢。相關(guān)子查詢：子查詢的查詢條件依賴于父查詢

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

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

SELECTSno,Sname,SdeptFROMStudentWHERESdept

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

查詢與“劉晨”在同一個系學習的學生88需要注意的是，子查詢一定要跟在比較符之后，下列寫法是錯誤的：

SELECTSno,Sname,Sdept

FROMStudent

WHERE(SELECTSdept

FROMStudent

WHERESname='劉晨')=Sdept;

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

SELECTSno,CnoFROMSCx

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

意義為

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

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

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

SELECTSname,SageFROMStudent

WHERESage<ANY

(SELECTSage

--CS系中學生的年齡

FROMStudent

WHERESdept='CS')

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

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

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

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

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引出的子查詢，其目標列表達式通常都用

*，因為帶EXISTS的子查詢只返回真值或假值，給出列名無實際意義NOTEXISTS謂詞--結(jié)果為空，返回真，否則假98[例44]

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

SELECTSname

FROMStudentass

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

(SELECT*

FROMSC

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

查詢沒有選修1號課程的學生姓名。

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

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

查詢選修了全部課程的學生姓名。

SELECTSnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS

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

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

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

))

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

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

查詢計算機科學系的學生及年齡不大于19歲的學生。方法一：

SELECT*FROMStudentWHERESdept='CS'

UNIONSELECT*FROMStudentWHERESage<=19；方法二：

SELECT*FROMStudentWHERE