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

第四章SQL語言第四章SQL4.1SQL概述4.2示例數(shù)據(jù)庫4.3SQL數(shù)據(jù)定義功能4.4SQL數(shù)據(jù)查詢功能4.5SQL數(shù)據(jù)修改功能4.6SQL數(shù)據(jù)控制功能4.7嵌入式SQL及存儲過程4.8ODBC編程4.1SQL概述歷史1974年，由Boyce和Chamber提出。1975-1979年，在SystemR上實現(xiàn)，由IBM的SanJose研究室研制，稱為Sequel，現(xiàn)在稱為SQL(StruceuredQueryLanguang),結(jié)構(gòu)化查詢語言，是關(guān)系數(shù)據(jù)庫的標準語言;SQL是一個通用的、功能極強的關(guān)系數(shù)據(jù)庫語言。4.1SQL概述有關(guān)標準SQL-86：“數(shù)據(jù)庫語言SQL”，1986.10SQL-89：“具有完整性增強的數(shù)據(jù)庫語言SQL”，增加了對完整性約束的支持。1989年SQL-92：“數(shù)據(jù)庫語言SQL”，是SQL-89的超集，增加了許多新特性，如新的數(shù)據(jù)類型，更豐富的數(shù)據(jù)操作，更強的完整性、安全性支持等。1992年SQL-3：又稱SQL-99，增加對面向?qū)ο竽Ｐ偷闹С帧?999年SQL20032003年4.1SQL概述SQL語言的特點1.綜合統(tǒng)一集數(shù)據(jù)定義語言（DDL），數(shù)據(jù)操縱語言（DML），數(shù)據(jù)控制語言（DCL）功能于一體?？梢元毩⑼瓿蓴?shù)據(jù)庫生命周期中的全部活動：定義關(guān)系模式，插入數(shù)據(jù)，建立數(shù)據(jù)庫；對數(shù)據(jù)庫中的數(shù)據(jù)進行查詢和更新；數(shù)據(jù)庫重構(gòu)和維護數(shù)據(jù)庫安全性、完整性控制等用戶數(shù)據(jù)庫投入運行后，可根據(jù)需要隨時逐步修改模式，不影響數(shù)據(jù)的運行。單一的結(jié)構(gòu)----關(guān)系，數(shù)據(jù)操作符統(tǒng)一4.1SQL概述2.高度非過程化非關(guān)系數(shù)據(jù)模型的數(shù)據(jù)操縱語言“面向過程”，必須制定存取路徑用戶只需提出“做什么”，無須告訴“怎么做”，不必了解存取路徑。存取路徑的選擇以及SQL的操作過程由系統(tǒng)自動完成。4.1SQL概述3.面向集合的操作方式非關(guān)系數(shù)據(jù)模型采用面向記錄的操作方式，操作對象是一條記錄SQL采用集合操作方式操作對象、查找結(jié)果可以是元組的集合一次插入、刪除、更新操作的對象可以是元組的集合4.1SQL概述4.以同一種語法結(jié)構(gòu)提供多種使用方式SQL是獨立的語言

能夠獨立地用于聯(lián)機交互的使用方式SQL又是嵌入式語言

SQL能夠嵌入到高級語言（例如C，C++，Java）程序中，供程序員設(shè)計程序時使用4.1SQL概述5.語言簡潔，易學(xué)易用

SQL功能極強，完成核心功能只用了9個動詞。4.1SQL概述SQL語言的基本概念1.SQL支持關(guān)系數(shù)據(jù)庫三級模式結(jié)構(gòu)。SQL視圖2視圖1基本表2基本表1基本表3基本表4存儲文件2存儲文件1外模式模式內(nèi)模式4.1SQL概述2.基本表本身獨立存在的表SQL中一個關(guān)系就對應(yīng)一個基本表一個(或多個)基本表對應(yīng)一個存儲文件一個表可以帶若干索引3.存儲文件邏輯結(jié)構(gòu)組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式物理結(jié)構(gòu)是任意的，對用戶透明4.視圖從一個或幾個基本表導(dǎo)出的表數(shù)據(jù)庫中只存放視圖的定義而不存放視圖對應(yīng)的數(shù)據(jù)視圖是一個虛表用戶可以在視圖上再定義視圖4.2示例數(shù)據(jù)庫DEPT(DNO,DNAME,DEAN)—系表S(SNO,SNAME,SEX,AGE,DNO,ADRESS)—學(xué)生表COURSE(CNO,CN,PCNO,CREDIT,GRADE)–-課程表SC(SNO,CNO,SCORE)–-學(xué)生選課表PROF(PNO,PNAME,AGE,DNO,SAL)–-教師表PC(PNO,CNO)–-教師授課表學(xué)生成績及教師任課數(shù)據(jù)庫4.3SQL數(shù)據(jù)定義功能4.3.1數(shù)據(jù)庫的建立與撤消4.3.2域定義4.3.3基本表的定義4.3.4索引的定義4.3.5數(shù)據(jù)庫的建立與撤消4.3.6SQL數(shù)據(jù)定義特點4.3SQL數(shù)據(jù)定義功能SQL的數(shù)據(jù)定義功能:域定義、表定義、視圖和索引的定義。4.3.1數(shù)據(jù)庫的建立與撤消有的數(shù)據(jù)庫系統(tǒng)支持多庫。建立一個新數(shù)據(jù)庫

createdatabase

