第四章結(jié)構(gòu)化查詢語言SQL教學(xué)內(nèi)容：教學(xué)重點(diǎn)：

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

嵌入式SQL的使用。

SQL數(shù)據(jù)庫的體系結(jié)構(gòu)，SQL的組成；

SQL的數(shù)據(jù)定義：SQL模式、基本表和索引的創(chuàng)建和撤銷；

SQL的數(shù)據(jù)查詢：SELECT語句的句法和使用；

SQL的數(shù)據(jù)更新：插入、刪除和修改語句；

視圖的創(chuàng)建和撤消，對視圖更新操作的限制；

嵌入式SQL：預(yù)處理方式,使用規(guī)定,使用技術(shù),動態(tài)SQL語句。

§1SQL概貌及特點(diǎn)一、SQL數(shù)據(jù)庫的體系結(jié)構(gòu)

SQL數(shù)據(jù)庫的體系結(jié)構(gòu)基本上也是三級模式結(jié)構(gòu)。

在SQL中:外模式對應(yīng)于視圖，

模式對應(yīng)于基本表，元組稱為“行”，屬性稱為“列”，

內(nèi)模式對應(yīng)于存儲文件。用戶1用戶2用戶3用戶4視圖1視圖2

外模式基本表1基本表2基本表3基本表4模式存儲文件1存儲文件2存儲文件3存儲文件4內(nèi)模式

二、SQL的組成

SQL主要分成四個部分：數(shù)據(jù)定義；數(shù)據(jù)操縱；數(shù)據(jù)控制；嵌入式SQL的使用。

三、SQL的主要特點(diǎn)

1.一體化；

2.兩種使用方式，統(tǒng)一的語法結(jié)構(gòu)；

3.高度非過程化；

4.語言簡潔，易學(xué)易用。§2SQL的數(shù)據(jù)定義一、SQL模式的創(chuàng)建和撤消1.SQL模式的創(chuàng)建

CREATESCHEMA<模式名>AUTHORIZATION<用戶名>2.SQL模式的撤消

DROPSCHEMA<模式名>[CASCADE|RESTRICT]CREATEDATABASE<數(shù)據(jù)庫名>DROPDATABASE<數(shù)據(jù)庫名>

例：創(chuàng)建工程數(shù)據(jù)庫PROJECT

CREATEDATABASEPROJECT

例：撤消工程數(shù)據(jù)庫PROJECTDROPDATABASEPROJECT

例：創(chuàng)建學(xué)生數(shù)據(jù)庫STUDENT

CREATEDATABASESTUDENT

例：撤消學(xué)生數(shù)據(jù)庫STUDENT

DROPDATABASESTUDENT

二、基本表的創(chuàng)建、修改和撤消

1.基本表的創(chuàng)建

CREATETABLESQL

模式名.基本表名（列名類型，

……

完整性約束，

……）

完整性規(guī)則主要有三種：

主鍵子句（PRIMARYKEY）；

檢查子句（CHECK）；

外鍵子句（FOREIGNKEY）。SQL提供的基本數(shù)據(jù)類型:數(shù)值型:INTEGER長整數(shù)（也可寫成INT）

SMALLINT短整數(shù)

REAL取決于機(jī)器精度的浮點(diǎn)數(shù)

DOUBLEPRECISION取決于機(jī)器精度的雙精度浮點(diǎn)數(shù):FLOAT(n)浮點(diǎn)數(shù)，精度至少為n位數(shù)字;

NUMERIC（p,d）定點(diǎn)數(shù)，由p位數(shù)字（不包括符號、小數(shù)點(diǎn)）組成，小數(shù)點(diǎn)后面有d位數(shù)字;

也可寫成:DECIMAL（P，d）或DEC（P，d））SQL提供的基本數(shù)據(jù)類型

字符串型:CHAR（n）長度為n的定長字符串

VARCHAR(n)具有最大長度為n的變長字符串位串型:

BIT（n）長度為n的二進(jìn)制位串

BITVARYING（n）最大長度為n的變長二進(jìn)制位時間型:DATE日期，包含年、月、日,YYYY—MM－DDTIME時間，包含時、分、秒，HH:MM:SS【例4.1】在有關(guān)工程項(xiàng)目的數(shù)據(jù)庫中，有四個關(guān)系，其結(jié)構(gòu)如下：供應(yīng)商關(guān)系：S(SNO，SNAME，SADDR)零件關(guān)系：P(PNO，PNAME，COLOR，WEIGHT)工程項(xiàng)目關(guān)系：J(JNO，JNAME，JCITY，BALANCE)供應(yīng)情況關(guān)系：SPJ(SNO，PNO，JNO，PRICE，QTY)例:工程項(xiàng)目數(shù)據(jù)庫PROJECT中有四個關(guān)系，其結(jié)構(gòu)如下：供應(yīng)商關(guān)系：

S(SNO，SNAME，SADDR)零件關(guān)系：

P(PNO，PNAME，COLOR，WEIGHT)工程項(xiàng)目關(guān)系：J(JNO，JNAME，JCITY，BALANCE)供應(yīng)情況關(guān)系：SPJ(SNO，PNO，JNO，PRICE，QTY)可用下列語句創(chuàng)建表S:CREATETABLES(SNOCHAR(4)NOTNULL,SNAMECHAR(20)NOTNULL,SADDRCHAR(20)，

PRIMARYKEY(SNO));可用下列語句創(chuàng)建表P:

