數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用 課件 郭勝 第3、4章 關(guān)系型數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL、關(guān)系模式設(shè)計理論

第3章關(guān)系型數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL●SQL簡介●

SQL的數(shù)據(jù)定義：基本表,視圖,索引,約束●

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

SQL的數(shù)據(jù)更新●嵌入式SQL的使用主要內(nèi)容：重點：●

SQL的數(shù)據(jù)查詢3.1SQL概述SQL（StructuredQueryLanguage）起源于1974年，作為關(guān)系DB的標(biāo)準(zhǔn)語言，用于：ORACLE，SQL/DB，DB2等關(guān)系數(shù)據(jù)庫系統(tǒng)中。標(biāo)準(zhǔn)版本：SQL89→SQL2（92年）→SQL3（99年）。一、SQL的特點集DB定義，操縱，控制功能于一體。高度非過程化。面向集合的操作方式。提供兩種用方式：交互式和嵌入式。語言簡潔，易學(xué)易用。二、SQL結(jié)構(gòu)基本表（BaseTable）——關(guān)系模式支持DB的三級模式的結(jié)構(gòu)：視圖（View）——子模式存儲文件（StoredFile）——存儲模式其中：元組稱為行（row），屬性稱為列（column）。（1）SQL中的基本表對應(yīng)DB中的一個關(guān)系，一行對應(yīng)一個元組，一列對應(yīng)一個屬性值域。（2）SQL中的視圖由基本表或視圖導(dǎo)出；基本表是實際存儲在DB中的表,視圖是虛表，它的數(shù)據(jù)仍在導(dǎo)出的基本表中，它的定義存在數(shù)據(jù)字典中。（3）一個基本表可以跨一個或多個存儲文件，一個存儲文件也可以存放一個或多個基本表；每一個存儲文件對應(yīng)外存上一個物理文件。（4）用戶可用SQL語句對視圖和基本表作查詢等操作。在用戶來看視圖與基本表都是關(guān)系。（5）SQL的語句既可交互使用，也可嵌套到宿主語言的程序中使用。結(jié)構(gòu)要點：三、SQL的構(gòu)成數(shù)據(jù)定義（SQLDDL）：用于定義基本表、視圖、索引等。數(shù)據(jù)操縱（SQLDML）：用于對DB的查詢和更新等操作。數(shù)據(jù)控制：包括對基本表和視圖的授權(quán)，完整性規(guī)則的描述、事務(wù)控制等。嵌入式SQL的使用規(guī)定。4個部分構(gòu)成：3.2SQL的數(shù)據(jù)定義包括：定義和撤消：基本表（table）：表名，屬性名，類型，長度。視圖（view）索引（index）完整性約束條件一、基本表的創(chuàng)建、修改和撤消1．SQL提供的主要數(shù)據(jù)類型（1）數(shù)值型

INTEGER（或INT）長整型SMALLINT短整型

REAL浮點型（取決于機(jī)器）FLOAT（n）浮點型精度為n位NCMERIC（P，d）定點數(shù)（p位數(shù)字，小數(shù)點后d位）（2）字符串型

CHAR（n）最大長度為n的定長字符串

VARCHAR（n）長度可變字符串，具有最大長度n（3）位串型BIT（N）長度為N的二進(jìn)制的往事。（4）時間型

DATE日期：YYYY—MM—DD（年-月-號數(shù)）TIME時間：HH：MM：SS（時：分：秒）算術(shù)操作僅限于數(shù)值型的數(shù)據(jù)。2．基本表的創(chuàng)建，修改和撤消（1）創(chuàng)建基本表句型：CreateTable基本表名（列名類型[列完整性約束]，……）；主關(guān)鍵字子句（PRIMARYKEY）其中：完整性約束定義有三種子句：檢查子句（CHECK）外關(guān)鍵字子句（FOREIGNKEY）例：教學(xué)DB中有三個關(guān)系模式：S（sno，sname，sex，age，dept）

Sc（sno，cno，grade）C（cno，cname，credit）定義其基本表。Createtables（snoCHAR（5）[NOTNULLUNIQUE]，snameCHAR（8），sexCHAR（2），ageINT，deptCHAR（6）,primaryKEY（sno）)；Createtablesc(snoCHAR(5),cnoCHAR(4)，gradeSMALLINT，PRIMARYKEY（sno，cno），F(xiàn)OREIGNKEY（cno）REFERENCESC（cno），F(xiàn)OREIGNKEY（sno）REFERENCESS（sno），CHECK(（gradeISNULL）OR（gradeBETWEEN0AND100）))；其基本表定義如下：表C定義略（2）基本表結(jié)構(gòu)的修改增加新的屬性：ALTERTABLE基本表名ADD列名類型例：ALTERTABLESADDPHONCHAR（8）;刪除原有的屬性:ALTERTABLE基本表名DROP列名

[CASCADE|RESTRICT];其中：CASCADE：所有引用該列的視圖和約束也要一起被刪除。RESTRICT：只有視圖和約束沒有引用該屬性時，才能刪除，否則拒絕。例：ALTERTABLESDROPageCASCADE；（3）基本表的撤消句型：DROPTABLE基本表名；例：DROPTABLES；三、視圖的創(chuàng)建與撤消視圖：外模式（子模式）：由基本表或其它已建視圖構(gòu)造出的表。（1）視圖的創(chuàng)建句型：CreateView視圖名（列名表）ASSELECT查詢子句例：若用戶經(jīng)常用到sno，sname，cname和grade信息，則建立視圖供查詢CreateViewSg（sno，sname，cname，grade）Asselects?sno，sname，cname，gradefroms，sc，cwheres?sno=sc?snoAndsc.cno=c.cno；（2）視圖撤消句型：DROPView視圖名四、索引的創(chuàng)建及撤消

