关系数据库标准语言SQL课件

關係資料庫標準語言SQL

關係資料庫標準語言SQLSQL概述SQL的數據定義SQL的數據查詢SQL的數據更新視圖SQL的數據控制嵌入式SQL4.1SQL概述StructuredQueryLanguage結構化查詢語言介於關係代數與關係演算之間，功能包括：

數據查詢、數據操縱、數據定義、數據控制

已成為關係資料庫領域中一個主流語言發展過程

1974年由Boyce和Chamberlin提出

1975-1979年在IBM研製的System-R上實現

SQL-86——

第一個SQL標準

SQL-89——

第二次公佈SQL標準

SQL-92——

第三次公佈SQL標準

SQL-99——

第四次公佈SQL標準（SQL3）SQL的特點

一種一體化的語言SQL具有集數據查詢、數據操縱、數據定義、數據控制為一體的特點。一種高度非過程化的語言面向集合的操作方式同一種語法結構提供兩種使用方法聯機交互使用方式——自含式語言嵌入主語言的使用方式——嵌入式語言語言簡潔、易學易用SQL的命令SQL功能命令動詞數據定義CREATE，DROP，ALTER數據操縱SELECTINSERT，UPDATE，DELETE數據控制GRANT，REVOKESQL語言的基本概念

基本表（basetable)

——本身獨立存在的表。在SQL中一個關係對應一個表。每個基本表邏輯上對應一個存儲檔，可以帶若干索引。視圖（View）

——從一個基本表或幾個基本表或其他視圖導出的表。視圖本身不獨立存儲在資料庫中，即資料庫中只存放視圖的定義，不存放它對應的數據。注意：在用戶眼中，基本表和視圖一樣都是關係，都可以用SQL語言進行查詢。是一個虛表SQL對數據庫三級模式結構的支持SQL視圖1存儲檔1存儲模式外模式模式存儲檔2存儲檔3存儲檔4基本表1基本表2基本表3基本表4視圖1用戶4.2SQL的數據定義

SQL的數據定義功能包括：

定義基本表、定義視圖、定義索引（還有：定義資料庫、定義規則、定義存儲過程）SQL的數據定義語句一、基本表的定義一般格式CREATETABLE表名（列定義[，列定義]...[其他參數]）；

其中，列定義

格式為：列名類型[列級完整性約束]

其他參數

定義表級完整性約束條件列級完整性約束如：不允許空值語句含義執行CREATE語句後，在資料庫中建立一個表框架（空表），表的描述存於數據字典中。例：CREATETABLE

訂購單（職工號CHAR(2)，

供應商號CHAR(2)，

訂購單號CHAR(4)NOTNULL，訂購日期DATE）；

