MySQL常用函数使用案例

日期转换

字符串转换Date

select STR_TO_DATE(now(),'%Y-%m-%d') from dual
执行结果：2021-01-11
select str_to_date(DATE_FORMAT('2020-12-23','%Y-%m-%d'),'%Y-%m-%d')
执行结果：2020-12-23

日期转换字符

date_format

select DATE_FORMAT(STR_TO_DATE('2020-12-22','%Y-%m-%d'),'%Y-%m-%d') from dual;
执行结果：2020-12-22

字符串转日期时分秒

SELECT DATE_FORMAT('2017-09-20 08:30:45', '%Y-%m-%d %H:%i:%S');
输出结果：
2017-09-20 08:30:45（日期Date格式）

SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%S');
输出结果：
2017-04-05 16:53:59（String型格式）

附日期格式如下：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)

当月最后一天

select last_day(NOW());

当月第一天

select date_add(date_add(last_day(now()),interval 1 day),interval -1 month);
select STR_TODATE(DATE_FORMAT(now(),'%Y-%m-01'),'%Y-%m-%d');

日期加减

date_add() date_sub()
date_add('某个日期时间',interval 1 时间种类名);

example
select date_add(@dt, interval 1 year); -- 加1年
select date_add(@dt, interval 1 month); -- 加1月

字符串处理

字符串截取

left(), right(), substring(), substring_index()
select left('example.com', 3)

字符串拼接

concat(n,n1,n2)
select concat('1','2','3') from dual;

if

IF(expr1,expr2,expr3)
If expr1 is TRUE (expr1 <> 0 and expr1 <> NULL), IF() returns expr2. Otherwise, it returns expr3.

mysql> SELECT IF(1>2,2,3);
        -> 3
mysql> SELECT IF(1<2,'yes','no');
        -> 'yes'
mysql> SELECT IF(STRCMP('test','test1'),'no','yes');
        -> 'no'

DAYOFYEAR(date)

Returns the day of the year for date, in the range 1 to 366.

mysql> SELECT DAYOFYEAR('2007-02-03');
        -> 34

curdate() now()

curdate() 查询当前日期格式 yyyy-MM-dd
now() 查询当前日期格式 yyyy-MM-dd HH:mm:ss

查询指定日期的当年第一天日期

SELECT DATE_SUB(curdate(),INTERVAL dayofyear(now())-1 DAY);

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

MySQL decode()的等同实现

在Oracle中使用decode方法可以轻松实现代码和值之间的转换，但是在MySQL中该如何实现类似功能呢？
MySQL中没有直接的方法可以使用，但是我们可以通过下面两种方法来实现：

1.case when then
如：Select title,case Emergency when 1 then '紧急'  else '普通' End  as  emergency   from already_sign
```
 case ifnull(T.C_CHECK_FLAG,'0')
            when '0' then 1
            when '1'  then 0
            else -1
            end     
```

2.if
如：select a.title,if(a.Emergency=1,'紧急','普通')emergency from already_sign a

trim

trim函数可以过滤指定的字符串：

完整格式：TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

简化格式：TRIM([remstr FROM] str)

返回字符串 str ，其中所有remstr前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH。remstr为可选项，在未指定情况下，可删除空格。

mysql> SELECT TRIM(' bar ');

    -> 'bar'

mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); --删除指定的首字符 x

    -> 'barxxx'

mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); --删除指定的首尾字符 x

    -> 'bar'

mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); --删除指定的尾字符 x

    -> 'barx'