第3章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL

2023/2/51本章內(nèi)容SQL的數(shù)據(jù)查詢3.3SQL的數(shù)據(jù)操作3.43.5

SQL的數(shù)據(jù)控制

SQL概述3.1

存儲過程3.7視圖

本章小結(jié)3.8

SQL的數(shù)據(jù)定義3.2結(jié)構(gòu)化查詢語言（StructuredQueryLanguage，SQL）是一種介于關(guān)系代數(shù)與關(guān)系演算之間的語言，其功能包括查詢、操縱、定義和控制4個(gè)方面，目前已成為關(guān)系數(shù)據(jù)庫的標(biāo)準(zhǔn)語言。SQL語言簡潔、方便、功能齊全，是目前應(yīng)用最廣的關(guān)系數(shù)據(jù)庫語言之一。3.1SQL概述

3.1.1SQL語言的發(fā)展簡史SQL語言的發(fā)展ChamberlinSEQUELSQL大型數(shù)據(jù)庫

SybaseINFORMIXSQLServerOracleDB2INGRES小型數(shù)據(jù)庫

FoxProAccess3.1.2SQL語言的主要特點(diǎn)數(shù)據(jù)定義語言DDL數(shù)據(jù)操縱語言DML數(shù)據(jù)控制語言DCL

一體化

高度非過程化

統(tǒng)一的語法結(jié)構(gòu)

自含式語言嵌入式語言語言簡捷,易學(xué)易用Select、create、drop、alter、insert、update、delete、grant、revoke3.1.3SQL語言的主要功能（1）數(shù)據(jù)定義語句（DataDefinitionLanguage，DDL）：用于定義關(guān)系數(shù)據(jù)庫的模式、外模式和內(nèi)模式，以實(shí)現(xiàn)對數(shù)據(jù)庫基本表、視圖以及索引文件的定義、修改和刪除等操作。（2）數(shù)據(jù)操縱語句（DataManipulationLanguage，DML）：用于完成對數(shù)據(jù)庫表數(shù)據(jù)的查詢和更新操作。其中，數(shù)據(jù)更新指對數(shù)據(jù)進(jìn)行插入、刪除和修改操作。（3）數(shù)據(jù)控制語句（DataControlLanguage，DCL）：用于控制對數(shù)據(jù)庫的訪問及服務(wù)器的關(guān)閉、啟動等操作。最常用的語句有GRANT、REVOKE等。

(4)數(shù)據(jù)查詢SQL語言具有數(shù)據(jù)查詢、數(shù)據(jù)定義、數(shù)據(jù)操縱和數(shù)據(jù)控制四種功能

3.1.4SQL對關(guān)系數(shù)據(jù)庫模式的支持圖3-1SQL語言支持的關(guān)系數(shù)據(jù)庫的三級模式結(jié)構(gòu)3.2SQL的數(shù)據(jù)定義3.2.1SQL的數(shù)據(jù)類型1．整數(shù)數(shù)值類型

（1）INT

每個(gè)INT類型的數(shù)據(jù)按4個(gè)字節(jié)存儲，其中1位表示整數(shù)值的正負(fù)號，其他31位表示整數(shù)值的長度和大小，表示范圍為?231～231。（2）SMALLINT。每個(gè)SMALLINT類型的數(shù)據(jù)占用2個(gè)字節(jié)的存儲空間，其中1位表示整數(shù)值的正負(fù)號，其他15位表示整數(shù)值的長度和大小，表示范圍為?215～215。（3）TINYINT。每個(gè)TINYINT類型的數(shù)據(jù)占用1個(gè)字節(jié)的存儲空間，表示范圍為0～255。（4）BIGINT。每個(gè)BIGINT類型的數(shù)據(jù)占用8個(gè)字節(jié)的存儲空間，表示范圍為?263～263。（5）BIT。

BIT為可以取值為1、0或NULL的整數(shù)數(shù)據(jù)類型。字符串值TRUE和FALSE可以轉(zhuǎn)換為以下BIT值：TRUE轉(zhuǎn)換為1，F(xiàn)ALSE轉(zhuǎn)換為0。

2．精確數(shù)值類型DECIMAL[(p[，s)]]和NUMERIC[(p[，s])]都是固定精度和小數(shù)位數(shù)（定點(diǎn)數(shù)）的數(shù)據(jù)類型，使用最大精度時(shí)，有效值范圍為?1038+1到1038?1。NUMERIC在功能上等價(jià)于DECIMAL。p（精度）是最多可以存儲的十進(jìn)制數(shù)字的總位數(shù)，包括小數(shù)點(diǎn)左邊和右邊的位數(shù)。該精度必須是從1到最大精度38之間的值，默認(rèn)為18；s（小數(shù)位數(shù)）是小數(shù)點(diǎn)右邊可以存儲的十進(jìn)制數(shù)字的最大位數(shù)，必須是0～p之間的值，僅在指定精度后才可以指定，默認(rèn)值為0。3．近似浮點(diǎn)數(shù)據(jù)類型浮點(diǎn)數(shù)據(jù)類型用于存儲十進(jìn)制小數(shù)。浮點(diǎn)數(shù)值的數(shù)據(jù)在SQL中采用上舍入（RoundUp）方式進(jìn)行存儲。所謂上舍入是指當(dāng)（且僅當(dāng)）要舍入的數(shù)是一個(gè)非零數(shù)時(shí)，對其保留數(shù)字部分的最低有效位上的數(shù)值加1，并進(jìn)行必要的進(jìn)位。若一個(gè)數(shù)是上舍入數(shù)，其絕對值不會減少。（1）REAL（2）FLOAT

4．貨幣數(shù)據(jù)類型（1）MONEY。

MONEY型數(shù)據(jù)可以存儲?922337203685477.5808～922337203685477.5807之間的數(shù)。（2）SMALLMONEY。

SMALLMONEY只能存儲?214

748.3648～214

748.3647之間的數(shù)。5．日期時(shí)間類型（1）DATETIME。DATETIME用兩個(gè)4字節(jié)的整數(shù)存儲。（2）SMALLDATETIME。SMALLDATETIME數(shù)據(jù)類型的精確度低于DATETIME，用兩個(gè)2字節(jié)的整數(shù)存儲。

