拆解 MySQL 的高階使用與概念

Maximilian6 7年前發布 | 74K 次閱讀 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()	連接ID
DATEBASE()	當前數據庫
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語句，那么這些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)
BEGIN
DELETE FROM users WHERE id = p_id;
END

# 創建帶有IN和OUT類型參數的存儲過程(users為數據表名)
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

# 創建帶有多個OUT類型參數的存儲過程(users為數據表名)
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 存儲引擎

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/

本文由用戶 Maximilian6 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1496392285880.html

MySQL 數據庫服務器