會(huì)計(jì)學(xué)1的特征SQL的數(shù)據(jù)定義第三章關(guān)系數(shù)據(jù)庫語言SQL3.1SQL的特征1.SQL的主要標(biāo)準(zhǔn)

SQL-86SQL-89SQL-92(SQL2)SQL-99(SQL3)第1頁/共119頁SQL-86。SQL的第一個(gè)標(biāo)準(zhǔn)是1986年10月由美國國家標(biāo)準(zhǔn)化組織(ANSI)公布的。

SQL-89。ANSI以后通過對(duì)SQL-86的不斷修改和完善，于1989年第二次公布了SQL標(biāo)準(zhǔn)，即SQL-89，該標(biāo)準(zhǔn)增強(qiáng)了完整性的語言特征。

SQL-92(SQL2)。1992年又公布了SQL-92標(biāo)準(zhǔn)，該標(biāo)準(zhǔn)增加了支持對(duì)遠(yuǎn)程數(shù)據(jù)庫的訪問，擴(kuò)充了數(shù)據(jù)類型、操作類型、動(dòng)態(tài)SQL等許多新的特征。

SQL-99(SQL3)。完成于1999年的SQL-99修訂本具有更高級(jí)的特征。引入了支持對(duì)象-關(guān)系DBMS模型的SQL，擴(kuò)展了對(duì)象、遞歸、觸發(fā)等許多新的特征，支持用戶自定義函數(shù)、自定義數(shù)據(jù)類型。第2頁/共119頁2.SQL的功能特點(diǎn)功能：數(shù)據(jù)定義數(shù)據(jù)查詢數(shù)據(jù)操縱數(shù)據(jù)控制第3頁/共119頁第三章關(guān)系數(shù)據(jù)庫語言SQL

特點(diǎn)：綜合統(tǒng)一

非關(guān)系模型的數(shù)據(jù)語言不同模式有不同的數(shù)據(jù)定義語言及數(shù)據(jù)操縱語言。SQL語言則集數(shù)據(jù)定義、操縱和控制功能于一體，語言風(fēng)格統(tǒng)一，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)，包括定義關(guān)系模式、錄入數(shù)據(jù)以建立數(shù)據(jù)庫、查詢、更新、維護(hù)、數(shù)據(jù)庫重構(gòu)、數(shù)據(jù)庫安全性控制等一系列操作要求，這就為數(shù)據(jù)庫應(yīng)用系統(tǒng)開發(fā)提供了良好的環(huán)境，例如用戶在數(shù)據(jù)庫投入運(yùn)行后，還可根據(jù)需要隨時(shí)地逐步地修改模式，并不影響數(shù)據(jù)庫的運(yùn)行，從而使系統(tǒng)具有良好的可擴(kuò)充性。

第4頁/共119頁第三章關(guān)系數(shù)據(jù)庫語言SQL高度非過程化

非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言是面向過程的語言，用其完成某項(xiàng)請(qǐng)求，必須指定存取路徑。而用SQL語言進(jìn)行數(shù)據(jù)操作，用戶只需提出“做什么”，而不必指明“怎么做”，因此用戶無需了解存取路徑，存取路徑的選擇以及SQL語句的操作過程由系統(tǒng)自動(dòng)完成。這不但大大減輕了用戶負(fù)擔(dān)，而且有利于提高數(shù)據(jù)獨(dú)立性。第5頁/共119頁第三章關(guān)系數(shù)據(jù)庫語言SQL靈活的使用方式SQL語言既是自含式語言，又是嵌入式語言。作為自含式語言，它能夠獨(dú)立地用于聯(lián)機(jī)交互的使用方式，用戶可以在終端鍵盤上直接鍵入SQL命令對(duì)數(shù)據(jù)庫進(jìn)行操作。作為嵌入式語言，SQL語句能夠嵌入到高級(jí)語言（例如C、COBOL、FORTRAN、PL/1）程序中，供程序員設(shè)計(jì)程序時(shí)使用。而在兩種不同的使用方式下，SQL語言的語法結(jié)構(gòu)基本上是一致的。這種以統(tǒng)一的語法結(jié)構(gòu)提供兩種不同的使用方式的作法，為用戶提供了極大的靈活性與方便性。

第6頁/共119頁第三章關(guān)系數(shù)據(jù)庫語言SQL

簡潔、通用、功能強(qiáng)

SQL語言功能極強(qiáng)，設(shè)計(jì)巧妙，語言簡潔，完成數(shù)據(jù)定義、數(shù)據(jù)操縱、數(shù)據(jù)控制的核心功能只用了9個(gè)動(dòng)詞，如表3-1所示。而且SQL語言語法簡單，接近英語口語，因此容易學(xué)習(xí)，容易使用。SQL功能核心動(dòng)詞數(shù)據(jù)查詢SELECT數(shù)據(jù)定義CREATE,DROP,ALTER數(shù)據(jù)控制INSERT,UPDATE,DELETE數(shù)據(jù)操縱GRANT,REVOKE第7頁/共119頁3.1SQL的特征3.SQL的基本組成

（1）SQL數(shù)據(jù)庫層次結(jié)構(gòu)

存儲(chǔ)文件1存儲(chǔ)文件2SQL視圖1視圖2基本表1基本表2基本表3術(shù)語對(duì)照：一般關(guān)系模型SQL外模式-----視圖（VIEW)模式-----基本表(TABLE)內(nèi)模式-----存儲(chǔ)文件（索引）元組--------行(ROW)屬性--------列(COLUMN)第8頁/共119頁3.1SQL的特征基本表是本身獨(dú)立存在的表，在SQL中一個(gè)關(guān)系就對(duì)應(yīng)一個(gè)表。一些基本表對(duì)應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以帶若干索引，索引也存放在存儲(chǔ)文件中。存儲(chǔ)文件的邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。存儲(chǔ)文件的物理文件結(jié)構(gòu)是任意的。視圖是從基本表或其他視圖中導(dǎo)出的表，它本身不獨(dú)立存儲(chǔ)在數(shù)據(jù)庫中，也就是說數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對(duì)應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在導(dǎo)出視圖的基本表中，因此視圖是一個(gè)虛表。用戶可以用SQL語言對(duì)視圖和基本表進(jìn)行查詢。在用戶眼中，視圖和基本表都是關(guān)系，而存儲(chǔ)文件對(duì)用戶是透明的。

第9頁/共119頁

3.1SQL的特征（2）SQL語言的組成

數(shù)據(jù)定義語言（DDL）數(shù)據(jù)操縱語言（DML）數(shù)據(jù)控制語言（DCL）

嵌入與會(huì)話規(guī)則

（3）SQL的語句類型

SQL模式語句

SQL數(shù)據(jù)語句

SQL事務(wù)與控制語句

SQL連接、會(huì)話及診斷語句

4.SQL的數(shù)據(jù)類型

預(yù)定義數(shù)據(jù)類型構(gòu)造數(shù)據(jù)類型用戶定義數(shù)據(jù)類型（UDT，UserDefinedType）

