Basic SQL operations (MySQL mode)

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

Create a database

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

Example: create a database named db1, specify the character set as utf8mb4, and set the read/write attributes.

obclient(root@mysqltenant)[(none)]> CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 READ WRITE;

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

After the database is created, you can execute the SHOW DATABASES statement to view all databases in the current database server.

obclient(root@mysqltenant)[(none)]> SHOW DATABASES;

The return result is as follows:

+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| oceanbase          |
| test               |
| test_db            |
+--------------------+
6 rows in set

Table operations

In OceanBase Database, a table is the most basic data storage unit. It contains all the data that users can access. Each table contains multiple records, and each record consists of multiple columns. This topic provides the syntax and examples for creating, viewing, modifying, and deleting tables in a database.

Create a table

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

Example: create a table named test in the db1 database.

1. Switch to the db1 database.

   obclient(root@mysqltenant)[(none)]> USE db1;
   

2. Create a table named test.

   obclient(root@mysqltenant)[db1]> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
   

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

View a table

You can execute the SHOW CREATE TABLE statement to view the statement for creating a table.

Example: view the statement for creating the test table.

obclient(root@mysqltenant)[db1]> SHOW CREATE TABLE test\G

The return result is as follows:

*************************** 1. row ***************************
        Table: test
  Create Table: CREATE TABLE `test` (
    `c1` int(11) NOT NULL,
    `c2` varchar(3) DEFAULT NULL,
    PRIMARY KEY (`c1`)
  ) ORGANIZATION INDEX DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE ENABLE_MACRO_BLOCK_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
1 row in set

You can also execute the SHOW TABLES statement to view all tables in a specified database.

Example: view all tables in the db1 database.

obclient(root@mysqltenant)[db1]> SHOW TABLES FROM db1;

The return result is as follows:

+---------------+
| Tables_in_db1 |
+---------------+
| test          |
+---------------+
1 row in set

For more information about the SHOW statement, see SHOW.

Modify a table

You can execute 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:

• Rename the c2 column to c3 in the test table and modify its data type.

  1. View the column definitions of the test table.

     obclient(root@mysqltenant)[db1]> DESCRIBE test;
     

     The return result is as follows:

     +-------+------------+------+-----+---------+-------+
     | Field | Type       | Null | Key | Default | Extra |
     +-------+------------+------+-----+---------+-------+
     | c1    | int(11)    | NO   | PRI | NULL    |       |
     | c2    | varchar(3) | YES  |     | NULL    |       |
     +-------+------------+------+-----+---------+-------+
     2 rows in set
     

  2. Rename the c2 column to c3 in the test table and modify its data type to char.

     obclient(root@mysqltenant)[db1]> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);
     

  3. View the column definitions of the test table again to confirm the modification.

     obclient(root@mysqltenant)[db1]> DESCRIBE test;
     

     The return result is as follows:

     +-------+----------+------+-----+---------+-------+
     | Field | Type     | Null | Key | Default | Extra |
     +-------+----------+------+-----+---------+-------+
     | c1    | int(11)  | NO   | PRI | NULL    |       |
     | c3    | char(10) | YES  |     | NULL    |       |
     +-------+----------+------+-----+---------+-------+
     2 rows in set
     

• Add and delete columns in the test table.

  1. View the column definitions of the test table.

     obclient(root@mysqltenant)[db1]> DESCRIBE test;
     

     The return result is as follows:

     +-------+----------+------+-----+---------+-------+
     | Field | Type     | Null | Key | Default | Extra |
     +-------+----------+------+-----+---------+-------+
     | c1    | int(11)  | NO   | PRI | NULL    |       |
     | c3    | char(10) | YES  |     | NULL    |       |
     +-------+----------+------+-----+---------+-------+
     2 rows in set
     

  2. Add a column named c4 to the test table.

     obclient(root@mysqltenant)[db1]> ALTER TABLE test ADD c4 int;
     

  3. View the column definitions of the test table again to confirm that the c4 column has been added.

     obclient(root@mysqltenant)[db1]> DESCRIBE test;
     

     The return result is as follows:

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

  4. Delete the c3 column from the test table.

     obclient(root@mysqltenant)[db1]> ALTER TABLE test DROP c3;
     

  5. View the column definitions of the test table again to confirm that the c3 column has been deleted.

     obclient(root@mysqltenant)[db1]> DESCRIBE test;
     

     The return result is as follows:

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

Example: drop the test table.

obclient(root@mysqltenant)[db1]> 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 the database table. Its main purpose is to improve query speed and reduce the performance overhead of the database system. This section mainly introduces the syntax and examples for creating, viewing, and deleting indexes in the database.

