mysql常用法

导出roobo_query数据库中的stat_ros_query_day,stat_ros_query_day_agentid 等表的结构和数据

mysqldump -h 'rm-2zez2yp26xr2e8ut5.mysql.rds.aliyuncs.com' -uroobo_query -pjRUVM5VWVt9hRatT roobo_query stat_ros_query_day stat_ros_query_day_agentid stat_ros_query_day_scene stat_ros_query_day_hour stat_ros_query_day_scene_top>/tmp/rosquery0911/stat_query.sql;

执行sql

mysql -h mysqlmaster.mysql.rds.aliyuncs.com -D operation_configs_cros -u operation_cros   -pWu9iHZmtwdV3apWq < /tmp/20170911-160304-ecf1/operation_configs_cros.sql;

导出指定数据库指定表的部分数据

mysqldump -h 'rm-2zez2yp26xr2e8ut5.mysql.rds.aliyuncs.com' -uroobo_query -pjRUVM5VWVt9hRatT roobo_query ros_query  -w"query_date>='2017-09-17'">/tmp/rosquery0911/ros_query.sql;

新加字段

ALTER TABLE `stat_ros_query_day_agentid` 
ADD COLUMN `agent_name` varchar(100) DEFAULT NULL COMMENT 'agent_name' AFTER `agent_code`;

爱接力数据

mysql -h 'rm-2zez2yp26xr2e8ut5.mysql.rds.aliyuncs.com' -uroobo_query -pjRUVM5VWVt9hRatT  -e "set names utf8;select * from roobo_query.ros_query where query_date >= '2017-09-11' and query_date <='2017-09-18' and agent_code = 'ZDk5ZDdlZmNmOTc5'" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > /tmp/ejiazi.csv;

storybox-sas

select query_date,sum(voice_count),sum(device_count),sum(voice_count)/sum(device_count) avgCount from ts_storybox_agentid_stat where query_date between '2017-09-17' and '2017-09-19' group by query_date;

删除指定文件之外的文件

ls | grep -v catalina.out | xargs rm

统计文件个数

ls -l |grep "^-"|wc -l