第3章數(shù)據(jù)庫關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL(更新與視圖)(2)

1、 數(shù)數(shù) 據(jù)據(jù) 更更 新新 1 插入數(shù)據(jù)插入數(shù)據(jù)2 修改數(shù)據(jù)修改數(shù)據(jù)3 刪除數(shù)據(jù)刪除數(shù)據(jù) 插入數(shù)據(jù)插入數(shù)據(jù)v兩種插入數(shù)據(jù)方式兩種插入數(shù)據(jù)方式1. 插入元組2. 插入子查詢結(jié)果可以一次插入多個元組 一、插入元組一、插入元組v語句格式語句格式INSERTINTO (，)VALUES ( ， )v功能功能n將新元組插入指定表中插入元組（續(xù)）插入元組（續(xù)）v INTO子句子句n屬性列的順序可與表定義中的順序不一致n沒有指定屬性列，則插入的元組在每列上都有值n指定部分屬性列,沒出現(xiàn)的列取空值v VALUES子句子句n 提供的值必須與INTO子句匹配值的個數(shù)值的類型字符串常數(shù)要用單引號（英文符號）括起來插入

2、元組（續(xù)）插入元組（續(xù)）例例1 將一個新學(xué)生元組（學(xué)號：將一個新學(xué)生元組（學(xué)號：201215128；姓名：陳；姓名：陳冬；性別：男；所在系：冬；性別：男；所在系：IS；年齡：；年齡：18歲）插入到歲）插入到Student表中。表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (201215128，陳冬陳冬，男男，IS，18)；插入元組（續(xù)）插入元組（續(xù)）例例2 將學(xué)生張成民的信息插入到將學(xué)生張成民的信息插入到Student表中。表中。 INSERT INTO Student VALUES (201215126，張成民張成民，男

3、男，18，CS); 只有表名，沒有屬性名，只有表名，沒有屬性名，所有列都要有值，所有列都要有值，并與表中屬性順序一致并與表中屬性順序一致插入元組（續(xù)）插入元組（續(xù)）例例3 插入一條選課記錄插入一條選課記錄( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； 沒有指定值的沒有指定值的Grade列將自動地賦空值列將自動地賦空值。 或者：或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；沒有指定屬性名，且某個列沒有指定值，則要明確給出空值沒有指定屬性名，且某個列沒有指定值，

4、則要明確給出空值二、插入子查詢結(jié)果二、插入子查詢結(jié)果v語句格式語句格式 INSERT INTO ( ， ) 子查詢；子查詢；v功能功能 將子查詢結(jié)果插入指定表中將子查詢結(jié)果插入指定表中nSELECT子句目標(biāo)列必須與INTO子句匹配值的個數(shù)值的類型插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）例例4 對每一個系，求學(xué)生的平均年齡，并把結(jié)果對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。存入數(shù)據(jù)庫。第一步：建表第一步：建表 CREATE TABLE Dept_age (Sdept CHAR(15) /* 系名系名*/ Avg_age SMALLINT)； /*學(xué)生平均年齡學(xué)生平均年齡*/ 插入子查詢結(jié)

5、果（續(xù)）插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù)第二步：插入數(shù)據(jù) INSERT INTO Dept_age(Sdept，Avg_age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入語句時會檢查所插元組是在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 參照完整性 用戶定義的完整性NOT NULL約束UNIQUE約束值域約束3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 1、 插入數(shù)據(jù)插入數(shù)據(jù)2 、 修改數(shù)據(jù)修改數(shù)據(jù)3 、 刪除數(shù)據(jù)刪除數(shù)據(jù) 修改

6、數(shù)據(jù)修改數(shù)據(jù)v語句格式語句格式 UPDATE SET =，= WHERE ；v功能功能n修改指定表中滿足WHERE子句條件的元組修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）nSET子句指定修改方式要修改的列修改后取值nWHERE子句指定要修改的元組缺省表示要修改表中的所有元組修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）v三種修改方式三種修改方式1. 修改某一個元組的值2. 修改多個元組的值3. 帶子查詢的修改語句1. 修改某一個元組的值修改某一個元組的值例例5 將學(xué)生將學(xué)生201215121的年齡改為的年齡改為22歲歲 UPDATE Student SET Sage=22 WHERE Sno= 201215121 ； 2.

7、修改多個元組的值修改多個元組的值例例6 將所有學(xué)生的年齡增加將所有學(xué)生的年齡增加1歲歲 UPDATE Student SET Sage= Sage+1；3. 帶子查詢的修改語句帶子查詢的修改語句例例7 將計算機(jī)科學(xué)系全體學(xué)生的成績置零。將計算機(jī)科學(xué)系全體學(xué)生的成績置零。 UPDATE SC SET Grade=0 WHERE CS= (SELECT Sdept FROM Student WHERE Student.Sno = SC.Sno)；修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語句時會檢查修改操作在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則是否破壞表上已定義的完整性

8、規(guī)則n實(shí)體完整性n主碼不允許修改n用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 1 、插入數(shù)據(jù)、插入數(shù)據(jù)2、 修改數(shù)據(jù)修改數(shù)據(jù)3、 刪除數(shù)據(jù)刪除數(shù)據(jù) 刪除數(shù)據(jù)刪除數(shù)據(jù)v 語句格式語句格式 DELETE FROM WHERE ；v 功能功能n刪除指定表中滿足WHERE子句條件的元組v WHERE子句子句n指定要刪除的元組n缺省表示要刪除表中的全部元組，表的定義仍在字典中刪除數(shù)據(jù)（續(xù)）刪除數(shù)據(jù)（續(xù)）v三種刪除方式三種刪除方式1. 刪除某一個元組的值2. 刪除多個元組的值3. 帶子查詢的刪除語句1. 刪除某一個元組的值刪除某一個元組的值例例8 刪除

9、學(xué)號為刪除學(xué)號為201215128的學(xué)生記錄。的學(xué)生記錄。 DELETE FROM Student WHERE Sno= 201215128 ；2. 刪除多個元組的值刪除多個元組的值例例9 刪除所有的學(xué)生選課記錄。刪除所有的學(xué)生選課記錄。 DELETE FROM SC；3. 帶子查詢的刪除語句帶子查詢的刪除語句例例10 刪除計算機(jī)科學(xué)系所有學(xué)生的選課記錄。刪除計算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE Sno IN (SELECT Sno FROM Student WHERE Sdept=CS)；空值的處理空值的處理v空值就是空值就是“不知道不知道”或或“不存在

10、不存在”或或“無意義無意義”的值。的值。v一般有以下幾種情況：一般有以下幾種情況： 該屬性應(yīng)該有一個值，但目前不知道它的具體值 該屬性不應(yīng)該有值 由于某種原因不便于填寫 v空值是一個很特殊的值，含有不確定性。對關(guān)系空值是一個很特殊的值，含有不確定性。對關(guān)系運(yùn)算帶來特殊的問題，需要做特殊的處理。運(yùn)算帶來特殊的問題，需要做特殊的處理?？罩档漠a(chǎn)生空值的產(chǎn)生 例例 向向SCSC表中插入一個元組，學(xué)號是表中插入一個元組，學(xué)號是201215126201215126，課程號是課程號是1 1，成績?yōu)榭?。，成績?yōu)榭?。INSERT INTO SC(Sno,Cno,Grade)INSERT INTO SC(Sno,

11、Cno,Grade)VALUES(201215126 ,1,NULL); /VALUES(201215126 ,1,NULL); /* *該學(xué)生還沒有該學(xué)生還沒有考試成績，取空值考試成績，取空值* */ /或或INSERT INTO SC(Sno,Cno)INSERT INTO SC(Sno,Cno)VALUES( 201215126 ,1); /VALUES( 201215126 ,1); /* *沒有賦值的屬性，沒有賦值的屬性，其值為空值其值為空值* */ /空值的產(chǎn)生空值的產(chǎn)生例例 將將Student表中學(xué)生號為表中學(xué)生號為”201215200”的學(xué)生的學(xué)生所屬的系改為空值。所屬的系改為

