MySQL命令，一篇文章替你全部搞定

你聽___發表於2018-04-29
MySql

原創文章&經驗總結&從校招到A廠一路陽光一路滄桑

詳情請戳www.coderccc.com

MySQL命令，一篇文章替你全部搞定

MySQL的基本操作可以包括兩個方面：MySQL常用語句如高頻率使用的增刪改查（CRUD）語句和MySQL高階功能，如儲存過程，觸發器，事務處理等。而這兩個方面又可以細分如下：

MySQL基本操作總結.png

  • MySQL常用語句

    1. 表（或者資料庫）的CRUD
    2. 表資料的CRUD，其中表資料查詢使用最多，也更復雜。查詢可以按照單表還是多表可以分為：單表SELECT查詢和多表的聯結查詢（INNER JOIN, LEFT JOIN, RIGHT JOIN和FULL JOIN）以及組合查詢UNION和UNION ALL
    3. SQL語句中各個關鍵字的執行順序

  • MySQL的高階功能

    1. 儲存過程
    2. 事務處理
    3. 觸發器

1. 表(或資料庫)操作語句

1.1. 查詢表（或資料庫）

  1. 獲取所有可用的資料庫：SHOW DATABASES；
  2. 選擇資料庫：USE customers；
  3. 用於顯示資料庫伺服器的狀態資訊：SHOW STATUS；
  4. 用來顯示授權使用者的安全許可權：SHOW GRANTS；
  5. 用來顯示資料庫伺服器或警告資訊：SHOW ERRORS 或者 SHOW WARNINGS；
  6. 用於顯示建立資料庫時的建立語句：SHOW CREATE DATABASE customers；
  7. 用於顯示建立表時的建立語句：SHOW CREATE TABLE customers；
  8. 獲取當前所選的資料庫中所有可用的表：SHOW TABLES；
  9. 獲取表中所有列的資訊：SHOW COLUMNS FROM tableName；同時DESCRIBE語句有相同的效果：DESCRIBE tableName；

1.2. 新建表（或）資料庫

  1. 新建資料庫：CREATE DATABASE customers;

  2. 建立表可以使用CREATE TABLE語句:

     CREATE TABLE customers(
   cust_id INT NOT NULL AUTO_INCREMENT,
   cust_name CHAR(50) NOT NULL,
   cust_age INT NULL DEFAULT 18,
   PRIMARY KEY(cust_id)
 )ENGINE=INNODB;
複製程式碼

    有這樣一些細節：

    1. 允許NULL值，則說明在插入行資料時允許不給出該列的值，而NOT NULL則表示在插入或者更新該列資料，必須明確給出該列的值；
    2. DEFAULT表示該列的預設值，在插入行資料時，若沒有給出該列的值就會使用其指定的預設值；
    3. PRIMARY KEY用於指定主鍵，主鍵可以指定一列資料，而可以由多列資料組合構成，如PRIMARY KEY(cust_id,cust_name)；
    4. ENGINE用於指定引擎型別。常見的引擎型別有這些：（1）InnoDB是一個支援可靠的事務處理的引擎，但是不支援全文字搜尋；（2）MyISAM是一個效能極高的引擎，它支援全文字搜尋，但是不支援事務處理；（3）MEMORY在功能上等同於MyISAM，但由於資料儲存在記憶體中，速度很快（特別適合於臨時表）；

  3. 在建立表的時候可以使用FOREIGN KEY來建立外來鍵，即一個表中的FOREIGN KEY指向另一個表中PRIMARY KEY。外來鍵FOREIGN KEY用於約束破壞表的聯結動作，保證兩個表的資料完整性。同時也能防止非法資料插入外來鍵列，因為該列值必須指向另一個表的主鍵。例項為：

     CREATE TABLE Orders
 (
 Id_O int NOT NULL,
 OrderNo int NOT NULL,
 Id_P int,
 PRIMARY KEY (Id_O),
 FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
 )
複製程式碼

1.3 刪除表（或資料庫）

  1. 刪除資料庫：DROP DATABASE customers；
  2. 刪除表，使用DROP TABLE子句：DROP TABLE customers。

1.4 更新表

  1. 更新表結構資訊可以使用ALTER TABLE子句，如為表增加一列：ALTER TABLE vendors ADD vend_name CHAR(20)；另外經常用於定義外來鍵，如：

     ALTER TABLE customers 
 ADD CONSTRAINT fk_custormer_orders
 FOREIGN KEY(cust_id) REFERENCES orders (order_cust)
