MySQL之21-29重點：視圖，觸發器，存儲過程，游標，事務處理_MySQL

21．創建和操作表

21.1.創建表

CREATE TABLE創建表，必須給出下列信息：

1） 新表的名字，在關鍵字CREATETABLE之后

2） 表列的名字和定義，用逗號分隔

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOTNULL,

cust_address char(50) NULL,

cust_city char(50) NULL,

cust_state char(5) NULL,

cust_zip char(10) NULL,

cust_country char(50) NULL,

cust_contact char(50) NULL,

cust_email char(255) NULL,

primaryKEY (cust_id)

) ENGINE = InnoDB;

有以下幾點需要注意：

1）其中主鍵也可以用多個列組成，如orderitems表中在主鍵表示如下：

PRIMARY KEY(order_num,order_item)

主鍵只能使用不允許NULL值的列。

2）每個表只允許一個AUTO_INCREMENT列。可用SELECTlast_insert_id()獲取最后一個AUTO_INCREMENT值。

3）用DEFAULT指定默認值

4)引擎類型

InnoDB是一個可靠的事物處理引擎，它不支持全文本搜索

MEMORY在功能上等同于MyISAM,但由于數據存儲在內存中，速度很快（特適合于臨時表）

MyISAM是一個性能極高的引擎，它支持全文本搜索，但不支持事物處理

引擎可以混用，但外鍵不能夸引擎。

21.2更新表

必須提供以下信息：

1）在ALTER TABLE之后給出要更改的表名

2）所做更改的列表

ALTER TABLE vendors ADD vend_phoneCHAR(20);//增加一個新列

ALTER TABLE vendors DROP vend_phone;//刪除一個列

定義外鍵：

ALTER TABLE orderitems ADD CONSTRAINTfk_orderitems_orders FOREIGN KEY (order_num) REFERENCE orders(order_num);

21.3刪除表

DROP TABLE customers;

21.4重命名表

RENAME TABLE backup_customers TO customers;

22.使用視圖

視圖是虛擬的表。與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢。

視圖不包含表中應該有的任何列或數據，它包含的是一個SQL查詢。視圖僅僅用來查看存儲在別處數據的一種設施。視圖本身不包含數據，因此它們返回的數據是從其他表中檢索出來的。在添加或更改這些彪中國的數據時，視圖將返回改變過的數據。每次使用視圖時，都必須處理查詢執行時所需要的任一檢索。

視圖不能索引，也不能有關聯的觸發器或默認值。

使用視圖：

1）視圖用CREATE VIEW來創建

2）使用SHOW CREATE VIEWviewname;來查看創建視圖的語句

3）用DROP刪除視圖，其語法為DROPVIEW viewname;

4) 更新視圖時，可以先用DROP再用CREATE,也可以直接用CREATEOR REPLACE VIEW。

22.1利用視圖簡化復雜的聯結

mysql> CREATE VIEW productcustomers ASSELECT cust_name,cust_contact,prod_id FRO

M customers,orders,orderitems WHEREcustomers.cust_id = orders.cust_id AND order

items.order_num = orders.order_num;

Query OK, 0 rows affected (0.13 sec)

為檢索訂購產品TNT2的客戶，可如下執行：

mysql> SELECT cust_name,cust_contactFROM productcustomers WHERE prod_id = 'TNT2

';

+----------------+--------------+

| cust_name | cust_contact |

+----------------+--------------+

| Coyote Inc. | YLee |

| Yosemite Place | Y Sam |

+----------------+--------------+

2 rows in set (0.00 sec)

22.2用視圖重新格式化檢索出的數據

SELECT * FROM productcustomers;

22.2視圖的更新

有時，視圖是可更新的（即可以對它們使用INSERT、UPDATE和DELETE），對視圖增加或刪除行，實際上是對其基表增加或刪除行。

但是，如果視圖定義中有以下操作，則不能進行更新：

1） 分組（使用GROUP BY和HAVING）

2） 聯結

3） 子查詢

4） 并

5） 聚集函數

6） DISTINCT

7） 導出（計算）列

23.使用存儲過程

