MariaDB Display List of Databases

We will now talk about a few practical MariaDB commands. Since MariaDB is merely a forked-out version of MySQL, these very basic commands will get you started using MariaDB. You can also use them with MySQL.

This guide is intended for newcomers to MariaDB and beginning developers. It aids them in comprehending fundamental MariaDB commands. Below is the following topics that will be covered in this tutorial.

• How to Display a List of Databases in MariaDB
• How to Show Database Users in MariaDB
• How to Show Tables in MariaDB
• MariaDB Select Database
• How to Create a Database in MariaDB
• MariaDB Delete Database
• How to Connect to MariaDB Database
• MariaDB Display List of Database Size

MariaDB Display List of Databases

The command SHOW DATABASES is used to view all the databases on the MariaDB server. A similar term to SHOW DATABASES is SHOW SCHEMAS.

The syntax is given below.

SHOW DATABASES | SCHEMAS
    LIKE 'pattern' | WHERE expression

Where,

• SHOW DATABASES | SCHEMAS: It is the command to show all the databases of the MariaDB server.
• LIKE ‘pattern’: When used alone, the LIKE clause specifies which database names should be matched.
• WHERE expression: The WHERE and LIKE clauses can be used to pick rows using more general criteria.

Let’s take an example by following the below steps:

Use the below command to view all the databases.

SHOW DATABASES;

To search for the specific database based on any pattern use the LIKE, use the blow query to show the database whose name starts with c.

SHOW DATABASES
LIKE "c%";

The above output shows the databases starting with the letter C. This is how to display the all databases of the MariaDB server.

Read: MariaDB Vs SQL Server

MariaDB Show Database Users

A table called user contains the MariaDB users’ data. It contains all of the information on them.

So, you can use the following command to list the users.

SELECT User, Host FROM mysql.user;

In the above query. fetching the information from the mysql.user table.

The above output shows all the users of the MariaDB database. This is how to view all the users of the database.

Read: Check MariaDB Version

MariaDB Show Tables

A database’s non-TEMPORARY tables, sequences, and views are listed with the SHOW TABLES command.

Use the below query to show the all tables of the database classiccars.

SHOW TABLES;

The above command provided a list of every table in the current database, known as classiccars.

Read: Grant User Access to a MariaDB Database

MariaDB Select Database

If you access the MariaDB server without first explicitly selecting which database you want to work with, you will be prompted to choose one of the available databases to use as the current database.

To pick a particular database, you must issue the use statement in the following format.

use database_name

Where,

• use: It command to select the database.
• database_name: It is the name of the database that you want to display.

Let’s check with an example and select the specific database by following the below steps:

Use the below command to select the classiccars database.

use classiccars

The above output shows the database classiccars selected as we can see the result of the command is Database changed. This is how to select the MariaDB database.

Read: How to Create a Table in MariaDB

MariaDB Create Database

The CREATE DATABASE command is used to create a new database in MariaDB. The syntax is given below.

CREATE DATABASE database_name;

Where,

• CREATE DATABASE: It is the command to create a database.
• database_name: It is the name of the database that you want to create.

Let’s take an example and create a database by following the below steps:

Open the MariaDB command prompt as shown in the below picture.

Make sure you log in as the root user and admin when setting up a new database in MariaDB to take advantage of the unique privileges available to them exclusively. Start by entering the following command into your command line.

mysql -u root –p

You will be required to enter the password after entering the above command. The password you initially created while configuring MariaDB will be used here, and you will then be logged in.

As demonstrated by the command below, create the database using the “CREATE DATABASE” command.

CREATE DATABASE usaemployee;

After creating the database, select that database using the below command.

use usaemployee

The above output shows the newly created database named usaemployee. This is how to create a new database using the command CREATE DATABASE.

Read: How to Truncate Table in MariaDB

MariaDB Delete Database

We have already learned how to create a new database in the above subsection. Here we will learn how to delete the database using the command DROP DATABASE.

The syntax is given below.

DROP DATABASE database_name;

Where,

• DROP DATABASE: It is the command to delete the database.
• database_name: It is the name of the database that you want to delete.

Let’s take an example by following the below steps:

First, check the database on the MariaDB server using the below command.

SHOW DATABASES;

From the above output, we can see the list of databases. Now choose the database that you want to delete like usaemployee.

DROP DATABASES usaemployee;

From the above output, we have successfully deleted the database. This is how to delete the database using the command DROP DATABASE of MariaDB.

Read: MariaDB Foreign Key + Examples

MariaDB Connect to Database

Connecting to MariaDB and the fundamental connection parameters are covered in this section. A MariaDB Primer should be read first if you are absolutely unfamiliar with MariaDB.

The client application must offer the proper connection parameters in order to establish a connection to the MariaDB server. Most frequently, the client software will be the MySQL client, which is used to enter statements from the command line.

The syntax is given below.

mysql -h host_ip -u user_name -p_password db_name

Where,

• -h: A host is specified by h. It uses the IP address rather than localhost, if not provided by default localhost is considered.
• -u: Name of the user who is connecting the database.
• -p: Password is specified with the -p option. Keep in mind that, unlike the other arguments, the value and the option (-p) for passwords cannot be separated by a space (_password).
• db_name: After all the options, the database name is supplied as the first input.

Let’s take an example and connect to the MariaDB database directly from the command line.

Open the command prompt or terminal on your computer and enter the following command to connect to the database classiccars.

mysql -u root -p23456 classiccars

From the above output, we have connected to the database classiccars from the command line. This is how to connect to the MariaDB database.

Read: MariaDB Check Constraint

MariaDB Display List of Database Size

A system administrator needs to display the database size in order to predict when the database server’s hard disc will be full.

To know the size of the databases, first, you need to know about the Information Schema Tables of MariaDB. This table contains information about different non-TEMPORARY tables and views on the server displayed in the Information Schema table, with the exception of tables from the Information Schema database.

Second, we are going to know about the three columns data_length, index_length, and table_schema of the Information Schema Tables.

Use the below query to compute the database size in the MariaDB server.

SELECT table_schema AS "Databases", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size in MegaBytes (MB)" 
FROM information_schema.tables 
GROUP BY table_schema;

From the output, we can see the size of each database in the MariaDB server. This is how to display or compute the size of the databases.

You may also like to read the following SQL Server tutorials.

• MariaDB Set Auto Increment Value
• MariaDB Alter Table Add Index
• MariaDB Add Auto Increment Column

We have learned how to view all the databases, tables, and users of MariaDB. Also learned how to create, connect and delete the database. At last, found a way to compute the size of the database.

• How to Display a List of Databases in MariaDB
• How to Show Database Users in MariaDB
• How to Show Tables in MariaDB
• MariaDB Select Database
• How to Create a Database in MariaDB
• MariaDB Delete Database
• How to Connect to MariaDB Database
• MariaDB Display List of Database Size