# Learn CockroachDB SQL

This page walks you through some of the most essential CockroachDB SQL statements. For a complete list and related details, see SQL Statements.

Tip:

Use an interactive SQL shell to try out these statements. If you have a cluster already running, use the `cockroach sql` command. Otherwise, use the `cockroach demo` command to open a shell to a temporary, in-memory cluster.

Note:

CockroachDB aims to provide standard SQL with extensions, but some standard SQL functionality is not yet available. See our SQL Feature Support page for more details.

## Create a table

To create a table, use `CREATE TABLE` followed by a table name, the column names, and the data type and constraint, if any, for each column:

    > CREATE TABLE accounts (
        id INT PRIMARY KEY,
        balance DECIMAL
    );

Table and column names must follow these rules. Also, when you do not explicitly define a primary key, CockroachDB will automatically add a hidden `rowid` column as the primary key.

To avoid an error in case the table already exists, you can include `IF NOT EXISTS`:

    > CREATE TABLE IF NOT EXISTS accounts (
        id INT PRIMARY KEY,
        balance DECIMAL
    );

To show all of the columns from a table, use `SHOW COLUMNS FROM` followed by the table name:

    > SHOW COLUMNS FROM accounts;

      column_name | data_type | is_nullable | column_default | generation_expression |   indices   | is_hidden
    +-------------+-----------+-------------+----------------+-----------------------+-------------+-----------+
      id          | INT       |    false    | NULL           |                       | {"primary"} |   false
      balance     | DECIMAL   |    true     | NULL           |                       | {}          |   false
    (2 rows)

When you no longer need a table, use `DROP TABLE` followed by the table name to remove the table and all its data:

    > DROP TABLE accounts;

## Show tables

To see all tables in the active database, use the `SHOW TABLES` statement:

    > SHOW TABLES;

      table_name
    +------------+
      accounts
    (1 row)

## Insert rows into a table

To insert a row into a table, use `INSERT INTO` followed by the table name and then the column values listed in the order in which the columns appear in the table:

    > INSERT INTO accounts VALUES (1, 10000.50);

If you want to pass column values in a different order, list the column names explicitly and provide the column values in the corresponding order:

    > INSERT INTO accounts (balance, id) VALUES
        (25000.00, 2);

To insert multiple rows into a table, use a comma-separated list of parentheses, each containing column values for one row:

    > INSERT INTO accounts VALUES
        (3, 8100.73),
        (4, 9400.10);

Default values are used when you leave specific columns out of your statement, or when you explicitly request default values. For example, both of the following statements would create a row with `balance` filled with its default value, in this case `NULL`:

    > INSERT INTO accounts (id) VALUES
        (5);

    > INSERT INTO accounts (id, balance) VALUES
        (6, DEFAULT);

    > SELECT * FROM accounts WHERE id in (5, 6);

      id | balance
    +----+---------+
       5 | NULL
       6 | NULL
    (2 rows)

## Create an index

Indexes help locate data without having to look through every row of a table. They're automatically created for the primary key of a table and any columns with a `UNIQUE` constraint.

To create an index for non-unique columns, use `CREATE INDEX` followed by an optional index name and an `ON` clause identifying the table and column(s) to index. For each column, you can choose whether to sort ascending (`ASC`) or descending (`DESC`).

    > CREATE INDEX balance_idx ON accounts (balance DESC);

You can create indexes during table creation as well; just include the `INDEX` keyword followed by an optional index name and the column(s) to index:

    > CREATE TABLE accounts (
        id INT PRIMARY KEY,
        balance DECIMAL,
        INDEX balance_idx (balance)
    );

## Show indexes on a table

To show the indexes on a table, use `SHOW INDEX FROM` followed by the name of the table:

    > SHOW INDEX FROM accounts;

      table_name | index_name  | non_unique | seq_in_index | column_name | direction | storing | implicit
    +------------+-------------+------------+--------------+-------------+-----------+---------+----------+
      accounts   | primary     |   false    |            1 | id          | ASC       |  false  |  false
      accounts   | balance_idx |    true    |            1 | balance     | DESC      |  false  |  false
      accounts   | balance_idx |    true    |            2 | id          | ASC       |  false  |   true
    (3 rows)

## Query a table

To query a table, use `SELECT` followed by a comma-separated list of the columns to be returned and the table from which to retrieve the data:

    > SELECT balance FROM accounts;

      balance
    +----------+
      10000.50
      25000.00
       8100.73
       9400.10
      NULL
      NULL
    (6 rows)

To retrieve all columns, use the `*` wildcard:

    > SELECT * FROM accounts;

      id | balance
    +----+----------+
       1 | 10000.50
       2 | 25000.00
       3 |  8100.73
       4 |  9400.10
       5 | NULL
       6 | NULL
    (6 rows)

To filter the results, add a `WHERE` clause identifying the columns and values to filter on:

    > SELECT id, balance FROM accounts WHERE balance > 9000;

      id | balance
    +----+----------+
       2 | 25000.00
       1 | 10000.50
       4 |  9400.10
    (3 rows)

To sort the results, add an `ORDER BY` clause identifying the columns to sort by. For each column, you can choose whether to sort ascending (`ASC`) or descending (`DESC`).

    > SELECT id, balance FROM accounts ORDER BY balance DESC;

      id | balance
    +----+----------+
       2 | 25000.00
       1 | 10000.50
       4 |  9400.10
       3 |  8100.73
       5 | NULL
       6 | NULL
    (6 rows)

## Update rows in a table

To update rows in a table, use `UPDATE` followed by the table name, a `SET` clause identifying the columns to update and their new values, and a `WHERE` clause identifying the rows to update:

    > UPDATE accounts SET balance = balance - 5.50 WHERE balance < 10000;

    > SELECT * FROM accounts;

      id | balance
    +----+----------+
       1 | 10000.50
       2 | 25000.00
       3 |  8095.23
       4 |  9394.60
       5 | NULL
       6 | NULL
    (6 rows)

If a table has a primary key, you can use that in the `WHERE` clause to reliably update specific rows; otherwise, each row matching the `WHERE` clause is updated. When there's no `WHERE` clause, all rows in the table are updated.

## Delete rows in a table

To delete rows from a table, use `DELETE FROM` followed by the table name and a `WHERE` clause identifying the rows to delete:

    > DELETE FROM accounts WHERE id in (5, 6);

    > SELECT * FROM accounts;

      id | balance
    +----+----------+
       1 | 10000.50
       2 | 25000.00
       3 |  8095.23
       4 |  9394.60
    (4 rows)

Just as with the `UPDATE` statement, if a table has a primary key, you can use that in the `WHERE` clause to reliably delete specific rows; otherwise, each row matching the `WHERE` clause is deleted. When there's no `WHERE` clause, all rows in the table are deleted.

## What's next?

- Explore all SQL Statements
- Use the built-in SQL client to execute statements from a shell or directly from the command line
- Install the client driver for your preferred language and build an app
- Explore core CockroachDB features like automatic replication, rebalancing, and fault tolerance