11 Main MySQL Commands?

The most common way to show a list of the MySQL databases is by using the MySQL command.

Which command is used to show the existing databases on the MySQL?

1. To show list all databases on the MySql server.

show databases;

2. Switch to a database from one to another.

use [Your_db_name];

3. To show all the table in the database

show tables;

4. To see database’s field formats.

describe [Your_table_name];

5. Delete database.

drop database [Your_database_name];

6. Delete Table Name.

drop table [table_name];

7. Load a data from CSV file into a table.

LOAD DATA INFILE ‘/tmp/your_filename.csv’ replace INTO TABLE [your_table_name] FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’ (field1,field2,field3….);

8. Dump all Databases for backup. Backup file is SQL commands to recreate all Database.

[mysql dir]/bin/mysqldump -u root -ppassword –opt >/tmp/alldatabases.sql

9. Take Dump one database for backup.

[mysql dir]/bin/mysqldump -u username -ppassword –databases yourdatabasename >/tmp/yourdatabasename.sql

10. Take Dump a table from a database.

[mysql dir]/bin/mysqldump -c -u username -ppassword yourdatabasename yourtablename> /tmp/yourdatabasename.yourtablename.sql

11. Restore database (or database table) from backup.

[mysql dir]/bin/mysql -u username -ppassword yourdatabasename < /tmp/yourdatabasename.sql