Basic SQL operations

This topic describes basic SQL operations in OceanBase Community Edition.

Create databases

Use the CREATE DATABASE statement to create databases.

Example:

• Create a database named test1 and specify the UTF8MB4 character set.

  obclient> CREATE DATABASE test1 DEFAULT CHARACTER SET UTF8MB4;
  Query OK, 1 row affected
  

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

  obclient> CREATE DATABASE test2 READ WRITE;
  Query OK, 1 row affected
  

• Use the USE DATABASE statement to switch to the test1 database.

  obclient> USE test1;
  Database changed
  

• Use the SHOW DATABASES statement to list all databases hosted on the current database server.

  obclient> SHOW DATABASES;
  +--------------------+
  | Database           |
  +--------------------+
  | oceanbase          |
  | test1              |
  +--------------------+
  7 rows in set
  
  
  

Table-level operations

Tables are the most basic data storage units in OceanBase Database. A table contains specific data that a user can access. A table consists of rows, and a row consists of columns.

Create a table

Use the CREATE TABLE statement to create a table in the database.

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

View tables

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

obclient> SHOW CREATE TABLE test;

Use the DESCRIBE TABLE statement to view table creation information.

Example:

obclient> DESCRIBE test;

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

obclient> SHOW TABLES FROM test;

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 the c2 field in the test table 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 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    |       |
  +-------+----------+------+-----+---------+-------+
  

• Add and drop columns

  • Before you add a column, execute the DESCRIBE statement 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
    

  • Execute the following statement to add Column c3:

    obclient> ALTER TABLE test ADD c3 int;
    Query OK, 0 rows affected
    

  • After you add a column, execute the DESCRIBE statement 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
    

  • Run the following command to drop Column 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
    

  • Set the number of replicas of the test table and add Column 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 a table

Use the DROP TABLE statement to drop 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 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 the test table:

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

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

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

View indexes

You can execute the SHOW INDEX statement to view the indexes of a table.

Example:

View the index of the test table.

obclient> SHOW INDEX FROM test;

Drop an index

Use the DROP INDEX statement to drop a table index.

Example:

Drop the index of the test table.

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

• Insert a row of data into Table 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
  

• 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
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  1 |    1 |
  |  2 | NULL |
  |  4 |   12 |
  +----+------+
  3 rows in set
  

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-partitioned table and the partition names are p0, p1, p2, and p3, which are automatically generated based on the partitioning command rules.

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

• Delete data in a single table: Delete rows where c1 = 2. Column c1 is the primary key column of Table t1.

  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
  

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

  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
  

• 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
  
  obclient> DELETE FROM t2  PARTITION(p2);
  Query OK, 3 rows affected
  
  obclient> SELECT * FROM t2;
  +----+------+
  | c1 | c2   |
  +----+------+
  |  5 |    5 |
  +----+------+
  1 row in set
  

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

  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
  

• Delete data from 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
  
  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 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
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

• 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
  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
  

• 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
  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
  

• 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
  
  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
  

• Update multiple tables. For rows where t1.c2 = t2.c2, set their values in Column c2 of Table t1 to 100 and set their values in Column c2 of 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
  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
  

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

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

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

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

2. Deduplicate the query results of the name field.

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

3. Return the values of the corresponding id, name, and num fields based on the filter 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
   

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 persisted. 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******@obmysql#obdemo -P2883 -p****** 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

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