1 在SQL中对日期进行处理
如下代码中的date_format函数,将日期转为 2017-11-03的格式。
SELECT (@i:=@i+1) AS rownum, id,content,date_format(createtime,'%Y-%m-%d') AS createtime,status
FROM t_message
join
(select @i:=#{start} )it
<where>
<if test="beginDate != null and beginDate != ''">
createtime >= #{beginDate}
</if>
<if test="endDate != null and endDate != ''">
<![CDATA[ AND createtime <= #{endDate} ]]>
</if>
</where>
ORDER BY id DESC
${limit}
2 自动编号
如上中的:
SELECT (@i:=@i+1) AS rownum, id,content,date_format(createtime,'%Y-%m-%d') AS createtime,status
FROM t_message
join
(select @i:=#{start} )it
3 返回行数,null参与
SELECT count(*) FROM user
4 返回指定字段不同值的数目,为null的不参与
SELECT count(DISTINCT name) FROM user
5 求某一列的数据的总和
SELECT SUM(count) FROM user
6 截取一部分字段
SELECT MID(name,1,3) AS name FROM user
7 格式化查询出来的值2018-01-15 17:13:17
SELECT DATE_FORMAT(Now(),'%Y-%m-%d %H:%i:%S') as PerDate
8 连接字符串
SELECT * FROM user WHERE name LIKE concat('%','2','%')
9 时间比较
SELECT * FROM user WHERE createtime<NOW()
10 插入时间数据
// format:"yyyy-MM-dd HH:mm:ss"
insert into user(name,password,count,createtime)
value('dema','123',1,'2018-01-15 17:00:00')
11 Mybatis时间比较
AND t.end_time <![CDATA[ > ]]>#{limitTime}