spl語言說明教程課件

第5章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言—SQL關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言—SQL5.1SQL概述及特點5.2數(shù)據(jù)定義語句

5.3數(shù)據(jù)查詢語句5.4數(shù)據(jù)更新語句5.5嵌入式SQL5.6數(shù)據(jù)控制機制和語句5.1SQL概述及特點1.SQL的主要功能

(1)數(shù)據(jù)定義功能

定義關(guān)系數(shù)據(jù)庫的模式、外模式和內(nèi)模式，以實現(xiàn)對基本表、視圖以及索引文件的定義、修改和刪除等操作。

(2)數(shù)據(jù)操縱功能

包括數(shù)據(jù)查詢和數(shù)據(jù)更新兩種數(shù)據(jù)操作語句：數(shù)據(jù)查詢指對數(shù)據(jù)庫中的數(shù)據(jù)查詢、統(tǒng)計、分組、排序操作；數(shù)據(jù)更新指數(shù)據(jù)的插入、刪除、修改等數(shù)據(jù)維護(hù)操作。

(3)數(shù)據(jù)控制功能

通過對數(shù)據(jù)庫用戶的授權(quán)和收權(quán)命令來實現(xiàn)有關(guān)數(shù)據(jù)的存取控制，以保證數(shù)據(jù)庫的安全性。SQL功能極強，完成核心功能只用了9個動詞。5.2數(shù)據(jù)定義語句5.2.1基本表的定義和維護(hù)

1.定義基本表

定義基本表語句的一般格式為：CREATETABLE[〈庫名〉]〈表名〉(〈列名〉〈數(shù)據(jù)類型〉[〈列級完整性約束條件〉]，〈列名〉〈數(shù)據(jù)類型〉[〈列級完整性約束條件〉]][，…n]

[，〈表級完整性約束條件〉][，…n])；類型表示類型說明數(shù)值型數(shù)據(jù)SMALLINT半字長二進(jìn)制整數(shù)。15bits數(shù)據(jù)INTEGER或INT全字長（四字長）整數(shù)。31bits數(shù)據(jù)DECIMAL(p[，q])十進(jìn)制數(shù)，共p位，其中小數(shù)點后q位。0≤q≤p，q=0時可省略不寫FLOAT雙字長浮點數(shù)字符型數(shù)據(jù)CHARTER(n)或CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串特殊數(shù)據(jù)類型GRAPHIC(n)長度為n的定長圖形字符串VARGRAPHIC(n)最大長度為n的變長圖形字符串日期時間型DATE日期型，格式為YYYY-MM-DDTIME時間型，格式為HH.MM.SSTIMESTAMP日期加時間(1)SQL支持的數(shù)據(jù)類型(3)表級完整性約束條件涉及到關(guān)系中多個列的限制條件。

1)UNIQUE約束。惟一性約束。

2)PRIMARYKEY約束。定義主碼，保證惟一性和非空性。CONTRAINT〈約束名〉PRIMARYKEY[CLUSTERED](〈列組〉)

3)FOREIGNKEY約束。用于定義參照完整性。

CONTRAINT〈約束名〉FOREIGNKEY(〈外碼〉)

REFERENCES〈被參照表名〉(〈與外碼對應(yīng)的主碼名〉)

CREATETABLE學(xué)生( 學(xué)號CHAR(5)NOTNULLUNIQUE，

姓名CHAR(8)NOTNULL， 年齡SMALLINT，

性別CHAR(2)， 所在系CHAR(20)，

DEFAULTC120FOR年齡，

CONSTRAINTC2CHECK(性別IN(‘男’，‘女’)))；

建立基本表：學(xué)生(學(xué)號，姓名，年齡，性別，所在系)；

課程(課程號，課程名，先行課)；

選課(學(xué)號，課程號，成績).CREATETABLE選課( 學(xué)號CHAR(5)， 課程號CHAR(5)，

成績SMALLINT，

CONSTRAINTC3CHECK(成績BETWEEN0AND100)，

CONSTRAINTC4PRIMARYKEY(學(xué)號，課程號)，

CONSTRAINTC5FOREIGNKEY(學(xué)號)REFERENCES學(xué)生(學(xué)號)，

CONSTRAINTC6FOREIGNKEY(課程號)REFERENCES課程(課程號))；實體完整性定義關(guān)系模型的實體完整性CREATETABLE中用PRIMARYKEY定義單屬性構(gòu)成的碼有兩種說明方法定義為列級約束條件定義為表級約束條件對多個屬性構(gòu)成的碼只有一種說明方法定義為表級約束條件實體完整性定義(續(xù))(2)在表級定義主碼CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(20)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，

