totn MySQL

MySQL: SELECT Statement

This MySQL tutorial explains how to use the MySQL SELECT statement with syntax and examples.

Description

The MySQL SELECT statement is used to retrieve records from one or more tables in MySQL.

Syntax

In its simplest form, the syntax for the SELECT statement in MySQL is:

SELECT expressions
FROM tables
[WHERE conditions];

However, the full syntax for the SELECT statement in MySQL is:

SELECT [ ALL | DISTINCT | DISTINCTROW ]
       [ HIGH_PRIORITY ]
       [ STRAIGHT_JOIN ]
       [ SQL_SMALL_RESULT | SQL_BIG_RESULT ] [ SQL_BUFFER_RESULT ]
       [ SQL_CACHE | SQL_NO_CACHE ]
       [ SQL_CALC_FOUND_ROWS ]
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];

Parameters or Arguments

ALL
Optional. Returns all matching rows
DISTINCT
Optional. Removes duplicates from the result set. Learn more about DISTINCT clause.
DISTINCTROW
Optional. Synonym for DISTINCT. Removes duplicates from the result set.
HIGH_PRIORITY
Optional. It tells MySQL to run the SELECT before any UPDATE statements that are waiting for the same resource. It may be used with MyISAM, MEMORY and MERGE tables that use table-level locking.
STRAIGHT_JOIN
Optional. It tells MySQL to join the tables in the order that they are listed in the FROM clause.
SQL_SMALL_RESULT
Optional. Uses fast temporary tables to store results (used with DISTINCT and GROUP BY).
SQL_BIG_RESULT
Optional. Prefers sorting rather than using a temporary table to store results (used with DISTINCT and GROUP BY).
SQL_BUFFER_RESULT
Optional. Uses temporary tables to store results (can not be used with subqueries).
SQL_CACHE
Optional. Stores the results in the query cache.
SQL_NO_CACHE
Optional. Does not store the results in the query cache.
SQL_CALC_FOUND_ROWS
Optional. Calculates how many records are in the result set (not taking into account the LIMIT modifier) which can then be retrieved using the FOUND_ROWS function.
expressions
The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.
tables
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
WHERE conditions
Optional. The conditions that must be met for the records to be selected.
GROUP BY expressions
Optional. It collects data across multiple records and groups the results by one or more columns. Learn more about the GROUP BY clause.
HAVING condition
Optional. It is used in combination with the GROUP BY to restrict the groups of returned rows to only those whose the condition is TRUE. Learn more about the HAVING clause.
ORDER BY expression
Optional. It is used to sort the records in your result set. Learn more about the ORDER BY clause.
LIMIT
Optional. If LIMIT is provided, it controls the maximum number of records to retrieve. At most, the number of records specified by number_rows will be returned in the result set. The first row returned by LIMIT will be determined by offset_value.
PROCEDURE
Optional. If provided, procedure_name is the name of the procedure that should process the data in the result set.
INTO

Optional. If provided, it allows you to write the result set to either a file or variable.

Value Explanation
INTO OUTFILE 'filename' options Writes the result set to a file called filename on the server host. For options, you can specify:

FIELDS ESCAPED BY 'character'
FIELDS TERMINATED BY 'character' [ OPTIONALLY ENCLOSED BY 'character' ]
LINES TERMINATED BY 'character'

where character is the character to display as the ESCAPE, ENCLOSED, or TERMINATED character. For example:

SELECT supplier_id, supplier_name
FROM suppliers
INTO OUTFILE 'results.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
INTO DUMPFILE 'filename' Writes one row of the result set to a file called filename on the server host. With this method, there is no column termination, no line termination, or escape processing.
INTO @variable1, @variable2, ... @variable_n Writes the result set to one or more variables, as specified by @variable1, @variable2, ... @variable_n
FOR UPDATE
Optional. Records affected by the query are write-locked until the transaction has completed.
LOCK IN SHARE MODE
Optional. Records affected by the query can be used by other transactions but can not be updated or deleted by those other transactions.

Example - Select all fields from one table

Let's look at how to use a MySQL SELECT query to select all fields from a table.

SELECT *
FROM order_details
WHERE quantity >= 10
ORDER BY quantity DESC;

In this MySQL SELECT statement example, we've used * to signify that we wish to select all fields from the order_details table where the quantity is greater than or equal to 10. The result set is sorted by quantity in descending order.

Example - Select individual fields from one table

You can also use the MySQL SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 500
ORDER BY quantity ASC, unit_price DESC;

This MySQL SELECT example would return only the order_id, quantity, and unit_price fields from the order_details table where the quantity is less than 500. The results are sorted by quantity in ascending order and then unit_price in descending order.

Example - Select fields from multiple tables

You can also use the MySQL SELECT statement to retrieve fields from multiple tables.

SELECT order_details.order_id, customers.customer_name
FROM customers
INNER JOIN order_details
ON customers.customer_id = order_details.customer_id
ORDER BY order_id;

This MySQL SELECT example joins two tables together to gives us a result set that displays the order_id and customer_name fields where the customer_id value matches in both the customers and order_details table. The results are sorted by order_id in ascending order.

Example - write to a file

You can also use the MySQL SELECT statement to write the result set to a file.

For example:

SELECT order_id, quantity, unit_price
FROM order_details
WHERE quantity < 500
ORDER BY quantity
INTO OUTFILE 'results.txt'
     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
     LINES TERMINATED BY '\n';

This MySQL SELECT example would return only the order_id, quantity, and unit_price fields from the order_details table where the quantity is less than 500. The results would be sorted by quantity in ascending order and written to a file called results.txt.

Share on: