數據庫系統概論AnIntroductiontoDatabaseSystem第三章關系數據庫標準語言SQL(續2）第三章

關系數據庫標準語言SQL3.1SQL概述3.2學生-課程數據庫3.3數據定義3.4數據查詢3.5數據更新3.6視圖3.7小結3.5數據更新3.5.1插入數據3.5.2修改數據3.5.3刪除數據

3.5.1插入數據兩種插入數據方式1.插入元組2.插入子查詢結果可以一次插入多個元組一、插入元組語句格式

INSERT INTO<表名>[(<屬性列1>[，<屬性列2>…)] VALUES(<常量1>[，<常量2>]…)功能將新元組插入指定表中插入元組（續）

INTO子句屬性列的順序可與表定義中的順序不一致沒有指定屬性列指定部分屬性列

VALUES子句

提供的值必須與INTO子句匹配值的個數值的類型插入元組（續）［例1］將一個新學生元組（學號：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。

INSERTINTOStudent(Sno，Sname，Ssex，Sdept，Sage)VALUES('200215128'，'陳冬'，'男'，'IS'，18)；插入元組（續）［例2］將學生張成民的信息插入到Student表中。

INSERT INTOStudent VALUES(‘200215126’，‘張成民’，‘男’，18，'CS');插入元組（續）［例3］插入一條選課記錄('200215128'，'1')。

INSERTINTOSC(Sno，Cno)VALUES(‘200215128’，‘1’)；

RDBMS將在新插入記錄的Grade列上自動地賦空值。或者：

INSERTINTOSCVALUES('200215128'，'1'，NULL)；二、插入子查詢結果語句格式

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

子查詢；功能將子查詢結果插入指定表中插入子查詢結果（續）

INTO子句(與插入元組類似)

子查詢SELECT子句目標列必須與INTO子句匹配值的個數值的類型插入子查詢結果（續）[例4]對每一個系，求學生的平均年齡，并把結果存入數據庫。第一步：建表

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

Avg_ageSMALLINT)；

/*學生平均年齡*/

插入子查詢結果（續）第二步：插入數據

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；數據庫更新：插入子查詢結果往Studio(name,address,presC#)中添加在Movie(title,year,length,inColor,studioname,producerC#)中提到的，但沒有在Studio出現的所有制片廠。

Insertintostudio(name)SelectdistinctstudionameFrommovieWherestudionamenotin(selectnamefromstudio);插入子查詢結果（續）RDBMS在執行插入語句時會檢查所插元組是否破壞表上已定義的完整性規則實體完整性參照完整性用戶定義的完整性NOTNULL約束UNIQUE約束值域約束SqlServer中一次插入多行數據Insertintostudentselect‘95001’,‘Mike’unionselect‘95002’,‘Lily’unionselect‘95003’,‘sara’3.5數據更新3.5.1插入數據3.5.2修改數據3.5.3刪除數據

3.4.2修改數據語句格式

UPDATE<表名>SET<列名>=<表達式>[，<列名>=<表達式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組修改數據（續）SET子句指定修改方式要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組修改數據（續）三種修改方式1.修改某一個元組的值2.修改多個元組的值3.帶子查詢的修改語句1.修改某一個元組的值[例5]將學生200215121的年齡改為22歲

UPDATEStudentSETSage=22WHERESno='200215121'；

2.修改多個元組的值[例6]將所有學生的年齡增加1歲

UPDATEStudentSETSage=Sage+1；3.帶子查詢的修改語句[例7]將計算機科學系全體學生的成績置零。

UPDATESCSETGrade=0WHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；修改數據（續）RDBMS在執行修改語句時會檢查修改操作是否破壞表上已定義的完整性規則實體完整性主碼不允許修改用戶定義的完整性

NOTNULL約束

UNIQUE約束值域約束更新操作與數據庫的一致性需要注意一點，更新操作不要破壞關系的實體完整性和引用完整性。例如不能對Student、SC中的Sno以及Course、SC中的Cno插入空值或修改為空值，如果Student表中某學號修改了，必須對SC中相應學號作同樣修改；刪除了Student表中的某個學生，則必須對SC中此學生的選修記錄也作刪除。否則會出現不一致。這一類數據的一致性，RDBMS應當自動支持。各個系統可能會采取不同的策略來保證完整性。一般來講，當用戶在進行更新操作時，系統會自動進行檢查，對于那些會破壞完整性的操作將拒絕執行，或者自動執行一些相關操作來保證完整性。3.5數據更新3.5.1插入數據3.5.2修改數據3.5.3刪除數據

3.5.3刪除數據語句格式

DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要刪除表中的全部元組，表的定義仍在字典中刪除數據（續）三種刪除方式1.刪除某一個元組的值2.刪除多個元組的值3.帶子查詢的刪除語句1.刪除某一個元組的值[例8]刪除學號為200215128的學生記錄。

DELETEFROMStudentWHERESno=200215128'；2.刪除多個元組的值[例9]刪除所有的學生選課記錄。

DELETEFROMSC；3.帶子查詢的刪除語句[例10]刪除計算機科學系所有學生的選課記錄。

DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno)；第三章

關系數據庫標準語言SQL3.1SQL概述3.2學生-課程數據庫3.3數據定義3.4數據查詢3.5數據更新3.6視圖3.7小結3.6視圖視圖的特點虛表，是從一個或幾個基本表（或視圖）導出的表只存放視圖的定義，不存放視圖對應的數據基表中的數據發生變化，從視圖中查詢出的數據也隨之改變3.6視圖基于視圖的操作

查詢刪除受限更新定義基于該視圖的新視圖3.6視圖3.6.1定義視圖3.6.2查詢視圖3.6.3更新視圖3.6.4視圖的作用3.6.1定義視圖建立視圖刪除視圖一、建立視圖語句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]AS<子查詢>[WITHCHECKOPTION]；組成視圖的屬性列名：全部省略或全部指定子查詢不允許含有ORDERBY子句和DISTINCT短語建立視圖（續）RDBMS執行CREATEVIEW語句時只是把視圖定義存入數據字典，并不執行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數據查出。建立視圖（續）

[例1]建立信息系學生的視圖。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'；建立視圖（續）[例2]建立信息系學生的視圖，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生

。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；建立視圖（續）對IS_Student視圖的更新操作：修改操作：自動加上Sdept='IS'的條件刪除操作：自動加上Sdept='IS'的條件插入操作：自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作如果沒有提供Sdept屬性值，則自動定義Sdept為'IS'建立視圖（續）基于多個基表的視圖[例3]建立信息系選修了1號課程的學生視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)ASSELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'AND

