Skip to main content

Basic SQL operations

This topic introduces some basic SQL operations in seekdb.

Create a database​

Use 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 attribute.

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

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

After creation, you can use the SHOW DATABASES command to view all databases in the current database server.

obclient> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| db1                |
| information_schema |
| mysql              |
| oceanbase          |
| sys_external_tbs   |
| test               |
+--------------------+
6 rows in set

Table operations​

In seekdb, a table is the most basic data storage unit that contains all data accessible to users. Each table contains multiple rows of 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​

Use the CREATE TABLE statement to create a new table in a database.

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

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

Use the SHOW CREATE TABLE statement to view the table creation statement.

Examples:

  • View the table creation statement for the table test.

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

  • Use the SHOW TABLES statement to view all tables in the database db1.

    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 table attributes, adding columns, modifying columns and their attributes, and deleting columns.

Examples:

  • Rename the column c2 to c3 in the table test and change its data 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 and delete columns in the table test.

    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.

Delete a table​

Use the DROP TABLE statement to delete a table.

Example: Delete the table test.

obclient> DROP TABLE test;
Query OK, 0 rows affected

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

Index operations​

An index is a structure created on a table that sorts 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 topic introduces the syntax and examples for creating, viewing, and deleting indexes in a database.

Create an index​

Use the CREATE INDEX statement to create an index on a table.

Example: Create an index on the table test.

obclient> CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(3));
Query OK, 0 rows affected (0.10 sec)

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> CREATE INDEX test_index ON test (c1, c2);
Query OK, 0 rows affected

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

View indexes​

Use the SHOW INDEX statement to view indexes on a table.

Example: View index information for the table test.

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

Delete an index​

Use the DROP INDEX statement to delete an index on a table.

Example: Delete the index on the table test.

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​

Use the INSERT statement to insert data into an existing table.

Examples:

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

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

Use the DELETE statement to delete data. It supports deleting data from a single table or multiple tables.

Examples:

  • Create tables t2 and t3 using CREATE TABLE. Delete the row where c1=2, where c1 is the PRIMARY KEY column in the table t2.

    /*Table `t3` is a `KEY` partitioned table, and the partition names are automatically generated by the system according to the partition naming rules, that is, the partition names are `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 first row of data from the table t2 after sorting by the c2 column.

    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 data from the p2 partition of the table t3.

    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 data from tables t2 and t3 where t2.c1 = t3.c1.

    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​

Use the UPDATE statement to modify field values in a table.

Examples:

  • Create tables t4 and t5 using CREATE TABLE. Modify the c2 column value to 100 for the row where t4.c1=10 in the table t4.

    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

  • Modify the c2 column value to 100 for the first two rows of data in the table t4 after sorting by the c2 column.

    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

  • Modify the c2 column value to 100 for the rows in the p1 partition of the table t5 where t5.c1 > 20.

    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 in tables t4 and t5 that satisfy t4.c2 = t5.c2, modify the c2 column value in the table t4 to 100 and the c2 column value in the table t5 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​

Use the SELECT statement to query the contents of a table.

Examples:

  • Create a table t6 using CREATE TABLE. Read the name data from the table t6.

    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

  • Remove duplicates from the name column in the query results.

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

  • Output the corresponding id, name, and num from the table t6 based on the filter condition name = 'a'.

    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​

Use the COMMIT statement to commit a transaction.

Before committing a transaction (COMMIT):

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

After committing a transaction (COMMIT):

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

Example: Create a table t_insert using CREATE TABLE. Use the COMMIT statement to commit the 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> BEGIN;
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 | 2025-11-07 16:01:53 |
|  2 | US   | 10002 | 2025-11-07 16:01:53 |
|  3 | EN   | 10003 | 2025-11-07 16:01:53 |
+----+------+-------+---------------------+
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 -uroot -P2881 -Ddb1

obclient> SELECT * FROM t_insert;
+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 16:01:53 |
|    2 | US   | 10002 | 2025-11-07 16:01:53 |
|    3 | EN   | 10003 | 2025-11-07 16:01:53 |
|    4 | JP   |  NULL | 2025-11-07 16:02:02 |
+------+------+-------+---------------------+
4 rows in set

For more information about transaction control statements, see Transaction management overview.

Roll back a transaction​

Use the ROLLBACK statement to roll back a transaction.

Rolling back a transaction means undoing all modifications made in the transaction. You can roll back the entire uncommitted transaction or roll back to any savepoint in the transaction. To roll back to a savepoint, you must use the ROLLBACK statement together with TO SAVEPOINT.

  • 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 to a savepoint:

    • The transaction does not end.
    • Modifications before the savepoint are retained, and modifications after the savepoint are discarded.
    • Savepoints after the savepoint are cleared (excluding the savepoint itself).
    • All locks held by the transaction after the savepoint are released.

Example: Roll back all modifications in a transaction.

obclient> SELECT * FROM t_insert;
+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 16:01:53 |
|    2 | US   | 10002 | 2025-11-07 16:01:53 |
|    3 | EN   | 10003 | 2025-11-07 16:01:53 |
|    4 | JP   |  NULL | 2025-11-07 16:02:02 |
+------+------+-------+---------------------+
4 rows in set

obclient> BEGIN;
Query OK, 0 rows affected

obclient> INSERT INTO t_insert(id, name, value) VALUES(5,'JP',10004),(6,'FR',10005),(7,'RU',10006);
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

obclient> SELECT * FROM t_insert;
+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 16:01:53 |
|    2 | US   | 10002 | 2025-11-07 16:01:53 |
|    3 | EN   | 10003 | 2025-11-07 16:01:53 |
|    4 | JP   |  NULL | 2025-11-07 16:02:02 |
|    5 | JP   | 10004 | 2025-11-07 16:04:14 |
|    6 | FR   | 10005 | 2025-11-07 16:04:14 |
|    7 | RU   | 10006 | 2025-11-07 16:04:14 |
+------+------+-------+---------------------+
7 rows in set

obclient> ROLLBACK;
Query OK, 0 rows affected

obclient> SELECT * FROM t_insert;
+------+------+-------+---------------------+
| id   | name | value | gmt_create          |
+------+------+-------+---------------------+
|    1 | CN   | 10001 | 2025-11-07 16:01:53 |
|    2 | US   | 10002 | 2025-11-07 16:01:53 |
|    3 | EN   | 10003 | 2025-11-07 16:01:53 |
|    4 | JP   |  NULL | 2025-11-07 16:02:02 |
+------+------+-------+---------------------+
4 rows in set

For more information about transaction control statements, see Transaction management overview.

Create a user​

Use the CREATE USER statement to create a user.

Example:

Create a user named test.

obclient> CREATE USER 'test' IDENTIFIED BY '******';
Query OK, 0 rows affected

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

Grant user privileges​

Use the GRANT statement to grant privileges to a user.

Example:

Grant the user test the privilege to access all tables in the database db1.

obclient> GRANT SELECT ON db1.* TO test;
Query OK, 0 rows affected

Check the privileges of the user test.

obclient> SHOW GRANTS for test;
+-----------------------------------+
| Grants for test@%                 |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'test'      |
| GRANT SELECT ON `db1`.* TO 'test' |
+-----------------------------------+
2 rows in set

For more information about the GRANT statement, see GRANT.

Delete a user​

Use the DROP USER statement to delete a user.

Example:

Delete the user test.

obclient> DROP USER  test;
Query OK, 0 rows affected

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

Contents

Edit this page