3.1SQL的特征3.2SQL的數(shù)據(jù)定義3.3SQL的數(shù)據(jù)查詢3.4SQL的數(shù)據(jù)更新3.5

視圖第三章關(guān)系數(shù)據(jù)庫語言SQL3.6嵌入式SQL

3.7動態(tài)SQL

3.8SQL編程與擴(kuò)充

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

3.1SQL的特征

SQL—StructuredQueryLanguage（結(jié)構(gòu)化查詢語言），是通用的，功能極強(qiáng)的關(guān)系DB語言。1.SQL的主要標(biāo)準(zhǔn)

SQL-86SQL-89SQL-92(SQL2)SQL-99(SQL3)SQL20033.1SQL的特征2.SQL的功能特點功能：數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制特點：一體化非過程化集合式操作靈活的使用方式（交互、嵌入式）簡潔、方便、功能強(qiáng)3.1SQL的特征3.SQL的基本組成

（1）SQL數(shù)據(jù)庫層次結(jié)構(gòu)

存儲文件1存儲文件2SQL視圖1視圖2基本表1基本表2基本表3術(shù)語對照：一般關(guān)系模型SQL

外模式-------視圖（VIEW)

模式-------基本表(TABLE)

內(nèi)模式-------存儲文件（索引）元組--------行(ROW)

屬性--------列(COLUMN)（2）SQL的語句類型

SQL模式語句

SQL數(shù)據(jù)語句

SQL事務(wù)與控制語句

SQL連接、會話及診斷語句3.1SQL的特征4.SQL的數(shù)據(jù)類型

預(yù)定義數(shù)據(jù)類型

int、real、double、char、varchar、date、boolean等構(gòu)造數(shù)據(jù)類型

array、ref、row等。用戶定義數(shù)據(jù)類型（UDT，UserDefinedType）3.1SQL的特征5.SQL環(huán)境（1）SQL模式與目錄

SQL模式：基本表、視圖、角色等的集合。好處：允許在不同的SQL模式中出現(xiàn)同名的基表名或視圖名。目錄：SQL環(huán)境中所有模式的集合。定位基表的方式：

<目錄名>．<模式名>．<表名>（2）SQL環(huán)境設(shè)置默認(rèn)的目錄和模式設(shè)置用戶身份

3.2SQL的數(shù)據(jù)定義1.SQL模式的定義與撤銷（1）SQL模式的定義

CREATESCHEMA＜模式名＞AUTHRIZATION＜用戶名＞[＜CREATEDOMAIN子句＞|＜CREATETABLE子句＞|＜CREATEVIEW＞|……]其中：[…]：表示其中的成分為任選項。

<…>：表示其中的成分由用戶具體給定。

|：表示其中并列的成分只能擇一。例：CREATESCHEMATeaching_dbAUTHRIZATIONHang；3.2SQL的數(shù)據(jù)定義（2）數(shù)據(jù)庫模式的刪除

DROPSCHEMA＜模式名＞{CASCADE|RESTRICT}

CASCADE：級聯(lián)式

RESTRICT：約束式（受限式）3.2SQL的數(shù)據(jù)定義

2.表的建立和刪除

（1）表的建立命令格式：

CREATETABLE[模式名.]<表名>(<列名><類型>[<列約束條件>][，<列名><類型>[<列約束條件>]]…[<表約束條件>])；例：CREATETABLEStudent

(snoCHAR(5)NOTNULL

UNIQUE，

snameCHAR(8)NOTNULL

，

sexCHAR(2)，

ageINT，

deptCHAR(20)

)；主碼3.2SQL的數(shù)據(jù)定義完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可定義在列級，也可以定義在表級。注意：

例：定義學(xué)生_選課數(shù)據(jù)庫中的三個表結(jié)構(gòu)，并指定相應(yīng)的數(shù)據(jù)完整性約束條件。分析外碼：sno,cno

主碼：sno姓名：非空性別：男、女兩值Student表：Course表：主碼：cno課程名：非空外碼：pcnoSC表：主碼：(sno,cno)成績：0—1003.2SQL的數(shù)據(jù)定義CREATETABLEStudent

(

snoCHAR(5),snameCHAR(8)NOTNULL,sexCHAR(2),ageSMALLINT,deptCHAR(20),PRIMARYKEY(sno),CHECKsexIN(‘男’,‘女’)

);列級完整性約束條件實體完整性約束條件用戶自定義完整性約束條件3.2SQL的數(shù)據(jù)定義CREATETABLECourse

(

cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditSMALLINT,PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno)

);參照完整性約束條件3.2SQL的數(shù)據(jù)定義CREATETABLESC

(

snoCHAR(5),cnoCHAR(4),gradeSMALLINT,PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK((gradeISNULL)OR(gradeBETWEEN0AND100))

);3.2SQL的數(shù)據(jù)定義（2）表的刪除

格式：DROPTABLE<表名>[CASCADE|RESTRICT]例：DROPTABLESt-quitCASCADE；

3.表的擴(kuò)充和修改（1）在現(xiàn)存表中增加新列

格式：ALTERTABLE<表名>ADD(<列名><數(shù)據(jù)類型>，…)

例：ALTERTABLEStudentADD(placeCHAR(20)，addrCHAR(20))；

3.2SQL的數(shù)據(jù)定義（2）刪除已存在的某個列

格式：ALTERTABLE<表名>DROP<列名>[CASCADE|RESTRICT]

例：ALTERTABLEStudentDROPaddr；（3）修改原有列的類型

格式：ALTERTABLE<表名>MODIFY<列名><類型>；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；

3.2SQL的數(shù)據(jù)定義（4）補(bǔ)充定義主碼

格式：ALTERTABLE<表名>ADDPRIMARYKEY(<列名表>)（5）刪除主碼

格式：ALTERTABLE<表名>DROPPRIMARYKEY3.2SQL的數(shù)據(jù)定義4.域定義