12、空值。UPDATE StudentSET Sdept = NULLWHERE Sno=201215200;空值的判斷空值的判斷v判斷一個屬性的值是否為空值，用判斷一個屬性的值是否為空值，用IS NULLIS NULL或或IS IS NOT NULLNOT NULL來表示。來表示。 例例 從從StudentStudent表中找出漏填了數(shù)據(jù)的學(xué)生信息表中找出漏填了數(shù)據(jù)的學(xué)生信息SELECT *FROM StudentWHERE Sname IS NULL OR Ssex IS NULL OR Sage IS NULL OR Sdept IS NULL; 空值的約束條件空值的約束條件v屬性定義（或者

13、域定義）中屬性定義（或者域定義）中 有NOT NULL約束條件的不能取空值 加了UNIQUE限制的屬性不能取空值 碼屬性不能取空值 空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算v空值與另一個值（包括另一個空值）的算術(shù)運(yùn)算空值與另一個值（包括另一個空值）的算術(shù)運(yùn)算的結(jié)果為空值的結(jié)果為空值v空值與另一個值（包括另一個空值）的比較運(yùn)算空值與另一個值（包括另一個空值）的比較運(yùn)算的結(jié)果為的結(jié)果為UNKNOWN。v有有UNKNOWN后，傳統(tǒng)二值（后，傳統(tǒng)二值（TRUE，F(xiàn)ALSE）邏輯就擴(kuò)展成了三值邏輯邏輯就擴(kuò)展成了三值邏輯 空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算空值的算術(shù)運(yùn)算、比較運(yùn)

