MySQL事務(wù)、索引、數(shù)據(jù)恢復(fù)和備份全套電子課件完整版ppt整本書(shū)電子教案最全教學(xué)教程整套課件

1、MySQL事務(wù)、索引、數(shù)據(jù)恢復(fù)和備份線(xiàn)上線(xiàn)下平臺(tái)預(yù)習(xí)2/32本課目標(biāo)學(xué)完本次課程后，你能夠：掌握MySQL的事務(wù)處理方法理解MySQL的ACID原則創(chuàng)建數(shù)據(jù)庫(kù)索引了解MySQL數(shù)據(jù)庫(kù)備份和恢復(fù)的幾種方法掌握mysqldump命令導(dǎo)出數(shù)據(jù)掌握source命令導(dǎo)入數(shù)據(jù)3/32MySQL的事務(wù)處理事務(wù)就是將一組SQL語(yǔ)句放在同一批次內(nèi)去執(zhí)行如果一個(gè)SQL語(yǔ)句出錯(cuò)，則該批次內(nèi)的所有SQL都將被取消執(zhí)行注意MySQL事務(wù)處理只支持InnoDB和BDB數(shù)據(jù)表類(lèi)型4/32事務(wù)的特性事務(wù)必須具備以下四個(gè)屬性，簡(jiǎn)稱(chēng)ACID 屬性原子性（Atomicity）一致性（Consistency）隔離性（Isolatio

2、n）持久性（Durability）5/61MySQL的事務(wù)實(shí)現(xiàn)方法SET AUTOCOMMIT使用SET語(yǔ)句來(lái)改變自動(dòng)提交模式MySQL中默認(rèn)是自動(dòng)提交使用事務(wù)時(shí)應(yīng)先關(guān)閉自動(dòng)提交 SET AUTOCOMMIT = 0; # 關(guān)閉自動(dòng)提交模式SET AUTOCOMMIT = 1; # 開(kāi)啟自動(dòng)提交模式示例注意6/32MySQL的事務(wù)實(shí)現(xiàn)方法START TRANSACTION開(kāi)始一個(gè)事務(wù),標(biāo)記事務(wù)的起始點(diǎn)COMMIT提交一個(gè)事務(wù)給數(shù)據(jù)庫(kù) ROLLBACK將事務(wù)回滾，數(shù)據(jù)回到本次事務(wù)的初始狀態(tài) SET AUTOCOMMIT = 1;開(kāi)啟MySQL數(shù)據(jù)庫(kù)的自動(dòng)提交7/32MySQL事務(wù)處理步驟8/32

3、模擬網(wǎng)上支付2-1模擬網(wǎng)上支付顧客A在線(xiàn)購(gòu)買(mǎi)一款商品，價(jià)格為500.00元，采用網(wǎng)上銀行轉(zhuǎn)賬的方式支付假如顧客A銀行卡的余額為2000.00元，且向賣(mài)家B支付購(gòu)買(mǎi)商品費(fèi)用500.00元，起始賣(mài)家B的賬號(hào)金額10000.00元?jiǎng)?chuàng)建數(shù)據(jù)庫(kù)shop和創(chuàng)建表account并插入2條數(shù)據(jù)+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| id | int(11) | NO | PRI | NULL | auto_increment | name | varchar(32) | NO | | NULL | |

4、 cash | decimal(9,2) | NO | | NULL | |+-+-+-+-+-+-+ INSERT INTO account (name,cash) VALUES (A,2000.00);INSERT INTO account (name,cash) VALUES (B,10000.00);9/32模擬網(wǎng)上支付2-2設(shè)置場(chǎng)景A賬戶(hù)成功減少500元，B賬戶(hù)應(yīng)該增加500元，但一些錯(cuò)誤導(dǎo)致未增加成功，這時(shí)則需返回A賬戶(hù)的500元，達(dá)到賬戶(hù)總額的平衡要求：使用事務(wù)模擬以上過(guò)程演示示例1： 網(wǎng)上支付10/32數(shù)據(jù)庫(kù)索引作用提高查詢(xún)速度確保數(shù)據(jù)的唯一性可以加速表和表之間的連接，實(shí)現(xiàn)表與

