mysql用sql_Mysql常用sql方法

1.mysql 导出文件：

SELECT `pe2e_user_to_company`.company_name, `pe2e_user_to_company`.company_code, `users`.name, `users`.uid, `users`.mail, `pe2e_email_notification_email`.`email_cc` FROM `users` , `pe2e_user_to_company` LEFT JOIN `pe2e_email_notification_email` ON `pe2e_user_to_company`.`uid` = `pe2e_email_notification_email`.`uid` WHERE `users`.`uid` = `pe2e_user_to_company`.`uid` into outfile '/tmp/users.csv'  fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

2.关联查询

sql中多个left join，为了保证返回数量和主表一样，要加个group by 主表id

3.if，ifnull，concat_ws等常见方法

(1).concat_ws('',country, province, city) region  三字段按照''之间的内容合拼;

concat_ws('',CASE p.gameType1 WHEN 1 THEN '朗诵讲故事' WHEN 2 THEN '朗诵情景演讲' END,CASE p.gameType2 WHEN 3 THEN '主题写作' END) as gameType;

(2).if(gender=1,'男','女') as gender;

(3).ifnull(age,0) as age;

(4).(CASE ageGroup WHEN 1 THEN '儿童A组' WHEN 2 THEN '儿童B组' WHEN 3 THEN '少年A组' WHEN 4 THEN '少年B组' END) as ageGroup;

4.mysql5.7 找回root密码

6、批量修改字段数据

update  t_comment SET  avatar = replace(avatar, 'http', 'https');//替换

update t_log set message=concat("https",message);//前面追加