EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB List Databases
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB List Databases

By Aanchal SharmaAanchal Sharma

MariaDB List Databases

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

For this MariaDB List Databases we can follow the syntax as follows:

SHOW (SCHEMAS | DATABASES) {LIKE ‘pattern’ | WHERE expressional statement};

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,754 ratings)
  • 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:

mariadb 1

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:

mariadb 2

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 –

  1. MariaDB GROUP_CONCAT
  2. MariaDB wait_timeout
  3. MariaDB Timezone
  4. MariaDB MaxScale
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more