第10頁/共119頁3.1SQL的特征5.SQL環(huán)境（1）SQL模式與目錄SQL模式：基本表的集合。好處：允許在不同的SQL模式中出現(xiàn)同名的基表名或視圖名。目錄：SQL環(huán)境中所有模式的集合。定位基表的方式：

<目錄名>．<模式名>．<表名>（2）SQL環(huán)境設(shè)置默認(rèn)的目錄和模式設(shè)置用戶身份

第11頁/共119頁3.1SQL的特征（3）存儲(chǔ)過程

存儲(chǔ)過程是存儲(chǔ)在SQL服務(wù)器上的預(yù)編譯好的一組為了完成特定功能的SQL語句集。通過指定存儲(chǔ)過程的名字并給出參數(shù)來執(zhí)行它。可分為兩類：

◆系統(tǒng)存儲(chǔ)過程：由系統(tǒng)自動(dòng)創(chuàng)建，完成的功能主要是從系統(tǒng)表中獲取信息。

◆用戶定義的存儲(chǔ)過程：由用戶為完成某一特定功能而編寫的存儲(chǔ)過程。使用存儲(chǔ)過程的好處：

◆可減少網(wǎng)絡(luò)流量。

◆增強(qiáng)代碼的重用性和共享性。

◆加快系統(tǒng)運(yùn)行速度。

◆保證數(shù)據(jù)安全性。第12頁/共119頁3.2SQL的數(shù)據(jù)定義1.SQL模式的定義與撤銷（1）SQL模式的定義

CREATESCHEMA＜模式名＞AUTHRIZATION＜用戶名＞[＜CREATEDOMAIN子句＞|＜CREATETABLE子句＞|＜CREATEVIEW＞|……]例；CREATESCHEMATeaching_dbAUTHRIZATIONHang；（2）數(shù)據(jù)庫模式的刪除

DROPSCHEMA＜模式名＞{CASCADE|RESTRICT}

CASCADE(級(jí)聯(lián)式)：

RESTRICT（約束式)：

第13頁/共119頁3.2SQL的數(shù)據(jù)定義

2.表的建立和刪除

（1）表的建立命令格式：

CREATETABLE<表名>(<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件][，<列名><數(shù)據(jù)類型>[列級(jí)完整性約束條件]...)[,<表級(jí)完整性約束條件>];

例：CREATETABLEStudent

(snoCHAR(5)NOTNULL

UNIQUE，

snameCHAR(20)NOTNULL

，

sexCHAR(1)，

ageINT，

deptCHAR(15)

)；主碼第14頁/共119頁3.2SQL的數(shù)據(jù)定義完整性約束條件涉及到該表的多個(gè)屬性列，則必須定義在表級(jí)上，否則既可定義在列級(jí)，也可以定義在表級(jí)。注意：

例：定義P.76的三個(gè)表結(jié)構(gòu)，并指定相應(yīng)的數(shù)據(jù)完整性約束條件。分析外碼：sno,cno

主碼：sno姓名：非空性別：男、女兩值Student表：Course表：主碼：cno課程名：非空外碼：pcnoSC表：主碼：(sno,cno)成績：0—100第15頁/共119頁3.2SQL的數(shù)據(jù)定義CREATETABLEStudent

(

snoCHAR(5),snameCHAR(8)NOTNULL,sexCHAR(2),ageSMALLINT,deptCHAR(20),PRIMARYKEY(sno),CHECKsexIN(‘男’,‘女’)

);列級(jí)完整性約束條件實(shí)體完整性約束條件用戶自定義完整性約束條件第16頁/共119頁3.2SQL的數(shù)據(jù)定義CREATETABLECourse

(

cnoCHAR(4),cnameCHAR(10)NOTNULL,pcnoCHAR(4),creditSMALLINT,PRIMARYKEY(cno),FOREIGNKEY(pcno)REFERENCESCourse(cno)

);參照完整性約束條件第17頁/共119頁3.2SQL的數(shù)據(jù)定義CREATETABLESC

(

snoCHAR(5),cnoCHAR(4),gradeSMALLINT,PRIMARYKEY(sno,cno),FOREIGNKEY(sno)REFERENCESStudent(sno),FOREIGNKEY(cno)REFERENCESCourse(cno),CHECK((gradeISNULL)OR(gradeBETWEEN0AND100))

);第18頁/共119頁3.2SQL的數(shù)據(jù)定義（2）表的刪除

格式：DROPTABLE<表名>[CASCADE|RESTRICT]

基本表定義一旦刪除，表中的數(shù)據(jù)、在此表上建立的視圖、索引、觸發(fā)器、斷言都將自動(dòng)被刪除掉。RESTRICT確保只有不具有相關(guān)對(duì)象的表才能被撤銷。例：DROPTABLEStudentCASCADE；第19頁/共119頁3.2SQL的數(shù)據(jù)定義3.基本表的擴(kuò)充和修改

一般格式為：

ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名><完整性約束名>][MODIFY<列名><數(shù)據(jù)類型><數(shù)據(jù)類型>];

其中<表名>指定需要修改的基本表，ADD子句用于增加新列和新的完整性約束條件，DROP子句用于刪除指定的完整性約束條件，MODIFY子句用于修改原有的列定義。（1）在現(xiàn)存表中增加新列

格式：ALTERTABLE<表名>ADD(<列名><數(shù)據(jù)類型>，…)第20頁/共119頁3.2SQL的數(shù)據(jù)定義（2）刪除已存在的某個(gè)列

格式：ALTERTABLE<表名>DROP<列名>[CASCADE|RESTRICT]

例：ALTERTABLEStudentDROPaddr；（3）修改原有列的類型

格式：ALTERTABLE<表名>MODIFY<列名><類型>；例：ALTERTABLEStudentMODIFYplaceCHAR(8)；第21頁/共119頁3.2SQL的數(shù)據(jù)定義（4）補(bǔ)充定義主碼

格式：ALTERTABLE<表名>ADDPRIMARYKEY(<列名表>)（5）刪除主碼

格式：ALTERTABLE<表名>DROPPRIMARYKEY第22頁/共119頁3.2SQL的數(shù)據(jù)定義修改基本表實(shí)例例向Student表增加“入學(xué)時(shí)間”列，其數(shù)據(jù)類型為日期型ALTERTABLEStudentADDScomeDATE;

例將年齡的數(shù)據(jù)類型改為半字長整數(shù)ALTERTABLEStudentMODIFYSageSMALLINT;例刪除關(guān)于學(xué)號(hào)必須取唯一值的約束ALTERTABLEStudentDROPUNIQUE(Sno);

第23頁/共119頁3.2SQL的數(shù)據(jù)定義4.索引的建立與刪除

建立索引的目的：基本表上建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查找速度。

命令格式：

CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名><次序>[[，<列名><次序>]]…)；次序：升序（ASC，缺?。┙敌颍―ESC）