域定義用于建立用戶自定義的數(shù)據(jù)類型。命令格式：

CREATEDOMAIN<域名>[AS]<數(shù)據(jù)類型>[DEFAULT<缺省值>][<域約束>][例3.9]建立一個建筑公司的數(shù)據(jù)庫模式，由3個表組成。其中：

CRETEDOMAINITEM_IDNUMBERIC(4)DEFAULT0CHECK(VALUEISNOTNULL)3.2SQL的數(shù)據(jù)定義5.索引的建立與刪除

建立索引的目的：基本表上建立一個或多個索引，以提供多種存取路徑，加快查找速度。

命令格式：

CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名><次序>[[，<列名><次序>]]…)；次序：升序（ASC，缺省）降序（DESC）

UNIQUE:每一個索引值只對應(yīng)惟一的數(shù)據(jù)記錄。

CLUSTER:建立聚簇索引，即索引項的順序與表中記錄的物理順序一致。

3.2SQL的數(shù)據(jù)定義注意：在一個基本表上最多只能建立一個聚簇索引。經(jīng)常更新的列不宜建立聚簇索引。所建索引放何處？例:①

為Student表按學(xué)號升序建惟一聚簇索引。

②為SC表按學(xué)號升序和課程號降序建惟一索引。

①

CREATEUNIQUECLUSTERINDEXStno

ONStudent(Sno)；

②

CREATEUNIQUEINDEXScno

ONSC(Sno，CnoDESC)；刪除索引一般格式為：

DROPINDEX<索引名>；例：

DROPINDEXStno

；刪除索引時，系統(tǒng)會同時從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。3.3SQL的數(shù)據(jù)查詢基本形式：SELECT<A>FROM<R>WHERE<F>;查詢語句塊含義:從表

(視圖)R中找出滿足條件F的行，再從中選出目標(biāo)屬性A的值形成結(jié)果表。查詢目標(biāo):為屬性名表或表達(dá)式或‘*’數(shù)據(jù)來源:表或視圖選擇行(元組)的條件關(guān)系代數(shù):∏A(σF(R))

例:已知R(no,name,sum)求總分大于600的學(xué)生號和姓名。目標(biāo)A條件F來源RSELECTno,nameFROMRWHEREsum>600;

解:Πno，name(σsum>600(R))

3.3SQL的數(shù)據(jù)查詢學(xué)生-課程數(shù)據(jù)庫:Student(sno，sname，sex，age，dept)Course(cno，cname，credit，pcno)SC(sno，cno，grade)3.3.1單表查詢

一、選擇表中的列例:查詢所有學(xué)生的姓名、學(xué)號、所在院系。SELECTsname，sno，deptFROMStudentWHERE.T.;snamesnodept———————————王蕭虎200101信息院……來源R條件F

目標(biāo)A3.3SQL的數(shù)據(jù)查詢例：查全體學(xué)生的姓名及其出生年份。

SELECTsname，2008-ageFROMStudent；來源R條件F目標(biāo)ASname

2008-age——————————王蕭虎1988……ASBirth別名Birth二、選擇表中的行（1）比較例:查考試成績不及格的學(xué)號。SELECTSnoFROMSCWHEREgrade<60；DISTINCT去掉重復(fù)元組表達(dá)式3.3SQL的數(shù)據(jù)查詢

(2)確定范圍(BETWEEN…AND…)[例3.15]查詢選002號課程且成績在80--90的學(xué)生號。

SELECTsnoFROMSCWHEREcno=‘002’ANDgradeBETWEEN80AND90；

其他表示？

3.3SQL的數(shù)據(jù)查詢

(3)字符匹配---近似查詢，模糊查詢格式1：[NOT]LIKE’<匹配串>’

含義：查找指定的屬性列值與<匹配串>相匹配的元組。其中匹配串可含：％：代表任意長度(可為0)的字符串。

_：代表任意單個字符。[例3.16]

查所有姓劉或姓王的學(xué)生姓名、學(xué)號和性別。

SELECTsname，sno，sexFROMStudentWHEREsnameLIKE‘劉％’ORsnameLIKE‘王％’；?查詢所有不姓劉或不姓王的學(xué)生姓名、學(xué)號和性別。NOTNOT注意：一個漢字要占兩個字符的位置3.3SQL的數(shù)據(jù)查詢格式2：LIKE’<匹配串>’ESCAPE’<換碼字符>’若要查的串本身含％或_，用ESCAPE‘<換碼字符>’對通配符進(jìn)行轉(zhuǎn)義。例：查“DB_”開頭，且倒數(shù)第2個字符為i的課程情況。

SELECT*FROMCourseWHEREcnameLIKE‘DB\

_

％i_’ESCAPE’\’；若要查以“DB\_”開頭的呢？……LIKE‘DB\*_

’ESCAPE’*’；

3.3SQL的數(shù)據(jù)查詢

(4)確定集合(IN)例：查詢信息院、數(shù)學(xué)系和計算機(jī)學(xué)院學(xué)生的姓名和性別。

SELECTsname，sexFROMStudentWHEREdeptIN(‘信息院’，‘?dāng)?shù)學(xué)系’，‘計算機(jī)學(xué)院’);?查詢不是這三個系的學(xué)生的姓名和性別。

NOT

3.3SQL的數(shù)據(jù)查詢一般形式：SELECT<A>FROM<R>WHERE<F>查詢目標(biāo):為屬性名表或表達(dá)式或‘*’數(shù)據(jù)來源:表或視圖選擇行(元組)的條件將選擇結(jié)果按<列名>的值進(jìn)行分組選擇滿足條件的小組按<列名>排序查詢結(jié)果目標(biāo)A來源R[條件F][分組[選組]][排序]GROUPBY<列名>HAVING<條件>ORDERBY<列名>[ASC|DESC]；3.3SQL的數(shù)據(jù)查詢?nèi)?、對查詢結(jié)果排序[例3.20]

查詢?nèi)w男學(xué)生的學(xué)號、姓名，結(jié)果按所在的系升序排列，同一系中的學(xué)生按年齡降序排列。

來源R

條件F目標(biāo)AStudent表排序二排序一SELECTsno，snameFROMStudentWHEREsex=‘男’

ORDERBYdept，ageDESC；

3.3SQL的數(shù)據(jù)查詢3.3.2表達(dá)式與函數(shù)的使用

一、使用集函數(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]<列名>)求一列值的最小值缺省值無重復(fù)值3.3SQL的數(shù)據(jù)查詢例:

查詢女學(xué)生的總?cè)藬?shù)和平均年齡。

SELECTCOUNT(*),AVG(age)FROMStudentWHEREsex=‘女’;[例3.22]

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

SELECTCOUNT(DISTINCTsno)FROMSC;3.3SQL的數(shù)據(jù)查詢[例3.24]

查詢選修001號課程并及格的學(xué)生的最高分?jǐn)?shù)、最低分及總分。

SELECTMAX(grade),MIN(grade),SUM(grade)FROMSCWHEREcno=‘001’andgrade>=60;3.3SQL的數(shù)據(jù)查詢

二、對查詢結(jié)果分組：

GROUPBY子句將查詢結(jié)果表按某一(多)列值分組，值相等的為一組。目的：細(xì)化集函數(shù)的作用對象。[例3.27]求每個學(xué)生的平均成績。

SELECTsno，AVG(grade)FROMSCGROUPBYsnoHAVING短語：在各組中選擇滿足條件的小組。WHERE子句：在表中選擇滿足條件的元組。WHERE?并將其超過88分的輸出。HAVINGAVG(grade)>88；分組情況snocnograde000101c190000101c285000101c380010101c185010101c275020101c380……分組情況及查詢結(jié)果示意圖3組…1組2組snoAVG(grade)0001018501010180……例:求每個學(xué)生的平均成績。

SELECTsno,AVG(grade)FROMSCGROUPBYsno；求每門課程的平均成績？3.3SQL的數(shù)據(jù)查詢例：查詢至少選修4門課程的學(xué)生學(xué)號。

SELECTsnoFROMSCGROUPBYsnoHAVINGCOUNT(*)>=43.3SQL的數(shù)據(jù)查詢[例3.28]

找出選課學(xué)生超過30人的課程的平均成績及選課人數(shù)。

SELECTcno,AVG(grade),COUNT(*)ASst_numberFROMSCGROUPBYcnoHAVINGst_number>30;3.3SQL的數(shù)據(jù)查詢[例3.29]

求學(xué)生關(guān)系中女生的每一年齡組（超過20人）有多少，要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同時按年齡降序排列。

SELECTage,COUNT(sno)ASnumberFROMStudentWHEREsex=‘女’

GROUPBYageHAVINGnumber>20ORDERBYnumber,ageDESC;3.3SQL的數(shù)據(jù)查詢例：按平均成績的降序給出所有課程都及格的學(xué)生號及其平均成績,其中成績統(tǒng)計時不包括008號考查課。SELECTsno，AVG(grade)ASavg_gFROMSCWHEREcno<>’008’GROUPBYsnoHAVINGMIN(grade)>=60ORDERBYavg_gDESC；來源RSC表3.3SQL的數(shù)據(jù)查詢3.3.3多表查詢

1.子查詢（嵌套查詢）查詢塊：SELECT<A>FROM<R>WHERE<F>子查詢（嵌套查詢）：一個查詢塊嵌套在另一查詢塊中作為條件的查詢。子查詢一般跟在IN、SOME（ANY）、ALL和EXIST等謂詞后面。

3.3SQL的數(shù)據(jù)查詢[例3.35]查詢選修了數(shù)據(jù)庫課程的學(xué)生號、成績。

SELECTsno，gradeFROMSCWHEREcnoIN

(SELECTcnoFROMCourseWHEREcname=‘?dāng)?shù)據(jù)庫’)；

[例3.30]找出年齡超過平均年齡的學(xué)生姓名。SELECTsnameFROMStudentWHEREage＞

(SELECTAVG(age)FROMStudent)；3.3SQL的數(shù)據(jù)查詢帶SOME(ANY)、ALL謂詞的子查詢>SOME

大于子查詢結(jié)果中的某個值<ALL

小于子查詢結(jié)果中的所有值注意：SOME、ALL必須與關(guān)系比較符同時使用。例：查詢其他系比信息院某學(xué)生年齡小的學(xué)生名、年齡。

SELECTsname，ageFROMStudentWHEREage<SOME

(SELECTageFROMStudentWHEREdept=‘信息院’)

ANDdept<>‘信息院’；(SELECTMAX(age)

<

3.3SQL的數(shù)據(jù)查詢[例3.31]查平均成績最高的學(xué)生號。SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(grade)

>=ALL

(SELECTAVG(grade)FROMSCGROUPBYsno)；姓名？集合一個3.3SQL的數(shù)據(jù)查詢[例3.32]找出有一門選課成績在90分以上的學(xué)生姓名。

SELECTsnameFROMStudentWHEREsnoIN

(SELECTDISTINCTsnoFROMSCWHEREgrade>=90)；=SOME3.3SQL的數(shù)據(jù)查詢

SELECTStudent.*，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；

2.條件連接查詢

通過連接，使查詢的數(shù)據(jù)從多個表中取得。

等值連接條件格式為：

[<表名1>.]<列名1>=[<表名2>.]<列名2>[例3.33]查詢每個學(xué)生的學(xué)號、姓名及所選修的課程號、成績。自然連接？SC.*Student.sno，sname，cno，grade3.3SQL的數(shù)據(jù)查詢[例3.34]找出籍貫為湖北或河北，選課成績?yōu)?0分以上的學(xué)生的姓名、課號和成績。