6．字符串類型字符數(shù)據(jù)類型是使用最多的數(shù)據(jù)類型之一，它可以用來存儲各種字母、數(shù)字符號、特殊符號。

(1)CHAR

(2)NCHAR

(3)VARCHAR(4)NVARCHAR(5)TEXT(6)NTEXT

7．二進(jìn)制數(shù)據(jù)類型

（1）BINARY

（2）VARBINARY。

（3）IMAGE

3.2.2基本表的定義和維護(hù)（1）語句格式約定符號

尖括號“<>“中的內(nèi)容為實(shí)際語句；方括號“[]”中的內(nèi)容為任選項(xiàng)；大括號“{}”或分隔符“|”中的內(nèi)容為必選其中的一項(xiàng)；[,…n]表示前面的項(xiàng)可重復(fù)多次。（2）一般語法規(guī)定

SQL中的數(shù)據(jù)項(xiàng)分隔符為“,”，其字符串常數(shù)的定界符用單引號“'”表示。（3）SQL特殊語法規(guī)定

SQL的關(guān)鍵詞一般使用大寫字母表示；語句的結(jié)束符為“;”。1．定義基本表CREATETABLE<表名>（<列名><數(shù)據(jù)類型>[列級完整性約束條件][,<表級完整性約束條件>]）;完整性約束的基本語法格式

[CONSTRAINT<約束名>]<約束類型>

NULL/NOTNULL

UNIQUE

PRIMARYKEY

FOREIGNKEY

CHECK2．完整性約束條件（1）NULL|NOTNULL約束

NULL表示“不知道”、“不確定”或“沒有數(shù)據(jù)”的意思主鍵列不允許出現(xiàn)空值[CONSTRAINT<約束名>][NULL|NOTNULL](2)UNIQUE約束（惟一約束）

指明基本表在某一列或多個(gè)列的組合上的取值必須惟一UNIQUE用于定義列約束

[

CONSTRAINT<約束名>]UNIQUE

UNIQUE用于定義表約束[CONSTRAINT<約束名>]UNIQUE（<列名>[{,<列名>}]）

(3)PRIMARYKEY約束（主鍵約束）

用于定義基本表的主鍵，起惟一標(biāo)識作用PRIMARYKEY與UNIQUE的區(qū)別：一個(gè)基本表中只能有一個(gè)PRIMARYKEY，但可多個(gè)UNIQUE對于指定為PRIMARYKEY的一個(gè)列或多個(gè)列的組合，其中任何一個(gè)列都不能出現(xiàn)NULL值，而對于UNIQUE所約束的惟一鍵，則允許為NULL對于指定為PRIMARYKEY的一個(gè)列或多個(gè)列的組合，其中任何一個(gè)列都不能出現(xiàn)NULL值，而對于UNIQUE所約束的惟一鍵，則允許為NULL不能為NULL不能重復(fù)PRIMARYKEY用于定義列約束

CONSTRAINT<約束名>PRIMARYKEYPRIMARYKEY用于定義表約束[CONSTRAINT<約束名>]PRIMARYKEY(<列名>[{,<列名>}])

(4)CHECK約束

CHECK約束用來檢查字段值所允許的范圍在建立CHECK約束時(shí)，需要考慮以下幾個(gè)因素：一個(gè)表中可以定義多個(gè)CHECK約束。每個(gè)字段只能定義一個(gè)CHECK約束。在多個(gè)字段上定義的CHECK約束必須為表約束。當(dāng)執(zhí)行INSERT、UNDATE語句時(shí)CHECK約束將驗(yàn)證數(shù)據(jù)。

[CONSTRAINT<約束名>]CHECK(<條件>)

(5)FOREIGNKEY約束（外鍵約束）[CONSTRAINT<約束名>]FOREIGNKEYREFERENCES<主表名>(<列名>[{,<列名>}])外部鍵從表主鍵主表引用

【例3-1】

創(chuàng)建部門表D，由部門號（Dno）和部門（Dept）兩個(gè)屬性組成。

CREATETABLED(DnoCHAR(10),DeptCHAR(15));【例3-2】建立一個(gè)課程表C，由課程號（Cno）、課程名（Cn）、課時(shí)數(shù)（Ct）、開課學(xué)期（Term）、課程性質(zhì)（Cx）和課程先行課（Cpno）組成，其中課程號不能為空。

CREATETABLEC(CnoCHAR(8)CONSTRAINTcno_consNOTNULL,CnCHAR(20),CtINTDEFAULT64,TermCHAR(20)NULL,CxCHAR(20),CpnoCHAR(8));【例3-3】

建立選課表SC，由學(xué)號（Sno）、課程號（Cno）和成績（Score）組成。一個(gè)學(xué)生某一門課程的成績應(yīng)該是唯一的，即Sno+Cno為唯一鍵。

CREATETABLESC(SnoCHAR(8)NOTNULL,CnoCHAR(8)NOTNULL,ScoreFLOAT(1),UNIQUE(Sno,Cno));【例3-4】

創(chuàng)建部門表D，由部門號（Dno）、部門（Dept）兩個(gè)屬性組成，其中部門號為主關(guān)鍵字，部門名稱為唯一鍵。

CREATETABLED(DnoCHAR(10)PRIMARYKEY, DeptCHAR(15)UNIQUE);【例3-5】創(chuàng)建學(xué)生表S，由學(xué)號（Sno）、姓名（Sn）、性別（Sex）、年齡（Age）、籍貫（BP）和部門號（Dno）6個(gè)屬性組成，其中學(xué)號為主關(guān)系鍵，部門號（Dno）為部門表D的外部關(guān)系鍵。

CREATETABLES(SnoCHAR(10)PRIMARYKEY, SnCHAR(12), SexCHAR(4), AgeINT, BPCHAR(20), DnoCHAR(10), FOREIGNKEY(Dno)REFERENCESD(Dno));【例3-6】

