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 Reply