Common mysql statements

Common mysql statements

1. display the names of all tables in the current database (the prerequisite is that you must enter the database and enter the database statement: [use Database Name ;])
Mysql> show create table name;

2. Check which storage engines mysql supports:
Mysql> show engines;

3. view the default storage engine of mysql:
Mysql> show variables like '% storage_engine % ';

 

4. Back up a database[Used in windows cmd]

Mysqldump basic syntax [bold as the content ]:

　　Mysqldump-hHostname-PPort-UUsername-PDbname table1 table2...>C: \ BackupName. SQL

Where:

• Hostname Host Name: generally localhost or ip address of the host where the database is located (for example, 192.168.0.123)
• Port: Generally, the default port 3306 may also be 3307 or another port.
• Username: generally root
• Dbname Database Name: for example, mytest (enter the name of the database to be backed up)
• The table1 and table2 parameters indicate the name of the table to be backed up. If it is null, the entire database is backed up ;(Mysqldump-hLocalhostP3306-uRoot-P mytest>C :\Mytest. SQL orMysqldump-hLocalhost-P3306-uRoot-P mytest>C :\Mytest. SQL) The command-u-h-p and parameter can have spaces or do not contain spaces.
• The path (absolute path) of the BackupName. SQL backup file is as follows: C :\Mytest. SQL indicates that the mytest. SQL file in drive C is a backup file.. SQL can be another suffix
• -P indicates the password. Enter is required only after the command is entered.

Another example: Use the root user to back up the person table in the test Database

　　 mysqldump -u root -p test person > D:\backup.sql

5. Run the mysqldump command to restore the database. The syntax is as follows ]:

Mysql-hlocalhost-P3306-uroot-p dbname <C: \ mytest. SQL

• Dbname is the name of the database to be restored: It means to restore the table structure and data of mytest. SQL to the dbname database.
• The database must have been created
• For other methods, you can also use the command sourceC: \ mytest. SQL [prerequisites: 1. Enter the command in the mysql command line; 2. Use current data;]