第8章MySQL進(jìn)階《PHP+MySQL動(dòng)態(tài)網(wǎng)站開發(fā)》學(xué)習(xí)目標(biāo)/Target

了解事務(wù)的概念，能夠說出什么是事務(wù)和事務(wù)的4個(gè)特性。

掌握事務(wù)處理，能夠使用事務(wù)處理復(fù)雜的數(shù)據(jù)操作。

掌握事務(wù)保存點(diǎn)的基本使用方法，能夠在事務(wù)中正確使用保存點(diǎn)。

了解視圖的概念，能夠說出視圖的優(yōu)點(diǎn)。

掌握視圖的創(chuàng)建，能夠根據(jù)需求創(chuàng)建視圖。學(xué)習(xí)目標(biāo)/Target

掌握視圖的管理，能夠修改視圖和刪除視圖。

掌握視圖數(shù)據(jù)操作，能夠通過視圖查詢、添加、修改和刪除數(shù)據(jù)。

掌握數(shù)據(jù)備份和數(shù)據(jù)還原，能夠通過命令備份數(shù)據(jù)和還原數(shù)據(jù)。

掌握用戶的管理方法，能夠創(chuàng)建用戶和刪除用戶。

掌握權(quán)限的管理方法，能夠?yàn)槠渌脩羰谟铏?quán)限和回收權(quán)限。學(xué)習(xí)目標(biāo)/Target

了解索引的概念，能夠說出索引的分類。

掌握索引的創(chuàng)建方法，能夠根據(jù)需求創(chuàng)建索引。

了解分區(qū)技術(shù)，能夠說出數(shù)據(jù)分區(qū)的實(shí)現(xiàn)原理。

掌握創(chuàng)建分區(qū)的語法格式，能夠創(chuàng)建分區(qū)。

了解存儲(chǔ)過程的概念，能夠說出使用存儲(chǔ)過程的優(yōu)點(diǎn)。學(xué)習(xí)目標(biāo)/Target

掌握存儲(chǔ)過程的創(chuàng)建和調(diào)用，能夠創(chuàng)建和調(diào)用存儲(chǔ)過程。

了解觸發(fā)器的概念，能夠說出觸發(fā)器的優(yōu)點(diǎn)和缺點(diǎn)。

掌握觸發(fā)器的創(chuàng)建和使用，能夠給數(shù)據(jù)表創(chuàng)建觸發(fā)器。章節(jié)概述/Summary前面的章節(jié)已經(jīng)講解了MySQL的基本操作，包括數(shù)據(jù)的增加、刪除、修改、查詢等操作。在實(shí)際開發(fā)中，有時(shí)還需要利用事務(wù)保證一組SQL語句同時(shí)執(zhí)行成功或失敗、利用視圖滿足不同的查詢需求、利用索引提高數(shù)據(jù)庫的查詢效率等，這就需要學(xué)習(xí)MySQL中的一些進(jìn)階技術(shù)。本章將對(duì)MySQL中的事務(wù)、視圖、數(shù)據(jù)備份和數(shù)據(jù)還原、用戶與權(quán)限、索引、分區(qū)技術(shù)、存儲(chǔ)過程和觸發(fā)器進(jìn)行詳細(xì)講解。目錄/Contents8.18.28.3事務(wù)視圖數(shù)據(jù)備份和數(shù)據(jù)還原8.4用戶與權(quán)限目錄/Contents8.58.68.7索引分區(qū)技術(shù)存儲(chǔ)過程8.8觸發(fā)器事務(wù)8.1

先定一個(gè)小目標(biāo)！了解事務(wù)的概念，能夠說出什么是事務(wù)和事務(wù)的4個(gè)特性。8.1.1事務(wù)概述在MySQL中，事務(wù)是針對(duì)數(shù)據(jù)庫的一組操作，它可以由一條或多條SQL語句組成，且每條SQL語句是相互依賴的。事務(wù)執(zhí)行過程中，只要有一條SQL語句執(zhí)行失敗或發(fā)生錯(cuò)誤，其他語句就都不會(huì)執(zhí)行。也就是說，要么事務(wù)的執(zhí)行成功，要么數(shù)據(jù)庫的狀態(tài)退回到執(zhí)行事務(wù)前的狀態(tài)。8.1.1事務(wù)概述事務(wù)的特性原子性（Atomicity）持久性（Durability）一致性（Consistency）隔離性（Isolation）8.1.1事務(wù)概述原子性是指一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，只有事務(wù)中所有的數(shù)據(jù)操作都執(zhí)行成功，整個(gè)事務(wù)才算執(zhí)行成功。事務(wù)中如果有任何一條SQL語句執(zhí)行失敗，已經(jīng)執(zhí)行成功的SQL語句也必須撤銷，數(shù)據(jù)庫的狀態(tài)退回到執(zhí)行事務(wù)前的狀態(tài)。原子性8.1.1事務(wù)概述一致性是指在使用事務(wù)時(shí)，無論執(zhí)行成功還是失敗，都要使數(shù)據(jù)庫處于一致的狀態(tài)，保證數(shù)據(jù)庫不會(huì)返回到一個(gè)未處理的事務(wù)中。MySQL中事務(wù)的一致性主要由日志機(jī)制實(shí)現(xiàn)，通過日志記錄數(shù)據(jù)庫的所有變化，為事務(wù)恢復(fù)提供跟蹤記錄。一致性8.1.1事務(wù)概述隔離性是指當(dāng)一個(gè)事務(wù)在執(zhí)行時(shí)，不會(huì)受到其他事務(wù)的影響。隔離性保證了未完成事務(wù)的所有操作與數(shù)據(jù)庫系統(tǒng)的隔離，直到事務(wù)完成后才能看到事務(wù)的執(zhí)行結(jié)果。隔離性8.1.1事務(wù)概述持久性是指事務(wù)一旦提交，其對(duì)數(shù)據(jù)庫中數(shù)據(jù)的修改就是永久性的。事務(wù)不能做到百分之百的持久性，只能從事務(wù)本身的角度來保證持久性，而如果一些外部原因（如硬盤損壞等）導(dǎo)致數(shù)據(jù)庫發(fā)生故障，那么數(shù)據(jù)庫中的所有數(shù)據(jù)都有可能會(huì)丟失。持久性8.1.1事務(wù)概述

