• 外鍵約束
• 事務(wù)管理
• 預(yù)處理
• 視圖
• 數(shù)據(jù)備份與還原
• 用戶管理

1、安全管理是每一個接觸數(shù)據(jù)庫的人都應(yīng)該考慮的問題，尤其是DBA（數(shù)據(jù)庫管理員）

2、數(shù)據(jù)庫安全的維度有很多

• 管理安全：用戶、權(quán)限、備份還原等
• 結(jié)構(gòu)安全：外鍵、視圖、事務(wù)等
• 執(zhí)行層：預(yù)處理

一、外鍵約束

• 外鍵
• 外鍵約束
• 外鍵管理

1、外鍵

概念

外鍵：foreign key，表中指向外部表主鍵的字段定義成外鍵

• 外鍵必須要通過語法指定才能稱之為外鍵
  • [constraint外鍵名] foreign key(當(dāng)前表字段名) references 外部表(主鍵字段)
• 外鍵構(gòu)成條件
  • 外鍵字段必須與對應(yīng)表的主鍵字段類型一致
  • 外鍵字段本身要求是一個索引（創(chuàng)建外鍵會自動生成一個索引）

步驟

1、確定表中字段與另外一張表存在關(guān)聯(lián)關(guān)系

2、使用外鍵明確關(guān)聯(lián)外表

3、外鍵約束成功

示例

1、創(chuàng)建專業(yè)表和學(xué)生表，學(xué)生表中的專業(yè)id指向?qū)I(yè)表id

create table t_47(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_48(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment '指向t_46表中的id主鍵',
    constraint `c_id` foreign key(c_id) references t_47(id)
)charset utf8;

2、外鍵可以不指定名字，系統(tǒng)會自動生成

create table t_49(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_47(id)
)charset utf8;

小結(jié)

1、外鍵是需要保證字段與外部連接的主鍵字段一致的

2、一張表可以有多個外鍵，但是一個字段只能產(chǎn)生一個外鍵

2、外鍵約束

概念

外鍵約束：當(dāng)表建立外鍵關(guān)系后，外鍵就會對主表（外鍵指向的表）和子表（外鍵所在的表）里的數(shù)據(jù)產(chǎn)生約束效果

• 外鍵約束的是寫操作（默認操作）
  • 新增：子表插入的數(shù)據(jù)對應(yīng)的外鍵必須在主表存在
  • 修改：主表的記錄如果在子表存在，那么主表的主鍵不能修改（主鍵不能修改）
  • 刪除：主表的記錄如果在子表存在，那么主表的主鍵不能刪除
  • 刪除：主表的記錄如果在子表存在，那么主表的主鍵不能刪除
• 外鍵約束控制：外鍵可以在定義時控制外鍵的約束作用
  • 控制類型
    • on update：父表更新時子表的表現(xiàn)
    • on delete：父表刪除時子表的表現(xiàn)
  • 控制方式
    • cascade：級聯(lián)操作，父表操作后子表跟隨操作
    • set null：置空操作，父表操作后，子表關(guān)聯(lián)的外鍵字段置空
    • restrict：嚴格模式，不允許父表操作（默認的）
    • no action：子表不管

步驟

1、確定表的外鍵關(guān)聯(lián)關(guān)系

2、確定主表的約束控制

3、明確使用相應(yīng)的約束控制

4、系統(tǒng)自動約束

示例

1、子表不能插入主表不存在的數(shù)據(jù)

insert into t_48 values(null,'Tony',2); # 錯誤

insert into t_47 values(null,'English');
insert into t_48 values(null,'Peny',1);

2、默認的外鍵產(chǎn)生后，主鍵不能更新被關(guān)聯(lián)的主鍵字段或者刪除被關(guān)聯(lián)的主鍵記錄

# 錯誤
update t_47 set id = 2;
delete from t_47 where id = 1;

3、限制外鍵約束，一般使用更新級聯(lián)，刪除置空

• on update cascade：更新級聯(lián)
• on delete set null：刪除置空

create table t_50(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_51(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int, # 如果要允許置空，就不能not null
    foreign key(c_id) references t_50(id) on update cascade on delete set null
)charset utf8;

insert into t_50 values(null,'Chinese'),(null,'Computer');
insert into t_51 values(null,'Tony',1),(null,'Petter',2);

• 子表依然不允許插入父表不存在的外鍵
• 但是可以插入外鍵為Null的數(shù)據(jù)

# 錯誤
insert into t_51 values(null,'Lilei',3);

insert into t_51 values(null,'Lilei',NULL); # OK                               

• 父表的更新（主鍵）會讓關(guān)聯(lián)的外鍵自動級聯(lián)更新

update t_50 set id = 3 where id = 1;

• 父表的刪除會讓關(guān)聯(lián)的外鍵自動自動置空

delete from t_50 where id = 3;

小結(jié)

1、外鍵約束對子表和父表都有約束

• 子表約束：子表不能插入父表不存在的外鍵
• 父表約束
  • 更新約束（默認不允許）
  • 刪除約束（默認不允許）
• 一般約束
  • 級聯(lián)更新
  • 刪除置空

2、外鍵約束增強了數(shù)據(jù)的安全性和可靠性，但是會增加程序?qū)τ跀?shù)據(jù)的不可控性，所以是實際開發(fā)中一般會通過程序邏輯控制來保證數(shù)據(jù)的完整性和安全性，外間使用較少

3、外鍵管理

概念

外鍵管理：在表創(chuàng)建后期維護外鍵

• 新增外鍵

alter table 表名 add [constraint `外建名`] foreign key(外鍵字段) references 表名(主鍵) [on 外鍵約束]

• 刪除外鍵

alter table 表名 drop foreign key 外鍵名;

• 更新外鍵：先刪除后新增

示例

1、刪除外鍵

alter table t_51 drop foreign key t_51_ibfk_1;  # 系統(tǒng)生成的外鍵

2、追加外鍵

alter table t_51 add constraint `t_51_50` foreign key(c_id) references t_50(id);

• 注意：追加外鍵需要保證外鍵字段里的值要么為Null，要么在父表中都能找到

小結(jié)

1、外鍵的使用最好的創(chuàng)建表結(jié)構(gòu)的時候就維護好，后期的維護對子表數(shù)據(jù)有要求

二、事務(wù)安全

• 事務(wù)概念
• 事務(wù)處理
• 事務(wù)特點

1、事務(wù)

概念

事務(wù)：要做的某個事情

• 計算機中的事務(wù)是指某個程序執(zhí)行單元（寫操作）
• 事務(wù)安全：當(dāng)事務(wù)執(zhí)行后，保障事務(wù)的執(zhí)行是有效的，而不會導(dǎo)致數(shù)據(jù)錯亂
• 事務(wù)安全通常針對的是一連串操作（多個事務(wù)）而產(chǎn)生的統(tǒng)一結(jié)果

• MySQL中默認的寫操作是直接寫入的
  • 執(zhí)行寫操作SQL
  • 同步到數(shù)據(jù)表

示例

銀行轉(zhuǎn)賬：從A賬戶轉(zhuǎn)賬到B賬戶

創(chuàng)建數(shù)據(jù)表

create table t_52(
    id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_52 values(null,'Tom',10000),(null,'Lucy',100);

轉(zhuǎn)賬：Tom向Lucy轉(zhuǎn)賬，一定是分為兩步

# Tom扣錢
update t_52 set account = account - 1000 where id = 1;

# Lucy收錢
update t_52 set account = account + 1000 where id = 2;

• 以上兩步必須都成功轉(zhuǎn)賬才能叫成功
• 兩步操作無法確保哪一步會出問題（尤其是第二步）
• 為了保障兩步都成功才能叫事務(wù)安全

事務(wù)安全原理

事務(wù)安全是在操作前告知系統(tǒng)，接下來所有的操作都暫不同步到數(shù)據(jù)表，而是記錄到事務(wù)日志，指導(dǎo)后續(xù)所有操作都成功，再進行同步；否則取消所有操作

以上述轉(zhuǎn)賬為例

graph TB
A(轉(zhuǎn)賬開始)-->B[開啟事務(wù)]
B-->C{事務(wù)1:Tom轉(zhuǎn)出1000}
C-->|成功|D[記錄到事務(wù)日志]
C-->|失敗|G
D-->E{事務(wù)2:Lucy轉(zhuǎn)入1000}
D-->|失敗|G
E-->|成功|F[記錄到事務(wù)日志]
F-->G[關(guān)閉事務(wù)<br>成功:提交事務(wù) 同步到數(shù)據(jù)表\清除事務(wù)日志<br>失敗:回滾事務(wù) 清除事務(wù)日志]
G-->H((結(jié)束))

小結(jié)

1、事務(wù)的目的就是為了保障連續(xù)操作的一致性，保證結(jié)果的完整性

2、事務(wù)的原理是通過將操作結(jié)果暫時保存在事務(wù)日志中，等所有操作的結(jié)果都是成功的，然后一并同步到數(shù)據(jù)表

2、事務(wù)處理

概念

事務(wù)處理：利用自動或者手動方式實現(xiàn)事務(wù)管理

• 自動事務(wù)處理：系統(tǒng)默認，操作結(jié)束直接同步到數(shù)據(jù)表（事務(wù)關(guān)閉狀態(tài)）
  • 系統(tǒng)控制：變量 autocommit（值為ON，自動提交）
• 手動事務(wù)處理
  • 開啟事務(wù)： start transaction
  • 關(guān)閉事務(wù)
    • 提交事務(wù)：commit（同步到數(shù)據(jù)表同時清空日志數(shù)據(jù)）
    • 回滾事務(wù)：rollback（清空日志數(shù)據(jù)）
• 事務(wù)回滾：在長事務(wù)執(zhí)行中，可以在某個已經(jīng)成功的節(jié)點處設(shè)置回滾點，后續(xù)回滾的話可以回到某個成功點
  • 設(shè)置回滾點：savepoint 回滾點名字
  • 回滾到回滾點：rollback to 回滾點名字

步驟

1、確定操作需要使用到事務(wù)操作

2、開啟事務(wù)

3、執(zhí)行事務(wù)

• 如果需要回滾點設(shè)置：設(shè)置回滾點
• 如果需要回滾：回滾到回滾點

4、結(jié)束事務(wù)

• 成功提交事務(wù)：同步到數(shù)據(jù)表，清空事務(wù)日志
• 失敗回滾事務(wù)：清空事務(wù)日志

示例

1、手動事務(wù)：啟用事務(wù)轉(zhuǎn)賬，成功提交事務(wù)

# 開啟事務(wù)
start transaction;

# Tom扣錢
update t_52 set account = account - 1000 where id  = 1;

# Lucy收錢
update t_52 set account = account + 1000 where id  = 2;

# 提交事務(wù)
commit;

2、手動事務(wù)：啟用事務(wù)轉(zhuǎn)賬，成功提交事務(wù)（回滾點）

# 開啟事務(wù)
start transaction;

# Tom扣錢
update t_52 set account = account - 1000 where id= 1;

# 設(shè)置回滾點
savepoint sp1;

# Lucy收錢
update t_52 set account = account + 10000 where id= 2;

# 操作失敗回到回滾點
rollback to sp1;

# Lucy收錢
update t_52 set account = account + 1000 where id= 2;

# 提交事務(wù)
commit;

3、自動事務(wù)

• Mysql默認是自動提交事務(wù)的：所以事務(wù)一旦發(fā)生就會立即寫入到數(shù)據(jù)表（不能多個事務(wù)一起完成任務(wù)）

show variables like 'autocommit';

• 關(guān)閉自動提交事務(wù)（當(dāng)前設(shè)置級別用戶級：當(dāng)前用戶檔次連接有效）

set autocommit = 0; 

• 手動提交事務(wù)

insert into t_52 values(null,'Liu',1000);
commit;

小結(jié)

1、事務(wù)處理要應(yīng)用到多次寫操作組成的大事務(wù)中，如金融安全等

2、事務(wù)處理通常都會使用手動控制事務(wù)，沒必要去修改原本的自動提交的機制，開啟所有事務(wù)

3、擴展：事務(wù)處理的支持是有條件的

• 存儲引擎需要為InnoDB

3、事務(wù)特點

概念

事務(wù)特點：事務(wù)處理具有ACID四大特性

• 原子性（Atomicity ）：一個事務(wù)操作是一個整體，不可拆分，要么都成功，要么都失敗
• 一致性（Consistency）：事務(wù)執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)，數(shù)據(jù)的完整性沒有被破壞（事務(wù)邏輯的準(zhǔn)確性）
• 隔離性（Isolation ）：事務(wù)操作過程中，其他事務(wù)不可見
• 持久性（Durability ）：事務(wù)一旦提交，結(jié)果不可改變

小結(jié)

1、事務(wù)特點需要在對應(yīng)事務(wù)操作時，結(jié)合多個用戶來看才能看的完整和親切

2、擴展

• 事務(wù)鎖：當(dāng)一個事務(wù)開啟時，另外一個事務(wù)是不能對當(dāng)前事務(wù)鎖占用的數(shù)據(jù)進行操作的
  • 行所：當(dāng)前事務(wù)只占用了一行（id精確檢索數(shù)據(jù)），那么其他事務(wù)可以操作其他行數(shù)據(jù)
  • 表所：當(dāng)前事務(wù)占用了整張表（like掃碼整個表），那么其他事務(wù)對整張表都不能操作
