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

1、數(shù)據(jù)庫系統(tǒng)概論數(shù)據(jù)庫系統(tǒng)概論An Introduction to Database System第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL SQL (續(xù)續(xù)2）第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述概述3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 視圖視圖3.7 小結(jié)小結(jié)3.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ù) 3.5.1 插入數(shù)據(jù)插入數(shù)據(jù)v兩種插入數(shù)據(jù)方式1. 插入元組2. 插入子查詢結(jié)果可以一次插

2、入多個(gè)元組 一、插入元組一、插入元組v語句格式INSERTINTO (，)VALUES ( ， )v功能n將新元組插入指定表中插入元組（續(xù)）插入元組（續(xù)）v INTO子句n屬性列的順序可與表定義中的順序不一致n沒有指定屬性列n指定部分屬性列v VALUES子句n 提供的值必須與INTO子句匹配值的個(gè)數(shù)值的類型插入元組（續(xù)）插入元組（續(xù)）例1 將一個(gè)新學(xué)生元組（學(xué)號(hào)：200215128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。 INSERT INTO Student (Sno，Sname，Ssex，Sdept，Sage) VALUES (200215128，陳

3、冬，男，IS，18)；插入元組（續(xù)）插入元組（續(xù)）例2 將學(xué)生張成民的信息插入到Student表中。 INSERT INTO Student VALUES (200215126， 張成民， 男，18，CS); 插入元組（續(xù)）插入元組（續(xù)）例3 插入一條選課記錄( 200215128，1 )。 INSERT INTO SC(Sno，Cno) VALUES ( 200215128 ， 1 )； RDBMS將在新插入記錄的Grade列上自動(dòng)地賦空值。 或者： INSERT INTO SC VALUES ( 200215128 ， 1 ，NULL)；二、插入子查詢結(jié)果二、插入子查詢結(jié)果v語句格式 IN

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

5、age) SELECT Sdept，AVG(Sage) FROM Student GROUP BY Sdept；數(shù)據(jù)庫更新：插入子查詢結(jié)果數(shù)據(jù)庫更新：插入子查詢結(jié)果 往往Studio(name ,address,presCStudio(name ,address,presC#)#)中添加在中添加在Movie(title,year,length,inColor,studioname,produceMovie(title,year,length,inColor,studioname,producerCrC#)#)中提到的，但沒有在中提到的，但沒有在StudioStudio出現(xiàn)的所有制片廠。出現(xiàn)的所

6、有制片廠。 Insert into studio(name) Select distinct studioname From movie Where studioname not in (select name from studio);插入子查詢結(jié)果（續(xù)）插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入語句時(shí)會(huì)檢查所插元組是否破壞表上已定義的完整性規(guī)則 實(shí)體完整性 參照完整性 用戶定義的完整性NOT NULL約束UNIQUE約束值域約束Sql Server中一次插入多行數(shù)據(jù)中一次插入多行數(shù)據(jù)vInsert into student select 95001, Mike union select 95

7、002, Lily union select 95003, sara 3.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ù) 3.4.2 修改數(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è)元

8、組的值例5 將學(xué)生200215121的年齡改為22歲 UPDATE Student SET Sage=22 WHERE Sno= 200215121 ； 2. 修改多個(gè)元組的值修改多個(gè)元組的值例6 將所有學(xué)生的年齡增加1歲 UPDATE Student SET Sage= Sage+1；3. 帶子查詢的修改語句帶子查詢的修改語句例7 將計(jì)算機(jī)科學(xué)系全體學(xué)生的成績(jī)置零。 UPDATE SC SET Grade=0 WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno = SC.Sno)；修改數(shù)據(jù)（續(xù)）修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語句時(shí)

9、會(huì)檢查修改操作是否破壞表上已定義的完整性規(guī)則n實(shí)體完整性n主碼不允許修改n用戶定義的完整性 NOT NULL約束 UNIQUE約束 值域約束更新操作與數(shù)據(jù)庫的一致性更新操作與數(shù)據(jù)庫的一致性 需要注意一點(diǎn)，更新操作不要破壞關(guān)系的實(shí)體完整性和引用完整性。需要注意一點(diǎn)，更新操作不要破壞關(guān)系的實(shí)體完整性和引用完整性。例如不能對(duì)例如不能對(duì)Student、SC中的中的Sno以及以及Course、SC中的中的 Cno插入空值插入空值或修改為空值，如果或修改為空值，如果 Student表中某學(xué)號(hào)修改了，必須對(duì)表中某學(xué)號(hào)修改了，必須對(duì)SC中相應(yīng)學(xué)號(hào)中相應(yīng)學(xué)號(hào)作同樣修改；刪除了作同樣修改；刪除了Student表中

