Most SQL content on the web seems to be written with data analysts in mind. And that’s fine, but developers need SQL too! Your application is only as available and performant as your database, and solid database performance doesn’t just mean knowing how to INNER JOIN or SELECT *, it also means understanding monitoring and ops commands, using EXPLAIN ANALYZE, etc.
So we’re going to cover all of those things…and more!
Whether you’re a developer or an analyst, this SQL cheat sheet is designed to make it quick and easy to find the right SQL commands, and see examples of queries in action. It covers all the fundamentals you need for analytics work and the fundamentals required for basic performance monitoring and query optimization.
A quick note: this cheat sheet uses the PostgreSQL dialect. We’ve also included some CockroachDB-specific commands (marked with an asterisk) where CockroachDB provides useful features that don’t exist in Postgres. However, most of the commands on this cheat sheet will work with most flavors of SQL.
Want a printable version you can hang on your wall? Check out this printer-friendly version of this cheat sheet for easy reference (preview below). Or, scroll down for the entire thing with easy-to-copy code snippets!
Quick jump to a section:
Getting Started
Creating and managing databases
CREATE DATABASE
Creates a new database.
CREATE DATABASE bank;
DROP DATABASE
Delete a database and all of its contents.
DROP DATABASE bank;
SHOW DATABASES*
Show all databases in your cluster.
SHOW DATABASES;
IF EXISTS can be used to prevent errors if we (for example) attempt to delete a database that doesn’t exist.
IF EXISTS can also be used with many other SQL statements and combined with other operators.
Examples:
DROP DATABASE … CASCADE can be used to remove all objects that rely on the database that is being dropped. DROP DATABASE … RESTRICT can be used to prevent the DROP DATABASE command from executing unless the database is empty.
Examples:
Creating tables and schema
CREATE TABLE
Create a new table in the database.
CREATE TABLE users (
id UUID PRIMARY KEY,
city STRING,
name STRING,
address STRING,
credit_card STRING,
dl STRING
);
For each row in the table, you must specify at least a row name (i.e. city) and a datatype (i.e. STRING). But you can also do a lot more with CREATE TABLE statements, such as:
Define a primary key column:
CREATE TABLE users (
id UUID PRIMARY KEY,
city STRING,
name STRING
);
Define a multi-column primary key:
CREATE TABLE users (
id UUID,
city STRING,
name STRING,
PRIMARY KEY (city, id)
);
Define a foreign key referencing another table in the database:
(In this case, referencing a column called city in table called locations).
CREATE TABLE users (
id UUID PRIMARY KEY,
city STRING REFERENCES locations(city),
name STRING
);
Create an index based on a column:
CREATE TABLE users (
id UUID PRIMARY KEY,
city STRING,
name STRING,
INDEX (name)
);
Define a default for a column:
In this case, using CockroachDB’s gen_random_uuid() function to generate a random UUID as the default value. (Most database management systems include built-in functions like this for auto-generating certain types of data).
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
city STRING,
name STRING
);
Disallow NULL values from a column:
CREATE TABLE users (
id UUID NOT NULL,
city STRING,
name STRING
);
Create a table with a computed column:
CREATE TABLE users (
id UUID NOT NULL,
city STRING,
name STRING,
name_and_city STRING AS (CONCAT(name, ' ', city)) STORED
);
Specify what happens when a referenced object is updated:
CREATE TABLE users (
id UUID PRIMARY KEY,
city STRING REFERENCES locations(city) ON DELETE CASCADE,
name STRING
);
Create a new table using the results of a query:
CREATE TABLE users_ny(user_id, name, city)
AS SELECT * FROM users
WHERE city = 'new york';
Managing SQL tables
ALTER TABLE
Apply a schema change to a table.
ALTER TABLE bank ADD COLUMN active BOOL;
ALTER TABLE is used with subcommands such as:
ADD COLUMN
Add a column.
ALTER TABLE bank ADD COLUMN active BOOL;
DROP COLUMN
Remove a column.
ALTER TABLE bank DROP COLUMN active;
ALTER COLUMN
Change column constraints, datatypes, etc.
ALTER TABLE bank ALTER account_balance TYPE FLOAT;
RENAME COLUMN
Rename a column.
ALTER TABLE bank RENAME COLUMN account_balance TO balance;
RENAME TO
Rename a table.
ALTER TABLE bank RENAME TO users;
DROP TABLE
Remove a table.
DROP TABLE bank;
DROP TABLE … CASCADE can be used to remove all objects (constraints, views, etc.) that rely on the table being dropped.
DROP TABLE … RESTRICT can be used to prevent the DROP TABLE command from executing unless the table is empty.
Use DROP TABLE statements with caution!
Managing SQL constraints
ADD CONSTRAINT
Add a key, check, or unique constraint to a column.
ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);
DROP CONSTRAINT
Remove a constraint from a column.
ALTER TABLE users DROP CONSTRAINT id_name_unique;
ALTER COLUMN
Add or remove DEFAULT and NOT NULL constraints, change datatypes.
ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;
Inserting data
INSERT INTO … VALUES
Insert rows with specified values into a table.
INSERT INTO users (name, city) VALUES('Alice', 'New York');
INSERT INTO … SELECT
Insert rows into a table from the results of a query.
INSERT INTO drivers (id, city, name, address)
SELECT id, city, name, address FROM users
WHERE name IN ('Anita Atkinson', 'Devin Jordan');
Working with your data
Modifying data
UPDATE
Update row(s) in a table.
UPDATE users SET address = '201 E Randolph St' WHERE id = '851eb851-eb85-4000-8000-00000000001a';
Note: without a WHERE statement, UPDATE will update the value of the specified column or columns for all rows.
INSERT INTO … ON CONFLICT
Insert a new row, or perform a different action if a conflict with an existing row is detected (i.e., an “upsert”).
INSERT INTO employees (id, name, email)
VALUES (2, ‘Dennis’, ‘dennisp@weyland.corp’)
ON CONFLICT (id) DO UPDATE;
UPSERT*
Upsert a row into the database.
UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘lambert@weyland.corp`);
Note: By default, CockroachDB uses the primary key column’s value to determine whether or not there is a conflict (i.e., whether an existing row should be updated or a new row should be inserted). More information about upserts in SQL is available on our blog.
DELETE FROM
Delete a specific row or rows.
DELETE FROM promo_codes WHERE code = 'HAPPY50';
Querying data
SELECT … FROM …
Return the values of specific columns in a table.
SELECT id, city, name FROM users;
SELECT * FROM …
Return the values of all columns in a table.
SELECT * FROM users;
LIMIT
Limit the number of rows returned by a query.
SELECT * FROM users LIMIT 5;
OFFSET
Skip the first n rows before returning a query result.
SELECT * FROM users LIMIT 5 OFFSET 5;
Note: in real-world applications with large databases where performance matters, keyset pagination is recommended over using LIMIT/OFFSET because in most circumstances it will execute significantly faster.
WHERE
Filter the results of a query based on a condition or conditions.
SELECT * FROM vehicles WHERE city = 'seattle' AND status = 'available';
GROUP BY
Group the results of a query based on a column.
SELECT city FROM rides
WHERE city IN ('new york', 'chicago', 'seattle') GROUP BY city;
HAVING
Filter a query based on the results of running an aggregate function.
SELECT city, AVG(revenue) as avg FROM rides GROUP BY city
HAVING AVG(revenue) BETWEEN 50 AND 60;
Joining tables
A join statement in SQL looks like this:
SELECT table1.column1, table2.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.column1 = table2.column1;
Note that “left” refers to the table listed first in your SQL statement, so in the example above, table1 is the left table. The output of that query is a table containing column1 and column2 for all of the rows in table1 and table2 that match on column1.
Types of joins:
Aggregating data
Selected aggregate functions:
Example:
SELECT AVG(balance) FROM accounts WHERE balance > 0;
This SQL query that would return the average value of the balance column from the table accounts, not including rows with a balance of zero.
Logical operators
Selected logical operators:
Managing indexes
CREATE INDEX
Create an index for a table using one or more columns.
CREATE INDEX ON table1 (column1, column2);
ALTER INDEX … RENAME TO
Rename an index.
ALTER INDEX usersname_idx RENAME TO users_name_idx;
DROP INDEX
Remove an index.
DROP INDEX users_name_idx;
Administration/Ops
Database access
CREATE ROLE
Create a SQL user role (group of users).
CREATE ROLE basic_user;
DROP ROLE
Remove a SQL user role.
DROP ROLE basic_user;
CREATE USER
Create a new user.
CREATE USER alex;
DROP USER
Remove a user.
DROP USER alex;
GRANT
Grant privileges to a user or a role.
GRANT DELETE ON TABLE rides TO alex;
REVOKE
Revoke database privileges from a user or role.
REVOKE ALL ON DATABASE defaultdb FROM alex;
SHOW ROLES*
List the roles for all databases in a cluster.
SHOW ROLES;
SHOW USERS*
List the users for all databases in a cluster.
SHOW USERS;
SHOW GRANTS*
View the privileges granted to a user or role.
SHOW GRANTS FOR alex;
Performance investigation
EXPLAIN
View the query plan for a query without executing it.
EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;
EXPLAIN ANALYZE
Execute a query and generate a physical query plan with execution statistics.
EXPLAIN ANALYZE SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;
Build what you dream. Never worry about your database again. Start building today with CockroachDB.
