第3章數(shù)據(jù)庫語言與訪問接口

第3章數(shù)據(jù)庫語言與訪問接口第一頁，共118頁。3.1

SQL簡介

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

SQL-86。SQL的第一個(gè)標(biāo)準(zhǔn)是1986年10月由美國國家標(biāo)準(zhǔn)化組織(ANSI)公布的。

SQL-89。ANSI以后通過對SQL-86的不斷修改和完善，于1989年第二次公布了SQL標(biāo)準(zhǔn)，即SQL-89，該標(biāo)準(zhǔn)增強(qiáng)了完整性的語言特征。

SQL-92(SQL2)。1992年又公布了SQL-92標(biāo)準(zhǔn)，該標(biāo)準(zhǔn)增加了支持對遠(yuǎn)程數(shù)據(jù)庫的訪問，擴(kuò)充了數(shù)據(jù)類型、操作類型、動態(tài)SQL等許多新的特征。

SQL-99(SQL3)。完成于1999年的SQL-99修訂本具有更高級的特征。引入了支持對象-關(guān)系DBMS模型的SQL，擴(kuò)展了對象、遞歸、觸發(fā)等許多新的特征，支持用戶自定義函數(shù)、自定義數(shù)據(jù)類型。第二頁，共118頁。3.1

SQL簡介2.SQL的功能、特點(diǎn)

功能：數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)操縱數(shù)據(jù)控制

特點(diǎn)：

綜合統(tǒng)一SQL語言集數(shù)據(jù)定義、操縱和控制功能于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動，包括定義關(guān)系模式、錄入數(shù)據(jù)以建立數(shù)據(jù)庫、查詢、更新、維護(hù)、數(shù)據(jù)庫重構(gòu)、數(shù)據(jù)庫安全性控制等一系列操作要求。第三頁，共118頁。3.1

SQL簡介

高度非過程化

非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言是面向過程的語言，用其完成某項(xiàng)請求，必須指定存取路徑。而用SQL語言進(jìn)行數(shù)據(jù)操作，用戶只需提出“做什么”，而不必指明“怎么做”，因此用戶無需了解存取路徑，存取路徑的選擇以及SQL語句的操作過程由系統(tǒng)自動完成。這不但大大減輕了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨(dú)立性。靈活的使用方式SQL語言既是自含式語言，又是嵌入式語言。作為自含式語言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對數(shù)據(jù)庫進(jìn)行操作。作為嵌入式語言，SQL語句能夠嵌入到高級語言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序員設(shè)計(jì)程序時(shí)使用。而在兩種不同的使用方式下，SQL語言的語法結(jié)構(gòu)基本上是一致的。

第四頁，共118頁。3.1

SQL簡介簡潔、通用、功能強(qiáng)

SQL語言功能極強(qiáng)，設(shè)計(jì)巧妙，語言簡潔，完成數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制的核心功能只用了9個(gè)動詞，如表3-1所示。而且SQL語言語法簡單，接近英語口語，因此容易學(xué)習(xí)，容易使用。第五頁，共118頁。3.1

SQL簡介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)第六頁，共118頁。3.SQL的基本組成基本表是本身獨(dú)立存在的表，在SQL中一個(gè)關(guān)系就對應(yīng)一個(gè)表。一些基本表對應(yīng)一個(gè)存儲文件，一個(gè)表可以帶若干索引，索引也存放在存儲文件中。存儲文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲文件的物理文件結(jié)構(gòu)是任意的。視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表。用戶可以用SQL語言對視圖和基本表進(jìn)行查詢。在用戶眼中，視圖和基本表都是關(guān)系，而存儲文件對用戶是透明的。

第七頁，共118頁。

3.SQL的基本組成（2）SQL語言的組成數(shù)據(jù)定義語言（DDL）數(shù)據(jù)操縱語言（DML）數(shù)據(jù)控制語言（DCL）嵌入與會話規(guī)則（3）SQL的語句類型SQL模式語句SQL數(shù)據(jù)語句SQL事務(wù)與控制語句SQL連接、會話及診斷語句第八頁，共118頁。3.1

SQL簡介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）第九頁，共118頁。3.1

SQL簡介5、SQL環(huán)境（1）SQL模式與目錄SQL模式：基本表、視圖、角色等的集合。好處：允許在不同的SQL模式中出現(xiàn)同名的基表名或視圖名。目錄：SQL環(huán)境中所有模式的集合。定位基表的方式：<目錄名>．<模式名>．<表名>（2）SQL環(huán)境設(shè)置默認(rèn)的目錄和模式設(shè)置用戶身份

第十頁，共118頁。3.2SQL的數(shù)據(jù)定義1.SQL模式的定義與撤銷（1）SQL模式的定義

（P.68）

CREATESCHEMA＜模式名＞AUTHORIZATION＜用戶名＞[＜CREATEDOMAIN子句＞|＜CREATETABLE子句＞|＜CREATEVIEW＞|……]其中：[…]：表示其中的成分為任選項(xiàng)。<…>：表示其中的成分由用戶具體給定。|：表示其中并列的成分只能擇一。例：CREATESCHEMATeaching_dbAUTHORIZATIONHang；第十一頁，共118頁。3.2SQL的數(shù)據(jù)定義（2）數(shù)據(jù)庫模式的刪除DROPSCHEMA＜模式名＞{CASCADE|RESTRICT}

