Basic Operations

This topic describes the basic operations about OceanBase.

Database operations

Create a database

Use the CREATE DATABASE statement to create a database.
Example:

• Create a database named test 2 and specify the character set as UTF8.

  obclient> CREATE DATABASE test2 DEFAULT CHARACTER SET UTF8; 
  Query OK, 1 row affected (0.00 sec)
  

• Create a database named test3 that supports read and write operations.

  obclient> CREATE DATABASE test3 READ WRITE; 
  Query OK, 1 row affected (0.03 sec)
  

View databases

Use the SHOW DATABASES statement to view databases.
Example:

obclient> SHOW DATABASES;

Modify a database

Use the ALTER DATABASE statement to modify database attributes.
Example:
Change the character set of Database test2 to UTF8MB4, set the database collation to UTF8MB4_BIN, and set the database property to Read and Write.

obclient> ALTER DATABASE test2 DEFAULT CHARACTER SET UTF8MB4; 

obclient> ALTER DATABASE test2 DEFAULT COLLATE UTF8MB4_BIN; 

obclient> ALTER DATABASE test2 READ WRITE;

Delete a database

Use the DROP DATABASE statement to delete a database.
Example:

obclient> DROP DATABASE my_db; 

Table operations

Create a table

Use the CREATE TABLE statement to create a table.
Example:

obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(3));

View a table

Use the SHOW CREATE TABLE statement to view table creation statements.
Example:

obclient> SHOW CREATE TABLE test;

Use the SHOW TABLES statement to view all tables in a specified database.
Example:

obclient> SHOW TABLES FROM my_db;

Modify a table

Use the ALTER TABLE statement to modify the structure of an existing table, including modifying the table name and table attributes, adding columns, modifying columns and attributes, and dropping columns.
Example:

• Change the name of Field c2 in Table test to c3 and modify its field type.

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 CHAR(10);
Query OK, 0 rows affected (0.08 sec)
obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1    | int(11)  | NO   | PRI | NULL    |       |
| c3     | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

• Add and drop a column.

  • Before you add a column, run the DESCRIBE test; command to view the table information.

    obclient> DESCRIBE test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | c1    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(3) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
    

  • Run the following command to add Column c3:

    obclient> ALTER TABLE test ADD c3 int; 
    Query OK, 0 rows affected (0.08 sec)
    

  • After you add a column, run the DESCRIBE test; command to view the table information.

    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 (0.00 sec)
    

  • Run the following command to drop Column c3:

    obclient> ALTER TABLE test DROP c3; 
    Query OK, 0 rows affected (0.08 sec)
    

  • After you drop a column, run the DESCRIBE test; command to view the table information.

    obclient> DESCRIBE test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | c1    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    

  • Set the number of replicas of Table test and add Column c5.

    obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;
    Query OK, 0 rows affected (0.06 sec)
    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 (0.00 sec)
    

Delete a table

Use the DROP TABLE statement to delete a table.
Example:

obclient> DROP TABLE test;

or

obclient> DROP TABLE IF EXISTS test;

Index operations

An index is a database structure created for a table to sort data in one or more columns of the table in a specific order. It mainly improves the query speed and reduces the performance overhead of database systems.

Create an index

Use the CREATE INDEX statement to create a table index.
Example:

1. Run the following command to create Table test:

   obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(10));
   

2. Run the following command to create an index for Table test:

   obclient> CREATE INDEX test_index ON test (c1, c2);
   

View an index

Use the SHOW INDEX statement to view the index of a table.
Example:
View the index of Table test.

obclient> SHOW INDEX FROM test;

Delete an index

Use the DROP INDEX statement to delete the index of a table.
Example:
Delete the index of Table test.

obclient> DROP INDEX test_index ON test;

Insert data

Use the INSERT statement to insert data into an existing table.
Example:
Create Table t1 that contains the following data:

obclient> CREATE TABLE t1(c1 int primary key, c2 int) partition BY key(c1) partitions 4;
Query OK, 0 rows affected (0.11 sec)

• Insert a row of data into Table t1.

  obclient> INSERT INTO t1 VALUES(1,1);
  Query OK, 1 row affected (0.01 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  +----+------+
  1 row in set (0.04 sec)
  

• Insert two or more rows of data into Table t1.

  obclient> INSERT t1 VALUES(1,1),(2,default),(2+2,3*4);
  Query OK, 3 rows affected (0.02 sec)
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 | NULL |
  |  4 |   12 |
  +----+------+
  3 rows in set (0.02 sec)
  

Delete data

Use the DELETE statement to delete data.
Example:
Create Tables t1 and t2 that contain the following data: Table t2 is a key-based partition table and the partition names are automatically generated by the system according to the partitioning command rules. That is, the partition names are p0, p1, p2, and p3.

obclient> CREATE TABLE t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t1; 
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)

obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition BY key(c1) partitions 4;
Query OK, 0 rows affected (0.19 sec)
obclient> INSERT INTO t2 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.02 sec)

• Delete data in a single table: Delete rows where c1 = 2 from Table t1. The values in Column c1 are primary keys.

  obclient> DELETE FROM t1 WHERE c1 = 2;
  Query OK, 1 row affected (0.02 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  3 rows in set (0.01 sec)
  

• Delete data in a single table: Delete the data in the first row sorted by Column c2from Table t1.

  obclient> DELETE FROM t1 ORDER BY c2 LIMIT 1;
  Query OK, 1 row affected (0.01 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  2 |    2 |
  |  3 |    3 |
  |  4 |    4 |
  +----+------+
  3 rows in set (0.00 sec)
  

• Delete data in a single table: Delete data in Partition p2 from Table t2.

  obclient> SELECT * FROM t2 PARTITION(p2); 
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  |  3 |    3 |
  +----+------+
  3 rows in set (0.01 sec)
  
  obclient> DELETE FROM t2  PARTITION(p2); 
  Query OK, 3 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   | 
  +----+------+
  |  5 |    5 |
  +----+------+
  1 row in set (0.02 sec)
  

• Delete data in two or more tables: Delete rows where t1.c1 = t2.c1 from Tables t1 and t2.

  obclient> DELETE t1, t2 FROM t1, t2 WHERE t1.c1 = t2.c1;
  Query OK, 3 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  4 |    4 |
  +----+------+
  1 row in set (0.01 sec)
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   |
  +----+------+ 
  |  5 |    5 | 
  +----+------+
  1 row in set (0.01 sec)
  

• Delete data in two or more tables:Delete rows where t1.c1 = t2.c1 from Tables t1 and t2.

  obclient> DELETE FROM t1, t2 USING t1, t2 WHERE t1.c1 = t2.c1;
  Query OK, 4 rows affected (0.02 sec)
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  4 |    4 |
  +----+------+
  1 row in set (0.01 sec)
  
  obclient> SELECT * FROM t2;
  Empty set (0.01 sec)
  

Update data

Use the UPDATE statement to modify the field values in a table.
Example:
Create Tables t1 and t2.

obclient> CREATE TABLE t1(c1 int primary key, c2 int);
Query OK, 0 rows affected (0.16 sec)
obclient> INSERT t1 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  4 |    4 |
+----+------+
4 rows in set (0.06 sec)

obclient> CREATE TABLE t2(c1 int primary key, c2 int) partition by key(c1) partitions 4;
Query OK, 0 rows affected(0.19 sec)
obclient> INSERT t2 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
4 rows in set (0.02 sec)

• For the row where t1.c1 = 1 in Table t1, set its value in Column c2 to 100.

  obclient> UPDATE t1 SET t1.c2 = 100 WHERE t1.c1 = 1;
  Query OK, 1 row affected (0.02 sec)
  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 (0.01 sec)
  

• For the first two rows sorted by Column c2 in Table t1, set their values in Column c2 to 100.

  obclient> UPDATE t1 set t1.c2 = 100 ORDER BY c2 LIMIT 2;
  Query OK, 2 rows affected (0.02 sec)
  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 (0.01 sec)
  

• In Partition p2 of Table t2, for rows where t2.c1 > 2, set their values in Column c2 to 100.

  obclient> SELECT * FROM t2 partition (p2);
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  |  3 |    3 |
  +----+------+
  3 rows in set (0.01 sec)
  
  obclient> UPDATE t2 partition(p2) SET t2.c2 = 100 WHERE t2.c1 > 2;
  Query OK, 1 row affected (0.02 sec)
  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 (0.00 sec)
  

• Update two or more tables. For rows where t1.c2 = t2.c2 in Tables t1 and t2, set the values of Column c2 in Table t1 to 100, and the values of Column c2 in Table t2 to 200.

  obclient> UPDATE t1,t2 SET t1.c2 = 100, t2.c2 = 200 WHERE t1.c2 = t2.c2;
  Query OK, 6 rows affected (0.03 sec)
  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 (0.00 sec)
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  5 |    5 |
  |  1 |  200 |
  |  2 |  200 |
  |  3 |  200 |
  +----+------+
  4 rows in set (0.01 sec)
  

Query data

Use the SELECT statement to query data from a table.
Example:
Create Table a that contains the following data:

obclient> CREATE TABLE a (id int, name varchar(50), num int);
Query OK, 0 rows affected (0.07 sec)

obclient> INSERT INTO a VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected (0.00 sec)
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 (0.00 sec)

1. Query the data in the name field from Table a:

   obclient> SELECT name FROM a;
   +------+
   | NAME |
   +------+
   | a    |
   | b    |
   | a    |
   +------+
   3 rows in set (0.00 sec)
   

2. Deduplicate the query results of the name field.

   obclient> SELECT DISTINCT name FROM a;
   +------+
   | NAME |
   +------+
   | a    |
   | b    |
   +------+
   2 rows in set (0.01 sec)
   

3. Return the values of the corresponding id, name, and num fields based on the filtering condition name = 'a' from Table a.

   obclient> SELECT id, name, num FROM a WHERE name = 'a';
   +------+------+------+
   | ID   | NAME | NUM  |
   +------+------+------+
   |    1 | a    |  100 |
   |    3 | a    |   50 |
   +------+------+------+
   2 rows in set (0.00 sec)
   

Commit a transaction

Use the COMMIT statement to commit a transaction.
Before you commit a transaction:

• Your modifications are visible only to the current session and invisible to other database sessions.
• Your modifications are not persistent. You can use the ROLLBACK statement to undo them.

After you commit a transaction:

• Your changes are visible to all database sessions.
• Your modifications are persisted into the database. You cannot use the ROLLBACK statement to undo them.

Example:

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******@obbmsql#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

Roll back a transaction

Use the ROLLBACK statement to roll back a transaction.
A transaction rollback reverses all changes made in the transaction. You can roll back an entire uncommitted transaction or roll back a transaction to any savepoints of the transaction. To roll back to a specific savepoint, you must use both the ROLLBACK and TO SAVEPOINT statements.
Notes:

• If you roll back an entire transaction:
  • The transaction will end.
  • All modifications made from the start of the transaction will be discarded.
  • All savepoints will be cleared.
  • All locks held by the transaction will be released.
• If you roll back a transaction to a specific savepoint:
  • The transaction will not end.
  • Modifications made before the savepoint will be retained but those made after it will be discarded.
  • All savepoints after the specific savepoint will be cleared.
  • All locks held by the transaction after the specific savepoint will be released.

Example:
Undo all data modifications of a transaction.

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 (0.00 sec)

obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
Query OK, 1 row affected (0.00 sec)

obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected (0.00 sec)
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 (0.00 sec)


obclient> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

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 (0.00 sec)