cockroachdb mysql_[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 |

+----+----------+