10、的某個(gè)學(xué)生，則必須對(duì)表中的某個(gè)學(xué)生，則必須對(duì)SC中此學(xué)生的中此學(xué)生的選修記錄也作刪除。否則會(huì)出現(xiàn)不一致。選修記錄也作刪除。否則會(huì)出現(xiàn)不一致。 這一類數(shù)據(jù)的一致性，這一類數(shù)據(jù)的一致性，RDBMS應(yīng)當(dāng)自動(dòng)支持。各個(gè)系統(tǒng)可能會(huì)采應(yīng)當(dāng)自動(dòng)支持。各個(gè)系統(tǒng)可能會(huì)采取不同的策略來保證完整性。一般來講，當(dāng)用戶在進(jìn)行更新操作時(shí)，系取不同的策略來保證完整性。一般來講，當(dāng)用戶在進(jìn)行更新操作時(shí)，系統(tǒng)會(huì)自動(dòng)進(jìn)行檢查，對(duì)于那些會(huì)破壞完整性的操作將拒絕執(zhí)行，或者自統(tǒng)會(huì)自動(dòng)進(jìn)行檢查，對(duì)于那些會(huì)破壞完整性的操作將拒絕執(zhí)行，或者自動(dòng)執(zhí)行一些相關(guān)操作來保證完整性。動(dòng)執(zhí)行一些相關(guān)操作來保證完整性。3.5 數(shù)數(shù) 據(jù)據(jù) 更更 新新 3.

11、5.1 插入數(shù)據(jù)插入數(shù)據(jù)3.5.2 修改數(shù)據(jù)修改數(shù)據(jù)3.5.3 刪除數(shù)據(jù)刪除數(shù)據(jù) 3.5.3 刪除數(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 刪除學(xué)號(hào)為200215128的學(xué)生記錄。 DELETE FROM Student WHERE Sno= 200215128 ；2. 刪除多個(gè)元

12、組的值刪除多個(gè)元組的值例9 刪除所有的學(xué)生選課記錄。 DELETE FROM SC；3. 帶子查詢的刪除語句帶子查詢的刪除語句例10 刪除計(jì)算機(jī)科學(xué)系所有學(xué)生的選課記錄。 DELETE FROM SC WHERE CS= (SELETE Sdept FROM Student WHERE Student.Sno=SC.Sno)；第三章第三章 關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1 SQL概述概述3.2 學(xué)生學(xué)生-課程數(shù)據(jù)庫課程數(shù)據(jù)庫3.3 數(shù)據(jù)定義數(shù)據(jù)定義3.4 數(shù)據(jù)查詢數(shù)據(jù)查詢3.5 數(shù)據(jù)更新數(shù)據(jù)更新3.6 視圖視圖3.7 小結(jié)小結(jié)3.6 視視 圖圖視圖的特點(diǎn)v 虛表，是從一個(gè)或幾個(gè)

13、基本表（或視圖）導(dǎo)出的表v 只存放視圖的定義，不存放視圖對(duì)應(yīng)的數(shù)據(jù)v 基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變3.6 視視 圖圖基于視圖的操作v 查詢v 刪除v 受限更新v 定義基于該視圖的新視圖3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用3.6.1 定義視圖定義視圖v建立視圖v刪除視圖一、建立視圖一、建立視圖v語句格式 CREATE VIEW ( ，) AS WITH CHECK OPTION；v組成視圖的屬性列名：全部省略或全部指定v子查詢不允許含有ORDER BY子句和DISTINCT短

14、語 建立視圖（續(xù)）建立視圖（續(xù)）vRDBMS執(zhí)行CREATE VIEW語句時(shí)只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。v在對(duì)視圖查詢時(shí)，按視圖的定義從基本表中將數(shù)據(jù)查出。建立視圖（續(xù)）建立視圖（續(xù)） 例1 建立信息系學(xué)生的視圖。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；建立視圖（續(xù)）建立視圖（續(xù)）例2建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時(shí)仍需保證該視圖只有信息系的學(xué)生 。 CREATE VIEW IS_Student AS SELECT Sno，Snam

15、e，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION；建立視圖（續(xù)）建立視圖（續(xù)）對(duì)IS_Student視圖的更新操作：v 修改操作：自動(dòng)加上Sdept= IS的條件v 刪除操作：自動(dòng)加上Sdept= IS的條件v 插入操作：自動(dòng)檢查Sdept屬性值是否為IS 如果不是，則拒絕該插入操作 如果沒有提供Sdept屬性值，則自動(dòng)定義Sdept為IS建立視圖（續(xù)）建立視圖（續(xù)）v基于多個(gè)基表的視圖例3 建立信息系選修了1號(hào)課程的學(xué)生視圖。 CREATE VIEW IS_S1(Sno，Sname，Grade) AS SELECT Student.S

16、no，Sname，Grade FROM Student，SC WHERE Sdept= IS AND Student.Sno=SC.Sno AND SC.Cno= 1；建立視圖（續(xù)）建立視圖（續(xù)）v基于視圖的視圖例4 建立信息系選修了1號(hào)課程且成績(jī)?cè)?0分以上的學(xué)生的視圖。 CREATE VIEW IS_S2 AS SELECT Sno，Sname，Grade FROM IS_S1 WHERE Grade=90；建立視圖（續(xù)）建立視圖（續(xù)）v帶表達(dá)式的視圖例5 定義一個(gè)反映學(xué)生出生年份的視圖。 CREATE VIEW BT_S(Sno，Sname，Sbirth) AS SELECT Sno，S

