Table-level operations

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

Create a table

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

Example:

obclient> CREATE TABLE test (c1 int primary key, c2 VARCHAR(3)) REPLICA_NUM = 3, PRIMARY_ZONE = 'zone1';

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

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:

• Modify the type of Field c2 in Table test.

  obclient> DESCRIBE test;
  +-------+-------------+------+-----+---------+-------+
  | FIELD | TYPE        | NULL | KEY | DEFAULT | EXTRA |
  +-------+-------------+------+-----+---------+-------+
  | C1    | NUMBER(38)  | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(3) | YES  | NULL | NULL    | NULL  |
  +-------+-------------+------+-----+---------+-------+
  2 rows in set (0.02 sec)
  
  obclient> ALTER TABLE test MODIFY c2 CHAR(10);
  Query OK, 0 rows affected (0.05 sec)
  
  obclient> DESCRIBE test;
  +-------+------------+------+-----+---------+-------+
  | FIELD | TYPE       | NULL | KEY | DEFAULT | EXTRA |
  +-------+------------+------+-----+---------+-------+
  | C1    | NUMBER(38) | NO   | PRI | NULL    | NULL  |
  | C2    | CHAR(10)   | YES  | NULL | NULL    | NULL  |
  +-------+------------+------+-----+---------+-------+
  2 rows in set (0.00 sec)
  

• Add and drop a column.

  1. 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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
     | C2    | VARCHAR2(3) | YES  | NULL | NULL    | NULL  |
     +-------+--------------+------+-----+---------+-------+
     2 rows in set (0.02 sec)
     

  2. Run the following command to add Column c3:

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

  3. 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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
     | C2    | VARCHAR2(3) | YES  | NULL | NULL    | NULL  |
     | C3    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
     +-------+--------------+------+-----+---------+-------+
     3 rows in set (0.00 sec)
     

  4. Run the following command to drop Column c3:

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

  5. 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    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
     | C2    | VARCHAR2(3) | YES  | NULL | NULL    | NULL  |
     +-------+--------------+------+-----+---------+-------+
     2 rows in set (0.01 sec)
     

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

  obclient> ALTER TABLE test SET REPLICA_NUM=2, ADD c5 INT;
  Query OK, 0 rows affected (0.02 sec)
  
  obclient> DESCRIBE test;
  +-------+--------------+------+-----+---------+-------+
  | FIELD | TYPE         | NULL | KEY | DEFAULT | EXTRA |
  +-------+--------------+------+-----+---------+-------+
  | C1    | NUMBER(38)   | NO   | PRI | NULL    | NULL  |
  | C2    | VARCHAR2(3) | YES  | NULL | NULL    | NULL  |
  | C5    | NUMBER(38)   | YES  | NULL | NULL    | NULL  |
  +-------+--------------+------+-----+---------+-------+
  3 rows in set (0.01 sec)
  

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

Drop a table

Use the DROP TABLE statement to delete a table.

Example:

obclient> DROP TABLE test; 
Query OK, 0 rows affected (0.04 sec)

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