Create an index

You can 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(root@mysqltenant)[db1]> DESCRIBE test;
   

   The return result is as follows:

   +-------+----------+------+-----+---------+-------+
   | Field | Type     | Null | Key | Default | Extra |
   +-------+----------+------+-----+---------+-------+
   | c1    | int(11)  | NO   | PRI | NULL    |       |
   | c2    | char(3)  | YES  |     | NULL    |       |
   +-------+----------+------+-----+---------+-------+
   2 rows in set
   

2. Create a composite index named test_index on the c1 and c2 columns of the test table.

   obclient(root@mysqltenant)[db1]> CREATE INDEX test_index ON test (c1, c2);
   

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

View indexes

You can use the SHOW INDEX statement to view indexes of a table.

Example: View the index information of the test table.

obclient(root@mysqltenant)[db1]> SHOW INDEX FROM test\G

The return result is as follows:

*************************** 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
   Expression: NULL
*************************** 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
   Expression: NULL
*************************** 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
   Expression: NULL
3 rows in set

For more information about the SHOW statement, see SHOW.

Delete an index

You can use the DROP INDEX statement to delete an index from a table.

Example: Delete the index from the test table.

obclient(root@mysqltenant)[db1]> DROP INDEX test_index ON test;

For more information about the DROP INDEX statement, see DROP INDEX.

Insert data

You can use the INSERT statement to insert data into an existing table.

Example:

• Create the t1 table and insert a row of data.

  1. Create the t1 table.

     obclient(root@mysqltenant)[db1]> CREATE TABLE t1(c1 INT PRIMARY KEY, c2 int) PARTITION BY KEY(c1) PARTITIONS 4;
     

  2. View the data in the table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t1;
     

     The query result is empty, indicating that the table contains no data.

  3. Insert a row of data into the t1 table.

     obclient(root@mysqltenant)[db1]> INSERT t1 VALUES(1,1);
     

  4. View the data in the table again to confirm that the data was inserted successfully.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t1;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     +----+------+
     1 row in set
     

• Insert multiple rows of data into the t1 table.

  1. View the data in the current table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t1;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     +----+------+
     1 row in set
     

  2. Insert multiple rows of data into the t1 table.

     obclient(root@mysqltenant)[db1]> INSERT t1 VALUES(2,2),(3,default),(2+2,3*4);
     

  3. View the data in the table again to confirm that the data was inserted successfully.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t1;
     

     The return result is as follows:

     +----+------+
     | 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 use the DELETE statement to delete data from a single table or multiple tables.

To facilitate the examples, create two sample tables t2 and t3 and insert data into them.

1. Create the sample table t2 and insert data into it.

   1. Create a non-partitioned table t2.

      obclient(root@mysqltenant)[db1]> CREATE TABLE t2(c1 INT PRIMARY KEY, c2 INT);
      

   2. Insert multiple rows of data into the t2 table.

      obclient(root@mysqltenant)[db1]> INSERT t2 VALUES(1,1),(2,2),(3,3),(5,5);
      

   3. View the data in the table to confirm that the data was inserted.

      obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
      

      The return result is as follows:

      +----+------+
      | c1 | c2   |
      +----+------+
      |  1 |    1 |
      |  2 |    2 |
      |  3 |    3 |
      |  5 |    5 |
      +----+------+
      4 rows in set
      

