SQL--函数

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}