【整理】MySQL深入

1. 視圖

    視圖是虛擬表。與包含資料的表不一樣，視圖只包含使用時動態檢索資料的查詢（即：包含一個SQL查詢），僅僅是用來查看儲存在別處的資料的一種設施。

    視圖基本操作：（1）建立：CREATE VIEW；（2）查看：SHOW CREATE VIEW viewname；（3）刪除：DROP VIEW viewname；（4）更新：CREATE OR REPLACE VIEW。

    視圖可以嵌套，但不能索引，也不能有關聯的觸發器或預設值。並非所有視圖都是可更新的，如果MySQL不能正確確定被更新的基資料，則不允許更新（包括插入和刪除）。

    視圖不能更新的情況：（1）分組，使用GROUP BY和HAVING；（2）聯結；（3）子查詢；（4）並；（5）聚集合函式，Min/Count/Sum等；（6）DISTINCT；（7）匯出列。

    視圖常見應用：（1）隱藏複雜的SQL，這通常都會涉及聯結；（2） 重新格式化檢索出的資料；（3）過濾不想要的資料；（4）簡化計算欄位的使用。

    eg：CREATE VIEW productcustomers
AS

            SELECT cust_name, cust_contact, prod_id

            FROM customers, orders, orderitems

            WHERE customers.cust_id = orders.cust_id

                AND orderitems.order_num = orders.order_num;

            SELECT cust_name, cust_contact FROM
productcustomers WHERE prod_id = 'TNT2';

 

2. 預存程序

     預存程序簡單來說，就是為以後的使用而儲存的一條或多條MySQL語句的集合（實際上是一種函數），可將其視為批次檔，雖然它們的作用不僅限於批處理。使用預存程序有3個主要的好處：簡單、安全、高效能。

    （1）執行：CALL procedure_name(parameter_list);

    （2）建立：CREATE PROCEDURE procedure_name(parameter_list) BEGIN ...... END;

    （3）刪除：DROP PROCEDUREIF EXISTS procedure_name;

    （4）查看：SHOW CREATE PRODECURE procedure_name; SHOW PROCEDURE STATUR LIKE '***';

    變數：記憶體中一個特定的位置，用於臨時儲存資料，所有MySQL變數都必須以@開頭。

    eg: CREATE PROCEDURE
productpricing(

                OUT pl DECIMAL(8, 2), OUT ph DECIMAL(8, 2), OUT pa DECIMAL(8, 2) 

            )

            BEGIN

                SELECT Min(prod_price) INTO pl FROM products;

                SELECT Max(prod_price) INTO ph FROM products;

                 SELECT Avg(prod_price) INTO pa FROM products;

            END;

        調用：CALL productpricing(@pricelow, @pricehigh, @priceaverage);         

         

3. 遊標

    遊標（cursor）是一個儲存在MySQL伺服器上的資料庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結果集，主要用於互動式應用（定位結果集的行，通過判斷全域變數@@FETCH_STATUS可判斷其是否到達了最後，通常此變數不等於0時表示出錯或到了最後），其中使用者需要滾動螢幕上的資料，並對資料進行瀏覽或作出更改。MySQL遊標只能用於預存程序（和函數）。

    使用遊標的步驟：（1）在能夠使用遊標前，必須聲明/定義它，這個過程實際上沒有檢索資料，只是定義要使用的SELECT語句；（2）一旦聲明後，必須開啟遊標以供使用，這個過程用前面定義的SELECT語句把資料實際檢索出來；（3）對於填有資料的遊標，根據需要取出/檢索各行；（4）在結束遊標使用時，必須關閉遊標。

    遊標用DECLARE語句建立，DECLARE命名遊標，並定義相應的SELECT語句，根據需要帶WHERE和其他子句。用DECLARE定義的局部變數必須在定義任意遊標或控制代碼之前定義，而控制代碼必須在遊標之後定義。

    eg：CREATE PROCEDURE
processorders()

            BEGIN

                DECLARE ordernumbers CURSOR

                FOR

                SELECT order_num FROM orders;

            END;

    開啟遊標：OPEN ordernumbers;    #在處理OPEN語句時執行查詢，儲存檢索出的資料以供瀏覽和滾動

    關閉遊標：CLOSE ordernumbers；    #CLOSE釋放遊標使用的所有內部記憶體和資源

    使用遊標資料：在一個遊標被開啟後，可使用FETCH語句分別訪問它的每一行。

 

