MariaDB Basic Tutorial

Satish Kumar August 15, 2022
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:

MariaDB MySQL
It can be used without any cost. It has both free and paid versions.
It is released in 2009. It is released in 1995.
It is developed by using C, C++, Perl, and Bash languages. It is developed by using C and C++ languages.
It works faster. It works slower.
It supports many new database engines. It supports fewer database engines.
It does not support data masking and dynamic tools. It supports data masking and dynamic tools.
It supports large numbers of connections. It supports a small number of connections.
It supports binary encryption and a temporary table. It does not support binary encryption and a temporary 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:

$ sudo apt update

Run the following command to install the MariaDB server:

$ sudo apt install 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:

$ sudo mysql_secure_installation

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:

$ sudo mariadb

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.

GRANT ALL ON *.* TO 'fahmida'@'localhost' IDENTIFIED BY 'secret'
WITH GRANT OPTION;

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:

$ mariadb -u fahmida -p

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:

$ mysql -u fahmida –p

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.

<?php
//Initialize connection variable
$username = 'fahmida';
$password = 'secret';
$host = 'localhost';

//Create a connection with the database server
$db_connection = new mysqli($host, $username, $password);

//Check the connection
if(!$db_connection)
   die('Unable to connect with the database server.' . mysql_error());
         
echo 'Connected to the database server successfully.';
mysql_close($db_connection);
?>

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.

CREATE DATABASE IF NOT EXISTS test;

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:

DROP DATABASE test;

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:

Numeric Data types Purposes
BOOLEAN It is used to store TRUE or FALSE values.
TINYINT It is used to store a very small integer.
SMALLINT It is used to store a small integer.
MEDIUMINT It is used to store a medium-sized integer.
INT It is used to store an integer.
BIGINT It is used to store a very big integer.
DECIMAL It is used to store fixed-point numbers.
FLOAT It is used to store a single-precision floating-point number.
DOUBLE It is used to store a double-precision floating-point number.
BIT It is used to store a bit of value.

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:

String Data Types Purposes
CHAR It is used to store a fixed-length character string.
VARCHAR It is used to store a variable-length character string.
TINYTEXT It is used to store a very small non-binary string.
TEXT It is used to store a small non-binary string.
MEDIUMTEXT It is used to store a medium-sized non-binary string.
LONGTEXT It is used to store a large non-binary string.
TINYBLOB It is used to store a very small binary large object.
BLOB It is used to store a small binary large object.
MEDIUMBLOB It is used to store a medium-sized binary large object.
LONGBLOB It is used to store a very large binary large object.
BINARY It is used to store a fixed-length binary string.
VARBINARY It is used to store a variable-length binary string.
ENUM It is used to store the enumeration data.
SET It is used to store the set data.

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:

Temporal Data Types Purposes
DATE It is used to store the date value in the format, YYYY-MM-DD.
TIME It is used to store the time value in the format, hh:mm:ss.
DATETIME It is used to store the date and time value in the format, YYYY-MM-DD hh:mm:ss.
TIMESTAMP It is used to store the current date and time value in the format, YYYY-MM-DD hh:mm:ss.
YEAR It is used to store the value in 2-digits format (YY) or 4-digits format (YYYY).

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:

