《PHP+MySQL動(dòng)態(tài)網(wǎng)站開發(fā)》 課件-第8章 MySQL進(jìn)階

第8章MySQL進(jìn)階《PHP+MySQL動(dòng)態(tài)網(wǎng)站開發(fā)》學(xué)習(xí)目標(biāo)/Target掌握事務(wù)的開啟、提交和回滾操作掌握使用視圖對(duì)數(shù)據(jù)進(jìn)行操作掌握數(shù)據(jù)庫(kù)的備份與還原學(xué)習(xí)目標(biāo)/Target了解用戶賬號(hào)的管理和授權(quán)熟悉索引及分區(qū)的應(yīng)用熟悉存儲(chǔ)過(guò)程及觸發(fā)器的使用方法章節(jié)概述/Summary在前面的章節(jié)中，已經(jīng)學(xué)會(huì)了如何使用MySQL來(lái)對(duì)數(shù)據(jù)進(jìn)行增加、刪除、修改、查詢操作，這些是MySQL的基本功能。而在實(shí)際開發(fā)中，有時(shí)還需要利用事務(wù)保證多條SQL語(yǔ)句同時(shí)成功或失敗、利用視圖來(lái)滿足不同的查詢需求、利用索引來(lái)提高數(shù)據(jù)庫(kù)的查詢效率等，這就需要學(xué)習(xí)MySQL中的一些進(jìn)階技術(shù)。本章將針對(duì)MySQL中的事務(wù)、視圖、數(shù)據(jù)備份與還原、用戶管理、索引、分區(qū)、存儲(chǔ)過(guò)程以及觸發(fā)器進(jìn)行詳細(xì)講解。目錄/Contents01020304事務(wù)視圖數(shù)據(jù)備份與還原用戶管理目錄/Contents05060708索引分區(qū)存儲(chǔ)過(guò)程觸發(fā)器事務(wù)8.18.1.1事務(wù)的概念

先定一個(gè)小目標(biāo)！熟悉事務(wù)的概念，能夠說(shuō)出在什么情況下需要用到事務(wù)8.1.1事務(wù)的概念事務(wù)（Transaction）：針對(duì)數(shù)據(jù)庫(kù)的一組操作，由一條或多條SQL語(yǔ)句組成，每個(gè)SQL語(yǔ)句相互依賴。保證數(shù)據(jù)完整性：程序執(zhí)行過(guò)程中有一條SQL語(yǔ)句執(zhí)行失敗或發(fā)生錯(cuò)誤，其他語(yǔ)句就不會(huì)執(zhí)行，返回到事務(wù)開始前的狀態(tài)。8.1.1事務(wù)的概念應(yīng)用場(chǎng)景：轉(zhuǎn)賬時(shí)如果任意一條SQL出現(xiàn)異常，導(dǎo)致兩個(gè)賬戶的金額錯(cuò)誤。用戶A用戶B用戶A執(zhí)行轉(zhuǎn)出SQL用戶B執(zhí)行轉(zhuǎn)入SQL8.1.2事務(wù)特性

先定一個(gè)小目標(biāo)！熟悉事務(wù)的特性，能夠說(shuō)出事務(wù)的4個(gè)基本特性分別是什么8.1.2事務(wù)特性原子性Atomicity事務(wù)必須被視為不可分割的最小工作單元，只有事務(wù)中所有的數(shù)據(jù)操作都執(zhí)行成功，整個(gè)事務(wù)才算執(zhí)行成功。一致性Consistency持久性Durability在事務(wù)處理時(shí)，無(wú)論執(zhí)行成功還是失敗，都要保證數(shù)據(jù)庫(kù)系統(tǒng)中的事務(wù)狀態(tài)一致，保證數(shù)據(jù)庫(kù)系統(tǒng)不會(huì)返回到一個(gè)未處理的事務(wù)中。事務(wù)在執(zhí)行時(shí)，不會(huì)受其他事務(wù)的影響。保證未完成事務(wù)的所有操作與數(shù)據(jù)庫(kù)系統(tǒng)的隔離，事務(wù)執(zhí)行成功后才能看到執(zhí)行結(jié)果。事務(wù)一旦提交，其對(duì)數(shù)據(jù)庫(kù)的修改就是永久性的。隔離性Isolation8.1.2事務(wù)特性實(shí)現(xiàn)事務(wù)特性的途徑：一致性：主要由日志機(jī)制實(shí)現(xiàn)，記錄數(shù)據(jù)庫(kù)的所有變化，提供恢復(fù)事務(wù)的跟蹤記錄。隔離性：通過(guò)并發(fā)控制、可串行化、鎖等技術(shù)。8.1.3事務(wù)處理

先定一個(gè)小目標(biāo)！掌握事務(wù)的處理，能夠進(jìn)行開啟事務(wù)、提交事務(wù)、回滾事務(wù)，以及設(shè)置保存點(diǎn)等操作8.1.3事務(wù)處理實(shí)現(xiàn)事務(wù)的步驟：開啟事務(wù)提交事務(wù)回滾事務(wù)刪除保存點(diǎn)回滾至保存點(diǎn)創(chuàng)建保存點(diǎn)8.1.3事務(wù)處理開啟事務(wù)：STARTTRANSACTION;語(yǔ)法格式8.1.3事務(wù)處理提交事務(wù)：COMMIT;語(yǔ)法格式8.1.3事務(wù)處理回滾事務(wù)：ROLLBACK;語(yǔ)法格式8.1.3事務(wù)處理創(chuàng)建保存點(diǎn)：SAVEPOINT保存點(diǎn)名;語(yǔ)法格式用于撤銷一部分事務(wù)，一個(gè)事務(wù)可以創(chuàng)建多個(gè)保存點(diǎn)，提交事務(wù)后保存點(diǎn)會(huì)被刪除，回滾到某個(gè)保存點(diǎn)后，該保存點(diǎn)之后的保存點(diǎn)也會(huì)消失。8.1.3事務(wù)處理回滾至保存點(diǎn)：ROLLBACKTOSAVEPOINT保存點(diǎn)名;語(yǔ)法格式8.1.3事務(wù)處理刪除保存點(diǎn)：ELEASESAVEPOINT保存點(diǎn)名;語(yǔ)法格式8.1.3事務(wù)處理mysql>CREATETABLE`my_user`(->`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENTCOMMENT'用戶id',->`name`VARCHAR(100)NOTNULLUNIQUEDEFAULT''COMMENT'用戶名',->`money`DECIMAL(10,2)UNSIGNEDNOTNULLDEFAULT0COMMENT'金額'->)ENGINE=InnoDBDEFAULTCHARSET=utf8;mysql>INSERTINTO`my_user`(`id`,`name`,`money`)VALUES->(1,'Alex',1000),(2,'Bill',1000);案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例創(chuàng)建數(shù)據(jù)表。創(chuàng)建用戶表插入測(cè)試數(shù)據(jù)。STEP018.1.3事務(wù)處理mysql>SELECT`name`,`money`FROM`my_user`;+------+---------+|name|money|+------+---------+|Alex|1000.00||Bill|1000.00|+------+---------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例查看數(shù)據(jù)。創(chuàng)建用戶表插入測(cè)試數(shù)據(jù)。STEP028.1.3事務(wù)處理#①開啟事務(wù)mysql>STARTTRANSACTION;#②Alex減少100元mysql>UPDATE`my_user`SET`money`=`money`-100WHERE`name`='Alex';#③Bill增加100元mysql>UPDATE`my_user`SET`money`=`money`+100WHERE`name`='Bill';#④提交事務(wù)mysql>COMMIT;案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例執(zhí)行轉(zhuǎn)賬操作。通過(guò)UPDATE語(yǔ)句將Alex用戶的100元錢轉(zhuǎn)給Bill用戶，最后提交事務(wù)。STEP038.1.3事務(wù)處理mysql>SELECT`name`,`money`FROM`my_user`;+------+---------+|name|money|+------+---------+|Alex|900.00||Bill|1100.00|+------+---------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例查看轉(zhuǎn)賬結(jié)果。查詢Alex和Bill的金額。STEP048.1.3事務(wù)處理mysql>STARTTRANSACTION;mysql>UPDATE`my_user`SET`money`=`money`-100WHERE`name`='Bill';mysql>SELECT`name`,`money`FROM`my_user`WHERE`name`='Bill';+------+---------+|name|money|+------+---------+|Bill|1000.00|+------+---------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例回滾事務(wù)。將Bill的金額扣除100元。STEP058.1.3事務(wù)處理#①回滾事務(wù)mysql>ROLLBACK;#②查看Bill的金額mysql>SELECT`name`,`money`FROM`my_user`WHERE`name`='Bill';+------+---------+|name|money|+------+---------+|Bill|1100.00|+------+---------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例執(zhí)行回滾操作。查詢Bill的金額。STEP068.1.3事務(wù)處理#①開啟事務(wù)mysql>STARTTRANSACTION;#②Alex扣除100元mysql>UPDATE`my_user`SET`money`=`money`-100WHERE`name`='Alex';#③創(chuàng)建保存點(diǎn)s1mysql>SAVEPOINTS1;#④Alex扣除50元mysql>UPDATE`my_user`SET`money`=`money`-50WHERE`name`='Alex';案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例創(chuàng)建保存點(diǎn)。將Alex（當(dāng)前金額900）的金額扣除100元，創(chuàng)建保存點(diǎn)S1，再扣除50元。STEP078.1.3事務(wù)處理#①回滾到保存點(diǎn)s1mysql>ROLLBACKTOSAVEPOINTS1;#②查詢Alex的金額mysql>SELECT`name`,`money`FROM`my_user`WHERE`name`='Alex';+------+--------+|name|money|+------+--------+|Alex|800.00|+------+--------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例將事務(wù)回滾至保存點(diǎn)。查詢Alex的金額。STEP088.1.3事務(wù)處理#①回滾事務(wù)mysql>ROLLBACK;#②查詢Alex的金額mysql>SELECT`name`,`money`FROM`my_user`WHERE`name`='Alex';+------+--------+|name|money|+------+--------+|Alex|900.00|+------+--------+案例：使用事務(wù)實(shí)現(xiàn)轉(zhuǎn)賬代碼示例回滾事務(wù)。再次執(zhí)行回滾操作，則恢復(fù)到事務(wù)開始時(shí)的狀態(tài)。STEP09視圖8.28.2.1創(chuàng)建視圖

先定一個(gè)小目標(biāo)！掌握視圖的創(chuàng)建，能夠通過(guò)CREATEVIEW語(yǔ)句創(chuàng)建視圖8.2.1創(chuàng)建視圖什么是視圖：視圖是從一個(gè)或多個(gè)表中導(dǎo)出來(lái)的表，它是虛擬存在的表，表結(jié)構(gòu)和數(shù)據(jù)都依賴于基本表。視圖的作用：通過(guò)視圖可以查看基本表的數(shù)據(jù)，還可以對(duì)數(shù)據(jù)進(jìn)行查詢、添加、修改和刪除。CREATEVIEW視圖名稱ASSELECT指令;語(yǔ)法格式CREATEVIEW創(chuàng)建視圖SELECT指令獲取視圖的結(jié)構(gòu)和數(shù)據(jù)8.2.1創(chuàng)建視圖mysql>CREATEVIEW`view_student`AS->SELECTs.`id`,s.`name`,m.`name`ASmajorFROM`majors`ASm->LEFTJOIN`student`ASsONm.`id`=s.`mid`;案例：視圖的使用創(chuàng)建視圖。STEP018.2.1創(chuàng)建視圖mysql>SHOWTABLES;+----------------+|Tables_in_mydb|+----------------+|goods||majors||student||view_student|+----------------+案例：視圖的使用查看創(chuàng)建的視圖。STEP028.2.1創(chuàng)建視圖mysql>SELECT*FROM`view_student`;+----+------+-------+|id|name|major|+----+------+-------+|3|Alen|前端||4|Andy|前端|+----+------+-------+案例：視圖的使用查詢視圖。STEP038.2.2視圖管理

先定一個(gè)小目標(biāo)！掌握視圖的管理，能夠?qū)σ晥D進(jìn)行查看、修改和刪除操作8.2.2視圖管理查看視圖使用的語(yǔ)法和查看數(shù)據(jù)表使用的語(yǔ)法相同。重點(diǎn)學(xué)習(xí)修改視圖和刪除視圖。查看視圖修改視圖刪除視圖視圖管理語(yǔ)法格式：ALTERVIEW視圖名稱ASSELECT指令;語(yǔ)法格式：DROPVIEW[IFEXISTS]視圖名稱;8.2.2視圖管理mysql>ALTERVIEW`view_student`AS->SELECTs.`name`,m.`name`ASmajorFROM`majors`ASm->LEFTJOIN`student`ASsONm.`id`=s.`mid`;案例：修改和刪除視圖代碼示例修改視圖的數(shù)據(jù)源。STEP018.2.2視圖管理mysql>SELECT*FROM`view_student`;+------+-------+|name|major|+------+-------+|Alen|前端||Andy|前端|+------+-------+案例：修改和刪除視圖代碼示例查詢視圖。STEP028.2.2視圖管理mysql>DROPVIEW`view_student`;#檢查視圖是否已被刪除mysql>SELECT*FROM`view_student`;Error1146(42S02):Table'mydb.view_student'doesn'texist案例：修改和刪除視圖代碼示例刪除視圖。STEP038.2.3視圖數(shù)據(jù)操作

先定一個(gè)小目標(biāo)！掌握視圖數(shù)據(jù)操作，能夠?qū)σ晥D中的數(shù)據(jù)進(jìn)行添加、修改和刪除操作8.2.3視圖數(shù)據(jù)操作視圖數(shù)據(jù)操作：對(duì)視圖進(jìn)行添加、修改或刪除等操作，實(shí)現(xiàn)對(duì)基本表的修改。數(shù)據(jù)操作的限制：數(shù)據(jù)操作只對(duì)單張基本表的視圖有效，多張基本表的視圖不允許進(jìn)行數(shù)據(jù)操作。8.2.3視圖數(shù)據(jù)操作mysql>CREATEVIEW`view_student`AS->SELECT`id`,`name`,`mid`FROM`student`;案例：對(duì)單張表的視圖進(jìn)行數(shù)據(jù)操作代碼示例創(chuàng)建視圖。STEP018.2.3視圖數(shù)據(jù)操作mysql>INSERTINTO`view_student`VALUES(NULL,'Sun',2);mysql>SELECT*FROM`view_student`;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|2||4|Andy|2||5|Sun|2|+----+------+-----+案例：對(duì)單張表的視圖進(jìn)行數(shù)據(jù)操作代碼示例通過(guò)視圖添加數(shù)據(jù)。STEP028.2.3視圖數(shù)據(jù)操作mysql>UPDATE`view_student`SET`name`='Sun1'WHERE`id`=5;mysql>SELECT*FROM`view_student`;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|2||4|Andy|2||5|Sun1|2|+----+------+-----+案例：對(duì)單張表的視圖進(jìn)行數(shù)據(jù)操作代碼示例通過(guò)視圖更新數(shù)據(jù)。STEP038.2.3視圖數(shù)據(jù)操作mysql>DELETEFROM`view_student`WHERE`id`=5;mysql>SELECT*FROM`view_student`;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|2||4|Andy|2|+----+------+-----+案例：對(duì)單張表的視圖進(jìn)行數(shù)據(jù)操作代碼示例通過(guò)視圖刪除數(shù)據(jù)。STEP048.3數(shù)據(jù)備份與還原8.3.1數(shù)據(jù)備份

先定一個(gè)小目標(biāo)！掌握數(shù)據(jù)的備份，能夠通過(guò)相關(guān)命令對(duì)數(shù)據(jù)庫(kù)、數(shù)據(jù)表進(jìn)行備份8.3.1數(shù)據(jù)備份MEDIUNINTmysqldump工具：將數(shù)據(jù)庫(kù)導(dǎo)出成SQL腳本，SQL腳本包含表結(jié)構(gòu)和數(shù)據(jù)信息，執(zhí)行腳本可以導(dǎo)入備份的數(shù)據(jù)。8.3.1數(shù)據(jù)備份MEDIUNINT備份單個(gè)數(shù)據(jù)庫(kù)或數(shù)據(jù)表mysqldump-uusername-ppassworddbname[tbname1[tbname2…]]語(yǔ)法格式username表示用戶名password表示密碼dbname表示需要備份的數(shù)據(jù)庫(kù)名稱tbname表示數(shù)據(jù)庫(kù)中的表名，多個(gè)表名使用用空格分隔8.3.1數(shù)據(jù)備份MEDIUNINT備份多個(gè)數(shù)據(jù)庫(kù)mysqldump-uusername-ppassword--databasedbname1[dbname2…]語(yǔ)法格式--database后指定數(shù)據(jù)庫(kù)名稱，如果有多個(gè)數(shù)據(jù)庫(kù)使用空格分隔8.3.1數(shù)據(jù)備份MEDIUNINT備份所有數(shù)據(jù)庫(kù)mysqldump-uusername-ppassword--all-databases語(yǔ)法格式--all-databases表示備份所有的數(shù)據(jù)庫(kù)8.3.1數(shù)據(jù)備份#①備份mydb數(shù)據(jù)庫(kù)的student表mysqldump-uroot-pmydbstudent>D:/student.sql#②備份單個(gè)數(shù)據(jù)庫(kù)mysqldump-uroot-pmydb>D:/mydb.sql#③備份所有數(shù)據(jù)庫(kù)mysqldump-uroot-p--all-databases>D:/mysql.sql案例：備份數(shù)據(jù)庫(kù)代碼示例STEP01備份數(shù)據(jù)庫(kù)和數(shù)據(jù)表。備份成功后在D盤根目錄下生成student.sql、mydb.sql和mysql.sql3個(gè)文件。8.3.1數(shù)據(jù)備份案例：備份數(shù)據(jù)庫(kù)代碼示例STEP02查看備份結(jié)果。打開student.sql查看備份內(nèi)容。8.3.1數(shù)據(jù)備份STEP02SQL腳本中包含內(nèi)容：MySQLdump版本、MySQL服務(wù)器版本、主機(jī)名、備份的數(shù)據(jù)庫(kù)名稱、SQL語(yǔ)句。以/*!開頭*/結(jié)尾的語(yǔ)句是可執(zhí)行的MySQL注釋，可以被特定版本的MySQL執(zhí)行，在其他數(shù)據(jù)庫(kù)管理系統(tǒng)中將被作為注釋忽略，提高數(shù)據(jù)庫(kù)的可移植性。8.3.2數(shù)據(jù)還原

先定一個(gè)小目標(biāo)！掌握數(shù)據(jù)的還原，能夠通過(guò)輸入重定向、source命令這兩種方式進(jìn)行數(shù)據(jù)還原8.3.2數(shù)據(jù)還原提示：若SQL腳本不包含創(chuàng)建和選擇數(shù)據(jù)庫(kù)的語(yǔ)句，在還原數(shù)據(jù)前，應(yīng)先創(chuàng)建和選擇數(shù)據(jù)庫(kù)。輸入重定向mysql-uusername-ppassword[dbname]<filename.sql語(yǔ)法格式username表示用戶名password表示密碼dbname表示數(shù)據(jù)庫(kù)名稱filename.sql是SQL腳本文件，文件名前面可以加上路徑8.3.2數(shù)據(jù)還原案例：利用輸入重定向還原數(shù)據(jù)代碼示例STEP02#①登錄MySQL，創(chuàng)建mydb2數(shù)據(jù)庫(kù)后退出mysql>CREATEDATABASE`mydb2`;mysql>exit;#②還原數(shù)據(jù)mysql-uroot-pmydb2<D:/mydb.sql8.3.2數(shù)據(jù)還原source命令source文件路徑語(yǔ)法格式8.3.2數(shù)據(jù)還原案例：利用source命令還原數(shù)據(jù)代碼示例STEP02#①刪除student數(shù)據(jù)表mysql>DROPtable`student`;#②還原數(shù)據(jù)mysql>sourceD:/student.sql;#③查看數(shù)據(jù)是否還原mysql>SELECT*FROM`student`;8.4用戶管理8.4.1賬號(hào)管理

先定一個(gè)小目標(biāo)！掌握賬號(hào)的管理，能夠利用CREATEUSER語(yǔ)句創(chuàng)建賬號(hào)，利用DROPUSER語(yǔ)句刪除賬號(hào)8.4.1賬號(hào)管理什么是賬號(hào)：用戶是數(shù)據(jù)庫(kù)的使用者和管理者，每個(gè)用戶都有對(duì)應(yīng)的賬號(hào)，MySQL通過(guò)設(shè)置不同的賬號(hào)來(lái)控制操作人員的訪問和操作范圍。創(chuàng)建賬號(hào)語(yǔ)法格式CREATEUSER[IFNOTEXISTS]賬號(hào)名[,賬號(hào)名]…多個(gè)賬號(hào)之間使用逗號(hào)分隔，賬號(hào)名是由“用戶名@主機(jī)地址”組成8.4.1賬號(hào)管理#①創(chuàng)建test1賬號(hào)mysql>CREATEUSER'test1'IDENTIFIEDBY'123456';#②創(chuàng)建test2賬號(hào)mysql>CREATEUSER'test2@127.0.0.1'IDENTIFIEDBY'123456';案例：創(chuàng)建賬號(hào)代碼示例test1賬號(hào)：IDENTIFIEDBY后指定字符串形式的明文密碼test2賬號(hào)：指定登錄服務(wù)器的IP地址，限定只有指定的服務(wù)器可以進(jìn)行登錄8.4.1賬號(hào)管理案例：查看創(chuàng)建的賬號(hào)代碼示例mysql>SELECT`host`,`user`FROM`mysql.user`;+-----------+-----------------+|host|user|+-----------+-----------------+|%|test1||%|test2@127.0.0.1||localhost|mysql.session||localhost|mysql.sys||localhost|root|+-----------+-----------------+8.4.1賬號(hào)管理刪除賬號(hào)：某個(gè)賬號(hào)不需要再對(duì)數(shù)據(jù)庫(kù)進(jìn)行管理時(shí)，就將該賬號(hào)刪除。語(yǔ)法格式DROPUSER[IFEXISTS]賬戶名[,賬戶名…];8.4.1賬號(hào)管理案例：刪除賬號(hào)代碼示例mysql>DROPUSERIFEXISTS'test2@127.0.0.1';注意：通過(guò)已刪除的賬號(hào)創(chuàng)建的數(shù)據(jù)庫(kù)信息不會(huì)因此刪除操作而失效。8.4.2權(quán)限管理

先定一個(gè)小目標(biāo)！掌握權(quán)限的管理，能夠利用GRANT語(yǔ)句授予用戶權(quán)限，利用REVOKE語(yǔ)句回收用戶權(quán)限8.4.2權(quán)限管理GRANT語(yǔ)句：用于實(shí)現(xiàn)賬號(hào)權(quán)限的設(shè)置。語(yǔ)法格式GRANT權(quán)限列表ON[目標(biāo)類型]權(quán)限級(jí)別TO賬號(hào)權(quán)限列表：指要賦予的權(quán)限，常見的有數(shù)據(jù)權(quán)限、結(jié)構(gòu)權(quán)限和管理權(quán)限。目標(biāo)類型：默認(rèn)為TABLE，表示將全局、數(shù)據(jù)庫(kù)、表或列中的某些權(quán)限授予給指定的用戶。權(quán)限級(jí)別：用于定義全局權(quán)限、數(shù)據(jù)庫(kù)權(quán)限和表權(quán)限。8.4.2權(quán)限管理不同權(quán)限級(jí)別對(duì)應(yīng)的語(yǔ)法：權(quán)限級(jí)別實(shí)現(xiàn)語(yǔ)法全局權(quán)限GRANT權(quán)限列表ON*.*TO賬戶名[WITHGRANTOPTION];數(shù)據(jù)庫(kù)級(jí)權(quán)限GRANT權(quán)限列表ON數(shù)據(jù)庫(kù)名.*TO賬戶名[WITHGRANTOPTION];表級(jí)權(quán)限GRANT權(quán)限列表ON數(shù)據(jù)庫(kù)名.表名TO賬戶名[WITHGRANTOPTION];列級(jí)權(quán)限GRANT權(quán)限類型（字段列表）[,…]ON數(shù)據(jù)庫(kù)名.表名TO賬戶名[WITHGRANTOPTION];8.4.2權(quán)限管理mysql>GRANTALLPRIVILEGESON`mydb2`.*TO'test1'@'%';QueryOK,0rowsaffected(0.00sec)案例：給用戶授權(quán)代碼示例8.4.2權(quán)限管理mysql>SELECTuser,db,select_priv,insert_priv,update_priv,delete_priv->FROMmysql.dbWHEREuser='test1';+-------+-------+-------------+-------------+-------------+-------------+|user|db|select_priv|insert_priv|update_priv|delete_priv|+-------+-------+-------------+-------------+-------------+-------------+|test1|mydb2|Y|Y|Y|Y|+-------+-------+-------------+-------------+-------------+-------------+1rowinset(0.00sec)案例：查看授權(quán)結(jié)果代碼示例8.4.2權(quán)限管理收回用戶權(quán)限：為了保證數(shù)據(jù)庫(kù)安全，需要將用戶不需要的權(quán)限回收。語(yǔ)法格式REVOKE權(quán)限類型[(字段列表)][,權(quán)限類型[(字段列表)]]…ON[目標(biāo)類型]權(quán)限級(jí)別FROM賬戶名[,賬戶名]…8.4.2權(quán)限管理mysql>REVOKEallONmydb2.*FROM'test1'@'%';QueryOK,0rowsaffected(0.00sec)案例：收回權(quán)限代碼示例8.5索引8.5.1索引的類型

先定一個(gè)小目標(biāo)！熟悉索引的類型，能夠說(shuō)出MySQL中常用的5種類型索引的作用8.5.1索引的類型什么是索引：索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，利用MySQL提供的語(yǔ)法將數(shù)據(jù)表中的某個(gè)字段或某些字段與記錄的位置建立對(duì)應(yīng)關(guān)系，并按照一定的順序排列好，類似于書籍中的目錄，目的就是為了快速定位指定數(shù)據(jù)的位置。8.5.1索引的類型根據(jù)索引實(shí)現(xiàn)語(yǔ)法的不同，索引分類：普通索引唯一性索引主鍵索引全文索引空間索引基本索引類型，使用KEY或INDEX定義，不需要添加任何限制條件，作用是加快對(duì)數(shù)據(jù)的訪問速度。使用UNIQUEINDEX定義，創(chuàng)建唯一性索引的字段需要添加唯一性約束，用于防止用戶添加重復(fù)的值。使用PRIMARYKEY定義，是特殊的唯一性索引，根據(jù)主鍵自身的唯一性標(biāo)識(shí)每條記錄，防止字段值重復(fù)或?yàn)镹ULL。使用FULLTEXTINDEX定義，提高數(shù)據(jù)量較大的字段查詢速度，定義時(shí)字段類型必須是CHAR、VARCHAR或TEXT。使用SPATIALINDEX定義在空間數(shù)據(jù)類型字段上的索引，提高系統(tǒng)獲取空間數(shù)據(jù)的效率。8.5.1索引的類型根據(jù)創(chuàng)建索引的字段個(gè)數(shù)，索引分類：?jiǎn)瘟兴饕冈诒碇袉蝹€(gè)字段上創(chuàng)建的索引，可以是普通索引、唯一索引、主鍵索引或者全文索引，只要保證該索引對(duì)應(yīng)表中一個(gè)字段即可。復(fù)合索引指在表的多個(gè)字段上創(chuàng)建一個(gè)索引，且只有在查詢條件中使用了這些字段中的第一個(gè)字段時(shí)，該索引才會(huì)被使用。8.5.1索引的類型前綴索引：創(chuàng)建的索引從左開始截取數(shù)據(jù)表中字段值的一部分內(nèi)容，前綴索引可以節(jié)約索引空間，提高索引的效率，類似在漢語(yǔ)詞典中根據(jù)偏旁部首（類似前綴索引）查找漢字（要索引的數(shù)據(jù)）。8.5.2索引的使用

先定一個(gè)小目標(biāo)！掌握索引的使用，能夠?yàn)閿?shù)據(jù)表添加合適的索引8.5.2索引的使用mysql>ALTERTABLE`student`ADDINDEXname_index(`name`);QueryOK,0rowsaffected(0.00sec)Records:0Duplicates:0Warnings:0案例：創(chuàng)建普通索引代碼示例INDEX表示創(chuàng)建的索引為普通索引name_index表示為索引定義的名稱name表示在數(shù)據(jù)表student的name字段上創(chuàng)建索引8.5.2索引的使用#查看創(chuàng)建的索引mysql>SHOWCREATETABLE`student`\G******************************1.row************************************Table:studentCreateTable:CREATETABLE`student`(……此處省略字段的創(chuàng)建信息

PRIMARYKEY(`id`),

KEY`name_index`(`name`))ENGINE=InnoDBAUTO_INCREMENT=8DEFAULTCHARSET=utf8案例：創(chuàng)建普通索引代碼示例8.6分區(qū)8.6.1什么是分區(qū)

先定一個(gè)小目標(biāo)！熟悉什么是分區(qū)，能夠說(shuō)出分區(qū)技術(shù)的作用8.6.1什么是分區(qū)分區(qū)技術(shù)：操作數(shù)據(jù)表時(shí)根據(jù)給定的算法，將數(shù)據(jù)在邏輯上分到多個(gè)區(qū)域中存儲(chǔ)，在分區(qū)中還可以設(shè)置子分區(qū)，將數(shù)據(jù)存放到更加具體的區(qū)域內(nèi)。8.6.1什么是分區(qū)分區(qū)技術(shù)：將一張數(shù)據(jù)表中的數(shù)據(jù)存儲(chǔ)在不同物理磁盤中，比單個(gè)磁盤存儲(chǔ)更多的數(shù)據(jù)，如果WHERE子句的條件命中一個(gè)分區(qū)，掃描相關(guān)的一個(gè)分區(qū)而不用全表掃描，提高查詢效率。提示：如果查詢id為3~5的數(shù)據(jù)，掃描磁盤1即可。8.6.1什么是分區(qū)分區(qū)技術(shù)對(duì)存儲(chǔ)引擎以及鎖的要求：分區(qū)技術(shù)不適用于MERGE、CSV或FEDERATED存儲(chǔ)引擎。InnoDB分區(qū)表不能設(shè)置外鍵，同樣地，與外鍵相關(guān)的主表和從表也不能被分區(qū)。MySQL5.7中InnoDB表不支持子分區(qū)在多個(gè)磁盤中存儲(chǔ)，目前僅MyISAM存儲(chǔ)引擎支持。同一個(gè)分區(qū)表的所有分區(qū)必須使用相同存儲(chǔ)引擎。當(dāng)建表時(shí)未指定存儲(chǔ)引擎，在創(chuàng)建分區(qū)時(shí)就必須設(shè)置存儲(chǔ)引擎。MySQL5.6及更早的版本中，對(duì)分區(qū)的MyISAM表進(jìn)行操作時(shí)會(huì)鎖定所有的分區(qū)，直到操作完成后才會(huì)釋放鎖。而在MySQL5.7中，僅會(huì)鎖定與操作相關(guān)的分區(qū)，不會(huì)影響其他分區(qū)。8.6.2創(chuàng)建分區(qū)

先定一個(gè)小目標(biāo)！掌握分區(qū)的創(chuàng)建，能夠?qū)?shù)據(jù)表中的記錄進(jìn)行分區(qū)保存8.6.2創(chuàng)建分區(qū)mysql>CREATETABLE`mydb`.`p_list`(->`id`INTAUTO_INCREMENTCOMMENT'ID編號(hào)',->`name`VARCHAR(50)COMMENT'姓名',->`dpt`INTCOMMENT'部門編號(hào)',->KEY(`id`)->)ENGINE=INNODB->PARTITIONBYLIST(`dpt`)(->PARTITIONp1VALUESIN(1,3),->PARTITIONp2VALUESIN(2,4)

->);案例：創(chuàng)建分區(qū)代碼示例將dpt字段進(jìn)行分區(qū)：值為1或3時(shí)，將記錄放在p1分區(qū)值為2或4時(shí)，將記錄放在p2分區(qū)8.6.2創(chuàng)建分區(qū)#在MySQL的data/mydb目錄下查看分區(qū)數(shù)據(jù)文件p_list#p#p1.ibdp_list#p#p2.ibd案例：創(chuàng)建分區(qū)代碼示例p_list是建立分區(qū)的數(shù)據(jù)表名，p1和p2表示分區(qū)名稱。8.7存儲(chǔ)過(guò)程8.7.1初識(shí)存儲(chǔ)過(guò)程

先定一個(gè)小目標(biāo)！熟悉存儲(chǔ)過(guò)程，能夠說(shuō)出存儲(chǔ)過(guò)程的作用，能說(shuō)出存儲(chǔ)過(guò)程與函數(shù)的區(qū)別8.7.1初識(shí)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程：一種在數(shù)據(jù)庫(kù)中存儲(chǔ)復(fù)雜程序，以便外部程序調(diào)用的一種數(shù)據(jù)庫(kù)對(duì)象，它是在大型數(shù)據(jù)庫(kù)系統(tǒng)中用于完成特定功能的一組SQL語(yǔ)句集，在第一次使用時(shí)經(jīng)過(guò)編譯后，再次調(diào)用就不需要重復(fù)編譯，執(zhí)行效率比較高。8.7.1初識(shí)存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程與函數(shù)的相同點(diǎn)：目的都是為了可重復(fù)地執(zhí)行數(shù)據(jù)庫(kù)SQL語(yǔ)句的集合，經(jīng)過(guò)一次編譯后，后面再次需要時(shí)直接執(zhí)行即可。存儲(chǔ)過(guò)程與函數(shù)的不同點(diǎn)：語(yǔ)法中實(shí)現(xiàn)的標(biāo)識(shí)符不同，存儲(chǔ)過(guò)程使用PROCEDURE，函數(shù)為FUNCTION。存儲(chǔ)過(guò)程在創(chuàng)建時(shí)沒有返回值，而函數(shù)在定義時(shí)必須設(shè)置返回值。存儲(chǔ)過(guò)程沒有返回值類型，且不能將結(jié)果直接賦值給變量；而函數(shù)定義時(shí)需要設(shè)置返回值類型，且在調(diào)用時(shí)必須將返回值賦給變量。存儲(chǔ)過(guò)程必須通過(guò)CALL進(jìn)行調(diào)用，不能使用SELECT調(diào)用；而函數(shù)則可在SELECT語(yǔ)句中直接使用。8.7.2存儲(chǔ)過(guò)程的使用

先定一個(gè)小目標(biāo)！掌握存儲(chǔ)過(guò)程的使用，能夠創(chuàng)建存儲(chǔ)過(guò)程進(jìn)行數(shù)據(jù)的查詢操作8.7.2存儲(chǔ)過(guò)程的使用mysql>DELIMITER$$#將語(yǔ)句的結(jié)束符號(hào)從分號(hào);臨時(shí)改為兩個(gè)$$(可以是自定義)mysql>CREATEPROCEDUREproc(INgidINT)->BEGIN->SELECT`id`,`name`FROM`my_goods`where`id`>`gid`;->END->$$Query0K,0rowsaffected(0.00sec)mysql>DELIMITER;

#將語(yǔ)句的結(jié)束符號(hào)恢復(fù)為分號(hào)案例：創(chuàng)建存儲(chǔ)過(guò)程代碼示例gid表示調(diào)用存儲(chǔ)過(guò)程時(shí)傳入的參數(shù)8.7.2存儲(chǔ)過(guò)程的使用mysql>CALLproc(9);+----+--------+|id|name|+----+--------+|10|薄毛衣|+----+--------+1rowinset(0.00sec)QueryOK,0rowsaffected(0.03sec)案例：調(diào)用存儲(chǔ)過(guò)程代碼示例表示執(zhí)行存儲(chǔ)過(guò)程體內(nèi)的SQL語(yǔ)句的描述信息表示調(diào)用存儲(chǔ)過(guò)程的結(jié)果描述信息。8.8觸發(fā)器8.8.1什么是觸發(fā)器

先定一個(gè)小目標(biāo)！熟悉什么是觸發(fā)器，能夠說(shuō)出觸發(fā)器的作用，能夠說(shuō)出觸發(fā)器的優(yōu)點(diǎn)與缺點(diǎn)8.8.1什么是觸發(fā)器觸發(fā)器：可以看作是一種特殊類型的存儲(chǔ)過(guò)程，它與存儲(chǔ)過(guò)程的區(qū)別在于存儲(chǔ)過(guò)程使用時(shí)需要調(diào)用，而觸發(fā)器是在預(yù)先定義好的事件（如INSERT、DELETE等操作）發(fā)生時(shí)，才會(huì)被MySQL自動(dòng)調(diào)用。觸發(fā)器的優(yōu)點(diǎn)：觸發(fā)器可以通過(guò)數(shù)據(jù)庫(kù)中的相關(guān)表實(shí)現(xiàn)級(jí)聯(lián)無(wú)痕更改操作。保證數(shù)據(jù)安全，進(jìn)行安全校驗(yàn)。觸發(fā)器的缺點(diǎn)：觸發(fā)器的使用會(huì)影響數(shù)據(jù)庫(kù)的結(jié)構(gòu)，同時(shí)增加了維護(hù)的復(fù)雜程度。觸發(fā)器的無(wú)痕操作會(huì)造成數(shù)據(jù)在程序（如PHP、Java等）層面不可控。8.8.2觸發(fā)器的使用

先定一個(gè)小目標(biāo)！掌握觸發(fā)器的使用，能夠?yàn)閿?shù)據(jù)表設(shè)置觸發(fā)器，使其在特定的時(shí)機(jī)觸發(fā)8.8.2觸發(fā)器的使用CREATETABLE`my_shopcart`(`id`INTUNSIGNEDPRIMARYKEYAUTO_INCREMENTCOMMENT'購(gòu)物車id',`user_id`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'用戶id',`goods_id`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'商品id',`goods_price`DECIMAL(10,2)UNSIGNEDNOTNULLDEFAULT0COMMENT'單價(jià)',`goods_num`INTUNSIGNEDNOTNULLDEFAULT0COMMENT'購(gòu)買件數(shù)',`is_select`TINYINTUNSIGNEDNOTNULLDEFAULT0COMMENT'是否選中',`create_time`DATETIMENOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'創(chuàng)建時(shí)間',`update_time`DATETIMEDEFAULTNULLCOMMENT'更新時(shí)間')ENGINE=InnoDBDEFAULTCHARSET=utf8;案例：觸發(fā)器的使用代碼示例STEP01創(chuàng)建my_shopcart購(gòu)物車表。以my_goods和my_shopcart為例，演示添加商品到購(gòu)物車后自動(dòng)減少商品庫(kù)存。8.8.2觸發(fā)器的使用mysql>DELIMITER$$mysql>CR