二、修改基本表的結構一般格式ALTERTABLE表名[ADD(列定義[，列定義]...)

[MODIFY(列名類型[，列名類型]...)]

[DROP

完整性約束名]；增加新列修改原有的列定義刪除指定的約束注意：無論基本表中原來是否有數據，新加的列一律為空值；不能修改屬性列的名稱；

沒有提供刪除屬性列的語句。例：ALTERTABLE

訂購單ADD（完成日期DATE）；

ALTERTABLE訂購單

MODIFY(職工號CHAR(4))；

三、刪除基本表一般格式

DROPTABLE表名；語句含義執行DROP語句後，將基本表的定義(表框架)連同它的所有元組、索引以及由它導出的所有視圖全部刪除，並釋放相應的存儲空間。例DROPTABLE訂購單；四、索引的定義和維護索引的作用加快數據查詢速度保證數據的唯一性加快連接速度建立索引的原則由DBA(DBO)和DBMS完成索引的建立、刪除和維護大表應該建立索引一個基本表的索引不要太多根據查詢要求建立索引索引的建立和刪除建立索引

CREATE[UNIQUE][CLUSTER]INDEX索引名

ON基本表名（列名[次序][，列名[次序]]...）；次序:索引值的排列次序，ASC|

DESC，缺省值為ASC。刪除索引

DROPINDEX索引名；

索引的建立和刪除例

CREATEUNIQUEINDEXpidxON訂購單（訂購單號）；

CREATEINDEXsupp_idxON訂購單（供應商號）；

DROPINDEXsupp_idx；4.3SQL的數據查詢

SQL數據查詢語句的基本格式：

SELECT目標列——要查詢的內容

FROM

基本表或視圖——涉及的關係名

[WHERE

條件運算式]——查詢條件

[GROUPBY列名1[HAVING條件運算式]]——分組

[ORDERBY

列名2[ASC|DESC]]——排序

[COMPUTE

短語]；——帶明細的分組匯總

兩個基本表倉庫職工SQL數據查詢——簡單查詢(1)檢索工資多於1230元的職工號。

SELECT

職工號

FROM

職工

WHERE

工資>1230；

SELECT*FROM職工

WHERE工資>1230；SQL數據查詢——簡單查詢(2)檢索選修課程的學生號。

SELECTDISTINCTSno

FROMSC；檢索電腦科學系的學生姓名和出生年份。

SELECTSN,2005-SAFROMSWHERESD=‘CS’；SQL數據查詢——連接查詢找出工資多於1230元的職工號和他們所在的城市。

SELECT職工號，城市

FROM

職工，倉庫

WHERE(職工.倉庫號=倉庫.倉庫號)

AND(工資>1230)；SQL數據查詢——嵌套查詢(1)檢索選修DB課程的學生學號和名字。

SELECTSno，SNFROMSWHERESnoIN

SELECTSno

FROMSCWHERECnoIN

SELECTCno

FROMC

WHERE

CN=‘DB’；

從內到外逐層處理C4S2,S3,S6SELECTSno，SNFROMS,C,SCWHERES.Sno=SC.Sno

ANDC.

Cno

=SC.

Cno

ANDCN=‘DB’;SQL數據查詢——嵌套查詢(2)查詢所有職工工資都多於1210元的倉庫資訊。

SELECT*FROM倉庫WHERE倉庫號NOTINSELECT

倉庫號

FROM職工

WHERE

工資<=1210；WH1SQL數據查詢——幾種特殊算符WHERE子句的條件運算式中可以使用的幾種形式：

列名BETWEEN

低值AND

高值列名NOTBETWEEN低值AND高值列名IN（值1，值2，……

）列名NOTIN（值1，值2，……

）列名LIKE

字串常量—可以用通配符‘%’和‘_’

列名NOT

LIKE字串常量

列名ISNULL

列名ISNOTNULLSQL數據查詢——相關子查詢(1)與子查詢相關的另外兩種嵌套查詢

形如：

[NOT]EXISTS（子查詢）

即：檢查在子查詢中是否存在或不存在元組。

運算式比較算符[ANY|ALL|SOME]（子查詢）

即：對ANY、SOME，在比較運算中只要子查詢中有一元組使結果為真，則結果為真；對ALL，要求子查詢中的所有元組都使結果為真，結果才為真。SQL數據查詢——相關子查詢(2)檢索選修C2課程的學生名。

SELECTSNFROMSWHERE

EXISTS

（SELECT*FROMSCWHERESno=S.SnoANDCno=‘C2’）；

按外層元組的值逐個處理SQL數據查詢——相關子查詢(3)檢索有職工工資大於WH1倉庫中任意一職工工資的倉庫號。

SELECTDISTINCT倉庫號

FROM職工

WHERE

工資>ANY

（SELECT工資

FROM

職工

WHERE

倉庫號=‘WH1’）；1210，1250SQL數據查詢——排序按職工工資值的降序列出全部職工的資訊。

SELECT*FROM職工

ORDERBY工資

DESC；SQL數據查詢——庫函數SQL為了增強檢索功能，方便計算提供若干庫函數：

COUNTSUMAVGMAXMIN給出WH1倉庫的職工人數。

SELECTCOUNT（*）

FROM

職工

WHERE

倉庫號=’WH1‘；給出WH1倉庫的職工工資總和。

SELECTSUM（工資）

FROM職工

WHERE倉庫號=‘WH1’；

SQL數據查詢——分組(1)求每個倉庫的職工的平均工資。

SELECT倉庫號，AVG(工資)FROM職工

GROUPBY倉庫號；

求有一個以上職工的倉庫的平均工資。SELECT倉庫號，COUNT(*)，AVG(工資)FROM職工GROUPBY倉庫號

HAVINGCOUNT(*)>=2；SQL數據查詢——分組(2)列出全部職工資訊並計算各倉庫的平均工資和工資小計，最後給出全體職工的平均工資和工資總和。

SELECT倉庫號，職工號，工資

FROM職工

ORDERBY倉庫號

COMPUTEAVG(工資)，SUM(工資)BY倉庫號

COMPUTEAVG(工資)，SUM(工資)；帶明細的分組匯總倉庫號職工號工資WH1E31210WH1E7125012302460WH2E11220WH2E4125012352470WH3E61230123012301231.56160SQL數據查詢——自連接查詢查詢每門課的間接先修課。

在同一個關係C中進行連接，為加以區分引入別名。

SELECTFIRST.Cno，SECOND.PCno

FROMCFIRST，CSECONDWHEREFIRST.Pcno=SECOND.Cno；FIRSTSECONDSQL數據查詢——外部連接查詢內連接：結果集中只保留符合連接條件的元組（一般、等值、自然）外部連接：查詢結果集中保留非匹配元組左外部連接（*=）：結果集中保留連接運算式左表中非匹配元組右外部連接（=*）：結果集中保留連接運算式右表中非匹配元組例：外部連接查詢工號姓名性別年齡部門1010李勇男20111011劉晨女191012王明女22121014張立男2113部門號部門名稱電話11生產科56612計畫科57813一車間46714科研所職工表部門表內連接SELECT職工.*，部門名稱，電話FROM

職工，部門WHERE職工.部門=部門.部門號；工號姓名性別年齡部門部門名稱電話1010李勇男2011生產科5661012王明女2212計畫科5781014張立男2113一車間467左外連接SELECT職工.*，部門名稱，電話FROM職工，部門WHERE職工.部門*=部門.部門號；工號姓名性別年齡部門部門名稱電話1010李勇男2011生產科5661011劉晨女191012王明女2212計畫科5781014張立男2113一車間467右外連接SELECT職工.*，部門名稱，電話FROM職工，部門WHERE職工.部門=*部門.部門號；工號姓名性別年齡部門部門名稱電話1010李勇男2011生產科5661012王明女2212計畫科5781014張立男2113一車間467科研所SQL數據查詢—組合查詢（並）求選修了C1或C2課程的學生學號SELECTSNOFROMSCOWHERECNO=‘C1’UNIONSELECTSNOFROMSCOWHERECNO=‘C2’；SQL數據查詢—組合查詢（交）求選修了C1和C2課程的學生學號SELECTSNOFROMSCOWHERECNO=‘C1’INTERSECTSELECTSNOFROMSCOWHERECNO=‘C2’；SQL數據查詢—組合查詢（差）求選修了C1但未選C2課程的學生學號SELECTSNOFROMSCOWHERECNO=‘C1’MINUSSELECTSNOFROMSCOWHERECNO=‘C2’；求選修了C2課程的學生姓名SELECTSNFROMSWHEREEXISTS(SELECT*FROMSCWHERES.Sno=SnoANDCno=‘C2’);SELECTSNFROMS,SCWHERES.Sno=SC.SnoANDCno=‘C2’;求沒有選修C2課程的學生姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMSCWHERES.Sno=SnoANDCno=‘C2’);SELECTSNFROMS,SCWHERES.Sno=SC.SnoANDCno<>‘C2’;×查詢選修了全部課程的學生姓名SELECTSNFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS

(SELECT*

FROMSCWHERES.Sno=SnoANDC.Cno=Cno));求至少選修了‘S2’所選修的全部課程的學生學號和姓名SELECTSno,SNFROMSWHERENOTEXISTS(SELECT*FROMSC,SC1WHERESC1.Sno=‘S2’ANDNOTEXISTS

(SELECT*

FROMSC,SC2

WHERES.Sno=SC2.SnoANDSC2.Cno=SC1.Cno));別名4.4SQL的數據更新SQL的數據更新功能包括：

插入數據、修改數據、刪除數據

不同於數據定義，實現對數據庫中數據的操作。SQL的三類數據更新語句：插入語句——

INSERT

有兩種形式修改語句——

UPDATE

刪除語句——

DELETE一、插入數據插入單個元組

INSERTINTO表名[(列名1[，列名2]…)]VALUES(常量1[，常量2]…

)；例：插入一個學生記錄

INSERTINTOS

VALUES(‘S7’,‘程成‘,‘CS’,19)；例：插入一個選課記錄

INSERTINTOSC(Sno，Cno)

VALUES(‘S7’，’C2’)；沒有列出的G取空值插入數據——

批量插入

插入子查詢結果——

批量插入

INSERTINTO表名[(列名1[，列名2]…)]

子查詢；例：將每個系學生的平均年齡存入資料庫。