17、name，2009-Sage FROM Student；建立視圖（續(xù)）建立視圖（續(xù)）v分組視圖例6 將學(xué)生的學(xué)號(hào)及他的平均成績(jī)定義為一個(gè)視圖 假設(shè)SC表中“成績(jī)”列Grade為數(shù)字型 CREAT VIEW S_G(Sno，Gavg) AS SELECT Sno，AVG(Grade) FROM SC GROUP BY Sno； 建立視圖（續(xù)）建立視圖（續(xù)）v不指定屬性列例7將Student表中所有女生記錄定義為一個(gè)視圖 CREATE VIEW F_Student(F_Sno，name，sex，age，dept) AS SELECT * FROM Student WHERE Ssex=女； 缺點(diǎn)：

18、 修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。 上面例子中，上面例子中，CREATE VIEW子句有些沒有寫上列名表，子句有些沒有寫上列名表，這樣視圖中的列將自動(dòng)來自子查詢中這樣視圖中的列將自動(dòng)來自子查詢中SELECT子句描述的列。子句描述的列。但有三種情況必須在但有三種情況必須在CREATE VIEW子句中寫上列名表：子句中寫上列名表： 某個(gè)目標(biāo)列不是單純的屬性名，而是集函數(shù)或列表某個(gè)目標(biāo)列不是單純的屬性名，而是集函數(shù)或列表達(dá)式；達(dá)式； 多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段；多表連接時(shí)選出了幾個(gè)同名列作為視圖的字段；