創(chuàng)建教師表T，由工號（Tno）、姓名（Tn）、性別（Sex）、年齡（Age）、職稱（Prof）和部門號（Dno）6個(gè)屬性組成，其中工號為主關(guān)系鍵，部門號（Dno）為部門表D的外部關(guān)系鍵，年齡為空或取0～120之間的整數(shù)。

CREATETABLET(TnoCHAR(10)PRIMARYKEY,TnCHAR(12),SexCHAR(4),AgeINT,ProfCHAR(20),DnoCHAR(10),FOREIGNKEY(Dno)REFERENCESD(Dno),CHECK((AgeISNULL)OR(AgeBETWEEN0AND120)));3．修改基本表SQL語言用ALTERTABLE語句修改表的結(jié)構(gòu)，其語法格式如下：ALTERTABLE<表名>[ADD<新列名><數(shù)據(jù)類型>[完整性約束>]][DROP<完整性約束名>][MODIFY<列名><數(shù)據(jù)類型>];其中：（1）ADD子句用于添加新字段或新的完整性約束條件。（2）DROP子句用于刪除已存在的完整性約束條件。（3）MODIFY子句用于修改屬性的數(shù)據(jù)類型。注意：不論基本表中是否有數(shù)據(jù)，新增的列一律為空?！纠?-7】

向?qū)W生表S中增加出生日期屬性（BD），其數(shù)據(jù)類型為日期型。

ALTERTABLESADDBDDATE;【例3-8】

刪除S表中Sno列必須取唯一值的約束。

ALTERTABLESDROPUNIQUE(Sno);【例3-9】

將學(xué)生表S中的Age屬性列的數(shù)據(jù)類型改為SMALLINT。

ALTERTABLESMODIFYAgeSMALLINT;

4．刪除基本表

SQL語言用DROPTABLE語句刪除表，其語法格式如下：DROPTABLE<表名>

【例3-10】

刪除學(xué)生表S。

DROPTABLES;

索引的作用1.索引的分類

加快查詢速度保證行的惟一性聚集索引與非聚集索引唯一索引復(fù)合索引聚集索引：查詢速度快非聚集索引：更新速度快排列的結(jié)果存儲在表中只有一個(gè)排列的結(jié)果不存儲在表中可以有多個(gè)有UNIQUE，自動建立非聚集的惟一索引有PRIMARYKEY，自動建立聚集索引將兩個(gè)或多個(gè)字段組合起來建立的索引，單獨(dú)的字段允許有重復(fù)的值3.2.3索引的定義和維護(hù)

非葉級Page12-RootPage37Page28葉級

（鍵值）Page41Page51Page61Page71Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03堆Page707Page808Page70901020304..................AkhtarFunkSmithMatey...Page704Page705Page706010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#41、非聚集索引idindid=2rootsysindexesSELECTlastname,firstnameFROMmemberWHERElastnameBETWEEN'Masters'AND'Rudd'

非葉級第12頁–根第37頁第28頁葉級

（鍵值）第41頁第51頁第61頁第71頁Akhtar...MartinAkhtarBarrConFunkFunk4:706:014:705:034:704:014:706:024:704:02MartinSmith...SmithSmithSmithWhiteWhite4:706:034:708:044:707:014:704:034:705:02AkhtarGanio...GanioHallJonesJonesJones4:709:014:709:044:709:024:708:034:707:03堆第707頁第708頁第709頁01020304..................AkhtarFunkSmithMatey...第704頁第705頁第706頁010203.....................ConnFunkWhite......010203.....................RuddWhiteBarr......010203.....................SmithOtaJones......01020304..................MartinPhuaJonesSmith...010203.....................GanioJonesHall......MartinMateyOtaPhuaRudd4:708:014:706:044:707:024:708:024:705:01非聚集索引FileID#4MartinMartin04...MateyMatey4:706:0402...PhuaPhua4:708:0201...RuddRudd4:705:0102...OtaOta4:707:02聚集索引第140頁–根第100頁第120頁第130頁第141頁第145頁AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…第110頁GanioHallJonesJonesJones...76788078243459782634.....................2、聚集索引SELECTlastname,firstnameFROMmemberWHERElastname='Ota'聚集索引第140頁–根第100頁第120頁第130頁第141頁第145頁AkhtarBarrConFunkFunk...23345678253413341534.....................MartinMartinOtaPhuaRudd...12347778587878786078.....................SmithSmithSmithWhiteWhite...14345778797822341634.....................AkhtarGanio…Akhtar…MartinMartinSmith…第110頁GanioHallJonesJonesJones...76788078243459782634.....................MartinOta5878...Martinidindid=1rootsysindexesLastName上的聚集索引FirstName上的非聚集索引非葉級葉級

（聚集鍵值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’Melia3、使用基于聚集索引的非聚集索引查找行idindid=2rootsysindexesSELECTlastname,firstname,phoneFROMmemberWHEREfirstname='Mike'姓氏上的

聚集索引名字上的

非聚集索引非葉級葉級

（聚集鍵值）AaronDeanna…Aaron...JoseJoseNina…DeannaDonDougDaumHallHampton……AaronAdamAmieConBarrBaldwin……JoseJudyMikeLugoKaethlerNash……BarrAdamCoxDaumArletteDeanna………………KimKobaraLaBrieShaneLindaRyan………………NagataNashNixonSusanneMikeToby………………BarrKimNagataO’MeliaMikeNashNagataNashMike…

CREATE[UNIQUE][CLUSTER]INDEX<索引名>ON<表名>(<列名>[次序][{,<列名>}][次序]…)

2．建立索引建立惟一索引

建立聚集索引

ASC或DESC，默認(rèn)為ASC【例3-11】在學(xué)生表S的姓名（Sn）列上建立一個(gè)聚集索引Studentname，且表S中的記錄按照Sn值的升序存放。

CREATECLUSTERINDEXStudentnameONS(snASC);【例3-12】

為表SC在Sno和Cno上建立一個(gè)唯一索引SCI。

CREATEUNIQUEINDEXSCIONSC(sno,cno);DROPINDEX數(shù)據(jù)表名.索引名