存儲過程簡單來說，就是為以后的使用而保存的一條或多條MySQL語句的集合。可將其視為批文件，雖然它們的作用不僅限于批處理。

存儲過程有3個主要的好處：簡單、安全、高性能。

23.1執行存儲過程

MySQL稱存儲過程的執行為調用，因此MySQL執行存儲過程的語句為CALL。CALL接受存儲過程的名字以及需要傳遞給它的任意參數。

CALL producpricing(@pricelow,@pricehigh,priceaverage);

其中，執行名為productpricing的存儲過程，它計算并返回產品的最低、最高和平均價格。存儲過程可以顯示結果，也可以不顯示結果。

23.2創建過程

CREATE PROCEDURE productpricing()

BEGIN

SELECTAve(prod_price) AS priceaverage

FROMproducts;

END;

此存儲過程名為productricing,用CREATE PROCEDURE productpricing()語句定義。如果存儲過程接受參數，它們將在()中列舉出來。此存儲過程沒有參數，但后跟的()仍然需要。BEGIN和END語句用來限定存儲過程體，過程體本事僅是一個簡單的SELECT語句。

在MySQL處理這段代碼時，它創建一個新的存儲過程productpricing。沒有返回數據，因為這段代碼并未調用存儲過程，這里只是為以后使用而創建它。

如果使用MySQL命令行使用程序，由于默認的MySQL語句結束符為；，則存儲體內的；字符將不會最終成為儲存過程的成分，這會導致語法錯誤。解決辦法是使用新語句分隔符。DELIMITER //

CREATE PROCEDURE productpricing()

BEGIN

SELECTAvg(prod_price) AS priceaverage

FROMproducts;

END//

DELIMITER;

其中DELIMITER//告訴命令行實用程序使用//作為新的語句結束符，可以看到表示存儲過程結束的END定義END//而不是END;作為語句結束。最后使用DELIMITER ；恢復原來的語句結束符。然后調用結果如下：

mysql> CALL productpricing();

+--------------+

| priceaverage |

+--------------+

| 16.133571 |

+--------------+

1 row in set (0.30 sec)

Query OK, 0 rows affected (0.30 sec)

23.2刪除存儲過程

DROP PROCEDURE productpricing;//當不存在時報錯

DROP PROCEDURE productpricing IF EXISTS;//僅當存在時刪除，不存在時不報錯

23.3使用參數

一般，存儲過程并不顯示結果，而是把結果返回給你指定的變量。

變量：內存中一個特定的位置，用來臨時存儲數據。所有MySQL變量必須以@開始。

CREATE PROCEDURE order（IN onumberINT, OUT ototal DECIMAL(8,2)）

BEGIN

SELECTSum(item_price*quantity)

FROMorderitems

WHEREorder_num = onumber

INTOototal;

END;

此存儲過程接受兩個參數，每個參數必須指定參數類型。關鍵字IN指出相應參數用來傳遞給存儲過程，OUT（從存儲過程傳出），INOUT（對存儲過程傳入和傳出）。存儲過程的代碼位于BEGIN和END語句內。

為調用這個存儲過程，可使用如下語句：

CALL ordertotal(2005,@total);

為顯示此合集，如下

SELECT @total;

為得到另一個訂單的合計信息，需要再次調用存儲過程

CALL ordertotal(2009,@total);

SELECT @total;

23.4 建立智能存儲過程

只有在存儲過程內包含業務規則和智能處理時，他們的威力彩真正顯現出來。

CREATE PROCEDURE ordertotal(

INonumber INT,

INtaxable BOOLEAN,

OUTototal DECIMAL(8,2)

) COMMENT 'Obtain order total, optionallyadding tax'

BEGIN

--Declare variable for total

DECLARE total DECIMAL(8,2);

--DECLARE tax percentage

DECLARE taxrate INT DEFAULT 6;

--Getthe order total

SELECT Sum(item_price*quantity)

FROM orderitems

WHERE order_num = onumber

INTO total;

--Is this taxable?

IFtaxable THEN

--Yes, so add taxrate to total

SELECT total+(total/100*taxrate) INTO total;

ENDIF;