複製程式碼

  2. 重新命名錶，使用RENAME子句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors；更改多個表名，之間用逗號間隔

2 表資料操作語句

2.1 查詢表資料

基本查詢語句

  1. 根據過濾條件查詢表中的單列或者多列或者全部列的資訊SELECT FROM WEHERE：SELECT cust_id,cust_name FROM customers WHERE cust_id=10086；其中過濾條件操作符有：=，<>,!=,<,<=,>,>=,BETWEEN AND,IS NULL；
  2. 為查詢出的某一列資訊去重DISTINCT：SELECT DISTINCT cust_name FROM customers；
  3. 限制單列查詢結果的行數：SELECT cust_name FROM customers LIMIT 5；LIMIT後跟一個數值，表示從第0行開始取，共取5行資料；如果LIMIT 5，5表示從第5行（資料庫中實際第6行記錄）開始取，共取5行資料。注意：資料是從第0行開始計數的；
  4. ORDER BY子句取一個或者多個列，據此對輸出進行排序：SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name;
  5. IN操作符用來指定條件範圍，範圍中的每個條件都可以進行匹配：SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)。另外，NOT操作符可以和IN操作符配合使用，用於表示檢索出不符合條件的所有資料；
  6. LIKE操作符用來表明模糊查詢，與之配合使用的萬用字元有**%**，%表示任何字元出現任何次數；_，_表示只能匹配一個字元：SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'；
  7. 使用分組查詢並可以滿足一定的分組過濾條件GROUP BY HAVING。如檢索總計訂單金額大於等於50的訂單號和訂單總金額，並按總金額進行排序：SELECT order_num,SUM(quantity*item_price) AS order_total FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY order_total
  8. WHERE和HAVING的比較。WHERE是行級過濾，而HAVING是組級過濾。被WHERE過濾掉的資料不會出現在分組中。WHERE中萬用字元以及多個WHERE子句的連線同樣適用於HAVING子句；
  9. GROUP BY的使用注意事項： （1）GROUP BY子句中可以巢狀分組（即通過多個列進行分組GROUP BY cust_id, cust_name），但是進行資料彙總時，是在最後規定的分組上進行；（2）GROUP BY子句中列出的每個列都必須是檢索列或者是有效的表示式。（3）如果有NULL值，將值NULL作為一個分組進行返回，如果有多行NULL值，它們將分為一組
  10. 巢狀其他查詢中的查詢，稱之為子查詢。執行過程由裡向外，裡層查詢結果作為外層查詢的條件：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')。當然，多表的查詢可以是用聯結查詢。

聯結查詢

  1. 內聯結用又稱之為內部聯結，是基於兩個表 之間的的相等測試。如果不加過濾條件，會造成“笛卡爾積”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id；同樣可以使用WHERE進行多表聯結查詢，但是更推薦使用INNER JOIN等聯結方式；
  2. 外部聯結包括左外聯結LEFT JOIN和右外聯結RIGHT JOIN和全連線FULL JOIN。例如查詢每個客戶的訂單數：SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id；LEFT JOIN 會全部返回左表資料，RIGHT JOIN會全部返回右表資料，FULL JOIN會將左右兩個表的資料全部返回；
  3. 聯結查詢與聚集函式一起使用。如查詢每個客戶的訂單數：SELECT customers.cust_name,customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id=orders.cust_id GROUP BY customers.cust_id；

組合查詢

  1. 多個查詢（SELECT）可以使用UNION將多個查詢結果進行合併成一個結果集返回，UNION必須包含兩個及兩個以上的SELECT查詢，並且每個傳必須包含相同的列、表示式或聚集函式，資料型別不必完全相同，MySQL會進行隱式的型別轉換。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

  2. UNION返回的是去重後的結果，如果不需要去重則可以使用UNION ALL；

  3. 可以多組合查詢使用ORDER BY進行排序，但是是針對的最終的結果集進行排序，而不是其中單個SELECT查詢進行排序，因此對於組合查詢來說ORDER BY子句只有一個。SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price>5 UINON SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id

使用函式對資料進行處理

  1. 拼接列名：SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName；

  2. 執行算術表示式計算：SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems；

  3. 文字處理函式如Upper(),LTrim(),RTrim()等函式。比如使用Upper函式將文字轉換成大寫：SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName；

  4. 時間和日期處理函式，如Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01'；

  5. 數值處理函式，如Abs(),Cos()等；

  6. 常用的聚集函式。如AVG(),COUNT(),MAX(),MIN()以及SUM()。SELECT COUNT(*) AS numbers, MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM products；

