第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é)果可以一次插入多個(gè)元組 一、插入元組一、插入元組v語句格式語句格式INSERTINTO (，)VALUES ( ， )v功能功能n將新元組插入指定表中插入元組（續(xù)）插入元組（續(xù)）v INTO子句子句n屬性列的順序可與表定義中的順序不一致n沒有指定屬性列，則插入的元組在每列上都有值n指定部分屬性列,沒出現(xiàn)的列取空值v VALUES子句子句n 提供的值必須與INTO子句匹配值的個(gè)數(shù)值的類型字符串常數(shù)要用單引號(hào)（英文符號(hào)）括起來插入

2、元組（續(xù)）插入元組（續(xù)）例例1 將一個(gè)新學(xué)生元組（學(xué)號(hào)：將一個(gè)新學(xué)生元組（學(xué)號(hào)：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列將自動(dòng)地賦空值列將自動(dòng)地賦空值。 或者：或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；沒有指定屬性名，且某個(gè)列沒有指定值，則要明確給出空值沒有指定屬性名，且某個(gè)列沒有指定值，

4、則要明確給出空值二、插入子查詢結(jié)果二、插入子查詢結(jié)果v語句格式語句格式 INSERT INTO ( ， ) 子查詢；子查詢；v功能功能 將子查詢結(jié)果插入指定表中將子查詢結(jié)果插入指定表中nSELECT子句目標(biāo)列必須與INTO子句匹配值的個(gè)數(shù)值的類型插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）例例4 對(duì)每一個(gè)系，求學(xué)生的平均年齡，并把結(jié)果對(duì)每一個(gè)系，求學(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í)行插入語句時(shí)會(huì)檢查所插元組是在執(zhí)行插入語句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(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. 修改某一個(gè)元組的值2. 修改多個(gè)元組的值3. 帶子查詢的修改語句1. 修改某一個(gè)元組的值修改某一個(gè)元組的值例例5 將學(xué)生將學(xué)生201215121的年齡改為的年齡改為22歲歲 UPDATE Student SET Sage=22 WHERE Sno= 201215121 ； 2.

7、修改多個(gè)元組的值修改多個(gè)元組的值例例6 將所有學(xué)生的年齡增加將所有學(xué)生的年齡增加1歲歲 UPDATE Student SET Sage= Sage+1；3. 帶子查詢的修改語句帶子查詢的修改語句例例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)；修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語句時(shí)會(huì)檢查修改操作在執(zhí)行修改語句時(shí)會(huì)檢查修改操作是否破壞表上已定義的完整性規(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指定要?jiǎng)h除的元組n缺省表示要?jiǎng)h除表中的全部元組，表的定義仍在字典中刪除數(shù)據(jù)（續(xù)）刪除數(shù)據(jù)（續(xù)）v三種刪除方式三種刪除方式1. 刪除某一個(gè)元組的值2. 刪除多個(gè)元組的值3. 帶子查詢的刪除語句1. 刪除某一個(gè)元組的值刪除某一個(gè)元組的值例例8 刪除

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

10、不存在”或或“無意義無意義”的值。的值。v一般有以下幾種情況：一般有以下幾種情況： 該屬性應(yīng)該有一個(gè)值，但目前不知道它的具體值 該屬性不應(yīng)該有值 由于某種原因不便于填寫 v空值是一個(gè)很特殊的值，含有不確定性。對(duì)關(guān)系空值是一個(gè)很特殊的值，含有不確定性。對(duì)關(guān)系運(yùn)算帶來特殊的問題，需要做特殊的處理。運(yùn)算帶來特殊的問題，需要做特殊的處理?？罩档漠a(chǎn)生空值的產(chǎn)生 例例 向向SCSC表中插入一個(gè)元組，學(xué)號(hào)是表中插入一個(gè)元組，學(xué)號(hào)是201215126201215126，課程號(hào)是課程號(hào)是1 1，成績(jī)?yōu)榭?。，成?jī)?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é)生還沒有考試成績(jī)，取空值考試成績(jī)，取空值* */ /或或INSERT INTO SC(Sno,Cno)INSERT INTO SC(Sno,Cno)VALUES( 201215126 ,1); /VALUES( 201215126 ,1); /* *沒有賦值的屬性，沒有賦值的屬性，其值為空值其值為空值* */ /空值的產(chǎn)生空值的產(chǎn)生例例 將將Student表中學(xué)生號(hào)為表中學(xué)生號(hào)為”201215200”的學(xué)生的學(xué)生所屬的系改為空值。所屬的系改為

