CockroachDB SQL Cheat Sheet

Written by orkb

May 10, 2019

CockroachDB

Introduction

This is a cheat sheet for all the basic SQL commands that you’ll need to work in CockroachDB. CockroachDB is a new technology to that allows companies to create distributed SQL databases, so large databases no longer have to live on a single machine. If you don’t know SQL syntax don’t be intimidated because we’ll give you simple examples that you can use as a basis for your own queries.

Note: If you’re experienced with SQL the syntax should be identical for these basic commands but will vary as you get into more complex commands.

Prerequisites

You should have CockroachDB installed.
It’s recommended that you have some experience working in the Terminal.

Cheat Sheet

Get Started: To get started we’ll get access to the SQL client by running this next command which opens a shell to a temporary cluster. We’ll also be demo’ing with a table of products using fields like price but as we mentioned before these are simple examples meant to be used as a basis for your own queries.

cockroach demo

You should see a response similar to this letting you know you’re working in a temporary cluster.

#
# Welcome to the CockroachDB demo database!
#
# You are connected to a temporary, in-memory CockroachDB
# instance. Your changes will not be saved!
#
# Web UI: http://127.0.0.1:62471
#
# Server version: CockroachDB CCL v19.1.0 (x86_64-apple-darwin14, built 2019/04/29 18:31:15, go1.11.6) (same version as client)
# Cluster ID: 234f6003-3b26-4135-a4e5-7feb33c66ebd
#
# Enter \? for a brief introduction.
#

Creating Tables

Let’s create a new table called products with three columns of different datatypes.

CREATE TABLE products (
id INT PRIMARY KEY,
product STRING,
price DECIMAL
);

Create a table only if it doesn’t exist already.

CREATE TABLE IF NOT EXISTS products (
id INT PRIMARY KEY,
product STRING,
price DECIMAL
);

Display Columns

List all the columns of a given table.

SHOW COLUMNS FROM products;

You should see a response like this:

column_name | data_type | is_nullable | column_default | generation_expression | indices | is_hidden
+-------------+-----------+-------------+----------------+-----------------------+-----------+-----------+
id | INT8 | false | NULL | | {primary} | false
product | STRING | true | NULL | | {} | false
price | DECIMAL | true | NULL | | {} | false

Drop/Delete Table

To drop, or delete, a table this is the syntax.

DROP TABLE products;

Show All Tables

SHOW TABLES;

If you have a table the response should look something like this:

table_name
+------------+
products
(1 row)

Insert Rows into a Table

Continuing with our products table we could insert a new product like this:

INSERT INTO products (id, product, price) VALUES (1, 'Milk', 2.5);

To insert multiple rows.

INSERT INTO products (id, product, price) VALUES (2, 'Lemons', 1.5), (3, 'Apples', 4.0);

If you insert a record but don’t specify a field its default value will be used.

Select All

To select all records in a table

SELECT * FROM products;

Response:

Select Based on a Condition

There are tons of conditions you can use to limit your query but we’ll just show a couple examples.

SELECT * FROM products WHERE id = 1;
SELECT * FROM products WHERE id in (1, 2);
SELECT * FROM products WHERE price > 2.0;
SELECT * FROM products WHERE price < 2.0;
SELECT * FROM products WHERE price < 10.0 AND id in (1,2);

Update Records

Our example of an update will be to set the price of Milk to $15.00.

UPDATE products SET price = 15.0 WHERE id = 1;

Delete Records

Our example for deleting a record will to delete the Milk product from our products table.

DELETE FROM products WHERE id = 1;

Here’s another condition to delete based on a field that is not the id.

DELETE FROM products WHERE price > 0.1;

Conclusion

This completes our CockroachDB basic SQL Cheat Sheet. If you’re familiar with SQL you’ll notice that for the basic commands there is little to no change in the syntax. Once you start getting into joins the SQL queries can get a lot more complex but you should feel confident that you have a good basis for learning the rest of the CockroachDB SQL commands. If you have any questions about this cheat sheet or about CockroachDB in general please don’t hesitate to reach out.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started