講稿優(yōu)化myschool數(shù)據(jù)庫(kù)設(shè)計(jì)tp09

1、項(xiàng)目案例：銀行ATM存取款機(jī)系統(tǒng)第九章訓(xùn)練的技能點(diǎn)訓(xùn)練的技能點(diǎn)使用使用T-SQL語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)和表結(jié)構(gòu)語(yǔ)句創(chuàng)建數(shù)據(jù)庫(kù)和表結(jié)構(gòu)使用使用T-SQL語(yǔ)句編程實(shí)現(xiàn)用戶(hù)業(yè)務(wù)語(yǔ)句編程實(shí)現(xiàn)用戶(hù)業(yè)務(wù)使用事務(wù)和存儲(chǔ)過(guò)程封裝業(yè)務(wù)邏輯使用事務(wù)和存儲(chǔ)過(guò)程封裝業(yè)務(wù)邏輯使用視圖簡(jiǎn)化復(fù)雜的數(shù)據(jù)查詢(xún)使用視圖簡(jiǎn)化復(fù)雜的數(shù)據(jù)查詢(xún)2任務(wù)描述任務(wù)描述系統(tǒng)功能概述系統(tǒng)功能概述系統(tǒng)概述系統(tǒng)概述開(kāi)戶(hù)開(kāi)戶(hù)填寫(xiě)開(kāi)戶(hù)申請(qǐng)單填寫(xiě)開(kāi)戶(hù)申請(qǐng)單自動(dòng)開(kāi)通賬戶(hù)自動(dòng)開(kāi)通賬戶(hù)存取款存取款查詢(xún)余額查詢(xún)余額轉(zhuǎn)賬轉(zhuǎn)賬演示案例：銀行演示案例：銀行ATM存取款機(jī)系統(tǒng)存取款機(jī)系統(tǒng)3系統(tǒng)開(kāi)發(fā)步驟系統(tǒng)開(kāi)發(fā)步驟問(wèn)題分析：整體開(kāi)發(fā)思路問(wèn)題分析：整體開(kāi)發(fā)思路2-1明確需求明確需求數(shù)

2、據(jù)庫(kù)設(shè)數(shù)據(jù)庫(kù)設(shè)計(jì)計(jì)編碼實(shí)現(xiàn)編碼實(shí)現(xiàn)功能功能測(cè)試測(cè)試?yán)L制繪制E-R圖圖繪制數(shù)據(jù)庫(kù)模型繪制數(shù)據(jù)庫(kù)模型圖圖使用三大范式規(guī)使用三大范式規(guī)范數(shù)據(jù)庫(kù)結(jié)構(gòu)范數(shù)據(jù)庫(kù)結(jié)構(gòu)4問(wèn)題分析：整體開(kāi)發(fā)思路問(wèn)題分析：整體開(kāi)發(fā)思路2-2銀行業(yè)務(wù)處理銀行業(yè)務(wù)處理每個(gè)用戶(hù)可以開(kāi)設(shè)多個(gè)銀行卡賬戶(hù)每個(gè)用戶(hù)可以開(kāi)設(shè)多個(gè)銀行卡賬戶(hù)每個(gè)銀行卡賬戶(hù)可以有多筆交易每個(gè)銀行卡賬戶(hù)可以有多筆交易設(shè)計(jì)符合第三范式的數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)符合第三范式的數(shù)據(jù)庫(kù)結(jié)構(gòu)根據(jù)銀行業(yè)務(wù)繪制根據(jù)銀行業(yè)務(wù)繪制E-R圖圖繪制數(shù)據(jù)庫(kù)模型圖繪制數(shù)據(jù)庫(kù)模型圖使用三大范式規(guī)范數(shù)據(jù)庫(kù)結(jié)構(gòu)使用三大范式規(guī)范數(shù)據(jù)庫(kù)結(jié)構(gòu)實(shí)現(xiàn)實(shí)現(xiàn)“ATM取款機(jī)系統(tǒng)取款機(jī)系統(tǒng)”的相關(guān)業(yè)務(wù)的相關(guān)業(yè)務(wù)5難點(diǎn)分析難點(diǎn)分

3、析1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)數(shù)據(jù)庫(kù)設(shè)計(jì)步驟數(shù)據(jù)庫(kù)設(shè)計(jì)步驟E-R圖要素圖要素實(shí)體實(shí)體關(guān)系關(guān)系一對(duì)一關(guān)系一對(duì)一關(guān)系一對(duì)多關(guān)系一對(duì)多關(guān)系多對(duì)多關(guān)系多對(duì)多關(guān)系數(shù)據(jù)庫(kù)模型圖數(shù)據(jù)庫(kù)模型圖數(shù)據(jù)庫(kù)設(shè)計(jì)的三大規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)的三大規(guī)范第一范式第一范式第二范式第二范式第三范式第三范式6難點(diǎn)分析難點(diǎn)分析2：理解業(yè)務(wù)：理解業(yè)務(wù)ATM取款業(yè)務(wù)取款業(yè)務(wù)卡號(hào)構(gòu)成卡號(hào)構(gòu)成假設(shè)某行由假設(shè)某行由16位數(shù)字構(gòu)成，每位數(shù)字構(gòu)成，每4位數(shù)字一組，位數(shù)字一組，中間用空格隔開(kāi)中間用空格隔開(kāi)如：如：1010 3576 1001 1202前前8位數(shù)字固定的，表示發(fā)卡銀行識(shí)別號(hào)（位數(shù)字固定的，表示發(fā)卡銀行識(shí)別號(hào)（BIN）前前8位是位是1010 3

