Basic SQL operations (Oracle-compatible mode)

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

Table operations

This section describes the syntax and examples for creating, viewing, modifying, and dropping tables in a database.

Create a table

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

Example: Create a table named TEST.

obclient(SYS@oracletenant)[SYS]> CREATE TABLE TEST (C1 INT PRIMARY KEY, C2 VARCHAR(3));

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

Modify a table

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

Example:

• Modify the field type of column C2 in the TEST table.

  1. View the column definition of the TEST table.

     obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
     

     The result is as follows:

     +-------+-------------+------+-----+---------+-------+
     | FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
     +-------+-------------+------+-----+---------+-------+
     | C1    | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
     | C2    | VARCHAR2(3) | YES  | NULL| NULL    | NULL  |
     +-------+-------------+------+-----+---------+-------+
     2 rows in set
     

  2. Modify the field type of C2 in the TEST table to CHAR.

     obclient(SYS@oracletenant)[SYS]> ALTER TABLE TEST MODIFY C2 CHAR(10);
     

  3. View the column definition of the TEST table again to confirm the modification.

     obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
     

     The result is as follows:

     +-------+------------+------+-----+---------+-------+
     | 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 and drop columns in the TEST table.

  1. View the current column definition of the TEST table.

     obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
     

     The result is as follows:

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

  2. Add column C3 to the TEST table.

     obclient(SYS@oracletenant)[SYS]> ALTER TABLE TEST ADD C3 int;
     

  3. View the column definition of the TEST table again to confirm that C3 was added.

     obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
     

     The result is as follows:

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

  4. Drop column C3 from the TEST table.

     obclient(SYS@oracletenant)[SYS]> ALTER TABLE TEST DROP COLUMN C3;
     

  5. View the column definition of the TEST table again to confirm that C3 was dropped.

     obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
     

     The result is as follows:

     +-------+------------+------+-----+---------+-------+
     | 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 syntax of the ALTER TABLE statement, see ALTER TABLE.

Drop a table

Use the DROP TABLE statement to drop a table.

Example: Drop the TEST table.

obclient(SYS@oracletenant)[SYS]> DROP TABLE TEST;

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

Index operations

An index is a structure created on a table to sort the values in one or more columns of the table. It improves query performance and reduces the performance overhead of the database system.

Create an index

Use the CREATE INDEX statement to create an index on a table.

Example: Create an index on the TEST table.

1. View the column definition of the TEST table.

   obclient(SYS@oracletenant)[SYS]> DESCRIBE TEST;
   

   The result is as follows:

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

2. Create a composite index named TEST_INDEX on columns C1 and C2 of the TEST table.

   obclient(SYS@oracletenant)[SYS]> CREATE INDEX TEST_INDEX ON TEST (C1, C2);
   

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

View indexes

• View all indexes of a table by using the ALL_INDEXES view.

  obclient(SYS@oracletenant)[SYS]> SELECT OWNER,INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME FROM ALL_INDEXES WHERE table_name='TEST';
  

  The result is as follows:

  +-------+----------------------------+------------+-------------+------------+
  | OWNER | INDEX_NAME                 | INDEX_TYPE | TABLE_OWNER | TABLE_NAME |
  +-------+----------------------------+------------+-------------+------------+
  | SYS   | TEST_OBPK_1762742804587195 | NORMAL     | SYS         | TEST       |
  | SYS   | TEST_INDEX                 | NORMAL     | SYS         | TEST       |
  +-------+----------------------------+------------+-------------+------------+
  2 rows in set
  

• View the details of table indexes by using USER_IND_COLUMNS.

  obclient(SYS@oracletenant)[SYS]> SELECT * FROM USER_IND_COLUMNS WHERE table_name='TEST'\G
  

  The result is as follows:

  *************************** 1. row ***************************
          INDEX_NAME: TEST_OBPK_1762742804587195
          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: 3
         CHAR_LENGTH: 3
             DESCEND: ASC
  COLLATED_COLUMN_ID: NULL
  3 rows in set
  

