MySQL in a Nutshell, 2nd Edition

Show Me

Let’s take a moment to admire our work and see what we’ve done so far. To get a list of databases, use the SHOW DATABASES statement:

SHOW DATABASES;
   
+-----------+
| Database  |
+-----------+
| bookstore |
| mysql     |
| test      |
+-----------+

The result of the SHOW DATABASES statement lists not only the database we’ve created, but also two others. One is the mysql database, which contains data about user privileges and was covered in Chapter 2. The third database is the test database, which is set up by default when MySQL is installed. It’s there as a convenience for you to be able to add tables or run SQL statements for testing.

To see a list of tables in the bookstore database, once we select the bookstore database with the USE statement shown earlier, we would enter the following statement:

SHOW TABLES;
   
+---------------------+
| Tables_in_bookstore |
+---------------------+
| authors             |
| books               |
+---------------------+

The result of the SHOW TABLES statement provides a list containing our two tables, just as we expected. If you want to see a list of tables from another database while still using the bookstore database, add a FROM clause to the previous statement:

SHOW TABLES FROM mysql;

This displays a list of tables from the mysql database, even though the default database for the client session is the bookstore database.