MySQL SELECT statement
last modified January 10, 2023
This part of the MySQL tutorial will be covering the SELECT
statement
understood by the MySQL in detail.
Retrieving data
The following SQL statement is one of the most common ones. It is also one of the most expensive ones.
mysql> SELECT * FROM Cars; +----+------------+--------+ | Id | Name | Cost | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ 8 rows in set (0.00 sec)
Here we retrieve all data from the Cars
table.
Selecting specific columns
We can use the SELECT
statement to retrieve specific columns.
The column names follow the SELECT
word.
mysql> SELECT Name, Cost FROM Cars; +------------+--------+ | Name | Cost | +------------+--------+ | Audi | 52642 | | Mercedes | 57127 | | Skoda | 9000 | | Volvo | 29000 | | Bentley | 350000 | | Citroen | 21000 | | Hummer | 41400 | | Volkswagen | 21600 | +------------+--------+ 8 rows in set (0.00 sec)
We retrieve the Name
and the Cost
columns. The column
names are separated by commas.
Renaming column names
We can rename the column names of the returned result set. For this, we use the
AS
clause.
mysql> SELECT Name, Cost AS Price FROM Cars; +------------+--------+ | Name | Price | +------------+--------+ | Audi | 52642 | | Mercedes | 57127 | | Skoda | 9000 | | Volvo | 29000 | | Bentley | 350000 | | Citroen | 21000 | | Hummer | 41400 | | Volkswagen | 21600 | +------------+--------+ 8 rows in set (0.00 sec)
Say we wanted to name the column price rather than cost. With the above SQL statement, we have accomplished this.
Limiting data output
As we mentioned above, retrieving all data is expensive when dealing with
large amounts of data. We can use the LIMIT
clause to limit the
data amount returned by the statement.
mysql> SELECT * FROM Cars LIMIT 4; +----+----------+-------+ | Id | Name | Cost | +----+----------+-------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | +----+----------+-------+ 4 rows in set (0.00 sec)
The LIMIT
clause limits the number of rows returned to 4.
With two arguments, the LIMIT
returns rows beginning from an
offset value.
mysql> SELECT * FROM Cars LIMIT 2, 4; +----+---------+--------+ | Id | Name | Cost | +----+---------+--------+ | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | +----+---------+--------+ 4 rows in set (0.00 sec)
The first value is the offset and the second is the number of rows to be returned. Here we select all data from max four rows, and we begin with the third row.
mysql> SELECT * FROM Cars LIMIT 4 OFFSET 2; +----+---------+--------+ | Id | Name | Cost | +----+---------+--------+ | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | +----+---------+--------+ 4 rows in set (0.00 sec)
To be compatible with PostgreSQL database, MySQL has also the OFFSET
keyword. The above code is equivalent to the previous example.
Ordering data
We use the ORDER BY
clause to sort the returned data set.
The ORDER BY
clause is followed by the column on which
we do the sorting. The ASC
keyword sorts the data
in ascending order, the DESC
in descending order.
mysql> SELECT Name, Cost FROM Cars ORDER BY Cost DESC; +------------+--------+ | Name | Cost | +------------+--------+ | Bentley | 350000 | | Mercedes | 57127 | | Audi | 52642 | | Hummer | 41400 | | Volvo | 29000 | | Volkswagen | 21600 | | Citroen | 21000 | | Skoda | 9000 | +------------+--------+ 8 rows in set (0.00 sec)
In the above SQL statement, we select name, cost columns from the
Cars
table and sort it by the cost of the cars in descending
order. So the most expensive cars come first.
Selecting specific rows with the WHERE clause
In the following examples, we are going to use the
Orders
table.
mysql> SELECT * FROM Orders; +----+------------+------------+ | Id | OrderPrice | Customer | +----+------------+------------+ | 1 | 1200 | Williamson | | 2 | 200 | Robertson | | 3 | 40 | Robertson | | 4 | 1640 | Smith | | 5 | 100 | Robertson | | 6 | 50 | Williamson | | 7 | 150 | Smith | | 8 | 250 | Smith | | 9 | 840 | Brown | | 10 | 440 | Black | | 11 | 20 | Brown | +----+------------+------------+ 11 rows in set (0.00 sec)
Here we see all the data from the Orders
table.
Next, we want to select a specific row.
mysql> SELECT * FROM Orders WHERE Id=6; +----+------------+------------+ | Id | OrderPrice | Customer | +----+------------+------------+ | 6 | 50 | Williamson | +----+------------+------------+ 1 row in set (0.00 sec)
The above SQL statement selects a row which has Id 6.
mysql> SELECT * FROM Orders WHERE Customer="Smith"; +----+------------+----------+ | Id | OrderPrice | Customer | +----+------------+----------+ | 4 | 1640 | Smith | | 7 | 150 | Smith | | 8 | 250 | Smith | +----+------------+----------+ 3 rows in set (0.00 sec)
The above SQL statement selects all orders created by Smith customer.
We can use the LIKE
keyword to look for a specific pattern
in the data.
mysql> SELECT * FROM Orders WHERE Customer LIKE "B%"; +----+------------+----------+ | Id | OrderPrice | Customer | +----+------------+----------+ | 9 | 840 | Brown | | 10 | 440 | Black | | 11 | 20 | Brown | +----+------------+----------+ 3 rows in set (0.00 sec)
This SQL statement selects all orders from customers whose names begin with B character.
Removing duplicate items
The DISTINCT
keyword is used to select only unique items
from the result set.
mysql> SELECT Customer FROM Orders WHERE Customer LIKE 'B%'; +----------+ | Customer | +----------+ | Brown | | Black | | Brown | +----------+ 3 rows in set (0.00 sec)
This time we have selected customers whose names begin with B character.
We can see that Brown is mentioned twice. To remove duplicates, we use the
DISTINCT
keyword.
mysql> SELECT DISTINCT Customer FROM Orders WHERE Customer LIKE 'B%'; +----------+ | Customer | +----------+ | Brown | | Black | +----------+ 2 rows in set (0.00 sec)
This is the correct solution.
Say we wanted to figure out, how many orders were placed by Brown customer.
We would utilize the COUNT()
function.
mysql> SELECT COUNT(Customer) AS "Orders by Brown" FROM Orders WHERE Customer="Brown"; +-----------------+ | Orders by Brown | +-----------------+ | 2 | +-----------------+ 1 row in set (0.00 sec)
The customer has placed two orders.
Grouping data
The GROUP BY
clause is used to combine database
records with identical values into a single record. It is often used
with the aggregation functions.
Say we wanted to find out, the sum of each customers' orders.
mysql> SELECT SUM(OrderPrice) AS Total, Customer FROM Orders GROUP BY Customer; +-------+------------+ | Total | Customer | +-------+------------+ | 440 | Black | | 860 | Brown | | 340 | Robertson | | 2040 | Smith | | 1250 | Williamson | +-------+------------+ 5 rows in set (0.00 sec)
The SUM()
keyword returns the total sum of
a numeric column. The GROUP BY
clause divides the
total sum among the customers. So we can see that Black has ordered items for
440 or Smith for 2040.
We cannot use the WHERE
clause when aggregate functions were
used. We use the HAVING
clause instead.
mysql> SELECT SUM(OrderPrice) AS Total, Customer FROM Orders -> GROUP BY Customer HAVING SUM(OrderPrice)>1000; +-------+------------+ | Total | Customer | +-------+------------+ | 2040 | Smith | | 1250 | Williamson | +-------+------------+ 2 rows in set (0.00 sec)
The above SQL statement selects customers whose total orders where greater than 1000 units.
Selecting data into a file
The SELECT
statement can be used to write data from tables
to a file.
mysql> SELECT * INTO OUTFILE '/tmp/cars.txt' -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n' -> FROM Cars; Query OK, 8 rows affected (0.00 sec)
We write data from the Cars
table into a cars.txt
file. The output file is a CSV (Comma Separated Values) file.
Note that this operation is error prone, we can easily
run into permission denied errors.
$ cat /tmp/cars.txt 1,Audi,52642 2,Mercedes,57127 3,Skoda,9000 4,Volvo,29000 5,Bentley,350000 6,Citroen,21000 7,Hummer,41400 8,Volkswagen,21600
We can do the opposite operation; load the data from the file into the table.
mysql> DELETE FROM Cars; Query OK, 8 rows affected (0.00 sec) mysql> SELECT * FROM Cars; Empty set (0.00 sec)
We delete all the rows from the Cars
table.
mysql> LOAD DATA INFILE '/tmp/cars.txt' -> INTO TABLE Cars -> FIELDS TERMINATED BY ',' -> LINES TERMINATED BY '\n'; Query OK, 8 rows affected (0.00 sec) Records: 8 Deleted: 0 Skipped: 0 Warnings: 0 mysql> SELECT * FROM Cars; +----+------------+--------+ | Id | Name | Cost | +----+------------+--------+ | 1 | Audi | 52642 | | 2 | Mercedes | 57127 | | 3 | Skoda | 9000 | | 4 | Volvo | 29000 | | 5 | Bentley | 350000 | | 6 | Citroen | 21000 | | 7 | Hummer | 41400 | | 8 | Volkswagen | 21600 | +----+------------+--------+ 8 rows in set (0.00 sec)
We use the LOAD DATA INFILE
statement to load
the data back into the table. We verify that the data was
loaded correctly.
In this part of the MySQL tutorial, we mentioned the SQL
SELECT
statement in more detail.