• 臟讀：一個事務(wù)在對某個數(shù)據(jù)進行操作但尚未提交，而另外一個事務(wù)讀到了這個“歷史”數(shù)據(jù)其實已經(jīng)被修改

三、預(yù)處理

• 預(yù)處理
• 預(yù)處理傳參

1、預(yù)處理

目標(biāo)：了解預(yù)處理的概念，掌握預(yù)處理的基本處理方式和應(yīng)用場景

概念

預(yù)處理：prepare statement，一種預(yù)先編譯SQL指令的方式（然后命令執(zhí)行）

• 預(yù)處理不同于直接處理，是將要執(zhí)行的SQL指令先發(fā)送給服務(wù)器編譯，然后通過指令執(zhí)行
  • 發(fā)送預(yù)處理：prepare 預(yù)處理名字 from '要執(zhí)行的SQL指令'
  • 執(zhí)行預(yù)處理：execute 預(yù)處理名字
• 預(yù)處理管理
  • 預(yù)處理屬于會話級別：即當(dāng)前用戶當(dāng)次連接有效（斷開會被服務(wù)器清理掉）
  • 刪除預(yù)處理：deallocate | drop prepare 預(yù)處理名字

步驟

1、要執(zhí)行的SQL指令想使用預(yù)處理

• 重復(fù)執(zhí)行的指令
• 涉及數(shù)據(jù)安全的指令

2、發(fā)送預(yù)處理指令

3、執(zhí)行預(yù)處理

示例

1、查詢學(xué)生的SQL指令需要重復(fù)執(zhí)行很多次

# 普通操作
select * from t_42;

# 預(yù)處理操作：發(fā)送預(yù)處理
prepare p1 from 'select * from t_42';

# 預(yù)處理操作：執(zhí)行預(yù)處理
execute p1;

# 刪除預(yù)處理
deallocate  prepare p1;

預(yù)處理原理

普通處理和預(yù)處理對比

graph TB
A(普通處理)-->B[接收SQL指令]
B-->C[編譯SQL]
C-->D[執(zhí)行SQL]
D-->E((返回結(jié)果))

A1(預(yù)處理)-->B1[接收預(yù)處理指令]
B1-->C1[編譯預(yù)處理指令]
C1-->D1{是否執(zhí)行}
D1-->|execute<br>以后都不需要執(zhí)行接收SQL和編譯SQL|E1[執(zhí)行SQL<br>可重復(fù)執(zhí)行]
D1-->|不執(zhí)行|F1
E1-->F1((返回結(jié)果))

小結(jié)

1、預(yù)處理就是把要執(zhí)行的結(jié)構(gòu)（SQL指令）提前發(fā)送給服務(wù)器端，服務(wù)器進行編譯但不執(zhí)行，等待執(zhí)行指令后才執(zhí)行

2、預(yù)處理的作用

• 性能優(yōu)化
  • 效率優(yōu)化：同樣的SQL不用每次都進行編譯（編譯耗時）
    • 普通處理：每次都需要編譯
    • 預(yù)處理：編譯一次
  • 網(wǎng)絡(luò)傳輸優(yōu)化：復(fù)雜的SQL指令只需要傳輸一次
    • 普通處理：每次都需要網(wǎng)絡(luò)傳輸SQL指令
    • 預(yù)處理：傳輸一次SQL指令，以后都是執(zhí)行指令
• 安全：有效防止SQL注入（外部通過數(shù)據(jù)的特殊使用使得SQL的執(zhí)行方式改變）
  • 普通處理：直接發(fā)送給服務(wù)器執(zhí)行（容易出現(xiàn)SQL注入）
  • 預(yù)處理：發(fā)送的是結(jié)構(gòu)，數(shù)據(jù)是后期執(zhí)行傳入（傳入?yún)f(xié)議不一樣，數(shù)據(jù)安全性高）

2、預(yù)處理傳參

概念

預(yù)處理傳參：在執(zhí)行預(yù)處理的時候傳入預(yù)處理需要的可變數(shù)據(jù)

• 一般預(yù)處理都不會是固定死的SQL指令，而是具有一些數(shù)據(jù)可變的執(zhí)行（條件）

  • 可變數(shù)據(jù)的位置使用占位符 ? 占位

  prepare 預(yù)處理名字 from `預(yù)處理指令 變化部分使用?替代`
  

• 在執(zhí)行預(yù)處理的時候?qū)嶋H數(shù)據(jù)傳進去代替占位符執(zhí)行SQL

  • 數(shù)據(jù)存儲到變量（預(yù)處理傳入的值必須是變量保存的）

  set @變量名 = 值
  

  • 使用using關(guān)鍵字傳參

  execute 預(yù)處理名字 using @變量名
  

  • 數(shù)據(jù)傳入的順序與預(yù)處理中占位符的順序一致

步驟

1、同樣的SQL指令要執(zhí)行N次，但是條件不一致

2、使用預(yù)處理占位符發(fā)送預(yù)處理指令

3、設(shè)定變量保存要傳入的數(shù)據(jù)

4、執(zhí)行預(yù)處理，攜帶變量參數(shù)

示例

向t_40表中插入數(shù)據(jù)

# 準(zhǔn)備預(yù)處理：涉及參數(shù)
prepare t_40_insert from 'insert into t_40 values(null,?,?,?,?)';

# 設(shè)置變量并傳入?yún)?shù)
set @name = '藥師兜';
set @gender = '男';
set @age = 23;
set @class_name = '木葉1班';

# 執(zhí)行預(yù)處理
execute t_40_insert using @name,@gender,@age,@class_name;

小結(jié)

1、預(yù)處理傳參是實際應(yīng)用預(yù)處理時最常見的方式

2、預(yù)處理指令可以適用于增刪改查各種指令

3、如果預(yù)處理的指令不是在一次連接中重復(fù)使用，那么預(yù)處理反而會降低效率。所以預(yù)處理的執(zhí)行如果不是考慮到安全因素，那么一定是SQL需要重復(fù)執(zhí)行

四、視圖

• 視圖概念
• 視圖管理
• 視圖數(shù)據(jù)操作
• 視圖算法

1、視圖

概念

視圖：view，一種由select指令組成的虛擬表

• 視圖是虛擬表，可以使用表管理（結(jié)構(gòu)管理）
  • 為視圖提供數(shù)據(jù)的表叫做基表

# 創(chuàng)建視圖
create view 視圖名字 as select指令;

# 訪問視圖：一般都是查詢
select */字段名 from 視圖名字;

• 視圖有結(jié)構(gòu)，但不存儲數(shù)據(jù)
  • 結(jié)構(gòu)：select選擇的字段
  • 數(shù)據(jù)：訪問視圖時執(zhí)行的select指令

步驟

1、確定需要使用視圖提供數(shù)據(jù)

• 數(shù)據(jù)來源是多張表
• 對外部系統(tǒng)提供數(shù)據(jù)支撐（保護基表數(shù)據(jù)）

