MySQL Common commands
Note: The bold part is the part that the user needs to replace according to their actual situation, the italic part is the optional content, #开头的命令是在终端中执行,> the beginning of the command is executed in MySQL
1. Log in to MySQL
- # mysql-u username -P (enter password after return)
- # msyql-uRoot -P123(root is username, 123 is password-u and Root middle,-p and 123 have no spaces)
- Remote connection to MySQL
Usage: # Mysql-uusername-ppassword- H host - p port number- d databases
Example: # mysql-uroot-p123456-h 192.168.1.91-p 3306-d Test
2. Execute the SQL file
- > Source/path/source.sql;
- # mysql-uroot-p123 < source.sql
3.1 Database backup
Suppose you want to back up the TEMP database:
- # mysqldump-uroot-p123 temp > dump.sql
Suppose you want to back up the tables table1 and table2 in the TEMP database:
- # mysqldump-uroot-p123 temp table1 table2 > dump.sql
Compress data with gzip:
- # mysqldump-uroot-p123 temp table1 table2 | gzip > dump.sql.gz
To back up multiple databases:
- # mysqldump-uroot-p123--databases db1 [DB2 db3 ...] > Dump.sql
Add a Where Condition:
- # mysqldump-uroot-p123 temp Table--where= "" > dump.sql
When Mysqldump is not available
- # mysql-uroot-p123-ddatabase-e "Select Concat (' INSERT into table values (', ID, ') '"
3.2 Database Recovery
- # mysql-uroot-p123 Temp < dump.sql
To restore directly from a compressed file:
- # Gunzip < dump.sql.gz | mysql-uroot-p123 Temp
4. Table Structure Modification
- 4.1 Add a new column usage:> ALTER TABLE tablename add column name type constraints ;
Example:> ALTER TABLE user add name varchar (+) not null default ' Unknow ';
- 4.2 Delete Column usage:> ALTER TABLE tablename drop column name ;
Example:> ALTER TABLE user drop column name;
- 4.3 Renaming column usage:> ALTER TABLE tablename Change original column name the original type constraint condition ;
Example:> ALTER TABLE user change name nickname varchar (+) not null default ' Unknow ';
- 4.4 Changing the column type Usage:> ALTER TABLE tablename change column name column name new type constraint condition ;
Example:> ALTER TABLE user change name name tinyint not null default ' 0 ';
Usage:> ALTER TABLE tablename alter column name new type ;
Example:> ALTER TABLE user ALTER column name tinyint;
- 4.5 Renaming table Usage:> ALTER TABLE tablename1 rename tablename2;
Example:> ALTER TABLE user rename New_user;
- 4.6 Gazzo Reference Method:> ALTER TABLE tablename Add index name (field name 1[, field Name 2 ...]);
Example:> ALTER TABLE user add index name_age(name,age);
- 4.7 Plus primary keyword index usage:> ALTER TABLE tablename add primary KEY (ID);
Example:> ALTER TABLE user add primary key (ID);
- 4.8 Plus unique restriction index usage:> ALTER TABLE tablename add unique index name ( field name );
Example:> ALTER TABLE user add unique index_name (name);
- 4.9 Delete an index usage:> ALTER TABLE tablename DROP index name ;
Example:> ALTER TABLE user drop index_name;
5. View index information for a table
- > Show index from tablename;
6. SQL Performance Analysis
- SQL uses index, number of rows scanned
> Explain select * from user;
7. sql that the database is executing
> Show processlist;
8. Random number
The MySQL machine function rand () generates decimals that are greater than or equal to 0, less than 1 (0 to 1), and the floor function can turn decimals into integer integers
- Random numbers from 0 to 99
> select Floor (rand () *100);
- Random numbers from 1 to 99
> Select Floor (1+rand () *99);
9. Create a user
Before creating a user, you need to ensure that there are no users named "", and if necessary, remove
> select User,password from Mysql.user;
> Delete from mysql.user where user = ';
> Create User identified by ' password ';
> GRANT all on table name . * To user life ;
> Flush Privileges; (also refresh the System permissions table after authorization)
Granting permissions to Users
Usage:> GRANT operation on library name. Table name to user name; (Library name table name can be matched with *)
Example:> GRANT SELECT, insert,update on Temp.user to NewUser;
10. Clear the data in the table
Usage: # mysqldump-u user name -P password --add-drop-table--no-data Database | Mysql-u User name -P password database
Example: # mysqldump-uroot-p123--add-drop-table--no-data Temp | mysql-uroot-p123 Temp
Example: # mysqldump-uroot-p123--add-drop-table--no-data Temp | grep user | mysql-uroot-p123 Temp
Principle: According to the backup parameters of mysqldump, including the drop table and the CREATE TABLE statement, it is the first to drop all tables in database and then rebuild the table structure.
11. Delete the specified table in bulk
Usage: # mysqldump-u user name -P password --add-drop-table--no-data Database | grep \^drop | grep Conditions | Mysql-u User name -P password database
Example: # mysqldump-uroot-p123--add-drop-table--no-data Temp | grep \^drop | grep user | mysql-uroot-p123 Temp
12. Start MySQL
- # service Mysqld Start
- #/etc/inint.d/mysqld Start
13. Turn off MySQL
- # service Mysqld Start
- #/etc/inint.d/mysqld Start
14. Restart MySQL
- # Service Mysqld Restart
- #/etc/inint.d/mysqld Restart
15. Set MySQL terminal encoding
- In the terminal use MSYQL Chinese often garbled, this time can set names UTF8;
16. Disable MySQL Cache results
- Select Sql_no_cache Count (*) from TableName; It is important to note that this is the result of a forbidden query in the cache, rather than prohibiting querying from the cache
17. Time consuming to query n times
- Select Benchmark (n, (select COUNT (*) from TableName)); Test indicates that the test statement is not cached
Benchmark repeats the expression n times, and the benchmark function only measures the performance of the numeric expression, although the expression can be a subquery, but the subquery returns only a single value
18. Variables
- Tests show that using variables does not do any caching, compared to using Sql_no_cache,sql_no_cache will cache some SQL analysis, execute the plan
> Set @id: = 0;
> select COUNT (*) from tablename where ID > @id;
19. Querying tables with more rows
> select Table_name,table_rows from Information_schema.tables ORDER BY table_rows desc limit 10;
20. Do not generate Binlog delete data mode
> Truncate tablename;
21. Number of seconds and date conversion
> From_unixtime (1466780897)
> Unix_timestamp (' 2016-05-25 00:00:00 ')
22. Line up the identified columns
> select Group_concat (ID) from TableName;
MySQL Common commands
