第3章標(biāo)準(zhǔn)sql語句詳解

數(shù)據(jù)庫系統(tǒng)原理AnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQLAnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2

數(shù)據(jù)定義3.3查詢3.4

數(shù)據(jù)更新3.5

視圖AnIntroductiontoDatabaseSystem3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)AnIntroductiontoDatabaseSystem3.4.1插入數(shù)據(jù)兩種插入數(shù)據(jù)方式插入單個元組插入子查詢結(jié)果AnIntroductiontoDatabaseSystem1.插入單個元組語句格式INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]VALUES(<常量1>[，<常量2>]…)功能將新元組插入指定表中。AnIntroductiontoDatabaseSystem插入單個元組（續(xù)）[例1]將一個新學(xué)生記錄（學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

INSERTINTOStudentVALUES('95020'，'陳冬'，'男'，'IS'，18)；AnIntroductiontoDatabaseSystem插入單個元組（續(xù)）[例2]插入一條選課記錄('95020'，'1')。

INSERTINTOSC(Sno，Cno)VALUES('95020'，'1')；

新插入的記錄在Grade列上取空值A(chǔ)nIntroductiontoDatabaseSystem插入單個元組（續(xù)）

INTO子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入的元組在其余屬性列上取空值

VALUES子句

提供的值必須與INTO子句匹配值的個數(shù)值的類型AnIntroductiontoDatabaseSystem2.插入子查詢結(jié)果語句格式

INSERT

INTO

<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢；功能將子查詢結(jié)果插入指定表中AnIntroductiontoDatabaseSystem插入子查詢結(jié)果（續(xù)）[例3]對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。第一步：建表

CREATETABLEDeptage(SdeptCHAR(15)/*系名*/

AvgageINT)；/*學(xué)生平均年齡*/

AnIntroductiontoDatabaseSystem插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù)

INSERTINTODeptage(Sdept，Avgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；AnIntroductiontoDatabaseSystem插入子查詢結(jié)果（續(xù)）INTO子句(與插入單條元組類似)指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組指定部分屬性列：插入的元組在其余屬性列上取空值子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個數(shù)值的類型AnIntroductiontoDatabaseSystem插入子查詢結(jié)果（續(xù)）DBMS在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則實體完整性參照完整性用戶定義的完整性對于有NOTNULL約束的屬性列是否提供了非空值對于有UNIQUE約束的屬性列是否提供了非重復(fù)值對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)AnIntroductiontoDatabaseSystem3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)AnIntroductiontoDatabaseSystem3.4.2修改數(shù)據(jù)語句格式

UPDATE<表名>SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組AnIntroductiontoDatabaseSystem修改數(shù)據(jù)（續(xù)）三種修改方式修改某一個元組的值修改多個元組的值帶子查詢的修改語句AnIntroductiontoDatabaseSystem1.修改某一個元組的值[例4]將學(xué)生95001的年齡改為22歲。UPDATEStudentSETSage=22WHERESno='95001'；AnIntroductiontoDatabaseSystem2.修改多個元組的值[例5]將所有學(xué)生的年齡增加1歲。UPDATEStudentSETSage=Sage+1

AnIntroductiontoDatabaseSystem修改多個元組的值(續(xù))[例6]將信息系所有學(xué)生的年齡增加1歲。

UPDATEStudentSETSage=Sage+1WHERESdept='IS'；AnIntroductiontoDatabaseSystem3.帶子查詢的修改語句[例7]將計算機(jī)科學(xué)系全體學(xué)生的成績置零UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；AnIntroductiontoDatabaseSystem修改數(shù)據(jù)（續(xù)）SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組AnIntroductiontoDatabaseSystem修改數(shù)據(jù)（續(xù)）DBMS在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則實體完整性參照完整性用戶定義的完整性

NOTNULL約束

UNIQUE約束值域約束AnIntroductiontoDatabaseSystem3.4數(shù)據(jù)更新3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)

AnIntroductiontoDatabaseSystem3.4.3刪除數(shù)據(jù)

DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要刪除表中的所有元組AnIntroductiontoDatabaseSystem刪除數(shù)據(jù)（續(xù)）三種刪除方式刪除某一個元組的值刪除多個元組的值帶子查詢的刪除語句AnIntroductiontoDatabaseSystem1.刪除某一個元組的值[例8]刪除學(xué)號為95003的學(xué)生記錄。