CASCADE：級聯(lián)式

RESTRICT：約束式（受限式）第十二頁，共118頁。3.2SQL的數(shù)據(jù)定義

2.表的建立和刪除

（1）表的建立命令格式：CREATETABLE[模式名.]<表名>(<列名><類型>[<列約束條件>][，<列名><類型>[<列約束條件>]]…[<表約束條件>])；例：CREATETABLEStudent

(snoCHAR(5)NOTNULL

UNIQUE，snameCHAR(8)NOTNULL，sexCHAR(2)，ageINT，deptCHAR(20)

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

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

主鍵：sno姓名：非空性別：男、女兩值Student表：Course表：主鍵：cno課程名：非空外鍵：pcnoSC表：主鍵：(sno,cno)成績：0—100第十四頁，共118頁。3.2SQL的數(shù)據(jù)定義CREATETABLEStudent

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

);列級完整性約束條件實(shí)體完整性約束條件用戶自定義完整性約束條件第十五頁，共118頁。3.2SQL的數(shù)據(jù)定義CREATETABLECourse

(

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

);參照完整性約束條件第十六頁，共118頁。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))

);第十七頁，共118頁。3.2SQL的數(shù)據(jù)定義（2）表的刪除

格式：DROPTABLE<表名>[CASCADE|RESTRICT]基本表定義一旦刪除，表中的數(shù)據(jù)、在此表上建立的視圖、索引、觸發(fā)器、斷言都將自動被刪除掉。RESTRICT確保只有不具有相關(guān)對象的表才能被撤銷。例：DROPTABLESt-quitCASCADE；

第十八頁，共118頁。3.2SQL的數(shù)據(jù)定義3.表的擴(kuò)充和修改一般格式為：

ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名><完整性約束名>][MODIFY<列名><數(shù)據(jù)類型><數(shù)據(jù)類型>];其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件，MODIFY子句用于修改原有的列定義。（1）在現(xiàn)存表中增加新列

格式：ALTERTABLE<表名>ADD(<列名><數(shù)據(jù)類型>，…)例：ALTERTABLEStudentADD(placeCHAR(20)，addrCHAR(20))；

第十九頁，共118頁。3.2SQL的數(shù)據(jù)定義（2）刪除已存在的某個(gè)列

格式：ALTERTABLE<表名>DROP<列名>[CASCADE|RESTRICT]例：ALTERTABLEStudentDROPaddr；（3）修改原有列的類型

格式：ALTERTABLE<表名>

MODIFY<列名><類型>；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；

第二十頁，共118頁。3.2SQL的數(shù)據(jù)定義（4）補(bǔ)充定義主鍵

格式：ALTERTABLE<表名>

ADDPRIMARYKEY(<列名表>)（5）刪除主鍵

格式：ALTERTABLE<表名>

DROPPRIMARYKEY第二十一頁，共118頁。3.2SQL的數(shù)據(jù)定義4.域定義

域定義用于建立用戶自定義的數(shù)據(jù)類型。命令格式：CREATEDOMAIN<域名>[AS]<數(shù)據(jù)類型>[DEFAULT<缺省值>][<域約束>][例3.3]建立一個(gè)建筑公司的數(shù)據(jù)庫模式，由3個(gè)表組成。其中：

CRETEDOMAINITEM_IDNUMBERIC(4)DEFAULT0CHECK(VALUEISNOTNULL)第二十二頁，共118頁。3.2SQL的數(shù)據(jù)定義5.索引的建立與刪除

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

命令格式：

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

ON<表名>(<列名><次序>[[，<列名><次序>]]…)；次序：升序（ASC，缺?。┙敌颍―ESC）UNIQUE:每一個(gè)索引值只對應(yīng)惟一的數(shù)據(jù)記錄。CLUSTER:建立聚簇索引，即索引項(xiàng)的順序與表中記錄的物理順序一致。

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

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

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

①CREATEUNIQUECLUSTERINDEXStno

ONStudent(Sno)；

②CREATEUNIQUEINDEXScno

ONSC(Sno，CnoDESC)；刪除索引一般格式為：DROPINDEX<索引名>；例：DROPINDEXStno；刪除索引時(shí)，系統(tǒng)會同時(shí)從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。第二十四頁，共118頁。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))第二十五頁，共118頁。

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)A第二十六頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢例：查全體學(xué)生的姓名及其出生年份。SELECTsname，2015-ageFROMStudent；來源R條件F目標(biāo)ASname

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

(2)確定范圍(BETWEEN…AND…)[例3.5]查詢選004號課程且成績在85--95的學(xué)生號。SELECTsnoFROMSCWHEREcno=‘004’ANDgradeBETWEEN85

AND95；

其他表示？

第二十八頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢

(3)字符匹配---近似查詢，模糊查詢格式1：[NOT]LIKE’<匹配串>’含義：查找指定的屬性列值與<匹配串>相匹配的元組。其中匹配串可含：％：代表任意長度(可為0)的字符串。_：代表任意單個(gè)字符。[例]查所有姓劉或姓王的學(xué)生姓名、學(xué)號和性別。

SELECTsname，sno，sexFROMStudentWHEREsnameLIKE‘劉％’ORsnameLIKE‘王％’；?查詢所有不姓劉或不姓王的學(xué)生姓名、學(xué)號和性別。NOTNOT第二十九頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢[例]查姓“歐陽”且全名為三個(gè)漢字的學(xué)生的姓名。

