mysql工作中使用语句记录

id自增的情况下，插入数据：

INSERT INTO `freeway_source`.`biz_pd_statisticsdata` select null as `ID`, `GPSType`, `AlarmNum`, `TotalNum`, REPLACE(recordTime,date(recordTime),date(now()))`recordTime` from biz_pd_statisticsdata

地理point类型字段赋值：

GeomFromText(CONCAT('POINT(',GeoX,' ',GeoY,')'))

地理line类型字段赋值：

GEOMFROMTEXT(CONCAT('LINESTRING(',Geox1,' ',Geoy1,',',Geox2,' ',Geoy2,')'))

只取整数部分：FLOOR(GeoX)

四舍五入：ROUND(GeoX)

随机取100条数据：order by rand() limit 0, 100

替换日期：update  `biz_alert_hisinfo` set AlertTime=REPLACE(AlertTime,date(AlertTime),date(DATE_ADD(now(),INTERVAL -1 day)))

日期时间拼接：cast(CONCAT(date(`日期`),' ',time(`时间`)) as datetime)

修改某字段值递增：

set @rownum=0;

update geo_canyin

SET FID = (

select @rownum := @rownum +1 as nid)；

5层数据拆分参考https://www.cnblogs.com/lazyInsects/p/8075487.html：

select SUBSTRING_INDEX(Remark,'/',1) as 1_name,

SUBSTRING_INDEX(SUBSTRING_INDEX(Remark,'/',2),'/',-1) 2_name,

SUBSTRING_INDEX(SUBSTRING_INDEX(Remark,'/',3),'/',-1) 3_name,

SUBSTRING_INDEX(SUBSTRING_INDEX(Remark,'/',4),'/',-1) 4_name,

SUBSTRING_INDEX(SUBSTRING_INDEX(Remark,'/',5),'/',-1) 5_name,

Remark

from  dept;