3．刪除索引不能刪除由CREATE或ALTER命令創(chuàng)建的索引，也不能刪除系統(tǒng)表中的索引【例3-13】

刪除表S中的Studentname索引。

DROPINDEXStudentname;3.3SQL數(shù)據(jù)查詢

SELECT命令的格式與基本使用

SELECT[ALL|DISTINCT][TOPN[PERCENT][WITHTIES]] 〈列名〉[AS別名1][{，〈列名〉[AS別名2]}] [INTO新表名]

FROM〈表名1或視圖名1〉[[AS]表1別名][{，〈表名2或視圖名2〉[[AS]表2別名]}] [WHERE〈檢索條件〉] [GROUPBY<列名1>[HAVING<條件表達(dá)式>]] [ORDERBY<列名2>[ASC|DESC]]投影選取3.3.1單表查詢

1．選擇表中的若干列選擇列即為關(guān)系代數(shù)中的投影操作，得到的目標(biāo)列為表中的部分或全部列。（1）選擇列?！纠?-14】

查詢?nèi)w學(xué)生的學(xué)號與姓名。

SELECTSno,SnFROMS;SnoSn100101姜珊100102李思100103孫浩100104周強(qiáng)100105李斌100106黃琪【例3-15】

查詢?nèi)w學(xué)生的所有字段信息

SELECT*FROMS;

等價(jià)于：

SELECTSno,Sn,Sex,Age,BP,DnoFROMS（2）用DISTINCT刪除重復(fù)的行。

【例3-16】

查詢選課表SC中無重復(fù)的學(xué)號。

SELECTDISTINCTSnoFROMSC;Sno100101100102100103【例3-17】

查詢?nèi)w學(xué)生的姓名、出生年份、籍貫，要求用中文表示所有屬性。

SELECTSnAS姓名,2010?AgeAS出生年份,BPAS籍貫FROMS;姓名出生年份籍貫姜珊1992湖南李思1993江蘇孫浩1989江蘇周強(qiáng)1990新疆李斌1991河南黃琪1989湖北（3）修改查詢結(jié)果中的列標(biāo)題字段名|表達(dá)式|函數(shù)名AS別名運(yùn)算符含義=、>、<、>=、<=、!=、<>比較大小BETWEEN…AND…確定范圍AND、OR、NOT多重條件。也可以把AND、OR和NOT結(jié)合起來，使用圓括號來組成復(fù)雜的表達(dá)式IN|NOTIN確定集合LIKE|NOTLIKE字符匹配。Like是用于查找與指定字符串相匹配的字符串，可使用通配符%與_，一個(gè)_只代表1個(gè)字符，一個(gè)%可代表多個(gè)字符。注意：只允許在Like子句中使用通配符ISNULL空值表3-1WHERE子句中條件表達(dá)式常用的運(yùn)算符

2．選擇表中的若干行

表中查詢滿足條件的行可通過WHERE子句來實(shí)現(xiàn)。（1）表達(dá)式比較【例3-18】

查詢性別為“女”的學(xué)生的學(xué)號與姓名。

SELECTCno,CnFROMWHERESex='女';SnoSn100101姜珊100102李思【例3-19】

查詢年齡在20歲以下的學(xué)生的情況。

SELECT*FROMSWHEREage<20;（2）確定范圍BETWEEN…AND…和NOTBETWEEN…AND…用來確定查詢范圍，意指“在…和…之間”或“不在…和…之間”的數(shù)據(jù)?！纠?-20】

查詢年齡在18～20之間的學(xué)生的情況。

SELECT*FROMSWHEREAgeBETWEEN18AND20;（3）多重條件查詢。

使用邏輯運(yùn)算符AND、OR和NOT,其優(yōu)先級由高到低為NOT、AND、OR，但用戶可以使用括號改變優(yōu)先級?！纠?-21】

查詢年齡在18～20之間的女生的情況。

SELECT*

FROMS

WHERE(AgeBETWEEN18AND20)ANDSex='女';（4）確定集合。

IN|NOTIN運(yùn)算符用于查找列值屬于指定集合的元組?！纠?-22】

查詢選修了課程號為“150101”或“150102”的學(xué)生的學(xué)號、課程號和成績。

SELECTSno,Cno,ScoreFROMSC

WHERECnoIN('150101','150102');

等價(jià)于：SELECTSno,Cno,ScoreFROMSCWHERECno='150101'ORCno='150102';（5）字符串匹配當(dāng)不知道完全精確的值時(shí)，用戶可以使用LIKE或NOTLIKE進(jìn)行部分匹配查詢（也稱模糊查詢)<屬性名>LIKE<字符串常量>

通配符功能實(shí)例%代表0個(gè)或多個(gè)字符ab%,ab后可接任意字符串_（下劃線）代表1個(gè)字符a_b,a與b之間可有1個(gè)字符[]表示在某一范圍的字符[0～9]：0～9之間的字符[^]表示不在某一范圍的字符[^0～9]：不在0～9之間的字符表3-2通配符及其功能【例3-23】

查詢所有姓“張”的教師的教師號和姓名

SECLECTTno,TnFROMTWHERETnLIKE'張%';（6）涉及空值查詢。某個(gè)字段沒有值稱之為具有空值（NULL）空值不同于零和空格，它不占任何存儲空間【例3-24】查詢沒有考試成績的學(xué)生的學(xué)號和相應(yīng)的課程號。

SELECTSno,Cno

FRONSC

WHEREScoreISNULL;注意：這里的空值條件為ScoreISNULL，不能寫成Score=NULL3．對查詢結(jié)果進(jìn)行排序

使用ORDERBY子句即可實(shí)現(xiàn)【例3-26】

查詢所有教師的姓名、性別、年齡、職稱，并按照年齡降序排列，年齡相同的按照職稱降序排列