SELECTSname

FROMStudent

WHERESnameLIKE‘歐陽__';注意，由于一個(gè)漢字占兩個(gè)字符的位置，所以匹配串歐陽后面需要跟２個(gè)_。[例]查名字中第二字為“陽”字的學(xué)生的姓名和學(xué)號。

SELECTSname,Sno

FROMStudent

WHERESnameLIKE'__陽%';第三十頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢格式2：LIKE’<匹配串>’ESCAPE’<換碼字符>’若要查的串本身含％或_，用ESCAPE‘<換碼字符>’對通配符進(jìn)行轉(zhuǎn)義。ESCAPE’\’短語表示\為換碼字符，這樣匹配串中緊跟在\后面的字符”_”不再具有通配符的含義，而是取其本身含義，被轉(zhuǎn)義為普通的”_”字符。[例]查以“數(shù)據(jù)_”開頭，且倒數(shù)第2個(gè)漢字為”原”的課程情況。SELECT*FROMCourseWHEREcnameLIKE‘?dāng)?shù)據(jù)\

_

％原__’ESCAPE’\’；

若要查以“數(shù)據(jù)\_”開頭的呢？

……LIKE‘?dāng)?shù)據(jù)\*_

’ESCAPE’*’；第三十一頁，共118頁。

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

(4)確定集合(IN)例：查詢信息院、數(shù)學(xué)系和計(jì)算機(jī)學(xué)院學(xué)生的姓名和性別。SELECTsname，sexFROMStudentWHEREdeptIN(‘信息院’，‘?dāng)?shù)學(xué)系’，‘計(jì)算機(jī)學(xué)院’);?查詢不是這三個(gè)系的學(xué)生的姓名和性別。

NOT

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

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

來源R

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

第三十四頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢四、表達(dá)式與函數(shù)的使用

1、使用集函數(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]<列名>)求一列值的最小值缺省值無重復(fù)值第三十五頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢例:查詢女學(xué)生的總?cè)藬?shù)和平均年齡。

SELECTCOUNT(*)AS女學(xué)生人數(shù),AVG(age)AS平均年齡FROMStudentWHEREsex=‘女’;[例3.6]查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTsno)AS選課學(xué)生人數(shù)

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

SELECTMAX(grade)AS最高分,MIN(grade)AS最低分,SUM(grade)AS總分FROMSCWHEREcno=‘001’andgrade>=60;第三十六頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢2、使用表達(dá)式[例]查詢各課程的學(xué)時(shí)數(shù)。

SELECTcnameAScourse_name,credit*18AScourse_timeFROMcourse;第三十七頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢

3、對查詢結(jié)果分組（GROUPBY子句）將查詢結(jié)果表按某一(多)列值分組，值相等的為一組。目的：細(xì)化集函數(shù)的作用對象。如果未對查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果，即整個(gè)查詢結(jié)果只有一個(gè)函數(shù)值。否則，集函數(shù)將作用于每一個(gè)組，即每一組都有一個(gè)函數(shù)值。[例3.6④]求每個(gè)學(xué)生所選課程的平均成績。SELECTsno，AVG(grade)AS平均成績FROMSCGROUPBYsnoHAVING短語：在各組中選擇滿足條件的小組。WHERE子句：在表中選擇滿足條件的元組。WHERE?并將其超過88分的輸出。HAVINGAVG(grade)>88；分組情況第三十八頁，共118頁。snocnograde000101c190000101c285000101c380010101c185010101c275020101c380……分組情況及查詢結(jié)果示意圖3組…1組2組snoAVG(grade)0001018501010180……例:求每個(gè)學(xué)生的平均成績。SELECTsno,AVG(grade)FROMSCGROUPBYsno；求每門課程的平均成績？第三十九頁，共118頁。3.3SQL的數(shù)據(jù)查詢－單表查詢集函數(shù)在成組之前不計(jì)算，因此不能用于WHERE子句,一般將簡單條件寫入WHERE。HAVING子句的條件運(yùn)算數(shù)之一是一個(gè)集函數(shù)。若HAVING子句無前導(dǎo)GROUPBY，選擇清單中不能含有非集函數(shù)列。第四十頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢【練習(xí)】查詢至少選修4門課程的學(xué)生學(xué)號。SELECTsnoFROMSCGROUPBYsnoHAVINGCOUNT(*)>=4第四十一頁，共118頁。3.3SQL的數(shù)據(jù)查詢--單表查詢[例3.6⑤]

求學(xué)生關(guān)系中湖北籍男生的每一年齡組（不少于30人）共有多少人，要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同時(shí)按年齡降序排列。

SELECTage,COUNT(sno)numberFROMStudentWHEREsex=‘男’ANDplace='湖北'GROUPBYageHAVINGnumber>30ORDERBYnumber,ageDESC;第四十二頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢3.3.2

多表查詢

1.子查詢（嵌套查詢）查詢塊：SELECT<A>FROM<R>WHERE<F>子查詢（嵌套查詢）：一個(gè)查詢塊嵌套在另一查詢塊中作為條件的查詢。上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層查詢塊又稱為內(nèi)層查詢或子查詢。第四十三頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢子查詢一般跟在IN、SOME（ANY）、ALL和EXIST等謂詞后面。SQL語言允許多層嵌套查詢。嵌套查詢的求解方法是由里向外處理。即每個(gè)子查詢在其上一級查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。嵌套查詢使得可以用一系列簡單查詢構(gòu)成復(fù)雜的查詢，從而明顯地增強(qiáng)了SQL的查詢能力。以層層嵌套的方式來構(gòu)造程序正是SQL(StructurredQueryLanguage)中“結(jié)構(gòu)化”的含義所在。第四十四頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢(1)帶有IN謂詞的子查詢