4、576后后8位中前位中前7位是開(kāi)戶(hù)順序號(hào)，后位是開(kāi)戶(hù)順序號(hào)，后1位是校驗(yàn)位位是校驗(yàn)位開(kāi)戶(hù)順序號(hào)從開(kāi)戶(hù)順序號(hào)從1開(kāi)始，每開(kāi)一戶(hù)，該值加一開(kāi)始，每開(kāi)一戶(hù)，該值加一校驗(yàn)位算法校驗(yàn)位算法以以8位隨機(jī)數(shù)替代卡號(hào)的后位隨機(jī)數(shù)替代卡號(hào)的后8位數(shù)字位數(shù)字客戶(hù)用身份證開(kāi)設(shè)賬戶(hù)客戶(hù)用身份證開(kāi)設(shè)賬戶(hù)開(kāi)戶(hù)金額不得小于開(kāi)戶(hù)金額不得小于1元元存款余額不得小于存款余額不得小于1元元7難點(diǎn)分析難點(diǎn)分析3：理解業(yè)務(wù)：理解業(yè)務(wù)ATM取款業(yè)務(wù)取款業(yè)務(wù)存款存款記錄交易發(fā)生額，增加賬戶(hù)余額記錄交易發(fā)生額，增加賬戶(hù)余額取款取款記錄交易發(fā)生額，減少賬戶(hù)余額記錄交易發(fā)生額，減少賬戶(hù)余額轉(zhuǎn)賬轉(zhuǎn)賬關(guān)系到二個(gè)賬戶(hù)，總賬平衡關(guān)系到二個(gè)賬戶(hù)，總賬平衡

5、轉(zhuǎn)出賬戶(hù)：記錄交易轉(zhuǎn)入金額，減少該賬戶(hù)轉(zhuǎn)出賬戶(hù)：記錄交易轉(zhuǎn)入金額，減少該賬戶(hù)的余額的余額轉(zhuǎn)入賬戶(hù)：記錄交易轉(zhuǎn)出金額（同轉(zhuǎn)入金額轉(zhuǎn)入賬戶(hù)：記錄交易轉(zhuǎn)出金額（同轉(zhuǎn)入金額），增加該賬戶(hù)余額），增加該賬戶(hù)余額8難點(diǎn)分析難點(diǎn)分析4：來(lái)理解業(yè)務(wù)：來(lái)理解業(yè)務(wù)ATM取款業(yè)務(wù)取款業(yè)務(wù)修改密碼修改密碼變更客戶(hù)密碼變更客戶(hù)密碼掛失掛失客戶(hù)填寫(xiě)特殊業(yè)務(wù)申請(qǐng)單（掛失申請(qǐng)書(shū)），客戶(hù)填寫(xiě)特殊業(yè)務(wù)申請(qǐng)單（掛失申請(qǐng)書(shū)），停止該賬戶(hù)發(fā)生存款支取交易停止該賬戶(hù)發(fā)生存款支取交易在客戶(hù)信息中設(shè)置列記錄客戶(hù)當(dāng)前賬戶(hù)狀態(tài)在客戶(hù)信息中設(shè)置列記錄客戶(hù)當(dāng)前賬戶(hù)狀態(tài)9開(kāi)發(fā)計(jì)劃開(kāi)發(fā)計(jì)劃用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì) 40分鐘分鐘用例用例2：建庫(kù)

6、、建表、建約束：建庫(kù)、建表、建約束 20分鐘分鐘用例用例3：插入測(cè)試數(shù)據(jù)：插入測(cè)試數(shù)據(jù) 10分鐘分鐘用例用例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù) 30分鐘分鐘用例用例5：利用視圖實(shí)現(xiàn)較復(fù)雜的數(shù)據(jù)查詢(xún)：利用視圖實(shí)現(xiàn)較復(fù)雜的數(shù)據(jù)查詢(xún) 30分鐘分鐘用例用例6：使用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：使用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理 90分鐘分鐘用例用例7：利用事務(wù)實(shí)現(xiàn)較復(fù)雜的數(shù)據(jù)更新：利用事務(wù)實(shí)現(xiàn)較復(fù)雜的數(shù)據(jù)更新 30分鐘分鐘10用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-1 需求說(shuō)明需求說(shuō)明根據(jù)銀行根據(jù)銀行ATM取款機(jī)業(yè)務(wù)處理流程設(shè)計(jì)數(shù)據(jù)取款機(jī)業(yè)務(wù)處理流程設(shè)計(jì)數(shù)據(jù)庫(kù)庫(kù)繪制繪制E-R圖圖繪制數(shù)據(jù)庫(kù)模型圖繪制數(shù)據(jù)庫(kù)模型圖使用第三范式規(guī)

