5SQL更新定義與安全控制

1、關(guān)系數(shù)據(jù)庫標準語言SQL數(shù)據(jù)更新數(shù)據(jù)定義數(shù)據(jù)控制嵌入式SQL新增數(shù)據(jù)新增一門課程： ( 編號：10，名稱：運籌學(xué) ，學(xué)分：3)“is系所有同學(xué)選修10號課程添加 數(shù) 據(jù) 命令 插入數(shù)據(jù)：向指定表中插入一條或多條元組；INSERT INTO ( ) VALUES ( ) | 子查詢;INTO子句屬性列的順序可與表定義中的順序不一致可以只指定局部屬性列必須包含主屬性如果不指定屬性列：表示要插入的是一條完整的元組，且屬性列順序與表定義中的順序一致不建議使用VALUES子句：用于插入知道具體屬性值的單條元組；子查詢：用于插入從數(shù)據(jù)庫中查詢得到的多條元組；VALUES子句提供的值子查詢SELECT子句的

2、目標列必須與INTO子句匹配：值的個數(shù)值的類型添加數(shù)據(jù)例題續(xù)現(xiàn)有一個新建的學(xué)生平均成績表stu_avg(空表，用于存放每個學(xué)生的學(xué)號和平均成績。 Sno CHAR(8) , Avg_grade number(5,1)請向表中插入數(shù)據(jù)： INSERT INTO stu_avg(Sno，Avg_grade) SELECT sno，AVG(grade) FROM Sc GROUP BY Sno； 修改數(shù)據(jù)將所有學(xué)生的年齡增加1歲將10“課程名改為運籌學(xué)原理及應(yīng)用，同時學(xué)分改為2將“IS系全體學(xué)生的成績折算為總分值60分的成績 。 修改數(shù)據(jù)命令修改指定表中滿足WHERE子句條件的元組的指定列；UPDA

3、TE SET = ，= WHERE ；注意：如果條件中涉及其他表的屬性，應(yīng)該在WHERE中用子查詢。刪除數(shù)據(jù)刪除年齡大于30歲的學(xué)生根本信息。 刪除“IS系的學(xué)生的選課記錄。實際系統(tǒng)中刪除信息不一定是真正的刪除 刪除數(shù)據(jù)命令刪除指定表中的元組 DELETE FROM WHERE ；如果無WHERE子句，那么表示修改或刪除表中的所有元組；如果WHERE條件中涉及其他表中的屬性，使用子查詢;更新數(shù)據(jù)與完整性約束DBMS在執(zhí)行數(shù)據(jù)更新語句時會檢查操作是否破壞表上已定義的完整性規(guī)那么：實體完整性：主屬性不能插入空值；主碼取值必須唯一。參照完整性：更新操作不能違背參照完整性；在刪除某一外碼引用的候選碼值

4、所在的行時，可能存在以下三種處理情況：不允許刪除級聯(lián)刪除設(shè)為空值用戶定義的完整性對于有NOT NULL約束的屬性列是否提供了非空值 對于有UNIQUE約束的屬性列是否提供了非重復(fù)值 對于有值域約束的屬性列所提供的屬性值是否在值域范圍內(nèi)練習(xí)將成績在55到60之間的學(xué)生的成績增加5分將選修了學(xué)分大于3的課程的成績增加5% 刪除成績?yōu)榭盏倪x修記錄刪除學(xué)分為0的課程對應(yīng)的選修信息將“200215010，“張明，“20，“CS 插入STUDENT表的SNO，SNAME，SAGE，SDEPT200215010同學(xué)選修了所有課程數(shù) 據(jù) 定 義 定義根本表CREATE TABLE ， ， ；表名：所要定義的根

