使用java實現(xiàn)數(shù)據(jù)庫編程

第五章

事務(wù)、視圖、索引、備份和恢復(fù)預(yù)習(xí)檢查SQL語句如何提交一個事務(wù)？事務(wù)具有的4個特性是什么？索引的作用是什么？數(shù)據(jù)庫備份的命令是什么？集中測試2/61回顧與作業(yè)點評2-1下列語句實現(xiàn)了什么功能？

是否能夠使用IN子查詢改寫上述語句？請使用連接查詢改寫上述語句SELECT*FROM`student`WHERE`studentNo`IN(SELECT`studentNo`FROM`result`);SELECT*FROM`student`INNERJOIN`result`ON`student`.`studentNo`=`result`.`studentNo`;SELECT*FROM`student`WHEREEXISTS(SELECT*FROM`result`WHERE`studentNo`=`student`.`studentNo`);

查詢參加考試的學(xué)生提問3/61點評作業(yè)的提交情況和共性問題回顧與作業(yè)點評2-2作業(yè)點評4/61本章任務(wù)批量插入學(xué)生考試成績辦理畢業(yè)學(xué)生離校手續(xù)查看學(xué)生各科目考試成績平均分創(chuàng)建學(xué)生表和成績表索引并查看索引myschool數(shù)據(jù)庫的備份和恢復(fù)科目表數(shù)據(jù)的導(dǎo)出和導(dǎo)入5/61本章目標(biāo)使用事務(wù)保證操縱數(shù)據(jù)的完整性掌握如何創(chuàng)建并使用視圖掌握如何創(chuàng)建并使用索引掌握如何進行數(shù)據(jù)庫的備份和恢復(fù)6/61為什么需要事務(wù)5-1銀行轉(zhuǎn)賬問題假定資金從賬戶A轉(zhuǎn)到賬戶B，至少需要兩步賬戶A的資金減少然后賬戶B的資金相應(yīng)增加銀行轉(zhuǎn)賬賬戶A賬戶B示例7/61為什么需要事務(wù)5-2假定張三的賬戶有1000元，李四賬戶有1元CREATEDATABASEmybank;USEmybank;CREATETABLE`bank`(`customerName`CHAR(10),#用戶名

`currentMoney`DECIMAL(10,2)#當(dāng)前余額 );/*插入數(shù)據(jù)*/INSERTINTO`bank`(`customerName`,`currentMoney`)VALUES('張三',1000);INSERTINTO`bank`(`customerName`,`currentMoney`)VALUES('李四',1);創(chuàng)建賬戶表，存放用戶的賬戶信息張三開戶，開戶金額為1000元；李四開戶，開戶金額1元示例8/61為什么需要事務(wù)5-3目前兩個賬戶的余額總和：1000+1=1001元示例演示示例1：創(chuàng)建賬戶表及測試數(shù)據(jù)9/61為什么需要事務(wù)5-4模擬實現(xiàn)轉(zhuǎn)賬從張三的賬戶轉(zhuǎn)賬500元到李四的賬戶正常情況下轉(zhuǎn)賬后兩賬戶總和：500+501=1001元問題/*--轉(zhuǎn)賬測試:張三希望通過轉(zhuǎn)賬,直接匯錢給李四500元--*/#張三的賬戶減500元,李四的賬戶增500元UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='張三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';如果轉(zhuǎn)賬過程中出現(xiàn)問題是什么情況？10/61為什么需要事務(wù)5-5張三賬戶：1000元更新成功李四賬戶：1元張三賬戶：500元故障賬戶金額總和：500+1=501如何解決可能發(fā)生的數(shù)據(jù)不一致問題？演示示例2：未使用事務(wù)進行轉(zhuǎn)賬11/61什么是事務(wù)事務(wù)(TRANSACTION)是作為單個邏輯工作單元執(zhí)行的一系列操作多個操作作為一個整體向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行事務(wù)是一個不可分割的工作邏輯單元轉(zhuǎn)賬過程就是一個整體它需要兩條UPDATE語句來完成，這兩條語句是一個整體如果其中任一條出現(xiàn)錯誤，則整個轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個賬戶中的余額應(yīng)恢復(fù)到原來的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額不變，即都是1001元12/61事務(wù)的特性事務(wù)必須具備以下四個屬性，簡稱ACID屬性原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持久性（Durability）事務(wù)是一個完整的操作，事務(wù)的各步操作是不可分的(原子的)，要么都執(zhí)行，要么都不執(zhí)行當(dāng)事務(wù)完成時，數(shù)據(jù)必須處于一致狀態(tài)并發(fā)事務(wù)之間彼此隔離、獨立，它不應(yīng)以任何方式依賴于或影響其他事務(wù)事務(wù)完成后,它對數(shù)據(jù)庫的修改被永久保持13/61如何創(chuàng)建事務(wù)MySQL中支持事務(wù)的存儲引擎有InnoDB和BDB開始事務(wù)提交事務(wù)回滾（撤銷）事務(wù)ROLLBACK;COMMIT;BEGIN;或STARTTRANSACTION;語法14/61BEGIN;/*--轉(zhuǎn)賬:張三的賬戶減少500元,李四的賬戶增加500元--*/UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='張三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';COMMIT;使用事務(wù)解決銀行轉(zhuǎn)賬問題3-1從張三的賬戶轉(zhuǎn)出500元，存入李四的賬戶中開始事務(wù)（指定事務(wù)從此處開始，后續(xù)的SQL語句都是一個整體）提交事務(wù)，事務(wù)結(jié)果示例15/61如何創(chuàng)建事務(wù)2-2事務(wù)過程中事務(wù)執(zhí)行完畢示例使用事務(wù)解決銀行轉(zhuǎn)賬問題3-216/61示例BEGIN;UPDATE`bank`SET`currentMoney`=`currentMoney`-1000WHERE`customerName`='張三';ROLLBACK;如何創(chuàng)建事務(wù)2-2如何創(chuàng)建事務(wù)2-2回滾事務(wù)，數(shù)據(jù)恢復(fù)到原始狀態(tài)演示示例3：使用事務(wù)進行轉(zhuǎn)賬使用事務(wù)解決銀行轉(zhuǎn)賬問題3-317/61自動關(guān)閉和開啟事務(wù)2-1默認(rèn)情況下，每條單獨的SQL語句視為一個事務(wù)關(guān)閉默認(rèn)提交狀態(tài)后，可手動開啟、關(guān)閉事務(wù)關(guān)閉/開啟自動提交狀態(tài)值為0：關(guān)閉自動提交值為1：開啟自動提交語法SETmit=0|1;關(guān)閉自動提交后，從下一條SQL語句開始則開啟新事務(wù)，需使用COMMIT或ROLLBACK語句結(jié)束該事務(wù)!18/61示例自動關(guān)閉和開啟事務(wù)2-2SETmit=0;/*--轉(zhuǎn)賬:張三的賬戶減少500元,李四的賬戶增加500元--*/UPDATE`bank`SET`currentMoney`=`currentMoney`-500WHERE`customerName`='張三';UPDATE`bank`SET`currentMoney`=`currentMoney`+500WHERE`customerName`='李四';COMMIT;UPDATE`bank`SET`currentMoney`=`currentMoney`-1000WHERE`customerName`='張三';ROLLBACK;SETmit=1;關(guān)閉自動提交，以下視為一個事務(wù)提交事務(wù)回滾事務(wù)開啟自動提交，恢復(fù)默認(rèn)狀態(tài)演示示例4：關(guān)閉自動提交19/61學(xué)員操作——批量插入學(xué)生成績2-1訓(xùn)練要點使用事務(wù)向表中插入多條記錄需求說明批量插入?yún)⒓咏裉臁癓ogicJava”課程考試的十名學(xué)生成績?nèi)绻斎氲某煽兇笥?00分，則取消操作指導(dǎo)講解需求說明20/61學(xué)員操作——批量插入學(xué)生成績2-2實現(xiàn)思路開啟事務(wù)插入3條正確數(shù)據(jù)，在未提交時，打開另一會話查看成績數(shù)據(jù)是否變化提交事務(wù)打開另一會話查看成績數(shù)據(jù)是否變化開啟事務(wù)插入2條錯誤數(shù)據(jù)，回滾事務(wù)，查看成績數(shù)據(jù)是否變化指導(dǎo)完成時間：20分鐘21/61學(xué)員操作——辦理學(xué)生離校手續(xù)需求說明將畢業(yè)學(xué)生的基本信息和考試成績分別保存到歷史表中使用事務(wù)完成以下操作查詢result表中所有Y2學(xué)員的考試成績，保存到表historyResult中刪除result表中所有Y2學(xué)員的考試成績查詢student表中所有Y2學(xué)員的記錄，保存到表historyStudent中刪除student表中所有Y2學(xué)員的記錄提交事務(wù)，查看各表中數(shù)據(jù)的變化回滾事務(wù)，查看各表中數(shù)據(jù)的變化練習(xí)提示完成時間：20分鐘22/61常見問題及解決辦法代碼規(guī)范問題調(diào)試技巧共性問題集中講解共性問題集中講解23/61雇員ID姓名薪金職務(wù)績效E10004IanSmith43000技術(shù)BE10005RickyM.32000營銷A………………………………………………………………………………為什么需要視圖不同的人員關(guān)注不同的數(shù)據(jù)保證信息的安全性雇員數(shù)據(jù)庫老板出納人力資源主管員工拒絕訪問24/61什么是視圖3-1教師需要的視圖：

方便查看學(xué)生的成績基于學(xué)生信息表和成績表創(chuàng)建視圖班主任需要的視圖：

方便查看學(xué)生的檔案示例25/61什么是視圖3-2視圖是一張?zhí)摂M表表示一張表的部分?jǐn)?shù)據(jù)或多張表的綜合數(shù)據(jù)其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上視圖中不存放數(shù)據(jù)數(shù)據(jù)存放在視圖所引用的原始表中一個原始表，根據(jù)不同用戶的不同需求，可以創(chuàng)建不同的視圖26/61什么是視圖3-3視圖的用途篩選表中的行防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)降低數(shù)據(jù)庫的復(fù)雜程度將多個物理數(shù)據(jù)庫抽象為一個邏輯數(shù)據(jù)庫27/61如何創(chuàng)建視圖2-1使用SQL語句創(chuàng)建視圖CREATEVIEWview_name