Drop an index

Use the DROP INDEX statement to drop an index from a table.

Example: Drop the index TEST_INDEX.

obclient(SYS@oracletenant)[SYS]> DROP INDEX TEST_INDEX;

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

Insert data

Use the INSERT statement to add one or more records to a table.

Example:

• Create table T1 using CREATE TABLE and insert a row into table T1.

  1. Create table T1.

     obclient(SYS@oracletenant)[SYS]> CREATE TABLE T1(C1 INT PRIMARY KEY, C2 INT);
     

  2. View the data in the table.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
     

     The query result is empty; the table has no data.

  3. Insert a row into table T1.

     obclient(SYS@oracletenant)[SYS]> INSERT INTO T1 VALUES(1,1);
     

  4. View the data in the table again to confirm the insertion.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
     

     The result is as follows:

     +----+------+
     | C1 | C2   |
     +----+------+
     |  1 |    1 |
     +----+------+
     1 row in set
     

• Insert data directly into a subquery.

  obclient(SYS@oracletenant)[SYS]> INSERT INTO (SELECT * FROM T1) VALUES(2,2);
  

  After the statement is executed successfully, view the data in the table to confirm the insertion.

  obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
  

  The result is as follows:

  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |    1 |
  |  2 |    2 |
  +----+------+
  2 rows in set
  

• Insert data with the RETURNING clause.

  1. Insert a row into table T1 and return the value of the C1 column of the inserted row.

     obclient(SYS@oracletenant)[SYS]> INSERT INTO T1 VALUES(3,3) RETURNING C1;
     

     The result is as follows:

     +----+
     | C1 |
     +----+
     |  3 |
     +----+
     1 row in set
     

  2. View the data in the table again to confirm the insertion.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
     

     The result is as follows:

     +----+------+
     | C1 | C2   |
     +----+------+
     |  1 |    1 |
     |  2 |    2 |
     |  3 |    3 |
     +----+------+
     3 rows in set
     

For more information about the syntax of the INSERT statement, see INSERT.

Delete data

Use the DELETE statement to delete data.

Example: Delete the row where C1=2 from table T1.

1. View the data in table T1 before deletion.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
   

   The result is as follows:

   +----+------+
   | C1 | C2   |
   +----+------+
   |  1 |    1 |
   |  2 |    2 |
   |  3 |    3 |
   +----+------+
   3 rows in set
   

2. Delete the row where C1=2 from table T1.

   obclient(SYS@oracletenant)[SYS]> DELETE FROM T1 WHERE C1 = 2;
   

3. View the data in table T1 after deletion.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
   

   The result is as follows:

   +----+------+
   | C1 | C2   |
   +----+------+
   |  1 |    1 |
   |  3 |    3 |
   +----+------+
   2 rows in set
   

For more information about the syntax of the DELETE statement, see DELETE.

Update data

Use the UPDATE statement to modify the field values in a table.

Example:

• Update the value of the C2 column to 100 for the row where T1.C1=1 in table T1.

  1. View the data in table T1 before update.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
     

     The result is as follows:

     +----+------+
     | C1 | C2   |
     +----+------+
     |  1 |    1 |
     |  3 |    3 |
     +----+------+
     2 rows in set
     

  2. Update the value of the C2 column to 100 for the row where T1.C1=1 in table T1.

     obclient(SYS@oracletenant)[SYS]> UPDATE T1 SET T1.C2 = 100 WHERE T1.C1 = 1;
     

  3. View the data in table T1 after update.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
     

     The result is as follows:

     +----+------+
     | C1 | C2   |
     +----+------+
     |  1 |  100 |
     |  3 |    3 |
     +----+------+
     2 rows in set
     

• Update the subquery directly: update the value of the C2 column to 300 for the row where V.C1=3 in the subquery.

  obclient(SYS@oracletenant)[SYS]> UPDATE (SELECT * FROM T1) V SET V.C2 = 300 WHERE V.C1 = 3;
  

  After the statement is executed successfully, view the data in table T1 after update.

  obclient(SYS@oracletenant)[SYS]> SELECT * FROM T1;
  

  The result is as follows:

  +----+------+
  | C1 | C2   |
  +----+------+
  |  1 |  100 |
  |  3 |  300 |
  +----+------+
  2 rows in set
  

For more information about the syntax of the UPDATE statement, see UPDATE.

Query data

Use the SELECT statement to query data from a table.

Example:

• Create table T2 using CREATE TABLE and read the NAME data from table T2.

  1. Create table T2.

     obclient(SYS@oracletenant)[SYS]> CREATE TABLE T2 (ID INT, NAME VARCHAR(50), NUM INT);
     

  2. Insert multiple rows into table T2.

     obclient(SYS@oracletenant)[SYS]> INSERT INTO T2 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
     

  3. View the data in table T2 to confirm the insertion.

     obclient(SYS@oracletenant)[SYS]> SELECT * FROM T2;
     

     The result is as follows:

     +------+------+------+
     | ID   | NAME | NUM  |
     +------+------+------+
     |    1 | a    |  100 |
     |    2 | b    |  200 |
     |    3 | a    |   50 |
     +------+------+------+
     3 rows in set
     

  4. Read the NAME data from table T2.

     obclient(SYS@oracletenant)[SYS]> SELECT NAME FROM T2;
     

     The result is as follows:

     +------+
     | NAME |
     +------+
     | a    |
     | b    |
     | a    |
     +------+
     3 rows in set
     

• Deduplicate the NAME in the query result.

  obclient(SYS@oracletenant)[SYS]> SELECT DISTINCT NAME FROM T2;
  

  The result is as follows:

  +------+
  | NAME |
  +------+
  | a    |
  | b    |
  +------+
  2 rows in set
  

• Output the corresponding ID, NAME, and NUM from table T2 based on the filter condition NAME = 'a'.

  obclient(SYS@oracletenant)[SYS]> SELECT ID, NAME, NUM FROM T2 WHERE NAME = 'a';
  

  The result is as follows:

  +------+------+------+
  | ID   | NAME | NUM  |
  +------+------+------+
  |    1 | a    |  100 |
  |    3 | a    |   50 |
  +------+------+------+
  2 rows in set
  

For more information about the syntax of the SELECT statement, see SELECT.

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, and you can use the ROLLBACK statement to undo them.

After you commit a transaction, your modifications are visible to all database sessions. Your modifications are persisted successfully, and you cannot use the ROLLBACK statement to undo them.

Example: Create table T_INSERT using CREATE TABLE and use the COMMIT statement to commit a transaction.

1. Create table T_INSERT.

   obclient(SYS@oracletenant)[SYS]> 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
   );
   

2. Insert multiple rows into table T_INSERT.

   obclient(SYS@oracletenant)[SYS]> INSERT INTO T_INSERT(ID, NAME, VALUE, GMT_CREATE) VALUES(1,'CN',10001, sysdate),(2,'US',10002, sysdate),(3,'EN',10003, sysdate);
   

3. View the data in table T_INSERT after modification to confirm the insertion.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T_INSERT;
   

   The result is as follows:

   +------+------+-------+------------+
   | ID   | NAME | VALUE | GMT_CREATE |
   +------+------+-------+------------+
   |    1 | CN   | 10001 | 10-NOV-25  |
   |    2 | US   | 10002 | 10-NOV-25  |
   |    3 | EN   | 10003 | 10-NOV-25  |
   +------+------+-------+------------+
   3 rows in set
   

4. Insert another row into table T_INSERT.

   obclient(SYS@oracletenant)[SYS]> INSERT INTO T_INSERT(ID, NAME, VALUE) VALUES(4,'JP',10004);
   

