mysql函数用法

if()

select 
   i.type, 
   count(1) total,
   sum(if(i.status = 1, 1, 0)) passnum,
   sum(if(i.status != 1, 1, 0)) failnum 
from 
   info i group by i.type

case when else end
用于判断一些逻辑来确定记录的一个属性，且该属性不在数据库设置

select 
    case 
      when paymoney < 200 then 1 
      when 200 <= paymoney < 1000 then 2
      else  3 end type
from order_info

ifnull()

select concat(ifnull(firstname, ' '), ifnull(lastname), ' ') fullname from user

to_days()
查询某一天开始到最新的数据

select * from capture_log where cdate  > '2020-4-20 00:00:00'

查询某一天的数据

select * from capture_log where to_days(cdate) >= to_days( '2020-4-20 00:00:00')

查询某一天前一天的数据， 一般指定当天

select * from capture_log where to_days( '2020-4-20 00:00:00') - to_days(cdate) = 1

datediff() 前减后
查询某一天近n天的数据 ， 一般指定当天

select * from capture_log where to_days( '2020-4-20 00:00:00') - to_days(cdate)
between 0 and n
-- or
select *  from capture_log where datediff( '2020-4-20 00:00:00', cdate)
between 0 and n

yearweek()
前 n 周

select * from capture_log where yearweek(cdate) = yearweek(now()) - n;  

period_diff() 前减后
前 n 月

select * from capture_log where period_diff(date_format(now(),'%Y%m'), date_format(cdate,'%Y%m')) = n

timestampdiff() 后减前，时差间隔（second）
计算指定两个时间的间隔，此时间间隔一般与我们规定的时间内比较，例如最新 n 小时内的数据

select * ,timestampdiff(second, cdate, now()) 
from capture_log where timestampdiff(second, cdate, now()) between 0 and n * 3600

time_to_sec()
可以把时段转化成秒，通常用于时间相加

-- 当前时间的未来五小时内可用的数据
select * from info where available_time  < from_unixtime(unix_timestamp(now()) + time_to_sec('5:00:00'),'%Y-%m-%d %H:%i:%S');