數(shù)據(jù)庫第三章

SQL概述數(shù)據(jù)定義查詢更新視圖數(shù)據(jù)控制關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL第3章1

SQL語言的特點(diǎn)掌握SQL語言的關(guān)系表定義及更新操作掌握SQL語言強(qiáng)大的查詢功能掌握SQL語言的關(guān)系表更新操作第3章重點(diǎn)用SQL語言正確完成復(fù)雜查詢第3章難點(diǎn)2●

SQL的發(fā)展及現(xiàn)狀

1974年，由Boyce和Chamberlin提出。1975－1979，IBM公司SanJoseResearchLab研制了關(guān)系數(shù)據(jù)庫管理系統(tǒng)原型SystemR并實(shí)現(xiàn)了這種語言。1986年美國國家標(biāo)準(zhǔn)局(ANSI)的數(shù)據(jù)庫委員會(huì)X3H2批準(zhǔn)SQL作為關(guān)系數(shù)據(jù)庫語言的美國標(biāo)準(zhǔn)（SQL-86）。ANSI不斷修改和完善SQL標(biāo)準(zhǔn)，公布了SQL-89、SQL-92(SQL2)、SQL-99(SQL3)大部分DBMS產(chǎn)品都支持SQL，成為操作數(shù)據(jù)庫的標(biāo)準(zhǔn)語言33.1SQL概述3.1.1SQL的特點(diǎn)SQL是一種介于關(guān)系代數(shù)和關(guān)系演算之間的結(jié)構(gòu)化查詢語言。綜合統(tǒng)一

集DDL、DML、DCL的功能于一體，可以獨(dú)立完成數(shù)據(jù)庫生命周期中的全部活動(dòng)。

高度非過程化

無需了解存取路徑，存取路徑的選擇以及SQL語句的操作過程由系統(tǒng)自動(dòng)完成。

面向集合的操作方式

操作對象、查找結(jié)果、插入、刪除、更新操作的對象可以是元組集合。以同一種語法結(jié)構(gòu)提供兩種使用方式

可獨(dú)立的用于聯(lián)機(jī)交互的使用方式，用戶可在終端鍵入SQL命令對數(shù)據(jù)庫進(jìn)行操作；SQL語句可嵌入到高級(jí)語言程序中，供程序員設(shè)計(jì)程序時(shí)使用。

語言簡單，易學(xué)易用

核心功能只有9個(gè)動(dòng)詞。4數(shù)據(jù)查詢(DataQuery)SQL語言

數(shù)據(jù)操縱(DataManipulation)數(shù)據(jù)定義(DataDefinition)數(shù)據(jù)控制(DataControl)SQL功能數(shù)據(jù)查詢數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制動(dòng)詞SELECTCREATE,DROP,ALTERINSERT,UPDATE,DELETEGRANT,REVOKE表3.1SQL語言的動(dòng)詞5SQL用戶基本表1視圖1視圖2基本表2基本表3基本表4存儲(chǔ)文件1存儲(chǔ)文件2外模式模式內(nèi)模式圖3.1SQL對關(guān)系數(shù)據(jù)庫模型的支持3.1.2SQL語言的基本概念6基本概念：用戶可以用SQL語言對視圖(View)和基本表(BaseTable)進(jìn)行查詢等操作，在用戶觀點(diǎn)里，視圖和表一樣，都是關(guān)系。

視圖是從一個(gè)或多個(gè)基本表中導(dǎo)出的表，本身不存儲(chǔ)在數(shù)據(jù)庫中，只有其定義，可以將其理解為一個(gè)虛表。

基本表是本身獨(dú)立存在的表，每個(gè)基本表對應(yīng)一個(gè)存儲(chǔ)文件，一個(gè)表可以帶若干索引，存儲(chǔ)文件及索引組成了關(guān)系數(shù)據(jù)庫的內(nèi)模式。7SQL

Server數(shù)據(jù)庫創(chuàng)建方法83.2數(shù)據(jù)定義操作對象表視圖索引操作方式創(chuàng)建CREATETABLE刪除DROPTABLE修改ALTERTABLECREATEINDEXDROPINDEXCREATEVIEWDROPVIEW表3.2SQL的數(shù)據(jù)定義語句視圖是基于基本表的虛表，索引是依附于基本表的，因此，SQL通常不提供修改視圖定義和索引定義的操作。（刪除后重建；Oracle允許直接修改視圖定義）操作對象表視圖索引操作方式創(chuàng)建CREATETABLE刪除DROPTABLE修改ALTERTABLECREATEINDEXDROPINDEXCREATEVIEWDROPVIEW表3.2SQL的數(shù)據(jù)定義語句93.2.1定義、刪除與修改基本表定義基本表格式：CREATETABLE<表名>

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

[,<表級(jí)完整性約束條件>])；建立一個(gè)新表，表中無記錄10

CREATETABLE

Student

(

Sno

CHAR(5)NOTNULL

UNIQUE,/*列級(jí)完整性約束條件*/

Sname

CHAR(20)UNIQUE,

Ssex

CHAR(1),

Sage

INT,

Sdept

CHAR(15))；例1：建立學(xué)生表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性構(gòu)成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名取值也唯一。11