Spatial Data Types Purposes
POINT It is used to store point values (X and Y coordinates.
MULTIPOINT It is used to store the collection of point values.
POLYGON It is used to store polygon values.
MULTIPOLYGON It is used to store the collection of polygon values.
GEOMETRY It is used to store the spatial value of any type.
GEOMETRYCOLLECTION It is used to store the collection of geometry values.
LINESTRING It is used to store curve value.
MULTILINESTRING It is used to store the collection of LINESTRING values.

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:

CREATE OR REPLACE TABLE [IF NOT EXISTS] table_name(
    column_1_definition,
    column_2_definition,
    column_2_definition,
    ...,
    table_constraints
) engine=storage_engine;

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:

column_name data_type(length) [NOT NULL] [DEFAULT VALUE] [AUTO_INCREMENT] column_constraint;

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:

CREATE DATABASE testdb;

Run the following command to select the testdb database:

use testdb;

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

CREATE TABLE products(
    id int auto_increment,
    name varchar(255) not null,
    type varchar(50) not null,
    manufacturing_date date not null,
    expire_date date not null,
    price decimal(5,2) not null,
    primary key(id)
);

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:

DROP TABLE IF EXISTS products;

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:

INSERT INTO products
(name, type, manufacturing_date, expire_date, price)
VALUES ("Diploma Milk 1 KG","Milk","2022-01-12","2023-01-12", 600);

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:

INSERT INTO products
(name, type, manufacturing_date, expire_date, price)
VALUES ("FRESH Oil 5 KG","Oil","2022-06-15","2024-05-15", 900),
                 ("Teer Salt 1 KG","Salt","2022-02-10","2023-02-11", 50),
                 ("FRESH Sugar 1 KG","Sugar","2022-05-25","2024-01-19", 100) ;

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:

SELECT * FROM products;

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.

UPDATE products
SET type =  "Powder Milk", price = 650
WHERE id = 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”:

DELETE FROM products WHERE type = "Sugar";

The following output appears if the DELETE query works properly:

Run the following DELETE query to make the table empty:

DELETE FROM products ;

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:

INSERT INTO products
(name, type, manufacturing_date, expire_date, price)
VALUES ("Teer Oil 5 KG","Oil","2021-06-15","2023-05-15", 950),
                 ("Arong Salt 500G","Salt","2020-02-19","2022-12-11", 20),
                 ("FRESH Salt 1 KG","Salt","2022-06-25","2024-03-19", 55);

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

SELECT * FROM products
ORDER BY price DESC;

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:

Comparison Operator Purpose
!= It is used for not equal comparison.
< It is used for less than comparison.
<= It is used for less than or equal comparison.
<=> It is used for NULL-safe equal comparison.
= It is used for equal comparison.
> It is used for greater than comparison.
>= It is used for greater than or equal comparison.
BETWEEN AND It is used to check the values that are between the values.
NOT BETWEEN It is used to check the values that are not between the values.
IN It is used to check the values that match the list of the values.
NOT IN It is used to check the values that do not match the list of the values.
IS NOT NULL It is used to check whether the value is not NULL or not.
IS NULL</td> It is used to check whether the value is NULL or not.

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:

SELECT name, type, price
FROM products
WHERE type IN (“Salt”, “Oil”)
ORDER BY type;

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:

Logical Operator Purpose
! It is used for logical NOT comparison.
&& It is used for logical AND comparison.
XOR It is used for logical XOR comparison.
|| It is used for logical OR comparison.

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

SELECT name, price
FROM products
WHERE type = “Powder Milk” || type = “Salt”;

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:

Aggregate function Purpose
avg() It is used to find out the average of non-null numbers.
count() It is used to count the number of rows in a table.
group_concat() It is used to concatenate the string from a group with various options.
max() It is used to find out the maximum value in a set.
min() It is used to find out the minimum value in a set.
Sum() It is used to find out the sum of a particular numeric field.
Stddev() It is used to find out the population standard deviation.
Stddev_pop() Same as stddev()
stddev_samp() It is used to find out the sample standard deviation.
Var_pop() It is used to find out the population standard variance.
Varp_sam() It is used to find out the sample variance.
Variance() Same as var_pop().
Bit_and() It is used to find out the bitwise AND.
Bit_or() It is used to find out the bitwise OR.
Bit_xor() It is used to find out the bitwise XOR.

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

SELECT COUNT(id) as Total_Products
FROM products;

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:

SELECT type, SUM(price)
FROM products
GROUP BY type;

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:

SELECT name, type, MAX(price)
FROM products;

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.



from https://ift.tt/5gbDIOT
Tags:

Post a Comment

0 Comments