2.2 插入表資料

  1. 向表中插入行資料可以使用INSERT INTO子句，更安全的方式是指定列名。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com')；在INSERT INTO子句中能夠省略列名的前提條件是：該列可以允許定義為NULL值或者在定義該列時給出去了預設值；
  2. 如果插入多行資料可以將多組值用逗號進行分隔即可。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com')；
  3. 將查詢出來的資料插入表中，可以使用INSERT SELECT語句。INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5；其中SELECT中可以帶WHERE過濾條件；INSERT SELECT通常被用於複製表資料

2.3 更新表資料

  1. 如果要更新表資料的話，使用UPDATE子句：UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001；
  2. 注意：如果不加WHERE條件指定到某一行的話，會更新表中某一列全部的資料。

2.4 刪除表資料

  1. 如果從表中刪除資料的話，可以使用DELETE子句。DELETE FROM customers WHERE cust_id = 10086；刪除的資料必定是表中行資料，而不是某一列。因此，與UPDATE子句相比，DELETE子句並不需要指定是哪一列，而僅僅只需要指定具體的表名即可；
  2. 注意：如果不新增WHERE指定條件的話，會將整個表中所有行資料全部刪除。另外，DELETE只是刪除表中的資料，而不會刪除表結構資訊；
  3. 如果想刪除表中全部的資料，可以使用TRUNCATE，比DELETE刪除效率更高；

3. SQL中關鍵字執行順序

在SQL語句中每個關鍵字都會按照順序往下執行，而每一步操作，會生成一個虛擬表，最後產生的虛擬表會作為執行的最終結果返回。下面的是常用的關鍵字的執行順序：

(8)SELECT (9)DISTINCT<select_list>
(1)FROM <left_table>
(3)<join_type> JOIN <right_table>
(2)            ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY<group_by_list>
(6)WITH{CUBE|ROLLUP}
(7)HAVING<having_condition>
(10)ORDER BY<order_by_list>
(11)LIMIT<limit_number>
複製程式碼
  1. FROM：對FROM左邊的表和右邊的表計算笛卡爾積，產生虛表VT1；
  2. ON：對虛擬表VT1進行ON篩選，只有那些符合<join_condition>條件的行才會被記錄在虛擬表VT2中；
  3. JOIN：如果是OUT JOIN，那麼將保留表中（如左表或者右表）未匹配的行作為外部行新增到虛擬表VT2中，從而產生虛擬表VT3；
  4. WHERE:對虛擬表VT3進行WHERE條件過濾，只有符合<where_condition>的記錄才會被放入到虛擬表VT4；
  5. GROUP BY：根據GROUP BY子句中的列，對虛擬表VT4進行分組操作，產生虛擬表VT5；
  6. CUBE|ROLLUP：對虛擬表VT5進行CUBE或者ROLLUP操作，產生虛擬表VT6；
  7. HAVING：對虛擬表VT6進行HAVING條件過濾，只有符合<having_condition>的記錄才會被插入到虛擬表VT7中；
  8. SELECT：執行SELECT操作，選擇指定的列，插入到虛擬表VT8中；
  9. DISTINCT：對虛擬表VT8中的記錄進行去重，產生虛擬表VT9；
  10. ORDER BY：將虛擬表VT9中的記錄按照<order_by_list>進行排序操作，產生虛擬表VT10；
  11. LIMIT：取出指定行的記錄，產生虛擬表VT11，並將結果返回。

4. 索引

MySQL索引的建立對於MySQL的高效執行是很重要的，索引可以大大提高MySQL的檢索速度。索引分單列索引和組合索引。單列索引，即一個索引只包含單個列，而組合索引，即一個索引包含多個列。

4.1 建立索引

建立索引有兩種方式，一種是直接利用CREATE INDEX進行建立，另外一種則是通過修改表結構來進行新增，則是利用ALTER TABLE語句。

  1. 使用CREATE INDEX

    語法為：

      CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [USING index_type]
  ON table_name (index_col_name,...)
