第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL(三)

1、An Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL SQL An Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1 SQL概述概述3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 空值的處理空值的處理 3.7 視圖視圖3.8 小結(jié)小結(jié)An Introduction to Database System3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)

2、修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) An Introduction to Database System3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)v兩種插入數(shù)據(jù)方式兩種插入數(shù)據(jù)方式1. 插入元組插入元組2. 插入子查詢結(jié)果插入子查詢結(jié)果可以一次插入多個(gè)元組可以一次插入多個(gè)元組 An Introduction to Database System一、插入元組一、插入元組v語(yǔ)句格式語(yǔ)句格式INSERTINTO (，)VALUES ( ， )v功能功能n將新元組插入指定表中將新元組插入指定表中An Introduction to Database System插入元組（續(xù)）插入元組（續(xù)）v INTO子句子句n屬

3、性列的順序可與表定義中的順序不一致屬性列的順序可與表定義中的順序不一致n沒(méi)有指定屬性列沒(méi)有指定屬性列：表示要插入的是一條完整的元組，且屬性：表示要插入的是一條完整的元組，且屬性列的屬性與表定義中的順序一致列的屬性與表定義中的順序一致n指定部分屬性列指定部分屬性列：插入的元組在其余屬性列上取空值：插入的元組在其余屬性列上取空值v VALUES子句子句n 提供的值必須與提供的值必須與INTO子句匹配子句匹配值的個(gè)數(shù)值的個(gè)數(shù)值的類型值的類型An Introduction to Database System插入元組（續(xù)）插入元組（續(xù)）例例1 將一個(gè)新學(xué)生元組（學(xué)號(hào)：將一個(gè)新學(xué)生元組（學(xué)號(hào)：20021

4、5128；姓名：陳冬；姓名：陳冬；性別：男；所在系：性別：男；所在系：IS；年齡：；年齡：18歲）插入到歲）插入到Student表表中。中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陳冬陳冬，男男，IS，18)；Student(Sno , Sname , Ssex , Sage , Sdept)Course(Cno , Cname , Cpno , Credit)SC(Sno , Cno , Grade)屬性列的順序和表定義中屬性列的順序和表定義中的順序不一致的順序不一致An Introduction

5、to Database System插入元組（續(xù)）插入元組（續(xù)）例例2 將學(xué)生將學(xué)生張成民張成民的信息插入到的信息插入到Student表中。表中。 INSERT INTO Student VALUES (200215126， 張成民張成民， 男男，18，CS); 沒(méi)有指定屬性列沒(méi)有指定屬性列Student(Sno , Sname , Ssex , Sage , Sdept)Course(Cno , Cname , Cpno , Credit)SC(Sno , Cno , Grade)An Introduction to Database System插入元組（續(xù)）插入元組（續(xù)）例例3 插入一條

6、選課記錄插入一條選課記錄( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS將在新插入記錄的將在新插入記錄的Grade列上自動(dòng)地賦列上自動(dòng)地賦空值空值 或者：或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；指定部分屬性列指定部分屬性列沒(méi)有指定屬性列沒(méi)有指定屬性列An Introduction to Database System二、插入子查詢結(jié)果二、插入子查詢結(jié)果v語(yǔ)句格式語(yǔ)句格式 INSERT INTO ( ， ) 子查詢子查詢；v功能功能 將子查詢結(jié)

7、果插入指定表中將子查詢結(jié)果插入指定表中An Introduction to Database System插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）v INTO子句子句(與插入元組類似與插入元組類似)n屬性列的順序可與表定義中的順序不一致屬性列的順序可與表定義中的順序不一致n沒(méi)有指定屬性列沒(méi)有指定屬性列n指定部分屬性列指定部分屬性列v 子查詢子查詢nSELECT子句子句目標(biāo)列目標(biāo)列必須與必須與INTO子句子句匹配匹配值的個(gè)數(shù)值的個(gè)數(shù)值的類型值的類型An Introduction to Database System插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）例例4 對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把

8、結(jié)果對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫(kù)。存入數(shù)據(jù)庫(kù)。第一步：建表第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15), /* 系名系名*/ Avg_age SMALLINT)； /*學(xué)生平均年齡學(xué)生平均年齡*/ An Introduction to Database System插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù)第二步：插入數(shù)據(jù) INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；值的個(gè)數(shù)值的個(gè)數(shù)值的類

