《數(shù)據(jù)庫高級應(yīng)用技術(shù)》教案電子教案授課教案整本書教案電子講義

課程教案

系部：_____________________

課程代碼：_____________________

課程名稱：_____________________

課程組成員：___________________

第一講

教學周第1周節(jié)次第1、2節(jié)

章節(jié)內(nèi)容關(guān)系數(shù)據(jù)庫范式設(shè)計

授課方式理論課（V）討論課（）實驗課（V）習題課（）其他（）

主要內(nèi)容與教學目的：

1.數(shù)據(jù)庫范式的概念

2.數(shù)據(jù)庫范式的作用

3.數(shù)據(jù)庫范式的原理

4.數(shù)據(jù)庫范式的適用范圍

重點：

三大范式的原理標準

難點：

數(shù)據(jù)庫范式在數(shù)據(jù)庫設(shè)計中的運用

課堂教學組織：

1、數(shù)據(jù)庫范式的概念、作用

2、第一范式的標準

?數(shù)據(jù)庫表中的字段都是單一屬性，

?字段不可再分

?同一列中不能有多個值

3、第二范式的標準

?滿足第一范式

?每張表均有主鍵

?單字段主鍵

?聯(lián)合主鍵

4、第三范式的標準

?數(shù)據(jù)庫表滿足第二范式

?數(shù)據(jù)表的非主鍵字段不存在傳遞依賴關(guān)系

?非主鍵字段不能決定其它非主鍵字段

5、數(shù)據(jù)庫范式應(yīng)用舉例

6、數(shù)據(jù)庫范式錯誤糾正

擬提問題：

在進行數(shù)據(jù)庫設(shè)計為什么要遵循數(shù)據(jù)庫范式基本原則。

課后作業(yè)：

完成一個借書模塊的數(shù)據(jù)庫設(shè)計，需滿足數(shù)據(jù)庫三大范式，并符合如下的要求：

?讀者人數(shù)眾多、可借圖書眾多

?需維護讀者的姓名、年齡、職業(yè)、學歷、地址、電話、是否可借閱等信息

?需維護圖書的書名、出版社、ISBN號-、出版日期、價格等信息

?需維護出借的日期、到期時間、是否續(xù)期等信息

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第二講

教學周第1周節(jié)次第3、4節(jié)

