使用Java實現資料庫編程—05 事務、視圖、索引、備份和恢復

事務是將一系列數據操作綁成一個整體進行統一管理。

如果一事務執行成功，則咋子該事務中進行的所有數據更改均會提交，稱為資料庫中的永久成部分。

如果事務執行是遇到錯誤且必須取消或回滾，則數據將全部恢復到操作前的狀態，所有數據的更改均被清除。

定義：

事務是一種機制、一個操作序列，包含了一組資料庫操作命令，並且把所欲的命令作為一個整體起向系統提交側小操作請求，即這一組資料庫命令要麼都執行，要麼都不執行。

  Eg：

轉賬過程就是一個整體

它需要兩條UPDATE語句來完成，這兩條語句是一個整體

如果其中任一條出現錯誤，則整個轉賬業務也應取消，兩個賬戶中的餘額應恢復到原來的數據，從而確保轉賬前和轉賬後的餘額不變，即都是1001元

事務是作為單一邏輯工作單元執行一系列操作。一個邏輯工作單位必須有四個屬性，

即原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）、持久性（Durability），這些特性通常簡稱為ACID。

●原子性：事務是一個完整的操作，事務的各步操作（各元素）是不可分的(原子的)，要麼都執行，要麼都不執行

●一致性：當事務完成時，數據必須處於一致狀態。

●隔離性：對數據進行修改的所有併發事務是彼此隔離的，這表明事務必須是獨立的，他不應以任何方式依賴於或影響其他事務。

● 持久性：事務的持久性指不管系統是否發生了故障，事務處理的結果都是永久的。

預設設置下，枚舉SQL語句就是一個事務，即執行SQL語句後自動提交，為了達到將幾個操作作為一個整體的目的，

需要使用BEGIN或START TRANSACTION開啟一個事務，或者執行命令SETAUTOCOMMIT = 0，

來禁止當前回話的自動提交後，後面的語句作為事務的開始。

MySQL中支持事務的存儲引擎有InnoDB和BDB

執行事務的語法：

SQL使用下列語句來管理事務。

（1）開始事務

語法

BEGIN;   //開始的意思  

或者  

START  TRANSACTION;

這個語句顯式地標記一個事務的起始點。

（2）提交事務

語法

COMMIT;

這個語句標誌一個事務成功提交。

自事務開始至提交語句之間執行的所有數據更新將永久地保存在資料庫數據文件中，並釋放連接是占用的資源。

（3）回滾(撤銷)事務

語法

ROLLBACK;

清除自事務起始點至該語句所做的所有數據更新操作，將數據狀態回滾到事務開始前，並釋放由事務控制的資源。

設置值自動提交關閉或開始：

MySQL中預設開啟自動提交模式，即為指定開啟事務時，

每條SQL語句都是單獨的事務執行完畢自動提交，可以關閉自動提交模式，手動提交或回滾事務。

語法

SET  autocommit = 0 | 1;

值為0：關閉自動提交。

值為1：開啟自動提交。

                           當執行SET autocommit = 0後，即關閉自動提交，

從下一條SQL語句開始則開啟新事務，需使用COMMIT或ROLLBACK語句結束該事務。

編寫事務的原則：

事務儘可能簡短；

事務中訪問的數據量儘量最少；

查詢數據時儘量不要使用事務；

在事務處理過程中儘量不要出現等待用戶輸入的操作；

視圖是保存在資料庫中的SELECT查詢。因此，對查詢執行的大多數操作餓可在視圖上進行。

使用視圖的原因有兩點：一個是處於安全考慮，用戶不必看到整個資料庫的結構，而隱藏部分數據;另一個是符合用戶日常業務邏輯，使他們更容易理解數據。

定義：

       視圖是另一種查看資料庫中一個或多個表中數據的方法。

視圖是一種虛擬表，通常是作為來自一個或多個表的行或列的子集創建的。當然，它也可以包含全部的行和列。

但是，視圖並不是資料庫中存儲的數值的集合，它的行和列來自查詢中引用的表。在執行是，他直接顯示來自於表中的數據。