UNIQUE:每一個(gè)索引值只對(duì)應(yīng)惟一的數(shù)據(jù)記錄。

CLUSTER:建立聚簇索引，即索引項(xiàng)的順序與表中記錄的物理順序一致。

第24頁/共119頁3.2SQL的數(shù)據(jù)定義注意：在一個(gè)基本表上最多只能建立一個(gè)聚簇索引。經(jīng)常更新的列不宜建立聚簇索引。所建索引放何處？例:①

為Student表按學(xué)號(hào)升序建惟一聚簇索引。

②為SC表按學(xué)號(hào)升序和課程號(hào)降序建惟一索引。

①

CREATEUNIQUECLUSTERINDEXStno

ONStudent(Sno)；

②

CREATEUNIQUEINDEXScno

ONSC(Sno，CnoDESC)；刪除索引一般格式為：

DROPINDEX<索引名>；例：DROPINDEXStno

；刪除索引時(shí)，系統(tǒng)會(huì)同時(shí)從數(shù)據(jù)字典中

刪去有關(guān)該索引的描述。第25頁/共119頁3.3SQL的數(shù)據(jù)查詢一般形式：SELECT<A>FROM<R>WHERE<F>GROUPBY<列名1>HAVING<條件表達(dá)式>ORDERBY<列名2>[ASC|DESC];基本語句含義:

根據(jù)WHERE中的F，從基表或視圖R中找出滿足條件的元組，再從中選出目標(biāo)屬性值形成結(jié)果表。查詢目標(biāo)數(shù)據(jù)來源選擇元組的條件將結(jié)果按<列名1>的值進(jìn)行分組滿足條件的組才予輸出按<列名2>排序查詢結(jié)果第26頁/共119頁3.3SQL的數(shù)據(jù)查詢?nèi)绻蠫ROUP子句，則將結(jié)果按<列名1>的值進(jìn)行分組，該屬性列值相等的元組為一個(gè)組，每個(gè)組產(chǎn)生結(jié)果表中的一條記錄。通常在成組后再使用集函數(shù)。如果GROUP子句帶HAVING短語，則只有滿足指定條件的組才予輸出。如果有ORDER子句，則結(jié)果表還要按<列名2>的值的升序或降序排序。第27頁/共119頁3.3SQL的數(shù)據(jù)查詢其中：[…]：表示其中的成分為任選項(xiàng)。

<…>：表示其中的成分由用戶具體給定。

|：表示其中并列的成分只能擇一。查詢目標(biāo)：

ALL：表示保留滿足條件的所有元組（缺?。?。

DISTINCT：表示去掉重復(fù)元組。目標(biāo)列：可以為屬性名、表達(dá)式、通配符‘*’（表示所有屬性列）。第28頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢學(xué)生-課程數(shù)據(jù)庫:Student(sno，sname，sex，age，dept)Course(cno，cname，pcno，credit)SC(sno，cno，grade)3.3.1單表查詢

一、選擇表中的列例：查詢所有學(xué)生的姓名、學(xué)號(hào)、所在系。

SELECTsname，sno，deptFROMStudent

（次序不同）;目標(biāo)A來源R條件Fsnamesnodept———————————王蕭虎200101信息院……第29頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢查詢經(jīng)過計(jì)算的值SELECT子句的<目標(biāo)列表達(dá)式>不僅可以是表中的屬性列，也可以是有關(guān)表達(dá)式，即可以將查詢出來的屬性列經(jīng)過一定的計(jì)算后列出結(jié)果。例4查全體學(xué)生的姓名及其出生年份

SELECTSname,2006-SageFROMStudent;例5查全體學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名

SELECTSname,'YearofBirth:',1996-Sage,ISLOWER(Sdept)FROMStudent;第30頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢例：查全體學(xué)生的姓名及其出生年份。

SELECTsname，2007-ageASFROMStudent；來源R條件F目標(biāo)Asname

2005-age———————————王蕭虎

1987……Birthday別名Birthday二、選擇表中的行1消除取值重復(fù)的行例：查考試成績不及格的學(xué)號(hào)。

SELECTsnoFROMSCWHEREgrade<60；DISTINCT第31頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢2.查詢滿足條件的元組查詢滿足指定條件的元組可以通過WHERE子句實(shí)現(xiàn)。WHERE子句常用的查詢條件如下所示。

常用的查詢條件：

查詢條件謂詞比較比較運(yùn)算符確定范圍BETWEENAND,NOTBETWEENAND確定集合IN,NOTIN字符匹配LIKE,NOTLIKE空值ISNULL,ISNOTNULL多重條件AND,OR第32頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢(1)比較（=,<,>…

）

例查計(jì)算機(jī)系全體學(xué)生的姓名

SELECTSname

FROMStudentWHERESdept='CS';

例查所有年齡在20歲以下的學(xué)生姓名及其年齡

SELECTSname,Sage

FROMStudent

WHERESage<20;

或WHERENOTSage>=20;第33頁/共119頁

3.3SQL的數(shù)據(jù)查詢－單表查詢

(2)確定范圍(BETWEEN…AND…)例:

查詢選002號(hào)課程且成績?cè)?0--90的學(xué)生號(hào)。

SELECTsnoFROMSCWHEREsno=‘002’ANDgradeBETWEEN80AND90；

其他表示？關(guān)系運(yùn)算？

(3)確定集合(IN)例：查詢信息院、數(shù)學(xué)系和計(jì)算機(jī)學(xué)院學(xué)生的姓名和性別。

SELECTsname，sexFROMStudentWHEREdeptIN('信息院'，'數(shù)學(xué)系'，'計(jì)算機(jī)學(xué)院')?查詢不是這三個(gè)系的學(xué)生的姓名和性別。

NOT

第34頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢

(4)字符匹配---近似查詢，模糊查詢格式：[NOT]LIKE’<匹配串>’

含義：是查找指定的屬性列值與<匹配串>相匹配的元組。其中匹配串可含：％：代表任意長度(可為0)的字符串。

_：代表任意單個(gè)字符。例：查所有姓劉或姓王的學(xué)生姓名、學(xué)號(hào)和性別。

SELECTsname，sno，sexFROMStudentWHEREsnameLIKE‘劉％’ORsnameLIKE‘王％’；?查詢所有非姓劉或非姓王的學(xué)生姓名、學(xué)號(hào)和性別。NOTNOT第35頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢例查姓“歐陽”且全名為三個(gè)漢字的學(xué)生的姓名SELECTSname

FROMStudent

WHERESnameLIKE‘歐陽__';

注意，由于一個(gè)漢字占兩個(gè)字符的位置，所以匹配串歐陽后面需要跟２個(gè)_。例查名字中第二字為“陽”字的學(xué)生的姓名和學(xué)號(hào)SELECTSname,Sno

FROMStudent

WHERESnameLIKE'__陽%';注意：一個(gè)漢字要占兩個(gè)字符的位置第36頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢

格式2：LIKE’<匹配串>’ESCAPE’<換碼字符>’若要查的串本身就含有％或_，則用ESCAPE‘<換碼字符>’對(duì)通配符進(jìn)行轉(zhuǎn)義。ESCAPE’\’短語表示\為換碼字符，這樣匹配串中緊跟在\后面的字符”_”不再具有通配符的含義，而是取其本身含義，被轉(zhuǎn)義為普通的”_”字符。例：查“DB_”開頭且倒數(shù)第2個(gè)字符為i的課程情況。

SELECT*FROMCourseWHEREcnameLIKE‘DB\

_

％i_’ESCAPE’\’；第37頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢查以”DB_”開頭，且倒數(shù)第三個(gè)字符為i的課程的詳細(xì)情況

SELECT*FROMCourseWHERECnameLIKE’DB_%i__’ESCAPE’&’;

注意這里的匹配字符串’DB\_%i__’。第一個(gè)_前面有換碼字符\，所以它被轉(zhuǎn)義為普通的_字符。而%、第二個(gè)_和第三個(gè)_前面均沒有換碼字符\，所以它們?nèi)宰鳛橥ㄅ浞?。?zhí)行結(jié)果為：CnoCnameCcredit8DB_Design410DB_Programming213

DB_DBMSDesign4&第38頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢(5)涉及空值的查詢例某些學(xué)生選修某門課程后沒有參加考試，所以有選課記錄，但沒有考試成績，下面我們來查一下缺少成績的學(xué)生的學(xué)號(hào)和相應(yīng)的課程號(hào)

SELECTSno,Cno

FROMSC

WHEREGradeISNULL;

注意這里的‘IS’不能用等號(hào)（‘=’）代替。

例查所有有成績的記錄的學(xué)生學(xué)號(hào)和課程號(hào)

SELECTSno,Cno

FROMSC

WHEREGradeISNOTNULL;第39頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢?nèi)?、?duì)查詢結(jié)果排序例:

查詢?nèi)w男學(xué)生的學(xué)號(hào)、系、年齡結(jié)果按所在的系升序排列，同一系中的學(xué)生按年齡降序排列。

來源R

條件F目標(biāo)AStudent表排序二排序一

SELECTsno,dept,ageFROMStudentWHEREsex=‘男’

ORDERBYdept，ageDESC；

Orderby的排序鍵應(yīng)該是查詢清單中的列名第40頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢四、使用集函數(shù)

COUNT([DISTINCT|ALL]*)統(tǒng)計(jì)元組個(gè)數(shù)

COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值的個(gè)數(shù)

SUM([DISTINCT|ALL]<列名>)計(jì)算一數(shù)值型列值的總和

AVG([DISTINCT|ALL]<列名>)計(jì)算一數(shù)值型列值的平均值

MAX([DISTINCT|ALL]<列名>)求一列值的最大值

MIN([DISTINCT|ALL]<列名>)求一列值的最小值缺省值無重復(fù)值第41頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢例:

查詢女學(xué)生的總?cè)藬?shù)和平均年齡。

SELECTCOUNT(sno),AVG(age)FROMStudentWHEREsex=‘女’例:

查詢選修001號(hào)課程并及格的學(xué)生的最高分?jǐn)?shù)、最低分及總分。

SELECTMAX(grade),MIN(grade),SUM(grade)FROMSCWHEREcno=‘001’andgrade>=60

第42頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢五、對(duì)查詢結(jié)果分組：GROUPBY子句將查詢結(jié)果表按某一(多)列值分組，值相等的為一組。目的：細(xì)化集函數(shù)的作用對(duì)象。如果未對(duì)查詢結(jié)果分組，集函數(shù)將作用于整個(gè)查詢結(jié)果，即整個(gè)查詢結(jié)果只有一個(gè)函數(shù)值。否則，集函數(shù)將作用于每一個(gè)組，即每一組都有一個(gè)函數(shù)值。

例查詢各個(gè)課程號(hào)與相應(yīng)的選課人數(shù)

SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;第43頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢?cè)揝ELECT語句對(duì)SC表按Cno的取值進(jìn)行分組，所有具有相同Cno值的元組為一組，然后對(duì)每一組作用集函數(shù)COUNT以求得該組的學(xué)生人數(shù)。查詢結(jié)果為：

Cno

COUNT(Sno)

----------------

1

22

2

34

3

44

4

33

5

48

第44頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢?nèi)绻纸M后還要求按一定的條件對(duì)這些組進(jìn)行篩選，最終只輸出滿足指定條件的組，則可以使用HAVING短語指定篩選條件。

例：求每個(gè)學(xué)生（號(hào)）的平均成績，并將其超過88分的輸出。

SELECTsno，AVG(grade)FROMSCGROUPBYsno；HAVING短語：在各組中選擇滿足條件的小組。WHERE子句：在表中選擇滿足條件的元組。WHERE?HAVINGAVG(grade)>88；第45頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢集函數(shù)在成組之前不計(jì)算，因此不能用于WHERE子句,一般將簡單條件寫入WHERE。HAVING子句的條件運(yùn)算數(shù)之一是一個(gè)集函數(shù)。若HAVING子句無前導(dǎo)GROUPBY，選擇清單中不能含有非集函數(shù)列。第46頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢練習(xí)：查詢至少選修4門課程的學(xué)生學(xué)號(hào)。例：找出選課學(xué)生超過30人的課程的平均成績及選課人數(shù)。

SELECTcno,AVG(grade),COUNT(*)ASst_numberFROMSCGROUPBYsnoHAVINGst_number>=30第47頁/共119頁3.3SQL的數(shù)據(jù)查詢－單表查詢例：求學(xué)生關(guān)系中女生的每一年齡組（超過20人）有多少，要求查詢結(jié)果按人數(shù)升序排列，人數(shù)相同時(shí)按年齡降序排列。

SELECTage,COUNT(sno)ASnumberFROMStudentWHEREsex=‘女’

GROUPBYageHAVINGnumber>20ORDERBYnumber,ageDESC第48頁/共119頁3.3SQL的數(shù)據(jù)查詢3.3.3嵌套查詢

1.子查詢（嵌套查詢）查詢塊：SELECT<A>FROM<R>WHERE<F>子查詢（嵌套查詢）：一個(gè)查詢塊嵌套在另一查詢塊的條件之中。上層的查詢塊又稱為外層查詢或父查詢或主查詢，下層查詢塊又稱為內(nèi)層查詢或子查詢。第49頁/共119頁3.3SQL的數(shù)據(jù)查詢子查詢一般跟在IN、SOME（ANY）、ALL和EXIST等謂詞后面。SQL語言允許多層嵌套查詢。嵌套查詢的求解方法是由里向外處理。即每個(gè)子查詢?cè)谄渖弦患?jí)查詢處理之前求解，子查詢的結(jié)果用于建立其父查詢的查找條件。嵌套查詢使得可以用一系列簡單查詢構(gòu)成復(fù)雜的查詢，從而明顯地增強(qiáng)了SQL的查詢能力。以層層嵌套的方式來構(gòu)造程序正是SQL(StructurredQueryLanguage)中“結(jié)構(gòu)化”的含義所在。第50頁/共119頁3.3SQL的數(shù)據(jù)查詢(1)帶有IN謂詞的子查詢帶有IN謂詞的子查詢是指父查詢與子查詢之間用IN進(jìn)行連接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中。例：查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生的學(xué)號(hào)、姓名、系①確定“劉晨”所在系名

SELECTSdept

FROMStudent

WHERESname=‘劉晨’;

②查找所有在IS系學(xué)習(xí)的學(xué)生。

SELECTSno,Sname,Sdept

FROMStudent

WHERESdept='IS';

子查詢實(shí)現(xiàn)：將第一步查詢嵌入到第二步查詢中，用以構(gòu)造第二步查詢的條件。WHERESdeptIN(SELECT…)

第51頁/共119頁3.3SQL的數(shù)據(jù)查詢例:查詢選修了數(shù)據(jù)庫課程的學(xué)生號(hào)、成績。

SELECTsno，gradeFROMSCWHEREcnoIN

(SELECTcnoFROMCourseWHEREcname=‘?dāng)?shù)據(jù)庫’)；

（2）帶有比較運(yùn)算符的子查詢例:找出年齡超過平均年齡的學(xué)生姓名。SELECTsnameFROMStudentWHEREage＞

(SELECTAVG(age)FROMStudent)；第52頁/共119頁3.3SQL的數(shù)據(jù)查詢（3）帶SOME(ANY)、ALL謂詞的子查詢>SOME

大于子查詢結(jié)果中的某個(gè)值<ALL

小于子查詢結(jié)果中的所有值注意：SOME、ALL必須與關(guān)系比較符同時(shí)使用。例：查詢有一門課程成績?cè)?0分以上的學(xué)生的姓名。

SELECTsnameFROMStudentWHEREsno=SOME(SELECTDISTINCTsnoFROMSCWHEREgrade>90);第53頁/共119頁3.3SQL的數(shù)據(jù)查詢例：找出(平均)成績最高的學(xué)生號(hào)。

SELECTsnoFROMSCGROUPBYsnoHAVINGAVG(grade)>=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)；SELECTsnoFROMSCWHEREAVG(grade)>=ALL(SELECTAVG(grade)FROMSCGROUPBYsno)???第54頁/共119頁3.3SQL的數(shù)據(jù)查詢例：查詢其他系中比IS系任一學(xué)生年齡大的學(xué)生名單SELECTSname,Sage

FROMStudent

WHERESage>

(SELECTMAX(Sage)

FROMStudent

WHERESdept='IS')

ANDSdept<>'IS'第55頁/共119頁3.3SQL的數(shù)據(jù)查詢

SELECTStudent.sno，sname，sex，age，

dept，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.sno；

2.條件連接查詢

連接條件的一般格式為：

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>當(dāng)連接運(yùn)算符為=時(shí)，稱為等值連接。例:查詢每個(gè)學(xué)生的情況及其選課成績。第56頁/共119頁3.3SQL的數(shù)據(jù)查詢例：找出系為信息系，選課成績?yōu)?0分以上的學(xué)生的姓名、課號(hào)和成績。

SELECTsname，cno，gradeFROMStudent，SCWHEREStudent.sno=SC.snoANDdept=‘信息系’

ANDgrade＞＝90；第57頁/共119頁3.3SQL的數(shù)據(jù)查詢例:查詢選修了數(shù)據(jù)庫的學(xué)生號(hào)、成績

SELECTsno，gradeFROMSCWHEREcnoIN

(SELECTcnoFROMCourseWHEREcname=‘?dāng)?shù)據(jù)庫’)；學(xué)分？SELECTsno，gradeFROMSC，CourseWHERESC.cno=CoANDcname=‘?dāng)?shù)據(jù)庫’；，Ccredit=子查詢連接查詢第58頁/共119頁3.3SQL的數(shù)據(jù)查詢例：按平均成績的降序給出所有課程都及格的學(xué)生（號(hào)、名）及其平均成績，其中成績統(tǒng)計(jì)時(shí)不包括008號(hào)考查課。

SELECTStudent.sno，sname，AVG(grade)ASavg_gFROMStudent，SCWHEREStudent.sno=SC.snoANDcno<>’008’GROUPBYsnoHAVINGMIN(grade)>=60ORDERBYavg_gDESC；

第59頁/共119頁3.3SQL的數(shù)據(jù)查詢3.自身連接查詢例：找出年齡比“王迎”同學(xué)大的同學(xué)的姓名及年齡。SELECTs1.sname，s1.ageFROMSASs1，SASs2WHEREs1.age>s2.ageANDs2.sname=‘王迎’；

其他方法？練習(xí)：查詢每一門課的間接先修課（即先修課的先修課）。SELECTFIRST.cno,SECOND.pcnoFROMCourseFIRST,CourseSECONDWHEREFIRST.pcno=SECOND.cno;第60頁/共119頁3.3SQL的數(shù)據(jù)查詢4.FROM子句中的子查詢?cè)贔ROM子句中可以使用子查詢，其查詢的結(jié)果表稱為導(dǎo)出關(guān)系（DerivedRelation）。例：查出選課成績?cè)?0分以上的女學(xué)生的姓名、課程名和成績。SELECTsname，cname，gradeFROM(SELECTsname，cname，gradeFROMStudent，SC，CourseWHEREStudent.sno=SC.snoANDSC.cno=CoANDsex＝'女')

ASTemp(sname，cname，grade)WHEREgrade>=80；

導(dǎo)出關(guān)系第61頁/共119頁3.3SQL的數(shù)據(jù)查詢5.相關(guān)子查詢當(dāng)一個(gè)子查詢的判斷條件涉及到一個(gè)來自外部查詢的列時(shí)，稱為相關(guān)子查詢。

帶存在謂詞的子查詢：只產(chǎn)生邏輯值存在謂詞EXISTS作用:若內(nèi)層查詢結(jié)果非空，則外層的WHERE子句返回真值，否則返回假值。求解相關(guān)子查詢不能象求解不相關(guān)子查詢那樣，一次將子查詢求解出來，然后求解父查詢。相關(guān)子查詢的內(nèi)層查詢由于與外層查詢有關(guān)，因此必須反復(fù)求值。

第62頁/共119頁3.3SQL的數(shù)據(jù)查詢例：查詢所有選修了1號(hào)課程的學(xué)生姓名。

SELECTsnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.Sno

ANDcno=’1’)；分析：查詢所有選修了1號(hào)課程的學(xué)生姓名涉及Student關(guān)系和SC關(guān)系，在Student關(guān)系中依次取每個(gè)元組的Sno值，用此Student.Sno值去檢查SC關(guān)系，若SC中存在這樣的元組：其SC.Sno值等于用來檢查的Student.Sno值，并且其SC.Cno=‘1’，則取此Student.Sname送入結(jié)果關(guān)系。第63頁/共119頁3.3SQL的數(shù)據(jù)查詢相關(guān)子查詢的一般處理過程是：

