MariaDB Select Statement- Detailed Guide

In this MariaDB tutorial, we will study the use of the MariaDB Select statements and we will also cover some examples. There are lists of the topic that comes under discussion:

• MariaDB select
• MariaDB select database
• MariaDB select query example
• MariaDB select top 10
• MariaDB select for update example
• MariaDB select for update
• MariaDB select distinct
• MariaDB select count
• MariaDB select all
• MariaDB select as
• MariaDB select all tables
• MariaDB select all columns except one
• MariaDB select all users
• MariaDB select a database
• MariaDB select as Name
• MariaDB select by id
• MariaDB select order by
• MariaDB select count distinct
• MariaDB select duplicates
• MariaDB select database version
• MariaDB select exists
• MariaDB select except
• MariaDB select empty
• MariaDB select first row
• MariaDB select from select
• MariaDB select from subquery
• MariaDB select from dual
• MariaDB select group by
• MariaDB select having
• MariaDB select without header
• MariaDB select last 24 hours
• MariaDB select last row
• MariaDB select last 10 rows
• MariaDB select last_insert_id()
• MariaDB select left join
• MariaDB select max
• MariaDB select multiple columns
• MariaDB select max date
• MariaDB select multiple tables
• MariaDB select most recent record
• MariaDB select max row
• MariaDB select min
• MariaDB select max id
• MariaDB select null
• MariaDB select not null
• MariaDB select no cache
• MariaDB select not in
• MariaDB select nth row
• MariaDB select output to file
• MariaDB select one row
• MariaDB select random rows
• MariaDB select without table

MariaDB Select Statement

In this section, we will learn how to use the MariaDB SELECT statement with the help of syntax and examples.

In MariaDB, the SELECT statement is used to recover records from one or more tables in the MariaDB. The simplest syntax form of the SELECT statement of the table is given below:

SELECT EXPRESSIONS FROM TABLE_NAME WHERE [CONDITIONS];

The full syntax form of the SELECT statement is given below:

SYNTAX:

SELECT [ ALL | DISTINCT ]
expressions
FROM tables
[WHERE conditions]
[GROUP BY expressions]
[HAVING condition]
[ORDER BY expression [ ASC | DESC ]]
[LIMIT [offset_value] number_rows | LIMIT number_rows OFFSET offset_value]
[PROCEDURE procedure_name]
[INTO [ OUTFILE 'file_name' options 
       | DUMPFILE 'file_name'
       | @variable1, @variable2, ... @variable_n ]
[FOR UPDATE | LOCK IN SHARE MODE];

The syntax explanation:

• ALL: The ALL constraints returns all matching rows of the table.
• DISTINCT: It removes the duplicate from result set from the table.
• EXPRESSIONS: The colun or calculations that we want to regain. If we can use * expressions (asterik) from to call all rows from the table.
• TABLES: The table from which we want to retreive all rows. There should be atleast one table from FROM clause.
• WHERE conditions: [Optional] By using the WHERE condition, it must met a records to be selected.
• GROUP BY expressions: [Optional]. It collects data from multiple records and group the select by one or more than one columns.
• HAVING condition: [Optional]. It is used in union with GROUP BY condition to restrict the grouped rows to those whose condition turn out to be TRUE.
• ORDER BY expression: [Optional]. It is used to arrange the records in ascending or descending by using the ASC or DESC constraint in the result set.
• LIMIT: [Optional]. If the LIMIT clause is used in the statment then it controls the maximum results to be retrieve.
• PROCEDURE: The procedure_name is the name of the procedure that process the data in the result set,
• INTO: It allows us to write the result set to a file or variable.
• FOR UPDATE: Until the transaction has completed, the records will be affected by the query are write_locked.
• LOCK IN SHARE MODE: The records can be affected by query by their transcations but it will not affected by update or delete those other transcations.

The sample example of using the SELECT statement is given below:

EXAMPLE:

The example to select all the rows from the Teacher_Activities Table is given below:

SELECT * FROM TEACHER_ACTIVITIES;

As we see in the above image, we have retrieved all rows from the Teacher_Activities table by using the SELECT statement.

Read: MariaDB Join with Examples

MariaDB Select Query Examples

In this section, we will see the syntax and the example of the MariaDB SELECT query. As we know that the SELECT statement is used to retrieve one or more rows of information from the table.

The syntax of the SELECT query is given below:

SYNTAX:

SELECT expressions FROM TABLE_NAME where [CONDITIONS];

The sample example of the SELECT query is given below:

First, let us show you all the columns from the USA_Basketball Table by using the SELECT statement.

SELECT * FROM usa_basketball;

As we see in the above image, we have retrieved all columns from the USA_BasketBall table by using the asterisk expression (*) before the FROM clause inside the SELECT statement.

EXAMPLE 1: Selecting the multiple columns from the USA_BasketBall Table by using the SELECT statement:

SELECT player_name,current_age FROM USA_BASKETBALL;

As we see in the above image, we have selected the PLAYER_NAME, CURRENT_AGE columns from the USA_BasketBall table by using the SELECT query.

EXAMPLE 2: Let’s use the WHERE conditions with the SELECT statement in the USA_BasketBall table.

SELECT * FROM USA_BASKETBALL WHERE CURRENT_AGE>=25;

• As we see in the above image, we have used the WHERE condition with >= operator to use as a condition and to check the age of the player in the Current_Age column.
• And we have retrieved all rows from the USA_Basketball table by using the asterisk expression in the SELECT statement.
• So it provided 4 rows by using the WHERE condition with the SELECT statement with the help of >= operator as (greater or equal sign operator).

EXAMPLE 3: Let’s use the DISTINCT clause with the SELECT statement in the USA_BasketBall Table.

SELECT DISTINCT(Current_Age),player_name FROM usa_basketball;

• As we see in the above image, we have used the SELECT query in the usa_basketball table with the distinct clause on the Current_Age column and also called the player_name column from the table.
• With the help of a distinct clause, it called all unique ages from the Current_Age column along with the player_name column.
• As the result, we have retrieved 8 rows from the Usa_basketball table with the help of a distinct clause on the current_age column along with player_name as the output.

Read: MariaDB Set Variable – Complete Guide

MariaDB Select a Database

In this section, we will learn how to select a database in MariaDB with the syntax and examples.

The USE database_name refers to the current database for the following statement expressed in the MariaDB. The database statement remains the default until another USE statement has been issued.

The syntax of the SELECT Database is given below:

SYNTAX:

USE DATABASE_NAME;

The sample example of the SELECT database is given below:

Let’s have a quick look at all databases by using the below query:

SHOW DATABASES;

As we see in the above image, we have shown all databases by using the SHOW statement for all databases created by the user as per requirement.

USE AIRBNB_DB;

As we see in the above image, we have selected the database as per the user’s choice and used it for some expressions on the table after using the USE statement.

Read: MariaDB Select Into + Examples

MariaDB Select Top 10

In this section, we will learn how to select the first 10 rows from the table by using the LIMIT clause in MariaDB with the syntax and example.

In MariaDB, the LIMIT clause is used to retrieve records from the table and the number of records will be returned based on the limit value. The syntax of the SELECT TOP 10 statement is given below:

SELECT EXPRESSIONS FROM TABLE_NAME WHERE [CONDITIONS] 
[ORDER BY  EXPRESSION [ASC | DESC]]
LIMIT ROW_COUNT;

The syntax explanation:

• expressions: The column or expressions that we have to retreive.
• tables: The tables that we want to retreive records from. There must be atleast one table from the FROM clause.
• conditions: The conditions that to be met from which records to be selected.
• ORDER BY expression: It must be used in the SELECT LIMIT statement so that we can order a result and target those result that we want to see as a return.
• LIMIT row_count: Specified a limited number of rows to be get as a result on row_count.

The sample example of the SELECT TOP 10 statement is given below:

Let’s first create and insert data in the WALMART table by using the INSERT and the CREATE TABLE statement is given below:

CREATE TABLE WALMART(
product_id INT PRIMARY KEY AUTO_INCREMENT,
Product_Name VARCHAR(50),
Product_Price FLOAT);

INSERT INTO WALMART (product_name,product_price) 
VALUES('BADDDASH T-Shirt',19.95),
('Vintage US American Flag T-shirt',14.95),
('EA FIFA 22',34.29),
('SIMS 4',23.99),
('Premium Beauty HairCare',200.00),
('Mellani Bed-Sheet',13.97),
('Modenna Face Mask Disposable',12.74),
('Derro Space Heaters',67.49),
('Annn Klein Watch',600.00),
('FitBit Charger',119.95),
('IT TAKES TWO XBOX Game',95.99),
('WAITIEE Wireless Charger',33.14),
('GrandeULips Cosmetic',216.00),
('PurePremium Blood Pressure Support',19.52),
('GLOBALWIN Ankle Boat Shoe for Women',13.99),
('PillowCase for Hair and Skin',12.74),
('Acer Inspire 5 Laptop',312.45);

SELECT * FROM walmart;

EXAMPLE:

SELECT * FROM walmart ORDER BY product_price DESC LIMIT 10;

As we see in the above image, we have limited only the top 10 products based on the product_price column in descending order.

So, by using the SELECT statement we have got the top 10 products inside the product_id, product_name, and product_price column in descending order but in a synchronized way.

Read: MariaDB regexp + Examples

MariaDB Select for Update

In this section, we will learn how to use the MariaDB SELECT FOR UPDATE statement with the syntax and examples.

In MariaDB, the SELECT FOR UPDATE statement allows us to lock the set records by using the SELECT statement. In that period, we can’t make any changes in the records to use the statement. It has the same locks searched for the MariaDB UPDATE statement which is used to set the rows.

The syntax of the MariaDB SELECT FOR UPDATE is given below:

SYNTAX:

SELECT * FROM TABLE_NAME
WHERE [CONDITIONS] FOR UPDATE;

The syntax explanation:

• SELECT statement: The select statement will give us the output result.
• TABLE_NAME: The tables that we want to retreive records from. There must be atleast one table from the FROM clause.
• conditions: The conditions that to be met from which records to be selected.
• FOR UPDATE: The columns which we want to update.

The sample example of the MariaDB SELECT FOR UPDATE statement is given below:

EXAMPLE:

SELECT * FROM usa_basketball WHERE current_age>=23 FOR UPDATE;

As we see in the above image, we have selected all columns from the usa_basketball table by using the SELECT statement with an asterisk sign (*) before the FROM clause and use as the condition to find the current_age column is greater than or equal to 23 with >= operator and update it by using the FOR UPDATE clause.

As the result, we have all details of all columns with 5 rows as the output by using the SELECT FOR UPDATE statement.

Read: MariaDB Row_Number Tutorial

MariaDB Select for Update Example

In this section, we will see another example related to MariaDB SELECT FOR UPDATE statement.

The syntax of the SELECT FOR UPDATE statement is given below:

SYNTAX:

SELECT EXPRRESSIONS FROM TABLE_NAME 
WHERE [CONDITIONS]
FOR UPDATE;

The sample example of MariaDB SELECT FR UPDATE statement is given below:

EXAMPLE:

SELECT player_name,nba_team FROM usa_basketballteam 
WHERE current_age<=23 FOR UPDATE;

In this image, we have used the FOR UPDATE clause to update the player_name and nba_team column from the SELECT statement.

And it is done based on the condition by using the WHERE clause to check the current_age column if it contains an age value less than or equal to 23 and to produce the output based on the result set.

As the result, we have details of player_name and nba_team by using the SELECT statement and the provide the output as 6 rows by using the FOR UPDATE clause with the help of the WHERE condition.

Read: MariaDB on Duplicate Key Update

MariaDB Select Distinct

In this section, we will learn how to use the MariaDB DISTINCT clause with the SELECT statement with the given syntax and examples.

The DISTINCT clause is used to remove the duplicate things from the result in the SELECT statement. The syntax of the DISTINCT clause in the SELECT statement is given below:

SYNTAX:

SELECT DISTINCT [expressions] from TABLE_NAME WHERE [CONDITIONS];

The syntax explanation:

• expressions: the columns that we want to retreive.
• TABLE_NAME: The table we want to retreive the rows from. There should be atleast one column in the FROM clause.
• WHERE conditions: [OPTIONAL]. The condition that mets for the records to be selected.

Note, while applying the DISTINCT clause in the SELECT statement of the table:

• When one expression is provided in the DISTINCT clause, the query will return unique vale of that expression.
• When one or more is provided in the DISTINCT clause, the query will return the combination of multiple expressions listed.
• In MariaDB, the DISTINCT clause doesn’t ignore the NULL values so during the result set it provides NULL value as the set value.

The sample example of the DISTINCT clause in the SELECT statement is given below:

Let’s have a quick look at all columns of the USA_basketballteam table by using the SELECT statement:

SELECT * FROM USA_basketballteam;

As we see in the above image, we have retrieved all columns data from the usa_basketballteam table by using the SELECT statement.

EXAMPLE 1: SINGLE EXPRESSION

Let’s have a look at how to use the distinct clause to remove duplicate values from a single expression by using the SELECT statement in MariaDB:

SELECT DISTINCT player_name FROM usa_basketballteam 
WHERE current_age>=21;

As we see in the above image, we have used the DISTINCT clause to remove duplicate rows from the USA_basketballteam table by using the >= operator in the SELECT statement.

By using the DISTINCT clause, we have got the output of 8 rows based on the condition for the current_age to check whether the age is greater than or equal to 21.

EXAMPLE 2: MULTIPLE EXPRESSIONS

Let’s have a look at how to use the DISTINCT clause to remove duplicates from more than one expression in the SELECT statement in MariaDB:

SELECT DISTINCT player_name,nba_team FROM usa_basketballteam 
WHERE current_age>=20;

• In this image, we have used the SELECT statement for the DISTINCT clause for multiple columns to remove duplicates from the USA_basketballteam table.
• And with the help of the WHERE clause as the condition to check the current_age for whose age is greater than or equal to 20.
• So as the result, 6 rows are given as the output for both player_name, nba_team columns in the usa_basketballteam table.

Read: MariaDB Window functions

MariaDB Select Count

In this section, we will learn how to use the MariaDB COUNT() function in the SELECT statement with the syntax and examples.

In MariaDB, the count function is used to return the count of an expression. The syntax of the COUNT() function in the SELECT statement is given below:

SYNTAX:

SELECT COUNT(AGGREGATE_EXPRESSION) FROM TABLE_NAME 
WHERE CONDITIONs [EXPRESSIONS];

The syntax explanation:

• aggregate_expression: In this column or expression where non-null values will be counted.
• table_name: The table from which we want to retreive data. And their should be atleast one column listed in the FROM clause in the table.
• WHERE conditions: There are the conditions that must be met for the reocrds to be selected.

The syntax example of the COUNT() function in the SELECT statement is given below:

Let’s have a quick review of all columns details by using the USA_BASKETBALLTEAM table by using the SELECT statement.

SELECT * FROM USA_BASKETBALLTEAM;

In this image, w have retrieved all column details by using the SELECT statement in the USA_BASKETBALLTEAM table.

EXAMPLES:

SELECT COUNT(Current_Age) as Current_Age from usa_basketballteam;

As we see in the above image, we have used the COUNT() function to calculate the current_age columns based on the rows and put them alias name as Current_Age column in the usa_basketballteam table.

Therefore, it showed 6 rows as the total count of the current_age column in the usa_basketballteam table.

Read: MariaDB Comment – How to use

MariaDB Select AS

In this section, we will learn how to use the MariaDB SELECT AS statement with the help of syntax and examples:

The AS keyword is known as ALIAS NAME which is used as a temporary name for columns or tables in the database.

DESCRIPTION:

MariaDB ALIAS is used to create temporary names for columns or tables.

• COLUMN HEADING: It is used in the result set which makes the user to read as a new name.
• TABLE HAEADING: It is used to shorten your MariaDB statement which makes it easier to read or when we are performing a self join (which means listing the table more than once in the FROM clause).

The syntax of the MariaDB SELECT AS statement is given below:

SYNTAX:

SELECT COLUMN_NAME AS ALIAS_NAME 
FROM TABLE_NAME; 

The syntax explanation:

• column_name: The original column name which we want to change into new_column_name by using the alias clause AS keyword.
• AS: [Optional] Whether we specify the AS keyword or it has no impact on the alias in MariaDB.
• alias_name: The temporary name provided to the original column in the result set.

NOTE:

• If the alias_name contains spaces then enclose it into the quotes.
• It is acceptable to use alias_name for the spaces in the column_name but is not the good practise to use spaces when we are aliasing the table_name as new alias_name.
• The alias_name is only valid within the scope of MariaDB statement.
• Most programmers will specify the alias_name while using in the column_name but not in the table_name.

Let’s have a quick view of the WALMART table by using the SELECT statement:

SELECT * FROM walmart;