MySQL SELECT Statement

Overview

In this tutorial, we will learn MySQL SELECT statement with examples. The SELECT statement is used to retrieve data from the MySQL database.

SELECT Statement

The SELECT is the most commonly used statement. It is a DML statement.(Data Manipulation Language). The SELECT statement is the description of data that is retrieved from the MySQL database server. The response result( often called as the result set) returned by the server takes the form of table.

The basic syntax of the of the statement:

mysql> SELECT [options] column_list / expressions

[FROM table_name(s)] [ clauses] ;

The column_list is the list of columns names that will appear in the query result. We can specify multiple columns in the SELECT query. To retrieve all the columns of the table we can use the * symbol in the SQL query.

Some clauses that can be specified in the SELECT statement are as follows:

SELECT clause	Description
DISTINCT	This clause is used to eliminate duplicate rows from the result set.
FROM	This keyword is used to specify the database table(s) to retrieve data from the database.
WHERE	This clause is used to filter specific rows from the result set.
ORDER BY	This clause is used to sort rows by specific order For example, ascending or descending.
LIMIT	This clause limits the maximum number of rows form the result set.

The clauses are added to modify the results of the query in some form. For example, WHERE clause to restrict or filter the rows. The GROUP BY clause to group the data, etc.

We can also format the SELECT query into multiple lines. This will increase the readability of the query.

For example,

SELECT [DISTINCT] columns_in_result
FROM table_name(s)
WHERE condition(s)
ORDER BY column_name sort_order
LIMIT row_count;

Examples

Select all columns and rows

To retrieve all the rows and columns of the department table. The * means all the columns.

mysql> SELECT * FROM department;

Select multiple columns

We can specify the columns in the result. We can specify multiple columns with comma as separator in the SELECT statement. For example, to retrieve Name and Capital columns in the Country table from the world MySQL database.

mysql> SELECT Name, Capital FROM Country;

The result set will contain the columns specified in the query.