--And finally,save to out variable

SELECT total INTO ototal;

END；

這里COMMERNT關鍵字不是必須的，但如果給出，將在SHOW PROCEDURE STATU的結果中顯示。

MySQL除了IF語句,還指出ELSEIF和ELSE子句（ELSEIF還須用THEN, ELSE不使用）

調用并顯示如下結果1:如下

mysql> CALL ordertotal(20005,0,@total);

Query OK, 0 rows affected (0.05 sec)

mysql> SELECT @total;

+--------+

| @total |

+--------+

| 149.87 |

+--------+

1 row in set (0.00 sec)

調用并顯示如下結果2如下

mysql> CALL ordertotal(20005,1,@total);

Query OK, 0 rows affected, 1 warning (0.00sec)

mysql> SELECT @total;

+--------+

| @total |

+--------+

| 158.86 |

+--------+

1 row in set (0.00 sec)

23.5檢查存儲過程

為了顯示用來創建一個存儲過程的CREATE語句，使用SHOW CREATE PROCEDURE語句：

SHOW CREATE PROCEDURE ordertotal;

為了獲得包括何時，由誰創建等詳細信息的存儲過程列表，使用SHOW PROCEDURE STATUS,并且可用LIKE指定一個過濾模式，例如

SHOW PROCEDURE STATUS LIKE ‘ordertotal’;

24使用游標

有時，需要在檢索出來的行中前進或后退一行或多行。這就是使用游標的原因。游標是一個存儲在MySQL服務器上的數據庫查詢，它不是一條SELECT語句，而是被該語句檢索出來的結構集。在存儲了游標之后，應用程序可以根據需要滾動或瀏覽或更改其中的數據。

24.1使用游標

步驟：

1) 在使用有游標前，必須聲明它。這個過程實際上沒有檢索數據，它只是ing醫藥使用的SELECT語句

2) 一旦聲明后，必須打開游標供使用。這個過程用前面定義的SELECT語句把數據實際檢索出來。

3) 對于填有數據的游標，根據需要取出各行

4) 在結束游標使用時，必須關閉游標

24.2創建游標和使用游標數據

在一個游標被打開后，可以使用FETCH語句分別訪問它的每一行，FETCH指定檢索什么數據，檢索出來的數據存儲在什么地方。它還向前移動游標中的內部行指針，使下一條FETCH語句檢索下一行。

舉例如下：

CREATE PROCEDURE processorders()

BEGIN

--Declarelocal variables

DECLAREdone BOOLEAN DEFAULT 0;

DECLAREo INT;

DECLAREt DECIMAL(8,2);

--Declarethe cursor

DECLAREordernumbers CURSOR

FOR

SELECTorder_num FROM orders;

--Declarecontinue handler

DECLARECONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;

--Createa table to store the results

CREATETABLE IF NOT EXISTS ordertotals

(order_numINT, total DECIMAL(8,2));

--Openthe cursor

OPENordernumbers;

--Loopthrough all rows

REPEAT

--Getorder number

FETCH ordernumbers INTO o;

--Getthe total for this order

CALL ordertotal(o,1,t);

--Insertorder and total into ordertotals

INSERTINTO ordertotals(order_num,total)

VALUES(o,t);

--ENDOF LOOP

UNITLdone END REPEAT;

--Closethe cursor;

CLOSEordernumbers;

END;

在這個例子中，FETCH在REPEAT內，因此它反復執行直到done為真（由UNTIL done END REPEAT;規定）。為使它起作用，用一個DEFAULT 0定義變量done。然后用DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done =1;這條語句定義一個CONTINUEHANDLER,它是在條件出現時被執行的代碼。這里，它指出當SQLSTATE ‘02000’出現時，SET done=1。SQLSTATE ‘02000’是一個未找到條件，當REPEAT由于沒有更多的行工循環而不能繼續時，出現這個條件。這里還調用了另外一個存儲過程CALL ordertotal(o,1,t);這是在前一章創建的存儲過程，實現計算每個訂單帶稅額合計。此存儲過程不返回數據，但它能夠創建和填充另一個表。

