1、1,第8章 SQL的系統(tǒng)特征,2,8.1 編程環(huán)境下的SQL,3,數(shù)據(jù)庫(kù)應(yīng)用系統(tǒng)為何需要其它編程語(yǔ)言,一個(gè)數(shù)據(jù)庫(kù)僅為一個(gè)特定環(huán)境提供共享的數(shù)據(jù)資源； 眾多用戶(hù)不可能在客戶(hù)端直接使用SQL語(yǔ)言來(lái)操作數(shù)據(jù)庫(kù)； 許多前臺(tái)編程語(yǔ)言可連接和操作數(shù)據(jù)庫(kù)。Java, PowerBuilder, Delphi, C/C+,VB,4,如何構(gòu)建一個(gè)相對(duì)完整的數(shù)據(jù)庫(kù)應(yīng)用環(huán)境,對(duì)于簡(jiǎn)單的應(yīng)用系統(tǒng)，使用單層C/S結(jié)構(gòu)； 對(duì)于復(fù)雜的應(yīng)用系統(tǒng)，使用多層C/S結(jié)構(gòu)。,Java程序1,JDBC,數(shù)據(jù)庫(kù),Java程序2,JDBC,PB程序1,ODBC,JDBC,Java類(lèi),Web服務(wù)器,瀏覽器,5,SQL語(yǔ)言和傳統(tǒng)編程語(yǔ)言之間最

2、大區(qū)別是什么,SQL語(yǔ)言是非過(guò)程性；一條語(yǔ)句作為一個(gè)單位作用于多個(gè)數(shù)據(jù)的集合；SQL的核心使用的是關(guān)系數(shù)據(jù)庫(kù)模型。 傳統(tǒng)語(yǔ)言是過(guò)程性；要處理一個(gè)數(shù)據(jù)集合需要多條語(yǔ)句的循環(huán)和判斷；傳統(tǒng)語(yǔ)言使用的數(shù)據(jù)模型有整型、實(shí)型、算術(shù)型、字符型、指針、記錄、數(shù)組等等。,6,SQL與宿主語(yǔ)言接口,在數(shù)據(jù)庫(kù)和宿主語(yǔ)言程序之間的信息轉(zhuǎn)移是通過(guò)宿主語(yǔ)言的變量來(lái)實(shí)現(xiàn)的，這種變量可以被SQL語(yǔ)句讀寫(xiě)。 在宿主語(yǔ)言中使用SQL語(yǔ)句時(shí)，SQL代碼語(yǔ)句必須緊跟在關(guān)鍵字EXEC SQL之后。系統(tǒng)將預(yù)處理這些語(yǔ)句，并用宿主語(yǔ)言中合適的函數(shù)來(lái)代替這些語(yǔ)句。 在SQL標(biāo)準(zhǔn)中，SQLSTATE這個(gè)特殊的變量用于連接宿主語(yǔ)言程序與SQL執(zhí)

3、行系統(tǒng)。 SQLSTATE是5個(gè)字符的數(shù)組類(lèi)型。每次調(diào)用SQL庫(kù)函數(shù)，向SQLSTATE變量存放一個(gè)代碼，該代碼表示調(diào)用過(guò)程中出現(xiàn)的問(wèn)題。,7,共享變量的聲明,共享變量的聲明加入到兩個(gè)嵌套SQL語(yǔ)句之間： EXEC SQL BEGIN DECLARE SECTION EXEC SQL END DECLARE SECTION 兩個(gè)語(yǔ)句之間稱(chēng)為DECLARE節(jié) 如： EXEC SQL BEGIN DECLARE SECTION char studioName50,studioAddr255; char SQLATATE6; EXEC SQL END DECLARE SECTION,8,使用共享變量

4、,共享變量可以在SQL語(yǔ)句中任何需要和允許常量的地方。共享變量在SQL語(yǔ)句中使用時(shí)，需加前綴冒號(hào)。 例： void getStudio() EXEC SQL BEGIN DECLARE SECTION char studioName50,studioAddr255; char SQLATATE6; EXEC SQL END DECLARE SECTION /*顯示輸入需求，將輸入的數(shù)據(jù)送到變量studioName和studioAddr中*/ EXEC SQL INSERT INTO Studio(name,address) VALUES(:studioName,:studioAddr) ,9,

