MySQL SHOW Command

MySQL SHOW Command

SHOW command is used to learn more about the databases found in the server, tables found in a database, and specific information about the columns or fields within a table.

Let’s take a look on the uses and examples of the SHOW Command.

We want to know the databases that reside on our MySQL server, to do that issue the command:

mysql>show databases;
+----------+
| Database |
+----------+
| mysql    |
| sample   |
| test     |
+----------+
3 rows in set (0.14 sec)

Note: the results may vary depending on the stored databases on your system.

After we have enumerated the databases on our server, next is to view the tables under a certain database. To be able to do that, first we have to select which database we are going to use or manage. To select a database issue the command:

mysql> use test;
Database changed

We are now inside the test database and we want to determine the available tables under the test database, to do that issue the command:

mysql> show tables;
+-----------------+
| Tables_in_test  |
+-----------------+
| product         |
| customer        | 
| users           |
+-----------------+
3 rows in set (0.01 sec)

 Note: to be able to determine the records inside the specific table visit this lesson.

The other use of the show command is to determine the specific information of a certain table such as the field name and the data types. To view the specific information of a table issue the command:

 mysql> show columns users;

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned |      | PRI | 0       | auto_increment |
| f_name | varchar(20)      | YES  |     | NULL    |                |
| l_name | varchar(20)      | YES  |     | NULL    |                |
|position| varchar(30)      | YES  |     | NULL    |                |
| age    | int(11)          | YES  |     | NULL    |                |
| salary | int(11)          | YES  |     | NULL    |                |
| email  |varchar(60)       | YES  |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

Note: there are two other ways to view the information of the table:show fields (show fields from table_name) and describe (describe table_name). The result is still the same.