視圖充當著查詢中指定表篩選器。定義視圖的查詢可以基於一個或多個表，也可以基於其他視圖、當前資料庫或其他資料庫。

n  視圖是一張虛擬表

u 表示一張表的部分數據或多張表的綜合數據

u 其結構和數據是建立在對錶的查詢基礎上

n  視圖中不存放數據

u 數據存放在視圖所引用的原始表中

n  一個原始表，根據不同用戶的不同需求，可以創建不同的視圖

視圖通常用來進行一下三種操作：

（1）篩選表中的行。

（2）防止為經許可的用戶訪問敏感數據。

（3）將多個物理數據表抽象為一個邏輯數據表。

（4）降低資料庫的複雜程度

對最終用戶的好處：

（1）結果更容易理解。創建視圖是，可以將列名改為有意義的名稱，使用戶更容易理解列所有表的內容。在視圖中修改名不會影響基表的列名。

（2）獲得數據更容易。很多人對SQL不太瞭解，因此對他們來說，創建對多個表的複雜查詢很困難，可以通過創建視圖來方便用戶訪問多個表中的數據。

對開發人員的好處：

（1）限制數據檢索更容易。開發人員於是需要隱藏某些行或列中的信息。通過使用視圖，用戶可以靈活地訪問他們的數據，同時保證同一個表或其他表中的其他數據的安全性。要實現這一個目標，可以在創建視圖時將對用戶保密的列排出在外。

（2）維護應用程式更方便。調試視圖表調試查詢更容易，跟蹤視圖中各個步驟的錯誤更為容易，這是因為所有的步驟都是視圖的組成部分。

使用SQL語句創建視圖

           語法：

CREATE  VIEW  視圖名

AS

<SELECT語句>

註：在SQL語句名命規範中，視圖一般以view_xxx或v_xxx的樣式來命名；

與創建數據表相同，在創建視圖之前，如果資料庫中已存在同名視圖，需要先刪除在創建。

使用SQL語句刪除視圖

語法

DROP  VIEW  [IF  EXISTE] 視圖名;

使用SQL語句看視圖數據

語法

SELECT  欄位1，欄位2，…… FROM  view_name;

                使用查詢語句SELECT執行視圖的SQL代碼，可獲得數據結果集。

查看所有視圖

USE information_schema;

SELECT * FROM views\G;

使用視圖的註意事項

（1）每個視圖中可以使用多個表。

（2）與查詢相似，一個視圖可以嵌套另一個視圖，但最好不要超過三層。

（3）對視圖數據進行添加、更新和刪除操作直接引用表中的數據。

（4）當視圖數據來自多個表時，不允許添加和刪除數據。

註：使用視圖修改數據會有許多限制，一般在實際開發中視圖僅用作查詢

索引提供指針有存儲在表中指定列的數據值，在根據指定的排序列這些指針。

資料庫使用索引的方式與使用書的目錄很相似；通過搜索索引找到特定的值，在跟隨指針到包含該值的行。

 索引是一種有效組合數據的方式，為快速查找到指定記錄

   作用:

　　大大提高資料庫的檢索速度

　　改善資料庫性能

　　MySQL索引按存儲類型分類

　　B-樹索引：InnoDB、MyISAM均支持

　　哈希索引

MySQL中，常用的索引有以下六類。

1. 普通索引：普通索引是MySQL中的基本索引類型，允許在定義索引的列中插入重覆值和空值。

2.唯一索引：唯一索引不允許兩行具有相同的索引值。如果現有數據中存在重覆的鍵值，則一般情況下多數資料庫不允許創建唯一索引。

允許有空值

3.主鍵索引：在資料庫關係圖中為表定義一個主鍵將自動創建主鍵索引，主鍵索引是唯一索引的特殊類型。

           主鍵列中的每個值是非空、唯一的

4.複合索引：在創建索引是，並不是只能對其他一列創建索引，與創建主鍵一樣，可以多個列組合作為索引，這種索引稱為符合索引。

5.全文索引：全文索引的作用是在定義索引的列上支持值的全文查找，允許這些索引列中插入重覆的空值和重覆值。