5、本表的名字可以是模式名.表名列名：組成該表的各個屬性列名列級完整性約束條件：涉及相應(yīng)屬性列的完整性約束條件表級完整性約束條件：涉及一個或多個屬性列的完整性約束條件 SQL2標準中的常用數(shù)據(jù)類型字符型char(n) 固定長度字符串varchar(n) 變長長度字符串 ORACLE: VARCHAR2(N)數(shù)值型Int 整型 NUMBER(P,D)smallint. 短整型(半字長整數(shù))Real 雙精度浮點型float(n) 單精度浮點型，n為有效數(shù)位numeric(p,d)/decimal(p,d 定點小數(shù)，p為有效數(shù)位，d為小數(shù)位數(shù)。日期時間型date. 日期型4位年。一般格式： yyyy-m

6、m-dd 例如： 2004-7-27time. 時間型。一般格式： hh-mi-ss 例如： 09:00:30.75timestamp: 時間戳。例如：. 2001-7-27 09:00:30.75Big object type(in oracle)Blob;clob,nclob;BfileRowid:Rowid,urowid數(shù)據(jù)類型確實定？常用完整性約束條件常用列級完整性約束條件NOT NULLPRIMARY KEY 僅適用于單屬性主碼UNIQUECHECK 定義域常用表級完整性約束條件PRIMARY KEY(主碼)CHECK (條件)FOREIGN KEY (外碼) REFERENCES

7、表名與外碼對應(yīng)的主碼UNIQUE屬性數(shù)據(jù)約束與數(shù)據(jù)操作約束完整性約束處理定義定義子句命名：constraint 名稱 定義子句檢查數(shù)據(jù)更新語句增、刪、改執(zhí)行后 或事務(wù)提交時違約處理拒絕操作NO ACTION)級聯(lián)操作CASCADE)設(shè)置為空SET NULL缺省值的設(shè)置列定義后跟： DEFAULT 值例如：create table sc(grade number(3,1) default 0);定義根本表例題 建立一個“學(xué)生表Student，它由學(xué)號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。 其中學(xué)號為主碼，并且姓名取值非空。 增加約束： Ssex取值為0