PRIMARYKEY(Sno));實體完整性定義(續(xù))［例］將SC表中的Sno，Cno屬性組定義為碼CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)/*只能在表級定義主碼*/);實體完整性檢查和違約處理(續(xù))檢查記錄中主碼值是否唯一的一種方法是進(jìn)行全表掃描實體完整性檢查和違約處理(續(xù))索引參照完整性定義關(guān)系模型的參照完整性定義在CREATETABLE中用FOREIGNKEY短語定義哪些列為外碼用REFERENCES短語指明這些外碼參照哪些表的主碼參照完整性定義(續(xù))例如，關(guān)系SC中一個元組表示一個學(xué)生選修的某門課程的成績，（Sno，Cno）是主碼。Sno，Cno分別參照引用Student表的主碼和Course表的主碼［例］定義SC中的參照完整性CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，/*在表級定義實體完整性*/FOREIGNKEY(Sno)REFERENCESStudent(Sno)，/*在表級定義參照完整性*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)

/*在表級定義參照完整性*/);違約處理參照完整性違約處理1.拒絕(NOACTION)執(zhí)行默認(rèn)策略2.級聯(lián)(CASCADE)操作3.設(shè)置為空值（SET-NULL）對于參照完整性，除了應(yīng)該定義外碼，還應(yīng)定義外碼列是否允許空值違約處理(續(xù))［例］顯式說明參照完整性的違約處理示例CREATETABLESC(SnoCHAR(9)NOTNULL，CnoCHAR(4)NOTNULL，GradeSMALLINT，PRIMARYKEY（Sno，Cno）， FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*級聯(lián)刪除SC表中相應(yīng)的元組*/ONUPDATECASCADE，/*級聯(lián)更新SC表中相應(yīng)的元組*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*當(dāng)刪除course表中的元組造成了與SC表不一致時拒絕刪除*/ONUPDATECASCADE /*當(dāng)更新course表中的cno時，級聯(lián)更新SC表中相應(yīng)的元組*/)；用戶定義的完整性用戶定義的完整性就是針對某一具體應(yīng)用的數(shù)據(jù)必須滿足的語義要求RDBMS提供，而不必由應(yīng)用程序承擔(dān)屬性上的約束條件的定義CREATETABLE時定義列值非空（NOTNULL）列值唯一（UNIQUE）檢查列值是否滿足一個布爾表達(dá)式（CHECK）屬性上的約束條件的定義(續(xù))2.列值唯一［例］建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼CREATETABLEDEPT(DeptnoNUMERIC(2)，DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，PRIMARYKEY(Deptno))；屬性上的約束條件的定義(續(xù))3.用CHECK短語指定列值應(yīng)該滿足的條件［例7］Student表的Ssex只允許取“男”或“女”。CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，SnameCHAR(8)NOTNULL，SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’)

)，/*性別屬性Ssex只允許取'男'或'女'*/SageSMALLINT，SdeptCHAR(20));屬性上的約束條件檢查和違約處理插入元組或修改屬性的值時，RDBMS檢查屬性上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行元組上的約束條件的定義在CREATETABLE時可以用CHECK短語定義元組上的約束條件，即元組級的限制同屬性值限制相比，元組級的限制可以設(shè)置不同屬性之間的取值的相互約束條件元組上的約束條件的定義(續(xù))［例9］當(dāng)學(xué)生的性別是男時，其名字不能以Ms.打頭。CREATETABLEStudent(SnoCHAR(9)，SnameCHAR(8)NOTNULL，SsexCHAR(2)，SageSMALLINT，SdeptCHAR(20)，PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定義了元組中Sname和Ssex兩個屬性值之間的約束條件*/)；性別是女性的元組都能通過該項檢查，因為Ssex=‘女’成立；當(dāng)性別是男性時，要通過檢查則名字一定不能以Ms.打頭元組上的約束條件檢查和違約處理插入元組或修改屬性的值時，RDBMS檢查元組上的約束條件是否被滿足如果不滿足則操作被拒絕執(zhí)行完整性約束命名子句CONSTRAINT約束CONSTRAINT<完整性約束條件名>［PRIMARYKEY短語|FOREIGNKEY短語|CHECK短語］完整性約束命名子句(續(xù))［例］建立學(xué)生登記表Student，要求學(xué)號在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。CREATETABLEStudent(SnoNUMERIC(6)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，SnameCHAR(20)

CONSTRAINTC2NOTNULL，SageNUMERIC(3)

CONSTRAINTC3CHECK(Sage<30)，SsexCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男'，'女'))，CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。2.修改基本表和刪除基本表

ALTERTABLE〈表名〉

[ADD(〈新列名〉〈數(shù)據(jù)類型〉[完整性約束][，…n])]

[DROP〈完整性約束名〉]

[MODIFY(〈列名〉〈數(shù)據(jù)類型〉[，…n])]；

向Student表增加“入學(xué)時間”列，其數(shù)據(jù)類型為日期型。ALTERTABLEStudentADDS_entranceDATE；不論基本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值。

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

ALTERTABLECourseADDUNIQUE(Cname);

［例］修改表Student中的約束條件，要求學(xué)號改為在900000~999999之間，年齡由小于30改為小于40可以先刪除原來的約束條件，再增加新的約束條件ALTERTABLEStudentDROPCONSTRAINTC1;ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，ALTERTABLEStudentDROPCONSTRAINTC3;ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；5.2.2索引的定義和維護(hù)1.索引的作用

1)使用索引可以明顯地加快數(shù)據(jù)查詢的速度。