6.空間索引：空間索引是對空間數據類型的列建立的索引，如GEOMETRY、POINT等。

創建索引：使用CREATE INDEX語句可以在以經存在的表上添加索引：

語法

CREATE  [UNIQUE | FULLTEXT | SPATIAL] INDEX  index_name

ON  table_name  (column_name[length]……)

其中：

（1）UNIQUE | FULLTEXT | SPATIAL：分別表示唯一索引、全文索引的空間索引，為可選參數。

（2）index_name：指定索引名。

（3）column_name：指定需要創建索引的列。

（4）length：指定索引長度，可選參數，只有字元創類型才能指定索引長度。

(5) table_name：指定創建索引的表名。

刪除索引：

語法

DROP  INDEX  index_name  ON  table_name;

  註：刪除表時，該表的所有索引將同時被刪除。

刪除表中的列時，如果要刪除的列為索引的組成部分，則該列也會從索引中刪除。如果組成索引的所有列都被刪除，則整個索引將被刪除。

n  按照下列標準選擇建立索引的列

u 頻繁搜索的列

u 經常用作查詢選擇的列

u 經常排序、分組的列

u 經常用作連接的列（主鍵/外鍵）

n  不要使用下麵的列創建索引

u 僅包含幾個不同值的列

u 表中僅包含幾行

使用索引時註意事項：

n  查詢時減少使用*返回全部列，不要返回不需要的列

n  索引應該儘量小，在位元組數小的列上建立索引

n  WHERE子句中有多個條件表達式時，包含索引列的表達式應置於其他條件表達式之前

n  避免在ORDER BY子句中使用表達式

n  根據業務數據發生的頻率，定期重新生成或重新組織索引，進行碎片整理；

查看索引：

語法：

SHOW INDEX FROM table_name;

Eg：查看myschool資料庫中全部索引信息

USE myschool;

SHOW INDEX FROM `student`\G;

分析：

Table：創建索引的表

Non_unique：索引是否非唯一

Key_name：索引的名稱

Column_name：定義索引的列欄位

Seq_in_index：該列在索引中的位置

Null：該列是否能為空值

Index_type：索引類型

mysqldump是MySQL一個常用的備份命令（屬於DOS命令），執行此命令將包含數據的表結構和數據內容轉換成相應的CREATE語句和INSERT INTO語句，保存在文本文件中，將來如果需要還原數據，只需執行該文本文件中的SQL語句即可。

mysqldump命令格式

語法

mysqldump  -u  username  -h  host  -ppassword

           dbname[ tbname1 [,tbname2……] ] > filename.sql

其中：

（1）username表示用戶名。

（2）host表示登錄用戶的主機名稱，如本機為主機可省略。

（3）password表示登錄密碼。

（4）dbname為需要備份的資料庫。

（5）tbname為需要備份的數據表，可指定多張表。為可選項，如備份整個資料庫則此項省略。

（6）filename.sql表示備份的文件名。

   註意：mysqldump是DOS系統下的命令，在使用時無須進入mysql命令行，否則將無法執行

mysqldump的常用參數：

語法：

mysqldump –help

備份文件包含的主要信息

　　備份後文件包含信息MySQL及mysqldump工具版本號

　　備份賬戶的名稱

　　主機信息

　　備份的資料庫名稱

　　SQL語句註釋和伺服器相關註釋

　　CREATE和INSERT語句

對於備份資料庫後生成的包含有建庫、建表、插入數據等SQL語句的文本文件，可以通過mysql命令還遠到心的資料庫中，實現資料庫的恢復。

語法

mysql –u username –p [dbname] < filename.sql

         其中：

（1）username表示用戶名。

（2）dbname表示資料庫名字。

（3）filename.sql為資料庫備份後的文件。

註：1. mysql為DOS命令

2.在執行該語句之前，必須在MySQL伺服器中創建新資料庫，如果不存在恢複數據庫過程將會出錯

mysql命令是在DOS環境下的恢複數據庫命令，如果已經登錄了MySQL伺服器，也可以使用source命令恢複數據庫。