4. 觸發器

    觸發器是MySQL響應DELETE/INSERT/UPDATE語句而自動執行的一條MySQL語句（或位於BEGIN/END間的一組語句）。只有表才支援觸發器，視圖不支援，暫存資料表也不支援。觸發器按每個表每個事件每次地定義，每個表每個事件每次只允許一個觸發器，所以每個表最多支援6個觸發器（每條INSERT、UPDATE和DELETE的之前和之後）。單一觸發器不能與多個事件或多個表關聯。

    建立觸發器時，需要給出4條資訊：（1） 唯一的觸發器名；（2）觸發器關聯的表；（3）觸發器應該響應的活動；（4）觸發器何時執行（處理之前或之後）。如果BEFORE觸發器失敗，MySQL將不執行請求的操作；如果BEFORE觸發器或語句本身失敗，MySQL將不執行AFTER觸發器。觸發器不能更新或覆蓋。MySQL觸發器不支援CALL語句，即不能從觸發器調用預存程序。

    （1）建立：CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';

    （2）刪除：DROP TRIGGER newproduct;

    INSERT觸發器：在INSERT語句執行之前或之後執行。（1）在INSERT觸發器代碼內，可飲用一個名為NEW的虛擬表，訪問被插入的行；（2）在BEFORE INSERT觸發器中，NEW中的值也可以被更新，即允許更改被插入的值；（3）對於AUTO_INCREMENT列，NEW在INSERT執行之前包含0，在INSERT執行之後包含新的自動產生值。

    DELETE觸發器：在DELETE語句執行之前或之後執行。（1）在DELETE觸發器代碼內，可引用一個名為OLD的虛擬表，訪問被刪除的行；（2）OLD中的值全都是唯讀，不能更新。

    UPDATE觸發器：在UPDATE語句執行之前或之後執行。（1）在UPDATE觸發器代碼中，可引用一個名為OLD的虛擬表訪問以前的值，引用一個名為NEW的虛擬表訪問新更新的值；（2）在BEFORE UPDATE觸發器中，NEW中的值可能也被更新，即允許更改將要用於UPDATE語句中的值；（3）OLD中的值全都是唯讀，不能更新。

    應該用觸發器來保證資料的一致性（大小寫、格式等）：在觸發器中執行這種類型處理的優點是它總是進行這種處理，且是透明地進行，與客戶機應用無關。

 

5. 交易處理

    交易處理（transaction processing）用於維護資料庫的完整性，保證成批的MySQL操作要麼完全執行，要麼完全不執行。並非所有引擎都支援交易處理，MyISAM不支援明確的交易處理管理，而InnoDB支援。管理交易處理的關鍵在於將SQL語句組分解為邏輯塊，並明確規定資料何時應該回退，何時不應該回退。 

    事務（transaction）：指一組SQL語句，具有原子性、一致性、獨立性及持久性等特點。

    回退（rollback）：指撤銷指定SQL語句的過程；ROLLBACK只能在一個交易處理內使用，即在執行一條START TRANSACTION命令之後。交易處理用來管理INSERT、UPDATE和DELETE語句，你不能回退SELECT語句，也不能回退CREATE或DROP操作，交易處理中可以使用這兩條語句，但如果你執行回退，它們不會被撤銷。

    提交（commit）：指將未儲存的SQL語句結構哦寫入資料庫表；在交易處理塊中，提交不會隱含地進行，須使用COMMIT明確提交。當COMMIT或ROLLBACK執行後，事務會自動關閉，將來的更改會隱含提交。

    保留點（savepoint）：指交易處理中設定的臨時預留位置，可對它發布回退（與回退整個交易處理不同）；保留點在交易處理完成（執行一條ROLLBACK或COMMIT）後自動釋放，也可使用RELEASE SAVEPOINT明確釋放保留點。

    更改預設提交行為：SET autocommit=0; autocommit標誌決定是否自動認可更改，不管有沒有COMMIT語句；autocommit標誌是針對每個串連而不是伺服器的。

 

6. 索引

    叢集索引確定資料在表中的實體儲存體順序，一個表只能包含一個叢集索引，但該索引可包含多個列（複合式索引）。叢集索引對那些經常要搜尋範圍值的列特別有效，使用叢集索引找到包含第一個值的行後，便可確定包含後續索引值的行在物理上相鄰。

    非叢集索引順序與資料物理排列順序無關，索引儲存在一個地方，資料存放區在另一個地方，索引帶有指標指向資料的儲存位置。索引中的項目按索引索引值的順序儲存，而表中的資訊按另一種順序儲存(可由叢集索引規定)。如果在表中未建立叢集索引，則無法保證這些行具有任何特定的順序。