帶有IN謂詞的子查詢是指父查詢與子查詢之間用IN進(jìn)行連接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中。例：查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生的學(xué)號、姓名、系①

確定“劉晨”所在系名

SELECTSdept

FROMStudent

WHERESname=‘劉晨’;

②

查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept='IS';

子查詢實(shí)現(xiàn)：將第一步查詢嵌入到第二步查詢中，用以構(gòu)造第二步查詢的條件。WHERESdeptIN(SELECT…)

第四十五頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例]查詢選修了數(shù)據(jù)庫課程的學(xué)生號、成績。

SELECTsno，gradeFROMSCWHEREcnoIN

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

（2）帶有比較運(yùn)算符的子查詢

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

SELECTsname

FROMStudent

WHEREage＞

(SELECTAVG(age)

FROMStudent)；第四十六頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢(3)帶SOME(ANY)、ALL謂詞的子查詢>SOME大于子查詢結(jié)果中的某個(gè)值<ALL小于子查詢結(jié)果中的所有值注意：SOME、ALL必須與關(guān)系比較符同時(shí)使用。例：查詢其他系比信息院某學(xué)生年齡小的學(xué)生名、年齡。

SELECTsname，ageFROMStudentWHEREage<SOME

(SELECTageFROMStudentWHEREdept=‘信息院’)

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

<

第四十七頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例3.7]查平均成績最高的學(xué)生號。SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(grade)

>=ALL

(SELECTAVG(grade)FROMSCGROUPBYsno)；姓名？集合一個(gè)第四十八頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例]找出有一門選課成績在90分以上的學(xué)生姓名。

SELECTsnameFROMStudentWHEREsnoIN

(SELECTDISTINCTsnoFROMSCWHEREgrade>=90)；=SOME第四十九頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢

SELECTStudent.sno，sname，sex，age，dept，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；

2.條件連接查詢

連接條件的一般格式為：[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>當(dāng)連接運(yùn)算符為=時(shí)，稱為等值連接。例:查詢每個(gè)學(xué)生的情況及其選課成績。第五十頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例]找出籍貫為湖北或河北，選課成績?yōu)?0分以上的學(xué)生的姓名、課號和成績。

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

SELECTsno，gradeFROMSCWHEREcnoIN=方法1：方法2：第五十二頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例3.8]按平均成績的降序給出所有課程都及格的學(xué)生（號、名）及其平均成績，其中成績統(tǒng)計(jì)時(shí)不包括008號考查課。SELECTStudent.sno，sname，AVG(grade)ASavg_gFROMStudent，SCWHEREStudent.sno=SC.snoANDcno<>’008’GROUPBYStudent.sno,snameHAVINGMIN(grade)>=60ORDERBYavg_gDESC；

第五十三頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢自身連接查詢[例3.8④]找出年齡比“王迎”同學(xué)大的同學(xué)的姓名及年齡。SELECTs1.sname，s1.ageFROMStudentASs1，StudentASs2WHEREs1.age>s2.ageANDs2.sname=‘王迎’；

其他方法？第五十四頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢練習(xí)：查每門課的間接先修課(即先修課的先修課)。d2d1003003004001SELECTo,d2.pcnoFROMCourseASd1,CourseASd2WHEREd1.pcno=o;第五十五頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢3、相關(guān)子查詢當(dāng)一個(gè)子查詢的判斷條件涉及到一個(gè)來自外部查詢的列時(shí)，稱為相關(guān)子查詢。帶存在謂詞的子查詢：只產(chǎn)生邏輯值存在謂詞EXISTS作用:若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值，否則返回假值。求解相關(guān)子查詢不能象求解不相關(guān)子查詢那樣，一次將子查詢求解出來，然后求解父查詢。相關(guān)子查詢的內(nèi)層查詢由于與外層查詢有關(guān)，因此必須反復(fù)求值。

第五十六頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例3.9]查詢所有選修了005號課程的學(xué)生姓名和學(xué)號。SELECTsname,snoFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESC.sno=Student.sno

ANDcno=’005’)；

分析：查詢所有選修了005號課程的學(xué)生姓名涉及Student關(guān)系和SC關(guān)系，在Student關(guān)系中依次取每個(gè)元組的Sno值，用此Student.Sno值去檢查SC關(guān)系，若SC中存在這樣的元組：其SC.Sno值等于用來檢查的Student.Sno值，并且其SC.Cno=‘005’，則取此Student.Sname送入結(jié)果關(guān)系。第五十七頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢

相關(guān)子查詢的一般處理過程：

首先取外層查詢中Student表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值（即Sno值）處理內(nèi)層查詢，若WHERE子句返回值為真（即內(nèi)層查詢結(jié)果非空），則取此元組放入結(jié)果表；然后再檢查Student表的下一個(gè)元組；重復(fù)這一過程，直至Student表全部檢查完畢為止。

第五十八頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例3.9]查詢沒選修001號課程的學(xué)生學(xué)號和姓名。方法1：SELECTsno,snameFROMStudentWHERE

NOTEXISTS(SELECT*FROMSCWHERESC.sno=Student.sno

ANDcno=’001’)；方法2：SELECTsno,snameFROMStudentWHERE

<>ALL(SELECTsnoFROMSCWHEREcno=’001’)；第五十九頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢SQL語言中沒有全稱量詞∨（Forall）。因此對于求所有的操作，必須利用謂詞演算將一個(gè)帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞。SQL語言中也沒有蘊(yùn)涵（Implication）邏輯運(yùn)算。因此也必須利用謂詞演算將一個(gè)邏輯蘊(yùn)函的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞。第六十頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[例3.9]查詢選修了全部課程的學(xué)生姓名。轉(zhuǎn)換成：查詢這樣的學(xué)生，沒有一門課程是他不選修的。(x)P≡(x(P))

SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHERESC.sno=Student.snoANDSC.cno=Co))；每門課都沒選某學(xué)生選某課第六十一頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢[練習(xí)]查詢至少選修了學(xué)號為200101的學(xué)生選修的全部課程的學(xué)生學(xué)號。分析：查詢這樣的學(xué)生，凡是200101選修的課，他都選修了。換句話說，若有一個(gè)學(xué)號為x的學(xué)生，對所有的課程y，只要學(xué)號為200101的學(xué)生選修了課程y，則x也選修了y；那么就將他的學(xué)號選出來。它所表達(dá)的語義為：不存在這樣的課程y，學(xué)生200101選修了y，而學(xué)生x沒有選。

第六十二頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=‘200101’ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDY.cno=Z.cno))第六十三頁，共118頁。3.3SQL的數(shù)據(jù)查詢--多表查詢4、集合運(yùn)算（1）UNION

（并）（2）INTERSECT

（交）（3）EXCEPT

（差）[例3.10]求選修了001或002號而沒有選003號課程的學(xué)生號。(SELECTsnoFROMSCWHEREcno=’001’ORcno=’002’)EXCEPT(SELECTsnoFROMSCWHEREcno=‘003’)；第六十四頁，共118頁。3.3SQL的數(shù)據(jù)查詢--連接查詢3.3.3

連接查詢

1.自然連接(NATURALJOIN)[例3.11]查詢在計(jì)算機(jī)學(xué)院且課程成績在90分以上的學(xué)生檔案及其成績情況。

SELECT*FROMStudentNATURALJOINSCWHEREdept=‘計(jì)算機(jī)學(xué)院’ANDgrade>=90；2.外連接左外連接。LEFT[OUTER]JOIN，保留左關(guān)系的所有元組。右外連接。RIGHT[OUTER]JOIN，保留右關(guān)系的所有元組。全外連接。FULL[OUTER]JOIN，保留左右兩關(guān)系的所有元組。

第六十五頁，共118頁。3.3SQL的數(shù)據(jù)查詢--連接查詢SELECT*FROMStudentNATURALJOINSC;?FROMStudentLEFTOUTERJOINSC；用左外連接產(chǎn)生未選課的空行教師表：Teacher(教師號，姓名，所屬大學(xué)，職稱)

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

例：SELECT*FROMTeacherFULLOUTERJOINPost；未選課？例:查每個(gè)學(xué)生及其選課情況。P78表3-3、表3-4第六十六頁，共118頁。3.3SQL的數(shù)據(jù)查詢--遞歸合并查詢

遞歸合并語句格式：

WITHRECURSIVE<臨時(shí)視圖R>AS

<R的定義>

UNION

<涉及到R的查詢>

<遞歸結(jié)果查詢>說明：任何遞歸視圖都必須被定義為兩個(gè)子查詢的并：一個(gè)非遞歸的基查詢和一個(gè)使用遞歸視圖的遞歸查詢。