CREATETABLES (S#CHAR(6)NOTNULL,/*學(xué)號(hào)*/

SNAMECHAR(8)NOTNULL,/*姓名*/

SAGESMALLINT,/*年齡*/

SDCHAR(10),/*系名*/

SEXCHAR(2)DEFAULT

'男'

CHECK((SEX='男')OR(SEX='女')),/*性別*/

PRIMARYKEY(S#))；例2：建立學(xué)生S、課程C、選課SC三個(gè)表S表：12

CREATETABLEC (C#CHAR(6)NOTNULL,/*課程號(hào)*/

CNAMECHAR(30)NOTNULL,/*課程名*/

TNAMECHAR(8),/*教師姓名*/

PC#CHAR(6),/*先行課*/

PRIMARYKEY(C#))；例2：建立學(xué)生S、課程C、選課SC三個(gè)表C表：13CREATETABLESC(S#CHAR(6)NOTNULL,/*學(xué)號(hào)*/

C#CHAR(6)NOTNULL,/*課程號(hào)*/

GRSMALLINT,/*成績*/PRIMARYKEY(S#,C#),FOREIGNKEY(S#)REFERENCESS(S#),FOREIGNKEY(C#)REFERENCESC(C#),CHECK((GRISNULL) OR(GRBETWEENOAND100)))；例2：建立學(xué)生S、課程C、選課SC三個(gè)表SC表：14例3：設(shè)關(guān)系S_G(S#,AVG_G),把平均成績大于80的男生的學(xué)號(hào)及平均成績存入S_G中。CREATETABLES_G(S#CHAR(6)NOTNULL,

AVG_GSMALLINTDEFAULTNULL)AS(SELECTS#，AVG(ALLGRADE)FROMSCWHERES#IN

(SELECTS#FROMSWHERESEX=‘男’)

GROUPBYS#

HAVING

AVG(ALLGRADE)>80)15修改基本表改變表名增加列改變列的數(shù)據(jù)類型刪除列的約束刪除列改變列名基本表的修改操作：16格式：ALTERTABLE<表名>

[ADD

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

完整性約束][DROP<列名>

][DROP<完整性約束名>] [ALTERCOLUMN<列名><數(shù)據(jù)類型>] [RENAME<舊表名>TO<新表名>];

增加新列增加新的完整性約束條件刪除指定列刪除指定的完整性約束條件修改列的定義（列名和數(shù)據(jù)類型）改變表名17例4：在Student表中增加“入學(xué)時(shí)間”列，數(shù)據(jù)類型為日期型。

ALTERTABLE

Student

ADD

Scome

DATETIME；例5：把年齡的數(shù)據(jù)類型改為半字長整數(shù).ALTERTABLEStudentALTERCOLUMNSageSMALLINT；例6：刪除學(xué)生姓名必須取唯一值的約束。

ALTERTABLEStudentDROPCONSTRAINTNAME；18刪除基本表格式：DROPTABLE<表名>；刪除一個(gè)表，及與該表相關(guān)的索引、視圖、碼和外部碼。例7：刪除Student表。

DROPTABLEStudent;193.2.2建立與刪除索引建立索引是加快查詢速度的有效手段，一個(gè)基本表上可建立一個(gè)或多個(gè)索引，以提供多種存取路徑，加快查找速度。建立與刪除索引由DBA或表的屬主負(fù)責(zé)完成，用戶不必也不能選擇索引。20建立索引格式：CREATE[UNIQUE][CLUSTER]INDEX<索引名>

ON<表名>(<列名>[<次序>],[,<列名>[<次序>]]…)；

UNIQUE

表示索引值唯一。

CLUSTER

表示索引是聚簇索引,指索引項(xiàng)的順序與表中記錄的物理順序一致。索引一旦建立，交由系統(tǒng)使用和維護(hù)。對指定的表的列建立索引。21例8：為學(xué)生－課程數(shù)據(jù)庫中的S,C,SC三個(gè)表建立索引。其中S表按學(xué)號(hào)升序建唯一索引，C表按課程號(hào)升序建唯一索引，SC表按學(xué)號(hào)升序和課程號(hào)降序建唯一索引。CREATEUNIQUEINDEXSIDXONS(S#);CREATEUNIQUEINDEXCIDXONC(C#);CREATEUNIQUEINDEXSCIDXONS(S#ASC,C#DESC);22例9：為學(xué)生Student表建立聚簇索引（按學(xué)生姓名升序建索引）。CREATECLUSTERINDEXSNAMEIDXONStudent(Sname);一個(gè)基本表最多只能建立一個(gè)聚簇索引。建立索引后，更新索引列數(shù)據(jù)時(shí)，往往導(dǎo)致表中記錄的物理順序的變更，代價(jià)較大，因此對于經(jīng)常更新的列不宜建立聚簇索引。23刪除索引格式：

DROPINDEX<表名.索引名>；例10：刪除Student表的SNAMEIDX索引。

DROPINDEX

Student.SNAMEIDX

；243.3查詢

基本結(jié)構(gòu)

SelectA1,A2,...,An

FromR1,R2,...,Rm

WherePSelectWhereFrom

πA1,A2,...,An(σp(R1×R2×...×Rm))數(shù)據(jù)查詢是數(shù)據(jù)庫應(yīng)用的核心功能。25格式

SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[,<目標(biāo)列表達(dá)式>]……

FROM<表名或視圖名>[,<表名或視圖名>]…[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]]；26SELECT…FROM常用語句執(zhí)行過程

SELECT…⑤

投影

FROM…①TABLE→內(nèi)存

WHERE…②

選取元組

GROUP…③

分組

HAVING…④

選擇分組

[{UNION|…}⑥

查徇結(jié)果的集合運(yùn)算

SELECT…]①～⑤

ORDERBY……⑦

排序輸出273.3.1單表查詢

選擇表中的若干列

選擇表中的若干元組

對查詢結(jié)果排序

使用集函數(shù)

對查詢結(jié)果分組281.查詢指定列例1.查詢?nèi)w學(xué)生的學(xué)號(hào)及姓名。學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)查詢各列的先后順序可以與表中的順序不一致。例2.查詢?nèi)w學(xué)生的姓名、學(xué)號(hào)、所在系。SELECTSno,SnameFROMStudent；SELECTSname,Sno,Sdept FROMStudent；29302.查詢?nèi)苛袑W(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)例3.查詢?nèi)w學(xué)生的全部信息。SELECTSno,Sname,Ssex,Sage,Sdept FROMStudent；

