sql优化相关之如何写出一条好sql

sql架构与存储引擎

Qps与Tps

​ TPS: 指服务器每秒处理的事务数 ，支持事务的存储引擎特有的一个性能指标

TPS = (事务提交+事务回滚）/运行时间

​ QPS:执行sql等待返回结果之间的等待时间

QPS=查询个数/运行时间

mysqlslap

MySQLSlap是从MySQL5.1.4版本就开始的官方提供的压力测试工具，能测试MySQL服务器的性能

MySQL逻辑架构

​ 连接层

MySQL启动时 MySQL自己会创建一个线程 每一个用户对MySQL进行连接时，服务器都会新建一个线程去处理这一个连接，每个线程都是独立的 但是如果有两个线程进行修改同一块内存时会引发数据同步问题

sql处理层

sql语句的解析，优化，缓存的查询 MySQL内置函数的实现等

1，如果是查询语句select，首先会查询缓存是否已经有对应的结果，有返回结果 没有的话进行下一步

2，解析查询，创建一个内部数据结构这个解析树用于解析SQL语法

3，优化sql语句，列如重写查询，决定表的读写顺序以及选择需要的查询等，这一阶段用户是可以查询的，用户可以针对的进行一个调优

sql缓存

show variables like ‘%query_cache_type%’

查询数据库缓存是否开启

sql转换器

将传输进来的sql语句转换成MySQL能看得懂的样子

sql优化器

能够优化一些MySQL中很明显得一些冗余代码 （列如 1=1），使得代码变得更加简单

mylsam存储引擎

文件结构

• frm //存储表结构，所有得引擎都具备
• myd //数据文件
• myi //索引文件

mysam特点

• 并发性与锁级别-表级锁
• 支持全文检索
• 支持数据压缩
• myisampack -d -f testmysam.myl 文件压缩指令

mysam适用场景

• 非事务型应用（数据仓库，报表，日志数据）
• 只读类应用
• 空间类应用（地图等应用 更好得计算函数）

innodb存储引擎

innodb是MySQL在5.5以后版本的默认数据库

innodb_file_per_table 属性 no代表独立表空间 off代表系统表空间 on的时候会创建一个.ibd文件 内容是（数据+索引）mysql5.6以前都是默认系统表空间

比较：

• 系统表空间无法简单的收缩文件大小（所以占用磁盘内存会越来越大）
• 独立表空间可以通过optimize table收缩系统文件
• 系统表空间会产生io瓶颈
• 独立表空间可以同时向多个文件刷新数据

（列如一张表有100w条数据占用磁盘内存 100000kb 当你把表中数据清除时 他在磁盘的占用内存还是 100000kb 无法收缩文件大小）

适用场景：

修改较多，事务操作较多的场景

CSV存储引擎

​ 结构

• ​ 数据以文本的方式存储在文件
• ​ .csv文件存储内容
• ​ .csm文件存储表的元数据如表的状态数据量等
• ​ .frm表结构

​ 特点

• 以csv格式进行数据存储 所有的列都不能为null 不支持索引 （不适合大表，不适合在线处理）
• 可以对数据文件直接编辑（保存文本文件内容）
• 修改文件以后运行FLUSH TABLES进行刷新
• 列的最后得有一个空行

Archive存储引擎

组成

以zlib对表数据进行压缩，磁盘i/o更少数据存储在ARZ为后缀的文件中

特点

• 只支持insert和select操作
• 只允许在自增ID列上加索引

使用场景

日志和数据采集应用

Memory 存储引擎

特点

• 文件系统存储特点
• 支持hash索引和btree索引
• 所有字段都是固定长度
• 不支持blog和text等大字段
• Memory存储引擎使用表级锁
• 最大大小由max_heap_table_size参数决定
• 数据存储到内存当中

使用场景

• hash索引用于查找或者是映射表

• 用于保存数据分析中产生的中间表

• 用于缓存周期性聚合数据的结果表
  注意：memory数据易丢失，所以要求数据可再生

Ferderated 存储引擎

特点

• 提供了访问远程MySQL服务器上表的方法
• 本地不存储数据，数据全部放到远程服务器上
• 本地需要保存表结构和远程服务器的连接信息

使用场景

• 偶尔的统计分析及手工查询

sql锁机制

MySQL中的锁

​ 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突的概率较高，并发低 常见表级锁的存储引擎有mylsam 表级锁更加适合以查询为主的场景

​ 行级锁: 开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度高，常见的行级锁存储引擎有innodb 更适合有修改操作的事务场景

myisam的表锁

读锁

加共享读锁 lock table 表名 read

测试

1. lock table testmysam READ 启动另外一个session select * from
   testmysam 可以查询
2. insert into testmysam value(2);
   update testmysam set id=2 where id=1;
   报错
3. 在另外一个session中
   insert into testmysam value(2); 等待
4. 在同一个session中
   insert into testdemo value(2,‘2’,‘3’); 报错
   select * from testdemo ; 报错
5. 在另外一个session中
   insert into testdemo value(2,‘2’,‘3’); 成功
6. 加索在同一个session 中 select s.* from testmysam s 报错
   lock table 表名 as 别名 read;
7. 查看 show status LIKE ‘table_locks_waited’ 表被锁过几次
8. UNLOCK TABLES 删除锁

写锁

加独占写锁 lock table 表名 write

测试

1. lock table testmysam WRITE
   在同一个session中
   insert testmysam value(3);
   delete from testmysam where id = 3
   select * from testmysam
2. 对不同的表操作（ 报错）
   select s.* from testmysam s
   insert into testdemo value(2,‘2’,‘3’);
3. 在其他session中 （ 等待）
   select * from testmysam

总结

• 对myisam表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求
• 对myisam表的读操作，不会阻塞但前session对表读，当对表进行修改会保存
• 一个session使用LOCK TABLE命令给表f加了读锁，这个session可以查询锁定表中的记录，但更新或访问其他表都会提示错误
• 另外一个session可以查询表中的记录，但更新就会出现锁等待
• 对myisam表的写操作，则会阻塞其他用户对同一表的读和写操作
• 对myisam表的写操作，当前session可以对本表做crud，但对其他表进行操作就会报错

innoDB行锁

行锁简介

共享锁又称：读锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读操作，但不允许其进行写操作，也不允许其他事务给这几行上排它锁，但允许上读锁

排他锁又称：写锁。当一个事务对某几个上写锁时，不允许其他事务写，但允许读，更不允许其他事务给这几行上任何锁，包括写锁。

语法：

上共享锁的写法：lock in share mode

列如：select * from 表 where 条件 lock in share mode

上排他锁的写法： for update

列如： select * from 表 where 条件 for update

注意：

1. 两个事务不能锁同一个索引
2. insert，delete，update 在事务中都会自动默认加上排他锁
3. 行锁必须有索引才能实现，否则会自动锁全表，那么就不是行锁了

测试：

1. BEGIN
   select * from testdemo where id =1 for update
   在另外一个session中
   update testdemo set c1 = ‘1’ where id = 2 成功
   update testdemo set c1 = ‘1’ where id = 1 等待

2. BEGIN
   update testdemo set c1 = ‘1’ where id = 1
   在另外一个session中
   update testdemo set c1 = ‘1’ where id = 1 等待

3. BEGIN

   update testdemo set c1 = ‘1’ where c1 = ‘1’ 在另外一个session中 update testdemo set c1 = ‘2’ where c1 = ‘2’ 等待

先来看下行锁
第一个session中
select * from testdemo where id =1 for update
第二个session
select * from testdemo where id =1 lock in share mode
回到第一个session UNLOCK TABLES 并不会解锁
使用commit 或者 begin或者ROLLBACK 才会解锁

再来看下表锁
lock table testdemo WRITE
使用commit， ROLLBACK 并不会解锁
使用UNLOCK TABLES 或者begin会解锁

sql事务

事务的特性

事务有4个属性：原子性，一致性，隔离性，持久性。简称为ACID特性

• 原子性：一组事务要么全部成功要么全部失败
• 一致性：事务前后的数据不可以丢失不可以不一致
• 隔离性：一个事务不能被另一个事务所干扰，即并发执行的各个事务之间不能互相干扰。
• 持久性：一个事务一旦提交 其对数据的改变就必须是永久性的

事务隔离级别

事务有四种隔离级别

• ​ 读未提交 可能会产生脏读问题
• ​ 读已提交 可能会产生不可重复读问题
• ​ 可重复读 可以对产生幻读问题
• ​ 可串行化

MySQL的默认隔离级别为可重复读

并发问题

• 脏读：事务a读取到了事务b更新的数据，然后b进行了回滚操作 导致a读到的数据并不准确
• 不可重复读： 事务A多次读取一个数据，事务b在事务a读取时进行了更新操作，但是由于事务a读取的还是未进行更新的数据，所以事务A读取的数据并不准确
• 幻读：事务A开启事务准备往表里插入一条数据，而事务B也一样

事务的语法

• 开启事务
  1. start transaction
  2. begin work
  3. begin
• 事务回滚 rollback
• 事务提交 commit
• 还原点 savepoint

总结

事务隔离级别为可重复读时，如果有索引（包括主键索引）的时候，以索引列为条件更新数据，会存在间隙锁间、行锁、页锁的问题，从而锁住一些行；如果没有索引，更新数据时会锁住整张表

事务隔离级别为串行化时，读写数据都会锁住整张表

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed**，它能够避免脏读取，而且具有较好的并发性能。**

sql业务设计

范式设计 三大范式

第一范式

数据库表中的所有字段都只具有单一属性

单一属性的列是由基本数据类型所构成的

设计出来的表都是简单的二维表

name-age列具有两个属性，一个name,一个 age不符合第一范式，把它拆分成两列

第二范式

要求表中只具有一个业务主键，也就是说符合第二范式的表不能存在非主键列只对部分主键的依赖关系

有两张表：订单表，产品表

一个订单有多个产品，所以订单的主键为【订单ID】和【产品ID】组成的联合主键，这样2个组件不符合第二范式，而且产品ID和订单ID没有强关联，故，把订单表进行拆分为订单表与订单与商品的中间表

第三范式

指每一个非非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上相处了非主键对主键的传递依赖

其中

客户编号 和订单编号管理 关联

客户姓名 和订单编号管理 关联

客户编号 和 客户姓名 关联

如果客户编号发生改变，用户姓名也会改变，这样不符合第三大范式，应该把客户姓名这一列删除

反范式设计

什么叫反范式化设计

• 反范式化是针对范式化而言得，在前面介绍了数据库设计得范式
• 所谓得反范式化就是为了性能和读取效率得考虑而适当得对数据库设计范式得要求进行违反
• 允许存在少量得冗余，换句话来说反范式化就是使用空间来换取时间

商品信息反范式设计

下面是范式设计的商品信息表

商品信息和分类信息经常一起查询，所以把分类信息也放到商品表里面，冗余存放

在线销售功能反范式

下面是在线手写功能的范式设计

范式化设计优缺点

优点：

可以尽量得减少数据冗余

范式化的更新操作比反范式化更快

范式化的表通常比反范式化的表更小

缺点：

对于查询需要对多个表进行关联

更难进行索引优化

反范式化设计优缺点

优点：

可以减少表的关联

可以更好的进行索引优化

缺点：

存在数据冗余及数据维护异常

对数据的修改需要更多的成本

物理设计

命名规范

数据库、表、字段的命名要遵守可读性原则

使用大小写来格式化的库对象名字以获得良好的可读性

例如：使用custAddress而不是custaddress来提高可读性。

数据库、表、字段的命名要遵守表意性原则

对象的名字应该能够描述它所表示的对象

例如：

对于表，表的名称应该能够体现表中存储的数据内容；对于存储过程

存储过程应该能够体现存储过程的功能。

数据库、表、字段的命名要遵守长名原则

尽可能少使用或者不使用缩写

存储引擎选择

数据类型选择

当一个列可以选择多种数据类型时

l 优先考虑数字类型

l 其次是日期、时间类型

l 最后是字符类型

l 对于相同级别的数据类型，应该优先选择占用空间小的数据类型

浮点类型

注意float 和double 是非精度类型，如果是和金额相关尽量用decimal

select sum(c1),sum(c2),sum(c3) from test_numberic

日期类型

面试经常问道 timestamp 类型 与 datetime区别

datetime类型在5.6中字段长度是5个字节

datetime类型在5.5中字段长度是8个字节

timestamp 和时区有关，而datetime****无关

insert into test_time VALUES(NOW(),NOW(),NOW());

set time_zone="-10:00"

sql慢查询

什么是慢查询

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。（也就是说 把查询慢的SQL写到一个日志中）

1.1.1. 慢查询基本配置

• slow_query_log 启动停止技术慢查询日志
• slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
• long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
• log_queries_not_using_indexes 是否记录未使用索引的SQL
• log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL

包括：

1. 查询语句
2. 数据修改语句
3. 已经回滚得SQL

实操：

通过下面命令查看下上面的配置：

show VARIABLES like ‘%slow_query_log%’

show VARIABLES like ‘%slow_query_log_file%’

show VARIABLES like ‘%long_query_time%’

show VARIABLES like ‘%log_queries_not_using_indexes%’

show VARIABLES like ‘log_output’

set global long_query_time=0; —默认10秒，这里为了演示方便设置为0

set GLOBAL slow_query_log = 1; --开启慢查询日志

set global log_output=‘FILE,TABLE’ --项目开发中日志只能记录在日志文件中，不能记表中

设置完成后，查询一些列表可以发现慢查询的日志文件里面有数据了。

1.1.1. 慢查询基本配置

• slow_query_log 启动停止技术慢查询日志
• slow_query_log_file 指定慢查询日志得存储路径及文件（默认和数据文件放一起）
• long_query_time 指定记录慢查询日志SQL执行时间得伐值（单位：秒，默认10秒）
• log_queries_not_using_indexes 是否记录未使用索引的SQL
• log_output 日志存放的地方【TABLE】【FILE】【FILE,TABLE】

配置了慢查询后，它会记录符合条件的SQL

包括：

• 查询语句
• 数据修改语句
• 已经回滚得SQL

实操：

通过下面命令查看下上面的配置：

• show VARIABLES like ‘%slow_query_log%’
• show VARIABLES like ‘%slow_query_log_file%’
• show VARIABLES like ‘%long_query_time%’
• show VARIABLES like ‘%log_queries_not_using_indexes%’
• show VARIABLES like ‘log_output’
• set global long_query_time=0; —默认10秒，这里为了演示方便设置为0
• set GLOBAL slow_query_log = 1; --开启慢查询日志
• set global log_output=‘FILE,TABLE’ --项目开发中日志只能记录在日志文件中，不能记表中

设置完成后，查询一些列表可以发现慢查询的日志文件里面有数据了。

Mysqldumpslow

常用的慢查询日志分析工具，汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所指定的顺序输出。

语法：

mysqldumpslow -s r -t 10 slow-mysql.log

-s order (c,t,l,r,at,al,ar)

​ c:总次数

​ t:总时间

​ l:锁的时间

​ r:总数据行

​ at,al,ar :t,l,r平均数 【例如：at = 总时间/总次数】

-t top 指定取前面几天作为结果输出

//运行指令

mysqldumpslow.pl -s t -t 10 D:\DESKTOP-2EKGEE5-slow.log

sql索引与执行计划

生活中的索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

可以得到索引的本质：索引是数据结构。

上面的理解比较抽象，举一个例子，平时看任何一本书，首先看到的都是目录，通过目录去查询书籍里面的内容会非常的迅速。

MySql中的索引

MySql中的索引其实也是这么一回事，我们可以在数据库中建立一系列的索引，比如创建主键的时候默认会创建主键索引，上图是一种BTREE的索引。每一个节点都是主键的ID

当我们通过ID来查询内容的时候，首先去查索引库，在到索引库后能快速的定位索引的具体位置。

索引的分类

**普通索引：**即一个索引只包含单个列，一个表可以有多个单列索引

**唯一索引：**索引列的值必须唯一，但允许有空值

**复合索引：**即一个索引包含多个列

聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。具体细节取决于不同的实现，InnoDB的聚簇索引其实就是在同一个结构中保存了B-Tree索引(技术上来说是B+Tree)和数据行。

**非聚簇索引：**不是聚簇索引，就是非聚簇索引

基础语法

查看索引

SHOW INDEX FROM table_name\G

创建索引

CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));

ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))

