大資料教程之《MYSQL資料庫》TCL語言和DCL語言

　　好程式設計師大資料培訓分享之《MYSQL 資料庫》 TCL 語言和 DCL 語言： 一、TCL 語言的操作語法 ， 有時候我們的一個需求( 一個任務 ), 可能會涉及到多個 DML( 增刪改 ) 操作。那麼這一系列操作， 我們要看成一個整體，不可切割。如在一個員工系統中，某員工 A 的資料可能會存在多個表中，如基本資訊表 , 業務資訊表，郵箱資訊表等。當我們想刪除此人的所有資訊時，除了刪除這個人的基本資訊外，還應該刪除與此人有關的其他所有資訊，如郵箱，地址等等。這樣的操作會涉及到多個 DML 語句。那麼從開始執行到真正結束，就會構成一個事務 (Transaction) 。對於事務，我們要保證事務的完整性，不能分割。事務要麼成功， 要麼撤回 ( 回到執行前 ) ，不能執行一部分就終止。

　　 注意，只有DML 操作時，才會涉及到事務。而 mysql 資料庫在執行完每一個 DML 操作時，會預設 commit; 可以使用 start transaction 來取消自動提交

commit：用於提交，表示事務成功結束。用於確認資料的更改，持久化。

rollback：用於回滾，回退到事務開始前。取消所有的DML操作。

savepoint: 設定事務的儲存點，可以回退到指定的儲存點。

事務有四個基本特徵，簡稱ACID.

(1) 原子性(Atomicity)： 即事務要麼成功，要麼撤回，不可切割。
(2) 一致性(Consistency)：事務開始前和結束後，要保證資料的一致性。如轉賬案例，轉賬前賬號A和賬號B的錢的總數為10000。轉賬後賬號A和賬號B的錢的總數應該還是10000;
(3) 隔離性(Isolation)：當涉及到多使用者操作同一張表時，資料庫為會每一個使用者開啟一個事務。那麼當其中一個事務正在進行時，其他事務應該處於等待狀態。保證事務之間不會受影響。
(4) 永續性(Durability)： 當一個事務被提交後，我們要保證資料庫裡的資料是永久改變的。即使資料庫崩潰了，我們也要保證事務的完整性。

關於事務的隔離性，尤為重要。當多使用者(多執行緒)同時操作同一張表時，如果沒有隔離機制，可能會發生以下問題：

髒讀：事務A讀取了事務B剛剛更新的資料，但是事務B回滾了，這樣就導致事務A讀取的為髒資料，我們稱之為髒讀。
如公司某財務人員更新公司入賬報表時，在DML語句中的數字後少新增了一個0，但是未提交，然後吃飯，吃飯回來，發現錯誤，然後更正後做了提交。而在吃飯期間，老闆要求秘書檢視一下報表，秘書看到的是少個0的資料。這就是髒讀。

 

不可重複讀：事務A讀取同一條記錄兩次，但是在兩次之間事務B對該條記錄進行了修改並提交，導致事務A兩次讀取的資料不一致。
它和髒讀的區別是，髒讀是事務A讀取了另一個事務B未提交的髒資料，而不可重複讀則是事務A讀取了事務B提交的資料。
多數情況下，不可重複讀並不是問題，因為我們多次查詢某個資料時，當然要以最後查詢得到的結果為主。但在另一些情況下就有可能發生問題，比如，老闆讓B和C分別核對事務A操作的資料，結果可能不同，老闆是懷疑B呢，還是C呢？

 

幻讀：事務A在修改全表的資料，比如將欄位age全部修改為0歲，在未提交時，事務B向表中插入或刪除資料，如插入一條age為25歲的資料。這樣導致事務A讀取的資料與需要修改的資料不一致，就和幻覺一樣。
幻讀和不可重複讀的相同點：都是針對於另外一個已經提交的事務而言。不同點：不可重複讀是針對於同一條記錄來說的（delete或update 同一條記錄），而幻讀是針對於一批資料來說的（insert）

事務的隔離機制有以下四種：

1、未提交讀（read uncommitted）：就是不做隔離控制，可以讀到“髒資料”，可能發生不可重複讀，也可能出現幻讀。
2、提交讀（read committed）：提交讀就是不允許讀取事務沒有提交的資料。顯然這種級別可以避免了髒讀問題。但是可能發生不可重複讀，幻讀。這個隔離級別是大多數資料庫（除了mysql）的預設隔離級別。  
3、可重複讀（repeatable read）：為了避擴音交讀級別不可重複讀的問題，在事務中對符合條件的記錄上"排他鎖"，這樣其他事務不能對該事務操作的資料進行修改，可避免不可重複讀的問題產生。由於只對運算元據進行上鎖的操作，所以當其他事務插入或刪除資料時，會出現幻讀的問題，此種隔離級別為Mysql預設的隔離級別。
4、序列化（Serializable），在事務中對錶上鎖，這樣在事務結束前，其他事務都不能夠對錶資料進行操作（包括新增，刪除和修改），這樣避免了髒讀，不可重複讀和幻讀，是最安全的隔離級別。但是由於該操作是堵塞的，因此會嚴重影響效能。

相關語法格式如下：

檢視資料庫的當前隔離機制： select @@tx_isolation;
修改資料庫的隔離機制：set transaction isolation level 機制名稱 或者是 set tx_isolation='機制名稱';
如：set transaction isolation level read uncommitted 或者 set tx_isolation=‘read-uncommitted’；

二、DCL語言的操作語法

（1）建立使用者：create user username@ip identified by newPassword;

（2）修改密碼：alter user username@ip identified by newPassword;

（3）顯示使用者的許可權：show grants for username@ip;

（4）授權語句：grant 許可權1，許可權2，...... on databaseName.* to username@ip;

DDL許可權名稱: create、alter、drop、truncate 、create view 等等

DML許可權名稱: insert、delete、update

DQL許可權名稱: select

（5）撤銷許可權：revoke 許可權1，許可權2，..... on databaseName.* from username@ip;

（6）刪除使用者：drop user username;

（7）許可權生效語句：flush privileges;