可以用以下語句執行存儲過程和查看存儲結果：

mysql> CALL processorders();

mysql> SELECT * FROM ordertotals;

+-----------+---------+

| order_num | total |

+-----------+---------+

| 20005 | 158.86 |

| 20006 | 58.30 |

| 20007 | 1060.00 |

| 20008 | 132.50 |

| 20009 | 40.78 |

+-----------+---------+

6 rows in set (0.00 sec)

這樣我們就得到了存儲過程，游標，逐行處理以及存儲過程調用其他存儲工程的一個完整的工作樣例。

25.使用觸發器

25.1觸發器

觸發器是MySQL響應一下任意語句而自動執行的一條MySQL語句（或位于BEGIN和END語句之間的一組語句）：

DELETE

INSERT

UPDATE

25.2創建觸發器

創建觸發器時，需要給出4條信息：

1) 唯一的觸發器名；

2) 觸發器關聯的表

3) 觸發器應該響應的活動（DELETE、INSERT或UPDATE）

4) 觸發器何時執行(處理之前或之后)

只有表可以支持觸發器，視圖不支持（臨時表也不支持）。

CREATE TRIGGER newproduct AFTER INSERT ONproducts FOR EACH ROW SELECT 'd';

這里用CREATE TRIGGER創建一個名為newproduct的新觸發器。觸發器可在一個操作發生之前或之后執行，這里給出了AFTER INSERT，所以觸發器將在INSERT語句成功后執行。這個觸發器還制定了FOR EACH ROW，因此代碼對每個插入行執行。在這個例子中，文本Product added將對每個插入的行顯示一次。PS.在我的MySQL版本中，不能成功實現，會提示不能返回結果集Not allowed to return a result set from a trigger錯誤。不知道是MySQL版本問題還是MySQL必知必會中的錯誤。

觸發器按每個表每個時間每次的定義，每個表每個時間每次只能定義一個觸發器，因此一個表最多支持6個觸發器（INSERT,UPDATE和DELETE之前或之后）。

25.3刪除觸發器：

DROP TRIGGER newproduct;

25.4使用觸發器

25.4.1 INSERT觸發器

1)在INSERT觸發器代碼內，可引用一個名為NEW的虛擬表，訪問被插入的行；

2)在BEFORE INSERT觸發器中，NEW中的值可以被更新（允許更改被插入的值）

3）對于AUTO_INCREMENT列，NEW在INSERRT執行之前包含0，在執行之后包含新的自動生成的值。

CREATE TRIGGER neworder AFTER INSERT ONorders FOR EACH ROW SELECT NEW.ORDER_num;

25.4.2DELETE觸發器

1）在DELEYE觸發器代碼內，你可以引用一個名為OLD的虛擬表，訪問被刪除的行

2）OLD中的值全部都是只讀的，不能更新。

CREATE TRIGGER deleteorder BEFORE DELETE ONorders

FOR EACH ROW

BEGIN

INSERT INTO

archive_orderss(order_num,order_date,cust_id)

VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);

END;

25.5 UPDATE觸發器

1)可以用OLD的虛擬表訪問以前的值，也可以用名為NEW的虛擬表訪問新更新的值

2）在BEFFORE UPDATE觸發器中，NEW中的值可能也被更新

3）OLD中的值全部都是只讀的，不能更新

26.管理事務處理

事務處理可以用來維護數據庫的完整性，它保證成批的MySQL操作要么完全執行，要么完全不執行。

術語：

事務：指一組SQL語句

回退：指撤銷指定SQL語句的過程

提交：指將未存儲的SQL語句結果寫入數據庫表

保留點：值事務處理中設置的臨時占位符，你可以對它發布回退。

26.1控制事務處理

管理事務處理的關鍵在于將SQL語句組分解為邏輯塊，并明確規定數據何時應該回退，何時不應該回退。

START TRANSACTION//標記是事務開始

26.2使用ROLLBACK

ROLLBACK用來回退MySQL語句

SELECT * FROM ordertotals;

START TRANSACTION;

DELETE FROM ordertotals;

SELECT * FROM ordertotals;

ROLLBACK;

