http://tatiyants.com/pev/ - Explain Visualiser
|Data Description Language (DDL)||Data Manipulation Language (DML)||Data Control Language (DCL)||Transaction Control Language (TCL)|
The key actions are:
SELECT [DISTINCT] ... FROM ... UNION [INNER|LEFT|RIGHT|OUTER] JOIN ... ON ... WHERE ... GROUP BY ... HAVING ... ORDER BY ... [ASC|DESC] LIMIT ... OFFSET ... INSERT INTO ... VALUES ... UPDATE ... SET ... [WHERE ...] DELETE ...
SELECT can be used with AS to rename a table or a column.
UNION adds the rows of different queries
WHERE determines which rows will be processed by the query and filters rows before the GROUP BY.
HAVING comes in at the end of a query after the GROUP BY and determines which rows will be sent to the client. It allows aggregate functions by filtering group rows created by the GROUP BY.
The order is in fact:
- FROM [UNION/JOIN]
- GROUP BY
- ORDER BY
ORDER BYclause example:
ORDER BY x DESC, y NULLS FIRST.
OFFSET n ROWS FETCH FIRST m ROWS ONLY.
We have the arithmetic operators +, -, *, /, and %.
Logical/Comparison operators include:
IS (NOT) NULL UNIQUE? =, <, > <>, != NOT, AND, OR BETWEEN ... AND ... EXISTS IN ... LIKE ... ... ANY, ALL
BETWEEN can be numbers, texts, or dates. In the case of text it seems to use alphabetical ordering (Unicode considerations)?
EXISTS tests whether a subquery returns any values (boolean existence check).
IN can use a tuple of values
('George', 'Clooney'), or a subquery.
LIKE uses two wildcards % (0+ characters), and _ (exactly one character). For example,
_r% would mean values with "r" in the 2nd position.
ANY and ALL can be used with an operator against a subquery, e.g.
WHERE column = ANY (SELECT ...).
We have aggregate functions that can act on values:
MIN MAX COUNT AVG SUM
Be aware, that the aggregate selects on the column, and will not select the entire row.
Create composable queries using query-specific views. I call these
WITH my_view AS ( SELECT x FROM y ) -- Remove invalid things , my_filter AS ( SELECT x FROM y WHERE x IS NOT NULL ) SELECT x FROM my_filter ;
A query nested inside another query that uses values from the outer query.
The subquery is (usually) evaluated once for each outer query row.
SELECT a , ( SELECT AVG(x2.a) FROM x AS x2 WHERE x2.b = x1.b ) FROM x AS x1
SELECT a FROM x AS x1 WHERE a > ( SELECT AVG(a) FROM x AS x2 WHERE x2.b = x1.b ) ;
-- comment /* block comment */