项目中常用写法（数据库）

sql

项目中常用的书写方式

base64转码

select to_base64('helloworld')

查询字段是json类型的，是否包含某个字

select * from table where j_ext::text like '%112%'

日期字段转化成固定格式比较

to_char(c_aa,'yyyy-mm-dd') >= '2021-01-01'

某两列的数据互换

pdate aa set name=xname ,xname=name 

某一列去掉最后两个字符

update aa set name=substr(name,1,length(name)-2);

某一列结尾加上固定文字

UPDATE aa SET name = CONCAT(name,'哈哈哈')

数据库插入，如果有值就不插入

insert into aa(aa_id,aa_sn,aa_url,aa_count,create_time,aa_bh)
            select uuid(),11,11,11,now(),11
            where  NOT EXISTS (
            select * FROM aa where aa_sn=11 and aa_url = 11)

数据从右截取字符串

select max(RIGHT(GFPD_BH, 3)) from judgmentbook 
select max(substring(GFPD_BH, -5))from judgmentbook 

数据有相同的标识，标志相同的一组数据日期相减，在累计所有的相减后和

select deviceId,time,SEC_TO_TIME(SUM(milliseconds)) as cumulativeTime,convert(SUM(milliseconds)/43200*100,decimal(10,2)) as startRate from  
(
SELECT
       deviceId,
       DATE(time) as time,
       TIMESTAMPDIFF(SECOND, MIN(time), MAX(time)) AS milliseconds
FROM device
where DATE(time) = CURDATE()
GROUP BY count,DATE(time),deviceId 
) deviceTemp GROUP BY time, deviceId