This topic describes the basic SQL operations in OceanBase Database in Oracle mode.
Table-level operations
This topic describes how to create, view, modify, and drop database tables.
Create a table
Use the CREATE TABLE statement to create a table in the database.
Example: Create a table named test.
obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected
Modify a table
Use the ALTER TABLE statement to modify the structure of an existing table, including modifying the table name and table attributes, adding columns, modifying columns and attributes, and dropping columns.
Example 1: Modify the type of the c2 field 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
Example 2: 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
Drop a table
Use the DROP TABLE statement to drop a table.
Example: Drop the test table.
obclient> DROP TABLE test;
Query OK, 0 rows affected
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 an index
Use the CREATE INDEX statement to create a table index.
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
Query indexes
Query all indexes on a table by using the ALL_INDEXES view. Sample code:
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 (0.02 sec)
Use the USER_IND_COLUMNS statement to view details about the indexes of a table. Sample code:
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
Use the DROP INDEX statement to drop a table index.
Example: Drop the index named test_index.
obclient> DROP INDEX test_index;
Query OK, 0 rows affected
Insert data
Use the INSERT statement to add one or more records to a table.
Example 1: 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
Example 2: Insert data into 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
Example 3: 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
Delete data
Use the DELETE statement to delete data.
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
Update data
Use the UPDATE statement to modify the field values in a table.
Example 1: For the row where t1.c1 = 1 in the t1 table, set its value in the c2 column 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
Example 2: For the row where v.c1 = 1, run a subquery and set its 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
Querying data
Use the SELECT statement to query data from a table.
Example 1: Use the CREATE TABLE statement to create a table named t2. 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
Example 2: Deduplicate the query results of the name field.
obclient> SELECT DISTINCT name FROM t2;
+------+
| NAME |
+------+
| a |
| b |
+------+
2 rows in set
Example 3: Return the values of the corresponding id, name, and num fields based on the filter condition name = 'a' from the t2 table.
obclient> SELECT id, name, num FROM t2 WHERE name = 'a';
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 3 | a | 50 |
+------+------+------+
2 rows in set
Commit a transaction
Use the COMMIT statement to commit a transaction.
Before you commit the transaction, your changes are not persisted and take effect only for the current session. You can use the ROLLBACK statement to revoke the changes.
After you commit the transaction, your changes take effect for all database sessions. After your changes are persisted, you cannot roll them back with a ROLLBACK statement.
Example: Use the CREATE TABLE statement to create a table named t_insert. Use 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> 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
Roll back a transaction
Use the ROLLBACK statement to roll back a transaction.
A transaction rollback reverses all changes made in the transaction. You can roll back an entire uncommitted transaction or roll back a transaction to any savepoints of the transaction. To roll back to a specific savepoint, you must use both the ROLLBACK and TO SAVEPOINT statements.
Notes:
If you roll back an entire transaction:
- 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:
- 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.
Example: Roll back all the changes 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