章節(jié)內(nèi)容數(shù)據(jù)庫關(guān)系模式(D

授課方式理論課(J)討論課()實驗課()習題課()其他()

主要內(nèi)容與教學目的：

1、關(guān)系數(shù)據(jù)庫基本概念

2、關(guān)系模式定義

3、關(guān)系模式分解標準

重點：

關(guān)系模式分解標準

難點：

關(guān)系模式分解的函數(shù)傳遞依賴

課堂教學組織：

1、關(guān)系數(shù)據(jù)庫基本概念

?關(guān)系(Relation)

?關(guān)系模式(RelationSchema)

?元組(Tuple)

?碼(Key)

?超碼(SuperKey)

?候選碼(CandidateKey)

?主碼(PrimaryKey)

2、關(guān)系模式定義

關(guān)系模式是由5個元素來定義，表示為：R(U,D,dom,I,F)

?R為關(guān)系名

?U為組成該關(guān)系的屬性名的集合

?D為U中屬性的域的集合

?dom為屬性集U向域集D的映射

?I為完整約束集合

?F為屬性間數(shù)據(jù)的依賴關(guān)系集合

3、關(guān)系模式分解標準

?分解具有無損連接性。

?分解要保持函數(shù)依賴性。

?分解既要保持函數(shù)依賴，又要具有無損連接性。

擬提問題：

關(guān)系與關(guān)系模式有什么聯(lián)系與區(qū)別？

課后作業(yè)：

完成教材P14頁練習題。

注：每兩節(jié)課(90分鐘)為一講，每講必須填寫此表。

第三講

教學周第2周節(jié)次第5、6節(jié)

章節(jié)內(nèi)容數(shù)據(jù)庫關(guān)系模式(2)

授課方式理論課(V)討論課()實驗課()習題課()其他()

主要內(nèi)容與教學目的：

1、關(guān)系模式分解原理

2、關(guān)系模式分解算法

重點：

無損連接分解過程

難點：

判別多元分解的無損性

課堂教學組織：

1、關(guān)系模式分解原理

?分解過程：

規(guī)范化過程中將一個關(guān)系模式分解為若干個關(guān)系模式，應(yīng)該保證分解后產(chǎn)生的模式和原

來的模式等價。將一個關(guān)系模式R(U,F)分解為若干個關(guān)系模式R1(U1,F1),R2(U2,F2)…

Rn(Un,Fn)(其中U=U1U2…Un,R1為F在U1上的投影)，這意味著相應(yīng)的將存儲在

一個二維表r中的數(shù)據(jù)分散到若干個二維表rl,r2,…,rn中(其中rl是r在屬性組U1

上的投影)。

?有損分解

將關(guān)系r投影為rl,r2,???,rn時并不會丟失信息，但對rl,r2,…,rn作自然連接可能會

產(chǎn)生一些原來r中沒有的元組，從而無法區(qū)別那些元組是r中原來有的，即數(shù)據(jù)庫中應(yīng)

該存在的數(shù)據(jù)，在這個意義上丟失了信息。

?無損分解

關(guān)系模式R(U,F)分解為關(guān)系模式R1(U1,F1),R2(U2,F2)是具有無損連接性的分解的充

分必要條件是(U1CU2-U1-U2)6F+,或者(U2AUlfU2-U1)eF+

2、關(guān)系模式分解算法

?關(guān)系模式分解的分解算法中有如下幾個重要引理：

(1)分解具有無損連接性和分解保持函數(shù)依賴是兩個互相獨立的標準。

(2)若要求分解具有無損連接性，那么模式分解一定可以達到4NF。

(3)若要求分界保持函數(shù)依賴，那么模式分解可以達到3NF,但不一定能達到BCNF。

(4)若要求分解既具有無損連接性，又保持函數(shù)依賴，則模式分解可以達到3NF,但

不一定能達到BCNFo

?判別一個二元分解的無損連接性

若F+中至少存在如下函數(shù)依賴中的一個：

(1)(U1AU2)-U1-U2

(2)(U1AU2)-U2-U1

則P={R1<U1>,R2〈U2>}是R的無損分解。反之也成立。

擬提問題：

如何判別一個二元分解是否為無損連接分解?

課后作業(yè)：

自己動手，重新操作教材P12-13有損分解與無損分解例子。

注：每兩節(jié)課(90分鐘)為一講，每講必須填寫此表。

第四講

教學周第2周節(jié)次第7、8節(jié)

章節(jié)內(nèi)容關(guān)系數(shù)據(jù)庫事務(wù)基本知識(1)

授課方式理論課(J)討論課()實驗課()習題課()其他()

主要內(nèi)容與教學目的：

1、ACID特性

2、事務(wù)模型

3、并發(fā)事務(wù)

重點：

事務(wù)模型

難點：

并發(fā)事務(wù)控制

課堂教學組織：

1、ACID特性

?原子性(Atomicity)

?一致性(Consistency)

?隔離性(Isolation)

?持久性(Durability)

2、事務(wù)模型

?顯式事務(wù)

?隱式事務(wù)

?自動事務(wù)

3、并發(fā)事務(wù)

當多個用戶對同一數(shù)據(jù)進行操作時，會產(chǎn)生如下問題:

?丟失更新

?臟讀

?非重復讀

?幻讀

擬提問題：

如何理解數(shù)據(jù)庫事務(wù)的ACTD特性？

課后作業(yè)：

完成教材P26頁練習題（1-6）。

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第五講

教學周第3周節(jié)次第9、10節(jié)

章節(jié)內(nèi)容關(guān)系數(shù)據(jù)庫事務(wù)基本知識（2）

授課方式理論課討論課（）實驗課（）習題課（）其他（）

主要內(nèi)容與教學目的：

1、隔離級別

2、事務(wù)鎖

重點：

事務(wù)鎖機制

難點：

合理設(shè)置事務(wù)隔離級別

課堂教學組織：

1、隔離級別

?未提交讀（readuncommitted）

?提交讀（readcommitted）

?可重復讀（RepeatableRead）

?可串行化（Serializable）

2、事務(wù)鎖

?共享鎖（S鎖）

共享封鎖是為讀操作設(shè)置的一種封鎖，所以也稱作讀鎖，或簡稱s鎖

目的是想讀到一組不變的數(shù)據(jù)，也就是在讀數(shù)據(jù)的過程中，不允許其他用戶對該數(shù)據(jù)進

行任何修改操作。

這種封鎖可以保證最大的讀并發(fā)性，任何數(shù)量的用戶都可以同時對同樣的數(shù)據(jù)施加這種

共享鎖。已經(jīng)實施共享鎖的表拒絕來自其他事務(wù)的獨占封鎖和更新封鎖。

?更新鎖（U鎖）

當需要對一個記錄或一組記錄進行更新時（不包括插入和刪除）使用更新封鎖，該封鎖

的目的是防止其他用戶在同一時刻修改同一記錄。

已經(jīng)實施更新封鎖的記錄，拒絕來自其他用戶的任何封鎖。

?排他鎖（X鎖）

排他封鎖也叫獨占封鎖，

它是為寫（修改、增加、刪除）操作設(shè)置的一種封鎖，也稱為寫封鎖，或簡稱為X鎖,

這是最嚴格的一類封鎖。當需要對表實施插入、刪除或修改操作時，應(yīng)該使用獨占封鎖。

已經(jīng)實施獨占封鎖的表，拒絕來自其他用戶的任何封鎖。

擬提問題：

死鎖產(chǎn)生的原因與條件？

課后作業(yè)：

完成教材P26練習題（7-12）。

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第六講

教學周第3周節(jié)次第11、12節(jié)

章節(jié)內(nèi)容關(guān)系數(shù)據(jù)庫事務(wù)操作

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、隔離級別設(shè)置

2、手動事務(wù)控制操作

3、自動事務(wù)控制操作

重點：

事務(wù)控制操作

難點：

恰當使用事務(wù)維護數(shù)據(jù)準確性

課堂教學組織：

1、隔離級別設(shè)置

查詢事務(wù)隔離級別

?SELECT@@global.txisolation

設(shè)置事務(wù)隔離級別

MySQL引擎為：innodb的事務(wù)級別設(shè)置方法是：

?set+作用域+transactionisolationlevel+隔離級別

設(shè)置隔離級別為：未提交讀

setglobaltransactionisolationlevelREADUNCOMMITTED

設(shè)置隔離級別為：提交讀

setglobaltransactionisolationlevelREADCOMITTED

設(shè)置隔離級別為：可重復讀

setglobaltransactionisolationlevelREPEATABLEREAD

設(shè)置隔離級別為：可串行化

setglobaltransactionisolationlevelSERIALIZABLE

2、事務(wù)控制操作

語法命令

?開啟事務(wù)

方式一

starttransaction

方式二

begin

?提交事務(wù)

commit

?回滾事務(wù)

rollback

擬提問題：

常用的關(guān)系數(shù)據(jù)庫事務(wù)操作命令有哪些?

課后作業(yè)：

按要求建立數(shù)據(jù)庫表環(huán)境，并用事務(wù)命令進行按要求進行事務(wù)控制操作。

?建立學生表、課程表、學生選課表

/學生表：學號、姓名、年級、專業(yè)、入學年份、已修學分等

/課程表：課程號、課程名、學分、課程性質(zhì)（選修、必修）、課程歸屬專業(yè)等

/學生選課表：學號、課程號、成績

往以上數(shù)據(jù)表插入若干條記錄

?在數(shù)據(jù)庫命令行帶事務(wù)（成功/失?。┻M行以下操作

/增加一個學生數(shù)據(jù)（同時往三張表寫入對應(yīng)數(shù)據(jù)）

/刪除一個學生數(shù)據(jù)（同時刪除三張表對應(yīng)的此學生數(shù)據(jù)）

/修改某門課程的性質(zhì)，同時修改此門課程對應(yīng)的學生的成績。

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第七講

教學周第4周節(jié)次第13、14節(jié)

章節(jié)內(nèi)容存儲過程基本語法（1）

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、存儲過程的種類

2、存儲過程創(chuàng)建

3、儲過程調(diào)用

重點：

存儲過程創(chuàng)建

難點：

存儲過程創(chuàng)建語法調(diào)試

課堂教學組織：

1、存儲過程的種類

?系統(tǒng)存儲過程

?本地存儲過程

?臨時存儲過程

?遠程存儲過程

?擴展存儲過程

2、存儲過程創(chuàng)建

創(chuàng)建語法格式：

CREATEPROCEDURE存儲過程名（［過程參數(shù)［,..」］）

BEGIN

過程控制語句（Transaction-SQL）

END

3、儲過程調(diào)用

調(diào)用格式如下：

CALL+存儲過程名+參數(shù)

例如：CALLfirst_proc（100）

擬提問題：

存儲過程與單條SQL語句有什么聯(lián)系與區(qū)別？

課后作業(yè)：

自己動手，在數(shù)據(jù)庫客戶端逐一創(chuàng)建、運行本次課PPT講稿上的每個例子。

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第八講

教學周第4周節(jié)次第15、16節(jié)

章節(jié)內(nèi)容存儲過程基本語法（2）

授課方式理論課（J）討論課（）實驗課（習題課（）其他（）

主要內(nèi)容與教學目的：

1、IN輸入?yún)?shù)

2、OUT輸出參數(shù)

3、INOUT輸入輸出參數(shù)

重點：

全部

難點：

根據(jù)場合靈活選擇合適的參數(shù)類型

課堂教學組織：

1、IN輸入?yún)?shù)

表示該參數(shù)為輸入型參數(shù)，只能從外面?zhèn)髦档酱鎯^程內(nèi)部，反過來則無效，即：在存

儲過程內(nèi)部修改該參數(shù)值將不會返回到外部。此參數(shù)值必須在調(diào)用存儲過程中指定。

?IN參數(shù)案例：

DELIMITER//

CREATEPROCEDUREdemoinparameter（INpidint,INpscoreaddint）

BEGIN

updateusersetscore=（score+pscoreadd）whereuserid=pid;

END;

//

DELIMITER;

2、OUT輸出參數(shù)

表示該參數(shù)為輸出型參數(shù)，只能從存儲過程內(nèi)部傳值到存儲過程外部，反過來則無效，

即：該值可在存儲過程內(nèi)部被改變，并可返回。此參數(shù)值必須在調(diào)用存儲調(diào)用過程中賦

值。

?OUT參數(shù)案例：

DELIMITER//

CREATEPROCEDUREdemooutparameter(OUTpoutint)

BEGIN

SELECTCOUNT(*)INTOpoutFROMuser;

END

//

DELIMITER;

3.INOUT輸入輸出參數(shù)

表示該參數(shù)為輸入輸出型參數(shù)，同時俱備輸入、輸出功能，既可從外面?zhèn)髦档酱鎯^程

內(nèi)部，也可以從存儲過程內(nèi)部傳值到存儲過程外部。

?INOUT參數(shù)案例：

DELIMITER//

CREATEPROCEDUREdemo_inout_parameter

(INOUTp_inout_numint,INOUTp_inout_strvarchar(50))

BEGIN

DECLAREidintdefault0;

SETid=p_inout_num;

UPDATEuserSETemail=pinoutstrWHEREuserid=id;

SELECTscoreINTOp_inout_numFROMuserWHEREuser_id=id;

SELECTphoneINTOp_inout_strFROMuserWHEREuser_id=id;

