數(shù)據(jù)庫概論-第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL.ppt

計算機(jī)科學(xué)與技術(shù)學(xué)院,數(shù)據(jù)庫系統(tǒng)概論 第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL (3）,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式SQL 3.8 小結(jié),3.4 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),3.4.1 插入數(shù)據(jù),兩種插入數(shù)據(jù)方式 插入單個元組 插入子查詢結(jié)果,1. 插入單個元組,語句格式 INSERT INTO (，) VALUES ( ， ) 功能 將新元組插入指定表中。,插入單個元組（續(xù)）,例1 將一個新學(xué)生記錄 （學(xué)號：95020；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student VALUES (95020，陳冬，男，IS，18)；,插入單個元組（續(xù)）,例2 插入一條選課記錄( 95020，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 95020 ， 1 )； 新插入的記錄在Grade列上取空值,插入單個元組（續(xù)）,插入單個元組（續(xù)）,INTO子句 指定要插入數(shù)據(jù)的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致 指定部分屬性列：插入的元組在其余屬性列上取空值 VALUES子句 提供的值必須與INTO子句匹配 值的個數(shù) 值的類型,2. 插入子查詢結(jié)果,語句格式 INSERT INTO ( ， ) 子查詢； 功能 將子查詢結(jié)果插入指定表中,插入子查詢結(jié)果（續(xù)）,例3 對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。 第一步：建表 CREATE TABLE Deptage (Sdept CHAR(15) /* 系名*/ Avgage SMALLINT)； /*學(xué)生平均年齡*/,插入子查詢結(jié)果（續(xù)）,第二步：插入數(shù)據(jù) INSERT INTO Deptage(Sdept，Avgage) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；,插入子查詢結(jié)果（續(xù)）,INTO子句(與插入單條元組類似) 指定要插入數(shù)據(jù)的表名及屬性列 屬性列的順序可與表定義中的順序不一致 沒有指定屬性列：表示要插入的是一條完整的元組 指定部分屬性列：插入的元組在其余屬性列上取空值 子查詢 SELECT子句目標(biāo)列必須與INTO子句匹配 值的個數(shù) 值的類型,插入子查詢結(jié)果（續(xù)）,DBMS在執(zhí)行插入語句時會檢查所插元組是 否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 參照完整性 用戶定義的完整性 對于有NOT NULL約束的屬性列是否提供了非空值 對于有UNIQUE約束的屬性列是否提供了非重復(fù)值 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi),3.4 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),3.4.2 修改數(shù)據(jù),語句格式 UPDATE SET =，= WHERE ； 功能 修改指定表中滿足WHERE子句條件的元組,修改數(shù)據(jù)（續(xù)）,三種修改方式 修改某一個元組的值 修改多個元組的值 帶子查詢的修改語句,1. 修改某一個元組的值,例4 將學(xué)生95001的年齡改為22歲。 UPDATE Student SET Sage=22 WHERE Sno= 95001 ；,2. 修改多個元組的值,例5 將所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1；,修改多個元組的值(續(xù)),例6 將信息系所有學(xué)生的年齡增加1歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；,3. 帶子查詢的修改語句,例7 將計算機(jī)科學(xué)系全體學(xué)生的成績置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；,修改數(shù)據(jù)（續(xù)）,SET子句 指定修改方式 要修改的列 修改后取值 WHERE子句 指定要修改的元組 缺省表示要修改表中的所有元組,修改數(shù)據(jù)（續(xù)）,DBMS在執(zhí)行修改語句時會檢查修改操作 是否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 主碼不允許修改 用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束,3.4 數(shù) 據(jù) 更 新,3.4.1 插入數(shù)據(jù) 3.4.2 修改數(shù)據(jù) 3.4.3 刪除數(shù)據(jù),3.4.3 刪除數(shù)據(jù),DELETE FROM WHERE ； 功能 刪除指定表中滿足WHERE子句條件的元組 WHERE子句 指定要刪除的元組 缺省表示要修改表中的所有元組,刪除數(shù)據(jù)（續(xù)）,三種刪除方式 刪除某一個元組的值 刪除多個元組的值 帶子查詢的刪除語句,1. 刪除某一個元組的值,例8 刪除學(xué)號為95019的學(xué)生記錄。 DELETE FROM Student WHERE Sno=95020；,2. 刪除多個元組的值,例9 刪除2號課程的所有選課記錄。 DELETE FROM SC； WHERE Cno=2； 例10 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；,3. 帶子查詢的刪除語句,例11 刪除計算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；,刪除數(shù)據(jù)(續(xù)),DBMS在執(zhí)行插入語句時會檢查所插元組 是否破壞表上已定義的完整性規(guī)則 參照完整性 不允許刪除 級聯(lián)刪除,更新數(shù)據(jù)與數(shù)據(jù)一致性,DBMS在執(zhí)行插入、刪除、更新語句時必 須保證數(shù)據(jù)庫一致性 必須有事務(wù)的概念和原子性 完整性檢查和保證,第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL,3.1 SQL概述 3.2 數(shù)據(jù)定義 3.3 查詢 3.4 數(shù)據(jù)更新 3.5 視圖 3.6 數(shù)據(jù)控制 3.7 嵌入式SQL 3.8 小結(jié),3.5 視 圖,視圖的特點(diǎn) 虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表 數(shù)據(jù)庫中只存放視圖的定義，而不存放視圖對應(yīng)的數(shù)據(jù)，這些數(shù)據(jù)仍存放在原來的基本表中。不會出現(xiàn)數(shù)據(jù)冗余 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變 視圖可以和基本表一樣被查詢、被刪除。,3.5 視 圖,基于視圖的操作 查詢 刪除 受限更新 定義基于該視圖的新視圖,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,1. 建立視圖,語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION； 其中子查詢可以是任意復(fù)雜的SELECT語句，但通常不允許含有ORDER BY子句和DISTINCT短語。,建立視圖（續(xù)）,DBMS執(zhí)行CREATE VIEW語句時只是把 視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中 的SELECT語句。 在對視圖查詢時，按視圖的定義從基本表 中將數(shù)據(jù)查出。,組成視圖的屬性列名,全部省略或全部指定 省略: 由子查詢中SELECT目標(biāo)列中的諸字段組成 明確指定視圖的所有列名: (1) 某個目標(biāo)列是集函數(shù)或列表達(dá)式 (2) 多表連接時選出了幾個同名列作為視圖的字段 (3) 需要在視圖中為某個列啟用新的更合適的名字,行列子集視圖,例1 建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS； 從單個基本表導(dǎo)出 只是去掉了基本表的某些行和某些列 保留了碼,建立視圖（續(xù)）,WITH CHECK OPTION 透過視圖進(jìn)行增刪改操作時，不得破壞視 圖定義中的謂詞條件 （即子查詢中的條件表達(dá)式）,WITH CHECK OPTION的視圖,例2 建立信息系學(xué)生的視圖，并要求透過該視圖進(jìn)行的更新操作只涉及信息系學(xué)生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；,對IS_Student視圖的更新操作,修改操作：DBMS自動加上Sdept= IS的條件 刪除操作：DBMS自動加上Sdept= IS的條件 插入操作：DBMS自動檢查Sdept屬性值是否為IS 如果不是，則拒絕該插入操作 如果沒有提供Sdept屬性值，則自動定義Sdept為IS,基于多個基表的視圖,例3 建立信息系選修了1號課程的學(xué)生視圖。 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；,基于視圖的視圖,例4 建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；,帶表達(dá)式的視圖,例5 定義一個反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2006-Sage FROM Student 設(shè)置一些派生屬性列, 也稱為虛擬列-Sbirth 帶表達(dá)式的視圖必須明確定義組成視圖的各個屬 性列名,建立分組視圖,例6 將學(xué)生的學(xué)號及他的平均成績定義為一個視圖 假設(shè)SC表中“成績”列Grade為數(shù)字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,建立視圖（續(xù)）,一類不易擴(kuò)充的視圖 以 SELECT * 方式創(chuàng)建的視圖可擴(kuò)充性差，應(yīng)盡可能避免,建立視圖（續(xù)）,例7將Student表中所有女生記錄定義為一個視圖 CREATE VIEW F_Student1(stdnum，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女； 缺點(diǎn)：修改基表Student的結(jié)構(gòu)后，Student表 與F_Student1視圖的映象關(guān)系被破壞， 導(dǎo)致該視圖不能正確工作。,建立視圖（續(xù)）,CREATE VIEW F_Student2 (stdnum，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex=女； 為基表Student增加屬性列不會破壞Student表 與F_Student2視圖的映象關(guān)系。,常見的視圖形式,行列子集視圖 WITH CHECK OPTION的視圖 基于多個基表的視圖 基于視圖的視圖 帶表達(dá)式的視圖 分組視圖,2. 刪除視圖,DROP VIEW ； 該語句從數(shù)據(jù)字典中刪除指定的視圖定義 由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除 刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式刪除,刪除視圖(續(xù)）,例8 刪除視圖IS_S1 DROP VIEW IS_S1；,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,3.5.2 查詢視圖,從用戶角度：查詢視圖與查詢基本表相同 DBMS實(shí)現(xiàn)視圖查詢的方法 實(shí)體化視圖（View Materialization） 有效性檢查：檢查所查詢的視圖是否存在 執(zhí)行視圖定義，將視圖臨時實(shí)體化，生成臨時表 查詢視圖轉(zhuǎn)換為查詢臨時表 查詢完畢刪除被實(shí)體化的視圖(臨時表),查詢視圖（續(xù)）,視圖消解法（View Resolution） 進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義 把視圖定義中的子查詢與用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢 執(zhí)行修正后的查詢,查詢視圖（續(xù)）,例1 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。 SELECT Sno，Sage FROM IS_Student WHERE Sage25； IS_Student視圖的定義 (視圖定義例1)： CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；,查詢視圖（續(xù)）,視圖實(shí)體化法 視圖消解法 轉(zhuǎn)換后的查詢語句為： SELECT Sno，Sage FROM Student WHERE Sdept= IS AND Sage25；,查詢視圖（續(xù)）,例2 查詢信息系選修了1號課程的學(xué)生 SELECT Sno，Sname FROM IS_Student，SC WHERE IS_Student.Sno =SC.Sno AND SC.Cno= 1；,查詢視圖（續(xù)）,視圖消解法的局限 有些情況下，視圖消解法不能生成正確查詢。采用視圖消解法的DBMS會限制這類查詢。,查詢視圖（續(xù)）,例3在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績 SELECT * FROM S_G WHERE Gavg=90； S_G視圖定義： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,查詢轉(zhuǎn)換,錯誤： SELECT Sno，AVG(Grade) FROM SC WHERE AVG(Grade)=90 GROUP BY Sno； 正確： SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade)=90；,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,3.5.3 更新視圖,用戶角度：更新視圖與更新基本表相同 DBMS實(shí)現(xiàn)視圖更新的方法 視圖實(shí)體化法（View Materialization） 視圖消解法（View Resolution） 指定WITH CHECK OPTION子句后 DBMS在更新視圖時會進(jìn)行檢查，防止用戶通過視圖對不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新,更新視圖（續(xù)）,例1 將信息系學(xué)生視圖IS_Student中學(xué)號95002 的學(xué)生姓名改為“劉辰”。 UPDATE IS_Student SET Sname= 劉辰 WHERE Sno= 95002； 轉(zhuǎn)換后的語句： UPDATE Student SET Sname= 劉辰 WHERE Sno= 95002 AND Sdept= IS；,更新視圖（續(xù)）,例2 向信息系學(xué)生視圖IS_S中插入一個新的學(xué)生記錄：95029，趙新，20歲 INSERT INTO IS_Student VALUES(95029，趙新，20)； 轉(zhuǎn)換為對基本表的更新： INSERT INTO Student(Sno，Sname，Sage，Sdept) VALUES(95029，趙新，20，IS )；,更新視圖（續(xù)）,例3 刪除視圖CS_S中學(xué)號為95029的記錄 DELETE FROM IS_Student WHERE Sno= 95029； 轉(zhuǎn)換為對基本表的更新： DELETE FROM Student WHERE Sno= 95029 AND Sdept= IS；,更新視圖的限制,一些視圖是不可更新的，因?yàn)閷@些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新(對兩類方法均如此) 例：視圖S_G為不可更新視圖。 CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；,更新視圖（續(xù)）,對于如下更新語句： UPDATE S_G SET Gavg=90 WHERE Sno= 95001； 無論實(shí)體化法還是消解法都無法將其轉(zhuǎn)換成對 基本表SC的更新,實(shí)際系統(tǒng)對視圖更新的限制,允許對行列子集視圖進(jìn)行更新 對其他類型視圖的更新不同系統(tǒng)有不同限制 DB2對視圖更新的限制： (1) 若視圖是由兩個以上基本表導(dǎo)出的，則此視圖不允許更新。 (2) 若視圖的字段來自字段表達(dá)式或常數(shù)，則不允許對此視圖執(zhí)行INSERT和UPDATE操作，但允許執(zhí)行DELETE操作。,更新視圖（續(xù)）,(3) 若視圖的字段來自集函數(shù)，則此視圖不允許更新。 (4) 若視圖定義中含有GROUP BY子句，則此視圖不允許更新。 (5) 若視圖定義中含有DISTINCT短語，則此視圖不允許更新。 (6) 若視圖定義中有嵌套查詢，并且內(nèi)層查詢的FROM子句中涉及的表也是導(dǎo)出該視圖的基本表，則此視圖不允許更新。 (7) 一個不允許更新的視圖上定義的視圖也不允許更新,更新視圖（續(xù)）,例：視圖GOOD_SC(修課成績在平均成績之上的元組) CREATE VIEW GOOD_SC AS SELECT Sno，Cno，Grade FROM SC WHERE Grade (SELECT AVG(Grade) FROM SC)；,3.5 視 圖,3.5.1 定義視圖 3.5.2 查詢視圖 3.5.3 更新視圖 3.5.4 視圖的作用,1. 視圖能夠簡化用戶的操作,當(dāng)視圖中數(shù)據(jù)不是直接來自基本表時，定 義視圖能夠簡化用戶的操作 基于多張表連接形成的視圖 基于復(fù)雜嵌套查詢的視圖 含導(dǎo)出屬性的視圖,2. 視圖使用戶能以多種角