數(shù)據(jù)庫名示例:createdatabasestudent指定當(dāng)前數(shù)據(jù)庫

database

數(shù)據(jù)庫名關(guān)閉當(dāng)前數(shù)據(jù)庫

closedatabase

數(shù)據(jù)庫名撤消一個數(shù)據(jù)庫

dropdatabase

數(shù)據(jù)庫名示例:dropdatabasestudent4.3.2域定義域類型（SQL-92）

SQL中域的概念用數(shù)據(jù)類型來實現(xiàn)，定義表的屬性時需要指明其數(shù)據(jù)類型及長度。char（n）：固定長度的字符串。varchar（n）：可變長字符串。int：整數(shù)。smallint：小整數(shù)類型。numeric（p，d）：定點數(shù)，小數(shù)點左邊p位，右邊q位。real：浮點數(shù)，取決于機器精度。doubleprecision：雙精度浮點數(shù)，取決于機器精度。date：日期（年、月、日）。time：時間（小時、分、秒）。interval：兩個date或time類型數(shù)據(jù)之間的差。4.3.2域定義域定義格式

createdomain

域名數(shù)據(jù)類型示例createdomain

person-namechar（20）

類似C語言中：

typedef

ADDRESS_LIST{ charname[10]; chartelephone[20]; charlocation[20] charemail[20]};

ADDRESS_LISTtom;4.3.3基本表的定義基本表的定義（CREATE）createtable

表名（列名數(shù)據(jù)類型[default

缺省值][notnull][，列名數(shù)據(jù)類型[default缺省值][notnull]]…… [，primarykey（列名[，列名]…）] [，foreignkey（列名[，列名]…）

references

表名（列名[，列名]…）] [，check（條件）]）注：如果完整性約束條件涉及到該表的多個屬性列，則必須定義在表級上，否則既可以定義在列級也可以定義在表級。4.3.3基本表的定義示例1建立“學(xué)生”表S，學(xué)號是主碼，姓名取值唯一。

CREATETABLES (SnoCHAR(9)PRIMARYKEY，/*列級完整性約束條件*/

SnameCHAR(20)UNIQUE，/*Sname取唯一值*/

sexCHAR(2)，ageSMALLINT，

DNOCHAR(20))；4.3.3基本表的定義示例2

createdomainperson_namechar(20)

createtablePROF (PNOchar(10), PNAMEperson_namenotnull, SAL int, AGEint,

DNOchar(10),

primarykey

(PNO),

foreignkey

(DNO)references

DEPT(DNO),

check(SAL>0))4.3.3基本表的定義示例3建立一個“學(xué)生選課”表SC

CREATETABLESC (SnoCHAR(9)，

CnoCHAR(4)，

SCORESMALLINT，

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

FOREIGNKEY(Sno)REFERENCESS(Sno)，/*表級完整性約束條件，Sno是外碼，被參照表是S*/

FOREIGNKEY(Cno)REFERENCESCourse(Cno)/*表級完整性約束條件，Cno是外碼，被參照表是Course*/ );4.3.3基本表的定義修改基本表定義（ALTER）格式：

ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束]]--增加新列[DROP<完整性約束名>]--刪除列[ALTERCOLUMN<列名><數(shù)據(jù)類型>]；--修改列定義

4.3.3基本表的定義示例1向prof表增加“l(fā)ocation”列，字符類型為字符型。

altertablePROF

add

LOCATIONchar(30)

不論基本表中原來是否已有數(shù)據(jù)，新增加的列的值一律為空值。示例2將年齡的數(shù)據(jù)類型由字符型（假設(shè)原來的數(shù)據(jù)類型是字符型）改為整數(shù)。

ALTERTABLESALTERCOLUMNageINT；示例3增加課程名稱必須取唯一值的約束條件。

ALTERTABLECourseADDUNIQUE(Cname);4.3.3基本表的定義撤消基本表定義（drop）格式

droptable

表名［RESTRICT|CASCADE］；RESTRICT：刪除表是有限制的。欲刪除的基本表不能被其他表的約束所引用如果存在依賴該表的對象，則此表不能被刪除CASCADE：刪除該表沒有限制。在刪除基本表的同時，相關(guān)的依賴對象一起刪除

危險

撤消基本表后，基本表的定義、表中數(shù)據(jù)、索引、以及由此表導(dǎo)出的視圖的定義都被刪除。4.3.3基本表的定義示例1刪除S表

DROPTABLEStudentCASCADE

;基本表定義被刪除，數(shù)據(jù)被刪除表上建立的索引、視圖、觸發(fā)器等一般也將被刪除

示例2若表上建有視圖，選擇RESTRICT時表不能刪除

CREATEVIEWIS_Student AS SELECTSno，Sname，Sage FROMS WHERESdept='IS'； DROPTABLEStudentRESTRICT;--ERROR:cannotdroptableStudentbecauseotherobjectsdependonit4.3.3基本表的定義示例3如果選擇CASCADE時可以刪除表，視圖也自動被刪除

DROPTABLESCASCADE;