END

//

DELIMITER;

擬提問題：

存儲過程的OUT參數(shù)與編程語言中方法的返回值有什么異同？

課后作業(yè)：

寫一個存儲過程，實現(xiàn)以下功能：

2、把上面USRE表中積分（score）大于60的用戶同步到一個VIP_USER表中

3、給積分60以下的女性會員（sex字段值為0）,每人加5分

4、返回以上操作以后男會員的平均分與女會員的平均分

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第九講

教學周第5周節(jié)次第17、18節(jié)

章節(jié)內(nèi)容存儲過程基本語法（3）

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、會話變量

2、存儲過程變量

重點：

存儲過程變量

難點：

變量作用范圍

課堂教學組織：

1、會話變量

會話變量也叫用戶變量，可以在一個客戶端會話的任何地方聲明,作用域是整個會話，

會話斷開后，會話變量也就消失。

?會話變量定義：

會話變量名以@開頭，使用set直接賦值，在一個會話內(nèi)，會話變量只須初始化一次。

例如:SET@num=l

表示聲明了一個名字叫“@num”的會話變量，其初始值為1。

2、存儲過程變量

存儲過程變量，只能在存儲過程中使用，存儲過程變量存在于數(shù)據(jù)庫服務(wù)器上，而不是

在于客戶端。

?存儲過程變量的按如下格式定義：

DECLARE+變量名+數(shù)據(jù)類型+[DEFAULTVALUE]

①DECLARE為聲明存儲過程變量的關(guān)鍵字；

②變量名可以任意，但盡可能要達到能表意的目的；

③數(shù)據(jù)類型為MySQL的數(shù)據(jù)類型,如:int,float,date,varchar（length）；

?[DEFAULTVALUE]為變量的初始值，可有可無，若不指定，則自動賦null值。

?存儲過程變量賦值

變量賦值采用關(guān)鍵字SET開頭，后面跟變量名，賦值符號用等號“="表示，等號的

右邊為所需要賦予的值，可以是具體值，也可以是個表達式，還可以是個查詢返回值。

賦值格式：SET變量名=表達式值

?變量取值

存儲過程變量的取值與其它面向?qū)ο蟮木幊陶Z言一樣，直接調(diào)用變量名就可以取得變

量上存儲的值。

?變量作用范圍：

存儲過程變量為數(shù)據(jù)庫服務(wù)器上的變量，存儲在服務(wù)器中，但其作用域并不能認為是

整個數(shù)據(jù)庫服務(wù)器。

存儲過程變量作用域的認定按下三個步驟進行：

①.從變量所在位置開始，往上回溯，從最靠近變量的第一個begin開始；

②.往下延伸，找到與上面begin相匹配的end結(jié)束；

③.內(nèi)部的變量比外部變量在其作用域范圍內(nèi)享有更高的優(yōu)先權(quán)。

擬提問題：

存儲過程變量與會話的異同？

課后作業(yè)：

寫一個存儲過程，實現(xiàn)以下功能：

?給本章教材上User表，增加一個備注字段（mark）

?用DECLARE定義幾個存儲過程變量

?變量1值為：“不活躍會員”

?變量2值為：“普通會員”

?變量3值為：“高級會員”

?根據(jù)上面定義的變量填充mark字段

?如果積分在60分以下，填充變量1的值

?如果積分在60-79分，填充變量2的值

?如果積分在80分以上，填充變量3的值

?返回以上操作以后每個等級會員的個數(shù)

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十講

教學周第5周節(jié)次第19、20節(jié)

章節(jié)內(nèi)容存儲過程流程控制TF條件語句

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、IF語句結(jié)構(gòu)

2、IF條件函數(shù)

重點：

IF語句

難點：

IF條件函數(shù)靈活運用

課堂教學組織：

1、1F語句結(jié)構(gòu)

IF語句是最普通的條件控制語句，其語法簡單易懂，在存儲過程代碼中隨處可見，也

是使用頻率最高的條件判斷語句。

?語法格式：if…then…elseif…then…else…endif

/if后面跟判斷條件

/elseif后面跟判斷條件

/then后面跟條件分支語句塊

“可以有多個elseif…then語句塊，也可以沒有

/else表示以上條件均不滿足時會執(zhí)行的語句塊

/整個條件語句塊的最后面以endif表示結(jié)束

?IF語句案例：

DELIMITER//

CREATEPROCEDUREproc_iCelse(INifLparameterint)

begin

declarecondition_paraint;

setcondition_para=ifLparameter;

ifcondition_para=1then

INSERTINTO'user'

('user_name','pass_word','email、,'phone','sex','score')

VALUES

('ifLuser*,'if_user7if_user@,,188888888,,W,!00);

elseifcondition_para=2then

update'user'setscore=200whereuser_id<=5;

elseifcondition_para=3then

update'user'setscore=400whereuser_id>5;

else

update'user'setscore=1000;

endif;

end;

//

DELIMITER;

2、IF條件函數(shù)

?IF語法格式：

IF(exprl,expr2,expr3)

(1)如果exprl是TRUE則IF()的返回值為expr2

(2)否則返回值則為expr3

(3)IF()的返回值為數(shù)字值或字符串值

如：SELECTUSER_NAME,IF(SCORE>60,'鉆石級普通級')AS'等級'FROMUSER

擬提問題：

存儲過程IF條件控制與編程語言中的IF條件控制語句有哪些異同點？

課后作業(yè)：

寫一個存儲過程，用不同的方法實現(xiàn)以下功能：

?如果輸入?yún)?shù)值為1,把user表數(shù)據(jù)同步到user_info表;