等價(jià)于SELECT*FROMStudent;31323.查詢經(jīng)過計(jì)算的值學(xué)生表：Student(Sno,Sname,Ssex,Sage,Sdept)例4.查詢?nèi)w學(xué)生的姓名及其出生年份。SELECTSname,2006-Sage FROMStudent；33例5.查詢?nèi)w學(xué)生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname,'YearofBirth:',2006-Sage,LOWER(Sdept)FROMStudent；SELECTSnameNAME,'YearofBirth:'

BIRTH,2006-Sage

BIRTHDAY,LOWER(Sdept)DEPARTMENT

FROMStudent；34351.消除取值重復(fù)的行例6.查詢選修了課程的學(xué)生學(xué)號(hào)。學(xué)生課程表：SC(Sno,Cno,Grade)消除重復(fù)的行SELECTSnoFROMSC；SELECTDISTINCTSno FROMSC；36372.查詢滿足條件的元組Where子句——運(yùn)算符比較：<、<=、>、>=、=、<>、!=、!>、!<not+上述比較運(yùn)算符確定范圍：BetweenAandB、NotBetweenAandB確定集合：IN、NOTIN字符匹配：LIKE、NOTLIKE空值：ISNULL、ISNOTNULL多重條件：AND、OR38例7.查詢計(jì)算機(jī)系全體學(xué)生的名單。(1)比較大小SELECTSnameFROMStudentWHERESdept='CS'；3940例8.查詢所有年齡在20歲以下的學(xué)生姓名及其年齡。SELECTSname,Sage FROMStudentWHERESage<

20；SELECTSname,Sage FROMStudentWHERENOTSage>=

20；或4142例9.查詢考試成績有不及格的學(xué)生的學(xué)號(hào)。SELECTDISTINCTSno FROMSCWHEREGrade<60；4344例10.查詢年齡在20～23歲（包括20歲和23歲）之間的學(xué)生的姓名、系別和年齡。(2)確定范圍SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23；SELECTSname,Sdept,SageFROMStudentWHERESage>=20ANDSage<=

23；4546SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23；例11.查詢年齡不在20～23歲之間的學(xué)生姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESage<20ORSage>

23；4748例12.查詢信息系(IS)、數(shù)學(xué)系(MA)和計(jì)算機(jī)科學(xué)系(CS)學(xué)生的姓名和性別。(3)確定集合SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept=

'IS'

ORSdept=

'MA'ORSdept=

'CS'；4950例13.查詢既不是信息系(IS)、數(shù)學(xué)系(MA)也不是計(jì)算機(jī)科學(xué)系(CS)學(xué)生的姓名和性別。SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept!=

'IS'ANDSdept!=

'MA'

ANDSdept!=

'CS'；5152(4)字符匹配Where子句——Like格式:[NOT]LIKE

