• 常用SQL
  • 日期区间查询
  • 同表里字段x不同，字段y相同的数据
• 第一个库比第二个库多出来的表
  • 查询日期
  • 解析JSON
  • 时间差
  • 导入指定表
  • 导入所有表 排除指定表

日期区间查询

SELECT COUNT(DISTINCT user_id) FROM `akc_member_user_login_log` WHERE DATE_FORMAT(`create_date`, '%Y%m') BETWEEN '201909' AND '201910' AND REPLACE(app_version,'.','') < 360;

jira，TAPD,禅道，redmine，teambition

同表里字段x不同，字段y相同的数据

字段1 字段2 
1 a 
2 a 
1 b 
1 b 
2 a 
用sql语句实现查询，查询出 
1 a 
1 b

select distinct x.name,x.card 
from different as x,different as y 
where x.name!=y.name and x.card=y.card

第一个库比第二个库多出来的表

SELECT table_name as nt 
FROM information_schema.TABLES WHERE table_schema LIKE ‘ufs_com_cn_prd_new’ AND table_name NOT IN ( 
SELECT table_name as ot 
FROM information_schema.TABLES WHERE table_schema LIKE ‘ufs_com_cn’)

查询日期

SELECT DATE_FORMAT(r.create_date,’%Y-%m-%d’) as day,if(e.status is null, 0 , e.status) as stat,COUNT(r.user_id) FROM akc_member_label_relationr 
LEFT JOIN akc_member_user_status_extend e ON r.user_id = e.member_user_id 
WHERE r.label_id = ‘5e9f9441c718dd14dac2d6a86ba89f0c’ 
GROUP BY day,stat

explain select * from user_clue_advisor_log where update_time >= STR_TO_DATE(‘2015-09-01′,’%Y-%m-%d’) ;

解析JSON

SELECT biz_data, JSON_EXTRACT(biz_data, ‘$.productType’), todo_id 
from todo_attachment 
where todo_id = ( 
SELECT id from todo where biz_no = (SELECT biz_no from user_clue_chance where clue_id = ‘29650’) and type = ‘IMPROVE_AUTHORIZATION’);

时间差

select AVG(TIMESTAMPDIFF(SECOND,create_time,update_time)) as AVG,MAX(TIMESTAMPDIFF(SECOND,create_time,update_time)) as max,MIN(TIMESTAMPDIFF(SECOND,create_time,update_time)) as MIN from cirus.chance_auth_status where DATE_SUB(CURDATE(), INTERVAL 30 DAY) >= create_time

select AVG(TIMESTAMPDIFF(SECOND,create_time,update_time)),TIMESTAMPDIFF(SECOND,create_time,update_time) elapsed_time from cirus.chance_auth_status where create_time < DATE_FORMAT(‘2021-04-09 11:11:34
‘, ‘%Y-%m-%d %hh:%MM:%ss’) AND create_time > DATE_FORMAT(‘2021-04-08’, ‘%Y-%m-%d’)

select AVG(TIMESTAMPDIFF(SECOND,create_time,update_time)),TIMESTAMPDIFF(SECOND,create_time,update_time) elapsed_time from cirus.chance_auth_status where create_time > DATE_FORMAT(‘2021-04-09 11:11:34
‘, ‘%Y-%m-%d %hh:%MM:%ss’)

导入指定表

–table 为多个指定表

mysqldump -hcn-lamp-prd-2xl-0020-ufs-v3-rds.ccdb0jpzzrmn.rds.cn-north-1.amazonaws.com.cn ufs_com_cn -uufs_admin -pKHnXAPSjrKNMZnDr --table tb_show_comment_taskDetails tb_user_grade tb_user_score tb_user_score_detail tb_app_spread_log tb_app_member_add_record tb_app_member_start_record tb_show_comment_sign tb_show_comment_sign_log --single-transaction -q -e --no-autocommit | mysql -hcn-lamp-prd-2xl-0020-ufs-v3-rds.ccdb0jpzzrmn.rds.cn-north-1.amazonaws.com.cn ufs_com_cn_prd_new -uufs_admin -pKHnXAPSjrKNMZnDr

导入所有表 排除指定表

— –ignore-table 排除指定表

nohup mysqldump -hcn-lamp-prd-2xl-0020-ufs-v3-rds.ccdb0jpzzrmn.rds.cn-north-1.amazonaws.com.cn ufs_com_cn -uufs_admin -pKHnXAPSjrKNMZnDr --single-transaction -q -e --no-autocommit --ignore-table=ufs_com_cn.acc_task_dimension_sync --ignore-table=ufs_com_cn.adam_category --ignore-table=ufs_com_cn.adam_category_relation --ignore-table=ufs_com_cn.adam_circle --ignore-table=ufs_com_cn.adam_circle_content_relation | mysql -hcn-lamp-prd-2xl-0020-ufs-v3-rds.ccdb0jpzzrmn.rds.cn-north-1.amazonaws.com.cn ufs_com_cn_prd_new -uufs_admin -pKHnXAPSjrKNMZnDr > /data/hanson-dump.log 2>&1 &

---