14、算和邏輯運(yùn)算邏輯運(yùn)算符真值表邏輯運(yùn)算符真值表(T(T表示表示TRUETRUE，F(xiàn) F表示表示FALSEFALSE，U U表示表示UNKNOWN)UNKNOWN)x y x AND yx OR y NOT xT T T T FT U U T FT F F T FU T U T UU U U U UU F F U UF T F T TF U F U TF F F F T空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算空值的算術(shù)運(yùn)算、比較運(yùn)算和邏輯運(yùn)算 例例 找出選修找出選修1 1號課程的不及格的學(xué)生。號課程的不及格的學(xué)生。SELECT SnoFROM SCWHERE Grade 60 AND Cno=1;查詢結(jié)

15、果不包括缺考的學(xué)生，因?yàn)樗麄兊牟樵兘Y(jié)果不包括缺考的學(xué)生，因?yàn)樗麄兊腉radeGrade值值為為nullnull。 例例 選出選修選出選修1號課程的不及格的學(xué)生以及缺考的學(xué)生。號課程的不及格的學(xué)生以及缺考的學(xué)生。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 IS NULL); 視圖視圖 定義視圖 查詢視圖 更新視圖 視圖的作用數(shù)據(jù)庫三級模式數(shù)據(jù)庫三級模

16、式表表表、視圖表、視圖設(shè)計數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫：將對象及對象的聯(lián)系定義為單張表，力求全面將對象及對象的聯(lián)系定義為單張表，力求全面使用數(shù)據(jù)庫使用數(shù)據(jù)庫：根據(jù)應(yīng)用的需要，將表合成或分解根據(jù)應(yīng)用的需要，將表合成或分解v視圖的特點(diǎn)視圖的特點(diǎn) 虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表 只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù) 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變一、創(chuàng)建視圖一、創(chuàng)建視圖 CREATE VIEW (,.) AS WITH CHECK OPTION WITH CHECK OPTION表示對視圖進(jìn)行UPDATE、 INSERT和DELETE操作時要保證更新、插入或刪除的行滿足視圖

