D-3關系數據庫標準語言SQL

1第三講關系數據庫標準語言SQL3.1SQL概述3.2數據定義3.3查詢3.4數據更新3.5視圖3.6數據控制2【學習目標】

本講的內容是關系數據庫的標準語言SQL(StructuredQueryLanguage)--結構化查詢語言。本講的學習目標主要是熟練掌握SQL的語法，能夠在實踐中熟練地運用SQL實現各種查詢請求?！緦W習方法】

要求大家將課程中講到的例子在MicrosoftSQLServer中進行上機練習，看一看SQL與Transaction-SQL(簡稱T-SQL)到底有什么不同。3第三講關系數據庫標準語言SQL3．1SQL概述

SQL語言集數據查詢(dataquery)、數據操縱(datamanipulation)、數據定義(datadefinition)和數據控制(datacontrol)功能于一體，充分體現了關系數據語言的特點和優(yōu)點：1．綜合統一2．高度非過程化3．面向集合的操作方式（元組集合）4．以同一種語法結構提供兩種使用方式（自含式，嵌入式）5．語言簡潔，易學易用4SQL的基本概念SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲文件2存儲文件1外模式模式內模式SQL支持關系數據庫三級模式結構3.2數據定義

SQL的數據定義功能:模式定義、表定義、視圖和索引的定義

3.2.1基本表的定義、刪除與修改一、定義基本表CREATETABLE<表名>

（<列名><數據類型>[<列級完整性約束條件>][，<列名><數據類型>[<列級完整性約束條件>]]…[，<表級完整性約束條件>]）；如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。8定義基本表常用完整性約束主碼約束：PRIMARYKEY唯一性約束：UNIQUE非空值約束：NOTNULL參照完整性約束:

FOREIGNKEY學生表Student[例1]建立“學生”表Student，學號是主碼，姓名取值唯一。

CREATETABLEStudent (SnoCHAR(9)PRIMARYKEY，/*列級完整性約束條件*/SnameCHAR(20)UNIQUE，/*Sname取唯一值*/SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20))；

主碼課程表Course[例2]建立一個“課程”表CourseCREATETABLECourse(CnoCHAR(4)PRIMARYKEY，

CnameCHAR(40)，

CpnoCHAR(4)，

CcreditSMALLINT，

FOREIGNKEY(Cpno)REFERENCESCourse(Cno));先修課Cpno是外碼被參照表是Course被參照列是Cno學生選課表SC[例3]建立一個“學生選課”表SC CREATETABLESC (SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

/*主碼由兩個屬性構成，必須作為表級完整性進行定義*/ FOREIGNKEY(Sno)REFERENCESStudent(Sno)，

/*表級完整性約束條件，Sno是外碼，被參照表是Student*/ FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cno是外碼，被參照表是Course*/ );二、數據類型SQL中域的概念用數據類型來實現定義表的屬性時需要指明其數據類型及長度選用哪種數據類型取值范圍要做哪些運算二、數據類型數據類型含義CHAR(n)長度為n的定長字符串VARCHAR(n)最大長度為n的變長字符串INT長整數（也可以寫作INTEGER）SMALLINT短整數NUMERIC(p，d)定點數，由p位數字（不包括符號、小數點）組成，小數后面有d位數字REAL取決于機器精度的浮點數DoublePrecision取決于機器精度的雙精度浮點數FLOAT(n)浮點數，精度至少為n位數字DATE日期，包含年、月、日，格式為YYYY-MM-DDTIME時間，包含一日的時、分、秒，格式為HH:MM:SS二、修改基本表ALTERTABLE<表名>[ADD<新列名><數據類型>[完整性約束]][DROP<完整性約束名>][ALTERCOLUMN<列名><數據類型>]；15二．修改基本表(續(xù))例4向Student表增加“入學時間”列，其數據類型為日期型。

ALTERTABLEStudentADDScomeDATE注意：修改原有的列定義有可能會破壞已有數據。三、刪除基本表

DROPTABLE<表名>［RESTRICT|CASCADE］；RESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制。在刪除基本表的同時，相關的依賴對象一起刪除17三．刪除基本表（續(xù)）例5刪除Student表

DROPTABLEStudent注意：基本表定義一旦刪除，表中的數據、此表上建立的索引和視圖都將自動被刪除。3.2.2索引的建立與刪除建立索引的目的：加快查詢速度誰可以建立索引DBA或表的屬主（即建立表的人）DBMS一般會自動建立以下列上的索引

PRIMARYKEYUNIQUE誰維護索引

DBMS自動完成

使用索引

