Basic SQL operations (MySQL-compatible mode)

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

Create a database

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

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

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

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

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

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

The 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 fundamental data storage unit. It contains all the data accessible to users. Each table contains multiple rows of records, and each record consists of multiple columns. This section provides the syntax and examples for creating, viewing, modifying, and dropping tables in a database.

Create a table

You can execute the CREATE TABLE statement to create a 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 the test table.

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

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

View a table

You can execute the SHOW CREATE TABLE statement to view the statement used to create a table.

Example: view the statement used to create the test table.

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

The 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 result is as follows:

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

For more information about the syntax of 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 name and table attributes, adding columns, modifying the column name and attributes, and dropping columns.

Example:

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

  1. View the column definitions of the test table.

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

     The 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 field in the test table to c3 and modify its field 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 that the modification is successful.

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

     The 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 a column to and drop a column from the test table.

  1. View the current column definitions of the test table.

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

     The 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 c4 column 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 is added.

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

     The 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. Drop 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 is dropped.

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

     The 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 syntax of 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 syntax of 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 table. It improves the query speed and reduces the performance overhead of the database system. This section provides the syntax and examples for creating, viewing, and dropping indexes in a database.

Create an index

You can execute 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 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 syntax of the CREATE INDEX statement, see CREATE INDEX.

View indexes

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

Example: view the index information of the test table.

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

The 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 syntax of the SHOW statement, see SHOW.

Drop an index

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

Example: drop the index of the test table.

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

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

Insert data

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

Example:

• Create a table named t1 and insert a row of data into it.

  1. Create a table named t1.

     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 result is empty, which indicates 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 row is inserted.

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

     The result is as follows:

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

• Insert multiple rows of data into the t1 table.

  1. View the current data in the table.

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

     The result is as follows:

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

  2. Insert multiple rows of data into the t1 table at a time.

     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 is inserted.

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

     The result is as follows:

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

For more information about the syntax of the INSERT statement, see INSERT.

Delete data

You can execute the DELETE statement to delete data from a single table or multiple tables.

For the convenience of the examples, create 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 is inserted.

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

      The 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 KEY-partitioned table t3. The partition names are automatically generated by the system based on the partitioning rule, 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 is inserted.

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

      The result is as follows:

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

Example:

• Delete the specified rows from a table.

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

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

     The result is as follows:

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

  2. Delete the rows where c1=2 from the t2 table. Note that c1 is the PRIMARY KEY column 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 deletion is successful.

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

     The result is as follows:

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

• Delete rows that meet a specific condition from a table.

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

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

     The result is as follows:

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

  2. Delete the first row sorted by the c2 column from the t2 table.

     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 deletion is successful.

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

     The result is as follows:

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

• Delete data from a specified partition.

  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 result is as follows:

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

  2. Delete the data from 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 deletion is successful.

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

     The result is as follows:

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

• Delete data from multiple tables at a time.

  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 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 result is as follows:

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

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

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

     This statement is equivalent to:

     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 deletion is successful.

     View the data in the t2 table.

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

     The 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 result is empty, which indicates that the data in the t3 table is deleted.

For more information about the syntax of the DELETE statement, see DELETE.

Update data

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

For the convenience of the examples, create 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 t4.

      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 that the data is inserted.

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

      The 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 KEY-partitioned table t5. The partition names are automatically generated by the system based on the partitioning rule, namely, 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 that the data is inserted.

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

      The result is as follows:

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

Example:

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

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

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

     The result is as follows:

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

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

     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 that the update is successful.

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

     The result is as follows:

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

• Change the values in the c2 column to 100 for the first two rows sorted by the c2 column in the t4 table.

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

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

     The result is as follows:

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

  2. Change the values in the c2 column to 100 for the first two rows sorted by the c2 column in the t4 table.

     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 that the update is successful.

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

     The result is as follows:

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

• Update the value in a specified partition.

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

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

     The result is as follows:

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

  2. Change the values in the c2 column to 100 for the rows where t5.c1 > 20 in the p1 partition of the t5 table.

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

  3. View the value in the p1 partition of the t5 table after the update to confirm that the update is successful.

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

     The result is as follows:

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

• For the rows where t4.c2 = t5.c2 in the t4 and t5 tables, change the values in the c2 column of the t4 table to 100 and those in the c2 column of 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 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 result is as follows:

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

  2. For the rows where t4.c2 = t5.c2 in the t4 and t5 tables, change the values in the c2 column of the t4 table to 100 and those in the c2 column of 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 that the update is successful.

     View the data in the t4 table.

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

     The 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 result is as follows:

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

For more information about the syntax of the UPDATE statement, see UPDATE.

Query data

You can execute the SELECT statement to query the content of a table.

Example:

• Create a table named t6 by using the CREATE TABLE statement, and read the data of the name field from the t6 table.

  1. Create a table named t6.

     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. View the data in the t6 table to confirm that the data is inserted.

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

     The result is as follows:

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

  4. Read the data of the name field from the t6 table.

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

     The result is as follows:

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

• Deduplicate the query results of the name field.

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

  The result is as follows:

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

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

  obclient(root@mysqltenant)[db1]> SELECT id, name, num FROM t6 WHERE name = 'a';
  

  The result is as follows:

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

For more information about the syntax of 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. You cannot use the ROLLBACK statement to roll them back.

Example: create a table named t_insert by using the CREATE TABLE statement, and use the COMMIT statement to commit a transaction.

1. Create a table named t_insert.

   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. View the data in the t_insert table after the modification. You can see that the data is inserted.

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

   The 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 another 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. Exit the current session.

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

8. Log on to the database again.

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

9. View the data in the t_insert table again. You can see that the modifications to the data in the t_insert table are persisted.

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

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

Roll back a transaction

You can execute the ROLLBACK statement to roll back a transaction.

A transaction rollback undoes all modifications made in the transaction. You can roll back the entire uncommitted transaction or roll back the transaction to any savepoint. To roll back to a specific savepoint, you must use the ROLLBACK and TO SAVEPOINT statements together. Note that:

• If you roll back the entire transaction:

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

• If you roll back the transaction to a specific savepoint:

  • The transaction does not end.
  • Modifications made before the savepoint are retained, and those made after the savepoint are discarded.
  • Savepoints after the specific savepoint are cleared (excluding the savepoint itself).
  • Locks held by the transaction after the specific savepoint are released.

Example: roll back all modifications of a transaction.

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

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

   The 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 the modification. You can see that the data is inserted.

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

   The 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. Roll back the transaction.

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

6. View the data in the t_insert table again after the rollback. You can see that the inserted data is rolled back.

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

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