5、單元組選擇語(yǔ)句,單元組選擇的形式類(lèi)似于普通的select-from-where語(yǔ)句，只是SELECT子句后緊跟著關(guān)鍵字INTO和一連串的共享變量。如果查詢(xún)的結(jié)果是個(gè)單一元組，則這個(gè)元組的組成部分將被賦予這些共享變量。如果結(jié)果沒(méi)有元組或多于一個(gè)元組，則不會(huì)分配給這些共享變量，同時(shí)一個(gè)相應(yīng)的錯(cuò)誤碼寫(xiě)入SQLSTATE變量中。,10,例子,Void printNetWorth() EXEC SQL BEGIN DECLARE SECTION char studioName50; int presNetWorth; char SQLATATE6; EXEC SQL END DECLARE SECTIO

6、N /* print request that syudio name be entered. read response into studioName*/ EXEC SQL SELECT NetWorth INTO :presNetWorth FROM Studio,MovieExec WHERE PresC#=cent# AND S=:studioName; /*check that SQLSTATE has all Os and if so.print the value of presNetWorth*/ ,11,關(guān)于游標(biāo)(光標(biāo)CURSOR),大多數(shù)編程語(yǔ)言操作數(shù)

7、據(jù)的方式是一次一條記錄。故需使用游標(biāo)。 建立和使用游標(biāo)需要以下步驟： 游標(biāo)說(shuō)明 DECLARE SCROLL CURSOR FOR SELECT 打開(kāi)游標(biāo) OPEN 推進(jìn) FETCH NEXT | PRIOR | FIRST | LAST | RELATIVE n | ABSOLUTE n FROM INTO 變量名, FETCH_STATUS=0 表示正常。 更新數(shù)據(jù) UPDATE|DELETE table WHERE CURRENT OF 關(guān)閉游標(biāo) CLOSE ,12,例子,Void worthRanges() int I,digits,count15; EXEC SQL BEGIN DE

8、CLARE SECTION int worth; char SQLATATE6; EXEC SQL END DECLARE SECTION EXEC SQL DECLARE execCursor CURSOR FOR MovieExec; EXEC SQL OPEN execCursor; for (i=1;i0) digits+; if (digits=14) countsdigits+; EXEC SQL CLOSE execCursor; for (i=1;i=15;i+) printf(“digits=%d: number of execs=%dn”,i,counti) ,13,游標(biāo)修

9、改,游標(biāo)掃描一個(gè)基本表的元組時(shí)，不僅可以讀和處理每個(gè)元組的值，而且可以更新或刪除元組。,14,例子,Void ChangeWorth () EXEC SQL BEGIN DECLARE SECTION int certNo,worth; char execName30,execAddr255,SQLATATE6; EXEC SQL END DECLARE SECTION EXEC SQL DECLARE execCursor CURSOR FOR MovieExec; EXEC SQL OPEN execCursor; while (1) EXEC SQL FETCH FROM execCur

10、sor INTO :execName, :execAddr,certNo,:worth; if (NO_MORE_TUPLES) break; if (worth1000) EXEC SQL DELETE MovieExec WHERE CURRENT OF execCursor else EXEC SQL UPDATE MovieExec SET netWorth=2*netWorth WHERE CURRENT OF execCursor; EXEC SQL CLOSE execCursor; ,15,8.2 存儲(chǔ)過(guò)程,16,存儲(chǔ)過(guò)程stored procedure,存儲(chǔ)過(guò)程stored

11、procedure是什么？ 簡(jiǎn)稱(chēng)過(guò)程procedure。在數(shù)據(jù)庫(kù)中存儲(chǔ)的一組SQL指令，有唯一命名，可供所有應(yīng)用程序調(diào)用。 過(guò)程的優(yōu)勢(shì)是什么？ 標(biāo)準(zhǔn)化；效率；安全性。 并非增強(qiáng)功能。,17,Transact-SQL,擴(kuò)充的SQL，包括變量定義，條件IFELSE或循環(huán)WHILE等流程控制語(yǔ)句，函數(shù)，及事務(wù)定義等語(yǔ)句。 例如： DECLARE Max_Amount INT SELECT Max_Amount=MAX(Amount) FROM Orderamount SELECT Max_Amount,18,如何創(chuàng)建過(guò)程,CREATE PROCEDURE (參數(shù) 類(lèi)型,) AS 子查詢(xún) 例如： Cr

