Table operations

This topic describes how to create, view, modify, and delete database tables.

Create a table

Use the CREATE TABLE statement to create a table.

Example:

obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(3));

For more information on the syntax of the CREATE TABLE statement, see the "CREATE TABLE" topic in SQL Reference (MySQL Mode).

View a table

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

Example:

obclient> SHOW CREATE TABLE test;

Use the SHOW TABLES statement to view all tables in a specified database.

Example:

obclient> SHOW TABLES FROM my_db;

Modify a table

Use the ALTER TABLE statement to modify the structure of an existing table, including modifying the table name and table attributes, adding columns, modifying columns and attributes, and dropping columns.

Example:

• Change the name of Field c2 in Table test to c3 and modify its field type.

  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | Field | Type       | Null | Key | Default | Extra |
  +-------+------------+------+-----+---------+-------+
  | c1    | int(11)    | NO   | PRI | NULL    |       |
  | c2     | varchar(3) | YES  |     | NULL    |       |
  +-------+------------+------+-----+---------+-------+
  
  obclient> ALTER TABLE test CHANGE COLUMN c2 c3 CHAR(10);
  Query OK, 0 rows affected (0.08 sec)
  obclient> DESCRIBE test;
  +-------+----------+------+-----+---------+-------+
  | Field | Type     | Null | Key | Default | Extra |
  +-------+----------+------+-----+---------+-------+
  | c1    | int(11)  | NO   | PRI | NULL    |       |
  | c3     | char(10) | YES  |     | NULL    |       |
  +-------+----------+------+-----+---------+-------+
  

• Add and drop a column.

  • Before you add a column, run the DESCRIBE test``; command to view the table information.

    obclient> DESCRIBE test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | c1    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(3) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.01 sec)
    

  • Run the following command to add Column c3:

    obclient> ALTER TABLE test ADD c3 int; 
    Query OK, 0 rows affected (0.08 sec)
    

  • After you add a column, run the DESCRIBE test; command to view the table information.

    obclient> DESCRIBE test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | c1    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(3) | YES  |     | NULL    |       |
    | c3    | int(11)     | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    3 rows in set (0.00 sec)
    

  • Run the following command to drop Column c3:

    obclient> ALTER TABLE test DROP c3; 
    Query OK, 0 rows affected (0.08 sec)
    

  • After you drop a column, run the DESCRIBE test; command to view the table information.

    obclient> DESCRIBE test;
    +-------+-------------+------+-----+---------+-------+
    | Field | Type        | Null | Key | Default | Extra |
    +-------+-------------+------+-----+---------+-------+
    | c1    | int(11)     | NO   | PRI | NULL    |       |
    | c2    | varchar(50) | YES  |     | NULL    |       |
    +-------+-------------+------+-----+---------+-------+
    2 rows in set (0.00 sec)
    

• Set the number of replicas of Table test and add Column c5.

  obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD COLUMN c5 INT;
  Query OK, 0 rows affected (0.06 sec)
  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | Field | Type       | Null | Key | Default | Extra |
  +-------+------------+------+-----+---------+-------+
  | c1    | int(11)    | NO   | PRI | NULL    |       |
  | c2    | varchar(3) | YES  |     | NULL    |       |
  | c5    | int(11)    | YES  |     | NULL    |       |
  +-------+------------+------+-----+---------+-------+
  3 rows in set (0.00 sec)
  

For more information on the syntax of the ALTER TABLE statement, see the "ALTER TABLE" topic in SQL Reference (MySQL Mode).

Drop a table

Use the DROP TABLE statement to drop a table.

Example:

obclient> DROP TABLE test;

or

obclient> DROP TABLE IF EXISTS test;

For more information on the syntax of the DROP TABLE statement, see the "DROP TABLE" topic in SQL Reference (MySQL Mode).