17、定義中的謂詞條件（即子查詢中的條件表達(dá)式）。 子查詢可以是任意的SELECT語句，是否可以含有ORDER BY子句和DISTINCT短語，則取決于具體系統(tǒng)的實(shí)現(xiàn)。定義視圖定義視圖 CREATE VIEW 必須是批查詢中的第一條語句。必須是批查詢中的第一條語句。v視圖中所包含的列名是否需要定義？視圖中所包含的列名是否需要定義？ 要么全部省略，要么全部指定。 以下以下三種情況須指定組成視圖的所有列名： 某個目標(biāo)列集函數(shù)或列表達(dá)式 多表連接時選出了幾個同名列作為視圖的字段 需要在視圖中為某個列啟用新的更合適的名字v如何執(zhí)行？如何執(zhí)行？DBMS執(zhí)行CREATE VIEW語句的結(jié)果只是把對視圖的定義存入

18、數(shù)據(jù)字典，不執(zhí)行其中的SELECT語句。在對視圖查詢時，才按視圖的定義從基本表中將數(shù)據(jù)查出。 定義視圖定義視圖視圖建立在單張表上：例例1 建立信息系學(xué)生的視圖。建立信息系學(xué)生的視圖。 create view is_studentas select sno,sname,sage from student where sdept=is例例2 建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時仍建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時仍須保證該視圖只有信息系的學(xué)生須保證該視圖只有信息系的學(xué)生 create view is_student1as select sno,sname,sage fro

19、m student where sdept=iswith check option 定義視圖定義視圖說明說明(針對針對SQL Server)： insert into is_student (sno,sname,sage)values (3001510101,王一,20) insert into is_student1 (sno,sname,sage)values (3001510101,王一,20)第2）句會產(chǎn)生錯誤？更改更改:在視圖中加入在視圖中加入is系系create view is_studentasselect sno,sname,sage,sdept from student wh

20、ere sdept=iswith check optioninsert into is_student1 (sno,sname,sage,sdept)values (3001510101,王一,20,is)定義視圖定義視圖 視圖可以建立在多個基本表上 例例3 建立信息系選修了建立信息系選修了001號課程的學(xué)生的視圖號課程的學(xué)生的視圖 create view is_s1(sno,sname,grade)as select student.sno,sname,sc.grade from student,scwhere sdept=is and student.sno=sc.sno and o=00

21、1 視圖建立在視圖上 例例4 建立信息系選修了建立信息系選修了1號課程且成績在號課程且成績在90分以上的學(xué)生分以上的學(xué)生的視圖的視圖 CREATE VIEW IS_S2 AS SELECT sno,sname ,grade FROM IS_S1 WHERE grade=90視圖IS_S2就是建立在視圖IS_S1之上的。 定義視圖定義視圖 帶表達(dá)式的視圖:例例5 定義一個反映學(xué)生出生年份的視圖定義一個反映學(xué)生出生年份的視圖 CREATE VIEW BT_S(sno,sname,Sbirth) as SELECT sno,sname,2016-sage FROM student此例中，定義視圖時必

22、須明確定義該視圖的各個屬性列名。BT_S視圖是一個帶表達(dá)式的視圖。 定義視圖定義視圖定義視圖定義視圖v分組視圖:帶有聚集函數(shù)和GROUP BY子句的查詢 例6 將學(xué)生的學(xué)號及他的平均成績定義為一個視圖 ？CREATE VIEW S_G(Sno,Gavg)ASSELECT Sno,AVG(Grade)FROM SCGROUP BY Sno;例例7 將將Student表中所有女生記錄定義為一個視圖表中所有女生記錄定義為一個視圖 CREATE VIEW F_Student(sno,sname,ssex,sage,sdept) AS SELECT * FROM Student WHERE ssex=女

23、 思考思考：視圖F_Student是由子查詢“SELECT *”建立，視圖一旦建立，Student表構(gòu)成視圖定義的一部分如果修改Student的結(jié)構(gòu)，則Student表與F_Student視圖的映像關(guān)系受到破壞，視圖不能正確工作了。解決方法解決方法： 1)建立視圖時明確指明屬性列名，而不是用select *。即： CREATE VIEW F_Student(sno,sname,ssex,sage,sdept) AS SELECT sno,sname,ssex,sage,sdept FROM StudentWHERE ssex=女 若Student表增加新列，原視圖仍能正常工作，只是新增的列不在

