1.批量修改
字段一表示要修改的字段
字段二、字段三表示根据什么来进行修改
UPDATE 表名
SET 字段一 = CASE 字段二
WHEN 1 THEN 10
WHEN 2 THEN 35
WHEN 3 THEN 35
END
WHERE 字段三=2 AND 字段二 in (1,2,3);
2.批量修改多个字段
字段一、字段三表示要修改的字段
字段二表示根据什么来进行修改
UPDATE 表名
SET
字段一 = CASE 字段二
WHEN 1 THEN 10
WHEN 2 THEN 35
WHEN 3 THEN 35
END,
字段三 = CASE 字段二
WHEN 1 THEN 2
WHEN 2 THEN 2
WHEN 3 THEN 2
END
WHERE 字段二 in (1,2,3);
3.批量添加
parameterType="java.util.List"
insert into t_score_week_bed_item(bedscoreid,score,itemid) values
<foreach collection="list" item="item" separator="," >
(#{item.bedscoreid},#{item.score},#{item.itemid})
</foreach>
4.单个添加语句返回ID
useGeneratedKeys="true" keyProperty="checkid"
5.MySQL设置某个字段为默认时间函数
CURRENT_TIMESTAMP
6.结果集中设置某个字段不为空时显示自己,为空时显示第二个
IFNULL(字段名,"为空值显示")
7.设置根据查询出来的状态显示的值
CASE a.`status`
WHEN '0' THEN '未打款'
WHEN '1' THEN '已打款未确认'
WHEN '2' THEN '已完成'
WHEN '3' THEN '未打款被投诉'
WHEN '4' THEN '未确认被投诉'
END AS `status`
IF(num<0,0,num)
格式:IF(Condition,A,B)
意义:当Condition为TRUE时,返回A;当Condition为FALSE时,返回B
8.设置时间输出格式
参考:
http://www.w3school.com.cn/sql/sql_func_now.asp
http://www.w3school.com.cn/sql/func_date_format.asp
DATE_FORMAT(a.regtime,'%Y-%m-%d %T')
9.MyBaits报异常“元素内容必须由格式正确的字符数据或标记组成”,解决方式如下
<if test="beginDate!=null">
<![CDATA[AND a.adddate >#{beginDate}]]>
</if>
<if test="endDate!=null">
<![CDATA[AND a.adddate < #{endDate}]]>
</if>
10.生成随机固定时间
SELECT FLOOR(7 + (RAND() * 6)); 取7到12的随机整数,包括7到12
SELECT FLOOR(12+(rand()*13)); 取12到24的随机整数,包括12到24
LPAD(FLOOR(0 + (RAND() * 23)),2,0) 取0-24之间的随机数,不够2位的前补0
格式 : 2008-12-11 17:43:11
举例:
给用户赋一个随机日期,年份为1990-2014
select CONCAT(FLOOR(1990 + (RAND() * 25)),'-',LPAD(FLOOR(1 + (RAND() * 12)),2,0),'-',LPAD(FLOOR(3 + (RAND() * 8)),2,0))
结果:1999-10-07
select CONCAT(LPAD(FLOOR(0 + (RAND() * 23)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0))
结果:15:39:18
日期转换为时间戳
SELECT UNIX_TIMESTAMP('2015-04-29')
字符串转化成日期时间
str_to_date('2017-05-27 12:08:19',,'%Y-%m-%d %T')
下面是一个示例:生成2017-04-19 早上9点下下午21点之间生成一个随机时间并转换成时间戳
select CONCAT('2017-04-19 ',LPAD(FLOOR(9 + (RAND() * 9)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0));
select UNIX_TIMESTAMP(str_to_date(CONCAT('2017-04-19 ',LPAD(FLOOR(9 + (RAND() * 9)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0),':',LPAD(FLOOR(0 + (RAND() * 59)),2,0)),'%Y-%m-%d %T'));
11.根据某个字段进行分组然后查询另一个字段最大值的所在的记录
select * from (select * from 表名 order by 最大值字段 desc) as a group by a.分组字段
12.判断两个字符串是否一样
如果这两个字符串相等返回0,如果第一个参数是根据当前的排序小于第二个参数顺序返回-1,否则返回1
SELECT STRCMP('2017-06-02',DATE_FORMAT(NOW(),'%Y-%m-%d'));
13.需要某个时间的前面多久或者后面多久的时间
例如:今天是2016年08月01日。
date_sub('2016-08-01',interval 1 day) 表示 2016-07-31
date_sub('2016-08-01',interval 0 day) 表示 2016-08-01
date_sub('2016-08-01',interval -1 day) 表示 2016-08-02
date_sub(curdate(),interval 1 day) 表示 2016-07-31
date_sub(curdate(),interval -1 day) 表示 2016-08-02
date_sub(curdate(),interval 1 month) 表示 2016-07-01
date_sub(curdate(),interval -1 month) 表示 2016-09-01
date_sub(curdate(),interval 1 year) 表示 2015-08-01
date_sub(curdate(),interval -1 year) 表示 2017-08-01
备注:
SELECT NOW(),CURDATE(),CURTIME()
结果类似:
NOW() 2016-08-01 16:25:46
CURDATE() 2016-08-01
CURTIME() 16:25:46
14.获取一个时间的年、月、日、时、分、秒
set @dt = '2008-09-10 07:15:30.123456';
select date(@dt); -- 2008-09-10
select time(@dt); -- 07:15:30.123456
select year(@dt); -- 2008
select quarter(@dt); -- 3
select month(@dt); -- 9
select week(@dt); -- 36
select day(@dt); -- 10
select hour(@dt); -- 7
select minute(@dt); -- 15
select second(@dt); -- 30
15.根据开始时间结束时间判断状态
CASE
WHEN NOW() < ROOT.START_DATE THEN 0
WHEN NOW() > ROOT.END_DATE THEN 2
ELSE 1 END AS `STATUS`,