SQL is not a hard language, but the common usage of different ORM tools can make us less focused on clean SQL queries than ever.
This is why i wrote here a small cheatsheet with simple SQL commands.
- SELECT - retrieve information from a database
# typical use, get 2 columns from table SELECT column_name1, column_name2 FROM table_name; # don't use * if you don't have to, it can slow your application SELECT * FROM table_name;
- SELECT DISTINCT - retrieve an unique values from column inside a database
# retrieves unique values from the table SELECT DISTINCT column_name FROM table_name;
- COUNT - returns the number of input rows matching a specific query
SELECT COUNT(column_name) FROM table_name; # you can combine COUNT with f.e. DISTINCT SELECT COUNT(DISTINCT column_name) FROM table_name;
- WHERE - it can scope any statement
SELECT first_name, last_name FROM user WHERE first_name = 'Thomas'; # connect with for example COUNT SELECT COUNT(first_name) FROM user WHERE first_name = 'Thomas'; SELECT first_name, last_name FROM user WHERE first_name = 'Thomas' AND age > 30 AND city = 'Warsaw';
- ORDER BY - order your results ASC or DESC
# typical use of ORDER BY SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 ASC; # you don't have to use ASC when ordering with ASC order SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1; # ORDER BY used to order by multiple columns - first ordering by brand, then order cars within same brand by price SELECT brand, model, price FROM cars ORDER BY brand, price DESC; # you can use ASC and DESC in the single query SELECT brand, model, price FROM cars ORDER BY brand ASC, price DESC;
- LIMIT - limits the number of rows returned as a result of a query
# typical use of limit SELECT * FROM payments ORDER BY created_at DESC LIMIT 10;
- BETWEEN - matches values against a range of values
# typical use # remember that BETWEEN includes extreme values inside query SELECT brand, model WHERE price BETWEEN 100000 AND 150000; # you can use NOT BETWEEN SELECT brand, model WHERE price NOT BETWEEN 100000 AND 150000; # how to use BETWEEN with dates SELECT brand, model WHERE created_at BETWEEN '1990-01-01' AND '1999-12-31'
- IN - use if you want to select rows where column includes IN
# typical use of IN SELECT brand, model WHERE brand IN ('Opel', 'Mazda') # you can use as exclude SELECT brand, model WHERE brand NOT IN ('Ford', 'Fiat')
- LIKE and ILIKE - looks for pattern in a string - checks if value is inside string
# LIKE operator uses % and _ to match pattern # returns all rows which brand starts with a letter "M" # notice that LIKE operator is case-sensitive SELECT brand,model FROM cars WHERE brand LIKE 'M%' # if you don't want to be case-sensitive use ILIKE instead of LIKE SELECT brand,model FROM cars WHERE brand ILIKE 'm%' # use underscore instead of % sign if you want to replace only one sign # the example below will return all version of Ford Focus SELECT brand,model FROM cars WHERE model LIKE 'Focus MK_' # if you want to match against 3 letters SELECT brand,model FROM cars WHERE model LIKE 'Focus ___'