5、表之間的參照完整性使用分組和排序子句進(jìn)行數(shù)據(jù)檢索時(shí)，可以顯著減少分組和排序的時(shí)間全文檢索字段進(jìn)行搜索優(yōu)化分類(lèi)主鍵索引（PRIMARY KEY）唯一索引（UNIQUE）常規(guī)索引（INDEX）全文索引（FULLTEXT）11/32主鍵索引某一個(gè)屬性或?qū)傩缘慕M合能唯一標(biāo)識(shí)一條記錄如：學(xué)生表（學(xué)號(hào)，姓名，班級(jí)，性別等） ，學(xué)號(hào)就是唯一標(biāo)識(shí)的，可作為主鍵特點(diǎn)最常見(jiàn)的索引類(lèi)型確保數(shù)據(jù)記錄的唯一性確定特定數(shù)據(jù)記錄在數(shù)據(jù)庫(kù)中的位置 CREATE TABLE Grade ( GradeID INT(11) AUTO_INCREMENT PRIMARY KEY, #省略代碼 #主鍵索引也可在字段字義之后，如 #

6、PRIMARY KEY(GradeID) )示例12/32唯一索引（UNIQUE）作用避免同一個(gè)表中某數(shù)據(jù)列中的值重復(fù)與主鍵索引的區(qū)別主鍵索引只能有一個(gè)唯一索引可有多個(gè) CREATE TABLE Grade (GradeID INT(11) AUTO_INCREMENT PRIMARY KEY,GradeName VARCHAR(32) NOT NULL UNIQUE#或 UNIQUE KEY GradeID (GradeID)示例13/32常規(guī)索引（INDEX）作用：快速定位特定數(shù)據(jù)注意index和key關(guān)鍵字都可設(shè)置常規(guī)索引應(yīng)加在查找條件的字段不宜添加太多常規(guī)索引，影響數(shù)據(jù)的插入、刪除和修

7、改操作CREATE TABLE result ( /省略一些代碼 INDEX/KEY ind (studentNo, subjectNo)ALTER TABLE result ADD INDEX ind (studentNo, subjectNo);創(chuàng)建表時(shí)添加創(chuàng)建后追加示例14/32作用：快速定位特定數(shù)據(jù)注意只能用于MyISAM類(lèi)型的數(shù)據(jù)表只能用于 CHAR 、 VARCHAR、TEXT數(shù)據(jù)列類(lèi)型適合大型數(shù)據(jù)集 CREATE TABLE student ( #省略一些SQL語(yǔ)句 FULLTEXT (StudentName) )ENGINE=MYISAM;ALTER TABLE employe

8、e ADD FULLTEXT (first_name);全文索引（FULLTEXT）示例15/32創(chuàng)建表時(shí)添加創(chuàng)建后追加管理索引創(chuàng)建索引創(chuàng)建表時(shí)添加建表后追加刪除索引查看索引ALERT TABLE 表名 ADD 索引類(lèi)型（數(shù)據(jù)列名）DROP INDEX 索引名 ON 表名ALTER TABLE 表名 DROP INDEX 索引名ALTER TABLE 表名 DROP PRIMARY KEYSHOW INDEX(或KEYS) FROM 表名語(yǔ)法語(yǔ)法語(yǔ)法16/32索引準(zhǔn)則索引不是越多越好不要對(duì)經(jīng)常變動(dòng)的數(shù)據(jù)加索引小數(shù)據(jù)量的表建議不要加索引索引一般應(yīng)加在查找條件的字段17/32給數(shù)據(jù)庫(kù)表添加索引給數(shù)

9、據(jù)庫(kù)表student添加索引學(xué)號(hào)StudentNo，添加主鍵索引身份證IdentityCard，添加唯一索引郵箱Email，添加常規(guī)索引姓名StudentName，添加全文索引 (MyISAM類(lèi)型數(shù)據(jù)表)演示示例2：添加索引18/32分析SQL語(yǔ)句的執(zhí)行性能提示：使用 G 結(jié)尾能豎排顯示 查詢(xún)中不包含子查詢(xún)連接類(lèi)型,從最好到最差的連接類(lèi)型為const、eq_reg、ref、range、indexhe和ALL包含MySQL解決查詢(xún)的詳細(xì)信息演示示例3：EXPLAIN語(yǔ)句使用EXPLAIN 表名 （DESC 表名）EXPLAIN SELECT語(yǔ)句語(yǔ)法19/32添加正確的索引在WHERE、ORDER

10、 BY 子句中經(jīng)常使用的字段字段的值是多個(gè)（例如性別字段則不適合）字段內(nèi)容不是經(jīng)常變化的經(jīng)常變化的字段，添加索引反而降低性能不宜過(guò)多添加索引每添加一條索引都會(huì)占用磁盤(pán)空間20/32MySQL的備份數(shù)據(jù)庫(kù)備份必要性保證重要數(shù)據(jù)不丟失數(shù)據(jù)轉(zhuǎn)移MySQL數(shù)據(jù)庫(kù)備份方法mysqldump備份工具數(shù)據(jù)庫(kù)管理工具，如SQLyog直接拷貝數(shù)據(jù)庫(kù)文件和相關(guān)配置文件21/32mysqldump數(shù)據(jù)庫(kù)備份作用轉(zhuǎn)儲(chǔ)數(shù)據(jù)庫(kù)搜集數(shù)據(jù)庫(kù)進(jìn)行備份將數(shù)據(jù)轉(zhuǎn)移到另一個(gè)SQL服務(wù)器(不一定是MySQL服務(wù)器) # 備份myschool數(shù)據(jù)庫(kù)如： mysqldump -u root -p myschool d:/myschool.

11、sql EnterPassword: *mysqldump -h 主機(jī)名 u 用戶(hù)名 p options 數(shù)據(jù)庫(kù)名 table1 table2 table3 path/filename.sql預(yù)存文件目錄，須有該目錄讀寫(xiě)權(quán)限語(yǔ)法示例22/32mysqldump常用選項(xiàng)2-1符號(hào)名稱(chēng)描述-add-drop-table 導(dǎo)出sql腳本會(huì)加上 DROP TABLE IF EXISTS 語(yǔ)句默認(rèn)是打開(kāi)的，可以用 -skip-add-drop-table 來(lái)取消-add-locks該選項(xiàng)會(huì)在INSERT 語(yǔ)句中捆綁一個(gè)LOCK TABLE 和UNLOCK TABLE 語(yǔ)句好處：防止記錄被再次導(dǎo)入時(shí)，其他

12、用戶(hù)對(duì)表進(jìn)行的操作，默認(rèn)是打開(kāi)的-t 或-no-create-info忽略不寫(xiě)重新創(chuàng)建每個(gè)轉(zhuǎn)儲(chǔ)表的CREATE TABLE語(yǔ)句23/32提示可通過(guò)mysqldump -help 查看該命名的選項(xiàng)mysqldump常用選項(xiàng)2-2符號(hào)名稱(chēng)描述-c或 -complete-insert在每個(gè)INERT語(yǔ)句的列上加上字段名在數(shù)據(jù)庫(kù)導(dǎo)入另一個(gè)數(shù)據(jù)庫(kù)時(shí)非常有用-d 或 -no-data不寫(xiě)表的任何行信息。對(duì)于只想轉(zhuǎn)儲(chǔ)表的結(jié)構(gòu)很有用-where where-condition, -w where-condition只轉(zhuǎn)儲(chǔ)給定的WHERE條件選擇的記錄-opt該選項(xiàng)是速記；等同于指定 -add-drop-tab

13、les -add-locking -create-option -disable-keys-extended-insert -lock-tables -quick -set-charset 24/32mysqldump數(shù)據(jù)庫(kù)備份備份MySchool數(shù)據(jù)庫(kù)的subject課程表，保存為subject.sql腳本文件要求在每個(gè)INERT語(yǔ)句的列上加上字段名25/32MySQL數(shù)據(jù)庫(kù)的恢復(fù)方法一用 SOURCE 語(yǔ)法 /path/是一個(gè)絕對(duì)路徑，并且必須是mysql 運(yùn)行用戶(hù)有權(quán)限讀取的文件SOURCE 在MySQL命令行里執(zhí)行方法二用 mysql 客戶(hù)端SOURCE /path/db_name.s

14、ql;mysql u root p dbname /path/db_name.sql;都要先創(chuàng)建或選擇數(shù)據(jù)庫(kù)26/32演示示例4：數(shù)據(jù)庫(kù)的備份和恢復(fù)利用SQL語(yǔ)句導(dǎo)出、導(dǎo)入數(shù)據(jù)輸出的文件不能先存在，否則報(bào)錯(cuò)SELECT * INTO OUTFILE file_name FROM tbl_nameLOAD DATA INFILE file_name INTO TABLE tbl_nameFIELDS語(yǔ)法演示示例5：數(shù)據(jù)的導(dǎo)出和導(dǎo)入27/32語(yǔ)法導(dǎo)出數(shù)據(jù)導(dǎo)入數(shù)據(jù)練習(xí)1：數(shù)據(jù)庫(kù)備份和恢復(fù)需求說(shuō)明使用mysqldump命令備份myschool數(shù)據(jù)庫(kù)要求在每個(gè)INSERT語(yǔ)句上加上字段名導(dǎo)入備份的myschool數(shù)據(jù)庫(kù)要求使用SOURCE命令和mysql命令兩種方式恢復(fù)數(shù)據(jù)28/32練習(xí)2：查詢(xún)綜合練習(xí)查詢(xún)密碼長(zhǎng)度不足6位的學(xué)員編號(hào)、姓名、密碼、年級(jí)名查找鄭環(huán)環(huán)所有考試的成績(jī)（科目名稱(chēng)、考試成績(jī)）使用表連接方式查詢(xún)所有U1年級(jí)學(xué)員學(xué)號(hào)、姓名及年級(jí)名使用子查詢(xún)方式查詢(xún)所有U1年級(jí)學(xué)員學(xué)號(hào)、姓名、年級(jí)ID使用表連接方式查詢(xún)“Java”課程至少一次考試剛好等于60分