EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB List Databases

MariaDB List Databases

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated June 5, 2023

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.

ADVERTISEMENT
Popular Course in this category
MARIADB Course Bundle - 5 Courses in 1

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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:

mariadb 1

Execute the command to display only specific databases that follow name conditions or patterns.

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’. 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.

  1. MariaDB GROUP_CONCAT
  2. MariaDB wait_timeout
  3. Timezone in MariaDB
  4. MariaDB MaxScale
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

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

EDUCBA

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

Let’s Get Started

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW