Summary of common sql statements in Mysql

1. mysql export file:

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. Related Query

In sql, in order to ensure that the number of returned items is the same as the main table, add a group by main table id

3. Common methods such as if, ifnull, concat_ws, etc.

1) concat_ws('',country, province, city) region The three fields are combined according to the content between '';

concat_ws('',CASE p.gameType1 WHEN 1 THEN 'Recite and tell stories' WHEN 2 THEN 'Recite and present situational speeches' END,CASE p.gameType2 WHEN 3 THEN 'Theme writing' END) as gameType;

2)if(gender=1,'male','female') as gender;

3)ifnull(age,0) as age;

4)(CASE ageGroup WHEN 1 THEN 'Children Group A' WHEN 2 THEN 'Children Group B' WHEN 3 THEN 'Teenagers Group A' WHEN 4 THEN 'Teenagers Group B' END) as ageGroup;

4. Retrieve the root password in mysql5.7

[root@166087 mysql]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --datadir=/data/mysql --skip-grant-tables

mysql> update user set authentication_string=password('123456') where user='root';

5. Alibaba Cloud Usage Notes - MySQL Remote Connection - CentOS7

First log in:

mysql -u root -h localhost -p

use mysql #Open the mysql database

2) Setting host to % means that any IP address can connect to MySQL. Of course, you can also specify a specific IP address as host.

update user set host='%' where user='root' and host='localhost';

flush privileges; #Refresh the permission table to make the configuration effective

Then we can connect to our mysql remotely.

3) If you want to close the remote connection and restore the default settings of MySQL (local connection only), you can follow these steps:

use mysql #Open mysql database update user set host='localhost' where user='root'; #Setting host to localhost means that you can only connect to mysql locally

flush privileges; #Refresh the permission table to make the configuration effective update user set password=password('123456') where User='root';#Change password flush privileges; #Refresh the permission table to make the configuration effective

Note: You can also add a remote connection user with the username yuancheng, password 123456, and permission % (indicates that any IP can connect). The command reference is as follows:

grant all on *.* to 'yuancheng'@'%' identified by '123456';

flush privileges;

4) What to do if the field is varchar when sorting in mysql?

2 ways:

1. Order by field + 0

2. order by cast(field as int)

6. Modify field data in batches

update t_comment SET avatar = replace(avatar, 'http', 'https');//Replace update t_log set message=concat("https",message);//Append to the front