CREATETABLEDeptAge(SdeptCHAR(15)，

AvgageDEC(4,1))；

INSERTINTO

DeptAge(Sdept，Avgage)SELECTSD，AVG(SA)FROMSGROUPBYSD；按系分組二、修改數據修改操作又稱更新操作，其語句的一般格式為：

UPDATE

表名

SET

列名1=運算式1[，列名2=運算式2]…[WHERE條件運算式]；語句含義對指定表中滿足條件的元組，按SET子句中的運算式的值修改相應的列值。若無WHERE子句，則修改全部元組。允許帶子查詢修改數據（1）給WH1倉庫的職工提高10%的工資。

UPDATE

職工

SET

工資=工資*1.10

WHERE倉庫號=‘WH1’；將北京職工的工資提高10%。

UPDATE職工

SET工資=工資*1.10

WHERE倉庫號INSELECT倉庫號

FROM倉庫

WHERE城市=‘北京’；修改數據（2）修改學號，將‘97036’號學生改為‘98070’號。

修改學生關係表S：

UPDATESSET

Sno=‘98070’WHERESno=‘97036’；

修改學生選課關係表SC：

UPDATESCSETSno=‘98070’WHERESno=‘97036’；注意參照完整性，保持修改操作與資料庫的一致性。三、刪除數據刪除語句的一般格式

DELETEFROM

表名

[WHERE條件運算式]；語句含義從指定表中刪除滿足WHERE子句條件的元組。若無WHERE子句，則刪除表中的全部元組。

注意：此語句刪除的是表中的數據，不是表的定義。允許帶子查詢刪除數據刪除WH1倉庫的職工記錄。

DELETEFROM職工

WHERE倉庫號=‘WH1’；刪除人工智慧（AI）課的學生選課記錄。

DELETEFROMSCWHERECnoINSELECTCno

FROMCWHERECN=‘AI’；保持數據的一致性！4.5視圖視圖（View）

——

從一個或幾個基本表(或視圖)導出的表，是虛表。

即：資料庫中只存放視圖的定義，不存放視圖對應的數據，它的數據仍然存放在原來的基本表中。

視圖象一個窗口，透過它可以看到自己感興趣的數據及其變化。

視圖一經定義，可以和基本表一樣被查詢、被刪除，也可用來定義新的視圖，但對視圖的更新操作有限制。

用戶的外模式由若干基本表和若干視圖組成。一、視圖的定義建立視圖

格式：CREATEVIEW

視圖名[（列名[，列名]…）]

AS

子查詢

[WITHCHECKOPTION]；

若省略，則用子查詢中SELECT子句的各目標列作為視圖的列名。注意：此時目標列只能是單純的屬性名。表示對視圖進行更新時要保證更新的元組滿足視圖定義中的條件（子查詢中的條件）。建立視圖（1）建立電腦科學系學生的視圖。

CREATEVIEWCS-SASSELECTSno，SN，SAFROMSWHERESD=‘CS’WITHCHECKOPTION；要求插入、修改時要滿足SD=‘CS’的條件CS-S建立視圖（2）建立職工與工作所在城市關係的視圖。

CREATEVIEWemp_cityASSELECT職工號，工資，城市

FROM職工，倉庫

WHERE職工.倉庫號

=倉庫.倉庫號；emp_city建立視圖（3）建立職工年、月工資的視圖。

CREATEVIEWE_sal(職工號，月工資，年工資

)ASSELECT

職工號，工資，工資*12FROM

職工；

E_sal視圖列名不可省略刪除視圖刪除視圖

格式：DROPVIEW

視圖名；語句含義

將視圖的定義從數據字典中刪除。由該視圖導出的視圖，儘管其定義還在數據字典中，但已無法使用，應同時刪除。

