SQL Basic Commands

Category: SQL :: Published at: 27.12.2021

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 ___'

- Click if you liked this article

Views: 199