2. Create the sample table t3 and insert data into it.

   1. Create a primary partitioned table t3 with a KEY partitioning scheme. The partition names are automatically generated by the system based on the partitioning command rules, namely p0, p1, p2, and p3.

      obclient(root@mysqltenant)[db1]> CREATE TABLE t3(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
      

   2. Insert multiple rows of data into the t3 table.

      obclient(root@mysqltenant)[db1]> INSERT INTO t3 VALUES(5,5),(1,1),(2,2),(3,3);
      

   3. View the data in the table to confirm that the data was inserted.

      obclient(root@mysqltenant)[db1]> SELECT * FROM t3;
      

      The return result is as follows:

      +----+------+
      | c1 | c2   |
      +----+------+
      |  5 |    5 |
      |  1 |    1 |
      |  2 |    2 |
      |  3 |    3 |
      +----+------+
      4 rows in set
      

Examples:

• Delete specified rows from the table.

  1. View the data in the t2 table before the deletion.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     |  2 |    2 |
     |  3 |    3 |
     |  5 |    5 |
     +----+------+
     4 rows in set
     

  2. Delete the rows from the t2 table where c1=2, and c1 is the PRIMARY KEY of the t2 table.

     obclient(root@mysqltenant)[db1]> DELETE FROM t2 WHERE c1 = 2;
     

  3. View the data in the t2 table after the deletion to confirm that the rows were deleted.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     |  3 |    3 |
     |  5 |    5 |
     +----+------+
     3 rows in set
     

• Delete rows that meet the specified conditions from the table.

  1. View the data in the t2 table before the deletion.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     |  3 |    3 |
     |  5 |    5 |
     +----+------+
     3 rows in set
     

  2. Delete the first row of data in the t2 table after sorting by the c2 column.

     obclient(root@mysqltenant)[db1]> DELETE FROM t2 ORDER BY c2 LIMIT 1;
     

  3. View the data in the t2 table after the deletion to confirm that the rows were deleted.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  3 |    3 |
     |  5 |    5 |
     +----+------+
     3 rows in set
     

• Delete data from the specified partition in the table.

  1. View the data in the p2 partition of the t3 table before the deletion.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t3 PARTITION(p2); 
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  1 |    1 |
     |  2 |    2 |
     |  3 |    3 |
     +----+------+
     3 rows in set
     

  2. Delete the data in the p2 partition of the t3 table.

     obclient(root@mysqltenant)[db1]> DELETE FROM t3 PARTITION(p2); 
     

  3. View the data in the t3 table after the deletion to confirm that the data was deleted.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t3;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   | 
     +----+------+
     |  5 |    5 |
     +----+------+
     1 row in set
     

• Delete data from multiple tables.

  1. View the data in the t2 and t3 tables before the deletion.

     View the data in the t2 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  3 |    3 |
     |  5 |    5 |
     +----+------+
     2 rows in set
     

     View the data in the t3 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t3;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   | 
     +----+------+
     |  5 |    5 |
     +----+------+
     1 row in set
     

  2. Delete data from the t2 and t3 tables where t2.c1 = t3.c1.

     obclient(root@mysqltenant)[db1]> DELETE t2, t3 FROM t2, t3 WHERE t2.c1 = t3.c1;
     

     This statement is equivalent to the following statement:

     obclient(root@mysqltenant)[db1]> DELETE FROM t2, t3 USING t2, t3 WHERE t2.c1 = t3.c1;
     

  3. View the data in the t2 and t3 tables after the deletion to confirm that the data was deleted.

     View the data in the t2 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t2;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     |  3 |    3 |
     +----+------+
     1 row in set
     

     View the data in the t3 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t3;
     

     The return result is empty, indicating that the data in the t3 table has been deleted.

For more information about the DELETE statement, see DELETE.

Update data

You can use the UPDATE statement to modify field values in a table.

For the purpose of illustration, first create the sample tables t4 and t5 and insert data into them.

1. Create the sample table t4 and insert data into it.

   1. Create a non-partitioned table t2.

      obclient(root@mysqltenant)[db1]> CREATE TABLE t4(c1 INT PRIMARY KEY, c2 INT);
      

   2. Insert multiple rows of data into the t4 table.

      obclient(root@mysqltenant)[db1]> INSERT t4 VALUES(10,10),(20,20),(30,30),(40,40);
      

   3. View the data in the table to confirm the insertion.

      obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
      

      The return result is as follows:

      +----+------+
      | c1 | c2   |
      +----+------+
      | 10 |   10 |
      | 20 |   20 |
      | 30 |   30 |
      | 40 |   40 |
      +----+------+
      4 rows in set
      

2. Create the sample table t5 and insert data into it.

   1. Create a primary partitioned table t5 with a KEY partition. The partition names are automatically generated by the system based on the partitioning rules, which are p0, p1, p2, and p3.

      obclient(root@mysqltenant)[db1]> CREATE TABLE t5(c1 INT PRIMARY KEY, c2 INT) PARTITION BY KEY(c1) PARTITIONS 4;
      

   2. Insert multiple rows of data into the t5 table.

      obclient(root@mysqltenant)[db1]> INSERT t5 VALUES(50,50),(10,10),(20,20),(30,30);
      

   3. View the data in the table to confirm the insertion.

      obclient(root@mysqltenant)[db1]> SELECT * FROM t5;
      

      The return result is as follows:

      +----+------+
      | c1 | c2   |
      +----+------+
      | 20 |   20 |
      | 10 |   10 |
      | 50 |   50 |
      | 30 |   30 |
      +----+------+
      4 rows in set
      

Example:

• Update the value of a specified column in a specified row in the table.

  1. View the data in the t4 table before the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |   10 |
     | 20 |   20 |
     | 30 |   30 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

  2. Update the value of the c2 column in the row where t4.c1=10 in the t4 table to 100.

     obclient(root@mysqltenant)[db1]> UPDATE t4 SET t4.c2 = 100 WHERE t4.c1 = 10;
     

  3. View the data in the t4 table after the update to confirm the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |  100 |
     | 20 |   20 |
     | 30 |   30 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

• Update the value of the c2 column in the first two rows sorted by the c2 column in the t4 table to 100.

  1. View the data in the t4 table before the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |  100 |
     | 20 |   20 |
     | 30 |   30 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

  2. Update the value of the c2 column in the first two rows sorted by the c2 column in the t4 table to 100.

     obclient(root@mysqltenant)[db1]> UPDATE t4 set t4.c2 = 100 ORDER BY c2 LIMIT 2;
     

  3. View the data in the t4 table after the update to confirm the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |  100 |
     | 20 |  100 |
     | 30 |  100 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

• Update the values in a specified partition in the table.

  1. View the values in the p1 partition of the t5 table before the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t5 PARTITION (p1);
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |   10 |
     | 50 |   50 |
     +----+------+
     2 rows in set
     

  2. Update the value of the c2 column in the rows where t5.c1 > 20 in the p1 partition of the t5 table to 100.

     obclient(root@mysqltenant)[db1]> UPDATE t5 PARTITION(p1) SET t5.c2 = 100 WHERE t5.c1 > 20;
     

  3. View the values in the p1 partition of the t5 table after the update to confirm the update.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t5 PARTITION (p1);
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |   10 |
     | 50 |  100 |
     +----+------+
     2 rows in set
     

• For the rows in the t4 and t5 tables that satisfy the condition t4.c2 = t5.c2, update the value of the c2 column in the t4 table to 100 and the value of the c2 column in the t5 table to 200.

  1. View the data in the t4 and t5 tables before the update.

     View the data in the t4 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |  100 |
     | 20 |  100 |
     | 30 |  100 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

     View the data in the t5 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t5;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 20 |   20 |
     | 10 |   10 |
     | 50 |  100 |
     | 30 |   30 |
     +----+------+
     4 rows in set
     

  2. For the rows in the t4 and t5 tables that satisfy the condition t4.c2 = t5.c2, update the value of the c2 column in the t4 table to 100 and the value of the c2 column in the t5 table to 200.

     obclient(root@mysqltenant)[db1]> UPDATE t4,t5 SET t4.c2 = 100, t5.c2 = 200 WHERE t4.c2 = t5.c2;
     

  3. View the data in the t4 and t5 tables after the update to confirm the update.

     View the data in the t4 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t4;
     

     The return result is as follows:

     +----+------+
     | c1 | c2   |
     +----+------+
     | 10 |  100 |
     | 20 |  100 |
     | 30 |  100 |
     | 40 |   40 |
     +----+------+
     4 rows in set
     

     View the data in the t5 table.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t5;
     

     The return result is as follows:

     +----+------+
     | 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 use the SELECT statement to query data from a table.

Example:

• Execute the CREATE TABLE statement to create the t6 table and read the name field from the t6 table.

  1. Create the t6 table.

     obclient(root@mysqltenant)[db1]> CREATE TABLE t6 (id INT, name VARCHAR(50), num INT);
     

  2. Insert multiple rows of data into the t6 table.

     obclient(root@mysqltenant)[db1]> INSERT INTO t6 VALUES(1,'a',100),(2,'b',200),(3,'a',50);
     

  3. Query the t6 table and view the data to confirm the data insertion.

     obclient(root@mysqltenant)[db1]> SELECT * FROM t6;
     

     The returned result is as follows:

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

  4. Read the name field from the t6 table.

     obclient(root@mysqltenant)[db1]> SELECT name FROM t6;
     

     The returned result is as follows:

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

• Deduplicate the name field in the query results.

  obclient(root@mysqltenant)[db1]> SELECT DISTINCT name FROM t6;
  

  The returned result is as follows:

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

• Execute the SELECT statement to query the id, name, and num columns from the t6 table where name = 'a'.

  obclient(root@mysqltenant)[db1]> SELECT id, name, num FROM t6 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

You can use the COMMIT statement to commit a transaction.

Before you commit a transaction:

• Your modifications are only visible to your current session.
• Your modifications are not persisted to the database. You can use the ROLLBACK statement to undo the modifications.

After you commit a transaction:

• Your modifications are visible to all database sessions.
• Your modifications are successfully persisted to the database and cannot be rolled back by using the ROLLBACK statement.

Example: Create the t_insert table by using the CREATE TABLE statement and commit a transaction by using the COMMIT statement.

1. Create the t_insert table.

   obclient(root@mysqltenant)[db1]> 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
   );
   

