MySQL的基本操作可以包括兩個方面:MySQL常用語句如高頻率使用的增刪改查(CRUD)語句和MySQL高級功能,如存儲過程撇簿,觸發(fā)器擒抛,事務處理等。而這兩個方面又可以細分如下:
MySQL常用語句 表(或者數(shù)據(jù)庫)的CRUD 表數(shù)據(jù)的CRUD补疑,其中表數(shù)據(jù)查詢使用最多,也更復雜歹撒。查詢可以按照單表還是多表可以分為:單表SELECT查詢和多表的聯(lián)結查詢(INNER JOIN, LEFT JOIN, RIGHT JOIN和FULL JOIN)以及組合查詢UNION和UNION ALL SQL語句中各個關鍵字的執(zhí)行順序
MySQL的高級功能 存儲過程 事務處理 觸發(fā)器
1. 表(或數(shù)據(jù)庫)操作語句
1.1. 查詢表(或數(shù)據(jù)庫)
獲取所有可用的數(shù)據(jù)庫:SHOW DATABASES莲组;
選擇數(shù)據(jù)庫:USE customers;
用于顯示數(shù)據(jù)庫服務器的狀態(tài)信息:SHOW STATUS暖夭;
用來顯示授權用戶的安全權限:SHOW GRANTS锹杈;
用來顯示數(shù)據(jù)庫服務器或警告信息:SHOW ERRORS 或者 SHOW WARNINGS;
用于顯示創(chuàng)建數(shù)據(jù)庫時的創(chuàng)建語句:SHOW CREATE DATABASE customers迈着;
用于顯示創(chuàng)建表時的創(chuàng)建語句:SHOW CREATE TABLE customers竭望;
獲取當前所選的數(shù)據(jù)庫中所有可用的表:SHOW TABLES;
獲取表中所有列的信息:SHOW COLUMNS FROM tableName裕菠;同時DESCRIBE語句有相同的效果:DESCRIBE tableName咬清;
1.2. 新建表(或)數(shù)據(jù)庫
新建數(shù)據(jù)庫:CREATE DATABASE customers;
創(chuàng)建表可以使用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; 復制代碼 有這樣一些細節(jié): 允許NULL值,則說明在插入行數(shù)據(jù)時允許不給出該列的值奴潘,而NOT NULL則表示在插入或者更新該列數(shù)據(jù)旧烧,必須明確給出該列的值;?DEFAULT表示該列的默認值画髓,在插入行數(shù)據(jù)時掘剪,若沒有給出該列的值就會使用其指定的默認值;?PRIMARY KEY用于指定主鍵奈虾,主鍵可以指定一列數(shù)據(jù)夺谁,而可以由多列數(shù)據(jù)組合構成,如PRIMARY KEY(cust_id,cust_name)肉微;?ENGINE用于指定引擎類型匾鸥。常見的引擎類型有這些:(1)InnoDB是一個支持可靠的事務處理的引擎,但是不支持全文本搜索浪册;(2)MyISAM是一個性能極高的引擎扫腺,它支持全文本搜索,但是不支持事務處理村象;(3)MEMORY在功能上等同于MyISAM笆环,但由于數(shù)據(jù)存儲在內存中攒至,速度很快(特別適合于臨時表);
在創(chuàng)建表的時候可以使用FOREIGN KEY來創(chuàng)建外鍵躁劣,即一個表中的FOREIGN KEY指向另一個表中PRIMARY KEY迫吐。外鍵FOREIGN KEY用于約束破壞表的聯(lián)結動作,保證兩個表的數(shù)據(jù)完整性账忘。同時也能防止非法數(shù)據(jù)插入外鍵列志膀,因為該列值必須指向另一個表的主鍵誓竿。實例為: 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 刪除表(或數(shù)據(jù)庫)
刪除數(shù)據(jù)庫:DROP DATABASE customers岖免;
刪除表,使用DROP TABLE子句:DROP TABLE customers炸客。
1.4 更新表
更新表結構信息可以使用ALTER TABLE子句蒋荚,如為表增加一列:ALTER TABLE vendors ADD vend_name CHAR(20)戳稽;另外經(jīng)常用于定義外鍵,如: 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 表數(shù)據(jù)操作語句
2.1 查詢表數(shù)據(jù)
基本查詢語句
根據(jù)過濾條件查詢表中的單列或者多列或者全部列的信息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乓序;
限制單列查詢結果的行數(shù):SELECT cust_name FROM customers LIMIT 5;LIMIT后跟一個數(shù)值舟奠,表示從第0行開始取竭缝,共取5行數(shù)據(jù);如果LIMIT 5沼瘫,5表示從第5行(數(shù)據(jù)庫中實際第6行記錄)開始取抬纸,共取5行數(shù)據(jù)。注意:數(shù)據(jù)是從第0行開始計數(shù)的耿戚;
ORDER BY子句取一個或者多個列湿故,據(jù)此對輸出進行排序: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操作符配合使用,用于表示檢索出不符合條件的所有數(shù)據(jù)皂股;
LIKE操作符用來表明模糊查詢墅茉,與之配合使用的通配符有**%**,%表示任何字符出現(xiàn)任何次數(shù);_就斤,_表示只能匹配一個字符: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過濾掉的數(shù)據(jù)不會出現(xiàn)在分組中喜鼓。WHERE中通配符以及多個WHERE子句的連接同樣適用于HAVING子句;
GROUP BY的使用注意事項: (1)GROUP BY子句中可以嵌套分組(即通過多個列進行分組GROUP BY cust_id, cust_name)衔肢,但是進行數(shù)據(jù)匯總時庄岖,是在最后規(guī)定的分組上進行;(2)GROUP BY子句中列出的每個列都必須是檢索列或者是有效的表達式角骤。(3)如果有NULL值顿锰,將值NULL作為一個分組進行返回,如果有多行NULL值启搂,它們將分為一組
嵌套其他查詢中的查詢,稱之為子查詢刘陶。執(zhí)行過程由里向外胳赌,里層查詢結果作為外層查詢的條件:SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')。當然匙隔,多表的查詢可以是用聯(lián)結查詢疑苫。
聯(lián)結查詢
內聯(lián)結用又稱之為內部聯(lián)結,是基于兩個表 之間的的相等測試纷责。如果不加過濾條件捍掺,會造成“笛卡爾積”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id再膳;同樣可以使用WHERE進行多表聯(lián)結查詢挺勿,但是更推薦使用INNER JOIN等聯(lián)結方式;
外部聯(lián)結包括左外聯(lián)結LEFT JOIN和右外聯(lián)結RIGHT JOIN和全連接FULL JOIN喂柒。例如查詢每個客戶的訂單數(shù):SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id不瓶;LEFT JOIN 會全部返回左表數(shù)據(jù),RIGHT JOIN會全部返回右表數(shù)據(jù)灾杰,F(xiàn)ULL JOIN會將左右兩個表的數(shù)據(jù)全部返回蚊丐;
聯(lián)結查詢與聚集函數(shù)一起使用。如查詢每個客戶的訂單數(shù):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查詢,并且每個傳必須包含相同的列、表達式或聚集函數(shù)凛篙,數(shù)據(jù)類型不必完全相同黍匾,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
使用函數(shù)對數(shù)據(jù)進行處理
拼接列名:SELECT Concat (vendName,'(',vendCountry,')') FROM vendors ORDER BY vendName融痛;
執(zhí)行算術表達式計算:SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems壶笼;
文本處理函數(shù)如Upper(),LTrim(),RTrim()等函數(shù)。比如使用Upper函數(shù)將文本轉換成大寫:SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName雁刷;
時間和日期處理函數(shù)覆劈,如Date(),Day()等。SELECT custId, orderNum FROM orders WHERE Date(orderDate)='2015-09-01'沛励;
數(shù)值處理函數(shù)责语,如Abs(),Cos()等;
常用的聚集函數(shù)目派。如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 插入表數(shù)據(jù)
向表中插入行數(shù)據(jù)可以使用INSERT INTO子句企蹭,更安全的方式是指定列名白筹。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com');在INSERT INTO子句中能夠省略列名的前提條件是:該列可以允許定義為NULL值或者在定義該列時給出去了默認值谅摄;
如果插入多行數(shù)據(jù)可以將多組值用逗號進行分隔即可徒河。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com');
將查詢出來的數(shù)據(jù)插入表中送漠,可以使用INSERT SELECT語句顽照。INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5;其中SELECT中可以帶WHERE過濾條件闽寡;INSERT SELECT通常被用于復制表數(shù)據(jù)
2.3 更新表數(shù)據(jù)
如果要更新表數(shù)據(jù)的話棒厘,使用UPDATE子句:UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001;
注意:如果不加WHERE條件指定到某一行的話下隧,會更新表中某一列全部的數(shù)據(jù)奢人。
2.4 刪除表數(shù)據(jù)
如果從表中刪除數(shù)據(jù)的話,可以使用DELETE子句淆院。DELETE FROM customers WHERE cust_id = 10086何乎;刪除的數(shù)據(jù)必定是表中行數(shù)據(jù)句惯,而不是某一列。因此支救,與UPDATE子句相比抢野,DELETE子句并不需要指定是哪一列,而僅僅只需要指定具體的表名即可各墨;
注意:如果不添加WHERE指定條件的話指孤,會將整個表中所有行數(shù)據(jù)全部刪除。另外贬堵,DELETE只是刪除表中的數(shù)據(jù)恃轩,而不會刪除表結構信息;
如果想刪除表中全部的數(shù)據(jù)黎做,可以使用TRUNCATE叉跛,比DELETE刪除效率更高;
3. SQL中關鍵字執(zhí)行順序
在SQL語句中每個關鍵字都會按照順序往下執(zhí)行蒸殿,而每一步操作筷厘,會生成一個虛擬表,最后產(chǎn)生的虛擬表會作為執(zhí)行的最終結果返回宏所。下面的是常用的關鍵字的執(zhí)行順序:
(8)SELECT (9)DISTINCT
(1)FROM
(3) JOIN
(2)? ? ? ? ? ? ON
(4)WHERE
(5)GROUP BY
(6)WITH{CUBE|ROLLUP}
(7)HAVING
(10)ORDER BY
(11)LIMIT
FROM:對FROM左邊的表和右邊的表計算笛卡爾積酥艳,產(chǎn)生虛表VT1;
ON:對虛擬表VT1進行ON篩選爬骤,只有那些符合條件的行才會被記錄在虛擬表VT2中玖雁;
JOIN:如果是OUT JOIN,那么將保留表中(如左表或者右表)未匹配的行作為外部行添加到虛擬表VT2中盖腕,從而產(chǎn)生虛擬表VT3;
WHERE:對虛擬表VT3進行WHERE條件過濾浓镜,只有符合的記錄才會被放入到虛擬表VT4溃列;
GROUP BY:根據(jù)GROUP BY子句中的列,對虛擬表VT4進行分組操作膛薛,產(chǎn)生虛擬表VT5听隐;
CUBE|ROLLUP:對虛擬表VT5進行CUBE或者ROLLUP操作,產(chǎn)生虛擬表VT6哄啄;
HAVING:對虛擬表VT6進行HAVING條件過濾雅任,只有符合的記錄才會被插入到虛擬表VT7中;
SELECT:執(zhí)行SELECT操作咨跌,選擇指定的列沪么,插入到虛擬表VT8中;
DISTINCT:對虛擬表VT8中的記錄進行去重锌半,產(chǎn)生虛擬表VT9禽车;
ORDER BY:將虛擬表VT9中的記錄按照進行排序操作,產(chǎn)生虛擬表VT10;
LIMIT:取出指定行的記錄殉摔,產(chǎn)生虛擬表VT11州胳,并將結果返回。
4. 索引
MySQL索引的建立對于MySQL的高效運行是很重要的逸月,索引可以大大提高MySQL的檢索速度栓撞。索引分單列索引和組合索引。單列索引碗硬,即一個索引只包含單個列瓤湘,而組合索引,即一個索引包含多個列肛响。
4.1 創(chuàng)建索引
創(chuàng)建索引有兩種方式岭粤,一種是直接利用CREATE INDEX進行創(chuàng)建,另外一種則是通過修改表結構來進行添加特笋,則是利用ALTER TABLE語句剃浇。
使用CREATE INDEX 語法為: CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type] ON table_name (index_col_name,...) 其中對應的語法變量信息如下:?[UNIQUE|FULLTEXT|SPATIAL]?其中括號中的這三個關鍵字表示創(chuàng)建的索引類型,它們分別表示唯一索引猎物、全文索引虎囚、空間索引三種不同的索引類型。如果我們不指定任何關鍵字蔫磨,則默認為普通索引淘讥。?index_name?index_name表示索引的名稱,由用戶自行定義堤如,以便于以后對該索引進行修改等管理操作蒲列。?index_type?index_type表示索引的具體實現(xiàn)方式,在MySQL中搀罢,有兩種不同形式的索引——BTREE索引和HASH索引蝗岖。在存儲引擎為MyISAM和InnoDB的表中只能使用BTREE,其默認值就是BTREE榔至;在存儲引擎為MEMORY或者HEAP的表中可以使用HASH和BTREE兩種類型的索引抵赢,其默認值為HASH。?index_colname?index_col_name表示需要創(chuàng)建索引的字段名稱唧取,我們還可以針對多個字段創(chuàng)建復合索引铅鲤,只需要在多個字段名稱之間以英文逗號隔開即可。此外枫弟,對于CHAR或VARCHAR類型的字段邢享,我們還可以只使用字段內容前面的一部分來創(chuàng)建索引,只需要在對應的字段名稱后面加上形如(length)的指令即可淡诗,表示只需要使用字段內容前面的length個字符來創(chuàng)建索引驼仪。在這里掸犬,我們以customers表的cust_name字段(類型為VARCHAR(50))為例,使用cust_name字段的6個字符前綴來創(chuàng)建索引绪爸。 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中并沒有提供修改索引的直接指令奠货,一般情況下介褥,我們需要先刪除掉原索引,再根據(jù)需要創(chuàng)建一個同名的索引递惋,從而變相地實現(xiàn)修改索引操作柔滔。
--先刪除
ALTER TABLE user
DROP INDEX idx_user_username;
--再以修改后的內容創(chuàng)建同名索引
CREATE INDEX idx_cust_name ON customers (cust_name(8));
4.4 查看索引
在MySQL中,要查看某個數(shù)據(jù)庫表中的索引也非常簡單萍虽,只需要使用以下兩個命令中的任意一種即可睛廊。
--如果查看索引前,沒有使用user db_name等命令指定具體的數(shù)據(jù)庫杉编,則必須加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]
--如果查看索引前超全,沒有使用user db_name等命令指定具體的數(shù)據(jù)庫,則必須加上db_name.前綴
SHOW INDEX FROM [db_name.]table_name
5. 存儲過程
什么是存儲過程邓馒?存儲過程簡單來說嘶朱,就是為了復用性或者實現(xiàn)復雜的業(yè)務功能,而保存的一條或多條MySQL語句的集合光酣,可將其視為批文件疏遏;
為什么使用存儲過程?(1)通過把處理封裝在容易使用的單元中救军,簡化復雜的操作财异;(2)由于不要求反復建立一系列處理步驟,這保證了數(shù)據(jù)的完整性唱遭,如果所有的開發(fā)人員和應用程序都使用同一存儲過程戳寸,則所使用的代碼都是相同的;(3)簡化對變動的管理胆萧。如果表名、列名或業(yè)務邏輯有變化俐东,只需要更改存儲過程的代碼跌穗,使用它的開發(fā)人員甚至不需要知道這些變化,也就是具備了安全性虏辫;(4)提高了性能蚌吸,因為使用存儲過程比單獨使用SQL語句要快;(5)存儲過程可用來編寫功能更靈活的代碼砌庄。因此羹唠,存儲過程的具備三個特性:簡單可復用奕枢、安全以及高性能;
存儲過程的缺點佩微?(1)存儲過程編寫比基本的SQL語句更加復雜缝彬,需要更高的技能;(2)可能沒有創(chuàng)建存儲過程的權限哺眯,數(shù)據(jù)庫管理員可能會限制創(chuàng)建存儲過程的權限谷浅,允許用戶使用存儲過程,而不允許用戶自由創(chuàng)建存儲過程奶卓;
創(chuàng)建存儲過程
創(chuàng)建存儲過程一疯。如需要統(tǒng)計用戶訂單總金額,如果該用戶需要交稅的話夺姑,訂單總金額則需要再加上稅費 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 //
有這樣一些細節(jié): 1. 使用CREATE PROCEDURE語句進行創(chuàng)建墩邀,()圓括號中為存儲過程的參數(shù),其中參數(shù)類型有:1.?IN類型盏浙,表示傳遞給存儲過程眉睹;2.?OUT?類型,表示存儲過程返回的結果只盹,在調用存儲過程時需要傳入@開始的變量辣往;3.?INOUT類型,表示在存儲過程中可以傳入和傳出殖卑; 2.?DECLARE用來聲明一個變量站削,如這里的total,taxrate孵稽。注意MySQL中定義變量時都是變量名在前许起,數(shù)據(jù)類型在后。 3. 存儲過程具體邏輯寫在BEGIN END之間菩鲜; 4. 將值賦給變量使用INTO關鍵字园细; 5. 由于存儲過程中每個SQL語句中用;作為分隔符接校,會和單個SQL造成沖突猛频,因此可使用DELIMITER重新定義分類符,如該例子中定義//為分隔符蛛勉,自然存儲過程結尾就用END //結尾鹿寻,而不再是END。同時诽凌,分隔符//成對出現(xiàn)后毡熏,恢復到默認的";"作為分隔符;
執(zhí)行存儲過程
使用CALL子句執(zhí)行存儲過程侣诵,CALL子句接受存儲過程的名稱以及需要傳遞的參數(shù)痢法。 CALL ordertotal(1,TRUE,@total); SELECT @total;
如果存儲過程中定義了OUT類型的輸入?yún)?shù)狱窘,那么在執(zhí)行存儲過程時需要傳入變量,如這里@total财搁,并且變量都是用@開始的蘸炸。如果存儲過程中沒有參數(shù)的話,就用空圓括號表示即可妇拯,CALL ordertotal()幻馁;
刪除存儲過程
刪除存儲過程,可以使用DROP PROCEDURE子句越锈。如DROP PROCEDURE ordertotal仗嗦;
查詢存儲過程
顯示創(chuàng)建一個存儲過程的語句,可以使用SHOW CREATE PROCEDURE甘凭。如SHOW CREATE PROCEDURE ordertotal稀拐;
查詢所有存儲過程的狀態(tài),如果在定義存儲過程中使用COMMENT添加注釋丹弱,可以查看德撬。同時可以LIKE進行過濾結果。如SHOW PROCEDURE STATUS LIKE '%order%';
6. 事務處理
什么是事務躲胳? 事務處理是用來維護數(shù)據(jù)庫的完整性蜓洪,它保證成批的MySQL操作要么完全執(zhí)行,要么完全不執(zhí)行坯苹。事務處理是一種機制隆檀,用來管理必須成批執(zhí)行的MySQL操作,它們要么時作為整體執(zhí)行或者完全不執(zhí)行粹湃。
關鍵概念: 事務:是指一組SQL語句恐仑; 回退:是指撤銷指定的SQL語句的過程; 提交:指將未存儲的SQL語句的結果寫入數(shù)據(jù)庫表中为鳄; 保留點:指事務處理中設置的臨時占位符裳仆,可以對它發(fā)布回退;
如何創(chuàng)建執(zhí)行事務孤钦? 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; 執(zhí)行結果為:插入數(shù)據(jù)('1',5,18)有效歧斟,因為,只會從保留點SAFEPOINT之后開始回退偏形,也就是說保留點SAFEPOINT之前的SQL語句執(zhí)行的結果仍然有效静袖。 有這樣一些細節(jié):?STAET TRANSACTION用來表示下面的SQL語句集為一段事務;?SAFEPOINT?用于指定保留點insertinto壳猜;?ROLLBACK TO表示從指定保留點開始回退勾徽,也就是說保留點之前的SQL語句執(zhí)行結果依然有效滑凉。如果僅僅使用ROLLBACK進行回退的話就表示從STAET TRANSACTION之后所有的SQL語句執(zhí)行效果都會撤銷统扳;
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語句都執(zhí)行成功倾鲫,才會將數(shù)據(jù)都寫入表中。
7. 觸發(fā)器
什么是觸發(fā)器萍嬉? 當某條SQL語句發(fā)生時乌昔,自動執(zhí)行某些其他的SQL語句的時候就需要使用到觸發(fā)器。觸發(fā)器只能響應:DELETE壤追,INSERT,UPDATE這三個特定操作磕道。
創(chuàng)建觸發(fā)器? 創(chuàng)建觸發(fā)器時需要給出最重要的四條信息:1.全局唯一的觸發(fā)器名行冰;2.觸發(fā)器關聯(lián)的表溺蕉;3.觸發(fā)器在何時執(zhí)行(操作執(zhí)行之前或者之后)4.觸發(fā)器應該響應的活動(DELETE, INSERT或者UPDATE); 由于觸發(fā)器只能響應特定的三種類型的操作悼做,因此可創(chuàng)建的觸發(fā)器也就三種類型:INSERT觸發(fā)器疯特,DELETE觸發(fā)器以及UPDATE觸發(fā)器。?INSERT觸發(fā)器?在執(zhí)行INSERT觸發(fā)器時肛走,也這樣幾點需要注意:1.在INSERT觸發(fā)器代碼內漓雅,可以引用一個名為NEW的虛擬表,可以用NEW來訪問剛插入的行數(shù)據(jù)羹与;2.在BEFORE INSERT觸發(fā)器中故硅,NEW中的值可以被更新;3.對于AUTO_INCREMENT列纵搁,NEW在INSERT執(zhí)行之前包含0吃衅,在INSERT執(zhí)行之后包含新的自定生成值。 創(chuàng)建一個INSERT觸發(fā)器腾誉,每次插入一行數(shù)據(jù)徘层,每次會返回當前插入的行數(shù)據(jù)的id。 /*創(chuàng)建觸發(fā)器*/ CREATE TRIGGER insertcustomers AFTER INSERT ON customers FOR EACH ROW SELECT NEW.cust_id INTO @newinsertid; /*執(zhí)行觸發(fā)器*/ INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18); SELECT @newinsertid; 復制代碼 有這樣一些細節(jié): 使用CREATE TRIGGER來創(chuàng)建觸發(fā)器利职; AFTER INSERT表明在插入行數(shù)據(jù)之后趣效,觸發(fā)器才會執(zhí)行特征操作; FOR EACH ROW 表示對插入的每一行數(shù)據(jù)猪贪,觸發(fā)器都起作用跷敬; 針對INSERT觸發(fā)器,可以使用虛擬表NEW热押,來使用剛插入的行數(shù)據(jù)西傀。比如例子中斤寇,SELECT NEW.cust_id INTO @newinsertid表示將新插入的行數(shù)據(jù)的id賦值給變量@newinsertid;?DELETE觸發(fā)器?DELETE觸發(fā)器在DELETE語句執(zhí)行之前或者之后拥褂,需要知道以下兩點: 在DELETE觸發(fā)器代碼內娘锁,可以引用一個名為OLD的虛擬表,來訪問被刪除的行饺鹃; OLD表中的數(shù)據(jù)只能讀莫秆,不能被更新,而在INSERT觸發(fā)器中悔详,就可以通過NEW來更新被插入的行數(shù)據(jù)镊屎; 例如,針對customers表茄螃,當刪除一行數(shù)據(jù)時杯道,返回被刪除數(shù)據(jù)的cust_id以及cust_name: /*創(chuàng)建DELETE觸發(fā)器*/ 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觸發(fā)器*/ DELETE FROM customers WHERE cust_id = 3; SELECT @deletecustname; SELECT @deletecustid; 復制代碼 基本上與創(chuàng)建INSERT觸發(fā)器一樣,只不過在DELETE觸發(fā)器中只能使用OLD來訪問被刪除的行數(shù)據(jù)责蝠。?UPDATE觸發(fā)器?UPDATE觸發(fā)器在UPDATE語句執(zhí)行之前或者之后執(zhí)行党巾,需要知道一下幾點: 在BEFORE UPDATE觸發(fā)器中可以使用NEW和OLD來訪問數(shù)據(jù),而在AFTER UPDATE觸發(fā)器中使用NEW來訪問數(shù)據(jù)會報錯霜医,只能使用OLD來訪問數(shù)據(jù)齿拂; 在BEFORE UPDATE觸發(fā)器中,NEW中的值可以被改變肴敛,即允許更改將用于UPDATE的數(shù)據(jù)署海; OLD中的行數(shù)據(jù)只能讀,不能被更新医男; 一個UPDATE觸發(fā)器示例如下: /*創(chuàng)建UPDATE觸發(fā)器*/ 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觸發(fā)器*/ UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5; SELECT @beforeupdate; SELECT @afterupdate; 復制代碼 輸出為@beforeupdate為‘happay’砸狞,而@afterupdate為'reset_name'。有這樣一些細節(jié): NEW虛擬表中的數(shù)據(jù)可以更改镀梭,如這里采用 SET NEW.cust_name = 'reset_name';刀森,將待更新的cust_name由“happy”變成了“reset_name”; 在BEFORE UPDATE觸發(fā)器中可以使用NEW和OLD來訪問數(shù)據(jù)报账,而在AFTER UPDATE觸發(fā)器中使用NEW來訪問數(shù)據(jù)會報錯研底;
刪除觸發(fā)器? 刪除觸發(fā)器透罢,可以使用 DROP TRIGGER語句榜晦,比如DROP TRIGGER insertcustomers;。觸發(fā)器不能更新或者覆蓋羽圃,如果要修改觸發(fā)器乾胶,必須刪除這個觸發(fā)器。