IFNULL
判断是否为空,如果为 NULL 则返回第二个参数的值,如果不为 NULL 则返回第一个参数的值
IFNULL(money,0)
排序
order by
asc是指定列来按升序排列,desc则是指定列按降序排列。
分组
group by
Date
1、DATE_FORMAT() 函数用于以不同的格式显示日期/时间数据
查询结果显示年月日
select date_format(create_date,’%Y-%m-%d’) createDate from assist
2、获取年周YEARWEEK
select YEARWEEK(create_date) createDate from assist
3、TO_DAYS(将这个日期转换成天)
select (TO_DAYS(NOW()) - TO_DAYS(create_date)) createDate from assist当前日期减去创建数据日期
4、NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
5、查询年月日
select * from qhh_assign where year(modify_date) = ‘2020’ and month(modify_date) = ‘03’ and day(modify_date) = ‘12’
查询时间段
包含<=和>=
select * from biz_evaluation_leave where create_date between ‘2020-04-18 00:00:00’
and ‘2020-04-18 23:59:59’
不包含
select * from biz_evaluation_leave where create_date > ‘2020-04-18 00:00:00’
and create_date < ‘2020-04-18 23:59:59’
时间转日期
date(aer.create_date) as assignDate
CONCAT字符串拼接
ids like concat(’%’, #{userId}, ‘%’)
IN
in和=相似,只是一个可以查询多个结果,一个只能查询一个
status in (‘FINISH’,‘WAITCOMENT’,‘COMMENT’)
or(或者)
select * from order_goods where goods_id = 1206507711361826818 or goods_id = 1206502773781020674
COUNT统计
SELECT COUNT(
a.assign_status IN (‘NODELIVER’,‘NOCHECK’,‘REFUSE’)
OR NULL
) EXECUTE
FROM
employee e
LEFT JOIN assign a ON a.employee_id = e.id
GROUP BY
e.id
order by e.create_date DESC
case when用法
select case when type = 0 then “平台” else “合伙人” end saleCount from assist where assign_id = 1258218132717129729 and form_type = “SERVICE”
case when相当于if else,end后面是别名
获取最后一条数据
select * from assign where order_id = #{orderId} ORDER BY id DESC limit 0,1
limit n 等同于 limit 0,n