同一个表查询某个字段重复的数据
select * from 数据表 WHERE 重复记录字段 in ( select 重复记录字段 from 数据表 group by 重复记录字段 having count(重复记录字段)>1)
将某个字段的值转换为文字
比如城市city字段,值有‘bj',‘sh’ 。将bj转为北京,sh转为上海
select case when city='bj' then '北京' when city='sh' then '上海' end as city from 表
mysql 计算时间差 TIMESTAMPDIFF(时间单位,开始时间,结束时间)
select TIMESTAMPDIFF(DAY,'2020-01-06 13:12:32', NOW()) as '天数差';
select TIMESTAMPDIFF(HOUR,'2020-01-06 13:12:32', NOW()) as '小时差';
将A表某个字段同步到B表某个字段
UPDATE A JOIN B SET A.字段=B.字段 WHERE A.id=B.id
查询今天,并且状态为0,3,4的数据。or和and
select t.val_status,t.* from business.bu_assign t where (t.val_status=0 or t.val_status =3 or t.val_status =4) and t.created_time > date(now()) and t.driver_id ='10000207'
根据某个字段的长度查询
select * from usercenter.uc_vehicle where CHAR_LENGTH(vehicle_num)>8 order by created_time desc limit 10;
LENGTH:如果是汉字是按照三个长度来统计的
CHAR_LENGTH:不管汉字数字英文都按一个长度来统计