AS<SELECT語句>;使用SQL語句刪除視圖DROPVIEW[IFEXISTS]view_name;使用SQL語句查看視圖SELECT

字段1,字段2,……FROM

view_name;刪除前判斷視圖是否存在語法語法語法28/61如何創(chuàng)建視圖2-2創(chuàng)建方便教師查看成績的視圖USEmyschool;DROPVIEWIFEXISTS`view_student_result`;CREATEVIEW`view_student_result`AS

……SELECT*FROM`view_student_result`;創(chuàng)建視圖查看視圖刪除視圖示例演示示例5：創(chuàng)建并查詢視圖29/61使用視圖注意事項視圖中可以使用多個表一個視圖可以嵌套另一個視圖對視圖數(shù)據(jù)進行添加、更新和刪除操作直接影響所引用表中的數(shù)據(jù)當(dāng)視圖數(shù)據(jù)來自多個表時，不允許添加和刪除數(shù)據(jù)查看所有視圖使用視圖修改數(shù)據(jù)會有許多限制，一般在實際開發(fā)中視圖僅用作查詢!提示USEinformation_schema;SELECT*FROMviews\G;經(jīng)驗30/61學(xué)員操作——查看學(xué)生各科目考試成績平均分2-1訓(xùn)練要點使用視圖獲取多表中數(shù)據(jù)需求說明統(tǒng)計每個學(xué)生所參考課程的平均成績指導(dǎo)講解需求說明31/61指導(dǎo)學(xué)員操作——查看學(xué)生各科目考試成績平均分2-2實現(xiàn)思路創(chuàng)建視圖，查詢語句需關(guān)聯(lián)多張表，可使用表連接或子查詢方式編碼查看視圖的運行結(jié)果完成時間：20分鐘32/61常見問題及解決辦法代碼規(guī)范問題調(diào)試技巧共性問題集中講解共性問題集中講解33/61什么是索引3-1漢語字典中的漢字按頁存放，一般都有漢語拼音目錄（索引）、偏旁部首目錄等我們可以根據(jù)拼音或偏旁部首，快速查找某個字詞34/61什么是索引3-2IndexesUseKeyValuestoLocateData（根據(jù)索引鍵查找定位數(shù)據(jù)行）DataPages（數(shù)據(jù)頁）…IndexPages（索引頁）