首先取外層查詢中Student表的第一個(gè)元組，根據(jù)它與內(nèi)層查詢相關(guān)的屬性值（即Sno值）處理內(nèi)層查詢，若WHERE子句返回值為真（即內(nèi)層查詢結(jié)果非空），則取此元組放入結(jié)果表；然后再檢查Student表的下一個(gè)元組；重復(fù)這一過程，直至Student表全部檢查完畢為止。

其它方法？優(yōu)點(diǎn)？第64頁/共119頁3.3SQL的數(shù)據(jù)查詢SQL語言中沒有全稱量詞∨（Forall）。因此對(duì)于求所有的操作，必須利用謂詞演算將一個(gè)帶有全稱量詞的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞。SQL語言中也沒有蘊(yùn)涵（Implication）邏輯運(yùn)算。因此也必須利用謂詞演算將一個(gè)邏輯蘊(yùn)函的謂詞轉(zhuǎn)換為等價(jià)的帶有存在量詞的謂詞。第65頁/共119頁3.3SQL的數(shù)據(jù)查詢例:查詢選修了全部課程的學(xué)生姓名。轉(zhuǎn)換成：查詢這樣的學(xué)生，沒有一門課程是他不選修的。

(x)P≡(x(P))

SELECTsnameFROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS(SELECT*FROMSCWHEREsno=Student.snoANDcno=Co))；每門課都沒選某學(xué)生選某課第66頁/共119頁3.3SQL的數(shù)據(jù)查詢練習(xí)：查詢至少選修了學(xué)號(hào)為200101的學(xué)生選修的全部課程的學(xué)生學(xué)號(hào)。分析：查詢這樣的學(xué)生，凡是200101選修的課，他都選修了。換句話說，若有一個(gè)學(xué)號(hào)為x的學(xué)生，對(duì)所有的課程y，只要學(xué)號(hào)為200101的學(xué)生選修了課程y，則x也選修了y；那么就將他的學(xué)號(hào)選出來。它所表達(dá)的語義為：不存在這樣的課程y，學(xué)生200101選修了y，而學(xué)生x沒有選。第67頁/共119頁3.3SQL的數(shù)據(jù)查詢SELECTsnoFROMStudentXWHERENOTEXISTS(SELECT*FROMSCYWHEREY.sno=‘200101’ANDNOTEXISTS(SELECT*FROMSCZWHEREX.sno=Z.snoANDY.cno=Z.cno))第68頁/共119頁3.3SQL的數(shù)據(jù)查詢6.集合運(yùn)算（1）UNION

（并）（2）INTERSECT

（交）（3）EXCEPT

（差）例：求選修了001或002號(hào)而沒有選003號(hào)課程的學(xué)生號(hào)。(SELECTsnoFROMSCWHEREcno=’001’ORcno=’002’)EXCEPT(SELECTsnoFROMSCWHEREcno=‘003’)；第69頁/共119頁3.3SQL的數(shù)據(jù)查詢3.3.4自然連接與外連接

1.自然連接(NATURALJOIN)例：查系別為信息、課程成績?cè)?0分以上的學(xué)生檔案及其成績情況。

SELECT*FROMStudentNATURALJOINSCWHEREdept=‘信息’ANDgrade>=90；2.

外連接左外連接。LEFT[OUTER]JOIN，保留左關(guān)系的所有元組。右外連接。RIGHT[OUTER]JOIN，保留右關(guān)系的所有元組。全外連接。FULL[OUTER]JOIN，保留左右兩關(guān)系的所有元組。

FROMSC,Student?自然連接的定義？第70頁/共119頁3.3SQL的數(shù)據(jù)查詢

教師表：Teacher(教師號(hào)，姓名，所屬大學(xué)，職稱)

任職表：Post(編號(hào)，姓名，職務(wù))

例：SELECT*FROMTeacherFULLOUTERJOINPost；例：SELECT*FROMTecher

LEFTOUTERJOINPost；例：SELECT*FROMTecherRIGHTOUTERJOINPost；第71頁/共119頁3.3SQL的數(shù)據(jù)查詢3.3.5SQL中的空值處理1.空值的含義值暫未知。值不適用。值需隱瞞。2.空值的若干規(guī)則（1）空值與數(shù)值型數(shù)據(jù)進(jìn)行算術(shù)運(yùn)算，結(jié)果為空值。（2）空值和任何值(包括空值)進(jìn)行比較運(yùn)算，結(jié)果為UNKNOWN。

第72頁/共119頁3.3SQL的數(shù)據(jù)查詢（3）空值的布爾運(yùn)算UANDT=U；UANDF=F；UANDU=U；UORT=T；UORF=U；UORU=U；

NOTU=U?？罩档臏y(cè)試：ISNULL和ISNOTNULL。例：查詢?nèi)鄙龠x課成績的學(xué)生號(hào)和相應(yīng)的課程號(hào)。

SELECTSno，CnoFROMSCWHEREgradeISNULL；第73頁/共119頁3.3SQL的數(shù)據(jù)查詢3.3.6遞歸合并查詢

遞歸合并語句格式：WITHRECURSIVE<臨時(shí)表R>AS<R的定義>UNION<涉及到R的查詢><遞歸結(jié)果查詢>例：對(duì)表3.6所示的航班表Flight(airline，from，to，depart，arrive)，求出：能從一個(gè)城市飛到另一個(gè)城市的城市對(duì)集合(含直接到達(dá)和間接中轉(zhuǎn)到達(dá))。第74頁/共119頁3.3SQL的數(shù)據(jù)查詢

直接到達(dá)：

Reach(f，t)=Flight(a，f，t，d，r)f，t在表的同一個(gè)元組中。間接到達(dá)：Reach(f，t)=Flight(a，f，t1，d，r)ANDReach(t1，t)

SQL語句：WITHRECURSIVEReach(from，to)AS(SELECTfrom，to//選出直接到達(dá)城市對(duì)

FROMFlight)UNION(SELECTFlight.from，Reach.to//選出間接到達(dá)城市對(duì)

FROMFlight，Reach//遞歸

WHEREFlight.to=Reach.from)SELECT*FROMReach；

第75頁/共119頁3.3SQL的數(shù)據(jù)查詢例：有下列所示的一棵二叉樹：abecfd162453id

pidname1NULLa21b31c42d53e63f求出:該子樹中除子樹根結(jié)點(diǎn)k以外的所有子孫結(jié)點(diǎn)的集合。

第76頁/共119頁3.3SQL的數(shù)據(jù)查詢分析：定義結(jié)果集為Tsub(id，name)：結(jié)點(diǎn)k的所有子女：Tsub(id，name)=Tree(id，pid，name)ANDpid=k結(jié)點(diǎn)k的所有子女的孩子（k的子孫）：Tsub(id，name)=Tsub(id1，name1)ANDTree(id，id1，name)SQL:WITHRECURSIVETsub(id，name)AS(SELECTid，nameFROMTree//結(jié)點(diǎn)k的所有子女

WHEREpid=k)UNION(SELECTTree.id，TFROMTsub，Tree//結(jié)點(diǎn)k的所有子孫

WHERETsub.id=Tree.pid)