7、范數(shù)據(jù)庫(kù)表結(jié)構(gòu)使用第三范式規(guī)范數(shù)據(jù)庫(kù)表結(jié)構(gòu)思路分析思路分析按照數(shù)據(jù)庫(kù)設(shè)計(jì)步驟確定實(shí)體及實(shí)體關(guān)系按照數(shù)據(jù)庫(kù)設(shè)計(jì)步驟確定實(shí)體及實(shí)體關(guān)系使用使用Visio繪制繪制E-R圖圖使用使用Visio繪制數(shù)據(jù)庫(kù)模型圖繪制數(shù)據(jù)庫(kù)模型圖使用第三范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)使用第三范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)講解需求說(shuō)明講解需求說(shuō)明11用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-2 難點(diǎn)提示難點(diǎn)提示尋找、確定尋找、確定ATM取款機(jī)系統(tǒng)的實(shí)體及實(shí)體間取款機(jī)系統(tǒng)的實(shí)體及實(shí)體間關(guān)系關(guān)系根據(jù)業(yè)務(wù)確定實(shí)體根據(jù)業(yè)務(wù)確定實(shí)體確定實(shí)體之間關(guān)系確定實(shí)體之間關(guān)系使用三大范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)使用三大范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)表內(nèi)的每一個(gè)值都只能被表達(dá)一次表內(nèi)的每一個(gè)值都只

8、能被表達(dá)一次表內(nèi)的每一行都應(yīng)該被唯一的標(biāo)識(shí)（主鍵）表內(nèi)的每一行都應(yīng)該被唯一的標(biāo)識(shí)（主鍵）表內(nèi)不應(yīng)該存儲(chǔ)依賴(lài)于其他鍵的非鍵信息表內(nèi)不應(yīng)該存儲(chǔ)依賴(lài)于其他鍵的非鍵信息完成時(shí)間：完成時(shí)間：40分鐘分鐘12用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-3提交設(shè)計(jì)文檔提交設(shè)計(jì)文檔小組討論確定實(shí)體及實(shí)體關(guān)系（小組討論確定實(shí)體及實(shí)體關(guān)系（10分鐘）分鐘）繪制繪制E-R圖（圖（10分鐘）分鐘）繪制數(shù)據(jù)庫(kù)模型圖（繪制數(shù)據(jù)庫(kù)模型圖（10分鐘）分鐘）使用第三范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)（使用第三范式規(guī)范數(shù)據(jù)庫(kù)設(shè)計(jì)（10分鐘）分鐘）要求要求互相驗(yàn)證完成的程序功能互相驗(yàn)證完成的程序功能小組長(zhǎng)檢查本組完成情況小組長(zhǎng)檢查本組完成情況功能測(cè)試功

9、能測(cè)試13用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-4繪制繪制ER圖、數(shù)據(jù)庫(kù)模型圖、規(guī)圖、數(shù)據(jù)庫(kù)模型圖、規(guī)范數(shù)據(jù)庫(kù)表結(jié)構(gòu)范數(shù)據(jù)庫(kù)表結(jié)構(gòu)14客戶(hù)信息表結(jié)構(gòu)（客戶(hù)信息表結(jié)構(gòu)（UserInfo） 列名稱(chēng)列名稱(chēng)數(shù)據(jù)數(shù)據(jù)類(lèi)型類(lèi)型說(shuō)明customerIDint客戶(hù)編號(hào)客戶(hù)編號(hào)自動(dòng)編號(hào)（標(biāo)識(shí)列），從自動(dòng)編號(hào)（標(biāo)識(shí)列），從1開(kāi)始，主鍵開(kāi)始，主鍵customerNamevarchar開(kāi)戶(hù)名開(kāi)戶(hù)名必填必填PIDvarchar身份證號(hào)身份證號(hào)必填，只能是必填，只能是18位或位或15位，位，身份證號(hào)唯一約束身份證號(hào)唯一約束telephonevarchar聯(lián)系電話(huà)聯(lián)系電話(huà)必填，格式為必填，格式為xxxx-xxxxxxxx或

10、或xxx-xxxxxxxx或手機(jī)號(hào)或手機(jī)號(hào)11位位addressvarchar居住地址居住地址可選輸入可選輸入用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-515列名稱(chēng)列名稱(chēng)數(shù)據(jù)數(shù)據(jù)類(lèi)型類(lèi)型說(shuō)明說(shuō)明cardIDchar卡號(hào)卡號(hào)必填，主鍵，銀行的卡號(hào)規(guī)則和電話(huà)號(hào)碼一樣，必填，主鍵，銀行的卡號(hào)規(guī)則和電話(huà)號(hào)碼一樣，一般前一般前8位代表特殊含義，如某總行某支行等。位代表特殊含義，如某總行某支行等。假定該行要求其營(yíng)業(yè)廳的卡號(hào)格式為：假定該行要求其營(yíng)業(yè)廳的卡號(hào)格式為：1010 3576 xxxx xxxx。每。每4位號(hào)碼后有空格，卡號(hào)一位號(hào)碼后有空格，卡號(hào)一般是隨機(jī)產(chǎn)生般是隨機(jī)產(chǎn)生curIDvarchar貨幣種類(lèi)