35/61什么是索引3-3索引是一種有效組合數(shù)據(jù)的方式，為快速查找到指定記錄作用大大提高數(shù)據(jù)庫的檢索速度改善數(shù)據(jù)庫性能MySQL索引按存儲類型分類B-樹索引：InnoDB、MyISAM均支持哈希索引36/61常用索引類型普通索引基本索引類型允許在定義索引的列中插入重復(fù)值和空值唯一索引索引列數(shù)據(jù)不重復(fù)允許有空值主鍵索引主鍵列中的每個值是非空、唯一的一個主鍵將自動創(chuàng)建主鍵索引復(fù)合索引將多個列組合作為索引全文索引支持值的全文查找允許重復(fù)值和空值空間索引對空間數(shù)據(jù)類型的列建立的索引37/61如何創(chuàng)建/刪除索引2-1創(chuàng)建索引CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name[length]…);唯一索引、全文索引或空間索引，可選刪除索引DROPINDEXindex_nameONtable_name;刪除表時，該表的所有索引同時會被刪除語法語法38/61如何創(chuàng)建/刪除索引2-2在student表的studentName列創(chuàng)建普通索引USEmyschool;CREATEINDEX`index_student_studentName`ON`student`(`studentName`);示例演示示例6：創(chuàng)建索引39/61創(chuàng)建索引的指導(dǎo)原則按照下列標(biāo)準(zhǔn)選擇建立索引的列頻繁搜索的列經(jīng)常用作查詢選擇的列經(jīng)常排序、分組的列經(jīng)常用作連接的列（主鍵/外鍵）請不要使用下面的列創(chuàng)建索引僅包含幾個不同值的列表中僅包含幾行40/61使用索引時注意事項查詢時減少使用*返回全部列，不要返回不需要的列索引應(yīng)該盡量小，在字節(jié)數(shù)小的列上建立索引WHERE子句中有多個條件表達式時，包含索引列的表達式應(yīng)置于其他條件表達式之前避免在ORDERBY子句中使用表達式經(jīng)驗41/61查看索引SHOWINDEXFROMtable_name;語法示例查看myschool數(shù)據(jù)庫中全部索引信息USEmyschool;SHOWINDEXFROM`student`\G;Table：創(chuàng)建索引的表Non_unique：索引是否非唯一Key_name：索引的名稱Column_name：定義索引的列字段Seq_in_index：該列在索引中的位置Null：該列是否能為空值Index_type：索引類型演示：查看索引42/61需求說明為提高以下查詢的速度，為學(xué)生表和成績表添加適合的索引，并查看索引按學(xué)生名和年級編號組合查詢學(xué)生身份證號是唯一的按成績區(qū)間范圍查找學(xué)生考試信息可使用唯一索引、普通索引、組合索引提高查詢效率學(xué)員操作——創(chuàng)建學(xué)生表和成績表索引并查看索引練習(xí)完成時間：20分鐘提示43/61常見問題及解決辦法代碼規(guī)范問題調(diào)試技巧共性問題集中講解共性問題集中講解44/61為什么進行數(shù)據(jù)庫備份數(shù)據(jù)庫故障突然斷電病毒入侵誤操作導(dǎo)致數(shù)據(jù)丟失……如何避免意外狀況導(dǎo)致的數(shù)據(jù)丟失？問題45/61使用mysqldump命令備份數(shù)據(jù)庫4-1mysqldump命令——MySQL一個常用的備份工具將CREATE和INSERTINTO語句保存到文本文件屬于DOS命令mysqldump[options]