?如果輸入?yún)?shù)值為2,刪除user_info表數(shù)據(jù)；

?如果輸入?yún)?shù)值為3,把user表數(shù)據(jù)同步到user_bak表；

?如果輸入?yún)?shù)值為4,刪除usejbak表數(shù)據(jù)；

?如果輸入?yún)?shù)值為5,同時刪除user_info、user_bak表數(shù)據(jù)。

user、userinfo、userbak三個表結(jié)構(gòu)相同，參考本章教材上user.sql腳本

?用if…then…elseif…then…else語句實現(xiàn)

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十一講

教學周第6周節(jié)次第21、22節(jié)

章節(jié)內(nèi)容存儲過程流程控制-CASE條件語句

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、CASE語句結(jié)構(gòu)

2、SELECT語句中CASE語法

重點：

CASE語句結(jié)構(gòu)

難點：

SELECT語句中CASE語法

課堂教學組織：

1、CASE語句結(jié)構(gòu)

CASE語句靈活性非常高，不但可以用在存儲過程中，也可以用在單個的SQL語句中

作條件選擇。

?語法格式：case…when…then…else

/case后面跟條件變量

/when后面跟條件值

/then后面跟條件分支語句塊

/可以有多個when…then語句塊，也可以只有有一個

/else表示以上條件均不滿足時會執(zhí)行的語句塊

/整個條件語句塊最后面以endcase表示結(jié)束

?CASE語句案例：

DELIMITER//

CREATEPROCEDUREproc_case_when（INcase_parameterint）

begin

declarecondition_paraint;

setcondition_para=case_parameter;

casecondition_para

when1then

INSERTINTO'user'

（'user_name','pass_word'email'phone'sex'score'）

VALUES

('case_user7case_userVcase_user@',7777777701,200);

when2then

update'user'setscore=300whereuser_id<=5;

when3then

update'user'setscore=600whereuser_id>5;

else

update'user'setscore=2000;

endcase;

end;

//

DELIMITER;

2、單個SQL語句CASE語法格式：

/SELECTCASEWHENA條件THEN字段1WHENB條件THEN字段2

ELSE字段0ENDfromtb

擬提問題：

存儲過程CASE條件控制與編程語言中的哪種條件控制語句非常類似?

課后作業(yè)：

寫一個存儲過程，用不同的方法實現(xiàn)以下功能：

?如果輸入?yún)?shù)值為1,把user表數(shù)據(jù)同步到user_info表；

?如果輸入?yún)?shù)值為2,刪除usejinfo表數(shù)據(jù)；

?如果輸入?yún)?shù)值為3,把user表數(shù)據(jù)同步到user_bak表；

?如果輸入?yún)?shù)值為4,刪除usejbak表數(shù)據(jù)；

?如果輸入?yún)?shù)值為5,同時刪除user_info、user_bak表數(shù)據(jù)。

?user、user_info、user_bak三個表結(jié)構(gòu)相同，參考本章教材上user.sql腳本

?用case…when…then…else語句實現(xiàn)

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十二講

教學周第6周節(jié)次第23、24節(jié)

章節(jié)內(nèi)容存儲過程流程控制-WHILE循環(huán)語句

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、WHILE語句結(jié)構(gòu)

重點：

WHILE語句

難點：

WHILE循環(huán)語句的使用場景

課堂教學組織：

1、循環(huán)語句的使用場景

存儲過程中需要重復執(zhí)行某些代碼語句，執(zhí)行這些語句就需要考慮用循環(huán)控制語句。循

環(huán)控制語句是由循環(huán)體及循環(huán)的終止條件兩部分組成的，被重復執(zhí)行的語句稱為循環(huán)

體，循環(huán)的終止條件一旦出現(xiàn)，程序流程就會跨出循環(huán)體。

2、WHILE語句結(jié)構(gòu)

?WHILE語法

/語法格式：WHILE-DO

/WHILE后面跟條件表達式

/DO后面跟條件分支語句塊

/整個條件語句塊最后面以ENDWHILE表示結(jié)束

?WHILE語句案例

DELIMITER//

CREATEPROCEDUREproc_while_do()

begin

declareaint;

seta=0;

whilea<100do

INSERTINTO'user'

('user_name','pass_word','email','phone','sex','score')

VALUES

