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

第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浮點型（取決于機器）FLOAT（n）浮點型精度為n位NCMERIC（P，d）定點數(shù)（p位數(shù)字，小數(shù)點后d位）（2）字符串型

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

VARCHAR（n）長度可變字符串，具有最大長度n（3）位串型BIT（N）長度為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)系的查找。（浪費外存空間）在基本表上可建立一個或多個索引，索引存在存儲文件中稱索引文件。句型：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〉的值進行分組，列值相等的元組分為一組，每一組會產(chǎn)生結(jié)果表中的一條記錄。having子句將滿足條件的組給出輸出。order子句對輸出的目標(biāo)表按ASC升序或DESC降序排列。

select基本句型

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

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

R2…Rn))算術(shù)比較符：<，<=，>，>=，=，<>或！=。邏輯符：AND，OR，NOT。集合運算符：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運算的關(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ù)運算的結(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）和計算機系（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.集合查詢例：查找計算機系的學(xué)生及年齡不大于19歲的學(xué)生實際求計算機系的所有學(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提供了一定程度的邏輯獨立性。例：將表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ù)提供保護。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ī)定●擴充宿主語言的編譯程序功能，直接處理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）機制，把集合操作轉(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)推進語句（FETCH）：游標(biāo)推進一行，將游標(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)機制的使用技術(shù)b、不涉及游標(biāo)機制的使用技術(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)機制將多個元組一次一個從DB中讀出并處理。具體過程：用游標(biāo)定義語句定義一個游標(biāo)與某一個SELECT語句對應(yīng)。用游標(biāo)打開語句（OPEN）打開，使游標(biāo)