24、視圖中而已。 2)最安全的方法：修改基本表之后刪除原來的視圖，然后重建視圖。定義視圖定義視圖 二、二、 刪除視圖刪除視圖 DROP VIEW CASCADE一個視圖被刪除后，由此視圖導(dǎo)出的其他視圖也將失效，用戶應(yīng)該使用CASCADE級聯(lián)刪除語句將它們?nèi)縿h除。 例例8 刪除視圖刪除視圖IS_S1 DROP VIEW BT_S; /成功DROP VIEW IS_S1 ; /失敗，因?yàn)镮S_S1還導(dǎo)出了IS_S2DROP VIEW IS_S1 CASCADE; /成功定義視圖定義視圖查詢視圖如同查詢表：例9 在信息系學(xué)生視圖中找出年齡小于20歲的學(xué)生 SELECT sno,sage FROM IS

25、_Student WHERE sage20DBMS執(zhí)行此查詢時，將其與IS_Student視圖定義中的子查詢 :select sno,sname,sage from Student WHERE sdept=IS 結(jié)合起來，轉(zhuǎn)換成對基本表Student的查詢，修正后的查詢語句為：SELECT Sno, Sage FROM Student WHERE Sdept=is AND sage20這一轉(zhuǎn)換過程稱為“視圖消解”查詢視圖查詢視圖v 視圖是定義在基本表上的視圖是定義在基本表上的虛表虛表v 可以和其他基本表一起使用，可以和其他基本表一起使用，實(shí)現(xiàn)連接查實(shí)現(xiàn)連接查詢或嵌套查詢詢或嵌套查詢。例例10

26、查詢信息系選修了查詢信息系選修了1號課程的學(xué)生號課程的學(xué)生 SELECT sc.sno,sname FROM IS_Student, scWHERE IS_Student.sno=sc.sno AND o=001 本查詢涉及虛表本查詢涉及虛表IS_Student和基本表和基本表sc，通，通過這兩個表的連接來完成用戶請求。過這兩個表的連接來完成用戶請求。 查詢視圖查詢視圖v更新視圖包括（更新視圖包括（INSERT）、）、DELETE）和修改）和修改（UPDATE）三類操作。）三類操作。v對視圖的更新最終要對視圖的更新最終要轉(zhuǎn)換為對基本表的更新轉(zhuǎn)換為對基本表的更新。v定義視圖時加上定義視圖時加上W

27、ITH CHECK OPTION子句，子句，在視圖上增刪改數(shù)據(jù)時，在視圖上增刪改數(shù)據(jù)時，DBMS會進(jìn)一步檢查視會進(jìn)一步檢查視圖定義中的條件圖定義中的條件 例11 將信息系學(xué)生視圖IS_Student中學(xué)號為2001010401的學(xué)生姓名改為“李四” UPDATE IS_Student SET sname=李四 WHERE sno=2001010401 更新視圖更新視圖例例12 向信息系學(xué)生視圖向信息系學(xué)生視圖IS_Student中插入一新的學(xué)生記中插入一新的學(xué)生記錄，學(xué)號為錄，學(xué)號為20122140101，姓名為趙新，年齡為，姓名為趙新，年齡為20歲歲 INSERT INTO IS_Student VALUES(20122140101 , 趙新趙新, 20)轉(zhuǎn)換為對基本表的更新： INSERT INTO Student(sno,sname,sage,sdept)VALUES(22, 趙新, 20, null); 例例13 刪除計算機(jī)系學(xué)生視圖中學(xué)號為刪除計算機(jī)系學(xué)生視圖中學(xué)號為222的記錄的記錄 DELETE FROM IS_Student WHERE sno=222 轉(zhuǎn)換為對基本表的更新： DELETE FROM