2)使用索引可保證數(shù)據(jù)的惟一性。

3)使用索引可以加快連接速度。2.建立索引的原則

1)索引的建立和維護(hù)由DBA和DBMS完成。

2)大表應(yīng)當(dāng)建索引，小表則不必建索引。

3)對于一個基本表，不要建立過多的索引。

4)根據(jù)查詢要求建索引。

3.建立和刪除索引的格式建立格式為：

CREATE[UNIQUE][CLUSTER]INDEX〈索引名〉ON〈表名〉(〈列名〉[〈次序〉][，〈列名〉[〈次序〉]]…)；為學(xué)生_課程數(shù)據(jù)庫中的學(xué)生、課程和選課三個表建立索引。其中，學(xué)生表按學(xué)號升序建立索引；課程表按課程號升序建惟一索引；選課表按學(xué)號升序和課程號降序建惟一索引。

CREATEUNIQUEINDEXstusnoON學(xué)生(學(xué)號)；

CREATEUNIQUEINDEXcoursnoON課程(課程號)；

CREATEUNIQUEINDEXscnoON選課(學(xué)號ASC，課程號DESC)；

刪除索引格式為： DROPINDEX〈索引名〉；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例]刪除Student表的Stusno索引 DROPINDEXStusno；3.視圖的刪除、查詢和維護(hù)視圖刪除語句的一般格式為：

DROPVIEW〈視圖名〉；

視圖可以和基本表一樣被查詢，其使用方法與基本表相同，但利用視圖進(jìn)行數(shù)據(jù)增、刪、改操作，會受到一定的限制。

5.3數(shù)據(jù)查詢語句5.3.1數(shù)據(jù)查詢的基本語法1.SELECT語句的語法SELECT〈目標(biāo)列組〉FROM〈數(shù)據(jù)源〉[WHERE〈元組選擇條件〉][GROUPBY〈分列組〉[HAVING〈組選擇條件〉]][ORDERBY〈排序列1〉〈排序要求1〉[，…n]]；語法說明(1)SELECT子句：指明目標(biāo)列（字段、表達(dá)式、函數(shù)表達(dá)式、常量）?；颈碇邢嗤牧忻硎緸椋骸幢砻?〈列名〉(2)FROM子句：指明數(shù)據(jù)源。表間用“，”分割。數(shù)據(jù)源不在當(dāng)前數(shù)據(jù)庫中，使用“〈數(shù)據(jù)庫名〉.〈表名〉”表示。一表多用，用別名標(biāo)識。定義表別名：〈表名〉〈別名〉(3)WHERE子句：元組選擇條件。(4)GROUPBY子句：結(jié)果集分組。當(dāng)目標(biāo)列中有統(tǒng)計函數(shù)，則統(tǒng)計為分組統(tǒng)計，否則為對整個結(jié)果集統(tǒng)計。子句后帶上HAVING子句表達(dá)組選擇條件（帶函數(shù)的表達(dá)式）。(5)ORDERBY子句：排序。當(dāng)排序要求為ASC時升序排序；排序要求為DESC時降序排列。2.SELECT語句的操作符(1)算術(shù)操作符

+（加號）、－（減號）、*（乘號）和/（除號）。(2)比較操作符

=（等于）、>（大于）、<（小于）、<=（小于等于）、>=（大于等于）、!=（不等于）、<>（小于大于）、!>（不大于）和!<（不小于），共9種操作符。

(3)邏輯操作符

語義操作符使用格式或示例在[不在]其中[NOT]IN〈字段〉IN（〈數(shù)據(jù)表|子查詢〉）任何一個ANY〈字段〉〈比較符〉A(chǔ)NY（數(shù)據(jù)表|子查詢）全部(每個)ALL〈字段〉〈比較符〉A(chǔ)LL（數(shù)據(jù)表|子查詢）[不]存在EXISTSEXISTS(〈子查詢〉)在[不在]范圍BETWEEN…AND…〈字段〉BETWEEN小值A(chǔ)ND大值是[不是]空值IS[NOT]NULL〈字段〉IS[NOT]NULL模式比較[NOT]LIKE〈字段〉LIKE〈字符常數(shù)〉其中，“_”單字符通配符和“%”多字符通配符與運算AND〈條件1〉A(chǔ)ND〈條件2〉或運算OR〈條件1〉OR〈條件2〉非運算NOTNOT〈條件〉(4)組合查詢操作符和其他SQL操作符〈查詢1〉〈組合操作符〉〈查詢2〉