'<匹配串>'[ESCAPE'<換碼字符>']%:表示任意長度(長度≥0）的字符串。_:表示單個(gè)的任意字符ESCAPE'\':表示\為換碼字符，匹配串中緊跟在\后面的字符‘%’或‘_’，被定義為普通字符(不作通配符用)53例14.查詢學(xué)號(hào)為95001的學(xué)生的詳細(xì)情況。如果LIKE后面的匹配串中不含通配符，則可以用＝運(yùn)算符取代LIKE謂詞；用!=或<>取代NOTLIKE謂詞。SELECT*FROMStudentWHERESnoLIKE

'95001'；SELECT*FROMStudentWHERESno=

'95001'；等價(jià)于5455例15.查詢所有姓王的學(xué)生的姓名、學(xué)號(hào)和性別。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE

'王％'；5657例16.查詢姓李，且姓名為兩個(gè)漢字的學(xué)生姓名。SELECTSnameFROMStudentWHERESnameLIKE'李_'

；5859例17.查詢名字中第二個(gè)字為“晨”字的學(xué)生姓名和學(xué)號(hào)。SELECTSname,SnoFROMStudentWHERESnameLIKE

'_晨%

'

；6061例18.查詢所有不姓劉的學(xué)生姓名。SELECTSnameFROMStudentWHERESnameNOTLIKE

'劉%

'

；6263例19.查詢DB_Design課程的課程號(hào)和學(xué)分。SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'

ESCAPE

'\'

；64例20.查詢以“DB_”開頭，且倒數(shù)第三個(gè)字符為i的課程的詳細(xì)情況。SELECT*FROMCourseWHERECnameLIKE

'DB\_%i__'

ESCAPE'\'；65(5)涉及空值的查詢例21.某些學(xué)生選修課后沒有參加考試，所以有選課錄，但沒有成績。查詢?nèi)鄙俪煽兊膶W(xué)生的學(xué)號(hào)和相應(yīng)課程號(hào)。SELECTSno,CnoFROMSCWHEREGradeISNULL；6667例22.查詢所有有成績的學(xué)生學(xué)號(hào)和課程號(hào)。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL；6869(6)多重條件查詢例23.查詢計(jì)算機(jī)系年齡在20歲以下的學(xué)生姓名。SELECTSnameFROMStudentWHERESdept='CS'

ANDSage<20；7071例24.查詢信息系(IS)、數(shù)學(xué)系(MA)和計(jì)算機(jī)科學(xué)系(CS)的男學(xué)生的姓名和年齡。邏輯運(yùn)算符AND的優(yōu)先級(jí)比OR高，可通過括號(hào)改變優(yōu)先級(jí)。SELECTSname,SageFROMStudentWHERE(Sdept='IS'

ORSdept='MA'

OR

Sdept='CS')ANDSsex='男'；72733.對查詢結(jié)果的排序例25.查詢選修了3號(hào)課程的學(xué)生的學(xué)號(hào)及其成績，查詢結(jié)果按分?jǐn)?shù)的降序排列。SELECTSno,GradeFROMSCWHERECno='3'

ORDERBYGradeDESC；7475例26.查詢?nèi)w學(xué)生情況，查詢結(jié)果按所在系的系號(hào)升序排列，同在一個(gè)系的按年齡降序排列。SELECT*FROMStudent

ORDERBYSdeptASC,SageDESC；SELECT*FROMStudent

ORDERBYSdept,SageDESC；76774.使用集函數(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]<列名>)求一列值中的最小值78例27.查詢學(xué)生總?cè)藬?shù)。SELECTCOUNT

(*)FROMStudent;例28.查詢選修了課程的學(xué)生人數(shù)。SELECTCOUNT

(DISTINCTSno)FROMSC;79例29.計(jì)算2號(hào)課程的學(xué)生平均成績。SELECTAVG(Grade)FROMSCWHERECno='2';例30.查詢選修2號(hào)課程的學(xué)生最高成績。SELECTMAX(Grade)FROMSCWHERECno='2';80815.對查詢結(jié)果分組例31.求各個(gè)課程號(hào)及相應(yīng)的選課人數(shù)。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;82例32.查詢選修了3門以上課程的學(xué)生學(xué)號(hào)。SELECTSnoFROMSCGROUPBYSnoHAVING

COUNT(*)>=3;HAVING用于分組后按一定條件對這些分組進(jìn)行篩選。

WHERE子句作用于基本表或視圖，從中選擇滿足條件的元組

HAVING短句作用于組，從中選擇滿足條件的組8384SQL

Server數(shù)據(jù)庫查詢演示853.3.2連接查詢

等值與非等值連接查詢

自身連接

外連接

復(fù)合條件連接861.等值與非等值連接查詢連接條件一：

[<表名1>.]<列名1><比較運(yùn)算符>[<表名2>.]<列名2>連接條件二：

