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

2024/1/12數(shù)據(jù)庫技術(shù)與應(yīng)用DatabaseTechnologyandApplication2013年4月第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制3.7小結(jié)2024/1/123.4數(shù)據(jù)更新

3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)2024/1/123.4.1插入數(shù)據(jù)兩種插入數(shù)據(jù)方式1.插入元組2.插入子查詢結(jié)果可以一次插入多個元組2024/1/12一、插入元組語句格式

INSERT INTO<表名>[(<屬性列1>[，<屬性列2>…)] VALUES(<常量1>[，<常量2>]…)功能將新元組插入指定表中2024/1/12插入元組（續(xù)）

INTO子句指定要插入數(shù)據(jù)的表名及屬性列屬性列的順序可與表定義中的順序不一致沒有指定屬性列：表示要插入的是一條完整的元組，且屬性列屬性與表定義中的順序一致指定部分屬性列：插入的元組在其余屬性列上取空值

VALUES子句提供的值必須與INTO子句匹配值的個數(shù)值的類型2024/1/12插入元組（續(xù)）［例1］將一個新學(xué)生元組（學(xué)號：0903128；姓名：陳冬；性別：男；所在系：IS；年齡：18歲）插入到Student表中。INSERTINTOStudent(Sno,Sname,Ssex,Sdept,Sage)VALUES('0903128'，'陳冬'，'男'，'IS'，18);2024/1/12[例2］將學(xué)生張成民的信息插入到Student表中INSERTINTOStudentVALUES(‘0903126’,‘張成民’,‘男’，18，'CS');插入元組（續(xù)）［例3］插入一條選課記錄('0903128'，'1')。

INSERTINTOSC(Sno，Cno)VALUES(‘0903128’，‘1’)；

RDBMS將在新插入記錄的Grade列上自動地賦空值。

或者：

INSERTINTOSCVALUES('0903128'，'1'，NULL)；2024/1/12二、插入子查詢結(jié)果語句格式

INSERTINTO

<表名>[(<屬性列1>[，<屬性列2>…)]]

子查詢；功能將子查詢結(jié)果插入指定表中2024/1/12插入子查詢結(jié)果（續(xù)）

INTO子句(與插入元組類似)

子查詢SELECT子句目標(biāo)列必須與INTO子句匹配值的個數(shù)值的類型2024/1/12插入子查詢結(jié)果（續(xù)）[例4]對每一個系，求學(xué)生的平均年齡，并把結(jié)果存入數(shù)據(jù)庫。第一步：建表

CREATETABLEDept_age(SdeptCHAR(15) /*系名*/

Avg_ageSMALLINT)；/*學(xué)生平均年齡*/

2024/1/12插入子查詢結(jié)果（續(xù)）第二步：插入數(shù)據(jù)

INSERTINTODept_age(Sdept，Avg_age)SELECTSdept，AVG(Sage)FROMStudentGROUPBYSdept；2024/1/12插入子查詢結(jié)果（續(xù)）RDBMS在執(zhí)行插入語句時會檢查所插元組是否破壞表上已定義的完整性規(guī)則:實體完整性參照完整性用戶定義的完整性NOTNULL約束UNIQUE約束值域約束2024/1/123.4數(shù)據(jù)更新

3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)2024/1/123.4.2修改數(shù)據(jù)語句格式

UPDATE<表名>

SET<列名>=<表達(dá)式>[，<列名>=<表達(dá)式>]…[WHERE<條件>]；功能修改指定表中滿足WHERE子句條件的元組2024/1/12修改數(shù)據(jù)（續(xù)）SET子句指定修改方式（3種）修改某一個元組的值修改多個元組的值帶子查詢的修改語句要修改的列修改后取值WHERE子句指定要修改的元組缺省表示要修改表中的所有元組2024/1/121.修改某一個元組的值[例5]將學(xué)生0903121的年齡改為22歲

UPDATEStudent

SETSage=22WHERESno='0903121'；

2024/1/122.修改多個元組的值[例6]將所有學(xué)生的年齡增加1歲

UPDATEStudent

SETSage=Sage+1；2024/1/123.帶子查詢的修改語句[例7]將計算機(jī)科學(xué)系全體學(xué)生的成績置零。

UPDATESCSETGrade=0WHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；或：UPDATESCSETGrade=0WHERESC.SnoIN(SELECTSnoFROMStudentWHERESdept=‘CS’)；2024/1/12修改數(shù)據(jù)（續(xù)）RDBMS在執(zhí)行修改語句時會檢查修改操作是否破壞表上已定義的完整性規(guī)則實體完整性主碼不允許修改用戶定義的完整性

NOTNULL約束

UNIQUE約束值域約束2024/1/123.4數(shù)據(jù)更新

3.4.1插入數(shù)據(jù)3.4.2修改數(shù)據(jù)3.4.3刪除數(shù)據(jù)

2024/1/123.4.3刪除數(shù)據(jù)語句格式

DELETEFROM<表名>[WHERE<條件>]；功能刪除指定表中滿足WHERE子句條件的元組WHERE子句指定要刪除的元組缺省表示要刪除表中的全部元組，表的定義仍在字典中2024/1/12刪除數(shù)據(jù)（續(xù)）三種刪除方式:1.刪除某一個元組的值2.刪除多個元組的值3.帶子查詢的刪除語句2024/1/121.刪除某一個元組的值[例8]刪除學(xué)號為0903128的學(xué)生記錄。

DELETEFROMStudentWHERESno=‘0903128'；2024/1/122.刪除多個元組的值[例9]刪除所有的學(xué)生選課記錄。

DELETEFROMSC；2024/1/123.帶子查詢的刪除語句[例10]刪除計算機(jī)科學(xué)系所有學(xué)生的選課記錄。

DELETEFROMSCWHERE'CS'=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno);2024/1/12刪除數(shù)據(jù)(續(xù))DBMS在執(zhí)行刪除語句時會檢查所刪除元組是否破壞表上已定義的完整性規(guī)則，參照完整性，若破壞，則不允許刪除級聯(lián)刪除2024/1/12第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制3.7小結(jié)2024/1/123.5視圖視圖的特點(diǎn)虛表，是從一個或幾個基本表（或視圖）導(dǎo)出的表數(shù)據(jù)庫中只存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù)基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變2024/1/123.5視圖基于視圖的操作