注意：如果導出視圖的基本表被刪除，該視圖將失效，需要用DROPVIEW語句刪除。二、視圖的查詢視圖定義後，用戶可以象基本表一樣對視圖查詢。例：在電腦科學系學生視圖中查小於20歲的學生姓名。

SELECTSNFROMCS_SWHERESA<20；視圖查詢的處理DBMS對視圖查詢的處理

——

將查詢操作和視圖定義中的子查詢結合起來，形成一個修正的對基本表的查詢語句，然後執行之。SELECTSNFROMSWHERESD=‘CS’ANDSA<20；注意：這種轉換有時不能直接進行三、視圖的更新視圖是虛表，對它的更新最終要轉換為對基本表的更新。為防止非法操作，視圖定義時加WITH子句，DBMS將在視圖更新時檢查視圖定義中的條件，不滿足則拒絕執行。並非所有視圖都可更新。

視圖的更新例：向電腦科學系學生視圖CS_S插入一個新記錄。INSERTINTOCS_SVALUES(‘S9’,‘楊力’,18)；INSERTINTOSVALUES(‘S9’,‘楊力’,‘CS’,18)；四、視圖的用途（優點）簡化用戶的操作

——

視圖機制使用戶可以只注意他所關心的數據。能使用戶以多種角度看待同一數據

——視圖機制為不同用戶使用同一個數據庫提供靈活性。對重構資料庫提供了一定程度的邏輯獨立性

——在資料庫的邏輯結構改變後，用新建立的視圖定義用戶原來的關係，使用戶的外模式保持不變。能夠對機密數據提供安全保護

——視圖機制可以對不同的用戶定義不同的視圖，使機密數據不出現在用戶視圖中，從而自動提供了對機密數據的保護。4.6SQL的數據控制數據控制的兩個主要方面：一是控制用戶對數據的存取許可權——

安全性控制

保護資料庫防止非法使用造成的數據洩露和破壞二是控制數據的完整性——

提供完整性約束

數據的完整性是指數據庫中數據的正確性和相容性注意：數據控制也稱數據保護，除包括數據的安全性控制和完整性控制外，還包括併發控制和數據庫恢復。本節僅討論SQL的安全性控制功能

存取控制資料庫系統中保證數據安全性的主要措施：

存取控制——規定用戶對數據的存取許可權SQL語言為DBA和表主提供定義和回收這種許可權的手段授權語句的一般格式：

GRANT許可權[，許可權]…[ON對象類型對象名]TO用戶[，用戶]...

[WITHGRANTOPTION]；語義：將指定對象的指定許可權授予指定用戶。

獲得授權許可權PUBLIC代表全體用戶不同對象類型允許的操作許可權授權與收回許可權授權例：

GRANTSELECTONTABLE

倉庫

TOPUBLIC；

GRANTSELECT，INSERTONTABLE

職工

TOZhaoWITHGRANTOPTION；收回許可權語句的一般格式：

REVOKE許可權[，許可權]…[ON

對象類型對象名]FROM用戶[，用戶]…；語義：收回指定用戶在指定對象上的指定許可權。收回許可權例：

REVOKEINSERTONTABLE

職工FROMZhao；授權與收回許可權舉例GRANTUPDATE(SN),SELECTONTABLESTOWANG；GRANTALLPRIVILEGESONTABLES,C,SCTO

ZHANG；GRANTCREATETABONDATABASES_CTOLI；GRANTSELECTONTABLESCTOPUBLIC；REVOKEUPDATE(SN)ONTABLESFROMWANG；REVOKESELECTONTABLESCFROMU2；4.7嵌入式SQL把SQL嵌入到宿主語言中使用必須解決的三個問題：

(1)嵌入識別問題

宿主語言的編譯程序如何區分主語句和SQL語句。

(2)宿主語言與SQL語言的數據交互

SQL語句的結果提交宿主語言處理，宿主語言的數據交給SQL語句使用。

(3)宿主語言的單記錄與SQL的多記錄問題

宿主語言一般只能在單記錄方式下工作，一次處理一條記錄；而SQL常常處理的是記錄集合。嵌入式SQL語句的識別

識別標記

——

為區分SQL語句和主語句，對嵌入宿主語言的SQL語句常必須加首碼EXECSQL。SQL語句的結束標誌隨主語言不同而不同。

