常用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);

--修改字段的长度

ALTER TABLE "public"."tsac_orderinfo"
ALTER COLUMN "remark" TYPE varchar(1000);

 

--创建id自增的表：

drop table `sc_incrementtab`;
CREATE TABLE `sc_incrementtab` (
  `in_id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`in_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

其中，AUTO_INCREMENT为初始值，目前为 1。

每次往自增表中插入数据时，用如下方式插入：

insert into sc_incrementtabvalues(null);，值设为null。

数据库会自动+1的。

 

 -- 左补齐，右补齐

lpad(obligatea,6,'0')

rpad(obligatea,6,'0')

 

--查找重复数据

select xm,count(xm) from sc_rkjbxxnew where 1=1 group by xm having count(xm) >1;

 

select xm,count(xm)

from sc_rkjbxxnew

where xm in (select xm from sc_rkjbxxnew group by xm having count(xm) >= 2)

GROUP BY xm;

 

 --根据查询结果，更新数据表的数据字段

update t_apprecord a set datetime = (select '2017-04-19'||substr(to_char(b.datetime),-9) 

from t_apprecord b where b.datetime < '1990-01-01 08:00:37' 

and a.prjid=b.prjid

and a.userid=b.userid

and a.signtag= b.signtag

and a.lat=b.lat

and a.lon=b.lon

and a.datetime=b.datetime)

where a.datetime < '1990-01-01 08:00:37';

 

 

 更新字段，更新字段的值为查询语句的结果

UPDATE bp_requirelist2

INNER JOIN (

SELECT

ProjectName,Sdepartment,

SEProjectId

FROM

bp_project

) b ON bp_requirelist2.ProjectName= b.ProjectName AND bp_requirelist2.Sdepartment=b.Sdepartment

SET bp_requirelist2.ProjectName = b.SEProjectId

 

--查询重复数据

select * from bp_requirelist t

where (select count(1) from bp_requirelist b where t.SEProjectID = b.SEProjectID 

AND t.SYear = b.SYear 

AND t.SQuarter = b.SQuarter

AND t.ReqName = b.ReqName)>1 

ORDER BY t.SEProjectID,t.ReqName

 

 

UPDATE bp_requirelist b INNER JOIN bp_requirelist a ON a.SEId=b.SEId SET b.SQuarter= SUBSTRING(a.SQuarter,2,1) 

 

UPDATE sys_user b INNER JOIN sys_user a ON a.SEId=b.SEId SET b.username= left(a.email,INSTR(a.email,'@')-1) WHERE a.email=b.email AND a.email !='xxxxxxxx';

 

UPDATE sys_user b INNER JOIN sys_user a ON a.SEId=b.SEId SET b.mobile= CONCAT(SUBSTRING(a.mobile,1,3),'0000',SUBSTRING(a.mobile,7,4))