How to View and Switch Databases in MySQL

Viewing the List of Databases

The SHOW DATABASES command allows you to see all databases available on your MySQL server.

Syntax

SHOW DATABASES;

Example: Step-by-Step

Suppose you want to view all databases on your MySQL server. Here’s how you do it:

SHOW DATABASES;

Explanation of the code:

1. SHOW DATABASES – This command queries the MySQL server and returns a list of all databases your current user has permission to access.
2. Output – You will see a table listing database names such as information_schema, mysql, and any user-created databases.

This command is particularly useful for confirming that a database exists before performing operations like creating tables or inserting data.

Filtering Databases

You can also filter databases using a LIKE clause to find a specific database:

SHOW DATABASES LIKE 'Bookstore%';

This command lists all databases whose names start with Bookstore. Filtering is useful when you have many databases and need to locate one quickly.

Switching Between Databases

Once you know the database you want to work with, you can switch to it using the USE command.

Syntax

USE database_name;

Example: Step-by-Step

Suppose you have a database named BookstoreDB and want to work with it:

USE BookstoreDB;

Explanation of the code:

1. USE – This keyword tells MySQL to set a specific database as the active one.
2. BookstoreDB – The name of the database you want to switch to.

After executing this command, all subsequent SQL operations (such as creating tables or inserting data) will apply to BookstoreDB.

Confirming the Active Database

To verify which database is currently active, use the following command:

SELECT DATABASE();

This command returns the name of the currently selected database, helping you avoid mistakes when working with multiple databases.

Practical Tips for Managing Databases

1. Always check permissions: Your MySQL user may not have access to all databases. Use SHOW DATABASES to confirm access.
2. Use descriptive database names: Meaningful names make it easier to identify and switch between databases.
3. Confirm the active database: Using SELECT DATABASE() ensures you are working in the correct database before performing any critical operations.
4. Filter when necessary: For servers with many databases, filtering using LIKE can save time.