SELECTTn,Sex,Age,ProfFROMTORDERBYAgeDESC,ProfDESC;4．使用庫函數(shù)及統(tǒng)計(jì)匯總查詢函數(shù)名功能COUNT([DISTINCT|ALL]*)統(tǒng)計(jì)元組數(shù)量COUNT([DISTINCT|ALL]<列名>)統(tǒng)計(jì)一列中值的數(shù)量SUM([DISTINCT|ALL]<列名>)計(jì)算一列值的總和（此列必須是數(shù)值型）MAX([DISTINCT|ALL]<列名>)計(jì)算一列值的平均值（此列必須是數(shù)值型）AVG([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值【例3-27】

求學(xué)號為“100101”的學(xué)生的總分和平均分SELECTSUM(Score)ASTOTAL,AVG(Score)ASAVG

FROMSCWHERE(Sno='100101');【例3-28】

求信電學(xué)院學(xué)生的總數(shù)

SELECTCOUNT(Sno)FROMSWHEREDEPT='信電';

或

SELECTCOUNT(*)FROMSWHEREDEPT='信電';注意：二者的區(qū)別是，COUNT(*)統(tǒng)計(jì)的是行數(shù)，而COUNT(Sno)統(tǒng)計(jì)的是Sno具有值（非空）的行數(shù)。本例中Sno是主關(guān)系鍵，其值不能為空，所以二者等價(jià)。其他情況下，二者不一定等價(jià)?！纠?-31】統(tǒng)計(jì)教師人數(shù)在2人以上的職稱名稱。

SELECTCOUNT(*)AS人數(shù)，ProfFROMTGROUPBYProfHAVINGCOUNT(*)>1;【例3-32】查詢選修課程在3門以上，并且各門課程均及格的學(xué)生學(xué)號和平均成績，查詢結(jié)果按照平均成績降序輸出。

SELECTSno,AVG(Score)AS平均成績FROMSC

WHEREScore>=60GROUPBYSnoHAVINGCOUNT(*)>=3ORDERBY2DESC;5．對查詢結(jié)果進(jìn)行分組計(jì)算

使用GROUPBY子句和HAVING子句實(shí)現(xiàn)。

3.3.2連接查詢連接查詢：一個(gè)查詢需要對多個(gè)表進(jìn)行操作表之間的連接：連接查詢的結(jié)果集或結(jié)果表連接字段：數(shù)據(jù)表之間的聯(lián)系是通過表的字段值來體現(xiàn)的連接操作的目的：從多個(gè)表中查詢數(shù)據(jù)表的連接方法:

表之間滿足一定條件的行進(jìn)行連接時(shí)，F(xiàn)ROM子句指明進(jìn)行連接的表名，WHERE子句指明連接的列名及其連接條件利用關(guān)鍵字JOIN進(jìn)行連接：當(dāng)將JOIN關(guān)鍵詞放于FROM子句中時(shí)，應(yīng)有關(guān)鍵詞ON與之對應(yīng)，以表明連接的條件INNERJOIN顯示符合條件的記錄，此為默認(rèn)值LEFT（OUTER）JOIN為左（外）連接，用于顯示符合條件的數(shù)據(jù)行以及左邊表中不符合條件的數(shù)據(jù)行，此時(shí)右邊數(shù)據(jù)行會以NULL來顯示RIGHT（OUTER）JOIN右（外）連接，用于顯示符合條件的數(shù)據(jù)行以及右邊表中不符合條件的數(shù)據(jù)行。此時(shí)左邊數(shù)據(jù)行會以NULL來顯示FULL（OUTER）JOIN顯示符合條件的數(shù)據(jù)行以及左邊表和右邊表中不符合條件的數(shù)據(jù)行。此時(shí)缺乏數(shù)據(jù)的數(shù)據(jù)行會以NULL來顯示CROSSJOIN將一個(gè)表的每一個(gè)記錄和另一表的每個(gè)記錄匹配成新的數(shù)據(jù)行JION的分類

【例3-33】查詢每個(gè)學(xué)生及其選修課程的情況。

SELECTS.*,SC.*FROMS,SCWHERES.sno=SC.sno;

采用JOIN連接查詢的SQL語句為：

SELECTS.*,SC.*FROMSINNERJOINSCONS.sno=SC.sno;

注意：連接運(yùn)算可以使用關(guān)系代數(shù)中的連接運(yùn)算1．WHERE連接查詢方式

2．JOIN連接查詢方式

SELECT[選取字段|表達(dá)式]…

FROM<表名>INNER|LEFTOUTER|RIGHTOUTER|FULLOUTER|CROSSJOIN<表名>ON連接條件

【例3-34】

現(xiàn)在有兩個(gè)表：St和Sc（如下所示），則INNER、LEFT、RIGHT、FULLOUTER連接查詢的結(jié)果如圖3-13所示。St表Sc表SnuSnAgeSnuCnuScore100101姜珊1810010115010190100102李思1710010215010286100103孫浩211001011501038810010415010365圖3-12自然連接查詢結(jié)果圖SnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388（a）SELECT*FROMStINNERJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388100103孫浩21（b）SELECT*FROMStLEFTJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思171501038810010415010365（c）SELECT*FROMStRIGHTJOINScONSt.Snu=Sc.SnuSnuSnAgeCnuScore100101姜珊1815010190100102李思1715010286100102李思1715010388100103孫浩2110010415010365

（d）SELECT*FROMStFULLJOINScONSt.Snu=Sc.Snu【例3-35】

假設(shè)課程表C的結(jié)構(gòu)如下，查詢每一門課程的間接先修課（即先修課的先修課）。CnuCno課程名Ct課時(shí)Cpno先修課程150101數(shù)據(jù)結(jié)構(gòu)64150104150102操作系統(tǒng)48150103150103數(shù)據(jù)庫原理64150101150104離散數(shù)學(xué)64150105150105高等數(shù)學(xué)803．自身連接查詢自身連接是指相互連接的表物理上為同一張表，要為這張表取兩個(gè)別名以方便操作。方法1:

SELECTfirst.Cno,second.Cpno

FROMCASfirst,CASsecond

WHEREfirst.Cpno=second.Cno;方法2:

SELECTfirst.Cno,second.Cpno

FROMCASfirstINNERJOINCASsecond

ONfirst.Cpno=second.Cno;查詢結(jié)果如圖3-14所示。CnoCpno1501011501051501021501011501031501044.外連接

而在外部連接中，參與連接的表有主從之分，以主表的每行數(shù)據(jù)去匹配從表的數(shù)據(jù)列。符合連接條件的數(shù)據(jù)將直接返回到結(jié)果集中，對那些不符合連接條件的列，將被填上NULL值后再返回到結(jié)果集中?！纠?-36】

查詢所有學(xué)生的選課情況（沒有選課的學(xué)生只輸出學(xué)生的基本信息）。方法一：使用WHERE連接。

SELECTS.Sno,Sn,Sex,Age,Dno,SC.Cno,ScoreFROMS,SCWHERES.Sno=SC.Sno(*);

方法二：使用JOIN連接。

SELECTS.Sno,Sn,Sex,Age,Dno,SC.Cno,ScoreFROMSLEFTJOINSCONS.Sno=SC.Sno;左外部連接右外部連接SnuSnSexAgeDeptCnoScore100101姜珊女180115010160100101姜珊女180115010270100102李思女170215010280100103孫浩男210315010372100104周強(qiáng)男2004100105李斌男1901100106黃琪男2102圖3-15外連接查詢結(jié)果3.3.3嵌套查詢

在WHERE子句中包含一個(gè)形如SELECT-FROM-WHERE的查詢塊，此查詢塊稱為子查詢或嵌套查詢。返回一個(gè)值的子查詢使用比較運(yùn)算符（=,>,<,>=,<=,!=）嵌套查詢通常分為不相關(guān)子查詢和相關(guān)子查詢兩類。（1）不相關(guān)子查詢。子查詢的查詢條件不依賴于父查詢。子查詢可以獨(dú)立運(yùn)行，并且只執(zhí)行一次，執(zhí)行完畢后將值傳遞給外部查詢?！纠?-37】查詢選修“150102”號課程的學(xué)生姓名。

SELECTSnFROMSWHERESnoIN(SELECTSnoFROMSCWHERECno='150102');（2）相關(guān)子查詢子查詢的查詢條件依賴于父查詢。子查詢不能獨(dú)立運(yùn)行，必須依靠父查詢數(shù)據(jù)，并且外部查詢執(zhí)行一行，子查詢就執(zhí)行一次?！纠?-38】

查詢每個(gè)部門中教師年齡大于本部門學(xué)生平均年齡的教師的姓名、部門號和年齡。

SELECTTn,Dept,AgeFROMTWHEREAge>(SELECTAVG(Age)FROMSWHERET.Dept=S.Dept);

嵌套查詢中最常見的是不相關(guān)子查詢1．帶有IN謂詞的子查詢

帶有IN謂詞的子查詢是指父查詢和子查詢之間用IN進(jìn)行連接，判斷某個(gè)屬性列值是否在子查詢的結(jié)果中?！纠?-39】

查詢與“王紅”職稱相同的教師。本查詢可以分成如下兩步完成：（1）確定“王紅”的職稱。

SELECTProfFROMTWHERETn='王紅';（2）確定與“王紅”職稱相同的教師。

SELECTTnFROMTWHEREProfIN(SELECTProfFROMTWHERETn='王紅'）；使用復(fù)合連接條件查詢來實(shí)現(xiàn)：

SELECTt1.TnFROMTASt1,TASt2WHEREt1.Prof=fANDt2.Tn='王紅';2．帶有比較運(yùn)算符的子查詢使用>、<、=、>=、<=、!=或<>等比較運(yùn)算符。帶有比較運(yùn)算符的子查詢是指父查詢和子查詢之間用比較運(yùn)算符進(jìn)行連接。

【例3-41】查詢與“王紅”職稱相同的教師。

SELECTT.TnFROMTWHEREProf=(SELECTProfFROMTWHERETn='王紅');

3．帶有ANY或ALL謂詞的子查詢當(dāng)子查詢的返回值為一個(gè)集合時(shí)，除了可以使用IN連接詞以外，還可以使用ANY或ALL謂詞。注意，使用ANY或ALL謂詞時(shí)必須同時(shí)使用比較運(yùn)算符。表達(dá)式含義>ANY大于子查詢中的某個(gè)值<ANY小于子查詢中的某個(gè)值>=ANY大于等于子查詢中的某個(gè)值<=ANY小于等于子查詢中的某個(gè)值=ANY等于子查詢中的某個(gè)值!=ANY或<>ANY不等于子查詢中的某個(gè)值>ALL大于子查詢中的所有值<ALL小于子查詢中的所有值>=ALL大于等于子查詢中的所有值<=ALL小于等于子查詢中的所有值=ALL等于子查詢中的所有值!=ALL或<>ALL不等于子查詢中的所有值【例3-42】

查詢其他部門中比“信電學(xué)院”任意一個(gè)（其中某一個(gè)）學(xué)生年齡小的學(xué)生的姓名和年齡。方法1:

SELECTSn,AgeFROMSWHEREDept<>'信電'ANDAge<ANY(SELECTAgeFROMSWHEREDept='信電');

方法2:

SELECTSn,AgeFROMSWHEREdept<>'信電'ANDAge<(SELECTMAX(Age)FROMSWHEREdept='信電');【例3-43】

查詢其他部門中比“信電學(xué)院”所有學(xué)生年齡都小的學(xué)生的姓名和年齡。方法1:

SELECTSn,AgeFROMSWHEREDept<>'信電'ANDAge<ALL(SELECTAgeFROMSWHEREDept='信電');方法2：

SELECTSn,AgeFROMSWHEREDept<>'信電'ANDAge<(SELECTMIN(Age)FROMSWHEREDept='信電');4．帶有EXISTS謂詞的子查詢

帶有EXISTS的子查詢不返回任何實(shí)際數(shù)據(jù)，它只得到邏輯值“真”或“假”。當(dāng)子查詢的的查詢結(jié)果集合為非空時(shí)，外層的WHERE子句返回真值，否則返回假值。NOTEXISTS與此相反。含有IN的查詢通?？捎肊XISTS表示，但反過來不一定?！纠?-44】

查詢所有選修了“150102”號課程的學(xué)生的姓名和部門號。

SELECTSn,DeptFROMSWHEREEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno='150102');【例3-45】

查詢所有選修了全部課程的學(xué)生的姓名和部門。

SELECTsn,deptFROMSWHERENOTEXISTS(SELECT*FROMCWHERENOTEXISTS(SELECT*FROMSCWHERESno=S.SnoANDCno=C.Cno));

3.3.4集合查詢并操作UNION、交操作INTERSECT和差操作MINUS。SQLServer的Transact-SQL語言只提供UNION運(yùn)算符，實(shí)現(xiàn)并操作。

1．UNION操作使用UNION操作符將來自不同查詢的數(shù)據(jù)組合起來，形成一個(gè)具有綜合信息的查詢結(jié)果。參加合并查詢的各子查詢的使用的表結(jié)構(gòu)應(yīng)該相同?！纠?-46】

查詢年齡在30歲以上和部門號為“01”的所有教師的情況。

SELECT*FROMTWHEREAge>30UNIONSELECT*FROMTWHEREDno='01';

2．集合差/集合交運(yùn)算

EXCEPT運(yùn)算符可以實(shí)現(xiàn)集合差操作，即從左查詢中返回右查詢沒有找到的所有非重復(fù)值。INTERSECT運(yùn)算符可實(shí)現(xiàn)集合交操作，即返回INTERSECT操作符左右兩邊的兩個(gè)查詢都返回的所有非重復(fù)值?！纠?-47】

查詢所有非信電學(xué)院的學(xué)生。

SELECT*FROMSEXCEPTSELECT*FROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信電');【例3-48】

查詢所有職稱為“講師”的女教師。方法1:

SELECT*FROMTWHERESex='女'INTERSECTSELECT*FROMTWHEREProf='講師';方法2:

SELECT*FROMTWHERESex='女'ANDProf='講師';

3.4SQL的數(shù)據(jù)操作數(shù)據(jù)操作添加數(shù)據(jù)（INSERTINTO）修改數(shù)據(jù)（UPDATE）刪除數(shù)據(jù)（DELETE）

3.4.1插入數(shù)據(jù)用SQL命令插入數(shù)據(jù)

INSERTINTO

1．插入單個(gè)元組INSERTINTO<表名>[(<列名1>[,<列名2>…])]VALUES(<值>)

【例3-50】

在學(xué)生表S中插入一條學(xué)生記錄（學(xué)號：100108，姓名：朱良）。

INSERTINTOS(Sno,Sn)VALUES('100108','朱良');必須用逗號將各個(gè)數(shù)據(jù)分開，字符型數(shù)據(jù)要用單引號括起來。如果INTO子句中沒有指定列名，則新添加的記錄必須在每個(gè)屬性列上均有值，且VALUES子句中值的排列順序要和表中各屬性列的排列順序一致。INSERTINTO<表名>[(<列名1>[,<列名2>…])]子查詢

2．插入多個(gè)元組【例3-51】

將每一個(gè)部門學(xué)生的平均年齡存入新表SAVGA中（本表包括兩個(gè)屬性列：部門號（Dno）和平均年齡（Avgage））。

CREATETABLESAVGA(DnoCHAR(15)AvgageINT);INSERTINTOSAVGASELECTDno,AVG(Age)FROMSGROUPBYDno;3.4.2修改數(shù)據(jù)用SQL命令修改數(shù)據(jù) UPDATE<表名> SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>]… [WHERE<條件>]

1．修改單個(gè)或部分元組的值

【例3-52】

將學(xué)生“100107”的年齡改為22歲。

UPDATESSETAge=22WHERESno='100107';2．修改全部元組的值【例3-53】

將所有學(xué)生的年齡增加1歲。

UPDATESSETAge=Age+1;3．利用子查詢修改部分元組的值【例3-54】

將信電學(xué)院全體學(xué)生的成績置0。

UPDATESCSETScore=0WHERESnoIN(SELECTSnoFROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信電'));3.4.3刪除數(shù)據(jù)DELETEFROM<表名>[WHERE<條件>]1．刪除單個(gè)或多個(gè)元組【例3-55】

刪除學(xué)號為“100107”和“100108”的學(xué)生的記錄。

DELETEFROMS

WHERESno='100107'ORSno='100108';

2．刪除全部元組的值

【例3-56】

刪除所有學(xué)生的選課記錄。

DELETEFROMSC;3．帶子查詢的刪除語句子查詢同樣也可以嵌套在DELETE語句中，用以構(gòu)造執(zhí)行刪除操作的條件。

【例3-57】

刪除信電學(xué)院所有學(xué)生的選課記錄。

DELETEFROMSCWHERESnoIN(SELECTSnoFROMSWHEREDno=(SELECTDnoFROMDWHEREDept='信電'))；3.5視圖視圖是虛表，其數(shù)據(jù)不進(jìn)行存儲，其記錄來自基本表，只在數(shù)據(jù)庫中存儲其定義。3.5.1建立視圖CREATEVIEW<視圖名>[(<視圖列表>)] AS<子查詢>

【例3-58】

建立信電學(xué)院教師的視圖，并要求進(jìn)行修改和插入操作時(shí)仍保持該視圖只有信電學(xué)院教師。

CREATEVIEWITteacherASSELECTTno,Tn,ProfFROMTWHEREDno=(SELECTDnoFROMDWHEREDept='信電')WITHCHECKOPTION;

2．多表視圖建立在多個(gè)基本表（視圖）上，這樣的視圖稱為多表視圖。

【例3-59】

建立選修了“150102”號課程的所有女生的視圖。

CREATEVIEWFStu(Sno,Sn,Sex)ASSELECTS.Sno,Sn,Sex,BPFROMS,SCWHERESex='女'ANDS.Sno=SC.SnoANDSC.Sno='150102';

【例3-60】

建立信電學(xué)院所有職稱為“講師”的教師及所任課程的視圖。

CREATEVIEWITea(Tno,Tn,TC.Cno)ASSELECTITteacher.Tno,Tn,TC.cnoFROMITteacher,TCWHEREITteacher.Tno=TC.TnoANDITteache.Prof='講師';

3.帶表達(dá)式的視圖定義視圖時(shí)可以根據(jù)應(yīng)用的需要，設(shè)置一些派生屬性列。因這些派生屬性在基本表中并不實(shí)際存在，所以有時(shí)也稱它們?yōu)樘摂M列。帶虛擬列的視圖稱為帶表達(dá)式的視圖。

【例3-61】

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

CREATEVIEWSBir(Sno,Sn,Sbirth) AS SELECTSno,Sn,2010-S.Age FROMS4．分組視圖

使用帶有集函數(shù)和GROUPBY字句的查詢來定義的視圖稱為分組視圖。

【例3-62】定義一個(gè)反映學(xué)生的學(xué)號及其平均成績的視圖。

CREATEVIEWStuAvg(Sno,Savg)ASSELECTSno,AVG(Score)FROMSCGROUPBYSno;3.5.2查詢視圖視圖定義后，對視圖的查詢操作如同對基本表的查詢操作一樣。

【例3-63】

查找視圖ITteacher中職稱為“教授”的教師號和姓名。

方法1：SELECTTno,TnFROMITteacherWHERE(Prof='教授')方法2：SELECTTno,TnFROMTWHERE(Prof='教授'ANDDno=(SELECTDnoFROMDWHEREDept='信電'));視圖的建立簡化了查詢操作3.5.3刪除視圖視圖建好后，若刪除導(dǎo)出此視圖的基本表，該視圖也將失效，但一般不會被自動刪除。刪除視圖通常需要顯式地使用DROPVIEW語句進(jìn)行。

DROPVIEW<視圖名>【例3-64】

刪除視圖Itteacher。

DROPVIEWITteacher注意：執(zhí)行該語句后，ITteacher視圖的定義將從數(shù)據(jù)字典中刪除。由ITteacher視圖導(dǎo)出的ITea視圖的定義雖然在數(shù)據(jù)字典中，但該視圖已無法使用，所以也應(yīng)該刪除。3.5.4更新視圖由于視圖是一張?zhí)摫?，所以對視圖的更新，最終轉(zhuǎn)換成對基本表的更新。其語法格式如同對基本表的更新操作一樣。視圖的優(yōu)點(diǎn)

