11 Useful MySQL Commands for Quick Reference

I am not a MySQL database administrator. Nor do I write MySQL statements every day. But I do deal with this amazing (and free!) database server every now and then. As I go out of touch with MySQL for quite sometime, I tend to forget several key commands. So, I have made a list of some very useful MySQL commands and I keep them with me as ready reference guide. These commands help you saving time when you know what you want to do but don’t know how to do it! I would suggest that you take a note of these commands and keep them printed on your desk.

If you are using MySQL Server and have to work through the command prompt, following are a few very handy statements:

mysql> SHOW TABLES;

It shows the list of tables that exist in the database currently in use.

mysql> DESCRIBE table_name;

Returns the structure of the table mentioned.

mysql> SHOW TABLE STATUS LIKE "table_name";

Returns more information about the table. Returned information includes engine type, number of rows, data length, index length, checksum, creation time etc.

mysql> SHOW CREATE TABLE table_name;

Returns the CREATE statement that was used to create the table specified. It is useful in case we want to create the same table again -or with a slight modification.

mysql> SHOW PRIVILEGES;

This statement shows the list privileges that the current user has.

There are a number of other SHOW commands which you could see in1 MySQL Reference Manual

Note: There is no SHOW command for displaying procedures. In order to see the list of procedures in the database, you can use following command:

mysql> SELECT type, db, name, param_list FROM mysql.proc;

This command will extract the procedure name, database name, and list of parameters that these procedures operate on.

mysql> SELECT * FROM information_schema.routines;

Use the above statement to see the definitions of all the procedures/functions.

mysql> SELECT * INTO OUTFILE "file_name" FROM table_name;

It’s a useful command to dump the specified table into a text file.

shell> mysqldump -u<user_name> -p<password> database_name table_name > file_name.sql

The above command, mysqldump, also dumps a table into a file but in addition it also includes the table drop and table creation statements in the file.

mysql> SOURCE file_name;

If you want to execute a set of SQL commands in one go -then write them into a text file (called a SQL script file and is generally stored with a .sql extension) and use the above command to execute them all. If you are on the shell prompt you can use the following command to do the same:

shell> mysql database_name < file_name

As I said this is a ready reference list for me, I keep on adding new statements to this list as I come across during my work. I hope these were useful for you. Please let me know if you have more such MySQL commands. We can grow this list together.

Leave a Comment

Your email address will not be published. Required fields are marked *