This topic describes the basic SQL operations in OceanBase Database in MySQL mode.
Create a database
Use the CREATE DATABASE statement to create a database.
Example: Create a database named db1, set the character set to UTF8, and specify the read and write attributes.
obclient> CREATE DATABASE db1 DEFAULT CHARACTER SET UTF8 READ WRITE;
Query OK, 1 row affected
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 in the db1 database.
obclient> USE db1;
Database changed
obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected
View a table
Use the SHOW CREATE TABLE statement to view table creation statements.
Example: View the statement for creating the test table.
obclient> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
Table: test
Create Table: CREATE TABLE `test` (
`c1` int(11) NOT NULL,
`c2` varchar(3) DEFAULT NULL,
PRIMARY KEY (`c1`)
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set
Use the SHOW TABLES statement to view all tables in a specified database.
Example: View all tables in the db1 database.
obclient> SHOW TABLES FROM db1;
+---------------+
| Tables_in_db1 |
+---------------+
| test |
+---------------+
1 row in set
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: Rename the c2 field in the test table as c3 and modify its field type.
obclient> DESCRIBE test;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set
obclient> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c3 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set
Example 2: Add a column to and drop a column from the test table.
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
obclient> ALTER TABLE test ADD c3 int;
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
| c3 | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set
obclient> ALTER TABLE test DROP c3;
Query OK, 0 rows affected
obclient> DESCRIBE test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c1 | int(11) | NO | PRI | NULL | |
| c2 | varchar(3) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set
### Drop a table
Use the `DROP TABLE` statement to drop a table.
Example: Drop the `test` table.
```sql
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 | int(11) | NO | PRI | NULL | |
| c3 | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
2 rows in set
obclient> CREATE INDEX test_index ON test (c1, c3);
Query OK, 0 rows affected
Query indexes
You can execute the SHOW INDEX statement to query the indexes of a table.
Example: Query the indexes of the test table.
obclient> SHOW INDEX FROM test\G
*************************** 1. row ***************************
Table: test
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: c1
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment: available
Index_comment:
Visible: YES
*************************** 2. row ***************************
Table: test
Non_unique: 1
Key_name: test_index
Seq_in_index: 1
Column_name: c1
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment: available
Index_comment:
Visible: YES
*************************** 3. row ***************************
Table: test
Non_unique: 1
Key_name: test_index
Seq_in_index: 2
Column_name: c3
Collation: A
Cardinality: NULL
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment: available
Index_comment:
Visible: YES
3 rows in set
Drop an index
Use the DROP INDEX statement to drop a table index.
Example: Drop an index from the test table.
obclient> DROP INDEX test_index ON test;
Query OK, 0 rows affected
Insert data
Use the INSERT statement to insert data into an existing table.
Example 1: Create a table named t1 and insert a row into it.
obclient> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> SELECT * FROM t1;
Empty set
obclient> INSERT t1 VALUES(1,1);
Query OK, 1 row affected
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
+----+------+
1 row in set
Example 2: Insert multiple rows into the t1 table.
obclient> INSERT t1 VALUES(2,2),(3,default),(2+2,3*4);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t1;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | NULL |
| 4 | 12 |
+----+------+
4 rows in set
Delete data
Use the DELETE statement to delete data. You can delete data from one or multiple tables.
Example 1: Use the CREATE TABLE statement to create tables t2 and t3. Drop the rows whose c1 value is 2. c1 is the primary key column of the t2 table.
/*The `t3` table is a `KEY`-partitioned table and the partition names are automatically generated by the system based on the partitioning rule, namely, `p0`, `p1`, `p2`, and `p3`.*/
obclient> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> INSERT t2 VALUES(1,1),(2,2),(3,3),(4,4);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
+----+------+
4 rows in set
obclient> CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> INSERT INTO t3 VALUES(5,5),(1,1),(2,2),(3,3);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t3;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
4 rows in set
obclient> DELETE FROM t2 WHERE c1 = 2;
Query OK, 1 row affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 3 | 3 |
| 4 | 4 |
+----+------+
3 rows in set
Example 2: Delete the data in the first row sorted by the c2 column from the t2 table.
obclient> DELETE FROM t2 ORDER BY c2 LIMIT 1;
Query OK, 1 row affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 3 | 3 |
| 4 | 4 |
+----+------+
2 rows in set
Example 3: Delete the data in the p2 partition from the t3 table.
obclient> SELECT * FROM t3 PARTITION(p2);
+----+------+
| c1 | c2 |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+------+
3 rows in set
obclient> DELETE FROM t3 PARTITION(p2);
Query OK, 3 rows affected
obclient> SELECT * FROM t3;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set
Example 4: Delete the data meeting the t2.c1 = t3.c1 condition from the t2 and t3 tables.
obclient> DELETE t2, t3 FROM t2, t3 WHERE t2.c1 = t3.c1;
Query OK, 3 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 3 | 3 |
| 4 | 4 |
+----+------+
2 rows in set
obclient> SELECT * FROM t3;
+----+------+
| c1 | c2 |
+----+------+
| 5 | 5 |
+----+------+
1 row in set
/*Equivalent to:*/
obclient> DELETE FROM t2, t3 USING t2, t3 WHERE t2.c1 = t3.c1;
Query OK, 4 rows affected
obclient> SELECT * FROM t2;
+----+------+
| c1 | c2 |
+----+------+
| 4 | 4 |
+----+------+
1 row in set
obclient> SELECT * FROM t3;
Empty set
Update data
Use the UPDATE statement to modify the field values in a table.
Example 1: Use the CREATE TABLE statement to create tables t4 and t5. For the row corresponding to t2.c1=10, change its value in the c2 column of the t4 table to 100.
obclient> CREATE TABLE t4(c1 INT PRIMARY KEY, c2 INT);
Query OK, 0 rows affected
obclient> INSERT t4 VALUES(10,10),(20,20),(30,30),(40,40);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t4;
+----+------+
| c1 | c2 |
+----+------+
| 10 | 10 |
| 20 | 20 |
| 30 | 30 |
| 40 | 40 |
+----+------+
4 rows in set
obclient> CREATE TABLE t5(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
Query OK, 0 rows affected
obclient> INSERT t5 VALUES(50,50),(10,10),(20,20),(30,30);
Query OK, 4 rows affected
Records: 4 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t5;
+----+------+
| c1 | c2 |
+----+------+
| 20 | 20 |
| 10 | 10 |
| 50 | 50 |
| 30 | 30 |
+----+------+
4 rows in set
obclient> UPDATE t4 SET t4.c2 = 100 WHERE t4.c1 = 10;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT * FROM t4;
+----+------+
| c1 | c2 |
+----+------+
| 10 | 100 |
| 20 | 20 |
| 30 | 30 |
| 40 | 40 |
+----+------+
4 rows in set
Example 2: For the first two rows sorted by column c2 in table t4, set their values in column c2 to 100.
obclient> UPDATE t4 set t4.c2 = 100 ORDER BY c2 LIMIT 2;
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
obclient> SELECT * FROM t4;
+----+------+
| c1 | c2 |
+----+------+
| 10 | 100 |
| 20 | 100 |
| 30 | 100 |
| 40 | 40 |
+----+------+
4 rows in set
Example 3: In partition p1 of table t5, for rows where t2.c1 > 2, set their values in column c2 to 100.
obclient> SELECT * FROM t5 PARTITION (p1);
+----+------+
| c1 | c2 |
+----+------+
| 10 | 10 |
| 50 | 50 |
+----+------+
2 rows in set
obclient> UPDATE t5 PARTITION(p1) SET t5.c2 = 100 WHERE t5.c1 > 20;
Query OK, 1 row affected
Rows matched: 1 Changed: 1 Warnings: 0
obclient> SELECT * FROM t5 PARTITION(p1);
+----+------+
| c1 | c2 |
+----+------+
| 10 | 10 |
| 50 | 100 |
+----+------+
2 rows in set
Example 4: For rows that meet the t4.c2 = t5.c2 condition in tables t4 and t5, change the value in the c2 column of the t4 table to 100, and that in the c2 column of the t5 table to 200.
obclient> UPDATE t4,t5 SET t4.c2 = 100, t5.c2 = 200 WHERE t4.c2 = t5.c2;
Query OK, 1 row affected
Rows matched: 4 Changed: 1 Warnings: 0
obclient> SELECT * FROM t4;
+----+------+
| c1 | c2 |
+----+------+
| 10 | 100 |
| 20 | 100 |
| 30 | 100 |
| 40 | 40 |
+----+------+
4 rows in set
obclient> SELECT * FROM t5;
+----+------+
| c1 | c2 |
+----+------+
| 20 | 20 |
| 10 | 10 |
| 50 | 200 |
| 30 | 30 |
+----+------+
4 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 t6. Query the data in the name field from the t6 table.
obclient> CREATE TABLE t6 (id INT, name VARCHAR(50), num INT);
Query OK, 0 rows affected
obclient> INSERT INTO t6 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t6;
+------+------+------+
| ID | NAME | NUM |
+------+------+------+
| 1 | a | 100 |
| 2 | b | 200 |
| 3 | a | 50 |
+------+------+------+
3 rows in set
obclient> SELECT name FROM t6;
+------+
| NAME |
+------+
| a |
| b |
| a |
+------+
3 rows in set
Example 2: Deduplicate the query results of the name field.
obclient> SELECT DISTINCT name FROM t6;
+------+
| 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 t6 table.
obclient> SELECT id, name, num FROM t6 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,
gmt_create DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(id, name, value, gmt_create) VALUES(1,'CN',10001, current_timestamp),(2,'US',10002, current_timestamp),(3,'EN',10003, current_timestamp);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-08-22 16:19:26 |
| 2 | US | 10002 | 2022-08-22 16:19:26 |
| 3 | EN | 10003 | 2022-08-22 16:19:26 |
+----+------+-------+---------------------+
3 rows in set
obclient> INSERT INTO t_insert(id,name) VALUES(4,'JP');
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 | 2022-08-22 16:19:26 |
| 2 | US | 10002 | 2022-08-22 16:19:26 |
| 3 | EN | 10003 | 2022-08-22 16:19:26 |
| 4 | JP | NULL | 2022-08-22 16:21:39 |
+----+------+-------+---------------------+
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 | 2022-08-22 16:19:26 |
| 2 | US | 10002 | 2022-08-22 16:19:26 |
| 3 | EN | 10003 | 2022-08-22 16:19:26 |
+----+------+-------+---------------------+
3 rows in set
obclient> BEGIN;
Query OK, 0 rows affected
obclient> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004);
ERROR 1062 (23000): Duplicate entry '4' for key 'PRIMARY'
obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'FR',10005),(6,'RU',10006);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-08-22 16:19:26 |
| 2 | US | 10002 | 2022-08-22 16:19:26 |
| 3 | EN | 10003 | 2022-08-22 16:19:26 |
| 4 | JP | 10004 | 2022-08-22 16:25:45 |
| 5 | FR | 10005 | 2022-08-22 16:26:23 |
| 6 | RU | 10006 | 2022-08-22 16:26:23 |
+----+------+-------+---------------------+
6 rows in set
obclient> ROLLBACK;
Query OK, 0 rows affected
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10001 | 2022-09-28 15:45:26 |
| 2 | US | 10002 | 2022-09-28 15:45:26 |
| 3 | EN | 10003 | 2022-09-28 15:45:26 |
| 4 | JP | NULL | 2022-09-28 15:45:48 |
+----+------+-------+---------------------+
4 rows in set