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

數(shù)據(jù)庫系統(tǒng)概論AnIntroductiontoDatabaseSystem第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL（3）第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結(jié)3.5數(shù)據(jù)更新3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)3.5.1插入數(shù)據(jù)SQL中使用INSERT語句向表中插入新元組：提供兩種插入數(shù)據(jù)方式插入元組一次只插入一個(gè)元組插入子查詢結(jié)果可以一次插入多個(gè)元組一、插入元組插入元組的語句

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

VALUES(<常量1>[，<常量2>]…)將一個(gè)新元組插入指定表中。INTO子句用于指定表名稱及屬性列<表名>：指定要插入的表<屬性列1>、<屬性列2>、…：指定接受數(shù)據(jù)的屬性列VALUES子句用于提供數(shù)據(jù)<常量1>、<常量2>、…：為屬性列提供的值插入元組（續(xù)）若INTO子句中有屬性列表屬性列的順序可與表定義中的順序不一致VALUES子句提供的值應(yīng)與INTO子句中的屬性列相匹配值的個(gè)數(shù)與屬性的個(gè)數(shù)相同順序與類型一致若INTO子句中無屬性列表VALUES子句提供的值應(yīng)與表定義中定義的屬性列相匹配插入元組（續(xù)）[例3.69]將一個(gè)新學(xué)生元組（201215128，陳冬，男，IS，18歲）插入到Student表中。

INSERTINTOStudent(Sno，Sname，Ssex，Sdept，Sage)VALUES('201215128'，'陳冬'，'男'，'IS'，18)；INTO子句中屬性列個(gè)數(shù)與順序可任意VALUES字句應(yīng)與之對(duì)應(yīng)字符數(shù)據(jù)要用單引號(hào)括起來插入元組（續(xù)）[例3.70]將學(xué)生張成民的信息插入到Student表中。

INSERTINTOStudent VALUES(‘201215126’，‘張成民’，‘男’，18，'CS');INTO子句中沒有指出列名稱一般應(yīng)為表中全部列提供值順序、個(gè)數(shù)、類型與CREATETABLEtudent中的順序一致插入元組（續(xù)）對(duì)于未提供值的列，RDBMS將填入默認(rèn)值或設(shè)為空值［例3.71］插入一條選課記錄('201215128'，'1')。

INSERTINTOSC(Sno，Cno)VALUES(‘201215128’，‘1’)；RDBMS將在新插入記錄的Grade列上自動(dòng)地賦空值。如果定義了默認(rèn)值，則設(shè)置為默認(rèn)值或者：

INSERTINTOSCVALUES(‘201215128’，‘1’，NULL)；VALUES子句中用NULL代表空值如果列定義中有約束NOTNULL，必須為其提供非空值二、插入子查詢結(jié)果INSERT語句中可以使用子查詢，將子查詢結(jié)果插入指定表中：格式：INSERTINTO<表名>[(<屬性列1>[，<屬性列2>…)]

子查詢；子查詢用于生成要插入的批量數(shù)據(jù)。SELECT子句目標(biāo)列必須與INTO子句匹配插入子查詢結(jié)果（續(xù)）[例3.72]對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。建立保存結(jié)果的表：

CREATETABLEDept_age(SdeptCHAR(15) /*系名*/Avg_ageSMALLINT)； /*學(xué)生平均年齡*/

執(zhí)行插入：

INSERTINTODept_age(Sdept，Avg_age)

SELECT

Sdept，AVG(Sage)

FROMStudentGROUPBYSdept；

插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入、修改或刪除元組時(shí)會(huì)檢查元組是否破壞表上已定義的完整性規(guī)則實(shí)體完整性參照完整性用戶定義的完整性NOTNULL約束UNIQUE約束值域約束數(shù)據(jù)發(fā)生變化后，同時(shí)需要維護(hù)索引3.5數(shù)據(jù)更新3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)3.4.2修改數(shù)據(jù)SQL中使用UPDATE語句修改表中的元組