1)UNION：并查詢，并在結(jié)果集中去掉重復(fù)行。

2)MINUS：差查詢操作。

3)INTERSECT：交查詢操作。

4)*：取全部字段。格式為：*或〈表名〉.*

5)ALL：全部。保留重復(fù)值（有統(tǒng)計函數(shù)時要求計算重復(fù)值）。

格式為：ALL〈字段〉或ALL〈字段組〉

6)DISTINCT：去掉重復(fù)值。在結(jié)果集中去掉重復(fù)值，或在統(tǒng)計函數(shù)中不計重復(fù)值。

格式為：DISTINCT〈字段〉或DISTINCT〈字段組〉

5.3.2數(shù)據(jù)查詢實例學(xué)生課程庫結(jié)構(gòu)為：學(xué)生(學(xué)號，姓名，年齡，所在系)；課程(課程號，課程名，先行課)；選課(學(xué)號，課程號，成績).1.簡單查詢：查詢過程中只涉及到一個表的查詢語句。

【例】求數(shù)學(xué)系學(xué)生的學(xué)號和姓名。

SELECT學(xué)號，姓名

FROM學(xué)生

WHERE所在系=‘?dāng)?shù)學(xué)系’；

【例】求選修了課程的學(xué)生學(xué)號。

SELECTDISTINCT學(xué)號

FROM選課；【例】求選修C1課程的學(xué)生學(xué)號和成績，并要求對查詢結(jié)果按成績的降序排列，如果成績相同則按學(xué)號的升序排列。

SELECT學(xué)號，成績

FROM選課

WHERE課程號=‘C1’

ORDERBY成績DESC，學(xué)號ASC；【例】求選修課程C1且成績在80～90之間的學(xué)生學(xué)號和成績，并將成績乘以系數(shù)0.8輸出。

SELECT學(xué)號，成績*0.8FROM選課WHERE課程號='C1'AND成績BETWEEN80AND90；【例】求數(shù)學(xué)系或計算機系姓張的學(xué)生的信息。

SELECT*

FROM學(xué)生

WHERE所在系IN(‘?dāng)?shù)學(xué)系’，‘計算機系’)AND姓名LIKE‘張%’；【例】求缺少了成績的學(xué)生的學(xué)號和課程號。

SELECT學(xué)號，課程號

FROM選課

WHERE成績ISNULL；

2.連接查詢

連接查詢中的連接條件通過WHERE子句表達(dá)，連接條件和元組選擇條件之間用AND（與）操作符銜接。(1)等值連接和非等值連接

[〈表名1〉.]〈列名1〉〈比較運算符〉[〈表名2〉.]〈列名2〉

比較運算符：=、>、<、>=、<=和!=；列名稱為連接字段。

【例】查詢每個學(xué)生的情況以及他(她)所選修的課程。

SELECT學(xué)生.*，選課.*

FROM學(xué)生，選課

WHERE學(xué)生.學(xué)號=選課.學(xué)號；

【例】求學(xué)生的學(xué)號、姓名、選修的課程名及成績。

SELECT學(xué)生.學(xué)號，姓名，課程名，成績FROM學(xué)生，課程，選課WHERE學(xué)生.學(xué)號=選課.學(xué)號AND課程.課程號=選課.課程號；【例】求選修C1課程且成績?yōu)?0分以上的學(xué)生學(xué)號、姓名及成績。SELECT學(xué)生.學(xué)號，姓名，成績FROM學(xué)生，選課WHERE學(xué)生.學(xué)號=選課.學(xué)號AND課程號=‘C1’AND成績>90；(2)自身連接

例如，課程表中的先行課是在上學(xué)期應(yīng)開設(shè)的，先行課的先行課，即間接先行課應(yīng)提前一學(xué)年開設(shè)。如果求查詢某門課的間接先行課或全部課程的間接先行課，就需要對課程表進(jìn)行自身連接。課程號課程名先行課C1計算機引論—C2PASCAL語言C1C3數(shù)據(jù)結(jié)構(gòu)C2C4數(shù)據(jù)庫C3C5軟件工程C4課程的先行關(guān)系鏈為：C5→C4→C3→C2→C1，課程的間接關(guān)系鏈為：C5→C3→C1。

課程號課程名先行課

課程號課程名先行課C1計算機引論

C1計算機引論

C2Pascal語言C1C2Pascal語言C1C3數(shù)據(jù)結(jié)構(gòu)C2C3數(shù)據(jù)結(jié)構(gòu)C2C4數(shù)據(jù)庫C3C4數(shù)據(jù)庫C3C5軟件工程C4C5軟件工程C4A.課程號A.課程名B.先行課C2Pascal語言

