|
|
|
|
The SELECT query allows data to be read from a table. A selection can be made from a single table or several tables, and you can write expressions around column names, so that table values are processed in some way before reaching the resultset. There can also be a SELECT query with no tables used to evaluate expressions.
Below is a query that reads every row from a single table, products, which has columns for id, product_name, and price. SELECT reads each row of the table in turn and puts the data into a resultset. The resultset can then be displayed to the user, but it could be returned to an application that is querying MySQL through an API.
A wildcard * can be used to retrieve the data in every column of a table.
mysql> SELECT * FROM products;
This is identical to naming all the columns:
mysql> SELECT id, product_name, price FROM products;
The output looks like this:
+------+--------------------+-------+ | id | product_name | price | +------+--------------------+-------+ | 102 | Light brown jacket | 10.00 | | 103 | Leather jacket | 28.00 | | 104 | Charcoal trousers | 39.50 | | 113 | Dark grey jacket | 10.00 | +------+--------------------+-------+
The names of the columns in products are returned as column headers in the resultset, with the data for every row in the body of the resultset.
It is possible to restrict the data retrieved to include just a subset of the columns. For example, to get only the column product_name, name just this column:
mysql> SELECT product_name FROM products; +--------------------+ | product_name | +--------------------+ | Light brown jacket | | Leather jacket | | Charcoal trousers | | Dark grey jacket | +--------------------+
A WHERE condition is used to control which rows are retrieved. For example, to only retrieve products with an id equal to 102, and to display product_name and price, do this:
mysql> SELECT product_name, price FROM products
-> WHERE product_id=102;
+--------------------+-------+
| product_name | price |
+--------------------+-------+
| Light brown jacket | 10.00 |
+--------------------+-------+
SELECT can be asked to evaluate expressions, without needing to refer to a table. For example:
mysql> SELECT 12.50 * 7, 1+2, 'hello world'; +-----------+-----+-------------+ | 12.50 * 7 | 1+2 | hello world | +-----------+-----+-------------+ | 87.50 | 3 | hello world | +-----------+-----+-------------+
In this example, the query has multiplied two numbers together using the * multiplication operator, added two numbers using +, and returned a constant string. SELECT can also evaluate functions, of which MySQL has a large library. For example, it can process dates and times, and here's an example of it getting the current time using the function NOW():
mysql> SELECT NOW(); +---------------------+ | NOW() | +---------------------+ | 2002-09-07 20:05:45 | +---------------------+