---

slug: /basic-sql-operations
---

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

```sql
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974111).

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

```sql
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`.

```sql
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974140).

### View tables

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

Examples:

* View the table creation statement for the table `test`.

  ```sql
  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`.

  ```sql
  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.

  ```sql
  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`.

  ```sql
  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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974126).

### Delete a table

Use the `DROP TABLE` statement to delete a table.

Example: Delete the table `test`.

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

For more information about the `DROP TABLE` statement, see [DROP TABLE](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974139).

## 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`.

```sql
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974165).

### View indexes

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

Example: View index information for the table `test`.

```sql
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`.

```sql
obclient> DROP INDEX test_index ON test;
Query OK, 0 rows affected
```

For more information about the `DROP INDEX` statement, see [DROP INDEX](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974168).

## Insert data

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

Examples:

* Create a table `t1` and insert one row of data.

  ```sql
  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`.

  ```sql
  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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974718).

## 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`.

  ```sql
  /*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.

  ```sql
  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`.

  ```sql
  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`.

  ```sql
  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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974138).

## 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`.

  ```sql
  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.

  ```sql
  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`.

  ```sql
  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`.

  ```sql
  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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974152).

## 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`.

  ```sql
  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.

  ```sql
  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'`.

  ```sql
  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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974942).

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

```sql
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001971667).

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

```sql
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001971667).

## Create a user

Use the `CREATE USER` statement to create a user.

Example:

Create a user named `test`.

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

For more information about the `CREATE USER` statement, see [CREATE USER](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974176).

## 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`.

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

Check the privileges of the user `test`.

```shell
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](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974144).

## Delete a user

Use the `DROP USER` statement to delete a user.

Example:

Delete the user `test`.
     
```shell
obclient> DROP USER  test;
Query OK, 0 rows affected
```

For more information about the `DROP USER` statement, see [DROP USER](https://en.oceanbase.com/docs/common-oceanbase-database-10000000001974172).