5. Commit the transaction.

   obclient(SYS@oracletenant)[SYS]> COMMIT;
   

6. Exit the session.

   obclient(SYS@oracletenant)[SYS]> exit;
   

7. Log in to the database again.

   obclient -h127.0.0.1 -us**@oracletenant -P2881 -p******
   

8. View the data in table T_INSERT. The modifications to the data in table T_INSERT have been persisted successfully.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T_INSERT;
   

   The result is as follows:

   +------+------+-------+------------+
   | ID   | NAME | VALUE | GMT_CREATE |
   +------+------+-------+------------+
   |    1 | CN   | 10001 | 10-NOV-25  |
   |    2 | US   | 10002 | 10-NOV-25  |
   |    3 | EN   | 10003 | 10-NOV-25  |
   |    4 | JP   | 10004 | 10-NOV-25  |
   +------+------+-------+------------+
   4 rows in set
   

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

Roll back a transaction

Use the ROLLBACK statement to roll back a transaction.

A transaction rollback means undoing all modifications made in the transaction. You can roll back the entire uncommitted transaction or roll back to any savepoint in the transaction. To roll back to a savepoint, you must use both the ROLLBACK and TO SAVEPOINT statements.

Note:

• If you roll back the entire transaction:

  • The transaction ends.
  • All modifications are discarded.
  • All savepoints are cleared.
  • All locks held by the transaction are released.

• If you roll back to a savepoint:

  • The transaction does not end.
  • Modifications made before the savepoint are retained; modifications made after the savepoint are discarded.
  • Savepoints after the specified savepoint are cleared (excluding the savepoint itself).
  • All locks held by the transaction after the savepoint are released.

Example: Roll back all modifications of a transaction.

1. View the data in table T_INSERT before modification.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T_INSERT;
   

   The result is as follows:

   +------+------+-------+------------+
   | ID   | NAME | VALUE | GMT_CREATE |
   +------+------+-------+------------+
   |    1 | CN   | 10001 | 10-NOV-25  |
   |    2 | US   | 10002 | 10-NOV-25  |
   |    3 | EN   | 10003 | 10-NOV-25  |
   |    4 | JP   | 10004 | 10-NOV-25  |
   +------+------+-------+------------+
   4 rows in set
   

2. Insert two rows into table T_INSERT.

   obclient(SYS@oracletenant)[SYS]> INSERT INTO T_INSERT(ID, NAME, VALUE) VALUES(5,'FR',10005),(6,'RU',10006);
   

3. View the data in table T_INSERT after modification to confirm the insertion.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T_INSERT;
   

   The result is as follows:

   +------+------+-------+------------+
   | ID   | NAME | VALUE | GMT_CREATE |
   +------+------+-------+------------+
   |    1 | CN   | 10001 | 10-NOV-25  |
   |    2 | US   | 10002 | 10-NOV-25  |
   |    3 | EN   | 10003 | 10-NOV-25  |
   |    4 | JP   | 10004 | 10-NOV-25  |
   |    5 | FR   | 10005 | 10-NOV-25  |
   |    6 | RU   | 10006 | 10-NOV-25  |
   +------+------+-------+------------+
   6 rows in set
   

4. Roll back the transaction.

   obclient(SYS@oracletenant)[SYS]> ROLLBACK;
   

5. View the data in table T_INSERT again after the rollback. The inserted data has been rolled back.

   obclient(SYS@oracletenant)[SYS]> SELECT * FROM T_INSERT;
   

   The result is as follows:

   +------+------+-------+------------+
   | ID   | NAME | VALUE | GMT_CREATE |
   +------+------+-------+------------+
   |    1 | CN   | 10001 | 10-NOV-25  |
   |    2 | US   | 10002 | 10-NOV-25  |
   |    3 | EN   | 10003 | 10-NOV-25  |
   |    4 | JP   | 10004 | 10-NOV-25  |
   +------+------+-------+------------+
   4 rows in set
   

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