–uusername–hhost–ppassworddbname[tbname1[,tbname2……]]>filename.sql用戶名登錄主機名密碼備份后的文件名稱需備份的數(shù)據(jù)庫名需備份的表名（省略則備份所有表）mysqldump是DOS系統(tǒng)下的命令，在使用時無須進入mysql命令行，否則將無法執(zhí)行!注意選項參數(shù)46/61使用root賬戶登錄到MySQL服務(wù)器，備份myschool數(shù)據(jù)庫下的student表

使用mysqldump命令備份數(shù)據(jù)庫4-2示例mysqldump–uroot–pmyschoolstudent>d:\backup\myschool_20160808.sql輸入密碼為保證賬戶密碼安全，命令中可不寫密碼，但參數(shù)“-p”必須有，回車后根據(jù)提示寫密碼!注意47/61使用mysqldump命令備份數(shù)據(jù)庫4-3參數(shù)描述-add-drop-table在每個CREATETABLE語句前添加DROPTABLE語句，默認(rèn)是打開的，可以用-skip-add-drop-table來取消--add-locks該選項會在INSERT語句中捆綁一個LOCKTABLE和UNLOCKTABLE語句好處：防止記錄被再次導(dǎo)入時，其他用戶對表進行的操作-t或-no-create-info只導(dǎo)出數(shù)據(jù)，而不添加CREATETABLE語句-c或plete-insert在每個INSERT語句的列上加上列名，在數(shù)據(jù)導(dǎo)入另一個數(shù)據(jù)庫時有用-d或--no-data不寫表的任何行信息，只轉(zhuǎn)儲表的結(jié)構(gòu)常用參數(shù)選項48/61備份文件包含的主要信息備份后文件包含信息MySQL及mysqldump工具版本號備份賬戶的名稱主機信息備份的數(shù)據(jù)庫名稱SQL語句注釋和服務(wù)器相關(guān)注釋CREATE和INSERT語句備份文件使用mysqldump命令備份數(shù)據(jù)庫4-4演示示例7：mysqldump備份數(shù)據(jù)庫49/61恢復(fù)數(shù)據(jù)庫3-1方法一：使用mysql命令恢復(fù)數(shù)據(jù)庫mysql–uusername–p[dbname]<filename.sql用戶名數(shù)據(jù)庫名備份文件名1.mysql為DOS命令2.在執(zhí)行該語句之前，必須在MySQL服務(wù)器中創(chuàng)建新數(shù)據(jù)庫，如果不存在恢復(fù)數(shù)據(jù)庫過程將會出錯!注意50/61為何報錯？使用備份文件將myschool數(shù)據(jù)庫中student表信息恢復(fù)到schoolDB數(shù)據(jù)庫中示例mysql–uroot–pschoolDB<d:\backup\myschool_20160808.sql恢復(fù)數(shù)據(jù)庫3-251/61方法二：使用source命令恢復(fù)數(shù)據(jù)庫恢復(fù)數(shù)據(jù)庫3-3語法sourcefilename;示例CREATEDATABASEmyschoolDB2;#創(chuàng)建數(shù)據(jù)庫USEmyschoolDB2;#選擇數(shù)據(jù)庫sourced:\backup\myschool_20160808.sql;#恢復(fù)數(shù)據(jù)庫演示示例8：mysql/source恢復(fù)數(shù)據(jù)庫登錄MySQL服務(wù)后使用執(zhí)行該命令前，先創(chuàng)建并選擇恢復(fù)后的目標(biāo)數(shù)據(jù)庫注意52/61通過復(fù)制文件實現(xiàn)數(shù)據(jù)備份和恢復(fù)直接復(fù)制MySQL數(shù)據(jù)庫的存儲目錄及文件進行備份缺點復(fù)制數(shù)據(jù)前停止服務(wù)不適合InnoDB數(shù)據(jù)庫MySQL版本不同不兼容優(yōu)點操作簡單此方法不是最優(yōu)的數(shù)據(jù)庫備份和恢復(fù)方案！!53/61表數(shù)據(jù)導(dǎo)出到文本文件導(dǎo)出/導(dǎo)入數(shù)據(jù)——可實現(xiàn)數(shù)據(jù)庫服務(wù)器間移動數(shù)據(jù)將成績表中“LogicJava”課程的成績信息導(dǎo)出到文本文件SELECT*FROMtablename[WHEREcontion]

INTOOUTFILE'filename'[OPTION]語法示例SELECT*FROM`result`WHERE`subjectNo`=(SELECT`subjectNo`FROM`subject`WHERE`subjectName`='LogicJava')INTOOUTFILE'd:/backup/result_Java.txt';導(dǎo)出的目標(biāo)文件54/61文本文件導(dǎo)入到數(shù)據(jù)表語法LOADDATAINFILEfilenameINTOTABLEtablename[OPTION]將數(shù)據(jù)從文本文件導(dǎo)入到myschoolDB的result表LOADDATAINFILE'd:/back/result_Java.txt'INTOTABLEresult;導(dǎo)入數(shù)據(jù)前應(yīng)確保目標(biāo)表已存在！!注意演示示例9：表數(shù)