先定一個(gè)小目標(biāo)！掌握事務(wù)的處理，能夠使用事務(wù)處理復(fù)雜的數(shù)據(jù)操作。8.1.2事務(wù)處理默認(rèn)情況下，用戶執(zhí)行的每一條SQL語句都會(huì)被當(dāng)成單獨(dú)的事務(wù)自動(dòng)提交。要將一組SQL語句作為一個(gè)事務(wù)，則需要先啟動(dòng)事務(wù)，開啟事務(wù)的語句如下：STARTTRANSACTION;開啟事務(wù)后，每一條SQL語句不再自動(dòng)提交，而需要手動(dòng)提交，手動(dòng)提交事務(wù)的語句如下：COMMIT;如果不想提交當(dāng)前事務(wù)，可以將事務(wù)取消（即回滾事務(wù)），回滾事務(wù)的語句如下：ROLLBACK;8.1.2事務(wù)處理通過案例演示如何使用事務(wù)。8.1.2事務(wù)處理步驟一：創(chuàng)建用戶表并插入數(shù)據(jù)：CREATETABLEmy_user(idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENTCOMMENT'用戶id',nameVARCHAR(100)NOTNULLUNIQUEDEFAULT''COMMENT'用戶名',moneyDECIMAL(10,2)UNSIGNEDNOTNULLDEFAULT0COMMENT'金額');INSERTINTOmy_user(id,name,money)VALUES(1,'Alex',1000),(2,'Bill',1000);8.1.2事務(wù)處理步驟二：查詢用戶表的數(shù)據(jù)：mysql>SELECTname,moneyFROMmy_user;+------+---------+|name|money|+------+---------+|Alex|1000.00||Bill|1000.00|+------+---------+8.1.2事務(wù)處理步驟三：開啟事務(wù)，使用UPDATE語句將Alex的100元錢轉(zhuǎn)給Bill，提交事務(wù)：#開啟事務(wù)mysql>STARTTRANSACTION;#Alex的金額減少100元mysql>UPDATEmy_userSETmoney=money-100WHEREname='Alex';#Bill的金額增加100元mysql>UPDATEmy_userSETmoney=money+100WHEREname='Bill';#提交事務(wù)mysql>COMMIT;8.1.2事務(wù)處理步驟四：使用SELECT語句查詢Alex和Bill的金額：mysql>SELECTname,moneyFROMmy_user;+------+----------+|name|money|+------+----------+|Alex|

900.00

||Bill|1100.00

|+------+----------+從查詢結(jié)果可以看出，通過事務(wù)成功完成了轉(zhuǎn)賬。8.1.2事務(wù)處理步驟五：測(cè)試事務(wù)的回滾。開啟事務(wù)后，將Bill的金額減少100元，查詢Bill的金額：mysql>STARTTRANSACTION;mysql>UPDATEmy_userSETmoney=money-100WHEREname='Bill';mysql>SELECTname,moneyFROMmy_userWHEREname='Bill';+------+---------+|name|money|+------+---------+|Bill|1000.00|+------+---------+8.1.2事務(wù)處理步驟六：回滾事務(wù)后查詢Bill的金額：mysql>ROLLBACK;#回滾事務(wù)mysql>SELECTname,moneyFROMmy_userWHEREname='Bill';+------+---------+|name|money|+------+---------+|Bill|1100.00

|+------+---------+Bill的金額恢復(fù)成1100元，說明事務(wù)回滾成功。8.1.2事務(wù)處理

先定一個(gè)小目標(biāo)！掌握事務(wù)保存點(diǎn)的基本使用方法，能夠在事務(wù)中正確使用保存點(diǎn)。8.1.3事務(wù)保存點(diǎn)回滾事務(wù)時(shí)，事務(wù)內(nèi)的所有操作都將撤銷。如果希望只撤銷一部分，可以使用事務(wù)保存點(diǎn)來實(shí)現(xiàn)。在事務(wù)中設(shè)置保存點(diǎn)的語句：SAVEPOINT保存點(diǎn)名稱;設(shè)置保存點(diǎn)后，可以將事務(wù)回滾到指定保存點(diǎn)?；貪L到指定保存點(diǎn)的語句：ROLLBACKTOSAVEPOINT保存點(diǎn)名稱;如果不再需要使用某個(gè)保存點(diǎn)，可以將這個(gè)保存點(diǎn)刪除。刪除保存點(diǎn)的語句：RELEASESAVEPOINT保存點(diǎn)名稱;8.1.3事務(wù)保存點(diǎn)在一個(gè)事務(wù)中可以創(chuàng)建多個(gè)保存點(diǎn)，提交事務(wù)后，事務(wù)中的保存點(diǎn)就會(huì)被刪除。另外，在事務(wù)回滾至某個(gè)保存點(diǎn)后，該保存點(diǎn)之后創(chuàng)建的保存點(diǎn)都會(huì)消失。通過案例演示事務(wù)保存點(diǎn)的使用。8.1.3事務(wù)保存點(diǎn)步驟一：查詢Alex的金額：mysql>SELECTname,moneyFROMmy_user

->WHEREname='Alex';+------+--------+|name|money|+------+--------+|Alex|900.00