2. Start a transaction.

   obclient(root@mysqltenant)[db1]> BEGIN;
   

3. Insert multiple rows of data into the t_insert table.

   obclient(root@mysqltenant)[db1]> 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);
   

4. Query the t_insert table to view the data. You can find the newly inserted data.

   obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;
   

   The returned result is as follows:

   +------+------+-------+---------------------+
   | id   | name | value | gmt_create          |
   +------+------+-------+---------------------+
   |    1 | CN   | 10001 | 2025-11-07 17:46:35 |
   |    2 | US   | 10002 | 2025-11-07 17:46:35 |
   |    3 | EN   | 10003 | 2025-11-07 17:46:35 |
   +------+------+-------+---------------------+
   3 rows in set
   

5. Insert one more row of data into the t_insert table.

   obclient(root@mysqltenant)[db1]> INSERT INTO t_insert(id,name) VALUES(4,'JP');
   

6. Commit the transaction.

   obclient(root@mysqltenant)[db1]> COMMIT;
   

7. Log out of your current session.

   obclient(root@mysqltenant)[db1]> exit;
   

8. Log in to the database.

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

9. Query the t_insert table to view the data. You can find that the modifications are persisted to the database.

   obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;
   

   The returned result is as follows:

   +------+------+-------+---------------------+
   | id   | name | value | gmt_create          |
   +------+------+-------+---------------------+
   |    1 | CN   | 10001 | 2025-11-07 17:46:35 |
   |    2 | US   | 10002 | 2025-11-07 17:46:35 |
   |    3 | EN   | 10003 | 2025-11-07 17:46:35 |
   |    4 | JP   |  NULL | 2025-11-07 17:46:53 |
   +------+------+-------+---------------------+
   4 rows in set
   

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

