D-3關(guān)系數(shù)據(jù)庫標準語言SQL

1第三講關(guān)系數(shù)據(jù)庫標準語言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制2【學(xué)習(xí)目標】

本講的內(nèi)容是關(guān)系數(shù)據(jù)庫的標準語言SQL(StructuredQueryLanguage)--結(jié)構(gòu)化查詢語言。本講的學(xué)習(xí)目標主要是熟練掌握SQL的語法，能夠在實踐中熟練地運用SQL實現(xiàn)各種查詢請求?！緦W(xué)習(xí)方法】

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

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

SQL的數(shù)據(jù)定義功能:模式定義、表定義、視圖和索引的定義

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

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

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

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學(xué)生選課表SC[例3]建立一個“學(xué)生選課”表SC CREATETABLESC (SnoCHAR(9)，

CnoCHAR(4)，

GradeSMALLINT，

PRIMARYKEY(Sno，Cno)，

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

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

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

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

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

PRIMARYKEYUNIQUE誰維護索引

DBMS自動完成

使用索引

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

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

Student表按學(xué)號升序建唯一索引

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

SC表按學(xué)號升序和課程號降序建唯一索引二、刪除索引DROPINDEX<索引名>；刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。[例8]刪除Student表的Stusname索引

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

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

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

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

SELECTSno

FROMSC等價于

SELECTAllSno

FROMSC要去掉重復(fù)行，應(yīng)寫：

SELECTDISTINCTSno

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

SELECTSname，Ssex

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

SELECT*

FROMStudent

WHERESnoLIKE’95001’等價于：

SELECT*

FROMStudent

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

SELECTCno，COUNT(Sno)

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

SELECTSnoFROMSC

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

SELECTStudent.*，SC.*

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

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

FROMStudent，SC

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

FROMSC

WHERECno='1'

UNION

SELECTSno

FROMSC

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

SELECT*

FROMStudentWHERESdept=‘CS’ANDSage<=19

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

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

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

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

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

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

UPDATEStudent

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

UPDATESC

SETSno='96089’

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

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

刪除數(shù)據(jù)433．5視圖3．5．1定義視圖1．建立視圖

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

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

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

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

CREATEVIEWS_G(Sno，Gavg)

AS

SELECTSno，AVG(Grade)

FROMSC

GROUPBYSno2．刪除視圖

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

SELECTSno，SageFROMIS_StudentWHERESage＜20DBMS轉(zhuǎn)換后的查詢語句為：

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

SELECTSno，Sname

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

CREATEVIEWIS_Student AS

SELECTSno，Sname，Sage

FROMStudent WHERESdept='IS’

WITHCHECKOPTIONUPDATEIS_Student

SETSname=‘劉辰’

WHERESno='95002'49DBMS轉(zhuǎn)換后的更新語句為

UPDATEStudent

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

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

UPDATES_G

SETGavg=90

WHERESno='95001‘

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

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

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

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

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

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

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

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

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

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

GRANTSELECTONTABLESCTOPUBLIC56

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

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

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

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

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

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

(2)查詢按出版社統(tǒng)計其出版圖書總數(shù)。

(3)刪除索引ITS。3.設(shè)有學(xué)生表S(SNO，SN)(SNO為學(xué)生號，SN為姓名)學(xué)生選修課程表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關(guān)系數(shù)據(jù)庫標準語言SQL”閱讀提示示例數(shù)據(jù)庫——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)選擇子句對應(yīng)于關(guān)系代數(shù)中的投影運算，用于列出出現(xiàn)在結(jié)果關(guān)系中的屬性名?！罢页鲫P(guān)系loan中所有分支機構(gòu)的名稱”

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

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

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

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

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

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

$90,000并且

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

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

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

selectdistinctcustomer-name,borrower.loan-number fromborrower,loan whereborrower.loan-number=loan.loan-numberandbranch-name=“Perryridge”更名運算(RenameOperation)需要更名運算的理由在結(jié)果關(guān)系中可能出現(xiàn)具有相同名稱的屬性結(jié)果關(guān)系的屬性中可能沒有名字希望在結(jié)果關(guān)系中更改屬性的名稱一個關(guān)系可能以不同的名稱參與查詢關(guān)系和屬性通過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定義一個元組變量必須與一個特定的關(guān)系相關(guān)聯(lián)，大部分用于在同一個關(guān)系中比較兩個元組“找出在銀行中有貸款的客戶姓名和貸款號”

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

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

selectcustomer-name fromcustomer wherecustomer-streetlike“%Main%”轉(zhuǎn)義符(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)對大數(shù)量的元組排序代價高僅在需要時才進行排序重復(fù)元組(Duplicates)在關(guān)系中保留重復(fù)的元組是有用的SQL形式化地定義在結(jié)果關(guān)系中有什么元組和多少個副本元組出現(xiàn)SQL查詢中的重復(fù)元組的語義可以用多重集(multi-set)的關(guān)系代數(shù)來定義

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

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

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

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

“找出在Brooklyn所有分支機構(gòu)都有帳戶的客戶”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))測試是否存在重復(fù)元組

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

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

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

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

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

r

是關(guān)系名

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

謂詞：check(P),其中P是一個謂詞Example:說明branch-name為關(guān)系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即在多種程序設(shè)計語言中使用SQLSQL所嵌入的語言稱為宿主語言(hostlanguage)動態(tài)SQL允許程序在運行時構(gòu)造和提交SQL查詢DBMS的完整性控制機制（續(xù)）2.檢查功能立即執(zhí)行的約束(Immediateconstraints)

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

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

修改外碼值拒絕刪除元組

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

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

CREATETABLESC(SnoCHAR(9)NOTNULL，

CnoCHAR(4)NOTNULL，

GradeSMALLINT，

PRIMARYKEY（Sno，Cno），

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

屬性上的約束條件的定義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短語指定列值應(yīng)該滿足的條件［例7］Student表的Ssex只允許取“男”或“女”。

CREATETABLEStudent(SnoCHAR(9)PRIMARYKEY，

SnameCHAR(8)NOTNULL，

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

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

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

CREATETABLEStudent(SnoCHAR(9)，

SnameCHAR(8)NOTNULL，

SsexCHAR(2)，

SageSMALLINT，

SdeptCHAR(20)，

PRIMARYKEY(Sno)，

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

|FOREIGNKEY短語

|CHECK短語］［例10］建立學(xué)生登記表Student，要求學(xué)號在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中的約束條件，要求學(xué)號改為在900000~999999之間，年齡由小于30改為小于40可以先刪除原來的約束條件，再增加新的約束條件

ALTERTABLEStudentDROPCONSTRAINTC1;

ALTERTABLEStudentADDCONSTRAINTC1CHECK(SnoBETWEEN900000AND999999)，

ALTERTABLEStudentDROPCONSTRAINTC3;

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