DELETEFROMStudentWHERESno='95003'；AnIntroductiontoDatabaseSystem2.刪除多個元組的值[例9]刪除2號課程的所有選課記錄。[例10]刪除所有的學(xué)生選課記錄。

DELETEFROMSC；

WHERECno='2'；DELETEFROMSC；AnIntroductiontoDatabaseSystem3.帶子查詢的刪除語句[例11]刪除信息系所有學(xué)生的選課記錄。DELETEFROMSCWHERE‘IS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；AnIntroductiontoDatabaseSystem刪除數(shù)據(jù)(續(xù))DBMS在執(zhí)行刪除語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則參照完整性不允許刪除級聯(lián)刪除AnIntroductiontoDatabaseSystem更新數(shù)據(jù)與數(shù)據(jù)一致性DBMS在執(zhí)行插入、刪除、更新語句時必須保證數(shù)據(jù)庫一致性必須有事務(wù)的概念和原子性完整性檢查和保證AnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2

數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖AnIntroductiontoDatabaseSystem3.5視圖視圖的特點虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變AnIntroductiontoDatabaseSystem3.5視圖基于視圖的操作

查詢刪除受限更新定義基于該視圖的新視圖AnIntroductiontoDatabaseSystem3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用AnIntroductiontoDatabaseSystem1.建立視圖語句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]

AS<子查詢>[WITHCHECKOPTION]；AnIntroductiontoDatabaseSystem行列子集視圖

[例1]建立信息系學(xué)生的視圖。CREATEVIEWIS_StudentAS

SELECTSno，Sname，SageFROMStudentWHERESdept='IS'；從單個基本表導(dǎo)出只是去掉了基本表的某些行和某些列保留了碼AnIntroductiontoDatabaseSystem建立視圖（續(xù)）DBMS執(zhí)行CREATEVIEW語句時只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。AnIntroductiontoDatabaseSystem插入子查詢結(jié)果（續(xù)）[例]對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。1.創(chuàng)建表，存入數(shù)據(jù)2.通過視圖AnIntroductiontoDatabaseSystem創(chuàng)建基表存數(shù)據(jù)

CREATETABLEDeptage(SdeptCHAR(15)/*系名*/

AvgageINT)；/*學(xué)生平均年齡*/

INSERTINTODeptage(Sdept，vgage)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；AnIntroductiontoDatabaseSystem直接創(chuàng)建視圖

CreateviewVSA(Sdept，vgage)asSELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；AnIntroductiontoDatabaseSystem組成視圖的屬性列名全部省略或全部指定省略:

由子查詢中SELECT目標(biāo)列中的諸字段組成明確指定視圖的所有列名:(1)某個目標(biāo)列是集函數(shù)或列表達(dá)式(2)目標(biāo)列為*(3)多表連接時選出了幾個同名列作為視圖的字段(4)需要在視圖中為某個列啟用新的更合適的名字AnIntroductiontoDatabaseSystem建立視圖（續(xù)）WITHCHECKOPTION透過視圖進(jìn)行增刪改操作時，不得破壞視圖定義中的謂詞條件（即子查詢中的條件表達(dá)式）AnIntroductiontoDatabaseSystem

WITHCHECKOPTION的視圖[例2]建立信息系學(xué)生的視圖，并要求透過該視圖進(jìn)行的更新操作只涉及信息系學(xué)生。CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTIONAnIntroductiontoDatabaseSystem對IS_Student視圖的更新操作修改操作：DBMS自動加上Sdept='IS'的條件插入操作：DBMS自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作刪除操作：DBMS自動加上Sdept='IS'的條件AnIntroductiontoDatabaseSystem基于多個基表的視圖[例4]建立選修了1號課程的學(xué)生視圖(包括學(xué)號、姓名、成績)。CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHEREStudent.Sno=SC.SnoAND

SC.Cno='1'；AnIntroductiontoDatabaseSystem基于視圖的視圖

[例5]建立選修了1號課程且成績在90分以上的學(xué)生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；AnIntroductiontoDatabaseSystem帶表達(dá)式的視圖[例6]定義一個反映學(xué)生出生年份的視圖（包括學(xué)號、姓名、出生年份）。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2005-SageFROMStudent設(shè)置一些派生屬性列,也稱為虛擬列--Sbirth

帶表達(dá)式的視圖必須明確定義組成視圖的各個屬性列名AnIntroductiontoDatabaseSystem建立分組視圖[例7]將學(xué)生的學(xué)號及他的平均成績定義為一個視圖

CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；AnIntroductiontoDatabaseSystem建立視圖（續(xù)）一類不易擴(kuò)充的視圖以SELECT*方式創(chuàng)建的視圖可擴(kuò)充性差，應(yīng)盡可能避免AnIntroductiontoDatabaseSystem建立視圖（續(xù)）[例8]將Student表中所有女生記錄定義為一個視圖

CREATEVIEWF_Student1(stdnum，name，sex，age，dept)ASSELECT*FROMStudentWHERESsex='女'；AnIntroductiontoDatabaseSystem建立視圖（續(xù)）缺點：修改基表Student的結(jié)構(gòu)后，Student表與F_Student1視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。AnIntroductiontoDatabaseSystem建立視圖（續(xù)）

CREATEVIEW

F_Student2(stdnum，name，sex，age，dept)ASSELECTSno，Sname，Ssex，Sage，Sdept

FROMStudentWHERESsex='女'；為基表Student增加屬性列不會破壞Student表與F_Student2視圖的映象關(guān)系。AnIntroductiontoDatabaseSystem常見的視圖形式行列子集視圖WITHCHECKOPTION的視圖基于多個基表的視圖基于視圖的視圖帶表達(dá)式的視圖分組視圖AnIntroductiontoDatabaseSystem2.刪除視圖DROPVIEW<視圖名>；該語句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除AnIntroductiontoDatabaseSystem刪除視圖(續(xù)）

[例9]刪除視圖IS_S1

DROPVIEWIS_S1；

AnIntroductiontoDatabaseSystem3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用AnIntroductiontoDatabaseSystem3.5.2查詢視圖從用戶角度：查詢視圖與查詢基本表相同DBMS實現(xiàn)視圖查詢的方法實體化視圖（ViewMaterialization）有效性檢查：檢查所查詢的視圖是否存在執(zhí)行視圖定義，將視圖臨時實體化，生成臨時表查詢視圖轉(zhuǎn)換為查詢臨時表查詢完畢刪除被實體化的視圖(臨時表)AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）視圖消解法（ViewResolution）進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義把視圖定義中的子查詢與用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢執(zhí)行修正后的查詢AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）[例1]在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student視圖的定義(視圖定義例1)：

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS‘；AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）視圖實體化法視圖消解法轉(zhuǎn)換后的查詢語句為：SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）[例2]查詢信息系選修了1號課程的學(xué)生SELECTSno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoAND

SC.Cno='1'；AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）視圖消解法的局限有些情況下，視圖消解法不能生成正確查詢。采用視圖消解法的DBMS會限制這類查詢。

AnIntroductiontoDatabaseSystem查詢視圖（續(xù)）[例3]在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績SELECT*FROMS_GWHEREGavg>=90；S_G視圖定義：

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；AnIntroductiontoDatabaseSystem查詢轉(zhuǎn)換錯誤：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正確：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；AnIntroductiontoDatabaseSystem3.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用AnIntroductiontoDatabaseSystem3.5.3更新視圖用戶角度：更新視圖與更新基本表相同DBMS實現(xiàn)視圖更新的方法視圖實體化法（ViewMaterialization）視圖消解法（ViewResolution）指定WITHCHECKOPTION子句后

DBMS在更新視圖時會進(jìn)行檢查，防止用戶通過視圖對不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新AnIntroductiontoDatabaseSystem更新視圖（續(xù)）[例1]將信息系學(xué)生視圖IS_Student中學(xué)號95002的學(xué)生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='95002'；轉(zhuǎn)換后的語句：UPDATEStudentSETSname='劉辰'WHERESno='95002'ANDSdept='IS'；AnIntroductiontoDatabaseSystem更新視圖（續(xù)）[例2]向信息系學(xué)生視圖IS_S中插入一個新的學(xué)生記錄：95029，趙新，20歲INSERTINTOIS_StudentVALUES(‘95029’，‘趙新’，20)；轉(zhuǎn)換為對基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES('95029'，'趙新'，20，'IS')；AnIntroductiontoDatabaseSystem更新視圖（續(xù)）[例3]刪除視圖CS_S中學(xué)號為95029的記錄DELETEFROMIS_StudentWHERESno='95029'；轉(zhuǎn)換為對基本表的更新：DELETEFROMStudentWHERESno='95029'ANDSdept='IS'；AnIntroductiontoDatabaseSystem更新視圖的限制一些視圖是不可更新的，因為對這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新(對兩類方法均如此)例：視圖S_G為不可更新視圖。

CREATEVIEWS_G(Sno，Gavg)