删除索引

DROP INDEX [indexName] ON mytable;

执行计划

什么是执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是

如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

执行计划的作用

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

执行计划的语法

执行计划的语法其实非常简单： 在SQL查询的前面加上EXPLAIN关键字就行。

比如：EXPLAIN select * from table1

重点的就是EXPLAIN后面你要分析的SQL语句

执行计划详解

通过EXPLAIN关键分析的结果由以下列组成，接下来挨个分析每一个列

1. ID列

ID列：描述select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序

根据ID的数值结果可以分成一下三种情况

• id相同：执行顺序由上至下 （按照表在sql语句中的排列顺序进行加载）
• id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行（如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行）
• id相同不同：同时存在（id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行）

2. select_type列

Select_type：查询的类型

用于区别：普通查询、联合查询、子查询等的复杂查询

类型如下

3.table列

显示这一行数据是关于那张表从那张表而来的

4.Type列

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

属性解析：

System：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计

Const：表示通过索引一次就找到了const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快

eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

Ref： 非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体

Range： 只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index：当查询的结果全为索引列的时候，虽然也是全部扫描，但是只查询的索引库，而没有去查询数据。

All： Full Table Scan，将遍历全表以找到匹配的行

5.possible_keys 与Key列

possible_keys:可能使用的索引