SELECT * FROM ordertotals;

依次執行上面語句，可以看到刪除的表格內容又被回退了。

ROLLBACK只能在一個事務處理內使用（在執行一條STARTTRANSACTION命令之后）。

26.3使用COMMIT

一般的MySQL都是直接對數據庫表執行和編寫的，這是隱含提交。但在事務處理塊中，提交不會隱含地進行。為進行明確的提交，使用COMMIT語句，如下所示：

START TRANACTION;

DELETE FROM orderitems WHERE order_num =20010;

DELETE FROM orders WHERE order_num = 20010;

COMMIT;

如果第一條起作用，第二條失敗，則事務不處理，也就是兩條DELETE不會被提交。

26.4使用保留點

簡單的ROLLBACK和COMMIT語句就可以寫入或撤銷整個事務處理。但是，只是對簡單的事務處理才嫩這樣做，更復雜的事務處理可能需要部分提交或回退。

為了支持回退部分事務處理，必須使用保留點，可如下使用SAVEPOINR語句：

SAVEPOINT delete1;

每個保留點都表示它的唯一名字，以便回退時，MySQL知道要回退到何處。

ROLLBACK TO delete1;

26.5 更改默認的提交行為

為指示MySQL不自動提交更改，需要使用以下語句:

SET autocommit = 0;

27.全球化和本地化

CREATETABLE mytable

(

Column1 INT,

Column2 VARCHAR(0)

)DEAULT CHARACTER SET hebrew CLLATEhebrew_general_ci;

這條語句創建一個表，并且制定一個字符集和一個校對順序

SHOW CHARACTER SET;//可以查看所支持的字符集完整列表

SHOW COLLATION; //查看所支持的校對完整列表

28．安全管理

管理用戶

USE mysql;

SELECT user FROM user;

Mysql數據庫有一個名為user的表，它包含所有用戶賬號。User表有一個user列。

28.1創建用戶賬戶

為創建新用戶賬戶，使用CREATE USER語句

CREATE USER ben IDENTIFIED BY ‘P@$$w0rd’;

創建一個用戶名為ben的用戶，口令為P@$$w0rd。

RENAME USER ben TO bforta;

28.2刪除用戶賬號

DROP USER bforta;

28.3設置訪問權限

mysql> SHOW GRANTS FOR bforta;

+-------------------------------------------------------------------------------

------------------------+

| Grants for bforta@%

|

+-------------------------------------------------------------------------------

------------------------+

| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6

9274F8C3D15B788433A9EB' |

+-------------------------------------------------------------------------------

------------------------+

1 row in set (0.00 sec)

輸出結果顯示用戶bforta有一個權限USAGE ON *.*。USAGE表示沒有權限，所以USAGE ON *.*表示在任意數據庫和人意表上對任何東西都沒有權限。

使用GRANT設置權限，需要給出以下信息：
1）要授予的權限

2）被授予訪問權限的數據庫或表

3） 用戶名

GRANT SELECT ON crashcourse.* TO bforta;

賦予bforta在crashcourse.*擁有SELECT權限。

在此顯示權限如下：

SHOW GRANTS FOR bforta;

+-------------------------------------------------------------------------------

------------------------+

| Grants for bforta@%

|

+-------------------------------------------------------------------------------

------------------------+

| GRANT USAGE ON *.* TO 'bforta'@'%'IDENTIFIED BY PASSWORD '*A6210E6C376AADB5A6

9274F8C3D15B788433A9EB' |

| GRANT SELECT ON `crashcourse`.* TO'bforta'@'%'

|

+-------------------------------------------------------------------------------

------------------------+

2 rows in set (0.00 sec)

可以用REVOKE取消權限。

REVOKE SELECT ON crashcourse.* FROM bforta;

28.4更改口令

更新用戶口令

SET PASSWORD FOR bforta = Password(‘n3wp@$$w0rd’);

設置自己的口令

SET PASSWOR = Password(‘n3w p@$$w0rd’);

第29章數據庫維護

ANALYZE TABLE：用來檢查表鍵是否正確

CHECK TABLE:用來針對許多問題對表進行檢查

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com