數(shù)據(jù)庫課件DBS：第三章 關(guān)系數(shù)據(jù)庫語言SQL

3.1SQL的體系結(jié)構(gòu)3.2SQL的數(shù)據(jù)定義3.3SQL的數(shù)據(jù)查詢3.4SQL的數(shù)據(jù)更新3.5視圖的定義和對視圖的操作3.6嵌入式SQL

第三章關(guān)系數(shù)據(jù)庫語言SQL第三章關(guān)系數(shù)據(jù)庫語言SQL第三章關(guān)系數(shù)據(jù)庫語言SQL

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)3.1SQL的體系結(jié)構(gòu)

SQL：StructuredQueryLanguage高度非過程化語言→各種RDBMS都支持，但有差異

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)3.1.1SQL的產(chǎn)生和發(fā)展

1989年ISO公布SQL891974年由Boyce和Chamberlin提出，IBM的SystemR上實現(xiàn)SEQUEL(結(jié)構(gòu)化英語查詢語言)1986年10月ANSI公布第一個SQL標準SQL86

1987年被ISO接受并公布為SQL標準SQL861992年ISO公布SQL92→SQL21999年ISO公布SQL3(增加了面向?qū)ο蠹坝|發(fā)器功能)1972年

IBM研制SYSTEMR,配查詢語言SQUARE1970年

E.F.Codd提出關(guān)系模型

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)3.1.2SQL數(shù)據(jù)庫的體系結(jié)構(gòu)

——三級結(jié)構(gòu)基本表1基本表2基本表3基本表4關(guān)系模式存儲文件1存儲文件2存儲文件3存儲文件4存儲模式視圖1視圖2子模式用戶1用戶2用戶3用戶4

要點：p75

*一個SQL模式(Schema)是表和約束的集合。

*一個SQL表(Table)由行集組成，行是列的序列，每列對應(yīng)一個數(shù)據(jù)項

*表有三種類型：

基表：實際存儲在數(shù)據(jù)庫中的表，也稱基本表

視圖：由若干基表或視圖構(gòu)成的表的定義→虛表

是邏輯定義，無具體數(shù)據(jù)同一基表可為不同用戶提供不同的視圖視圖增強了數(shù)據(jù)獨立性，有利于安全，方便了用戶

導(dǎo)出表：執(zhí)行查詢后產(chǎn)生的表

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)

要點(續(xù))：p75

*一個SQL基表可以跨越一個或多個存儲文件，每個存儲文件與外存上的一個物理文件對應(yīng)

*用戶可以用SQL語句對基表和視圖進行查詢，但對視圖的其它操作受限

*SQL用戶類型：應(yīng)用程序、終端用戶

*SQL中未使用術(shù)語：關(guān)系、數(shù)據(jù)庫

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)

數(shù)據(jù)定義語言DDL――用于定義、撤銷和修改數(shù)據(jù)模式

SQL模式、基表、視圖、索引

查詢語言DQL――用于查詢數(shù)據(jù)數(shù)據(jù)操縱語言DML――用于增、刪、改數(shù)據(jù)數(shù)據(jù)控制語言DCL――用于數(shù)據(jù)訪問權(quán)限的控制完整性規(guī)則描述事務(wù)控制等

按功能分為四大部分：

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)3.1.3SQL的組成交互式SQL（ISQL）→單獨使用的SQL，不能編程嵌入式SQL

→將SQL嵌入其它程序設(shè)計語言(主語言)中兩種使用方法：

數(shù)據(jù)定義語言DDL數(shù)據(jù)操縱語言DML：查詢語言QL＋更新語言數(shù)據(jù)控制語言DCL說明：教材中：SQL的組成嵌入式SQL的使用規(guī)定

第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)程序設(shè)計語言—(宿)主語言SQL語句對數(shù)據(jù)庫進行(訪問)操作宿主語言對數(shù)據(jù)進行計算、處理、流程控制

3.1SQL的體系結(jié)構(gòu)3.2SQL的數(shù)據(jù)定義3.3SQL的數(shù)據(jù)查詢3.4SQL的數(shù)據(jù)更新3.5視圖的定義和對視圖的操作3.6嵌入式SQL

第三章關(guān)系數(shù)據(jù)庫語言SQL補充：教學(xué)中書寫語句所用符號的說明

a

：

a為任選項，可有可無a

b

：可在a、b選一個<a

b>：必須取a、b之一

a

：

a為必選項a

：帶下劃線的a為缺省選項例:[asc

desc]，不寫，則默認為升序

：其中內(nèi)容可重復(fù)0次～多次

第三章關(guān)系數(shù)據(jù)庫語言SQL補充；：不是語句的組成部分，但有些DBMS需以此表示語句結(jié)束3.2SQL的數(shù)據(jù)定義一、SQL模式的創(chuàng)建和撤銷二、基本數(shù)據(jù)類型三、基表的創(chuàng)建、修改和撤銷四、索引的創(chuàng)建和撤銷

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義3.2.1SQL模式的創(chuàng)建和撤銷1.SQL模式的創(chuàng)建

CREATESCHEMA<模式名>AUTHORIZATION<用戶名>

SQL模式由模式名和擁有者的用戶名或帳號確定2.SQL模式的撤銷

DROPSCHEMA<模式名>[CASCADE

｜RESTRICT]

CASCADE：該模式下的數(shù)據(jù)庫對象隨模式而撤消

RESTRICT：只當該模式下無元素時才可撤消CREATESCHEMATLAUTHORIZATIONU1；DROPSCHEMATLCASCADE；數(shù)據(jù)類型說明符備注整數(shù)INTEGER字長32位短整數(shù)SMALLINT字長16位十進制數(shù)NUMBERIC(m,n)m為總位數(shù)（不包括小數(shù)點），n為小數(shù)位數(shù)浮點數(shù)FLOAT一般指雙精度浮點數(shù)，即字長64位定長字符串CHAR(n)按固定長度n存儲字符串。如果實際長度小于n，后面填空格；如果實際字符串長大于n，則報錯變長字符串VARCHAR(n)按實際字符串長度存儲，超過n否則報錯日期DATE格式為“yyyymmdd”，年份為0000至9999；月份，為1至12；dd為1至31時間TIME格式為“hhmmss”，hh表示時，范圍為0至24；mm表示分，ss表示秒，范圍都是0至59時標TIMESTAMP格式為“yyyymmddhhmmssnnnnnn”,nnnnnn表示微秒，范圍為0至999999，其它符號意義同上位串型BIT(n)時間間隔INTERVAL

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義3.2.2基本的數(shù)據(jù)類型自學(xué)注意：不同產(chǎn)品會不同(1)基本句法：

(2)域完整性約束條件

CREATETABLE<表名>

(<列名><類型>[域完整性約束條件]{,<列名><類型>[域完整性約束條件]}[,<表級完整性約束條件>])

；

――三個任選項

NOTNULL

→

此列不得取NULL，主鍵可不指出

UNIQUE→列值唯一，不得重復(fù)

DEFAULT→此列的值空缺時，由系統(tǒng)填該缺省值

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義一、基表的創(chuàng)建－－CREATETABLE

3.2.3基表的創(chuàng)建、修改和撤銷例1：定義有關(guān)學(xué)生信息的STUDENT基表。

CREATETABLESTUDENT

(SNOCHAR(7)NOTNULL，SNAMEVARCHAR(8)NOTNULL,SEXCHAR(2)NOTNULL,BDATEDATENOTNULL,HEIGHTDEC(5,2)DEFAULT000.00,…);

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義(3)表級完整性約束條件(主鍵子句,外鍵子句,檢查子句)

作用：提供實體完整性約束的說明每表一個主鍵(任選項)格式：PRIMARYKEY(<列名表>)

列名表中的各列必須滿足NOTNULL和唯一性系統(tǒng)一般自動在主鍵上建索引并在插入新行時做唯一性檢查

主鍵為單屬性時：

可直接在屬性定義后用PRIMARYKEY

主鍵(PRIMARYKEY)子句

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義例：CREATETABLESTUDENT(SNOCHAR(7)NOTNULL，

…

或CREATETABLESTUDENT(SNOCHAR(7)

PRIMARYKEY，…);

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義，PRIMARYKEY(SNO));

作用：提供引用完整性約束的說明每表可有0～多個外鍵（任選項）可以附加引用完整性約束選項[ONDELETE]外鍵(FOREIGNKEY)子句

格式：FOREIGNKEY[外鍵名]

(<列名序列1>)P79,236REFERENCES

<主表名>[(列名序列2)][ONDELETE

<RESTRICT｜CASCADE｜SETNULL>]

從表的列隨主表主鍵的刪除設(shè)為NULL，該列應(yīng)無NOTNULL說明從表的行隨主表主鍵的刪除而刪除被引用的主鍵不得刪除

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義可給外鍵另取一個標識名不需寫出例如：說明分數(shù)GRADE應(yīng)取NULL或0～100之間的整數(shù)值

CREATETABLESC(SNOCHAR(7)NOTNULL，…，

GRADESMALLINT,可選的檢查(CHECK)子句

作用：對某元組某屬性的取值的約束說明(例：成績)

格式：CHECK(約束表達式)CHECK((GRADEISNULL)OR(GRADEBETWEEN0AND100)));

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義

(4)舉例

例1：定義基表學(xué)生STUDENT、課程COURSE、選課SC。CREATETABLESTUDENT

(SNOCHAR(7)NOTNULL，

SNAMEVARCHAR(8)NOTNULL,

SEXCHAR(2)NOTNULL,

BDATEDATENOTNULL,

HEIGHTDEC(5,2)DEFAULT000.00,

PRIMARYKEY(SNO));

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義STUDENT(SNO,SNAME,SEX,BDATE,HEIGHT)COURSE(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)

CREATETABLECOURSE

(CNO

CHAR(6)NOTNULL,

LHOUR

SMALLINTNOTNULL,

CREDIT

DEC(1,0)NOTNULL,

SEMESTER

CHAR(2)NOTNULL,

PRIMARYKEY(CNO))；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義CREATETABLESC

(SNO

CHAR(7)NOTNULL,

CNO

CHAR(6)NOTNULL,

GRADE

DEC(4,1)DEFAULTNULL,

PRIMARYKEY

(SNO,CNO),

FOREIGNKEY

(SNO)

REFERENCESSTUDENTONDELETECASCADE,

FOREIGNKEY

(CNO)

REFERENCESCOURSE

ONDELETE

RESTRICT);

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義(5)說明

數(shù)據(jù)庫對象(基表、視圖等)都有其擁有者(用戶or模式)

其它用戶對該對象的訪問受限，并且要說明表創(chuàng)建者：

<表的創(chuàng)建者名>.<表名〉

用CREATE語句創(chuàng)建的基表，只是一個空框架需裝入數(shù)據(jù)才可使用數(shù)據(jù)的裝入可用

Insert命令、數(shù)據(jù)裝載程序

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義

復(fù)制某表結(jié)構(gòu)的命令如下：

createtable表名asselect*from

(1)增加屬性ALTERTABLE

[<表的創(chuàng)建者名>.]<基表名>

ADD

<屬性名><類型>；*對該表的創(chuàng)建者本身，可略去[<表的創(chuàng)建者名>.](2)刪除屬性ALTERTABLE

<基表名>DROP<屬性名>

[CASCADE

｜RESTRICT]

CASCADE：引用該列的視圖和約束隨之刪除

RESTRICT：只在沒有被引用時才可刪除

*不是所有的DBMS都支持

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義二、基表結(jié)構(gòu)的修改(列.主鍵.外鍵.別名)--ALTERTABLE

(4)補充定義主鍵

ALTERTABLE<表名>

ADDPRIMARYKEY(<列名表>)；*可用于在插入大批數(shù)據(jù)時，暫時撤銷主鍵定義，提高系統(tǒng)效率

*被定義為主鍵的屬性名必須滿足NOTNULL和唯一性條件

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義ALTERTABLE

<基表名>MODIFY

<列名><新類型>；(3)修改屬性類型

(5)撤銷主鍵定義

ALTERTABLE

<表名>

DROPPRIMARYKEY；

*可用于在插入大批數(shù)據(jù)時，暫時撤銷主鍵定義，提高系統(tǒng)效率

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義

ALTERTABLE

<從表名>

ADDFOREIGNKEY[<外鍵名>](<列名表>)

REFERENCES

<主表名>

[ONDELETE<RESTRICT|CASCADE|SETNULL>];(6)補充定義外鍵

(7)撤銷外鍵定義

ALTERTABLE

<表名>

DROP

<外鍵名>；

*供必要時暫時撤銷外鍵之用

(8)定義和撤銷別名

別名(alias)――用簡名代替全名，方便書寫和輸入

例如：可以簡寫“表創(chuàng)建者.表名”

對同一數(shù)據(jù)對象，可定義不同的別名

例如：工資、薪水、薪金表示同一對象

句法：

定義CREATESYNONYM

<標識符>

FOR<表創(chuàng)建者>.<表名|視圖名>；

撤銷

DROPSYNONYM<標識符>；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義DROP

TABLE<表名>[CASCADE｜RESTRICT]

；*注意與DELETE的區(qū)別三、基表的撤銷

－－DROPTABLE

撤銷該基本表時，所有引用該基本表的視圖和約束一起自動被撤銷沒有視圖和約束引用該基本表時，才能撤銷

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義索引鍵按降序排列創(chuàng)建：

CREATE[UNIQUE]INDEX索引名

ON基表名(<列名

>[ASC

DESC][{，<列名>[ASC

DESC]}])；索引鍵按升序排列(1)索引

――是物理存取路徑，不屬于邏輯數(shù)據(jù)模式

RDBMS通常在主鍵上自動建立索引查詢、更新時自動起作用索引屬性名，也稱索引鍵名DROPINDEX<索引名列表>；撤銷：(2)句法：

可在多屬性上建立索引每個屬性排序可不同

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義3.2.4索引的建立和撤銷

每個索引鍵值只能對應(yīng)一個元組例：主鍵或候補鍵

(3)舉例

例1：在基表STUDENT的屬性HEIGHT上建升序索引

例2：在基表SC的屬性SNO和CNO上建索引，分別采用降序和升序，索引鍵取值唯一

CREATEUNIQUEINDEXSC_INDEX

ONSC(SNODESC,CNOASC)；

例3：撤銷索引H_INDEXDROPINDEXH_INDEX；

CREATEINDEX

H_INDEX

ON

STUDENT(HEIGHT)；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.2SQL的數(shù)據(jù)定義3.1SQL的體系結(jié)構(gòu)3.2SQL的數(shù)據(jù)定義3.3SQL的數(shù)據(jù)查詢3.4SQL的數(shù)據(jù)更新3.5視圖的定義和對視圖的操作3.6嵌入式SQL

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢一、基本SQL查詢語句(1)

基本句型

SELECT－FROM－WHERE

SELECT子句：指明要查詢的項目:列名或表達式FROM子句：指明被查詢的表或視圖名WHERE子句：說明查詢的條件*只有SELECT和FROM子句是每個SQL

查詢語句所必需的

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

(2)語義SELECT

A1，…，An

FROM

R1

，…，RnWHERE

F;*在關(guān)系R1

，…，Rn中查詢符合條件F的元組中的屬性

A1，…，An的值，其結(jié)果仍是一張二維表

ΠA1，…，An(σF(R1

…

Rn))

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢P82

教材舉例所用基本模式教師關(guān)系T(T#,TNAME,TITLE)課程關(guān)系C(C#,CNAME,T#)選課關(guān)系SC(S#,C#,SCORE)學(xué)生關(guān)系S(S#,SNAME,AGE,SEX)說明：

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例1：寫出查詢S表中男生的學(xué)號S#

和姓名SNAME的SQL語句。

SELECT

S#，SNAME

FROM

SWHERE

SEX=‘男’；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例2：P83例3.9①檢索選修C2號課程的學(xué)生的學(xué)號

S#和成績SCORE。SELECT

S#，SCORE

FROM

SCWHERE

C#=‘C2’；二、完整句法

(1)

句法

SELECT

<目標表的列名或列表達式>FROM

<基表和視圖名列表>

[WHERE

<行條件表達式>]

行條件子句[GROUPBY

<列名序列>

分組子句[HAVING

<組條件表達式>]

]

組條件子句[ORDERBY<列名>[ASC

DESC]

]

排序子句

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(2)執(zhí)行過程P88

讀取FROM子句中基表、視圖的數(shù)據(jù)，執(zhí)行‘×’選取滿足WHERE子句中所給條件表達式的元組按GROUPBY子句中指定列的值將元組分組選滿足HAVING子句中組條件表達式的那些組按SELECT子句中所給的列或列表達式求值輸出按ORDERBY子句對輸出的目標表進行排序

④SELECT①FROM

②WHERE

③GROUPBY

③HAVING

⑤ORDERBY

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢?nèi)?、有關(guān)限定

(1)通常每條語句以‘；’結(jié)束

(2)表達式的組成及元素

表達式：項

[+│-

│

*

│/]

項項：[基表名.│視圖名.]<列名>

常量特殊常量:

USER，DATE，TIME，TIMESTAMP，TIMEZONE

標量函數(shù):日期、時間YEAR()，MONTH()，DAY()，HOUR()等

字符運算及數(shù)據(jù)類型轉(zhuǎn)換函數(shù)：LENGTH()，DEC()等

聚集函數(shù):COUNT()，SUM()，AVG()，MAX()，MIN()

(聚合函數(shù))

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢聚集函數(shù)P87COUNT(*)：

計算二維表的元組數(shù)COUNT([DISTINCT|ALL]<列名>)：計算該列的(不同值的)個數(shù)SUM([DISTINCT|ALL]<列名>)：求該列的值的總和(限數(shù)值型列)AVG([DISTINCT|ALL]<列名>)：求該列的值的平均值(同上)MAX(<列名>)：

求該列的值中的最大值MIN(<列名>)：

求該列的值中的最小值

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢*

：所有列例：查詢S，C，SC三表的全部內(nèi)容

查詢S表的全部內(nèi)容

SELECT*FROMS；9309203歐陽美林女1975-06-031.629208123王義平男1974-08-201.719104421周遠行男1973-07-161.839309119李維女1976-08-101.689209120王大力男1973-10-201.75S#SNAMESEXBDATEHEIGHT(3)SELECT子句中目標表的列名或列表達式

查詢C表的全部內(nèi)容

SELECT*FROMC；CS-110603秋CS-201804春CS-221402秋EE-1221065秋EE-201452春C#LHOURCREDITSEMESTER

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢查詢SC表的全部內(nèi)容

SELECT*FROMSC；

查詢結(jié)果中‘？’代表NULL

9309203CS-11082.59309203CS-20180.09309203EE-20175.09308123EE-12291.09308123EE-20183.09104421EE-201100.09104421CS-11091.09309119CS-11072.09309119CS-20165.09309120CS-221?S#C#SCORE

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

[<基表名>

<視圖名>.]<列名表>：

多表查詢時區(qū)分同名列

例：

SELECTSNAME，S.S#，

SCOREFROMS，SCWHERE…

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢*

：所有列(3)SELECT子句中目標表的列名或列表達式

[<基表名>

<視圖名>.]<列名表>：

多表查詢時區(qū)分同名列

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢*

：所有列(3)SELECT子句中目標表的列名或列表達式

例1：

SELECTS#AS學(xué)號,SANMEAS姓名

FROMSWHERESEX=‘男’;例2：P90例3.14SELECTSNAMEASSTUDENT_NAME,2010－AGEASBIRTH_YEARFROMS;例3：查詢所有女學(xué)生的身高（以厘米表示）SELECTSNAME,100*HEIGHTFROMSWHERESEX=‘女’；

<表達式>→列名、常數(shù)、運算符(＋－×/)→列名AS別名改名，AS可省

[<基表名>

<視圖名>.]<列名表>：

多表查詢時區(qū)分同名列

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢*

：所有列(3)SELECT子句中目標表的列名或列表達式

<表達式>→列名、常數(shù)、運算符(＋－×/)→列名AS別名改名，AS可省→常數(shù)：例：SELECTS#,‘birth_year:’

FROMS

[<基表名>

<視圖名>.]<列名表>：

多表查詢時區(qū)分同名列

*

：所有列(3)SELECT子句中目標表的列名或列表達式

<表達式>→列名、常數(shù)、運算符(＋－×/)→列名AS別名改名，AS可省→常數(shù)→聚集函數(shù)(列名)

，不允許對聚集運算復(fù)合使用例1：求男生的總?cè)藬?shù)和平均年齡

SELECTCOUNT(*)，AVG(AGE)FROMS

WHERESEX=‘M’;例3：求選修了課程的學(xué)生人數(shù)