--NOTICE:dropcascadestoviewIS_StudentSELECT*FROMIS_Student;--ERROR:relation"IS_Student"doesnotexist4.3.3基本表的定義DROPTABLE時，SQL99與3個RDBMS的處理策略比較序號標準及主流數(shù)據(jù)庫的處理方式依賴基本表的對象SQL99KingbaseESORACLE9iMSSQLSERVER2000RCRCC1.索引無規(guī)定√√√√√2.視圖×√×√√保留√保留√保留3.DEFAULT，PRIMARYKEY，CHECK（只含該表的列）NOTNULL等約束√√√√√√√4.ForeignKey×√×√×√×5.TRIGGER×√×√√√√6.函數(shù)或存儲過程×√√保留√保留√保留√保留√保留R表示RESTRICT,C表示CASCADE'×'表示不能刪除基本表，'√'表示能刪除基本表，‘保留’表示刪除基本表后，還保留依賴對象4.3.4索引的定義建立索引的目的：加快查詢速度誰可以建立索引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語句定義索引時，可以定義索引是唯一、非唯一或聚簇索引

4.3.4索引的定義索引的定義格式

create[unique|distinct][cluster]index

索引名

on

表名(列名[asc|desc][,列名[asc|desc]]…)

unique|distinct：唯一性索引，不允許表中不同的行在索引列上取相同值。若已有相同值存在，則系統(tǒng)給出相關(guān)信息，不建此索引。系統(tǒng)并拒絕違背唯一性的插入、更新。

cluster：聚集索引，表中元組按索引項的值排序并物理地聚集在一起。一個基本表上只能建一個聚集索引。

asc|desc：索引表中索引值的排序次序，缺省為asc。示例：

createclusterindexs-indexonS（S#）4.3.4索引的定義示例1:

CREATECLUSTERINDEXStusnameONS(Sname)；--在S表的Sname（姓名）列上建立一個聚簇索引示例2為學(xué)生-課程數(shù)據(jù)庫中的S，Course，SC三個表建立索引。CREATEUNIQUEINDEXStusnoONS(Sno)；CREATEUNIQUEINDEXCoucnoONCourse(Cno)；CREATEUNIQUEINDEXSCnoONSC(SnoASC，CnoDESC)；

--S表按學(xué)號升序建唯一索引--Course表按課程號升序建唯一索引--SC表按學(xué)號升序和課程號降序建唯一索引4.3.4索引的定義索引的刪除格式：

dropindex

索引名刪除索引時，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述。示例1：刪除S表的Stusname索引

DROPINDEXStusname；4.3.4索引的定義索引的有關(guān)說明可以動態(tài)地定義索引，即可以隨時建立和刪除索引。不允許用戶在數(shù)據(jù)操作中引用索引。索引如何使用完全由系統(tǒng)決定，這支持了數(shù)據(jù)的物理獨立性。在最經(jīng)常查詢的列上建立聚簇索引以提高查詢效率。經(jīng)常更新的列不宜建立聚簇索引。應(yīng)該在使用頻率高的、經(jīng)常用于連接的列上建索引。一個表上可建多個索引。索引可以提高查詢效率，但索引過多耗費空間，且降低了插入、刪除、更新的效率。4.3.5SQL數(shù)據(jù)定義特點

SQL中，任何時候都可以執(zhí)行一個數(shù)據(jù)定義語句，隨時修改數(shù)據(jù)庫結(jié)構(gòu)。數(shù)據(jù)庫定義不斷增長（不必一開始就定義完整）。數(shù)據(jù)庫定義隨時修改（不必一開始就完全合理）?？蛇M行增加索引、撤消索引的實驗，檢驗其對效率的影響。4.4SQL數(shù)據(jù)查詢功能4.4.1SQL數(shù)據(jù)查詢基本結(jié)構(gòu)4.4.2單表查詢4.4.3連接查詢4.4.4嵌套查詢4.4.5集合查詢4.4.6Select語句的一般形式4.4SQL數(shù)據(jù)查詢功能語句格式

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

<表名或視圖名>[，<表名或視圖名>]…[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]]；4.4.1SQL數(shù)據(jù)查詢基本結(jié)構(gòu)基本結(jié)構(gòu)

select

A1,A2,…,An

from

r1,

r2,…

,rm

where

P

∏A1,A2,…,An(p(r1r2…

rm))示例給出所有老師的姓名。select PNAMEfrom PROF?4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--選擇表中的若干列目標列形式：

可以為指定列名，所有屬性列(*)，目標列表達式。查詢指定列[例1]查詢?nèi)w學(xué)生的學(xué)號與姓名。

SELECTSno，Sname

FROMS；

[例2]查詢?nèi)w學(xué)生的姓名、學(xué)號、所在系代碼。

SELECTSname，Sno，Dno

FROMS；4.4.2單表查詢--選擇表中的若干列查詢所有屬性列：在SELECT關(guān)鍵字后面列出所有列名將<目標列表達式>指定為*，表示“所有的屬性”[例3]查詢?nèi)w學(xué)生的詳細記錄。

SELECTSno，Sname，sex，age，Dno

FROMS；

或

SELECT*FROMS；兩者完全等價。4.4.2單表查詢--選擇表中的若干列[例1]查詢?nèi)w學(xué)生的姓名、出生年份和所有地址，要求用小寫字母表示所有地址SELECTSname，‘YearofBirth:‘，2007-age，LOWER(adress)FROMS；--LOWER():為SQLSERVER函數(shù)輸出結(jié)果：Sname無列名無列名無列名李勇YearofBirth:1984 abc劉晨YearofBirth:1985 bcv王敏YearofBirth:1986 mmm張立YearofBirth:1985 kkk目標列表達式可以為：算術(shù)表達式(帶，，，)字符串常量函數(shù)列別名

