惯用sql语句整理

--获得指定格式的时间
SELECT DATE_FORMAT(NOW(), '%Y%m%d%H%I%S');
--获得随机数
SELECT round(round(rand(),4)*10000);

--字符串拼接
select CONCAT(CONCAT(a.big_type,a.type,a.code),DATE_FORMAT(NOW(), '%Y%m%d%H%I%S'),round(round(rand(),4)*10000)) from sc_chshbjlb a

--更新
update sc_bjgl b set b.citypartcode= (select CONCAT(CONCAT('#{type_code}','#{citypart_apl}'),DATE_FORMAT(NOW(), '%Y%m%d%H%I%S'),round(round(rand(),4)*10000)))
where b.id='#{id}';
--更新
update sc_chshbjlb set type = (case type when 'p' then 'point'
                                when 'l' then 'line'
                                 when 'a' then 'polygon'
                                 else '' END);