These will be in SQLite, specifically.
SELECT * FROM foo_bar SELECT quux,baz FROM foo_bar /* find this thing in that table */ SELECT * FROM what WHERE things = 'stuff' /* WHERE is a simple conditional */
< > = <>etc).
ANDword chains conditions in queries. Syntax is
SELECT foo FROM bar WHERE quux = 'baz' AND jargon > 3
ORis the same concept.
INkind of a shorthand way of doing a bunch of chained
WHERE things IN ('foo', 'bar'), or
NOT IN ('quux', 'baz')
DISTINCTis the first thing on this list that doesn't come after the
SELECT DISTINCT column FROM table
SELECT DISTINCT column FROM table WHERE condition <> 'thing'
ORDER BYwould be
SELECT * FROM foo ORDER BY id descfor example. Simple sort.
LIMITthe amount in a page. There's also
COUNT(*) FROM foo WHERE bar
SUM(num_people) FROM group
SELECT AVG(num_people) FROM group
MINare largest and smallest from that thing you're querying.
GROUP BYsplits table based on the values of the rows.
SELECT COUNT(*) things FROM bar_table GROUP BY foo_column
SELECT * FROM foo_table WHERE foo_column = (SELECT MIN(num_people) FROM foo_table)
NULLfor empty values
AS, as in
SELECT f.thing, b.thing FROM foo AS f
SELECT thing.stuff AS asdf
LIKEhas two characters:
%represents zero, one, or multiple characters;
_represents one character.
LIKE "SUPER _"would be "SUPER 1", "SUPER A", etc, whereas
LIKE "SUPER%"would be "SUPERASDF", "SUPER FOO BAR QUUX BAZ", "SUPER" on its own, etc.
LIKEis not case sensitive (the actual query).
CASE WHEN foo = whatever THEN value WHEN bar = whatever THEN stuff... ELSE thing_for_else END
SUBSTR(column_name, index, number_of_chars). Character number is optional; if not included, it returns the whole rest of the string. (In other versions of SQL,
RIGHTcan do this.)
COALESCEtakes a list of columns, returns value of the first column that is not
SELECT foo, COALESCE(bar, quux) as baz FROM asdfghjkl
This is a big deal, so it gets its own subsection.
INNER JOIN ... ON
SELECT table.column, table_two.column_thing FROM table_two JOIN table ON table.column = table_two.column_two
INNER JOINis the default join. There are many ways to join, though.
SELECT ..., ... FROM ... INNER JOIN ... ON ... = ... INNER JOIN ... ON ... = ...
WHEREclauses in joins can be like
ON ... = ... WHERE ... <> ... AND .... <> ...
LEFT JOINwill return