mysql 各种日期转化_mysql 时间转换常见用法汇总

1.数字转字符

cast((t1.year * 100 + t1.month) as char(6)) 转字符

2.字符转时间

str_to_date('2020-04-28 16:09:36','%Y-%m-%d %H:%i:%s')

3.日期转第几周，返回当年的第几周，整形

select WEEK(date_add('2021-01-04',interval 6 day),2)

返回值第二周：2

4.字符转数字

CAST(value as type);

CONVERT(value, type);

这里的type可以为：

浮点数 : DECIMAL

整数 : SIGNED

无符号整数 : UNSIGNED

5.字符替代

update brand set applicant_name =replace(replace(applicant_name,'(','('),')',')'),

company_name =replace(replace(company_name,'(','('),')',')')

6.字符串截取

select substring(age,1,3) from t_zhou2 (从第一个开始，截取3个)

select left(age,3) from t_zhou2 (左边截取，截取3个)

select right(age,3) from t_zhou2 (右边截取，截取3个)

7.字符串按某一个符号分开，然后取值

select substring_index('192.168.11.12:12345',':',1); 取第一个：前的字符串

8.创建索引

create index in_time_id on r_commodity_sales_all (time_id);

9.创建联合唯一索引

alter table map_investor_company add unique index (investor_company_code,invested_company_code)

10.添加联合唯一键

alter table bi.call_records add constraint Time_unique UNIQUE key (Time, CallerNumber, CalleeNumber);

11.编码互转

CONVERT(t2.name USING utf8) COLLATE utf8_unicode_ci

12.update

update bi.project_active a1 set a1.target_value=(select t.target_value from t where t.id=a1.id)

where exists(select * from t where t.id=a1.id)

#更新多个字段

update company a,company2 b set a.usci=b.usci,a.term_start_day=b.term_start_day,a.term_end_day=b.term_end_day

where a.create_time>'2020-12-14 14:00:28' and a.name=b.name

13.分组去重

#id,name,link三组，根据id分组，name,link字段取个最大的，去重id,name两组相同的，

select t1.id,t1.name,t1.link from (select id,name,max(link) as link from t_zhou group by id,name) t1 inner join

(select id,max(name) as name from t_zhou group by id)t2 on t1.id=t2.id and t1.name=t2.name;

#(选出所有重复的id行)

select * from t_zhou where id in (select id from t_zhou group by id having count(id)>1) ;

#多列联合重复

select investor_company_code,invested_company_code from map_investor_company

group by investor_company_code,invested_company_code having count(investor_company_code)>1 and count(invested_company_code)>1)a1

14.with as 用法

with t2 as (select * from t_zhou3)

select * from t_zhou3 t1 left join t2 on t1.send=t2.receive and t1.session=t2.session

15.查看表空间大小

use information_schema;

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables

where table_schema ='spiderdata' and table_name = 'mob_data_app_userremain_data_2020_04_21';

16.选出子集删除

delete from alternativedb.map_security_company a where a.security_id in (select b.security_id from (select t1.security_id from alternativedb.map_security_company t1

left join alternativedb.securities_info si on t1.security_id = si.Security_ID where si.List_State<>'1') b)

17.当前日期转周一日期

select subdate('2020-01-01',date_format('2020-01-01','%w')-1)

18..表重新命名

rename table company_name_code to company_name_code3;

19.给一列值排名

select score_risk_list->'$[0]' risk1,row_number() over(order by score_risk_list->'$[0]' desc) as rank_risk1

from r_fin_report limit 10