使用更名運算old_nameasnew_name為關(guān)系和屬性重新命名，可出現(xiàn)在select和from子句中。As可省略。SELECTSname，‘YearofBirth:‘a(chǎn)sBIRTH，2007–ageBIRTHDAY，LOWER(adress)AdressFROMS；--LOWER():為SQLSERVER函數(shù)輸出結(jié)果：SnameBIRTHBIRTHDAY

Adress李勇YearofBirth:1984 abc劉晨YearofBirth:1985 bcv王敏YearofBirth:1986 mmm張立YearofBirth:1985 kkk4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢—from子句說明

from子句列出查詢的對象表。當(dāng)目標列取自多個表時，在不混淆的情況下可以不用顯式指明來自哪個關(guān)系。示例例：找出工資低于500的職工的姓名、工資、系別。

select PNAME,SAL,DNAME

from PROF,DEPT

whereSAL<500

andPROF.DNO=DEPT.DNO4.4.2單表查詢--選擇表中的若干元組1.重復(fù)元組的處理

如果沒有指定Distinct或Unique關(guān)鍵詞，則缺省為ALL。[例1]查詢選修了課程的學(xué)生學(xué)號。

SELECTSnoFROMSC；

等價于：

SELECTALLSnoFROMSC；

執(zhí)行上面的SELECT語句后，結(jié)果為：

Sno200215121200215121200215121200215122200215122指定DISTINCT關(guān)鍵詞，去掉表中重復(fù)的行。

SELECTDISTINCTSno

FROMSC；

執(zhí)行結(jié)果：

Sno

2002151212002151224.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--選擇表中的若干元組2.查詢滿足條件的元組---where子句表4.4常用的查詢條件查

詢

條

件謂

詞比

較=，>，<，>=，<=，!=，<>，!>，!<；NOT+上述比較運算符確定范圍BETWEENAND，NOTBETWEENAND確定集合IN，NOTIN字符匹配LIKE，NOTLIKE空

值ISNULL，ISNOTNULL多重條件（邏輯運算）AND，OR，NOT4.4.2單表查詢--選擇表中的若干元組比較大?。劾?］查詢計算機科學(xué)系全體學(xué)生的名單。

SELECTSname

FROMSWHEREDno=‘CS’；

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

SELECTSname，ageFROMSWHEREage<20；［例3］查詢考試成績有不及格的學(xué)生的學(xué)號。

SELECTDISTINCTSno

FROMSCWHEREGrade<60；4.4.2單表查詢--選擇表中的若干元組（2）確定范圍謂詞:

BETWEEN…AND…NOTBETWEEN…AND…[例1]查詢年齡在20~23歲(包括20歲和23歲)之間的學(xué)生的姓名、系別和年齡

SELECTSname，Dno，ageFROMSWHEREageBETWEEN20AND23；[例2]查詢年齡不在20~23歲之間的學(xué)生姓名、系別和年齡

SELECTSname，Dno，age FROMS WHEREageNOTBETWEEN20AND23；SELECTSname，Dno，ageFROMSWHEREage>=20ANDage<=23?4.4.2單表查詢--選擇表中的若干元組(3)確定集合謂詞：IN<值表>,NOTIN<值表>

[例1]查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，sexFROMSWHEREDnoIN('IS'，'MA'，'CS');[例2]查詢既不是信息系、數(shù)學(xué)系，也不是計算機科學(xué)系的學(xué)生的姓名和性別。SELECTSname，SsexFROMStudent

WHERESdeptNOTIN('IS'，'MA'，'CS')SELECTSname，sexFROMSWHEREDno='IS‘orDno='MA‘OrDno='CS'?4.4.2單表查詢--字符匹配謂詞：[NOT]LIKE‘<匹配串>’[ESCAPE‘<換碼字符>’]匹配串為固定字符串[例1]查詢學(xué)號為200215121的學(xué)生的詳細情況。

SELECT*FROMSWHERESno

LIKE‘200215121'；等價于：

SELECT*FROMStudentWHERESno='200215121'；4.4.2單表查詢--字符匹配2)匹配串為含通配符的字符串匹配規(guī)則：“%”：匹配零個或多個字符?！埃摺保浩ヅ淙我鈫蝹€字符。

[例2]查詢所有姓劉學(xué)生的姓名、學(xué)號和性別。

SELECTSname，Sno，sexFROMSWHERESname

LIKE‘劉%’；[例3]查詢姓“歐陽”且全名為三個漢字的學(xué)生的姓名。

SELECTSname

FROMSWHERESname

LIKE‘歐陽__’[例4]查詢名字中第2個字為“陽”字的學(xué)生的姓名和學(xué)號SELECTSname，SnoFROMSWHERESname

LIKE‘__陽%’；[例5]查詢所有不姓劉的學(xué)生姓名。SELECTSname，Sno，sexFROMSWHERESname

NOTLIKE'劉%'；4.4.2單表查詢--字符匹配3)使用換碼字符將通配符轉(zhuǎn)義為普通字符

定義轉(zhuǎn)義字符，以去掉特殊字符的特定含義，使其被作為普通字符看待。如escape“\”，是定義了\作為轉(zhuǎn)義字符，則可用\%去匹配%，用\＿去匹配＿，用\\去匹配\。

ESCAPE‘＼’表示“＼”為換碼字符

[例6]查詢DB_Design課程的課程號和學(xué)分。

SELECTCno，creditFROMCourseWHERECnameLIKE'DB\

