SQL--函式

BtWangZhi發表於2017-11-03
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}

相關文章