|+------+--------+8.1.3事務(wù)保存點(diǎn)步驟二：開啟事務(wù)，將Alex的金額扣除100元，創(chuàng)建保存點(diǎn)S1：#開啟事務(wù)mysql>STARTTRANSACTION;#Alex的金額扣除100元mysql>UPDATEmy_userSETmoney=money-100WHEREname='Alex';#創(chuàng)建保存點(diǎn)S1mysql>SAVEPOINTS1;8.1.3事務(wù)保存點(diǎn)步驟三：查詢Alex的金額：SELECTname,moneyFROMmy_userWHEREname='Alex';步驟四：再將Alex金額扣除50元：#Alex的金額再扣除50元mysql>UPDATEmy_userSETmoney=money-50WHEREname='Alex';8.1.3事務(wù)保存點(diǎn)步驟五：查詢Alex的金額：SELECTname,moneyFROMmy_userWHEREname='Alex';步驟六：將事務(wù)回滾到保存點(diǎn)S1，查詢Alex的金額：#回滾到保存點(diǎn)S1mysql>ROLLBACKTOSAVEPOINTS1;#查詢Alex的金額mysql>SELECTname,moneyFROMmy_user

->WHEREname='Alex';+------+--------+|name|money|+------+--------+|Alex|800.00|+------+--------+Alex的金額只減少了100元，說明當(dāng)前恢復(fù)到了保存點(diǎn)S1時(shí)的狀態(tài)8.1.3事務(wù)保存點(diǎn)步驟七：再次回滾事務(wù)，查詢Alex的金額：#回滾事務(wù)mysql>ROLLBACK;#查看Alex的金額mysql>SELECTname,moneyFROMmy_userWHEREname='Alex';+------+--------+|name|money|+------+--------+|Alex|900.00|+------+--------+Alex的金額與事務(wù)開啟時(shí)的金額相同，說明事務(wù)恢復(fù)到了事務(wù)開啟時(shí)的狀態(tài)8.1.3事務(wù)保存點(diǎn)視圖8.2

先定一個(gè)小目標(biāo)！了解視圖的概念，能夠說出視圖的優(yōu)點(diǎn)。8.2.1視圖概述視圖是一種虛擬表，視圖的結(jié)構(gòu)和數(shù)據(jù)來源于數(shù)據(jù)庫中的數(shù)據(jù)表。從概念上講，數(shù)據(jù)庫中的數(shù)據(jù)表被稱為基本表。通過視圖不僅可以看到基本表中的數(shù)據(jù)，還可以對(duì)基本表中的數(shù)據(jù)進(jìn)行添加、修改和刪除。數(shù)據(jù)庫只保存視圖的定義，不保存視圖對(duì)應(yīng)的數(shù)據(jù)。若基本表中的數(shù)據(jù)發(fā)生了變化，通過視圖查詢出來的數(shù)據(jù)也會(huì)發(fā)生變化。若通過視圖修改數(shù)據(jù)時(shí)，基本表中的數(shù)據(jù)也會(huì)發(fā)生變化。8.2.1視圖概述視圖不僅可以簡(jiǎn)化用戶對(duì)數(shù)據(jù)的理解，還可以簡(jiǎn)化用戶對(duì)數(shù)據(jù)的操作。例如，在日常開發(fā)中經(jīng)常使用一個(gè)比較復(fù)雜的語句進(jìn)行查詢，此時(shí)就可以將該語句定義為視圖，從而避免大量重復(fù)且復(fù)雜的查詢操作。簡(jiǎn)化查詢語句8.2.1視圖概述與直接操作基本表相比，視圖具有以下優(yōu)點(diǎn)。通過視圖可以很方便地進(jìn)行權(quán)限控制，指定某個(gè)用戶只能查詢和修改指定數(shù)據(jù)，例如，不負(fù)責(zé)處理工資單的員工，不能查看員工的工資信息。安全性8.2.1視圖概述與直接操作基本表相比，視圖具有以下優(yōu)點(diǎn)。視圖可以幫助用戶屏蔽數(shù)據(jù)表結(jié)構(gòu)變化帶來的影響，例如，數(shù)據(jù)表增加字段，不會(huì)影響基于該數(shù)據(jù)表創(chuàng)建的視圖。數(shù)據(jù)獨(dú)立性8.2.1視圖概述與直接操作基本表相比，視圖具有以下優(yōu)點(diǎn)。

先定一個(gè)小目標(biāo)！掌握視圖的創(chuàng)建，能夠根據(jù)需求創(chuàng)建視圖。8.2.2創(chuàng)建視圖使用CREATEVIEW語句創(chuàng)建視圖的語法格式：CREATEVIEW視圖

AS查詢語句;注意：使用SHOWTABLES語句查看數(shù)據(jù)表時(shí)，查詢結(jié)果中會(huì)包含數(shù)據(jù)表和視圖。在同一個(gè)數(shù)據(jù)庫中，為了區(qū)分?jǐn)?shù)據(jù)表和視圖，在創(chuàng)建視圖時(shí)，視圖名稱和數(shù)據(jù)表名稱不能相同，建議在給視圖命名時(shí)添加“view_”前綴或“_view”后綴。8.2.2創(chuàng)建視圖通過案例演示視圖的使用。8.2.2創(chuàng)建視圖步驟一：創(chuàng)建視圖view_student，查詢專業(yè)表和學(xué)生表的數(shù)據(jù)：CREATEVIEWview_studentASSELECTs.id,,ASmajorFROMmajorsASmLEFTJOINstudentASsONm.id=s.mid;8.2.2創(chuàng)建視圖步驟二：查看mydb數(shù)據(jù)庫中的所有數(shù)據(jù)表：mysql>SHOWTABLES;+-----------------+|Tables_in_mydb|+-----------------+|goods

||majors|……|student

||view_student

|+-----------------+27rowsinset(0.01sec)8.2.2創(chuàng)建視圖步驟三：查詢view_student視圖：mysql>SELECT*FROMview_student;+----+------+-------+|id|name|major|+----+------+-------+|3|Alen|前端|+----+------+-------+1rowinset(0.00sec)8.2.2創(chuàng)建視圖

