MySQL的基本操作可以包括兩個(gè)方面：MySQL常用語(yǔ)句如高頻率使用的增刪改查（CRUD）語(yǔ)句和MySQL高級(jí)功能，如存儲(chǔ)過(guò)程、觸發(fā)器、事務(wù)處理等。而這兩個(gè)方面又可以細(xì)分如下：

1、MySQL常用語(yǔ)句

• 表（或者數(shù)據(jù)庫(kù)）的CRUD
• 表數(shù)據(jù)的CRUD，其中表數(shù)據(jù)查詢使用最多，也更復(fù)雜。查詢可以按照單表還是多表可以分為：?jiǎn)伪鞸ELECT查詢和多表的聯(lián)結(jié)查詢（INNER JOIN, LEFT JOIN, RIGHT JOIN和FULL JOIN）以及組合查詢UNION和UNION ALL
• SQL語(yǔ)句中各個(gè)關(guān)鍵字的執(zhí)行順序

2、MySQL高級(jí)功能

• 存儲(chǔ)過(guò)程
• 事務(wù)處理
• 觸發(fā)器

一、表（或數(shù)據(jù)庫(kù)）操作語(yǔ)句

查詢表（或數(shù)據(jù)庫(kù)）

1、獲取所有可用的數(shù)據(jù)庫(kù)：SHOW DATABASES；

2、選擇數(shù)據(jù)庫(kù)：USE customers；

3、用于顯示數(shù)據(jù)庫(kù)服務(wù)器的狀態(tài)信息：SHOW STATUS；

4、用來(lái)顯示授權(quán)用戶的安全權(quán)限：SHOW GRANTS；

5、用來(lái)顯示數(shù)據(jù)庫(kù)服務(wù)器或警告信息：SHOW ERRORS 或者SHOW WARNINGS；

6、用于顯示創(chuàng)建數(shù)據(jù)庫(kù)時(shí)的創(chuàng)建語(yǔ)句：SHOW CREATE DATABASE customers；

7、用于顯示創(chuàng)建表時(shí)的創(chuàng)建語(yǔ)句：SHOW CREATE TABLE customers；

8、獲取當(dāng)前所選的數(shù)據(jù)庫(kù)中所有可用的表：SHOW TABLES；

9、獲取表中所有列的信息：SHOW COLUMNS FROM tableName；同時(shí)DESCRIBE語(yǔ)句有相同的效果：DESCRIBE tableName；

新建表（或）數(shù)據(jù)庫(kù)

新建數(shù)據(jù)庫(kù)：CREATE DATABASE customers；

創(chuàng)建表可以使用CREATE TABLE語(yǔ)句：

 

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; 

 

有這樣一些細(xì)節(jié)：

• 允許NULL值，則說(shuō)明在插入行數(shù)據(jù)時(shí)允許不給出該列的值，而NOT NULL則表示在插入或者更新該列數(shù)據(jù)，必須明確給出該列的值；
• DEFAULT表示該列的默認(rèn)值，在插入行數(shù)據(jù)時(shí)，若沒(méi)有給出該列的值就會(huì)使用其指定的默認(rèn)值；
• PRIMARY KEY用于指定主鍵，主鍵可以指定一列數(shù)據(jù)，而可以由多列數(shù)據(jù)組合構(gòu)成，如PRIMARY KEY(cust_id,cust_name)；
• ENGINE用于指定引擎類型。常見(jiàn)的引擎類型有這些：

（1）InnoDB是一個(gè)支持可靠的事務(wù)處理的引擎，但是不支持全文本搜索；

（2）MyISAM是一個(gè)性能極高的引擎，它支持全文本搜索，但是不支持事務(wù)處理；

（3）MEMORY在功能上等同于MyISAM，但由于數(shù)據(jù)存儲(chǔ)在內(nèi)存中，速度很快（特別適合于臨時(shí)表）；

