前面我們主要分享了MySQL中的常見(jiàn)知識(shí)與使用。這里我們主要分享一下MySQL中的高階使用，主要包括：函數(shù)、存儲(chǔ)過(guò)程和存儲(chǔ)引擎。

對(duì)于MySQL中的基礎(chǔ)知識(shí)，可以參見(jiàn)

《與 MySQL 的零距離接觸》

1. 函數(shù)

函數(shù)可以返回任意類(lèi)型的值，也可以接收這些類(lèi)型的參數(shù)。

字符函數(shù)

函數(shù)名稱(chēng)	描述
CONCAT()	字符連接
CONCAT_WS()	使用指定的分隔符進(jìn)行字符連接
FORMAT()	數(shù)字格式化
LOWER()	轉(zhuǎn)換成小寫(xiě)字母
UPPER()	轉(zhuǎn)換成大寫(xiě)字母
LEFT()	獲取左側(cè)字符
RIGHT()	獲取右側(cè)字符
LENGTH()	獲取字符串長(zhǎng)度
LTRIM()	刪除前導(dǎo)空格
RTRIM()	刪除后續(xù)空格
TRIM()	刪除前導(dǎo)和后續(xù)空格
SUBSTRING()	字符串截取
[NOT] LIKE	模式匹配
REPLACE()	字符串替換

函數(shù)可以嵌套使用。

% (百分號(hào))：代表任意個(gè)字符。

_ (下劃線(xiàn))：代表任意一個(gè)字符。

# 刪除前導(dǎo)'?'符號(hào) 
SELECT TRIM(LEADING '?' FROM '??MySQL???'); 
# 刪除后續(xù)'?'符號(hào) 
SELECT TRIM(TRAILING '?' FROM '??MySQL???'); 
# 刪除前后'?'符號(hào) 
SELECT TRIM(BOTH '?' FROM '??My??SQL???'); 
# 將'?'符號(hào)替換成'!'符號(hào) 
SELECT REPLACE('??My??SQL???', '?', '!'); 
# 從中'MySQL'第1個(gè)開(kāi)始，截取2個(gè)字符 
SELECT SUBSTRING('MySQL', 1, 2); 
# 從中'MySQL'截取最后1個(gè)字符 
SELECT SUBSTRING('MySQL', -1); 
# 從中'MySQL'第2個(gè)開(kāi)始，截取至結(jié)尾 
SELECT SUBSTRING('MySQL', 2);  

數(shù)值運(yùn)算符函數(shù)

函數(shù)名稱(chēng)	描述
CEIL()	進(jìn)一取整
DIV	整數(shù)除法
FLOOR()	舍一取整
MOD	取余數(shù)（取模）
POWER()	冪運(yùn)算
ROUND()	四舍五入
TRUNCATE()	數(shù)字截取

比較運(yùn)算符函數(shù)

函數(shù)名稱(chēng)	描述
[NOT]BETWEEN…AND..	[不]在范圍之內(nèi)
[NOT]IN()	[不]在列出值范圍內(nèi)
IS[NOT]NULL	[不]為空

日期時(shí)間函數(shù)

函數(shù)名稱(chēng)	描述
NOW()	當(dāng)前日期和時(shí)間
CURDATE()	當(dāng)前日期
CURTIME()	當(dāng)前時(shí)間
DATE_ADD()	日期變化
DATEDIFF()	日期差值
DATE_FORMAT()	日期格式化

  

# 時(shí)間增加1年 
SELECT DATE_ADD('2016-05-28', INTERVAL 365 DAY); 
# 時(shí)間減少1年 
SELECT DATE_ADD('2016-05-28', INTERVAL -365 DAY); 
# 時(shí)間增加3周 
SELECT DATE_ADD('2016-05-28', INTERVAL 3 WEEK); 
# 日期格式化 
SELECT DATE_FORMAT('2016-05-28', '%m/%d/%Y'); 
# 更多時(shí)間格式可以前往MySQL官網(wǎng)查看手冊(cè)  

