SQL概述數(shù)據(jù)定義查詢更新視圖數(shù)據(jù)控制嵌入式SQL

SQL概述數(shù)據(jù)定義查詢更新視圖數(shù)據(jù)控制嵌入式SQL關系數(shù)據(jù)庫標準語言SQL第3章12/31/20221●SQL的開展及現(xiàn)狀1974年，由Boyce和Chamberlin提出。1975－1979，IBM公司SanJoseResearchLab研制了關系數(shù)據(jù)庫管理系統(tǒng)原型SystemR并實現(xiàn)了這種語言。1986年美國國家標準局(ANSI)的數(shù)據(jù)庫委員會X3H2批準SQL作為關系數(shù)據(jù)庫語言的美國標準〔SQL-86〕。ANSI不斷修改和完善SQL標準，公布了SQL-89、SQL-92(SQL2)、SQL-99(SQL3)大局部DBMS產品都支持SQL，成為操作數(shù)據(jù)庫的標準語言12/31/202223.1SQL概述3.1.1SQL的特點SQL是一種介于關系代數(shù)和關系演算之間的結構化查詢語言。綜合統(tǒng)一

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

高度非過程化

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

面向集合的操作方式

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

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

語言簡單，易學易用

核心功能只有9個動詞。12/31/20223數(shù)據(jù)查詢(DataQuery)SQL語言

數(shù)據(jù)操縱(DataManipulation)數(shù)據(jù)定義(DataDefinition)數(shù)據(jù)控制(DataControl)SQL功能數(shù)據(jù)查詢數(shù)據(jù)定義數(shù)據(jù)操縱數(shù)據(jù)控制動詞SELECTCREATE,DROP,ALTERINSERT,UPDATE,DELETEGRANT,REVOKE表3.1SQL語言的動詞12/31/20224SQL用戶根本表1視圖1視圖2根本表2根本表3根本表4存儲文件1存儲文件2外模式模式內模式圖3.1SQL對關系數(shù)據(jù)庫模型的支持3.1.2SQL語言的根本概念12/31/20225根本概念：用戶可以用SQL語言對視圖(View)和根本表(BaseTable)進行查詢等操作，在用戶觀點里，視圖和表一樣，都是關系。視圖是從一個或多個根本表中導出的表，本身不存儲在數(shù)據(jù)庫中，只有其定義，可以將其理解為一個虛表。根本表是本身獨立存在的表，每個根本表對應一個存儲文件，一個表可以帶假設干索引，存儲文件及索引組成了關系數(shù)據(jù)庫的內模式。12/31/202263.2數(shù)據(jù)定義操作對象表視圖索引操作方式創(chuàng)建CREATETABLE刪除DROPTABLE修改ALTERTABLECREATEINDEXDROPINDEXCREATEVIEWDROPVIEW表3.2SQL的數(shù)據(jù)定義語句視圖是基于基本表的虛表，索引是依附于基本表的，因此，SQL通常不提供修改視圖定義和索引定義的操作。（刪除后重建；Oracle允許直接修改視圖定義）12/31/202273.2.1定義、刪除與修改根本表定義根本表格式：CREATETABLE<表名> (<列名><數(shù)據(jù)類型>[列級完整性約束條件] [,<列名><數(shù)據(jù)類型>[列級完整性約束條件]]…… [,<表級完整性約束條件>])；建立一個新表，表中無記錄12/31/20228

CREATETABLE

Student