[<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>

比較運(yùn)算符主要有：＝、>、<、>=、<=、!=。連接查詢中用來連接兩個(gè)表的條件稱為連接條件或連接謂詞。連接謂詞中的列名稱為連接字段，其各字段應(yīng)是可比的。87例33.查詢每個(gè)學(xué)生及其選修課程的情況。

等值連接SELECTStudent.*,SC.*FROMStudent,SCWHEREStudent.Sno=

SC.Sno;自然連接：在等值連接中把目標(biāo)列中重復(fù)的屬性列去掉。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=

SC.Sno;88892.自身連接例34.查詢每門課的間接先修課。一個(gè)表與其自身進(jìn)行連接SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=

SECOND.Cno;90913.外連接在連接條件的某側(cè)加上(＊)或(+)，表示該側(cè)所對應(yīng)的表中可形成一個(gè)各數(shù)據(jù)項(xiàng)均為空值的萬能替代行，用來與另一側(cè)對應(yīng)的表中所有不滿足條件的元組進(jìn)行連接。外連接符(＊)或（+）出現(xiàn)在左側(cè)稱為右外連接、出現(xiàn)在右側(cè)稱為左連接、兩側(cè)都出現(xiàn)的稱為全外連接。92例35.查詢每個(gè)學(xué)生及其選修課的情況。SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMSCRIGHTJOINStudentONStudent.Sno=SC.Sno;93944.復(fù)合條件連接例36.查詢選修了2號(hào)課程且成績在90分以上的所有學(xué)生。WHERE子句中有多個(gè)連接條件SELECTStudent.Sno,SnameFROMStudent,SCWHEREStudent.Sno=SC.SnoANDSC.Cno='2'

ANDSC.Grade>=90;95例37.查詢每個(gè)學(xué)生的學(xué)號(hào)、姓名、選修課程名及成績。多表連接SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;96973.3.3嵌套查詢在SQL語言中，一個(gè)SELECT-FROM-WHERE語句稱為一個(gè)查詢塊。一個(gè)查詢塊嵌套在另一個(gè)查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。例如：SELECTSnameFROMStudentWHERESnoINSELECTSnoFROMSCWHERECno='2';外層查詢內(nèi)層查詢不能使用ORDERBY子句98

帶有IN謂詞的子查詢

帶有比較運(yùn)算符的子查詢

帶有ANY或ALL謂詞的子查詢

帶有EXISTS謂詞的子查詢3.3.3嵌套查詢99例38.查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。

SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='劉晨');(1)首先在Student關(guān)系中找出劉晨所在的系，結(jié)果為'IS'。(2)其次在Student關(guān)系中找出'IS'系的學(xué)生1.帶有IN謂詞的子查詢或SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='劉晨'；100101例39.查詢選修了課程名為“信息系統(tǒng)”的學(xué)生學(xué)號(hào)和姓名。

SELECTSno,Sname FROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='信息系統(tǒng)'));(1)首先在Course關(guān)系中找出“信息系統(tǒng)的課程號(hào)”，結(jié)果為3。(2)其次在SC關(guān)系中找出選修3號(hào)課程的學(xué)生(3)最后在Student關(guān)系中取出學(xué)號(hào)和姓名?；騍ELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='信息系統(tǒng)'

；1021032.帶有比較運(yùn)算符的子查詢例40.查詢與“劉晨”在同一個(gè)系學(xué)習(xí)的學(xué)生。內(nèi)存查詢返回的是單值時(shí)，可以用比較運(yùn)算符；子查詢要跟在比較符之后。SELECTSno,Sname,SdeptFROMStudentWHERESdept＝(SELECTSdeptFROMStudentWHERESname='劉晨');1041053.帶有ANY或ALL謂詞的子查詢子查詢返回單值時(shí)可以用比較運(yùn)算符，而使用ANY或ALL謂詞時(shí)必須同時(shí)使用比較運(yùn)算符。其語義為：>(>=、=)ANY

大于（大于等于、等于）子查詢結(jié)果中的某個(gè)值>(>=、=)ALL

大于（大于等于、等于）子查詢結(jié)果中的所有值<(<=)ANY

小于（小于等于）子查詢結(jié)果中的某個(gè)值<(<=)ALL

小于（小于等于）子查詢結(jié)果中的所有值!=(或<>)ANY

不等于子查詢結(jié)果中的某個(gè)值!=(或<>)ALL

不等于子查詢結(jié)果中的任何一個(gè)值106例41.查詢其他系中比信息系某一學(xué)生年齡小的學(xué)生姓名和年齡。

SELECTSname,Sage FROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或

SELECTSname,Sage FROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';107108例42.查詢其他系中比信息系所有學(xué)生年齡小的學(xué)生姓名和年齡。

SELECTSname,Sage FROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或

SELECTSname,SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';109＝<>或!=<<=>>=ANYIN__<MAX<=MAX>MIN>=MINALL__NOTIN<MIX<=MIN>MAX>MAX表3-4ANY,ALL謂詞與集函數(shù)及IN謂詞的等價(jià)轉(zhuǎn)換關(guān)系集函數(shù)實(shí)現(xiàn)子查詢比直接用ANY或ALL查詢效率更高。1104.帶有EXISTS謂詞的子查詢不相關(guān)子查詢：子查詢的查詢條件不依賴于父查詢的子查詢。相關(guān)子查詢(CorrelatedSubquery)：子查詢的查詢條件依賴于外層父查詢的某個(gè)屬性值的子查詢。帶EXISTS的子查詢就是相關(guān)子查詢

EXISTS表示存在量詞帶有EXISTS的子查詢不返回任何記錄的數(shù)據(jù)，只返回邏輯值

'True'

或'False'111例43-1.查詢所有選修了1號(hào)課程的學(xué)生姓名。

SELECTSnameFROMStudentWHERESnoIN(SELECTSNOFROMSCWHERESC.Cno='1');不相關(guān)子查詢112例43-2.查詢所有選修了1號(hào)課程的學(xué)生姓名。

SELECTSname FROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');執(zhí)行過程：

先在外層查詢中取Student表的第一個(gè)元組，用該元組的相關(guān)的屬性值（在內(nèi)層WHERE子句中給定的）處理內(nèi)層查詢，若外層的WHERE子句返回'TRUE'值，則此元組送入結(jié)果的表中。然后再取下一個(gè)元組；重復(fù)上述過程直到外層表的元組全部遍歷一次為止。相關(guān)子查詢113114說明：不關(guān)心子查詢的具體內(nèi)容，因此用SELECT*

