MariaDB Display List of Databases

This MariaDB tutorial will discuss the execution of the query to display a list of databases in MariaDB. To assist you in better understanding the topic, we shall explore and conclude several situations in this section. The whole list of topics we’ll cover is given below.

  • MariaDB Display List of Databases
  • MariaDB Show Database Users
  • MariaDB Show Tables
  • MariaDB Select Database
  • MariaDB Create Database
  • MariaDB Delete Database
  • MariaDB Connect to Database

MariaDB Display List of Databases

Here we will learn how to show a list of databases by the query, which will be explained with the help of syntax and an illustrated example.

In MariaDB, the SHOW DATABASES statement allows listing databases from the MariaDB server by a query. Let’s see the syntax of the MariaDB SHOW DATABASES statement by the following query:

SYNTAX:

SHOW DATABASES
[ LIKE 'PATTERN' | WHERE SEARCH_EXPRESSION ];

In this syntax explanation:

  • We can’t use the LIKE clause or WHERE condition at the same time. They specify a condition to search in the database. But if we skip the LIKE clause or WHERE condition then it will show all databases in the MariaDB server.
  • The MariaDB SHOW SCHEMAS is a synonym of the SHOW DATABASES therefore we can use them interchangeably.

Here’s a sample example of the MariaDB SHOW DATABASES statement by the following query:

EXAMPLE:

SHOW DATABASES 
LIKE '%a%';

As we see in the above query, we have used the SHOW DATABASES statement to show the list of all databases from the MariaDB server whose name is between the alphabet a by the query.

Example of MariaDB show list of databases
Example of MariaDB Show List of Databases

We hope that you have understood the subtopic “MariaDB Display List of Databases” by using the MariaDB SHOW DATABASES statement by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Set Auto Increment Value

MariaDB Show Database Users

Here, using an example to show, we will learn and comprehend how to utilize a query to connect to a database user on the MariaDB server.

In MariaDB, the mysql.db table is used to contain information about the database level privileges. The best way is to use the GRANT for setting privileges and it is also possible to directly update the table.

EXAMPLE:

SELECT USER,Db,Host FROM MYSQL.DB;

In the above query, the SELECT statement retrieves all records of the USER, DB, and HOST columns from MySQL.DB table. In the USER column, it will carry out the user_name which has been recently created or added before in the table. In the DB column, it will carry created or existing database_name in the table. The HOST column will carry out the hostname in the mysql.db table.

MariaDB show database users example
Example of MariaDB Show Database Users

We hope that you have understood the subtopic “MariaDB Show Database Users” by using the MariaDB SELECT statement on the mysql.db table in the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Show Column Data Type

MariaDB Show Tables

Here we will learn how to show tables from the database by the query, which will be explained with the help of syntax and an illustrated example.

The MariaDB SHOW TABLES statement allows us to list the non-temporary tables, views, and sequences from the current database. Here is the syntax of the MariaDB SHOW TABLES statement by the following query:

SYNTAX:

SHOW [ FULL ] TABLES
[ FROM YOUR_DATABASENAME ]
[ LIKE 'PATTERN' | WHERE 'SEARCH_EXPRESSION' ];

In the syntax explanation:

  • If we have already selected the current database then the SHOW TABLES statement will return the tables, views, and sequences from the current_database.
  • In case, we have not connected to any database, we can use the FROM clause to specify the name of the database from which we want to show tables, views, and sequences.
SHOW FULL TABLES FROM CURRENT_DATABASE;

EXAMPLE:

SHOW FULL TABLES 
FROM AIRBNB_DB;

In the aforementioned query, we have used the SHOW TABLES statement with the FULL keyword in the AIRBNB_DB database. This means that while adding the FULL keyword in the SHOW TABLES statement which will provide additional information as TABLE_TYPE column in the result set.

And we have not connected to any database, so with the FROM clause, we will get the list of all tables from the current_database from the above query.

MariaDB show tables example
Example of MariaDB SHOW TABLES statement

We hope that you have understood the subtopic “MariaDB Show Tables” by using the MariaDB SHOW TABLES statement on the current database by the query. We have used an example and explained it in depth, for better explanation.

Read: MariaDB Order By Multiple Columns

MariaDB Select Database

We will learn how to select the database from the MariaDB Server by the query, which will be explained with the help of syntax and an illustrated example.

SYNTAX:

USE CURRENT_DATABASENAME;

EXAMPLE:

SHOW DATABASES;

USE AIRBNB_DB;

In the preceding query, first, we will use the SHOW DATABASES statement to list all database names from the MariaDB Server. Then we will use the USE statement to select the database name in which we want to work on any table by the query.