9、型值的類型An Introduction to Database System插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插入的元組是否破壞在執(zhí)行插入語(yǔ)句時(shí)會(huì)檢查所插入的元組是否破壞表上已定義的完整性規(guī)則表上已定義的完整性規(guī)則 實(shí)體完整性實(shí)體完整性 參照完整性參照完整性 用戶定義的完整性用戶定義的完整性NOT NULL約束約束UNIQUE約束約束值域約束值域約束An Introduction to Database System3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) An Introdu

10、ction to Database System3.4.2 修改數(shù)據(jù)修改數(shù)據(jù)v語(yǔ)句格式語(yǔ)句格式 UPDATE SET =，= WHERE ；v功能功能n修改指定表中滿足修改指定表中滿足WHERE子句條件的元組子句條件的元組An Introduction to Database System修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）nSET子句子句指定修改方式指定修改方式要修改的列要修改的列修改后取值修改后取值nWHERE子句子句指定要修改的元組指定要修改的元組缺省表示要修改表中的所有元組缺省表示要修改表中的所有元組An Introduction to Database System修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)

11、）v三種修改方式三種修改方式1. 修改某一個(gè)元組的值修改某一個(gè)元組的值2. 修改多個(gè)元組的值修改多個(gè)元組的值3. 帶子查詢的修改語(yǔ)句帶子查詢的修改語(yǔ)句An Introduction to Database System1. 修改某一個(gè)元組的值修改某一個(gè)元組的值例例5 將學(xué)生將學(xué)生200215121的年齡改為的年齡改為22歲歲 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； An Introduction to Database System2. 修改多個(gè)元組的值修改多個(gè)元組的值例例6 將所有學(xué)生的年齡增加將所有學(xué)生的年齡增加1歲歲 UPDAT

12、E Student SET Sage= Sage+1；例例 將信息系所有學(xué)生的年齡增加將信息系所有學(xué)生的年齡增加1歲。歲。 UPDATE Student SET Sage= Sage+1 WHERE Sdept= IS ；An Introduction to Database System3. 帶子查詢的修改語(yǔ)句帶子查詢的修改語(yǔ)句例例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。 UPDATE SC SET Grade=0 WHERE CS = (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)；相關(guān)子查詢相關(guān)

13、子查詢An Introduction to Database System帶子查詢的修改語(yǔ)句（續(xù)）帶子查詢的修改語(yǔ)句（續(xù)）例例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。另解：另解： UPDATE SC SET Grade=0 WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept=計(jì)算機(jī)計(jì)算機(jī))不相關(guān)子查詢不相關(guān)子查詢An Introduction to Database System修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修改操作是否破在執(zhí)行修改語(yǔ)句時(shí)會(huì)檢查修改操作是否破壞表上已定義的完整性規(guī)則壞表

14、上已定義的完整性規(guī)則n實(shí)體完整性實(shí)體完整性n主碼不允許修改主碼不允許修改n用戶定義的完整性用戶定義的完整性 NOT NULL約束約束 UNIQUE約束約束 值域約束值域約束An Introduction to Database System3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) An Introduction to Database System3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù)v 語(yǔ)句格式語(yǔ)句格式 DELETE FROM WHERE ；v 功能功能n刪除指定表中滿足刪除指定表中滿足WHERE子句條件的元組子句條件的元組v

