《數(shù)據(jù)庫系統(tǒng)教程（第3版）》第3章 關系數(shù)據(jù)庫語言SQL

第3章關系數(shù)據(jù)庫語言SQL

本章重要概念

（1）SQL數(shù)據(jù)庫的體系結(jié)構(gòu)，SQL的組成。（2）SQL的數(shù)據(jù)定義：SQL模式、基本表和索引的創(chuàng)建和撤銷。（3）SQL的數(shù)據(jù)查詢；SELECT語句的句法，SELECT語句的三種形式及各種限定，基本表的聯(lián)接操作，SQL3中的遞歸查詢。（4）SQL的數(shù)據(jù)更新：插入、刪除和修改語句。（5）視圖的創(chuàng)建和撤銷，對視圖更新操作的限制。（6）嵌入式SQL：預處理方式，使用規(guī)定，使用技術，卷游標，動態(tài)SQL語句。

本章概述SQL是關系數(shù)據(jù)庫的標準語言，對關系模型的發(fā)展和商用DBMS的研制起著重要的作用。SQL語言是介乎于關系代數(shù)和元組演算之間的一種語言。本章詳細介紹SQL的核心部分內(nèi)容：數(shù)據(jù)定義、數(shù)據(jù)查詢、數(shù)據(jù)更新和嵌入式SQL。

關系數(shù)據(jù)庫語言SQL3.1SQL的體系結(jié)構(gòu)

3.2SQL的數(shù)據(jù)定義

3.3SQL的數(shù)據(jù)查詢3.4數(shù)據(jù)更新3.5視圖的定義和對視圖的操作3.6嵌入式SQL

3.1SQL的體系結(jié)構(gòu)

3.1.1SQL的產(chǎn)生與發(fā)展

3.1.2SQL數(shù)據(jù)庫的體系結(jié)構(gòu)

3.1.3

SQL的組成

返回SQL的產(chǎn)生與發(fā)展

1970年，美國IBM研究中心的E.F.Codd連續(xù)發(fā)表多篇論文，提出關系模型。1972年，IBM公司開始研制實驗型關系數(shù)據(jù)庫管理系統(tǒng)SYSTEMR，配制的查詢語言稱為SQUARE(SpecifyingQueriesAsRelationalExpression)語言，在語言中使用了較多的數(shù)學符號。1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL(StructuredEnglishQUEryLanguage)語言。后來SEQUEL簡稱為SQL(StructuredQueryLanguage)，即“結(jié)構(gòu)式查詢語言”，SQL的發(fā)音仍為“sequel”?，F(xiàn)在SQL已經(jīng)成為一個標準。SQL數(shù)據(jù)庫的體系結(jié)構(gòu)

用戶1用戶2用戶3用戶4視圖1視圖1基本表1基本表2基本表3基本表4存儲文件1存儲文件2存儲文件3存儲文件4圖3.1SQL數(shù)據(jù)庫的體系結(jié)構(gòu)SQL用戶ViewBasetableStoredfileSQL的組成

核心SQL主要有四個部分: (1)數(shù)據(jù)定義語言，即SQLDDL，用于定義SQL模式、基本表、視圖、索引等結(jié)構(gòu)。 (2)數(shù)據(jù)操縱語言，即SQLDML。數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩類。其中數(shù)據(jù)更新又分成插入、刪除和修改三種操作。 (3)嵌入式SQL語言的使用規(guī)定。這一部分內(nèi)容涉及到SQL語句嵌入在宿主語言程序中的規(guī)則。 (4)數(shù)據(jù)控制語言，即SQLDCL，這一部分包括對基本表和視圖的授權(quán)、完整性規(guī)則的描述、事務控制等內(nèi)容。返回3.2SQL的數(shù)據(jù)定義

3.2.1SQL模式的創(chuàng)建和撤銷

3.2.2基本數(shù)據(jù)類型

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

3.2.4索引的創(chuàng)建和撤銷

