关系数据库语言SQL课件

關係資料庫語言SQL

3.1SQL的體系結構

3.1.1SQL的產生與發展

3.1.2SQL資料庫的體系結構

3.1.3

SQL的組成

返回SQL的產生與發展

1970年，美國IBM研究中心的E.F.Codd連續發表多篇論文，提出關係模型。1972年，IBM公司開始研製實驗型關係資料庫管理系統SYSTEMR，配製的查詢語言稱為SQUARE(SpecifyingQueriesAsRelationalExpression)語言，在語言中使用了較多的數學符號。1974年，Boyce和Chamberlin把SQUARE修改為SEQUEL(StructuredEnglishQUEryLanguage)語言。後來SEQUEL簡稱為SQL(StructuredQueryLanguage)，即“結構式查詢語言”，SQL的發音仍為“sequel”。現在SQL已經成為一個標準。SQL資料庫的體系結構

用戶1用戶2用戶3用戶4視圖1視圖1基本表1基本表2基本表3基本表4存儲檔1存儲檔2存儲檔3存儲檔4圖3.1SQL資料庫的體系結構SQL用戶ViewBasetableStoredfileSQL的組成

核心SQL主要有四個部分: (1)數據定義語言，即SQLDDL，用於定義SQL模式、基本表、視圖、索引等結構。

(2)數據操縱語言，即SQLDML。數據操縱分成數據查詢和數據更新兩類。其中數據更新又分成插入、刪除和修改三種操作。

(3)嵌入式SQL語言的使用規定。這一部分內容涉及到SQL語句嵌入在宿主語言程式中的規則。

(4)數據控制語言，即SQLDCL，這一部分包括對基本表和視圖的授權、完整性規則的描述、事務控制等內容。返回3.2SQL的數據定義

3.2.1SQL模式的創建和撤銷

3.2.2基本數據類型

3.2.3基本表的創建、修改和撤銷

3.2.4索引的創建和撤銷

返回SQL模式的創建和撤銷

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

CREATESCHEMA〈模式名〉AUTHORIZATION〈用戶名〉

DROP語句的句法如下：

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

CASCADE(級聯式)方式。

RESTRICT(約束式)方式。

基本數據類型

SQL提供的主要數據類型(也稱為“域類型”)有：（1）數值型（2）字串型

(3)位串型

(4)時間型返回基本表的創建、修改和撤銷

(1)

基本表的創建

例3.1

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

CREATETABLES (S# CHAR(4)NOTNULL，

SNAMECHAR(8)NOTNULL，

AGE CHAR(1)，

SEX CHAR(1), PRIMARYKEY(S#))；基本表的創建、修改和撤銷(2)基本表結構的修改增加新的列用“ALTER…ADD…”語句，其句法如下：

ALTERTABLE<基本表名>ADD<列名><類型>

刪除原有的列用“ALTER…DROP…”語句，句法如下：

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

基本表的創建、修改和撤銷(3)

例3.2在基本表S中增加一個地址（ADDRESS）列，可用下列語句：

ALTERTABLESADDADDRESSVARCHAR（30）；

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

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

ALTERTABLESMODIFYS#CHAR（6）；

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

DROPTABLE<基本表名>［CASCADE│RESTRICT］此處的CASCADE、RESTRICT的語義同前面句法中的語義一樣。

返回索引的創建和撤銷

索引的創建 創建索引可用“CREATEINDEX”語句實現。其句法如下：

CREATE［UNIQUE］INDEX<索引名>ON<基本表名>（列名表）

索引的撤銷 當索引不需要時，可以用“DROPINDEX”語句撤銷，其句法如下：

DROPINDEX<索引名>返回3.3SQL的數據查詢

3.3.1

SELECT查詢語句

3.3.2

SELECT語句完整的句法

3.3.3

SELECT語句中的限定

3.3.4

基本表的聯接操作

3.3.5

SQL3中的遞歸查詢

返回SELECT查詢語句

(1)SELECT—FROM—WHERE句型 在關係代數中最常用的式子是下列運算式：πA1,…,An(σF(R1×…×Rm))

這裏R1、…、Rm為關係，F是公式，A1、…、An為屬性。 針對上述運算式，SQL為此設計了SELECT—FROM—WHERE句型：

SELECTA1，…，An FROMR1，…，Rm

WHEREF

這個句型是從關係代數運算式演變來的，但WHERE子句中的條件運算式F要比關係代數中公式更靈活。SELECT查詢語句

(2)SELECT句型使用實例 例3.8教學數據中有三個基本表（關係）：

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)

聚合函數

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

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

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

FROM基本表名和（或）視圖序列

[WHERE行條件運算式][GROUPBY列名序列

[HAVING組條件運算式]][ORDERBY列名[ASC|DESC]，…]

SELECT語句完整的句法(2)整個語句的執行過程如下：（1）讀取FROM子句中基本表、視圖的數據，執行笛卡爾積操作。（2）選取滿足WHERE子句中給出的條件運算式的元組。（3）按GROUP子句中指定列的值分組，同時提取滿足HAVING子句中組條件運算式的那些組。（4）按SELECT子句中給出的列名或列表達式求值輸出。（5）ORDER子句對輸出的目標表進行排序，按附加說明ASC昇冪排列，或按DESC降序排列。SELECT語句完整的句法

(3)例3.11對教學資料庫的基本表S、SC、C中數據進行查詢和計算。①統計每一年齡選修課程的學生人數SELECTAGE，COUNT（DISTINCTS.S#）FROMS，SCWHERES.S#=SC.S#GROUPBYAGE；由於要統計每一個年齡的學生人數，因此要把滿足WHERE子句中條件的查詢結果按年齡分組，在每一組中的學生年齡相同。此時的SELECT子句應對每一組分開進行操作，在每一組中，年齡只有一個值，統計的人數是這一組中的學生人數。SELECT語句完整的句法

(4)②求基本表S中男同學的每一年齡組（超過50人）有多少人？要求查詢結果按人數昇冪排列，人數相同按年齡降序排列。

SELECTAGE，COUNT（S#）

FROMS WHERESEX='M' GROUPBYAGE HAVINGCOUNT（*）>50 ORDERBY2，AGEDESC；返回SELECT語句中的限定

(1)

SELECT子句中的規定

SELECT子句描述查詢輸出的表格結構，即輸出值的列名或運算式。其形式如下：

SELECT[ALL|DISTINCT]<列名或列表達式序列>|*

條件運算式中的算術比較操作條件運算式中可出現算術比較運算符（＜，＜＝，＞，＞＝，＝，!＝），也可以用“BETWEEN…AND…”比較運算符限定一個值的範圍。列和基本表的改名操作SELECT語句中的限定

(2)字串的匹配操作 條件運算式中字串匹配操作符是“LIKE”。在運算式中可使用兩個通配符： 百分號（%）：與零個或多個字元組成的字串匹配。 下劃線（_）：與單個字元匹配。集合的並、交、差操作當兩個子查詢結果的結構完全一致時，可以讓這兩個子查詢執行並、交、差操作。並、交、差的運算符為UNION、INTERSECT和EXCEPT。

空值的比較操作

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

SELECT語句中的限定

(3)集合的比較操作

SQL提供SELECT語句的嵌套子查詢機制。子查詢是嵌套在另一個查詢中的SELECT語句。

（1）集合成員資格的比較（2）集合成員的算術比較（3）空關係的測試（4）重複元組的測試導出表的使用

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

返回基本表的聯接操作

基本表的聯接操作

(例)SQL3中的遞歸查詢

例3.23

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

3.4.1數據插入

3.4.2

數據刪除

3.4.3數據修改

返回數據插入

（1）單元組的插入

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

INSERTINTO基本表名[（列名表）]

VALUES（元組值），（元組值），……，（元組值）

(3)查詢結果的插入

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

INSERTINTO 基本表名1[（列名表）]TABLE基本表名2

這個語句可把基本表2的值插入到基本表1中。數據刪除

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

DELETEFROM基本表名

[WHERE條件運算式]

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

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

UPDATE基本表名

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

[WHERE條件運算式]

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

3.5.1視圖的創建和撤銷

3.5.2

對視圖的更新操作

3.5.3視圖的優點

返回視圖的創建和撤銷

(1)視圖的創建創建視圖可用“CREATEVIEW”語句實現。其句法如下：

CREATEVIEW<視圖名>（列表名）

AS<SELECT查詢語句>

例3.27對於教學資料庫中基本表S、SC、C，用戶經常要用到S#、SNAME、CNAME和GRADE等列的數據，那麼可用下列語句建立視圖：

CREATEVIEWSTUDENT_GRADE(S#，SNAME，CNAME，GRADE)

ASSELECTS.S#，SNAME，CNAME，GRADE

FROMS，SC，C

WHERES.S#=SC.S#ANDSC.C#=C.C#；視圖的創建和撤銷

(2)視圖的撤銷在視圖不需要時，可以用“DROPVIEW”語句把其從系統中撤銷。其句法如下：

DROPVIEW視圖名例3.28撤銷STUDENT_GRADE視圖，可用下列語句實現：

DROPVIEWSTUDENT_GRADE；

對視圖的更新操作

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

CREATEVIEWSUDENT_GRADE ASSELECTS.S#,SNAME，CNAME，GRADE FROMS，SC，C WHERES.S#=SC.S#ANDSC.C#=C.C#；這個視圖定義了學生選修的課程名和成績，是由三個基本表聯接而成的。 如果用戶要在視圖中執行插入操作: INSERTINTOSTUDENT_GRADE VALUES('S24','WANG','MATHS',80)；若在基本表C中，課程名為MATHS的課程號有多個，則往基本表SC插入元組時，課程號C#就不定了。因此，對這個視圖的更新是不允許的。

對視圖的更新操作(3)②設有一個視圖定義，包含每個學生選修課程（成績為非空）的門數和平均成績：

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#，因此是可更新的。譬如，執行插入操作：

INSERTINTOS_MALEVALUES（'S28','WU',18）；系統自動會把它轉變成下列語句:

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

返回視圖的優點

視圖是用戶一級的數據觀點，由於有了視圖，使資料庫系統具有下列優點：（1）視圖提供了邏輯數據獨立性。

（2）簡化了用戶觀點。資料庫的全部結構是複雜的,並有多種聯繫。

（3）數據的安全保護功能。

返回3.6嵌入式SQL

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

3.6.1SQL的運行環境

3.6.2嵌入式SQL的使用規定

3.6.3

嵌入式SQL的使用技術

3.6.4

動態SQL語句

SQL的運行環境

宿主語言十嵌入式SQL

預處理程式

宿主語言十函數調用

SQL函數定義庫

宿主語言編譯程序

目標程式

圖3.14根源程式處理過程嵌入式SQL的使用規定

在程式中要區分SQL語句與宿主語言語句允許嵌入的SQL語句引用宿主語言的程式變數（稱為共用變數），但有兩條規定：

(1)引用時，這些變數前必須加冒號“：”作為首碼標識，以示與資料庫中變數有區別。

(2)這些變數由宿主語言的程式定義，並用SQL的DECLARE語句說明。

SQL的集合處理方式與宿主語言單記錄處理方式之間的協調。

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

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

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

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

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

（1）SELECT語句的使用方式

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

動態SQL語句

(1)動態SQL預備語句

EXECSQLPREPARE〈動態SQL語句名〉FROM〈共用變數或字串〉這裏共用變數或字串的值應是一個完整的SQL語句。這個語句可以在程式運行時由用戶輸入才組合起來。此時，這個語句並不執行。動態SQL語句

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

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

EXECSQLEXECUTE<動態SQL語句名>USING<共用變數>

動態SQL語句

(3)例3.33下麵兩個C語言的程式段說明了動態SQL語句的使用技術。①EXECSQLBEGINDECLARESECTION；char*query；

EXECSQLENDDECLARESECTION；

scanf（〞%s〞，query）； /*從鍵盤輸入一個SQL語句*/

EXECSQLPREPAREqueFROM:query；

EXECSQLEXECUTEque；這個程式段表示從鍵盤輸入一個SQL語句到字元數組中；字元指針query指向字串的第1個字元。如果執行語句只做一次，那麼程式段最後兩個語句可合併成一個語句：

EXECSQLEXECUTEIMMEDIATE:query；動態SQL語句

(4)②char*query=〞UPDATEsc SETgrade=grade*1.1 WHEREc#=？〞；

EXECSQLPREPAREdynprogFROM:query；

charcno[5]=〞C4〞；

EXECSQLEXECUTEdynprogUSING:cno；這裏第一個char語句表示用戶組合成一個SQL語句，但有一個值（課程號）還不能確定，因此用“？”表示。第二個語句是動態SQL預備語句。第三個語句（char語句）表示取到了課程號值。第四個語句是動態SQL執行語句，“？”值到共用變數cno中取。

小結

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

本章的重點篇幅

（1）