先定一個(gè)小目標(biāo)！掌握視圖的管理，能夠修改視圖和刪除視圖。8.2.3視圖管理使用ALTERVIEW語句可以修改視圖，使用DROPVIEW語句可以刪除視圖。修改視圖和刪除視圖的語法格式：#修改視圖ALTERVIEW視圖名稱AS查詢語句;#刪除視圖DROPVIEW[IFEXISTS]視圖名稱;8.2.3視圖管理通過案例演示視圖的修改和刪除。8.2.3視圖管理步驟一：修改view_student視圖，去掉查詢語句中的id字段：ALTERVIEWview_studentASSELECT,ASmajorFROMmajorsASmLEFTJOINstudentASsONm.id=s.mid;8.2.3視圖管理步驟二：查看修改后的view_student視圖：mysql>SELECT*FROMview_student;+------+-------+|name|major|+------+-------+|Alen|前端|+------+-------+1rowinset(0.00sec)8.2.3視圖管理步驟三：刪除view_student視圖：mysql>DROPVIEWview_student;QueryOK,0rowsaffected(0.01sec)8.2.3視圖管理步驟四：檢查view_student視圖是否已被刪除：mysql>SELECT*FROMview_student;Error1146(42S02):Table'mydb.view_student'doesn'texistview_student不存在，說明視圖刪除成功8.2.3視圖管理

先定一個(gè)小目標(biāo)！掌握視圖數(shù)據(jù)操作，能夠通過視圖查詢、添加、修改和刪除數(shù)據(jù)。8.2.4視圖數(shù)據(jù)操作視圖是虛擬表，不保存數(shù)據(jù)。通過視圖進(jìn)行數(shù)據(jù)操作時(shí)，實(shí)際上操作的是基本表中的數(shù)據(jù)。通常對(duì)基于單表的視圖進(jìn)行數(shù)據(jù)操作，主要的操作包括查詢數(shù)據(jù)、添加數(shù)據(jù)、修改數(shù)據(jù)和刪除數(shù)據(jù)。8.2.4視圖數(shù)據(jù)操作通過案例演示對(duì)視圖進(jìn)行數(shù)據(jù)操作。8.2.4視圖數(shù)據(jù)操作步驟一：創(chuàng)建view_student視圖，通過視圖添加數(shù)據(jù)，姓名為Sun，專業(yè)id為2：mysql>CREATEVIEWview_studentAS

->SELECTid,name,midFROMstudent;8.2.4視圖數(shù)據(jù)操作步驟二：通過視圖添加數(shù)據(jù)，數(shù)據(jù)對(duì)應(yīng)的姓名為Sun，專業(yè)id為2：mysql>INSERTINTOview_studentVALUES(NULL,'Sun',2);步驟三：通過視圖查詢數(shù)據(jù)：mysql>SELECT*FROMview_student;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|

2||4|Sun|

2|+----+------+-----+2rowsinset(0.00sec)8.2.4視圖數(shù)據(jù)操作步驟四：通過視圖更新數(shù)據(jù)，將專業(yè)id為4的數(shù)據(jù)的name字段修改為Sun1，查詢數(shù)據(jù)：mysql>SELECT*FROMview_student;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|

2||4|Sun1|

2|+----+------+-----+8.2.4視圖數(shù)據(jù)操作步驟五：通過視圖查詢數(shù)據(jù)：mysql>UPDATEview_studentSETname='Sun1'WHEREid=4;步驟六：通過視圖刪除數(shù)據(jù)：mysql>DELETEFROMview_studentWHEREid=4;8.2.4視圖數(shù)據(jù)操作mysql>SELECT*FROMview_student;+----+------+-----+|id|name|mid|+----+------+-----+|3|Alen|2|+----+------+-----+1rowinset(0.00sec)步驟七：通過視圖查詢數(shù)據(jù)：數(shù)據(jù)備份和數(shù)據(jù)還原8.3

先定一個(gè)小目標(biāo)！掌握數(shù)據(jù)備份，能夠通過命令備份數(shù)據(jù)。8.3.1數(shù)據(jù)備份在MySQL的bin目錄中提供了mysqldump命令行工具。該工具用于將數(shù)據(jù)庫的數(shù)據(jù)導(dǎo)出成SQL腳本，實(shí)現(xiàn)數(shù)據(jù)的備份。使用mysqldump命令備份數(shù)據(jù)庫或數(shù)據(jù)表時(shí)，不需要登錄MySQL，直接在命令行窗口執(zhí)行命令即可。mysqldump命令可以備份單個(gè)數(shù)據(jù)庫或數(shù)據(jù)表、備份多個(gè)數(shù)據(jù)庫和備份所有數(shù)據(jù)庫。8.3.1數(shù)據(jù)備份1.備份單個(gè)數(shù)據(jù)庫或數(shù)據(jù)表使用mysqldump命令備份單個(gè)數(shù)據(jù)庫或數(shù)據(jù)表的語法格式：-u后面的username表示登錄的用戶名；-p后面的password表示密碼；數(shù)據(jù)表名稱可以指定一個(gè)或多個(gè)，多個(gè)數(shù)據(jù)表名稱之間使用空格分隔，如果不指定數(shù)據(jù)表名稱則備份整個(gè)數(shù)據(jù)庫。8.3.1數(shù)據(jù)備份mysqldump–uusername–ppassword

數(shù)據(jù)庫名稱[數(shù)據(jù)表名稱1[數(shù)據(jù)表名稱2...]]>filename.sql2.備份多個(gè)數(shù)據(jù)庫使用mysqldump命令備份多個(gè)數(shù)據(jù)庫的語法格式：--databases表示備份數(shù)據(jù)庫，該參數(shù)后面應(yīng)至少指定一個(gè)數(shù)據(jù)庫名稱。8.3.1數(shù)據(jù)備份mysqldump–uusername–ppassword