11、貨幣種類(lèi)外鍵，必填，默認(rèn)為外鍵，必填，默認(rèn)為RMBsavingIDvarchar存款類(lèi)型存款類(lèi)型外鍵，必填外鍵，必填openDatedatetime開(kāi)戶(hù)日期開(kāi)戶(hù)日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期openMoneydecimal開(kāi)戶(hù)金額開(kāi)戶(hù)金額必填，不低于必填，不低于1元元balancedecimal余額余額必填，不低于必填，不低于1元元passwordvarchar密碼密碼必填，必填，6位數(shù)字，開(kāi)戶(hù)時(shí)默認(rèn)為位數(shù)字，開(kāi)戶(hù)時(shí)默認(rèn)為6個(gè)個(gè)“8”IsReportLoss bit是否掛失是否掛失必填，是必填，是/否值，默認(rèn)為否值，默認(rèn)為“否否”customerIDint客戶(hù)編號(hào)客戶(hù)編號(hào)

12、外鍵，必填外鍵，必填銀行卡信息表結(jié)構(gòu)（銀行卡信息表結(jié)構(gòu)（CardInfo） 用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-616交易信息表結(jié)構(gòu)交易信息表結(jié)構(gòu) （TradeInfo）用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-7字段名稱(chēng)字段名稱(chēng)數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型說(shuō)明transDatedatetime交易日期交易日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期必填，默認(rèn)為系統(tǒng)當(dāng)前日期cardIDvarchar卡號(hào)卡號(hào)外鍵，必填外鍵，必填transTypeint交易類(lèi)型交易類(lèi)型必填，只能是存入必填，只能是存入/支取支取transMoneydecimal交易金額交易金額必填，大于必填，大于0remarkvarchar備注備注可選輸入，其

13、他說(shuō)明可選輸入，其他說(shuō)明17存款類(lèi)型表結(jié)構(gòu)（存款類(lèi)型表結(jié)構(gòu)（Deposit） 用例用例1：數(shù)據(jù)庫(kù)設(shè)計(jì)：數(shù)據(jù)庫(kù)設(shè)計(jì)8-8字段名稱(chēng)字段名稱(chēng)數(shù)據(jù)數(shù)據(jù)類(lèi)型類(lèi)型說(shuō)明savingIDint存款類(lèi)型號(hào)存款類(lèi)型號(hào)自動(dòng)編號(hào)（標(biāo)識(shí)列）從自動(dòng)編號(hào)（標(biāo)識(shí)列）從1 1開(kāi)始，主鍵開(kāi)始，主鍵savingNamevarchar存款類(lèi)型名稱(chēng)存款類(lèi)型名稱(chēng)必填必填descripvarchar描述描述可空可空18共性問(wèn)題集中講解共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題代碼規(guī)范問(wèn)題調(diào)試技巧調(diào)試技巧19用例用例2：建庫(kù)、建表、建約束：建庫(kù)、建表、建約束4-1 需求說(shuō)明需求說(shuō)明使用使用SQL語(yǔ)言創(chuàng)建數(shù)據(jù)庫(kù)語(yǔ)言創(chuàng)建數(shù)

14、據(jù)庫(kù)BankDB數(shù)據(jù)庫(kù)保存在數(shù)據(jù)庫(kù)保存在D:bank目錄下，文件增長(zhǎng)率為目錄下，文件增長(zhǎng)率為15%使用使用SQL語(yǔ)言創(chuàng)建表語(yǔ)言創(chuàng)建表存款類(lèi)型表存款類(lèi)型表客戶(hù)信息表客戶(hù)信息表銀行卡信息表銀行卡信息表交易信息表交易信息表使用使用SQL語(yǔ)言在每個(gè)表上添加約束語(yǔ)言在每個(gè)表上添加約束主鍵約束、外鍵約束、主鍵約束、外鍵約束、CHECK約束、默認(rèn)約約束、默認(rèn)約束、束、非空約束非空約束講解需求說(shuō)明講解需求說(shuō)明20用例用例2：建庫(kù)、建表、建約束：建庫(kù)、建表、建約束4-2 建庫(kù)與建表建庫(kù)與建表-建庫(kù)語(yǔ)句：建庫(kù)語(yǔ)句：CREATE DATABASE bankDB ON ( NAME=, FILENAME=, SIZE

15、=, FILEGROWTH= ) LOG ON ( )-建表語(yǔ)句：建表語(yǔ)句：CREATE TABLE 表名表名 ( customerID INT IDENTITY(1,1), customerName CHAR(8) NOT NULL, )文件增長(zhǎng)率文件增長(zhǎng)率數(shù)據(jù)文件數(shù)據(jù)文件日志文件日志文件自動(dòng)編號(hào)，從自動(dòng)編號(hào)，從1開(kāi)始開(kāi)始非空非空/必填必填完成時(shí)間：完成時(shí)間：10分鐘分鐘21用例用例2：建庫(kù)、建表、建約束：建庫(kù)、建表、建約束4-3加約束、建關(guān)系部分加約束、建關(guān)系部分 -建約束語(yǔ)句：建約束語(yǔ)句：ALTER TABLE cardInfo ADD CONSTRAINT PK_cardID PRIM

