關系數據庫標準語言SQL-3.修改和視圖.ppt

An Introduction to Database System,數據庫系統概論 An Introduction to Database System 第三章 關系數據庫標準語言SQL (續(xù)2）,An Introduction to Database System,第三章 關系數據庫標準語言SQL,3.1 SQL概述 3.2 數據定義 3.3 查詢 3.4 數據更新 3.5 視圖,An Introduction to Database System,3.4 數 據 更 新,3.4.1 插入數據 3.4.2 修改數據 3.4.3 刪除數據,An Introduction to Database System,3.4.1 插入數據,兩種插入數據方式 插入單個元組 插入子查詢結果,An Introduction to Database System,1. 插入單個元組,語句格式 INSERT INTO (，) VALUES ( ， ) 功能 將新元組插入指定表中。,An Introduction to Database System,插入單個元組（續(xù)）,例1 將一個新學生記錄 （學號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student VALUES (95020，陳冬，男，IS，18)；,An Introduction to Database System,插入單個元組（續(xù)）,例2 插入一條選課記錄( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的記錄在Grade列上取空值,An Introduction to Database System,插入單個元組（續(xù)）,INTO子句 指定要插入數據的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致 指定部分屬性列：插入的元組在其余屬性列上取空值 VALUES子句 提供的值必須與INTO子句匹配 值的個數 值的類型,An Introduction to Database System,2. 插入子查詢結果,語句格式 INSERT INTO ( ， ) 子查詢； 功能 將子查詢結果插入指定表中,An Introduction to Database System,插入子查詢結果（續(xù)）,例3 所有學生均選修了課程號為“IT”開始的所有課程，把此選課信息插入SC中 INSERT INTO sc (sno,cno) SELECT a.sno,o FROM student a ,course b WHERE o LIKE IT%,An Introduction to Database System,3.4 數 據 更 新,3.4.1 插入數據 3.4.2 修改數據 3.4.3 刪除數據,An Introduction to Database System,3.4.2 修改數據,語句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中滿足WHERE子句條件的元組,An Introduction to Database System,修改數據（續(xù)）,三種修改方式 修改某一個元組的值 修改多個元組的值 帶子查詢的修改語句,An Introduction to Database System,1. 修改某一個元組的值,例4 將學生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,An Introduction to Database System,2. 修改多個元組的值,例5 將所有學生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；,An Introduction to Database System,修改多個元組的值(續(xù)),例6 將信息系所有學生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,An Introduction to Database System,3. 帶子查詢的修改語句,例7 將IS系全體學生的成績置零。 UPDATE sc SET Grade=0 WHERE sno IN (SELETE sno FROM Student WHERE sdept =IS),An Introduction to Database System,4.用子查詢結果更新,例8在student中增加一個平均成績列avggrade，根據SC計算IS系的每個學生的平均成績并更新student該列值。 UPDATE student SET avggrade=(SELECT avg(grade) FROM sc WHERE sc.sno=students.sno) WHERE cdept=IS,An Introduction to Database System,3.4 數 據 更 新,3.4.1 插入數據 3.4.2 修改數據 3.4.3 刪除數據,An Introduction to Database System,3.4.3 刪除數據,DELETE FROM WHERE ； 功能 刪除指定表中滿足WHERE子句條件的元組 WHERE子句 指定要刪除的元組 缺省表示要修改表中的所有元組,An Introduction to Database System,刪除數據（續(xù)）,三種刪除方式 刪除某一個元組的值 刪除多個元組的值 帶子查詢的刪除語句,An Introduction to Database System,1. 刪除某一個元組的值,例9 刪除學號為95019的學生記錄。 DELETE FROM Student WHERE Sno=95019；,An Introduction to Database System,2. 刪除多個元組的值,例10 刪除2號課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2； 例11 刪除所有的學生選課記錄。 DELETE FROM SC；,An Introduction to Database System,3. 帶子查詢的刪除語句,例12 刪除IS系所有學生的選課記錄。 DELETE FROM SC WHERE sno IN (SELETE sno FROM Student WHERE sdept =IS),An Introduction to Database System,第三章 關系數據庫標準語言SQL,3.1 SQL概述 3.2 數據定義 3.3 查詢 3.4 數據更新 3.5 視圖,An Introduction to Database System,3.5 視 圖,視圖的特點 虛表，是從一個或幾個基本表（或視圖）導出的表 只存放視圖的定義，不會出現數據冗余 基表中的數據發(fā)生變化，從視圖中查詢出的數據也隨之改變,An Introduction to Database System,3.5 視 圖,基于視圖的操作 查詢 刪除 受限更新 定義基于該視圖的新視圖,An Introduction to Database System,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,An Introduction to Database System,1. 建立視圖,語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；,An Introduction to Database System,建立視圖（續(xù)）,DBMS執(zhí)行CREATE VIEW語句時只是把 視圖的定義存入數據字典，并不執(zhí)行其中 的SELECT語句。 在對視圖查詢時，按視圖的定義從基本表 中將數據查出。,An Introduction to Database System,例：把對所有學生及所有選修課程的成績定義成視圖,建立視圖： CREATE VIEW v_allgrade AS SELECT a.sno,a.sname,ame,c.grade FROM student a,course b,sc c WHERE a.sno=c.sno AND o=o 查詢視圖： SELECT * FROM v_allgrade,An Introduction to Database System,組成視圖的屬性列名,把所有學生所有課程的平均成績定義成視圖： CREATE VIEW v_avggrade (sno,sname,avggrade) AS SELECT a.sno,a.sname,avg(c.grade) FROM student a,course b,sc c WHERE a.sno=c.sno AND o=o GROUP BY a.sno,a.sname,An Introduction to Database System,2. 刪除視圖,DROP VIEW ； 該語句從數據字典中刪除指定的視圖定義 由該視圖導出的其他視圖定義仍在數據字典中，但已不能使用，必須顯式刪除 刪除基表時，由該基表導出的所有視圖定義都必須顯式刪除,An Introduction to Database System,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,An Introduction to Database System,查詢視圖,與基本表的查詢的相同 可以和其他表和視圖一起進行多表查詢,An Introduction to Database System,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,An Introduction to Database System,3.5.3 更新視圖,更新視圖與更新基本表相同 對視圖的更新事實上是對其對應子查詢中的基本表的更新，即對視圖的更新必須能被轉換為對基本表的更新 所以對視圖的更新是有限制的,An Introduction to Database System,實際系統對視圖更新的限制,(1) 若視圖是由兩個以上基本表導出的，則此視圖不允許更新。 (2) 若視圖的字段來自字段表達式或常數，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。 (3) 若視圖的字段來自集函數，則此視圖不允許更新。,An Introduction to Database System,更新視圖（續(xù)）,(4) 若視圖定義中含有GROUP BY子句，則此視圖不允許更新。 (5) 若視圖定義中含有DISTINCT短語，則此視圖不允許更新。 (6) 若視圖定義中有嵌套查詢，并且內層查詢的FROM子句中涉及的表也是導出該視圖的基本表，則此視圖不允許更新。 (7) 一個不允許更新的視圖上定義的視圖也不允許更新,An Introduction to Database System,WITH CHECK OPTION,建立“IS”系學生的視圖 CREATE VIEW v_isstudent AS SELECT Sno,Sname,Sage,Sdept FROM Student WHERE Sdept= IS WITH CHECK OPTION,An Introduction to Database System,WITH CHECK OPTION 對v_isstudent視圖的更新操作的影響,插入操作：以下插入語句僅第一句能成功 insert into v_isstudent (sno,sname,sdept) values (95006 , 吳月明,IS) insert into v_isstudent (sno,sname,sdept) values (95006, 吳月明, CS),An Introduction to Database System,WITH CHECK OPTION 對v_isstudent視圖的更新操作的影響,更新：不允許通過視圖修改Sdept，即下列語句不能被成功執(zhí)行： UPDATE v_isstudent SET Sdept=MA 雖能執(zhí)行以下語句，但是無意義的語句 UPDATE v_isstudent SET Sdept=IS 刪除：無限制,An Introduction to Database System,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,An Introduction to Database System,3.5.4 視圖的作用,視圖能夠簡化用戶的操作 視圖使用戶能以多種角度看待同一數據 視圖對重構數據庫提供了一定程度的邏輯獨立性 視圖能夠對機密數據提供安全保護,An Introduction to Database System,建立視圖（續(xù)）,建立1號課程的選課視圖，并要求透過該視圖進行的更新操作只涉及1號課程，同時對該視圖的任何操作只能在工作時間進行。 CREATE VIEW v_elective AS SELECT Sno,Cno,Grade FROM SC WHERE Cno= 1 AND DATEPART(HOU