--databases數(shù)據(jù)庫名稱1[數(shù)據(jù)庫名稱2...]>filename.sql3.備份所有數(shù)據(jù)庫使用mysqldump命令備份所有數(shù)據(jù)庫的語法格式：mysqldump–uusername–ppassword

--all-databases>filename.sql--all-databases表示備份所有數(shù)據(jù)庫。通過案例演示使用mysqldump命令備份單個(gè)數(shù)據(jù)庫。8.3.1數(shù)據(jù)備份步驟一：在命令提示符窗口中切換到C:\web\mysql8.0\bin目錄：cdC:\web\mysql8.0\bin8.3.1數(shù)據(jù)備份步驟二：備份mydb數(shù)據(jù)庫：mysqldump-uroot-pmydb>D:\mydb.sql查看備份文件：8.3.1數(shù)據(jù)備份

先定一個(gè)小目標(biāo)！掌握數(shù)據(jù)還原，能夠通過命令還原數(shù)據(jù)。8.3.2數(shù)據(jù)還原在數(shù)據(jù)備份后，當(dāng)數(shù)據(jù)丟失、損壞或需要遷移時(shí)，數(shù)據(jù)就可以通過備份文件還原。數(shù)據(jù)還原有兩種常用的方式，分別是使用mysql命令還原數(shù)據(jù)和使用source命令還原數(shù)據(jù)。8.3.2數(shù)據(jù)還原1.使用mysql命令還原數(shù)據(jù)使用mysql命令還原數(shù)據(jù)的語法格式：mysql-uusername-ppassword

[數(shù)據(jù)庫名稱]<filename.sql8.3.2數(shù)據(jù)還原username表示登錄的用戶名。password表示密碼。<filename.sql表示使用輸入重定向讀取SQL腳本還原數(shù)據(jù)。通過案例演示將mydb數(shù)據(jù)庫的備份數(shù)據(jù)還原到mydb2數(shù)據(jù)庫中。8.3.2數(shù)據(jù)還原步驟一：登錄MySQL，創(chuàng)建mydb2數(shù)據(jù)庫后退出MySQL：mysql>CREATEDATABASEmydb2;mysql>exit;8.3.2數(shù)據(jù)還原步驟二：還原數(shù)據(jù)后查看數(shù)據(jù)：mysql-uroot-pmydb2<D:\mydb.sql8.3.2數(shù)據(jù)還原2.使用source命令還原數(shù)據(jù)source命令是MySQL客戶端提供的命令。使用source命令還原數(shù)據(jù)時(shí)，需要登錄MySQL，在登錄后的狀態(tài)下執(zhí)行該命令。使用source命令還原數(shù)據(jù)的語法格式：source文件路徑8.3.2數(shù)據(jù)還原通過案例演示使用source命令將mydb數(shù)據(jù)庫的備份數(shù)據(jù)還原到mydb3數(shù)據(jù)庫中。8.3.2數(shù)據(jù)還原步驟一：登錄MySQL，創(chuàng)建mydb3數(shù)據(jù)庫：mysql>CREATEDATABASEmydb3;8.3.2數(shù)據(jù)還原步驟二：還原數(shù)據(jù)：mysql>sourceD:/mydb.sql用戶與權(quán)限8.4

先定一個(gè)小目標(biāo)！掌握用戶的管理方法，能夠創(chuàng)建用戶和刪除用戶。8.4.1用戶管理1.創(chuàng)建用戶在MySQL中，使用CREATEUSER語句創(chuàng)建用戶的語法格式：CREATEUSER[IFNOTEXISTS]賬戶名[,賬戶名…][IDENTIFIEDBY'密碼']賬戶名的格式為“用戶名@主機(jī)名”。用戶名是登錄MySQL服務(wù)器時(shí)使用的，用戶名不超過32個(gè)字符且區(qū)分大小寫。主機(jī)名是登錄MySQL服務(wù)器時(shí)所使用的地址，主機(jī)名可以是IP地址或字符串。如果允許任何主機(jī)連接MySQL服務(wù)器，則主機(jī)名可以使用通配符“%”或“''”（空字符串）表示。8.4.1用戶管理創(chuàng)建test1用戶，可以從任意主機(jī)登錄：CREATEUSER'test1'IDENTIFIEDBY'123456';創(chuàng)建test2用戶，只能通過指定的主機(jī)登錄：CREATEUSER'test2'@''IDENTIFIEDBY'123456';8.4.1用戶管理查看創(chuàng)建的test1用戶和test2用戶：mysql>SELECThost,userFROMmysql.user;+-----------+------------------+|host|user|+-----------+------------------+|%

|test1||

|test2|……+-----------+------------------+%表示test1用戶可以通過任意主機(jī)連接MySQL服務(wù)器。表示test2用戶可以通過IP是的主機(jī)登錄MySQL服務(wù)器。8.4.1用戶管理2.刪除用戶如果某個(gè)用戶不再需要管理數(shù)據(jù)庫，就可以將其刪除。刪除用戶的語法格式：DROPUSER[IFEXISTS]賬戶名[,賬戶名...];刪除test2用戶：DROPUSERIFEXISTS'test2'@'';8.4.1用戶管理