複製程式碼

    其中對應的語法變數資訊如下：

    [UNIQUE|FULLTEXT|SPATIAL]

    其中括號中的這三個關鍵字表示建立的索引型別，它們分別表示唯一索引、全文索引、空間索引三種不同的索引型別。如果我們不指定任何關鍵字，則預設為普通索引。

    index_name

    index_name表示索引的名稱，由使用者自行定義，以便於以後對該索引進行修改等管理操作。

    index_type

    index_type表示索引的具體實現方式，在MySQL中，有兩種不同形式的索引——BTREE索引和HASH索引。在儲存引擎為MyISAM和InnoDB的表中只能使用BTREE，其預設值就是BTREE；在儲存引擎為MEMORY或者HEAP的表中可以使用HASH和BTREE兩種型別的索引，其預設值為HASH。

    index_colname

    index_col_name表示需要建立索引的欄位名稱，我們還可以針對多個欄位建立複合索引，只需要在多個欄位名稱之間以英文逗號隔開即可。此外，對於CHAR或VARCHAR型別的欄位，我們還可以只使用欄位內容前面的一部分來建立索引，只需要在對應的欄位名稱後面加上形如(length)的指令即可，表示只需要使用欄位內容前面的length個字元來建立索引。在這裡，我們以customers表的cust_name欄位(型別為VARCHAR(50))為例，使用cust_name欄位的6個字元字首來建立索引。

     CREATE INDEX idx_cust_name ON user (cust_name(6));
複製程式碼

  2. 使用ALTER TABLE

    語法為：

     ALTER TABLE table_name
 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
 (index_col_name,...) [USING index_type]
複製程式碼

4.2 刪除索引

刪除指定表中指定名稱的索引，語法為：

ALTER TABLE table_name
DROP INDEX index_name;
複製程式碼

例如刪除名稱為idx_cust_name的索引，其SQL語句為：

ALTER TABLE customers
DROP INDEX idx_cust_name;
複製程式碼

4.3 修改索引

在MySQL中並沒有提供修改索引的直接指令，一般情況下，我們需要先刪除掉原索引，再根據需要建立一個同名的索引，從而變相地實現修改索引操作。

--先刪除
ALTER TABLE user
DROP INDEX idx_user_username;
--再以修改後的內容建立同名索引
CREATE INDEX idx_cust_name ON customers (cust_name(8));
複製程式碼

4.4 檢視索引

在MySQL中，要檢視某個資料庫表中的索引也非常簡單，只需要使用以下兩個命令中的任意一種即可。

--如果檢視索引前，沒有使用user db_name等命令指定具體的資料庫，則必須加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果檢視索引前，沒有使用user db_name等命令指定具體的資料庫，則必須加上db_name.字首
SHOW INDEX FROM [db_name.]table_name
複製程式碼

5. 儲存過程

  1. 什麼是儲存過程？儲存過程簡單來說，就是為了複用性或者實現複雜的業務功能，而儲存的一條或多條MySQL語句的集合，可將其視為批檔案；
  2. 為什麼使用儲存過程？（1）通過把處理封裝在容易使用的單元中，簡化複雜的操作；（2）由於不要求反覆建立一系列處理步驟，這保證了資料的完整性，如果所有的開發人員和應用程式都使用同一儲存過程，則所使用的程式碼都是相同的；（3）簡化對變動的管理。如果表名、列名或業務邏輯有變化，只需要更改儲存過程的程式碼，使用它的開發人員甚至不需要知道這些變化，也就是具備了安全性；（4）提高了效能，因為使用儲存過程比單獨使用SQL語句要快；（5）儲存過程可用來編寫功能更靈活的程式碼。因此，儲存過程的具備三個特性:簡單可複用、安全以及高效能；
  3. 儲存過程的缺點？（1）儲存過程編寫比基本的SQL語句更加複雜，需要更高的技能；（2）可能沒有建立儲存過程的許可權，資料庫管理員可能會限制建立儲存過程的許可權，允許使用者使用儲存過程，而不允許使用者自由建立儲存過程；

建立儲存過程

  1. 建立儲存過程。如需要統計使用者訂單總金額，如果該使用者需要交稅的話，訂單總金額則需要再加上稅費

     DELIMITER //
 CREATE PROCEDURE ordertotal(
 IN custid INT,
 IN taxable BOOLEAN,
 OUT ototal DECIMAL(8,2)
 )COMMENT 'obtain total order price'
 
 BEGIN
 
 /*declare variable for total*/
 DECLARE total DECIMAL(8,2);
 DECLARE taxrate INT DEFAULT 6;
 
 
 /*get the order total*/
 
 SELECT SUM(item_price*item_quantity) INTO total
 FROM customers
 WHERE cust_id = custid;
 
 /*is this taxable?*/
 
 IF taxable THEN
 	SELECT total+(total/100*taxrate) INTO total;
 END IF;
 SELECT total INTO ototal;
 END //