SELECTCOUNT(DISTINCTS#)FROMSC

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(4)FROM子句中的限定

--可以為表和視圖取一只在本句中有效的別名(改名)例：檢索至少選修C2和C4號課程的學(xué)生學(xué)號

π1(

σ1=4∧2=‘c2’∧5=‘c4’(SC×SC))

SELECTX.S＃

FROMSCASX，SCASYX,Y→元組變量

WHEREX.S＃=Y.S＃AND

X.C＃=‘C2’ANDY.C＃=‘C4’；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(4)FROM子句中的限定

--可以為表和視圖取一只在本句中有效的別名(改名)--可以使用導(dǎo)出表→子查詢需給查詢的結(jié)果賦表名和相應(yīng)的列名P94例3.21

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(5)

條件表達式F――簡單條件，復(fù)合條件，基于多表的查詢P91

WHERE子句的F中可使用的運算符：

算術(shù)比較θ

：…構(gòu)造簡單條件

邏輯運算：…構(gòu)造復(fù)合條件

集合成員資格運算：IN、NOTIN

謂詞：EXISTS(存在量詞)、ALL、SOME、UNIQUE

子查詢：SELECT…WHERE子句的F中不可使用聚集函數(shù)(在有GROUP子句時)

◆

WHERE子句不支持含聚集函數(shù)的比較表達式

HAVING允許聚集函數(shù)做比較對象

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

(a)簡單條件――比較、BETWEEN、LIKE、IN、EXISTS*比較條件(3種形式):<表達式>IS[NOT]NULL空值的比較

算術(shù)運算有一值為空→結(jié)果為空

例：查在S中檢索年齡為空的學(xué)生姓名

SELECTSNAMEFROMSWHEREAGE

ISNULL；

<表達式>θ<表達式>(字符大小與編碼有關(guān))

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例：查詢小于18歲的學(xué)生姓名

SELECTSNAMEFROMSWHERE

YEAR(BDATE)<1976；

(5)

條件表達式F――簡單條件，復(fù)合條件，基于多表的查詢

<表達式或集合>

θ<ALL

SOME>(<子查詢塊>或集合)

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例：查詢不學(xué)C2課程的學(xué)生姓名

SELECTSNAMEFROMSWHERE

S#<>ALL(SELECTS#FROMSC

WHEREC#=‘C2’）；

例：查詢1974~1976年出生的學(xué)生姓名

SELECTSNAMEFROMSTUDENTWHEREYEAR(BDATE)

BETWEEN1974AND1976；若：

1974≤YEAR(BDATE)

≤1976×

若：

YEAR(BDATE)≥1974andYEAR(BDATE)≤1976√*

BETWEEN條件――限定取值范圍

<表達式1>[NOT]BETWEEN<表達式2>AND<表達式3>

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例：列出計算機系所開課程（課程號以CS開頭）的最高成績和平均成績

SELECTC#,MAX(SCORE),AVG(SCORE)FROMSCWHEREC#

LIKE

‘CS%’GROUPBYC#；

*

LIKE條件――字符串匹配用<列名>[NOT]LIKE“匹配字符串”“_”表示任意單字符

“CS-1_0”“%”表示任意長字符串“CS%”匹配符：

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

*

IN條件

<表達式或元組>[NOT]IN(<常數(shù)集>

<子查詢>)例2：查詢秋季學(xué)期選了一門以上T1老師教的課程的學(xué)生學(xué)號

SELECTS#

FROMSC

WHEREC#

IN

(SELECTC#

FROMC

WHERET#=‘T1’）；例1：查詢在CS-110和CS-210課程中至少選了一門的學(xué)號

SELECTS#FROMSCWHEREC#

IN(‘CS-110’,’CS-210’)；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

*

EXISTS條件

[NOT]EXISTS(<子查詢>)：子查詢非空為真

例：查詢選了課的學(xué)生姓名

SELECTSNAMEFROMSWHERE

EXISTS

(SELECT*FROMSCWHERES#=S.S#)；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

*

UNIQUE條件

[NOT]UNIQUE(<子查詢或集合>)無重復(fù)為真例：查只開一門課程的教師姓名(例3.20)

SELECTTNAME

FROMT

WHEREUNIQUE(SELECTTNAME

FROMC

WHEREC.T#=T.T#)；P84例3.8⑥查詢不學(xué)C2號課程的學(xué)生姓名與年齡

SELECTSNAME，AGE

FROMS

WHERE

S#

NOTIN(SELECTS#

FROMSC

WHEREC#=‘C2’)；或：SELECTSNAME，AGEFROMSWHERE

NOTEXISTS(SELECT*

FROMSC

WHERE

SC.S#=S.S#ANDC#=‘C2’);或：P93例3.19③

SELECTSNAME，AGE

FROMS

WHERE

S#<>ALL(SELECT

S#

FROM

SC

WHEREC#=‘C2’）；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(b)復(fù)合條件――簡單條件用邏輯運算符連接而成

[NOT]<簡單條件>＜AND

OR＞<簡單條件>

例2：查1976年出生的學(xué)生姓名及其秋季所修課程的課程號與成績

SELECTSNAME，COURSE.CNO，GRADEFROMSTUDENT，COURSE，SCWHERESTUDENT.SNO=SC.SNO

ANDSC.CNO=COURSE.CNO

ANDYEAR(BDATE)=1976

ANDSEMESTER=’秋’；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例1：P84例3.9④SELECTS.S#，SNAMEFROMS，SCWHERES.S#=SC.S#ANDC#=‘C2’；(c)基于多表的查詢－－多種實現(xiàn)方法

例：P82例3.8查詢選修了C2號課程的學(xué)生的姓名與學(xué)號SELECTS#，SNAMEFROMSWHERES#IN(SELECT

S#

FROMSCWHEREC#=‘C2’)；SELECTS#，SNAMEFROMSWHEREEXISTS(SELECT

*

FROMSC

WHERESC.S#=S.S#ANDC#=‘C2’)；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢解法1：連接查詢拼接后按條件篩選解法2：嵌套查詢在SC中找到選C2的學(xué)號，再在S中找姓名解法3：使用存在量詞的嵌套查詢對S的各學(xué)號，在SC中找

四、嵌套查詢

一個SELECT-FROM-WHERE語句稱為一個查詢塊。嵌套查詢:將一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢例如：

SELECTSNAMEFROMSWHERES#IN

(SELECTS#FROMSCWHEREC#=‘SC-101’);父查詢子查詢

等價的連接查詢：

SELECTSNAMEFROMS,SCWHERES.S#=SC.S#ANDC#=‘SC-101’;

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢補充：關(guān)于嵌套查詢(子查詢)的注意事項：

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢◆

輸出為中間結(jié)果，不需存儲和排序，

不使用ORDERBY

◆

查詢條件中仍可嵌入子查詢塊，層數(shù)僅受DBMS的限制◆

一般，嵌套查詢的代價比連接查詢要小，效率高◆

EXISTS、IN、連接有時可替代，又各有應(yīng)用場合，可用EXISTS和IN處可用連接，其否定形式不成立例:P99例3.8⑥、P108例3.18③

查詢不學(xué)C2號課程的學(xué)生姓名與年齡

SELECTSNAME，AGEFROMSWHERE

S#NOTIN(SELECTS#

FROMSC

WHEREC#=‘C2’)；

可用：NOTEXISTS、<>ALL

無法用：連接查詢(大家想一想？)

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(1)帶有IN謂詞的子查詢

常用于：需求中有“至少”，父查詢結(jié)果中的列屬于單個表,

子查詢項目為一列或表達式。公共屬性隱含相等例：查詢選修了課程名為“數(shù)據(jù)庫系統(tǒng)”的學(xué)生學(xué)號和姓名

SELECTS#,SANME→屬單表

FROMSWHERES#IN(SELECTS#

→單列

FROMSCWHEREC#IN(SELECTC#FROMCWHERECNAME=“數(shù)據(jù)庫系統(tǒng)”));

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(2)帶有SOME或ALL謂詞的子查詢使用SOME或ALL謂詞必須同時使用比較運算符。其語義為：

>SOME

大于子查詢結(jié)果中的某個值(<,<=,=以此類推)

>ALL

大于子查詢結(jié)果中的所有值

<>SOME

不等于子查詢結(jié)果中的某個值

<>ALL

不等于子查詢結(jié)果中的所有值例：查詢其它系中比IS系所有學(xué)生年齡都小的學(xué)生姓名及年齡

SELECTSNAME,AGEFROMSWHEREAGE<ALL(SELECTAGEFROMSWHERESDEPT=‘IS’)

ANDSDEPT<>‘IS’;

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(3)帶有EXISTS謂詞的子查詢

EXISTS代表存在量詞

→需求中含“全部”或否定帶有EXISTS謂詞的子查詢不返回任何數(shù)據(jù)，只產(chǎn)生邏輯真或假

子查詢中要顯式指出公共屬性相等

例：↘

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例1:查詢所有選修了‘CS-110’

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

SELECTSNAMEFROMSWHEREEXISTS(SELECT*FROMSCWHERES#=S.S#ANDC#=‘CS-110’);例2:查詢沒有選修‘CS-110’號課程的學(xué)生姓名

SELECTSNAMEFROMSWHERENOTEXISTS(SELECT*FROMSCWHERES#=S.S#ANDC#=‘CS-110’);

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例3：P85例3.9⑦查詢選修了全部課程的學(xué)生姓名

SQL中沒有全稱量詞把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞：

(∨x)P≡(

x(P))

本題轉(zhuǎn)換成等價的存在量詞形式：

查詢這樣的學(xué)生姓名，沒有一門課程是他沒選修的

SELECTSNAMEFROMSWHERENOTEXISTS

(

SELECT*

FROMC

WHERE

NOTEXISTS

(

SELECT*

FROMSC

WHERES#=S.S#

ANDC#=C.C#));

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢對S中的每個元組依次取C中的每個元組每次按S的S#和C的C#

在SC中找相應(yīng)元組若每次的結(jié)果都不為空則S＃中對應(yīng)的SNAME

為所選

五、GROUPBY和ORDERBY子句的應(yīng)用

GROUPBY<列名表>[HAVING<條件表達式>]

將表按列名表中列的值分組

如果GROUPBY后有多個列名，則先按第一列名分組，再按第二列名在組中分組，直到分出所指明的列都具有相同值的基本組

HAVING后的條件是選擇基本組的條件，支持聚集函數(shù)，而WHERE子句不支持聚集函數(shù)

使SELECT子句中的聚集函數(shù)對組計算

限定：加了GROUPBY子句后，SELECT子句的<列表達式>中只能取聚集函數(shù)或GROUPBY子句指的列

(1)

GROUPBY子句

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢SELECTS#，AVG(SCORE)FROMSCGROUPBYC#ORDERBYC#；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢×思考：以下語句是否正確？(2)ORDERBY子句

ORDERBY<列名

列序號>[ASC

DESC][｛,<列名︱列序號>[ASC︱DESC]｝]

對查詢結(jié)果按子句中指定的列的值排序

如果ORDERBY后有多個列名，則先按第一列名排序，再對于具有相同第一列值的各行，按第二列名排序，

列序號是在SELECT子句中出現(xiàn)的序號(選的列是聚集函數(shù)或表達式時要用)

ASC表示升序，DESC表示降序，缺省時表示升序

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例1：列出計算機系所開課程（課程號以CS開頭）的最高成績，最低成績和平均成績。若某門課程的成績不全（即GRADE

中有NULL出現(xiàn)），則該課程不予統(tǒng)計，結(jié)果按C#升序排列。

SELECTC#，MAX(SCORE)，MIN(SCORE)，AVG(SCORE)FROMSCWHEREC#LIKE‘CS%’GROUPBYC#HAVINGC#NOTIN(SELECTC#FROMSCWHERESCOREISNULL)ORDERBYC#；

(2)然后按C＃分組(3)再按HAVING子句條件刪去成績不全的組(4)最后按SELECT子句列出所需結(jié)果(4)按C＃升序排序(1)先按WHERE子句的條件選出計算機系所開的課程

語義↗：結(jié)果:↘

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢

CS-201

C＃

CS-110MAX(SCORE)91.080.0MIN(SCORE)

72.0

65.0AVG(SCORE)

81.8

72.5回顧:SELECT*FROMSC；

查詢結(jié)果中‘？’代表NULL9309203CS-11082.59309203CS-20180.09309203EE-20175.09308123EE-12291.09308123EE-20183.09104421EE-201100.09104421CS-11091.09309119CS-11072.09309119CS-20165.09309120CS-221?S#C#SCORE查詢結(jié)果:(1)選出CS開頭的(2)然后按C#分組(3)刪去成績不全的組(4)最后按SELECT子句列出所需結(jié)果,按C＃升序排序

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例2：統(tǒng)計每一年齡選修課程的學(xué)生人數(shù)。

SelectAGE,COUNT(DISTINCTS.S#)

FromS,SC

WHERES.S#=SC.S#

GroupbyAGE；

例3：統(tǒng)計男生每個人數(shù)>50的年齡組的人數(shù)，查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列

SelectAGE,COUNT(S#)

FromS

WHERESEX=‘M’

GroupbyAGEHavingCOUNT(*)>50

Orderby

2,AGEDesc；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢例4：查詢平均成績超過80分的學(xué)生的學(xué)號和平均成績

SelectS#,AVG(SCORE)

FromSC

GroupbyS#

HavingAVG(SCORE)>80；

第三章關(guān)系數(shù)據(jù)庫語言SQL3.3SQL的數(shù)據(jù)查詢(

)

ASRESULT(S#,AVG_GRADE)SELECTS#,AVG_GRADEFROMWHEREAVG_GRADE>80;六、導(dǎo)出表的使用

在FROM子句中使用子查詢，要給子查詢的結(jié)果起表名和相應(yīng)列名P110例3.20

：檢索平均成績超過80分的學(xué)生學(xué)號和平均成績

SELECTS#，AVG（SCORE）

FROMSCGROUPBYS#HAVINGAVG（SCORE）>80；用導(dǎo)出表：

SELEC