Examples of Show/List Databases
Setting up the environment
Let’s first create some databases in MySQL.
CREATE database student;
CREATE database teacher;
CREATE database customer;
CREATE database orders;
CREATE database stud;
Example 1: Show/List all Databases
Let’s write a query to Show/List all the databases in MySQL.
Syntax:
Syntax 1:
SHOW databases;
Syntax 2:
SHOW schemas;
Query:
Query 1:
SHOW databases;
Query 2:
SHOW schemas;
Output:
Explanation: In the above example all the databases will be listed as we are not using the LIKE or WHERE clause. Both Let’squeries will give the same output. It will also print the default system databases.
Example 2: Show/List Databases using LIKE
Let’s write a query to Show/List all the databases starting with the letter ‘s‘.
Syntax:
Syntax 1:
SHOW databases LIKE pattern;
Syntax 2:
SHOW schemas LIKE pattern;
Query:
Query 1:
SHOW databases LIKE 's%';
Query 2:
SHOW schemas LIKE 's%';
Output:
Explanation: In the above example we are printing all the databases whose name starts with s. There are a total of 3 databases whose name starts with ‘s‘ including one system database. We are using LIKE keyword to specify the condition.
Example 3: Show/List Databases using WHERE clause
In MySQL, We cannot use the WHERE clause with SHOW DATABASES. Instead, we can fire the queries on the information_schema.SCHEMATA Table.
Let’s take an example of how we can use the ‘WHERE‘ clause to filter the database on some specific conditions.
Syntax:
SELECT schema_name
FROM information_schema.SCHEMATA
WHERE schema_name LIKE ‘Pattern’;
Let’s write a query to Show/List all the databases starting with the letter ‘s‘.
Query:
SELECT schema_name
FROM information_schema.SCHEMATA
WHERE schema_name LIKE 's%';
Output:
Explanation: In the above example we are printing all the schemas whose name starts with s. There are a total of 3 schemas whose name starts with ‘s‘ including one system schema. Here we cannot use the WHERE clause directly with SHOW DATABASE so we are using information_schema.SCHEMATA table to use the WHERE clause.
How to Show a List of All Databases in MySQL
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995.
MySQL is reputed for its sturdy and quick functioning attributes which involve easy-to-handle features and dependability. MySQL can normally be seen together with dynamic web applications and is generally used to serve languages such as PHP but also other server-side programming languages like Python. In this article, you will discover how to list all the databases in MySQL along with some examples.