Basic SQL operations (Oracle mode)

This topic describes the basic SQL operations in OceanBase Database in Oracle mode.

Table operations

This section describes how to create, view, modify, and drop database tables.

Create a table

You can execute the CREATE TABLE statement to create a table in a database.

For example, create a table named test.

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected

For more information about the CREATE TABLE statement, see CREATE TABLE.

Modify a table

You can execute the ALTER TABLE statement to modify the structure of an existing table, including modifying the table name and table attributes, adding columns, modifying the column name and attributes, and dropping columns.

Here are some examples:

• Modify the field type of c2 in the test table.

  obclient> DESCRIBE test;
  +-------+-------------+------+-----+---------+-------+
  | FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
  +-------+-------------+------+-----+---------+-------+
  | C1    | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(3) | YES  | NULL| NULL    | NULL  |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set
  
  obclient> ALTER TABLE test MODIFY c2 CHAR(10);
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
  | C2    | CHAR(10)   | YES  | NULL| NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

• Add a column to and drop a column from the test table.

  obclient> ALTER TABLE test ADD c3 int;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
  | C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
  | C3    | NUMBER(38) | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  3 rows in set
  
  obclient> ALTER TABLE test DROP COLUMN c3;
  Query OK, 0 rows affected
  
  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
  | C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set
  

For more information about the ALTER TABLE statement, see ALTER TABLE.

Drop a table

You can execute the DROP TABLE statement to drop a table.

For example, drop the test table.

obclient> DROP TABLE test;
Query OK, 0 rows affected

For more information about the DROP TABLE statement, see DROP TABLE.

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 indexes

You can execute the CREATE INDEX statement to create a table index.

For example, create an index on the test table.

obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
+-------+------------+------+-----+---------+-------+
| C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
| C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
+-------+------------+------+-----+---------+-------+
2 rows in set

obclient> CREATE INDEX test_index ON test (c1, c2);
Query OK, 0 rows affected

For more information about the CREATE INDEX statement, see CREATE INDEX.

Query indexes

• Query all indexes on a table by using the ALL_INDEXES view.

  obclient> SELECT * FROM ALL_INDEXES WHERE table_name='TEST'\G
  *************************** 1. row ***************************
                    OWNER: SYS
              INDEX_NAME: TEST_OBPK_1664353339491130
              INDEX_TYPE: NORMAL
              TABLE_OWNER: SYS
              TABLE_NAME: TEST
              TABLE_TYPE: TABLE
              UNIQUENESS: UNIQUE
              COMPRESSION: ENABLED
            PREFIX_LENGTH: NULL
          TABLESPACE_NAME: NULL
                INI_TRANS: NULL
                MAX_TRANS: NULL
          INITIAL_EXTENT: NULL
              NEXT_EXTENT: NULL
              MIN_EXTENTS: NULL
              MAX_EXTENTS: NULL
            PCT_INCREASE: NULL
            PCT_THRESHOLD: NULL
          INCLUDE_COLUMN: NULL
                FREELISTS: NULL
          FREELIST_GROUPS: NULL
                PCT_FREE: NULL
                  LOGGING: NULL
                  BLEVEL: NULL
              LEAF_BLOCKS: NULL
            DISTINCT_KEYS: NULL
  AVG_LEAF_BLOCKS_PER_KEY: NULL
  AVG_DATA_BLOCKS_PER_KEY: NULL
        CLUSTERING_FACTOR: NULL
                  STATUS: VALID
                NUM_ROWS: NULL
              SAMPLE_SIZE: NULL
            LAST_ANALYZED: NULL
                  DEGREE: 1
                INSTANCES: NULL
              PARTITIONED: NO
                TEMPORARY: NULL
                GENERATED: NULL
                SECONDARY: NULL
              BUFFER_POOL: NULL
              FLASH_CACHE: NULL
        CELL_FLASH_CACHE: NULL
              USER_STATS: NULL
                DURATION: NULL
        PCT_DIRECT_ACCESS: NULL
              ITYP_OWNER: NULL
                ITYP_NAME: NULL
              PARAMETERS: NULL
            GLOBAL_STATS: NULL
            DOMIDX_STATUS: NULL
          DOMIDX_OPSTATUS: NULL
          FUNCIDX_STATUS: NULL
              JOIN_INDEX: NO
  IOT_REDUNDANT_PKEY_ELIM: NULL
                  DROPPED: NO
              VISIBILITY: VISIBLE
        DOMIDX_MANAGEMENT: NULL
          SEGMENT_CREATED: NULL
        ORPHANED_ENTRIES: NULL
                INDEXING: NULL
                    AUTO: NULL
  *************************** 2. row ***************************
                    OWNER: SYS
              INDEX_NAME: TEST_INDEX
              INDEX_TYPE: NORMAL
              TABLE_OWNER: SYS
              TABLE_NAME: TEST
              TABLE_TYPE: TABLE
              UNIQUENESS: NONUNIQUE
              COMPRESSION: ENABLED
            PREFIX_LENGTH: NULL
          TABLESPACE_NAME: NULL
                INI_TRANS: NULL
                MAX_TRANS: NULL
          INITIAL_EXTENT: NULL
              NEXT_EXTENT: NULL
              MIN_EXTENTS: NULL
              MAX_EXTENTS: NULL
            PCT_INCREASE: NULL
            PCT_THRESHOLD: NULL
          INCLUDE_COLUMN: NULL
                FREELISTS: NULL
          FREELIST_GROUPS: NULL
                PCT_FREE: NULL
                  LOGGING: NULL
                  BLEVEL: NULL
              LEAF_BLOCKS: NULL
            DISTINCT_KEYS: NULL
  AVG_LEAF_BLOCKS_PER_KEY: NULL
  AVG_DATA_BLOCKS_PER_KEY: NULL
        CLUSTERING_FACTOR: NULL
                  STATUS: VALID
                NUM_ROWS: NULL
              SAMPLE_SIZE: NULL
            LAST_ANALYZED: NULL
                  DEGREE: 1
                INSTANCES: NULL
              PARTITIONED: NO
                TEMPORARY: NULL
                GENERATED: NULL
                SECONDARY: NULL
              BUFFER_POOL: NULL
              FLASH_CACHE: NULL
        CELL_FLASH_CACHE: NULL
              USER_STATS: NULL
                DURATION: NULL
        PCT_DIRECT_ACCESS: NULL
              ITYP_OWNER: NULL
                ITYP_NAME: NULL
              PARAMETERS: NULL
            GLOBAL_STATS: NULL
            DOMIDX_STATUS: NULL
          DOMIDX_OPSTATUS: NULL
          FUNCIDX_STATUS: NULL
              JOIN_INDEX: NO
  IOT_REDUNDANT_PKEY_ELIM: NULL
                  DROPPED: NO
              VISIBILITY: VISIBLE
        DOMIDX_MANAGEMENT: NULL
          SEGMENT_CREATED: NULL
        ORPHANED_ENTRIES: NULL
                INDEXING: NULL
                    AUTO: NULL
  2 rows in set
  