C3數(shù)據(jù)結(jié)構(gòu)C1C4數(shù)據(jù)庫C2C5軟件工程C3AB結(jié)果【例】查詢每一門課的間接先行課。

SELECTA.課程號，A.課程名，B.先行課

FROM課程A，課程B

WHEREA.先行課=B.課程號

關(guān)系代數(shù):外連接如果把舍棄的元組也保存在結(jié)果關(guān)系中，而在其他屬性上填空值(Null)，這種連接就叫做外連接（OUTERJOIN）。左外連接如果只把左邊關(guān)系R中要舍棄的元組保留就叫做左外連接(LEFTOUTERJOIN或LEFTJOIN)右外連接如果只把右邊關(guān)系S中要舍棄的元組保留就叫做右外連接(RIGHTOUTERJOIN或RIGHTJOIN)。loan-numberamountL-170L-230L-260300040001700customer-nameloan-numberJonesSmithHayesL-170L-230L-155branch-nameDowntownRedwoodPerryridge關(guān)系loan關(guān)系borrower例：內(nèi)連接

loanBorrowerloanborrower左外連接loan-numberamountL-170L-23030004000customer-nameJonesSmithbranch-nameDowntownRedwoodloan-numberamountL-170L-230L-260300040001700customer-nameJonesSmithnullbranch-nameDowntownRedwoodPerryridge右外連接loan

borrowerloan-numberamountL-170L-230Lullcustomer-nameJonesSmithHayesloan-numberamountL-170L-230L-260L-155300040001700nullcustomer-nameJonesSmithnullHayesloan

borrower全外連接branch-nameDowntownRedwoodnullbranch-nameDowntownRedwoodPerryridgenull(3)外部連接

左外部連接操作是在結(jié)果集中保留連接表達(dá)式左表中的非匹配記錄；右外部連接操作是在結(jié)果集中保留連接表達(dá)式右表中的非匹配記錄。外部連接符號為“*=”，右外部連接符號為“=*”。外部連接中不匹配的分量用NULL表示。職工號姓名性別年齡所在部門

部門號部門名稱電話1010李勇男201111生產(chǎn)科5661011劉晨女19

12計劃科5781012王敏女221213一車間4671014張立男211314科研所

職工表部門表

連接的結(jié)果集

職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661012王敏女2212計劃科5781014張立男2113一車間467職工號姓名性別年齡所在部門部門名稱電話1010李勇男2011生產(chǎn)科5661011劉晨女19

1012王敏女2212計劃科5781014張立男2113一車間467內(nèi)連接的結(jié)果集

左外部連接的結(jié)果集內(nèi)連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=部門.部門號；左外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門*=部門.部門號；右外部連接：

SELECT職工.*，部門名稱，電話

FROM職工，部門

WHERE職工.所在部門=*部門.部門號；用SQL表達(dá)職工和部門之間的內(nèi)連接、左外部連接和右外部連接的語句3.嵌套查詢使用IN操作符的嵌套查詢

【例5-20】求選修了高等數(shù)學(xué)的學(xué)生學(xué)號和姓名。

SELECT學(xué)號，姓名FROM學(xué)生