以C作為主語言的嵌入式SQL語句：

EXECSQLSQL語句；

以COBOL作為主語言的嵌入式SQL語句：

EXECSQLSQL語句

END-EXEC

PowerBuilder

SQL語句；嵌入式SQL語句的處理方法預編譯（常用）修改和擴展主語言使之能處理SQL語句直接傳遞給DBMS（PowerBuilder）嵌入式SQL語句與主語言之間的通信在嵌入SQL語句的高級語言程式中SQL語句負責操縱資料庫高級語言語句負責控制程式流程、預處理兩種計算模型之間的通信主要包括：

–

向主語言傳遞SQL執行狀態，以便據此控制程式流程。

通過SQL通信區（SQLCA）實現

–主語言向SQL語句提供參數。

通過主變數實現

–

SQL語句查詢資料庫的結果交主語言處理。

通過主變數和游標實現SQL通信區與主變數SQL通信區（SQLCommunicationArea）

定義：EXECSQLINCLUEDSQLCA；

說明：SQLCA是SQL與主語言的通信區，類似於結構變數，資料庫系統將SQL的執行狀態(成功、失敗、出錯原因等)記錄在其中(SQLCODE)，程式從該通信區中得到相關的資訊。主變數

——

SQL中使用的主語言程式變數簡稱主變數。定義：EXECSQLBEGINDECLARESECTION；

主變數說明……

EXECSQLENDDECLARESECTION；

主變數在SQL語句中使用需要在變數名前加首碼‘:’。SQL的應用(1)1.根據給定學號查尋學生資訊，學號在主變數GIVENSNO中。EXECSQLSELECTSno,Sn,SD,SAFromSInto:Hsno,:Hsn,:Hsd,:Hsa

WhereSno=:givensno;SQL的應用(2)2.根據學號和課程號查尋學生選課成績，學號在主變數GIVENSNO和GIVENCNO中。EXECSQLSELECTSn,CN,GFromS,C,SCInto:Hsn,:Hcn,:Hgrade

WhereSno=:givensnoandCno=:givencnoand&S.Sno=SC.SnoandC.Cno=SC.Cno;SQL的應用(3)3.將所有學生選修資料庫(某課程，課程名在主變數Cname中)的成績加10分。EXECSQLUpdateSCSetG=G+10WhereCnoInSelectCno

FromCWhereCN=“DB”;

CN=:Cname

SQL的應用(4)4.刪除退學學生的資訊，學生姓名已經在主變數Sname中。EXECSQLDeleteFromSCWhereSnoInselectSno

FromSWhereSN=:Sname;EXECSQLDeleteFromSWhereSN=:Sname;SQL的應用（PB）1SQLCA：缺省的事務對象CONNECTUSINGSQLCA；DISCONNECTUSINGSQLCA；單行檢索SELECT*FROMSWHERESNO=“S3”INTO:NO,:NAME,:DEPT,:AGE；插入語句INSERTINTOEmployee(emp_no,emp_name)VALUES(:NO,:NAME)；SQL的應用（PB）2修改語句UPDATESSETSD=:NEWSDWHERESNO=:SNO；刪除語句DELETEFROMEmployee

WHEREemp_no=:NO；SQL的應用（PB）3檢查SQL語句的執行情況SQLCode（事務對象）0：成功-1：失敗100：沒有返回數據IntStudent_noStudent_no=Integer(sle_Sno.text)SELECTS.SN,S.SDINTO:sle_Name.text,:sle_Dept.text FROMsWHERES.SNO=:Student_no

USINGSQLCA;IfSQLCA.SQLCode=100thenMessageBox(“查詢學生”，“未找到指定學生”)ElseifSQLCA.SQLCode<0thenMessageBox(“資料庫錯誤”,SQLCA.SQLErrText,Exclamation!)EndifIntEmp_numEmp_num=Integer(sle_Emp_Num.text)SELECTemployee.Emp_Lname,employee.Emp_LnameINTO:sle_LName.text,:sle_FName.text FROMemployeeWHEREemployee.Emp_nbr=:Emp_numUSINGEmp_tran;IfEmp_tran.SQLCode=100then

MessageBox(“查詢雇員”，“未找到指定雇員”)ElseifEmp_tran.SQLCode<0then