MySQL is an open-source relational database management system that stores and retrieves data from structured tables. It is one of the most popular databases used by web developers and businesses worldwide. In MySQL, a database is a collection of tables, and each table consists of rows and columns. Listing databases in MySQL is an essential task that helps in managing databases and their tables. In this article, we will discuss how to list databases in MySQL.
There are various ways to list databases in MySQL, and we will discuss the most commonly used methods.
1. Using SHOW DATABASES Command
The SHOW DATABASES command is the most straightforward and easiest way to list all the databases in MySQL. To use this command, open the MySQL command-line client or any MySQL client of your choice and enter the following command:
SHOW DATABASES;
This command will display a list of all the databases present in the MySQL server. The output of the SHOW DATABASES command will look like this:
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
2. Using SELECT Statement from Information_Schema Database
Another way to list the MySQL databases is by querying the information_schema database. The information_schema database contains metadata about all the databases and tables present in the MySQL server. To list all the databases using the SELECT statement, enter the following command:
SELECT schema_name FROM information_schema.schemata;
This command will display a list of all the databases present in the MySQL server. The output of the SELECT statement will look like this:
+——————–+
| schema_name |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| test |
+——————–+
3. Using MySQL Workbench
MySQL Workbench is a graphical user interface tool that is used to manage MySQL servers. It provides a user-friendly interface to manage databases, tables, and queries. To list all the databases in MySQL Workbench, follow the steps below:
Step 1: Open MySQL Workbench and connect to the MySQL server.
Step 2: Click on the Navigator icon from the left sidebar.
Step 3: Under the Navigator, click on the Schemas tab.
Step 4: The Schemas tab will display a list of all the databases present in the MySQL server.
4. Using MySQL Command-Line Client
The MySQL command-line client is a command-line tool that is used to manage MySQL servers. To list all the databases in the MySQL command-line client, follow the steps below:
Step 1: Open the command prompt or terminal.
Step 2: Type the following command to connect to the MySQL server:
Mysql -u username -p
Replace the username with your MySQL username.
Step 3: Enter your MySQL password when prompted.
Step 4: Type the following command to list all the databases:
Show databases;
This command will display a list of all the databases present in the MySQL server.
5. Using PHP Script
PHP is a popular programming language used to develop dynamic web applications. To list all the databases using a PHP script, follow the steps below:
Step 1: Open a text editor and create a new file.
Step 2: Add the following code to the file:
<?php
$dbhost = 'localhost';
$dbuser = 'username';
$dbpass = 'password';
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
If(! $conn ) {
Die('Could not connect: ' . mysqli_error());
}
$sql = 'SHOW DATABASES';
$result = mysqli_query($conn, $sql);
If(! $result ) {
Die('Could not show databases: ' . mysqli_error());
}
While($row = mysqli_fetch_array($result)) {
Echo $row[0] . "<br>";
}
Mysqli_close($conn);
?>
Replace the username and password with your MySQL username and password.
Step 3: Save the file with a .php extension.
Step 4: Open the file in a web browser, and it will display a list of all the databases present in the MySQL server.
Listing databases in MySQL is an essential task that helps in managing databases and their tables. In this article, we have discussed the most commonly used methods to list databases in MySQL. You can choose any of these methods based on your preference and requirements. Always remember to secure your MySQL server by using strong passwords and limiting access to authorized users only.