19、需要在視圖中為某個(gè)列定義新的更合適的名字。需要在視圖中為某個(gè)列定義新的更合適的名字。重命名屬性重命名屬性vCreate view student_cs(cs_sno,cs_sname) vasv select sno,snamev from studentv where sdept=cs二、刪除視圖二、刪除視圖v語句的格式：DROP VIEW ； 該語句從數(shù)據(jù)字典中刪除指定的視圖定義 如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級(jí)聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除 刪除基表時(shí)，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROP VIEW語句刪除 刪除視圖刪除視圖(續(xù)）續(xù)）例8

20、 刪除視圖BT_S： DROP VIEW BT_S； 刪除視圖IS_S1：DROP VIEW IS_S1；拒絕執(zhí)行級(jí)聯(lián)刪除： DROP VIEW IS_S1 CASCADE; 3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用3.6.2 查詢視圖查詢視圖v 用戶角度：查詢視圖與查詢基本表相同v RDBMS實(shí)現(xiàn)視圖查詢的方法 視圖消解法（View Resolution） 進(jìn)行有效性檢查 轉(zhuǎn)換成等價(jià)的對(duì)基本表的查詢 執(zhí)行修正后的查詢查詢視圖（續(xù)）查詢視圖（續(xù)）例9 在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。 S

21、ELECT Sno，Sage FROM IS_Student WHERE Sage20；IS_Student視圖的定義 (參見視圖定義例1)查詢視圖（續(xù)查詢視圖（續(xù)）視圖消解轉(zhuǎn)換后的查詢語句為： 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；查詢轉(zhuǎn)換查詢轉(zhuǎn)換錯(cuò)誤：SELECT Sno，AVG(Grade)FROM SCWHERE AVG(Grade)=90GR

22、OUP BY Sno；正確：SELECT Sno，AVG(Grade)FROM SCGROUP BY SnoHAVING AVG(Grade)=90；3.6 視視 圖圖3.6.1 定義視圖定義視圖3.6.2 查詢視圖查詢視圖3.6.3 更新視圖更新視圖3.6.4 視圖的作用視圖的作用更新視圖（續(xù)）更新視圖（續(xù)）例12 將信息系學(xué)生視圖IS_Student中學(xué)號(hào)200215122的學(xué)生姓名改為“劉辰”。UPDATE IS_StudentSET Sname= 劉辰WHERE Sno= 200215122 ；轉(zhuǎn)換后的語句：UPDATE StudentSET Sname= 劉辰WHERE Sno= 2

23、00215122 AND Sdept= IS；更新視圖（續(xù)）更新視圖（續(xù)）例13 向信息系學(xué)生視圖IS_S中插入一個(gè)新的學(xué)生記錄：200215129，趙新，20歲INSERTINTO IS_StudentVALUES(95029，趙新，20)；轉(zhuǎn)換為對(duì)基本表的更新：INSERTINTO Student(Sno，Sname，Sage，Sdept)VALUES(200215129 ，趙新，20，IS )；更新視圖（續(xù)）更新視圖（續(xù)）例14刪除信息系學(xué)生視圖IS_Student中學(xué)號(hào)為200215129的記錄 DELETEFROM IS_StudentWHERE Sno= 200215129 ；轉(zhuǎn)換

24、為對(duì)基本表的更新：DELETEFROM StudentWHERE Sno= 200215129 AND Sdept= IS；更新視圖（續(xù)）更新視圖（續(xù)）v 更新視圖的限制：一些視圖是不可更新的，因?yàn)閷?duì)這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新例：視圖S_G為不可更新視圖。UPDATE S_GSET Gavg=90WHERE Sno= 200215121；這個(gè)對(duì)視圖的更新無法轉(zhuǎn)換成對(duì)基本表SC的更新 有些視圖是無法更新的，如前面定義的平均成有些視圖是無法更新的，如前面定義的平均成績(jī)視圖績(jī)視圖S_G,因?yàn)槠渲械囊驗(yàn)槠渲械腉avg是從是從SC中的若干行經(jīng)中的若干行經(jīng)分組求函數(shù)得到的分組

25、求函數(shù)得到的,對(duì)對(duì)Gavg的更新無法還原到的更新無法還原到SC中中去。去。 可以肯定，行列子集視圖是可以更新的，其余可以肯定，行列子集視圖是可以更新的，其余的視圖有些在理論上可以更新，有些則完全不可更的視圖有些在理論上可以更新，有些則完全不可更新。視圖的更新不同系統(tǒng)有不同限制新。視圖的更新不同系統(tǒng)有不同限制關(guān)于更新的限制：關(guān)于更新的限制： 在關(guān)系數(shù)據(jù)庫中，并不是所有的視圖都是可更新的，因?yàn)橛行┮晥D的更新不能唯一地有意義在關(guān)系數(shù)據(jù)庫中，并不是所有的視圖都是可更新的，因?yàn)橛行┮晥D的更新不能唯一地有意義地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新。地轉(zhuǎn)換成對(duì)相應(yīng)基本表的更新。1. 若視圖是由兩個(gè)以上基本表導(dǎo)出的，則此視圖