Retrieve data from table with SQL SELECT statement

Select data from the database. The data returned is stored in a result table called the result-set.

A table named models:

Basic usage

SELECT name FROM models

We can pass in a comma-separated list of fields:

SELECT name, releaseDate FROM models

With parameters

WHERE

The WHERE clause specifies a search criterion. The WHERE clause should always be last in the syntax. The clauses are always written in this order: FROM followed by WHERE

SELECT name FROM models WHERE cores > 3

Compound statements

Compound statements allow you to apply more filters to your clauses within an SQL statement. SQL allows you to build complex, combinatorial WHERE clauses by using Boolean and mathematical operators (i.e AND , OR , > , < , != , <= …)

Multiple clauses:

SELECT name, ram, release_date
FROM model
WHERE release_date > '2018-01-01' AND ram > 512;

More complex logic achieve with parentheses:

SELECT name, cores, release_date
FROM model
WHERE (manufacturer_id = 1 OR manufacturer_id = 2) AND cores >= 2;

Transform returned data

We can apply ordering to the return data

SELECT name FROM models ORDER BY name, cores
SELECT name FROM models ORDER BY name, cores DESC

ORDER BY always comes last, after the selection and any filtering clauses but before a WHERE clause

Paginate

We can break our returned data into blocks with LIMIT

LIMIT 5 -- Return first five items
LIMIT 5,5 -- Return first five items from the sixth row