在創(chuàng)建表的時(shí)候可以使用FOREIGN KEY來(lái)創(chuàng)建外鍵，即一個(gè)表中的FOREIGN KEY指向另一個(gè)表中PRIMARY KEY。外鍵FOREIGN KEY用于約束破壞表的聯(lián)結(jié)動(dòng)作，保證兩個(gè)表的數(shù)據(jù)完整性。同時(shí)也能防止非法數(shù)據(jù)插入外鍵列，因?yàn)樵摿兄当仨氈赶蛄硪粋€(gè)表的主鍵。實(shí)例為：

 

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)  
) 

刪除表（或數(shù)據(jù)庫(kù)）

1、刪除數(shù)據(jù)庫(kù)：DROP DATABASE customers；

2、刪除表，使用DROP TABLE子句：DROP TABLE customers。

更新表

1、更新表結(jié)構(gòu)信息可以使用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) 

 

2、重命名表，使用RENAME子句。RENAME TABLE backup_customers TO customers, backup_vendors TO vendors；更改多個(gè)表名，之間用逗號(hào)間隔。

二、表數(shù)據(jù)操作語(yǔ)句

查詢表數(shù)據(jù)

1、基本查詢語(yǔ)句

（1）根據(jù)過(guò)濾條件查詢表中的單列或者多列或者全部列的信息SELECT FROM WEHERE：SELECT cust_id,cust_name FROM customers WHERE cust_id.10086；其中過(guò)濾條件操作符有：=，<>,!=,<,<=,>,>=,BETWEEN AND,IS NULL；

（2）為查詢出的某一列信息去重DISTINCT：SELECT DISTINCT cust_name FROM customers；

（3）限制單列查詢結(jié)果的行數(shù)：SELECT cust_name FROM customers LIMIT 5；LIMIT后跟一個(gè)數(shù)值，表示從第0行開(kāi)始取，共取5行數(shù)據(jù)；如果LIMIT 5，5表示從第5行（數(shù)據(jù)庫(kù)中實(shí)際第6行記錄）開(kāi)始取，共取5行數(shù)據(jù)。注意：數(shù)據(jù)是從第0行開(kāi)始計(jì)數(shù)的；

（4）ORDER BY子句取一個(gè)或者多個(gè)列，據(jù)此對(duì)輸出進(jìn)行排序：SELECT cust_id,cust_name FROM customers ORDER BY cust_id DESC, cust_name；

（5）IN操作符用來(lái)指定條件范圍，范圍中的每個(gè)條件都可以進(jìn)行匹配：SELECT cust_id, cust_name FROM customers WHERE cust_id IN (1000,2000)。另外，NOT操作符可以和IN操作符配合使用，用于表示檢索出不符合條件的所有數(shù)據(jù)；

（6）LIKE操作符用來(lái)表明模糊查詢，與之配合使用的通配符有**%**，%表示任何字符出現(xiàn)任何次數(shù)；_，_表示只能匹配一個(gè)字符：SELECT cust_id,cust_name FROM customers WHERE cust_name LIKE '%happy%'；

（7）使用分組查詢并可以滿足一定的分組過(guò)濾條件GROUP BY HAVING。如檢索總計(jì)訂單金額大于等于50的訂單號(hào)和訂單總金額，并按總金額進(jìn)行排序：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是行級(jí)過(guò)濾，而HAVING是組級(jí)過(guò)濾。被WHERE過(guò)濾掉的數(shù)據(jù)不會(huì)出現(xiàn)在分組中。WHERE中通配符以及多個(gè)WHERE子句的連接同樣適用于HAVING子句；

（9）GROUP BY的使用注意事項(xiàng)： 

• GROUP BY子句中可以嵌套分組（即通過(guò)多個(gè)列進(jìn)行分組GROUP BY cust_id, cust_name），但是進(jìn)行數(shù)據(jù)匯總時(shí)，是在最后規(guī)定的分組上進(jìn)行；
• GROUP BY子句中列出的每個(gè)列都必須是檢索列或者是有效的表達(dá)式；
• 如果有NULL值，將值NULL作為一個(gè)分組進(jìn)行返回，如果有多行NULL值，它們將分為一組；