16、ARY KEY(cardID), CONSTRAINT CK_cardID CHECK(cardID LIKE 1010 3576 0-9), CONSTRAINT DF_curType DEFAULT(RMB) FOR curType CONSTRAINT FK_customerID FOREIGN KEY(customerID) REFERENCES userInfo(customerID), CONSTRAINT UQ_PID UNIQUE(PID), .主鍵約束主鍵約束檢查約束檢查約束外鍵約束（建關(guān)系）外鍵約束（建關(guān)系）唯一約束唯一約束默認(rèn)約束默認(rèn)約束完成時(shí)間：完成時(shí)間：10分鐘分鐘2

17、2共性問(wèn)題集中講解共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題代碼規(guī)范問(wèn)題調(diào)試技巧調(diào)試技巧23用例用例2：建庫(kù)、建表、建約束：建庫(kù)、建表、建約束4-4代碼審查代碼審查審查創(chuàng)建數(shù)據(jù)庫(kù)、表的審查創(chuàng)建數(shù)據(jù)庫(kù)、表的SQL代碼代碼審查添加約束的審查添加約束的SQL代碼代碼要求要求互相驗(yàn)證完成的程序功能互相驗(yàn)證完成的程序功能小組長(zhǎng)檢查本組完成情況小組長(zhǎng)檢查本組完成情況功能測(cè)試功能測(cè)試24用例用例3：插入測(cè)試數(shù)據(jù)：插入測(cè)試數(shù)據(jù)4-1 需求說(shuō)明需求說(shuō)明使用使用SQL語(yǔ)言向每個(gè)表中插入至少語(yǔ)言向每個(gè)表中插入至少5條記錄條記錄思路分析思路分析先增加主表的記錄，后增加子表的記錄先增加主表的記錄，

18、后增加子表的記錄難點(diǎn)提示難點(diǎn)提示插入數(shù)據(jù)表的先后順序插入數(shù)據(jù)表的先后順序插入客戶(hù)記錄時(shí)，先要查詢(xún)插入客戶(hù)記錄時(shí)，先要查詢(xún)完成時(shí)間：完成時(shí)間：10分鐘分鐘25用例用例3：插入測(cè)試數(shù)據(jù)：插入測(cè)試數(shù)據(jù)4-2張三和李四開(kāi)戶(hù)：張三和李四開(kāi)戶(hù)：SET NOCOUNT ON -不顯示受影響的條數(shù)信息不顯示受影響的條數(shù)信息INSERT INTO userInfo(customerName,PID,telephone,address ) VALUES(張三張三,123456789012345, ,北京海淀北京海淀)INSERT INTO cardInfo(cardID,savingType,openMoney

19、,balance,customerID) VALUES(1010 3576 1234 5678, 活期活期,1000,1000,1)INSERT INTO userInfo(customerName,PID,telephone) VALUES(李四李四, , )INSERT INTO cardInfo(cardID,savingType,openMoney,balance, customerID) VALUES(1010 3576 1212 1134,定期定期,1,1,2)SELECT * FROM userInfoSELECT * FROM cardInfoGO26用例用例3：插入測(cè)試數(shù)據(jù)：

20、插入測(cè)試數(shù)據(jù)4-3張三的卡號(hào)取款張三的卡號(hào)取款900元，李四的卡號(hào)存款元，李四的卡號(hào)存款5000元元/*-交易信息表插入交易記錄交易信息表插入交易記錄-*/INSERT INTO transInfo(transType,cardID,transMoney) VALUES(支取支取,1010 3576 1234 5678,900) /*-更新銀行卡信息表中的現(xiàn)有余額更新銀行卡信息表中的現(xiàn)有余額-*/UPDATE cardInfo SET balance=balance-900 WHERE cardID=1010 3576 1234 5678/*-交易信息表插入交易記錄交易信息表插入交易記錄-*/