2、使用視圖

示例

1、需要對外提供一個學(xué)生詳情的數(shù)據(jù)，經(jīng)常使用：可以利用視圖實現(xiàn)

# 對外提供數(shù)據(jù)，要保護數(shù)據(jù)本身的安全
# 需要長期使用

# 創(chuàng)建視圖
create view v_student_info as select * from t_45 left join t_46 using(c_id);
# 使用視圖：像表一樣使用
select * from v_student_info;

2、有些復(fù)雜的SQL又是經(jīng)常用到的，如多張表的連表操作：可以利用視圖實現(xiàn)

# 院系表
create table t_53(
    id int primary key auto_increment,
    name varchar(50) not null
)charset utf8;
insert into t_53 values(null,'語言系'),(null,'考古系');

# 專業(yè)表
create table t_54(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '學(xué)院id'
)charset utf8;
insert into t_54 values(null,'English',1),(null,'Chinese',1);

# 學(xué)生表
create table t_55(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '專業(yè)Id'
)charset utf8;
insert into t_55 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1);

# 獲取所有學(xué)生的明細信息
select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

# 以視圖保存這類復(fù)雜指令，后續(xù)可以直接訪問視圖
create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_55 as stu left join t_54 sub on stu.s_id = sub.id left join t_53 sch on sub.s_id = sch.id;

select * from v_student_detail;

小結(jié)

1、視圖是用來提供數(shù)據(jù)支持的，是由select指令組成的結(jié)構(gòu)

• 存在結(jié)構(gòu)
• 不存在數(shù)據(jù)（數(shù)據(jù)是使用時調(diào)用select指令動態(tài)獲取數(shù)據(jù)）

2、視圖的目的

• 方便提供全面數(shù)據(jù)：可以根據(jù)需求組織數(shù)據(jù)，而實際上不會在數(shù)據(jù)庫產(chǎn)生數(shù)據(jù)冗余
• 數(shù)據(jù)安全：視圖本質(zhì)是來源于數(shù)據(jù)基表，但是對外可以保護基本的數(shù)據(jù)結(jié)構(gòu)

2、視圖管理

概念

視圖管理：對視圖結(jié)構(gòu)的管理

• 視圖查看：顯示視圖結(jié)構(gòu)和具體視圖信息

show tables;    # 查看全部視圖
show create table/view 視圖名字;    # 查看視圖創(chuàng)建指令
desc 視圖名字;   # 查看視圖結(jié)構(gòu)

• 視圖修改：更改視圖邏輯

# 更改視圖
alter view 視圖名 as 新的查詢指令;
create or replace view 視圖名 as 新的查詢指令;   # 創(chuàng)建新的或者替換新的

• 視圖刪除

drop view 視圖名; 

示例

1、查看全部視圖和視圖明細

show tables;    # 查看全部表，包括視圖
desc v_student_detail;  # 查看視圖結(jié)構(gòu)
show create view v_student_detail;  # 查看視圖創(chuàng)建明細

2、修改視圖：重置視圖數(shù)據(jù)邏輯

alter view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);
create or replace view v_student_info as select t1.s_name,t2.c_name from t_45 t1 left join t_46 t2 using(c_id);

3、刪除視圖

drop view v_student_info;

小結(jié)

1、視圖操作與表操作類似，通常情況下不會經(jīng)常的去修改維護，而是會在一開始就維護好

2、視圖管理可以與表一樣對結(jié)構(gòu)進行管理

3、視圖數(shù)據(jù)操作

概念

視圖數(shù)據(jù)操作：直接對視圖進行寫操作（增刪改）然后實現(xiàn)基表數(shù)據(jù)的變化

• 視圖所有的數(shù)據(jù)操作都是最終對基表的數(shù)據(jù)操作
• 視圖操作條件
  • 多基表視圖：不允許操作（增刪改都不行）
  • 單基表視圖：允許增刪改
    • 新增條件：視圖的字段必須包含基表中所有不允許為空的字段
  • with check option：操作檢查規(guī)則
    • 默認不需要這個規(guī)則（創(chuàng)建視圖時指定）：視圖操作只要滿足前面上述條件即可
    • 增加此規(guī)則：視圖的數(shù)據(jù)操作后，必須要保證該視圖還能把通過視圖操作的數(shù)據(jù)查出來（否則失?。?/li>

步驟

1、根據(jù)需求確定需要使用視圖

2、確定允許視圖進行數(shù)據(jù)操作（通常用戶權(quán)限設(shè)定，且是單基表視圖）

3、確定視圖數(shù)據(jù)的操作是否需要操作檢查（有where條件篩選，且只對新增和更新有影響）

• 需要：增加with check option
• 不需要

4、使用視圖進行數(shù)據(jù)操作（最終數(shù)據(jù)寫落點是基表）

示例

1、增加一個單表視圖和多表視圖

create view v_student_1 as select s_id,s_name from t_45;
create view v_student_2 as select s.*,c.c_name from t_45 s left join t_46 c using(c_id);
create or replace view v_student_3 as select * from t_45 where c_id is not null with check option;

2、新增數(shù)據(jù)

insert into v_student_1 values(null,'student7');    # 正確：視圖包含所有必有字段
insert into v_student_2 values(null,'student8',null,null); # 錯誤：不可插入
insert into v_student_3 values(null,'student8',null);   # 錯誤：check option，因為第三個字段c_id為NULL，不符合視圖篩選條件，查不出來
insert into v_student_3 values(null,'Student9',1);  # 正確

3、更新數(shù)據(jù)

update v_student_1 set s_name = 'boy' where s_id = 8;
update v_student_2 set s_name = 'boy' where s_id = 7; # 錯誤：不可修改
update v_student_3 set c_id = null where s_id = 1;    # 錯誤：check option，修改后c_id為null，變得不符合視圖篩選條件了
update v_student_3 set s_name = 'boy' where s_id = 1; # 正確

4、刪除數(shù)據(jù)

delete from v_student_1 where s_id = 2;
delete from v_student_2 where s_id = 3; # 錯誤：不可刪除
delete from v_student_3 where s_id = 1; # 可以刪除，說明with check option不影響刪除操作

小結(jié)

1、視圖數(shù)據(jù)操作一般情況下是不允許的，通常之所以對外提供視圖就提供數(shù)據(jù)的只讀操作

2、視圖數(shù)據(jù)操作與視圖的基表數(shù)量和字段有關(guān)

• 多基表視圖不允許任何寫操作
• 單基表視圖允許更新和刪除、根據(jù)情況允許新增（視圖包含基表中所有不允許為空字段）

3、with check option是針對有where條件的視圖組成有效，需要手動選擇是否增加該選項

