SELECT Statement in MariaDB

MariaDB uses SQL (Structured Query Language) and it is an open-source relational database management system (RDBMS) for managing and manipulating data. The SELECT statement is the most basic of all the SQL statements. It is essential to get data out of one or more database tables and display it. In this article, we will examine the syntax of the SELECT statement in MariaDB to retrieve records from a table.

SELECT Statement

SELECT statement in MariaDB is used to data from one or more tables stored in a database. They include UNION statements, ORDER BY clauses, WHERE clauses, etc., and subqueries.

Syntax:

SELECT column1, column2, … FROM table_name  [WHERE condition];

• SELECT Clause: It specifies the columns you want to retrieve from the table.
  
• FROM Clause: It specifies the table from which the data will be retrieved.
  
• WHERE Clause: It filters the rows based on specific conditions.

Let's create a table of employees and insert some data into this.

Query for Create Table

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

Query for Insert Data

INSERT INTO employees VALUES
(1, 'John', 'Doe', 3, 50000.00),
(2, 'Jane', 'Smith', 2, 60000.00),
(3, 'Bob', 'Johnson', 1, 75000.00),
(4, 'Alice', 'Williams', 2, 65000.00),
(5, 'Charlie', 'Brown', 1, 70000.00);

Query to Fetch all the Fields From the Table Employees

Query:

SELECT * FROM employees;

Output:

Query to Fetch the Specific Columns From the Table Employees

Query:

SELECT first_name, last_name FROM employees;

Output:

Select Columns From Multiple Tables

Query:

SELECT employees.employee_id, employees.first_name, products.product_name FROM employees
JOIN products ON employees.employee_id = products.employee_id;

If you want to select a specific column from employees table and products table, let's say there is a common column called employee_id that joins these two tables we will use this type of query.

SELECT Statement with WHERE Clause

If we want to see the values in table with the specific conditions then we will use WHERE Clause with SELECT statement.

Query:

SELECT * FROM employees WHERE department_id = 2;

This retrieves all the columns from the "employees" table where the department ID is 2.

Output:

ORDER Data with ORDER BY Clause

Query:

SELECT * FROM employees ORDER BY salary DESC;

In order to arrange the results by salary in descending order, this fetches all columns from the "employees" table.

Output:

Aggregate Function

Query:

SELECT AVG(salary) AS average_salary FROM employees;

Calculates the average salary from the employees table.

Output:

Grouping Data

Query:

SELECT department_id, COUNT(*) AS employee_count FROM employees GROUP BY department_id;

Count the number of employees in each department by grouping the data according to the department ID.

Output:

Conclusion

MariaDB's strong and flexible SELECT statements allow users to access and modify data precisely. In order to effectively query databases and analyze data we have to first understand syntax and its other characteristics. It will definitely help you deepen your understanding of SELECT statements as you learn more about MariaDB. This article shows how to query data from a table using basic MariaDB SELECT statements.