先定一個(gè)小目標(biāo)！掌握權(quán)限的管理方法，能夠給其他用戶授予權(quán)限和回收權(quán)限。8.4.2權(quán)限管理1.授予權(quán)限MySQL提供的GRANT語句用于給用戶授予權(quán)限，基本語法格式：GRANT權(quán)限類型[(字段列表)][,權(quán)限類型[(字段列表)]...]ON[目標(biāo)類型]權(quán)限級(jí)別TO賬戶名[,賬戶名...];權(quán)限類型是指要賦予的權(quán)限。常見的權(quán)限有數(shù)據(jù)庫權(quán)限、數(shù)據(jù)庫對(duì)象權(quán)限和結(jié)構(gòu)權(quán)限，使用ALLPRIVILEGES表示賦予所有權(quán)限。字段列表用于指定授予列權(quán)限的列名。目標(biāo)類型的可選值有3個(gè)，分別是TABLE、FUNCTION和PROCEDURE，它們分別表示給數(shù)據(jù)表、自定義函數(shù)和存儲(chǔ)過程授予權(quán)限，默認(rèn)值為TABLE。權(quán)限級(jí)別是指權(quán)限可以被應(yīng)用在哪些數(shù)據(jù)庫的內(nèi)容中。8.4.2權(quán)限管理設(shè)置權(quán)限級(jí)別的6種方式：*：給默認(rèn)數(shù)據(jù)庫授予權(quán)限，如果沒有默認(rèn)數(shù)據(jù)庫，則會(huì)發(fā)生錯(cuò)誤。*.*：全局權(quán)限，可以給任意數(shù)據(jù)庫中的任意內(nèi)容授予權(quán)限。數(shù)據(jù)庫名稱.*：數(shù)據(jù)庫權(quán)限，可以給指定數(shù)據(jù)庫下的任意內(nèi)容授予權(quán)限。數(shù)據(jù)庫名稱.數(shù)據(jù)表名稱：數(shù)據(jù)表權(quán)限，可以給指定數(shù)據(jù)庫中的指定數(shù)據(jù)表授予權(quán)限。數(shù)據(jù)表名稱：列權(quán)限，給指定數(shù)據(jù)庫中的指定數(shù)據(jù)表的指定字段授予權(quán)限時(shí)，需要在字段列表中指定列名稱。數(shù)據(jù)庫名稱.存儲(chǔ)過程：存儲(chǔ)過程權(quán)限，可以給指定數(shù)據(jù)庫中的指定存儲(chǔ)過程授予權(quán)限。8.4.2權(quán)限管理給test1用戶授予mydb2數(shù)據(jù)庫的所有權(quán)限：GRANTALLPRIVILEGESONmydb2.*TO'test1'@'%';在mysql.db數(shù)據(jù)表中查看test1用戶的權(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|+-------+-------+-------------+------------+-------------+------------+8.4.2權(quán)限管理2.回收權(quán)限在MySQL中，為了保證數(shù)據(jù)庫的安全，需要將用戶不必?fù)碛械臋?quán)限回收。MySQL提供了REVOKE語句用于回收指定用戶的權(quán)限：REVOKE[IFEXISTS]權(quán)限類型[(字段列表)][,權(quán)限類型[(字段列表)]...]ON[目標(biāo)類型]權(quán)限級(jí)別FROM賬戶名[,賬戶名...]回收test1用戶對(duì)mydb2數(shù)據(jù)庫的權(quán)限：REVOKEALLONmydb2.*FROM'test1'@'%';8.4.2權(quán)限管理索引8.5

先定一個(gè)小目標(biāo)！了解索引的概念，能夠說出索引的分類。8.5.1索引概述索引是一種特殊的數(shù)據(jù)結(jié)構(gòu)，它可以將數(shù)據(jù)表中的某個(gè)或某些字段與記錄的位置建立一個(gè)對(duì)應(yīng)關(guān)系，并按照一定的順序排序好。使用索引可以快速定位到指定字段的位置。8.5.1索引概述普通索引索引分類唯一索引主鍵索引全文索引空間索引根據(jù)創(chuàng)建索引方式的不同，對(duì)索引分類：8.5.1索引概述MySQL數(shù)據(jù)庫的基本索引類型，使用KEY或INDEX定義，不需要添加任何限制條件。普通索引唯一索引使用UNIQUEINDEX定義，用于防止用戶添加重復(fù)的值，創(chuàng)建唯一索引的字段需要添加唯一約束。8.5.1索引概述使用PRIMARYKEY定義，是一種特殊的唯一索引，用于根據(jù)主鍵自身的唯一性標(biāo)識(shí)每條記錄，防止添加主鍵索引的字段值重復(fù)或?yàn)镹ULL。主鍵索引全文索引使用FULLTEXTINDEX定義，用于提高數(shù)據(jù)量較大的字段的查詢速度。8.5.1索引概述使用SPATIALINDEX定義，用于提高系統(tǒng)獲取空間數(shù)據(jù)的效率。使用空間索引的字段的類型必須是空間數(shù)據(jù)類型，并且字段不能為空。空間索引8.5.1索引概述在數(shù)據(jù)表的單個(gè)字段上創(chuàng)建的索引，可以是普通索引、唯一索引、主鍵索引或者全文索引，該索引對(duì)應(yīng)數(shù)據(jù)表中的一個(gè)字段。單列索引復(fù)合索引根據(jù)創(chuàng)建索引的字段個(gè)數(shù)不同，對(duì)索引分類：在數(shù)據(jù)表的多個(gè)字段上創(chuàng)建索引，當(dāng)查詢條件中使用了這些字段中的第一個(gè)字段時(shí)，下一個(gè)字段才有可能被匹配。8.5.1索引概述復(fù)合索引中字段的設(shè)置順序遵循“最左前綴”原則，也就是在創(chuàng)建復(fù)合索引時(shí)，要把使用頻率最高的字段放在索引字段列表的最左邊。當(dāng)查詢條件中使用了這些字段中的第一個(gè)字段時(shí)，該復(fù)合索引才會(huì)被使用，例如，給my_goods數(shù)據(jù)表的name字段和keyword字段創(chuàng)建復(fù)合索引，當(dāng)查詢條件中使用了name字段時(shí)，該復(fù)合索引才會(huì)被使用。8.5.1索引概述