UPDATE<表名>SET

<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；<表名>指定要修改的元組所在的表<列名>指定要修改的列<表達(dá)式>提供給列的新值WHERE子句指定要修改的元組，省略時(shí)為全部元組修改數(shù)據(jù)（續(xù)）修改某一個(gè)（些）元組的值：[例3.73]將學(xué)生201215121的年齡改為22歲

UPDATEStudentSETSage=22

WHERESno=‘201215121’；

根據(jù)表中的數(shù)據(jù)，可能修改0、1或多個(gè)元組中的數(shù)據(jù)修改數(shù)據(jù)（續(xù)）修改全部元組的值

[例3.74]將所有學(xué)生的年齡增加1歲

UPDATEStudentSETSage=Sage+1；使用過程中一定要注意WHERE子句的條件省略：將修改全部元組若不恰當(dāng)不能正確的定位元組修改數(shù)據(jù)（續(xù)）帶子查詢的修改語句[例3.75]將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。

UPDATESCSETGrade=0WHERESnoin(SELETESnoFROMStudentWHERESdept=‘SC’)；或：UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；此子查詢?yōu)橄嚓P(guān)子查詢UPDATE中的子查詢用于構(gòu)造修改的條件表中的每一行將依據(jù)子查詢確定是否修改3.5數(shù)據(jù)更新3.5.1插入數(shù)據(jù)3.5.2修改數(shù)據(jù)3.5.3刪除數(shù)據(jù)3.5.3刪除數(shù)據(jù)SQL中使用DELETE語句從表中刪除元組

DELETEFROM<表名>[WHERE<條件>]；<表名>指定要操作的表WHERE子句指定要?jiǎng)h除的元組，省略將刪除表中的全部元組刪除數(shù)據(jù)（續(xù)）刪除某一個(gè)（些）元組

[例3.76]刪除學(xué)號(hào)為201215128的學(xué)生記錄。

DELETEFROMStudentWHERESno=‘201215128‘；根據(jù)表中的數(shù)據(jù)，可能刪除0、1或多個(gè)元組刪除數(shù)據(jù)（續(xù)）刪除所有的元組

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

DELETEFROMSC；使用過程中一定要注意WHERE子句的條件省略：將刪除全部元組若不恰當(dāng)將刪除有用的元組刪除任何數(shù)據(jù)前應(yīng)首先確認(rèn)！3.帶子查詢的刪除語句帶子查詢的刪除語句

[例3.78]刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。DELETEFROMSCWHERESnoin(SELETESnoFROMStudentWHERESdept=‘SC’)；或：DELETEFROMSCWHERE'CS‘=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；DELETE中的子查詢用于構(gòu)造刪除的條件表中的每一行將依據(jù)子查詢確定是否刪除第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結(jié)3.6空值空值是指“不知道”、“不存在”或“無意義”的值，一般有以下幾種情況：該屬性應(yīng)該有一個(gè)值，但目前不知道該屬性不應(yīng)該有值由于某種原因不便于填寫，或不關(guān)心該屬性值?？罩稻哂胁淮_定性，因此，運(yùn)算時(shí)需要注意第三章

關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2學(xué)生-課程數(shù)據(jù)庫3.3數(shù)據(jù)定義3.4數(shù)據(jù)查詢3.5數(shù)據(jù)更新3.6空值的處理3.7視圖3.8小結(jié)3.7視圖視圖是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表它是一個(gè)虛表數(shù)據(jù)庫中只存放視圖的定義視圖對(duì)應(yīng)的數(shù)據(jù)存放在它的基表中視圖只是基表的一個(gè)透視窗口基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變3.7視圖視圖是用戶查看數(shù)據(jù)庫中數(shù)據(jù)的一種方式，視圖以另一種方式表示一個(gè)或多個(gè)表中的數(shù)據(jù)。605040302010603010基表視圖3.7視圖3.7.1定義視圖3.7.2查詢視圖3.7.3更新視圖3.7.4視圖的作用一、建立視圖建立視圖：

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