建立索引的目的，加快對關(guān)系的查找。（浪費(fèi)外存空間）在基本表上可建立一個或多個索引，索引存在存儲文件中稱索引文件。句型：Create[UNIQUE]INDEX索引名ON基本表名（列名）例：CreateindexSIONS（sno）按sno升序排列CreateUniqueindexSIONS（sno）；sno中值對應(yīng)唯一的記錄值

CreateUniqueindexSCIONSC（snoASC，cnoDESC）索引定義的結(jié)構(gòu)存在數(shù)據(jù)字典庫中，學(xué)號按升序排列，CNO值按降序排列。索引的撤消：DROP

index索引名；例：DROPindexSI；

3.3SQL的數(shù)據(jù)查詢一、select查詢語句的句型

select〈目標(biāo)表的屬性名或目標(biāo)列表達(dá)式〉……select子句from〈表名或視圖名〉……from子句[where〈行條件表達(dá)式〉]

……行條件子句[groupby〈列名1〉[having〈條件表達(dá)式〉]]……列條件子句[orderby〈列名2〉[asc|desc]]……排序子句執(zhí)行過程根據(jù)where子句的條件表達(dá)式，從from子句指定的基本表或視圖中找出滿足條件的元組，再按select子句中的目標(biāo)列表達(dá)式，選出元組中的屬性值形成結(jié)果表。group子句，將結(jié)果按〈列名1〉的值進(jìn)行分組，列值相等的元組分為一組，每一組會產(chǎn)生結(jié)果表中的一條記錄。having子句將滿足條件的組給出輸出。order子句對輸出的目標(biāo)表按ASC升序或DESC降序排列。

select基本句型

selectA1，A2，…，An（屬性名Ai）fromR1，R2…，Rn（關(guān)系名或視圖名Ri）whereF；（邏輯表達(dá)式F）其中：F可使用以下運(yùn)算符

等價于關(guān)系代數(shù)式：ΠA1,A2,…,An(σF(R1

R2…Rn))算術(shù)比較符：<，<=，>，>=，=，<>或！=。邏輯符：AND，OR，NOT。集合運(yùn)算符：union（并），intersect（交）except（差），IN（屬于）。謂詞：exists（存在量詞），all，any(some)，unique（唯一），LIKE。F中還可以是select子句（嵌套）。使用的函數(shù)：avg（列名）：求列中的平均值。min（列名）：求列中的最小值。max（列名）：求列中的最大值。sum（列名）：求列中值之和。count（列名）：求列中值的個數(shù)。count（*）：求元組個數(shù)。注意：select語句與關(guān)系代數(shù)表達(dá)式的關(guān)系：

selectA，B，CfromR1，R2ΠA,B,C(σF(R1

R2))whereF；例：已知對R運(yùn)算的關(guān)系代數(shù)表達(dá)式：∏A,B(σ

A>3∧C=1(R)):

將關(guān)系代數(shù)表達(dá)式用等價的SELECT語句寫出。selectA，BselectA，S.BfromRfromR，S

whereA>3andC=1;whereR.B=S.C;例：已知S是由R通過select語句執(zhí)行的結(jié)果關(guān)系，請寫出此select語句。已知S是由R通過關(guān)系代數(shù)運(yùn)算的結(jié)果關(guān)系，請寫出此關(guān)系代數(shù)表達(dá)式。

selectB，CfromR寫法不唯一。

whereB=5ORC=5;ABC631547154RBC5415SAs.B64∏A,S.B(σR.B=S.C(R×S))∏B,C(σB=5∨C=5(R))二、SELSCT語句的使用

單表查詢，多表關(guān)聯(lián)查詢（嵌套查詢），集合查詢。1、單表查詢例：教學(xué)DB三個基本表：

s（sno，sname，sex，age，dept）：學(xué)號，姓名，性別，年齡，系名

c（cno，cname，credit）：課號，課名，學(xué)分

sc（sno，cno，grade）：學(xué)號，課號，成績列上查詢例1：查詢學(xué)生的學(xué)號，姓名和系名selectsno，sname，deptfroms；例2：查詢?nèi)繉W(xué)生的詳細(xì)情況select*(*表示s中的全部屬性)froms；∏sno，sname，dept

(S)例3：查詢?nèi)繉W(xué)生的姓名及出生年份selectsname，2018-agefroms；結(jié)果：sname無列名李一1999劉二1996王三1995例4：查詢?nèi)繉W(xué)生的姓名，出生年份和系名要求：指定別名改變查詢結(jié)果的列標(biāo)題selectsnameNAME，2015-ageBIRTHDAY,deptDEPARTMENTfroms；結(jié)果：

NAMEBIRTHDAYDEPARTMENT李一1981cs劉二1982is王三1983ma行上查詢例1：查詢所有選修課程的學(xué)生的學(xué)號。SelectdistinctsnofromSC;例2：查找成績不及格學(xué)生的學(xué)號及課號。selectdistinctsno，cnofromscwheregrade<60；例3：查找成績在100分~90分之間的學(xué)號及課號。SelectSno，CnoFromSCwheregrade>=90ANDgrade<=100

;

或：wheregradebetween90and100;SC表原來值：SnoCnogradeSno20001180操作后：200012852000239020002280∏sno，cno

(σ

grade<60

(SC))例4:查找信息系（IS），數(shù)學(xué)系（MA）和計算機(jī)系（CS）的學(xué)生的姓名和性別。selectsname，sexfromswheredeptIN（‘IS’，‘MA’，‘CS’）；例5：查找所有姓“劉”的學(xué)生的姓名、學(xué)號和性別。謂詞LIKE可實現(xiàn)字符串的匹配，滿足模糊查詢的要求格式：[NOT]LIKE‘〈匹配串〉’[ESCAPE‘〈換碼字符〉’]含義：查找指定的屬性列值與〈匹配串〉相匹配的元組。一個完整字符串通配符

