启动服务器和客户端
启动服务器
./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 |
+----+----------+