1、An Introduction to Database System數據庫系統概論數據庫系統概論An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL SQL (續續2）An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL3.1 SQL概述概述3.2 學生學生-課程數據庫課程數據庫3.3 數據定義數據定義3.4 數據查詢數據查詢3.5 數據更新數據更新3.6 視圖視圖3.7 小結小結An Introduction to Database System3.

2、5 數數 據據 更更 新新 3.5.1 插入數據插入數據3.5.2 修改數據修改數據3.5.3 刪除數據刪除數據 An Introduction to Database System3.5.1 插入數據插入數據v兩種插入數據方式1. 插入元組2. 插入子查詢結果可以一次插入多個元組 An Introduction to Database System一、插入元組一、插入元組v語句格式INSERTINTO (，)VALUES ( ， )v功能n將新元組插入指定表中An Introduction to Database System插入元組（續）插入元組（續）v INTO子句n屬性列的順序可與表定

3、義中的順序不一致n沒有指定屬性列n指定部分屬性列v VALUES子句n 提供的值必須與INTO子句匹配值的個數值的類型An Introduction to Database System插入元組（續）插入元組（續）例1 將一個新學生元組（學號：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陳冬，男，IS，18)；An Introduction to Database System插入元組（續）插入元組（續）例2

4、將學生張成民的信息插入到Student表中。 INSERT INTO Student VALUES (200215126， 張成民， 男，18，CS); An Introduction to Database System插入元組（續）插入元組（續）例3 插入一條選課記錄( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS將在新插入記錄的Grade列上自動地賦空值。 或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；An Introduction to D

5、atabase System二、插入子查詢結果二、插入子查詢結果v語句格式 INSERT INTO ( ， ) 子查詢；v功能 將子查詢結果插入指定表中An Introduction to Database System插入子查詢結果（續）插入子查詢結果（續）v INTO子句(與插入元組類似)v 子查詢nSELECT子句目標列必須與INTO子句匹配值的個數值的類型An Introduction to Database System插入子查詢結果（續）插入子查詢結果（續）例4 對每一個系，求學生的平均年齡，并把結果存入數據庫。第一步：建表 CREATE TABLE Dept_age (Sdept

6、 CHAR(15) /* 系名*/ Avg_age SMALLINT)； /*學生平均年齡*/ An Introduction to Database System插入子查詢結果（續）插入子查詢結果（續）第二步：插入數據 INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；An Introduction to Database System插入子查詢結果（續）插入子查詢結果（續）RDBMS在執行插入語句時會檢查所插元組是否破壞表上已定義的完整性規則 實體完整性 參照完整性

7、用戶定義的完整性NOT NULL約束UNIQUE約束值域約束An Introduction to Database System3.5 數數 據據 更更 新新 3.5.1 插入數據插入數據3.5.2 修改數據修改數據3.5.3 刪除數據刪除數據 An Introduction to Database System3.4.2 修改數據修改數據v語句格式 UPDATE SET =，= WHERE ；v功能n修改指定表中滿足WHERE子句條件的元組An Introduction to Database System修改數據（續）修改數據（續）nSET子句指定修改方式要修改的列修改后取值nWHERE子

8、句指定要修改的元組缺省表示要修改表中的所有元組An Introduction to Database System修改數據（續）修改數據（續）v三種修改方式1. 修改某一個元組的值2. 修改多個元組的值3. 帶子查詢的修改語句An Introduction to Database System1. 修改某一個元組的值修改某一個元組的值例5 將學生200215121的年齡改為22歲 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； An Introduction to Database System2. 修改多個元組的值修改多個元組的值例6 將所

9、有學生的年齡增加1歲 UPDATE Student SET Sage= Sage+1；An Introduction to Database System3. 帶子查詢的修改語句帶子查詢的修改語句例7 將計算機科學系全體學生的成績置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；An Introduction to Database System修改數據（續）修改數據（續）RDBMS在執行修改語句時會檢查修改操作是否破壞表上已定義的完整性規則n實體完整性n主碼不允

10、許修改n用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束An Introduction to Database System3.5 數數 據據 更更 新新 3.5.1 插入數據插入數據3.5.2 修改數據修改數據3.5.3 刪除數據刪除數據 An Introduction to Database System3.5.3 刪除數據刪除數據v 語句格式 DELETE FROM WHERE ；v 功能n刪除指定表中滿足WHERE子句條件的元組v WHERE子句n指定要刪除的元組n缺省表示要刪除表中的全部元組，表的定義仍在字典中An Introduction to Database

11、System刪除數據（續）刪除數據（續）v三種刪除方式1. 刪除某一個元組的值2. 刪除多個元組的值3. 帶子查詢的刪除語句An Introduction to Database System1. 刪除某一個元組的值刪除某一個元組的值例8 刪除學號為200215128的學生記錄。 DELETE FROM Student WHERE Sno= 200215128 ；An Introduction to Database System2. 刪除多個元組的值刪除多個元組的值例9 刪除所有的學生選課記錄。 DELETE FROM SC；An Introduction to Database Syste

12、m3. 帶子查詢的刪除語句帶子查詢的刪除語句例10 刪除計算機科學系所有學生的選課記錄。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；An Introduction to Database System第三章第三章 關系數據庫標準語言關系數據庫標準語言SQL3.1 SQL概述概述3.2 學生學生-課程數據庫課程數據庫3.3 數據定義數據定義3.4 數據查詢數據查詢3.5 數據更新數據更新3.6 視圖視圖3.7 小結小結An Introduction to Database Syste

13、m3.6 視視 圖圖視圖的特點v 虛表，是從一個或幾個基本表（或視圖）導出的表v 只存放視圖的定義，不存放視圖對應的數據v 基表中的數據發生變化，從視圖中查詢出的數據也隨之改變An Introduction to Database System3.6 視視 圖圖基于視圖的操作v 查詢v 刪除v 受限更新v 定義基于該視圖的新視圖An Introduction to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System

14、3.6.1 定義視圖定義視圖v建立視圖v刪除視圖An Introduction to Database System一、建立視圖一、建立視圖v語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；v組成視圖的屬性列名：全部省略或全部指定v子查詢不允許含有ORDER BY子句和DISTINCT短語An Introduction to Database System 建立視圖（續）建立視圖（續）vRDBMS執行CREATE VIEW語句時只是把視圖定義存入數據字典，并不執行其中的SELECT語句。v在對視圖查詢時，按視圖的定義從基本表中將數據查出。An Introd

15、uction to Database System建立視圖（續）建立視圖（續） 例1 建立信息系學生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；An Introduction to Database System建立視圖（續）建立視圖（續）例2建立信息系學生的視圖，并要求進行修改和插入操作時仍需保證該視圖只有信息系的學生 。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS

16、 WITH CHECK OPTION；An Introduction to Database System建立視圖（續）建立視圖（續）對IS_Student視圖的更新操作：v 修改操作：自動加上Sdept= IS的條件v 刪除操作：自動加上Sdept= IS的條件v 插入操作：自動檢查Sdept屬性值是否為IS 如果不是，則拒絕該插入操作 如果沒有提供Sdept屬性值，則自動定義Sdept為ISAn Introduction to Database System建立視圖（續）建立視圖（續）v基于多個基表的視圖例3 建立信息系選修了1號課程的學生視圖。 CREATE VIEW IS_S1(Sno

17、，Sname，Grade) AS SELECT Student.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；An Introduction to Database System建立視圖（續）建立視圖（續）v基于視圖的視圖例4 建立信息系選修了1號課程且成績在90分以上的學生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；An Introduction to Databa

18、se System建立視圖（續）建立視圖（續）v帶表達式的視圖例5 定義一個反映學生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2000-Sage FROM Student；An Introduction to Database System建立視圖（續）建立視圖（續）v分組視圖例6 將學生的學號及他的平均成績定義為一個視圖 假設SC表中“成績”列Grade為數字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；An

19、 Introduction to Database System 建立視圖（續）建立視圖（續）v不指定屬性列例7將Student表中所有女生記錄定義為一個視圖 CREATE VIEW F_Student(F_Sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女； 缺點： 修改基表Student的結構后，Student表與F_Student視圖的映象關系被破壞，導致該視圖不能正確工作。An Introduction to Database System二、刪除視圖二、刪除視圖v語句的格式：DROP VIEW ； 該語句從數據字典

20、中刪除指定的視圖定義 如果該視圖上還導出了其他視圖，使用CASCADE級聯刪除語句，把該視圖和由它導出的所有視圖一起刪除 刪除基表時，由該基表導出的所有視圖定義都必須顯式地使用DROP VIEW語句刪除 An Introduction to Database System刪除視圖刪除視圖(續）續）例8 刪除視圖BT_S： DROP VIEW BT_S； 刪除視圖IS_S1：DROP VIEW IS_S1；拒絕執行級聯刪除： DROP VIEW IS_S1 CASCADE; An Introduction to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.

21、2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System3.6.2 查詢視圖查詢視圖v 用戶角度：查詢視圖與查詢基本表相同v RDBMS實現視圖查詢的方法 視圖消解法（View Resolution） 進行有效性檢查 轉換成等價的對基本表的查詢 執行修正后的查詢An Introduction to Database System查詢視圖（續）查詢視圖（續）例9 在信息系學生的視圖中找出年齡小于20歲的學生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_

22、Student視圖的定義 (參見視圖定義例1)An Introduction to Database System查詢視圖（續）查詢視圖（續）視圖消解轉換后的查詢語句為： SELECT Sno，Sage FROM Student WHERE Sdept= IS AND Sage=90； S_G視圖的子查詢定義： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；An Introduction to Database System查詢轉換查詢轉換錯誤：SELECT Sno，AVG(Grade)FROM S

23、CWHERE AVG(Grade)=90GROUP BY Sno；正確：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；An Introduction to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System更新視圖（續）更新視圖（續）例12 將信息系學生視圖IS_Student中學號200215122的學生姓名改為“劉辰”。UPDATE IS_StudentSET Sname= 劉辰WHERE Sno= 200215122 ；轉換后的語句：UPDATE StudentSET Sname= 劉辰WHERE Sno= 200215122 AND Sdept= IS；An Introduction to Database System更新視圖（續）更新視圖（續）例13 向信息系學生視圖IS_S中插入一個新的學生記錄：200215129，趙新，20歲INSERTINTO IS_StudentVALUES(95029，趙新，20)；轉換為對基本表的更新：INSERTINTO Student(Sno，Sname，Sage，