• 視圖數(shù)據(jù)的新增、修改后，必須與原來的查詢結(jié)果是一致的（新增一定要能在視圖中看到）
• 視圖數(shù)據(jù)的刪除不受with check option影響
• 視圖數(shù)據(jù)的新增、修改都是針對當(dāng)前視圖能查出來的，否則既不報錯也不生效
• with check option還可以更復(fù)雜，如果有興趣可以深入的了解一下

4、視圖算法

概念

視圖算法：指視圖在執(zhí)行過程中對于內(nèi)部的select指令的處理方式

• 視圖算法在創(chuàng)建視圖時指定

create ALGORITHM = 算法 view 視圖名字 as select指令;

• 視圖算法一共有三種
  • undefined：默認的，未定義算法，即系統(tǒng)自動選擇算法
  • merge：合并算法，就是將視圖外部查詢語句跟視圖內(nèi)部select語句合并后執(zhí)行，效率高（系統(tǒng)優(yōu)先選擇）
  • temptable：臨時表算法，即系統(tǒng)將視圖的select語句查出來先得出一張臨時表，然后外部再查詢（temptable算法視圖不允許寫操作）

步驟

1、確定使用視圖

2、確定視圖算法：考慮視圖內(nèi)部SQL指令中的子句使用情況

3、創(chuàng)建視圖并使用視圖

示例

1、創(chuàng)建三種不同算法視圖

create algorithm = undefined view v_student_4 as select * from t_42 order by age desc;
create algorithm = merge view v_student_5 as select * from t_42 order by age desc;
create algorithm = temptable view v_student_6 as select * from t_42 order by age desc;

2、使用視圖：為了體現(xiàn)算法效果，給視圖增加分組效果

select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_4 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_5 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_6 group by class_name;

3、臨時表算法的視圖不能進行數(shù)據(jù)插入操作

insert into v_student_6 values(null,'冥加','男',100,'神妖1班'); # 錯誤：不可插入

小結(jié)

1、視圖算法是用來結(jié)合外部外的查詢指令的優(yōu)化思路，主要的優(yōu)化方式有兩種

• merge：合并算法，將視圖的select與外部select合并成一條，然后執(zhí)行一次（效率高）
• temptable：臨時表算法，視圖的指令單獨執(zhí)行得到一個二維表，然后外部select再執(zhí)行（安全）
• undefined：未定義算法是一種系統(tǒng)自動選擇的算法，系統(tǒng)偏向于選擇merge算法

2、一般在設(shè)計視圖的時候要考慮到視圖算法的可行性，通常視圖中如果出現(xiàn)了order by排序的話，就要考慮使用temptable算法

• 只要merge以后，不會導(dǎo)致數(shù)據(jù)因為子句的先后順序而混亂（order by與group by的順序混亂容易出問題）

五、數(shù)據(jù)備份與還原

• 單表數(shù)據(jù)備份與還原

• 文件備份與還原

• SQL備份與還原

概念

備份：backup，將數(shù)據(jù)或者結(jié)構(gòu)按照一定的格式存儲到另外一個文件中，以保障階段數(shù)據(jù)的完整性和安全性

• 將當(dāng)前正確數(shù)據(jù)進行數(shù)據(jù)保存
• 備份通常是有固定的時間節(jié)點

還原：restore，在當(dāng)前數(shù)據(jù)出問題的情況下，將之前備份的數(shù)據(jù)替換掉當(dāng)前數(shù)據(jù)，保證系統(tǒng)的持續(xù)、正確的運行

• 基于備份進行數(shù)據(jù)還原
• 備份還原不一定能夠保證所有損失挽回

小結(jié)

1、數(shù)據(jù)的備份與還原是作為一個正常運行的數(shù)據(jù)庫必須做的事情

• 確保數(shù)據(jù)的安全
• 將數(shù)據(jù)出錯的風(fēng)險降低到最小

2、數(shù)據(jù)庫的備份與還原是作為一個DBA最基本的技術(shù)要求（開發(fā)者也要會）

1、表數(shù)據(jù)備份

概念

表數(shù)據(jù)備份：單獨針對表里的數(shù)據(jù)部分進行備份（數(shù)據(jù)導(dǎo)出）

• 將數(shù)據(jù)從表中查出，按照一定格式存儲到外部文件
  • 字段格式化：fields
    • terminated by：字段數(shù)據(jù)結(jié)束后使用的符號，默認是空格
    • enclosed by：字段數(shù)據(jù)包裹，默認什么都沒有
    • escaped by：特殊字符的處理，默認是轉(zhuǎn)義
  • 行格式化：lines
    • terminated by：行結(jié)束符號，默認是\n，自動換行
    • starting by：行開始符號，默認沒有

select 字段列表|*  into outfile 外部文件路徑 
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
from 數(shù)據(jù)表;

• 表數(shù)據(jù)備份不限定數(shù)據(jù)的來源是一張表還是多張表（可以連表）

步驟

1、確定需要對表數(shù)據(jù)進行導(dǎo)出處理（備份），而且不需要考慮字段名字

2、確定導(dǎo)出的數(shù)據(jù)的處理

• 字段處理（可以默認）
• 行處理（可以默認）

3、執(zhí)行表數(shù)據(jù)導(dǎo)出

示例

1、將t_40表的數(shù)據(jù)按照默認的方式導(dǎo)出到文件

select * into outfile  'D:/t_40.csv' from t_40;

• 如果系統(tǒng)提示：secure-file-priv問題，說明配置沒有允許進行文件的導(dǎo)入導(dǎo)出。需要在配置文件里（my.ini）配置好這個配置項：secure-file-priv = 數(shù)據(jù)導(dǎo)入導(dǎo)出路徑/不指定值（重啟MySQL生效）
  Ubuntu默認保存路徑/var/lib/mysql-files/
  2、將t_40表的數(shù)據(jù)按照指定格式導(dǎo)出到文件

select name,gender,age,class_name into outfile 'D:/t_40_self.csv'
    fields terminated by '-' enclosed by '"'
    lines starting by 'GO:'
from t_40;

3、多表數(shù)據(jù)導(dǎo)出：t_45連接t_46表

select * into outfile 'D:/t_45_46.csv' from t_45 left join t_46 using(c_id);

小結(jié)

1、表數(shù)據(jù)備份是一種將表中的數(shù)據(jù)按照一定的格式導(dǎo)出到外部文件進行保存

• 數(shù)據(jù)取出后方便進行加工管理
• SQL有不同的語法，但是數(shù)據(jù)的識別是一致的，所以方便進行數(shù)據(jù)庫間的切換

2、表數(shù)據(jù)備份通常是為了進行數(shù)據(jù)加工后存入回表中，或者到其他表

3、目前比較少用這種方式進行數(shù)據(jù)備份

2、表數(shù)據(jù)還原

目標(biāo)：了解表數(shù)據(jù)還原的概念，了解表數(shù)據(jù)還原所能解決的問題
概念

