Which MySQL query can be used with the help of which we can see the list of MySQL databases?
Answers
list all databases on a MySQL server host, you use the SHOW DATABASES command as follows:
1
SHOW DATABASES;
For example, to list all database in the local MySQL database server, first login to the database server as follows:
1
2
3
>mysql -u root -p
Enter password: **********
mysql>
And then use the SHOW DATABASES command:
1
2
3
4
5
6
7
8
9
10
11
12
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| classicmodels |
| information_schema |
| mysql |
| performance_schema |
| sys |
| test |
+--------------------+
6 rows in set (0.00 sec)
The SHOW SCHEMAS command is a synonym for SHOW DATABASES, therefore the following command returns the same result as the one above:
1
SHOW SCHEMAS;
If you want to query the database that matches a specific pattern, you use the LIKE clause as follows:
1
SHOW DATABASES LIKE pattern;
For example, the following statement returns database that ends with the string 'schema';
1
2
3
4
5
6
7
8
SHOW DATABASES LIKE '%schema';
+--------------------+
| Database (%schema) |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
It is important to note that if the MySQL database server started with --skip-show-database, you cannot use the database.
i hope this will help you.