21、INSERT INTO transInfo(transType,cardID,transMoney) VALUES(存入存入,1010 3576 1212 1134,5000) /*-更新銀行卡信息表中的現(xiàn)有余額更新銀行卡信息表中的現(xiàn)有余額-*/UPDATE cardInfo SET balance=balance WHERE cardID=1010 3576 1212 1134GO27用例用例3：插入測(cè)試數(shù)據(jù)：插入測(cè)試數(shù)據(jù)4-4功能測(cè)試功能測(cè)試使用查詢(xún)語(yǔ)句測(cè)試插入的數(shù)據(jù)是否正確（使用查詢(xún)語(yǔ)句測(cè)試插入的數(shù)據(jù)是否正確（5分分鐘）鐘）代碼審查代碼審查審查插入測(cè)試數(shù)據(jù)的審查插入測(cè)試數(shù)據(jù)的SQL代碼（

22、代碼（5分鐘）分鐘）要求要求互相驗(yàn)證完成的程序功能互相驗(yàn)證完成的程序功能小組長(zhǎng)檢查本組完成情況小組長(zhǎng)檢查本組完成情況功能測(cè)試功能測(cè)試演示案例演示案例3：插入測(cè)試數(shù)據(jù)：插入測(cè)試數(shù)據(jù)28用例用例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù)4-1需求說(shuō)明需求說(shuō)明修改客戶(hù)密碼修改客戶(hù)密碼辦理銀行卡掛失辦理銀行卡掛失統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算銀行資金流通余額銀行資金流通余額=總存入金額總存入金額-總支取金額總支取金額盈利結(jié)算盈利結(jié)算=總支取金額總支取金額 * 0.008 總存入金額總存入金額 * 0.003查詢(xún)本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信息查詢(xún)本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信息查詢(xún)本

23、月交易金額最高的卡號(hào)查詢(xún)本月交易金額最高的卡號(hào)查詢(xún)掛失賬號(hào)的客戶(hù)信息查詢(xún)掛失賬號(hào)的客戶(hù)信息催款提醒業(yè)務(wù)催款提醒業(yè)務(wù)講解需求說(shuō)明講解需求說(shuō)明29用例用例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù)4-2思路分析思路分析修改客戶(hù)密碼修改客戶(hù)密碼辦理銀行卡掛失辦理銀行卡掛失使用使用UPDATE語(yǔ)句實(shí)現(xiàn)密碼變更和卡掛失語(yǔ)句實(shí)現(xiàn)密碼變更和卡掛失UPDATE WHERE 統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算使用聚合函數(shù)使用聚合函數(shù)SUM()和數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù)和數(shù)據(jù)類(lèi)型轉(zhuǎn)換函數(shù)CONVERT()SELECT inMoney=SUM() FROM WHERE 查詢(xún)本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信息查詢(xún)

24、本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信息日期函數(shù)日期函數(shù)DATEDIFF()和和DATEPART()完成時(shí)間：完成時(shí)間：15分鐘分鐘30用例用例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù)4-3思路分析思路分析查詢(xún)本月交易金額最高的卡號(hào)查詢(xún)本月交易金額最高的卡號(hào)使用子查詢(xún)和使用子查詢(xún)和DISTINCT關(guān)鍵字去掉重復(fù)的卡關(guān)鍵字去掉重復(fù)的卡號(hào)號(hào)SELECT FROM transInfo WHERE transMoney = ( SELECT FROM )查詢(xún)掛失賬號(hào)的客戶(hù)信息查詢(xún)掛失賬號(hào)的客戶(hù)信息使用子查詢(xún)使用子查詢(xún)IN 或內(nèi)聯(lián)接查詢(xún)或內(nèi)聯(lián)接查詢(xún)INNER JOINSELECT FROM userInfo WHERE

25、customerID IN ( SELECT FROM )催款提醒業(yè)務(wù)催款提醒業(yè)務(wù)使用子查詢(xún)使用子查詢(xún)IN 或內(nèi)聯(lián)接查詢(xún)或內(nèi)聯(lián)接查詢(xún)INNER JOINSELECT FROM userInfo INNER JOIN 完成時(shí)間：完成時(shí)間：15分鐘分鐘31用例用例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù)4-4功能測(cè)試功能測(cè)試檢查點(diǎn)檢查點(diǎn) （5分鐘）分鐘）正確修改客戶(hù)密碼正確修改客戶(hù)密碼正確辦理銀行卡掛失，查詢(xún)獲得掛失賬號(hào)的正確辦理銀行卡掛失，查詢(xún)獲得掛失賬號(hào)的客戶(hù)信息客戶(hù)信息正確查詢(xún)本月交易金額最高的卡號(hào)正確查詢(xún)本月交易金額最高的卡號(hào)正確統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算正確統(tǒng)計(jì)銀行資金流通余額和盈利結(jié)算正確查

26、詢(xún)本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信正確查詢(xún)本周開(kāi)戶(hù)的卡號(hào)，顯示該卡相關(guān)信息息正確催款提醒業(yè)務(wù)正確催款提醒業(yè)務(wù)要求要求互相驗(yàn)證完成的程序功能互相驗(yàn)證完成的程序功能小組長(zhǎng)檢查本組完成情況小組長(zhǎng)檢查本組完成情況功能測(cè)試功能測(cè)試演示案例演示案例4：模擬常規(guī)業(yè)務(wù)：模擬常規(guī)業(yè)務(wù)32共性問(wèn)題集中講解共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題代碼規(guī)范問(wèn)題調(diào)試技巧調(diào)試技巧33用例用例5：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)2-1需求說(shuō)明需求說(shuō)明為客戶(hù)提供以下為客戶(hù)提供以下3個(gè)視圖供其查詢(xún)?cè)摽蛻?hù)數(shù)據(jù)個(gè)視圖供其查詢(xún)?cè)摽蛻?hù)數(shù)據(jù)客戶(hù)基本信息：客戶(hù)基本信息：vw_userInfo銀行卡信息：銀行

27、卡信息：vw_cardInfo銀行卡交易信息：銀行卡交易信息：vw_tradeInfo提供友好界面，要求各列名稱(chēng)為中文描述提供友好界面，要求各列名稱(chēng)為中文描述調(diào)用創(chuàng)建的視圖獲得查詢(xún)結(jié)果調(diào)用創(chuàng)建的視圖獲得查詢(xún)結(jié)果講解需求說(shuō)明講解需求說(shuō)明34用例用例5：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)2-1思路分析思路分析創(chuàng)建視圖創(chuàng)建視圖調(diào)用視圖調(diào)用視圖CREATE VIEW AS GOSELECT FROM 完成時(shí)間：完成時(shí)間：25分鐘分鐘35用例用例5：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)2-2功能測(cè)試功能測(cè)試檢查點(diǎn)檢查點(diǎn) （5分鐘）分鐘）正確創(chuàng)建視圖正確創(chuàng)建視圖正確使用視圖獲得查詢(xún)數(shù)據(jù)正確使

28、用視圖獲得查詢(xún)數(shù)據(jù)要求要求互相驗(yàn)證完成的程序功能互相驗(yàn)證完成的程序功能小組長(zhǎng)檢查本組完成情況小組長(zhǎng)檢查本組完成情況功能測(cè)試功能測(cè)試演示案例演示案例5：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)：利用視圖實(shí)現(xiàn)數(shù)據(jù)查詢(xún)36用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-1需求說(shuō)明需求說(shuō)明產(chǎn)生隨機(jī)卡號(hào)產(chǎn)生隨機(jī)卡號(hào)完成開(kāi)戶(hù)業(yè)務(wù)完成開(kāi)戶(hù)業(yè)務(wù)完成取款或存款業(yè)務(wù)完成取款或存款業(yè)務(wù)根據(jù)卡號(hào)打印對(duì)賬單根據(jù)卡號(hào)打印對(duì)賬單根據(jù)指定顯示的頁(yè)數(shù)和每頁(yè)的記錄數(shù)分頁(yè)顯根據(jù)指定顯示的頁(yè)數(shù)和每頁(yè)的記錄數(shù)分頁(yè)顯示數(shù)據(jù)示數(shù)據(jù)查詢(xún)、統(tǒng)計(jì)指定時(shí)間段內(nèi)沒(méi)有發(fā)生交易的賬查詢(xún)、統(tǒng)計(jì)指定時(shí)間段內(nèi)沒(méi)有發(fā)生交易的賬戶(hù)信息戶(hù)信息統(tǒng)計(jì)指定時(shí)間段內(nèi)某地區(qū)客戶(hù)在

29、銀行卡交易統(tǒng)計(jì)指定時(shí)間段內(nèi)某地區(qū)客戶(hù)在銀行卡交易量和交易額。如果不指定地區(qū)，則查詢(xún)所有量和交易額。如果不指定地區(qū)，則查詢(xún)所有客戶(hù)的交易量和交易額客戶(hù)的交易量和交易額講解需求說(shuō)明講解需求說(shuō)明37用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-2思路分析思路分析完成取款或存款業(yè)務(wù)完成取款或存款業(yè)務(wù)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_takeMoney card char(19)， type char(4) , inputPass char(6)= AS -SQL語(yǔ)句語(yǔ)句 IF() GO存儲(chǔ)過(guò)程的參數(shù)存儲(chǔ)過(guò)程的參數(shù)有默認(rèn)值的參數(shù)，放在最后有默認(rèn)值的參數(shù)，

30、放在最后調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程EXEC proc_takeMoney 1010 3576 0823 3262, 支取支取,123456檢測(cè)支取金額是否大于當(dāng)前存款金額加檢測(cè)支取金額是否大于當(dāng)前存款金額加1完成時(shí)間：完成時(shí)間：10分鐘分鐘38用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-3思路分析思路分析產(chǎn)生隨機(jī)卡號(hào)產(chǎn)生隨機(jī)卡號(hào)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_randCardID randCardID char(19) OUTPUT SELECT r = RAND (隨機(jī)種子隨機(jī)種子 ) SET randCardID =SUBSTRING(t

31、empStr,3,4) .GO 調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程DECLARE mycardID char(19)EXECUTE proc_randCardID mycardID OUTPUTOUTPUT表示傳出的參數(shù)表示傳出的參數(shù)產(chǎn)生產(chǎn)生0-1的隨機(jī)數(shù)的隨機(jī)數(shù)字符串截取函數(shù)字符串截取函數(shù)調(diào)用帶調(diào)用帶output輸出參數(shù)的存儲(chǔ)過(guò)程輸出參數(shù)的存儲(chǔ)過(guò)程完成時(shí)間：完成時(shí)間：10分鐘分鐘39用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-4思路分析思路分析完成開(kāi)戶(hù)業(yè)務(wù)完成開(kāi)戶(hù)業(yè)務(wù)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_openAccount 存儲(chǔ)過(guò)程參數(shù)存儲(chǔ)過(guò)程參數(shù)A

