關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL-3.修改和視圖.ppt

An Introduction to Database System,數(shù)據(jù)庫系統(tǒng)概論 An Introduction to Database System 第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL (續(xù)2）,An Introduction to Database System,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖,An Introduction to Database System,3.4 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),An Introduction to Database System,3.4.1 插入數(shù)據(jù),兩種插入數(shù)據(jù)方式 插入單個元組 插入子查詢結(jié)果,An Introduction to Database System,1. 插入單個元組,語句格式 INSERT INTO (，) VALUES ( ， ) 功能 將新元組插入指定表中。,An Introduction to Database System,插入單個元組（續(xù)）,例1 將一個新學(xué)生記錄 （學(xué)號：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子句 指定要插入數(shù)據(jù)的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致 指定部分屬性列：插入的元組在其余屬性列上取空值 VALUES子句 提供的值必須與INTO子句匹配 值的個數(shù) 值的類型,An Introduction to Database System,2. 插入子查詢結(jié)果,語句格式 INSERT INTO ( ， ) 子查詢； 功能 將子查詢結(jié)果插入指定表中,An Introduction to Database System,插入子查詢結(jié)果（續(xù)）,例3 所有學(xué)生均選修了課程號為“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 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),An Introduction to Database System,3.4.2 修改數(shù)據(jù),語句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中滿足WHERE子句條件的元組,An Introduction to Database System,修改數(shù)據(jù)（續(xù)）,三種修改方式 修改某一個元組的值 修改多個元組的值 帶子查詢的修改語句,An Introduction to Database System,1. 修改某一個元組的值,例4 將學(xué)生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,An Introduction to Database System,2. 修改多個元組的值,例5 將所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；,An Introduction to Database System,修改多個元組的值(續(xù)),例6 將信息系所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,An Introduction to Database System,3. 帶子查詢的修改語句,例7 將IS系全體學(xué)生的成績置零。 UPDATE sc SET Grade=0 WHERE sno IN (SELETE sno FROM Student WHERE sdept =IS),An Introduction to Database System,4.用子查詢結(jié)果更新,例8在student中增加一個平均成績列avggrade，根據(jù)SC計算IS系的每個學(xué)生的平均成績并更新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 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),An Introduction to Database System,3.4.3 刪除數(shù)據(jù),DELETE FROM WHERE ； 功能 刪除指定表中滿足WHERE子句條件的元組 WHERE子句 指定要刪除的元組 缺省表示要修改表中的所有元組,An Introduction to Database System,刪除數(shù)據(jù)（續(xù)）,三種刪除方式 刪除某一個元組的值 刪除多個元組的值 帶子查詢的刪除語句,An Introduction to Database System,1. 刪除某一個元組的值,例9 刪除學(xué)號為95019的學(xué)生記錄。 DELETE FROM Student WHERE Sno=95019；,An Introduction to Database System,2. 刪除多個元組的值,例10 刪除2號課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2； 例11 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；,An Introduction to Database System,3. 帶子查詢的刪除語句,例12 刪除IS系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE sno IN (SELETE sno FROM Student WHERE sdept =IS),An Introduction to Database System,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖,An Introduction to Database System,3.5 視 圖,視圖的特點(diǎn) 虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表 只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變,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語句時只是把 視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中 的SELECT語句。 在對視圖查詢時，按視圖的定義從基本表 中將數(shù)據(jù)查出。,An Introduction to Database System,例：把對所有學(xué)生及所有選修課程的成績定義成視圖,建立視圖： 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,組成視圖的屬性列名,把所有學(xué)生所有課程的平均成績定義成視圖： 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 ； 該語句從數(shù)據(jù)字典中刪除指定的視圖定義 由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除 刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除,An Introduction to Database System,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,An Introduction to Database System,查詢視圖,與基本表的查詢的相同 可以和其他表和視圖一起進(jìn)行多表查詢,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 更新視圖,更新視圖與更新基本表相同 對視圖的更新事實(shí)上是對其對應(yīng)子查詢中的基本表的更新，即對視圖的更新必須能被轉(zhuǎn)換為對基本表的更新 所以對視圖的更新是有限制的,An Introduction to Database System,實(shí)際系統(tǒng)對視圖更新的限制,(1) 若視圖是由兩個以上基本表導(dǎo)出的，則此視圖不允許更新。 (2) 若視圖的字段來自字段表達(dá)式或常數(shù)，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。 (3) 若視圖的字段來自集函數(shù)，則此視圖不允許更新。,An Introduction to Database System,更新視圖（續(xù)）,(4) 若視圖定義中含有GROUP BY子句，則此視圖不允許更新。 (5) 若視圖定義中含有DISTINCT短語，則此視圖不允許更新。 (6) 若視圖定義中有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。 (7) 一個不允許更新的視圖上定義的視圖也不允許更新,An Introduction to Database System,WITH CHECK OPTION,建立“IS”系學(xué)生的視圖 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 視圖的作用,視圖能夠簡化用戶的操作 視圖使用戶能以多種角度看待同一數(shù)據(jù) 視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性 視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù),An Introduction to Database System,建立視圖（續(xù)）,建立1號課程的選課視圖，并要求透過該視圖進(jìn)行的更新操作只涉及1號課程，同時對該視圖的任何操作只能在工作時間進(jìn)行。 CREATE VIEW v_elective AS SELECT Sno,Cno,Grade FROM SC WHERE Cno= 1 AND DATEPART(HOU