Learning MySQL

Exploring Databases and Tables with SHOW and mysqlshow

We’ve already explained how you can use the SHOW command to obtain information on the structure of a database, its tables, and the table columns. In this section, we’ll review the most common types of SHOW statement with brief examples using the music database. The mysqlshow command-line program performs the same function as several SHOW command variants, but without needing to start the monitor.

The SHOW DATABASES statement lists the databases you can access. If you’ve followed our sample database installation steps in Chapter 3 in Loading the Sample Databases,” your output should be as follows:

mysql> SHOW DATABASES;
+------------+
| Database   |
+------------+
| flight     |
| music      |
| mysql      |
| test       |
| university |
+------------+
5 rows in set (0.01 sec)

These are the databases that you can access with the USE command; as we explain in Chapter 9, you can’t see databases for which you have no access privileges unless you have the global SHOW DATABASES privilege. You can get the same effect from the command line using the mysqlshow program:

$ mysqlshow --user=root --password=the_mysql_root_password

You can add a LIKE clause to SHOW DATABASES. This is useful only if you have many databases and want a short list as output. For example, to see databases beginning with m, type:

mysql> SHOW DATABASES LIKE "m%";
+---------------+
| Database (m%) |
+---------------+
| music         |
| mysql         |
+---------------+
2 rows in set (0.00 sec)

The syntax ...