(

Sno

CHAR(5)NOTNULL

UNIQUE,/*列級完整性約束條件

Sname

CHAR(20)UNIQUE,

Ssex

CHAR(1),

Sage

INT,

Sdept

CHAR(15))；例1：建立學生表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性構成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。12/31/20229

CREATETABLES (S#CHAR(6)NOTNULL,/*學號

SNAMECHAR(8)NOTNULL,/*姓名

SAGESMALLINT,/*年齡

SDCHAR(10),/*系名

SEXCHAR(2)DEFAULT

'男'/*性別

CHECK((SEX='男')OR(SEX='女')),

PRIMARYKEY(S#))；例2：建立學生S、課程C、選課SC三個表S表：12/31/202210

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

CNAMECHAR(30)NOTNULL,/*課程名

TNAMECHAR(8),/*教師姓名

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

PRIMARYKEY(C#))；例2：建立學生S、課程C、選課SC三個表C表：12/31/202211CREATETABLESC(S#CHAR(6)NOTNULL,/*學號

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

GRSMALLINTDEFAULTNULL,/*成績PRIMARYKEY(S#,C#),FOREIGNKEY(S#)REFERENCESS(S#),FOREIGNKEY(C#)REFERENCESC(C#),CHECK(GRISNULL) OR(GRBETWEENOAND100))；例2：建立學生S、課程C、選課SC三個表SC表：12/31/202212例3：設關系S_G(S#,AVG_G),把平均成績大于80的男生的學號及平均成績存入S_G中。CREATETABLES_G(S#CHAR(6)NOTNULL,

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

(SELECTS#FROMSWHERESEX=‘男’)

GROUPBYS#

HAVING

AVG(ALLGRADE)>80)12/31/202213修改根本表改變表名增加列改變列的數(shù)據(jù)類型刪除列的約束刪除列改變列名根本表的修改操作：12/31/202214格式：ALTERTABLE<表名> [ADD

<新列名><數(shù)據(jù)類型>[完整性約束]][DROP<完整性約束名>] [MODIFY<列名><數(shù)據(jù)類型>] [RENAME<舊表名>TO<新表名>];增加新列和新的完整性約束條件刪除指定的完整性約束條件修改列的定義〔列名和數(shù)據(jù)類型〕改變表名12/31/202215SQL沒有提供刪除屬性列的語句，可通過先將要保存的列及其內容復制到一個新表中，然后刪除原表，再將新表重新命名為原表名。12/31/202216例4：在Student表中增加“入學時間〞列，數(shù)據(jù)類型為日期型。 ALETRTABLEStudentADDScomeDATE；例5：把年齡的數(shù)據(jù)類型改為半字長整數(shù).ALETRTABLEStudentMODIFYSageSMALLINT；例6：刪除學生姓名必須取唯一值的約束。

ALETRTABLEStudentDROPUNIQUE(Sname)；12/31/202217刪除根本表格式：DROPTABLE<表名>；刪除一個表，及與該表相關的索引、視圖、碼和外部碼。例7：刪除Student表。

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

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

UNIQUE表示索引值唯一。

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

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

DROPINDEXSNAMEIDX

；12/31/2022233.3查詢根本結構 SelectA1,A2,...,An

FromR1,R2,...,Rm

WherePSelectWhereFrom

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

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

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

SELECT…⑤投影

FROM…①TABLE→內存

WHERE…②選取元組

GROUP…③分組

HAVING…④選擇分組[{UNION|…}⑥查徇結果的集合運算SELECT…]①～⑤

ORDERBY……⑦排序輸出12/31/2022263.3.1單表查詢選擇表中的假設干列選擇表中的假設干元組對查詢結果排序使用集函數(shù)對查詢結果分組12/31/2022271.查詢指定列例1.查詢全體學生的學號及姓名。學生表：Student(Sno,Sname,Ssex,Sage,Sdept)查詢各列的先后順序可以與表中的順序不一致。例2.查詢全體學生的姓名、學號、所在系。SELECTSno,SnameFROMStudent；SELECTSname,Sno,Sdept FROMStudent；12/31/20222812/31/2022292.查詢全部列學生表：Student(Sno,Sname,Ssex,Sage,Sdept)例3.查詢全體學生的全部信息。SELECTSno,Sname,Ssex,Sage,Sdept FROMStudent；

等價于SELECT*FROMStudent;12/31/20223012/31/2022313.查詢經過計算的值學生表：Student(Sno,Sname,Ssex,Sage,Sdept)例4.查詢全體學生的姓名及其出生年份。SELECTSname,2006-Sage FROMStudent；12/31/202232例5.查詢全體學生的姓名、出生年份和所有系，要求用小寫字母表示所有系名。SELECTSname,'YearofBirth:',2006-Sage,LOWER(Sdept)FROMStudent；SELECTSnameNAME,'YearofBirth:'BIRTH,2006-Sage

BIRTHDAY,ISLOWER(Sdept)DEPARTMENT

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

20；SELECTSname,Sage FROMStudentWHERENOTSage>=

20；或12/31/20224012/31/202241例9.查詢考試成績有不及格的學生的學號。SELECTDISTINCTSno FROMSCWHEREGrade<60；12/31/20224212/31/202243例10.查詢年齡在20～23歲〔包括20歲和23歲〕之間的學生的姓名、系別和年齡。(2)確定范圍SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23；SELECTSname,Sdept,SageFROMStudentWHERESage>=20ANDSage<=

23；12/31/20224412/31/202245SELECTSname,Sdept,SageFROMStudentWHERESageNOTBETWEEN20AND23；例11.查詢年齡不在20～23歲之間的學生姓名、系別和年齡。SELECTSname,Sdept,SageFROMStudentWHERESage<20ORSage>

23；12/31/20224612/31/202247例12.查詢信息系(IS)、數(shù)學系(MA)和計算機科學系(CS)學生的姓名和性別。(3)確定集合SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept='IS'ORSdept=

'MA'ORSdept=

'CS'；12/31/20224812/31/202249例13.查詢既不是信息系(IS)、數(shù)學系(MA)也不是計算機科學系(CS)學生的姓名和性別。SELECTSname,SsexFROMStudentWHERESdeptNOTIN('IS','MA','CS')；SELECTSname,SsexFROMStudentWHERESdept!='IS'ANDSdept!=

'MA'

ANDSdept!=

'CS'；12/31/20225012/31/202251(4)字符匹配Where子句——Like格式:[NOT]LIKE'<匹配串>'[ESCAPE'<換碼字符>']%:表示任意長度(長度≥0〕的字符串。_:表示單個的任意字符ESCAPE'\':表示\為換碼字符，匹配串中緊跟在\后面的字符‘%’或‘_’，被定義為普通字符(不作通配符用)12/31/202252例14.查詢學號為95001的學生的詳細情況。如果LIKE后面的匹配串中不含通配符，那么可以用＝運算符取代LIKE謂詞；用!=或<>取代NOTLIKE謂詞。SELECT*FROMStudentWHERESnoLIKE

'95001'；SELECT*FROMStudentWHERESno='95001'；等價于12/31/20225312/31/202254例15.查詢所有姓王的學生的姓名、學號和性別。SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE

'王％'；12/31/20225512/31/202256例16.查詢姓李，且姓名為兩個漢字的學生姓名。一個漢字占兩個字符的位置SELECTSnameFROMStudentWHERESnameLIKE'李__'

；12/31/20225712/31/202258例17.查詢名字中第二個字為“晨〞字的學生姓名和學號。SELECTSname,SnoFROMStudentWHERESnameLIKE

'__晨%'

；12/31/20225912/31/202260例18.查詢所有不姓劉的學生姓名。SELECTSnameFROMStudentWHERESnameNOTLIKE

'劉%'

；12/31/20226112/31/202262例19.查詢DB_Design課程的課程號和學分。SELECTCno,CcreditFROMCourseWHERECnameLIKE'DB\_Design'ESCAPE

'\'

；12/31/202263例20.查詢以“DB_〞開頭，且倒數(shù)第三個字符為i的課程的詳細情況。SELECT*FROMCourseWHERECnameLIKE

'DB\_%i__'ESCAPE'\'；12/31/202264(5)涉及空值的查詢例21.某些學生選修課后沒有參加考試，所以有選課錄，但沒有成績。查詢缺少成績的學生的學號和相應課程號。SELECTSno,CnoFROMSCWHEREGradeISNULL；12/31/20226512/31/202266例22.查詢所有有成績的學生學號和課程號。SELECTSno,CnoFROMSCWHEREGradeISNOTNULL；12/31/20226712/31/202268(6)多重條件查詢例23.查詢計算機系年齡在20歲以下的學生姓名。SELECTSnameFROMStudentWHERESdept='CS'ANDSage<20；12/31/20226912/31/202270例24.查詢信息系(IS)、數(shù)學系(MA)和計算機科學系(CS)的男學生的姓名和年齡。邏輯運算符AND的優(yōu)先級比OR高，可通過括號改變優(yōu)先級。SELECTSname,SageFROMStudentWHERE(Sdept='IS'ORSdept='MA'OR

Sdept='CS')ANDSsex='男'；12/31/20227112/31/2022723.對查詢結果的排序例25.查詢選修了3號課程的學生的學號及其成績，查詢結果按分數(shù)的降序排列。SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC；12/31/20227312/31/202274例26.查詢全體學生情況，查詢結果按所在系的系號升序排列，同在一個系的按年齡降序排列。SELECT*FROMStudent

ORDERBYSdeptASC,SageDESC；SELECT*FROMStudent

ORDERBYSdept,SageDESC；12/31/20227512/31/2022764.使用集函數(shù)主要有：COUNT([DISTINCT|ALL]*)統(tǒng)計元組個數(shù)COUNT([DISTINCT|ALL]<列名>)統(tǒng)計一列中值的個數(shù)SUM([DISTINCT|ALL]<列名>)計算一列值的總和〔此列必須是數(shù)值型〕AVG([DISTINCT|ALL]<列名>)計算一列值的平均值〔此列必須是數(shù)值型〕MAX([DISTINCT|ALL]<列名>)求一列值中的最大值MIN([DISTINCT|ALL]<列名>)求一列值中的最小值12/31/202277例27.查詢學生總人數(shù)。SELECTCOUNT

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

(DISTINCTSno)FROMSC;12/31/202278例29.計算2號課程的學生平均成績。SELECTAVG(Grade)FROMSCWHERECno='2';例30.查詢選修2號課程的學生最高成績。SELECTMAX(Grade)FROMSCWHERECno='2';12/31/20227912/31/2022805.對查詢結果分組例31.求各個課程號及相應的選課人數(shù)。SELECTCno,COUNT(Sno)FROMSCGROUPBYCno;12/31/202281例32.查詢選修了3門以上課程的學生學號。SELECTSnoFROMSCGROUPBYSnoHAVING

COUNT(*)>=3;HAVING用于分組后按一定條件對這些分組進行篩選。WHERE子句作用于根本表或視圖，從中選擇滿足條件的元組HAVING短句作用于組，從中選擇滿足條件的組12/31/20228212/31/2022833.3.2連接查詢

等值與非等值連接查詢

自身連接

外連接

復合條件連接12/31/2022841.等值與非等值連接查詢連接條件一： [<表名1>.]<列名1><比較運算符>[<表名2>.]<列名2>連接條件二： [<表名1>.]<列名1>BETWEEN[<表名2>.]<列名2>AND[<表名2>.]<列名3>比較運算符主要有：＝、>、<、>=、<=、!=。連接查詢中用來連接兩個表的條件稱為連接條件或連接謂詞。連接謂詞中的列名稱為連接字段，其各字段應是可比的。12/31/202285例33.查詢每個學生及其選修課程的情況。

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

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

SC.Sno;12/31/20228612/31/2022872.自身連接例34.查詢每門課的間接先修課。一個表與其自身進行連接SELECTFIRST.Cno,SECOND.CpnoFROMCourseFIRST,CourseSECONDWHEREFIRST.Cpno=

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

ANDSC.Grade>=90;12/31/202293例37.查詢每個學生的學號、姓名、選修課程名及成績。多表連接SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;12/31/20229412/31/2022953.3.3嵌套查詢在SQL語言中，一個SELECT-FROM-WHERE語句稱為一個查詢塊。一個查詢塊嵌套在另一個查詢塊的WHERE子句或HAVING短語的條件中的查詢稱為嵌套查詢。例如：SELECTSnameFROMStudentWHERESnoINSELECTSnoFROMSCWHERECno='2';外層查詢內層查詢不能使用ORDERBY子句12/31/202296帶有IN謂詞的子查詢帶有比較運算符的子查詢帶有ANY或ALL謂詞的子查詢帶有EXISTS謂詞的子查詢3.3.3嵌套查詢12/31/202297例38.查詢與“劉晨〞在同一個系學習的學生。 SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='劉晨');(1)首先在Student關系中找出劉晨所在的系，結果為'IS'。(2)其次在Student關系中找出'IS'系的學生1.帶有IN謂詞的子查詢或SELECTS1.Sno,S1.Sname,S1.SdeptFROMStudentS1,StudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='劉晨'；12/31/20229812/31/202299例39.查詢選修了課程名為“信息系統(tǒng)〞的學生學號和姓名。 SELECTSno,Sname FROMStudentWHERESnoIN(SELECTSnoFROMSCWHERECnoIN(SELECTCnoFROMCourseWHERECname='信息系統(tǒng)'));(1)首先在Course關系中找出“信息系統(tǒng)的課程號〞，結果為3。(2)其次在SC關系中找出選修3號課程的學生(3)最后在Student關系中取出學號和姓名?；騍ELECTSno,SnameFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.CnoANDCourse.Cname='信息系統(tǒng)'

；12/31/202210012/31/20221012.帶有比較運算符的子查詢例40.查詢與“劉晨〞在同一個系學習的學生。內存查詢返回的是單值時，可以用比較運算符；子查詢要跟在比較符之后。SELECTSno,Sname,SdeptFROMStudentWHERESdept＝(SELECTSdeptFROMStudentWHERESname='劉晨');12/31/202210212/31/20221033.帶有ANY或ALL謂詞的子查詢子查詢返回單值時可以用比較運算符，而使用ANY或ALL謂詞時必須同時使用比較運算符。其語義為：>(>=、=)ANY大于〔大于等于、等于〕子查詢結果中的某個值>(>=、=)ALL大于〔大于等于、等于〕子查詢結果中的所有值<(<=)ANY小于〔小于等于〕子查詢結果中的某個值<(<=)ALL小于〔小于等于〕子查詢結果中的所有值!=(或<>)ANY不等于子查詢結果中的某個值!=(或<>)ALL不等于子查詢結果中的任何一個值12/31/2022104例41.查詢其他系中比信息系某一學生年齡小的學生姓名和年齡。 SELECTSname,Sage FROMStudentWHERESage<ANY(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或SELECTSname,Sage FROMStudentWHERESage<(SELECTMAX(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';12/31/202210512/31/2022106例42.查詢其他系中比信息系所有學生年齡小的學生姓名和年齡。 SELECTSname,Sage FROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='IS')ANDSdept<>'IS';或SELECTSname,SageFROMStudentWHERESage<(SELECTMIN(Sage)FROMStudentWHERESdept='IS')ANDSdept<>'IS';12/31/2022107＝<>或!=<<=>>=ANYIN__<MAX<=MAX>MIN>=MINALL__NOTIN<MIX<=MIN>MAX>MAX表3-4ANY,ALL謂詞與集函數(shù)及IN謂詞的等價轉換關系集函數(shù)實現(xiàn)子查詢比直接用ANY或ALL查詢效率更高。12/31/20221084.帶有EXISTS謂詞的子查詢不相關子查詢：子查詢的查詢條件不依賴于父查詢的子查詢。相關子查詢(CorrelatedSubquery)：子查詢的查詢條件依賴于外層父查詢的某個屬性值的子查詢。帶EXISTS的子查詢就是相關子查詢EXISTS表示存在量詞帶有EXISTS的子查詢不返回任何記錄的數(shù)據(jù)，只返回邏輯值

'True'或'False'12/31/2022109例43-1.查詢所有選修了1號課程的學生姓名。SELECTSnameFROMStudentWHERESnoIN(SELECTSNOFROMSCWHERESC.Cno='1');不相關子查詢12/31/2022110例43-2.查詢所有選修了1號課程的學生姓名。 SELECTSname FROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');執(zhí)行過程：先在外層查詢中取Student表的第一個元組，用該元組的相關的屬性值〔在內層WHERE子句中給定的〕處理內層查詢，假設外層的WHERE子句返回'TRUE'值，那么此元組送入結果的表中。然后再取下一個元組；重復上述過程直到外層表的元組全部遍歷一次為止。相關子查詢12/31/202211112/31/2022112說明：不關心子查詢的具體內容，因此用SELECT*Exists+子查詢用來判斷該子查詢是否返回元組當子查詢的結果集非空時，Exists為'True'當子查詢的結果集為空時，Exists為'False'NOTEXISTS：假設子查詢結果為空，返回'TRUE'值，否那么返回'FALSE'12/31/2022113例44.查詢沒有選修1號課程的學生姓名。 SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1');或SELECTSnameFROMStudentWHERESnoNOTIN(SELECTSNOFROMSCWHERESC.Cno='1');不相關子查詢相關子查詢12/31/202211412/31/2022115例45.查詢與劉晨在同一個系學習的學生。 SELECTSno,Sname,Sdept FROMStudentS1WHEREEXISTS(SELECT*FROMStudentS2WHERES1.Sdept=S2.SdeptANDS2.Sname='劉晨');或SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='劉晨');12/31/202211612/31/2022117例46.查詢選修了全部課程的學生姓名。 SELECTSname FROMStudentWHERENOTEXISTS(SELECT*FROMCourseWHERENOTEXISTS

(SELECT*FROMSCWHERESno=Student.SnoANDCno=Course.Cno));這門課他沒選這樣的課是不存在的12/31/202211812/31/2022119例47.查詢至少選修了學生95002選修的全部課程的學生號。SELECTDISTINCTSnoFROMSCSCXWHERENOTEXISTS

(SELECT*FROMSCSCY WHERESCY.Sno='95002' ANDNOTEXISTS

(SELECT* FROMSCSCZ WHERESCZ.Sno=SCX.Sno ANDSCZ.Cno=SCY.Cno))； 12/31/20221203.3.4集合查詢例48.查詢計算機系的學生或者年齡不大于19歲的學生。SELECT*FROMStudentWHERESdept='CS'UNIONSELECT*FROMStudentWHERESage<=1912/31/202212112/31/2022122例49.查詢選修了課程1或選修課程2的學生。SELECTSnoFROMSCWHERECno='1'UNIONSELECTSnoFROMSCWHERECno='2';12/31/202212312/31/2022124例50.查詢計算機系的學生與年齡不大于19歲的學生的交集。SELECT*FROMStudentWHERESdept='CS'ANDSage<=19;例51.查詢選修了課程1的學生集合與選修課程2的學生集合的交集。SELECTSnoFROMSCWHERECno='1'ANDSnoIN(SELECTSnoFROMSCWHERECno='2');12/31/2022125例52.查詢計算機系的學生與年齡不大于19歲的學生的差集。SELECT*FROMStudentWHERESdept='CS'ANDSage>19;例53.查詢選修課程1但沒有選修課程2的學生。SELECTSnoFROMSCWHERECno='1'ANDSnoNOTIN(SELECTSnoFROMSCWHERECno='2');12/31/20221263.3.5SELECT語句的一般格式SELECT[ALL|DISTINCT]<目標列表達式>[別名][,<目標列表達式>[別名]]……FROM<表名或視圖名>[別名][,<表名或視圖名>[別名]]……[WHERE<條件表達式>][GROUPBY<列名1>[HAVING<條件表達式>]][ORDERBY<列名2>[ASC|DESC]];12/31/20221273.4數(shù)據(jù)更新

插入操作INSERT

修改操作UPDATE

刪除操作DELETE12/31/20221283.4.1插入數(shù)據(jù)1.插入單個元組格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

VALUES(<常量1>[,<常量2>]…)；插入一元組的全部列的常量插入一元組的局部列的常量表定義時說明了NOTNULL的屬性列不能取空值如果INTO子句沒有指明任何列名，那么新插入的記錄必須在每個屬性列上均有值。12/31/2022129插入一元組的全部列常量

INSERTINTOStudent

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

INSERTINTOSC(Sno，Cno)VALUES('95005','1')；插入一元組的局部列常量例2：新增一條選課記錄12/31/202213012/31/20221312.插入子查詢結果格式：INSERTINTO<表名>[(<列名1>[,<列名2>]…)]

子查詢；CREATETABLEDeptage(SdeptCHAR(15)AvgageSMALLINT);INSERTINTODeptage(Sdept,Avgage)SELECTSdept,AVG(Sage)FROMStudentGROUPBYSdept;例3：對每個系，求學生的平均年齡，并把結果存入數(shù)據(jù)庫。12/31/202213212/31/20221333.4.2修改數(shù)據(jù)格式：

UPDATE<表名>

SET<列名>=<表達式>[,<列名>=<表達式>

]…[WHERE<條件>]；12/31/20221341.修改某一個元組的值UPDATEStudentSETSage=22WHERESno='95001';例4：將學生95001的年齡改為22。2.修改多個元組的值UPDATEStudentSETSage=Sage+1；例5：將所有學生的年齡都增加1。12/31/202213512/31/20221363.帶子查詢的修改語句UPDATESCSETGrade=0WHERESnoIN(SELECTSnoFROMStudentWHERESdept='CS')；例6：將計算機科學系學生的成績都置零。12/31/202213712/31/20221383.4.3刪除數(shù)據(jù)格式：DELETEFROM<表名> [WHERE<條件>]；只能對整個元組操作，不能只刪除某些屬性上的值。只能對一個關系起作用，假設要從多個關系中刪除元組，那么必須對每個關系分別執(zhí)行刪除命令。從關系R中刪除滿足P的元組，只是刪除數(shù)據(jù)，而不是定義。12/31/20221391.刪除某一個元組的值DELETE

FROMStudentWHERESno='95005'；例7：刪除學號為95005的學生記錄。2.刪除多個元組的值DELETE

FROMSC；例8：刪除所有的學生選課記錄。12/31/20221403.帶子查詢的刪除語句DELETE

FROMSCWHERESnoIN(SELECTSnoFROMStudentWHERESdept='CS')；例9：刪除計算機科學系所有學生的選課記錄。12/31/20221414.更新操作與數(shù)據(jù)庫的一致性首先DELETE

FROMSCWHERESno='95005'；其次DELETE

FROMStudentWHERESno='95005'；例10：刪除學號為95005的學生記錄。事務(Transaction)：保證語句要么都做，要么都不做，以保證關系的完整性。刪除表中元組的策略：(1)自動刪除參照表中相應的元組；(2)檢查參照表中是否存在相應的元組，如果存在相應的元組，如果存在，那么操作失敗。12/31/20221423.5視圖視圖是從一個或幾個表(或視圖)導出的表。視圖是一個虛表數(shù)據(jù)庫中只存放視圖的定義視圖對應的數(shù)據(jù)仍存放在原來的表中隨著表中數(shù)據(jù)的變化，視圖的數(shù)據(jù)隨之改變。對視圖的查詢與根本表一樣對視圖的更新將受到一定的限制12/31/20221433.5視圖

定義視圖

查詢視圖

更新視圖

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

AS子查詢[WITHCHECKOPTION]；下述必須指定全部列名：某個目標列是集函數(shù)或表達式多表連接時，目標列中出現(xiàn)同名列需在視圖中為某列用新的名字在定義視圖時要么指定全部視圖列，要么全部省略不寫；如果省略了視圖的屬性列名，那么視圖的列名與子查詢列名相同。子查詢中通常不包含ORDERBY和DISTINCT子句。WITHCHECKOPTION：對視圖進行UPDATE、INSERT、DELETE操作時要保證更新、插入或刪除的行滿足視圖定義中的謂詞條件。12/31/2022146行列子集視圖：從單個根本表導出，保存根本表的碼，但去掉其它的某些列和局部行的視圖。表達式視圖：帶虛擬列〔經過各種計算派生出的數(shù)據(jù)所設置的派生屬性列〕的視圖。分組視圖：子查詢目標表帶有組函數(shù)或子查詢帶有GROUPBY子句的視圖。視圖分類12/31/2022147例1：建立信息系學生視圖

CREATEVIEWIS_Student

AS

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

CREATEVIEWCS_Student

AS

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

WITHCHECKOPTION；由于加上了WITHCHECKOPTION子句，以后對該視圖進行插入、修改、刪除操作時，DBMS會自動加上Sdept=‘CS’的條件。12/31/2022149例3：建立信息系選修了1號課程的學生的視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)

AS

SELECTStudent.Sno，Sname，Grade FROMStudent,SC WHERESdept='IS'ANDSC.Cno='1'ANDStudent.Sno=SC.Sno；建立在多個根本表上12/31/2022150例4：建立信息系選修了1號課程且成績在90分以上的學生的視圖。

CREATEVIEWIS_S2

AS

SELECTSno，Sname，Grade FROMIS_S1 WHEREGrade>=90；建立在已定義的視圖上12/31/2022151例5：定義一個反映學生出生年份的視圖。

CREATEVIEWBT_S(Sno,Sname,Sbirth)

AS

SELECTSno，Sname，2006-Sage FROMStudent；

(表達式視圖)虛擬列12/31/2022152例6：將學生的學號及他的平均成績定義為一個視圖。

CREATEVIEWS_G(Sno,Gavg)

AS

SELECTSno，AVG(Grade) FROMSC

GROUPBYSno；

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

AS

SELECT* FROMStudentWHERESsex='女'；如果修改了基本表Student的結構，則視圖與基本表之間的映象關系被破壞，視圖就不能正確工作。為避免出現(xiàn)該類問題，最好在修改基本表之后刪除有該基本表導出的視圖，然后重建（同名）視圖。12/31/2022154刪除視圖格式：DROPVIEW<視圖名>；例8：刪除視圖IS_S1。DROPVIEWIS_S2；〔IS_S2由IS_S1導出〕DROPVIEWIS_S1；12/31/20221553.5.2查詢視圖視圖消解(ViewResolution)在對視圖查詢時，DBMS將進行有效性檢查〔表及視圖〕。假設存在，那么從數(shù)據(jù)字典中取出視圖定義，并把定義中的子查詢與用戶查詢結合起來轉換為等價的對根本表的查詢，然后再執(zhí)行。12/31/2022156例1：在信息系學生的視圖中查找年齡小于20的學生。 SELECTSno，Sname FROMIS_Student WHERESage<20；視圖消解

SELECTSno，Sname FROMStudent WHERESage<20ANDSdept='IS'；12/31/2022157例2：查詢信息系選修了1號課程的學生。 SELECTSno，Sname FROMIS_Student,SC WHERESC.Sno=IS_Student.SnoANDSC.Cno='1'；視圖消解

SELECTSno，Sname FROMStudent,SC WHERESC.Cno='1'ANDSdept='IS'Student.Sno=SC.Sno；12/31/2022158例3：在S_G視圖中查詢平均成績在90分以上的學生學號和平均成績。SELECT*FROMS_GWHEREGavg>=90；(系統(tǒng)轉換后)

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

(系統(tǒng)轉換后)UPDATEStudentSETSname='劉辰'WHERESno='95002'ANDSdept='IS'；12/31/2022162例2：向信息系學生視圖IS_Student中插入一名新生，學號為95006，姓名為趙新，年齡為20歲的學生。INSERTINTOIS_StudentVALUES('95006','趙新',20)；

(系統(tǒng)轉換后)INSERTINTOStudent(Sno,Sname,Sage,Sdept)VALUES('95006','趙新',20,'IS')；12/31/2022163例3：刪除信息系學生視圖IS_Student中學號為95006的記錄。DELETEFROMIS_StudentWHERESno='95006'；

(系統(tǒng)轉換后)DELETEFROMStudentWHERESno='95006'ANDSdept='IS'；12/31/2022164一般情況下，行列子集視圖是可更新的。不可更新的視圖〔各系統(tǒng)不太一致〕由多個表導出的視圖，不可更新視圖的列來自表達式或常數(shù)，不可插入、修改、可刪除視圖列是來自集函數(shù)，不可更新視圖定義中含有GROUPBY子句，不可更新視圖定義中含有DISTINCT短語，不可更新視圖定義中內層嵌套的表與導出該視圖表相同，不可更新在不允許更新的視圖上定義的視圖，不可更新12/31/2022165例：將SC中成績在平均成績之上的元組定義成一個視圖GOOD_SC。CREATEVIEWGOOD_SCASSELECTSno,Cno,GradeFROMSCWHEREGrade>(SELECTAVG(Grade)FROMSC);12/31/20221663.5.4視圖的作用視圖能夠簡化用戶的操作使數(shù)據(jù)庫看起來結構簡單、清晰、可簡化用戶的數(shù)據(jù)查詢操作視圖使用戶能以多種角度看待同一數(shù)據(jù)使不同的用戶以不同的方式看待同一數(shù)據(jù)視圖對重構數(shù)據(jù)庫提供了一定程度的邏輯獨立性視圖能夠對機密數(shù)據(jù)提供平安保護對不同的用戶定義不同的視圖，使機密數(shù)據(jù)不出現(xiàn)在不應看到這些數(shù)據(jù)的用戶的視圖上。12/31/20221673.6數(shù)據(jù)控制數(shù)據(jù)控制功能數(shù)據(jù)庫恢復、并發(fā)控制數(shù)據(jù)庫的平安性控制數(shù)據(jù)庫的完整性控制定義碼、取值唯一的列、不為空值的列、外碼及其他一些約束條件。支持事物、提交、回滾等。某用戶對某類數(shù)據(jù)具有何種操作權力。把授權決定告知系統(tǒng)把授權結果存入數(shù)據(jù)字典用戶提出操作請求，根據(jù)授權情況決定是否執(zhí)行請求12/31/20221683.6.1授權對象對象類型操作權限屬性列TABLESELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGES視圖TABLESELECT,INSERT,UPDATE,DELETE,ALLPRIVILEGES基本表TABLESELECT,INSERT