查詢刪除受限更新定義基于該視圖的新視圖2024/1/123.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2024/1/123.5.1定義視圖建立視圖刪除視圖2024/1/12一、建立視圖語句格式

CREATEVIEW

<視圖名>[(<列名>[，<列名>]…)]

AS<子查詢>[WITHCHECKOPTION]；組成視圖的屬性列名：全部省略或全部指定子查詢不允許含有ORDERBY子句和DISTINCT短語2024/1/12組成視圖的屬性列名屬性列名可全部省略或全部指定全部省略:

由子查詢中SELECT目標(biāo)列中的諸字段組成全部指定所有列名的情況:(1)某個目標(biāo)列是集函數(shù)或列表達(dá)式(2)多表連接時選出了幾個同名列作為視圖的字段(3)需要在視圖中為某個列啟用新的更合適的名字2024/1/12建立視圖（續(xù)）RDBMS執(zhí)行CREATEVIEW語句時只是把視圖定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。在對視圖查詢時，按視圖的定義從基本表中將數(shù)據(jù)查出。2024/1/12建立視圖（續(xù)）

[例1]建立信息系學(xué)生的視圖。

CREATEVIEWIS_StudentAS

SELECTSno，Sname，SageFROMStudentWHERESdept='IS'；2024/1/12建立視圖（續(xù)）[例2]建立信息系學(xué)生的視圖，并要求進(jìn)行修改和插入操作時仍需保證該視圖只有信息系的學(xué)生。

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'

WITHCHECKOPTION；2024/1/12建立視圖（續(xù)）對IS_Student視圖的更新操作：修改操作：自動加上Sdept='IS'的條件刪除操作：自動加上Sdept='IS'的條件插入操作：自動檢查Sdept屬性值是否為'IS'如果不是，則拒絕該插入操作如果沒有提供Sdept屬性值，則自動定義Sdept為'IS'2024/1/12建立視圖（續(xù)）基于多個基表的視圖[例3]建立信息系選修了1號課程的學(xué)生視圖。

