Mysql函数总结

NULL值处理

ifnull

如果为null，则返回0 ：

select ifnull(null, 0);

ifnull 函数：

如果参数1 为null，则返回参数2，否则返回本身

如果参数1 表达式有问题，也返回参数2： select IFNULL(1/0,'yes'); -> yes

应用场景：如果在进行右连接或者左连接时，有一些为空的字段，可以进行这样的处理

select ifnull( (B.submission_time-A.submission_time),0) from A right join B on 条件。

if + isnull

select if(isnull(null), 0, 1);

isnull函数：

判断值是否是null，如果是null，则返回1（代表true），否则返回0（代表false）

if函数：

当参数1为true时，返回参数2， 否则返回参数3

注意：

select if(0.1, 1, 0); -> 0
# 参数1应该写一个表达式，再mysql中，0代表false， 大于0 代表true
# 0.1 会被mysql 自动返回0，即false，所以返回 0

case when

SELECT CASE WHEN 1>0 THEN "true" ELSE "false" END; -> false

数学运算符和函数

算数运算符

# 加
select 1 + 2; # 3
# 减
select 1 - 2; # -1
# 一元减
select -2; # -2
# 乘
select 1 * 2; # 0
# 除
select 1 / 2; # 0.5000
# 除0为null
select 1 / 0; # null
# 取整
select 5 div 2;

数学函数

# 当数学函数出现错误，会返回null

# 绝对值
select abs(-1.1);
# 返回参数的符号，0为0 负数为-1 正数为1
select sign(-0.4);
# 取模运算 即 %操作
select mod(-30, 4);

################## 取整、保留小数操作
# floor
select floor(1.6); # 1
select floor(-1.6); # -2
# ceiling
select ceiling(1.4); # 2
select ceiling(-1.4); # -1
# round 四舍五入,保留d位小数，d默认为0
select round(-1.23); # -1
select round(1.23); # 1
select round(1.25, 1); # 1.3
# 保留n位小数
select truncate(1.223,1); # 1.2
select truncate(1.99, 1); # 1.9
select truncate(1.99, 0); # 1
###################

###################最大值、最小值
select least(2,0); # 最小值 0
select greatest(34.0,3.0,5.0,767.0); # 最大值 767.0
###################

################## 对数、幂数
# 以m为底n的对数
select log(3, 9); # 以3为底 9的对数 -> 2
# 以2为底n的对数
select log2(4); # 2
# 以10为底n的对数
select log10(100); # 2
# 以e为底n的对数
select log(10); # 2.302585092994046
# x的y次幂
select pow(2, 3); # 8
# 获取n的平方根，n为非负数
select sqrt(4); # 2
###################

################### 三角函数
# 圆周率π
select pi(); # 3.141593
# 复习：180°是一个π 30°是 π/6, 90°是 π/2

# 弧度转换角度
select degrees(pi() / 2); # 90
# 角度转换弧度
select radians(90); # 1.5707963267948966 即 π/2

# 正弦
select sin(pi() / 2); # 1 sin(90°)为1
# 余弦
select cos(pi() / 2); # 0.00000000000000006123233995736766 浮点运算问题 近似0 cos(90°) = 0
# 正切
select tan(pi() + 1); # 1.5574077246549018
# 余切
select cot(12); # -1.57267341

# 反正弦
select asin(1); # 1.5707963267948966
# 反余弦
select acos(0); # 1.5707963267948966
# 反正切
select atan(2); # 1.1071487177940904

###################

################### 随机数
select rand(); # 随机生成[0,1]之间的数字
select rand(1);# 设置随机数种子为1,种子相同，随机数也相同
###################

时间日期函数

函数列表

# 时间日期函数

# 获取当前时间，格式'yyyy-mm-dd hh:mm:ss'
select now(); # 2018-08-21 03:25:04
select sysdate(); # 2018-08-21 03:24:52
select current_timestamp(); # 2018-08-21 03:24:18

# 获取当前日期
select curdate();
select current_date(); # 2018-08-21

# 获取当前时间
select curtime();
select current_time(); # 03:23:45

# 获取时间戳
select unix_timestamp();
select unix_timestamp('1997-10-04 22:23:00');

# 日期格式化
select date_format(curdate(), '%Y年%c月%d日');

# 时间格式化
select time_format(curdate(), '%H时%i分%s秒');

# 时间戳格式化
select from_unixtime(875996580); # '1997-10-04 22:23:00'
select from_unixtime(875996580, '%y年%c月'); # 97年10月

# 获取具体的时间

# 返回周几
select dayofweek(now()); # (1=星期天,2=星期一,……7=星期六,odbc标准)
select weekday(now()); # 0=星期一,1=星期二,……6= 星期天

# 返回几号（一个月中的第几天）
select day(now());
select dayofmonth(now());

# 返回一年中的第几天
select dayofyear(now());

# 返回一年中的第几周
week(date,first)：
返回date是一年的第几周(first默认值0,first取值1表示周一是
周的开始,0从周日开始)
select week(now(), 0);
select weekofyear(now()); # 范围是从1到53

# 返回几月
select month(now()); # 1月返回1 2月返回2 ...

# 返回当前月份的名称 英文
select monthname(now()); # August

# 返回季度
select quarter(now()); # 3

# 返回年份
select year(now());

# 获取小时
select hour(now());

# 获取分钟
select min(now());

# 获取秒
select second(now());

日期格式化参数

file

自定义函数

用户自定义函数 (user-defined function, UDF)，是对Mysql扩展的一种途径，用法与内置函数相同。

函数的参数和返回值可以是Mysql任意的列类型，并且他们之间没有必然的内在的联系，并且参数个数理论上不可以超过1024个。

创建函数

create function function_name
returns
{string|integer|real|decimal}
routine_body

1. 函数体由合法的SQL语句构成

2. 函数体可以是简单的select或者insert语句

SQL与java函数优先级

mysql函数可做处理，业务层可以做处理，优先使用哪一个？

1. mysql函数肯定影响查询速度。（合理的表结构会减少不必要的函数使用，比如邮箱分开存储）

2. 当需要函数时，比如时间格式化，优先放在业务逻辑层处理

3. 在查询时使用函数，那么A列的索引将会无法使用，如果针对某列查询，而此列使用了函数， 那么此列将不会使用索引