Design'ESCAPE'\‘；[例7]查詢以"DB_"開頭，且倒數(shù)第3個字符為i的課程的詳細情況。

SELECT*FROMCourseWHERECnameLIKE'DB\

%I

'ESCAPE'\‘；4.4.2單表查詢--涉及空值的查詢謂詞：

ISNULL

或ISNOTNULL

測試指定列的值是否為空值。

“IS”不能用“=”代替[例1]某些學(xué)生選修課程后沒有參加考試，所以有選課記錄，但沒有考試成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTSno，Cno

FROMSCWHEREGradeISNULL[例2]查所有有成績的學(xué)生學(xué)號和課程號。

SELECTSno，Cno

FROMSCWHEREGradeISNOTNULL；4.4.2單表查詢--涉及空值的查詢注意事項除is[not]null之外，空值不滿足任何查找條件。如果null參與算術(shù)運算，則該算術(shù)表達式的值為null。如果null參與比較運算，則結(jié)果可視為false。在SQL-92中可看成unknown。如果null參與聚集運算，則除count(*)之外其它聚集函數(shù)都忽略null。

例：select

sum(SAL)

fromPROF

例：select

count(*)

fromPROF4.4.2單表查詢--多重條件查詢邏輯運算符：AND和OR來聯(lián)結(jié)多個查詢條件

AND的優(yōu)先級高于OR

可以用括號改變優(yōu)先級可用來實現(xiàn)多種其他謂詞[NOT]IN[NOT]BETWEEN…AND…[例1]查詢計算機系年齡在20歲以下的學(xué)生姓名。

SELECTSname

FROMStudentWHEREdno='CS'ANDSage<20；4.4.2單表查詢--多重條件查詢[例2]查詢信息系（IS）、數(shù)學(xué)系（MA）和計算機科學(xué)系（CS）學(xué)生的姓名和性別。SELECTSname，sexFROMSWHEREdnoIN('IS'，'MA'，'CS')可改寫為：SELECTSname，sexFROMSWHEREdno='IS'ORdno='MA'ORdno='CS'；4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--ORDERBY子句

格式：

orderby

列名[asc|

desc]ORDERBY子句可以按一個或多個屬性列排序升序：ASC；降序：DESC；缺省值為升序當(dāng)排序列含空值時（一般來說）ASC：排序列為空值的元組最后顯示DESC：排序列為空值的元組最先顯示[例1]查詢選修了3號課程的學(xué)生的學(xué)號及其成績，查詢結(jié)果按分數(shù)降序排列。

SELECTSno，GradeFROMSCWHERECno='3'ORDERBYGradeDESC；不同的DBMS的規(guī)則有所不一樣，如MSSQLSERVER的規(guī)則剛好相反。4.4.2單表查詢--ORDERBY子句［例2］查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號升序排列，同一系中的學(xué)生按年齡降序排列。

SELECT*FROMSORDERBYdno，SageDESC；4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--聚集函數(shù)聚集函數(shù)：計數(shù)COUNT（[DISTINCT|ALL]*）COUNT（[DISTINCT|ALL]<列名>）計算總和SUM（[DISTINCT|ALL]<列名>）

計算平均值A(chǔ)VG（[DISTINCT|ALL]<列名>）最大最小值

MAX（[DISTINCT|ALL]<列名>）

MIN（[DISTINCT|ALL]<列名>）4.4.2單表查詢--聚集函數(shù)[例1]查詢學(xué)生總?cè)藬?shù)。

SELECTCOUNT(*)FROMS；

[例2]查詢選修了課程的學(xué)生人數(shù)。

SELECTCOUNT(DISTINCTSno)FROMSC；[例3]計算1號課程的學(xué)生平均成績。

SELECTAVG(Grade)FROMSCWHERECno='1'；4.4.2單表查詢--聚集函數(shù)[例4]查詢選修1號課程的學(xué)生最高分數(shù)。