Exists+子查詢用來判斷該子查詢是否返回元組當(dāng)子查詢的結(jié)果集非空時(shí)，Exists

為'True'當(dāng)子查詢的結(jié)果集為空時(shí)，Exists

為'False'

NOTEXISTS

：若子查詢結(jié)果為空，返回'TRUE'值，否則返回'FALSE'115例44.查詢沒有選修1號(hào)課程的學(xué)生姓名。

SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');或

SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSNOFROMSCWHERESC.Cno='1');不相關(guān)子查詢相關(guān)子查詢116117例45.查詢與劉晨在同一個(gè)系學(xué)習(xí)的學(xué)生。

SELECTSno,Sname,Sdept FROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='劉晨');或

SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='劉晨');118119SQL無全稱量詞，但可以把全稱量詞轉(zhuǎn)化為等價(jià)的存在量詞形式。例46：“查詢選修全部課程的學(xué)生姓名”(?x)P≡!(?x(!P))原命題等價(jià)于：“查詢這樣的學(xué)生a，不存在某個(gè)課程x，這名學(xué)生沒有選修”

120例46.查詢選修了全部課程的學(xué)生姓名。

SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));這門課他沒選這樣的課是不存在的121122例47.查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生號(hào)。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS

(SELECT*FROMSCSCY

WHERESCY.Sno='95002' ANDNOTEXISTS

(SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno))； 1233.3.4集合查詢例48.查詢計(jì)算機(jī)系的學(xué)生或者年齡不大于19歲的學(xué)生。SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=19124125例49.查詢選修了課程1或選修課程2的學(xué)生。SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2';126127例50.查詢計(jì)算機(jī)系的學(xué)生與年齡不大于19歲的學(xué)生的交集。SELECT*FROMStudentWHERESdept='CS'ANDSage<=19;例51.查詢選修了課程1的學(xué)生集合與選修課程2的學(xué)生集合的交集。SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2');128例52.查詢計(jì)算機(jī)系的學(xué)生與年齡不大于19歲的學(xué)生的差集。SELECT*FROMStudentWHERESdept='CS'ANDSage>19;例53.查詢選修課程1但沒有選修課程2的學(xué)生。SELECTSnoFROMSCWHERECno='1'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno='2');1293.3.5SELECT語句的一般格式SELECT[ALL|DISTINCT]<目標(biāo)列表達(dá)式>[別名][,<目標(biāo)列表達(dá)式>[別名]]……FROM<表名或視圖名>[別名][,<表名或視圖名>[別名]]……[WHERE<條件表達(dá)式>][GROUPBY<列名1>[HAVING<條件表達(dá)式>]][ORDERBY<列名2>[ASC|DESC]];1303.4數(shù)據(jù)更新

插入操作

INSERT

修改操作

UPDATE

刪除操作

DELETE1313.4.1插入數(shù)據(jù)1.插入單個(gè)元組格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

VALUES(<常量1>[,<常量2>]…)；插入一已知元組的全部列的常量

插入一已知元組的部分列的常量表定義時(shí)說明了NOTNULL的屬性列不能取空值如果INTO子句沒有指明任何列名，則新插入的記錄必須在每個(gè)屬性列上均有值。132插入一已知元組的全部列常量

INSERTINTOStudent

VALUES('95005','陳冬','男',18,'CS')；例1：將一個(gè)新生記錄插入學(xué)生表。

INSERTINTOSC(Sno，Cno)

VALUES('95005','1')；插入一已知元組的部分列常量例2：新增一條選課記錄1331342.插入子查詢結(jié)果格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

子查詢；CREATETABLEDeptage(SdeptCHAR(15)AvgageSMALLINT);INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(Sage)

FROMStudentGROUPBYSdept;例3：對每個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。1351363.4.2修改數(shù)據(jù)格式：

UPDATE<表名>

SET<列名>=<表達(dá)式>[,<列名>=<表達(dá)式>

]…[WHERE<條件>]；1371.修改某一個(gè)元組的值UPDATEStudentSETSage=22WHERESno='95001';例4：將學(xué)生95001的年齡改為22。2.修改多個(gè)元組的值UPDATEStudentSETSage=Sage+1；例5：將所有學(xué)生的年齡都增加1。1381393.帶子查詢的修改語句UPDATESCSETGrade=0WHERESnoIN

(SELECTSno

FROMStudent

WHERESdept='CS')；例6：將計(jì)算機(jī)科學(xué)系學(xué)生的成績都置零。1401413.4.3刪除數(shù)據(jù)格式：DELETEFROM<表名>

[WHERE<條件>]；只能對整個(gè)元組操作，不能只刪除某些屬性上的值。只能對一個(gè)關(guān)系起作用，若要從多個(gè)關(guān)系中刪除元組，則必須對每個(gè)關(guān)系分別執(zhí)行刪除命令。

從關(guān)系R中刪除滿足P的元組，只是刪除數(shù)據(jù)，而不是定義。1421.刪除某一個(gè)元組的值DELETE