12、eate procedure new_dept( id int, dname char(40), head char(10) AS insert into department(deptid,deptname,headerid) values (id,dname,head),19,如何調(diào)用過(guò)程,EXEC 參數(shù)=值 例如： EXEC new_dept id=3,dname=北京代理,head=null 或 EXEC new_dept 3, 北京代理, null,20,過(guò)程中如何返回計(jì)算結(jié)果,可用OUTPUT參量返回結(jié)果值。 例如：給定某種商品貨號(hào)prodid，計(jì)算其最高銷(xiāo)售單價(jià)maxp (OUT

13、PUT) 定義如下過(guò)程maxPrice： create procedure maxPrice( pid char(20), maxp numeric(12,2) OUTPUT) AS select maxp=max(singlecost) from salesitem where prodid = pid 下面語(yǔ)句在SQL中完成調(diào)用。 DECLARE maxp Numeric(12,2); EXEC maxPrice TV9475,maxp; select maxp;,21,例子,例如：給定某種商品貨號(hào)prodid，檢索銷(xiāo)售該商品的訂單號(hào)orderno及銷(xiāo)售單價(jià)singlecost。 定義如下

14、過(guò)程： create procedure orderbyprod( pid char(20) AS select orderno,singlecost from salesitem where prodid = pid 下面語(yǔ)句調(diào)用該過(guò)程并顯示結(jié)果 EXEC orderbyprod TV9475;,22,8.3 SQL環(huán)境,23,環(huán)境,SQL環(huán)境是一個(gè)框架，該框架下的數(shù)據(jù)可以存在，在數(shù)據(jù)上的SQL操作可以執(zhí)行。SQL環(huán)境被當(dāng)作某些DBMS的運(yùn)行環(huán)境。 SQL的標(biāo)準(zhǔn)結(jié)構(gòu) 模式：模式是表、視圖、斷言、觸發(fā)等所構(gòu)成的集合。 目錄：數(shù)據(jù)庫(kù)模式的集合，是用來(lái)支持唯一的、可訪問(wèn)術(shù)語(yǔ)的基本單元。每個(gè)目錄有一

15、個(gè)或多個(gè)模式，在一個(gè)目錄中模式名必須唯一。 簇：目錄的集合。每個(gè)用戶(hù)有一個(gè)相聯(lián)的簇：用戶(hù)可訪問(wèn)的所有目錄的集合。,24,模式,模式聲明的形式為： 保留字CREATE SCHEMA 模式名稱(chēng) 模式元素如基本表、視圖、斷言等聲明列表。 CREATE SCHEMA ,25,在SQL Server中建立數(shù)據(jù)庫(kù)模式,1)使用CREATE DATABASE語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù) 語(yǔ)法： CREATE DATABASE database_name / 指定數(shù)據(jù)庫(kù)名 ON 子句 / 指定數(shù)據(jù)庫(kù)文件和文件組屬性 LOG ON 子句 / 指定日志文件屬性 其中： ldatabase_name：是所創(chuàng)建的數(shù)據(jù)庫(kù)邏輯名稱(chēng)，其

16、命名須遵循SQL Server 2000的命名規(guī)則，最大長(zhǎng)度為128個(gè)字符。 lON子句：指出了數(shù)據(jù)庫(kù)的數(shù)據(jù)文件和文件組，其格式為： ON PRIMARY,n ,n 其中PRIMARY用來(lái)指定主文件。若不指定主文件，則諸數(shù)據(jù)文件中的第一個(gè)文件將成為主文件。,26,在SQL Server中建立數(shù)據(jù)庫(kù)模式,數(shù)據(jù)文件的描述主要給出文件的邏輯名、存儲(chǔ)路徑、大小及增長(zhǎng)特性（這些特征可以與界面創(chuàng)建數(shù)據(jù)庫(kù)時(shí)對(duì)數(shù)據(jù)庫(kù)特征的設(shè)置相聯(lián)系）。其語(yǔ)法定義為： := ( NAME = 邏輯文件名 , FILENAME = 操作系統(tǒng)文件名 ,SIZE = size ,MAXSIZE = max_size | UNLIMI

17、TED ,FILEGROWTH = growth_increament ) 定義文件組的屬性，語(yǔ)法格式為： :=FILEGROUP 文件組名 ,n 文件組中各文件的描述和數(shù)據(jù)文件描述相同。 l LOG ON子句：用于指定數(shù)據(jù)庫(kù)事務(wù)日志文件的屬性，其定義格式與數(shù)據(jù)文件的格式相同。,27,例子,創(chuàng)建Student的數(shù)據(jù)庫(kù) CREATE DATABASE Student ON ( NAME = Student_data, FILENAME = E:dataStudent.mdf) GO 分析： 本例創(chuàng)建一個(gè)名為Student的數(shù)據(jù)庫(kù)，數(shù)據(jù)庫(kù)只包含一個(gè)主數(shù)據(jù)文件和一個(gè)主日志文件，它們均采用系統(tǒng)默認(rèn)存儲(chǔ)