Student.Sno=SC.SnoAND

SC.Cno='1'；建立視圖（續）基于視圖的視圖

[例4]建立信息系選修了1號課程且成績在90分以上的學生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；建立視圖（續）帶表達式的視圖[例5]定義一個反映學生出生年份的視圖。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2009-SageFROMStudent；建立視圖（續）分組視圖[例6]將學生的學號及他的平均成績定義為一個視圖 假設SC表中“成績”列Grade為數字型

CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；建立視圖（續）不指定屬性列[例7]將Student表中所有女生記錄定義為一個視圖

CREATEVIEWF_Student(F_Sno，name，sex，age，dept)AS

SELECT*FROMStudentWHERESsex=‘女’；缺點：修改基表Student的結構后，Student表與F_Student視圖的映象關系被破壞，導致該視圖不能正確工作。上面例子中，CREATEVIEW子句有些沒有寫上列名表，這樣視圖中的列將自動來自子查詢中SELECT子句描述的列。但有三種情況必須在CREATEVIEW子句中寫上列名表：⑴某個目標列不是單純的屬性名，而是集函數或列表達式；⑵多表連接時選出了幾個同名列作為視圖的字段；⑶需要在視圖中為某個列定義新的更合適的名字。重命名屬性Createviewstudent_cs(cs_sno,cs_sname)asselectsno,snamefromstudentwheresdept='cs'二、刪除視圖語句的格式：

DROPVIEW<視圖名>；該語句從數據字典中刪除指定的視圖定義如果該視圖上還導出了其他視圖，使用CASCADE級聯刪除語句，把該視圖和由它導出的所有視圖一起刪除刪除基表時，由該基表導出的所有視圖定義都必須顯式地使用DROPVIEW語句刪除刪除視圖(續）［例8］刪除視圖BT_S：DROPVIEWBT_S；

刪除視圖IS_S1：DROPVIEWIS_S1；拒絕執行級聯刪除：

DROPVIEWIS_S1CASCADE;3.6視圖3.6.1定義視圖3.6.2查詢視圖3.6.3更新視圖3.6.4視圖的作用3.6.2查詢視圖用戶角度：查詢視圖與查詢基本表相同RDBMS實現視圖查詢的方法視圖消解法（ViewResolution）進行有效性檢查轉換成等價的對基本表的查詢執行修正后的查詢查詢視圖（續）[例9]在信息系學生的視圖中找出年齡小于20歲的學生。

SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student視圖的定義(參見視圖定義例1)查詢視圖（續）視圖消解轉換后的查詢語句為：

SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；查詢視圖（續）[例10]查詢選修了1號課程的信息系學生SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；查詢視圖（續）視圖消解法的局限有些情況下，視圖消解法不能生成正確查詢。

查詢視圖（續）[例11]在S_G視圖中查詢平均成績在90分以上的學生學號和平均成績SELECT*FROMS_GWHEREGavg>=90；

S_G視圖的子查詢定義：

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；查詢轉換錯誤：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；正確：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；3.6視圖3.6.1定義視圖3.6.2查詢視圖3.6.3更新視圖3.6.4視圖的作用更新視圖（續）[例12]將信息系學生視圖IS_Student中學號200215122的學生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='200215122'；轉換后的語句：UPDATEStudentSETSname='劉辰'WHERESno='200215122'ANDSdept='IS'；更新視圖（續）[例13]向信息系學生視圖IS_S中插入一個新的學生記錄：200215129，趙新，20歲INSERTINTOIS_StudentVALUES(‘95029’，‘趙新’，20)；轉換為對基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(‘200215129'，'趙新'，20，'IS')；更新視圖（續）[例14]刪除信息系學生視圖IS_Student中學號為200215129的記錄DELETEFROMIS_StudentWHERESno='200215129'；轉換為對基本表的更新：DELETEFROMStudentWHERESno='200215129'ANDSdept='IS'；更新視圖（續）更新視圖的限制：一些視圖是不可更新的，因為對這些視圖的更新不能唯一地有意義地轉換成對相應基本表的更新例：視圖S_G為不可更新視圖。UPDATES_GSET