SQLCheat

SQL cheat sheet

Basic
BasicQueries
Queries The Joy
The Joyof
ofJOINs
JOINs
• filter your columns
-- filter yourcol1,
SELECT columns
col2, col3, ... FROM table1
SELECT col1, col2, col3, ... FROM table1
• filter the rows
-- filter the rows
WHERE col4 = 1 AND col5 = 2

A B A B
WHERE col4 = 1 AND col5 = 2
• aggregate
-- aggregate thethe data
data
GROUP by …
GROUP by …
• limit
-- limit aggregated
aggregated data
data
HAVING count(*) >>11
HAVING count(*)
-- order
• orderof of
thetheresults
results
ORDER
ORDER BYBY col2
col2 LEFT
LEFT OUTER
OUTERJOIN JOIN--all
allrows
rowsfrom table A,
from table A, INNER
INNERJOINJOIN- -fetch
fetchthe
theresults
resultsthat
that RIGHT
RIGHT OUTER
OUTER JOIN
JOIN- all rows
- all rowsfrom
fromtable
tableB,B,
eveneven if they
if they do exist
do not not exist in table
in table B B exist tables
exist in both in both tables eveneven if they
if they do exist
do not not exist in table
in table A A
USEFUL KEYWORDS
Useful keywords FOR SELECTS:
for SELECTS:
DISTINCT- return
DISTINCT - return unique
unique results
results
BETWEENa aAND
BETWEEN AND b -blimit
- limit
the the range,
range, the values can be
the values Updates onJOINed
Updates on JOINedQueries
Queries Useful Utility
Useful Functions
Utility Functions
can be numbers,
numbers, text, ortext, or dates
dates You canuse
You can JOINs
useJOINs in your
in your UPDATEs
UPDATEs -- convert
• strings
convert to dates:
strings to dates:
LIKE- -pattern
LIKE pattern search
searchwithin the the
within column text text
column UPDATE t1 SET a =
UPDATE t1 SET a = 1 1 TO_DATE (Oracle,
TO_DATE (Oracle, PostgreSQL), PostgreSQL), STR_TO_DATE
STR_TO_DATE (MySQL)
(MySQL)
IN(a,
IN (a,b,
b,c)c)- -check
checkif the value
if the is contained
value among
is contained given.
among given. FROM table1
FROM table1t1t1
JOIN
JOINtable2 t2 ON
table2 t1.idt1.id
t2 ON = t2.t1_id
= t2.t1_id -- return• the firstthe
return non-NULL argument:
first non-NULL argument:
WHERE t1.col1 = 0 AND t2.col2 IS
WHERE t1.col1 = 0 AND t2.col2 IS NULL; NULL;
COALESCE (col1, col2,
COALESCE “default
(col1, value”)value”)
col2, “default
Data Modification -- return• current time: time:
Data Modification NB! Use database specific syntax, it might be faster!
NB! Use database specific syntax, it might be faster!
return current
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
• update specific data with the WHERE clause
-- update specific
UPDATE table1data with=the
SET col1 WHERE
1 WHERE clause
col2 =2 -- compute set operations
• compute on two
set operations onresult setssets
two result
UPDATE SET col1 = 1 WHERE col2 = 2
table1manually
Semi JOINs
Semi JOINs SELECT col1, col2 FROM table1
SELECT col1, col2 FROM table1
• insert values
-- insert values manually You can use subqueries instead of JOINs: UNION /UNIONEXCEPT / INTERSECT
/ EXCEPT / INTERSECT
INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME)
You can use subqueries instead of JOINs: SELECT col3, col4 FROMcol4table2;
INSERT INTO (1,
VALUES table1 (ID,‘Labs’);
‘Rebel’, FIRST_NAME, LAST_NAME) SELECT col3, FROM table2;
SELECT col1, col2 FROM table1 WHERE id IN
VALUES (1, ‘Rebel’, ‘Labs’); SELECT col1, col2 FROM table1 WHERE id IN
• or by using the results of a query (SELECT t1_id FROM table2 WHERE date > - returns datafrom
from both
both queries
-- or by usingINTO
the table1
results ofFIRST_NAME,
a query (SELECT t1_id FROM table2 WHERE date > UnionUnion
- returns data queries
INSERT (ID, LAST_NAME) CURRENT_TIMESTAMP) rows from the first querythat
that are
INSERT INTO table1 (ID, FIRST_NAME, LAST_NAME) CURRENT_TIMESTAMP)
Except -
Except - rows from the first query arenot
notpresent
present
SELECT id, last_name, first_name FROM table2 in the second query
SELECT id, last_name, first_name FROM table2 in the second query
Intersect - rows that are returned from both queries
Indexes Intersect - rows that are returned from both queries
Views Indexes
If you query by a column, index it!
Views
A VIEW is a virtual table, which is a result of a query. IfCREATE
you query byindex1
INDEX a column, index(col1)
ON table1 it! Reporting
They can be used to create virtual tables of complex queries.
A VIEW is a virtual table, which is a result of a query. CREATE INDEX index1 ON table1 (col1) Reporting
Use aggregation functions
They can
CREATE be used
VIEW view1to
AScreate virtual tables of complex queries. DON’T FORGET: Use aggregation functions
COUNT - return the number of rows
SELECT col1, col2 Don’t
Avoid forget:
overlapping indexes - cumulate
CREATE VIEW view1 AS SUM
COUNT - return the the valuesof rows
number
FROM table1 Avoid overlapping
Avoid indexing on too many columns
indexes - return the average for the group
SELECT…
WHERE col1, col2 SUM AVG
- cumulate the values
Indexes can speed up DELETE and UPDATE operation MIN / MAX - smallest / largest value
FROM table1 Avoid indexing on too many columns AVG - return the average for the group
WHERE … Indexes can speed up DELETE and UPDATE operations MIN / MAX - smallest / largest value

www.jrebel.com