mysql 函数 用法_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');