（10）嵌套其他查詢中的查詢，稱之為子查詢。執(zhí)行過(guò)程由里向外，里層查詢結(jié)果作為外層查詢的條件：SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'happy')。當(dāng)然，多表的查詢可以是用聯(lián)結(jié)查詢。

2、聯(lián)結(jié)查詢

（1）內(nèi)聯(lián)結(jié)用又稱之為內(nèi)部聯(lián)結(jié)，是基于兩個(gè)表 之間的的相等測(cè)試。如果不加過(guò)濾條件，會(huì)造成“笛卡爾積”。SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id；同樣可以使用WHERE進(jìn)行多表聯(lián)結(jié)查詢，但是更推薦使用INNER JOIN等聯(lián)結(jié)方式；

（2）外部聯(lián)結(jié)包括左外聯(lián)結(jié)LEFT JOIN和右外聯(lián)結(jié)RIGHT JOIN和全連接FULL JOIN。例如查詢每個(gè)客戶的訂單數(shù)：SELECT customers.cust_id,orders.orders_num FROM customers LEFT JOIN orders ON orders.cust_id =customers.cust_id；LEFT JOIN會(huì)全部返回左表數(shù)據(jù)，RIGHT JOIN會(huì)全部返回右表數(shù)據(jù)，F(xiàn)ULL JOIN會(huì)將左右兩個(gè)表的數(shù)據(jù)全部返回；