信息函數(shù)

函數(shù)名稱(chēng)	描述
CONNECTION_ID()	連接ID
DATEBASE()	當(dāng)前數(shù)據(jù)庫(kù)
LAST_INSERT_ID()	最后插入記錄的ID號(hào)
USER()	當(dāng)前用戶(hù)
VERSION()	版本信息

聚合函數(shù)

函數(shù)名稱(chēng)	描述
AVG()	平均值
COUNT()	計(jì)數(shù)
MAX()	最大值
MIN()	最小值
SUM()	求和

加密函數(shù)

函數(shù)名稱(chēng)	描述
MD5()	信息摘要算法
PASSWORD()	密碼算法

自定義函數(shù)

用戶(hù)自定義函數(shù)(user-defined function，UDF)是一種對(duì)MySQL擴(kuò)展的途徑，其用法與內(nèi)置函數(shù)相同。UDF是對(duì)MySQL擴(kuò)展的一種途徑。

必要條件

• 參數(shù)：可以有零個(gè)或多個(gè)
• 返回值：只能有一個(gè)

參數(shù)和返回值沒(méi)有必然的聯(lián)系。

創(chuàng)建自定義函數(shù)

CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body 

函數(shù)體(routine_body)

• 函數(shù)體由合法的SQL語(yǔ)句構(gòu)成;
• 函數(shù)體可以是簡(jiǎn)單的SELECT或INSERT語(yǔ)句;
• 函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語(yǔ)句;
• 復(fù)合結(jié)構(gòu)可以包含聲明，循環(huán)，控制結(jié)構(gòu)。

示例

# 不帶參數(shù) 
CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s'); 
 
# 帶參數(shù) 
CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2) / 2; 
 
# 具有復(fù)合結(jié)構(gòu)函數(shù)體 
# 可能需要使用DELIMITER命令修改分隔符 
CREATE FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED  
BEGIN  
INSERT test(username) VALUES(username); 
RETURN LAST_INSERT_ID(); 
END  

2. 存儲(chǔ)過(guò)程  

 

存儲(chǔ)過(guò)程是SQL語(yǔ)句和控制語(yǔ)句的預(yù)編譯集合，以一個(gè)名稱(chēng)存儲(chǔ)作為一個(gè)單元處理?？梢杂捎脩?hù)調(diào)用執(zhí)行，允許用戶(hù)聲明變量以及進(jìn)行流程控制。存儲(chǔ)過(guò)程可以接收輸入類(lèi)型的參數(shù)，也可以接收輸出類(lèi)型的參數(shù)，并可以存在多個(gè)返回值。執(zhí)行效率比單一的SQL語(yǔ)句高。

優(yōu)點(diǎn)

• 增強(qiáng)SQL語(yǔ)句的功能和靈活性

在存儲(chǔ)過(guò)程中可以寫(xiě)控制語(yǔ)句具有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷及較復(fù)雜的運(yùn)算。

• 實(shí)現(xiàn)較快的執(zhí)行速度

如果某一操作包含了大量的SQL語(yǔ)句，那么這些SQL語(yǔ)句都將被MySQL引擎執(zhí)行語(yǔ)法分析、編譯、執(zhí)行，所以效率相對(duì)過(guò)低。而存儲(chǔ)過(guò)程是預(yù)編譯的，當(dāng)客戶(hù)端第一次調(diào)用存儲(chǔ)過(guò)程時(shí)，MySQL的引擎將對(duì)它進(jìn)行語(yǔ)法分析、編譯等操作，然后把這個(gè)編譯的結(jié)果存儲(chǔ)到內(nèi)存中，所以說(shuō)第一次使用的時(shí)候效率和以前是相同的。但是以后客戶(hù)端再次調(diào)用這個(gè)存儲(chǔ)過(guò)程時(shí)，直接從內(nèi)存中執(zhí)行，所以說(shuō)效率比較高，速度比較快。