(tase-user'/case-user1,'case_user@',7777777,,,0\a);

seta=a+1;

endwhile;

end;

//

DELIMITER;

擬提問題：

WHILE語句與編程語言中WHILE語句有何區(qū)別？

課后作業(yè)：

寫一個存儲過程，用不同的方法實現(xiàn)以下功能：

?往User_Info表、User_Bak表各插入500條記錄

?User_Info>User_Bak表與User表結(jié)構(gòu)相同，參考本章教材上user.sql腳本

?用while…do循環(huán)語句實現(xiàn)

注：每兩節(jié)課(90分鐘)為一講，每講必須填寫此表。

第十三講

教學周第7周節(jié)次第25、26節(jié)

章節(jié)內(nèi)容存儲過程流程控制-LOOP循環(huán)語句

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、LOOP語句結(jié)構(gòu)

重點：

LOOP語句

難點：

LOOP循環(huán)語句的使用場景

課堂教學組織：

1、循環(huán)語句的使用場景

存儲過程中需要重復執(zhí)行某些代碼語句，執(zhí)行這些語句就需要考慮用循環(huán)控制語句。循

環(huán)控制語句是由循環(huán)體及循環(huán)的終止條件兩部分組成的，被重復執(zhí)行的語句稱為循環(huán)

體，循環(huán)的終止條件一旦出現(xiàn)，程序流程就會跨出循環(huán)體。

2、LOOP語句

?LOOP語法

/語法格式：LOOP-LEAVE

/LOOP循環(huán)不需要初始條件

/LOOP循環(huán)格式：循環(huán)標識+:+LOOP

/不需要結(jié)束條件,LEAVE語句即表示跳出循環(huán)

/LEAVE的格式是：LEAVE+循環(huán)標識

/整個條件語句塊最后面以ENDLOOP表示結(jié)束

?LOOP語句案例

DELIMITER//

CREATEPROCEDUREprocJoop（）

begin

declarecint;

setc=0;

LOOP_LABLE:loop

INSERTINTO'user、

（'user_name\'pass_word\'emair/phone\'sex\'score'）

VALUES

('loop_user','loop_user',,loop_user@','22222222,/0,,c);

setc=c+1;

ifc>=400then

leaveLOOP_LABLE;

endif;

endloop;

end;

//

DELIMITER;

擬提問題：

LOOP語句與WHILE語句有何區(qū)別？

課后作業(yè)：

寫一個存儲過程，用不同的方法實現(xiàn)以下功能：

?往User_Info表、User_Bak表各插入500條記錄

?User_Info、User_Bak表與User表結(jié)構(gòu)相同，參考本章教材上user.sql腳本

?用loop循環(huán)語句實現(xiàn)

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十四講

教學周第7周節(jié)次第27、28節(jié)

章節(jié)內(nèi)容存儲過程流程控制-REPEAT循環(huán)語句

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、REPEAT語句結(jié)構(gòu)

重點：

REPEAT語句

難點：

REPEAT語句的跳出循環(huán)條件

課堂教學組織：

1、循環(huán)語句的使用場景

存儲過程中需要重復執(zhí)行某些代碼語句，執(zhí)行這些語句就需要考慮用循環(huán)控制語句。循

環(huán)控制語句是由循環(huán)體及循環(huán)的終止條件兩部分組成的，被重復執(zhí)行的語句稱為循環(huán)

體，循環(huán)的終止條件一旦出現(xiàn)，程序流程就會跨出循環(huán)體。

2、REPEAT語句

?REPEAT語法

/語法格式：REPEAT-UNTIL

/UNTIL后面跟條件表達式

/REPEAT后面跟條件分支語句塊

/整個條件語句塊最后面以ENDREPEAT表示結(jié)束

/先執(zhí)行REPEAT操作后檢查UNTIL條件語句，而WHILE則是執(zhí)行前進行檢

查。

?REPEAT語句案例

DELIMITER//

CREATEPROCEDUREproc_repeat_until()

begin

declarebint;

setb=0;

repeat

INSERTINTO'user、

('user_name','pass_word','email','phone','sex','score')

VALUES

CrepeaCuserVrepeaCusef/repeaCuserCaJ'/l111l','0',b);

setb=b+1;

untilb>=200

endrepeat;

end;

//

DELIMITER;

擬提問題：

REPEAT語句與WHILE語句有何異同？

課后作業(yè)：

寫一個存儲過程，用不同的方法實現(xiàn)以下功能：

?往User_Info表、User_Bak表各插入500條記錄

?User_Info>User_Bak表與User表結(jié)構(gòu)相同，參考本章教材上user.sql腳本

?用repeat循環(huán)語句實現(xiàn)

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十五講

教學周第8周節(jié)次第29、30節(jié)

章節(jié)內(nèi)容存儲過程流程控制-注釋語句與函數(shù)

授課方式理論課（J）討論課（）實驗課（J）習題課（）其他（）

主要內(nèi)容與教學目的：

1、注釋語句

2、數(shù)據(jù)庫函數(shù)

重點：

注釋語句

難點：

靈活運用數(shù)據(jù)庫函數(shù)

課堂教學組織：

1、注釋語句

存儲過程(MySQL)有兩種風格的注釋形式，如下：

?雙模杠：

+空格

該風格一般用于單行注釋

如：—DECLAREmypassCHAR(IO);

?編程風格：

/*...*/

一般用于多行注釋

如：/*SETmystr='birds';*/

2、數(shù)據(jù)庫函數(shù)

開發(fā)人員常用使用的數(shù)據(jù)庫函數(shù)有三種類型：字符串類型函數(shù)、數(shù)學類型函數(shù)、日期類

型函數(shù)

?字符串類型函數(shù)

LCASE(string)

轉(zhuǎn)換成小寫

UCASE(string)

轉(zhuǎn)換成大寫

REPLACE(str,search_str,replace_str)

在str中用replace_str替換search_str

SUBSTRING(str,pos,length)