SELECTsname，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.snoANDplaceIN(‘湖北’，‘河北’)ANDgrade＞＝90；3.3SQL的數(shù)據(jù)查詢[例3.35]查詢選修了數(shù)據(jù)庫的學(xué)生號、成績學(xué)分？SELECTsno，gradeFROMSC，CourseWHERESC.cno=CoANDcname=‘DB’；,credit注意：子查詢不能用ORDERBY子句為什么？不相關(guān)子查詢(SELECTcnoFROMCourseWHEREcname=‘DB’)；

SELECTsno，gradeFROMSCWHEREcnoIN=方法1：方法2：3.3SQL的數(shù)據(jù)查詢3.自身連接查詢[例3.37]找出年齡比“王迎”同學(xué)大的同學(xué)的姓名及年齡。SELECTs1.sname，s1.ageFROMStudentASs1，StudentASs2WHEREs1.age>s2.ageANDs2.sname=‘王迎’；

其他方法？3.3SQL的數(shù)據(jù)查詢例：查每門課的間接先修課(即先修課的先修課)。cnocnamecreditpcno001數(shù)學(xué)6002英語4003高級語言4001004數(shù)據(jù)結(jié)構(gòu)4003005數(shù)據(jù)庫3004006操作系統(tǒng)3004d2d1003003004001SELECTo,d2.pcnoFROMCourseASd1,CourseASd2WHEREo;3.3SQL的數(shù)據(jù)查詢4.FROM子句中的子查詢在FROM子句中可以使用子查詢，其查詢的結(jié)果表稱為導(dǎo)出關(guān)系（DerivedRelation）。[例3.38]查出選課成績在80分以上的女學(xué)生的姓名、課程名和成績。SELECTsname，cname，gradeFROM(SELECTsname，cname，gradeFROMStudent，SC，CourseWHEREStudent.sno=SC.snoANDoANDsex＝'女')ASTemp(sname，cname，grade)WHEREgrade>=80；

導(dǎo)出關(guān)系3.3SQL的數(shù)據(jù)查詢5.相關(guān)子查詢當(dāng)一個子查詢的判斷條件涉及到一個來自外部查詢的列時，稱為相關(guān)子查詢。帶存在謂詞的子查詢例：查詢所有選修了1號課程的學(xué)生姓名。執(zhí)行過程：沒有選修NOT①②③EXISTS作用:

若子查詢結(jié)果非空,則父查詢的WHERE子句返回真,否則返回假----產(chǎn)生邏輯值。SELECTsnameFROMStudentWHERE

EXISTS

(SELECT*FROMSCWHEREsno=Student.snoANDcno=’1’)；3.3SQL的數(shù)據(jù)查詢[例3.42]

查詢選修了全部課程的學(xué)生姓名。轉(zhuǎn)換成：查詢這樣的學(xué)生，沒有一門課程是他不選修的。

(x)P≡(x(P))

SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREsno=Student.snoANDcno=Co))；每門課都沒選某學(xué)生選某課3.3SQL的數(shù)據(jù)查詢練習(xí)：查詢至少選修了學(xué)號為200101的學(xué)生選修的全部課程的學(xué)生學(xué)號。SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=‘200101’ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDo))3.3SQL的數(shù)據(jù)查詢6.集合運(yùn)算（1）UNION

（并）（2）INTERSECT

（交）（3）EXCEPT

（差）[例3.44]求選修了001或002號而沒有選003號課程的學(xué)生號。

(SELECTsnoFROMSCWHEREcno=’001’ORcno=’002’)EXCEPT(SELECTsnoFROMSCWHEREcno=‘003’)；3.3SQL的數(shù)據(jù)查詢3.3.4自然連接與外連接

1.自然連接(NATURALJOIN)[例3.45]查籍貫為湖北、課程成績在90分以上的學(xué)生檔案及其成績情況。

SELECT*FROMStudentNATURALJOINSCWHEREplace=‘湖北’ANDgrade>=90；2.

外連接常用形式：

NATURAL<外連接類型>左外連接。LEFT[OUTER]JOIN，保留左關(guān)系的所有元組。右外連接。RIGHT[OUTER]JOIN，保留右關(guān)系的所有元組。全外連接。FULL[OUTER]JOIN，保留左右兩關(guān)系的所有元組。

3.3SQL的數(shù)據(jù)查詢SELECT*FROMStudentNATURALJOINSC;?FROMStudentNATURAL

LEFTOUTERJOINSC；用左外連接產(chǎn)生未選課的空行教師表：Teacher(教師號，姓名，所屬大學(xué)，職稱)

任職表：Post(編號，姓名，職務(wù))

例：SELECT*FROMTeacher

NATURALFULLOUTERJOINPost；未選課？例:

查每個學(xué)生及其選課情況。P79表3-43.3SQL的數(shù)據(jù)查詢3.3.5SQL中的空值處理1.空值的含義值暫未知。值不適用。值需隱瞞。2.空值的測試空值的測試：ISNULL和ISNOTNULL。例：查詢?nèi)鄙龠x課成績的學(xué)生號和相應(yīng)的課程號。

SELECTSno，CnoFROMSCWHEREgradeISNULL；

3.3SQL的數(shù)據(jù)查詢3.3.6遞歸合并查詢

遞歸合并語句格式：WITHRECURSIVE<臨時表R>AS<R的定義>UNION<涉及到R的查詢><遞歸結(jié)果查詢>[例3.50]對表3.5所示的航班表Flight(airline，from，to，depart，arrive)，求出：能從一個城市飛到另一個城市的城市對集合(含直接到達(dá)和間接中轉(zhuǎn)到達(dá))。3.3SQL的數(shù)據(jù)查詢

直接到達(dá)：

