拆解 MySQL 的高階使用與概念
前面我們主要分享了MySQL中的常見知識與使用。這里我們主要分享一下MySQL中的高階使用，主要包括：函數、存儲過程和存儲引擎。
對于MySQL中的基礎知識，可以參見
《與 MySQL 的零距離接觸》
1 函數函數可以返回任意類型的值，也可以接收這些類型的參數。
字符函數 函數名稱 描述CONCAT() 字符連接CONCAT_WS() 使用指定的分隔符進行字符連接FORMAT() 數字格式化LOWER() 轉換成小寫字母UPPER() 轉換成大寫字母LEFT() 獲取左側字符RIGHT() 獲取右側字符LENGTH() 獲取字符串長度LTRIM() 刪除前導空格RTRIM() 刪除后續空格TRIM() 刪除前導和后續空格SUBSTRING() 字符串截取[NOT] LIKE 模式匹配REPLACE() 字符串替換函數可以嵌套使用。
% （百分號）：代表任意個字符。
_ （下劃線）：代表任意一個字符。
# 刪除前導’?’符號SELECT TRIM(LEADING ’?’ FROM ’??MySQL???’);# 刪除后續’?’符號SELECT TRIM(TRAILING ’?’ FROM ’??MySQL???’);# 刪除前后’?’符號SELECT TRIM(BOTH ’?’ FROM ’??My??SQL???’);# 將’?’符號替換成’!’符號SELECT REPLACE(’??My??SQL???’, ’?’, ’!’);# 從中’MySQL’第1個開始，截取2個字符SELECT SUBSTRING(’MySQL’, 1, 2);# 從中’MySQL’截取最后1個字符SELECT SUBSTRING(’MySQL’, -1);# 從中’MySQL’第2個開始，截取至結尾SELECT SUBSTRING(’MySQL’, 2); 數值運算符函數 函數名稱 描述CEIL() 進一取整DIV 整數除法FLOOR() 舍一取整MOD 取余數（取模）POWER() 冪運算ROUND() 四舍五入TRUNCATE() 數字截取 比較運算符函數 函數名稱 描述[NOT]BETWEEN…AND.. [不]在范圍之內[NOT]IN() [不]在列出值范圍內IS[NOT]NULL [不]為空 日期時間函數 函數名稱 描述NOW() 當前日期和時間CURDATE() 當前日期CURTIME() 當前時間DATE_ADD() 日期變化DATEDIFF() 日期差值DATE_FORMAT() 日期格式化
# 時間增加1年SELECT DATE_ADD(’2016-05-28’, INTERVAL 365 DAY);# 時間減少1年SELECT DATE_ADD(’2016-05-28’, INTERVAL -365 DAY);# 時間增加3周SELECT DATE_ADD(’2016-05-28’, INTERVAL 3 WEEK);# 日期格式化SELECT DATE_FORMAT(’2016-05-28’, ’%m/%d/%Y’);# 更多時間格式可以前往MySQL官網查看手冊 信息函數 函數名稱 描述CONNECTION_ID() 連接IDDATEBASE() 當前數據庫LAST_INSERT_ID() 最后插入記錄的ID號USER() 當前用戶VERSION() 版本信息 聚合函數 函數名稱 描述AVG() 平均值COUNT() 計數MAX() 最大值MIN() 最小值SUM() 求和 加密函數 函數名稱 描述MD5() 信息摘要算法PASSWORD() 密碼算法 自定義函數
用戶自定義函數（user-defined function，UDF）是一種對MySQL擴展的途徑，其用法與內置函數相同。UDF是對MySQL擴展的一種途徑。
必要條件 參數：可以有零個或多個 返回值：只能有一個參數和返回值沒有必然的聯系。
創建自定義函數CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_body
函數體（routine_body）
函數體由合法的SQL語句構成； 函數體可以是簡單的SELECT或INSERT語句； 函數體如果為復合結構則使用BEGIN…END語句； 復合結構可以包含聲明，循環，控制結構。示例
# 不帶參數CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURN DATE_FORMAT(NOW(), ’%Y-%m-%d %H:%i:%s’);# 帶參數CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED) RETURNS FLOAT(10, 2) UNSIGNED RETURN (num1 + num2) / 2;# 具有復合結構函數體# 可能需要使用DELIMITER命令修改分隔符CREATE FUNCTION f3(username VARCHAR(20)) RETURNS INT UNSIGNED BEGIN INSERT test(username) VALUES(username);RETURN LAST_INSERT_ID();END 2 存儲過程
存儲過程是SQL語句和控制語句的預編譯集合，以一個名稱存儲作為一個單元處理。可以由用戶調用執行，允許用戶聲明變量以及進行流程控制。存儲過程可以接收輸入類型的參數，也可以接收輸出類型的參數，并可以存在多個返回值。執行效率比單一的SQL語句高。
在存儲過程中可以寫控制語句具有很強的靈活性，可以完成復雜的判斷及較復雜的運算。
實現較快的執行速度如果某一操作包含了大量的SQL語句，那么這些SQL語句都將被MySQL引擎執行語法分析、編譯、執行，所以效率相對過低。而存儲過程是預編譯的，當客戶端第一次調用存儲過程時，MySQL的引擎將對它進行語法分析、編譯等操作，然后把這個編譯的結果存儲到內存中，所以說第一次使用的時候效率和以前是相同的。但是以后客戶端再次調用這個存儲過程時，直接從內存中執行，所以說效率比較高，速度比較快。
減少網絡流量如果通過客戶端每一個單獨發送SQL語句讓服務器來執行，那么通過http協議來提交的數據量相對來說較大。
創建CREATE [DEFINER = {user|CURRENT_USER}] PROCEDURE sp_name ([proc_parameter[, ...]]) [characteristic ...] routine_body
proc_parameter :
[IN | OUT | INOUT] param_name type
參數：
IN ，表示該參數的值必須在調用存儲過程時指定。
OUT ，表示該參數值可以被存儲過程改變，并且可以返回。
INOUT ，表示該參數的調用時指定，并且可以被改變和返回。
特性：
COMMENT 注釋
CONTAINS SQL 包含SQL語句，但不包含讀或寫數據的語句。
NO SQL 不包含SQL語句。
READS SQL DATA 包含讀寫數據的語句。
MODIFIES SQL DATA 包含寫數據的語句。
SQL SECURITY {DEFINER | INVOKER} 指明誰有權限來執行。
過程體 過程體由合法的SQL語句構成； 過程體可以是任意SQL語句； 不能通過存儲過程來創建數據表、數據庫。可以通過存儲過程對數據進行增、刪、改、查和多表連接操作。 過程體如果為復合結構則使用BEGIN…END語句； 復合結構中可以包含聲明、循環、控制結構。 調用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} 存儲過程與自定義函數的區別 存儲過程實現的功能要復雜一些，而函數的針對性更強。 存儲過程可以返回多個值，函數只能有一個返回值。 存儲過程一般獨立執行，函數可以作為其他SQL語句的組成部分來實現。
示例：
# 創建不帶參數的存儲過程CREATE PROCEDURE sp1() SELECT VERSION();# 創建帶有IN類型參數的存儲過程(users為數據表名)# 參數的名字不能和數據表中的記錄名字一樣CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;END# 創建帶有IN和OUT類型參數的存儲過程(users為數據表名)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# 創建帶有多個OUT類型參數的存儲過程(users為數據表名)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 存儲引擎
MySQL可以將數據以不同的技術存儲在文件（內存）中，這種技術就稱為存儲引擎。
每一種存儲引擎使用不同的存儲機制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。
鎖
共享鎖（讀鎖）：在同一時間段內，多個用戶可以讀取同一個資源，讀取過程中數據不會發生任何變化。
排他鎖（寫鎖）：在任何時候只能有一個用戶寫入資源，當進行寫鎖時會阻塞其他的讀鎖或者寫鎖操作。
鎖顆粒
表鎖：是一種開銷最小的鎖策略。
行鎖：是一種開銷最大的鎖策略。
并發控制
當多個連接記錄進行修改時保證數據的一致性和完整性。
事務
事務用于保證數據庫的完整性。
舉例：用戶銀行轉賬
用戶A 轉賬200元 用戶B
實現步驟：
1）從當前賬戶減掉200元（賬戶余額大于等于200元）。
2）在對方賬戶增加200元。
事務特性：
1）原子性（atomicity）
2）一致性（consistency）
3）隔離性（isolation）
4）持久性（durability）
外鍵
是保證數據一致性的策略。
索引
是對數據表中一列或多列的值進行排序的一種結構。
類型MySQL主要支持以下幾種引擎類型：
MyISAM InnoDB Memory CSV Archive各類存儲引擎特點
特點 MyISAM InnoDB Memory Archive存儲限制 256TB 64TB 有 無事務安全 - 支持 - -支持索引 支持 支持 支持 鎖顆粒 表鎖 行鎖 表鎖 行鎖數據壓縮 支持 - - 支持支持外鍵 - 支持 - -CSV:實際上是由逗號分隔的數據引擎，在數據庫子目錄為每一個表創建一個 .csv 的文件，這是一種普通的文本文件，每一個數據行占用一個文本行。不支持索引。
BlackHole：黑洞引擎，寫入的數據都會消失，一般用于做數據復制的中繼。
MyISAM：適用于事務的處理不多的情況。
InnoDB：適用于事務處理比較多，需要有外鍵支持的情況。
索引分類：普通索引、唯一索引、全文索引、btree索引、hash索引…
修改存儲引擎 通過修改MySQL配置文件 default-storage-engine=engine_name 通過創建數據表命令實現 CREATE TABLE table_name(...)ENGINE=engine_name 通過修改數據表命令實現 ALTER TABLE table_name ENGINE[=]engine_name 4 管理工具phpMyAdmin
需要有PHP環境
Navicat
MySQL Workbench來自：http://chars.tech/2017/05/29/mysql-advanced-study/
相關文章：
網公網安備