添加修改

刪除INSERTUPDATE

DELETE利于數(shù)據(jù)保密

簡化查詢操作保證數(shù)據(jù)的邏輯獨(dú)立性1．UPDATE操作

【例3-65】

將信電學(xué)院教師的視圖中的工號為“05”的教師的姓名修改為“張賓”。方法1:

UPDATEITteacherSETTn='張賓'WHERETno='05';方法2:

UPDATETSETTn='張賓'WHERETno='05'ANDdept='信電';2．INSERT操作

【例3-66】

向信電學(xué)院教師的視圖插入一條新記錄（“07”，“張平”，“副教授”）。

INSERTINTOITteacherVALUES('07','張平','副教授');

DBMS自動將其轉(zhuǎn)換為對基本表T的更新，因?yàn)楸疽晥D是信電學(xué)院教師的視圖，所以系統(tǒng)自動會將部門號“01”放入VALUES子句中，更新語句為：

INSERTINTOT(Tno,Tn,Prof,Dno)

VALUES('07','張平','副教授','01');3．DELETE操作【例3-67】

將向信電學(xué)院教師的視圖中插入的記錄（“07”，“張平”，“副教授”）刪除。

DELETEFROMITteacherWHERETno='07';DBMS將其轉(zhuǎn)換為對基本表的更新，同理根據(jù)例3-66要將表達(dá)式“Dept='信電'”放入WHERE條件表達(dá)式中，轉(zhuǎn)換后的更新語句為：