15、 WHERE子句子句n指定要?jiǎng)h除的元組指定要?jiǎng)h除的元組n缺省表示要?jiǎng)h除表中的全部元組，缺省表示要?jiǎng)h除表中的全部元組，表的定義仍在字典中表的定義仍在字典中An Introduction to Database System刪除數(shù)據(jù)（續(xù)）刪除數(shù)據(jù)（續(xù)）v三種刪除方式三種刪除方式1. 刪除某一個(gè)元組的值刪除某一個(gè)元組的值2. 刪除多個(gè)元組的值刪除多個(gè)元組的值3. 帶子查詢的刪除語(yǔ)句帶子查詢的刪除語(yǔ)句An Introduction to Database System1. 刪除某一個(gè)元組的值刪除某一個(gè)元組的值例例8 刪除學(xué)號(hào)為刪除學(xué)號(hào)為200215128的學(xué)生記錄。的學(xué)生記錄。 DELETE FROM

16、 Student WHERE Sno= 200215128 ；An Introduction to Database System2. 刪除多個(gè)元組的值刪除多個(gè)元組的值例例9 刪除所有的學(xué)生選課記錄。刪除所有的學(xué)生選課記錄。 DELETE FROM SC；An Introduction to Database System3. 帶子查詢的刪除語(yǔ)句帶子查詢的刪除語(yǔ)句例例10 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno=SC.Sn

17、o)；相關(guān)子查詢相關(guān)子查詢An Introduction to Database System帶子查詢的刪除語(yǔ)句（續(xù)）帶子查詢的刪除語(yǔ)句（續(xù)）例例10 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。另解：另解： DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept=計(jì)算機(jī)計(jì)算機(jī))不相關(guān)子查詢不相關(guān)子查詢An Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1 SQL概述概述3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)3.3 數(shù)

18、據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 空值的處理空值的處理 3.7 視圖視圖3.8 小結(jié)小結(jié)3.6 空值的處理空值的處理v空值就是空值就是“不知道不知道”或或“不存在不存在”或或“無(wú)意義無(wú)意義”的值的值v一般有以下幾種情況：一般有以下幾種情況： 該屬性應(yīng)該有一個(gè)值，但目前不知道它的具體值該屬性應(yīng)該有一個(gè)值，但目前不知道它的具體值 該屬性不應(yīng)該有值該屬性不應(yīng)該有值 由于某種原因不便于填寫由于某種原因不便于填寫1. 空值的產(chǎn)生空值的產(chǎn)生v空值是一個(gè)很特殊的值，空值是一個(gè)很特殊的值，含有不確定性含有不確定性。對(duì)關(guān)系運(yùn)。對(duì)關(guān)系運(yùn)算帶來(lái)特殊的問(wèn)題，需要做特殊的處理算帶來(lái)特

19、殊的問(wèn)題，需要做特殊的處理v空值的產(chǎn)生空值的產(chǎn)生例例 2向向SC表中插入一個(gè)元組，學(xué)生號(hào)是表中插入一個(gè)元組，學(xué)生號(hào)是”201215126”，課程，課程號(hào)是號(hào)是”1”，成績(jī)?yōu)榭眨煽?jī)?yōu)榭?INSERT INTO SC(Sno,Cno,Grade) VALUES(201215126 ,1,NULL); /*該學(xué)生還沒(méi)有考試成績(jī)，取空值該學(xué)生還沒(méi)有考試成績(jī)，取空值*/或或 INSERT INTO SC(Sno,Cno) VALUES( 201215126 ,1); /*沒(méi)有賦值的屬性，其值為空值沒(méi)有賦值的屬性，其值為空值*/空值的產(chǎn)生（續(xù)）空值的產(chǎn)生（續(xù)）例例3.80 將將Student表中學(xué)生號(hào)為

20、表中學(xué)生號(hào)為”201215200”的學(xué)生所屬的學(xué)生所屬的系改為空值的系改為空值 UPDATE StudentSET Sdept = NULLWHERE Sno=201215200;2. 空值的判斷空值的判斷v判斷一個(gè)屬性的值是否為空值，用判斷一個(gè)屬性的值是否為空值，用IS NULL或或IS NOT NULL來(lái)表示來(lái)表示例例 3.81 從從Student表中找出漏填了數(shù)據(jù)的學(xué)生信息表中找出漏填了數(shù)據(jù)的學(xué)生信息SELECT *FROM StudentWHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL;3. 空值的

21、約束條件空值的約束條件v屬性定義（或者域定義）中屬性定義（或者域定義）中 有有NOT NULL約束條件約束條件的不能取空值的不能取空值 加了加了UNIQUE限制的屬性只能取一個(gè)空值限制的屬性只能取一個(gè)空值 碼屬性碼屬性不能取空值不能取空值4. 空值空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算n 空值與另一個(gè)值（包括另一個(gè)空值）的空值與另一個(gè)值（包括另一個(gè)空值）的算術(shù)運(yùn)算的結(jié)果為算術(shù)運(yùn)算的結(jié)果為空值空值n 空值與另一個(gè)值（包括另一個(gè)空值）的空值與另一個(gè)值（包括另一個(gè)空值）的比較運(yùn)算的結(jié)果為比較運(yùn)算的結(jié)果為UNKNOWNn 有有UNKNOWN后，傳統(tǒng)二值（后，傳統(tǒng)二值（TRUE

22、，F(xiàn)ALSE）邏輯就擴(kuò)）邏輯就擴(kuò)展成了三值邏輯展成了三值邏輯空值空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算( (續(xù)續(xù)) ) x y x AND y x OR y NOT x T T T T F T U U T F T F F T F U T U T U U U U U U U F F U U F T F T T F U F U T F F F F TT表示表示TRUE，F(xiàn)表示表示FALSE，U表示表示UNKNOWN表表3.8 邏輯運(yùn)算符真值表邏輯運(yùn)算符真值表 空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算（續(xù)）空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算（續(xù)）例例3.82 找出選修找出選修1號(hào)

23、課程的不及格的學(xué)生號(hào)課程的不及格的學(xué)生 SELECT Sno FROM SC WHERE Grade 60 AND Cno=1; 查詢結(jié)果查詢結(jié)果不包括缺考不包括缺考的學(xué)生，因?yàn)樗麄兊牡膶W(xué)生，因?yàn)樗麄兊腉rade值為值為NULL空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算（續(xù)）空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算（續(xù)）SELECT SnoFROM SCWHERE Grade 60 AND Cno=1UNIONSELECT SnoFROM SCWHERE Grade IS NULL AND Cno=1或者或者SELECT SnoFROM SCWHERE Cno=1 AND (Grade60 OR Grade

24、 IS NULL);例例 3.83 選出選修選出選修1號(hào)課程的不及格的學(xué)生以及缺考的學(xué)生。號(hào)課程的不及格的學(xué)生以及缺考的學(xué)生。An Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言關(guān)系數(shù)據(jù)庫(kù)標(biāo)準(zhǔn)語(yǔ)言SQL3.1 SQL概述概述3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫(kù)課程數(shù)據(jù)庫(kù)3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 空值的處理空值的處理 3.7 視圖視圖3.8 小結(jié)小結(jié)An Introduction to Database System3.6 視視 圖圖視圖的特點(diǎn)視圖的特點(diǎn)v 虛表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表虛

25、表，是從一個(gè)或幾個(gè)基本表（或視圖）導(dǎo)出的表v 只存放視圖的定義，不存放視圖對(duì)應(yīng)的數(shù)據(jù)只存放視圖的定義，不存放視圖對(duì)應(yīng)的數(shù)據(jù)v 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變之改變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 更新視圖更

26、新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System3.6.1 定義視圖定義視圖v建立視圖建立視圖v刪除視圖刪除視圖An Introduction to Database System一、建立視圖一、建立視圖v語(yǔ)句格式語(yǔ)句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION ；v子查詢不允許含有子查詢不允許含有ORDER BY子句子句和和DISTINCT短語(yǔ)短語(yǔ)v組成視圖的屬性列名：組成視圖的屬性列名：全部省略或全部指定全部省略或全部指定An Introduction to Database System建立視圖