FROMStudentWHERESno='95005'；例7：刪除學(xué)號(hào)為95005的學(xué)生記錄。2.刪除多個(gè)元組的值DELETE

FROMSC；例8：刪除所有的學(xué)生選課記錄。1433.帶子查詢的刪除語句DELETE

FROMSCWHERESnoIN

(SELECTSno

FROMStudent

WHERESdept='CS')；例9：刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。1444.更新操作與數(shù)據(jù)庫的一致性首先DELETE

FROMSCWHERESno='95005'；其次DELETE

FROMStudentWHERESno='95005'；例10：刪除學(xué)號(hào)為95005的學(xué)生記錄。事務(wù)(Transaction)：保證語句要么都做，要么都不做，以保證關(guān)系的完整性。刪除表中元組的策略：(1)自動(dòng)刪除參照表中相應(yīng)的元組；(2)檢查參照表中是否存在相應(yīng)的元組，如果存在相應(yīng)的元組，如果存在，則操作失敗。1453.5視圖視圖是從一個(gè)或幾個(gè)表(或視圖)導(dǎo)出的表。視圖是一個(gè)虛表數(shù)據(jù)庫中只存放視圖的定義視圖對應(yīng)的數(shù)據(jù)仍存放在原來的表中隨著表中數(shù)據(jù)的變化，視圖的數(shù)據(jù)隨之改變。對視圖的查詢與基本表一樣對視圖的更新將受到一定的限制1463.5視圖

定義視圖

查詢視圖

更新視圖

視圖的作用147視圖概念視圖是一個(gè)虛表數(shù)據(jù)庫中只存放視圖的定義視圖對應(yīng)的數(shù)據(jù)仍存放在原來的表中隨著表中數(shù)據(jù)的變化，視圖的數(shù)據(jù)隨之改變。對視圖的查詢與基本表一樣對視圖的更新將受到一定的限制基本表1基本表2視圖視圖概念示意圖3.5.1定義視圖148建立視圖格式：CREATEVIEW<視圖名>[(<列名>[,<列名>]…)]

AS

子查詢[WITHCHECKOPTION]；下述必須指定全部列名：某個(gè)目標(biāo)列是集函數(shù)或表達(dá)式多表連接時(shí)，目標(biāo)列中出現(xiàn)同名列需在視圖中為某列用新的名字在定義視圖時(shí)要么指定全部視圖列，要么全部省略不寫；如果省略了視圖的屬性列名，則視圖的列名與子查詢列名相同。子查詢中通常不包含ORDERBY和DISTINCT子句。

WITHCHECKOPTION：對視圖進(jìn)行UPDATE、INSERT、DELETE操作時(shí)要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件。149

行列子集視圖：從單個(gè)基本表導(dǎo)出，保留基本表的碼，但去掉其它的某些列和部分行的視圖。

表達(dá)式視圖：帶虛擬列（經(jīng)過各種計(jì)算派生出的數(shù)據(jù)所設(shè)置的派生屬性列）的視圖。

分組視圖：子查詢目標(biāo)表帶有組函數(shù)或子查詢帶有GROUPBY子句的視圖。視圖分類

150例1：建立信息系學(xué)生視圖

CREATEVIEWIS_Student

AS

SELECTSno，Sname，Ssex，Sage FROMStudent WHERESdept='IS'；(行列子集視圖)建立視圖的結(jié)果是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行SELECT語句；只在對視圖查詢時(shí)，才按其定義從基本表中將數(shù)據(jù)查出。151例2：建立計(jì)算機(jī)系學(xué)生視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有計(jì)算機(jī)系的學(xué)生。

CREATEVIEWCS_Student

AS

SELECTSno，Sname，Ssex，Sage FROMStudent WHERESdept='CS'

WITHCHECKOPTION；由于加上了WITHCHECKOPTION子句，以后對該視圖進(jìn)行插入、修改、刪除操作時(shí)，DBMS會(huì)自動(dòng)加上Sdept=‘CS’的條件。152例3：建立信息系選修了1號(hào)課程的學(xué)生的視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)

AS

SELECTStudent.Sno，Sname，Grade FROMStudent,SC WHERESdept='IS'ANDSC.Cno='1'

ANDStudent.Sno=SC.Sno；建立在多個(gè)基本表上153例4：建立信息系選修了1號(hào)課程且成績在90分以上的學(xué)生的視圖。

CREATEVIEWIS_S2

AS

SELECTSno，Sname，Grade FROMIS_S1 WHEREGrade>=90；建立在已定義的視圖上154例5：定義一個(gè)反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno,Sname,Sbirth)

AS

SELECTSno，Sname，2006-Sage FROMStudent；

(表達(dá)式視圖)虛擬列155例6：將學(xué)生的學(xué)號(hào)及他的平均成績定義為一個(gè)視圖。

CREATEVIEWS_G(Sno,Gavg)

AS

SELECTSno，AVG(Grade) FROMSC

GROUPBYSno；

(分組視圖)156例7：將Student表中所有女生記錄定義為一個(gè)視圖。CREATEVIEWF_Student(stdnum,name,sex,age,dept)

AS

SELECT* FROMStudent