CREATETABLEP(PNOCHAR(4)NOTNULL,PNAMECHAR(20)NOTNULL,COLORCHAR(8),WEIGHTSMALLINT,PRIMARYKEY(PNO));可用下列語句創(chuàng)建表S:

CREATETABLEJ(JNOCHAR(4)NOTNULL,JNAMECHAR(20),JCITYCHAR(20),BALANCENUMERIC(7,2),PRIMARYKEY(JNO));可用下列語句創(chuàng)建表SPJ:CREATETABLESPJ(SNOCHAR(4)NOTNULL,PNOCHAR(4)NOTNULL,JNOCHAR(4)NOTNULL,PRICENUMERIC(7,2),QTYSMALLINT，PRIMARYKEY(SNO,PNO,JNO),FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(PNO)REFERENCESP(PNO),FOREIGNKEY(JNO)REFERENCESJ(JNO),CONSTRAINTC_QTYCHECK(QTYBETWEEN0AND10000)【例4.1】在有關(guān)工程項(xiàng)目的數(shù)據(jù)庫中，有四個關(guān)系，其結(jié)構(gòu)如下：供應(yīng)商關(guān)系：S(SNO，SNAME，SADDR)零件關(guān)系：P(PNO，PNAME，COLOR，WEIGHT)工程項(xiàng)目關(guān)系：J(JNO，JNAME，JCITY，BALANCE)供應(yīng)情況關(guān)系：SPJ(SNO，PNO，JNO，PRICE，QTY)例:學(xué)生數(shù)據(jù)庫STUDENT中有三個關(guān)系，其結(jié)構(gòu)如下：學(xué)生關(guān)系：

S(SNO，SNAME，AGE，SEX，SDEPT)

課程關(guān)系：

C(CNO，CNAME，CDEPT，TNAME)

學(xué)習(xí)關(guān)系：SC(SNO，CNO，GRADE)可用下列語句創(chuàng)建表S:CREATETABLES(SNOCHAR(4)NOTNULL,SNAMECHAR(20)NOTNULL,AGECHAR(2),SEXCHAR(2),SDEPTCHAR(10),

PRIMARYKEY(SNO));可用下列語句創(chuàng)建表C:CREATETABLEC(CNOCHAR(4)NOTNULL,CNAMECHAR(20)NOTNULL,CDEPTCHAR(10),TNAMECHAR(8),PRIMARYKEY(CNO));主鍵子句主鍵子句可用下列語句創(chuàng)建表SC:CREATETABLESC(SNOCHAR(4)NOTNULL,CNOCHAR(4)NOTNULL,GRADENUMERIC(7,2),

PRIMARYKEY(SNO,CNO),

FOREIGNKEY(SNO)REFERENCESS(SNO),FOREIGNKEY(CNO)REFERENCESP(CNO),CONSTRAINTSC_GRADECHECK(GRADEBETWEEN0AND100));主鍵子句外鍵子句檢查子句2.基本表結(jié)構(gòu)的修改

增加新的屬性：

刪除原有的屬性：

ALTERTABLE基本表名ADD新屬性名新屬性類型例:在基本表S中增加一個電話號碼（TELE）屬性語句如下：

ALTERTABLESADDTELECHAR(12);ALTERTABLE基本表名DROP屬性名[CASCADE|RESTRICT]例:在表S中刪除電話號碼（TELE）屬性，并且將引用該屬性的所有視圖和約束也一起刪除，可用下列語句：

ALTERTABLESDROPTELECASCADE；

刪除指定的完整性約束條件，其句法如下：ALTERTABLE基本表名DROP約束名；例:在基本表SC中刪除對屬性GRADE的約束，可用下列語句：ALTERTABLESCDROPSC_GRADE；另外，可以用下列語句增加（即恢復(fù)）對屬性GRADE的約束：ALTERTABLESPJADDCONSTRAINTSC_GRADECHECK(GRADEBETWEEN0AND100)3.刪除基本表刪除基本表可以用“DROPTABLE”語句刪除基本表。其句法如下：

DROPTABLE基本表[CASCADE|RESTRICT]此處的CASCADE和RESTRICT的語義同前面句法中的語義一樣。

在一個基本表刪除后，其所有數(shù)據(jù)也就丟失了，使用時要謹(jǐn)慎?！?SQL的數(shù)據(jù)查詢

SQL的數(shù)據(jù)查詢(SELECT語句)是SQL的核心內(nèi)容。一、SELECT語句的來歷在關(guān)系代數(shù)中最常用的式子是下列表達(dá)式：

πA1,…,An(σF(R1×…×Rm))

這里R1、…、Rm為關(guān)系，F(xiàn)是公式，A1、…、An為屬性。為此SQL設(shè)計成SELECT–FROM-WHERE句型：

SELECTA1，…，An FROMR1，…，Rm WHEREF

二、SELECT語句格式：

SELECT[DISTINCT]目標(biāo)表的列名(或)列表達(dá)式序列

FROM

基本表名(或)視圖名序列|表引用

[WHERE行條件表達(dá)式］

[GROUPBY

列名1序列[HAVING

組條件表達(dá)式］］

[ORDERBY

列名2[ASC|DESC］序列］；整個語句的執(zhí)行過程如下：

2.選取滿足WHERE子句中給出的條件表達(dá)式的元組。3.按GROUP子句中指定列的值分組，同時提取滿足HAVING子句中組條件表達(dá)式的那些組。4.按SELECT子句中給出的列名或列表達(dá)式求值輸出。5.ORDER子句對輸出的目標(biāo)表進(jìn)行排序,按附加說明ASC升序排列，

或按DESC降序排列。1.讀取FROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。SELECT語句中:

WHERE子句稱為“行條件子句”，

GROUP子句稱為“分組子句”，

HAVING子句稱為“組條件子句”，

ORDER子句稱為“排序子句”。

在WHERE子句的行條件表達(dá)式中可使用下列運(yùn)算符：

算術(shù)比較運(yùn)算符：＜，＜＝，＞，＞＝，＝，＜＞或?。剑?/p>

邏輯運(yùn)算符：AND，OR，NOT；

集合成員資格運(yùn)算符：IN，NOTIN；

謂詞：EXISTS，ALL，SOME，UNIQUE；

聚合函數(shù)：AVG，MIN，MAX，SUM，COUNT；

集合運(yùn)算符：UNION，INTERSECT，EXCEPT。

3.舉例（板書舉例）

舉例：假設(shè)學(xué)生數(shù)據(jù)庫中的關(guān)系模式如下：

S（SNO,SNAME,AGE,SEX,SDEPT）

C（CNO,CNAME,CDEPT,TNAME）

SC（SNO,CNO,GRADE）試用SQL表達(dá)下列每個查詢語句。.

檢索選修課程號為C2的學(xué)生的學(xué)號和成績。SELECTSNO,GRADE

FROMSCWHERE

CNO='C2';2.檢索計算機(jī)軟件、姓“李”的全體男同學(xué)的學(xué)號、姓名和出生年份。SELECT

SNO,SNAME,2020-AGEAS

BIRTH_YEAR

FROMSWHERE

SNAMELIKE

'李%'

AND

SDEPT='計算機(jī)軟件'AND

SEX='男';LIKE謂詞的一般形式是：列名LIKE字符串常數(shù)其中：列名的類型必須是字符串或可變字符串。字符串常數(shù)中通配符的含義如下：%(百分號):表示可以與任意長度的字符串匹配。_(下劃線):表示可以與任意單個字符匹配。所有其他的字符只代表自己。別名3.檢索選修課程號為C4的學(xué)生的學(xué)號和姓名。第一種方法：連接查詢

SELECTS.SNO,SNAMEFROMS,SCWHERES.SNO=SC.SNOANDCNO='C4';第二種方法：嵌套查詢①

SELECTS.SNO,SNAMEFROMSWHERE

SNOIN

(SELECTSNOFROMSCWHERECNO='C4’);第二種方法：嵌套查詢②相關(guān)子查詢)

SELECTS.SNO,SNAMEFROMSWHERE

'C4'IN(SELECTCNOFROMSCWHERES.SNO=SC.SNO);第三種方法：使用存在量詞的嵌套SELECTS.SNO,SNAMEFROMSWHEREEXISTS(SELECT*FROMSCWHERES.SNO=SC.SNOANDSC.CNO='C4’);-外層查詢-內(nèi)層查詢(即:子查詢)子查詢依賴于外層查詢子查詢依賴于外層查詢SELECTS.SNO,SNAMEFROMSWHERESNOIN(SELECTSNOFROMSCWHERECNOIN(SELECTCNOFROMCWHERECNAME='數(shù)據(jù)庫原理'));4.檢索選修課程名為“數(shù)據(jù)庫原理”的學(xué)生的學(xué)號和姓名。--首先在C表中找出數(shù)據(jù)庫原理課程的課程號--然后在SC表中找出選了該課程的學(xué)生的學(xué)號--最后在S表中找出這些學(xué)生的姓名SELECTX.SNOFROMSCX,SCYWHEREX.SNO=Y.SNOANDX.CNO='C2'ANDY.CNO='C4';5.檢索選修課程號為C2和C4的學(xué)生的學(xué)號。或：SELECTX.SNOFROM(SELECTSNOFROMSCWHERECNO='C2')X,(SELECTSNOFROMSCWHERECNO='C4')YWHEREX.SNO=Y.SNO;派生表自身聯(lián)接6.檢索沒有選修C2課程的學(xué)生的姓名和年齡。SELECTSNAME,AGEFROMSWHERENOTEXISTS(SELECT*FROMSCWHERESC.SNO=S.SNOANDSC.CNO='C2');----找這樣的學(xué)生----不存在---他（即找的這個學(xué)生）---選了‘C2’課程假設(shè)：cno:表示課程；

p(cno):表示“選修了課程cno”；

p(cno):表示“沒有選修課程cno”形式化表示：

(

cno)p(cno)≡

(

cno)(

p(cno))變換后的語義：

找這樣的學(xué)生SNO，不存在一門課程，他沒有選修。7.檢索選修了全部課程的學(xué)生的姓名。--查詢變換依據(jù)：

7.檢索選修了全部課程的學(xué)生的姓名。--

變換為：找這樣的學(xué)生SNO，不存在一門課程，他沒有選修。SELECTSNAMEFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESC.SNO=S.SNOANDSC.CNO=C.CNO));----找這樣的學(xué)生----不存在一門課程他沒有--選修8.檢索至少選修了學(xué)生S2所選修的全部課程的學(xué)生的學(xué)號。變換表達(dá)形式：找這樣的學(xué)生SNO，對所有的課程CNO，只要學(xué)生S2選修了，那么學(xué)生SNO也選修了這些課程CNO。形式化表示：(

cno)p(cno)

q(cno)其中：cno:表示課程；

p(cno):表示“學(xué)生S2選修了課程cno”；

q(cno):表示“學(xué)生SNO選修了課程cno”

≡

(

cno)(

(p(cno)

q(cno)))≡

(

cno)(

(

p(cno)∨q(cno)))≡

(

cno)(p(cno)∧

q(cno))變換后的語義：找這樣的學(xué)生SNO，不存在一門課程cno，學(xué)生S2選修了，而學(xué)生sno沒選。SELECTDISTINCTSNOFROMSCXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.SNO='S2'AND

NOTEXISTS(SELECT*FROMSCZWHEREZ.SNO=X.SNOANDZ.CNO=Y.CNO));8.檢索至少選修了學(xué)生S2所選修的全部課程的學(xué)生的學(xué)號。----找這樣的學(xué)生----不存在一門課程---學(xué)生S2選了而這個學(xué)生沒選SELECTDISTINCTSNOFROMSCXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.SNO='S2'AND

NOTEXISTS(SELECT*FROMSCZWHEREZ.SNO=X.SNOANDZ.CNO=Y.CNO));8.檢索至少選修了學(xué)生S2所選修的全部課程的學(xué)生的學(xué)號。----找這樣的學(xué)生----不存在一門課程---學(xué)生S2選了而這個學(xué)生沒選如要在結(jié)果集中不包含學(xué)生S2的SQL語句如下：SELECTDISTINCTSNO

FROM

(SELECT*FROMSCWHERESNO<>'S2')XWHERENOTEXISTS(SELECT*FROMSCYWHEREY.SNO='S2'ANDNOTEXISTS(SELECT*FROMSCZWHEREZ.SNO=X.SNOANDZ.CNO=Y.CNO));四、聚合函數(shù)

SQL提供了下列聚合函數(shù)：

COUNT（*）計算元組的個數(shù)

COUNT（列名）對一列中的值計算個數(shù)

SUM（列名）求某一列值的總和（此列的值必須是數(shù)值）

AVG（列名）求某一列值的平均值（此列的值必須是數(shù)值）

MAX（列名）求某一列值的最大值

MIN（列名）求某一列值的最小值舉例：1.檢索年齡最大的學(xué)生的姓名和性別。SELECTSNAME,SEXFROMSWHEREAGE=(SELECTMAX(AGE)FROMS);2.檢索選修兩門以上課程的學(xué)生的學(xué)號。SELECTSNO,COUNT(*)COUNTFROMSCGROUPBYSNOHAVINGCOUNT(*)>2;

SELECTS.SNO學(xué)號,SNAME姓名,COUNT(CNO)課程門數(shù)

FROMSC,SWHERES.SNO=SC.SNO GROUPBYS.SNO,SNAME HAVINGCOUNT(*)>2 ORDERBY3DESC,1;3.統(tǒng)計每個學(xué)生選修課程的門數(shù)（超過2門的學(xué)生才統(tǒng)計）。

要求顯示學(xué)生的學(xué)號、姓名和選修課程門數(shù)，查詢結(jié)果按門數(shù)降序排列，若門數(shù)相同，按學(xué)號升序排列:

SELECT語句的語義通常有三種情況（SQL標(biāo)準(zhǔn)）:

以學(xué)生表S（SNO，SNAME，AGE，SEX，SDEPT）為例說明。第一種情況：SELECT語句中未使用分組子句,也未使用聚合操作，那么SELECT子句的語義是對查詢的結(jié)果執(zhí)行投影操作。如： SELECTSNO,SNAMEFROMSWHERESEX='男';第二種情況：SELECT語句中未使用分組子句，但在SELECT子句中使用了聚合操作，此時SELECT子句的語義是對查詢結(jié)果執(zhí)行聚合操作。如：

SELECTCOUNT(*)count_男,AVG(CAST(S.AGEASINT))avg_ageFROMSWHERESEX='男’;

該語句是求男同學(xué)的人數(shù)和平均年齡（假設(shè)AVG為字符型）第三種情況：SELECT語句使用了分組子句和聚合操作，此時SELECT子句的語義是對查詢結(jié)果的每一分組去做聚合操作。如：SELECTAGE,COUNT(*)countFROMSWHERESEX='男'GROUPBYAGE;

該語句是求男同學(xué)每一年齡的人數(shù)。

五.聯(lián)接操作

聯(lián)接條件可在WHERE中指定,也可以在FROM子句中指定。

在FROM子句中指定聯(lián)接條件時，SQL2開始將聯(lián)接操作符分成:

聯(lián)接類型、聯(lián)接條件。聯(lián)接類型：決定了如何處理聯(lián)接條件中不匹配的元組。聯(lián)接條件：決定了兩個關(guān)系中哪些元組應(yīng)該匹配。聯(lián)接類型中的OUTER字樣可不寫。聯(lián)接類型聯(lián)接類型說明INNERJOIN內(nèi)聯(lián)接：LEFTOUTERJOIN左外聯(lián)接：RIGHTOUTERJOIN右外聯(lián)接：FULLOUTERJOIN完全外聯(lián)接：CROSSJOIN交叉聯(lián)接：

結(jié)果為兩個聯(lián)接表中匹配行的聯(lián)接。

結(jié)果包括“左”表（出現(xiàn)在JOIN子句的最左邊）中的所有行。不包括右表中的不匹配行。

結(jié)果包括“右”表（出現(xiàn)在JOIN子句的最右邊）中的所有行。不包括左表中的不匹配行。結(jié)果包括所有聯(lián)接表中的所有行，不論它們是否匹配

結(jié)果包括兩個聯(lián)接表中所有可能的行組合。交叉聯(lián)接返回的是兩個表的笛卡兒積ABCabcbbfcadBCDbcdbceadbefg關(guān)系R關(guān)系SABCDabcdabcecadbbbfnullnullefgRSABCDabcdabcecadbbbfnullRSABCDabcdabcecadbnullefgRS聯(lián)接條件聯(lián)接條件說明ON聯(lián)接條件具體列出兩個關(guān)系在哪些相應(yīng)屬性上做聯(lián)接條件比較。聯(lián)接條件應(yīng)寫在聯(lián)接類型的右邊。例1：統(tǒng)計每個學(xué)生選修課程的門數(shù)（超過2門的學(xué)生才統(tǒng)計）。要求顯示學(xué)生的學(xué)號、姓名和選修門數(shù)，查詢結(jié)果按門數(shù)降序排列，若門數(shù)相同，按學(xué)號升序排列:

SELECTS.SNO,SNAME,COUNT(CNO)ASCOUNTERFROMS,SCWHERES.SNO=SC.SNOGROUPBYS.SNO,SNAMEHAVINGCOUNT(*)>2ORDERBY3DESC,1;

SELECTS.SNO,SNAME,COUNT(CNO)ASCOUNTERFROMS,SCWHERES.SNO=SC.SNOGROUPBYS.SNO,SNAMEHAVINGCOUNT(*)>2ORDERBY3DESC,1;SELECTS.SNO,S.SNAME,COUNT(SC.CNO)ASCOUNTERFROMSINNERJOINSCONSC.SNO=S.SNOGROUPBYS.SNO,S.SNAMEHAVING(COUNT(*)>2)ORDERBYCOUNT(SC.CNO)DESC,S.SNO內(nèi)連接

SELECT*FROMSFULLOUTERJOIN(SELECT*FROMSCWHERESC.CNO='C3')SC1ONS.SNO=SC1.SNO

SELECT*FROMSINNERJOINSCONS.SNO=SC.SNOWHERE(SC.CNO=‘C3')例2:檢索選修了課程號為C3的學(xué)生的情況。為顯示更多的信息：

SELECT*FROMSFULLOUTERJOIN(SELECT*FROMSCWHERESC.CNO='C3')SC1ONS.SNO=SC1.SNO

SELECT*FROMSINNERJOINSCONS.SNO=SC.SNOWHERE(SC.CNO=‘C3')例2:檢索選修了課程號為C3的學(xué)生的情況。為顯示更多的信息：§4SQL的數(shù)據(jù)更新

SQL的數(shù)據(jù)更新包括：數(shù)據(jù)插入、修改數(shù)據(jù)和數(shù)據(jù)刪除等操作。

一、數(shù)據(jù)插入

1.插入單個元組：

INSERTINTO

基本表名（列名表）

VALUES

（元組值）

例：

INSERTINTOSC(SNO,CNO)

VALUES(‘S3’,‘C3’)

2.插入子查詢的結(jié)果：

INSERTINTO

基本表名（列名表）

SELECT

查詢語句；例：

CREATETABLES_AVG_GRADE(SNOCHAR(4),AVGGRADENUMERIC(7,2));

INSERTINTOS_AVG_GRADE

SELECTSNO,AVG(GRADE)

FROMSC

GROUPBYSNO;

二、數(shù)據(jù)刪除刪除關(guān)系中滿足條件的元組語句的句法如下：

DELETEFROM<表名>

WHERE<條件表達(dá)式>舉例:例：把課程名為”數(shù)據(jù)結(jié)構(gòu)”的選課從表SC中刪除.

DELETEFROMSCWHERE

CNOIN(SELECTCNOFROMCWHERECNAME='數(shù)據(jù)結(jié)構(gòu)');

三、數(shù)據(jù)修改

UPDATE

基本表名

SET列名=值表達(dá)式[，列名=值表達(dá)式…][WHERE條件表達(dá)式]例：把課程名為”數(shù)據(jù)庫原理”的成績提高10%.

UPDATESCSETGRADE=1.1*GRADE

WHERE

CNOIN(SELECTCNOFROMCWHERECNAME='數(shù)據(jù)庫原理');

§5

視圖操作

在SQL中，外模式一級數(shù)據(jù)結(jié)構(gòu)的基本單位是視圖（View）。視圖是從若干基本表和（或）其他視圖構(gòu)造出來的表。在創(chuàng)建一個視圖時，系統(tǒng)把視圖的定義存放在數(shù)據(jù)字典中，而不存儲視圖對應(yīng)的數(shù)據(jù)，在用戶使用視圖時才去求對應(yīng)的數(shù)據(jù)。

視圖被稱為“虛表”。一、

視圖的創(chuàng)建

CREATEVIEW<視圖名>（列名表）

AS<SELECT查詢語句>例1：在基本表SC上，建立一個學(xué)生學(xué)習(xí)情況視圖，內(nèi)容包括：學(xué)號、選修課程門數(shù)、平均成績。CREATEVIEWS_GRADE(SNO,C_NUM,AVG_GRADE) AS(SELECTSNO,COUNT(CNO),AVG(GRADE) FROMSC GROUPBYSNO);例2：在基本表SC上，建立一個學(xué)生學(xué)習(xí)成績等級視圖：CREATEVIEW

SC_等級(SNO,SNAME,CNO,CNAME,成績等級)AS(SELECTS.SNO,SNAME,C.CNO,CNAME,

成績等級=CASE

WHENGRADEISNULLTHEN'未登分'WHENGRADE<60THEN'不及格'

WHENGRADE>=60ANDGRADE<70THEN'及格'

WHENGRADE>=70ANDGRADE<80THEN'中'

WHENGRADE>=80ANDGRADE<90THEN'良'ELSE'優(yōu)'ENDFROMSINNERJOINSCONS.SNO=SC.SNOINNERJOINCONSC.CNO=C.CNO)例2：在基本表SC上，建立一個學(xué)生學(xué)習(xí)成績等級視圖：CREATEVIEW

SC_等級(SNO,SNAME,CNO,CNAME,成績等級)AS(SELECTS.SNO,SNAME,C.CNO,CNAME,

成績等級=CASE

WHENGRADEISNULLTHEN'未登分'WHENGRADE<60THEN'不及格'

WHENGRADE>=60ANDGRADE<70THEN'及格'

WHENGRADE>=70ANDGRADE<80THEN'中'

WHENGRADE>=80ANDGRADE<90THEN'良'ELSE'優(yōu)'ENDFROMSINNERJOINSCONS.SNO=SC.SNOINNERJOINCONSC.CNO=C.CNO)二、視圖的撤消

DROPVIEW

視圖名例：撤消S_GRADE視圖，可用下列語句實(shí)現(xiàn)：

DROPVIEWS_GRADE;三、視圖的查詢

系統(tǒng)在實(shí)現(xiàn)對視圖的查詢時，根據(jù)數(shù)據(jù)字典的定義將對視圖的查詢轉(zhuǎn)換為對基本表的查詢。例：對學(xué)生學(xué)習(xí)情況視圖執(zhí)行如下操作：① SELECT*FROMS_GRADE;

相應(yīng)的查詢轉(zhuǎn)換操作如下：

SELECTSNO,COUNT(CNO)ASC_NUM,AVG(GRADE)ASAVG_GRADEFROMSCGROUPBYSNO;② SELECTSNO,C_NUM FROMS_GRADE

WHEREAVG_GRADE>80;

相應(yīng)的查詢轉(zhuǎn)換操作如下：SELECTSNO,COUNT(CNO)ASC_NUMFROMSCGROUPBYSNO

HAVINGAVG(GRADE)>80;③ SELECTSNO,AVG_GRADE FROMS_GRADE

WHEREC_NUM>(SELECTC_NUMFROMS_GRADE

WHERESNO=‘S4’);

相應(yīng)的查詢轉(zhuǎn)換操作如下：SELECTSNO,AVG(GRADE)ASAVG_GRADEFROMSCGROUPBYSNO

HAVINGCOUNT(CNO)>(SELECTCOUNT(CNO)FROMSCGROUPBYSNO

HAVINGSNO=‘S4’);

四、視圖的更新操作對于視圖的更新操作(INSERT、DELET、UPDATA)有以下三條規(guī)則：②如果在導(dǎo)出視圖的過程中，使用了分組和聚合操作，不允許對這個視圖執(zhí)行更新操作。③如果一個視圖是從多個基本表使用聯(lián)接操作導(dǎo)出的，通常不允許對這個視圖執(zhí)行更新操作。①如果視圖是從單個基本表使用選擇、投影操作導(dǎo)出的，并且包含了基本表的主鍵或某個候選鍵，那么這樣的視圖稱為

“行列子集視圖”，并且可以被執(zhí)行更新操作。

四、視圖的更新操作對于視圖的更新操作(INSERT、DELET、UPDATA)有以下三條規(guī)則：①如果一個視圖是從多個基本表使用聯(lián)接操作導(dǎo)出的，那么不允許對這個視圖執(zhí)行更新操作。②如果在導(dǎo)出視圖的過程中，使用了分組和聚合操作，也不允許對這個視圖執(zhí)行更新操作。③如果視圖是從單個基本表使用選擇、投影操作導(dǎo)出的，并且包含了基本表的主鍵或某個候選鍵，那么這樣的視圖稱為“行列子集視圖”，并且可以被執(zhí)行更新操作。在SQL2中,允許更新的視圖在定義時,必須加上“WITHCHECKOPTION”短語。例：如果定義“計算機(jī)應(yīng)用”學(xué)生視圖：

CREATEVIEWSTUDENT_COMPUTER(SNO,SNAME,SEX,AGE)ASSELECTSNO,SNAME,SEX,AGEFROMSWHERESDEPT=‘計算機(jī)應(yīng)用’

該視圖是從單個關(guān)系僅使用了選擇和投影導(dǎo)出的，而且包括鍵SNO，因此是可以修改的。如執(zhí)行插入操作：

INSERTINTOSTUDENT_COMPUTERVALUES(‘S99’，‘王敏’，‘男’,22);系統(tǒng)自動會把它轉(zhuǎn)換變成下列語句：

INSERTINTOSVALUES（’S99’,‘王敏’,‘男’,22,‘計算機(jī)應(yīng)用’

）；

對于學(xué)生學(xué)習(xí)情況視圖：

CREATEVIEWS_GRADE(SNO，C_NUM，AVG_GRADE) ASSELECTSNO，COUNT(CNO)，AVG（GRADE）

FROMSC

GROUPBYSNO執(zhí)行：UPDATES_GRADE SETSNO='S3' WHERESNO='S4'；

不允許。C_NUM是對SC中的學(xué)生選修門數(shù)進(jìn)行統(tǒng)計，在未更改SC表時，要在視圖S_GRADE中更改門數(shù)，是不可能的。執(zhí)行：DELETEFROMS_GRADEWHEREC_NUM>4；

也不允許的。在視圖S_GRADE中刪除選修門數(shù)在4門以上的學(xué)生元組，勢必造成SC中這些學(xué)生學(xué)習(xí)元組的刪除，這不一定是用戶的原意，因此使用分組和聚合操作的視圖，不允許用戶執(zhí)行更新操作。

嵌入式SQL

SQL語言有兩種使用方式：一種是在終端交互方式下使用，稱為交互式SQL；另一種是嵌入在高級語言的程序中使用，稱為嵌入式

SQL，而這些高級語言可以是C、PASCAL、COBOL或

PL／I等稱為宿主語言。§6嵌入式SQL的使用技術(shù)使用差別：SQL是基于關(guān)系數(shù)據(jù)模型的語言;

高級語言是基于基本數(shù)據(jù)類型（整型、字符串型、記錄、數(shù)組等）的語言。譬如，SQL語句不能直接使用指針、數(shù)組等數(shù)據(jù)結(jié)構(gòu)；

高級語言一般不能直接進(jìn)行集合的操作。為了能在宿主語言的程序中嵌入SQL語句，必須作某些規(guī)定。嵌入式SQL的實(shí)現(xiàn)，有兩種處理方式：1、擴(kuò)充宿主語言的編譯程序，使之能處理SQL語句；2、采用預(yù)處理方式。目前多數(shù)系統(tǒng)采用后一種方式。

預(yù)處理方式是:

先由預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識別出SQL語句，并處理成宿主語言的函數(shù)調(diào)用形式；然后再用宿主語言的編譯程序把源程序編譯成目標(biāo)程序。通常DBMS制造商提供一個SQL函數(shù)定義庫，供編譯時使用。源程序的預(yù)處理和編譯的具體過程如下圖所示：目標(biāo)程序宿主語言十嵌入式SQL

預(yù)處理程序

宿主語言十函數(shù)調(diào)用

宿主語言編譯程序一、嵌入式SQL使用時必須解決的問題①為區(qū)分SQL語句與宿主語言語句，在所有的SQL語句前必須加上前綴標(biāo)識“EXECSQL”，并以“ENDEXEC”作為語句結(jié)束標(biāo)志。格式：EXECSQL

<SQL語句>END＿EXEC結(jié)束標(biāo)志在不同的宿主語言中是不同的，在C和PASCAL語言程序中規(guī)定結(jié)束標(biāo)志不用END_EXEC，而使用分號“；”。②數(shù)據(jù)庫工作單元和主程序工作單元之間的通訊允許嵌入的SQL語句引用宿主語言的程序變量（稱為共享變量）。并規(guī)定:

在引用這些變量時必須在這些變量前加冒號“：”作為前綴標(biāo)識，以示與數(shù)據(jù)庫中變量有區(qū)別;

這些變量由宿主語言的程序定義;

由SQL的BEGINDECLARESECTION與ENDDECLARESECTION語句之間說明。而主語言不能引用數(shù)據(jù)庫中的字段變量。SQL2規(guī)定，SQLSTATE是一個特殊的共享變量，

起著解釋SQL語句執(zhí)行狀況的作用，是一個由5個字符組成的字符數(shù)組。SQLSTATE=0:表示SQL語句執(zhí)行成功；SQLSTATE<>0:表示執(zhí)行SQL語句時發(fā)生的各種特殊情況。譬如“02000”用來表示未找到元組。在執(zhí)行一個SQL語句后，程序可以根據(jù)SQLSTATE的值轉(zhuǎn)向不同的分支，以控制程序的流向。③引入游標(biāo)機(jī)制：

將集合操作轉(zhuǎn)換為單元組處理。

SQL的執(zhí)行是面向集合的，一條SQL語句原則上可以產(chǎn)生或處理多條記錄。宿主語言是面向記錄的，一組主變量一次只能存放一條記錄。為此引入游標(biāo)來協(xié)調(diào)SQL語言與主語言的不同數(shù)據(jù)處理方式。二、與游標(biāo)有關(guān)的語句：①定義游標(biāo)語句（DECLARE）游標(biāo)是與某一查詢結(jié)果相聯(lián)系的符號名。

EXECSQLDECLARE

<游標(biāo)名>

CURSORFOR

<SELECT語句>[FORUPDATE[OF<字段名1><，…,字段名n>]]；

END_EXEC

這是一個說明語句,定義中的SELECT語句并不立即執(zhí)行。EXECSQLDECLARE

scx

CURSORFOR

SELECTSNO,CNO,GRADEFROMSCWHERESNO=（SELECTSNOFROMSWHERESNAME=:givensname)

FORUPDATEOFGRADE;②打開游標(biāo)語句（OPEN）

打開游標(biāo)語句使游標(biāo)處于活動狀態(tài)。與游標(biāo)相應(yīng)的查詢語句被執(zhí)行。游標(biāo)指向查詢結(jié)果的第一個記錄之前。句法如下：

EXECSQLOPEN<游標(biāo)名>END_EXEC

③游標(biāo)推進(jìn)語句（FETCH）

此時游標(biāo)推進(jìn)一個記錄，并把游標(biāo)指向的記錄（稱為當(dāng)前行）中的值取出，送到INTO子句后相應(yīng)的主變量中。句法如下：

EXECSQLFETCHFROM

<游標(biāo)名>

INTO<變量表>

END_EXECFETCH語句常用于宿主語言程序的循環(huán)結(jié)構(gòu)中，并借助宿主語言的處理語句逐一處理查詢結(jié)果中的一個個元組。在游標(biāo)處于活動狀態(tài)時,可以修改和刪除游標(biāo)指向的元組。④

關(guān)閉游標(biāo)語句（CLOSE）關(guān)閉游標(biāo)，使它不再和查詢結(jié)果相聯(lián)系。關(guān)閉了的游標(biāo)，可以再次打開，與新的查詢結(jié)果相聯(lián)系。句法如下：

EXECSQLCLOSE<游標(biāo)名>END_EXEC三、嵌入式SQL的使用技術(shù)⑴在嵌入式SQL中，SQL的數(shù)據(jù)定義DDL與控制語句DCL都不需要使用游標(biāo)。它們是嵌入式SQL中最簡單的一類語句，不需要返回結(jié)果數(shù)據(jù)，也不需要使用主變量。在主語言中嵌入SQL說明性語句（DECLARE）及控制語句（GRANT），只要給語句加上前綴EXECSQL和語句結(jié)束符END_EXEC即可。在C語言中，用分號；代替END_EXEC例：在C語言中說明共享變量：EXECSQLBEGINDECLARESECTIONintgrade，rise;chargivencno[5],cname[13],tname[9]；

chargivensno[5],sname[9],sdept[11]；

charSQLSTATE[6];EXECSQLENDDECLARESECTION;

⑵不涉及游標(biāo)的嵌入式SQLDML語句

a.對于INSERT、DELETE和UPDATE語句，只要加上前綴標(biāo)識“EXECSQL”和結(jié)束標(biāo)志“END_EXEC”，就能嵌入在宿主語言程序中使用。例：①在關(guān)系C中插入一門新的課程，各屬性值已在相應(yīng)的共享變量中：

EXECSQLINSERTERINTOC（CNO,CNAME,TNAME）

VALUED(:givencno,:cname,:tname);②從關(guān)系SC中刪除一個學(xué)生的所有選課，該學(xué)生的姓名由共享變量sname提供。

EXECSQLDELETEFROMSCWHERESNO=(SELECTSNOFROMSWHERESNAME=:sname);③把“數(shù)據(jù)庫”課程的全部成績增加某個值（該值由共享變量raise提供）。

EXECSQLUPDATESCSETGRADE=GRADE+:riseWHERECNOIN(SELECTCNOFROMCWHERECNAME=‘?dāng)?shù)據(jù)庫’);b.對于SELECT語句，如果已知查詢結(jié)果肯定是單元組時，可直接嵌入在主程序中使用，此時在SELECI語句中增加一個INTO子句，指出找到的值應(yīng)送到相應(yīng)的共享變量中去。例：在關(guān)系S中根據(jù)共享變量givensno的值檢索學(xué)生的姓名和所在系。

例：在關(guān)系S中根據(jù)共享變量givensno的值檢索該學(xué)生的姓名和所在系：

EXECSQLSELECTSNAME,SDEPTINTO:sname,:sdeptFROMSWHERESNO=:givensno;此處sname,sdept,givensno都是共享變量，已在主程序中定義，并用SQL的DECLARE語句加以說明，在引用是加上“：”作為前綴標(biāo)識，以示與數(shù)據(jù)庫中變量區(qū)別。

⑶涉及游標(biāo)的嵌入式SQLDML語句

a、當(dāng)SELECT語句查詢結(jié)果是多個元組時，此時要用游標(biāo)機(jī)制把多個元組一次一個地傳送給宿主語言程序處理。例:在關(guān)系SC表中檢索某學(xué)生(學(xué)生名由共享變量givensname給出)選課信息（SNO,CNO,GRADE）,

該查詢的C語言程序段：

EXECSQLBEGINDECLARESECTION;Intgrade，rise;Charsno[5],cno[5],givensname[9]，SQLSTATE[6];EXECSQLENDDECLARESECTION;EXECSQLDECLAREscxCURSORFORSELECTSNO,CNO,GRADEFROMSCWHERESNO=（SELECTSNOFROMSWHERESNAME=:givensname)

FORUPDATEOFGRADE；EXECSQLOPENscx;

While(1){EXECSQLFETCHFROMscxINTO:sno,:cno,:grade;If(SQLCA.SQLSTATE=='02000’)

/﹡已取完查詢結(jié)果中的所有元