WHERE學(xué)號IN(SELECT學(xué)號FROM選課

WHERE課程號IN(SELECT課程號

FROM課程

WHERE課程名=‘高等數(shù)學(xué)'))；

該題也可以使用下面的連接查詢表達(dá)。

SELECT學(xué)生.學(xué)號，姓名FROM學(xué)生，課程，選課

WHERE學(xué)生.學(xué)號=課程.學(xué)號AND課程.課程號=選課.課程號AND課程.課程名='高等數(shù)學(xué)'；(2)使用比較符的嵌套查詢【例】求C1課程的成績高于張三的學(xué)生學(xué)號和成績。

SELECT學(xué)號，成績

FROM選課

WHERE課程號=‘C1’AND成績>(SELEC成績FROM選課

WHERE課程號=‘C1’AND學(xué)號=(SELECT學(xué)號FROM學(xué)生WHERE姓名='張三'))；(3)使用ANY或ALL操作符的嵌套查詢格式為：〈字段〉〈比較符〉[ANY|ALL]〈子查詢〉

操作符語意>ANY大于子查詢結(jié)果中的某個值，即表示大于查詢結(jié)果中最小值>ALL大于子查詢結(jié)果中的所有值，即表示大于查詢結(jié)果中最大值<ANY小于子查詢結(jié)果中的某個值，即表示小于查詢結(jié)果中最大值<ALL小于子查詢結(jié)果中的所有值，即表示小于查詢結(jié)果中最小值>=ANY大于等于子查詢結(jié)果中的某個值，即表示大于等于結(jié)果集中最小值>=ALL大于等于子查詢結(jié)果中的所有值，即表示大于等于結(jié)果集中最大值<=ANY小于等于子查詢結(jié)果中的某個值，即表示小于等于結(jié)果集中最大值<=ALL小于等于子查詢結(jié)果中的所有值，即表示小于等于結(jié)果集中最小值=ANY等于子查詢結(jié)果中的某個值，即相當(dāng)于IN=ALL等于子查詢結(jié)果中的所有值(通常沒有實際意義)!=(或<>)ANY不等于子查詢結(jié)果中的某個值，!=(或<>)ALL不等于子查詢結(jié)果中的任何一個值，即相當(dāng)于NOTIN【例】求其他系中比計算機系某一學(xué)生年齡小的學(xué)生。

SELECT*

FROM學(xué)生

WHERE年齡<ANY(SELECT年齡FROM學(xué)生

WHERE所在系=‘計算機系’)AND所在系<>‘計算機系’；【例】求其他系中比計算機系學(xué)生年齡都小的學(xué)生。

SELECT*

FROM學(xué)生

WHERE年齡<ALL(SELECT年齡FROM學(xué)生

WHERE所在系='計算機系')AND所在系<>'計算機系'；(4)使用EXISTS操作符的嵌套查詢【例】求選修了C2課程的學(xué)生姓名。

SELECT姓名FROM學(xué)生

WHEREEXISTS(SELECT*FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程號='C2')；【例】求沒有選修C2課程的學(xué)生姓名。

SELECT姓名

FROM學(xué)生

WHERENOTEXISTS(SELECT*FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程號='C2')；【例】查詢選修了全部課程的學(xué)生的姓名。

SELECT姓名FROM學(xué)生

WHERENOTEXISTS (SELECT*FROM課程

WHERENOTEXISTS (SELECT*

FROM選課

WHERE學(xué)生.學(xué)號=學(xué)號AND課程.課程號=課程號))；【例】求至少選修了學(xué)號為“S2”的學(xué)生所選修的全部課程的學(xué)生學(xué)號和姓名。SELECT學(xué)號，姓名FROM學(xué)生WHERENOTEXISTS(SELECT*FROM選課選課1WHERE選課1.學(xué)號='S2'ANDNOTEXISTS(SELECT*FROM選課選課2WHERE學(xué)生.學(xué)號=選課2.學(xué)號AND選課2.課程號=選課1.課程號)；不同形式的查詢間的替換一些帶EXISTS或NOTEXISTS謂詞的子查詢不能被其他形式的子查詢等價替換所有帶IN謂詞、比較運算符、ANY和ALL謂詞的子查詢都能用帶EXISTS謂詞的子查詢等價替換用EXISTS/NOTEXISTS實現(xiàn)全稱量詞(難點)SQL語言中沒有全稱量詞（Forall）可以把帶有全稱量詞的謂詞轉(zhuǎn)換為等價的帶有存在量詞的謂詞：(x)P≡(x(P))

4.組合查詢【例】求選修了C1課程或選修了C2課程的學(xué)生學(xué)號。

SELECT學(xué)號FROM選課WHERE課程號=‘C1’

UNION

SELECT學(xué)號FROM選課WHERE課程號=‘C2’【例】求選修C1課程，并且也選修C2課程的學(xué)生學(xué)號。

SELECT學(xué)號FROM選課WHERE課程號=‘C1’

INTERSECT

SELECT學(xué)號FROM選課WHERE課程號='C2'；【例】求選修了C1課程但沒有選修C2課程的學(xué)生學(xué)號。

SELECT學(xué)號FROM選課WHERE課程號=‘C1’

MINUS

SELECT學(xué)號FROM選課WHERE課程號=‘C2’；

本例也可以用下面的EXISTS嵌套查詢表示。

SELECT學(xué)號

FROM選課選課1

WHERE課程號=‘C1’ANDNOTEXISTS

(SELECT學(xué)號FROM選課選課2

WHERE選課1.學(xué)號=選課2.學(xué)號AND選課2.課程號='C2')；

GROUPBY子句分組：細(xì)化聚集函數(shù)的作用對象未對查詢結(jié)果分組，聚集函數(shù)將作用于整個查詢結(jié)果對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個組作用對象是查詢的中間結(jié)果表按指定的一列或多列值分組，值相等的為一組5.使用分組和SQL函數(shù)查詢函數(shù)功能AVG(〈數(shù)值表達(dá)式〉)求與字段相關(guān)的數(shù)值表達(dá)式的平均值SUM(〈數(shù)值表達(dá)式〉)求與字段相關(guān)的數(shù)值表達(dá)式的和值MIN(〈字段表達(dá)式〉)求字段表達(dá)式的最小值MAX(〈字段表達(dá)式〉)求字段表達(dá)式的最大值COUNT(*|〈字段〉)求記錄行數(shù)(*)，或求不是NULL的字段的行數(shù)【例】求學(xué)生的總?cè)藬?shù)。

SELECTCOUNT(*)FROM學(xué)生；【例】求選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCT學(xué)號)

