科技猎拆解 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() | 连接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
End.
转载请注明来自36大数据（36dsj.com)： 36大数据 » 拆解 MySQL 的高阶使用与概念