複製程式碼

有這樣一些細節： 1. 使用CREATE PROCEDURE語句進行建立，（）圓括號中為儲存過程的引數，其中引數型別有：1. IN型別，表示傳遞給儲存過程；2. OUT 型別，表示儲存過程返回的結果，在呼叫儲存過程時需要傳入@開始的變數；3. INOUT型別，表示在儲存過程中可以傳入和傳出； 2. DECLARE用來宣告一個變數，如這裡的total，taxrate。注意MySQL中定義變數時都是變數名在前，資料型別在後。 3. 儲存過程具體邏輯寫在BEGIN END之間； 4. 將值賦給變數使用INTO關鍵字； 5. 由於儲存過程中每個SQL語句中用；作為分隔符，會和單個SQL造成衝突，因此可使用DELIMITER重新定義分類符，如該例子中定義//為分隔符，自然儲存過程結尾就用END //結尾，而不再是END。同時，分隔符//成對出現後，恢復到預設的";"作為分隔符；

執行儲存過程

  1. 使用CALL子句執行儲存過程，CALL子句接受儲存過程的名稱以及需要傳遞的引數。

     CALL ordertotal(1,TRUE,@total);
 SELECT @total;
複製程式碼

如果儲存過程中定義了OUT型別的輸入引數，那麼在執行儲存過程時需要傳入變數，如這裡@total，並且變數都是用@開始的。如果儲存過程中沒有引數的話，就用空圓括號表示即可，CALL ordertotal()；

刪除儲存過程

  1. 刪除儲存過程，可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal；

查詢儲存過程

  1. 顯示建立一個儲存過程的語句，可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal；
  2. 查詢所有儲存過程的狀態，如果在定義儲存過程中使用COMMENT新增註釋，可以檢視。同時可以LIKE進行過濾結果。如SHOW PROCEDURE STATUS LIKE '%order%';

6. 事務處理

  1. 什麼是事務？

    事務處理是用來維護資料庫的完整性，它保證成批的MySQL操作要麼完全執行，要麼完全不執行。事務處理是一種機制，用來管理必須成批執行的MySQL操作，它們要麼時作為整體執行或者完全不執行。

  2. 關鍵概念：

    1. 事務：是指一組SQL語句；
    2. 回退：是指撤銷指定的SQL語句的過程；
    3. 提交：指將未儲存的SQL語句的結果寫入資料庫表中；
    4. 保留點：指事務處理中設定的臨時佔位符，可以對它釋出回退；

  3. 如何建立執行事務？

     START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 SELECT * FROM customers;
 SAVEPOINT insertinto;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 ROLLBACK TO insertinto;
複製程式碼

    執行結果為：插入資料('1',5,18)有效，因為，只會從保留點SAFEPOINT之後開始回退，也就是說保留點SAFEPOINT之前的SQL語句執行的結果仍然有效。

    有這樣一些細節：

    1. STAET TRANSACTION用來表示下面的SQL語句集為一段事務；
    2. SAFEPOINT 用於指定保留點insertinto；
    3. ROLLBACK TO表示從指定保留點開始回退，也就是說保留點之前的SQL語句執行結果依然有效。如果僅僅使用ROLLBACK進行回退的話就表示從STAET TRANSACTION之後所有的SQL語句執行效果都會撤銷；

  4. MySQL提交（寫或儲存）操作是自動進行的，這稱之為隱含提交。但是在事務處理塊中，提交不會隱含進行，要使用COMMIT子句進行提交。如：

     START TRANSACTION;
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 COMMIT;
複製程式碼

採用COMMIT提交事務，如果兩條SQL語句都執行成功，才會將資料都寫入表中。