SELECTMAX(Grade)FROMSCWHERCno=‘1’；[例5］查詢學(xué)生200215012選修課程的總學(xué)分數(shù)。

SELECTSUM(credit)FROMSC，CourseWHERSno='200215012'ANDSC.Cno=Course.Cno;

4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--GROUPBY子句

分組命令groupby

列名[having

條件表達式]

groupby將表中的元組按指定一列或多列上的值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值。having則對分組進行選擇，只將聚集函數(shù)作用到滿足條件的分組上。未對查詢結(jié)果分組，聚集函數(shù)將作用于整個查詢結(jié)果對查詢結(jié)果分組后，聚集函數(shù)將分別作用于每個組作用對象是查詢的中間結(jié)果表按指定的一列或多列值分組，值相等的為一組having子句對groupby子句所確定的行組進行控制,having子句條件中只允許涉及常量,聚組函數(shù)或groupby子句中的列.4.4.2單表查詢--GROUPBY子句[例1]求各個課程號及相應(yīng)的選課人數(shù)。

SELECTCno，COUNT(Sno)FROMSCGROUPBYCno；

查詢結(jié)果：

CnoCOUNT(Sno)

122

234 344 433 5484.4.2單表查詢--GROUPBY子句[例2]列出所有老師的最高、最低、平均工資。

selectmax(SAL)，min(SAL)，avg(SAL)fromPROF[例3]列出各系的老師的最高、最低、平均工資。

selectDNO，max(SAL)，min(SAL)，avg(SAL)fromPROFgroupbyDNO4.4.2單表查詢--GROUPBY子句[例4]查詢選修了3門以上課程的學(xué)生學(xué)號。

SELECTSno

FROMSCGROUPBYSno

HAVINGCOUNT(*)>3；[例5]列出所有課程均及格的學(xué)生的平均成績。

selectSNO，avg(SCORE)

fromSCgroupbySNO

having

min(SCORE)>=60HAVING短語與WHERE子句的區(qū)別：作用對象不同WHERE子句作用于基表或視圖，從中選擇滿足條件的元組HAVING短語作用于組，從中選擇滿足條件的組。

[例6]列出所有同學(xué)的及格課程的平均成績。selectSNO,avg(grade)fromSCwheregrade>=60groupbySNO4.4.2單表查詢查詢僅涉及一個表：一、選擇表中的若干列二、From子句三、選擇表中的若干元組四、ORDERBY子句五、聚集函數(shù)六、GROUPBY子句七、更名運算4.4.2單表查詢--更名運算格式old_nameas

new_name為關(guān)系和屬性重新命名，可出現(xiàn)在select和from子句中。示例屬性更名例：給出所有老師的姓名、所納稅額及稅后工資額。

selectPNAME，SAL0.05astaxi，SAL*0.95asincoming

fromPROF4.4.2單表查詢--更名運算關(guān)系更名找出工資比所在系主任工資高的老師姓名及工資。

selectP1.PNAME，P1.SAL

fromPROFasP1，PROFasP2，DEPT

whereP1.DNO=DEPT.DNO

andDEPT.DEAN=P2.PNO

andP1.SAL>P2.SAL

注：as可選。4.4.3連接查詢一、等值與非等值連接查詢

二、自身連接三、外連接四、復(fù)合條件連接4.4.3連接查詢連接查詢：同時涉及多個表的查詢連接條件或連接謂詞：用來連接兩個表的條件一般格式：[<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>連接字段：連接謂詞中的列名稱連接條件中的各連接字段類型必須是可比的，但名字不必是相同的4.4.3連接查詢連接操作的執(zhí)行過程嵌套循環(huán)法(NESTED-LOOP)首先在表1中找到第一個元組，然后從頭開始掃描表2，逐一查找滿足連接件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。表2全部查找完后，再找表1中第二個元組，然后再從頭開始掃描表2，逐一查找滿足連接條件的元組，找到后就將表1中的第二個元組與該元組拼接起來，形成結(jié)果表中一個元組。重復(fù)上述操作，直到表1中的全部元組都處理完畢4.4.3連接查詢排序合并法(SORT-MERGE）--常用于=連接首先按連接屬性對表1和表2排序?qū)Ρ?的第一個元組，從頭開始掃描表2，順序查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。當(dāng)遇到表2中第一條大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)找到表1的第二條元組，然后從剛才的中斷點處繼續(xù)順序掃描表2，查找滿足連接條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組。直接遇到表2中大于表1連接字段值的元組時，對表2的查詢不再繼續(xù)重復(fù)上述操作，直到表1或表2中的全部元組都處理完畢為止4.4.3連接查詢索引連接(INDEX-JOIN）對表2按連接字段建立索引對表1中的每個元組，依次根據(jù)其連接字段值查詢表2的索引，從中找到滿足條件的元組，找到后就將表1中的第一個元組與該元組拼接起來，形成結(jié)果表中一個元組4.4.3連接查詢一、等值與非等值連接查詢

二、自身連接三、外連接四、復(fù)合條件連接4.4.3連接查詢--等值與非等值連接查詢

集合操作（Ⅰ）命令集合并：union集合交：intersect集合差：

except示例求選修了001或002號課程的學(xué)生號。

（selectSNO

fromSC

whereCNO=001） unionall

（selectSNO

fromSC

whereCNO=002）集合操作（Ⅱ）求選修了001和002號而沒有選003號課程的學(xué)生號。

（selectSNO

fromSC

whereCNO=001orCNO=002） except

（selectSNO

fromSC

whereCNO=003）提示集合操作自動去除重復(fù)元組，如果要保留重復(fù)元組的話，必須用all關(guān)鍵詞指明。分組和聚集函數(shù)（Ⅰ）分組命令groupby

列名[having

條件表達式]

groupby將表中的元組按指定列上的值相等的原則分組，然后在每一分組上使用聚集函數(shù)，得到單一值。having則對分組進行選擇，只將聚集函數(shù)作用到滿足條件的分組上。聚集函數(shù)平均值：avg最小值：min最大值：max總和：sum記數(shù)：count分組和聚集函數(shù)（Ⅱ）示例列出各系的老師的最高、最低、平均工資。

selectDNO，max(SAL)，min(SAL)，avg(SAL)

fromPROFgroupbyDNO列出及格的學(xué)生的平均成績。

selectSNO，avg(SCORE)

fromSCgroupbySNO

having

min(SCORE)>=60分組和聚集函數(shù)（Ⅲ）?①求選修了課程的學(xué)生人數(shù)。

select

count(SNO)

fromSC②selectPNAME，max(SAL)

fromPROF③selectDNO，avg(SAL)

fromPROF

groupbyDNO

whereAGE>60空值（Ⅰ）空值測試is

[not]

null測試指定列的值是否為空值。示例找出年齡值為空的老師姓名。

selectPNAME

fromPROF

whereAGEisnull不可寫為whereAGE=null空值（Ⅱ）注意事項除is[not]null之外，空值不滿足任何查找條件。如果null參與算術(shù)運算，則該算術(shù)表達式的值為null。如果null參與比較運算，則結(jié)果可視為false。在SQL-92中可看成unknown。如果null參與聚集運算，則除count(*)之外其它聚集函數(shù)都忽略null。

例：select

sum(SAL)

fromPROF

例：select

count(*)

fromPROF嵌套子查詢集合成員資格集合之間的比較集合基數(shù)的測試測試集合是否為空測試集合是否存在重復(fù)元組集合成員資格（Ⅰ）in子查詢表達式[not]in（子查詢）判斷表達式的值是否在子查詢的結(jié)果中。示例選修了001號課程的學(xué)生的學(xué)號及姓名。

selectSNO，SNAME

fromSwhereSNOin (selectSNO

fromSC

whereCNO=001）集合成員資格（Ⅱ）列出選修了001號和002號課程的學(xué)生的學(xué)號。

selectSNO

fromSC

whereSC.CNO=001

andSNOin

（selectSNO

fromSC

whereCNO=002）列出張軍和王紅同學(xué)的所有信息。

select*

fromS

whereSNAMEin

（“張軍”，“王紅”）集合之間的比較（Ⅰ）some/all子查詢表達式比較運算符

some（子查詢）表達式的值至少與子查詢結(jié)果中的一個值相比滿足比較運算符。表達式比較運算符

all（子查詢）表達式的值與子查詢結(jié)果中的所有的值相比都滿足比較運算符。集合之間的比較（Ⅱ）示例找出平均成績最高的學(xué)生號。

selectSNO

fromSC

groupbySNO

having

avg(SCORE)>=all

（select

avg(SCORE)

fromSC

groupbySNO）集合基數(shù)的測試（Ⅰ）測試集合是否為空[not]exists（子查詢）判斷子查詢的結(jié)果集合中是否有任何元組存在。列出選修了01號課程的學(xué)生的學(xué)號及姓名。

selectSNO，SNAME

fromS

whereexists

（select*

fromSC

where

CNO=01

andSNO=S.SNO）集合基數(shù)的測試（Ⅱ）列出選修了001號和002號課程的學(xué)生的學(xué)號。

selectSNO

fromSCSC1

whereSC1.CNO=001

and

exists

（selectSNO

from

SCSC2

whereSC2.CNO=002

andSC2.SNO=SC1.SNO）注：in后的子查詢與外層查詢無關(guān)，每個子查詢執(zhí)行一次，而exists后的子查詢與外層查詢有關(guān)，需要執(zhí)行多次，稱之為相關(guān)子查詢。列出至少選修了001號學(xué)生選修的所有課程的學(xué)生名。

selectSNAME

fromS

where

notexists

（selectCNO

fromCOURSE

where

exists

（select*

fromSC

whereSC.CNO=COURSE.CNO

andSC.SNO=001）

andnotexists

（select*

fromSC

whereSC.CNO=COURSE.CNO

andSC.SNO=S.SNO）任意課程，001號學(xué)生選之，所求學(xué)生選之。不存在任何一門課程，001號學(xué)生選之，所求學(xué)生沒有選之。集合基數(shù)的測試（Ⅲ）測試集合是否存在重復(fù)元組unique(子查詢）如果子查詢結(jié)果中沒有重復(fù)元組，則返回true。示例找出所有只教授一門課程的老師姓名。

selectPNAME fromPROF

where

unique

（select

PNO

from

PC

wherePC.PNO=PROF.PNO)集合基數(shù)的測試（Ⅳ）找出至少選修了兩門課程的學(xué)生姓名。

selectSNAME fromS where

not

unique

（selectSNO

fromSC

whereSC.SNO=S.SNO)思考

toTRUEornottoTRUE,thatisthequestion.

unique{(a,b,null),(a,b,null)}?派生關(guān)系（Ⅰ）命令（子查詢）as

關(guān)系名（列名，列名，…）

SQL-92中，允許在from子句中使用子查詢表達式，這時可將該子查詢的結(jié)果命名為一個臨時關(guān)系加以引用。示例找出平均成績及格的學(xué)生。

先求出每個學(xué)生的平均成績，再從中找出及格的學(xué)生

select

SNAME，avg（SCORE）

from

S，SC

whereSC.SNO=S.SNO

groupbySC.SNO派生關(guān)系（Ⅱ）selectSNAME，AVG_SCOREfrom

(selectSNAME，avg（SCORE）

fromS，SC

whereSC.SNO=S.SNO

groupbySC.SNO)

as

result(SNAME，AVG_SCORE)whereAVG_SCORE>=60派生關(guān)系Vs

視圖？視圖（Ⅰ）定義視圖

createviewview_name[(列名[，列名]…)]

as（查詢表達式）[withcheckoption]

視圖的屬性名缺省為子查詢結(jié)果中的屬性名，也可以顯式指明。

withcheckoption指明當(dāng)對視圖進行insert，update時，要檢查是否滿足視圖定義中的條件。撤消視圖

dropviewview_name視圖（Ⅱ）示例createviewCOMPUTER_PROF

as(selectPNO,PNAME,SAL

fromPROF，DEPT

wherePROF.PNO=DEPT.PNO

andDEPT.DNAME=“計算機系”）createviewDEPTSAL(DNO,LOW,HIGH,AVERAGE,TOTAL)

as(selectDNO,min(SAL),max(SAL),

avg(SAL),sum(SAL)

fromPROF

groupbyDNO)視圖（Ⅲ）給出計算機系工資超過800的老師姓名。

selectPNAME fromCOMPUTER_PROF whereSAL>800給出計算機系老師的最低、最高、平均工資以及工資總額。

selectLOW,HIGH,AVERAGE,TOTAL fromDEPTSAL,DEPT whereDEPTSAL.DNO=DEPT.DNO

andDEPT.DNAME=“計算機系”關(guān)系的連接（Ⅰ）基本分類連接成分包括兩個輸入關(guān)系、連接條件、連接類型。連接條件：決定兩個關(guān)系中哪些元組相互匹配，以及連接結(jié)果中出現(xiàn)哪些屬性。連接類型：決定如何處理與連接條件不匹配的元組。連接類型連接條件innerjoinleftouterjoinrightouterjoinfullouterjoinnatureon<謂詞>using(A1,A2,…,An)關(guān)系的連接（Ⅱ）自然連接：出現(xiàn)在結(jié)果關(guān)系中的兩個連接關(guān)系的元組在公共屬性上取值相等，且公共屬性只出現(xiàn)一次。on<謂詞P>：出現(xiàn)在結(jié)果關(guān)系中的兩個連接關(guān)系的元組在公共屬性上取值滿足謂詞條件P，且公共屬性出現(xiàn)兩次。using(A1,A2,…,An)：(A1,A2,…,An)是兩個連接關(guān)系的公共屬性的子集，元組在(A1,A2,…,An)上取值相等，且(A1,A2,…,An)只出現(xiàn)一次。內(nèi)連接：舍棄不匹配的元組。左外連接：內(nèi)連接+左邊關(guān)系中失配的元組（缺少的右邊關(guān)系屬性值用null表示）。關(guān)系的連接（Ⅲ）右外連接：內(nèi)連接+右邊關(guān)系中失配的元組（缺少的左邊關(guān)系屬性值用null表示）。全外連接：內(nèi)連接+左邊關(guān)系中失配的元組（缺少的右邊關(guān)系屬性值用null表示）+右邊關(guān)系中失配的元組（缺少的左邊關(guān)系屬性值用null表示）。crossjoin：兩個關(guān)系的笛卡兒積。unionjoin：左邊關(guān)系中失配的元組+右邊關(guān)系中失配的元組。對于外連接，連接條件是必須的； 對于內(nèi)連接，連接條件是可選的，沒有連接條件等價于兩個關(guān)系的笛卡兒積。關(guān)系的連接（Ⅳ）列出老師的教工號、姓名、工資、所教課程號。

selectPNO，PNAME，SAL，CNO from（PROFnatureleftouterjoinPC）

RinnerjoinSonR.C=S.CRS關(guān)系的連接（Ⅴ）RleftouterjoinSonR.C=S.CRnaturerightouterjoinS關(guān)系的連接（Ⅵ）RfullouterjoinSonR.C=S.CSQL的數(shù)據(jù)修改功能插入刪除修改視圖更新插入操作（Ⅰ）命令insertinto

表名[（列名[，列名]…]values（值[，值]…）插入一條指定好值的元組insertinto

表名[（列名[，列名]…]（子查詢）插入子查詢結(jié)果中的若干條元組示例insertinto

PROFvalues(P123,“王明”,35,D08,498)insertinto

PROF

(PNO,PNAME,DNO)

values(P123,“王明”,D08)

思考：SAL取何值？如何防止插入帶有空值的元組？插入操作（Ⅱ）將平均成績大于90的學(xué)生加入到EXCELLENT中。

insertintoEXCELLENT(SNO,GRADE)

selectSNO,avg(SCORE)

fromSC

groupby(SNO)

having

avg(SCORE)>90FORBIDDEN(

INFORMIX) insertintoPROF

select*

fromPROF若支持，則完成查詢后，再執(zhí)行修改操作不支持修改在子查詢中出現(xiàn)的表刪除操作（Ⅰ）命令deletefrom

表名[where

條件表達式]從表中刪除符合條件的元組，如果沒有where語句，則刪除所有元組。示例清除所有選課記錄

deletefromSC刪除王明老師所有的任課記錄。

deletefromPC

wherePNOin(selectPNO

fromPROF

wherePNAME=“王明”)

刪除操作（Ⅱ）刪除低于平均工資的老師記錄。

deletefromPROF

whereSAL<(select

avg(SAL)

fromPROF)

思考：是先找到所有符合條件的元組，一并刪除，還是找到一個刪除一個？

更新操作（Ⅰ）命令

update表名

set

列名=表達式|子查詢 列名=[，表達式|子查詢]… [where

條件表達式]指定對哪些列進行更新，以及更新后的值是什么。示例老師工資上調(diào)5%。

updatePROF

setSAL=SAL*1.05更新操作（Ⅱ）將D01系系主任的工資改為該系的平均工資

updatePROF

setSAL= (select

avg(SAL)

fromPROF

whereDNO=D01)

wherePNO=(select

DEAN

fromDEPT

whereDNO=D01)更新操作（Ⅲ）工資超過2000的繳納10%所得稅，其余的繳納5%所得稅。 ①updatePROF

setSAL=SAL*0.9

whereSAL>2000

②updatePROF

setSAL=SAL*0.95

whereSAL<=2000

執(zhí)行順序是①，②，還是②，①？視圖更新（Ⅰ）示例createviewP_SAL

as(selectPNO,PNAME,SAL

fromPROF)insertintoP_SAL

values(P08,