How to display all MySQL databases

Database can be regarded as a container that specifically stores data objects. Each database has a unique name, and the names of the databases have practical meanings, so that it can be clearly See what data each database is used to store. There are system database and custom database in the MySQL database. The system database is the database that comes with the system after installing MySQL, and the custom database is the database created by user definition.

Display MySQL databases

The most common way to get a list of MySQL databases is to use the mysql client to connect to the MySQL server and runSHOW DATABASES command.

Access the MySQL server using the following command and enter your MySQL user password when prompted:

mysql -u user -p

❝

If you have not yet set a password for your MySQL user , you can omit the -p switch.

Execute the following command in the MySQL shell:

SHOW DATABASES;

This command will print the information of all databases to which the user has permissions List. The output will be similar to:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+
2 rows in set (0.00 sec)

SHOW SCHEMAS is another command that can be used to list databases, it is a synonym for the SHOW DATABASES command:

SHOW SCHEMAS;

The output is the same as when using the SHOW DATABASES command:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+
2 rows in set (0.00 sec)

Show all MySQL databases

To list all databases on the MySQL server, you need Log in as a user who can access all databases. By default, the root user has permission to view all databases. You can use SHOW DATABASES to list all databases.

Use the root user to log in to MySQL :

mysql -u root -p

Run the SHOW DATABASES command:

SHOW DATABASES;

You will see a list of all databases on the MySQL server:

How to display all databases in MySQL? How to display all databases in MySQL?

+--------------------+
|     Databases      |
+--------------------+
| information_schema |
| database_name      |
| mysql              |
| opencart           |
| wordpress          |
| performance_schema |
| sys                |
+--------------------+

7 rows in set (0.00 sec)

Filter results

If you want to filter the output of the command based on a specific pattern, please run# Use the LIKE clause when ##SHOW DATABASES.

SHOW DATABASES LIKE pattern;

For example, the following statement will return all databases whose names begin with "open":

SHOW DATABASES LIKE 'open%';
+--------------------+
| Database           |
+--------------------+
| opencart           |
+--------------------+
1 rows in set (0.00 sec)

❝

The percent sign (

%) represents zero, one or more characters.

If you want to perform a more complex search, you can query based on conditions from the

schemata table in the information_schema database.

The following statement will give you a list of all databases starting with "open" or "word":

SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE 'open%' OR
schema_name LIKE 'word%';
+--------------------+
| Database           |
+--------------------+
| opencart           |
| wordpress          |
+--------------------+
2 rows in set (0.00 sec)

Display MySQL databases from the command line

To get the database list without logging into the MySQL shell, you can use the

mysql command with the -e option (representing execute), or you can use mysqlshow Command to display database and table information.

This is especially useful when you want to use a MySQL database with a shell script.

Run the following command on the terminal to display a list of all databases:

mysql -u user -p -e 'show databases;'+--------------------+
| Database           |
+--------------------+
| information_schema |
| opencart           |
+--------------------+

The following is an example of using the

mysqlshow command:

mysqlshow -u user -p

The output will be Same output as in the previous command.

If you want to filter the output, you can use the grep command.

The above is the detailed content of How to display all MySQL databases. For more information, please follow other related articles on the PHP Chinese website!