Key:实际使用的索引。如果为NULL，则没有使用索引

6.key_len

Key_len表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

sql优化

尽量全值匹配 ： 如果可以匹配条件越多越好

**最佳左前缀：**让索引不失效的一个策略，也就是使用索引时，第一个索引字段一定要存在，如果第一个索引字段不存在则会出现索引失效问题

不在索引列上做任何操作：（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描

**范围条件放最后：**中间有范围查询会导致后面的索引列全部失效

覆盖索引尽量用：尽量使用覆盖索引(只访问索引的而不去访问整张表数据的查询(索引列和查询列一致) )，减少select *

不等于要甚用：在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

**Null/Not 有影响：**null/not null对索引的可能影响

**Like查询要当心：**like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

**字符类型加引号：**字符串不加单引号索引失效

**OR改UNION效率高：**在使用or的时候尽量更换为union

sql函数相关拓展

一、数学函数

ABS(x) 返回x的绝对值
BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）
CEILING(x) 返回大于x的最小整数值
EXP(x) 返回值e（自然对数的底）的x次方
FLOOR(x) 返回小于x的最大整数值
GREATEST(x1,x2,…,xn)返回集合中最大的值
LEAST(x1,x2,…,xn) 返回集合中最小的值
LN(x) 返回x的自然对数
LOG(x,y)返回x的以y为底的对数
MOD(x,y) 返回x/y的模（余数）
PI()返回pi的值（圆周率）
RAND()返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
ROUND(x,y)返回参数x的四舍五入的有y位小数的值
SIGN(x) 返回代表数字x的符号的值
SQRT(x) 返回一个数的平方根
TRUNCATE(x,y) 返回数字x截短为y位小数的结果

