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:
Select Statement
Query to Fetch the Specific Columns From the Table Employees
Query:
SELECT first_name, last_name FROM employees;
Output:
Select Statement
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:
Select Statement
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:
Select Statement
Aggregate Function
Query:
SELECT AVG(salary) AS average_salary FROM employees;
Calculates the average salary from the employees table.
Output:
Select Statement
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:
Select Statement
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.