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
用于判斷一些邏輯來確定記錄的一個屬性旅掂，且該屬性不在數(shù)據(jù)庫設置
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()
查詢某一天開始到最新的數(shù)據(jù)
select * from capture_log where cdate > '2020-4-20 00:00:00'
查詢某一天的數(shù)據(jù)
select * from capture_log where to_days(cdate) >= to_days( '2020-4-20 00:00:00')
查詢某一天前一天的數(shù)據(jù)， 一般指定當天
select * from capture_log where to_days( '2020-4-20 00:00:00') - to_days(cdate) = 1
datediff() 前減后
查詢某一天近n天的數(shù)據(jù) 访娶， 一般指定當天
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）
計算指定兩個時間的間隔，此時間間隔一般與我們規(guī)定的時間內比較崖疤，例如最新 n 小時內的數(shù)據(jù)
select * ,timestampdiff(second, cdate, now())
from capture_log where timestampdiff(second, cdate, now()) between 0 and n * 3600
time_to_sec()
可以把時段轉化成秒秘车，通常用于時間相加
-- 當前時間的未來五小時內可用的數(shù)據(jù)
select * from info where available_time < from_unixtime(unix_timestamp(now()) + time_to_sec('5:00:00'),'%Y-%m-%d %H:%i:%S');
