📖 MySQL Basic Select Statement

Retrieving, or viewing, as opposed to changing, data in a table is accomplished by using a SELECT statement. You tell MySQL what to retrieve (the columns) by using SELECT and FROM clauses within the SELECT statement.

Syntax

SELECT select_list
FROM table_source
[WHERE search_condition]
[ORDER BY order_by_list]
[LIMIT [offset,] row_limit]
;

The five clauses of the SELECT statement

SELECT
the columns (fields or data values) to be retrieved from the table
FROM
designates the table to retrieve data from
WHERE
optional – used to limit the rows selected based on some selection criteria.
If not specified, all rows from the table will be retrieved.
ORDER BY
optional – specifies the columns to be used to sort the display of the data.
If not specified, the data will be displayed in ascending order by the primary key.
LIMIT
optional - used to specify a numeric limit of the number of rows returned.

Example

SELECT LastName, FirstName FROM customer;
-- returns only two columns from all rows of the customer table

-- To select all columns from a table use the * wildcard 
SELECT * FROM customer;
ORDER BY

Sorts the result set by the specified column.

SELECT * FROM customer ORDER BY LastName;
-- sorts in ascending order, A-Z order by last name

SELECT * FROM customer ORDER BY LastName DESC;
-- sorts in descending order, Z-A by last name

A nested sort simply means that more than two columns are specified in the ORDER BY clause.

SELECT * FROM customer ORDER BY LastName, FirstName;
-- returns all rows but sorts the rows first by the last name  
-- and within each last name sort the rows by first name
LIMIT

Limiting the number of rows returned in a result set using a LIMIT clause.

SELECT * FROM invoice ORDER BY Total DESC LIMIT 5;
-- returns the first five invoices in the result set
-- since they are sorted in descending order, this will give the 5 largest invoices
OFFSET

Optionally an offset may be used. An offset is defined as a starting point in MySQL in a LIMIT clause the offset value means the first row after the offset value.

SELECT * FROM invoice ORDER BY InvoiceId LIMIT 2,3;
-- returns three invoices but don’t start with the first or second row
-- start with the third row
WHERE

Limiting Result Sets by using a WHERE clause. Tests the data against one or more conditions.

SELECT * FROM invoice WHERE InvoiceId = 3;

Conditional Operators
Operator
Explanation

=

Equal

<

Less than

>

Greater than

<=

Less than or equal to

>=

Greater than or equal to

<>

Not equal to

!=

Not equal to

For example if you wanted to see a list of customers that are located in Sheboygan.

SELECT * FROM customer WHERE City = 'Paris';

More than one comparison is allowed.

AND

The AND operator only returns rows that meet both criteria.

SELECT * FROM customer WHERE city = 'Paris' AND credit_limit < 5000.00;
-- This would return only customers located in Paris that has a credit limit less than $5000.00.
OR

The OR operator returns rows that meet either criteria.

SELECT * FROM customer WHERE city = 'Paris' OR  credit_limit < 5000.00;
-- This would return any customer who lives in Paris as well as any customer that has a credit limit less than $5000.00.
NOT

The NOT criteria negates the condition. Suppose you want to find all customers that aren’t located in California.

SELECT * FROM customer WHERE NOT State = 'CA';
IN

The IN operator tests rows against a list. For example suppose you wanted to find a list of colleges in colleges that are located in the states of Oklahoma, Louisiana or New Mexico.

SELECT * FROM customer WHERE State IN ('CA', 'FL', 'WA');
BETWEEN

The BETWEEN operator tests rows against a range. For example to find invoices from $5 to $10.

SELECT * FROM invoice WHERE Total BETWEEN 5 AND 10;
-- Note: Between is inclusive. This will include invoices that are $5 and $10.