（3）聯(lián)結(jié)查詢與聚集函數(shù)一起使用。如查詢每個(gè)客戶的訂單數(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；

3、組合查詢

（1）多個(gè)查詢（SELECT）可以使用UNION將多個(gè)查詢結(jié)果進(jìn)行合并成一個(gè)結(jié)果集返回，UNION必須包含兩個(gè)及兩個(gè)以上的SELECT查詢，并且每個(gè)傳必須包含相同的列、表達(dá)式或聚集函數(shù)，數(shù)據(jù)類型不必完全相同，MySQL會(huì)進(jìn)行隱式的類型轉(zhuǎn)換。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返回的是去重后的結(jié)果，如果不需要去重則可以使用UNION ALL；

（3）可以多組合查詢使用ORDER BY進(jìn)行排序，但是是針對(duì)的最終的結(jié)果集進(jìn)行排序，而不是其中單個(gè)SELECT查詢進(jìn)行排序，因此對(duì)于組合查詢來(lái)說(shuō)ORDER BY子句只有一個(gè)。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

4、使用函數(shù)對(duì)數(shù)據(jù)進(jìn)行處理

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

（2）執(zhí)行算術(shù)表達(dá)式計(jì)算：SELECT prodId, quantity,price, quantity*price AS expandedPrice FROM orderItems；

（3）文本處理函數(shù)如Upper(),LTrim(),RTrim()等函數(shù)。比如使用Upper函數(shù)將文本轉(zhuǎn)換成大寫：SELECT vendName, Upper(vendName) FROM vendors ORDER BY vendName；

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

（5）數(shù)值處理函數(shù)，如Abs(),Cos()等；

（6）常用的聚集函數(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；

插入表數(shù)據(jù)

1、向表中插入行數(shù)據(jù)可以使用INSERT INTO子句，更安全的方式是指定列名。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com')；在INSERT INTO子句中能夠省略列名的前提條件是：該列可以允許定義為NULL值或者在定義該列時(shí)給出去了默認(rèn)值；

2、如果插入多行數(shù)據(jù)可以將多組值用逗號(hào)進(jìn)行分隔即可。INSERT INTO customers (cust_name, cust_email) VALUES('happy','happy@gmail.com'),('smart','smart@gmail.com')；

3、將查詢出來(lái)的數(shù)據(jù)插入表中，可以使用INSERT SELECT語(yǔ)句。INSERT INTO customers(cust_id,cust_contact) SELECT cust_id, cust_contact FROM customers WHERE cust_id>5；其中SELECT中可以帶WHERE過(guò)濾條件；INSERT SELECT通常被用于復(fù)制表數(shù)據(jù)。

更新表數(shù)據(jù)

1、如果要更新表數(shù)據(jù)的話，使用UPDATE子句：UPDATE customers SET cust_name ='happy',cust_email='happy@gmail.com' WHERE cust_id = 1001；

2、注意：如果不加WHERE條件指定到某一行的話，會(huì)更新表中某一列全部的數(shù)據(jù)。

刪除表數(shù)據(jù)

1、如果從表中刪除數(shù)據(jù)的話，可以使用DELETE子句。DELETE FROM customers WHERE cust_id = 10086；刪除的數(shù)據(jù)必定是表中行數(shù)據(jù)，而不是某一列。因此，與UPDATE子句相比，DELETE子句并不需要指定是哪一列，而僅僅只需要指定具體的表名即可；

2、注意：如果不添加WHERE指定條件的話，會(huì)將整個(gè)表中所有行數(shù)據(jù)全部刪除。另外，DELETE只是刪除表中的數(shù)據(jù)，而不會(huì)刪除表結(jié)構(gòu)信息；

3、如果想刪除表中全部的數(shù)據(jù)，可以使用TRUNCATE，比DELETE刪除效率更高。

三、SQL中關(guān)鍵字執(zhí)行順序

在SQL語(yǔ)句中每個(gè)關(guān)鍵字都會(huì)按照順序往下執(zhí)行，而每一步操作，會(huì)生成一個(gè)虛擬表，最后產(chǎn)生的虛擬表會(huì)作為執(zhí)行的最終結(jié)果返回。下面的是常用的關(guān)鍵字的執(zhí)行順序：

(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：對(duì)FROM左邊的表和右邊的表計(jì)算笛卡爾積，產(chǎn)生虛表VT1；

2、ON：對(duì)虛擬表VT1進(jìn)行ON篩選，只有那些符合<join_condition>條件的行才會(huì)被記錄在虛擬表VT2中；

3、JOIN：如果是OUT JOIN，那么將保留表中（如左表或者右表）未匹配的行作為外部行添加到虛擬表VT2中，從而產(chǎn)生虛擬表VT3；

4、WHERE：對(duì)虛擬表VT3進(jìn)行WHERE條件過(guò)濾，只有符合<where_condition>的記錄才會(huì)被放入到虛擬表VT4；

5、GROUP BY：根據(jù)GROUP BY子句中的列，對(duì)虛擬表VT4進(jìn)行分組操作，產(chǎn)生虛擬表VT5；

6、CUBE|ROLLUP：對(duì)虛擬表VT5進(jìn)行CUBE或者ROLLUP操作，產(chǎn)生虛擬表VT6；

7、HAVING：對(duì)虛擬表VT6進(jìn)行HAVING條件過(guò)濾，只有符合<having_condition>的記錄才會(huì)被插入到虛擬表VT7中；

8、SELECT：執(zhí)行SELECT操作，選擇指定的列，插入到虛擬表VT8中；

9、DISTINCT：對(duì)虛擬表VT8中的記錄進(jìn)行去重，產(chǎn)生虛擬表VT9；

10、ORDER BY：將虛擬表VT9中的記錄按照<order_by_list>進(jìn)行排序操作，產(chǎn)生虛擬表VT10；

11、LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT11，并將結(jié)果返回。

四、索引

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的，索引可以大大提高M(jìn)ySQL的檢索速度。索引分單列索引和組合索引。單列索引，即一個(gè)索引只包含單個(gè)列，而組合索引，即一個(gè)索引包含多個(gè)列。

創(chuàng)建索引

創(chuàng)建索引有兩種方式，一種是直接利用CREATE INDEX進(jìn)行創(chuàng)建，另外一種則是通過(guò)修改表結(jié)構(gòu)來(lái)進(jìn)行添加，則是利用ALTER TABLE語(yǔ)句。

1、使用CREATE INDEX

語(yǔ)法為：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name

 [USING index_type]

 ON table_name (index_col_name,...)

其中對(duì)應(yīng)的語(yǔ)法變量信息如下：

• [UNIQUE|FULLTEXT|SPATIAL]

其中括號(hào)中的這三個(gè)關(guān)鍵字表示創(chuàng)建的索引類型，它們分別表示唯一索引、全文索引、空間索引三種不同的索引類型。如果我們不指定任何關(guān)鍵字，則默認(rèn)為普通索引。

• index_name

index_name表示索引的名稱，由用戶自行定義，以便于以后對(duì)該索引進(jìn)行修改等管理操作。

• index_type

index_type表示索引的具體實(shí)現(xiàn)方式，在MySQL中，有兩種不同形式的索引——BTREE索引和HASH索引。在存儲(chǔ)引擎為MyISAM和InnoDB的表中只能使用BTREE，其默認(rèn)值就是BTREE；在存儲(chǔ)引擎為MEMORY或者HEAP的表中可以使用HASH和BTREE兩種類型的索引，其默認(rèn)值為HASH。

• index_colname

index_col_name表示需要?jiǎng)?chuàng)建索引的字段名稱，我們還可以針對(duì)多個(gè)字段創(chuàng)建復(fù)合索引，只需要在多個(gè)字段名稱之間以英文逗號(hào)隔開(kāi)即可。

此外，對(duì)于CHAR或VARCHAR類型的字段，我們還可以只使用字段內(nèi)容前面的一部分來(lái)創(chuàng)建索引，只需要在對(duì)應(yīng)的字段名稱后面加上形如(length)的指令即可，表示只需要使用字段內(nèi)容前面的length個(gè)字符來(lái)創(chuàng)建索引。在這里，我們以customers表的cust_name字段(類型為VARCHAR(50))為例，使用cust_name字段的6個(gè)字符前綴來(lái)創(chuàng)建索引。

 

CREATE INDEX idx_cust_name ON user (cust_name(6)); 

2、使用ALTER TABLE

語(yǔ)法為：

ALTER TABLE table_name

ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 

(index_col_name,...) [USING index_type]

刪除索引

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

ALTER TABLE table_name

DROP INDEX index_name;

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

 

ALTER TABLE customers  
DROP INDEX idx_cust_name; 

修改索引

在MySQL中并沒(méi)有提供修改索引的直接指令，一般情況下，我們需要先刪除掉原索引，再根據(jù)需要?jiǎng)?chuàng)建一個(gè)同名的索引，從而變相地實(shí)現(xiàn)修改索引操作。

 