二、聚合函数(常用于GROUP BY从句的SELECT查询中)

AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果

三、字符串函数

ASCII(char)返回字符的ASCII码值
BIT_LENGTH(str)返回字符串的比特长度
CONCAT(s1,s2…,sn)将s1,s2…,sn连接成字符串
CONCAT_WS(sep,s1,s2…,sn)将s1,s2…,sn连接成字符串，并用sep字符间隔
INSERT(str,x,y,instr) 将字符串str从第x位置开始，y个字符长的子串替换为字符串instr，返回结果
FIND_IN_SET(str,list)分析逗号分隔的list列表，如果发现str，返回str在list中的位置
LCASE(str)或LOWER(str) 返回将字符串str中所有字符改变为小写后的结果
LEFT(str,x)返回字符串str中最左边的x个字符
LENGTH(s)返回字符串str中的字符数
LTRIM(str) 从字符串str中切掉开头的空格
POSITION(substr,str) 返回子串substr在字符串str中第一次出现的位置
QUOTE(str) 用反斜杠转义str中的单引号
REPEAT(str,srchstr,rplcstr)返回字符串str重复x次的结果
REVERSE(str) 返回颠倒字符串str的结果
RIGHT(str,x) 返回字符串str中最右边的x个字符
RTRIM(str) 返回字符串str尾部的空格
STRCMP(s1,s2)比较字符串s1和s2
TRIM(str)去除字符串首部和尾部的所有空格
UCASE(str)或UPPER(str) 返回将字符串str中所有字符转变为大写后的结果