8、表示男或1表示女; 設(shè)置缺省值為0 CREATE TABLE Student (Sno CHAR(5) PRIMARY KEY， Sname CHAR(20) constraint s_name NOT NULL ， Ssex CHAR(1) ， Sage INT， Sdept CHAR(15)； Ssex CHAR(1) default 0 check (ssex=0 or ssex=1)例題 續(xù)建立一個“學(xué)生選課表SC，它由學(xué)號Sno、課程號Cno，修課成績Grade組成，其中(Sno, Cno)為主碼。 Grade 取值0-100，Sno為外碼，刪除學(xué)生根本信息時，同時刪除其選修信息。C

9、REATE TABLE SC ( Sno CHAR(5) , Cno CHAR(3) , Grade int check(grade =0 and grade =1 AND CREDIT =4);練習(xí)： 建立一個“課程表course，它由課程號Cno，課程名cname、先修課pcno、學(xué)分credit組成，其中Cno為主碼，cname 非空，學(xué)分為14，隱含為2。根據(jù)查詢結(jié)果創(chuàng)立新表CREATE TABLE 表名(屬性列表) AS 子查詢；e.g create table s as select * from student where sdept=is ;修改根本表ALTER TABLE A

10、DD 或 DROP column | constraint MODIFY ；：要修改的根本表ADD子句：增加新列和新的完整性約束條件DROP子句：刪除指定的列或完整性約束條件很多數(shù)據(jù)庫系統(tǒng)不支持刪除列MODIFY子句：用于修改列的定義約束的重命名與禁止和激活修改根本表例題 向Student表增加“SCOME入學(xué)時間列，其數(shù)據(jù)類型為日期型。 不管根本表中原來是否已有數(shù)據(jù)，新增加的列一律為空值 對SC增加CNO的參照完整性約束 將學(xué)生姓名的長度減少為10個字符注：修改原有的列定義有可能會破壞已有數(shù)據(jù)刪除屬性列很多數(shù)據(jù)庫系統(tǒng)不支持該功能:刪除根本表 DROP TABLE ; 系統(tǒng)從數(shù)據(jù)字典中刪去有關(guān)

11、該根本表的描述刪除Student表 DROP TABLEStudent ;索引 建立索引是加快查詢速度的有效手段索引分類：聚簇索引Clustered Index 非聚簇索引：唯一索引、B樹索引等建立索引DBA或表的屬主即建立表的人根據(jù)需要建立很多DBMS自動建立具有以下約束的列上的索引 PRIMARY KEY UNIQUE維護和使用索引： DBMS自動完成建立和刪除索引 CREATE UNIQUE CLUSTERED INDEX ON ( , )；索引可以建立在該表的一列或多列上，各列名之間用逗號分隔次序指索引值的排列次序，升序：ASC缺省值，降序：DESC。DROP INDEX ；刪除索引時

12、，系統(tǒng)會從數(shù)據(jù)字典中刪去有關(guān)該索引的描述ALTER index rebuild;定義練習(xí)建立以下表和索引：1、書BOOK：書號 BNO主碼 、書名BNAME非空、出版社PUBLISHER2、讀者READER：學(xué)號SNO 主碼 、姓名SNAME 非空、班級SCLASS、可借書數(shù)量SCOUNT取值為1-10，缺省為33、借書BORROW：學(xué)號SNO 、書號 BNO、借閱日期BDATE 其中，學(xué)號SNO 、書號 BNO是主碼 學(xué)號SNO 、書號 BNO 是兩個外碼4、為表BOOK按出版社升序建立索引 IDX_BOOK5、在讀者READER表中增加一個屬性：性別SGENDER，取值為F女或M男6、將索

13、引IDX_BOOK更名為IDX_PUBLISHER7、刪除索引IDX_PUBLISHER視 圖視圖的特點虛表，是從一個或幾個根本表或視圖導(dǎo)出的表數(shù)據(jù)庫中只存放視圖的定義，不會出現(xiàn)數(shù)據(jù)冗余基表中的數(shù)據(jù)發(fā)生變化，從視圖中查詢出的數(shù)據(jù)也隨之改變什么時候需要建立和使用視圖？建立視圖語句格式 CREATE VIEW ( ) AS WITH CHECK OPTION；省略視圖的屬性列：視圖由子查詢中SELECT目標列中的諸屬性組成以下情況下需要明確指定視圖的所有屬性列：某個目標列是集函數(shù)或列表達式多表非自然連接時選出了同名屬性需要在視圖中為某個列啟用新的更適宜的名字WITH CHECK OPTION 帶有

14、 WITH CHECK OPTION選項，表示通過視圖進行增刪改操作時，不能破壞視圖定義中子查詢的條件表達式DBMS創(chuàng)立視圖時只是把視圖的定義存入數(shù)據(jù)字典，并不執(zhí)行其中的SELECT語句。視圖定義例題建立IS系學(xué)生的視圖IS_Student 。 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS；或 CREATE VIEW IS_Student AS SELECT Sno，Sname，Sage FROM Student WHERE Sdept= IS WITH CHECK OPTION ；對修

15、改操作、刪除操作，DBMS自動加上Sdept= IS的條件在ORACLE中，不能執(zhí)行插入操作，因為該視圖中不存在SDEPT屬性，不能滿足建立視圖時的條件視圖定義例題續(xù)建立學(xué)生平均成績的視圖Stu_avg，包括學(xué)號、姓名、平均成績 。 CREATE VIEW stu_avg(sno,sname,avg_grade) AS SELECT s.Sno，Sname，avg(Grade) FROM student s,sc WHERE s.sno=sc.sno GROUP BY S.SNO, SNAME；行列子集視圖從單個根本表導(dǎo)出保存了根本表的主碼只是去掉了根本表的某些行和某些列行列子集視圖是可以更新

16、的視圖刪除視圖DROP VIEW ；該語句從數(shù)據(jù)字典中刪除指定的視圖定義由該視圖導(dǎo)出的其他視圖定義仍在數(shù)據(jù)字典中，但已不能使用，必須顯式刪除刪除基表時，由該基表導(dǎo)出的所有視圖定義都要顯式刪除例如：刪除視圖IS_S1 DROP VIEW IS_S1； 查詢和更新視圖從用戶角度：查詢和更新視圖與根本表相同，對視圖的查詢和更新最終轉(zhuǎn)換為對根本表的操作。定義視圖時如果使用了WITH CHECK OPTION子句，DBMS在更新視圖時會進行條件檢查一些視圖是不可更新的，因為對這些視圖的更新不能轉(zhuǎn)換成對相應(yīng)根本表的更新，一般只允許對行列子集視圖進行更新視圖的作用視圖能夠簡化用戶的操作用戶可以直接對視圖進行

17、操作，不用對多張根本表進行復(fù)雜的查詢；視圖使用戶能以多種角度看待同一數(shù)據(jù)例如：student表中的sage，通過建立不同的視圖對不同用戶可以得到不同的信息：年齡、出生年視圖對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨立性例：將學(xué)生關(guān)系Student(Sno，Sname，Ssex，Sage，Sdept) “垂直地分成兩個根本表： SX(Sno，Sname，Sage) SY(Sno，Ssex，Sdept)通過建立視圖維持原有的對student表的操作視圖能夠?qū)C密數(shù)據(jù)提供平安保護用戶只能看到自己需要的數(shù)據(jù)，對數(shù)據(jù)的更新可以通過with check option 進行限制六、 數(shù)據(jù)控制SQL語言提供了數(shù)據(jù)控制

18、功能，能夠在一定程度上保證數(shù)據(jù)的:完整性定以及本表時可以指定完整性約束：主碼與外碼取值唯一取值非空其他條件的約束平安性控制用戶只能存取授權(quán)范圍內(nèi)的數(shù)據(jù)，主要包括授權(quán)和收回權(quán)限并發(fā)控制及恢復(fù)提供事務(wù)開始、結(jié)束、提交、回滾、重做等概念和操作DB 平安性用戶身份識別User id & password存取控制Discretionary access control (DAC自主存取控制)Authorization授權(quán) & 權(quán)限檢查 Mandatory access control(MAC強制存取控制) 密級 對數(shù)據(jù) & 許可證級別對用戶: top secret絕密, secret機密, confid

19、ential可信, public公開視圖 審計加密權(quán)限管理授予權(quán)限：GRANT ,. ON TO WITH GRANT OPTION;回收權(quán)限：REVOKE ,.ON FROM ;WITH GRANT OPTION 表示被授權(quán)用戶可以將獲得的權(quán)限再授予其他用戶；用戶名PUBLIC表示全體用戶權(quán)限：常用對象常用對象權(quán)限基本表、視圖select、update(屬性名)、insert、delete 、all系統(tǒng)權(quán)限create table、alter table、drop table、create view、drop view、權(quán)限管理例題把對表SC的查詢權(quán)限授予所有用戶GRANT SELECT O

20、N SC TO PUBLIC;把查詢Student表和修改學(xué)生年齡的權(quán)限授給用戶u2和u3GRANT UPDATE(Sage), SELECT ON Student TO u2,u3;把建立表的權(quán)限授予用戶U8GRANT CREATE TABLE TO U8;把對表SC的INSERT權(quán)限授予用戶U5 ，并允許他再將此權(quán)限授予其他用戶 GRANT INSERT ON SC TO U5 WITH GRANT OPTION; 收回用戶U4修改student表sage屬性的權(quán)限REVOKE UPDATE(Sage) ON Student FROM U4;收回所有用戶對表SC的查詢權(quán)限REVOKE SE

21、LECT ON SC FROM PUBLIC;練習(xí)建立成績在90分以上的學(xué)生選修信息的視圖將對表SC的查詢、刪除數(shù)據(jù)權(quán)限授予用戶st01將創(chuàng)立表的權(quán)限授予用戶st01； 創(chuàng)立根本表AVGGRADE,屬性包括SNO,GRADE ；并將每個學(xué)生的平均成績插入AVGGRADE表角色什么是角色？有何作用？create role teacher create role managergrant select on student to teacher grant update (grade) on sc to teachergrant all on sc to managergrant teacher

22、to manager七、 嵌 入 式 SQLSQL語言提供了兩種不同的使用方式：交互式 、嵌入式嵌入式SQL的一般形式區(qū)分SQL語句與主語言語句：需要為SQL語句指定前綴：EXEC SQL 結(jié)束標志：隨主語言的不同而不同，常用主語言，例如c、powerbuilder等都以“;結(jié)束 例：EXEC SQL DROP TABLE Student;主語言與SQL之間的通信1. SQL通信區(qū) SQL Communication Area ，SQLCA向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息，使主語言能夠據(jù)此控制程序流程2. 主語言向SQL語句傳遞數(shù)據(jù)即參數(shù)通過主變量 3. SQL語句向主語言傳遞數(shù)據(jù)即查詢結(jié)

23、果 1SQL語句查詢結(jié)果為單個元組時，使用主變量2 SQL語句查詢結(jié)果為多個元組時，使用游標，解決集合性操作語言與過程性操作語言的不匹配 SQLCA的使用方法定義SQLCA EXEC SQL INCLUDE SQLCA使用SQLCA SQLCA中有一個變量SQLCODE，用來存放最近執(zhí)行的SQL語句的返回代碼；如果SQLCODE等于預(yù)定義的常量SUCCESS或0，那么表示SQL語句成功，否那么表示出錯 應(yīng)用程序每執(zhí)行完一條SQL 語句之后都應(yīng)該測試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理例如：以主語言C語言為例。EXEC SQL INCLUDE SQLCA; /*說明性語

24、句*/EXEC SQL GRANT SELECT ON Student TO U1;if (sqlca.sqlcode = 0) printf(“the sql statement succeedn);elseprintf(“the sql statement error n);exec sql drop table course;2. 主變量在SQL語句中使用的主語言變量簡稱為主變量Host Variable主變量的類型 輸入主變量：由主語言賦值，在SQL語句中引用的變量 。用于主語言向SQL語句傳遞參數(shù)輸出主變量：由SQL語句賦值，在主語言中引用的變量。用于SQL語句向主語言傳遞結(jié)果.一般

25、出現(xiàn)在SQL語句的SELECT 子句中.注意：一個主變量有可能既是輸入主變量又是輸出主變量主變量的使用1) 聲明主變量 exec sql BEGIN DECLARE SECTION . (主變量定義) exec sql END DECLARE SECTION 2) 使用主變量 為了與數(shù)據(jù)庫對象名特別是屬性名相區(qū)別，SQL語句中的主變量名前要加冒號:作為標志；例如：SELECT 屬性1，屬性2， into :主變量1，:主變量2,主語言語句中可以直接引用主變量，不必加冒號 主變量的使用例如：在c語言中使用sql：exec sql include sqlca; /*說明性語句*/exec sql

