[Database] MySQL usage sharing (Continuous updates)

1. Try your best to use SQL statements for database operations, instead of using visual operation tools, because one day you will suddenly find that SQL statements are forgotten, which is a very sad thing.

2. When an SQL statement is wrong, the error prompt is not obvious, so I try my best to correct it. I often waste time on a typo.

3. mysqldump5-uroot-ppasswd database_name table_name;

Use the mysqladmin command line to modify the user name and password. The most correct format is as follows:
Mysqladmin-u root-P password 123456
Next, you will be prompted

Enter password:

If you have not modified the password for the first login, press enter (mysql5.0 is used, and the initial password for versions earlier than 4.0 is root ).

The root password is changed to 123456.

4. If you do not want to enter a password, you must use the following parameters:-ppasswd, instead of-P passwd (passwd is our password)

5. SQL statement memory rule: the command for table structure operation: Add Table table_name after create alter drop;

Instructions on data operations: Update insert into Delete from directly adding table_name;

6. display warning:

show warnings;

7. Run the shell command in MySQL. mysql> \! Ls;

8. When querying NULL:

select * from table_name where col is null;

9. Reset auto_incrementALTER
TABLE tablename AUTO_INCREMENT = 1

10. view the database version: Select @ version;

11. Error 1130: Host '192. 168.1.3 'is not allowed to connect to this MySQL Server

Solution:
1) Change the table method. It may be that your account is not allowed to log on remotely, but only on localhost. At this time, you only need to log in to MySQL on the computer of localhost, and change the "host" entry in the "user" table in the "MySQL" database to "%" from "localhost"

mysql -u root -pvmwaremysql>use mysql;mysql>update user set host = '%' where user = 'root';mysql>select host, user from user;

2) Authorization law. For example, if you want myuser to use mypassword to connect to the MySQL server from any host.

GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;

If you want to allow myuser to connect to the MySQL server from a host whose IP address is 192.168.1.3, and use mypassword as the password

GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.3' IDENTIFIED BY 'mypassword' WITH GRANT OPTION;GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.10.40.54' IDENTIFIED BY '123456' WITH GRANT OPTION;

12. Delete All Tables starting with myprefix _ in the database:

SELECT CONCAT( 'DROP TABLE ', GROUP_CONCAT(table_name) , ';' )     AS statement FROM information_schema.tables     WHERE table_name LIKE 'myprefix_%';

After obtaining the command, run it.

13. Save the data from the SELECT statement to a text file:

select * into outfile "/Users/rocco/a.txt" from xxx;

Note: The Path/users/Rocco/must be MySQL writable, otherwise it will not succeed.

14. exclude a table mysqldump-uxxx-pxxx -- ignore-table = database. Table1 -- ignore-table = database. Table2> Backup. SQL

..