四、日期和时间函数

CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DATE_ADD(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DATE_SUB(date,INTERVAL int keyword)返回日期date加上间隔时间int的结果(int必须按照关键字进行格式化),如：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)
一些示例：
获取当前系统时间：SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT EXTRACT(YEAR_MONTH FROM CURRENT_DATE);
SELECT EXTRACT(DAY_SECOND FROM CURRENT_DATE);
SELECT EXTRACT(HOUR_MINUTE FROM CURRENT_DATE);
返回两个日期值之间的差值(月数)：SELECT PERIOD_DIFF(200302,199802);
在Mysql中计算年龄：
SELECT DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)),’%Y’)+0 AS age FROM employee;
这样，如果Brithday是未来的年月日的话，计算结果为0。
下面的SQL语句计算员工的绝对年龄，即当Birthday是未来的日期时，将得到负值。
SELECT DATE_FORMAT(NOW(), ‘%Y’) - DATE_FORMAT(birthday, ‘%Y’) -(DATE_FORMAT(NOW(), ‘00-%m-%d’) <DATE_FORMAT(birthday, ‘00-%m-%d’)) AS age from employee

五、加密函数

AES_ENCRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法加密后的结果，调用AES_ENCRYPT的结果是一个二进制字符串，以BLOB类型存储
AES_DECRYPT(str,key) 返回用密钥key对字符串str利用高级加密标准算法解密后的结果
DECODE(str,key) 使用key作为密钥解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函数，用关键词salt(一个可以惟一确定口令的字符串，就像钥匙一样)加密字符串str
ENCODE(str,key) 使用key作为密钥加密字符串str，调用ENCODE()的结果是一个二进制字符串，它以BLOB类型存储
MD5() 计算字符串str的MD5校验和
PASSWORD(str) 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
SHA() 计算字符串str的安全散列算法(SHA)校验和
示例：
SELECT ENCRYPT(‘root’,‘salt’);
SELECT ENCODE(‘xufeng’,‘key’);
SELECT DECODE(ENCODE(‘xufeng’,‘key’),‘key’);#加解密放在一起
SELECT AES_ENCRYPT(‘root’,‘key’);
SELECT AES_DECRYPT(AES_ENCRYPT(‘root’,‘key’),‘key’);
SELECT MD5(‘123456’);
SELECT SHA(‘123456’);