• 減少網(wǎng)絡(luò)流量

如果通過(guò)客戶(hù)端每一個(gè)單獨(dú)發(fā)送SQL語(yǔ)句讓服務(wù)器來(lái)執(zhí)行，那么通過(guò)http協(xié)議來(lái)提交的數(shù)據(jù)量相對(duì)來(lái)說(shuō)較大。

創(chuàng)建

CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[, ...]]) [characteristic ...] routine_body 

proc_parameter :

[IN | OUT | INOUT] param_name type

參數(shù)：

IN ，表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過(guò)程時(shí)指定。

OUT ，表示該參數(shù)值可以被存儲(chǔ)過(guò)程改變，并且可以返回。

INOUT ，表示該參數(shù)的調(diào)用時(shí)指定，并且可以被改變和返回。

特性：

COMMENT 注釋

CONTAINS SQL 包含SQL語(yǔ)句，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句。

NO SQL 不包含SQL語(yǔ)句。

READS SQL DATA 包含讀寫(xiě)數(shù)據(jù)的語(yǔ)句。

MODIFIES SQL DATA 包含寫(xiě)數(shù)據(jù)的語(yǔ)句。

SQL SECURITY {DEFINER | INVOKER} 指明誰(shuí)有權(quán)限來(lái)執(zhí)行。

過(guò)程體

• 過(guò)程體由合法的SQL語(yǔ)句構(gòu)成;
• 過(guò)程體可以是任意SQL語(yǔ)句;

不能通過(guò)存儲(chǔ)過(guò)程來(lái)創(chuàng)建數(shù)據(jù)表、數(shù)據(jù)庫(kù)。可以通過(guò)存儲(chǔ)過(guò)程對(duì)數(shù)據(jù)進(jìn)行增、刪、改、查和多表連接操作。

• 過(guò)程體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語(yǔ)句;
• 復(fù)合結(jié)構(gòu)中可以包含聲明、循環(huán)、控制結(jié)構(gòu)。

調(diào)用

CALL sp_name ([parameter[, ...]]) 
 
CALL sp_name[()]  

刪除

DROP PROCEDURE [IF EXISTS] sp_name 

修改

ALTER PROCEDURE sp_name [characteristic ...] COMMENT 'string' 
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} 
| SQL SECURITY {DEFINER | INVOKER}  

存儲(chǔ)過(guò)程與自定義函數(shù)的區(qū)別

• 存儲(chǔ)過(guò)程實(shí)現(xiàn)的功能要復(fù)雜一些，而函數(shù)的針對(duì)性更強(qiáng)。
• 存儲(chǔ)過(guò)程可以返回多個(gè)值，函數(shù)只能有一個(gè)返回值。
• 存儲(chǔ)過(guò)程一般獨(dú)立執(zhí)行，函數(shù)可以作為其他SQL語(yǔ)句的組成部分來(lái)實(shí)現(xiàn)。

示例：

# 創(chuàng)建不帶參數(shù)的存儲(chǔ)過(guò)程 
CREATE PROCEDURE sp1() SELECT VERSION(); 
 
# 創(chuàng)建帶有IN類(lèi)型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名) 
# 參數(shù)的名字不能和數(shù)據(jù)表中的記錄名字一樣 
CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE id = p_id; 
END 
 