先定一個(gè)小目標(biāo)！掌握索引的創(chuàng)建方法，能夠根據(jù)需求創(chuàng)建索引。8.5.2創(chuàng)建索引在MySQL中，既可以在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建索引，也可以在修改數(shù)據(jù)表時(shí)創(chuàng)建索引。在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建索引的語法：8.5.2創(chuàng)建索引CREATE[TEMPORARY]TABLE[IFNOTEXISTS]數(shù)據(jù)表名稱(

字段名數(shù)據(jù)類型[字段屬性]…PRIMARYKEY[索引類型](字段列表)[索引選項(xiàng)],|

{INDEX|KEY}[索引名稱][索引類型](字段列表)[索引選項(xiàng)],|

UNIQUE[INDEX|KEY][索引名稱][索引類型](字段列表)[索引選項(xiàng)],|

{FULLTEXT|SPATIAL}[INDEX|KEY][索引名稱](字段列表)[索引選項(xiàng)])[表選項(xiàng)];在修改數(shù)據(jù)表時(shí)創(chuàng)建索引的語法：8.5.2創(chuàng)建索引ALTERTABLE數(shù)據(jù)表名稱ADDPRIMARYKEY[索引類型](字段列表)[索引選項(xiàng)]|ADD{INDEX|KEY}[索引名稱][索引類型](字段列表)[索引選項(xiàng)]|ADDUNIQUE[INDEX|KEY][索引名稱][索引類型](字段列表)[索引選項(xiàng)]|ADD{FULLTEXT|SPATIAL}[INDEX|KEY][索引名稱](字段列表)[索引選項(xiàng)];為student數(shù)據(jù)表中的name字段創(chuàng)建普通索引：ALTERTABLEstudentADDINDEXname_index(name);使用SHOWCREATETABLE語句查看指定表中創(chuàng)建的索引信息：mysql>SHOWCREATETABLEstudent\G******************************1.row************************************

Table:studentCreateTable:CREATETABLE`student`(……此處省略字段的創(chuàng)建信息

PRIMARYKEY(`id`),

KEY`name_index`(`name`))ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci1rowinset(0.00sec)8.5.2創(chuàng)建索引分區(qū)技術(shù)8.6

先定一個(gè)小目標(biāo)！了解分區(qū)技術(shù)，能夠說出數(shù)據(jù)分區(qū)的實(shí)現(xiàn)原理。8.6.1分區(qū)技術(shù)概述分區(qū)技術(shù)是指在操作數(shù)據(jù)表時(shí)根據(jù)給定的算法，將數(shù)據(jù)在邏輯上分到多個(gè)區(qū)域中存儲(chǔ)。在分區(qū)中還可以設(shè)置子分區(qū)，將數(shù)據(jù)存儲(chǔ)到更加具體的區(qū)域內(nèi)。使用分區(qū)技術(shù)可以將一張數(shù)據(jù)表的數(shù)據(jù)存儲(chǔ)在不同的物理磁盤中，相比使用單個(gè)磁盤或文件系統(tǒng)存儲(chǔ)，使用這種存儲(chǔ)方式能夠存儲(chǔ)更多的數(shù)據(jù)，得到更高的查詢吞吐量。如果在WHERE子句中包含分區(qū)條件，系統(tǒng)只需掃描相關(guān)的一個(gè)或多個(gè)分區(qū)而不需掃描全表，從而提高了查詢效率。8.6.1分區(qū)技術(shù)概述

先定一個(gè)小目標(biāo)！掌握創(chuàng)建分區(qū)的語法格式，能夠創(chuàng)建分區(qū)。8.6.2創(chuàng)建分區(qū)在創(chuàng)建數(shù)據(jù)表時(shí)創(chuàng)建分區(qū)的語法格式：CREATETABLE數(shù)據(jù)表名稱[(字段與索引列表)][表選項(xiàng)]PARTITIONBY分區(qū)算法(分區(qū)字段)[PARTITIONS分區(qū)數(shù)量][SUBPARTITIONBY子分區(qū)算法(子分區(qū)字段)[SUBPARTITIONS子分區(qū)數(shù)量]][(PARTITION分區(qū)名[VALUES分區(qū)選項(xiàng)][其他選項(xiàng)][(SUBPARTITION子分區(qū)名[子分區(qū)其他選項(xiàng)])],…)];PARTITIONBY關(guān)鍵字表示創(chuàng)建分區(qū)。一個(gè)數(shù)據(jù)表能夠包含的分區(qū)的最大數(shù)量為1024；分區(qū)文件的序號(hào)默認(rèn)從0開始，當(dāng)有多個(gè)分區(qū)時(shí)依次遞增加1。8.6.2創(chuàng)建分區(qū)分區(qū)算法分別為LIST、RANGE、HASH和KEY，每種算法對(duì)應(yīng)的分區(qū)字段不同，具體如下：RANGE|LIST{(表達(dá)式)|COLUMNS(字段列表)}HASH(表達(dá)式)KEY[ALGORITHM={1|2}](字段列表)KEY算法的ALGORITHM選項(xiàng)用于指定key-hashing函數(shù)的算法，值為1適用于MySQL5.1版本；默認(rèn)值為2適用于MySQL5.5及以后版本，子分區(qū)算法只支持HASH和KEY。當(dāng)使用RANGE算法時(shí)，必須使用LESSTHAN關(guān)鍵字定義分區(qū)選項(xiàng)；當(dāng)使用LIST算法時(shí)，必須使用IN關(guān)鍵字定義分區(qū)選項(xiàng)，具體語法如下：#RANGE算法的分區(qū)選項(xiàng)PARTITION分區(qū)名VALUESLESSTHAN{(表達(dá)式|值列表)|MAXVALUE}#LIST算法的分區(qū)選項(xiàng)PARTITION分區(qū)名VALUESIN(值列表)8.6.2創(chuàng)建分區(qū)創(chuàng)建LIST分區(qū)：mysql>CREATETABLEmydb.p_list(->idINTAUTO_INCREMENTCOMMENT'ID編號(hào)',->nameVARCHAR(50)COMMENT'姓名',->dptINTCOMMENT'部門編號(hào)',->KEY(id)->)->PARTITIONBYLIST(dpt)(->PARTITIONp1VALUESIN(1,3),->PARTITIONp2VALUESIN(2,4)->);給p_list表中的dpt字段分區(qū)當(dāng)字段的值為1或3時(shí)，將記錄放在p1分區(qū)當(dāng)字段的值為2或4時(shí)，將記錄放在p2分區(qū)8.6.2創(chuàng)建分區(qū)將分區(qū)創(chuàng)建完成后，可以在MySQL的data\mydb目錄下看到對(duì)應(yīng)的分區(qū)數(shù)據(jù)文件：p_list#p#p1.idbp_list#p#p2.idbp_list是建立分區(qū)的數(shù)據(jù)表名稱。p1和p2是分區(qū)的名稱。8.6.2創(chuàng)建分區(qū)存儲(chǔ)過程8.7