返回SQL模式的創(chuàng)建和撤銷

SQL模式的創(chuàng)建可用CREATESCHEMA語句定義，其基本句法如下：

CREATESCHEMA〈模式名〉AUTHORIZATION〈用戶名〉DROP語句的句法如下：

DROPSCHEMA〈模式名〉［CASCADE│RESTRICT］其方式有兩種：

CASCADE(級聯(lián)式)方式。

RESTRICT(約束式)方式。

基本數(shù)據(jù)類型

SQL提供的主要數(shù)據(jù)類型(也稱為“域類型”)有：（1）數(shù)值型（2）字符串型（3）位串型（4）時間型返回基本表的創(chuàng)建、修改和撤銷(1)基本表的創(chuàng)建

例3.1

基本表SS（S#，SNAME，AGE，SEX）可用下列語句創(chuàng)建：

CREATETABLES (S# CHAR(4)NOTNULL， SNAMECHAR(8)NOTNULL， AGE CHAR(1)， SEX CHAR(1), PRIMARYKEY(S#))；

基本表的創(chuàng)建、修改和撤銷(2)基本表結(jié)構(gòu)的修改增加新的列用“ALTER…ADD…”語句，其句法如下：

ALTERTABLE<基本表名>

ADD<列名><類型>刪除原有的列用“ALTER…DROP…”語句，句法如下：

ALTERTABLE<基本表名>DROP<列名>［CASCADE│RESTRICT］

此處CASCADE方式表示：在基本表中刪除某列時，所有引用到該列的視圖和約束也要一起自動地被刪除。而RESTRICT方式表示在沒有視圖或約束引用該屬性時，才能在基本表中刪除該列，否則拒絕刪除操作。

基本表的創(chuàng)建、修改和撤銷(3)例3.2在基本表S中增加一個地址（ADDRESS）列，可用下列語句：

ALTERTABLESADDADDRESSVARCHAR（30）；

應注意，新增加的列不能定義為“NOTNULL”?；颈碓谠黾右涣泻?，原有元組在新增加的列上的值都被定義為空值（NULL）。例3.3在基本表S中刪除年齡（AGE）列，并且把引用該列的所有視圖和約束也一起刪除，可用下列語句：

ALTERTABLESDROPAGECASCADE；例3.4在基本表S中S#的長度修改為6，可用下列語句：

ALTERTABLESMODIFYS#CHAR（6）；

基本表的創(chuàng)建、修改和撤銷(4)基本表的撤銷撤銷語句的句法如下：

DROPTABLE<基本表名>［CASCADE│RESTRICT］

此處的CASCADE、RESTRICT的語義同前面句法中的語義一樣。

返回索引的創(chuàng)建和撤銷

索引的創(chuàng)建 創(chuàng)建索引可用“CREATEINDEX”語句實現(xiàn)。句法如下：

CREATE［UNIQUE］INDEX<索引名>ON<基本表名>（列名表）索引的撤銷 當索引不需要時，可以用“DROPINDEX”語句撤銷，其句法如下：

DROPINDEX<索引名>返回3.3SQL的數(shù)據(jù)查詢

3.3.1

SELECT查詢語句

3.3.2

SELECT語句完整的句法

3.3.3

SELECT語句中的限定

3.3.4

基本表的聯(lián)接操作

3.3.5

SQL3中的遞歸查詢

返回

SELECT查詢語句(1)SELECT—FROM—WHERE句型 在關系代數(shù)中最常用的式子是下列表達式：

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

這里R1、…、Rm為關系，F(xiàn)是公式，A1、…、An為屬性。 針對上述表達式，SQL為此設計了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm

WHEREF

這個句型是從關系代數(shù)表達式演變來的，但WHERE子句中的條件表達式F要比關系代數(shù)中公式更靈活。SELECT查詢語句(2)SELECT句型使用實例 例3.8教學數(shù)據(jù)中有三個基本表（關系）：

S（S#，SNAME，AGE，SEX） SC（S#，C#，GRADE） C（C#，CNAME，TEACHER）

下面用SELECT查詢語句表達每個查詢。 ①檢索學習課程號為C2的學生學號與成績。

②檢索學習課程號為C2的學生學號與姓名。 ③檢索選修課程名為MATHS的學生學號與姓名。④檢索選修課程號為C2或C4的學生學號。⑤檢索至少選修課程號為C2和C4的學生學號。SELECT查詢語句(3)SELECT語句的圖示形式

例3.9對于例3.8中的第二個查詢語句的圖示形式如下所述。SELECT查詢語句(4)

聚合函數(shù)

COUNT（*）計算元組的個數(shù)COUNT（列名）對一列中的值計算個數(shù)SUM（列名）求某一列值的總和（此列的值必須是數(shù)值型）AVG（列名）求某一列值的平均值（此列的值必須是數(shù)值型）MAX（列名）求某一列值的最大值MIN（列名）求某一列值的最小值返回SELECT語句完整的句法

(1)SELECT語句完整的句法如下：

SELECT目標表的列名或列表達式序列

FROM基本表名和（或）視圖序列[WHERE行條件表達式][GROUPBY列名序列[HAVING組條件表達式]][ORDERBY列名[ASC|DESC]，…]SELECT語句完整的句法(2)整個語句的執(zhí)行過程如下：（1）讀取FROM子句中基本表、視圖的數(shù)據(jù)，執(zhí)行笛卡爾積操作。（2）選取滿足WHERE子句中給出的條件表達式的元組。（3）按GROUP子句中指定列的值分組，同時提取滿足HAVING子句中組條件表達式的那些組。（4）按SELECT子句中給出的列名或列表達式求值輸出。（5）ORDER子句對輸出的目標表進行排序，按附加說明ASC升序排列，或按DESC降序排列。SELECT語句完整的句法(3)例3.11對教學數(shù)據(jù)庫的基本表S、SC、C中數(shù)據(jù)進行查詢和計算。①統(tǒng)計每一年齡選修課程的學生人數(shù)

SELECTAGE，COUNT（DISTINCTS.S#）FROMS，SCWHERES.S#=SC.S#GROUPBYAGE；

由于要統(tǒng)計每一個年齡的學生人數(shù)，因此要把滿足WHERE子句中條件的查詢結(jié)果按年齡分組，在每一組中的學生年齡相同。此時的SELECT子句應對每一組分開進行操作，在每一組中，年齡只有一個值，統(tǒng)計的人數(shù)是這一組中的學生人數(shù)。SELECT語句完整的句法(4)②求基本表S中男同學的每一年齡組（超過50人）有多少人？要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同按年齡降序排列。

SELECTAGE，COUNT（S#） FROMS WHERESEX='M' GROUPBYAGE HAVINGCOUNT（*）>50 ORDERBY2，AGEDESC；返回SELECT語句中的限定(1)

SELECT子句中的規(guī)定

SELECT子句描述查詢輸出的表格結(jié)構(gòu)，即輸出值的列名或表達式。其形式如下：

SELECT[ALL|DISTINCT]<列名或列表達式序列>|*條件表達式中的算術比較操作條件表達式中可出現(xiàn)算術比較運算符（＜，＜＝，＞，＞＝，＝，!＝），也可以用“BETWEEN…AND…”比較運算符限定一個值的范圍。列和基本表的改名操作SELECT語句中的限定(2)字符串的匹配操作 條件表達式中字符串匹配操作符是“LIKE”。在表達式中可使用兩個通配符： 百分號（%）：與零個或多個字符組成的字符串匹配。 下劃線（_）：與單個字符匹配。集合的并、交、差操作當兩個子查詢結(jié)果的結(jié)構(gòu)完全一致時，可以讓這兩個子查詢執(zhí)行并、交、差操作。并、交、差的運算符為UNION、INTERSECT和EXCEPT。空值的比較操作

SQL中允許列值為空，空值用保留字NULL表示。

SELECT語句中的限定(3)集合的比較操作

SQL提供SELECT語句的嵌套子查詢機制。子查詢是嵌套在另一個查詢中的SELECT語句。（1）集合成員資格的比較（2）集合成員的算術比較（3）空關系的測試（4）重復元組的測試導出表的使用

SQL2允許在FROM子句中使用子查詢。如果在FROM子句中使用了子查詢，那么要給子查詢的結(jié)果起個表名和相應的列名。

返回基本表的聯(lián)接操作

基本表的聯(lián)接操作(例)SQL3中的遞歸查詢

例3.23

C1（BASIC）C3（COBOL）C2（FORTRAN）C5（C）C4（PASCAL）C9（PL/I）C8（VC++）C6（Ada）C7（C++）圖3.13課程之間的先修與后繼的聯(lián)系（箭尾指向的課程是箭頭指向課程的直接先修課）返回3.4數(shù)據(jù)更新

3.4.1數(shù)據(jù)插入

3.4.2

數(shù)據(jù)刪除

3.4.3數(shù)據(jù)修改

返回數(shù)據(jù)插入

（1）單元組的插入

INSERTINTO

基本表名[（列名表）]VALUES（元組值）（2）多元組的插入

INSERTINTO

基本表名[（列名表）]

VALUES（元組值），（元組值），……，（元組值）（3）查詢結(jié)果的插入

INSERTINTO

基本表名[(列名表)]＜SELECT查詢語句＞這個語句可把一個SELECT語句的查詢結(jié)果插到某個基本表中。（4）表的插入

INSERTINTO

基本表名1[（列名表）]TABLE基本表名2這個語句可把基本表2的值插入到基本表1中。數(shù)據(jù)刪除

SQL的刪除操作是指從基本表中刪除元組，其句法如下：

DELETEFROM基本表名

[WHERE條件表達式] 其語義是從基本表中刪除滿足條件表達式的元組。刪除語句實際上是“SELECT*FROM基本表名[WHERE條件表達式]”和DELETE操作的結(jié)合，每找到一個元組，就把它刪去。應該注意，DELETE語句只能從一個基本表中刪除元組。WHERE子句中條件可以嵌套，也可以是來自幾個基本表的復合條件。數(shù)據(jù)修改

當需要修改基本表中元組的某些列值時，可以用UPDATE語句實現(xiàn)，其句法如下：

UPDATE基本表名

SET列名=值表達式[，列名=值表達式…]│ROW=（元組）

[WHERE條件表達式]其語義是：修改基本表中滿足條件表達式的那些元組中的列值，需修改的列值在SET子句中指出。返回3.5視圖的定義和對視圖的操作

3.5.1視圖的創(chuàng)建和撤銷

3.5.2

對視圖的更新操作

3.5.3視圖的優(yōu)點

返回視圖的創(chuàng)建和撤銷(1)視圖的創(chuàng)建創(chuàng)建視圖可用“CREATEVIEW”語句實現(xiàn)。其句法如下：

CREATEVIEW<視圖名>（列表名）

AS<SELECT查詢語句>例3.27對于教學數(shù)據(jù)庫中基本表S、SC、C，用戶經(jīng)常要用到S#、SNAME、CNAME和GRADE等列的數(shù)據(jù)，那么可用下列語句建立視圖：

CREATEVIEWSTUDENT_GRADE(S#，SNAME，CNAME，GRADE)

ASSELECTS.S#，SNAME，CNAME，GRADE

FROMS，SC，C

WHERES.S#=SC.S#ANDSC.C#=C.C#；視圖的創(chuàng)建和撤銷(2)視圖的撤銷在視圖不需要時，可以用“DROPVIEW”語句把其從系統(tǒng)中撤銷。其句法如下：

DROPVIEW視圖名例3.28撤銷STUDENT_GRADE視圖，可用下列語句實現(xiàn)：

DROPVIEWSTUDENT_GRADE；

對視圖的更新操作(1)定義3.1如果視圖是從單個基本表只使用選擇、投影操作導出的，并且包含了基本表的主鍵，那么這樣的視圖稱為“行列子集視圖”，并且可以被執(zhí)行更新操作。對視圖的更新操作(2)例3.29下面討論對視圖更新的幾個例子。①設有一個視圖定義

CREATEVIEWSUDENT_GRADE ASSELECTS.S#,SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；

這個視圖定義了學生選修的課程名和成績，是由三個基本表聯(lián)接而成的。 如果用戶要在視圖中執(zhí)行插入操作:

INSERTINTOSTUDENT_GRADE VALUES('S24','WANG','MATHS',80)；

若在基本表C中，課程名為MATHS的課程號有多個，則往基本表SC插入元組時，課程號C#就不定了。因此，對這個視圖的更新是不允許的。

對視圖的更新操作(3)②設有一個視圖定義，包含每個學生選修課程（成績?yōu)榉强眨┑拈T數(shù)和平均成績：

CREATEVIEWS_GRADE（S#，C_NUM，AVG_GRADE） ASSELECTS#，COUNT（C#），AVG（GRADE） FROMSC WHEREGRADEISNOTNULL

視圖S_GRADE雖然是從單個基本表導出，但導出時使用了分組和聚集操作，因此也是不能更新的。譬如，在未更改基本表SC中值時，要在視圖S_GRADE中更改學生的平均成績，顯然是不切實際的。對視圖的更新操作(4) ③如果定義了一個有關男學生的視圖：

CREATEVIEWS_MALE ASSELECTS#,SNAME,AGE FROMS WHERESEX='M'；

這個視圖是從單個關系只使用選擇和投影導出的，并且包含鍵S#，因此是可更新的。例如，執(zhí)行插入操作：

INSERTINTOS_MALE

VALUES（'S28','WU',18）；

系統(tǒng)自動會把它轉(zhuǎn)變成下列語句：

INSERTINTOS VALUES('S28','WU',18,'M')；

返回視圖的優(yōu)點

視圖是用戶一級的數(shù)據(jù)觀點，由于有了視圖，使數(shù)據(jù)庫系統(tǒng)具有下列優(yōu)點：（1）視圖提供了邏輯數(shù)據(jù)獨立性。

（2）簡化了用戶觀點。數(shù)據(jù)庫的全部結(jié)構(gòu)是復雜的,并有多種聯(lián)系。

（3）數(shù)據(jù)的安全保護功能。

返回3.6嵌入式SQL

SQL語言有兩種使用方式：一種是在終端交互方式下使用，稱為交互式SQL；另一種是嵌入在高級語言的程序中使用，稱為嵌入式SQL，而這些高級語言可以是C、ADA、PASCAL、COBOL或PL/I等，稱為宿主語言。

3.6.1SQL的運行環(huán)境

3.6.2嵌入式SQL的使用規(guī)定

3.6.3

嵌入式SQL的使用技術

3.6.4

動態(tài)SQL語句

SQL的運行環(huán)境

宿主語言十嵌入式SQL

預處理程序

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

SQL函數(shù)定義庫宿主語言編譯程序

目標程序

圖3.14源程序處理過程嵌入式SQL的使用規(guī)定

在程序中要區(qū)分SQL語句與宿主語言語句允許嵌入的SQL語句引用宿主語言的程序變量（稱為共享變量），但有兩條規(guī)定： (1)引用時，這些變量前必須加冒號“：”作為前綴標識，以示與數(shù)據(jù)庫中變量有區(qū)別。(2)這些變量由宿主語言的程序定義，并用SQL的DECLARE語句說明。SQL的集合處理方式與宿主語言單記錄處理方式之間的協(xié)調(diào)。

(1)游標定義語句（DECLARE）。

(2)游標打開語句（OPEN）。

(3)游標推進語句(FETCH)。

(4)游標關閉語句（CLOSE）。

嵌入式SQL的使用技術不涉及游標的SQLDML語句涉及游標的SQLDML語句卷游標的定義和推進涉及游標的SQLDML語句

（1）SELECT語句的使用方式

當SELECT語句查詢結(jié)果是多個元組時，此時宿主語言程序無法使用，一定要用游標機制把多個元組一次一個地傳送給宿主語言程序處理。（2）對游標指向元組的修改或刪除操作 在游標處于活動狀況時，可以修改或刪除游標指向的元組。

動態(tài)SQL語句(1)動態(tài)SQL預備語句

EXECSQLPREPARE〈動態(tài)SQL語句名〉FROM〈共享變量或字符串〉這里共享變量或字符串的值應是一個完整的SQL語句。這個語句可以在程序運行時由用戶輸入才組合起來。此時，這個語句并不執(zhí)行。動態(tài)SQL語句(2)動態(tài)SQL執(zhí)行語句

EXECSQLEXECUTE〈動態(tài)SQL語句名〉 動態(tài)SQL語句使用時，還可以有兩點改進：（1）當預備語句中組合而成的SQL語句只需執(zhí)行一次時，那么預備語句和執(zhí)行語句可合并成一個語句：

EXECSQLEXECUTEIMMEDIATE〈共享變量或字符串〉（2）當預備語句中組合而成的SQL語句的條件值尚缺時，可以在執(zhí)行語句中用USING短語補上：

EXECSQLEXECUTE<動態(tài)SQL語句名>USING<共享變量>動態(tài)SQL語句(3)例3.33下面兩個C語言的程序段說明了動態(tài)SQL語句的使用技術。①EXECSQLBEGINDECLARESECTION；char*query； EXECSQLENDDECLARESECTION； scanf（〞%s〞，query）； /*從鍵盤輸入一個SQL語句*/

EXECSQLPREPAREqueFROM：query； EXECSQLEXECUTEque；

這個程序段表示從鍵盤輸入一個SQL語句到字符數(shù)組中；字符指針query指向字符串的第1個字符。如果執(zhí)行語句只做一次，那么程序段最后兩個語句可合并成一個語句：

EXECSQLEXECUTEIMMEDIATE：query；動態(tài)SQL語句(4)②char*query=〞UPDATEsc SETgrade=grade*1.1 WHEREc#=？〞；

EXECSQLPREPAREdynprogFROM:query；

charcno[5]=〞C4〞；

EXECSQLEXECUTEdynprogUSING:cno；

這里第一個char語句表示用戶組合成一個SQL語句，但有一個值（課程號）還不能確定，因此用“？”表示。第二個語句是動態(tài)SQL預備語句。第三個語句（char語句）表示取到了課程號值。第四個語句是動態(tài)SQL執(zhí)行語句，“？”值到共享變量cno中取。

小結(jié)

SQL是關系數(shù)據(jù)庫的標準語言，已廣泛應用在商用系統(tǒng)中。SQL的數(shù)據(jù)定義部分包括對SQL模式、基本表、視圖、索引的創(chuàng)建和撤銷。SQL的數(shù)據(jù)操縱分成數(shù)據(jù)查詢和數(shù)據(jù)更新兩部分。SQL的數(shù)據(jù)查詢是用SELECT語句實現(xiàn)，兼有關系代數(shù)和元組演算的特點。SQL的數(shù)據(jù)更新包括插入、刪除和修改等三種操作，在視圖中只有行列子集視圖是可以更新的。嵌入式SQL涉及到SQL語句的宿主語言程序中的使用規(guī)定，以解決兩種語言的不一致和相互聯(lián)系的問題。同時還介紹了動態(tài)SQL語句。

本章的重點篇幅

（1）教材中P97的例3.8（SELECT語句）。（2）教材