# 創(chuàng)建帶有IN和OUT類(lèi)型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名) 
CREATE PROCEDURE removeUserAndReturnUserNumsById(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE id = p_id; 
SELECT COUNT(id) FROM users INTO userNums; 
END 
 
# 創(chuàng)建帶有多個(gè)OUT類(lèi)型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名) 
CREATE PROCEDURE removeUserAndReturnInfosByAge(IN p_age SMALLINT UNSIGNED, OUT delUser SMALLINT UNSIGNED,  OUT userNums SMALLINT UNSIGNED) 
BEGIN 
DELETE FROM users WHERE age = p_age; 
SELECT ROW_COUNT INTO delUser; 
SELECT COUNT(id) FROM users INTO userNums; 
END  

3. 存儲(chǔ)引擎

MySQL可以將數(shù)據(jù)以不同的技術(shù)存儲(chǔ)在文件(內(nèi)存)中，這種技術(shù)就稱(chēng)為存儲(chǔ)引擎。

每一種存儲(chǔ)引擎使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。

• 鎖

共享鎖(讀鎖)：在同一時(shí)間段內(nèi)，多個(gè)用戶(hù)可以讀取同一個(gè)資源，讀取過(guò)程中數(shù)據(jù)不會(huì)發(fā)生任何變化。

排他鎖(寫(xiě)鎖)：在任何時(shí)候只能有一個(gè)用戶(hù)寫(xiě)入資源，當(dāng)進(jìn)行寫(xiě)鎖時(shí)會(huì)阻塞其他的讀鎖或者寫(xiě)鎖操作。

• 鎖顆粒

表鎖：是一種開(kāi)銷(xiāo)最小的鎖策略。

行鎖：是一種開(kāi)銷(xiāo)最大的鎖策略。

• 并發(fā)控制

當(dāng)多個(gè)連接記錄進(jìn)行修改時(shí)保證數(shù)據(jù)的一致性和完整性。

• 事務(wù)

事務(wù)用于保證數(shù)據(jù)庫(kù)的完整性。

舉例：用戶(hù)銀行轉(zhuǎn)賬

用戶(hù)A 轉(zhuǎn)賬200元 用戶(hù)B

實(shí)現(xiàn)步驟：

1)從當(dāng)前賬戶(hù)減掉200元(賬戶(hù)余額大于等于200元)。

2)在對(duì)方賬戶(hù)增加200元。

事務(wù)特性：

1)原子性(atomicity)

2)一致性(consistency)

3)隔離性(isolation)

4)持久性(durability)

• 外鍵

是保證數(shù)據(jù)一致性的策略。

• 索引

是對(duì)數(shù)據(jù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。

類(lèi)型

MySQL主要支持以下幾種引擎類(lèi)型：

• MyISAM
• InnoDB
• Memory
• CSV
• Archive

特點(diǎn)	MyISAM	InnoDB	Memory	Archive
存儲(chǔ)限制	256TB	64TB	有	無(wú)
事務(wù)安全	–	支持	–	–
支持索引	支持	支持	支持
鎖顆粒	表鎖	行鎖	表鎖	行鎖
數(shù)據(jù)壓縮	支持	–	–	支持
支持外鍵	–	支持	–	–

CSV:實(shí)際上是由逗號(hào)分隔的數(shù)據(jù)引擎，在數(shù)據(jù)庫(kù)子目錄為每一個(gè)表創(chuàng)建一個(gè) .csv 的文件，這是一種普通的文本文件，每一個(gè)數(shù)據(jù)行占用一個(gè)文本行。不支持索引。

BlackHole：黑洞引擎，寫(xiě)入的數(shù)據(jù)都會(huì)消失，一般用于做數(shù)據(jù)復(fù)制的中繼。

MyISAM：適用于事務(wù)的處理不多的情況。

InnoDB：適用于事務(wù)處理比較多，需要有外鍵支持的情況。

索引分類(lèi)：普通索引、唯一索引、全文索引、btree索引、hash索引…

修改存儲(chǔ)引擎

• 通過(guò)修改MySQL配置文件

default-storage-engine=engine_name

• 通過(guò)創(chuàng)建數(shù)據(jù)表命令實(shí)現(xiàn)

CREATE TABLE table_name(...)ENGINE=engine_name

• 通過(guò)修改數(shù)據(jù)表命令實(shí)現(xiàn)

ALTER TABLE table_name ENGINE[=]engine_name

4. 管理工具

• phpMyAdmin

需要有PHP環(huán)境

• Navicat
• MySQL Workbench