AS

<子查詢>[WITHCHECKOPTION]；<視圖名>為新建視圖的名稱<列名>指定視圖的列名稱，可省略子查詢可以是任意復(fù)雜的SELECT語句，但不允許含有ORDERBY子句和DISTINCT短語建立視圖（續(xù)）[例3.84]建立信息系學(xué)生的視圖。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept=‘IS’；查詢語句定義了視圖視圖的結(jié)構(gòu)由Sno，Sname，Sage視圖的數(shù)據(jù)為Student中Sdept=‘IS’的元組沒有指定視圖的列名時(shí)，視圖的結(jié)構(gòu)與查詢語句的結(jié)果集的結(jié)構(gòu)相同建立視圖（續(xù)）有時(shí)需要指定視圖的列名：SELECT中的目標(biāo)列不是屬性名，而是表達(dá)式時(shí)多表連接的結(jié)果集中有幾個(gè)同名列時(shí)需要使用一個(gè)更合適的名稱實(shí)現(xiàn)模式與子模式之間的映射例：CREATEVIEWIS_Student(學(xué)號(hào)，姓名，年齡)ASSELECTSno，Sname，SageFROMStudentWHERESdept=‘IS’；注意視圖的列數(shù)應(yīng)與查詢結(jié)果集的結(jié)構(gòu)一致建立視圖（續(xù)）WITHCHECKOPTION規(guī)定對(duì)視圖所執(zhí)行的所有數(shù)據(jù)修改操作必須遵守視圖定義中SELECT語句所設(shè)置的條件。

[例3.85]建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系的學(xué)生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudent

WHERESdept='IS'

WITHCHECKOPTION；建立視圖（續(xù)）CREATEVIEW語句用于定義視圖該語句執(zhí)行時(shí)首先檢查語法是否正確引用的對(duì)象是否存在然后把視圖的定義存入數(shù)據(jù)字典并不執(zhí)行其中的SELECT語句只有對(duì)視圖操作時(shí)，才按視圖的定義對(duì)表中數(shù)據(jù)進(jìn)行操作建立視圖（續(xù)）視圖建立在單個(gè)基表上，且保留主碼，稱為行列子集視圖視圖可建立在多個(gè)基表上

[例3.86]建立信息系選修了1號(hào)課程的學(xué)生視圖。

CREATEVIEWIS_S1(學(xué)號(hào)，姓名，成績(jī))ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'ANDStudent.Sno=SC.SnoANDSC.Cno='1'；建立視圖（續(xù)）視圖可以建立在視圖上[例3.87]建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；建立視圖（續(xù)）帶表達(dá)式的視圖（也稱帶虛擬列的視圖）[例3.88]定義一個(gè)反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2014-SageFROMStudent；查詢中使用了表達(dá)式，須指定列名稱建立視圖（續(xù)）分組視圖：視圖由帶有聚集函數(shù)和GROUPBY子句的查詢來定義

[例3.89]將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSC

GROUPBYSno；

建立視圖（續(xù)）[例3.90]將Student表中所有女生記錄定義為一個(gè)視圖

CREATEVIEWF_Student(F_Sno，name，sex，age，dept)ASSELECT*

FROMStudentWHERESsex=‘女’；查詢語句中沒有指定屬性列名稱缺點(diǎn)：修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。二、刪除視圖語句的格式：

DROPVIEW<視圖名>[CASCADE]；該語句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROPVIEW語句刪除