FROM選課；【例】求課程和選修該課程的人數(shù)。

SELECT課程號，COUNT(學(xué)號)

FROM選課

GROUPBY課程號；【例】求選修課超過3門課的學(xué)生學(xué)號。

SELECT學(xué)號

FROM選課

GROUPBY學(xué)號HAVINGCOUNT(*)>3；HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組HAVING短語作用于組，從中選擇滿足條件的組。5.4數(shù)據(jù)更新語句5.4.1數(shù)據(jù)插入語句

1.使用常量插入單個元組

格式為：

INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉…)]

VALUES(〈常量1〉[，〈常量2〉]…)；【例】將一個新學(xué)生記錄(學(xué)號：‘98010’，姓名：‘張三’，年齡：20，所在系：‘計算機系’)插入到學(xué)生表中。

INSERT

INTO學(xué)生

VALUES(‘98010’，‘張三’，20，‘計算機系’)；【例】插入一條選課記錄(學(xué)號：'98011'，課程號：'C10'，成績不詳)。

INSERT

INTO選課(學(xué)號，課程號)

VALUES('98011'，'C10')；

2.在表中插入子查詢的結(jié)果集

INSERT

INTO〈表名〉[(〈屬性列1〉[，〈屬性列2〉]…)]

〈子查詢〉；

【例】求每個系學(xué)生的平均年齡，把結(jié)果存入數(shù)據(jù)庫中。

CREATETABLE系平均年齡(系名稱CHAR(20)，

平均年齡SMALLINT)；

INSERT

INTO系平均年齡

SELECT所在系，AVG(ALL年齡)

FROM學(xué)生

GROUPBY所在系；5.4.2數(shù)據(jù)修改語句UPDATE〈表名〉SET〈列名〉=〈表達(dá)式〉[，〈列名〉=〈表達(dá)式〉][，…n][WHERE〈條件〉]；

【例】將學(xué)生表中全部學(xué)生的年齡加上2歲。

UPDATE學(xué)生

SET年齡=年齡+2；【例】將選課表中的數(shù)據(jù)庫課程的成績乘以1.2。

UPDATE選課

SET成績=成績*1.2

WHERE課程號=(SELECT課程號

FROM課程

WHERE課程名='數(shù)據(jù)庫')；5.4.3數(shù)據(jù)刪除語句

DELETE

FROM〈表名〉

[WHERE〈條件〉]；

【例】刪除藝術(shù)系的學(xué)生記錄及選課記錄。

DELETE

FROM選課

WHERE學(xué)號IN(SELECT學(xué)號

FROM學(xué)生

WHERE所在系='藝術(shù)系')；

DELETE

FROM學(xué)生WHERE所在系='藝術(shù)系'；5.5視圖視圖的特點虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù)基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變視圖基于視圖的操作查詢刪除受限更新定義基于該視圖的新視圖一、建立視圖語句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]；組成視圖的屬性列名：全部省略或全部指定子查詢不允許含有ORDERBY子句和DISTINCT短語建立視圖（續(xù)）RDBMS執(zhí)行CREATEVIEW語句時只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。建立視圖（續(xù)）[例]建立信息系學(xué)生的視圖。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；建立視圖（續(xù)）[例]建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時仍需保證該視圖只有信息系的學(xué)生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；建立視圖（續(xù)）對IS_Student視圖的更新操作：修改操作：自動加上Sdept='IS'的條件刪除操作：自動加上Sdept='IS'的條件插入操作：自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作如果沒有提供Sdept屬性值，則自動定義Sdept為'IS'建立視圖（續(xù)）基于多個基表的視圖[例]建立信息系選修了1號課程的學(xué)生視圖。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；建立視圖（續(xù)）基于視圖的視圖 [例]建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；建立視圖（續(xù)）帶表達(dá)式的視圖[例]定義一個反映學(xué)生出生年份的視圖。CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2000-SageFROMStudent；建立視圖（續(xù)）分組視圖[例]將學(xué)生的學(xué)號及他的平均成績定義為一個視圖 假設(shè)SC表中“成績”列Grade為數(shù)字型CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立視圖（續(xù)）不指定屬性列[例]將Student表中所有女生記錄定義為一個視圖CREATEVIEWF_Student(F_Sno，name，sex，age，dept)AS

