MariaDB query examples

Let us check out a few MariaDB query examples.

• MariaDB list all databases
• Mariadb list all users
• MariaDB how to delete database
• What port does mariadb use
• MariaDB change user passowrd
• MariaDB root password default
• How to display active connection in MariaDB
• MariaDB create admin user
• MariaDB describe tables
• MariaDB show columns
• Connect to MariaDB command line
• MariaDB explain
• MariaDB create boolean field
• MariaDB rename user

MariaDB list all databases

In MariaDB, we can check the availability of all the databases using the SHOW DATABASES command.

To know all the databases in the current session or user use the below query.

SHOW DATABASES;

The above command or query will list all databases in the MariaDB.

This shows the databases available in our current session and that is information_schema, Mysql, performance_schema, and sys. These databases are already created when we install the MariaDB.

Let’s create our own database and list it.

CREATE DATABASE demo_database;

Here, we have used the CREATE DATABASE to create the new database as demo_database.

As we can see the newly created database is demo_database.

This is how to list all databases in MariaDB.

Read: How to create a database from an SQL file in MariaDB

Mariadb list all users

In MariaDB, The user table stores the username/password and all the information related to the users. The command that we use to list or show all the users in MariaDB is given below.

SELECT USER FROM mysql.user;

Here in the above code, we are selecting the USER from a table user that exists in the MySQL database.

The output shows that we have five users in our MariaDB.

This is how to list all users in MariaDB.

Read: MariaDB Insert Into

MariaDB how to delete database

In MariaDB, the database is dropped or removed using the DROP DATABASE statement. The full syntax is given below.

DROP DATABASE database_name;

• DROP DATABASE: It is the command to drop the specific database from MariaDB.
• database_name: It is the name of the database that we want to drop.

Here, we are going to drop the demo_database that we have created in the above subsection of this tutorial.

The output shows that the demo_database was removed from the current session.

This is how to delete a database in MariaDB.

Read: MariaDB create procedure

What port does mariadb use

The MariaDB uses the 3306, this port is also used by another database like MySQL.

We can check which port MariaDB uses using the below command.

SHOW GLOBAL VARIABLES LIKE 'PORT';

Read: How to Change Column in MariaDB

MariaDB change user passowrd

In MariaDB, the SET PASSWORD statement is used to assign the new password to an existing user.

Log into the MariaDB prompt using your current password.

The syntax to change the password is given below.

SET PASSWORD FOR 'USER'@'ADDRESS' PASSWORD('NEW_PASSWORD');

Where SET PASSWORD is the command, the user is the USER of the session, ADDRESS is the address of the machine where MariaDB is installed and NEW_PASSWORD is the password that we want to set.

After logging into MariaDB prompt, enter the below code to set the password.

SET PASSWORD FOR 'root'@'localhost' PASSWORD('United_Kindom');

The above code will set the password (United_Kindom) for the MariaDB root user which is installed on localhost.

This is how to change user passwords in MariaDB.

Read: MariaDB Update Statement

MariaDB root password default

When to log into MariaDB to secure it, we are going to need the current password for the root user. If we’ve just installed MariaDB, and we haven’t set the root password yet, the password will be blank, so we should just press enter here.

How to display active connection in MariaDB

In MariaDB, we can view the active connection or number of MariaDB server instances or threads are running.

Use the below query in the MariaDB prompt to check the number of MariaDB instances are running.

show status where variable_name = 'threads_connected';

As the output, it shows a number of Threads_connected values are 2 which means that are two active connections to the MariaDB server.

MariaDB create admin user

In MariaDB, the admin user has all the permission to create, delete and modify any database, table or schema.

Use the below syntax to create a new admin user.

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'your_password';

• CREATE USER: It is the command to create a new user.
• ‘admin’@’localhost’: It has two parts, first before the @ which is the username and the second is after @ which is the IP address of the MariaDB server or address of the MariaDB server where it is installed.
• IDENTIFIED BY: It is a way to provide the password to a new user as ‘Password`, we can use our own password here. Actually, it secures the new user with a password so that only the person who knows about the password can log in with this user.

Then after running the above code, use the below code to give all kinds of permission to a new user.

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

At last, run the below code to make sure that the user we have created now has full access to databases and tables.

FLUSH PRIVILEGES;

To list all users in MariaDB type the below command in the prompt of MariaDB.

SELECT User FROM mysql.user;

As we can see the admin user that we have created.

MariaDB describe tables

In MariaDB, the table columns can be viewed or shown using the DESCRIBE attribute. Sometimes we need to know about the number of columns in a table, this can be checked using the DESCRIBE attribute.

The syntax to view the table columns is given below.

DESCRIBE table_name;

• DESCRIBE: It is the command to view the table columns.
• table_name: It is the name of the table whose columns are viewed.

Let’s a create table as a Person using the below code.

CREATE TABLE Person(id INT,person_name VARCHAR(30),birth_date DATE, birth_place VARCHAR(30));

Now view the table columns using the below code.

DESCRIBE Person;

Using the above code, we are viewing the columns of the table Person.

From the above output, it shows that the table Person has three columns as person_name, birth_date and birth_place.

MariaDB show columns

In MariaDB, the SHOW COLUMNS command is similar to the command DESCRIBE that we have learned above section.

The syntax of the command is given below.

SHOW COLUMNS FROM table_name;

SHOW COLUMNS FROM: It is the command to view the columns from a table.

table_name: It is the name of the table whose columns are shown.

Here, we will use the same table Person.

SHOW COLUMNS FROM Person;

View the columns of table Person using the above code.

From the output, we can see that we have achieved the same result as we run the command DESCRIBE.

Connect to MariaDB command line

To connect the MariaDB command line, first search for the MySQL client(MariaDB 10.6) in the search bar of the windows.

Open the MySQL client(MariaDB 10.6) by double-clicking on it or pressing enter from a keyboard.

Now, enter the password for the MariaDB server to enter in the MariaDB command line.

Enter the password to connect the MariaDB server in the command line as shown in the above output.

As we can see from the above output, we have entered into the MariaDB prompt after entering the password.

MariaDB explain

The EXPLAIN statement is used to show how the SELECT, DELETE and UPDATE statement is executed.

• The alternate statement of EXPLAIN is DESCRIBE and SHOW COLUMNS.

The syntax is given below.

EXPLAIN SELECT | DELETE | UPDATE

Where EXPLAIN shows the execution process of the statement SELECT, DELETE and UPDATE.

Let’s understand using the below demonstration.

EXPLAIN SELECT * FROM sales;

Look how EXPLAIN shows the table name, number of keys in column key, the key length in column key_len and the number of rows in column rows, etc.

MariaDB create boolean field

In MariaDB, we can create a boolean field or column by defining field type as BOOLEAN, the boolean field accepts TRUE or FALSE values.

• The MariaDB change the data type from BOOLEAN to TINYINT data type internally.

The syntax is given below.

Field_name BOOLEAN

Where Field_name is the name of the table column and BOOLEAN is the data type of that field.

Let’s create a table with a field of type BOOLEAN using the below query.

CREATE TABLE Restaurant(id int, seat_availability BOOLEAN);

Where,

• CREATE TABLE: It is the command to create a new table as here we are creating a table as Restaurant.
• id int: It is a column or field of type integer to store the id of the column.
• seat_availability BOOLEAN: It is a column or field of type boolean to store the value in TRUE and FALSE forms.

The output of the code is given below.

This is how the boolean field is created in MariaDB.

MariaDB rename user

In MariaDB, the RENAME USER statement is used to rename the name of the existing MariaDB user. Before renaming any user name, first, make sure that the database has the privilege of CREATE USER and UPDATE.

The syntax is given below.

RENAME USER old_username TO new_username;

Where,

• RENAME USER: It is the statement to rename the name of the old user name to a new user name.
• old_username TO new_username: old_username is the name of a user whose name is going to change and new_username is the name that is assigned to the old user as a new user name.

Let’s create a new user as ‘Jack` using the CREATE USER statements.

CREATE USER 'Jack';

View the user using the below query.

SELECT User FROM mysql.user;

The above code shows the all user of MariaDB that exist in the table user of database mysql.

Let’s rename the user ‘Jack’ that we can see in the above output using the below code.

RENAME USER 'Jack' TO 'Jhon'@'localhost';

Here in the above code, we renaming the user name from 'Jack' to 'Jhon' that exist on the local database as specified @'localhost'.

View the renamed user using the below statement.

SELECT User FROM mysql.user;

As from the output, we have renamed the user from ‘Jack’ to ‘Jhon’.

This is how to rename the user in MariaDB.

You may like the following MariaDB tutorials:

• How to Remove User in MariaDB
• How to Grant All Privileges in MariaDB
• MariaDB DateTime Tutorial 
• MariaDB AUTO_INCREMENT + Examples
• How to Create View in MariaDB
• How to Grant User Access to a MariaDB Database
• How to Add Column in MariaDB

So in this tutorial, we have learned the “MariaDB query examples” and covered the following topics.

• MariaDB list all databases
• Mariadb list all users
• MariaDB how to delete database
• What port does mariadb use
• MariaDB change user passowrd
• MariaDB root password default
• How to display active conneciton in MariaDB
• MariaDB create admin user
• MariaDB describe tables
• MariaDB show columns
• Connect to MariaDB command line
• MariaDB explain
• MariaDB create boolean field
• MariaDB rename user