六、控制流函数

MySQL有4个函数是用来进行条件操作的，这些函数可以实现SQL的条件逻辑，允许开发者将一些应用程序业务逻辑转换到数据库后台。
MySQL控制流函数：
CASE WHEN[test1] THEN [result1]…ELSE [default] END如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]…ELSE [default]END 如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f) 如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) 如果arg1=arg2返回NULL；否则返回arg1
这些函数的第一个是IFNULL()，它有两个参数，并且对第一个参数进行判断。如果第一个参数不是NULL，函数就会向调用者返回第一个参数；如果是NULL,将返回第二个参数。
如：SELECT IFNULL(1,2), IFNULL(NULL,10),IFNULL(4*NULL,‘false’);
NULLIF()函数将会检验提供的两个参数是否相等，如果相等，则返回NULL，如果不相等，就返回第一个参数。
如：SELECT NULLIF(1,1),NULLIF(‘A’,‘B’),NULLIF(2+3,4+1);
和许多脚本语言提供的IF()函数一样，MySQL的IF()函数也可以建立一个简单的条件测试，这个函数有三个参数，第一个是要被判断的表达式，如果表达式为真，IF()将会返回第二个参数，如果为假，IF()将会返回第三个参数。
如：SELECTIF(1<10,2,3),IF(56>100,‘true’,‘false’);
IF()函数在只有两种可能结果时才适合使用。然而，在现实世界中，我们可能发现在条件测试中会需要多个分支。在这种情况下，MySQL提供了CASE函数，它和PHP及Perl语言的switch-case条件例程一样。
CASE函数的格式有些复杂，通常如下所示：
CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
…
WHEN [val n] THEN [result n]
ELSE [default result]
END
这里，第一个参数是要被判断的值或表达式，接下来的是一系列的WHEN-THEN块，每一块的第一个参数指定要比较的值，如果为真，就返回结果。所有的WHEN-THEN块将以ELSE块结束，当END结束了所有外部的CASE块时，如果前面的每一个块都不匹配就会返回ELSE块指定的默认结果。如果没有指定ELSE块，而且所有的WHEN-THEN比较都不是真，MySQL将会返回NULL。
CASE函数还有另外一种句法，有时使用起来非常方便，如下：
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
这种条件下，返回的结果取决于相应的条件测试是否为真。
示例：
mysql>SELECT CASE ‘green’
WHEN ‘red’ THEN ‘stop’
WHEN ‘green’ THEN ‘go’ END;
SELECT CASE 9 WHEN 1 THEN ‘a’ WHEN 2 THEN ‘b’ ELSE ‘N/A’ END;
SELECT CASE WHEN (2+2)=4 THEN ‘OK’ WHEN(2+2)<>4 THEN ‘not OK’ END ASSTATUS;
SELECT Name,IF((IsActive = 1),‘已激活’,‘未激活’) AS RESULT FROMUserLoginInfo;
SELECT fname,lname,(math+sci+lit) AS total,
CASE WHEN (math+sci+lit) < 50 THEN ‘D’
WHEN (math+sci+lit) BETWEEN 50 AND 150 THEN ‘C’
WHEN (math+sci+lit) BETWEEN 151 AND 250 THEN ‘B’
ELSE ‘A’ END
AS grade FROM marks;
SELECT IF(ENCRYPT(‘sue’,‘ts’)=upass,‘allow’,‘deny’) AS LoginResultFROM users WHERE uname = ‘sue’;#一个登陆验证