26、begin declare section; /*說明性語句，定義主變量*/char v_sno8;char v_cno3char v_sname20;float v_grade;exec sql end declare section; v_cno=1; /*主語言中為主變量賦值*/ v_sno=95001;exec sql select sname ,grade into :v_sname，:v_grade /*查詢語句*/ from student,sc where student.sno=sc.sno and sc.sno=:v_sno and cno=:v_cno;if (sqlca

27、.sqlcode !=0) printf(“sql errorn); return；printf(“the students name is: %s,score is: %5.2fn,v_sname,v_grade); 3.游標游標是系統(tǒng)為用戶開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果，用于實現(xiàn)將sql查詢語句的多元組結(jié)果數(shù)據(jù)傳給主語言。 每個游標區(qū)都有一個名字 用戶可以從游標中逐條獲取記錄，并賦給主變量，交由主語言進一步處理使用游標的步驟1. 說明游標：開辟緩沖區(qū)EXEC SQL DECLARE CURSOR FOR ; 2. 翻開游標：EXEC SQL OPEN ;實際上是執(zhí)行相應(yīng)的SELECT語句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中 ，游標指針指向查詢結(jié)果集中第一條記錄之前。 3. 移動游標指針，取當前記錄。 EXEC SQL FETCH INTO ,.; FETCH語句通常用在一個循環(huán)結(jié)構(gòu)中，逐條取出結(jié)果集中的元組或稱記錄進行處理4. 關(guān)閉游標：釋放結(jié)果集占用的緩沖區(qū)及其他資源EXEC SQL CLOSE ;游標舉例EXEC