SELECT*FROMTsub；

第77頁/共119頁3.3SQL的數(shù)據(jù)查詢視圖的概念及作用

是從一個(gè)或幾個(gè)基本表(或視圖)導(dǎo)出的表，是虛表。與表一樣可被查詢。對(duì)視圖的更新操作有一定的限制。對(duì)視圖的一切操作最終將轉(zhuǎn)換為對(duì)基本表的操作。

視圖的作用：（1）簡化結(jié)構(gòu)及復(fù)雜操作。

（2）多角度地、更靈活地共享。

（3）提高邏輯獨(dú)立性。

（4）提供安全保護(hù)。

S#SNSUM虛表S#SNAVG…實(shí)表S#SUM…第78頁/共119頁3.4SQL的數(shù)據(jù)更新一.插入數(shù)據(jù)1.插入單個(gè)元組格式：INSERTINTO<表名>[（<屬性列1>[，<屬性列2>…)]

VALUES(<常量1>[,<常量2>]…);說明：（1）若插入全部屬性，則屬性列可省略。（2）表定義中說明為NOTNULL的列不能取空值。

（3）屬性值與相對(duì)應(yīng)的屬性列的數(shù)據(jù)類型要匹配。（4）向參照表中插入元組，關(guān)系系統(tǒng)自動(dòng)支持：

·

實(shí)體完整性

·

參照完整性第79頁/共119頁3.4SQL的數(shù)據(jù)更新2.

插入子查詢結(jié)果格式：INSERTINTO<表名>[(<屬性列1>[,<屬性列2>…)]

子查詢；二.修改數(shù)據(jù)格式：UPDATE<表名>SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]…[WHERE<條件>];三.刪除數(shù)據(jù)格式：DELETEFROM<表名>[WHERE<條件>];第80頁/共119頁3.4SQL的數(shù)據(jù)更新四.構(gòu)造數(shù)據(jù)類型

1.聚合類型

<數(shù)據(jù)類型>ARRAY[<無符號(hào)整數(shù)>]例：CREATETABLEmailout(nameCHAR(8)，

addressCHAR(20)ARRAY[3])；INSERTINTOTABLEmailout(name，address)VALUES('Wang'，ARRAY['20HongsangRoad'，'Wuhan'，'430079'])；第81頁/共119頁3.4SQL的數(shù)據(jù)更新2.行類型

格式：

ROW(<列名><數(shù)據(jù)類型>[{，<列名><數(shù)據(jù)類型>}…]例：CREATETABLElineage(nameCHAR(8)，

statusCHAR(10)，

lastoneROW(babyCHAR(8)，birthDATE))；……

//插入數(shù)據(jù)操作

SELECTname，status，lastoneFROMlineage

WHERElastone.baby='Liming'；作為一個(gè)整體第82頁/共119頁

3.5SQL的視圖

創(chuàng)建與使用視圖1.創(chuàng)建視圖一般格式：

CREATEVIEW<視圖名>[(<視圖列名>[,<視圖列名>]…)]AS<子查詢>[WITHCHECKOPTION]；其中：子查詢可以是任意復(fù)雜的SELECT語句，但通常不允許含有ORDERBY子句和DISTINCT短語。

WITHCHECKOPTION表示對(duì)視圖進(jìn)行UPDATE、INSERT和DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）。

作用：命名一個(gè)視圖，AS子句定義每次查看視圖時(shí)將看到的數(shù)據(jù)，在任何時(shí)刻，視圖的數(shù)據(jù)由對(duì)其查詢定義求值的結(jié)果行構(gòu)成。第83頁/共119頁3.5SQL的視圖例1：建立信息院學(xué)生的視圖，

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；例2：建立信息院學(xué)生的視圖，

要求進(jìn)行更新時(shí)仍保證該視圖只有信息院的學(xué)生。

CREATEVIEWD_StudentASSELECTsno，sname，ageFROMStudentWHEREdept=‘信息院’；

WITHCHECKOPTION第84頁/共119頁3.5SQL的視圖視圖不僅可以建立在一個(gè)或多個(gè)基本表上，也可以建立在一個(gè)或多個(gè)已定義好的視圖上，或同時(shí)建立在基本表與視圖上。

例3建立信息系選修了1號(hào)課程的學(xué)生的視圖CREATEVIEWIS_S1(Sno,Sname,Grade)

AS

SELECTStudent.Sno,Sname,Grade

FROMStudent,SC

WHERESdept='IS'AND

Student.Sno=SC.SnoAND

SC.Cno='1';第85頁/共119頁3.5SQL的視圖例4建立信息系選修了1號(hào)課程且成績?cè)?0分以上的學(xué)生的視圖

CREATEVIEWIS_S2

AS

SELECTSno,Sname,Grade

FROMIS_S1

WHEREGrade>=90;

這里的視圖IS_S2就是建立在視圖IS_S1之上的。

第86頁/共119頁3.5SQL的視圖帶虛擬列的視圖：是指設(shè)置了一些基本表中并不存在的派生列(虛擬列)的視圖。定義基本表時(shí)，為了減少數(shù)據(jù)庫中的冗余數(shù)據(jù)，表中只存放基本數(shù)據(jù)，由基本數(shù)據(jù)經(jīng)過各種計(jì)算派生出的數(shù)據(jù)一般是不存儲(chǔ)的。但由于視圖中的數(shù)據(jù)并不實(shí)際存儲(chǔ)，所以定義視圖時(shí)可以根據(jù)應(yīng)用的需要，設(shè)置一些派生屬性列。這些派生屬性由于在基本表中并不實(shí)際存在，所以有時(shí)也稱他們?yōu)樘摂M列，帶虛擬列的視圖也稱為帶表達(dá)式的視圖。

例5定義一個(gè)反映學(xué)生出生年份的視圖

CREATEVIEWBT_S(Sno,Sname,Sbirth)

ASSELECTSno,Sname,1996-Sage

FROMStudent;第87頁/共119頁3.5SQL的視圖分組視圖：帶有集函數(shù)和GROUPBY子句查詢所定義的視圖。例6將學(xué)生的學(xué)號(hào)及他的平均成績定義為一個(gè)視圖假設(shè)SC表中“成績”列Grade為數(shù)字型，否則無法求平均值。

CREATVIEWS_G(Sno,Gavg)

ASSELECTSno,AVG(Grade)

FROMSC

GROUPBYSno;例7將Student表中所有女生記錄定義為一個(gè)視圖

CREATEVIEWF_Student(stdnum,name,sex,age,dept)

ASSELECT*

FROMStudent

WHERESsex='女';第88頁/共119頁3.5SQL的視圖

刪除視圖