18、路徑和文件名，大小分別為model數(shù)據(jù)庫(kù)中主數(shù)據(jù)文件和日志文件的大小。,28,目錄,在目錄中建立和更新模式就像在模式中建立表之類(lèi)的模式元素一樣。 SQL規(guī)定了語(yǔ)句 SET CATALOG 表示允許設(shè)置“當(dāng)前”目錄。,29,SQL環(huán)境中兩個(gè)特殊的進(jìn)程,1. 服務(wù)器：運(yùn)行在一個(gè)大型的存儲(chǔ)數(shù)據(jù)庫(kù)的主機(jī)上，支持對(duì)數(shù)據(jù)元素的操作。 2. SQL客戶(hù)：允許用戶(hù)連接到服務(wù)器上對(duì)數(shù)據(jù)庫(kù)進(jìn)行操作。,30,傳統(tǒng)編程語(yǔ)言如何操作數(shù)據(jù)庫(kù),1. 連接數(shù)據(jù)庫(kù)，建立到數(shù)據(jù)庫(kù)的一個(gè)連接，即會(huì)話(huà)session。 2. 發(fā)送一組SQL指令，接受并處理返回的結(jié)果。 3. 中斷連接。,31,如何建立數(shù)據(jù)庫(kù)應(yīng)用,必要的構(gòu)件 連接前的準(zhǔn)

19、備：確認(rèn)數(shù)據(jù)庫(kù)服務(wù)器已啟動(dòng)。 準(zhǔn)備程序,32,如何連接數(shù)據(jù)庫(kù),1. 數(shù)據(jù)庫(kù)URL(Uniform Resource Locator) 2. 驅(qū)動(dòng)程序Driver 3. 用戶(hù)名 4. 口令,33,8.4 SQL中的事務(wù),34,如何控制事務(wù)運(yùn)行,事務(wù)的一次運(yùn)行只能是下面兩個(gè)結(jié)果之一： Commit提交；或Rollback退回 Commit指令提交當(dāng)前事務(wù)，完成對(duì)數(shù)據(jù)的更新。 Rollback指令退回當(dāng)前事務(wù)，并取消當(dāng)前事務(wù)對(duì)數(shù)據(jù)已做的更新。 Commit和Rollback都結(jié)束當(dāng)前事務(wù)，并自動(dòng)啟動(dòng)下一個(gè)事務(wù)。 注意：每一條SQL指令都在某個(gè)事務(wù)中執(zhí)行。,35,在多用戶(hù)多任務(wù)環(huán)境中，并發(fā)事務(wù)可能引起

20、哪些問(wèn)題,同時(shí)更新同一數(shù)據(jù) 臟讀dirty read 不可重復(fù)讀non-repeatable read 虛行(幻象元組)phantom rows,36,“寫(xiě)鎖”write lock是什么概念,某數(shù)據(jù)被一個(gè)事務(wù)修改，在該事務(wù)提交或退回之前，其他事務(wù)不能修改或刪除此數(shù)據(jù)。 具體來(lái)說(shuō)，事務(wù)A增加insert或修改update某行數(shù)據(jù)而未提交前，事務(wù)B不允許刪除delete或修改update此行數(shù)據(jù)。,事務(wù)A,數(shù)據(jù)T,事務(wù)B,UPDATE T=T+10,COMMIT,UPDATE T=T+20,寫(xiě)鎖：通常自動(dòng)施加“寫(xiě)鎖”，以避免并發(fā)事務(wù)同時(shí)修改同一數(shù)據(jù),37,臟讀是什么含義,當(dāng)事務(wù)A修改某數(shù)據(jù)后，在提

21、交或退回前，該數(shù)據(jù)被事務(wù)B讀取，若事務(wù)A退回，則事務(wù)B讀取的數(shù)據(jù)為“臟數(shù)據(jù)”，即事務(wù)B發(fā)生臟讀。,事務(wù)A,數(shù)據(jù)T,事務(wù)B,UPDATE T=T+10,ROLLBACK,SELECT T=110,臟讀：事務(wù)A認(rèn)為T(mén)=100；而事務(wù)B認(rèn)為T(mén)=110,SELECT T=100,？,38,不可重讀non-repeatable read是什么含義,事務(wù)A先讀某行數(shù)據(jù)，隨后事務(wù)B修改update或刪除delete該行數(shù)據(jù)并提交，事務(wù)A重復(fù)再讀該行數(shù)據(jù)，結(jié)果就不一致。,事務(wù)A,數(shù)據(jù)T,事務(wù)B,UPDATE T=T+10,SELECT T=100,不可重讀：事務(wù)A在重讀T之前認(rèn)為T(mén)=100；而實(shí)際上已改變,S