表數(shù)據(jù)還原：將符合數(shù)據(jù)表結(jié)構(gòu)的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)表中（數(shù)據(jù)導(dǎo)入）

• 將一定格式的數(shù)據(jù)按照一定的解析方式解析成符合表字段格式的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)表
  • 字段處理
  • 行處理

load data infile '數(shù)據(jù)文件所在路徑' into table 表名
    [fields terminated by 格式 enclosed by 格式]
    [lines terminated by 格式 starting by 格式]
    [(字段列表)];   # 如果是部分表字段，那么必須將字段列表放到最后

• 數(shù)據(jù)文件來源
  • 表數(shù)據(jù)備份的數(shù)據(jù)文件
  • 外部獲取或者制作的符合格式的數(shù)據(jù)

步驟

1、數(shù)據(jù)文件里的數(shù)據(jù)滿足數(shù)據(jù)表的字段要求

• 數(shù)據(jù)類型
• 字段對應(yīng)數(shù)（自增長id、可以為空字段除外）

2、數(shù)據(jù)文件里的數(shù)據(jù)可以通過字段加工、行加工處理滿足表字段要求

3、使用數(shù)據(jù)導(dǎo)入

示例

1、將t_40.csv數(shù)據(jù)導(dǎo)入到db_3數(shù)據(jù)庫中的一個與t_40表結(jié)構(gòu)一致的表中

create table t_40 like db_2.t_40;

load data infile 'D:/t_40.csv' into table t_40; # 有可能因為字符集出現(xiàn)問題           
load data infile 'D:/t_40.csv' into table t_40 charset utf8; 

注意：數(shù)據(jù)加載的時候需要注意外部數(shù)據(jù)的字符集，在加載的時候需要指定字符集為外部文件數(shù)據(jù)格式，在表后增加字符集charset 外部文件數(shù)據(jù)字符集
2、將t_40_self文件里的數(shù)據(jù)導(dǎo)入到db_3.t_40表中

load data infile 'D:/t_40_self.csv' into table t_40 charset utf8 fields terminated by '-' enclosed by '"' lines starting by 'GO:' (name,gender,age,class_name) ;

小結(jié)

1、表數(shù)據(jù)還原其實是將外部符合條件的數(shù)據(jù)，按照一定的格式要求導(dǎo)入到數(shù)據(jù)表中

2、數(shù)據(jù)導(dǎo)入可以解決不同格式數(shù)據(jù)或者不同數(shù)據(jù)庫產(chǎn)品間的數(shù)據(jù)互相導(dǎo)入到對應(yīng)數(shù)據(jù)庫產(chǎn)品的問題

3、目前較少使用這種方式進行數(shù)據(jù)導(dǎo)入：數(shù)據(jù)的生成應(yīng)該是業(yè)務(wù)產(chǎn)生，而不是人工參與（破壞數(shù)據(jù)的客觀有效性，使得數(shù)據(jù)不真實）

3、文件備份

概念

文件備份：直接對數(shù)據(jù)表進行文件保留，屬于物理備份

• 文件備份操作簡單，直接將數(shù)據(jù)表（或者數(shù)據(jù)庫文件夾）進行保存遷移
• MySQL中不同表存儲引擎產(chǎn)生的文件不一致，保存手段也不一致
  • InnoDB：表結(jié)構(gòu)文件在ibd文件中，數(shù)據(jù)和索引存儲在外部統(tǒng)一的ibdata文件中(Mysql7以前話是frm后綴)
  • MyIsam：每張表的數(shù)據(jù)、結(jié)構(gòu)和索引都是獨立文件，直接找到三個文件遷移即可

步驟

1、設(shè)定備份時間節(jié)點

2、設(shè)定備份文件存儲位置

3、確定備份表的存儲引擎

4、根據(jù)節(jié)點進行文件備份：將文件轉(zhuǎn)移（復(fù)制）到其他存儲位置

示例

1、MyIsam表的文件備份：找到三個文件，復(fù)制遷移

• sdi：表結(jié)構(gòu)文件

• MYI：索引文件

• MYD：數(shù)據(jù)文件
  2、InnoDB表的文件備份：找到兩個文件，復(fù)制遷移

• ibd：表結(jié)構(gòu)文件

• ibdata：所有InnoDB數(shù)據(jù)文件

小結(jié)

1、文件備份是一種簡單粗暴的數(shù)據(jù)備份方式，是直接將數(shù)據(jù)文件打包管理的方式

• MyIsam存儲引擎相對比較適合文件備份，因為MyIsam存儲引擎表文件獨立，不關(guān)聯(lián)其他表
• InnoDB不適合文件備份，因為不管是備份一張表還是全部數(shù)據(jù)表，都需要備份整個數(shù)據(jù)存儲文件ibdata（適合整庫遷移）

2、文件備份方式非常占用磁盤空間

4、文件還原

概念

文件還原：利用備份的文件，替換出現(xiàn)問題的文件，還原到備份前的良好狀態(tài)

• 直接將備份的文件放到對應(yīng)的位置即可

• 文件還原影響

  • MyIsam存儲引擎：單表備份，單表還原，不影響其他任何數(shù)據(jù)
  • InnoDB存儲引擎：單表結(jié)構(gòu)，整庫數(shù)據(jù)，只適合整庫備份還原，否則會影響其他InnoDB存儲表

步驟

1、找到出問題的數(shù)據(jù)文件

• MyIsam：表結(jié)構(gòu)、表數(shù)據(jù)、表索引三個文件（刪掉即可）
• InnoDB：表結(jié)構(gòu)、整庫數(shù)據(jù)表ibdata（刪掉）

2、將備份數(shù)據(jù)放到相應(yīng)刪除的文件位置

示例

1、MyIsam數(shù)據(jù)備份表的數(shù)據(jù)遷移：單表遷移到不同數(shù)據(jù)庫

2、InnoDB數(shù)據(jù)備份完成整個數(shù)據(jù)庫的遷移（包括數(shù)據(jù)庫用戶信息）

小結(jié)

1、文件備份的還原通常使用較少

• 數(shù)據(jù)備份占用空間大，這種備份方式就少
• InnoDB的備份是針對整個數(shù)據(jù)庫里所有InnoDB表，還原會覆蓋掉所有不需要還原的表

2、文件備份與還原通常可以在數(shù)據(jù)遷移的情況下使用

• MyIsam：獨立表的遷移（現(xiàn)在很少用，myisam很少用）
• InnoDB：整個數(shù)據(jù)庫的遷移

5、SQL備份

概念

SQL備份：將數(shù)據(jù)庫的數(shù)據(jù)以SQL指令的形式保存到文件當(dāng)中，屬于邏輯備份

• SQL備份是利用Mysqldump.exe客戶端實現(xiàn)備份

