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

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

《與 MySQL 的零距離接觸》

1 函數(shù)

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

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

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

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

_ （下劃線）：代表任意一個(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è)開始，截取2個(gè)字符SELECT SUBSTRING(’MySQL’, 1, 2);# 從中’MySQL’截取最后1個(gè)字符SELECT SUBSTRING(’MySQL’, -1);# 從中’MySQL’第2個(gè)開始，截取至結(jié)尾SELECT SUBSTRING(’MySQL’, 2); 數(shù)值運(yùn)算符函數(shù) 函數(shù)名稱 描述CEIL() 進(jìn)一取整DIV 整數(shù)除法FLOOR() 舍一取整MOD 取余數(shù)（取模）POWER() 冪運(yùn)算ROUND() 四舍五入TRUNCATE() 數(shù)字截取 比較運(yùn)算符函數(shù) 函數(shù)名稱 描述[NOT]BETWEEN…AND.. [不]在范圍之內(nèi)[NOT]IN() [不]在列出值范圍內(nèi)IS[NOT]NULL [不]為空 日期時(shí)間函數(shù) 函數(shù)名稱 描述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ù)名稱 描述CONNECTION_ID() 連接IDDATEBASE() 當(dāng)前數(shù)據(jù)庫(kù)LAST_INSERT_ID() 最后插入記錄的ID號(hào)USER() 當(dāng)前用戶VERSION() 版本信息 聚合函數(shù) 函數(shù)名稱 描述AVG() 平均值COUNT() 計(jì)數(shù)MAX() 最大值MIN() 最小值SUM() 求和 加密函數(shù) 函數(shù)名稱 描述MD5() 信息摘要算法PASSWORD() 密碼算法 自定義函數(shù)

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

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

參數(shù)和返回值沒有必然的聯(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ǔ)作為一個(gè)單元處理。可以由用戶調(diào)用執(zhí)行，允許用戶聲明變量以及進(jìn)行流程控制。存儲(chǔ)過(guò)程可以接收輸入類型的參數(shù)，也可以接收輸出類型的參數(shù)，并可以存在多個(gè)返回值。執(zhí)行效率比單一的SQL語(yǔ)句高。

優(yōu)點(diǎn) 增強(qiáng)SQL語(yǔ)句的功能和靈活性

在存儲(chǔ)過(guò)程中可以寫控制語(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)客戶端第一次調(diào)用存儲(chǔ)過(guò)程時(shí)，MySQL的引擎將對(duì)它進(jìn)行語(yǔ)法分析、編譯等操作，然后把這個(gè)編譯的結(jié)果存儲(chǔ)到內(nèi)存中，所以說(shuō)第一次使用的時(shí)候效率和以前是相同的。但是以后客戶端再次調(diào)用這個(gè)存儲(chǔ)過(guò)程時(shí)，直接從內(nèi)存中執(zhí)行，所以說(shuō)效率比較高，速度比較快。

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

如果通過(guò)客戶端每一個(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 包含讀寫數(shù)據(jù)的語(yǔ)句。

MODIFIES SQL DATA 包含寫數(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類型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名)# 參數(shù)的名字不能和數(shù)據(jù)表中的記錄名字一樣CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;END# 創(chuàng)建帶有IN和OUT類型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名)CREATE PROCEDURE removeUserAndReturnUserNumsById(IN p_id INT UNSIGNED, OUT userNums INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;SELECT COUNT(id) FROM users INTO userNums;END# 創(chuàng)建帶有多個(gè)OUT類型參數(shù)的存儲(chǔ)過(guò)程(users為數(shù)據(jù)表名)CREATE PROCEDURE removeUserAndReturnInfosByAge(IN p_age SMALLINT UNSIGNED, OUT delUser SMALLINT UNSIGNED, OUT userNums SMALLINT UNSIGNED)BEGINDELETE 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ǔ)引擎。

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

鎖

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

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

鎖顆粒

表鎖：是一種開銷最小的鎖策略。

行鎖：是一種開銷最大的鎖策略。

并發(fā)控制

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

事務(wù)

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

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

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

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

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

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

事務(wù)特性：

1）原子性（atomicity）

2）一致性（consistency）

3）隔離性（isolation）

4）持久性（durability）

外鍵

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

索引

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

類型

MySQL主要支持以下幾種引擎類型：

MyISAM InnoDB Memory CSV Archive

各類存儲(chǔ)引擎特點(diǎn)

特點(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：黑洞引擎，寫入的數(shù)據(jù)都會(huì)消失，一般用于做數(shù)據(jù)復(fù)制的中繼。

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

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

索引分類：普通索引、唯一索引、全文索引、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

來(lái)自：http://chars.tech/2017/05/29/mysql-advanced-study/