MariaDB

MariaDB

MariaDB Tutorial provides basic and advanced concepts of MariaDB. Our MariaDB Tutorial is designed for beginners and professionals both.

MariaDB is a community based project of the MySQL relational database management system. It is open source and relational database technology. It is a great replacement of MySQL.

Our MariaDB Tutorial includes all topics of MariaDB such asMariaDB Tutorial with what is mariadb, features, installation, heidisql, syntax, datatypes, connection, Datatabase, create database, select database, tables, crud operation, insert, limit, update, delete, statement, clauses, operators, aggregate function, sum, avg, MariaDB conditions, MariaDB join etc.

What is MariaDB
MariaDB is a community based project of the MySQL relational database management system. It is open source and relational database technology. It is a great replacement of MySQL.

MariaDB is developed by original developers of MySQL after concerns raised by Oracle’s acquisition of MySQL.

MariaDB is a relational database management system. It stores data in various tables. Primary keys and foreign keys are used to establish relationship between these tables.

Relational database management syatem provides the following features:

RDBMS facilitates you to implement a data source with tables, columns, and indices.
RDBMS provides integrity of references across rows of multiple tables.
It is used to automatically update indices.
It is used to interpret SQL queries and operations in manipulating or sourcing data from tables.

Terms used in RDBMS
Following is a list of terms used in relational database management systems i.e. MariaDB :

Database: A database is a data store which contains table and holds related data.

Table: A table is a matrix like structure which contains data.

Column: A column is s data element. It is a structure which holds same type of data. For example name.

Row: A row is a structure which stores related data. For example: data for a customer. It is also known as a tuple, entry, or record.

Redundancy: The term redundancy specifies how to store data twice in order to accelerate the system.

Primary Key: Primary key is a unique identifying value. This value cannot appear twice within a table, and there is only one row associated with it.

Foreign Key: A foreign key is used as a a link between two tables.

Compound Key: A compound key, or composite key, is a key that refers to multiple columns. It refers to multiple columns due to a column lacking a unique quality.

Index: An index is virtually identical to the index of a book.

Referential Integrity: This term refers to ensuring all foreign key values point to existing rows.

Features of MariaDB

MariaDB provides the same features of MySQL with some extensions. It is relatively new and advance.

Following is a list of features of MariaDB:

MariaDB is licenced under GPL, LGPL, or BSD.
MariaDB includes a wide selection of storage engines, including high-performance storage engines, for working with other RDBMS data sources.
MariaDB uses a standard and popular querying language.
MariaDB runs on a number of operating systems and supports a wide variety of programming languages.
MariaDB offers support for PHP, one of the most popular web development languages.
MariaDB offers Galera cluster technology.
MariaDB also offers many operations and commands unavailable in MySQL, and eliminates/replaces features impacting performance negatively.

MariaDB Data Types

Following is a list of data types in MariaDB:

String data types
Numeric data types
Date/time data types
Large object data types

MariaDB Functions

MariaDB function is a stored program that is used to pass parameters into them and return a value.

We can create and drop functions in MariaDB.

You can create your own function in MariaDB:

CREATE
[ DEFINER = { CURRENT_USER | user_name } ]
FUNCTION function_name [ (parameter datatype [, parameter datatype]) ]
RETURNS return_datatype [ LANGUAGE SQL
| DETERMINISTIC
| NOT DETERMINISTIC
| { CONTAINS SQL
| NO SQL
| READS SQL DATA
| MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘comment_value’
BEGIN
declaration_section
executable_section
END;

Parameter Explanation

Example:

Create a function CalcValue in MariaDB database.

DELIMITER //
CREATE FUNCTION CalcValue ( starting_value INT )
RETURNS INT DETERMINISTIC
BEGIN
DECLARE total_value INT;
SET total_value = 0;
label1: WHILE total_value <= 3000 DO
SET total_value = total_value + starting_value;
END WHILE label1;
RETURN total_value;
END; //
DELIMITER ;

DEFINER clause: it is an optional clause. If not specified, the definer is the user that created the function. If you wish to specify a different definer, you must include the DEFINER clause where user_name is the definer for the function.

function_name: It specifies the name to assign to this function in MariaDB.

return_datatype: It specifies the data type of the function’s return value.

LANGUAGE SQL: It is in the syntax for portability but will have no impact on the function.

DETERMINISTIC: It means that the function will always return one result given a set of input parameters.

NOT DETERMINISTIC: It means that the function may return a different result given a set of input parameters. The result may be affected by table data, random numbers or server variables.

CONTAINS SQL: It is the default. It is an informative clause that tells MariaDB that the function contains SQL, but the database does not verify that this is true.

NO SQL: An informative clause that is not used and will have no impact on the function.

READS SQL DATA: An informative clause that tells MariaDB that the function will read data using SELECT statements but does not modify any data.

MODIFIES SQL DATA: An informative clause that tells MariaDB that the function will modify SQL data using INSERT, UPDATE, DELETE, or other DDL statements.

declaration_section: The place in the function where you declare local variables.

MariaDB DROP Function
You can drop your created function very easily from your database.

Syntax:

DROP FUNCTION [ IF EXISTS ] function_name;
Parameter Explanation
function_name: It specifies the name of the function that you want to drop.

Example:

We have created a function name “CalcValue”. Now drop the function.

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.

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:
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.
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.

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:

Numeric Data Types
String Data Types
Temporal Data Types
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

B. String Data Types
Many types of string data types can be used to store the character or string values in MariaDB database tables.

C. Temporal Data Types
Temporal data types are used to store the data and the time-related data in the database tables.

D. Spatial Data Types
Many spatial data types are used to store the various geographical values in the MariaDB database tables.