List MySql Databases

List MySql Databases in MySql Command Line Client

Search MySql Command Line Client in Microsoft windows 10 and open it.

Enter mysql user password once opened.

Enter password:

Displays mysql prompt once logged in successfully.

Enter password: *****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Let us see how to list all the availabale databases in mysql database server.

show databases;

Output:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| pedia              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.48 sec)

Also we can use below command to get all the databases.

mysql> show schemas;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| pedia              |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

Filter mysql databases by pattern

like clause can be used to filter the databases based on pattern.

mysql> show databases like '%ma%';

Output:

+--------------------+
| Database (%ma%)    |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)

using like clause with show schemas.

mysql> show schemas like '%ma%';
+--------------------+
| Database (%ma%)    |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.15 sec)

How to use the required database from all the available database in mysql server.

mysql> use mysql;

Output:

Database changed

We can also list all the databases using schemata table.

mysql> use information_schema;
Database changed
mysql> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql              | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | information_schema | utf8                       | utf8_general_ci        |     NULL | NO                 |
| def          | performance_schema | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | sys                | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
| def          | jrbspedia          | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+--------------------+----------------------------+------------------------+----------+--------------------+
5 rows in set (0.01 sec)

mysql> select * from schemata where schema_name like 'mysql';
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
| def          | mysql       | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+-------------+----------------------------+------------------------+----------+--------------------+
1 row in set (0.01 sec)

mysql> select schema_name from schemata where schema_name like 'mysql'; +-------------+ | SCHEMA_NAME | +-------------+ | mysql | +-------------+ 1 row in set (0.11 sec)
« Previous Next »