本文介绍了社区版 OceanBase 数据库的一些基本操作。
使用 CREATE DATABASE 语句创建数据库。
示例如下:
创建数据库 test1,并指定字符集为 UTF8MB4。
obclient> CREATE DATABASE test1 DEFAULT CHARACTER SET UTF8MB4;
Query OK, 1 row affected
创建读写属性的数据库 test2。
obclient> CREATE DATABASE test2 READ WRITE;
Query OK, 1 row affected
使用 USE DATABASE 切换到 test1 数据库中。
obclient> USE test1;
Database changed
使用 SHOW DATABASES 列出当前数据库服务器中所有的数据库。
obclient> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| oceanbase |
| test1 |
+--------------------+
7 rows in set
在 OceanBase 数据库中,表是最基础的数据存储单元。表包含了所有用户可以访问的数据,每个表包含多行记录,每个记录由多个列组成。
使用 CREATE TABLE 语句在数据库中创建新表。
obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(3));
使用 SHOW CREATE TABLE 语句查看建表语句。
obclient> SHOW CREATE TABLE test;
使用 DESCRIBE TABLE 语句查看建表信息。
示例如下:
obclient> DESCRIBE test;
使用 SHOW TABLES 语句查看指定数据库中的所有表。
obclient> SHOW TABLES FROM test;
使用 ALTER TABLE 语句来修改已存在的表的结构,包括修改表及表属性、新增列、修改列及属性、删除列等。
示例如下:
把表 test 的字段 c2 改名为 c3,并同时修改其字段类型。
obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
obclient> ALTER TABLE test CHANGE COLUMN c2 c3 VARCHAR(10);
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c3 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
增加、删除列
增加列前,执行 DESCRIBE 命令查看表信息。
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
执行以下命令,增加 c3 列。
obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected
增加列后,通过 DESCRIBE 命令查看表信息。
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
执行以下命令,删除 c3 列。
obclient> ALTER TABLE test DROP c3;
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
设置表 test 的副本数,并且增加列 c5。
obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
| c5 | int(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
3 rows in set
使用 DROP TABLE 语句删除表。
示例如下:
obclient> DROP TABLE test;
或
obclient> DROP TABLE IF EXISTS test;
索引是创建在表上并对数据库表中一列或多列的值进行排序的一种结构。其作用主要在于提高查询的速度,降低数据库系统的性能开销。
使用 CREATE INDEX 语句创建表的索引。
示例:
执行以下命令,创建表 test。
obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
执行以下命令,创建表 test 的索引。
obclient> CREATE INDEX test_index ON test (c1, c2);
使用 SHOW INDEX 语句查看表的索引。
示例如下:
查看表 test 的索引。
obclient> SHOW INDEX FROM test;
使用 DROP INDEX 语句删除表的索引。
示例如下:
删除表 test 的索引。
obclient> DROP INDEX test_index ON test;
使用 INSERT 语句在已经存在的表中插入数据。
示例如下:
假设有如下所示数据的表 t1。
obclient> CREATE TABLE t1(c1 int primary key, c2 int) partition BY key(c1) partitions 4;
Query OK, 0 rows affected
向表 t1 中插入一行数据。
obclient> INSERT INTO t1 VALUES(1,1);
Query OK, 1 row affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set
向表 t1 中插入多行数据。
obclient> INSERT t1 VALUES(1,1),(2,default),(2+2,3*4);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | NULL |
| 4 | 12 |
+----+------+
3 rows in set
使用 DELETE 语句删除数据。
示例如下:
假设有如下所示数据的表 t1 和 t2。其中,表 t2 为 KEY 分区表,且分区名由系统根据分区命令规则自动生成,即分区名为 p0、p1、p2、p3。
obclient> CREATE TABLE t1(c1 int primary key, c2 int);
Query OK, 0 rows affected
obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition BY key(c1) partitions 4;
Query OK, 0 rows affected
obclient> INSERT INTO t2 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
4 rows in set
单表删除,删除 c1=2 的行,其中 c1 列为表 t1 中的 Primary Key。
obclient> DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 4 | 4 |
+----+------+
3 rows in set
单表删除,删除表 t1 中按照 c2 列排序之后的第一行数据。
obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1;
Query OK, 1 row affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
3 rows in set
单表删除,删除表 t2 的 p2 分区的数据。
obclient> SELECT * FROM t2 PARTITION(p2);
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set
obclient> DELETE FROM t2 PARTITION(p2);
Query OK, 3 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set
多表删除,删除 t1、t2 表中 t1.c1 = t2.c1 的数据。
obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;
Query OK, 3 rows affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 4 | 4 |
+----+------+
1 row in set
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set
多表删除,删除 t1、t2 表中 t1.c1 = t2.c1 的数据。
obclient> DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1;
Query OK, 4 rows affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 4 | 4 |
+----+------+
1 row in set
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set
使用 UPDATE 语句修改表中的字段值。
示例如下:
创建示例表 t1 和 t2。
obclient> CREATE TABLE t1(c1 int primary key, c2 int);
Query OK, 0 rows affected
obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected
obclient> INSERT t2 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
4 rows in set
将表 t1 中 t1.c1=1 对应的那一行数据的 c2 列值修改为 100。
obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
将表 t1 中按照 c2 列排序的前两行数据的 c2 列值修改为 100。
obclient> UPDATE t1 set t1.c2 = 100 ORDER BY c2 LIMIT 2;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
将表 t2 中 p2 分区的数据中 t2.c1 > 2 的对应行数据的 c2 列值修改为 100。
obclient> SELECT * FROM t2 partition (p2);
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set
obclient> UPDATE t2 partition(p2) SET t2.c2 = 100 WHERE t2.c1 > 2;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT * FROM t2 partition (p2);
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 100 |
+----+------+
3 rows in set
修改多个表。对于表 t1 和表 t2 中满足 t1.c2 = t2.c2 对应行的数据,将表 t1 中的 c2 列值修改为 100,表 t2 中的 c2 列值修改为 200。
obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
Query OK, 6 rows affected
Rows matched: 6 Changed: 6 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 100 |
| 2 | 100 |
| 3 | 100 |
| 4 | 4 |
+----+------+
4 rows in set
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 200 |
| 2 | 200 |
| 3 | 200 |
+----+------+
4 rows in set
使用 SELECT 语句查询表中的内容。
示例如下:
假设有如下所示数据的表 a。
obclient> CREATE TABLE a (id int, name varchar(50), num int);
Query OK, 0 rows affected
obclient> INSERT INTO a VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM a;
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 2 | b | 200 |
| 3 | a | 50 |
+------+------+------+
3 rows in set
从表 a 中读取 name 的数据。
obclient> SELECT name FROM a;
+------+
| NAME |
+------+
| a |
| b |
| a |
+------+
3 rows in set
在查询结果中对 name 进行去重处理。
obclient> SELECT DISTINCT name FROM a;
+------+
| NAME |
+------+
| a |
| b |
+------+
2 rows in set
从表 a 中根据筛选条件 name = ‘a’ ,输出对应的 id 、name 和 num。
obclient> SELECT id, name, num FROM a WHERE name = 'a';
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 3 | a | 50 |
+------+------+------+
2 rows in set
使用 COMMIT 语句提交事务。
在提交事务(COMMIT)之前:
您的修改只对当前会话可见,对其他数据库会话均不可见。
您的修改没有持久化,您可以通过 ROLLBACK 语句撤销修改。
在提交事务(COMMIT)之后:
您的修改对所有数据库会话可见。
您的修改持久化成功,不能通过 ROLLBACK 语句回滚修改。
示例如下:
obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
Query OK, 1 row affected
obclient> COMMIT;
Query OK, 0 rows affected
obclient> exit;
Bye
[user@host ~]$obclient -h10.10.10.1 -u******@obmysql#obdemo -P2883 -p**1*** TPCC
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | NULL | 2020-04-02 17:53:34 |
+----+------+-------+---------------------+
4 rows in set
使用 ROLLBACK 语句回滚事务。
回滚一个事务指将事务的修改全部撤销。可以回滚当前整个未提交的事务,也可以回滚到事务中任意一个保存点。如果要回滚到某个保存点,必须结合使用 ROLLBACK 和 TO SAVEPOINT 语句。
其中:
如果回滚整个事务,则:
事务会结束
所有的修改会被丢弃
清除所有保存点
释放事务持有的所有锁
如果回滚到某个保存点,则:
事务不会结束
保存点之前的修改被保留,保存点之后的修改被丢弃
清除保存点之后的保存点(不包括保存点自身)
释放保存点之后事务持有的所有锁
示例如下:
回滚事务的全部修改:
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
+----+------+-------+---------------------+
3 rows in set
obclient> BEGIN;
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
Query OK, 1 row affected
obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
| 4 | JP | 10004 | 2020-04-02 17:53:34 |
| 5 | FR | 10005 | 2020-04-02 17:54:53 |
| 6 | RU | 10006 | 2020-04-02 17:54:53 |
+----+------+-------+---------------------+
6 rows in set
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2020-04-02 17:52:31 |
| 2 | US | 10002 | 2020-04-02 17:52:38 |
| 3 | EN | 10003 | 2020-04-02 17:52:38 |
+----+------+-------+---------------------+
3 rows in set