[Translate] CockroachDB: 示例集

启动服务器和客户端

启动服务器

./cockroach start --insecure

启动客户端

➜  db ./cockroach sql --insecure
# Welcome to the cockroach SQL interface.
# All statements must be terminated by a semicolon.
# To exit: CTRL + D.

创建数据库

CREATE DATABASE bank;

显示数据库

SHOW DATABASES;

+----------+
| Database |
+----------+
| bank     |
| system   |
+----------+

设置默认数据库

要设置默认数据库, 使用SET DATABASE语句. (类似MYSQL的use)

SET DATABASE = bank;

默认数据库不需要应用其名称, 直接使用表名称即可. 使用SHOW DATABASE显示当前默认数据量.(注意是单数形式)

SHOW DATABASE

+----------+
| Database |
+----------+
| bank     |
| system   |
+----------+

创建表

使用CREATE TABLE语句创建表.

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

避免错误

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

要显示一个表的所有字段, 使用SHOW COLUMNS FROM语句, 后跟表的名称:

SHOW COLUMNS FROM accounts;

+---------+---------+-------+---------+
|  Field  |  Type   | Null  | Default |
+---------+---------+-------+---------+
| id      | INT     | false | NULL    |
| balance | DECIMAL | true  | NULL    |
+---------+---------+-------+---------+

当不再需要表的时候, 使用 DROP TABLE 删除表, 以及表中的所有数据:

DROP TABLE accounts;

显示表

要查看当前数据库中所有的表, 使用SHOW TABLES语句:

SHOW TABLES;

+----------+
|  Table   |
+----------+
| accounts |
| users    |
+----------+

要查看非当前数据库中的所有表, 使用SHOW TABLES FROM语句:

SHOW TABLES FROM animals;

+------------+
|   Table    |
+------------+
| aardvarks  |
| elephants  |
| frogs      |
| moles      |
| pandas     |
| turtles    |
+------------+

在表中插入数据

要插入一行到表中, 使用INSERT INTO语句:

INSERT INTO accounts VALUES (1, DECIMAL '10000.50');

不同的字段顺序

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

插入多行:

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

默认值, balance 字段的默认值为空:

INSERT INTO accounts (id, balance) VALUES (5);
INSERT INTO accounts (id, balance) VALUES (6, DEFAULT);
SELECT * FROM accounts WHERE id in (5, 6);

+----+---------+
| id | balance |
+----+---------+
|  5 | NULL    |
|  6 | NULL    |
+----+---------+

创建索引

在现有的表上创建索引

CREATE INDEX balance_idx ON accounts (balance DESC);

创建表的时候同时创建索引

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

显示表上的索引

SHOW INDEX FROM accounts;

+----------+-------------+--------+-----+---------+-----------+---------+
|  Table   |    Name     | Unique | Seq | Column  | Direction | Storing |
+----------+-------------+--------+-----+---------+-----------+---------+
| accounts | primary     | true   |   1 | id      | ASC       | false   |
| accounts | balance_idx | false  |   1 | balance | DESC      | false   |
+----------+-------------+--------+-----+---------+-----------+---------+

查询

查询一个字段

SELECT balance FROM accounts;

+----------+
| balance  |
+----------+
| 10000.50 |
| 25000.00 |
|  8100.73 |
|  9400.10 |
| NULL     |
| NULL     |
+----------+

查询所有的列, 使用 * 通配符

SELECT * FROM accounts;

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

使用WHERE字句过滤结果

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

+----+----------+
| id | balance  |
+----+----------+
|  4 |  9400.10 |
|  1 | 10000.50 |
|  2 |    25000 |
+----+----------+

使用ORDER BY排序结果, 以及制定正序(ASC)还是逆序(DESC)

SELECT id, balance FROM accounts ORDER BY balance DESC;

+----+----------+
| id | balance  |
+----+----------+
|  2 |    25000 |
|  1 | 10000.50 |
|  4 |  9400.10 |
|  3 |  8100.73 |
|  6 | NULL     |
|  5 | NULL     |
+----+----------+

更新表中的行

UPDATE accounts SET balance = balance - DECIMAL '5.50' WHERE balance < DECIMAL '10000';
SELECT * FROM accounts;

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

删除表中的行

要删除表中的行, 使用DELETE FROM子句, 后面跟上表的名称, 以及WHERE子句用于标识要删除哪些行:

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 |
+----+----------+