MariaDB, a popular open-source relational database management system, offers a wide range of powerful features and capabilities, We discussed about it in my Legacy Powerhouse: MySQL and MariaDB Still Reign Supreme. In this Tech Concept, I will guide you with the fundamentals of MariaDB by covering essential query operations. We will walk through creating a schema, creating a table, inserting data of various data types, updating records, executing SELECT queries, and performing aggregate functions. Each section will provide detailed explanations along with relevant code snippets. Let’s dive in!

MariaDB uses SQL:

SQL (Structured Query Language) is a standardized programming language used to manage and manipulate relational databases. It serves as a powerful tool for interacting with databases and performing various operations, such as retrieving data, inserting records, updating information, and deleting entries. SQL queries allow you to specify precisely what data you want to retrieve or modify from a database by using a combination of keywords, functions, and clauses. By crafting well-structured and efficient SQL queries, you can retrieve specific data subsets, combine information from multiple tables, perform calculations, sort results, and filter data based on specific conditions. Having gained a brief understanding of SQL, let us now proceed towards its practical application with MariaDB..

Creating a Schema:
A schema in MariaDB serves as a logical container for database objects. To create a new schema, use the CREATE SCHEMA statement:

CREATE SCHEMA IF NOT EXISTS my_schema;

This statement creates a new schema named my_schema if it doesn’t already exist.

Creating a Table:
Tables hold data in MariaDB. To create a table within a schema, use the CREATE TABLE statement:

CREATE TABLE my_schema.my_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    salary DECIMAL(10,2)
);

This statement creates a table named my_table in the my_schema schema with columns for id, name, age, and salary.

Inserting Data:
To insert data into a table, use the INSERT INTO statement:

INSERT INTO my_schema.my_table (id, name, age, salary)
VALUES (1, 'John', 30, 50000.00),
       (2, 'Jane', 28, 45000.00);

This statement inserts two rows of data into the my_table table.

Updating Records:
To update existing records in a table, use the UPDATE statement:

UPDATE my_schema.my_table
SET salary = 55000.00
WHERE id = 1;

This statement updates the salary to 55000.00 for the record with id equal to 1.

SELECT Queries:
To retrieve data from a table, use the SELECT statement:

SELECT * FROM my_schema.my_table;

This statement retrieves all rows and columns from the my_table table.

Aggregate Functions:
In addition to the basic SELECT queries, MariaDB provides a variety of powerful aggregate functions that allow you to perform calculations on sets of values within a table. Let’s explore some commonly used aggregate functions:

COUNT:
The COUNT function returns the number of rows that match a specific condition or the total number of rows in a table. For example:

SELECT COUNT(*) AS total_rows
FROM my_schema.my_table;

This statement calculates and returns the total number of rows in the my_table table.

SUM:
The SUM function calculates the sum of a specific numeric column within a table. For example, to calculate the total salary of all employees:

SELECT SUM(salary) AS total_salary
FROM my_schema.my_table;

This statement returns the sum of the salary column in the my_table table.

AVG:
The AVG function calculates the average value of a specific numeric column within a table. For example, to determine the average age of employees:

SELECT AVG(age) AS average_age
FROM my_schema.my_table;

This statement computes the average value of the age column in the my_table table.

MIN:
The MIN function retrieves the minimum value of a specific column within a table. For instance, to find the minimum salary:

SELECT MIN(salary) AS min_salary
FROM my_schema.my_table;

This statement returns the smallest value in the salary column of the my_table table.

MAX:
The MAX function retrieves the maximum value of a specific column within a table. For example, to find the highest salary:

SELECT MAX(salary) AS max_salary
FROM my_schema.my_table;

This statement retrieves the largest value in the salary column of the my_table table.

These are just a few examples of the aggregate functions available in MariaDB. Understanding and utilizing these functions empower you to derive valuable insights from your data and perform complex calculations effortlessly.

My Tech Advice: Understanding the basics of MariaDB query operations is crucial for working with databases effectively. In this blog post, we covered creating a schema, creating a table, inserting data, updating records, executing SELECT queries, and performing aggregate functions. Armed with these fundamental operations, you can begin building robust and efficient database solutions with MariaDB. Experiment with the provided snippets, explore more advanced features, and unlock the full potential of MariaDB for your data management needs. Happy querying!

#AskDushyant