This SQL cheat sheet is designed to make it quick and easy to find that SQL command that’s right on the tip of your tongue, or see an example of a common query in action.
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.
Coming soon: It’ll also be available in a printer-friendly, downloadable PDF form so that you can print it out and reference it easily from your desk.
Creates a new database.
CREATE DATABASE bank;
Delete a database and all of its contents.
DROP DATABASE bank;
Show all databases in your cluster.
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.
DROP DATABASE … CASCADE can be used to remove all objects that rely on the database being dropped.
DROP DATABASE … RESTRICT can be used to prevent the
DROP DATABASE command from executing unless the database is empty.
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:
CREATE TABLE users ( id UUID PRIMARY KEY, city STRING, name STRING );
CREATE TABLE users ( id UUID, city STRING, name STRING, PRIMARY KEY (city, id) );
(In this case, referencing a column called
city in table called
CREATE TABLE users ( id UUID PRIMARY KEY, city STRING REFERENCES locations(city), name STRING );
CREATE TABLE users ( id UUID PRIMARY KEY, city STRING, name STRING, INDEX (name) );
(In this case, using CockroachDB’s
gen_random_uuid() function to generate a random UUID as the default value).
CREATE TABLE users ( id UUID PRIMARY KEY DEFAULT gen_random_uuid(), city STRING, name STRING );
NULLvalues from a column:
CREATE TABLE users ( id UUID NOT NULL, city STRING, name STRING );
CREATE TABLE users ( id UUID NOT NULL, city STRING, name STRING, name_and_city STRING AS (CONCAT(name, ' ', city)) STORED );
CREATE TABLE users ( id UUID PRIMARY KEY, city STRING REFERENCES locations(city) ON DELETE CASCADE, name STRING );
CREATE TABLE users_ny(user_id, name, city) AS SELECT * FROM users WHERE city = 'new york';
Apply a schema change to a table.
ALTER TABLE bank ADD COLUMN active BOOL;
ALTER TABLE is used with subcommands such as:
Add a column.
ALTER TABLE bank ADD COLUMN active BOOL;
Remove a column.
ALTER TABLE bank DROP COLUMN active;
Change column constraints, datatypes, etc.
ALTER TABLE bank ALTER account_balance TYPE FLOAT;
Rename a column.
ALTER TABLE bank RENAME COLUMN account_balance TO balance;
Rename a table.
ALTER TABLE bank RENAME TO users;
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.
DROP TABLE statements with caution!
Add a key, check, or unique constraint to a column.
ALTER TABLE users ADD CONSTRAINT id_name_unique UNIQUE (id, name);
Remove a constraint from a column.
ALTER TABLE users DROP CONSTRAINT id_name_unique;
Add or remove
NOT NULL constraints, change datatypes.
ALTER TABLE subscriptions ALTER COLUMN newsletter SET NOT NULL;
Insert rows with specified values into a table.
INSERT INTO users (name, city) VALUES('Alice', 'New York');
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');
Update row(s) in a table.
UPDATE users SET address = '201 E Randolph St' WHERE id = '851eb851-eb85-4000-8000-00000000001a';
Note: without a
UPDATE will update the value of the specified column or columns for all rows.
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’, ‘email@example.com’) ON CONFLICT (id) DO UPDATE;
Upsert a row into the database.
UPSERT INTO employees (id, name, email) VALUES (6, ‘Lambert’, ‘firstname.lastname@example.org`);
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 a specific row or rows.
DELETE FROM promo_codes WHERE code = 'HAPPY50';
Return the values of specific columns in a table.
SELECT id, city, name FROM users;
Return the values of all columns in a table.
SELECT * FROM users;
Limit the number of rows returned by a query.
SELECT * FROM users LIMIT 5;
Skip the first n rows before returning a query result.
SELECT * FROM users LIMIT 5 OFFSET 5;
Filter the results of a query based on a condition or conditions.
SELECT * FROM vehicles WHERE city = 'seattle' AND status = 'available';
Group the results of a query based on a column.
SELECT city FROM rides WHERE city IN ('new york', 'chicago', 'seattle') GROUP BY city;
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;
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
column2 for all of the rows in
table2 that match on
|INNER JOIN||Return records with matching values in both tables.|
|LEFT JOIN||Return all records from the left table and matching values from the right table.|
|RIGHT JOIN||Return all records from the right table and matching values from the left table.|
|FULL JOIN||Return all records with matching values in either table.|
|UNION||“Stack” the tables, with rows from the left table returned first, followed by rows from the right table.|
|Function||Use it to…|
|COUNT()||Count the number of rows.|
|AVG()||Average the values in a column.|
|SUM()||Add the values in a column.|
|MIN()||Return the lowest value in a column.|
|MAX()||Return the highest value in a column.|
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.
|Operator||Use it to…|
|ALL||Return TRUE if all values in a subquery meet the specified condition.|
|AND||Return records for which the conditions separated by AND are true.|
|ANY||Return TRUE if any values in a subquery meet the specified condition.|
|BETWEEN||Return records from the query that fall between two specified values.|
|EXISTS||Return TRUE if any record exists in the subquery.|
|IN||Specify multiple values in a WHERE query without having to use OR between each value.|
|LIKE||Return records that match a specified pattern.|
|NOT||Return records that do not meet the specified condition.|
|OR||Return records for which any of the conditions separated by OR are true.|
Create an index for a table using one or more columns.
CREATE INDEX ON table1 (column1, column2);
Rename an index.
ALTER INDEX usersname_idx RENAME TO users_name_idx;
Remove an index.
DROP INDEX users_name_idx;
Create a SQL user role (group of users).
CREATE ROLE basic_user;
Remove a SQL user role.
DROP ROLE basic_user;
Create a new user.
CREATE USER alex;
Remove a user.
DROP USER alex;
Grant privileges to a user or a role.
GRANT DELETE ON TABLE rides TO alex;
Revoke database privileges from a user or role.
REVOKE ALL ON DATABASE defaultdb FROM alex;
List the roles for all databases in a cluster.
List the users for all databases in a cluster.
View the privileges granted to a user or role.
SHOW GRANTS FOR alex;
View the query plan for a query without executing it.
EXPLAIN SELECT * FROM rides WHERE revenue > 90 ORDER BY revenue ASC;
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, a free, serverless distributed SQL database.
Six months ago, we reported our first implementation of SQL joins in CockroachDB. At that point in …Read More
Upserts are useful for anyone who works with a database to know, but the term “upsert” might not even appear in your …Read More