從str的pos位置開始，取length個字符,pos從1開始

?數(shù)學類函數(shù)

ABS(number)

絕對值

MOD(numeratordenominator)

求余

SQRT(number)

開平方

RAND()

隨機數(shù)

?日期類函數(shù)

CURRENT_DATE()

取得當前日期，只包含：年、月、日

CURRENT_TIME()

取得當前時間，只包含：時、分、秒

NOW()

取得當前時間，包含：年、月、日、時、分、秒

擬提問題:

存儲過程的注釋語句與編程語言中的注釋語句有何異同？

課后作業(yè):

完成教材P59練習題

注：每兩節(jié)課(90分鐘)為一講，每講必須填寫此表。

第十六講

教學周第8周節(jié)次第31、32節(jié)

章節(jié)內(nèi)容存儲過程-游標

授課方式理論課(J)討論課()實驗課(J)習題課()其他()

主要內(nèi)容與教學目的：

1、游標的作用

2、游標的屬性

3、定義游標

4、使用游標

重點：

游標的使用過程

難點：

NOTFOUND條件處理函數(shù)

課堂教學組織：

1、游標的作用及屬性

?游標的作用是：

對查詢數(shù)據(jù)庫所返回的記錄進行遍歷

?游標如下屬性：

游標是只讀的，也就是不能更新它

游標是不能滾動的，也就是只能在一個方向上進行遍歷，不能在記錄之間隨意進退，

不能跳過某些記錄

避免在已經(jīng)打開游標的表上更新數(shù)據(jù)

2、聲明游標

?語法格式：DECLARE+游標名+CURSORFOR+SELECT語句

DECLARE為聲明游標的關(guān)鍵字，后面跟游標的名稱，CURSORFOR表示游標要操作

的數(shù)據(jù)結(jié)果集，后面跟一條數(shù)據(jù)庫的SELECT檢索語句。例如，下面語句聲明了一個

叫mycoursor的游標：

DECLAREmycoursorCURSORFORselectuser_name,passwordfromuser

3、打開游標

?語法格式：OPEN+游標名

OPEN為打開游標的關(guān)鍵字，在使用游標前，先要把游標打開，才能進行其它的操作。

例如，下面語句打開了一個叫mycoursor的游標：

OPENmycoursor

4、操作游標

?語法格式：FETCH+游標名+INTO+變量列表

FETCH為移動游標的關(guān)鍵字，每調(diào)用一次，游標指針往下移動一條記錄。FETCH后

面跟游標的名稱，表示要操作的是那個游標，關(guān)鍵字INTO表示把取得數(shù)據(jù)裝到本地,

后面跟一系列的本地變量列表。例如，下面的語句表示把mycoursor游標的一行記錄

裝進my_name、my_pwd變量，my_name、my_pwd兩個變量需提前定義好。

FETCHmycoursorINTOmy_name,my_pwd

5、關(guān)閉游標

?語法格式：CLOSE+游標名

CLOSE為關(guān)閉游標的關(guān)鍵字，后面跟游標的名稱，在游標使用完畢后，需要把游標關(guān)

閉，以釋放游標所占據(jù)的資源。例如，下面語句關(guān)閉了一個叫mycoursor的游標：

6、NOTFOUND條件處理函數(shù)

為了避免游標到達記錄的末尾時避免出現(xiàn)“nodatatofetch”這樣的錯誤，需要定義一

個：NOTFOUND條件處理函數(shù)。

?語句格式：

DECLARECONTINUEHANDLERFORNOTFOUND+需要執(zhí)行的語句

擬提問題:

使用游標有什么利弊？

課后作業(yè):

參考本章教材的student.sql腳本創(chuàng)建表環(huán)境，然后按要求實現(xiàn)以下功能：

?用存儲過程及游標為student_info表統(tǒng)計各個學生總分（total）字段值

?總分=數(shù)學成績+語文成績+音樂成績+歷史成績

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十七講

教學周第9周節(jié)次第33、34節(jié)

章節(jié)內(nèi)容存儲過程-實驗課

授課方式理論課（）討論課（）實驗課（J）習題課（J）其他（）

主要內(nèi)容與教學目的：

1、參數(shù)類型

2、變量定義

3、條件選擇分支結(jié)構(gòu)

4、循環(huán)控制結(jié)構(gòu)

重點：

條件選擇分支結(jié)構(gòu)

循環(huán)控制結(jié)構(gòu)

難點：

SQL語句的熟練運用

課堂教學組織:

1、任務(wù)一

參考教材orderl.sqkorder2.sql腳本創(chuàng)建數(shù)據(jù)表環(huán)境，用存儲過程對商品銷售明細表

(order_detail)作如下統(tǒng)計操作：

?按月統(tǒng)計出7、8月份的銷售金額

?把數(shù)據(jù)匯總到月銷售匯總表(order_month)

?按商品類型統(tǒng)計出各種類型商品銷售金額

?把數(shù)據(jù)匯總到類型銷售匯總表(order_total_type)

?按月份及商品類型統(tǒng)計出7、8月份各種類型商品銷售金額

?把數(shù)據(jù)匯總到月類型銷售匯總表(order_total_type_month)

數(shù)據(jù)遷移參考語句：

insertintonew_table(columnl,column2)selectcolumn1,column?fromold_table

2、任務(wù)二