22、ELECT T=100,？,COMMIT,SELECT T=110,39,虛行phantom rows是什么含義,事務(wù)A按某條件C查詢(xún)一組數(shù)據(jù)，隨后事務(wù)B增加insert滿(mǎn)足條件C的數(shù)據(jù)，或修改update條件C相關(guān)的數(shù)據(jù)，使得事務(wù)A按原條件C再次查詢(xún)的結(jié)果不一致。也稱(chēng)為“幻象”元組。,事務(wù)A,數(shù)據(jù)T,事務(wù)B,INSERT (T=100),虛行：事務(wù)A在重復(fù)讀T之前認(rèn)為滿(mǎn)足條件C的數(shù)據(jù)不變；而實(shí)際上已改變,SELECT ？WHERE T=100,？,COMMIT,SELECT ？WHERE T=100,40,“讀鎖”read lock是什么概念,當(dāng)某數(shù)據(jù)被一個(gè)事務(wù)處理時(shí)，其他事務(wù)限制讀該數(shù)據(jù)。

23、 設(shè)置為某個(gè)隔離級(jí)別。,41,有哪些事務(wù)隔離級(jí)別(isolation level)來(lái)避免沖突,所有隔離級(jí)別上都有“寫(xiě)鎖” L0：讀不提交，無(wú)讀鎖。 L1：讀提交，事務(wù)在讀取某行數(shù)據(jù)之前檢查該行是否有“寫(xiě)鎖”，若有則不能讀；若無(wú)則讀。 可避免臟讀。只有已提交更新后，其它事務(wù)才能看到該事務(wù)所做的更新。 L2：可重復(fù)讀，事務(wù)讀到某行數(shù)據(jù)就設(shè)讀鎖，事務(wù)結(jié)束前該行保持原值，其它事務(wù)不能刪改此行。 保證該行在同一事務(wù)中可重復(fù)讀，同時(shí)也避免了臟讀。 注意：多個(gè)事務(wù)可對(duì)同一行設(shè)讀鎖，故即使當(dāng)前事務(wù)讀到該行，也不能保證能修改此行。 L3：可串行事務(wù)，對(duì)同一組數(shù)據(jù)的各事務(wù)都不交叉運(yùn)行，不允許并發(fā)事務(wù)作用于同一組數(shù)

24、據(jù)。 對(duì)同一個(gè)數(shù)據(jù)幾乎沒(méi)有并行事務(wù)。,42,設(shè)置隔離級(jí)別,事務(wù)不論通過(guò)Commit還是Rollback結(jié)束，釋放自己所施加的寫(xiě)鎖和讀鎖。 當(dāng)客戶(hù)程序連接某個(gè)數(shù)據(jù)庫(kù)時(shí)，可設(shè)置某個(gè)隔離級(jí)別。 服務(wù)器可為所有用戶(hù)設(shè)置一個(gè)缺省的隔離級(jí)別。 例如：設(shè)置串行化和不提交 SET TRANSACTION READ WRITE ISOLATION LEVEL READ UNCOMMITTED,43,8.5 SQL中的安全機(jī)制和用戶(hù)認(rèn)證,44,數(shù)據(jù)庫(kù)安全性,訪問(wèn)控制Access Control，即限制什么用戶(hù)對(duì)哪些數(shù)據(jù)庫(kù)對(duì)象(如表、視圖、過(guò)程等)進(jìn)行哪些操作。,45,數(shù)據(jù)庫(kù)中被保護(hù)對(duì)象及可控制操作,表Table：

25、 References外鍵 Insert Delete Update Select (All表示以上所有權(quán)力) 視圖View： Delete Insert Select Update,46,數(shù)據(jù)庫(kù)中被保護(hù)對(duì)象及可控制操作,過(guò)程Procedure： Execute 用戶(hù)和組Users 建立一個(gè)沒(méi)有口令的用戶(hù)commonuser。 Grant Group To commonuser; 使commonuser成為一個(gè)組。,55,如何確定一個(gè)組的權(quán)力,Grant insert,delete,update,select On dba.customer to commonuser; 為commonuser組授權(quán)customer表的增刪改查權(quán)力。 若增加WITH