Mobile Number Tracker
Trace Mobile Number Earn Money Online
© TechWelkin.com

11 Useful MySQL Commands for Quick Reference

Samyak Lalit | July 3, 2008 (Last update: July 31, 2017)

Samyak Lalit is an Indian author and disability rights activist. He is the principal author and founder of projects like TechWelkin, WeCapable, Viklangta, Kavita Kosh among many others.

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.

© TechWelkin.com

Leave a Reply

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