MySQL的基本操作可以包括兩個方面:MySQL常用語句如高頻率使用的增刪改查(CRUD)語句和MySQL高階功能,如儲存過程,觸發器,事務處理等。而這兩個方面又可以細分如下:
-
MySQL常用語句
- 表(或者資料庫)的CRUD
- 表資料的CRUD,其中表資料查詢使用最多,也更復雜。查詢可以按照單表還是多表可以分為:單表SELECT查詢和多表的聯結查詢(INNER JOIN, LEFT JOIN, RIGHT JOIN和FULL JOIN)以及組合查詢UNION和UNION ALL
- SQL語句中各個關鍵字的執行順序
-
MySQL的高階功能
- 儲存過程
- 事務處理
- 觸發器
1. 表(或資料庫)操作語句
1.1. 查詢表(或資料庫)
- 獲取所有可用的資料庫:
SHOW DATABASES
; - 選擇資料庫:
USE customers
; - 用於顯示資料庫伺服器的狀態資訊:
SHOW STATUS
; - 用來顯示授權使用者的安全許可權:
SHOW GRANTS
; - 用來顯示資料庫伺服器或警告資訊:
SHOW ERRORS
或者SHOW WARNINGS
; - 用於顯示建立資料庫時的建立語句:
SHOW CREATE DATABASE customers
; - 用於顯示建立表時的建立語句:
SHOW CREATE TABLE customers
; - 獲取當前所選的資料庫中所有可用的表:
SHOW TABLES
; - 獲取表中所有列的資訊:
SHOW COLUMNS FROM tableName
;同時DESCRIBE語句有相同的效果:DESCRIBE tableName
;
1.2. 新建表(或)資料庫
-
新建資料庫:
CREATE DATABASE customers
; -
建立表可以使用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; 複製程式碼
有這樣一些細節:
- 允許NULL值,則說明在插入行資料時允許不給出該列的值,而NOT NULL則表示在插入或者更新該列資料,必須明確給出該列的值;
- DEFAULT表示該列的預設值,在插入行資料時,若沒有給出該列的值就會使用其指定的預設值;
- PRIMARY KEY用於指定主鍵,主鍵可以指定一列資料,而可以由多列資料組合構成,如
PRIMARY KEY(cust_id,cust_name)
; - ENGINE用於指定引擎型別。常見的引擎型別有這些:(1)InnoDB是一個支援可靠的事務處理的引擎,但是不支援全文字搜尋;(2)MyISAM是一個效能極高的引擎,它支援全文字搜尋,但是不支援事務處理;(3)MEMORY在功能上等同於MyISAM,但由於資料儲存在記憶體中,速度很快(特別適合於臨時表);
-
在建立表的時候可以使用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 刪除表(或資料庫)
- 刪除資料庫:
DROP DATABASE customers
; - 刪除表,使用DROP TABLE子句:
DROP TABLE customers
。
1.4 更新表
-
更新表結構資訊可以使用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) 複製程式碼
-
重新命名錶,使用RENAME子句。
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors
;更改多個表名,之間用逗號間隔
2 表資料操作語句
2.1 查詢表資料
基本查詢語句
- 根據過濾條件查詢表中的單列或者多列或者全部列的資訊SELECT FROM WEHERE:
SELECT cust_id,cust_name FROM customers WHERE cust_id=10086
;其中過濾條件操作符有:=,<>,!=,<,<=,>,>=,BETWEEN AND,IS NULL; - 為查詢出的某一列資訊去重DISTINCT:
SELECT DISTINCT cust_name FROM customers
; - 限制單列查詢結果的行數:
SELECT cust_name FROM customers LIMIT 5
;LIMIT後跟一個數值,表示從第0行開始取,共取5行資料;如果LIMIT 5,5表示從第5行(資料庫中實際第6行記錄)開始取,共取5行資料。注意:資料是從第0行開始計數的; - ORDER BY子句取一個或者多個列,據此對輸出進行排序:
SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name
; - IN操作符用來指定條件範圍,範圍中的每個條件都可以進行匹配:
SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)
。另外,NOT操作符可以和IN操作符配合使用,用於表示檢索出不符合條件的所有資料; - LIKE操作符用來表明模糊查詢,與之配合使用的萬用字元有**%**,%表示任何字元出現任何次數;
_
,_
表示只能匹配一個字元:SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'
; - 使用分組查詢並可以滿足一定的分組過濾條件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
- WHERE和HAVING的比較。WHERE是行級過濾,而HAVING是組級過濾。被WHERE過濾掉的資料不會出現在分組中。WHERE中萬用字元以及多個WHERE子句的連線同樣適用於HAVING子句;
- GROUP BY的使用注意事項:
(1)GROUP BY子句中可以巢狀分組(即通過多個列進行分組
GROUP BY cust_id, cust_name
),但是進行資料彙總時,是在最後規定的分組上進行;(2)GROUP BY子句中列出的每個列都必須是檢索列或者是有效的表示式。(3)如果有NULL值,將值NULL作為一個分組進行返回,如果有多行NULL值,它們將分為一組 - 巢狀其他查詢中的查詢,稱之為子查詢。執行過程由裡向外,裡層查詢結果作為外層查詢的條件:
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')
。當然,多表的查詢可以是用聯結查詢。
聯結查詢
- 內聯結用又稱之為內部聯結,是基於兩個表 之間的的相等測試。如果不加過濾條件,會造成“笛卡爾積”。
SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id
;同樣可以使用WHERE進行多表聯結查詢,但是更推薦使用INNER JOIN等聯結方式; - 外部聯結包括左外聯結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會將左右兩個表的資料全部返回; - 聯結查詢與聚集函式一起使用。如查詢每個客戶的訂單數:
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
;
組合查詢
-
多個查詢(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)
; -
UNION返回的是去重後的結果,如果不需要去重則可以使用UNION ALL;
-
可以多組合查詢使用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
使用函式對資料進行處理
-
拼接列名:
SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName
; -
執行算術表示式計算:
SELECT prodId, quantity,price, quantity
*price AS expandedPrice FROM orderItems
; -
文字處理函式如Upper(),LTrim(),RTrim()等函式。比如使用Upper函式將文字轉換成大寫:
SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName
; -
時間和日期處理函式,如Date(),Day()等。
SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01'
; -
數值處理函式,如Abs(),Cos()等;
-
常用的聚集函式。如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 插入表資料
- 向表中插入行資料可以使用INSERT INTO子句,更安全的方式是指定列名。
INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com')
;在INSERT INTO子句中能夠省略列名的前提條件是:該列可以允許定義為NULL值或者在定義該列時給出去了預設值; - 如果插入多行資料可以將多組值用逗號進行分隔即可。
INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com')
; - 將查詢出來的資料插入表中,可以使用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 更新表資料
- 如果要更新表資料的話,使用UPDATE子句:
UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001
; - 注意:如果不加WHERE條件指定到某一行的話,會更新表中某一列全部的資料。
2.4 刪除表資料
- 如果從表中刪除資料的話,可以使用DELETE子句。
DELETE FROM customers WHERE cust_id = 10086
;刪除的資料必定是表中行資料,而不是某一列。因此,與UPDATE子句相比,DELETE子句並不需要指定是哪一列,而僅僅只需要指定具體的表名即可; - 注意:如果不新增WHERE指定條件的話,會將整個表中所有行資料全部刪除。另外,DELETE只是刪除表中的資料,而不會刪除表結構資訊;
- 如果想刪除表中全部的資料,可以使用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>
複製程式碼
- FROM:對FROM左邊的表和右邊的表計算笛卡爾積,產生虛表VT1;
- ON:對虛擬表VT1進行ON篩選,只有那些符合<join_condition>條件的行才會被記錄在虛擬表VT2中;
- JOIN:如果是OUT JOIN,那麼將保留表中(如左表或者右表)未匹配的行作為外部行新增到虛擬表VT2中,從而產生虛擬表VT3;
- WHERE:對虛擬表VT3進行WHERE條件過濾,只有符合<where_condition>的記錄才會被放入到虛擬表VT4;
- GROUP BY:根據GROUP BY子句中的列,對虛擬表VT4進行分組操作,產生虛擬表VT5;
- CUBE|ROLLUP:對虛擬表VT5進行CUBE或者ROLLUP操作,產生虛擬表VT6;
- HAVING:對虛擬表VT6進行HAVING條件過濾,只有符合<having_condition>的記錄才會被插入到虛擬表VT7中;
- SELECT:執行SELECT操作,選擇指定的列,插入到虛擬表VT8中;
- DISTINCT:對虛擬表VT8中的記錄進行去重,產生虛擬表VT9;
- ORDER BY:將虛擬表VT9中的記錄按照<order_by_list>進行排序操作,產生虛擬表VT10;
- LIMIT:取出指定行的記錄,產生虛擬表VT11,並將結果返回。
4. 索引
MySQL索引的建立對於MySQL的高效執行是很重要的,索引可以大大提高MySQL的檢索速度。索引分單列索引和組合索引。單列索引,即一個索引只包含單個列,而組合索引,即一個索引包含多個列。
4.1 建立索引
建立索引有兩種方式,一種是直接利用CREATE INDEX進行建立,另外一種則是通過修改表結構來進行新增,則是利用ALTER TABLE語句。
-
使用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)); 複製程式碼
-
使用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. 儲存過程
- 什麼是儲存過程?儲存過程簡單來說,就是為了複用性或者實現複雜的業務功能,而儲存的一條或多條MySQL語句的集合,可將其視為批檔案;
- 為什麼使用儲存過程?(1)通過把處理封裝在容易使用的單元中,簡化複雜的操作;(2)由於不要求反覆建立一系列處理步驟,這保證了資料的完整性,如果所有的開發人員和應用程式都使用同一儲存過程,則所使用的程式碼都是相同的;(3)簡化對變動的管理。如果表名、列名或業務邏輯有變化,只需要更改儲存過程的程式碼,使用它的開發人員甚至不需要知道這些變化,也就是具備了安全性;(4)提高了效能,因為使用儲存過程比單獨使用SQL語句要快;(5)儲存過程可用來編寫功能更靈活的程式碼。因此,儲存過程的具備三個特性:簡單可複用、安全以及高效能;
- 儲存過程的缺點?(1)儲存過程編寫比基本的SQL語句更加複雜,需要更高的技能;(2)可能沒有建立儲存過程的許可權,資料庫管理員可能會限制建立儲存過程的許可權,允許使用者使用儲存過程,而不允許使用者自由建立儲存過程;
建立儲存過程
-
建立儲存過程。如需要統計使用者訂單總金額,如果該使用者需要交稅的話,訂單總金額則需要再加上稅費
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。同時,分隔符//成對出現後,恢復到預設的";"作為分隔符;
執行儲存過程
-
使用CALL子句執行儲存過程,CALL子句接受儲存過程的名稱以及需要傳遞的引數。
CALL ordertotal(1,TRUE,@total); SELECT @total; 複製程式碼
如果儲存過程中定義了OUT型別的輸入引數,那麼在執行儲存過程時需要傳入變數,如這裡@total,並且變數都是用@開始的。如果儲存過程中沒有引數的話,就用空圓括號表示即可,CALL ordertotal()
;
刪除儲存過程
- 刪除儲存過程,可以使用DROP PROCEDURE子句。如
DROP PROCEDURE ordertotal
;
查詢儲存過程
- 顯示建立一個儲存過程的語句,可以使用SHOW CREATE PROCEDURE。如
SHOW CREATE PROCEDURE ordertotal
; - 查詢所有儲存過程的狀態,如果在定義儲存過程中使用COMMENT新增註釋,可以檢視。同時可以LIKE進行過濾結果。如
SHOW PROCEDURE STATUS LIKE '%order%'
;
6. 事務處理
-
什麼是事務?
事務處理是用來維護資料庫的完整性,它保證成批的MySQL操作要麼完全執行,要麼完全不執行。事務處理是一種機制,用來管理必須成批執行的MySQL操作,它們要麼時作為整體執行或者完全不執行。
-
關鍵概念:
- 事務:是指一組SQL語句;
- 回退:是指撤銷指定的SQL語句的過程;
- 提交:指將未儲存的SQL語句的結果寫入資料庫表中;
- 保留點:指事務處理中設定的臨時佔位符,可以對它釋出回退;
-
如何建立執行事務?
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語句執行的結果仍然有效。
有這樣一些細節:
- STAET TRANSACTION用來表示下面的SQL語句集為一段事務;
- SAFEPOINT 用於指定保留點insertinto;
- ROLLBACK TO表示從指定保留點開始回退,也就是說保留點之前的SQL語句執行結果依然有效。如果僅僅使用ROLLBACK進行回退的話就表示從STAET TRANSACTION之後所有的SQL語句執行效果都會撤銷;
-
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. 觸發器
-
什麼是觸發器?
當某條SQL語句發生時,自動執行某些其他的SQL語句的時候就需要使用到觸發器。觸發器只能響應:DELETE,INSERT,UPDATE這三個特定操作。
-
建立觸發器?
建立觸發器時需要給出最重要的四條資訊: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; 複製程式碼
有這樣一些細節:
- 使用CREATE TRIGGER來建立觸發器;
- AFTER INSERT表明在插入行資料之後,觸發器才會執行特徵操作;
- FOR EACH ROW 表示對插入的每一行資料,觸發器都起作用;
- 針對INSERT觸發器,可以使用虛擬表NEW,來使用剛插入的行資料。比如例子中,
SELECT NEW.cust_id INTO @newinsertid
表示將新插入的行資料的id賦值給變數@newinsertid;
DELETE觸發器
DELETE觸發器在DELETE語句執行之前或者之後,需要知道以下兩點:
- 在DELETE觸發器程式碼內,可以引用一個名為OLD的虛擬表,來訪問被刪除的行;
- 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語句執行之前或者之後執行,需要知道一下幾點:
- 在BEFORE UPDATE觸發器中可以使用NEW和OLD來訪問資料,而在AFTER UPDATE觸發器中使用NEW來訪問資料會報錯,只能使用OLD來訪問資料;
- 在BEFORE UPDATE觸發器中,NEW中的值可以被改變,即允許更改將用於UPDATE的資料;
- 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'。有這樣一些細節:
- NEW虛擬表中的資料可以更改,如這裡採用
SET NEW.cust_name = 'reset_name';
,將待更新的cust_name由“happy”變成了“reset_name”
; - 在BEFORE UPDATE觸發器中可以使用NEW和OLD來訪問資料,而在AFTER UPDATE觸發器中使用NEW來訪問資料會報錯;
-
刪除觸發器?
刪除觸發器,可以使用 DROP TRIGGER語句,比如
DROP TRIGGER insertcustomers;
。觸發器不能更新或者覆蓋,如果要修改觸發器,必須刪除這個觸發器。