%

和

＿

%表示任意長度（長度可為0）的字符串：如：a%b表示以a開頭，b結(jié)尾任意長度的字符串。

＿（下劃線）表示任意單個字符。如：a

b表示a開頭，b結(jié)尾的長度為3的任意字符串。其中：〈匹配串〉：selectsname，sno，sexfromSwheresnameLIKE‘劉%’；例6：查找名字中第二個字為“濤”字的學(xué)生的學(xué)號及姓名

Selectsno，snamefromsWheresnameLIKE‘＿＿濤%’；“--”前一個字為漢字占二個字符位置。例7：查找選課后，沒有參加考試的學(xué)生的學(xué)號和課號Selectsno，cnofromscWheregradeISNULL；（注：IS不能用“=”代替）例8：查找選修3號課程的學(xué)生的學(xué)號及成績（按降序排列）。Selectsno，gradefromscWherecno=‘3’orderbygradeDESC；例9：統(tǒng)計查詢的例子

1）統(tǒng)計學(xué)生總?cè)藬?shù)selectcount(﹡)froms；

2）統(tǒng)計選了課程的學(xué)生的人數(shù)selectcount（distinctsno）fromsc；

3）查找1號課程考試的最高分?jǐn)?shù)Selectmax（grade）fromscwherecno=‘1’；例10：查找信息系“IS”選修了3門以上的課程的學(xué)生的學(xué)號：（假定SC中有一列dept記錄了系名）selectsnofromscwheredept=‘IS’

groupbysnohavingcount（﹡）>3；（分組查詢）SnoCnodept11IS12IS13IS14IS21IS22IS23IS24IS2、關(guān)聯(lián)查詢

①多個表連接查詢

例1：查詢每個學(xué)生及選修課程的情況。Selects·﹡,sc·﹡froms,scwheres·sno=sc·sno；

∏s.sno,sname,sex,dept,sc.sno,cno,grade(σ

s·sno=sc·sno(S×SC))②自身連接查詢例2：查找至少選擇選修課程號為“1”號和為“2”號的學(xué)生的學(xué)號。selectx·snofromSCx,SCyx，y是別名wherex·sno=y·snoANDx·cno=‘1’ANDy·cno=‘2’;另一嵌套結(jié)構(gòu)寫法：