--先刪除  
ALTER TABLE user  
DROP INDEX idx_user_username;  
--再以修改后的內(nèi)容創(chuàng)建同名索引  
CREATE INDEX idx_cust_name ON customers (cust_name(8)); 

查看索引

在MySQL中，要查看某個(gè)數(shù)據(jù)庫(kù)表中的索引也非常簡(jiǎn)單，只需要使用以下兩個(gè)命令中的任意一種即可。

 

--如果查看索引前，沒(méi)有使用user db_name等命令指定具體的數(shù)據(jù)庫(kù)，則必須加上FROM db_name  
SHOW INDEX FROM table_name [FROM db_name]  
--如果查看索引前，沒(méi)有使用user db_name等命令指定具體的數(shù)據(jù)庫(kù)，則必須加上db_name.前綴  
SHOW INDEX FROM [db_name.]table_name 

五、存儲(chǔ)過(guò)程

什么是存儲(chǔ)過(guò)程？存儲(chǔ)過(guò)程簡(jiǎn)單來(lái)說(shuō)，就是為了復(fù)用性或者實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)功能，而保存的一條或多條MySQL語(yǔ)句的集合，可將其視為批文件。

為什么使用存儲(chǔ)過(guò)程？

• 通過(guò)把處理封裝在容易使用的單元中，簡(jiǎn)化復(fù)雜的操作；
• 由于不要求反復(fù)建立一系列處理步驟，這保證了數(shù)據(jù)的完整性，如果所有的開(kāi)發(fā)人員和應(yīng)用程序都使用同一存儲(chǔ)過(guò)程，則所使用的代碼都是相同的；
• 簡(jiǎn)化對(duì)變動(dòng)的管理。如果表名、列名或業(yè)務(wù)邏輯有變化，只需要更改存儲(chǔ)過(guò)程的代碼，使用它的開(kāi)發(fā)人員甚至不需要知道這些變化，也就是具備了安全性；
• 提高了性能，因?yàn)槭褂么鎯?chǔ)過(guò)程比單獨(dú)使用SQL語(yǔ)句要快；
• 存儲(chǔ)過(guò)程可用來(lái)編寫功能更靈活的代碼。因此，存儲(chǔ)過(guò)程的具備三個(gè)特性:簡(jiǎn)單可復(fù)用、安全以及高性能。