• SQL備份是將備份目標(biāo)（數(shù)據(jù)表）以SQL指令形式，從表的結(jié)構(gòu)、數(shù)據(jù)和其他信息保存到文件

  mysqldump.exe -h -P -u -p [備份選項] 數(shù)據(jù)庫名字 [數(shù)據(jù)表列表] > SQL文件路徑

• 備份選項很多，常見的主要是數(shù)據(jù)庫的備份多少

  • 全庫備份：--all-databases 所有數(shù)據(jù)庫的所有表，也不需要指定數(shù)據(jù)庫名字
  • 單庫備份：[--databases] 數(shù)據(jù)庫 指定數(shù)據(jù)庫里的所有表（后面不要給表名）
  • 部分表（單表）備份：數(shù)據(jù)庫名字 表1[ 表2...表N]

步驟

1、確定備份的時間：通常是有規(guī)則的時間備份

2、確定備份的目標(biāo)級別：全庫、單庫、數(shù)據(jù)表

3、使用mysqldump實現(xiàn)備份

示例

1、全庫備份（借助于Windows下的cmd訪問mysqldump.exe，當(dāng)前用戶使用root賬號）

mysqldump.exe -uroot -proot --all-databases > D:/mysql.sql
2、單庫備份

mysqldump -uroot -proot --databases db_2 > D:/db_2.sql
3、單表備份（沒有創(chuàng)建數(shù)據(jù)庫的指令）

mysqldump -uroot -proot db_2 t_40 t_42 > D:/t_40_42.sql

小結(jié)

1、SQL備份是一般更新頻次不高的數(shù)據(jù)庫的常用備份方式

2、SQL備份是將數(shù)據(jù)表（庫）以SQL指令形式進行備份

• 結(jié)構(gòu)指令：表創(chuàng)建（庫創(chuàng)建）
• 數(shù)據(jù)指令：insert數(shù)據(jù)

3、SQL備份能夠完成的備份結(jié)構(gòu)和數(shù)據(jù)，而結(jié)構(gòu)和數(shù)據(jù)又是獨立的，所以比較方便用作備份和還原

• SQL備份比較耗費時間和占用性能，建議在閑時進行備份（用戶不活躍時）
• SQL備份可以根據(jù)數(shù)據(jù)表的重要性進行頻次區(qū)分備份

6、SQL還原

概念

SQL還原：在需要用到SQL備份數(shù)據(jù)時，想辦法讓SQL執(zhí)行，從而實現(xiàn)備份數(shù)據(jù)的還原

• SQL還原可以使用Mysql.exe進行操作

mysql.exe -h -P -u -p [數(shù)據(jù)庫名字] < SQL文件路徑

• SQL還原可以在進入到數(shù)據(jù)庫之后利用SQL指令還原

source SQL文件路徑;

步驟

1、確定數(shù)據(jù)庫（表）需要進行數(shù)據(jù)還原

• 數(shù)據(jù)錯亂
• 數(shù)據(jù)不完整

2、找到對應(yīng)節(jié)點的SQL備份文件

3、SQL還原

示例

1、使用mysql客戶端對db_2的數(shù)據(jù)文件進行單庫還原（通常針對數(shù)據(jù)庫）

mysql.exe -uroot -p < D:/db_2.sql

• 注意：如果不是庫備份，那么需要指定數(shù)據(jù)庫才能執(zhí)行的

mysql.exe -uroot -p db_2 < D:/t_40_42.sql

2、在進入數(shù)據(jù)庫之后，使用source指令還原SQL備份（通常針對表）

source D:/t_40_42.sql;

小結(jié)

1、SQL還原是利用SQL備份文件，觸發(fā)SQL指令執(zhí)行，從而恢復(fù)到指定時間點的結(jié)構(gòu)和數(shù)據(jù)

2、SQL還原不能百分百保證數(shù)據(jù)庫的數(shù)據(jù)不受影響

• SQL備份通常不具有實時性（一般都會有時間間斷）

7、總結(jié)

1、數(shù)據(jù)的備份與還原是作為數(shù)據(jù)庫管理者和使用者都應(yīng)該掌握的一項技能

• 保障數(shù)據(jù)安全
• 保證系統(tǒng)正常運行
• 保障公司和客戶的利益

2、數(shù)據(jù)庫備份與還原的方式很多，每一種都有自己的特點和適用點，需要我們熟練區(qū)分和選擇

• 表數(shù)據(jù)備份與還原：適用于數(shù)據(jù)導(dǎo)出和導(dǎo)入，數(shù)據(jù)具有結(jié)構(gòu)，但是不包含字段和類型
• 文件備份與還原：簡潔方便，但是需要區(qū)分存儲引擎InnoDB和MyIsam（InnoDB不適合進行文件備份）
• SQL備份與還原：不限定存儲引擎，隨時隨地可以備份，不過備份和還原的效率都比較低（完整備份）

3、數(shù)據(jù)庫的備份與還原是一門學(xué)問，所以不同的企業(yè)、業(yè)務(wù)都會選擇不同的備份策略，也有可能使用交叉策略備份來確保數(shù)據(jù)的安全，而且一般會將備份文件與運行環(huán)境分離開來以確保數(shù)據(jù)真正的隔離和安全。

六、用戶管理

學(xué)習(xí)目標(biāo)：了解用戶管理的目標(biāo)，掌握用戶管理在實際開發(fā)過程中的應(yīng)用

• 賬號管理
• 權(quán)限管理
• 角色管理

1、賬號管理

概念

賬號管理：根據(jù)項目的需求設(shè)置和管理賬號

• 賬號是權(quán)限依賴的對象，先有賬號才有權(quán)限
• MySQL中賬號的組成分為兩個部分：用戶名 @ 主機地址（root@localhost）
  • 用戶名為用戶登錄時的名字
  • 主機地址：是允許賬號所在客戶端的訪問的客戶端IP（如上述root只能在服務(wù)器本機通過客戶端訪問）
• 賬號管理
  • 創(chuàng)建賬號：create user 用戶名@主機地址 identified by '明文密碼';
  • 刪除賬號：drop user 用戶名@主機地址

步驟

1、根據(jù)項目要求創(chuàng)建用戶

2、根據(jù)項目要求刪除用戶

示例

1、根據(jù)項目情況，跟不同的項目組創(chuàng)建不同的賬號

# A團隊只允許在公司訪問服務(wù)器，公司IP為163.177.151.110
create user `admin`@`163.177.151.110` identified by 'admin123';

# B團隊不限定負責(zé)數(shù)據(jù)庫管理，不限定工作地點
create user `admin` identified by 'admin321';

2、開發(fā)任務(wù)結(jié)束，A團隊的任務(wù)已經(jīng)完成，不需要進行數(shù)據(jù)庫操作

drop user `admin`@`163.177.151.110`;

小結(jié)