32、S -SQL語(yǔ)句語(yǔ)句 WHILE EXISTS( ) EXEC PROCEDURE proc_randCardID GO調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程EXEC proc_openAccount 李四李四, , ,1,定期定期調(diào)用產(chǎn)生隨機(jī)卡號(hào)存儲(chǔ)過(guò)程，獲得唯一的卡號(hào)調(diào)用產(chǎn)生隨機(jī)卡號(hào)存儲(chǔ)過(guò)程，獲得唯一的卡號(hào)包括：開(kāi)戶(hù)名、身份證號(hào)、包括：開(kāi)戶(hù)名、身份證號(hào)、電話(huà)號(hào)碼、開(kāi)戶(hù)金額、存款類(lèi)型和地址電話(huà)號(hào)碼、開(kāi)戶(hù)金額、存款類(lèi)型和地址完成時(shí)間：完成時(shí)間：15分鐘分鐘40用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-5思路分析思路分析根據(jù)卡號(hào)打印對(duì)賬單根據(jù)卡號(hào)打印對(duì)賬單創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREAT

33、E PROCEDURE proc_CheckSheet cardID varchar(19)AS SELECT FROM WHERE cardID = cardID PRINT GO調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程EXEC proc_CheckSheet 1010 3576 1212 1134根據(jù)輸入的卡號(hào)查找相關(guān)交易記錄根據(jù)輸入的卡號(hào)查找相關(guān)交易記錄完成時(shí)間：完成時(shí)間：10分鐘分鐘41共性問(wèn)題集中講解共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題代碼規(guī)范問(wèn)題調(diào)試技巧調(diào)試技巧42用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-6思路分析思路分析根據(jù)指定顯示的頁(yè)數(shù)和每頁(yè)的

34、記錄數(shù)分頁(yè)顯示根據(jù)指定顯示的頁(yè)數(shù)和每頁(yè)的記錄數(shù)分頁(yè)顯示數(shù)據(jù)數(shù)據(jù)創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_pagingDisplay page int = 1, records int = 10AS SET statement=SELECT TOP + EXEC SP_EXECUTESQL GO調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程EXEC proc_pagingDisplay 2執(zhí)行動(dòng)態(tài)生成的執(zhí)行動(dòng)態(tài)生成的 T-SQL 語(yǔ)句語(yǔ)句 拼寫(xiě)拼寫(xiě)T-SQL語(yǔ)句語(yǔ)句完成時(shí)間：完成時(shí)間：10分鐘分鐘43用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-7思路分析思路分析查詢(xún)、統(tǒng)計(jì)在

35、指定時(shí)間段內(nèi)沒(méi)有發(fā)生交易的賬查詢(xún)、統(tǒng)計(jì)在指定時(shí)間段內(nèi)沒(méi)有發(fā)生交易的賬戶(hù)信息戶(hù)信息創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_getWithoutTrade Num int output, Amount decimal(18,2) output, AS SELECT FROM WHERE cardID NOT IN ( ) GO調(diào)用存儲(chǔ)過(guò)程調(diào)用存儲(chǔ)過(guò)程EXEC proc_getWithoutTrade NUM OUTPUT, Amount OUTPUT, 存儲(chǔ)過(guò)程的輸出參數(shù)存儲(chǔ)過(guò)程的輸出參數(shù)不存在交易記錄不存在交易記錄完成時(shí)間：完成時(shí)間：10分鐘分鐘44共性問(wèn)題集中講解共性問(wèn)題集中講解常見(jiàn)問(wèn)題及解決辦法常見(jiàn)問(wèn)題及解決辦法代碼規(guī)范問(wèn)題代碼規(guī)范問(wèn)題調(diào)試技巧調(diào)試技巧45用例用例6：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理：用存儲(chǔ)過(guò)程實(shí)現(xiàn)業(yè)務(wù)處理9-8思路分析思路分析統(tǒng)計(jì)指定時(shí)間段內(nèi)某地區(qū)客戶(hù)在銀行卡交易量和統(tǒng)計(jì)指定時(shí)間段內(nèi)某地區(qū)客戶(hù)在銀行卡交易量和交易額交易額如果不指定地區(qū)，則查詢(xún)所有客戶(hù)的交易量和交如果不指定地區(qū)，則查詢(xún)所有客戶(hù)的交易量和交易額易額創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程CREATE PROCEDURE proc_getTradeInfo AS S