这里我们主要分享一下MySQL中的高阶使用,主要包括:函数、存储过程和存储引擎。
函数可以返回任意类型的值,也可以接收这些类型的参数。
字符函数
函数名称 | 描述 |
---|---|
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
# 不带参数
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
存储过程是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}
示例:
# 创建不带参数的存储过程
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
MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术就称为存储引擎。 每一种存储引擎使用不同的存储机制、索引技巧、锁定水平,最终提供广泛且不同的功能。
锁 共享锁(读锁):在同一时间段内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化。 排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒 表锁:是一种开销最小的锁策略。 行锁:是一种开销最大的锁策略。
并发控制 当多个连接记录进行修改时保证数据的一致性和完整性。
事务 事务用于保证数据库的完整性。
举例:用户银行转账
用户A
转账200元 用户B
实现步骤:
1)从当前账户减掉200元(账户余额大于等于200元)。
2)在对方账户增加200元。
事务特性:
1)原子性(atomicity) 2)一致性(consistency) 3)隔离性(isolation) 4)持久性(durability)
外键 是保证数据一致性的策略。
索引 是对数据表中一列或多列的值进行排序的一种结构。
MySQL主要支持以下几种引擎类型:
各类存储引擎特点
特点 | MyISAM | InnoDB | Memory | Archive |
---|---|---|---|---|
存储限制 | 256TB | 64TB | 有 | 无 |
事务安全 | - | 支持 | - | - |
支持索引 | 支持 | 支持 | 支持 | |
锁颗粒 | 表锁 | 行锁 | 表锁 | 行锁 |
数据压缩 | 支持 | - | - | 支持 |
支持外键 | - | 支持 | - | - |
CSV:实际上是由逗号分隔的数据引擎,在数据库子目录为每一个表创建一个.csv
的文件,这是一种普通的文本文件,每一个数据行占用一个文本行。不支持索引。
BlackHole:黑洞引擎,写入的数据都会消失,一般用于做数据复制的中继。
MyISAM:适用于事务的处理不多的情况。
InnoDB:适用于事务处理比较多,需要有外键支持的情况。
索引分类:普通索引、唯一索引、全文索引、btree索引、hash索引…
default-storage-engine=engine_name
CREATE TABLE table_name(...)ENGINE=engine_name
ALTER TABLE table_name ENGINE[=]engine_name