DBMS自動選擇是否使用索引以及使用哪些索引索引RDBMS中索引一般采用B+樹、HASH索引來實現B+樹索引具有動態(tài)平衡的優(yōu)點HASH索引具有查找速度快的特點采用B+樹，還是HASH索引則由具體的RDBMS來決定索引是關系數據庫的內部實現技術，屬于內模式的范疇CREATEINDEX語句定義索引時，可以定義索引是唯一索引、非唯一索引或聚簇索引一、建立索引語句格式CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[<次序>][,<列名>[<次序>]]…)； 建立索引（續(xù)）[例6]CREATECLUSTERINDEXStusnameONStudent(Sname)；在Student表的Sname（姓名）列上建立一個聚簇索引在最經常查詢的列上建立聚簇索引以提高查詢效率一個基本表上最多只能建立一個聚簇索引建立聚簇索引后，更新索引列數據時，往往導致表中記錄的物理順序的變更，代價較大。經常更新的列不宜建立聚簇索引

建立索引（續(xù)）[例7]為學生-課程數據庫中的Student，Course，SC三個表建立索引。CREATEUNIQUEINDEXStusnoONStudent(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

Student表按學號升序建唯一索引

Course表按課程號升序建唯一索引

SC表按學號升序和課程號降序建唯一索引二、刪除索引DROPINDEX<索引名>；刪除索引時，系統會從數據字典中刪去有關該索引的描述。[例8]刪除Student表的Stusname索引

DROPINDEXStusname；243．3查詢以后均以下列三個表為操作對象：Student(Sno，Sname，Ssex，Sage，Sdept)Course(Cno,Cname,Cpno,Credit)SC(Sno,Cno,Grade)數據查詢格式

SELECT[ALL|DISTINCT]<目標列表達式>[，<目標列表達式>]…FROM

<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；

261．一般查詢例9查詢所有選修過課的學生的學號。

SELECTSno

FROMSC等價于

SELECTAllSno

FROMSC要去掉重復行，應寫：

SELECTDISTINCTSno

FROMSC272．條件查詢例10查信息系(IS)、數學系(MA)和計算機科學系(CS)的學生的姓名和性別。

SELECTSname，Ssex

FROMStudent WHERESdeptIN('IS'，'MA'，'CS')例11查詢學號為95001的學生的詳細情況。

SELECT*

FROMStudent

WHERESnoLIKE’95001’等價于：

SELECT*

FROMStudent

WHERESno='95001'28SQL提供的集函數：COUNT([DISTINCT|ALL]*) 統計元組個數COUNT([DISTINCT|ALL]＜列名＞) 統計一列中值的個數SUM([DISTINCT|ALL]＜列名＞) 計算一列值的總和(此列必須是數值型)AVG([DISTINCT|ALL＜列名＞) 計算一列值的平均值(此列必須是數值型)MAX([DISTINCT|ALL]＜列名＞)求一列中最大值MIN([DISTINCT|ALL]＜列名＞)求一列中最小值29例12查詢各個課程號與相應的選課人數。

SELECTCno，COUNT(Sno)

FROMSCGROUPBYCnoHAVING短語作用于組，從中選擇滿足條件的組。例13查詢選修了3門以上課程的學生的學號。

SELECTSnoFROMSC

GROUPBYSno HAVINGCOUNT(*)＞330３．連接查詢例14查詢每個學生及其選修課程的情況。

SELECTStudent.*，SC.*

FROMStudent，SCWHEREStudent.Sno＝SC.Sno（列Sno重復）例15自然連接Student和SC表。

SELECTStudent.Sno，Sname，Ssex，Sage，Sdept，Cno，Grade

FROMStudent，SC

WHEREStudent.Sno＝SC.Sno（列Sno不重復）31４．帶有ANY或ALL謂詞的子查詢＞ANY 大于子查詢結果中的某個值＜ANY 小于子查詢結果中的某個值＞＝ANY 大于等于子查詢結果中的某個值＜＝ANY 小于等于子查詢結果中的某個值＝ANY 等于子查詢結果中的某個值!＝ANY或＜＞ANY 不等于子查詢結果中的某個值＞ALL 大于子查詢結果中的所有值＜ALL 小于子查詢結果中的所有值＞＝ALL 大于等于子查詢結果中的所有值＜＝ALL 小于等于子查詢結果中的所有值＝ALL 等于子查詢結果中的所有值(通常沒有實際意義)!=ALL或＜＞ALL不等于子查詢結果中的任何一個值3216335．集合查詢例17查詢選修了課程1或者選修了課程2的學生。(并操作)SELECTSno

FROMSC

WHERECno='1'

UNION

SELECTSno

FROMSC

WHERECno='2’例18查詢計算機科學系中年齡不大于19歲的學生。

SELECT*

FROMStudentWHERESdept=‘CS’ANDSage<=19

（交操作的等價操作）343．4數據更新1.插入單個元組語句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能將新元組插入指定表中。2.插入子查詢結果語句格式

INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢；功能將子查詢結果插入指定表中37插入數據例19對每一個系，求學生的平均年齡，并把結果存入數據庫。CREATETABLEDeptage(SdepCHAR(15),AvgageSMALLINT)INSERT INTODeptage(Sdep,Avgage) SELECTSdept,AVG(Sage) FROMStudent GROUPBYSdept3修改數據語句格式

UPDATE<表名>SET<列名>=<表達式>[，<列名>=<表達式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組39

例20將計算機科學系全體學生的成績置零。

UPDATESCSETGrade=0 WHERE'CS‘= (SELECTSdeptFROMStudent WHEREStudent.Sno=SC.Sno)修改數據40注意：基本表的修改操作與數據庫的一致性由DBMS保證。例21將95007號學生的學號修改為96089。

UPDATEStudent

SETSno='96089' WHERESno='95007‘實際執(zhí)行了兩條語句，第二條為

UPDATESC

SETSno='96089’

WHERESno='95007'4刪除數據DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要修改表中的所有元組42例22刪除計算機科學系所有學生的選課記錄。

DELETEFROMSCWHERE'CS'= (SELECTSdept FROMStudent WHEREStudent.Sno=SC.Sno)

刪除數據433．5視圖3．5．1定義視圖1．建立視圖

CREATEVIEW＜視圖名＞[(＜列名＞[，＜列名＞]…)] AS＜子查詢＞

[WITHCHECKOPTION] “WITHCHECKOPTION”表示對視圖進行UPDATE，INSERT 和DELETE操作時要保證更新、插入或刪除的行滿足視圖定義時的謂詞條件（即子查詢中的條件表達式）。44三種情況下必須明確指定組成視圖的所有列名：其中某個目標列不是單純的屬性名，而是集函數或列表達式。多表連接時選出了幾個同名列作為視圖的字段。需要在視圖中為某個列啟用新的更合適的名字。例23建立信息系學生的視圖，并要求進行修改和插入操作時仍須保證該視圖只有信息系的學生。

CREATEVIEWIS_StudentAS SELECTSno，Sname，Sage FROMStudent WHERESdept='IS'

WITHCHECKOPTION45例24將學生的學號及其平均成績定義為一個視圖。

CREATEVIEWS_G(Sno，Gavg)

AS

SELECTSno，AVG(Grade)

FROMSC

GROUPBYSno2．刪除視圖

DROPVIEW＜視圖名＞例25刪除視圖IS_S1DROPVIEWIS_S1463．5．2查詢視圖例26在信息系學生的視圖中找出年齡小于20歲的學生。

SELECTSno，SageFROMIS_StudentWHERESage＜20DBMS轉換后的查詢語句為：

SELECTSno，SageFROMStudentWHERESdept＝'IS'ANDSage<2047例27查詢信息系選修了1號課程的學生。（基本表和虛表的連接）

SELECTSno，Sname

FROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1’483．5．3更新視圖例28將信息系學生視圖IS_Student中學號為95002的學生姓名改為“劉辰”。

CREATEVIEWIS_Student AS

SELECTSno，Sname，Sage

FROMStudent WHERESdept='IS’

WITHCHECKOPTIONUPDATEIS_Student

SETSname=‘劉辰’

WHERESno='95002'49DBMS轉換后的更新語句為

UPDATEStudent

SETSname='劉辰'WHERESno='95002'ANDSdept='IS';在關系數據庫中，并不是所有的視圖都是可更新的，因為有些視圖的更新不能唯一地有意義地轉換成對相應基本表的更新。50并不是所有的視圖都可以更新：

如果想把視圖S_G中學號為95001的學生的平均成績改成90分，SQL語句如下：

UPDATES_G

SETGavg=90

WHERESno='95001‘

這個對視圖的更新是無法轉換成對基本表SC的更新的，因為系統無法修改各科成績，以使平均成績成為90。所以S_G視圖是不可更新的。513．5．4視圖的用途1．視圖能夠簡化用戶的操作2．視圖使用戶能以多種角度看待同一數據3．視圖對重構數據庫提供了一定程度的邏輯獨立性4．視圖能夠對機密數據提供安全保護523．5．5使用視圖的限制

●在一個基本表上建立的視圖，只有包含基本表的主鍵才可以更新；

●一個視圖最多只能有250個列；

●不能在視圖上建立觸發(fā)器和索引；

●對視圖的一個更新語句只能影響一個基本表，所以由多表連接定義的視圖不允許更新。

●定義視圖語句不能使用UNION操作符。

●視圖定義中用到GROUPBY子句或包含集合函數、計算列的數據不能修改。

●注意：不同的系統對視圖的更新有不同的限制，使用時要參照具體的DBMS的說明。533．6數據控制

數據控制也稱為數據保護，包括數據的安全性控制、完整性控制、并發(fā)控制和恢復。541．授權GRANT＜權限＞[，＜權限＞]…[ON＜對象類型＞＜對象名＞]TO＜用戶＞[，＜用戶＞]…[WITHGRANTOPTION]//傳播權限55例29把對Student表和Course表的全部權限授予用戶U2和U3。

GRANTALLPRIVILEGESONTABLEStudent，CourseTOU2，U3例30把對表SC的查詢權限授予所有用戶。

GRANTSELECTONTABLESCTOPUBLIC56

2．收回權限REVOKE＜權限＞[，＜權限＞]…[ON＜對象類型＞＜對象名＞] FROM＜用戶＞[，＜用戶＞]…例31把用戶U4修改學生學號的權限收回。

REVOKEUPDATE(Sno)ONTABLEStudentFROMU4習題1.假定三個關系組成用戶子模式。A(A#ANAMEWQTYCITY)B(B#BNAMEPMCE)AB(A#B#QTY)各個屬性的含義如下：A#(商店代號)ANAME(商店名)、WQTY(店員人數)CITY(所在城市)、B#(商品號)BNAME(商品名稱)、PRICE(價格)QTY(商品數量)。試用SQL語言寫出下列查詢。

(1)找出店員人數不超過100人或者在長沙市的所有商店的代號和商店名。

(2)找出供應書包的商店名。

(3)找出至少供應商店代號為256的商店所供應的全部商品的商店名和所在城市。2.設有圖書登記表TS，具有屬性：BNO(圖書編號)，BC(圖書類別)，BNA(書名)，AU(著者)，PUB(出版社)。按下列要求用SQL語言進行設計：

(1)按圖書館編號BNO建立TS表的索引ITS。

(2)查詢按出版社統計其出版圖書總數。

(3)刪除索引ITS。3.設有學生表S(SNO，SN)(SNO為學生號，SN為姓名)學生選修課程表SC(SNO，CNO，CN，G)(CNO為課程號，CN為課程名，G為成績)，試用SQL語言完成以下各題：

(1)建立一個視圖v—SSC(SNO，SN，CNO，CN，G)，并按CNO升序排序；

(2)從視圖v-SSC上查詢平均成績在90分以上的SN、CN和G?！暗谌v關系數據庫標準語言SQL”閱讀提示示例數據庫——BankingEnterpriseBranch(branch-name,branch-city,assets)Customer(customer-name,customer-street,customer-city)Account(branch-name,account-number,balance)Depositor(customer-name,account-number)Loan(branch-name,loan-number,amount)Borrower(customer-name,loan-number)Select子句(SelectClause)選擇子句對應于關系代數中的投影運算，用于列出出現在結果關系中的屬性名。“找出關系loan中所有分支機構的名稱”

selectbranch-name fromloan與上面查詢對應的關系代數語義：在選擇子句中出現的“*”(asterisk)表示全部屬性(allattributes) select* fromloanSQL允許在關系中出現重復(duplicate)元組出于性能原因容許保留重復元組SQL運算基于多重集(multi-sets)，而非集合(sets)一些運算對重復元組敏感(e.g.,count,average,etc.)在select后插入distinct來刪除重復元組“找出關系loan中所有不同的分支機構的名稱”

selectdistinctbranch-name fromloan用all來說明不刪除重復的元組

selectallbranch-name fromloan在選擇子句中可以包括算術表達式在元組的屬性和常量上進行+,-,*,/運算

selectbranch-name,loan-number,amount*100 fromloan結果關系模式與loan相同，但屬性amount的值乘以100Where子句(WhereClause)對應于關系代數中的選擇謂詞(selectionpredicate)謂詞由涉及from子句中關系的屬性組成“找出所有在Perryridge分支機構貸款且貸款額超過$1200的貸款號”

selectloan-number fromloan wherebranch-name=“Perryridge”andamount>1200SQL應用邏輯連接詞and,or,和not在比較運算的操作數中允許使用算術表達式between比較運算簡化在where子句中出現的比較運算，如：avalue

somevalue并且someothervalue“找出貸款額在$90,000~$100,000(即：

$90,000并且

$100,000)之間的貸款號”

selectloan-number fromloan whereamountbetween90000and100000等價如下查詢：

selectloan-number fromloan whereamount>=90000andamount<=100000From子句(FromClause)對應于關系代數中的笛卡兒乘積(Cartesianproduct)運算From子句列出在查詢求值中需要掃描的關系“計算笛卡兒乘積borrowerloan” select* fromborrower,loan“找出在Perryridge銀行中有貸款的客戶姓名和貸款號”

selectdistinctcustomer-name,borrower.loan-number fromborrower,loan whereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”更名運算(RenameOperation)需要更名運算的理由在結果關系中可能出現具有相同名稱的屬性結果關系的屬性中可能沒有名字希望在結果關系中更改屬性的名稱一個關系可能以不同的名稱參與查詢關系和屬性通過as子句更名

old-nameasnew-name“找出在Perryridge銀行中有貸款的客戶姓名和貸款號，將貸款號loan-number更名為loan-id” selectdistinctcustomer-name,borrower,loan-numberasloan-id fromborrower,loan whereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”元組變量(TupleVariables)元組變量在from子句中用as定義一個元組變量必須與一個特定的關系相關聯，大部分用于在同一個關系中比較兩個元組“找出在銀行中有貸款的客戶姓名和貸款號”

selectdistinctcustomer-name,T.loan-number fromborrowerasT,loanasS whereT.loan-number=S.loan-number“找出資產至少比位于Brooklyn的某一家分支機構多的分支機構”

selectdistinctT,branch-name frombranchasT,branchasS whereT.assets>S.assetsandS.branch-city=“Brooklyn”字符串操作(StringOperations)字符串匹配(String-matching)是在字符串上的比較運算。用兩個特殊的字符來描述模式百分號(%)：%匹配任意子串(substring)下劃線(_)：_匹配任意字符(character)“找出在街道地址中包含子串‘Main’的所有顧客名”

selectcustomer-name fromcustomer wherecustomer-streetlike“%Main%”轉義符(Escapecharacter)\(backslash)匹配“Main%”：like“Main\%”匹配“ab\cd”：like“ab\\cd”任意只有一個字符的字符串?任意至少有一個字符的字符串?排序顯示的元組“按字母序列出有貸款的客戶名”

selectdistinctcustomer-name fromborrower,loan whereborrower.loan-number=loan.loan-number orderbycustomer-name用desc說明降序，asc說明升序(default) select* fromloan orderbyamountdesc,loan-numberascorderby請求完成排序(sort)對大數量的元組排序代價高僅在需要時才進行排序重復元組(Duplicates)在關系中保留重復的元組是有用的SQL形式化地定義在結果關系中有什么元組和多少個副本元組出現SQL查詢中的重復元組的語義可以用多重集(multi-set)的關系代數來定義

重復元組(Cont.)假定關系r(A,B)和s(C)是如下的多重集：r={(1,a),(2,a)},s={(2),(3),(3)}

SQLduplicate語義上面查詢等價于多重集的關系代數表達式：集合運算(SetOperations)集合運算union,intersect,和except在關系上進行操作對應于關系代數的,,

和–運算自動刪除重復元組多重集版本的unionall,intersectall和exceptall保留重復的元組假設一個元組在r中出現m次，在s中出現n次在runionalls中出現m+n次在rintersectalls中出現min(m,n)次在rexceptalls中出現max(0,m-n)次“找出在銀行有貸款、帳戶、或兩者都有的客戶”(selectcustomer-namefromdepositor)union(selectcustomer-namefromborrower)“找出在銀行同時有帳戶和貸款的客戶”(selectcustomer-namefromdepositor)intersect(selectcustomer-namefromborrower)“找出在銀行有帳戶而無貸款的客戶”(selectcustomer-namefromdepositor)except(selectcustomer-namefromborrower)集合運算(cont.)聚集函數(AggregateFunctions)聚集函數以多重集(multiset)為輸入，返回單個值

avg：平均值(averagevalue)min：最小值(minimumvalue)max：最大值(maximumvalue)sum：總和(sumofvalues)count：計數(numberofvalues)聚集函數(Cont.)“找出在Perryridge分支機構的帳戶余額的平均值”selectavg(balance)fromaccountwherebranch-name=“Perryridge”“計算在customer關系中元組的個數”selectcount(*)fromcustomer“計算在銀行中有存款的人數”selectcount(distinctcustomer-name)fromdepositor聚集函數—GroupBy列表“找出在每個分支機構的存款人數”selectbranch-name,count(distinctcustomer-name)fromdepositor,accountwheredepositor.account-number=account.account-numbergroupbybranch-name注意：在select子句中，聚集函數外的屬性必須出現在groupby列表中。聚集函數—Having子句“找出平均余額大于$1,200的分支機構和它們的平均余額”selectbranch-name,avg(balance)fromaccountgroupbybranch-namehavingavg(balance)>1200注意：having子句中的謂詞在形成分組后才求值空值(NullValues)元組的屬性有空值，由null表示。意義：任何涉及null的算術表達式，其結果是null所有涉及null的比較運算返回false更準確地：任何與null的比較運算返回unknown(SQL-92語義)(trueorunknown)=true(falseorunknown)=unknown(unknownorunknown)=unknown,(trueandunknown)=unknown(falseandunknown)=false(unknownandunknown)=unknown如果謂詞求值為unknown，則where子句對其處理為false如果謂詞P求值為unknown，則“Pisunknown”為true“找出在關系loan中amount為空值的貸款號”selectloan-numberfromloanwhereamountisnull“計算總的貸款值”selectsum(amount)fromloan以上查詢忽略空值。如果沒有非空值的amount，則結果是null聚集函數除count(*)外，都忽略null值。嵌套子查詢(NestedSubqueries)SQL提供了一套嵌套子查詢的機制，子查詢是一個嵌套在另一個查詢中的Select-from-where表達式。子查詢的一般用途是用于完成如下測試：集合成員資格(setmembership)集合的比較(setcomparison)集合的基數(setcardinality)集合成員資格(SetMembership)集合成員資格—Example(1)“找出在銀行中同時有帳戶和貸款的客戶”selectdistinctcustomer-namefromborrowerwherecustomer-namein(selectcustomer-name fromdepositor)“找出在銀行中有貸款而無帳戶的客戶”selectdistinctcustomer-namefromborrowerwherecustomer-namenotin(selectcustomer-namefromdepositor)“找出在Perryridge分支機構既有帳戶又有貸款的客戶”selectdistinctcustomer-namefromborrower,loanwhereborrower.loan-number=loan.loan-numberand branch-name=“Perryridge”and (branch-name,customer-name)in (selectbranch-name,customer-name fromdepositor,account wheredepositor.account-number=account.account-number)集合成員資格—Example(2)Some子句(SomeClause)集合比較(SetComparison)—Example“找出總資產至少比位于Brooklyn的某一家分支機構多的分支機構名稱”selectdistinctT.branch-namefrombranchasT,branchasSwhereT.assets>S.assetsand S.branch-city=“Brooklyn”selectbranch-namefrombranchwhereassets>some(selectassets frombranch wherebranch-city=“Brooklyn”）All子句(AllClause)集合比較(SetComparison)—Example“找出總資產比位于Brooklyn的任意一家分支機構都多的分支機構名稱”selectbranch-namefrombranchwhereassets>all(selectassets frombranch wherebranch-city=“Brooklyn”）測試是否為空關系Exists結構在作為參數的子查詢非空時返回true

“找出在Brooklyn所有分支機構都有帳戶的客戶”selectdistinctS.customer-namefromdepositorasSwherenotexists((selectbranch-name frombranch wherebranch-city=“Brooklyn”) except (selectR.branch-name fromdepositorasT,accountasR whereT.account-number=R.account-numberandS.customer-name=T.customer-name))測試是否存在重復元組

unique結構測試是否在作為參數的子查詢中存在任何重復元組“找出在Perryridge分支機構中只有一個帳戶的客戶”selectT.customer-namefromdepositorasTwhereunique( selectR.customer-name fromaccount,depositorasR whereT.customer-name=R.customer-nameand R.account-number=account.account-numberand account.branch-name=“Perryridge”派生關系(DerivedRelations)“找出平均帳戶結算大于$1200的分支機構和平均結算”selectbranch-name,avg-balancefrom(selectbranch-name,avg(balance) fromaccount groupbybranch-name) asresult(branch-name,avg-balance)whereavg-balance>1200視圖(Views)用視圖來對特定的用戶隱藏特定的數據createviewvas<queryexpression>

其中：<queryexpression>是任意的SQL查詢表達式視圖名為v視圖—Example包含各分支機構名稱以及在分支機構有帳戶或貸款的客戶名字createviewall-customeras(selectbranch-name,customer-namefromdepositor,accountwheredepositor.account-number=account.account-number)union(selectbranch-name,customer-namefromborrower,loanwhereborrower.loan-number=loan.loan-number)“找出在Perryridge分支機構的全部客戶”selectcustomer-namefromall-customerwherebranch-name=“Perryridge”修改數據庫—刪除(Deletion)“刪除在Perryridge分支機構的所有帳戶”deletefromaccountwherebranch-name=“Perryridge”“刪除位于Needham的沒一個分支機構的所有帳戶”deletefromaccountwherebranch-namein(selectbranch-name frombranch wherebranch-city=“Needham”)deletefromdepositorwhereaccount-numberin(selectaccount-number frombranch,account wherebranch-city=“Needham” andbranch.branch-name=account.branch-name)修改數據庫—刪除(Deletion)(Cont..)“刪除余額低于銀行平均余額的帳戶”deletefromaccountwherebalance<(selectavg(balance) fromaccount)問題：當刪除account中的元組時，平均余額隨之發(fā)生變化SQL的解決方法：首先計算平均余額，找出需要刪除的元組然后，刪除上一步找出的元組(不重新計算avg函數和測試要刪除的元組)修改數據庫—插入(Insertion)在account關系中增加一個元組insertintoaccountvalues(“Perryridge”,“A-9732”,1200)

或者insertintoaccount(branch-name,balance,account-number)values(“Perryridge”,1200,“A-9732”)在account關系中增加一個元組，該元組的balance設置為nullinsertintoaccountvalues(“Perryridge”,“A-777”,null)給所有在Perryridge分支機構有貸款的客戶獎勵$200，以他們的貸款帳號作為其新的存款帳號insertintoaccountselectbranch-name,loan-number,200fromloanwherebranch-name=“Perryridge”insertintodepositorselectcustomer-name,loan-numberfromloan,borrowerwherebranch-name=“Perryridge”andloan.account-number=borrower.account-number修改數據庫—插入(cont.)修改數據庫—更新(Update)給所有超過$10,000存款增加6%的利息，其它存款增加5%的利息。updateaccountsetbalance=balance*1.06wherebalance>10000updateaccountsetbalance=balance*1.05wherebalance10000運算的順序非常重要！關系的連接(JoinedRelations)關系的連接以兩個關系作為輸入，返回另一個關系作為連接的結果連接運算的典型應用是在from子句中作為子查詢表達式連接條件(Joincondition)：定義在參與連接的兩個關系中匹配的元組定義出現在連接結果關系中的屬性連接類型(Jointype)定義如何處理在連接運算中不匹配的元組關系的連接—DatasetsforExamples關系的連接—Examplesloaninnerjoinborroweronloan.loan-number=borrower.loan-numberloanleftouterjoinborroweronloan.loan-number=borrower.loan-number關系的連接—Examplesloannaturalinnerjoinborrowerloannaturalrightouterjoinborrower關系的連接—Examples

loanfullouterjoinborrowerusing(loan-number)“找出在銀行有貸款或者有存款的客戶(但不能兩者都有)selectcustomer-namefrom(depositornaturalfullouterjoinborrower)whereaccount-numberisnullorloan-numberisnull數據定義語言

(DDL)不僅允許定義一組關系，而且能夠說明各關系每個關系的模式與每個關系和屬性相關的說明：每個關系的模式各屬性的值域完整性約束每個關系要維護的索引集合每個關系的安全和授權信息每個關系在磁盤上的物理存儲結構SQL中的域類型char(n)：定長字符串，用戶說明長度nvarchar(n)：變長字符串，用戶說明長度nint：整數(與計算機相關的整數的有限子集)smallint：小整數(與計算機相關的整數域類型的子集)numeric(p,d)：定點數，用戶說明精度，有p位數字，小數點右邊有n位數字real,doubleprecision：浮點數和雙精度浮點數，精度與機器相關float(n)：浮點數，用戶指定至少為n位數字的精度date：日期，包括年(4位)、月和日time：時間，包括時、分和秒創(chuàng)建表的結構SQL關系通過createtable命令來創(chuàng)建

r

是關系名

Example:createtablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger)創(chuàng)建關系表中的完整性約束非空值：notnull

謂詞：check(P),其中P是一個謂詞Example:說明branch-name為關系branch的主碼，并且使得assets的值非負createtablebranch(branch-namechar(15)notnull,branch-citychar(30),assetsinteger,primarykey(branch-name),check(assets>=0))在SQL-92中，說明為primarykey的屬性自動為非空嵌入式(Embedded)SQL和動態(tài)(Dynamic)SQL嵌入式SQL即在多種程序設計語言中使用SQLSQL所嵌入的語言稱為宿主語言(hostlanguage)動態(tài)SQL允許程序在運行時構造和提交SQL查詢DBMS的完整性控制機制（續(xù)）2.檢查功能立即執(zhí)行的約束(Immediateconstraints)

語句執(zhí)行完后立即檢查是否違背完整性約束延遲執(zhí)行的約束(Deferredconstrainsts)完整性檢查延遲到整個事務執(zhí)行結束后進行DBMS的完整性控制機制（續(xù)）例：銀行數據庫中“借貸總金額應平衡”的約束就應該是延遲執(zhí)行的約束從賬號A轉一筆錢到賬號B為一個事務，從賬號A轉出去錢后賬就不平了，必須等轉入賬號B后賬才能重新平衡，這時才能進行完整性檢查。實體完整性檢查和違約處理插入或對主碼列進行更新操作時，RDBMS按照實體完整性規(guī)則自動進行檢查。包括：1.檢查主碼值是否唯一，如果不唯一則拒絕插入或修改2.檢查主碼的各個屬性是否為空，只要有一個為空就拒絕插入或修改參照完整性檢查和違約處理可能破壞參照完整性的情況及違約處理被參照表（例如Student）參照表（例如SC）違約處理可能破壞參照完整性

插入元組拒絕可能破壞參照完整性

修改外碼值拒絕刪除元組

可能破壞參照完整性拒絕/級連刪除/設置為空值修改主碼值

可能破壞參照完整性拒絕/級連修改/設置為空值違約處理參照完整性違約處理1.拒絕(NOACTION)執(zhí)行默認策略2.級聯(CASCADE)操作3.設置為空值（SET-NULL）對于參照完整性，除了應該定義外碼，還應定義外碼列是否允許空值［例4］顯式說明參照完整性的違約處理示例

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINT，

PRIMARYKEY（Sno，Cno），

FOREIGNKEY(Sno)REFERENCESStudent(Sno) ONDELETECASCADE/*級聯刪除SC表中相應的元組*/ONUPDATECASCADE，/*級聯更新SC表中相應的元組*/FOREIGNKEY(Cno)REFERENCESCourse(Cno) ONDELETENOACTION /*當刪除course表中的元組造成了與SC表不一致時拒絕刪除*/ONUPDATECASCADE/*當更新course表中的cno時級聯更新SC表中相應的元組*/)；

屬性上的約束條件的定義CREATETABLE時定義列值非空（NOTNULL）列值唯一（UNIQUE）檢查列值是否滿足一個布爾表達式（CHECK）屬性上的約束條件的定義(續(xù))1.不允許取空值［例5］在定義SC表時，說明Sno、Cno、Grade屬性不允許取空值。

CREATETABLESC

（SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINTNOTNULL，

PRIMARYKEY(Sno，Cno)，

/*如果在表級定義實體完整性，隱含了Sno，Cno不允許取空值，則在列級不允許取空值的定義就不必寫了*/）；屬性上的約束條件的定義(續(xù))2.列值唯一［例6］建立部門表DEPT，要求部門名稱Dname列取值唯一，部門編號Deptno列為主碼

CREATETABLEDEPT(DeptnoNUMERIC(2)，

DnameCHAR(9)UNIQUE，/*要求Dname列值唯一*/LocationCHAR(10)，

PRIMARYKEY(Deptno))；屬性上的約束條件的定義(續(xù))3.用CHECK短語指定列值應該滿足的條件［例7］Student表的Ssex只允許取“男”或“女”。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(8)NOTNULL，

SsexCHAR(2)CHECK(SsexIN(‘男’，‘女’))，

/*性別屬性Ssex只允許取'男'或'女'*/SageSMALLINT，

SdeptCHAR(20));元組上的約束條件的定義(續(xù))［例9］當學生的性別是男時，其名字不能以Ms.打頭。

CREATETABLEStudent(SnoCHAR(9)，

SnameCHAR(8)NOTNULL，

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20)，

PRIMARYKEY(Sno)，

CHECK(Ssex='女'ORSnameNOTLIKE'Ms.%')/*定義元組中Sname和Ssex兩個屬性值間的約束條件*/)性別是女性的元組都能通過該項檢查，因為Ssex=‘女’成立當性別是男性時，要通過檢查則名字一定不能以Ms.打頭完整性約束命名子句CONSTRAINT約束CONSTRAINT<完整性約束條件名>［PRIMARYKEY短語

|FOREIGNKEY短語

|CHECK短語］［例10］建立學生登記表Student，要求學號在90000~99999之間，姓名不能取空值，年齡小于30，性別只能是“男”或“女”。

CREATETABLEStudent(SnoNUMERIC(6)

CONSTRAINTC1CHECK(SnoBETWEEN90000AND99999)，

SnameCHAR(20)

CONSTRAINTC2NOTNULL，

SageNUMERIC(3)

CONSTRAINTC3CHECK(Sage<30)，

SsexCHAR(2)

CONSTRAINTC4CHECK(SsexIN('男'，'女'))，

CONSTRAINTStudentKeyPRIMARYKEY(Sno))；在Student表上建立了5個約束條件，包括主碼約束（命名為StudentKey）以及C1、C2、C3、C4四個列級約束。［例13］修改表Student中的約束條件，要求學號改為在900000~999999之間，年齡由小于30改為小于40可以先刪除原來的約束條件，再增加新的約束條件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，

ALTERTABLEStudentDROPCONSTRAINTC3;

ALTERTABLEStudentADDCONSTRAINTC3CHECK(Sage<40)；域中的完整性限制SQL支持域的概念，并可以用CREATEDOMAIN語句建立一個域以及該域應該滿足的完整性約束條件。