數(shù)據(jù)庫上課第九講數(shù)據(jù)庫開發(fā)

1、2021-12-152主要內(nèi)容基于數(shù)據(jù)庫的開發(fā)視圖存儲過程和函數(shù)游標(biāo)觸發(fā)器2021-12-153數(shù)據(jù)庫的開發(fā)及應(yīng)用 應(yīng)用程序應(yīng)用程序數(shù)據(jù)庫訪問數(shù)據(jù)庫訪問技術(shù)技術(shù)視圖事件函數(shù)存儲過程（函數(shù)）觸發(fā)器觸發(fā)器游標(biāo)數(shù)據(jù)庫數(shù)據(jù)庫事務(wù)注意注意：不同的數(shù)據(jù)庫，語法不盡相同標(biāo)準(zhǔn)標(biāo)準(zhǔn)SQL（非過程化的查詢語言）2021-12-154MySQL 參考手冊http:/ 視圖視圖是虛表，是從一個或幾個基本表(或視圖)中導(dǎo)出的表。僅在系統(tǒng)的數(shù)據(jù)字典中存放視圖的定義，不存放視圖對應(yīng)的數(shù)據(jù)。當(dāng)基本表中的數(shù)據(jù)發(fā)生變化時，從視圖中查詢出的數(shù)據(jù)也隨之改變。視圖實(shí)現(xiàn)了數(shù)據(jù)庫管理系統(tǒng)三級模式中的外模式。基于視圖的操作包括：查詢、刪除、

2、受限更新和定義基于該視圖的新視圖。2021-12-157視圖的主要作用簡化用戶的操作；使用戶能以多種角度看待同一數(shù)據(jù)；對重構(gòu)數(shù)據(jù)庫提供了一定程度的邏輯獨(dú)立性；能夠?qū)C(jī)密數(shù)據(jù)提供安全保護(hù)；適當(dāng)?shù)睦靡晥D可以更清晰的表達(dá)查詢。主要方法使用CREATE VIEW或ALTER VIEW創(chuàng)建或更改視圖。使用DROP VIEW銷毀視圖。使用SHOW CREATE VIEW 顯示視圖元數(shù)據(jù)。2021-12-158使用視圖的優(yōu)點(diǎn)為用戶集中數(shù)據(jù)為用戶集中數(shù)據(jù)，簡化用戶的數(shù)據(jù)查詢和處理。有時用戶所需要的數(shù)據(jù)分散在多個表中，定義視圖可將它們集中在一起，從而方便用戶的數(shù)據(jù)查詢和處理。屏蔽數(shù)據(jù)庫的復(fù)雜性屏蔽數(shù)據(jù)庫的復(fù)雜

3、性。用戶不必了解復(fù)雜的數(shù)據(jù)庫中的表結(jié)構(gòu)，并且數(shù)據(jù)庫表的更改也不影響用戶對數(shù)據(jù)庫的使用。簡化用戶權(quán)限的管理簡化用戶權(quán)限的管理。只需授予用戶使用視圖的權(quán)限，而不必指定用戶只能使用表的特定列，也增加了安全性。便于數(shù)據(jù)共享便于數(shù)據(jù)共享。各用戶不必都定義和存儲自己所需的數(shù)據(jù)，可共享數(shù)據(jù)庫的數(shù)據(jù)，這樣同樣的數(shù)據(jù)只需存儲一次?？梢灾匦陆M織數(shù)據(jù)以便輸出到其他應(yīng)用程序中。2021-12-1591.2 定義視圖CREATE VIEW , , . AS WITH CHECK OPTION視圖的名稱，該名稱在數(shù)據(jù)庫中唯一列名，可以省略不寫，自動取查詢出來的列名但是下列情況必須寫：某個目標(biāo)列是集函數(shù)或表達(dá)式；多表連接中

4、有相同的列名；1.在視圖中為某列取新的名稱更合適。增刪改操作增刪改操作必須滿足視圖定義的謂詞條件（子查詢中的條件表達(dá)式）。子查詢，不允許含有ORDER BY子句和DISTINCT短語例3.86創(chuàng)建僅包含1991年出生的學(xué)生視圖StudentView1991。CREATE VIEW StudentView1991AS SELECT * FROM Student WHERE year(birthday)=1991l注意：省略了視圖的列名，自動取查詢出來的列名。l沒有使用WITH CHECK OPTION選項，下面的語句可以執(zhí)行：INSERT INTO StudentView1991 VALUES(

5、 0700008, 李相東, 男, 1992-10-21 00:00, 云南, 撒呢族, CS0701 )2021-12-1511例3.87創(chuàng)建僅包含1991年出生的學(xué)生視圖StudentView1991Chk，并要求進(jìn)行修改和插入操作時仍需保證該視圖只有91年出生的學(xué)生。CREATE VIEW StudentView1991ChkAS SELECT * FROM Student WHERE year(birthday)=1991 WITH CHECK OPTION2021-12-1512lWITH CHECK OPTION l其更新操作必須滿足:修改操作：自動加上year(birthday)