刪除視圖(續(xù)）[例3.91]刪除視圖BT_S：

DROPVIEWBT_S；將從數(shù)據(jù)庫中將視圖的定義去除刪除視圖IS_S1：

DROPVIEWIS_S1；由于由視圖依賴它，拒絕執(zhí)行使用級(jí)聯(lián)刪除：

DROPVIEWIS_S1CASCADE;將連同由它導(dǎo)出的視圖一起刪除

3.7視圖3.7.1定義視圖3.7.2查詢視圖3.7.3更新視圖3.7.4視圖的作用3.7.2查詢視圖視圖一經(jīng)定義后，就可以像表一樣被查詢、修改、刪除和更新。

[例3.92]在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

SELECTSno，SageFROMIS_StudentWHERESage<20；3.7.2查詢視圖RDBMS實(shí)現(xiàn)視圖查詢的方法首先進(jìn)行有效性檢查然后轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢執(zhí)行修正后的查詢，此過程稱為視圖消解如上例中的查詢語句可能轉(zhuǎn)換后為：

SELECTSno，SageFROMStudentWHERESdept=‘IS’ANDSage<20；用戶不必關(guān)心如何進(jìn)行轉(zhuǎn)換查詢視圖（續(xù)）[例3.93]查詢選修了1號(hào)課程的信息系學(xué)生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno=‘1’；查詢使用了兩個(gè)數(shù)據(jù)源：一個(gè)視圖和一個(gè)基表查詢視圖（續(xù)）[例3.94]在S_G視圖中查詢平均成績(jī)?cè)?0分以上的學(xué)生學(xué)號(hào)和平均成績(jī)SELECT*FROMS_GWHEREGavg>=90；比較與如下方法的區(qū)別：CREATETABLES_G0(Snochar(9),Gavgreal);INSERTINTOS_G0SELECTSno,Avg(Grade)FROMSCGROUPBYSno;SELECT*FROMS_G0WHEREGavg>=90;3.7視圖3.7.1定義視圖3.7.2查詢視圖3.7.3更新視圖3.7.4視圖的作用3.7.3更新視圖視圖可以通過INSERT、DELETE和UPDATE語句修改其中的數(shù)據(jù)與表不同，對(duì)表的操作是直接對(duì)表中的數(shù)據(jù)進(jìn)行更新對(duì)視圖的更新則要轉(zhuǎn)換為對(duì)基表的更新因此可能存在一些限制更新視圖（續(xù)）[例3.95]將信息系學(xué)生視圖IS_Student中學(xué)號(hào)201215122的學(xué)生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='201215122'；轉(zhuǎn)換后的語句：UPDATEStudentSETSname='劉辰'WHERESno='201215122'ANDSdept='IS'；更新視圖（續(xù)）[例3.96]向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：

INSERTINTOIS_StudentVALUES(‘201215129’，‘趙新’，20)；[例3.97]刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為201215129的記錄

DELETEFROMIS_StudentWHERESno='201215129'；轉(zhuǎn)換為對(duì)基本表的更新：

DELETEFROMStudentWHERESno='201215129'ANDSdept='IS'；更新視圖（續(xù)）通過視圖修改行時(shí)，WITHCHECKOPTION可確保提交修改后，仍可通過視圖看到修改的數(shù)據(jù)。CREATEVIEWIS_Student1(Sno,Sname,Sage,Sdept)ASSELECTSno,Sname,Sage,Sdept

FROMStudent

WHERESdept='IS'WITHCHECKOPTION例:insertintoIS_Student1values('1001','zhang',20,'IS')

√

insertintoIS_Student1values('1002','wang',19,'MA')

╳更新視圖（續(xù)）更新視圖的限制：一些視圖是不可更新的，因?yàn)閷?duì)這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新允許對(duì)行列子集視圖進(jìn)行更新具有計(jì)算列或多表視圖等不可更新例：視圖S_G為不可更新視圖。UPDATES_GSETGavg=90WHERESno=‘201215121’；

Grav中的值是通過運(yùn)算得來，更新時(shí)不能找到其唯一來源更新視圖（續(xù)）數(shù)據(jù)來自多個(gè)基表：學(xué)號(hào)姓名…………學(xué)號(hào)課