Hsqldb 简明教程

HSQLDB - Alter Command

无论何时需要更改表或字段的名称、更改字段的顺序、更改字段的数据类型或任何表结构，你都可以使用 ALTER 命令。

Whenever there is a need to change the name of a table or a field, change the order of fields, change the datatype of fields, or any table structure, you can achieve the same using the ALTER command.

Example

让我们考虑一个解释 ALTER 命令使用不同场景的示例。

Let us consider an example that explains the ALTER command using different scenarios.

使用下面的查询创建名为 testalter_tbl 的表以及字段 id 和 name 。

Use the following query to create a table named testalter_tbl with the fields’ id and name.

//below given query is to create a table testalter_tbl table.
create table testalter_tbl(id INT, name VARCHAR(10));

//below given query is to verify the table structure testalter_tbl.
Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|   PUBLIC   |TESTALTER_TBL|     ID     |     4     |   INTEGER |     4      |
|   PUBLIC   |TESTALTER_TBL|    NAME    |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

Dropping or Adding a Column

无论何时想要从 HSQLDB 表中 DROP 现有列时，你都可以将 DROP 子句和 ALTER 命令一起使用。

Whenever you want to DROP an existing column from the HSQLDB table, then you can use the DROP clause along with the ALTER command.

使用以下查询从表 testalter_tbl 中删除一列 ( name )。

Use the following query to drop a column (name) from the table testalter_tbl.

ALTER TABLE testalter_tbl DROP name;

在成功执行以上查询后，你可以使用以下命令了解名称字段是否从表 testalter_tbl 中删除。

After successful execution of the above query, you can get to know if the name field is dropped from the table testalter_tbl using the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

执行上述命令后，您将收到以下输出。

After execution of the above command, you will receive the following output.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |      4    |   INTEGER |     4      |
+------------+-------------+------------+-----------+-----------+------------+

每当你想要向 HSQLDB 表中添加任何列时，你可以在 ALTER 命令中使用 ADD 子句。

Whenever you want to add any column into the HSQLDB table, you can use the ADD clause along with the ALTER command.

使用以下查询，向表 testalter_tbl 中添加一个名为 NAME 的列。

Use the following query to add a column named NAME to the table testalter_tbl.

ALTER TABLE testalter_tbl ADD name VARCHAR(10);

在成功执行以上查询后，你可以使用以下命令了解名称字段是否已添加到表 testalter_tbl 。

After successful execution of the above query, you can get to know if the name field is added to the table testalter_tbl using the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

在执行上述查询后，你将收到以下输出。

After execution of the above query, you will receive the following output.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |     4     |   INTEGER |     4      |
|  PUBLIC    |TESTALTER_TBL|     NAME   |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

Changing a Column Definition or Name

每当需要更改列定义时，请在 ALTER 命令中使用 MODIFY 或 CHANGE 子句。

Whenever there is a requirement of changing the column definition, use the MODIFY or CHANGE clause along with the ALTER command.

让我们考虑一个示例，来说明如何使用 CHANGE 子句。表 testalter_tbl 包含两个字段：id 和 name，分别具有数据类型 int 和 varchar。现在让我们尝试将 id 的数据类型从 INT 更改为 BIGINT。以下是进行更改的查询。

Let us consider an example that will explain how to use the CHANGE clause. The table testalter_tbl contains two fields - id and name - having datatypes int and varchar respectively. Now let us try to change the datatype of id from INT to BIGINT. Following is the query to make the change.

ALTER TABLE testalter_tbl CHANGE id id BIGINT;

在成功执行以上查询后，可以使用以下命令验证表结构。

After successful execution of the above query, the table structure can be verified using the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

执行上述命令后，您将收到以下输出。

After execution of the above command, you will receive the following output.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|      ID    |     4     |   BIGINT  |     4      |
|  PUBLIC    |TESTALTER_TBL|     NAME   |     12    |   VARCHAR |     10     |
+------------+-------------+------------+-----------+-----------+------------+

现在让我们尝试将表 testalter_tbl 中的列 NAME 的大小从 10 增加到 20。以下是使用 ALTER 命令和 MODIFY 子句实现此操作的查询。

Now let us try to increase the size of a column NAME from 10 to 20 in the testalter_tbl table. Following is the query to achieve this using the MODIFY clause along with the ALTER command.

ALTER TABLE testalter_tbl MODIFY name VARCHAR(20);

在成功执行以上查询后，可以使用以下命令验证表结构。

After successful execution of the above query, the table structure can be verified using the following command.

Select * From INFORMATION_SCHEMA.SYSTEM_COLUMNS as C Where C.TABLE_SCHEM =
   'PUBLIC' AND C.TABLE_NAME = 'TESTALTER_TBL';

执行上述命令后，您将收到以下输出。

After execution of the above command, you will receive the following output.

+------------+-------------+------------+-----------+-----------+------------+
|TABLE_SCHEM |  TABLE_NAME | COLUMN_NAME| DATA_TYPE | TYPE_NAME | COLUMN_SIZE|
+------------+-------------+------------+-----------+-----------+------------+
|  PUBLIC    |TESTALTER_TBL|    ID      |      4    |    BIGINT |     4      |
|  PUBLIC    |TESTALTER_TBL|    NAME    |     12    |   VARCHAR |    20      |
+------------+-------------+------------+-----------+-----------+------------+