• Use the USER_IND_COLUMNS statement to view details about the indexes of a table.

  obclient> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST'\G
  *************************** 1. row ***************************
          INDEX_NAME: TEST_OBPK_1664353339491130
          TABLE_NAME: TEST
        COLUMN_NAME: C1
    COLUMN_POSITION: 1
      COLUMN_LENGTH: 22
        CHAR_LENGTH: 0
            DESCEND: ASC
  COLLATED_COLUMN_ID: NULL
  *************************** 2. row ***************************
          INDEX_NAME: TEST_INDEX
          TABLE_NAME: TEST
        COLUMN_NAME: C1
    COLUMN_POSITION: 1
      COLUMN_LENGTH: 22
        CHAR_LENGTH: 0
            DESCEND: ASC
  COLLATED_COLUMN_ID: NULL
  *************************** 3. row ***************************
          INDEX_NAME: TEST_INDEX
          TABLE_NAME: TEST
        COLUMN_NAME: C2
    COLUMN_POSITION: 2
      COLUMN_LENGTH: 10
        CHAR_LENGTH: 10
            DESCEND: ASC
  COLLATED_COLUMN_ID: NULL
  3 rows in set
  

Drop an index

You can execute the DROP INDEX statement to drop a table index.

For example, drop the index named test_index.

obclient> DROP INDEX test_index;
Query OK, 0 rows affected

For more information about the DROP INDEX statement, see DROP INDEX.

Insert data

You can execute the INSERT statement to add one or more records to a table.

Here are some examples:

• Use the CREATE TABLE statement to create a table named t1 and insert a row into the t1 table.

  obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT);
  Query OK, 0 rows affected
  
  obclient> SELECT * FROM t1;
  Empty set
  
  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 data into the result set of a subquery.

  obclient> INSERT INTO (SELECT * FROM t1) VALUES(2,2);
  Query OK, 1 row affected
  
  obclient> SELECT * FROM t1;
  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  +----+------+
  2 rows in set
  

• Insert data by using the RETURNING clause.

  obclient> INSERT INTO t1 VALUES(3,3) RETURNING c1;
  +----+
  | C1 |
  +----+
  |  3 |
  +----+
  1 row in set
  
  obclient> SELECT * FROM t1;
  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  |  3 |    3 |
  +----+------+
  3 rows in set
  

For more information about the INSERT statement, see INSERT.

Delete data

You can execute the DELETE statement to delete data.

For example, delete the row where c1 = 2 from the t1 table.