selectsnofromSCwherecno=‘2’ANDsnoIN(selectsnofromSCwherecno=‘1’）;③嵌套結(jié)構(gòu)的查詢例3：查詢選修課程號為“2”的學(xué)生姓名與學(xué)號a.Selects·sno,s·sname（涉及表s和sc連接查找）：

froms,scs(sno,sname,sex,age,dept)wheres·sno=sc·snoANDcno=′2′;sc(sno,cno,grade)b.寫法：Selectsno,sname（嵌套查詢結(jié)構(gòu)）

fromswheresnoIN(selectsnofromscwherecno=′2′）;注：嵌套結(jié)構(gòu)層次分明，具有結(jié)構(gòu)化程序設(shè)計特點。嵌套結(jié)構(gòu)比不嵌套結(jié)構(gòu)查詢效率高（因先作選擇）。

c.寫法（使用存在量詞的嵌套查詢）

selectsno,snamefromswhereEXISTS(select＊

fromSCwhereSC·Sno=S·SnoANDCno=′2′);例4：查找選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號與姓名。

selectsno,snamefromswheresnoIN(selectsnofromscwherecnoIN(selectcnofromcwherecname=‘信息系統(tǒng)’);涉及：三個表：s(sno,sname,sex,age,dept)sc(sno,cno,grade)c(cno,cname,credit)關(guān)于多個表關(guān)聯(lián)查詢select語句設(shè)計問題：方法與步驟：①首先確定關(guān)系（確定from后面的關(guān)系名）根據(jù)已知的屬性和要查找的屬性是否在同一關(guān)系中？是，此關(guān)系就是要找的（單關(guān)系查找）。否，已知的屬性所在的關(guān)系R，與要查找的屬性所在的關(guān)系S之間，找外鍵的聯(lián)系；則：R,S與外鍵通過的關(guān)系（有的話）就是要找的關(guān)系。（多關(guān)系關(guān)聯(lián)查找）②確定查找條件（確定where后面的條件）根據(jù)題目給定的屬性和值組織。嵌套結(jié)構(gòu)中，將某一個查詢條件用子查詢表示，再參與主查詢之中。例5：查找其他系中比IS系任何學(xué)生年齡小的學(xué)生名單。

selectsname,agefromswhereage﹤ALL(selectagefromswheredept=′IS′)ANDdept<>′IS′OrderbyageDESC;例6：查找至少有一門課的成績超過學(xué)號為20002的一門課成績的學(xué)生學(xué)號。

selectdistinctsnofromscwheregrade﹥some(selectgradefromscwheresno=′20002′)；3.集合查詢例：查找計算機(jī)系的學(xué)生及年齡不大于19歲的學(xué)生實際求計算機(jī)系的所有學(xué)生和年齡不大于19歲其它系的學(xué)生的并集

select＊

fromswheredept=′cs′

UNIONSelect＊

fromswhereage﹤=19anddept<>′cs′;3.4SQL的數(shù)據(jù)更新數(shù)據(jù)插入數(shù)據(jù)刪除數(shù)據(jù)修改更新包括：一、數(shù)據(jù)插入元組值的插入查詢結(jié)果的插入包括：1、元組值的插入一般句型：INSERTINTO基本表名（列名表)

VALUES(元組值)一次僅插入一個元組。

如：INSERTINTOSC

VALUES（′20005′，′1′，85）；2、查詢結(jié)果的插入一般句型：INSERTINTO基本表名（列名表）

SELECT

查詢語句……把當(dāng)前表中滿足條件的元組送到已存在的表中。如：從基本表SC中，把平均成績﹥80的男學(xué)生的學(xué)號和平均成績存入另一個已知的基本表SG（sno,sg1）中：INSERTINTOSG(sno,sg1)SELECT

sno,AVG(grade)

FROMSCWHEREsnoIN(SELECTsnoFROMSWHERESEX=′男′)GROUPBYsnoHAVINGAVG(grade)﹥80;Snocnogreade117012801390219022702360二、數(shù)據(jù)的刪除一般句型：DELETEFROM基本表名[WHERE條件表達(dá)式]

從一個基本表中刪除滿足條件的元組例：刪除SC中‘1’號課程中小于該課程平均成績的成績元組。

DELETEFROMSCWHEREcno=‘1’ANDgrade﹤(SELECTAVG(grade)FROMSCWHEREcno=‘1’);三、數(shù)據(jù)的修改修改基本表中元組的某些列值一般句型：UPDATE基本表名

SET列名=值表達(dá)式[，列名=值表達(dá)式，…][WHERE條件表達(dá)式]例：將女同學(xué)的成績提高10%UPDATESCSETgrade=grade＊1·1WHEREsnoIN(selectsno

fromswheresex=′女′）；

3.5視圖1、視圖的概念視圖從一個或多個基本表（或已定義的視圖）導(dǎo)出的表，提供給用戶查詢DB中數(shù)據(jù)使用。視圖是一個虛表，它的框架定義的屬性存于數(shù)據(jù)字典庫中，其數(shù)據(jù)仍在原來基本表中。視圖的查詢與基本表的查詢一樣，使用select語句。視圖的作用：（1）視圖能簡化用戶的操作。通過視圖可以從一個基本表或多個基本表中抽取用戶感興趣的數(shù)據(jù)，方便用戶處理。（2）視圖對重構(gòu)DB提供了一定程度的邏輯獨(dú)立性。例：將表s(sno,sname,sex,age,dept)垂直劃分為兩個表：

sx(sno,sname,dept)sy(sno,sex,age)若建立一個視圖s，可以還原原來的表s:

Createviews(sno,sname,sex,age,dept)asselectsx·sno,sx·sname,sy·sex,sy·age,sx·deptfromsx,sywheresx·sno=sy·sno;這樣盡管DB邏輯結(jié)構(gòu)改變了，但應(yīng)用程序不必修改，因為新建立的視圖定義了用戶原來的關(guān)系，使用戶的外模式保持不變。(3)視圖能對保密數(shù)據(jù)提供保護(hù)。2、視圖的更新操作視圖是不實際存儲數(shù)據(jù)的虛表，因此：對視圖的更新，最終要轉(zhuǎn)換為對基本表的更新。必須對視圖的更新操作作一些限制（查詢不限制）。視圖更新的三條規(guī)則：（1）不允許對從多個基本表作連接操作導(dǎo)出的視圖作更新操作。（2）不允許對使用了分組（groupby）和函數(shù)（如：MIN等）操作導(dǎo)出的視圖作更新。（3）如果視圖從單個基本表，使用選擇、投影操作導(dǎo)出的，并包含了基本表的主關(guān)鍵字或某個候選關(guān)鍵字，那么這樣的視圖稱為“行列子集視圖”，可作更新操作。例：從s表中定義一個有關(guān)男同學(xué)的視圖CreateviewS1(sno,snam,age)asselectsno,sname,agefromSwheresex=′男′;該視圖從單基本表導(dǎo)出，且包含關(guān)鍵字sno,可以更新：執(zhí)行插入操作：INSERTINTOS1VALUES（′20006′，′王五′，20）；例：定義視圖

CreateviewSSG(sno,cnum,Avgg)asselectsno,count(sno),Avg(grade)fromscwheregradeISNOTNULL

Groupbysno;SSG由分組和函數(shù)操作導(dǎo)出，據(jù)（2）規(guī)則不能更新。

3.6嵌入式SQL的使用(不講，嵌入與語言有關(guān))交互式SQL：用戶在終端上以命令形式使用。嵌入式SQL：在高級語言源程序中嵌入SQL語句。SQL語言使用形式：一、嵌入式SQL的使用規(guī)定●擴(kuò)充宿主語言的編譯程序功能，直接處理SQL語句?！癫捎妙A(yù)處理方式處理SQL語句。1、嵌入式SQL兩種處理方式宿主語言語句+SQL語句預(yù)處理方式SQL語句的過程：預(yù)備處理程序宿主語言語句+SQL函數(shù)調(diào)用宿主語言編譯程序宿主語言目標(biāo)程序SQL函數(shù)定義庫????2.嵌入式SQL的使用規(guī)定

（1）SQL與宿主語言的接口SQL與宿主語言的接口是共享變量。1）共享變量由宿主語言程序定義，再用SQL的DECLARE語句說明，隨后SQL語句可引用這些變量。

2）引用時，變量前必須加冒號“：”作前綴標(biāo)識，以示區(qū)別數(shù)據(jù)庫中的變量。

（2）主語言語句與SQL語句的區(qū)別

在SQL語句前使用前綴標(biāo)識“EXECSQL”……；以示區(qū)別宿主語言語句。如：C語言程序中使用如下形式定義共享變量：

EXECSQLBEGINDECLARESECTIONCharSno[5],name[9]；CharSQLSTATE[6]；

EXECSQLENDDECLARESECTION；3.SQL的集合處理方式與宿主語言單記錄處理方式的協(xié)調(diào)由于SQL語句處理的是記錄集合，而宿主語言語句一次只能處理一個記錄，因此需要用游標(biāo)（cursor）機(jī)制，把集合操作轉(zhuǎn)換成單記錄處理方式。與游標(biāo)有關(guān)的SQL語句有下列四個：a、游標(biāo)定義語句：游標(biāo)是與某一查詢結(jié)果相聯(lián)系的符號名。定義形式：EXECSQLDECLARE〈游標(biāo)名〉CURSORFOR〈SELECT語句〉；

b、游標(biāo)打開語句（OPEN）：執(zhí)行游標(biāo)定義中的SELECT語句，使游標(biāo)（實際是指針）指向查詢結(jié)果的第一行之前。語句型：EXECSQLOPEN〈游標(biāo)名〉；注：在游標(biāo)打開后，可修改和刪除游標(biāo)所指的元組。c、游標(biāo)推進(jìn)語句（FETCH）：游標(biāo)推進(jìn)一行，將游標(biāo)所指的當(dāng)前行的值送共享變量。句型：EXECSQLFETCHFROM〈游標(biāo)名〉INTO〈變量表〉；

注：FETCH常置于宿主語言程序的循環(huán)語句中。d、游標(biāo)關(guān)閉語句（CLOSE）句型：EXECSQLCLOSE〈游標(biāo)名〉；

二、嵌入式SQL的使用技術(shù)a、涉及游標(biāo)機(jī)制的使用技術(shù)b、不涉及游標(biāo)機(jī)制的使用技術(shù)1、不涉及游標(biāo)的SQLDML語句

當(dāng)涉及對DB中一個元組操作的語句：INSERT，DELETE和UPDATE，SELECT，都不必使用游標(biāo)。如：EXECSQLSELECTsname,age,sex

INTO:sn，:sa，:ssFROMSWHERESno=:give；/*假設(shè)一個學(xué)生的學(xué)號已在give中*//*在s中尋找學(xué)生姓名、年齡和性別分別送到sn、sa、ss共享變量中*/2、涉及游標(biāo)的SQLDML語句

當(dāng)涉及對DB中多個元組操作時，都必須使用游標(biāo)。（1）、SELECT語句使用當(dāng)查找多個元組時，則用游標(biāo)機(jī)制將多個元組一次一個從DB中讀出并處理。具體過程：用游標(biāo)定義語句定義一個游標(biāo)與某一個SELECT語句對應(yīng)。用游標(biāo)打開語句（OPEN）打開，使游標(biāo)指向滿足條件的元組之前。每執(zhí)行一次FECTH，游標(biāo)指向下一個滿足條件的元組，并將其值送共享變量。繼續(xù)循環(huán)。關(guān)閉語句（CLOSE）關(guān)閉游標(biāo)。例：在SC基本表中查找某學(xué)生（學(xué)號由give給出）的學(xué)習(xí)成績信息（S,C,GRADE），下面是該查詢的C語言函數(shù)定義：#defineNOM!(strcmp(SQLSTATE,“02000”))voidsel(){EXECSQLBEGINDECLARESECTION;Charsno[5],cno[6],give[5];Intg;CharSQLSTATE[6];EXECSQLENDDECLARESECTION；EXECSQLDECLARESCXCURSORFOR

SELECTS,C,gradeFROMSCWHERES=:give;EXECSQLOPENSCX;

While(1){EXECSQLFETCHFROMSCXINTO:sno,:cno,:g;If(NOM)break;Printf(“%s,%s,%d”,sno,cno,g);}EXECSQLCLOSESCX;}

第3章課外練習(xí)題

班級：姓名：學(xué)號：一.填空題

1.語與SQL中術(shù)語存在如下對應(yīng)關(guān)系,關(guān)系模式在SQL中稱為______;存儲模式稱為_________;子模式稱為_________。2.視圖是一個虛表，它是一個從__________________中導(dǎo)出的表。3.SELECT語句中，______子句用于選擇滿足給定條件的元組,使用_______子句可按指定的列的值分組,同時使用__________子句提取滿足條件的元組。4.SQL語言的數(shù)據(jù)定義功能包括________，________，_____和________。5.SQL語言有兩種使用方式,分別是________方式和___________方式。二.已知關(guān)系R:ABC134aac154R1.請寫出下列SELECT語句對R執(zhí)行的結(jié)果。SELECTA，CFROMRWHEREB=a;2.將上題中的SELECT語句執(zhí)行的功能，用關(guān)系代數(shù)表達(dá)式寫出。3.已知對R運(yùn)算的關(guān)系代數(shù)表達(dá)式：∏A,B(σ

A>2∧C=5(R)):(1)將關(guān)系代數(shù)表達(dá)式用等價的SELECT語句寫出。

(2)寫出關(guān)系代數(shù)表達(dá)式運(yùn)算結(jié)果。三.已知DBS中包含了三個基本表商品表：GOODS(G,GNAME,PRICE,TYPE,FACT)，其中G：商品號,GNAME:商品名，PRICE：單價，TYPE：型號，F(xiàn)ACT：制造商；商場表：SHOPS（S，SNAME，ADDR，MANAG）。其中分別為：商場號、商場名、地址和經(jīng)理名；銷售表：SALES（S，G，QTY）其中QTY為數(shù)量。1.試用SQL語句完成下列查詢(1)查詢所有電視機(jī)的生產(chǎn)廠商、型號、單價；(2)查詢同時生產(chǎn)電視機(jī)和電冰箱的制造商;(3)查詢“吉利”商場所銷售的各種商品的商品號和數(shù)量；(4)查詢銷售量最高的商場號和所銷售的商品號。2.試用SQL對基本表做創(chuàng)建和更新操作(1)創(chuàng)建銷售表：SALES（S，G，QTY）(類型、長度自定，定義主鍵）；(2)將“南華”廠的所有產(chǎn)品的名稱、型號和單價插到一個已存在的基本表A（GN，GTYPE，PRICE）中；(3)將總銷售量低于1000的所有商品的價格降低10%。第4章關(guān)系模式設(shè)計理論主要內(nèi)容：重點：

●函數(shù)依賴

●模式的范式化解決的主要問題：從理論上來講，如何設(shè)計一個比較好的關(guān)系模式的集合？●數(shù)據(jù)依賴（函數(shù)依賴、關(guān)鍵字、函數(shù)依賴的推理規(guī)則）●關(guān)系模式的分解（兩種特性：無損聯(lián)接性和保持依賴性）●模式的范式理論（1NF-4NF）其中：數(shù)據(jù)依賴是基礎(chǔ)。即：范式理論和關(guān)系模式的分解都是建立在數(shù)據(jù)依賴的概念基礎(chǔ)之上。

4.1關(guān)系模式使用中的異常問題例:設(shè)有關(guān)系模式R（TNAME，ADDR，C#，CNAME）（分別為：教師名，地址，課程號，課程名）其關(guān)系如下：TNAMEADDRC#CNAMEt1t1t1t2t2t3a1a1a1a2a2a3c1c2c3c4c5c6n1n2n3n4n5n6R現(xiàn)實世界的事實可知：一個教師只有一個地址一個教師可講若干課程每門課程只有一個教師任教R的侯選關(guān)鍵字為:(TNAME,C#)在使用過程中會存在以下問題：（1）數(shù)據(jù)冗余：當(dāng)一個教師若講多門課程，則其地址值會重復(fù)存儲多次。數(shù)據(jù)冗余：同一個數(shù)據(jù)重復(fù)被存儲在DB不同的位置中。

數(shù)據(jù)冗余會引起：①浪費(fèi)存儲空間；②造成修改數(shù)據(jù)不一致性。（2）更新操作異常①修改異常：由數(shù)據(jù)冗余引起的。如上例：t1教師講了三門課，其地址值a1重復(fù)存儲了三次；若t1搬家，則它的地址值必須修改三處值，若只修改一處，則會產(chǎn)生修改不一致性。②插入異常：指該插入的數(shù)據(jù)而不能插入到關(guān)系中。如：新增加一個教師，但尚未分配講課任務(wù)，則不能將其姓名和地址值插入到R中。原因：R的候選關(guān)鍵字（TNAME，C#）中，C#為空值。即：候選關(guān)鍵字中主屬性為空或部分為空的元組違反了實體完整性原則。③刪除異常：指不該從關(guān)系中刪除的數(shù)據(jù)被刪除了。如：若要把原來上過課，但目前未上課的教師的所有元組刪去，則將該教師的姓名和地址信息也從R中刪除了。什么原因使得關(guān)系產(chǎn)生操作異常和數(shù)據(jù)冗余呢？原因：關(guān)系模式中的屬性之間存在依賴問題；這就是引入屬性之間函數(shù)依賴的原因?，F(xiàn)在采用函數(shù)依賴的概念，利用分解方法，將R分解兩個等價的關(guān)系：R1（TNAME，ADDR）R2（C#,CNAME,TNAME）TNAMEADDRt1t2t3a1a2a3數(shù)據(jù)冗余大減，上述情況的異常消除。關(guān)系模式如何分解；分解到一個什么程度為好？將是本章討論的問題。R1R2C#CNAMETNAMEc1c2c3c4c5c6n1n2n3n4n5n6t1t1t1t2t2t3

4.2函數(shù)依賴一、函數(shù)依賴（FunctionalDependency

簡稱FD）的定義定義1：設(shè)有關(guān)系模式R（U），U={A1，A2，…，An}，，若對R的所有具體關(guān)系r都存在：對于每一個X值，都有唯一的Y值與之對應(yīng)，則稱X函數(shù)決定Y；或說Y函數(shù)依賴于X，記為：X→Y如：學(xué)號→姓名，姓名→年齡，姓名→性別。定義2：設(shè)有關(guān)系模式R(A1,A2,…,An),X和Y均為{A1,A2,…,An}的子集，r是R的任一具體的關(guān)系(R-型,r值),t1和t2是r中任意兩個元組,若由t1[X]=t2[X]導(dǎo)致t1[Y]=t2[Y],則稱X函數(shù)決定Y,或說Y函數(shù)依賴于X,記為：X→Y

注：(1)FD是對R一切可能的當(dāng)前值r定義的，不是針對某個特定關(guān)系。(2)FD是語義范疇的概念，只有通過屬性之間的語義來確定是否存在函數(shù)依賴關(guān)系，不能用數(shù)學(xué)方法推導(dǎo)或證明。它是現(xiàn)實世界中屬性之間客觀存在或設(shè)計者人為強(qiáng)制相結(jié)合的產(chǎn)物。例:若設(shè)計者限定：無同名同姓；則：姓名→年齡（反之：年齡

姓名），若有同名同姓：則，姓名

年齡。(3)

X→Y中，X稱為決定的因素，只要X取一個值，則有Y唯一的值與之對應(yīng)。二、函數(shù)依賴與屬性間聯(lián)系的關(guān)系設(shè)關(guān)系模式R，，則：(1)如果X,Y之間是1:1聯(lián)系，則存在:X→Y和Y→X，即X,Y相互函數(shù)依賴記為。(2)如果X,Y之間是m:1聯(lián)系，則存在

X→Y

,YX。(3)如果X,Y之間是n:m

聯(lián)系，則X,Y之間不存在函數(shù)依賴，即，.結(jié)論：根據(jù)關(guān)系r當(dāng)前值，可從屬性間的聯(lián)系入手來決定函數(shù)依賴是否存在。例：已知關(guān)系r如下ABCDEa1a1a2a2b1b2b1b1c1c2c3c4d1d2d3d3e1e1e1e1r下列函數(shù)依賴中，關(guān)系r滿足哪些依賴？a、A→Bb、(A,B)→Dc、C→(B,D,E)d、E→Ae、A→E

三、關(guān)鍵字（鍵、碼）用FD概念精確定義關(guān)鍵字定義：設(shè)關(guān)系模式R（A1，A2，…，An），F(xiàn)是R上的函數(shù)依賴集，X是（A1，A2，…，An）的一個子集，如果：（1）X→A1，A2，…，An且（2）在X中不存在真子集Y，使得Y→A1，A2，…，An成立，則稱X是R的候選關(guān)鍵字。注：條件（1）表示X能唯一決定一個元組。(唯一性)條件（2）表示X是滿足（1）而無多余的屬性集。（無多余性）例：關(guān)系模式R（學(xué)號，姓名，性別，年齡）中，按語義：學(xué)號→姓名，學(xué)號→性別，學(xué)號→年齡

∵學(xué)號→（學(xué)號，姓名，性別，年齡）

∴學(xué)號是R一個候選關(guān)鍵字。也可說明：（學(xué)號，姓名）也可決定R中的全部屬性，但（學(xué)號，姓名）不是候選關(guān)鍵字。

∵（學(xué)號，姓名）存在真子集：“學(xué)號”可以決定全部屬性；“姓名”屬性多余。說明：主屬性：包含在候選關(guān)鍵字中的屬性。非主屬性：不包含在候選關(guān)鍵字中的屬性。四、函數(shù)依賴的分類

根據(jù)不同性質(zhì)，函數(shù)依賴分類：完全函數(shù)依賴部分函數(shù)依賴傳遞函數(shù)依賴1、完全函數(shù)依賴與部分函數(shù)依賴定義：在關(guān)系模式R（U）中，如果X→Y，并且對X的任何一個真子集X′，都有則稱Y完全函數(shù)依賴于X，記作。如果對X某個真子集X′,有X′→Y，則稱Y對X的函數(shù)依賴是部分的函數(shù)依賴，記作：。例：關(guān)系模式SC(學(xué)號，課程號，成績)中，顯然：

學(xué)號

成績，課程號

成績，而：(學(xué)號，課程號)→成績,可見：(學(xué)號、課程號)是候選關(guān)鍵字：學(xué)號，課程號是主屬性；成績?yōu)榉侵鲗傩?。又如：部分函?shù)依賴的例子f

注：只有決定因素是組合屬性，才存在部分函數(shù)依賴，當(dāng)決定因素是單屬性時，只能是完全函數(shù)依賴。TNAMEADDRC#CNAMEt1t1t1t2t2t3a1a1a1a2a2a3c1c2c3c4c5c6n1n2n3n4n5n6前面的例子，如右邊的關(guān)系：主鍵：（TNAME,ADDR）因為：C#→CNAME,TNAMECNAME∴（TNAME,ADDR）→CNAMEP2、傳遞函數(shù)依賴定義：在關(guān)系模式R（U）中，如果，，且，,,則稱Z傳遞函數(shù)依賴于

，記作:。注：當(dāng)條件不成立時，即，則，實際上是直接函數(shù)依賴而非傳遞函數(shù)依賴。例：在關(guān)系模式R（學(xué)號，姓名，系名，系主任名）中。學(xué)號→系名，系名學(xué)號，系名→系主任名，則有：學(xué)號→系主任名。另外，學(xué)號→姓名，設(shè)無同名同姓：姓名→系名，姓名→學(xué)號，則有：學(xué)號→系名，是完全函數(shù)依賴。t五、函數(shù)依賴的邏輯蘊(yùn)涵有時需要從一些已知的FD去判斷另一些FD是否成立。如：已知F={A→B，B→C}在模式R中成立，那么A→C在R中是否成立。這個問題稱為FD的邏輯蘊(yùn)涵問題。定義：設(shè)F在關(guān)系R上成立的函數(shù)依賴集,X→Y是一個其他的FD(X?R,Y?R)。如果從F中的函數(shù)依賴能推出X→Y，則稱F邏輯蘊(yùn)涵X→Y，記為：F|＝X→Y。定義：被F函數(shù)蘊(yùn)涵的函數(shù)依賴的全體構(gòu)成的集合稱為F的閉包(closure),

記為：F+

={x→Y|F?X→Y}一般，F(xiàn)F+，若F=F+

則稱F是函數(shù)依賴的完備集。值得注意：依據(jù)F計算F+

是很麻煩的，即使F不大，F(xiàn)+

也可能很大。如：有關(guān)系R(X,Y,Z)其函數(shù)依賴集F={X→Y,Y→Z}則共有43個依賴這些函數(shù)依賴怎么出來？待學(xué)習(xí)了函數(shù)依賴的推理規(guī)則，再回答此問題。

4.2.4FD公理目的：確定函數(shù)依賴的邏輯蘊(yùn)涵。即從給定的F中的函數(shù)依賴，推出F+中的函數(shù)依賴。也就是說給定了F和X,Y，判斷是X→Y否在F+中。

為此，要有一套推理規(guī)則。一、Armstrong公理

設(shè)有關(guān)系R(A1,A2,…,An)和屬性全集U=A1A2…AnX，Y，Z，W均是U的子集；F是U上的一個函數(shù)依賴集，推導(dǎo)規(guī)則是：A1

自反性(Reflexivity)：若，則F邏輯蘊(yùn)涵

X→Y。（平凡的函數(shù)依賴）A2

增廣性(Augmentation)：若F中X→Y成立，則F邏輯蘊(yùn)涵:XZ→YZ。A3傳遞性(Transitivity)：若F中X→Y,Y→Z成立,則F邏輯蘊(yùn)涵:

X→Z。例：設(shè)R(C,S,Z)，其中：C為城市名，S為街名，Z為郵編，給定F={CS→Z,Z→C}

即函數(shù)依賴圖如下：CSZ由Armstrong公理，有(1)Z→C(已知)說明：Z→C邏輯蘊(yùn)涵F+中。(2)SZ→CS(由(1)和A2增廣性)說明:SZ→CS邏輯蘊(yùn)涵在F+中。(3)CS→Z(已知)(4)CS→CSZ(由(3)和A2增廣性)說明：CS→CSZ在F+，也說明CS是R的一個候選關(guān)鍵字。(5)SZ→CSZ(由(2)和A2增廣性，或由(2),(4)和A3傳遞性)說明：SZ→CSZ在F+中，也說明SZ是R的另一個候選關(guān)鍵字。注：可以證明候關(guān)鍵字：CS和SZ中沒有多余的屬性存在。說明：①

通過公理可以推出F中的隱含的FD，尤其可推出關(guān)系R的候選關(guān)鍵字。②

能否保證由公理推出的函數(shù)依賴都是正確的，即所推出的函數(shù)依賴都是否都屬于F+？

——公理的正確性。正確性：只要F的FD為真，由公理推出的FD也為真，則推出的FD都由F+邏輯蘊(yùn)涵。若公理正確，求F+，則可根據(jù)F通過公理推出所有的函數(shù)依賴。③屬于F+中函數(shù)依賴是否都能夠由公理通過F中的FD推出？——公理完備性。

推論：由阿氐公理可以得出如下推論：(1)合并規(guī)則：若，成立，則成立(2)分解規(guī)則：若成立，則，成立(3)偽傳遞規(guī)則：若，成立，則成立證明：用公理證明(1)∵(已知)(2)∵ (3)∵(已知)∴(公理A2)

∴(公理A1)

∴(公理A2)

又∵(已知)同理(公理A1)∵(已知)∴(公理A2)∵(已知)∴(公理A3)∴(公理A3) ∴(公理A3)

同理(公理A3)二.推論根據(jù)推論中(1)和(2)合并和分解規(guī)則：得出推論：若Ai(i=1,2,…,n)是關(guān)系模式R的屬性集則X→A1A2…An成立的充分必要條件是X→Ai(i=1,2,…,n)均成立。例：判斷下列推論是否正確，若正確給出相關(guān)證明；若錯誤，試舉出一反例說明(1)如果AB→C，則B→C(2)如果AB→C，則A→B,或A→C(3)如果A→B并且BC→D,則ABC→D解：(1)錯誤 (2)錯誤 (3)正確ABC010001ABC000101RR∵A→B∴ABC→BC又∵BC→D∴ABC→D（公理A3）R滿足AB→C但B

C;R滿足AB→C但AC，AB說明：①

通過規(guī)則A1，得出平凡的FD概念：對于函數(shù)依賴X→Y,若,則稱X→Y是平凡的函數(shù)依賴；若，X→Y則為非平凡的FD。如：A→A,A→Φ是平凡FD.平凡FD總是成立的，對A的語義無影響，對模式設(shè)計也沒有影響。一般不考慮平凡FD已知函數(shù)依賴集F,利用阿氏公理求邏輯蘊(yùn)涵在F中的函數(shù)依賴（未知的FD，也就是包含F(xiàn)+中），工作量是指數(shù)級的。尋找求FD的工作量小的方法：屬性集的閉包。②屬性集的閉包概念定義：設(shè)有關(guān)系模式R(A1,A2,…,An)，U=A1,A2…,An；F是U上的一個函數(shù)依賴集X是U的子集，則稱所有用公理從F推出的函數(shù)依賴X→Ai中的Ai的屬性集合為X的屬性閉包，記為X+即：

X+={Ai∣用阿氏公理從F推出X→Ai

}。顯然：下列定理告訴我們判斷：從X+一眼看出某一函數(shù)依賴是否是用阿氏公理從F推出。定理：設(shè)F是關(guān)系模式R(A1,A2,…,An)的FD集，U=A1,A2…,An。若X,Y是U的子集，則X→Y是用阿氐公理從F推出的充要條件是。例：設(shè)關(guān)系模式R(A,B,C),R的FD集F={A→B,B→C}則：

C+=C(意味著：C→C成立)B+=BC(意味著：B→B,B→C,B→BC都成立)A+=ABC(意味著：A→A，A→B，A→C；A→AB,A→BC,A→AC,A→ABC7個成立)

∴A是R的侯選關(guān)鍵字

可見：計算屬性閉包X+比從F利用FD公理計算F+中的FD要簡單得多。三、Armstrong公理是完備的。

證明略。Armstrong公理是完備的，說明：包含在中F+的所有FD,都能用阿氐公理從F推導(dǎo)出。阿氏公理三個規(guī)則夠用了。

(2)把計算F+

轉(zhuǎn)化為X+計算，可達(dá)到同樣的目的。通過計算X+，若，則X→Y是由阿氐定理從F推出，則

X→Y是成立的。即：

,則X→Y成立。就是：算法：求屬性集在F上的屬性閉包。輸入：關(guān)系模式R的全部屬性集U，F(xiàn)為U上的函數(shù)依賴集輸出：在F上的屬性閉包方法：按下列規(guī)則計算；(1)置初值(2)其中A是這樣的屬性：在F中尋找尚未用過的左邊是的子集的函數(shù)依賴：其中：在Z中尋找中未出現(xiàn)過的屬性集合A；若無這樣的A則轉(zhuǎn)(4)，否則：(3)判斷是否有，若是則轉(zhuǎn)(4)，否則轉(zhuǎn)(2)(4)輸出，即為上述方法計算步驟是很有限的，因為U,X,F是有限集。四、屬性集閉包的計算例：設(shè)函數(shù)依賴集F={AB→C,D→EG,C→A,BE→C,BC→D,CG→BD,ACD→B,CE→AG}

試用算法，求(BD)+解：設(shè)