SQL Cheatsheet - gecko-8/devwiki GitHub Wiki
CREATE DATABASE <database name>
DROP DATABASE <database name>
CREATE TABLE <table name> (
<column 1 name> <column 1 data type> <optional constraints>,
<column 2 name> <column 2 data type> <optional constraints>,
...
)
DROP TABLE <table name>
ALTER TABLE <table name> ADD PRIMARY KEY (<comma separated columns>)
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> <constraint type> <any constraint parameters>
e.g. ALTER TABLE <table name> ADD CONSTRAINT unique_email UNIQUE (email)
or
ALTER TABLE <table name> ADD <constraint type> <any constraint parameters>
NOTE: Database will automatically create a constraint name that may not be as readable
ALTER TABLE <table name> DROP CONSTRAINT <constraint name>
ALTER TABLE <table name> ADD CONSTRAINT <constraint name> CHECK (<allowed value comparison>)
e.g. ALTER TABLE person ADD CONSTRAINT gender_constraint CHECK (gender == 'Male' OR gender == 'Female' OR gender == 'Other')
e.g. ALTER TABLE product ADD CONSTRAINT price_constraint CHECK (price > 0)
INSERT INTO <table name> (
<column 1 name>,
<column 2 name>,
...)
VALUES (<column 1 value>, <column 2 value>, ...)
INSERT INTO <table name> (
<column 1 name>,
<column 2 name>,
...)
VALUES (<column 1 value>, <column 2 value>, ...)
ON CONFLICT (<column1>, <column2>, ...) DO NOTHING;
NOTE: ON CONFLICT columns must have UNIQUE constraints.
INSERT INTO <table name> (
<column 1 name>,
<column 2 name>,
...)
VALUES (<column 1 value>, <column 2 value>, ...)
ON CONFLICT (<column1>, <column2>, ...) DO UPDATE SET <column1> = EXCLUDED.<column1>, <column2> = EXCLUDED.<column2>;
NOTE: ON CONFLICT columns must have UNIQUE constraints.
UPDATE <table name>
SET <column1 name>=<column1 value>, ...
WHERE <condition for row>
SELECT <comma separated column names or *> FROM <database table>
NOTE: LIMIT is a Postgres keyword and FETCH is a SQL standard keyword
SELECT * FROM <database table> LIMIT <number of rows>
or
SELECT * FROM <database table> FETCH FIRST <number of rows> ROW ONLY
SELECT * FROM <database table> OFFSET <number of rows>
NOTE: LIMIT is a Postgres keyword and FETCH is a SQL standard keyword
SELECT * FROM <database table> LIMIT <number of rows> OFFSET <number of rows>
or
SELECT * FROM <database table> FETCH FIRST <number of rows> ROW ONLY OFFSET <number of rows>
SELECT * FROM <database table> WHERE <condition>
e.g. SELECT * FROM person WHERE first_name = 'Test'
SELECT * FROM <database table> WHERE <condition> AND <condition> OR <condition>...
also with order of operations
SELECT * FROM <database table> WHERE <condition> AND (<condition> OR <condition>)...
SELECT * FROM <database table> WHERE <column> IN (<comma separated options>)
e.g. SELECT * FROM person WHERE country_of_origin IN ('Canada', 'United States', 'Ukraine')
SELECT * FROM <database table> WHERE <column> BETWEEN <type> <start condition> AND <end condition>
e.g. SELECT * FROM person WHERE date_of_birth BETWEEN DATE '2022-01-01' AND '2022-12-31'
NOTE: % is a wildcard
SELECT * FROM <database table> WHERE <column> LIKE <content to compare>
e.g. SELECT * FROM person WHERE email LIKE '%.com'
NOTE: Same as LIKE but ignores case
SELECT * FROM <database table> WHERE <column> LIKE <content to compare>
e.g. SELECT * FROM person WHERE email ILIKE '%.COM'
NOTE: _ is a character placeholder
SELECT * FROM <database table> WHERE <column> LIKE <content to compare>
e.g. SELECT * FROM person WHERE email LIKE '%.___'
Hint: Any 3 character domain ending
DELETE FROM <table name> WHERE <condition for row (e.g. id = 12>
SELECT <columns> FROM <table name> GROUP BY <column to group by>
e.g. SELECT email, count(*) FROM person GROUP BY email
SELECT <columns> FROM <table name> GROUP BY <column to group by> HAVING <column condition>
e.g. SELECT email, count(*) FROM person GROUP BY email HAVING COUNT(*) > 1
=
, >
, <
, >=
, <=
, <>
ALTER SEQUENCE <sequence name> RESTART WITH <new value>
SELECT schema_name FROM information_schema.schemata
SET search_path TO <schema name>
DROP SCHEMA <schema name> CASCADE