七、格式化函数

DATE_FORMAT(date,fmt) 依照字符串fmt格式化日期date值
FORMAT(x,y) 把x格式化为以逗号隔开的数字序列，y是结果的小数位数
INET_ATON(ip) 返回IP地址的数字表示
INET_NTOA(num) 返回数字所代表的IP地址
TIME_FORMAT(time,fmt) 依照字符串fmt格式化时间time值
其中最简单的是FORMAT()函数，它可以把大的数值格式化为以逗号间隔的易读的序列。
示例：
SELECT FORMAT(34234.34323432,3);
SELECT DATE_FORMAT(NOW(),’%W,%D %M %Y %r’);
SELECT DATE_FORMAT(NOW(),’%Y-%m-%d’);
SELECT DATE_FORMAT(19990330,’%Y-%m-%d’);
SELECT DATE_FORMAT(NOW(),’%h:%i %p’);
SELECT INET_ATON(‘10.122.89.47’);
SELECT INET_NTOA(175790383);

八、类型转化函数

为了进行数据类型转化，MySQL提供了CAST()函数，它可以把一个值转化为指定的数据类型。类型有：BINARY,CHAR,DATE,TIME,DATETIME,SIGNED,UNSIGNED
示例：
SELECT CAST(NOW() AS SIGNED INTEGER),CURDATE()+0;
SELECT ‘f’=BINARY ‘F’,‘f’=CAST(‘F’ AS BINARY);

九、系统信息函数

DATABASE() 返回当前数据库名
BENCHMARK(count,expr) 将表达式expr重复运行count次
CONNECTION_ID() 返回当前客户的连接ID
FOUND_ROWS() 返回最后一个SELECT查询进行检索的总行数
USER()或SYSTEM_USER() 返回当前登陆用户名
VERSION() 返回MySQL服务器的版本
示例：
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));#该例中,MySQL计算LOG(RAND()*PI())表达式9999999次。

本文章是作者在享学课堂学习MySQL相关内容时做出的笔记