6、=1991的條件；刪除操作：自動加上year(birthday)=1991的條件；插入操作：自動檢查birthday屬性值是否滿足為1991年出生，如果不是，則拒絕該插入操作。2021-12-1513l例：下面的插入語句可以執(zhí)行： INSERT INTO StudentView1991Chk VALUES( 0700011, 李相東, 男, 1991-10-21 00:00, 云南, 撒呢族, CS0701 )l下面的插入語句不可以執(zhí)行： INSERT INTO StudentView1991Chk VALUES( 0700009, 李相東, 男, 1992-10-21 00:00, 云南,

7、撒呢族, CS0701 )l插入的出生日期違反了出生日期必須為1991年。2021-12-1514l當(dāng)視圖是基于一張表，且保留了主碼屬性，這樣的視圖稱為行列子集視圖。l視圖可以建立在一張表上，也可以建立在多張表上。2021-12-1515例3.88創(chuàng)建一個包含學(xué)生學(xué)號、姓名、課程名、獲得的學(xué)分和相應(yīng)成績的視圖ScoreView。l由于成績必須大于等于60分才獲得學(xué)分，該視圖必須含有該條件。CREATE VIEW ScoreViewAS SELECT a.studentNo, studentName, courseName, creditHour, score FROM Student a, C

8、ourse b, Score c WHERE a.studentNo=c.studentNo AND b.courseNo=c.courseNo AND score=602021-12-1516結(jié)果（select * from scoreview）視圖也可以建立在視圖上2021-12-1517在設(shè)計表結(jié)構(gòu)時，為減少數(shù)據(jù)的冗余存放，往往僅存放基本數(shù)據(jù)凡是可以由基本數(shù)據(jù)導(dǎo)出的數(shù)據(jù)，在基本表中一般不存儲；如在學(xué)生Student表中沒有存放年齡，但可建立一個包含年齡屬性的視圖，這樣的視圖稱為帶表達(dá)式的視圖。例3.91 創(chuàng)建一個包含學(xué)生學(xué)號、姓名和年齡的視圖CREATE VIEW StudentAgeV

9、iew AS SELECT studentNo, studentName, year(now()-year(birthday) age FROM Student2021-12-15181.3 查詢視圖 從用戶的角度來看，查詢視圖及查詢基本表的方式是完全一樣的。從系統(tǒng)的角度來看，查詢視圖的過程是：進(jìn)行有效性檢查，檢查查詢中涉及的表和視圖是否存在；從數(shù)據(jù)字典中取出視圖的定義，將視圖定義的子查詢及用戶的查詢結(jié)合起來，轉(zhuǎn)換成等價的對基本表的查詢；執(zhí)行修正后的查詢。2021-12-1519例3.92在StudentView1991中查詢CS0701班同學(xué)的信息。SELECT *FROM StudentV

10、iew1991WHERE classNo=CS07012021-12-1520系統(tǒng)首先進(jìn)行有效性檢查，判斷視圖StudentView1991是否存在，如果存在，則從系統(tǒng)的數(shù)據(jù)字典中取出該視圖的定義；將定義中的子查詢及用戶的查詢結(jié)合起來，轉(zhuǎn)換為基于表的查詢：SELECT * FROM StudentWHERE year(birthday)=1991 AND classNo=CS0701然后系統(tǒng)執(zhí)行修正后的查詢。2021-12-15211.4 更新視圖更新視圖指通過視圖來插入、刪除和修改基本表中的數(shù)據(jù)。視圖不實(shí)際存放數(shù)據(jù)，對視圖的更新，最終要轉(zhuǎn)換為對基本表的更新。對視圖進(jìn)行更新操作，其限制條件比較

11、多建立視圖的作用不是利用視圖來更新數(shù)據(jù)庫中的數(shù)據(jù)，而是簡化用戶的查詢簡化用戶的查詢；達(dá)到一定程度的安全性保護(hù)；2021-12-1522如果視圖包含下述結(jié)構(gòu)中的任何一種，那么它就是不可更新的：（1）聚合函數(shù)；（2）DISTINCT關(guān)鍵字；（3）GROUP BY子句；（4）ORDER BY子句；（5）HAVING子句；（6）UNION運(yùn)算符；（7）位于選擇列表中的子查詢；（8）FROM子句中包含多個表；（9）SELECT語句中引用了不可更新視圖；（10）WHERE子句中的子查詢，引用FROM子句中的表；（11）ALGORITHM 選項指定為TEMPTABLE（使用臨時表總會使視圖成為不可更新的）。

12、盡量不要對視圖執(zhí)行更新操作。盡量不要對視圖執(zhí)行更新操作。2021-12-1523例3.95在StudentView1991中，將學(xué)號為0800004同學(xué)的名字修改為張小立。 UPDATE StudentView1991 SET studentName=張小立 WHERE studentNo=0800004對于該操作：系統(tǒng)首先進(jìn)行有效性檢查，判斷視圖StudentView1991是否存在，如果存在，則從系統(tǒng)的數(shù)據(jù)字典中取出該視圖的定義；將定義中的子查詢及用戶的查詢結(jié)合起來，轉(zhuǎn)換為基于基本表的修改：2021-12-15241.5 刪除數(shù)據(jù) 使用DELETE語句可以通過視圖刪除基本表的數(shù)據(jù)。語法：D

13、ELETE FROM 視圖 WHERE 條件注意：對依賴于多個基本表的視圖，不能使用DELETE語句。2021-12-15251.6 刪除視圖 刪除視圖：DROP VIEW CASCADEl其中，CASCADE為可選項，選擇表示級聯(lián)刪除。l該語句從數(shù)據(jù)字典中刪除指定的視圖定義如果該視圖上還導(dǎo)出了其他視圖，使用CASCADE級聯(lián)刪除語句，把該視圖和由它導(dǎo)出的所有視圖一起刪除；刪除基本表時，由該基本表導(dǎo)出的所有視圖定義都必須顯式地使用DROP view語句刪除。2021-12-1526例3.100刪除視圖及級聯(lián)視圖。刪除視圖StudentView1991： DROP VIEW StudentVie

14、w1991級聯(lián)刪除視圖SourceView： DROP VIEW SourceVIEW CASCADE2021-12-15282.1 存儲過程概述存儲過程（Stored Procedure）是一組為了完成特定功能的SQL 語句集，經(jīng)編譯后存儲在數(shù)據(jù)庫中。由SQL語句語句和一些特殊的控制結(jié)構(gòu)控制結(jié)構(gòu)組成。用戶通過指定存儲過程的名字并給出參數(shù)指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。用于封裝計算；存儲過程存放在數(shù)據(jù)庫中，且在服務(wù)器端服務(wù)器端運(yùn)行；可以定義多個輸入輸出參數(shù)輸入輸出參數(shù)。2021-12-1529存儲過程的優(yōu)點(diǎn)實(shí)現(xiàn)一定程度的安全性保護(hù)對存儲過程只需授予執(zhí)行權(quán)限，不需

15、授予表或視圖的操作權(quán)限。對于不允許用戶直接操作的表或視圖，可通過調(diào)用存儲過程來間接地訪問這些表或視圖，達(dá)到一定程度的安全性；用戶對存儲過程只有執(zhí)行權(quán)限，沒有查看權(quán)限。擁有存儲過程的執(zhí)行權(quán)限，自動獲取了存儲過程中對相應(yīng)表或視圖的操作權(quán)限；這些操作權(quán)限僅能通過執(zhí)行存儲過程來實(shí)現(xiàn)，一旦脫離存儲過程，也就失去了相應(yīng)操作權(quán)限。2021-12-1530特別適合統(tǒng)計和查詢操作一般統(tǒng)計和查詢，尤其是期末統(tǒng)計，往往涉及數(shù)據(jù)量大、表多，若在客戶端實(shí)現(xiàn)，數(shù)據(jù)流量和網(wǎng)絡(luò)通信量較大；很多情況下，管理信息系統(tǒng)的設(shè)計者，將復(fù)雜的查詢和統(tǒng)計用存儲過程來實(shí)現(xiàn)，免去客戶端的大量編程。減少網(wǎng)絡(luò)通信量存儲過程僅在服務(wù)器端執(zhí)行，客戶端

16、只接收結(jié)果；由于存儲過程及數(shù)據(jù)一般在一個服務(wù)器中，可減少大量的網(wǎng)絡(luò)通信量。2021-12-1531示例：helloWorld定義：定義：delimiter /Drop PROCEDURE if exists pro_helloworld/CREATE PROCEDURE pro_helloworld(in str varchar(40)begin SELECT str; end/delimiter ;執(zhí)行：執(zhí)行： Call pro_helloworld(helloWorld);改變命令結(jié)束符2021-12-1532存儲過程的語法創(chuàng)建存儲過程執(zhí)行存儲過程修改和刪除存儲過程參考：http:/ 創(chuàng)建

17、存儲過程基本語法：CREATE PROCEDURE name (參數(shù)參數(shù),.) 特性特性 . routine_body IN | OUT | INOUT 數(shù)據(jù)類型數(shù)據(jù)類型默認(rèn)是IN。集合值輸出參數(shù)不在存儲過程的參數(shù)中定義。函數(shù)體2021-12-1534函數(shù)體流程控制流程控制IF語句CASE語句LOOP語句LEAVE語句ITERATE語句REPEAT語句WHILE語句BEGIN . END復(fù)合語句復(fù)合語句使用BEGIN . END復(fù)合語句來包含多個語句。變量聲明變量聲明DECLARE可以是局部變量，條件和 處理程序、游標(biāo)變量賦值變量賦值SET var_name = expr , var_name

18、 = expr .SELECT . INTO語句語句把選定的列直接存儲到變量。2021-12-1535用戶變量用戶變量在MySQL客戶端使用的變量: variable示例：示例：set y=Goodbye Cruel World;Call pro_helloworld(y);2021-12-1536內(nèi)部變量聲明：DECLARE variable_name ,variable_name. datatype DEFAULT value;delimiter /Drop PROCEDURE if exists pro_helloworld/CREATE PROCEDURE pro_helloworld

19、(in str varchar(40)begin DECLARE str2 varchar(40) DEFAULT Ill be back! ;select CONCAT(str, ,str2) as voice;end /delimiter ;CONCAT()函數(shù)函數(shù): 返回結(jié)果為連接參數(shù)產(chǎn)生的字符串。如有任何一個參數(shù)為NULL ，則返回值為 NULL。 定義內(nèi)部變量str2，作用域為最近的begin和end之間2021-12-1537調(diào)用存儲過程set y=Goodbye Cruel World;Call pro_helloworld(y);2021-12-1538例9.23mySQL版

20、輸入某個同學(xué)的學(xué)號，統(tǒng)計該同學(xué)的平均分。delimiter /CREATE PROCEDURE proStudentByNo1(in sno char(7),out stno char(7),out sname varchar(20),out savg decimal(5,1) SELECT a.studentNo , studentName,avg(score) into stno,sname,savg FROM Student a, Score b WHERE a.studentNo=b.studentNo AND a.studentNo=sNo GROUP BY a.studentNo;

21、 /delimiter ;把取出的值存入out變量2021-12-1539執(zhí)行存儲過程mysql call proStudentByNo1(0700001,no,name,avg);mysql select no,name,avg;+-+-+-+| no | name | avg |+-+-+-+| 0700001 | 李小勇 | 78.7 |+-+-+-+一般在應(yīng)用程序中調(diào)用定義out變量該變量在存儲過程外部可調(diào)用2021-12-15402.4 修改存儲過程（自學(xué)） 修改存儲過程ALTER PROCEDURE | FUNCTION sp_name characteristic . chara

22、cteristic: CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | SQL SECURITY DEFINER | INVOKER | COMMENT string2021-12-15412.5 刪除存儲過程（自學(xué)） 語法： DROP PROCEDURE 例9.31 刪除存儲過程 DROP PROCEDURE proStudentByNo12021-12-15422.6 函數(shù)函數(shù)及存儲過程類似。函數(shù)參數(shù)均為“in”，有一個返回值有一個返回值。存儲過程無返回值，但可以定義多個out參數(shù)返回處理后的值?；菊Z法：CREATE

23、 FUNCTION name (參數(shù),.)RETURNS type characteristic . routine_body函數(shù)的使用：funcName (x)2021-12-1543例SET GLOBAL log_bin_trust_function_creators = 1;delimiter /CREATE FUNCTION hello (s CHAR(20) RETURNS CHAR(50)/*do something*/RETURN CONCAT(Hello, ,s,!); /delimiter ;調(diào)用函數(shù)：調(diào)用函數(shù)：SELECT hello(world);允許創(chuàng)建函數(shù)| hell

24、o(world) | Hello, world! |2021-12-15453.1 游標(biāo)游標(biāo)（cursor）用于指向SQL語句的執(zhí)行結(jié)果語句的執(zhí)行結(jié)果。游標(biāo)可前后移動。用戶可以對SELECT語句返回的結(jié)果值進(jìn)行逐行處理，逐一從游標(biāo)處獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理?？蓪τ螛?biāo)的當(dāng)前位置進(jìn)行更新、查詢和刪除注意： 在mySQL中，游標(biāo)僅在存儲過程和函數(shù)內(nèi)被支持。游標(biāo)僅在存儲過程和函數(shù)內(nèi)被支持。2021-12-15463.2 使用游標(biāo)的五個步驟1）聲明）聲明游標(biāo)標(biāo)DECLARE cursor_name CURSOR FOR select_statement：聲明一個游標(biāo)。也可以在子程序中

25、定義多個游標(biāo)，但是一個塊中的每一個游標(biāo)必須有唯一的名字。注意：SELECT語句不能有INTO子句。 2）OPEN cursor_name：打開先前聲明的游標(biāo)。3）FETCH cursor_name INTO var_name , var_name .用指定的打開游標(biāo)讀取下一行（如果有下一行的話），并且前進(jìn)游標(biāo)指針。 4）CLOSE cursor_name：關(guān)閉先前打開的游標(biāo)。 如果未被明確地關(guān)閉，光標(biāo)在它被聲明的復(fù)合語句的末尾被關(guān)閉。 3.3 獲取當(dāng)前游標(biāo)值 定義變量：DECLARE 變量 類型FETCH INTO 執(zhí)行一次該語句，系統(tǒng)將當(dāng)前游標(biāo)所指向的元組屬性值放到變量中，然后游標(biāo)自動下移一

26、個元組。當(dāng)前游標(biāo)所指向元組的每個屬性值必須分別用一個變量來接收，即變量個數(shù)、數(shù)據(jù)類型必須及定義游標(biāo)中的SELECT子句所定義的屬性(或表達(dá)式)個數(shù)、數(shù)據(jù)類型相一致一致。當(dāng)游標(biāo)移至尾部，不可再讀取游標(biāo)，必須關(guān)閉游標(biāo)然后重新打開游標(biāo)。 模板 CREATE PROCEDURE curdemo(參數(shù),.) /創(chuàng)建存儲過程 BEGIN DECLARE a CHAR(16); . /聲明變量DECLARE cur1 CURSOR FOR SELECT . FROM .; /聲明游標(biāo)OPEN cur1; /打開游標(biāo)REPEAT /流程控制及循環(huán)FETCH cur1 INTO a; /獲取當(dāng)前游標(biāo)值到變量aD

27、OSomeThingEND REPEAT; CLOSE cur1; /關(guān)閉游標(biāo)END 2021-12-15514.1 觸發(fā)器(TRIGGER)概述觸發(fā)器是用戶定義在關(guān)系表上的一類由事件驅(qū)動的存儲過程，由服務(wù)器自動激活。觸發(fā)器可進(jìn)行更為復(fù)雜的檢查和操作，具有更精細(xì)和更強(qiáng)大的數(shù)據(jù)控制能力。觸發(fā)器是一種特殊的存儲過程，不管什么原因造成的數(shù)據(jù)變化都能自動響應(yīng)，對于每條SQL語句，觸發(fā)器僅執(zhí)行一次，事務(wù)可用于觸發(fā)器中。觸發(fā)程序是及表有關(guān)的，當(dāng)表上出現(xiàn)特定事件時，將激活該對象。表必須引用永久性表。不能將觸發(fā)程序及臨時表或視圖關(guān)聯(lián)起來。觸發(fā)器 觸發(fā)器僅在當(dāng)前數(shù)據(jù)庫中生成觸發(fā)器有三種類型，即插入、刪除和更新；插入、刪除和更新可作為一種類型的觸發(fā)器；查詢操作不會產(chǎn)生觸發(fā)動作，沒有查詢觸發(fā)器類型。2021-12-1553創(chuàng)建觸發(fā)器的語法mySQL語法語法CREATE TRIGGER trigger_time trigger_event ON FOR EACH ROW SQLServer語法語法CREATE TRIGGER ON FOR