Updated June 5, 2023
Definition of MariaDB List Databases
MariaDB List Databases is defined to be the command to list out all of the databases which reside on the MariaDB server involving the tables of a specific database or fetching information about user accounts along with privileges.
For this type of MariaDB commands to be implemented, firstly, you need to get logged in as the administrative or root user to access all privileges related to the databases and records in the server operations.
To view the MariaDB server databases that have been created, we require to run the command using the SHOW SQL keyword as SHOW DATABASES. Once we get the list of all databases on the server, we can select the one on which we want to start our operations and work. You can perform this task in two different ways: through a PHP script or directly from the command prompt.
Syntax:
For this MariaDB List Databases, we can follow the syntax as follows:
SHOW (SCHEMAS | DATABASES) {LIKE 'pattern' | WHERE expressional statement};- Here, the SHOW DATABASES command on the MariaDB server host defines the list of databases, whereas the SHOW SCHEMAS command defines the synonym for the previous command, SHOW DATABASES. Again, the LIKE clause, which is optionally available on its own, shows which database names to counterpart. You can use other clauses like “LIKE” and “WHERE” to select table rows based on more common conditions. These clauses enable you to specify specific conditions or patterns for selecting rows from a table.
- But the user will simply view those databases in MariaDB for which there are some privileges except having the global privileges for SHOW DATABASES. You can obtain this type of list using the command “mysqlshow” as well.
- If the MariaDB server is initialized with the “–skip-show-database” option, “SHOW DATABASES” command execution requires the privilege to use it. This command retrieves the list of databases implemented as directories in the data directory. This implementation in MariaDB relies on the directories within the data directory to represent the databases. It may be possible that the result consists of directories that do not match the definite databases.
- The SCHEMA used also provides database information using the table Information_Schema_SCHEMATA_table. The schema stores info needed by the MariaDB/MySQL server as it executes, containing different categorizations as data dictionary tables holding the object metadata of the database and system tables implemented for other functioning purposes.
How to list databases in MariaDB?
In MariaDB, we will list the databases in the system or, say, the server. For this, we need to login to the shell as a root user with the particular username and Password as – u root –p
After this, we must provide the root password for logging in the shell as: Enter Password. Otherwise, you can type the other option command to login as: – u root –pyour-specifed_passoword. After logging into the server as a root user, you can proceed to execute the remaining commands to display the list of all databases.
We will apply the SHOW DATABASES command to output the result set displaying currently created databases with global privileges.
Again, suppose any user wants to list the table information available inside any databases. In that case, we need to run the command by selecting the particular database and using it to display the tables: USE Databasename and the next command will be SHOW Tables.
You can also view the databases in your system using the command line terminal command and without logging to the shell as -u root –specified_password –e ‘SHOW DATABASES;’
Like for creating a new MariaDB database, we need to have granted root users and admins with special privileges likewise, we require to have the same with showing the list of databases.
Examples
Before executing the examples illustrating how to list databases in MariaDB, certain requirements need to be fulfilled. These requirements are as follows:
- To perform the task, it is necessary to have MariaDB/MySQL installed on a Linux operating system.
- The system is configured with the root password.
Now, to show all available databases on the basis of users having few kinds of rights granted to. After logging in to the MariaDB server as a root user on the system, you can follow and execute the commands to display databases through a web-based interface – phpMyadmin and command line.
Let us show the list of MariaDB databases available on the server through the command:
SHOW DATABASES;Output:
Execute the command to display only specific databases that follow name conditions or patterns.
SHOW DATABASES LIKE 't%';Output:
As you can see, the above query has a condition for retrieving the specific group of databases which starts with ‘t’. After executing the given command, only the databases that start with the letter ‘t’ will be fetched.
The LIKE clause defines a data pattern when retrieving data from server databases or tables. It allows users to retrieve data that matches a specific pattern. It allows for pattern matching and performs a precise match that is compulsory. The symbol % denotes the match either for 0 or more characters. We can also use _(underscore symbol) to have a match with a single character.
You can also combine the clause “LIKE” with other SQL query keywords such as INSERT, SELECT, WHERE, UPDATE, and DELETE. You need to generally provide the pattern of data that we require for the clause and then, after executing the query, will output the result as true or false. These symbols of % and _ are wildcard characters that can be used and organized together in the LIKE query statement.
Conclusion
MariaDB List Databases is responsible for providing us the information about all databases in the server. This requires certain global privileges to operate and is specified to administrative or root users only. As Maria uses the Galera cluster technology, it supports several storage engines with high enactment, integrating many relational databases and regulating their operations.
Recommended Articles
We hope that this EDUCBA information on “MariaDB List Databases” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
