拆解MySQL的高阶使用与概念

拆解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)

示例

# 不带参数 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语句都将被MySQL引擎执行语法分析、编译、执行，所以效率相对过低。而存储过程是预编译的，当客户端第一次调用存储过程时，MySQL的引擎将对它进行语法分析、编译等操作，然后把这个编译的结果存储到内存中，所以说第一次使用的时候效率和以前是相同的。但是以后客户端再次调用这个存储过程时，直接从内存中执行，所以说效率比较高，速度比较快。

减少网络流量

创建

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语句;

不能通过存储过程来创建数据表、数据库。可以通过存储过程对数据进行增、删、改、查和多表连接操作。

调用

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主要支持以下几种引擎类型：

MyISAMInnoDBMemoryCSVArchive

特点MyISAMInnoDBMemoryArchive

存储限制 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环境

NavicatMySQL Workbench

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。