Basic SQL operations (Oracle-compatible mode)

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

Table operations

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

Create a table

Use the CREATE TABLE statement to create a new table in the 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 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 its attributes, adding columns, modifying columns and their attributes, and deleting columns.

Example:

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

  1. View the column definitions of the TEST table.

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

     The returned 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. Change the data type of the C2 column in the TEST table to CHAR.

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

  3. View the column definitions of the TEST table again to confirm the modification was successful.

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

     The returned 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 delete columns in the TEST table.

  1. View the column definitions of the TEST table.

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

     The returned 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 a column named C3 to the TEST table.

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

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

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

     The returned 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. Delete the C3 column from the TEST table.

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

  5. View the column definitions of the TEST table again to confirm that the C3 column was deleted.

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

     The returned 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 ALTER TABLE statement, see ALTER TABLE.

Delete a table

Use the DROP TABLE statement to delete a table.

Example: Delete the TEST table.

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

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

Index operations

An index is a structure created on a table that sorts the values of one or more columns in the database table. Its main purpose is to improve query speed and reduce 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 definitions of the TEST table.

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

   The returned 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 the C1 and C2 columns of the TEST table.

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

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

View indexes

• View all indexes of a table by querying 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 returned 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 querying the USER_IND_COLUMNS view.

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

  The returned 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
  

Delete an index

Use the DROP INDEX statement to delete an index on a table.

Example: Delete the TEST_INDEX index.

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

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

Insert data

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

Example:

• Create a table named T1 by using the CREATE TABLE statement and insert a row of data into the table.

  1. Create a table named t1.

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

  2. Query the data in the table.

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

     The query result is empty, indicating that the table contains no data.

  3. Insert a row of data into the table T1.

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

  4. Query the data in the table again to confirm that the row of data is inserted.

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

     The query result is as follows:

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

• Insert data into a subquery.

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

  After the statement is executed, query the data in the table to confirm that the data is inserted.

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

  The query result is as follows:

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

• Insert data into a subquery that contains the RETURNING clause.

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

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

     The query result is as follows:

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

  2. Query the data in the table again to confirm that the row of data is inserted.

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

     The query result is as follows:

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

For more information about the INSERT statement, see INSERT.

Delete data

Use the DELETE statement to delete data.

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

1. Query the data in the T1 table before the data is deleted.

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

   The query result is as follows:

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

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

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

3. Query the data in the T1 table after the data is deleted.

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

   The query result is as follows:

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

For more information about the DELETE statement, see DELETE.

Update data

Use the UPDATE statement to modify the values of fields in a table.

Example:

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

  1. Query the data in the T1 table before the data is updated.

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

     The query result is as follows:

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

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

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

  3. Query the data in the T1 table after the data is updated.

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

     The query result is as follows:

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

• Directly operate on a subquery to modify the value of the C2 column in the row in the subquery where V.C1=3 to 300.

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

  After the statement is executed, query the data in the T1 table to confirm that the data is updated.

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

  The query result is as follows:

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

• Create a table named T2 by using the CREATE TABLE statement. Then, query the NAME column in the T2 table.

  1. Create the t2 table.

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

  2. Insert multiple rows of data into the T2 table.

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

  3. Query the T2 table to verify that the data has been inserted.

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

     The query result is as follows:

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

  4. Query the NAME column in the T2 table.

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

     The query result is as follows:

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

• Remove duplicate values from the NAME column in the query result.

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

  The query result is as follows:

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

• Query the ID, NAME, and NUM columns in the T2 table based on the NAME = 'a' condition.

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

  The query result is as follows:

  +------+------+------+
  | 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 a transaction, your modifications are only visible to the current session and not to other database sessions. Your modifications are not persisted and can be rolled back by using the ROLLBACK statement.

After you commit a transaction, your modifications are visible to all database sessions. Your modifications are persisted and cannot be rolled back by using the ROLLBACK statement.

Here is an example:

1. Create a table named T_INSERT by using the CREATE TABLE statement.

   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 of data into the T_INSERT table.

   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. Query the T_INSERT table to verify that the data has been inserted.

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

   The query 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 a row of data into the T_INSERT table.

   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. Log out of the database.

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

7. Log in to the database again.

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

8. Query the T_INSERT table to verify that the modifications are persisted.

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

   The query 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

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

Rolling back a transaction means undoing all modifications made by the transaction. You can roll back the entire uncommitted transaction or roll back to any savepoint within the transaction. To roll back to a specific savepoint, you must use the ROLLBACK and TO SAVEPOINT statements together.

Here are the details:

• If you roll back the entire transaction:

  • The transaction will end.
  • All modifications will be discarded.
  • All savepoints will be cleared.
  • All locks held by the transaction will be released.

• If you roll back to a specific savepoint:

  • The transaction will not end.
  • Modifications before the savepoint are retained, and modifications after the savepoint are discarded.
  • Savepoints after the specified savepoint will be cleared (excluding the savepoint itself).
  • All locks held by the transaction after the specified savepoint will be released.

Here is an example:

1. Query the T_INSERT table to view the data before the modifications.

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

   The query 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 of data into the T_INSERT table.

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

3. Query the T_INSERT table to verify that the data has been inserted.

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

   The query 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. Query the T_INSERT table again to verify that the modifications have been rolled back.

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

   The query 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.