We hope that you have understood the subtopic “MariaDB Select Database” by using the MariaDB USE statement in the MariaDB Server by the query. We have used an example and explained it in deepness, for better explanation.

Read: MariaDB Alter Table If Exists

MariaDB Create Database

In this MariaDB subtopic section, we will learn how to create a database in the MariaDB Server by the query, which will be explained with the help of syntax and an illustrated example.

SYNTAX:

CREATE [ OR REPLACE ] DATABASE [ IF NOT EXISTS ] YOUR_DATABASE_NAME
[CHARACTER SET = CHAR_NAME]
[COLLATE = COLLATION_NAME];

In this syntax explanation:

  • First, we need to specify the database name that we want to create after the CREATE DATABASE keywords. The Database name should be unique in the MariaDB server instance. But if we create a database name with the existing database name then it will throw an error.
  • The second option is, that the OR REPLACE clause instructs MariaDB to drop the database first if it exists before creating a new database.
  • Third, we will use the IF NOT EXISTS option to conditionally create a database name if it does not exist. In another way, if we create a database that already exists with the IF NOT EXISTS option then the MariaDB will not do anything.

EXAMPLE:

CREATE DATABASE MariaDBTips;

In this query, we have used the CREATE DATABASE statement to create a database called MariaDBTips in the MariaDB Server query.

We hope that you have understood how to use the MariaDB CREATE DATABASE statement to create a database in the MariaDB Server by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB If statement in Select

MariaDB Delete Database

Here we will learn how to delete a database in the MariaDB Server by the query, which will be explained with the help of syntax and an illustrated example.

In MariaDB, the DROP DATABASE statement is used to delete a database from the current MariaDB server by the query. We hope that you know that we can’t undo this statement so we have to be careful with this statement. Here is the syntax of the MariaDB DROP DATABASE statement by the following query:

SYNTAX:

DROP DATABASE [ IF EXISTS] YOUR_DATABASE_NAME; 

In this syntax explanation:

  • First, we need to specify the database name that we want to remove after the DROP DATABASE statement.
  • Second, we will use the IF EXISTS statement to conditionally drop a database if not exists in the MariaDB server. If we drop a nonexistent database without the IF EXISTS option then MariaDB will throw an error.

EXAMPLE:

SHOW DATABASES;

DROP DATABASE IF EXISTS MariaDBTips;

In the above query, we have used the SHOW DATABASES statement to show a list of all databases in the MariaDB Server. Then we will drop a database called MariaDBTips with the IF EXISTS clause to check if this database exists in the MariaDB Server or not then delete it by using the DROP DATABASE statement.

We hope that you have understood the subtopic “MariaDB Delete Database” by using the MariaDB DROP DATABASE statement in the MariaDB Server by the query. For a better understanding, we have used an example and explained it in depth.

Read: MariaDB Select Unique

MariaDB Connect to Database

Here we will learn how to connect to a particular database in the MariaDB Server by the query, which will be explained with the help of syntax and an illustrated example.

Any MariaDB client software that has the right parameters, such as hostname, user name, password, and database name, can be used to connect to MariaDB. The following query will show the connection to the MariaDB Server by localhost:

mysql -u [user_name -p [ password ]

In the syntax explanation:

  • -u specifies the name of the user.
  • -p specifies the password which is created by the user. Note that the password is followed immediately after the -p option.

For example, the query will show the connection to the MariaDB Server by localhost:

mysql -u root -p G@meison00

In this query, the ROOT is the username and G@meison00 is the password that is created by the user account as ROOT.

So, to connect to the specific database, we specify the database name after all options:

SYNTAX:

mysql -u [username] -p[password] -h [hostname] database_name

EXAMPLE:

mysql -u root -p -h localhost Airbnb_db
Enter password: ********

The above command connects to the AIRBNB_DB database of the MariaDB server by the local host. The ENTER PASSWORD will come automatically after you hit the ENTER button from the keyword then just type your password and we are ready to enter inside the current_database of the MySQL Server.

We hope that you have understood the subtopic “MariaDB Connect to Database” by using the command prompt in the MySQL Client. We have used an example and explained it in depth, for better understanding.

You may also like to read the following MariaDB tutorials.

This MariaDB lesson should help you learn how to utilize it. After reading this lesson, MariaDB display List of Databases. To assist you to understand the concept, we also covered a few examples. Here is a list of every subject we have discussed.

  • MariaDB Display List of Databases
  • MariaDB Show Database Users
  • MariaDB Show Tables
  • MariaDB Select Database
  • MariaDB Create Database
  • MariaDB Delete Database
  • MariaDB Connect to Database