obclient> DELETE FROM t1 WHERE c1 = 2;
Query OK, 1 row affected

obclient> SELECT * FROM t1;
+----+------+
| C1 | C2   |
+----+------+
|  1 |    1 |
|  3 |    3 |
+----+------+
2 rows in set

For more information about the DELETE statement, see DELETE.

Update data

You can execute the UPDATE statement to modify the field values in a table.

Here are some examples:

• 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 |
  |  3 |    3 |
  +----+------+
  2 rows in set
  

• For the row where v.c1 = 3 in the result set of a subquery, change the value in the c2 column to 300.

  obclient> UPDATE (SELECT * FROM t1) v SET v.c2 = 300 WHERE v.c1 = 3;
  Query OK, 1 row affected
  Rows matched: 1  Changed: 1  Warnings: 0
  
  obclient> SELECT * FROM t1;
  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |  100 |
  |  3 |  300 |
  +----+------+
  2 rows in set
  

For more information about the UPDATE statement, see UPDATE.

Query data

You can execute the SELECT statement to query data from a table.

Here are some examples:

• Use the CREATE TABLE statement to create a table named t2 and query the data in the name field from the t2 table.

  obclient> CREATE TABLE t2 (id INT, name VARCHAR(50), num INT);
  Query OK, 0 rows affected
  
  obclient> INSERT INTO t2 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
  Query OK, 3 rows affected
  Records: 3  Duplicates: 0  Warnings: 0
  
  obclient> SELECT * FROM t2;
  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    2 | b    |  200 |
  |    3 | a    |   50 |
  +------+------+------+
  3 rows in set
  
  obclient> SELECT name FROM t2;
  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  | a    |
  +------+
  3 rows in set
  

• Deduplicate the query results of the name field.

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

• Return the values of the id, name, and num columns based on the filter condition name = 'a' from table t2.

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

For more information about the SELECT statement, see SELECT.

Commit a transaction

You can execute the COMMIT statement to commit a transaction.

Before you commit the transaction, your modifications are not persisted and take effect only for the current session. You can use the ROLLBACK statement to revoke the modifications.

After you commit the transaction, your modifications take effect for all database sessions. After your modifications are persisted, you cannot roll them back with a ROLLBACK statement.

For example, you can execute the CREATE TABLE statement to create a table named t_insert and the COMMIT statement to commit a transaction.

obclient> CREATE TABLE t_insert(
     id number NOT NULL PRIMARY KEY,
     name varchar(10) NOT NULL,
     value number NOT NULL,
     gmt_create date NOT NULL DEFAULT sysdate
 );
Query OK, 0 rows affected

obclient> INSERT INTO t_insert(id, name, value, gmt_create) VALUES(1,'CN',10001, sysdate),(2,'US',10002, sysdate),(3,'EN',10003, sysdate);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
+----+------+-------+------------+
3 rows in set

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

obclient> COMMIT;
Query OK, 0 rows affected

obclient> exit;
Bye

obclient> obclient -h127.0.0.1 -us**@oracle -P2881 -p******

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
|  4 | JP   | 10004 | 22-AUG-22  |
+----+------+-------+------------+
4 rows in set

For more information about transaction control statements, see Overview of transaction management.

Roll back a transaction

You can execute the ROLLBACK statement to roll back a transaction.

In a database, a transaction rollback is used to undo any modifications made during a transaction. You can undo all modifications made within an uncommitted transaction or roll back a transaction to a specific savepoint. To roll back to a specific savepoint, both the ROLLBACK and TO SAVEPOINT statements must be used.

• If you undo all modifications made within an uncommitted transaction, note that:

  • 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, note that:

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

For example, you can execute the following statements to undo all the modifications of a transaction.

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 29-SEP-22  |
|  2 | US   | 10002 | 29-SEP-22  |
|  3 | EN   | 10003 | 29-SEP-22  |
|  4 | JP   | 10004 | 29-SEP-22  |
+----+------+-------+------------+
4 rows in set

obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 22-AUG-22  |
|  2 | US   | 10002 | 22-AUG-22  |
|  3 | EN   | 10003 | 22-AUG-22  |
|  4 | JP   | 10004 | 22-AUG-22  |
|  5 | FR   | 10005 | 22-AUG-22  |
|  6 | RU   | 10006 | 22-AUG-22  |
+----+------+-------+------------+
6 rows in set

obclient> ROLLBACK;
Query OK, 0 rows affected

obclient> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
|  1 | CN   | 10001 | 29-SEP-22  |
|  2 | US   | 10002 | 29-SEP-22  |
|  3 | EN   | 10003 | 29-SEP-22  |
|  4 | JP   | 10004 | 29-SEP-22  |
+----+------+-------+------------+
3 rows in set

For more information about transaction control statements, see Overview of transaction management.