語法：

source  filename;             //filename為資料庫備份文件。

註：登錄MySQL服務後使用

執行該命令前，先創建並選擇恢復後的目標資料庫

MySQL伺服器的數據在磁碟中是以文件形式保存的，所以可以直接複製MySQL資料庫的存儲目錄及文件進行備份。

通過對錶數據的導出和導入，可以實現MySQL數據伺服器與其他資料庫伺服器間移動數據。

導出操作，是指將數據從MySQL數據表複製到文本文件，數據導出的方式有很多種。

使用SELECT …… INTO OUTFILE語句導出數據。

語法：

 SELECT  columnlist  FROM  tablename

     [WHERE  contion]

     INTO  OUTFILE  ‘filename’  [OPTION];

從上述語法中可以看出，該導出語句分成以下兩部分：

（1）普通的數據查詢語句，主要用來實現查詢所要導出到文本文件中的數據。

（2）通過參數filename指定導出數據的目標文件

導入操作，是指將數據從文本文件載入到MySQL資料庫表裡。

同樣，導入資料庫的方式也有多種。

使用LOAD DATA INFILE語句實現數據的導入，

語法：

LOAD  DATA  INFILE  filename  INTO  TABLE  tablename  [OPTION];

其中：

（1）filename用來指定文本文件的路徑和特征。

（2）tablename用來指定導入表的名稱。

              註：導入數據前應確保目標表已存在！

事務處理的命令包括START TRANSACTION, COMMIT和ROLLBACK，

其中COMMIT用於提交事務，

ROLLBACK用戶回滾事務。、

DDL也會影響到事務的提交，eg：TRUNCATE TABLE （DDL(數據定義語言)    用於創建和刪除資料庫對象等操作       CREATE 、DROP 、ALTER）

事務開啟，除了STARTTRANSACTION之外，DML語句中增加、修改、刪除都會開啟事務。

UPDATE：更新   DELETE：刪除    INSERT：新增     SELECT：查詢

唯一索引，不允許具有索引值相同的行，從而禁止重覆的索引或鍵值。

 mysql通過 GRANT 授予許可權 ，授予root用戶全局級全部許可權：

GRANT ALL ON *.* to 'root'@'%' IDENTFIED BY '123456'; //給root設置密碼

mysql的資料庫事務隔離級別是：

Read Uncommitted（讀取未提交內容）：在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。

本隔離級別很少用於實際應用，因為它的性能也不比其他級別好多少。

讀取未提交的數據，也被稱之為臟讀（Dirty Read）。

Read Committed（讀取提交內容）：這是大多數資料庫系統的預設隔離級別（但不是MySQL預設的）。

它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。

這種隔離級別也支持所謂的不可重覆讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的commit，

所以同一select可能返回不同結果。

Repeatable Read（可重讀）：這是MySQL的預設事務隔離級別，它確保同一事務的多個實例在併發讀取數據時，會看到同樣的數據行。

不過理論上，這會導致另一個棘手的問題：幻讀（Phantom Read）。

簡單的說，幻讀指當用戶讀取某一範圍的數據行時，另一個事務又在該範圍內插入了新行，

當用戶再讀取該範圍的數據行時，會發現有新的“幻影” 行。

InnoDB和Falcon存儲引擎通過多版本併發控制（MVCC，Multiversion Concurrency Control）機制解決了該問題。

Serializable（可串列化）：這是最高的隔離級別，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。

簡言之，它是在每個讀的數據行上加上共用鎖。在這個級別，可能導致大量的超時現象和鎖競爭。

索引是關係資料庫中用於存放每一條記錄的一種對象，主要目的是加快數據的讀取速度和完整性檢查；

創建唯一索引的目的不是為了提高訪問速度，而只是為了避免數據出現重覆。

唯一索引可以有多個但索引列的值必須唯一，索引列的值允許有空值。

如果能確定某個數據列將只包含彼此各不相同的值，在為這個數據列創建索引的時候就應該使用關鍵字UNIQUE。

唯一性索引列允許空值，而主鍵列不允許為空值。