目录
运算符
函数
字符函数
数值运算符和函数
比较运算符和函数
日期时间函数
信息函数
聚合函数
加密函数
CONCAT() - 字符连接
CONCAT_WS() - 使用指定的分隔符进行字符连接
FORMAT() - 数字格式化
LOWER() - 转换成小写字母
UPPER() - 转换成大写字母
LEFT() - 获取左侧字符
RIGHT() - 获取右侧字符
LENGTH() - 获取字符串长度
LTRIM() - 删除前导空格
RTRIM() - 删除后续空壳
TRIM() - 删除前导和后续空格
SUBSTRING() - 字符串截取
[NOT] LIKE - 模式匹配
%（百分号）：代表任意个字符
_（下划线）：代表任意一个字符
REPLACE() - 字符串替换
CEIL() - 进一取整
DIV - 整数除法
FLOOR() - 舍一取整
MOD - 取余数
POWER() - 幂运算
ROUND() - 四舍五入
TRUNCATE() - 数字截取
[NOT] BETWEEN…AND… - [不]在范围之内
[NOT] IN() - [不]在列出值范围内
IS [NOT] NULL - [不]为空
NOW() - 当前日期和时间
CURDATE() - 当前日期
CURTIME() - 当前时间
DATE_ADD() - 日期变化
DATEDIFF() - 日期差值
DATE_FORMAT() - 日期格式化
CONNECTION_ID() - 连接 ID
DATEBASE() - 当前数据库
LAST_INSERT_ID() - 最后插入记录的 ID
VERSION() - 最后插入记录的 ID 号
USER() - 当前用户
AVG() - 平均值
COUNT() - 计数
MAX() - 最大值
MIN() - 最小值
SUM() - 求和
MD5() - 信息摘要算法
PASSWORD() - 密码
自定义函数：用户自定义函数（user-defined function,UDF）是一种对 MySQL 扩展的途径，其用法与内置函数相同。
自定义函数的两个必要条件：
参数
返回值
函数可以接受任意类型的参数，也可以返回任意类型的值
函数的参数与返回值没有任何必然、内在的联系
创建自定义函数
CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} routine_bodyroutine_body - 函数体
函数体由合法的 SQL 语句构成
函数体可以是简单的 SELECT 或 INSERT 语句
函数体如果为复合结构则使用 BEGIN…END 语句
复合结构可以包含声明，循环，控制结构
create function f1() returns varchar(30) return date_format(now(),'%Y年%m月%d %H:%i:%s');执行 SELECT f1();，输出 2016年09月09 17:30:08
create function f2(num1 smallint unsigned,num2 smallint unsigned) returns float(10,2) unsigned return (num1+num2)/2;执行 SELECT f2(10,30);，输出 20.00
重定义 SQL 语句结束分隔符 - DELIMITER string
create function adduser(username varchar(20)) returns int unsigned begin
insert test(username) values(username); return last_insert_id(); end //
SQL 命令执行过程
SQL 命令 > MySQL 引擎 > 分析语法 > 可识别命令 > 执行 > 返回执行结果
存储过程是 SQL 语句和控制语句的预编译集合，以一个名称存储并作为一个单元处理，就是保存一条或多条 MySQL 语句的集合。
存储过程是业务逻辑和流程控制的集合，可以在执行过程中创建表，更新数据，删除数据等等。
增强 SQL 语句的功能和灵活性
实现较快的执行速度
减少网络流量
创建存储过程的语法结构
CREATE[DEFINER = {user | CURRENT_USER}]PROCEDURE sp_name([proc_parameter[,...]])[characteristic ...] routine_bodyproc_parameter:[IN | OUT | INOUT] param_name type
创建存储过程的参数含义
IN，表示该参数的值必须在调用存储过程时指定
OUT，表示该参数的值可以被存储过程改变，并且可以返回
INOUT，表示该参数在调用时指定，并且可以被改变和返回
创建存储过程的过程体
过程体由合法的 SQL 语句构成
过程体可以是任意 SQL 语句
过程体如果为复合结构则使用 BEGIN…END 语句
复合结构可以包含声明，循环，控制结构
SQL 语句 - CREATE PROCEDURE sp1() SELECT VERSION();
调用 - CALL sp1(); 或 CALL sp1
SQL 语句 - CREATE PROCEDURE removeUserById(IN user_id INT UNSIGNED) BEGIN DELETE FROM users WHERE id = user_id; END //
调用 - CALL removeUserById(22);
SQL 语句 - CREATE PROCEDURE removeUserAndReturnUserNums(IN user_id INT UNSIGNED,OUT user_nums INT UNSIGNED) BEGIN DELETE FROM users WHERE id = user_id; SELECT cout(id) FROM users INTO user_nums; END //
调用 - CALL removeUserAndReturnUserNums(27,@user_nums); SELECT @user_nums
@name 声明用户变量，只能是当前用户使用
SQL 语句 - CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN user_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED,OUT user_counts SMALLINT UNSIGNED) BEGIN DELETE FROM users WHERE age = user_age; SELECT ROW_COUNT() INTO deleteUsers; SELECT COUNT(id) FROM users INTO user_counts; END //
调用 - CALL removeUserByAgeAndReturnInfos(20,@a,@b)
存储过程实现的功能要复杂一些；而函数的针对性更强
存储过程可以返回多个值；函数只能有一个返回值
存储过程一般都是独立执行；而函数可以作为其他 SQL 语句的组成部分来出现。
简介：MySQL 可以将数据以不同的技术存储在文件（内存）中，这种技术就成为存储引擎。每一种存储引擎使用不同的存储机制、索引技巧、锁定水平，最终提供广泛且不同的功能。
类型：
MyISAM
InnoDB
Memory
CSV
Archive
并发控制：当多个连接对记录进行修改时保证数据的一致性和完整性
锁
概念：当多个查询同一时刻进行数据查询或修改时，就会产生并发控制的问题。
共享锁（读锁）：在同一时间段内，多个用户可以读取同一个资源，读取过程中数据不会发生任何变化。
排它锁（写锁）：在任何时候只能有一个用户写入资源，当进行写锁时会阻塞其他的读锁或者写锁操作。
锁颗粒
表锁，是一种开销最小的锁策略，会锁定整张表，MyISAM 使用表锁。
行锁，是一种开销最大的锁策略，最大程度地支持并发处理，InnoDB 使用行锁。
特性/引擎
MyISAM
Aria
InnoDB
XtraDB
Memory
Archive
数据存储
传统顺序数据储存
传统顺序数据储存
表空间存储方式
表空间存储方式
事务支持
不支持
支持
支持
支持
不支持
不支持
外键
不支持
不支持
支持
支持
不支持
不支持
全文检索
支持
支持
5.6 之后支持
5.6 之后支持
锁级别
表级锁
表级锁
行级锁
行级锁
表级锁
行级锁
Count 速度
快
快
慢
慢
适合业务
读多写少、单表数据量小于 1 KW
读多写少、单表数据量小于 1 KW
读写均衡、数据量不限
读写均衡、数据量不限
可用版本
MySQL、MariaDB、Percona
MariaDB
MySQL、MariaDB、Percona
MariaDB、Percona
其他说明
传统顺序索引数据库，适合读多写少小数据量业务
MyISAM 增强版，性能更好
适合高压力高性能的业务模型
InnoDB 增强版
修改搜索引擎的方法，主要有两种：
通过修改 MySQL 配置文件实现：default-storage-engine = engine
通过创建数据表命令实现
CREATE TABLE table_name(...,...) ENGINE = engine;
通过修改数据表命令实现
ALTER TABLE table_name ENGINE [=] engine_name;
PHPMyAdmin
Navicat for MySQL
MySQL Workbench
Sequel Pro
索引（Index）是帮助 MySQL 高效获取数据的数据结构
普通索引：最基本的索引，没有任何约束限制
唯一索引：类似普通索引，但具有唯一性约束
主键索引：特殊唯一索引，不允许列存在空值
复合索引：将多个列组合在一起创建索引，可以覆盖多个列
全文索引：只有 MyISAM 类型的表才能使用全文索引，并且只能对英文进行全文检索。
外键索引：只有 InnoDB 类型的表才能使用外键索引，保证数据的一致性、完整性和实现级联操作。
区别
一张表只能有一个主键索引，可以有多个唯一索引。
主键索引一定是唯一索引，唯一索引不是主键索引。
主键可以与外键构成参照完整性约束，防止数据不一致。
大大减少服务器需要扫描的数据量
帮助服务器避免排序和临时表
将随机 I/O 变顺序 I/O
大大提高查询速度，降低写的速度，占用硬件存储空间
对于非常小的数据表，大部分情况下全表扫描效率更高
对于中大型的数据表，使用索引非常有效
对于特大型的数据表，建立和使用索引的代价将随之增长，可以使用分区技术来解决
最适合索引的列是出现在 WHERE 子句中的列，或是 JION 子句中的列，而不是出现在 SELECT 关键字后的列。
索引列的基数越大，索引的效果越好。
对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间。
根据情况创建复合索引，复合索引可以提高查询效率。
避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率。
主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用，提高索引效率。
复合索引遵循最左前缀原则。
创建复合索引 KEY(a, b, c)
索引生效的条件查询如下：
WHERE a = 1 and b = 2 and c = 3WHERE a = 1 and b = 2WHERE a = 1
索引无效的条件查询如下：
WHERE b = 2 and c = 3WHERE a = 1 and c = 3
LIKE 查询时，% 不能在前，否则 MySQL 会使用全文索引。
WHERE name LIKE "%wange%"，name 索引无效。WHERE name lIKE "wang%"，name 索引生效。
column is null 会使用索引
如果 MySQL 估计使用索引比全表扫描更慢，会放弃使用索引。
WHERE id > 2 and id < 100
如果 OR 前的条件中的列有索引，OR 后的条件中的列没有索引，索引都不会生效。
列类型是字符串型，查询时一定要给值加引号，否则索引失效。
Newer
MySQL 5.7 版本新特性
Older
与 MySQL 的零距离接触（一）
Home
Archives