DROPVIEW<視圖名>[CASCADE|RESTRICT]

注意：視圖刪除后，視圖的定義將從數(shù)據(jù)字典中自動(dòng)刪除。但要?jiǎng)h除該視圖導(dǎo)出的其他視圖應(yīng)用CASCADE。作用：撤銷一個(gè)視圖，但不消除數(shù)據(jù)，在基礎(chǔ)表中的數(shù)據(jù)仍然保留。

RESTRICT確保只有不具有相關(guān)對(duì)象的視圖（不涉及任何的約束、斷言、觸發(fā)器）才能被撤消。第89頁/共119頁3.5SQL的視圖查詢視圖視圖定義后，用戶就可以象對(duì)基本表進(jìn)行查詢一樣對(duì)視圖進(jìn)行查詢了。

DBMS執(zhí)行對(duì)視圖的查詢時(shí)，首先進(jìn)行有效性檢查，檢查查詢涉及的表、視圖等是否在數(shù)據(jù)庫中存在，如果存在，則從數(shù)據(jù)字典中取出查詢涉及的視圖的定義，把定義中的子查詢和用戶對(duì)視圖的查詢結(jié)合起來，轉(zhuǎn)換成對(duì)基本表的查詢，然后再執(zhí)行這個(gè)經(jīng)過修正的查詢。將對(duì)視圖的查詢轉(zhuǎn)換為對(duì)基本表的查詢的過程稱為視圖的消解（ViewResolution）。

第90頁/共119頁3.5SQL的視圖例1在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生SELECTSno,Sage

FROMIS_Student

WHERESage<20;

視圖是定義在基本上的虛表，它可以和其他基本表一起使用，實(shí)現(xiàn)連接查詢或嵌套查詢。例2查詢信息系選修了1號(hào)課程的學(xué)生SELECTSno,Sname

FROMIS_Student,SC

WHEREIS_Student.Sno=SC.SnoAND

SC.Cno='1';

本查詢涉及虛表IS_Student和基本表SC，通過這兩個(gè)表的連接來完成用戶請(qǐng)求。第91頁/共119頁3.5SQL的視圖

更新視圖

最終要轉(zhuǎn)換為對(duì)基本表的更新。注意：并不是所有的視圖都可以更新的。

P.133—P.134視圖更新規(guī)定第92頁/共119頁3.5SQL的數(shù)據(jù)控制由DBMS提供統(tǒng)一的數(shù)據(jù)控制功能是數(shù)據(jù)庫系統(tǒng)的特點(diǎn)之一。數(shù)據(jù)控制亦稱為數(shù)據(jù)保護(hù)，包括數(shù)據(jù)的安全性控制(連接第九章數(shù)據(jù)庫安全性)、完整性控制(連接第十章數(shù)據(jù)庫完整性)、并發(fā)控制和恢復(fù)(連接第八章并發(fā)控制)。這里主要介紹SQL的數(shù)據(jù)控制功能。SQL語言提供了數(shù)據(jù)控制功能，能夠在一定程度上保證數(shù)據(jù)庫中數(shù)據(jù)的安全性、完整性，并提供了一定的并發(fā)控制及恢復(fù)能力。第93頁/共119頁3.5SQL的數(shù)據(jù)控制數(shù)據(jù)庫的完整性是指數(shù)據(jù)庫中數(shù)據(jù)的正確性與相容性。SQL語言定義完整性約束條件的功能主要體現(xiàn)在CREATETABLE語句中，可以在該語句中定義碼、取值唯一的列、參照完整性及其他一些約束條件。

并發(fā)控制指的是當(dāng)多個(gè)用戶并發(fā)地對(duì)數(shù)據(jù)庫進(jìn)行操作時(shí)，對(duì)他們加以控制、協(xié)調(diào)，以保證并發(fā)操作正確執(zhí)行，并保持?jǐn)?shù)據(jù)庫的一致性。恢復(fù)指的是當(dāng)發(fā)生各種類型的故障，使數(shù)據(jù)庫處于不一致狀態(tài)時(shí)，將數(shù)據(jù)庫恢復(fù)到一致狀態(tài)的功能。SQL語言也提供了并發(fā)控制及恢復(fù)的功能，支持事務(wù)、提交、回滾等概念。數(shù)據(jù)庫的安全性是指保護(hù)數(shù)據(jù)庫，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。數(shù)據(jù)庫系統(tǒng)中保證數(shù)據(jù)安全性的主要措施是進(jìn)行存取控制，即規(guī)定不同用戶對(duì)于不同數(shù)據(jù)對(duì)象所允許執(zhí)行的操作，并控制各用戶只能存取他有權(quán)存取的數(shù)據(jù)。不同的用戶對(duì)不同的數(shù)據(jù)應(yīng)具有何種操作權(quán)力，是由DBA和表的建立者（即表的屬主）根據(jù)具體情況決定的，SQL語言則為DBA和表的屬主定義與回收這種權(quán)力提供了手段。第94頁/共119頁3.5SQL的數(shù)據(jù)控制1.授權(quán)語句SQL語言用GRANT語句向用戶授予操作權(quán)限，GRANT語句的一般格式為：

GRANT<權(quán)限>[,<權(quán)限>]...

[ON<對(duì)象類型><對(duì)象名>]

TO<用戶>[,<用戶>]...

[WITHGRANTOPTION];

其語義為：將對(duì)指定操作對(duì)象的指定操作權(quán)限授予指定的用戶。接受權(quán)限的用戶可以是一個(gè)或多個(gè)具體用戶，也可以是PUBLIC即全體用戶。

如果指定了WITHGRANTOPTION子句，則獲得某種權(quán)限的用戶還可以把這種權(quán)限再授予別的用戶。如果沒有指定WITHGRANTOPTION子句，則獲得某種權(quán)限的用戶只能使用該權(quán)限，但不能傳播該權(quán)限。第95頁/共119頁3.5SQL的數(shù)據(jù)控制例1把查詢Student表權(quán)限授給用戶U1GRANTSELECTONTABLEStudentTOU1;

例2把對(duì)Student表和Course表的全部權(quán)限授予用戶U2和U3GRANTALLPRIVILIGESONTABLEStudent,CourseTOU2,U3;

例3把對(duì)表SC的查詢權(quán)限授予所有用戶GRANTSELECTONTABLESCTOPUBLIC;

例4把查詢Student表和修改學(xué)生學(xué)號(hào)的權(quán)限授給用戶U4GRANTUPDATE(Sno),SELECTONTABLEStudentTOU4;

這里實(shí)際上要授予U4用戶的是對(duì)基本表Student的SELECT權(quán)限和對(duì)屬性列Sno的UPDATE權(quán)限。授予關(guān)于屬性列的權(quán)限時(shí)必須明確指出相應(yīng)屬性列名。

第96頁/共119頁3.5SQL的數(shù)據(jù)控制例5把對(duì)表SC的INSERT權(quán)限授予U5用戶，并允許他再將此權(quán)限授予其他用