存儲(chǔ)過(guò)程的缺點(diǎn)？

• 存儲(chǔ)過(guò)程編寫比基本的SQL語(yǔ)句更加復(fù)雜，需要更高的技能；
• 可能沒(méi)有創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，數(shù)據(jù)庫(kù)管理員可能會(huì)限制創(chuàng)建存儲(chǔ)過(guò)程的權(quán)限，允許用戶使用存儲(chǔ)過(guò)程，而不允許用戶自由創(chuàng)建存儲(chǔ)過(guò)程。

創(chuàng)建存儲(chǔ)過(guò)程

1、創(chuàng)建存儲(chǔ)過(guò)程。如需要統(tǒng)計(jì)用戶訂單總金額，如果該用戶需要交稅的話，訂單總金額則需要再加上稅費(fèi)。

 

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 // 

有這樣一些細(xì)節(jié)：

 1. 使用CREATE PROCEDURE語(yǔ)句進(jìn)行創(chuàng)建，（）圓括號(hào)中為存儲(chǔ)過(guò)程的參數(shù)，其中參數(shù)類型有：

• IN類型，表示傳遞給存儲(chǔ)過(guò)程；
• OUT類型，表示存儲(chǔ)過(guò)程返回的結(jié)果，在調(diào)用存儲(chǔ)過(guò)程時(shí)需要傳入@開(kāi)始的變量；
• INOUT類型，表示在存儲(chǔ)過(guò)程中可以傳入和傳出；

2. DECLARE用來(lái)聲明一個(gè)變量，如這里的total，taxrate。注意MySQL中定義變量時(shí)都是變量名在前，數(shù)據(jù)類型在后。

3. 存儲(chǔ)過(guò)程具體邏輯寫在BEGIN END之間；

4. 將值賦給變量使用INTO關(guān)鍵字； 

5. 由于存儲(chǔ)過(guò)程中每個(gè)SQL語(yǔ)句中用；作為分隔符，會(huì)和單個(gè)SQL造成沖突，因此可使用DELIMITER重新定義分類符，如該例子中定義//為分隔符，自然存儲(chǔ)過(guò)程結(jié)尾就用END //結(jié)尾，而不再是END。同時(shí)，分隔符//成對(duì)出現(xiàn)后，恢復(fù)到默認(rèn)的";"作為分隔符。

執(zhí)行存儲(chǔ)過(guò)程

使用CALL子句執(zhí)行存儲(chǔ)過(guò)程，CALL子句接受存儲(chǔ)過(guò)程的名稱以及需要傳遞的參數(shù)。

 

CALL ordertotal(1,TRUE,@total);  
SELECT @total; 

 

如果存儲(chǔ)過(guò)程中定義了OUT類型的輸入?yún)?shù)，那么在執(zhí)行存儲(chǔ)過(guò)程時(shí)需要傳入變量，如這里@total，并且變量都是用@開(kāi)始的。如果存儲(chǔ)過(guò)程中沒(méi)有參數(shù)的話，就用空?qǐng)A括號(hào)表示即可，CALL ordertotal()；

刪除存儲(chǔ)過(guò)程

刪除存儲(chǔ)過(guò)程，可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal；

查詢存儲(chǔ)過(guò)程

1、顯示創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的語(yǔ)句，可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal；

2、查詢所有存儲(chǔ)過(guò)程的狀態(tài)，如果在定義存儲(chǔ)過(guò)程中使用COMMENT添加注釋，可以查看。同時(shí)可以LIKE進(jìn)行過(guò)濾結(jié)果。如SHOW PROCEDURE STATUS LIKE '%order%'

