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 simply view the MariaDB server databases that have been created, we require to run the command using the SHOW SQL keyword as: SHOW DATABASES. When once we get the list of all databases existing on the server, we can select the one on which we want to start our operations and work. This work may be performed in two different ways such as using a PHP script and 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 if optionally available on its own shows which database names to counterpart. The other clauses such as LIKE and WHERE may be provided to select table rows by means of more common conditions.
- But the user will view simply those databases in MariaDB for which there is some type of privileges except having the global privileges for SHOW DATABASES. This kind of list can also be obtained with the command mysqlshow.
- When the MariaDB server was initiated with the option like — skip-show-database, then one cannot implement this command statement at all without having the privilege SHOW DATABASES. The database list as result fetched with the execution of SHOW DATABASES which is built on directories present in the data directory that is how databases are implemented in MariaDB. It may be possible that the result consists of directories that do not match to the definite databases.
- The SCHEMA used also provides database information using the table as Information_Schema_SCHEMATA_table. The schema stores info needed by the MariaDB/MySQL server as it executes containing different categorization as data dictionary tables holding the object metadata of database and also system tables implemented for some other functioning purposes.
How to list databases in MariaDB?
In MariaDB, we will list out the databases that is present 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 need to 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. You will then be logged in to the server as a root user and now able to continue to execute the rest of the commands as to show all the databases list.
We will apply the command as SHOW DATABASES, to output the result set displaying currently created databases with global privileges.
Again, if in need any user wants to list the table information available inside any databases then, we need to run the command as follows 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 as root users and admins with special privileges likewise we require to have the same with showing the list of databases.
Examples
There can be many examples illustrating the List Databases in MariaDB to be executed but first, there are some requirements that are needed as follows:
- There should be installed MariaDB/MySQL in the system operating Linux.
- 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 root user on the system you can follow and execute the commands to display databases by the means of a web-based interface – phpMyadmin and command line.
Let us show the list of MariaDB databases available in the server through the command:
SHOW DATABASES;
Output:
Here, you will see all the databases present in the server but if the user wants to display only specific databases following name conditions or pattern then, it will be executed as follows:
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’. Thus, after running only those databases are fetched with starts with ‘t’.
The clause LIKE is implemented to define the data pattern while retrieving the data from the server databases or tables which follows 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.
This clause LIKE can also be operated together in combination to other SQL query keywords like INSERT, SELECT, WHERE, UPDATE and DELETE. You need to generally provide the pattern of data that we require for to the clause and then, after executing the query will output the result as true or false. These symbols of % and _ are defined as wildcard characters which can also be used organized together in the LIKE query statement.
Conclusion
MariaDB List Databases is responsible to provide us the information about all databases in the server. This requires certain global privileges to operate and is specified to administrative or say 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
This is a guide to MariaDB List Databases. Here we discuss the definition, syntax, How to list databases in MariaDB? and example with code implementation respectively. You may also have a look at the following articles to learn more –