CREATEVIEWIS_S1(Sno，Sname，Grade)AS

SELECTStudent.Sno，Sname，GradeFROMStudent，SCWHERESdept='IS'AND

Student.Sno=SC.SnoAND

SC.Cno='1'；2024/1/12建立視圖（續(xù)）基于視圖的視圖

[例4]建立信息系選修了1號課程且成績在90分以上的學(xué)生的視圖。

CREATEVIEWIS_S2ASSELECTSno，Sname，GradeFROMIS_S1WHEREGrade>=90；2024/1/12建立視圖（續(xù)）帶表達(dá)式的視圖[例5]定義一個反映學(xué)生出生年份的視圖。

CREATEVIEWBT_S(Sno，Sname，Sbirth)ASSELECTSno，Sname，2012-SageFROMStudent；2024/1/12建立視圖（續(xù)）分組視圖[例6]將學(xué)生的學(xué)號及他的平均成績定義為一個視圖（假設(shè)SC表中“成績”列Grade為數(shù)字型）。

CREATVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；2024/1/12建立視圖（續(xù)）不指定屬性列[例7]將Student表中所有女生記錄定義為一個視圖CREATEVIEWF_Student(F_Sno,name,sex,age,dept)ASSELECT*FROMStudentWHERESsex=‘女’；缺點(diǎn)： 以SELECT*方式創(chuàng)建的視圖可擴(kuò)充性差，應(yīng)盡可能避免。原因： 修改基表Student的結(jié)構(gòu)后，Student表與F_Student視圖的映象關(guān)系被破壞，導(dǎo)致該視圖不能正確工作。2024/1/12建立視圖（續(xù)）CREATEVIEWF_Student2(stdnum,name,sex,age,dept)ASSELECTSno，Sname，Ssex，Sage，SdeptFROMStudentWHERESsex='女'；為基表Student增加屬性列不會破壞Student表與F_Student2視圖的映象關(guān)系。2024/1/12二、刪除視圖語句的格式：

DROPVIEW<視圖名>；該語句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除刪除基表時，由該基表導(dǎo)出的所有視圖定義都必須顯式地使用DROPVIEW語句刪除2024/1/12刪除視圖(續(xù)）［例8］刪除視圖BT_S： DROPVIEWBT_S；

刪除視圖IS_S1： DROPVIEWIS_S1；級聯(lián)刪除：

DROPVIEWIS_S1CASCADE;

2024/1/123.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2024/1/123.5.2查詢視圖用戶角度：查詢視圖與查詢基本表相同RDBMS實現(xiàn)視圖查詢的方法通過視圖消解法（ViewResolution）完成進(jìn)行有效性檢查，檢查所查詢的視圖是否存在若視圖存在，將其定義中的子查詢和用戶查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢執(zhí)行修正后的查詢2024/1/12查詢視圖（續(xù)）[例9]在信息系學(xué)生的視圖中找出年齡小于20歲的學(xué)生。

SELECTSno，SageFROMIS_StudentWHERESage<20；IS_Student視圖的定義

CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS‘；2024/1/12查詢視圖（續(xù)）視圖消解轉(zhuǎn)換后的查詢語句為：

SELECTSno，SageFROMStudentWHERESdept='IS'ANDSage<20；2024/1/12查詢視圖（續(xù)）[例10]查詢選修了1號課程的信息系學(xué)生(涉及視圖和基本表的查詢)SELECTIS_Student.Sno，SnameFROMIS_Student，SCWHEREIS_Student.Sno=SC.SnoANDSC.Cno='1'；2024/1/12

CREATEVIEWIS_StudentAS

SELECTSno，Sname，SageFROMStudentWHERESdept='IS'；查詢視圖（續(xù)）視圖消解法的局限有些情況下，視圖消解法不能生成正確查詢。采用視圖消解法的DBMS會限制這類查詢

2024/1/12查詢視圖（續(xù)）[例11]在S_G視圖中查詢平均成績在90分以上的學(xué)生學(xué)號和平均成績SELECT*FROMS_GWHEREGavg>=90；

S_G視圖的子查詢定義：

CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；2024/1/12查詢轉(zhuǎn)換將查詢語句與S_G視圖的定義相結(jié)合：SELECTSno，AVG(Grade)FROMSCWHEREAVG(Grade)>=90GROUPBYSno；因WHERE子句不可用聚集函數(shù)，這種轉(zhuǎn)換不合理，出現(xiàn)語法錯誤正確：SELECTSno，AVG(Grade)FROMSCGROUPBYSnoHAVINGAVG(Grade)>=90；2024/1/123.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2024/1/123.5.3更新視圖用戶角度：更新視圖與更新基本表相同DBMS實現(xiàn)視圖更新的方法視圖消解法，轉(zhuǎn)換為對基本表的操作指定WITHCHECKOPTION子句后

DBMS在更新視圖時會進(jìn)行檢查，防止用戶通過視圖對不屬于視圖范圍內(nèi)的基本表數(shù)據(jù)進(jìn)行更新2024/1/12更新視圖（續(xù)）[例12]將信息系學(xué)生視圖IS_Student中學(xué)號0903122的學(xué)生姓名改為“劉辰”。UPDATEIS_StudentSETSname='劉辰'WHERESno='0903122'；轉(zhuǎn)換后的語句：UPDATEStudentSETSname='劉辰'WHERESno='0903122'ANDSdept='IS'；2024/1/12更新視圖（續(xù)）[例13]向信息系學(xué)生視圖IS_Student中插入一個新的學(xué)生記錄：95029，趙新，20歲INSERTINTOIS_StudentVALUES(‘95029’，‘趙新’，20)；轉(zhuǎn)換為對基本表的更新：INSERTINTOStudent(Sno，Sname，Sage，Sdept)VALUES(‘95029'，'趙新'，20，'IS')；2024/1/12更新視圖（續(xù)）[例14]刪除信息系學(xué)生視圖IS_Student中學(xué)號為0903129的記錄DELETEFROMIS_StudentWHERESno='0903129'；轉(zhuǎn)換為對基本表的更新：DELETEFROMStudentWHERESno='0903129'ANDSdept='IS'；2024/1/12注：IS_Student視圖的定義CREATEVIEWIS_StudentASSELECTSno，Sname，SageFROMStudentWHERESdept='IS'WITHCHECKOPTION；更新視圖（續(xù)）更新視圖的限制：一些視圖是不可更新的，因為對這些視圖的更新不能唯一地有意義地轉(zhuǎn)換成對相應(yīng)基本表的更新例：視圖S_G為不可更新視圖。UPDATES_GSETGavg=90WHERESno=‘0903121’；這個對視圖的更新無法轉(zhuǎn)換成對基本表SC的更新2024/1/12注：視圖S_G的定義CREATEVIEWS_G(Sno，Gavg)ASSELECTSno，AVG(Grade)FROMSCGROUPBYSno；更新視圖（續(xù)）允許對行列子集視圖進(jìn)行更新從單個基本表導(dǎo)出，只是去掉了基本表的某些行和列，且保留了主碼的視圖。對其它類型視圖的更新不同數(shù)據(jù)庫系統(tǒng)有不同限制2024/1/123.5視圖3.5.1定義視圖3.5.2查詢視圖3.5.3更新視圖3.5.4視圖的作用2024/1/121.視圖能夠簡化用戶的操作

當(dāng)視圖中數(shù)據(jù)不是直接來自基本表時，定義視圖能夠簡化用戶的操作基于多張表連接形成的視圖基于復(fù)雜嵌套查詢的視圖2024/1/123.5.4視圖的作用2.視圖使用戶能以多種角度看待同一數(shù)據(jù)視圖機(jī)制能使不同用戶以不同方式看待同一數(shù)據(jù)，適應(yīng)數(shù)據(jù)庫共享的需要3.視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性例：假設(shè)數(shù)據(jù)庫邏輯結(jié)構(gòu)發(fā)生改變學(xué)生關(guān)系Student(Sno，Sname，Ssex，Sage，Sdept)

“垂直”地分成兩個基本表：

SX(Sno，Sname，Sage)

SY(Sno，Ssex，Sdept)2024/1/123.視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性(續(xù))通過建立一個視圖Student：CREATEVIEWStudent(Sno，Sname，Ssex，Sage，Sdept)ASSELECTSX.Sno，SX.Sname，SY.Ssex，SX.Sage，SY.SdeptFROMSX，SYWHERESX.Sno=SY.Sno； 使用戶的外模式保持不變，從而對原Student表的查詢程序不必修改。3.視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性(續(xù))視圖只能在一定程度上提供數(shù)據(jù)的邏輯獨(dú)立性由于對視圖的更新是有條件的，因此應(yīng)用程序中修改數(shù)據(jù)的語句可能仍會因基本表結(jié)構(gòu)的改變而改變。2024/1/124.視圖能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)對不同用戶定義不同視圖，使每個用戶只能看到他有權(quán)看到的數(shù)據(jù)第三章關(guān)系數(shù)據(jù)庫標(biāo)準(zhǔn)語言SQL3.1SQL概述3.2數(shù)據(jù)定義3.3查詢3.4數(shù)據(jù)更新3.5視圖3.6數(shù)據(jù)控制3.7小結(jié)2024/1/123.6數(shù)據(jù)控制概述3.6.1授權(quán)3.6.2收回權(quán)限小結(jié)2024/1/12概述 數(shù)據(jù)庫系統(tǒng)的特點(diǎn)之一，由DBMS提供統(tǒng)一的數(shù)據(jù)控制功能，包括：安全性控制完整性控制并發(fā)控制數(shù)據(jù)庫的恢復(fù)2024/1/12

安全性安全性：保護(hù)數(shù)據(jù)庫，防止不合法的使用所造成的數(shù)據(jù)泄露和破壞。保證數(shù)據(jù)安全性的主要措施存取控制：控制用戶只能存取他有權(quán)存取的數(shù)據(jù)規(guī)定不同用戶對于不同數(shù)據(jù)對象所允許執(zhí)行的操作2024/1/12DBMS實現(xiàn)數(shù)據(jù)安全性保護(hù)的過程用戶或DBA把授權(quán)決定告知系統(tǒng)GRANT和REVOKEDBMS把授權(quán)的結(jié)果存入數(shù)據(jù)字典當(dāng)用戶提出操作請求時，DBMS根據(jù)授權(quán)定義進(jìn)行檢查，以決定是否執(zhí)行操作請求2024/1/123.6.1授權(quán)GRANT語句的一般格式：GRANT<權(quán)限>[,<權(quán)限>]...[ON<對象類型><對象名>]TO<用戶>[,<用戶>]...[WITHGRANTOPTION];GRANT功能：將指定操作對象的指定操作權(quán)限授予指定的用戶。2024/1/12(1)操作權(quán)限2024/1/12(2)用戶的權(quán)限建表（CREATETAB）的權(quán)限:屬于DBADBA授予-->普通用戶基本表或視圖的屬主擁有對該表或視圖的一切操作權(quán)限接受權(quán)限的用戶:一個或多個具體用戶PUBLIC（全體用戶）2024/1/12(4)WITHGRANTOPTION子句指定了WITHGRANTOPTION子句:

獲得某種權(quán)限的用戶還可以把這種權(quán)限再授予別的用戶。沒有指定WITHGRANTOPTION子句:獲得某種權(quán)限的用戶只能使用該權(quán)限，不能傳播該權(quán)限不允許循環(huán)授權(quán)2024/1/12例題例1把查詢Student表的權(quán)限授給用戶U1

GRANTSELECTONTABLEStudent

TO

U1;2024/1/12例題（續(xù)）例2把對Student表和Course表的全部權(quán)限授予用戶U2和U3GRANTALLPRIVILIGES

ONTABLEStudent,CourseTOU2,U3;2024/1/12例題（續(xù)）例3把對表SC的查詢權(quán)限授予所有用戶

GRANTSELECT

ONTABLESC

TO

PUBLIC;2024/1/12例題（續(xù)）例4把查詢Student表和修改學(xué)生學(xué)號的權(quán)限授給用戶U4

GRANTUPDATE(Sno),SELECT ONTABLEStudent TOU4;2024/1/12例題（續(xù)