六、事務(wù)處理

什么是事務(wù)？

• 事務(wù)處理是用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，它保證成批的MySQL操作要么完全執(zhí)行，要么完全不執(zhí)行。事務(wù)處理是一種機(jī)制，用來(lái)管理必須成批執(zhí)行的MySQL操作，它們要么時(shí)作為整體執(zhí)行或者完全不執(zhí)行。

關(guān)鍵概念：

• 事務(wù)：是指一組SQL語(yǔ)句；
• 回退：是指撤銷指定的SQL語(yǔ)句的過(guò)程；
• 提交：指將未存儲(chǔ)的SQL語(yǔ)句的結(jié)果寫入數(shù)據(jù)庫(kù)表中；
• 保留點(diǎn)：指事務(wù)處理中設(shè)置的臨時(shí)占位符，可以對(duì)它發(fā)布回退。

如何創(chuàng)建執(zhí)行事務(wù)？

 

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í)行結(jié)果為：插入數(shù)據(jù)('1',5,18)有效，因?yàn)?，只?huì)從保留點(diǎn)SAFEPOINT之后開(kāi)始回退，也就是說(shuō)保留點(diǎn)SAFEPOINT之前的SQL語(yǔ)句執(zhí)行的結(jié)果仍然有效。

有這樣一些細(xì)節(jié)：

• START TRANSACTION用來(lái)表示下面的SQL語(yǔ)句集為一段事務(wù)；
• SAFEPOINT 用于指定保留點(diǎn)insertinto；
• ROLLBACK TO表示從指定保留點(diǎn)開(kāi)始回退，也就是說(shuō)保留點(diǎn)之前的SQL語(yǔ)句執(zhí)行結(jié)果依然有效。如果僅僅使用ROLLBACK進(jìn)行回退的話就表示從STAET TRANSACTION之后所有的SQL語(yǔ)句執(zhí)行效果都會(huì)撤銷。

MySQL提交（寫或保存）操作是自動(dòng)進(jìn)行的，這稱之為隱含提交。但是在事務(wù)處理塊中，提交不會(huì)隱含進(jìn)行，要使用COMMIT子句進(jìn)行提交。如：

 

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提交事務(wù)，如果兩條SQL語(yǔ)句都執(zhí)行成功，才會(huì)將數(shù)據(jù)都寫入表中。

七、觸發(fā)器

什么是觸發(fā)器？

• 當(dāng)某條SQL語(yǔ)句發(fā)生時(shí)，自動(dòng)執(zhí)行某些其他的SQL語(yǔ)句的時(shí)候就需要使用到觸發(fā)器。觸發(fā)器只能響應(yīng)：DELETE、INSERT、UPDATE這三個(gè)特定操作。

如何創(chuàng)建觸發(fā)器？創(chuàng)建觸發(fā)器時(shí)需要給出最重要的四條信息：

• 全局唯一的觸發(fā)器名；
• 觸發(fā)器關(guān)聯(lián)的表；
• 觸發(fā)器在何時(shí)執(zhí)行（操作執(zhí)行之前或者之后）；
• 觸發(fā)器應(yīng)該響應(yīng)的活動(dòng)（DELETE、INSERT或者UPDATE）。

由于觸發(fā)器只能響應(yīng)特定的三種類型的操作，因此可創(chuàng)建的觸發(fā)器也就三種類型:INSERT觸發(fā)器，DELETE觸發(fā)器以及UPDATE觸發(fā)器。

1、INSERT觸發(fā)器

在執(zhí)行INSERT觸發(fā)器時(shí)，也這樣幾點(diǎn)需要注意：

• 在INSERT觸發(fā)器代碼內(nèi)，可以引用一個(gè)名為NEW的虛擬表，可以用NEW來(lái)訪問(wèn)剛插入的行數(shù)據(jù)；
• 在BEFORE INSERT觸發(fā)器中，NEW中的值可以被更新；
• 對(duì)于AUTO_INCREMENT列，NEW在INSERT執(zhí)行之前包含0.在INSERT執(zhí)行之后包含新的自定生成值。

創(chuàng)建一個(gè)INSERT觸發(fā)器，每次插入一行數(shù)據(jù)，每次會(huì)返回當(dāng)前插入的行數(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; 

 

有這樣一些細(xì)節(jié)：

• 使用CREATE TRIGGER來(lái)創(chuàng)建觸發(fā)器；
• AFTER INSERT表明在插入行數(shù)據(jù)之后，觸發(fā)器才會(huì)執(zhí)行特征操作；
• FOR EACH ROW 表示對(duì)插入的每一行數(shù)據(jù)，觸發(fā)器都起作用；
• 針對(duì)INSERT觸發(fā)器，可以使用虛擬表NEW，來(lái)使用剛插入的行數(shù)據(jù)。比如例子中，SELECT NEW.cust_id INTO @newinsertid表示將新插入的行數(shù)據(jù)的id賦值給變量@newinsertid。

2、DELETE觸發(fā)器

• DELETE觸發(fā)器在DELETE語(yǔ)句執(zhí)行之前或者之后，需要知道以下兩點(diǎn)：
• 在DELETE觸發(fā)器代碼內(nèi)，可以引用一個(gè)名為OLD的虛擬表，來(lái)訪問(wèn)被刪除的行；
• OLD表中的數(shù)據(jù)只能讀，不能被更新，而在INSERT觸發(fā)器中，就可以通過(guò)NEW來(lái)更新被插入的行數(shù)據(jù)。

例如，針對(duì)customers表，當(dāng)刪除一行數(shù)據(jù)時(shí)，返回被刪除數(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 //  
 
/*調(diào)用DELETE觸發(fā)器*/  
DELETE FROM customers WHERE cust_id = 3;  
SELECT @deletecustname;  
SELECT @deletecustid; 

 

基本上與創(chuàng)建INSERT觸發(fā)器一樣，只不過(guò)在DELETE觸發(fā)器中只能使用OLD來(lái)訪問(wèn)被刪除的行數(shù)據(jù)。

3、UPDATE觸發(fā)器

UPDATE觸發(fā)器在UPDATE語(yǔ)句執(zhí)行之前或者之后執(zhí)行，需要知道一下幾點(diǎn)：

• 在BEFORE UPDATE觸發(fā)器中可以使用NEW和OLD來(lái)訪問(wèn)數(shù)據(jù)，而在AFTER UPDATE觸發(fā)器中使用NEW來(lái)訪問(wèn)數(shù)據(jù)會(huì)報(bào)錯(cuò)，只能使用OLD來(lái)訪問(wèn)數(shù)據(jù)；
• 在BEFORE UPDATE觸發(fā)器中，NEW中的值可以被改變，即允許更改將用于UPDATE的數(shù)據(jù)；
• OLD中的行數(shù)據(jù)只能讀，不能被更新。

一個(gè)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 //  
 
/*調(diào)用UPDATE觸發(fā)器*/  
UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5;  
SELECT @beforeupdate;  
SELECT @afterupdate; 

 

輸出為@beforeupdate為‘happay’，而@afterupdate為'reset_name'。有這樣一些細(xì)節(jié)：

• NEW虛擬表中的數(shù)據(jù)可以更改，如這里采用SET NEW.cust_name = 'reset_name';，將待更新的cust_name由“happy”變成了“reset_name”；
• 在BEFORE UPDATE觸發(fā)器中可以使用NEW和OLD來(lái)訪問(wèn)數(shù)據(jù)，而在AFTER UPDATE觸發(fā)器中使用NEW來(lái)訪問(wèn)數(shù)據(jù)會(huì)報(bào)錯(cuò)。

刪除觸發(fā)器？

• 刪除觸發(fā)器，可以使用 DROP TRIGGER語(yǔ)句，比如DROP TRIGGER insertcustomers。觸發(fā)器不能更新或者覆蓋，如果要修改觸發(fā)器，必須刪除這個(gè)觸發(fā)器。