Rollback a transaction

You can use the ROLLBACK statement to rollback a transaction.

Rolling back a transaction means that all the changes made during the transaction are undone. You can either rollback the entire uncommitted transaction or rollback to any savepoint of the transaction. If you want to rollback to a savepoint, you must combine the ROLLBACK statement with the TO SAVEPOINT statement. Specifically:

• If you rollback the entire transaction, then:

  • The transaction ends.
  • All the changes are discarded.
  • All the savepoints are cleared.
  • All locks held by the transaction are released.

• If you rollback to a savepoint, then:

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

Example: Rollback all changes made in a transaction.

1. View the data in the t_insert table before modification.

   obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;
   

   The return result is as follows:

   +------+------+-------+---------------------+
   | id   | name | value | gmt_create          |
   +------+------+-------+---------------------+
   |    1 | CN   | 10001 | 2025-11-07 17:46:35 |
   |    2 | US   | 10002 | 2025-11-07 17:46:35 |
   |    3 | EN   | 10003 | 2025-11-07 17:46:35 |
   +------+------+-------+---------------------+
   3 rows in set
   

2. Start a transaction.

   obclient(root@mysqltenant)[db1]> BEGIN;
   

3. Insert multiple rows of data into the t_insert table.

   obclient(root@mysqltenant)[db1]> INSERT INTO t_insert(id, name, value) VALUES(4,'JP',10004),(5,'FR',10005),(6,'RU',10006);
   

4. View the data in the t_insert table after modification. You can see that the data insertion is successful.

   obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;
   

   The return result is as follows:

   +------+------+-------+---------------------+
   | id   | name | value | gmt_create          |
   +------+------+-------+---------------------+
   |    1 | CN   | 10001 | 2025-11-07 17:46:35 |
   |    2 | US   | 10002 | 2025-11-07 17:46:35 |
   |    3 | EN   | 10003 | 2025-11-07 17:46:35 |
   |    4 | JP   | 10004 | 2025-11-07 17:48:01 |
   |    5 | FR   | 10005 | 2025-11-07 17:48:01 |
   |    6 | RU   | 10006 | 2025-11-07 17:48:01 |
   +------+------+-------+---------------------+
   6 rows in set
   

5. Rollback the transaction.

   obclient(root@mysqltenant)[db1]> ROLLBACK;
   

6. After the rollback, view the data in the t_insert table again. You can see that the data insertion is rolled back.

   obclient(root@mysqltenant)[db1]> SELECT * FROM t_insert;
   

   The return result is as follows:

   +------+------+-------+---------------------+
   | id   | name | value | gmt_create          |
   +------+------+-------+---------------------+
   |    1 | CN   | 10001 | 2025-11-07 17:46:35 |
   |    2 | US   | 10002 | 2025-11-07 17:46:35 |
   |    3 | EN   | 10003 | 2025-11-07 17:46:35 |
   +------+------+-------+---------------------+
   3 rows in set
   

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