1、賬號管理是用戶管理的基礎(chǔ)，但是賬號管理也只是用戶管理的一部分

• 賬號管理是要考慮數(shù)據(jù)安全因素劃分
• 賬號管理單獨應(yīng)用較少，一般都要配合權(quán)限控制
• 賬號管理也是DBA對于數(shù)據(jù)庫管理的一種重要手段：根據(jù)項目劃分賬號
• 大的項目或者大的數(shù)據(jù)庫服務(wù)器上幾乎不會給開發(fā)使用root賬號（權(quán)限太大）

2、權(quán)限管理

概念

權(quán)限管理：對賬號進行權(quán)限的支持與回收

• 賬號創(chuàng)建之初除了登錄是沒有其他操作權(quán)限的

• 賬號的管理通常需要配合權(quán)限的使用

  • 賦權(quán)：給賬號綁定相應(yīng)的權(quán)限 grant 權(quán)限列表 on 數(shù)據(jù)庫|*.數(shù)據(jù)表|* to 用戶名@主機地址
  • 回收：將賬號已有的權(quán)限回收 revoke 權(quán)限列表 on 數(shù)據(jù)庫|*.數(shù)據(jù)表|* from 用戶名@主機地址
  • 刷新權(quán)限：flush privileges
  • 查看權(quán)限：show grants for 用戶名@主機地址

• MySQL提供的權(quán)限列表

步驟

1、創(chuàng)建新的用戶賬號

2、根據(jù)需求賦予/回收指定數(shù)據(jù)庫（一般整庫）或者指定數(shù)據(jù)表的操作權(quán)限

3、刷新權(quán)限

示例

1、給用戶admin@localhost分配權(quán)限：db_2下所有表的所有權(quán)限

create user `admin`@`localhost` identified by '123456';
grant all privileges on db_2.* to `admin`@`localhost`;

2、給用戶admin分配權(quán)限：db_2下的查看視圖權(quán)限

grant select on db_2.v_student_1 to `admin1`;
grant select on db_2.v_student_2 to `admin1`;
grant select on db_2.v_student_3 to `admin1`;
grant select on db_2.v_student_4 to `admin1`;
grant select on db_2.v_student_5 to `admin1`;
grant select on db_2.v_student_6 to `admin1`;

3、回收權(quán)限

# 如果用戶不要了，可以直接刪除用戶，保留用戶不給權(quán)限，就回收全部權(quán)限
revoke all on db_2.* from `admin`@`localhost`;

# 針對單獨授權(quán)表的權(quán)限回收：只能針對表進行操作
revoke select on db_2.v_student_1 from `admin1`;

小結(jié)

1、權(quán)限管理是整個用戶管理的核心：賬號只能讓用戶能夠連接服務(wù)器，而權(quán)限管理才能給用戶提供各類操作

2、權(quán)限的操作是根據(jù)使用賬號的用戶需要出發(fā)的

• DBA用戶通常可以分配整個數(shù)據(jù)庫所有庫的權(quán)限：all on *.*
• 項目管理級別的用戶可以針對所負責(zé)項目的權(quán)限：all on 數(shù)據(jù)庫.*（多個項目分配多次）
• 項目開發(fā)者用戶可以針對所負責(zé)項目模塊的權(quán)限：權(quán)限列表 on 數(shù)據(jù)庫.表名/*（如果是跨項目分配多次）
• 常用的開發(fā)者權(quán)限有：
  • create、alter、drop：庫、表結(jié)構(gòu)操作
  • insert、select、update、delete：數(shù)據(jù)操作
  • references：外鍵權(quán)限
  • index：索引

3、擴展：可以直接使用賦權(quán)創(chuàng)建新用戶（MySQL7以上不允許這么操作）

grant select on db_2.* to `user`@`localhost` with grant option;

3、角色管理

概念

角色管理：role，即根據(jù)角色來分配權(quán)限，然后用戶只需要關(guān)聯(lián)角色即可（分配角色）：Mysql8以后才有的

• 角色的存在可以更方便的用戶維護多個具有相同權(quán)限的用戶（核心價值）
• 角色相關(guān)操作和語法
  • 創(chuàng)建角色：create role 角色名字1[,角色名字2,...角色名字N]（可批量創(chuàng)建）
  • 分配權(quán)限：grant 權(quán)限列表 on 數(shù)據(jù)庫|*.數(shù)據(jù)表|* to 角色名字
  • 綁定角色：grant 角色名字 to 用戶名@主機地址
  • 撤銷角色：revoke 角色名字 from 用戶名@主機地址
  • 回收角色權(quán)限：revoke 權(quán)限列表 on 數(shù)據(jù)庫|*.數(shù)據(jù)表|* from 角色名字
  • 刪除角色：drop role 角色名字1[,角色名字2,...角色名字N]

步驟

關(guān)聯(lián)角色

1、創(chuàng)建角色

2、確定角色的權(quán)限：給角色分配權(quán)限

3、將角色分配給用戶（和第2步可以沒有先后關(guān)系）

取關(guān)角色

1、權(quán)限過大：回收角色權(quán)限

2、放棄角色：刪除角色

示例

1、創(chuàng)建用戶角色，分配給具有同樣權(quán)限的用戶

# 創(chuàng)建角色（角色與用戶名很相似）
create role developer,app_read,app_write;

# 給角色分配權(quán)限
grant all on db_2.* to developer;
grant select on db_2.* to app_read;
grant insert,update,delete on db_2.* to app_write;

# 創(chuàng)建用戶，并分配角色給用戶
create user 'admin1'@'%' identified by '1234';
create user 'admin2'@'%' identified by '1234';
create user 'admin3'@'%' identified by '1234';

grant developer to 'admin1'@'%';
grant app_read to 'admin2'@'%','admin1'@'%'; # 允許批量給用戶分配角色
grant app_write to 'admin3'@'%';

注意：雖然權(quán)限已經(jīng)最終關(guān)聯(lián)到用戶，但是用戶并不能真正使用權(quán)限，還需要權(quán)限分配者每次登陸服務(wù)器時激活角色：set default role all to 用戶名@主機地址（一次只能激活一個角色）

• 激活之后對應(yīng)的用戶需要退出之后重新登錄才行

2、回收角色權(quán)限或者角色

# 回收角色權(quán)限
revoke insert,delete on db_2.* from app_write;

# 回收角色
revoke app_read from 'admin2'@'%';

# 刪除角色
drop role developer;

小結(jié)

1、角色管理是利用角色與權(quán)限關(guān)聯(lián)，實現(xiàn)角色批量關(guān)聯(lián)用戶

• 方便權(quán)限的重復(fù)利用
• 方便相同權(quán)限用戶的批量維護

2、角色的使用需要角色創(chuàng)建者（有權(quán)限的就行）激活角色才能使用（關(guān)聯(lián)角色的用戶需要重新登錄才會生效）