[Extra Notes] 2.5 - Querying the Database

SELECT

SELECT syntax

All rows from specified columns

SELECT column1Name, column2Name,... FROM tableName

All rows from all columns

SELECT * FROM tableName

List rows that meet specified criteria

SELECT [...] FROM tableName WHERE criteria

SELECT without a table

SELECT 1+1;
> (2)
SELECT NOW();
> (YYYY-MM-DD HH:MM:SS)

Comparison Operators

Do not compare floats for equalities as they are not precise, use DECIMAL instead

For numbers (INT, DECIMAL, FLOAT)

=  equal to
<>  not equal to
!=  not equal to
>  greater than
<  less than
>=  greater than or equal to
<=  less than or equal to

String pattern Matching

LIKE and NOT LIKE 

Wilcards: 
_  Matches any single character
%  Matches any number of characters

Examples
abc%  Strings starting 'abc'
%xyz   Strings ending 'xyz'
%aaa%  Strings containing 'aaa'
_ _ _  [no spaces]  Strings containing any 3 characters

Arithmetic Operators

+  -  *  /
DIV  Integer Division
%  Modulus (Remainder)

Logical Operators

WHERE
NOT
AND
OR
XOR 
IN
NOT IN (AND-OR equivalent)

BETWEEN  NOT BETWEEN  Checking if a value is within a specified range

... WHERE value BETWEEN value1 AND value2

IS NULL  IS NOT NULL  Represents no value, missing value or unknown value.

Common mistake - NULL cannot be compared:

WHERE value = NULL;  INCORRECT
WHERE value IS NULL; CORRECT

ORDER BY Clause

SELECT ... FROM tableName WHERE criteria ORDER BY columnA ASC|DESC, columnB ASC|DESC

ASC default. If several values in columnA have the same value, they will be ordered by columnB and so on.

For strings, ordering can be either case sensitive or case insensitive. Returned values can be randomised using RAND()

LIMIT Clause

Limits the number of rows displayed, useful for large databases