27、（續(xù)）建立視圖（續(xù)） 省略省略: 由子查詢中由子查詢中SELECT目標(biāo)列中的諸字段組成目標(biāo)列中的諸字段組成 明確指定視圖的所有列名明確指定視圖的所有列名:(1) 某個(gè)目標(biāo)列是聚集函數(shù)或列表達(dá)式某個(gè)目標(biāo)列是聚集函數(shù)或列表達(dá)式(2) 目標(biāo)列為目標(biāo)列為 *(3) 多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段(4) 需要在視圖中為某個(gè)列啟用新的更合適的名字需要在視圖中為某個(gè)列啟用新的更合適的名字An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)）vRDBMS執(zhí)行執(zhí)行CREATE VIEW語(yǔ)句時(shí)只是語(yǔ)句時(shí)只是把視圖定把視圖

28、定義存入數(shù)據(jù)字典義存入數(shù)據(jù)字典，并不執(zhí)行其中的并不執(zhí)行其中的SELECT語(yǔ)句語(yǔ)句v在對(duì)視圖在對(duì)視圖查詢查詢時(shí)，按視圖的定義從基本表中將數(shù)時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出據(jù)查出An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)） 例例1 建立信息系學(xué)生的視圖。建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS； 從單個(gè)基本表導(dǎo)出從單個(gè)基本表導(dǎo)出 只是去掉了基本表的某些行和某些列只是去掉了基本表的某些行和某些列 保留了碼保留了

29、碼省略屬性列，由子查詢中省略屬性列，由子查詢中SELECT目標(biāo)列中的諸字目標(biāo)列中的諸字段組成段組成An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）vWITH CHECK OPTION 透過(guò)視圖進(jìn)行透過(guò)視圖進(jìn)行增刪改增刪改操作時(shí)，不得破壞視圖定義操作時(shí)，不得破壞視圖定義中的謂詞條件中的謂詞條件 （即子查詢中的條件表達(dá)式即子查詢中的條件表達(dá)式）An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）例例2 建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖

30、只有信息系的學(xué)生。作時(shí)仍需保證該視圖只有信息系的學(xué)生。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；透過(guò)視圖進(jìn)行透過(guò)視圖進(jìn)行增刪改增刪改操作時(shí)，操作時(shí)，不得破壞視圖定義中的子查詢不得破壞視圖定義中的子查詢中的條件表達(dá)式中的條件表達(dá)式An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）對(duì)對(duì)IS_Student視圖的更新操作：視圖的更新操作：v 修改操作：自動(dòng)加上修改操作：自動(dòng)加上Sdept= IS的條件的條件

31、v 刪除操作：自動(dòng)加上刪除操作：自動(dòng)加上Sdept= IS的條件的條件v 插入操作：自動(dòng)檢查插入操作：自動(dòng)檢查Sdept屬性值是否為屬性值是否為IS 如果不是，則拒絕該插入操作如果不是，則拒絕該插入操作 如果沒(méi)有提供如果沒(méi)有提供Sdept屬性值，則自動(dòng)定義屬性值，則自動(dòng)定義Sdept為為ISAn Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）v基于多個(gè)基表的視圖基于多個(gè)基表的視圖例例3 建立信息系選修了建立信息系選修了1號(hào)課程的學(xué)生視圖。號(hào)課程的學(xué)生視圖。 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT St

32、udent.Sno，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）v基于視圖的視圖基于視圖的視圖例例4 建立信息系選修了建立信息系選修了1號(hào)課程且成績(jī)?cè)谔?hào)課程且成績(jī)?cè)?0分以上的學(xué)分以上的學(xué)生的視圖。生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；IS_S1是信息系選修了是信息系選修

33、了1號(hào)課程學(xué)生的視圖號(hào)課程學(xué)生的視圖An Introduction to Database System建立視圖（續(xù)）建立視圖（續(xù)）v帶表達(dá)式的視圖帶表達(dá)式的視圖例例5 定義一個(gè)反映學(xué)生出生年份的視圖。定義一個(gè)反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，Sname，2014-Sage FROM Student；設(shè)置一些派生屬性列設(shè)置一些派生屬性列, 也稱為虛擬列也稱為虛擬列-Sbirth 帶表達(dá)式的視圖必須帶表達(dá)式的視圖必須明確定義組成視圖的各個(gè)屬性列名明確定義組成視圖的各個(gè)屬性列名表達(dá)式表達(dá)式An Introdu

34、ction to Database System建立視圖（續(xù)）建立視圖（續(xù)）v分組視圖分組視圖例例6 將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖 假設(shè)假設(shè)SC表中表中“成績(jī)成績(jī)”列列Grade為數(shù)字型為數(shù)字型 CREATE VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)）v不指定屬性列不指定屬性列例例7 將將Student表中所有女生記錄定義為一個(gè)視圖表中所有女生記錄定義為一個(gè)視

35、圖 CREATE VIEW F_Student(F_Sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女女； 缺點(diǎn)：缺點(diǎn)： 修改基表修改基表Student的結(jié)構(gòu)后，的結(jié)構(gòu)后，Student表與表與F_Student視圖的映象關(guān)系視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作被破壞，導(dǎo)致該視圖不能正確工作以以 SELECT * 方式創(chuàng)建方式創(chuàng)建的視圖可擴(kuò)充性差，應(yīng)的視圖可擴(kuò)充性差，應(yīng)盡可能避免盡可能避免An Introduction to Database System 建立視圖（續(xù)）建立視圖（續(xù)） CREATE VIEW F_S

36、tudent(F_Sno，name，sex，age，dept) AS SELECT Sno，Sname，Ssex，Sage，Sdept FROM Student WHERE Ssex=女女； 為為基表基表Student增加屬性列增加屬性列不會(huì)破壞不會(huì)破壞Student表表與與F_Student視圖視圖的映象關(guān)系的映象關(guān)系A(chǔ)n Introduction to Database System二、刪除視圖二、刪除視圖v語(yǔ)句的格式：語(yǔ)句的格式：DROP VIEW ； 該語(yǔ)句從數(shù)據(jù)字典中該語(yǔ)句從數(shù)據(jù)字典中刪除指定的視圖定義刪除指定的視圖定義 如果該視圖上還導(dǎo)出了其他視圖，使用如果該視圖上還導(dǎo)出了其他視圖

37、，使用CASCADE級(jí)級(jí)聯(lián)刪除語(yǔ)句，把該視圖和由它導(dǎo)出的所有視圖一起刪聯(lián)刪除語(yǔ)句，把該視圖和由它導(dǎo)出的所有視圖一起刪除除 刪除基表時(shí)刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用式地使用DROP VIEW語(yǔ)句刪除語(yǔ)句刪除 An Introduction to Database System刪除視圖刪除視圖(續(xù)）續(xù)）例例8 刪除視圖刪除視圖BT_S： DROP VIEW BT_S； 刪除視圖刪除視圖IS_S1：DROP VIEW IS_S1；拒絕執(zhí)行拒絕執(zhí)行級(jí)聯(lián)刪除：級(jí)聯(lián)刪除： DROP VIEW IS_S1 CASCADE; An Introduct

38、ion to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System3.6.2 查詢視圖查詢視圖v 用戶角度：用戶角度：查詢視圖與查詢基本表相同查詢視圖與查詢基本表相同v RDBMS實(shí)現(xiàn)視圖查詢的方法實(shí)現(xiàn)視圖查詢的方法 視圖消解法（視圖消解法（View Resolution） 進(jìn)行有效性檢查進(jìn)行有效性檢查 轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢 執(zhí)行執(zhí)行修正修正后的查詢后的查詢An Introductio

39、n to Database System視圖消解法（視圖消解法（View Resolution） 進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如進(jìn)行有效性檢查，檢查查詢的表、視圖等是否存在。如果存在，則從數(shù)據(jù)字典中取出視圖的定義果存在，則從數(shù)據(jù)字典中取出視圖的定義 把視圖定義中的把視圖定義中的子查詢子查詢與與用戶的查詢用戶的查詢結(jié)合起來(lái)，結(jié)合起來(lái)，轉(zhuǎn)換成轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢等價(jià)的對(duì)基本表的查詢 執(zhí)行執(zhí)行修正修正后的查詢后的查詢An Introduction to Database System查詢視圖（續(xù)）查詢視圖（續(xù)）例例9 在信息系學(xué)生的視圖中找出年齡小于在信息系學(xué)生的視圖中找出年

40、齡小于20歲的歲的學(xué)生。學(xué)生。 SELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student視圖視圖的的定義定義 (參見(jiàn)視圖定義例參見(jiàn)視圖定義例1) CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；An Introduction to Database System查詢視圖（續(xù)）查詢視圖（續(xù)）視圖消解轉(zhuǎn)換后的查詢語(yǔ)句為：視圖消解轉(zhuǎn)換后的查詢語(yǔ)句為： SELECT Sno，Sage FROM Student WHERE Sdept= IS A

41、ND Sage=90； S_G視圖的子查詢定義：視圖的子查詢定義： CREATE VIEW S_G (Sno，Gavg) AS SELECT Sno，AVG(Grade)FROM SCGROUP BY Sno；An Introduction to Database System查詢轉(zhuǎn)換查詢轉(zhuǎn)換錯(cuò)誤：錯(cuò)誤：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GROUP BY Sno；正確：正確：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；WHERE子句不能用聚集函數(shù)作子句不能用

42、聚集函數(shù)作為條件表達(dá)式為條件表達(dá)式An Introduction to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例例12 將信息系學(xué)生視圖將信息系學(xué)生視圖IS_Student中學(xué)號(hào)中學(xué)號(hào)200215122的學(xué)的學(xué)生姓名改為生姓名改為“劉辰劉辰”UPDATE IS_StudentSET Sname= 劉辰劉辰WHERE Sno= 200215122；轉(zhuǎn)換后的語(yǔ)句：轉(zhuǎn)換后的語(yǔ)句：U

43、PDATE StudentSET Sname= 劉辰劉辰WHERE Sno= 200215122 AND Sdept= IS；IS_Student的定義：的定義：CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例例13 向信息系學(xué)生視圖向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：中插入一個(gè)新的學(xué)生記錄：200215129，趙新，趙新，20歲。歲。INSERTINTO IS_StudentVA

44、LUES(200215129，趙新趙新，20)；轉(zhuǎn)換為對(duì)基本表的更新：轉(zhuǎn)換為對(duì)基本表的更新：INSERTINTO Student (Sno，Sname，Sage，Sdept)VALUES(200215129 ，趙新趙新，20，IS )；系統(tǒng)自動(dòng)將系名系統(tǒng)自動(dòng)將系名IS放入放入VALUESAn Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）例例14 刪除信息系學(xué)生視圖刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為中學(xué)號(hào)為200215129的記錄的記錄 DELETEFROM IS_StudentWHERE Sno= 200215129 ；轉(zhuǎn)換為對(duì)基本表的

45、更新：轉(zhuǎn)換為對(duì)基本表的更新：DELETEFROM StudentWHERE Sno= 200215129 AND Sdept= IS；An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）v 更新視圖的限制：更新視圖的限制：一些視圖是不可更新的，因?yàn)閷?duì)這些視一些視圖是不可更新的，因?yàn)閷?duì)這些視圖的更新不能圖的更新不能唯一唯一地地有意義有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新例例 視圖視圖S_G為不可更新視圖。為不可更新視圖。UPDATE S_GSET Gavg=90WHERE Sno= 200215121；這個(gè)對(duì)視圖的更新這個(gè)對(duì)視圖的更

46、新無(wú)法轉(zhuǎn)換成無(wú)法轉(zhuǎn)換成對(duì)基本表對(duì)基本表SC的更新的更新S_G的定義：的定義：CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno；An Introduction to Database System更新視圖（續(xù)）更新視圖（續(xù)）v允許對(duì)行列子集視圖進(jìn)行更新允許對(duì)行列子集視圖進(jìn)行更新v對(duì)其他類型視圖的更新不同系統(tǒng)有不同限對(duì)其他類型視圖的更新不同系統(tǒng)有不同限制制An Introduction to Database System3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用An Introduction to Database System3.6.4 視圖的作用視圖的作用v1. 視圖能夠簡(jiǎn)化用戶的操作視圖能夠簡(jiǎn)化用戶的操作v2. 視圖使用戶能以多種角度看待同