MySQL is a popular open-source database management software system and is freely available. It is famous for its speed and easy-to-use interface. If you are a database administrator in a big organization, you will likely often need to manage databases by listing them down and filtering through them. Perhaps, after successfully creating databases, you do not know how to list down the databases in the MySQL shell. So, in this article, we will look at some of the different methods that you can use to list databases in MySQL.
There are multiple methods available to list the databases in MySQL.
The simplest way to list down the databases is by using the ‘SHOW DATABASES’ command in the MySQL shell.
If you are not logged in as a root user in MySQL, you cannot access all the databases. Therefore, you must log in as a root user so that you have access to all the databases and are able to list down all the databases using the ‘SHOW DATABASES’ command.
So, first, log out of the MySQL shell using the \q command.
Next, log in as a root user using the following command.
Now, run the ‘SHOW DATABASES’ command.
As you can see in the output, the output has listed down more databases.
If you are an administrator managing several databases and you want to filter the databases, in MySQL, you can filter several databases at once using the ‘LIKE’ command.
The syntax for using the ‘LIKE’ command is as follows.
In this syntax, you will need to provide a specific pattern based on which to filter the list of databases. For example:
In this example, te % sign means that there can be zero, one, or more characters after the test.
In MySQL, you can use the ‘SHOW SCHEMA’ command, as well, to list the databases. This command will show the same list of databases as the ‘SHOW DATABASES’ command.
As you can see in the output, it displayed the same list of databases.
By using schemas and the ‘LIKE’ command, you can filter multiple databases. For example, to list two databases, whose name starts from the test and my, you can utilize the ‘SELECT’ statement to have such a result.
FROM information_schema.schemata
WHERE schema_name LIKE 'test%'
OR schema_name LIKE 'my%';
As you can see in the output, it has printed or displayed two databases based on the given condition.
You can even execute the MySQL shell command in the terminal without logging into MySQL and still have results. For example, you can show databases in the terminal using the following command:
You already know about the ‘sudo mysql’ part of this command. The ‘-e’ is used for executing the ‘SHOW DATABASES’ command. Now, if we run this command, it will print out the list of databases, as before.
So, these were some of the ways that you can use to list the databases in MySQL, according to your needs and requirements.
Conclusion
In this article, you learned how to list databases in MySQL using different techniques. You also learned that the “SHOW DATABASES” command shows databases on behalf of the privileges of a user, as well as how to list all the databases within a terminal. So, have fun with creating and listing databases yourself in MySQL.
