本小节将大致介绍CockroachDB当中部分重要的SQL语句,以便于快速入门。完整的SQL列表和细节介绍请查看SQL Statements章节。

创建数据库

CockroachDB有一个默认的只读数据库system,存储CockroachDB的元数据。

创建数据库

使用CREATE DATABASE+库名标志符 创建数据库并进行管理,例如:

CREATE DATABASE bank; //创建数据库bank
CREATE DATABASE IF NOT EXISTS bank; //如果数据库bank不存在,创建数据库
DROP DATABASE bank; //删除数据库bank

显示数据库列表

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| bank               |
| system             |
+--------------------+
(2 rows)

设置默认数据库

SET DATABASE = bank; //设置默认数据库为bank
SHOW DATABASE; //显示当前使用的数据库
+----------+
| database |
+----------+
| bank     |
+----------+
(1 row)

创建数据库表

//创建表accounts注:如果未指定数据库表的PRIMARY KEY,默认新增使用一个隐藏属性rowid作为主键
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL
); 

//如果表accounts不存在,则创建表
CREATE TABLE IF NOT EXISTS accounts (
id INT PRIMARY KEY,
balance DECIMAL
); 
 //显示表属性
SHOW COLUMNS FROM accounts;
+---------+---------+-------+---------+-----------+
|  Field  |  Type   | Null  | Default |  Indices  |
+---------+---------+-------+---------+-----------+
| id      | INT     | false | NULL    | {primary} |
| balance | DECIMAL | true  | NULL    | {}        |
+---------+---------+-------+---------+-----------+
(2 rows)
DROP TABLE accounts; //删除表accounts

显示数据表

//显示当前使用数据库的所有表
SHOW TABLES; 
+----------+
|  Table   |
+----------+
| accounts |
| users    |
+----------+
(2 rows)

//显示指定的animals数据库的所有表
SHOW TABLES FROM animals; 
+-----------+
|   Table   |
+-----------+
| aardvarks |
| elephants |
| frogs     |
| moles     |
| pandas    |
| turtles   |
+-----------+
(6 rows)

往表中插入记录

INSERT INTO accounts VALUES (1, 10000.50); //插入一条完整的记录
INSERT INTO accounts (balance, id) VALUES (25000.00, 2); //插入一条只有部分属性值的记录
INSERT INTO accounts VALUES (3, 8100.73), (4, 9400.10); //插入多条以逗号隔开的记录
INSERT INTO accounts (id) VALUES (5); //插入id为5的一条记录,balance隐式取默认值
INSERT INTO accounts (id, balance) VALUES (6, DEFAULT); //插入id为6的一条,balance显式取DEFAULT值

TIPS: 关于DEFAULT值更详细的介绍可见:Defaults values

SELECT * FROM accounts WHERE id in (5, 6);
+----+---------+
| id | balance |
+----+---------+
|  5 | NULL    |
|  6 | NULL    |
+----+---------+
(2 rows)

创建索引

索引能够定位到行,避免遍历所有的行。CockroachDB自动为主键和带有Unique约束的属性列建立索引。

//为accounts表的balance_idx属性创建一个倒序的索引
CREATE INDEX balance_idx ON accounts (balance DESC); 

//在创建accounts表同时创建balance属性的索引balance_idx
CREATE TABLE accounts (
id INT PRIMARY KEY,
balance DECIMAL,
INDEX balance_idx (balance)
); 

显示表索引

SHOW INDEX FROM accounts; //显示accounts表的所有索引
+----------+-------------+--------+-----+---------+-----------+---------+----------+
|  Table   |    Name     | Unique | Seq | Column  | Direction | Storing | Implicit |
+----------+-------------+--------+-----+---------+-----------+---------+----------+
| accounts | primary     | true   |   1 | id      | ASC       | false   | false    |
| accounts | balance_idx | false  |   1 | balance | DESC      | false   | false    |
| accounts | balance_idx | false  |   2 | id      | ASC       | false   | true     |
+----------+-------------+--------+-----+---------+-----------+---------+----------+
(3 rows)

查询表记录

//查询accounts表的所有记录并返回balance属性值
SELECT balance FROM accounts; 
+----------+
| balance  |
+----------+
| 10000.50 |
| 25000.00 |
|  8100.73 |
|  9400.10 |
| NULL     |
| NULL     |
+----------+
(6 rows)

//查询accounts表的所有记录并返回所有属性值
SELECT * FROM accounts; 
+----+----------+
| id | balance  |
+----+----------+
|  1 | 10000.50 |
|  2 | 25000.00 |
|  3 |  8100.73 |
|  4 |  9400.10 |
|  5 | NULL     |
|  6 | NULL     |
+----+----------+
(6 rows)

//查询accounts表balance值大于9000的所有记录,返回id、balance两个属性的值
SELECT id, balance FROM accounts WHERE balance > 9000; 
+----+---------+
| id | balance |
+----+---------+
|  2 |   25000 |
|  1 | 10000.5 |
|  4 |  9400.1 |
+----+---------+
(3 rows)

//查询accounts表的所有记录,并按照balance属性倒序排序,返回id、balance两个属性的值
SELECT id, balance FROM accounts ORDER BY balance DESC; 
+----+---------+
| id | balance |
+----+---------+
|  2 |   25000 |
|  1 | 10000.5 |
|  4 |  9400.1 |
|  3 | 8100.73 |
|  5 | NULL    |
|  6 | NULL    |
+----+---------+
(6 rows)

更新表记录

//将accounts中所有balance值小于1000的记录的balance值减去5.50
UPDATE accounts SET balance = balance - 5.50 WHERE balance < 10000; 
+----+----------+
| id | balance  |
+----+----------+
|  1 | 10000.50 |
|  2 | 25000.00 |
|  3 |  8095.23 |
|  4 |  9394.60 |
|  5 | NULL     |
|  6 | NULL     |
+----+----------+
(6 rows)

删除表记录

//从accounts表当中删除id为5或6的记录
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)