用存儲過程實現(xiàn)以下功能

?創(chuàng)建一個業(yè)務(wù)表

/創(chuàng)建表前先判斷此表是否存在，如果存在先刪除

/字段滿足如下要求

/ID自增

/業(yè)務(wù)名稱字段為字符串類型

/訂閱時間字段為日期類型

/收費方式字段（0：按流量收取、1：按小時收、2:按日收、3:按月收、4:按年收）

/是否生效字段（0:未生效、1:已生效）

?用循環(huán)往業(yè)務(wù)表插入1000條記錄

?按ID字段排序（升序）檢索出251-300行業(yè)務(wù)數(shù)據(jù)

/收費方式字段顯示

/“按流量收取”或“按小時收取”或“按日收取”或“按年收取”

/是否生效字段顯示

/“未生效”或“已生效”

3、任務(wù)三

參考教材course.sql腳本創(chuàng)建表環(huán)境，然后按要求實現(xiàn)以下功能：

?用存儲過程統(tǒng)計各個學生是否達到最低修讀要求：

/若course_student表中成績字段（course_score）大于60分，則學生取得該門課

程學分

,每門課程的學分（credit）在course表中可查詢到

/若某學生的所取得的全部學分大于7分，則該學生達到最低修學要求，在

student表的mark字段標注“合格”，否則標注“不合格”

擬提問題：

無

課后作業(yè)：

自己動手，在數(shù)據(jù)庫客戶端逐一創(chuàng)建、運行本次實驗課的每個例子。

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十八講

教學周第9周節(jié)次第35、36節(jié)

章節(jié)內(nèi)容觸發(fā)器基本語法（1）

授課方式理論課（J）討論課（）實驗課（）習題課（）其他（）

主要內(nèi)容與教學目的：

1、觸發(fā)器作用

2、觸發(fā)器原理

3、觸發(fā)器語法

重點：

觸發(fā)器語法

難點：

觸發(fā)器臨時表

課堂教學組織:

I、觸發(fā)器作用

?數(shù)據(jù)安全

/可以以基于時間限制用戶的操作

/可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作

?數(shù)據(jù)審計

/審計用戶操作數(shù)據(jù)庫的語句

/把用戶對數(shù)據(jù)庫的更新寫入審計表

?數(shù)據(jù)約束

/實現(xiàn)數(shù)據(jù)完整性檢查和約束

/提供可變的缺省值

?數(shù)據(jù)連環(huán)更新

/修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行

/修改或刪除時把其它表中與之匹配的行設(shè)成NULL值

/修改或刪除時把其它表中與之匹配的行級聯(lián)設(shè)成缺省值

2、觸發(fā)器原理

觸發(fā)器具有強大的功能，每個觸發(fā)器有兩個特殊的表，分別為：插入表和刪除表，有

以下幾個特點：

(1)這兩個表是邏輯表，并且這兩個表是由系統(tǒng)管理的，存儲在內(nèi)存中，不是存儲在

數(shù)據(jù)庫中，因此不允許用戶直接對其修改。

(2)這兩個表的結(jié)構(gòu)總是與被該觸發(fā)器作用的表有相同的表結(jié)構(gòu)。

(3)這兩個表是動態(tài)駐留在內(nèi)存中的，當觸發(fā)器工作完成，這兩個表也被刪除。這兩

個表主要保存因用戶操作而被影響到的原數(shù)據(jù)值或新數(shù)據(jù)值。

(4)這兩個表是只讀的，且只在觸發(fā)器內(nèi)部可讀，即用戶不能向這兩個表寫入內(nèi)容，

但可以在觸發(fā)器中引用表中的數(shù)據(jù)。

3、觸發(fā)器語法：

?創(chuàng)建語法格式：

CREATETRIGGER+觸發(fā)器名稱+觸發(fā)時間點+觸發(fā)事件+ON+表名+FOR

EACHROW

BEGIN

END

觸發(fā)時間點：

BEFORE或AFTER

指明是在觸發(fā)事件之前還是之后執(zhí)行

觸發(fā)事件：

INSERT,UPDATE、DELETE事件

如，以下語句創(chuàng)建一個名字叫：upd_check的觸發(fā)器，其在對account表作更新

(UPDATE)操作之前(BEFORE)自動觸發(fā)。

CREATETRIGGERupd.checkBEFOREUPDATEONaccountFOREACHROW

BEGIN

END

?刪除語法格式：

DROPTRIGGER+觸發(fā)器名稱

如，以下語句刪除了一個名字叫：upd_check的觸發(fā)器。

DROPTRIGGERupd_check

?觸發(fā)器案例

當往tabi表添加記錄后將解發(fā)此觸發(fā)器，將此新記錄同時插入到tab2表中。

DELIMITER//

DROPTRIGGERIFEXISTSt_afterinsert_on_tabl;

CREATETRIGGERt_afterinsert_on_tab1

AFTERINSERTONtabiFOREACHROW

BEGIN

insertintotab2（tab2_id）values（new.tabl_id）;

END;

//

DELIMITER;

擬提問題：

觸發(fā)器與存儲過程有什么異同？

課后作業(yè)：

完成教材P79練習題

注：每兩節(jié)課（90分鐘）為一講，每講必須填寫此表。

第十九講

教學周第10周節(jié)