Basic SQL operations (Oracle mode)|V4.1.0| docs|Distributed Database

Basic SQL operations (Oracle mode)

Last Updated：2023-08-28 09:28:41 Updated

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

Applicability

This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL 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.