MariaDB Tutorial

Introduction to MariaDB

Databases are primarily organized containers for information. Databases are everywhere. Nearly every website we observe and pretty nearly every shopping store we shop at has a database running quietly undercover.

MariaDB:

MariaDB is a database that has recognized the database community in the past few years; similarly, few others have. It was first launched in 2009.

MariaDB is a database system, a database server.
MariaDB is an improved version of MySQL.
MariaDB is a relational database management technology.
The original developers of MySQL develop it
It is open-source and assures to stay
It transforms data into structured information, used in a large variety of applications, from the bank and finance sector to websites.
Initially, it was developed as a replacement for MySQL; its main reason is that it is fast, scalable, and secure.
The wide range of storage engines, plugins, and many other tools allows us to use it in various uses.
It provides different features to store data in several tables.

There are few relational database terms one should be aware of before beginning this journey of the tutorial:

1. Database: It is a container for information, which holds tables consisting of data. It is a collection of well-ordered data saved on an electronic device, i.e., a computer. The database is managed by a Database Management System (DBMS). The data and the database management system combined, including the applications related to them, are called a database system. Using a database management system, we can access, manage, update, and modify the data with ease. There are various types of Databases.

i. Relational databases: Relational Database is a collection of data based on a relational model. The data stored in the database are related to each other. The data is stored in a structured form in relational databases, i.e., collection of rows and columns, which makes the table. For example,MariaDBis a relational database.

ii. Object-oriented databases: Object-oriented databases are based on object-oriented programming. The data is referred to as a complete object and is represented as an object. For example, MongoDB is an Object-oriented database.

iii. Distributed Database: Distributed Database is a collection of databases that are not stored in a single place instead distributed on multiple sites or computers connected through a network. Physically it is distributed, but logically for an application, it is accessible and manageable as a single database.

iv. NoSQL databases: NoSQL databases, also known as non-relational databases, are collections of unstructured or semistructured data. Firebase is a NoSQL database. We often use these types of databases for web applications.

2. Table: It is a structure carrying data. A table is a collection of rows and columns which store data. In relational database tables are referred to as Relations. Relations/ Tables are used to store related data.

Following structure represents Table:

MariaDB [university]> Select * from student;

+------+--------+------+

| s_id | name | age |

+------+--------+---+

| 101 | sumit  | 15 |

| 102 | Dkash | 16 |

| 103 | Nikhil | 14 |

| 104 | Mihir  | 13 |

| 105 | Aahana | 15 |

| 108 | Niel       | 16 |

+------+--------+------+

3. Column: Column is a vertical collection of data of a particular type. In a relational database, columns are referred to as fields. We can also call them attributes as they store data of a specific type.

Following highlighted part shows a column:

+------+--------+------+

| s_id | name | age |

+------+--------+------+

| 101 |sumit | 15 |

| 102 | Dkash | 16 |

| 103 | Nikhil | 14 |

| 104 |Mihir | 13 |

| 105 |Aahana | 15 |

| 108 | Niel | 16 |

+------+--------+------+

3. Row: It is a structure where we can store data of related types. The row is a set of fields that form a record. Rows are referred to by different names as record, tuple, and entry.

Following represents row:

+------+--------+------+

| s_id | name | age |

+------+--------+------+

| 101 | sumit | 15 |

| 102 | Dkash | 16 |

| 103 | Nikhil | 14 |

| 104 | Mihir | 13 |

| 105 | Aahana | 15 |

| 108 | Niel | 16 |

4. Primary Key: Primary key is a constraint we can apply on a column that prevents data redundancy. It allows us to identify each row in the table uniquely. We can apply the primary key as a constraint to a single column or multiple columns through which the uniqueness of a table is maintained.

MariaDB [university]>desc student;

+-------+-------------+------+-----+---------+-------+

| Field | Type          | Null  |Key  | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| s_id   | int(11)       | NO    | PRI   | NULL |          |

| name | varchar(20)| YES |           | NULL |         |

| age    | int(11)        | YES |           | NULL |         |

+-------+-------------+------+-----+---------+-------+

In the above table, column- s_id is defined as the primary key.

Foreign Key: Foreign key is a constraint that is used to represent a relationship between two tables. We link two tables through the primary key of another table which means one attribute of a table refers to the primary key of another table.
Index: The index is similar to the index of a book. The index makes the fetching of data accessible. It is a structure that is used to reduce the time required to retrieve data.

Features of MariaDB:

MariaDB provides similar features as MySQL with some extensions.

Following are key features that MariaDB provide:

MariaDB is certified under GPL, LGPL, or BSD.
MariaDB 10.3.4 makes it easy with a spider, a storage engine having inbuilt data partitioning and sharding.
MariaDB supports a large variety of programming languages and also runs on several systems.
MariaDB also added JSON support as Standard Feature. This feature allows you to insert JSON Documents in a particular identified column.
To explore relationships among data more efficiently, we use graph databases which allow you to store data. MariaDB lets you execute graph processing along with conventional SQL queries using OQGRAPH Store Engine.
MariaDB provides features to imitate the behavior of Oracle databases, precisely PL/SQL Language.
In version 10.3.4, MariaDB added a native feature of system versioned tables. This feature enables the database to track the versions of rows of tables.
In the latest version MariaDB 10.5, the S3 engine is added. So You can straight away shift the table to Amazon S3 from a local device by using ALTER.
Now, along with MariaDB 10.5 community server, MariaDBColumnStore 1.5 is also available. Earlier, the Column Store was there as a distinct part, but now it is integrated completely.
Available privileges are now more atomized. Super privileges are divided into small-scale privileges.
MariaDB provides the advanced Galera Cluster technology.
It also supports a web development language- PHP.