Reach(f，t)=Flight(a，f，t，d，r)f，t在表的同一個元組中。間接到達(dá)：Reach(f，t)=Flight(a，f，t1，d，r)ANDReach(t1，t)

SQL語句：WITHRECURSIVEReach(from，to)AS(SELECTfrom，to//選出直接到達(dá)城市對

FROMFlight)UNION(SELECTFlight.from，Reach.to//選出間接到達(dá)城市對

FROMFlight，Reach//遞歸

WHEREFlight.to=Reach.from)SELECT*FROMReach；

3.3SQL的數(shù)據(jù)查詢[例3.51]有下列所示的一棵二叉樹：abecfd162453id

pidname1NULLa21b31c42d53e63f求出:該子樹根結(jié)點k的所有子孫結(jié)點的集合。

3.3SQL的數(shù)據(jù)查詢分析：定義結(jié)果集為Tsub(id，name)：結(jié)點k的所有子女：Tsub(id，name)=Tree(id，pid，name)ANDpid=k結(jié)點k的所有子女的孩子（k的子孫）：Tsub(id，name)=Tsub(id1，name1)ANDTree(id，id1，name)SQL:WITHRECURSIVETsub(id，name)AS(SELECTid，nameFROMTree//結(jié)點k的所有子女

WHEREpid=k)UNION(SELECTTree.id，TFROMTsub，Tree//結(jié)點k的所有子孫

WHERETsub.id=Tree.pid)

SELECT*FROMTsub；

3.4SQL的數(shù)據(jù)更新一、插入數(shù)據(jù)格式：INSERTINTO<表名>[(<列名1>[,<列名2>…)]

<子查詢>|VALUES(<常量1>[，<常量2>]…)；說明：若插入全部屬性值，則屬性列可以省略。屬性值與相對應(yīng)的屬性列的數(shù)據(jù)類型要匹配。插入時，表定義中說明NOTNULL的列不能取空值。若已定義主碼、外碼及其他用戶完整性約束，向表中更新元組,系統(tǒng)自動支持其完整性。3.4SQL的數(shù)據(jù)更新[例3.52]向student表插入一行。

insertintostudentvalues(200510,’王雪’,20,’女’,’英語系’)[例3.53]向st1_grade表插入多行。

insretintost1_grade(gno,name,avg)selectstudent.sno,sname,avg(grade)asavg_goodfromstudent,scwherestudent.sno=sc.snogroupbysnohavingavg_good>=903.4SQL的數(shù)據(jù)更新二.修改數(shù)據(jù)格式：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]…

[WHERE<條件[子查詢]>];[例3.55]學(xué)生“左彼”在001號課程考試中作弊，該課成績作零分計。UPDATESCSETgrade＝0WHEREcno＝'001'ANDsno=

(SELECTsnoFROMStudentWHEREsname＝‘左彼’);3.4SQL的數(shù)據(jù)更新三.刪除數(shù)據(jù)格式：DELETEFROM<表名>

[WHERE<條件[子查詢]>];例：將選課人數(shù)不足10人的課程記錄刪除。

deletefromcoursewherecnoin(selectcnofromscgroupbycnohavingcount(*)<10)3.4SQL的數(shù)據(jù)更新四.構(gòu)造數(shù)據(jù)類型

1.聚合類型

<數(shù)據(jù)類型>ARRAY[<無符號整數(shù)>]例：CREATETABLEmailout(nameCHAR(8)，

addressCHAR(20)

ARRAY[3])；INSERTINTOmailout(name，address)VALUES('Wang'，ARRAY['20HongsangRoad'，'Wuhan'，'430079'])；3.4SQL的數(shù)據(jù)更新2.行類型格式：

ROW(<列名><數(shù)據(jù)類型>[{，<列名><數(shù)據(jù)類型>}…]例：CREATETABLElineage(nameCHAR(8)，

statusCHAR(10)，

lastoneROW(babyCHAR(8)，birthDATE))；……//插入數(shù)據(jù)操作

SELECTname，status，lastoneFROMlineageWHERElastone.baby='Liming'；作為一個整體，是表中的一項取行變量的一分量3.5視圖一、視圖的概念及作用

是從一個或幾個基本表(或視圖)導(dǎo)出的表，是虛表。與表一樣可被查詢。對視圖的更新操作有一定的限制。對視圖的一切操作最終將轉(zhuǎn)換為對基本表的操作。

視圖的作用：（1）簡化結(jié)構(gòu)及復(fù)雜操作。

（2）多角度地、更靈活地共享。

（3）提高邏輯獨立性。

（4）提供安全保護(hù)。

S#SNSUM虛表S#SNAVG…實表S#SUM…3.5視圖一、創(chuàng)建與使用視圖1.創(chuàng)建視圖一般格式：

CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]AS<子查詢>

[WITHCHECKOPTION]；更新操作時要保證操作的行滿足視圖定義中的謂詞條件。其中：子查詢不允許含有ORDERBY子句和DISTINCT短語。3.5視圖例：建立信息院學(xué)生的視圖，要求進(jìn)行更新時仍保證該視圖只有信息院的學(xué)生。

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’

WITHCHECKOPTION；行列子集視圖：視圖是從單個基本表導(dǎo)出，并且只是去掉了基本表的某些行和某些列，但保留了碼。D_Student視圖？3.5視圖帶虛擬列的視圖：是指設(shè)置了一些基本表中并不存在的派生列(虛擬列)的視圖。例：定義一個反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(sno，sname，sbirth)ASSELECTsno，sname，2008-ageFROMStudent；分組視圖：帶有集函數(shù)和GROUPBY子句查詢所定義的視圖。例：將學(xué)生的學(xué)號及其平均成績定義為一個視圖。

CREATEVIEWS_G(sno，gavg)ASSELECTsno，AVG(grade)FROMSCGROUPBYsno；3.5視圖三、查詢視圖視圖消解—把視圖定義中的子查詢和用戶查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢過程。四、更新視圖

