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

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