先定一個(gè)小目標(biāo)！了解存儲(chǔ)過程的概念，能夠說出使用存儲(chǔ)過程的優(yōu)點(diǎn)。8.7.1存儲(chǔ)過程概述存儲(chǔ)過程是一組為了完成特定功能的SQL語句集合，用戶通過創(chuàng)建存儲(chǔ)過程將經(jīng)常使用的SQL語句封裝起來，這樣可以避免編寫相同的SQL語句。8.7.1存儲(chǔ)過程概述0102030405存儲(chǔ)過程只編譯一次，再次調(diào)用時(shí)不需要重復(fù)編譯，執(zhí)行效率高。存儲(chǔ)過程編譯好后會(huì)保存在數(shù)據(jù)庫中，在遠(yuǎn)程調(diào)用時(shí)減少客戶端和服務(wù)器端的數(shù)據(jù)傳輸。針對(duì)特定功能編寫的存儲(chǔ)過程，當(dāng)再需要完成這個(gè)特定功能時(shí)，直接調(diào)用即可。當(dāng)功能需求發(fā)生變化時(shí)，可以修改已創(chuàng)建的存儲(chǔ)過程，花費(fèi)的時(shí)間相對(duì)較少。存儲(chǔ)過程只能由特定用戶使用，避免未授權(quán)用戶訪問，確保數(shù)據(jù)庫安全。執(zhí)行效率高能夠降低網(wǎng)絡(luò)流量復(fù)用率高可維護(hù)性高安全性高存儲(chǔ)過程的優(yōu)點(diǎn)8.7.1存儲(chǔ)過程概述

先定一個(gè)小目標(biāo)！掌握存儲(chǔ)過程的創(chuàng)建和調(diào)用，能夠創(chuàng)建和調(diào)用存儲(chǔ)過程。8.7.2創(chuàng)建和調(diào)用存儲(chǔ)過程使用CREATEPROCEDURE語句創(chuàng)建存儲(chǔ)過程的語法格式：CREATE

PROCEDURE[IFNOTEXISTS]存儲(chǔ)過程名稱([[IN|OUT|INOUT]參數(shù)名稱參數(shù)類型[,...]])BEGIN過程體END存儲(chǔ)過程的參數(shù)是可選的，參數(shù)名稱前面3個(gè)選項(xiàng)的具體含義：IN：表示輸入?yún)?shù)，該參數(shù)在調(diào)用存儲(chǔ)過程時(shí)傳入。OUT：表示輸出參數(shù)，初始值為NULL，表示將存儲(chǔ)過程中的值保存到OUT指定的參數(shù)中，返回給調(diào)用者。INOUT：表示輸入輸出參數(shù)，既可以作為輸入?yún)?shù)也可以作為輸出參數(shù)。8.7.2創(chuàng)建和調(diào)用存儲(chǔ)過程存儲(chǔ)過程中可能會(huì)包含多條SQL語句，每條SQL語句都有語句結(jié)束符“;”，MySQL遇到語句結(jié)束符“;”會(huì)自動(dòng)執(zhí)行SQL語句，但存儲(chǔ)過程只有在被調(diào)用時(shí)才需要執(zhí)行其中的SQL語句，這就需要在創(chuàng)建存儲(chǔ)過程時(shí)修改語句結(jié)束符。修改語句結(jié)束符的語法：DELIMITER新語句結(jié)束符存儲(chǔ)過程新語句結(jié)束符DELIMITER;8.7.2創(chuàng)建和調(diào)用存儲(chǔ)過程在mydb數(shù)據(jù)庫中創(chuàng)建存儲(chǔ)過程：mysql>DELIMITER$$#將語句結(jié)束符從分號(hào)臨時(shí)修改為$$(可以自定義)mysql>CREATEPROCEDUREproc(INgidINT)->BEGIN->SELECTid,nameFROMmy_goodswhereid>gid;->END->$$Query0K,0rowsaffected(0.00sec)mysql>DELIMITER;

#將語句結(jié)束符恢復(fù)為分號(hào)8.7.2創(chuàng)建和調(diào)用存儲(chǔ)過程調(diào)用名稱為proc的存儲(chǔ)過程：mysql>CALLproc(9);+----+---------------+|id|name

|+----+---------------+|10|薄毛衣聯(lián)名款|+----+---------------+1rowinset(0.00sec)QueryOK,0rowsaffected(0.03sec)執(zhí)行過程體內(nèi)的SQL語句的描述信息調(diào)用存儲(chǔ)過程的結(jié)果描述信息8.7.2創(chuàng)建和調(diào)用存儲(chǔ)過程