Basic SQL operations (MySQL mode)|V4.2.2|OceanBase Database|docs|OceanBase Documentations

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

Create a database

You can execute the CREATE DATABASE statement to create a database.

For example, create a database named db1, set the character set to utf8mb4, and specify the read and write attributes.

obclient> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 READ WRITE;
Query OK, 1 row affected

For more information about the syntax of the CREATE DATABASE statement, see CREATE DATABASE.

After a database is created, you can execute the SHOW DATABASES statement to list all databases on the current database server.

obclient> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| oceanbase          |
| db1                |
| test               |
+--------------------+
3 rows in set

Table operations

This section describes how to create, view, modify, and drop database tables. Tables serve as the fundamental data storage units in OceanBase Database. They hold all the data that users can access. Each table is comprised of multiple rows, with each row containing various columns.

Create a table

You can execute the CREATE TABLE statement to create a table in a database.

For 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

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

View a table

You can execute the SHOW CREATE TABLE statement to view table creation statements.

Here are some examples:

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

View all tables in the db1 database by using the SHOW TABLES statement.

obclient> SHOW TABLES FROM db1;
+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set

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:

Rename the c2 field to c3 in the test table and modify the 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

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    |       |
| c3    | char(10) | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set

obclient> ALTER TABLE test ADD c4 int;
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    |       |
| c4    | 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    |       |
| c4    | int(11) | YES  |     | 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

This section describes how to create, view, and drop indexes in a database. 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    | int(11)  | NO   | PRI | NULL    |       |
| c2    | char(3)  | YES  |     | 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

You can execute the SHOW INDEX statement to query the indexes of a table.

For 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: c2
    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

You can execute the DROP INDEX statement to drop a table index.

For example, drop an index from the test table.

obclient> DROP INDEX test_index ON test;
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 insert data into an existing table.

Here are some examples:

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

Insert multiple rows of data into table t1.

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

For more information about the INSERT statement, see INSERT.

Delete data

You can execute the DELETE statement to delete data from one or more tables.

Here are some examples:

Use the CREATE TABLE statement to create tables t2 and t3. Drop the rows whose c1 value is 2. Note that 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),(5,5);
Query OK, 4 rows affected
Records: 4  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
|  5 |    5 |
+----+------+
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 |
|  5 |    5 |
+----+------+
3 rows in set

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 |
|  5 |    5 |
+----+------+
2 rows in set

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

Delete the data meeting the t2.c1 = t3.c1 condition from tables t2 and t3.

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
|  5 |    5 |
+----+------+
2 rows in set

obclient> SELECT * FROM t3;
+----+------+
| c1 | c2   |
+----+------+
|  5 |    5 |
+----+------+

obclient> DELETE t2, t3 FROM t2, t3 WHERE t2.c1 = t3.c1;
Query OK, 3 rows affected
/* Equivalent to
obclient> DELETE FROM t2, t3 USING t2, t3 WHERE t2.c1 = t3.c1;
*/

obclient> SELECT * FROM t2;
+----+------+
| c1 | c2   |
+----+------+
|  3 |    3 |
+----+------+
1 row in set

obclient> SELECT * FROM t3;
Empty 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:

Use the CREATE TABLE statement to create tables t4 and t5. For the row corresponding to t4.c1=10 in the t4 table, change its value in the c2 column 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

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

In partition p1 of table t5, for rows where t5.c1 > 20, 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

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

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 t6 and 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

Deduplicate the query results of the name field.

obclient> SELECT DISTINCT name FROM t6;
+------+
| NAME |
+------+
| a    |
| b    |
+------+
2 rows in set

Return the values in the id, name, and num columns based on the filter condition name = 'a' from table t6.

obclient> SELECT id, name, num FROM t6 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 a transaction, note that:

Your modifications are visible only to the current session and invisible to other database sessions.
Your modifications are not persisted. You can use the ROLLBACK statement to undo them.

After you commit a transaction, note that:

Your modifications are visible to all database sessions.
Your modifications are persisted into the database. You cannot use the ROLLBACK statement to undo them.

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> BEGIN;
Query OK, 0 rows affected

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> exit;
Bye

obclient> obclient -h127.0.0.1 -ur**t@mysql -P2881 -p****** -Ddb1

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

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

For more information about transaction control statements, see Overview of transaction management.