Declaring Variable in MariaDB

Variables serve as essential components in database management systems like MariaDB facilitating the storage and manipulation of data within the database environment. Variable declaration in MariaDB is a fundamental concept, allowing users to store values temporarily, perform calculations, and streamline complex queries. In this article, we will delve into the various methods of variable declaration in MariaDB, providing detailed examples to illustrate their usage.

Variable in MariaDB

A Variable is the name of a storage location that holds a value of it. Generally, Variables are used to store data temporarily within the scope of a session or procedure execution. A variable can hold various types of data such as integers, strings, dates and more complex data types like arrays or JSON objects.

Variables in MariaDB are typically prefixed with the @symbol to define them from other identifiers like column or table names. For example, @my_variable is a valid variable name in MariaDB.

Variables serve several purposes in MariaDB, including:

• Temporary Storage: Variables can hold values temporarily during the execution of queries or stored procedures.
• Dynamic Query Construction: Variables are used to construct dynamic SQL queries where sub-parts of the query such as table names or column names are defined at runtime.
• Data Manipulation: The variables allow the users to perform calculations, comparisons as well as other data manipulation tasks within procedural code.
• Configuration Settings: Some variables in MariaDB are system variables that can control various aspects of the database server’s behavior such as maximum connections or buffer sizes.
• Overall the variables provide flexibility and power to MariaDB users. It allows them to effectively manage and manipulate data within the database environment.

Ways to Declaring Variable in MariaDB

To understand the Declaring Variable in MariaDB we need a table on which we will perform various operations. So here we will create a employees table.

Query:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (name, department, salary) VALUES
('Alice', 'Engineering', 60000),
('Bob', 'Engineering', 65000),
('Charlie', 'Marketing', 55000),
('David', 'Marketing', 58000),
('Emma', 'HR', 50000);

Output:

TABLE DATA

Method 1: Using SET Statement

The SET statement method is used for declaring variables in MariaDB. It allows for the assignment of values to variables.

Syntax:

SET variable_name = value;

Query:

SET @age = 30;

Output: No direct output. Variable @age is assigned the value 30.

Explanation: In the above query, We have declare a variable @age and assign a value of 30.

Method 2: Using DECLARE Statement

The DECLARE statement is typically used within stored procedures or blocks of procedural code. It enables the explicit declaration of variables along with their data types.

Syntax:

DECLARE variable_name datatype [DEFAULT value];

Query:

DECLARE employee_count INT DEFAULT 0;
SELECT COUNT(*) INTO employee_count FROM employees;
SELECT employee_count;

Output:

Explanation: In the above query, We have declares a variable called employee_count of type INT and initializes with a default value of 100.

Method 3: Using SELECT INTO Statement

The SELECT INTO statement is useful for assigning values from a query result directly to variables.

Syntax:

SELECT column_name INTO variable_name FROM table_name WHERE condition;

Query:

SELECT salary INTO @max_salary FROM employees WHERE department = 'Engineering';

Output:

Explanation: In the above query, We retrieve the maximum salary from the employees in the Engineering department and store it in the variable @max_salary.

Method 4: Using User-Defined Variables

User-defined variables in MariaDB are prefixed with the @symbol and can be assigned values using any valid SQL expression. They are session-specific and persist until the session ends.

Example:

Query:

SET @width = 5;
SET @height = 10;
SET @area = @width * @height;

Output:

Explanation: In the above query, We have calculate the area of a rectangle using user-defined variables called @width and @height and storing their result in the variable called @area.

Method 5: Using System Variables

MariaDB offers a range of system variables which is used for various purposes such as configuration settings and performance tuning.

Example:

SET GLOBAL max_connections = 1000;

Output: No output. The maximum number of connections allowed to the MariaDB server is set to 1000.

Explanation: In the above query, we set the maximum number of connections allowed to the MariaDB server using the system variable max_connections.

Conclusion

Mastering variable declaration in MariaDB is essential for effective database management and query optimization. Whether it’s for storing temporary values, retrieving query results, or configuring server settings, understanding the different methods of variable declaration empowers users to efficiently manipulate data within the database environment. By leveraging the diverse capabilities offered by variable declaration, users can enhance the performance and functionality of their MariaDB databases