拆解 MySQL 的高階使用與概念
前面我們主要分享了MySQL中的常見知識(shí)與使用。這里我們主要分享一下MySQL中的高階使用，主要包括：函數(shù)、存儲(chǔ)過程和存儲(chǔ)引擎。
1 函數(shù)
函數(shù)可以返回任意類型的值，也可以接收這些類型的參數(shù)。
字符函數(shù)
函數(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ù)
比較運(yùn)算符函數(shù)
日期時(shí)間函數(shù)
- # 時(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)查看手冊
信息函數(shù)
聚合函數(shù)
加密函數(shù)
自定義函數(shù)
用戶自定義函數(shù)(user-defined function，UDF)是一種對MySQL擴(kuò)展的途徑，其用法與內(nèi)置函數(shù)相同。UDF是對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語句構(gòu)成;
- 函數(shù)體可以是簡單的SELECT或INSERT語句;
- 函數(shù)體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語句;
- 復(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ǔ)過程
存儲(chǔ)過程是SQL語句和控制語句的預(yù)編譯集合，以一個(gè)名稱存儲(chǔ)作為一個(gè)單元處理?？梢杂捎脩粽{(diào)用執(zhí)行，允許用戶聲明變量以及進(jìn)行流程控制。存儲(chǔ)過程可以接收輸入類型的參數(shù)，也可以接收輸出類型的參數(shù)，并可以存在多個(gè)返回值。執(zhí)行效率比單一的SQL語句高。
優(yōu)點(diǎn)
- 增強(qiáng)SQL語句的功能和靈活性
在存儲(chǔ)過程中可以寫控制語句具有很強(qiáng)的靈活性，可以完成復(fù)雜的判斷及較復(fù)雜的運(yùn)算。
- 實(shí)現(xiàn)較快的執(zhí)行速度
如果某一操作包含了大量的SQL語句，那么這些SQL語句都將被MySQL引擎執(zhí)行語法分析、編譯、執(zhí)行，所以效率相對過低。而存儲(chǔ)過程是預(yù)編譯的，當(dāng)客戶端***次調(diào)用存儲(chǔ)過程時(shí)，MySQL的引擎將對它進(jìn)行語法分析、編譯等操作，然后把這個(gè)編譯的結(jié)果存儲(chǔ)到內(nèi)存中，所以說***次使用的時(shí)候效率和以前是相同的。但是以后客戶端再次調(diào)用這個(gè)存儲(chǔ)過程時(shí)，直接從內(nèi)存中執(zhí)行，所以說效率比較高，速度比較快。
- 減少網(wǎng)絡(luò)流量
如果通過客戶端每一個(gè)單獨(dú)發(fā)送SQL語句讓服務(wù)器來執(zhí)行，那么通過http協(xié)議來提交的數(shù)據(jù)量相對來說較大。
創(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ǔ)過程時(shí)指定。
OUT ，表示該參數(shù)值可以被存儲(chǔ)過程改變，并且可以返回。
INOUT ，表示該參數(shù)的調(diào)用時(shí)指定，并且可以被改變和返回。
特性：
COMMENT 注釋
CONTAINS SQL 包含SQL語句，但不包含讀或?qū)憯?shù)據(jù)的語句。
NO SQL 不包含SQL語句。
READS SQL DATA 包含讀寫數(shù)據(jù)的語句。
MODIFIES SQL DATA 包含寫數(shù)據(jù)的語句。
SQL SECURITY {DEFINER | INVOKER} 指明誰有權(quán)限來執(zhí)行。
過程體
- 過程體由合法的SQL語句構(gòu)成;
- 過程體可以是任意SQL語句;
- 不能通過存儲(chǔ)過程來創(chuàng)建數(shù)據(jù)表、數(shù)據(jù)庫?？梢酝ㄟ^存儲(chǔ)過程對數(shù)據(jù)進(jìn)行增、刪、改、查和多表連接操作。
- 過程體如果為復(fù)合結(jié)構(gòu)則使用BEGIN…END語句;
- 復(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ǔ)過程與自定義函數(shù)的區(qū)別
- 存儲(chǔ)過程實(shí)現(xiàn)的功能要復(fù)雜一些，而函數(shù)的針對性更強(qiáng)。
- 存儲(chǔ)過程可以返回多個(gè)值，函數(shù)只能有一個(gè)返回值。
- 存儲(chǔ)過程一般獨(dú)立執(zhí)行，函數(shù)可以作為其他SQL語句的組成部分來實(shí)現(xiàn)。
示例：
- # 創(chuàng)建不帶參數(shù)的存儲(chǔ)過程
- CREATE PROCEDURE sp1() SELECT VERSION();
- # 創(chuàng)建帶有IN類型參數(shù)的存儲(chǔ)過程(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類型參數(shù)的存儲(chǔ)過程(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類型參數(shù)的存儲(chǔ)過程(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ǔ)引擎。
每一種存儲(chǔ)引擎使用不同的存儲(chǔ)機(jī)制、索引技巧、鎖定水平，最終提供廣泛且不同的功能。
- 鎖
共享鎖(讀鎖)：在同一時(shí)間段內(nèi)，多個(gè)用戶可以讀取同一個(gè)資源，讀取過程中數(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ù)庫的完整性。
- 舉例：用戶銀行轉(zhuǎn)賬
- 用戶A 轉(zhuǎn)賬200元 用戶B
實(shí)現(xiàn)步驟：
1)從當(dāng)前賬戶減掉200元(賬戶余額大于等于200元)。
2)在對方賬戶增加200元。
事務(wù)特性：
1)原子性(atomicity)
2)一致性(consistency)
3)隔離性(isolation)
4)持久性(durability)
- 外鍵
是保證數(shù)據(jù)一致性的策略。
- 索引
是對數(shù)據(jù)表中一列或多列的值進(jìn)行排序的一種結(jié)構(gòu)。
類型
MySQL主要支持以下幾種引擎類型：
- MyISAM
- InnoDB
- Memory
- CSV
- Archive
各類存儲(chǔ)引擎特點(diǎn)
CSV:實(shí)際上是由逗號(hào)分隔的數(shù)據(jù)引擎，在數(shù)據(jù)庫子目錄為每一個(gè)表創(chuàng)建一個(gè) .csv 的文件，這是一種普通的文本文件，每一個(gè)數(shù)據(jù)行占用一個(gè)文本行。不支持索引。
BlackHole：黑洞引擎，寫入的數(shù)據(jù)都會(huì)消失，一般用于做數(shù)據(jù)復(fù)制的中繼。
MyISAM：適用于事務(wù)的處理不多的情況。
InnoDB：適用于事務(wù)處理比較多，需要有外鍵支持的情況。
索引分類：普通索引、唯一索引、全文索引、btree索引、hash索引…
修改存儲(chǔ)引擎
- 通過修改MySQL配置文件
- default-storage-engine=engine_name
- 通過創(chuàng)建數(shù)據(jù)表命令實(shí)現(xiàn)
- CREATE TABLE table_name(...)ENGINE=engine_name
- 通過修改數(shù)據(jù)表命令實(shí)現(xiàn)
- ALTER TABLE table_name ENGINE[=]engine_name
4 管理工具
- phpMyAdmin
需要有PHP環(huán)境
- Navicat
- MySQL Workbench