12、空值。UPDATE StudentSET Sdept = NULLWHERE Sno=201215200;空值的判斷空值的判斷v判斷一個(gè)屬性的值是否為空值，用判斷一個(gè)屬性的值是否為空值，用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空值與另一個(gè)值（包括另一個(gè)空值）的算術(shù)運(yùn)算空值與另一個(gè)值（包括另一個(gè)空值）的算術(shù)運(yùn)算的結(jié)果為空值的結(jié)果為空值v空值與另一個(gè)值（包括另一個(gè)空值）的比較運(yùn)算空值與另一個(gè)值（包括另一個(gè)空值）的比較運(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號(hào)課程的不及格的學(xué)生。號(hào)課程的不及格的學(xué)生。SELECT SnoFROM SCWHERE Grade 60 AND Cno=1;查詢結(jié)

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

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

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

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

19、m student where sdept=iswith check option 定義視圖定義視圖說明說明(針對(duì)針對(duì)SQL Server)： insert into is_student (sno,sname,sage)values (3001510101,王一,20) insert into is_student1 (sno,sname,sage)values (3001510101,王一,20)第2）句會(huì)產(chǎn)生錯(cuò)誤？更改更改:在視圖中加入在視圖中加入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)定義視圖定義視圖 視圖可以建立在多個(gè)基本表上 例例3 建立信息系選修了建立信息系選修了001號(hào)課程的學(xué)生的視圖號(hào)課程的學(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號(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_S2就是建立在視圖IS_S1之上的。 定義視圖定義視圖 帶表達(dá)式的視圖:例例5 定義一個(gè)反映學(xué)生出生年份的視圖定義一個(gè)反映學(xué)生出生年份的視圖 CREATE VIEW BT_S(sno,sname,Sbirth) as SELECT sno,sname,2016-sage FROM student此例中，定義視圖時(shí)必

22、須明確定義該視圖的各個(gè)屬性列名。BT_S視圖是一個(gè)帶表達(dá)式的視圖。 定義視圖定義視圖定義視圖定義視圖v分組視圖:帶有聚集函數(shù)和GROUP BY子句的查詢 例6 將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖 ？CREATE VIEW S_G(Sno,Gavg)ASSELECT Sno,AVG(Grade)FROM SCGROUP BY Sno;例例7 將將Student表中所有女生記錄定義為一個(gè)視圖表中所有女生記錄定義為一個(gè)視圖 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)建立視圖時(shí)明確指明屬性列名，而不是用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一個(gè)視圖被刪除后，由此視圖導(dǎo)出的其他視圖也將失效，用戶應(yīng)該使用CASCADE級(jí)聯(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í)行此查詢時(shí)，將其與IS_Student視圖定義中的子查詢 :select sno,sname,sage from Student WHERE sdept=IS 結(jié)合起來，轉(zhuǎn)換成對(duì)基本表Student的查詢，修正后的查詢語句為：SELECT Sno, Sage FROM Student WHERE Sdept=is AND sage20這一轉(zhuǎn)換過程稱為“視圖消解”查詢視圖查詢視圖v 視圖是定義在基本表上的視圖是定義在基本表上的虛表虛表v 可以和其他基本表一起使用，可以和其他基本表一起使用，實(shí)現(xiàn)連接查實(shí)現(xiàn)連接查詢或嵌套查詢?cè)兓蚯短撞樵儭＠?0

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

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