WHERESsex='女'；如果修改了基本表Student的結(jié)構(gòu)，則視圖與基本表之間的映象關(guān)系被破壞，視圖就不能正確工作。為避免出現(xiàn)該類問題，最好在修改基本表之后刪除有該基本表導(dǎo)出的視圖，然后重建（同名）視圖。157刪除視圖格式：DROPVIEW<視圖名>；例8：刪除視圖IS_S1。DROPVIEWIS_S2；

（IS_S2由IS_S1導(dǎo)出）

DROPVIEWIS_S1；1583.5.2查詢視圖視圖消解(ViewResolution)在對視圖查詢時(shí)，DBMS將進(jìn)行有效性檢查（表及視圖）。若存在，則從數(shù)據(jù)字典中取出視圖定義，并把定義中的子查詢與用戶查詢結(jié)合起來轉(zhuǎn)換為等價(jià)的對基本表的查詢，然后再執(zhí)行。159例1：在信息系學(xué)生的視圖中查找年齡小于20的學(xué)生。

SELECTSno，Sname FROMIS_Student WHERESage<20；視圖消解

SELECTSno，Sname FROMStudent WHERESage<20ANDSdept='IS'；160例2：查詢信息系選修了1號(hào)課程的學(xué)生。

SELECTSno，Sname FROMIS_Student,SC WHERESC.Sno=IS_Student.Sno

ANDSC.Cno='1'；視圖消解

SELECTStudent.Sno，Sname FROMStudent,SC WHERESC.Cno='1'ANDSdept='IS'

Student.Sno=SC.Sno；161例3：在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號(hào)和平均成績。SELECT*FROMS_GWHEREGavg>=90；(系統(tǒng)轉(zhuǎn)換后)

SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；SELECTSno，AVG(Grade)FROMSCGROUPBYSno；162例3：在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號(hào)和平均成績。SELECT*FROMS_GWHEREGavg>=90；(系統(tǒng)轉(zhuǎn)換后)SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；1633.5.3更新視圖更新視圖即通過視圖插入(INSERT)、刪除(DELETE)和修改(UPDATE)數(shù)據(jù)，實(shí)質(zhì)上轉(zhuǎn)換為對基本表的更新。為了防止用戶對超出視圖范圍的基本表的數(shù)據(jù)進(jìn)行操作，在定義視圖時(shí)，應(yīng)加上WITHCHECKOPTION子句，則在視圖上更新數(shù)據(jù)時(shí)，DBMS將檢查視圖定義中的條件，不滿足將拒絕執(zhí)行。164例1：將信息系學(xué)生視圖IS_Student中學(xué)號(hào)為95002的學(xué)生的姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='95002'；

(系統(tǒng)轉(zhuǎn)換后)UPDATEStudentSETSname='劉辰'WHERESno='95002'ANDSdept='IS'；165例2：向信息系學(xué)生視圖IS_Student中插入一名新生，學(xué)號(hào)為95006，姓名為趙新，年齡為20歲的學(xué)生。INSERTINTOIS_StudentVALUES('95006','趙新',20)；

(系統(tǒng)轉(zhuǎn)換后)INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES('95006','趙新',20,'IS')；166例3：刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為95006的記錄。DELETEFROMIS_StudentWHERESno='95006'；

(系統(tǒng)轉(zhuǎn)換后)DELETEFROMStudentWHERESno='95006'ANDSdept='IS'；167一般情況下，行列子集視圖是可更新的。不可更新的視圖（各系統(tǒng)不太一致）由多個(gè)表導(dǎo)出的視圖，不可更新視圖的列來自表達(dá)式或常數(shù)，不可插入、修改、可刪除視圖列是來自集函數(shù)，不可更新視圖定義中含有GROUPBY子句，不可更新視圖定義中含有DISTINCT短語，不可更新視圖定義中內(nèi)層嵌套的表與導(dǎo)出該視圖表相同，不可更新在不允許更新的視圖上定義的視圖，不可更新168例：將SC中成績在平均成績之上的元組定義成一個(gè)視圖GOOD_SC。

CREATEVIEWGOOD_SCASSELECTSno,Cno,GradeFROMSCWHEREGrade>(SELECTAVG(Grade)FROMSC);1693.5.4視圖的作用視圖能夠簡化用戶的操作

使數(shù)據(jù)庫看起來結(jié)構(gòu)簡單、清晰、可簡化用戶的數(shù)據(jù)查詢操作視圖使用戶能以多種角度看待同一數(shù)據(jù)

使不同的用戶以不同的方式看待同一數(shù)據(jù)視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)

對不同的用戶定義不同的視圖，使機(jī)密數(shù)據(jù)不出現(xiàn)在不應(yīng)看到這些數(shù)據(jù)的用戶的視圖上。1703.6數(shù)據(jù)控制數(shù)據(jù)控制功能數(shù)據(jù)庫恢復(fù)、并發(fā)控制數(shù)據(jù)庫的安全性控制數(shù)據(jù)庫的完整性控制定義碼、取值唯一的列、不為空值的列、外碼及其他一些約束條件。支持事物、提交、回滾等。某用戶對某類數(shù)據(jù)具有何種操作權(quán)力。把