Selecting data

SELECT statement

The SQL select statement is used to query for data. This statement will normally result in one or more rows.

Syntax:

SELECT [fields] FROM [table] [WHERE conditions ] [ORDER BY sort-fields]

[fields]
The list fields to select or * for all fields. Separate fields with a comma.
[table]
The name of the table to select data from
[WHERE conditions]
This clause acts as a filter. Only rows that match the conditions are included in the result.
[ORDER BY sort-fields]
This clause determines the order in which rows are included in the result.

The WHERE and ORDER BY clauses are optional.

WHERE clause

This clause acts as a filter expression. If the expression evaluates to true, the record is included in the query result. Multiple statements can be joined together using logical operators AND and OR.

For example:

SELECT * FROM orders WHERE payed=1 AND shipped=0

In addition you can use parenthesis to group conditions:

SELECT * FROM orders WHERE (payed=1 AND payment="Pre-Pay") OR (payment="Cash-on-delivery")

ORDER BY clause

With this clause you can sort the data. The clause is specified as follows:

ORDER BY field [direction] [, field [direction]]

The direction is either ascending (ASC) or descending (DESC). The order in which fields appear in the statement determines the order in which the sort is performed.

For example:

SELECT * FROM products ORDER BY brand, price ASC

This will sort all products by brand and then by price. The lowest price comes first, the highest price comes last. By default the direction is descending.

Examples

Select all data from a table called products:

SELECT * FROM products

Select only products that have the field brand set to "Acmee":

SELECT * FROM products WHERE brand="Acmee"

Select article_nr and stock for products that are in stock and sort them by article number:

SELECT article_nr, stock FROM products WHERE stock>0 ORDER BY article_nr