遞歸視圖的理解：首先計(jì)算基查詢并把所有結(jié)果元組添加到視圖關(guān)系中（初始時(shí)為空），然后用當(dāng)前視圖關(guān)系的內(nèi)容計(jì)算遞歸查詢，并把所有結(jié)果元組加回到視圖關(guān)系中。持續(xù)重復(fù)上述步驟直到?jīng)]有新的元組添加到視圖關(guān)系中為止。第六十七頁，共118頁。3.3SQL的數(shù)據(jù)查詢--遞歸合并查詢[例3.13]對表3-5所示的航班表Flight(airline，from，to，depart，arrive)，求出：能從一個(gè)城市飛到另一個(gè)城市的城市對集合(含直接到達(dá)和間接中轉(zhuǎn)到達(dá))。第六十八頁，共118頁。3.3SQL的數(shù)據(jù)查詢--遞歸合并查詢直接到達(dá)：Reach(f，t)=Flight(a，f，t，d，r)f，t在表的同一個(gè)元組中。間接到達(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；

第六十九頁，共118頁。3.3SQL的數(shù)據(jù)查詢--遞歸合并查詢[練習(xí)]有下列所示的一棵二叉樹：abecfd162453求出:該子樹根結(jié)點(diǎn)k的所有子孫結(jié)點(diǎn)的集合。

第七十頁，共118頁。3.3SQL的數(shù)據(jù)查詢--遞歸合并查詢分析：定義結(jié)果集為Tsub(id，name)：結(jié)點(diǎn)k的所有子女：Tsub(id，name)=Tree(id，pid，name)ANDpid=k結(jié)點(diǎn)k的所有子女的孩子（k的子孫）：Tsub(id，name)=Tsub(id1，name1)ANDTree(id，id1，name)SQL:WITHRECURSIVETsub(id，name)AS(SELECTid，nameFROMTree//結(jié)點(diǎn)k的所有子女WHEREpid=k)UNION(SELECTTree.id，TFROMTsub，Tree//結(jié)點(diǎn)k的所有子孫WHERETsub.id=Tree.pid)

SELECT*FROMTsub；

第七十一頁，共118頁。3.3SQL的數(shù)據(jù)查詢SQL中的空值處理1.空值的含義值暫未知。值不適用。值需隱瞞。2.空值的測試空值的測試：ISNULL和ISNOTNULL。第七十二頁，共118頁。3.3SQL的數(shù)據(jù)查詢[例3.14]查詢?nèi)鄙龠x課成績的學(xué)生號和相應(yīng)的課程號。SELECTSno,Cno

FROMSC

WHEREGradeISNULL;

注意這里的‘IS’不能用等號（‘=’）代替。

[例]查所有有成績的記錄的學(xué)生學(xué)號和課程號。

SELECTSno,Cno

FROMSC

WHEREGradeISNOTNULL;第七十三頁，共118頁。3.4SQL的數(shù)據(jù)更新一、插入數(shù)據(jù)格式：INSERTINTO<表名>[(<列名1>[,<列名2>…)]

<子查詢>|VALUES(<常量1>[，<常量2>]…)；說明：若插入全部屬性值，則屬性列可以省略。屬性值與相對應(yīng)的屬性列的數(shù)據(jù)類型要匹配。插入時(shí)，表定義中說明NOTNULL的列不能取空值。若已定義主碼、外碼及其他用戶完整性約束，向表中更新元組,系統(tǒng)自動支持其完整性。第七十四頁，共118頁。3.4SQL的數(shù)據(jù)更新[例3.15]向student表插入一行。insertintostudentvalues(200510,’王雪’,20,’女’,’英語系’);[例3.15]向st1_grade表插入多行。insretintost1_grade(gno,name,avg)selectstudent.sno,sname,avg(grade)asavg_goodfromstudent,scwherestudent.sno=sc.snogroupbysnohavingavg_good>=90;

第七十五頁，共118頁。3.4SQL的數(shù)據(jù)更新二.修改數(shù)據(jù)格式：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]…

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

(SELECTsnoFROMStudentWHEREsname＝‘左彼’);第七十六頁，共118頁。3.4SQL的數(shù)據(jù)更新[例3.16]若某學(xué)生008號課程的成績低于該門課程平均成績時(shí)，提高5%。UPDATEscSETgrade＝grade*1.05WHEREcno＝'008'ANDgrade<

(SELECTAVG(grade)FROMscWHEREcno＝‘008’);第七十七頁，共118頁。3.4SQL的數(shù)據(jù)更新三.刪除數(shù)據(jù)格式：DELETEFROM<表名>

[WHERE<條件[子查詢]>];[練習(xí)]將選課人數(shù)不足10人的課程記錄刪除。deletefromcoursewherecnoin(selectcnofromscgroupbycnohavingcount(*)<10)第七十八頁，共118頁。3.5SQL中的視圖一、視圖的概念及作用

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

視圖的作用：

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

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

（3）提高邏輯獨(dú)立性。（[例3.21]）

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

第七十九頁，共118頁。3.5SQL中的視圖二、創(chuàng)建與使用視圖1.創(chuàng)建視圖一般格式：CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]AS<子查詢>

[WITHCHECKOPTION]；更新操作時(shí)要保證操作的行滿足視圖定義中的謂詞條件。其中：子查詢不允許含有ORDERBY子句和DISTINCT短語。第八十頁，共118頁。3.5SQL中的視圖例：建立信息院學(xué)生的視圖，CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；[例3.18(2)]建立信息院學(xué)生的視圖，

要求進(jìn)行更新時(shí)仍保證該視圖只有信息院的學(xué)生。

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；

WITHCHECKOPTION行列子集視圖：視圖是從單個(gè)基本表導(dǎo)出，并且只是去掉了基本表的某些行和某些列，但保留了碼。第八十一頁，共118頁。3.5SQL中的視圖視圖不僅可以建立在一個(gè)或多個(gè)基本表上，也可以建立在一個(gè)或多個(gè)已定義好的視圖上，或同時(shí)建立在基本表與視圖上。

例:建立信息系選修了1號課程的學(xué)生的視圖。

CREATEVIEWIS_S1(Sno,Sname,Grade)

AS

SELECTStudent.Sno,Sname,Grade

FROMStudent,SC

WHERESdept='IS'AND

Student.Sno=SC.SnoAND

SC.Cno='1';第八十二頁，共118頁。3.5SQL中的視圖例：建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。CREATEVIEWIS_S2

AS

SELECTSno,Sname,Grade

FROMIS_S1

WHEREGrade>=90;這里的視圖IS_S2就是建立在視圖IS_S1之上的。

第八十三頁，共118頁。3.5SQL中的視圖帶虛擬列的視圖：是指設(shè)置了一些基本表中并不存在的派生列(虛擬列)的視圖。定義基本表時(shí)，為了減少數(shù)據(jù)庫中的冗余數(shù)據(jù)，表中只存放基本數(shù)據(jù)，由基本數(shù)據(jù)經(jīng)過各種計(jì)算派生出的數(shù)據(jù)一般是不存儲的。但由于視圖中的數(shù)據(jù)并不實(shí)際存儲，所以定義視圖時(shí)可以根據(jù)應(yīng)用的需要，設(shè)置一些派生屬性列。這些派生屬性由于在基本表中并不實(shí)際存在，所以有時(shí)也稱他們?yōu)樘摂M列，帶虛擬列的視圖也稱為帶表達(dá)式的視圖。

例：定義一個(gè)反映學(xué)生出生年份的視圖CREATEVIEWBT_S(Sno,Sname,Sbirth)

ASSELECTSno,Sname,2015-Sage

FROMStudent;第八十四頁，共118頁。3.5SQL中的視圖分組視圖：帶有集函數(shù)和GROUPBY子句查詢所定義的視圖。[例3.20]將學(xué)生的學(xué)號及他的平均成績定義為一個(gè)視圖。

CREATVIEWS_G(Sno,Gavg)

ASSELECTSno,AVG(Grade)

FROMSC

GROUPBYSno;第八十五頁，共118頁。3.5SQL中的視圖2、刪除視圖

DROPVIEW<視圖名>[CASCADE|RESTRICT]

注意：視圖刪除后，視圖的定義將從數(shù)據(jù)字典中自動刪除。但要刪除該視圖導(dǎo)出的其他視圖應(yīng)用CASCADE。作用：撤銷一個(gè)視圖，但不消除數(shù)據(jù)，在基礎(chǔ)表中的數(shù)據(jù)仍然保留。RESTRICT確保只有不具有相關(guān)對象的視圖（不涉及任何的約束、斷言、觸發(fā)器）才能被撤消。第八十六頁，共118頁。3.5SQL中的視圖三、查詢視圖視圖定義后，用戶就可以象對基本表進(jìn)行查詢一樣對視圖進(jìn)行查詢了。DBMS執(zhí)行對視圖的查詢時(shí)，首先進(jìn)行有效性檢查，檢查查詢涉及的表、視圖等是否在數(shù)據(jù)庫中存在，如果存在，則從數(shù)據(jù)字典中取出查詢涉及的視圖的定義，把定義中的子查詢和用戶對視圖的查詢結(jié)合起來，轉(zhuǎn)換成對基本表的查詢，然后再執(zhí)行這個(gè)經(jīng)過修正的查詢。將對視圖的查詢轉(zhuǎn)換為對基本表的查詢的過程稱為視圖的消解（ViewResolution）。

四、更新視圖

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

第八十七頁，共118頁。3.5SQL中的視圖視圖更新規(guī)定（不同系統(tǒng)不盡相同）：若視圖由兩個(gè)或以上基本表導(dǎo)出的，則此視圖不允許更新。若視圖的列來自表達(dá)式或常數(shù)，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。若視圖的來自聚集函數(shù)，則此視圖不允許更新。若視圖定義中含有GROUPBY子句，則此視圖不允許更新。若視圖定義中含有DISTINCT短語，則此視圖不允許更新。若視圖定義中有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。一個(gè)不允許更新的視圖上定義的視圖也不允許更新。第八十八頁，共118頁。3.6數(shù)據(jù)庫訪問技術(shù)應(yīng)用程序與SQL數(shù)據(jù)庫聯(lián)系的基本方式有：

（1）嵌入式SQL

（2）數(shù)據(jù)庫接口

（3）SQL模塊借助高級語言的功能實(shí)現(xiàn)過程化控制與高級計(jì)算。通過應(yīng)用程序編程接口（API）函數(shù)中的參數(shù)--SQL語句，來使用數(shù)據(jù)庫。利用SQL擴(kuò)展過程包的功能編程，調(diào)用程序模塊，來操作數(shù)據(jù)庫。SQL模塊是對SQL語句的過程化擴(kuò)展，常用于數(shù)據(jù)庫服務(wù)器的編程。第八十九頁，共118頁。一、嵌入式SQL1、嵌入式SQL概述SQL分：交互式嵌入式為何引入嵌入式:過程化應(yīng)用復(fù)雜數(shù)據(jù)處理主語言作用：負(fù)責(zé)控制流程及處理操縱結(jié)果。嵌入式SQL作用：負(fù)責(zé)操縱DB。第九十頁，共118頁。一、嵌入式SQL嵌入式SQL兩種實(shí)現(xiàn)方法：預(yù)編譯擴(kuò)充、修改主語言編譯程序使之能處理SQL預(yù)編譯方法：

預(yù)處理程序

宿主語言語句+函數(shù)調(diào)用宿主語言編譯程序目標(biāo)程序連接程序可執(zhí)行程序OS運(yùn)行結(jié)果宿主語言語句+嵌入式SQL語句第九十一頁，共118頁。一、嵌入式SQL2、嵌入式SQL的工作原理：（1）嵌入式SQL與主語言的接口SQL語句：面向集合主語言語句:過程性、記錄式

狀態(tài)變量SQLSTATE

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

×

×

×

×

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

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

輸入主變量由應(yīng)用程序?qū)ζ滟x值，SQL語句引用。輸出主變量由SQL語句對其賦值或置狀態(tài)，返應(yīng)用程序。第九十二頁，共118頁。一、嵌入式SQL

游標(biāo)(Cursor)游標(biāo)是系統(tǒng)開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。作用：用戶可以通過游標(biāo)獲取記錄，并賦給主變量，交主語言處理。游標(biāo)名……………...…………….……………..一條記錄主變量游標(biāo)指針SQL查詢結(jié)果第九十三頁，共118頁。一、嵌入式SQL（2）嵌入式SQL需解決的問題如何區(qū)分SQL語句與主語言語句?前綴加EXECSQL，結(jié)束處用END—EXEC(或用分號)例：嵌入到C程序中：

EXECSQL<SQL語句>；嵌入到Java程序中：#SQL{<SQL語句>}；如何區(qū)分主變量與SQL的列變量?主變量前加冒號。如::Cname主變量與SQL的列變量如何交互、轉(zhuǎn)換？需要有一種機(jī)制：能將SQL中的集合量逐個(gè)送入主變量內(nèi)，供主程序使用。方法:用一組游標(biāo)語句。第九十四頁，共118頁。一、嵌入式SQL(3)SQL語句與主語言間的通信主語言向SQL語句提供參數(shù)；向主語言傳遞SQL的執(zhí)行狀態(tài)；將SQL操作結(jié)果交主語言進(jìn)一步處理。嵌入式SQL工作原理：主語言程序SQL語句……游標(biāo)…主變量SQL狀態(tài)①執(zhí)行參數(shù)②執(zhí)行狀態(tài)結(jié)果狀態(tài)③第九十五頁，共118頁。一、嵌入式SQL3、嵌入式SQL的程序組成嵌入式SQL語句可分為：可執(zhí)行語句（數(shù)據(jù)定義、控制、操縱）說明性語句（說明變量、游標(biāo))程序的主要部分(1)DECLARE段(用于定義主變量)格式：EXECSQLBEGINDECLARESECTION；

……//主變量說明EXECSQLENDDECLARESECTION；(2)CONNECT語句（建立SQL連接）格式：EXECSQLCONNECTTO<SQL服務(wù)器或數(shù)據(jù)庫名>USER<用戶名>；第九十六頁，共118頁。一、嵌入式SQL(3)WHENEVER語句使用WHENEVER語句可以簡化每條嵌入式SQL語句后編寫檢查SQLSTATE值的程序。

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

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

WHENEVERSQLWARNING:通知預(yù)編譯程序產(chǎn)生處理警報(bào)的代碼(SQLSTATE=1)。

WHENEVERNOTFOUND:通知預(yù)編譯程序產(chǎn)生沒有查到內(nèi)容的代碼(SQLSTATE=100)。

第九十七頁，共118頁。一、嵌入式SQL用戶可指定預(yù)編譯程序采取以下行為：WHENEVER…GOTO:通知預(yù)編譯程序產(chǎn)生一條轉(zhuǎn)移語句。WHENEVER…CONTINUE:通知預(yù)編譯程序讓程序控制流轉(zhuǎn)入到下一個(gè)主語言語句。WHENEVER…CALL:通知預(yù)編譯程序調(diào)用函數(shù)。其完整語法如下：

WHENEVER{SQLWARNING|SQLERROR|NOTFOUND}

{CONTINUE|GOTOstmt_label|CALLfunction()}(4)應(yīng)用程序體若干可執(zhí)行SQL語句及主語言語句。提交和退出數(shù)據(jù)庫：COMMITWORKRELEASE；第九十八頁，共118頁。一、嵌入式SQL4、嵌入式SQL的使用技術(shù)（1）無游標(biāo)的操作

說明性語句數(shù)據(jù)定義語句數(shù)據(jù)控制語句數(shù)據(jù)插入語句非當(dāng)前形式的UPDATE、DELETE語句語句查詢結(jié)果為單記錄的SELECT（2）帶游標(biāo)的查詢操作查詢結(jié)果為多記錄。當(dāng)前形式的UPDATE、DELETE語句第九十九頁，共118頁。一、嵌入式SQL游標(biāo)語句作用：把對集合的操作轉(zhuǎn)換為對單個(gè)記錄的處理。使用游標(biāo)的步驟為：(1)定義游標(biāo)分配緩沖區(qū)并對應(yīng)查詢語句。

格式：EXECSQLDECLARE<游標(biāo)名>CURSORFOR<SELECT語句>；

第一百頁，共118頁。一、嵌入式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>，…(4)關(guān)閉游標(biāo)釋放緩沖區(qū)及其他資源。格式：EXECSQLCLOSE<游標(biāo)名>；查詢語句游標(biāo)名SELECT結(jié)果12…n主變量第一百零一頁，共118頁。一、嵌入式SQL[例]使用游標(biāo)的示例。#include<stdio.h>EXECSQLBEGINDECLARESECTIONVARCHARuid[20];INTst_no;CHARc_no[4];FLOATsc_grade;EXECSQLENDDECLARESECTION;/*SQL中使用的主變量說明*/voidmain(){strcpy(uid,”YIN”);EXECSQLWHENEVERSQLEXCEPTIONGOTOerror_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)*/第一百零二頁，共118頁。一、嵌入式SQL

EXECSQLWHENEVERNOTFOUNDDOBREAK;

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)*/

EXECSQLCOMMITWORKRELEASE;/*提交并退出DB*/

EXECSQLDISCONNECTCURRENT;