MariaDB Select Statement


Objectives

  • Understanding MariaDB Select Statement

  • Select all Data from MariaDB Table

  • Select Specific Column from MariaDB Table

  • MariaDB Where & Condition Statement

  • MariaDB Select Specific data using Multiple Where Condition


Create database and table if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)


Understanding MariaDB Select Statement

Syntax

SELECT column1, AGGREGATION(column2), column3, ... FROM table_name WHERE conditions;
-- OR Show all columns from the table
SELECT * FROM table_name WHERE conditions;
Here

  • SELECT : SELECT is a keyword of MariaDB, responsible for read data from the database table.

  • column1, column2, column3 : Earlier you saw that table consist with column, during the SELECT (read) operation by specifying column name you can get data from specific column.

  • AGGREGATION(column2) : Aggregation functions such as COUNT(), MAX(), MIN() are perform special task which you lear later on this tutorial.

  • FROM : FROM is a keyword of MariaDB, which indicate the table name from of the database.

  • table_name : Where data will come from.

  • WHERE : WHERE is a keyword of MariaDB as well, which indicate what will be the conditions of data select.

  • conditions : Equal, Not equal, Greater than, Less than etc. conditions.

  • SELECT * : When you decided to get all column from table then can use the * symbol after select keyword.


Before play with select statement, run the below insert SQL

Please make sure you have created database and table called person, for make things easy, please see to tutorial beginning, there is table creation SQL.

INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 1, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', 'Mia', 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@pf.local', 26, 300),
	('Rakib', 'Mia', 'rakib@pf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@pf.local', 20, 250),
	('Touhid', 'Mia', 'hmtmcse.com@gmail.com', 30, 500);


Select all Data from MariaDB Table

Syntax

SELECT * FROM table_name;

Example

SELECT * FROM person;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  1 | Faiyaz     | Mia       | faiyaz@pf.local       |    1 |   5000 |
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  3 | Tahsin     | Mia       | tahsin@pf.local       |   10 |    150 |
|  4 | Jane       | Doe       | jane@pf.local         |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@pf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@pf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
7 rows in set (0.000 sec)


Select Specific Column from MariaDB Table

Syntax

SELECT column1, column2, column3, ... FROM table_name;

Example

SELECT first_name, last_name, age FROM person;

Output

+------------+-----------+------+
| first_name | last_name | age  |
+------------+-----------+------+
| Faiyaz     | Mia       |    1 |
| John       | Doe       |   19 |
| Tahsin     | Mia       |   10 |
| Jane       | Doe       |   26 |
| Rakib      | Mia       |   24 |
| Sagor      | Sowrov    |   20 |
| Touhid     | Mia       |   30 |
+------------+-----------+------+
7 rows in set (0.001 sec)


MariaDB Where & Condition Statement

Here you are going to use Greater than condition after WHERE, which will find the person age greater than 18

Syntax

SELECT * FROM table_name WHERE column_name > 18;

Example

SELECT * FROM person WHERE age > 18;

Output

+----+------------+-----------+-----------------------+------+--------+
| id | first_name | last_name | email                 | age  | income |
+----+------------+-----------+-----------------------+------+--------+
|  2 | John       | Doe       | john@pf.local         |   19 |    100 |
|  4 | Jane       | Doe       | jane@pf.local         |   26 |    300 |
|  5 | Rakib      | Mia       | rakib@pf.local        |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@pf.local      |   20 |    250 |
|  7 | Touhid     | Mia       | hmtmcse.com@gmail.com |   30 |    500 |
+----+------------+-----------+-----------------------+------+--------+
5 rows in set (0.380 sec)


MariaDB Select Specific data using Multiple Where Condition

Here you are going to use multiple condition Greater than and Less than. The condition will be age > 18 and income < 300

Syntax

SELECT * FROM table_name WHERE column_name > 18 AND column_name < 300;

Example

SELECT * FROM person WHERE age > 18 AND income < 300;

Output

+----+------------+-----------+------------------+------+--------+
| id | first_name | last_name | email            | age  | income |
+----+------------+-----------+------------------+------+--------+
|  2 | John       | Doe       | john@pf.local    |   19 |    100 |
|  5 | Rakib      | Mia       | rakib@pf.local   |   24 |    200 |
|  6 | Sagor      | Sowrov    | email10@pf.local |   20 |    250 |
+----+------------+-----------+------------------+------+--------+
3 rows in set (0.344 sec)


MariaDB Select Statement Video tutorial

Share