Basic SQL operations (Oracle mode)

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

Table operations

This section provides 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.

     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 or 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 to sort the values of one or more columns in a 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 detailed information about the indexes of a table 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 from 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 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. View 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. View the data in the table again to confirm that the row of data was successfully inserted.

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

     The query 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, view the data in the table to confirm that the data was successfully 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 with 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. View the data in the table again to confirm that the row of data was successfully 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 where C1=2 from the table T1.

1. View the data in the table T1 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 where C1=2 from the table T1.

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

3. View the data in the table T1 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 where T1.C1=1 in the table T1 to 100.

  1. View the data in the table T1 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 where T1.C1=1 in the table T1 to 100.

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

  3. View the data in the table T1 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 where V.C1=3 in the subquery to 300.

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

  After the statement is executed, view the data in the table T1 after 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

Use 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 data from the NAME column in the T2 table.

  1. Create a table named t2.

     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 data from the T2 table to confirm that the data is inserted.

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

     The returned result is as follows:

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

  4. Query data from the NAME column in the T2 table.

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

     The returned result is as follows:

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

• Use the SELECT DISTINCT statement to query data from the NAME column in the T2 table.

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

  The returned result is as follows:

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

• Query data from the T2 table based on the NAME = 'a' condition. The query result includes the ID, NAME, and NUM columns.

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

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

Use the COMMIT statement to commit a transaction.

Before you commit a transaction, your modifications are visible only to the current session and not 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, and you cannot use the ROLLBACK statement to undo them.

Here is an example: Create a table named T_INSERT by using the CREATE TABLE statement. Then, commit the transaction by using the COMMIT statement.

1. Create a table named 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 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 data from the T_INSERT table to confirm that the data is inserted.

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

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

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

7. Log in to the database again.

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

8. Query data from the T_INSERT table. You can see that the modifications to the T_INSERT table are persisted.

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

   The returned 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 use the ROLLBACK statement to roll back a transaction.

Rolling back a transaction means undoing all the changes made by the transaction. You can roll back the entire uncommitted transaction or roll back to a specific savepoint within the transaction. To roll back to a savepoint, you must use the ROLLBACK statement in combination with the TO SAVEPOINT statement.

Here are the details:

• If you roll back the entire transaction, then:

  • 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 specific savepoint, then:

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

Example: Roll back all modifications made by the transaction.

1. View the data in the T_INSERT table before the modifications.

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

   The returned 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. View the data in the T_INSERT table after the modifications to confirm that the data was inserted successfully.

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

   The returned 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. After the roll back, view the data in the T_INSERT table again. The inserted data has been rolled back.

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

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