最終要轉(zhuǎn)換為對基本表的更新。注意：并不是所有的視圖都可以更新的。

P.90DB2對視圖更新規(guī)定二、刪除視圖

DROPVIEW<視圖名>[CASCADE|RESTRICT]

注意：視圖刪除后，視圖的定義將從數(shù)據(jù)字典中自動刪除。但由該視圖導(dǎo)出的其他視圖定義應(yīng)用該語句一一刪除。3.6嵌入式SQL

一、概述嵌入式SQL：作用：負(fù)責(zé)操縱DB。主語言：作用：負(fù)責(zé)控制流程及處理操縱結(jié)果。兩種實現(xiàn)方法：預(yù)編譯擴(kuò)充、修改主語言編譯程序使之能處理SQL預(yù)編譯方法：

預(yù)處理程序

宿主語言語句+函數(shù)調(diào)用宿主語言編譯程序目標(biāo)程序連接程序

可執(zhí)行程序OS

運(yùn)行結(jié)果宿主語言語句+嵌入式SQL語句3.6嵌入式SQL二、嵌入式SQL的工作原理

1.嵌入式SQL與主語言的接口

(1)狀態(tài)變量SQLSTATE

(描述系統(tǒng)當(dāng)前狀態(tài)和運(yùn)行情況)

\0×

×

×

×

×

一般類別特殊子類SQLSTATE的格式

在每一個SQL語句執(zhí)行之后，DBMS將描述當(dāng)前工作狀態(tài)和運(yùn)行環(huán)境的一個狀態(tài)值放人SQLSTATE中。

3.6嵌入式SQL(2)主變量(HostVariable)

SQL語句中使用的主語言程序變量稱為主變量。(3)游標(biāo)(Cursor)游標(biāo)是系統(tǒng)開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。作用：用戶可以通過游標(biāo)獲取記錄，并賦給主變量，交主語言處理。

游標(biāo)名……………...…………….……………..一條記錄主變量游標(biāo)指針SQL查詢結(jié)果3.6嵌入式SQL2.嵌入式SQL需解決的問題

(1)如何區(qū)分SQL與主語言語句?前綴加EXECSQL，結(jié)束處用END_EXEC(或用分號)。例嵌入到C程序中：EXECSQL<SQL語句>；(2)如何區(qū)分主語言變量與SQL的列變量

?

主變量前加冒號。如:：Cname(3)SQL的集合處理方式與主語言的單記錄處理方式之間的協(xié)調(diào)。需要有一種機(jī)制：能將SQL中的集合量逐個送入主變量內(nèi)，供主程序使用。方法:是在嵌入式SQL中增加一組游標(biāo)語句。3.6嵌入式SQL3.嵌入式SQL的工作過程

主語言程序

SQL語句……

主變量狀態(tài)變量SQLSTATE

游標(biāo)……①執(zhí)行參數(shù)②執(zhí)行狀態(tài)③操作結(jié)果

SQL工作原理及過程示意圖3.6嵌入式SQL三、嵌入式SQL的程序組成嵌入式SQL語句可分為：可執(zhí)行語句（數(shù)據(jù)定義、控制、操縱）說明性語句（說明變量、游標(biāo))程序的主要部分(1)DECLARE段(用于定義主變量)格式：EXECSQLBEGINDECLARESECTION；

……//主變量說明

EXECSQLENDDECLARESECTION；(2)CONNECT語句（建立SQL連接）格式：EXECSQLCONNECTTO<SQL服務(wù)器或數(shù)據(jù)庫名>USER<用戶名>；

3.6嵌入式SQL(3)WHENEVER語句使用WHENEVER語句可以簡化每條嵌入式SQL語句后編寫檢查SQLSTATE值的程序。

WHENEVER語句通知預(yù)編譯程序如何處理三種異常：

WHENEVERSQLERROR:通知預(yù)編譯程序產(chǎn)生處理錯誤的代碼(SQLSTATE<0).

WHENEVERSQLWARING:通知預(yù)編譯程序產(chǎn)生處理警報的代碼(SQLSTATE=1).

WHENEVERNOTFOUND:通知預(yù)編譯程序產(chǎn)生沒有查到內(nèi)容的代碼(SQLSTATE=100).3.6嵌入式SQL用戶可指定預(yù)編譯程序采取以下行為：WHENEVER…GOTO:通知預(yù)編譯程序產(chǎn)生一條轉(zhuǎn)移語句。WHENEVER…CONTINUE:通知預(yù)編譯程序讓程序控制流轉(zhuǎn)入到下一個主語言語句。WHENEVER…CALL:通知預(yù)編譯程序調(diào)用函數(shù)。其完整語法如下：

WHENEVER{SQLWARNING|SQLERROR|NOTFOUND}{CONTINUE|GOTOstmt_label|CALLfunction()}(4)應(yīng)用程序體若干可執(zhí)行SQL語句及主語言語句。提交和退出數(shù)據(jù)庫：

COMMITWORKRELEASE；3.6嵌入式SQL四、嵌入式SQL的使用技術(shù)1.無游標(biāo)的操作

說明性語句數(shù)據(jù)定義語句數(shù)據(jù)控制語句數(shù)據(jù)插入語句非當(dāng)前形式的UPDATE、DELETE語句語句查詢結(jié)果為單記錄的SELECT2.帶游標(biāo)的查詢操作

查詢結(jié)果為多記錄。當(dāng)前形式的UPDATE、DELETE語句3.6嵌入式SQL游標(biāo)語句作用：把對集合的操作轉(zhuǎn)換為對單個記錄的處理。使用游標(biāo)的步驟為：

(1)定義游標(biāo)分配緩沖區(qū)并對應(yīng)查詢語句。

格式：EXECSQLDECLARE

<游標(biāo)名>CURSORFOR<SELECT語句>；

3.6嵌入式SQL(2)打開游標(biāo)執(zhí)行相應(yīng)的語句，把查詢結(jié)果取到緩沖區(qū)中。

格式：

EXECSQLOPEN<游標(biāo)名>；(3)推進(jìn)游標(biāo)推進(jìn)游標(biāo)指針并取當(dāng)前記錄。格式：EXECSQLFETCH<游標(biāo)名>INTO:<主變量1>，:<主變量2>，…3.6嵌入式SQL

(4)關(guān)閉游標(biāo)釋放緩沖區(qū)及其他資源。格式：EXECSQLCLOSE<游標(biāo)名>；

查詢語句游標(biāo)名SELECT結(jié)果12…n主變量3.6嵌入式SQL[例3.76]使用游標(biāo)的示例。#include<stdio.h>EXECSQLBEGINDECLARESECTIONVARCHARuid[20];INTst_no;CHARc_no[4];FLOATsc_grade;EXECSQLENDDECLARESEXTION;/*SQL中使用的主變量說明*/voidmain(){strcpy(uid,”YIN”);EXECSQLWHENEVERSQLEXCEPIONGOTOerroe_abort;EXECSQLCONNECTTOstudent_dbUSER:uid;/*建立與DB的連接*/printf(“connectedtostudent_dbbyUser%s\n”,uid);EXECSQLDECLAREcu1CURSORFOR/*定義游標(biāo)*/SELECTsno,cno,gradeFROMscWHEREsno=:st_no;scanf(“pleaseenterthestudentno:%d”,st_no);EXECSQLOPENcu1;/*打開游標(biāo)*/3.6嵌入式SQLEXECSQLWHENEVERNOTFOUNDDOBREAK;printf(“snocourse_nograde\n”);for(;;){EXECSQLFETCHcu1INTO:st_no,:c_no,:sc_grade;/*推進(jìn)游標(biāo)*/printf(“%-10d,%-4s,%-8.2f\n”,st_no,c_no,sc_grade);}EXECSQLCLOSEcu1;/*關(guān)閉游標(biāo)*/EXECSQLWHENEVERSQLEXCEPIONCONTINUE;EXECSQLCOMMITWORKRELEASE;/*提交并退出DB*/EXECSQLDISCONNECTCURRENT;/*撤消當(dāng)前連接*/exit(0);}3.7動態(tài)SQL動態(tài)SQL語句的目的不是在編譯時確定SQL的表和列，而是讓程序在運(yùn)行時提供，并將SQL語句文本傳給DBMS執(zhí)行。動態(tài)SQL執(zhí)行過程：（1）執(zhí)行PREPARE語句要求DBMS分析、確認(rèn)和優(yōu)化語句，并為其生成執(zhí)行計劃。語句格式：PREPARE<語句名>FROM:<主變量>功能：接收含有SQL語句的主變量，并把該語句送到DBMS。DBMS編譯該語句并生成執(zhí)行計劃。（2）用EXECUTE語句執(zhí)行”執(zhí)行計劃”。[P.99例3.77]注意以下幾句：strcpy(select_st,”SELECTsno,sname,ageFROMstudentWHERE”);scanf(“pleaseenterthequerycondition:%s\n”,query_f);strcat(select_st,query_f);/*將SQL查詢語句保存在變量select_st中*/EXECSQLPREPARE

sql_stFROM:select_st

/*生成執(zhí)行計劃*/EXECSQLDECLAREst_cursorCURSORFORsql_st3.7動態(tài)SQL補(bǔ)充例：在sc表中插入一條記錄，記錄的值由程序決定。#include<string.h>#include<stdio.h>main(){EXECSQLBEGINDECLARESECTION;charperp[]=“INSERTINTOscVALUES(?,?,?)”;charsno[6];charcno[4];intgrade;EXECSQLENDDECLARESECTION;EXECSQLCONNECTTOstudentUSERsa.;參數(shù)標(biāo)志3.7動態(tài)SQLEXECSQLPREPAREprep_statFROM:prep;if(SQLSTATE==0)/*SQL語句執(zhí)行成功*/{strcpy(sno,”995201”);strcpy(cno,”c601”);grade=76;EXECSQLEXECUTEprep_statUSING:sno,:cno,:grade;}}代替準(zhǔn)備語句中的參數(shù)標(biāo)志“?”3.8SQL編程與擴(kuò)充一、流程控制功能以SQL3為標(biāo)準(zhǔn)的關(guān)系型DBMS中增加了流程控制功能。下列為在SQLServer中提供的流程控制語句：1.BEGIN…END語句（相當(dāng)于C的復(fù)合語句）

BEGIN…END語句用于將多條Transact-SQL語句組合成一個語句塊，并將它們視為一個單元處理。在條件語句和循環(huán)語句等控制流程語句中，當(dāng)符合特定條件需要執(zhí)行兩個或多個語句時，就應(yīng)該使用BEGIN…END語句將這些語句組合在一起?？刂屏髡Z句2.IF…ELSE語句（條件判斷語句）格式：IFBoolean_expressionsql語句|語句塊

[ELSEsql語句|語句塊]注意：條件表達(dá)式可以是SELECT語句，但必須用圓括號括起來。

IF…ELSE語句之例IF