SELECT*FROMStudentWHERESsex=‘女’；缺點：修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。二、刪除視圖語句的格式： DROPVIEW<視圖名>；該語句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROPVIEW語句刪除刪除視圖(續(xù)）［例］刪除視圖BT_S：DROPVIEWBT_S；

刪除視圖IS_S1：DROPVIEWIS_S1；拒絕執(zhí)行級聯(lián)刪除：DROPVIEWIS_S1CASCADE;查詢視圖用戶角度：查詢視圖與查詢基本表相同RDBMS實現(xiàn)視圖查詢的方法視圖消解法（ViewResolution）進(jìn)行有效性檢查轉(zhuǎn)換成等價的對基本表的查詢執(zhí)行修正后的查詢查詢視圖（續(xù)）[例]在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student視圖的定義(參見視圖定義例1)查詢視圖（續(xù)）視圖消解轉(zhuǎn)換后的查詢語句為：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；查詢視圖（續(xù)）[例]查詢選修了1號課程的信息系學(xué)生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；查詢視圖（續(xù)）視圖消解法的局限有些情況下，視圖消解法不能生成正確查詢。

查詢視圖（續(xù)）[例]在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績SELECT*FROMS_GWHEREGavg>=90；S_G視圖的子查詢定義：CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查詢轉(zhuǎn)換錯誤：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正確：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；更新視圖（續(xù)）[例]將信息系學(xué)生視圖IS_Student中學(xué)號200215122的學(xué)生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='200215122'；轉(zhuǎn)換后的語句：UPDATEStudentSETSname='劉辰'WHERESno='200215122'ANDSdept='IS'；更新視圖（續(xù)）[例]向信息系學(xué)生視圖IS_S中插入一個新的學(xué)生記錄：200215129，趙新，20歲INSERTINTOIS_StudentVALUES(‘95029’，‘趙新’，20)；轉(zhuǎn)換為對基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(‘200215129'，'趙新'，20，'IS')；更新視圖（續(xù)）[例]刪除信息系學(xué)生視圖IS_Student中學(xué)號為200215129的記錄DELETEFROMIS_StudentWHERESno='200215129'；轉(zhuǎn)換為對基本表的更新：DELETEFROMStudentWHERESno='200215129'ANDSdept='IS'；更新視圖（續(xù)）更新視圖的限制：一些視圖是不可更新的，因為對這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新例：視圖S_G為不可更新視圖。UPDATES_GSETGavg=90WHERESno=‘200215121’；這個對視圖的更新無法轉(zhuǎn)換成對基本表SC的更新更新視圖（續(xù)）允許對行列子集視圖進(jìn)行更新對其他類型視圖的更新不同系統(tǒng)有不同限制視圖的作用1.視圖能夠簡化用戶的操作2.視圖使用戶能以多種角度看待同一數(shù)據(jù)3.視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性4.視圖能夠?qū)C密數(shù)據(jù)提供安全保護(hù)5.適當(dāng)?shù)睦靡晥D可以更清晰的表達(dá)查詢5.5嵌入式SQL5.5.1嵌入式SQL的特點

1.嵌入式SQL應(yīng)注意的問題

1)SQL和主語言的配合問題。2)合理選擇主語言。

2.SQL嵌入主語言時必須解決的三個問題

(1)如何區(qū)別SQL和主語言

(2)使數(shù)據(jù)庫的工作單元與程序工作單元之間能夠通信

1)主語言通過主變量向SQL語句提供參數(shù)。 2)SQL語句的當(dāng)前工作狀態(tài)和運行環(huán)境數(shù)據(jù)要返饋給應(yīng)用程序。

(3)使用游標(biāo)解決SQL一次一集合的操作與主語言一次一記錄操作的矛盾5.5.2不用游標(biāo)的SQL語句1.幾種不需要使用游標(biāo)的SQL語句

(1)用于說明主變量的說明性語句

SQL的說明性語句主要有兩條：

EXECSQLBEGINDECLARESECTION；

EXECSQLENDDECLARESECTION；

(2)數(shù)據(jù)定義和數(shù)據(jù)控制語句

(3)查詢結(jié)果為單記錄的查詢語句

(4)數(shù)據(jù)的插入語句和某些數(shù)據(jù)刪除、修改語句

獨立的數(shù)據(jù)刪除和修改語句不需要使用游標(biāo)；與查詢語句配合，刪除或修改查詢到的當(dāng)前記錄的操作，與游標(biāo)有關(guān)。2.不用游標(biāo)的查詢語句EXECSQLSELECT[ALL|DISTINCT]〈目標(biāo)列表達(dá)式〉[，…n]

INTO〈主變量〉[〈指示變量〉][，…n]

FROM〈表名或視圖名〉[，…n][WHERE〈條件表達(dá)式〉]；

1)在語句開始前要加EXECSQL前綴。

2)該查詢語句中又?jǐn)U充了INTO子句。

3)在WHERE子句的條件表達(dá)式中可以使用主變量。

4)由于查詢的結(jié)果集中只有一條記錄，該語句中不必有排序和分組子句。

5)INTO子句中的