7. 觸發器

  1. 什麼是觸發器？

    當某條SQL語句發生時，自動執行某些其他的SQL語句的時候就需要使用到觸發器。觸發器只能響應：DELETE，INSERT,UPDATE這三個特定操作。

  2. 建立觸發器？

    建立觸發器時需要給出最重要的四條資訊：1.全域性唯一的觸發器名；2.觸發器關聯的表；3.觸發器在何時執行（操作執行之前或者之後）4.觸發器應該響應的活動（DELETE, INSERT或者UPDATE）；

    由於觸發器只能響應特定的三種型別的操作，因此可建立的觸發器也就三種型別:INSERT觸發器，DELETE觸發器以及UPDATE觸發器。

    INSERT觸發器

    在執行INSERT觸發器時，也這樣幾點需要注意：1.在INSERT觸發器程式碼內，可以引用一個名為NEW的虛擬表，可以用NEW來訪問剛插入的行資料；2.在BEFORE INSERT觸發器中，NEW中的值可以被更新；3.對於AUTO_INCREMENT列，NEW在INSERT執行之前包含0，在INSERT執行之後包含新的自定生成值。

    建立一個INSERT觸發器，每次插入一行資料，每次會返回當前插入的行資料的id。

     /*建立觸發器*/
 CREATE TRIGGER insertcustomers AFTER INSERT ON customers
 FOR EACH ROW SELECT NEW.cust_id INTO @newinsertid;
 
 /*執行觸發器*/
 INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);
 SELECT @newinsertid;
複製程式碼

    有這樣一些細節：

    1. 使用CREATE TRIGGER來建立觸發器；
    2. AFTER INSERT表明在插入行資料之後，觸發器才會執行特徵操作；
    3. FOR EACH ROW 表示對插入的每一行資料，觸發器都起作用；
    4. 針對INSERT觸發器，可以使用虛擬表NEW，來使用剛插入的行資料。比如例子中，SELECT NEW.cust_id INTO @newinsertid表示將新插入的行資料的id賦值給變數@newinsertid；

    DELETE觸發器

    DELETE觸發器在DELETE語句執行之前或者之後，需要知道以下兩點：

    1. 在DELETE觸發器程式碼內，可以引用一個名為OLD的虛擬表，來訪問被刪除的行；
    2. OLD表中的資料只能讀，不能被更新，而在INSERT觸發器中，就可以通過NEW來更新被插入的行資料；

    例如，針對customers表，當刪除一行資料時，返回被刪除資料的cust_id以及cust_name:

     /*建立DELETE觸發器*/
 
 DELIMITER //
 CREATE TRIGGER insertcustomers AFTER DELETE ON customers
 FOR EACH ROW 
 BEGIN 
   SELECT OLD.cust_name INTO @deletecustname;
   SELECT OLD.cust_id INTO @deletecustid;
 END //
 
 /*呼叫DELETE觸發器*/
 DELETE FROM customers WHERE cust_id = 3;
 SELECT @deletecustname;
 SELECT @deletecustid;
複製程式碼

    基本上與建立INSERT觸發器一樣，只不過在DELETE觸發器中只能使用OLD來訪問被刪除的行資料。

    UPDATE觸發器

    UPDATE觸發器在UPDATE語句執行之前或者之後執行，需要知道一下幾點：

    1. 在BEFORE UPDATE觸發器中可以使用NEW和OLD來訪問資料，而在AFTER UPDATE觸發器中使用NEW來訪問資料會報錯，只能使用OLD來訪問資料；
    2. 在BEFORE UPDATE觸發器中，NEW中的值可以被改變，即允許更改將用於UPDATE的資料；
    3. OLD中的行資料只能讀，不能被更新；

    一個UPDATE觸發器示例如下：

     /*建立UPDATE觸發器*/
 
 DELIMITER //
 CREATE TRIGGER insertcustomers BEFORE UPDATE ON customers
 FOR EACH ROW 
 BEGIN 
 
 SELECT NEW.cust_name INTO @beforeupdate;
 SET NEW.cust_name = 'reset_name';
 SELECT OLD.cust_name INTO @afterupdate;
 END //
 
 /*呼叫UPDATE觸發器*/
 UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5;
 SELECT @beforeupdate;
 SELECT @afterupdate;
複製程式碼

    輸出為@beforeupdate為‘happay’，而@afterupdate為'reset_name'。有這樣一些細節：

    1. NEW虛擬表中的資料可以更改，如這裡採用 SET NEW.cust_name = 'reset_name';，將待更新的cust_name由“happy”變成了“reset_name”；
    2. 在BEFORE UPDATE觸發器中可以使用NEW和OLD來訪問資料，而在AFTER UPDATE觸發器中使用NEW來訪問資料會報錯；

  3. 刪除觸發器？

    刪除觸發器，可以使用 DROP TRIGGER語句，比如DROP TRIGGER insertcustomers;。觸發器不能更新或者覆蓋，如果要修改觸發器，必須刪除這個觸發器。

相關文章