(SELECTAVG(grade)FROMSCWHEREcno=‘001')>80BEGINPRINT'課程:001'PRINT'考試成績還不錯'ENDELSEBEGINPRINT'課程:001'PRINT'考試成績一般'END

控制流語句3.CASE語句（相當(dāng)于C的switch語句）格式：CASE[input_expression]WHENwhen_expressionTHENresult_expression[…n][ELSEelse_result_expression]

END

執(zhí)行過程：先計算CASE后面表達(dá)式的值，然后將其與WHEN后面的表達(dá)式逐個進(jìn)行比較，若相等則返回THEN后面的表達(dá)式，否則返回ELSE后面的表達(dá)式。

可帶多個WHEN，但只能有一個ELSE。

CASE后不帶表達(dá)式的稱為搜索CASE格式。CASE語句之例有表teacher(tno,tname,tsex,prof,depart)SELECTtnameAS'姓名',departAS'單位',

CASEprofWHEN'教授'THEN'高級職稱'WHEN'副教授'THEN'高級職稱'WHEN'講師'THEN'中級職稱'WHEN'助教'THEN'初級職稱'

ENDAS'職稱類型'FROMteacher搜索

CASE語句之例SELECTsnoAS'學(xué)號',cnoAS'課程號',

CASE

WHENgrade>=90THEN'A'WHENgrade>=80THEN'B'WHENgrade>=70THEN'C'WHENgrade>=60THEN'D'WHENgrade<60THEN'E'

ENDAS'成績'FROMSCORDERBYsno控制流語句4.WHILE語句（循環(huán)語句）格式：WHILEBoolean_expressionsql語句|語句塊

[BREAK]sql語句|語句塊

[CONTINUE]功能：重復(fù)執(zhí)行SQL語句或語句塊。當(dāng)WHILE后面的條件為真，就重復(fù)執(zhí)行語句。其中，BREAK語句使程序完全跳出循環(huán)，CONTINUE語句使程序跳過CONTINUE語句后面的語句，回到WHILE循環(huán)的第一行命令。

控制流語句5.GOTO語句

GOTO語句可以實現(xiàn)無條件的跳轉(zhuǎn)。格式：GOTOlabel

……label:label:要跳轉(zhuǎn)的語句標(biāo)號，符合標(biāo)識符的規(guī)定。遇到GOTO語句后，直接跳轉(zhuǎn)到label標(biāo)號處繼續(xù)執(zhí)行，而GOTO后面的語句不被執(zhí)行。GOTO語句之例例：輸出200101學(xué)號的平均成績，若沒有該學(xué)生成績顯示相應(yīng)提示信息。DECLARE@avgfloatIF(SELECTCOUNT(*)FROMSCWHEREsno=‘200101’)=0

GOTOlabel1BEGINPRINT‘200101學(xué)號學(xué)生的平均成績：’

SELECT@avg=AVG(grade)FROMSCWHEREsno=‘200101’PRINT@avgRETURNENDlabel1:PRINT‘200101學(xué)號的學(xué)生無成績’控制流語句6.RETURN語句

RETURN語句用于無條件地終止一個查詢、存儲過程或者批處理。格式：RETURN[integer_expression]integer_expression：一個整數(shù)值，是RETURN語句的返回值。

RETURN語句與BREAK語句的作用類似，但RETURN語句可以返回一個整數(shù)值。函數(shù)二、函數(shù)

函數(shù)是用于封裝經(jīng)常執(zhí)行的邏輯的子例程。SQLServer提供了許多內(nèi)置函數(shù)，也允許用戶自定義函數(shù)。用戶定義函數(shù)格式：CREATEFUNCTION函數(shù)名稱（形式參數(shù)名稱數(shù)據(jù)類型[,…]）

RETURNS

返回數(shù)據(jù)類型

BEGIN

函數(shù)內(nèi)容

RETURN表達(dá)式

END函數(shù)調(diào)用：變量=用戶名.函數(shù)名稱（實際參數(shù)列表）用戶定義函數(shù)之例USEtestGOCREATEFUNCTIONCubicVolume--定義函數(shù)(@CubeLengthdecimal(4,1),@CubeWidthdecimal(4,1),@CubeHeightdecimal(4,1))--形參表RETURNSdecimal(12,3)--函數(shù)返回值數(shù)據(jù)類型BEGIN--函數(shù)體

RETURN(@CubeLength*@CubeWidth*@CubeHeight)ENDGOPRINT‘長、寬、高分別為6，4，3的立方體的體積’+_CAST(dbo.CubicVolume(6,4,3)ASchar(10))GO用戶定義函數(shù)之例USEschoolGOCREATEFUNCTIONstudent1(@deptnamechar(20))--定義函數(shù)RETURNS@stTABLE--函數(shù)返回表(snochar(5),snamechar(8),cnochar(5),gradeint)BEGIN--函數(shù)體INSERT@stSELECTo,sc.gradeFROMstudent,scWHEREstudent.sno=sc.snoANDstudent.dept=@deptnameRETURNENDGO函數(shù)調(diào)用：SELECT*FROMstudent1(‘計算機(jī)學(xué)院’)存儲過程三、存儲過程存儲過程--是為實現(xiàn)某個特定任務(wù)而編寫的一段代碼。存儲過程的優(yōu)點：實現(xiàn)模塊化編程。一個存儲過程可以被多個用戶共享和重用。加快程序的運(yùn)行速度。第一次執(zhí)行后的存儲過程會在緩沖區(qū)中創(chuàng)建查詢樹，使得第二次執(zhí)行時不用進(jìn)行預(yù)編譯?？梢詼p少網(wǎng)絡(luò)流量。存儲過程存儲在服務(wù)器上，只有觸發(fā)執(zhí)行存儲過程的命令和返回結(jié)果才在網(wǎng)絡(luò)上傳輸?？梢蕴岣邤?shù)據(jù)庫安全性?？梢灾皇谟栌脩粼L問存儲過程的權(quán)限，而不授予其直接修改數(shù)據(jù)表的權(quán)限。

存儲過程存儲過程的分類：系統(tǒng)存儲過程。由系統(tǒng)自動創(chuàng)建，主要存儲在master數(shù)據(jù)庫中，一般以sp_為前綴。系統(tǒng)存儲過程完成的功能主要是從系統(tǒng)表中獲取信息。用戶自定義存儲過程。由用戶創(chuàng)建并能完成某一特定功能的存儲過程。存儲過程

創(chuàng)建存儲過程格式：CREA