DELETEFROMTWHERETno='07'ANDDno=(SELECTDnoFROMDWHEREDept='信電'));

3.5.5視圖的作用1．視圖能使用戶以多種角度看待同一數(shù)據(jù)2．視圖能夠簡化用戶的操作3．視圖為重構(gòu)數(shù)據(jù)庫提供一定程度邏輯獨(dú)立性視圖可以使應(yīng)用程序和數(shù)據(jù)庫表在一定程度上獨(dú)立。4．視圖能夠?yàn)闄C(jī)密數(shù)據(jù)提供安全保護(hù)

3.6SQL的數(shù)據(jù)控制1．SQL的用戶

SQL中的用戶分為兩種：SQL服務(wù)器用戶（DBA）和數(shù)據(jù)庫用戶。在SQLServer中，有3種特殊的用戶：DBA、DBO和一般用戶。DBA對整個(gè)系統(tǒng)有操作權(quán)限；DBO對其所建立的數(shù)據(jù)庫具有全部操作權(quán)限；一般用戶對給定的數(shù)據(jù)庫只有被授權(quán)的操作權(quán)限。3.6.1授權(quán)權(quán)限

角色角色是多種權(quán)限的集合，當(dāng)要為某一用戶同時(shí)授予或收回多項(xiàng)權(quán)限時(shí)，則可以把這些權(quán)限定義為一個(gè)角色。這樣就簡化了管理數(shù)據(jù)庫用戶權(quán)限的工作。系統(tǒng)權(quán)限：數(shù)據(jù)庫用戶能夠?qū)?shù)據(jù)庫系統(tǒng)進(jìn)行某種特定的操作的權(quán)力對象權(quán)限：數(shù)據(jù)庫用戶在指定的數(shù)據(jù)庫對象上進(jìn)行某種特定的操作的權(quán)力

如：查詢、添加、修改和刪除2．權(quán)限與角色3．用戶授權(quán)GRANT<權(quán)限>|<角色>[,<權(quán)限>|<角色>]…[ON對象類型><對象名>]TO<用戶名>|<角色>|PUBLIC[,<用戶名>|<角色>]…[WITHGRANTOPTION];【例3-68】

把對表S的INSERT和UPDATE權(quán)限授予用戶User1和User2，并允許他們將此權(quán)限授予其他用戶。

GRANTINSERT,UPDATEONTABLESTOUser1,U