MariaDB Basic Tutorial

Data is the most important part of any type of application. The necessary data for the application is required to store permanently. The database server is used to store the application data permanently. MariaDB is one of the popular database servers that is called the branch of a MySQL server. The different features of MariaDB, the way of installing MariaDB in Ubuntu, creating databases and tables, and performing various types of database-related common tasks are all explained in this tutorial in detail.

Contents:

1. MariaDB Basics
2. Differences between MariaDB and MySQL
3. Important features of MariaDB
4. Install MariaDB on Ubuntu
5. Create a user with all privileges
6. Make Connection with MariaDB
7. Create and Delete Database
8. MariaDB Data types
9. Create and Delete tables
10. CRUD operations
11. Use of clauses
12. Use of comparison operator
13. Use of logical operator
14. Use of aggregate functions

MariaDB Basics

It is an open-source relational database server that can be used with the application without any cost to store important data. It is the improved version of the MySQL server. A client application or any script can be used to make the connection with the database server. The default client of the MariaDB database is mysql. The connection to the MariaDB database can be created from the terminal by using the client. Any Graphical User Interface(GUI) tool can be used also to make the connection with the MariaDB database such as phpMyAdmin. You can use any server-side scripting language such as PHP, Perl, Python, etc. to make the connection with the MariaDB database server. The ways to make the database connections from the terminal, using the GUI tool and using the PHP script are shown in the “Make Connection with MariaDB” part of this tutorial.

Difference Between MariaDB and MySQL

MariaDB and MySQL have many major differences. Some of them are mentioned in the following table:

Important Features of MariaDB

Some important features of the MariaDB database server are mentioned in the following:

• It is open-sourced and licensed under GPL, LGPL, or BSD.
• It is robust and scalable.
• It uses popular and standard query language.
• It is supported by different operating systems.
• It can be used with different programming languages.
• It updates the index automatically.
• It supports many scripting languages and Galera cluster technology.

Install MariaDB in Ubuntu

You have to install MariaDB in the Ubuntu operating system to do the database-related tasks.

Run the following command to update the system:

Run the following command to install the MariaDB server:

Run the following command to execute the security script of the MariaDB that will help the users to modify some default settings of the MariaDB server:

The following output appears after executing the security script. You can set the root password here or keep the password blank by pressing enter:

Next, many other security options will appear and the user will select the required options to modify the default settings. After setting, run the following command to check the installed version of the MariaDB server and whether it is working properly or not:

The following output shows that the MariaDB version 10.5.15 has been installed:

Create a User with All Privileges

You can create a new user with all privileges by executing the following command with the username and password.

The following output shows that a user named ‘fahmida’ has been created with all privileges. The password of the user is ‘secret’.

Now, run the following command from the terminal to connect with the MariaDB server by using the new user credentials:

Make Connection with MariaDB

The connection to the MariaDB server can be implemented by using any client application or by using any scripting language that is discussed earlier. In this part, the way to connect MariaDB server by using mysql client, GUI interface, and PHP script is shown.

1. Using Mysql Client

Run the following command to connect with the MariaDB server using the mysql client:

The following prompt appears if the log in to the server is successful.

2. Using PHPMyAdmin

You have to install the PHPMyAdmin package earlier which is not shown here. Open the browser and run the following URL to open the login form to connect with the database server.

http://localhost/phpmyadmin

Type the valid username and password to log in to the server.

The following interface appears if the login to the server is successful. The version of the installed MariaDB server is shown in the Database server section.

3. Using PHP Script

Create a PHP file with the following script to make the connection with the database server. The script prints a success message if the database connection is successful. Otherwise, it prints a failure message.

The following output shows that the database connection is successful:

Create and Delete Database

The SQL to create and delete the Database is mentioned in the following:

Create Database Syntax:

CREATE DATABASE [IF NOT EXISTS] db_name

Here, IF NOT EXISTS option is optional. It is used to check whether the database is created before or not. If the database is created before, the database will not create again.

Example:

The following SQL statement creates the database named test if it is not created before.

The following output shows that the database is created successfully:

Delete Database Syntax:

DROP DATABASE db_name

Any existing database can be deleted by using the preivous DROP command.

Example:

The following output shows that the database test has been deleted:

MariaDB Data Types

The table of the MariaDB database supports the various data types to store the different types of data. MariaDB data types are categorized into four groups. These are given in the following:

1. Numeric Data Types
2. String Data Types
3. Temporal Data Types
4. Spatial Data Types

A. Numeric Data Types

Many types of the numeric data types can be used to store the number values in MariaDB database tables. The purposes of using the different numeric data types are described in the following table:

B. String Data Types

Many types of string data types can be used to store the character or string values in MariaDB database tables. The purposes of using the different string data types are described in the following table:

C. Temporal Data Types

Temporal data types are used to store the data and the time-related data in the database tables. The purposes of using the different temporal data types are described in the following table:

D. Spatial Data Types

Many spatial data types are used to store the various geographical values in the MariaDB database tables. The purposes of using the different spatial data types are described in the following table:

The uses of some data types for creating a table are shown in the next part of this tutorial.

Create and Delete Tables

The way of creating and deleting the tables of the MariaDB database are shown in this part.

Create Table Syntax:

Here, IF NOT EXISTS option is optional and it is used to create the table if the table is not created before. A unique table name is used after the CREATE TABLE keyword to create the table. Next, the list of column names with the data types and necessary attributes are defined for the table. Each column name is separated by a comma. Lastly, the storage engine name of the table is defined. The default storage engine name is InnoDB if no engine type is defined at the time of the table creation.

Each column definition of the table can contain the following options:

The unique name is used for each column of the table followed by a data type that is supported by the MariaDB database. The NOT NULL attribute is used with the column name if the column can’t store the NULL value. The DEFAULT VALUE attribute is used to store a default value in the column if no value is inserted in the column at the insertion time. The AUTO_INCREMENT attribute is used to increment the value of the by 1 automatically. This attribute is mainly used for the PRIMARY KEY column.

Example:
Run the following command to create a database named testdb if it is not created before:

Run the following command to select the testdb database:

Run the following command to create a table named products 6 fields of the different data types:

The following output appears if the table is created successfully:

Delete Table Syntax:

DROP TABLE [IF EXISTS] table_name;

IF EXISTS option can be used to check whether the table already exists or not before creating the table.

Example:

The following output appears if the table is deleted successfully:

CRUD Operations

CRUD operations indicate the four types of operations. These are Create/Insert, Read/Select, Update, and Delete records into the table. These operations are explained with the examples in this part.

1. Insert Data

Run the following INSERT query to insert a single record into the products table:

The following output appears if a new record is inserted successfully into the table:

Run the following INSERT query to insert three records into the products table:

The following output appears if three records are inserted successfully into the table:

2. Read Data

Run the following SELECT query to read all records from the products table:

The following output appears if the SELECT query works properly:

3. Update Data

Run the following UPDATE query that updates the values of the type and price fields if the id value of the row is 1.

The following output appears if the UPDATE query works properly:

4. Delete Data

Run the following DELETE query that deletes the record(s) from the products table where the value of the type is “Sugar”:

The following output appears if the DELETE query works properly:

Run the following DELETE query to make the table empty:

Use of Clauses

Different clauses are used with the SELECT, INSERT, UPDATE, and DELETE queries. The uses of commonly used clauses are mentioned in the following:

1. WHERE Clause
   It is used with the SELECT, INSERT, UPDATE, and DELETE queries to filter the data.
2. FROM Clause
   It is mainly used to select the data from the table.
3. DISTINCT Clause
   It is used to retrieve a unique data from the table.
4. ORDER BY Clause
   It is used to read the records of the table in sorted order.
5. GROUP BY Clause
   It is used to retrieve the records from the table by grouping one or more columns.
6. HAVING Clause
   It is used with the GROUP BY clause to retrieve the records based on the condition.

Example:
The uses of the ORDER BY clause are shown in the following example.

Run the following INSERT query to add three more records to the products table for testing purposes:

Run the following SELECT query to read all the records from the products table by ordering the price value in descending order.

The following output appears after executing the previous query:

Use of Comparison Operators

Different types of comparison operators are supported by the MariaDB server that is used with the WHERE clause of the query. Some commonly used comparison operators are mentioned in the following table:

Example:
Run the following SELECT query with IN operator to read all the records that contain the “Salt” and “Oil” in the type field of the products table:

The following output appears after executing the previous query:

Use of Logical Operators

Different types of logical operators are supported by the MariaDB server that is used with the WHERE clause of the query. Some commonly used logical operators are mentioned in the following table:

Example:
Run the following SELECT query that uses the logical OR to retrieve the records from the products table:

The following output appears after executing the previous query:

Use of Aggregate Functions

Different types of aggregate functions are supported by the MariaDB server that is used with the SELECT query. Some commonly used aggregate functions are mentioned in the following table:

Examples:
Run the following SELECT query that uses the COUNT() function to count the total number of rows of the products table:

The following output appears after executing the previous query:

Run the following SELECT query that uses the SUM() function to calculate the sum of the price field based on the value of the type field of the products table:

The following output appears after executing the previous query:

Run the following SELECT query that uses the MAX() function to find out the maximum price value of the products table:

The following output appears after executing the previous query:

Conclusion

The purposes of using the MariaDB database server and the basic uses of this server are explained in this tutorial. Many advanced-level features like the JOIN clause, functions, procedures, triggers, etc. are more useful features of this server that are not covered in this tutorial. The new database users will get the basic concept of the database server and be able to start working with the MariaDB server after reading this tutorial.