H2 Database 简明教程

H2 Database - Alter

ALTER 是一个用于通过向 alter 命令中添加不同的子句从而改变表结构的命令。根据场景，我们需要向 alter 命令添加相应的子句。在此章节中，我们将讨论 alter 命令的多种场景。

ALTER is a command used to change the table structure by adding different clauses to the alter command. Based on the scenario, we need to add respective clause to the alter command. In this chapter, we will discuss various scenarios of alter command.

Alter Table Add

Alter Table Add 是一个用于向表中添加一个新列和相应数据类型的一个命令。此命令将提交此连接中的事务。

Alter Table Add is a command used to add a new column to a table along with the respective data type. This command commits the transaction in this connection.

Syntax

以下是 Alter Table Add 命令的通用语法。

Following is the generic syntax of the Alter Table Add command.

ALTER TABLE [ IF EXISTS ] tableName ADD [ COLUMN ]
{ [ IF NOT EXISTS ] columnDefinition [ { BEFORE | AFTER } columnName ]
   | ( { columnDefinition } [,...] ) }

Example

在此实例中，我们将向表 tutorials_tbl 中添加一个新列 start_date 。start_date 的数据类型是 Date。以下是用于添加一个新列的查询。

In this example, we will add a new column start_date to the table tutorials_tbl. The datatype for start_date is Date. Following is the query to add a new column.

ALTER TABLE tutorials_tbl ADD start_date DATE;

以上查询将产生以下输出。

The above query produces the following output.

(6) rows effected

Alter Table Add Constraint

Alter table add constraint 是一个用于向表中添加不同约束的命令，例如果键、外键、非空等等。

Alter table add constraint is a command used to add different constraints to the table such as primary key, foreign key, not null, etc.

如果所需的索引尚不存在，则系统将会自动创建它们。不可能禁用对唯一约束的检查。此命令会提交此连接中的一个打开事务。

The required indexes are automatically created if they don’t exist yet. It is not possible to disable checking for unique constraint. This command commits an open transaction in this connection.

Syntax

以下是 Alter table add constraint 命令的通用语法。

Following is the generic syntax of the Alter table add constraint command.

ALTER TABLE [ IF EXISTS ] tableName ADD constraint [ CHECK | NOCHECK ]

Example

在此实例中，让我们使用以下查询向表 tutorials_tbl 的列 id 中添加一个主键约束 (tutorials_tbl_pk) 。

In this example, let us add a primary key constraint (tutorials_tbl_pk) to the column id of the table tutorials_tbl, using the following query.

ALTER TABLE tutorials_tbl ADD CONSTRAINT tutorials_tbl_pk PRIMARYKEY(id);

以上查询将产生以下输出。

The above query produces the following output.

(6) row (s) effected

Alter Table Rename Constraint

此命令用于重命名特定关系表的约束名。此命令会提交此连接中的一个打开事务。

This command is used to rename the constraint name of a particular relation table. This command commits an open transaction in this connection.

Syntax

以下是 Alter Table Rename Constraint 命令的通用语法。

Following is the generic syntax of the Alter Table Rename Constraint command.

ALTER TABLE [ IF EXISTS ] tableName RENAME oldConstraintName TO newConstraintName

在使用此语法时，请确保旧的约束名与相应的列一同存在。

While using this syntax, make sure that the old constraint name should exist with the respective column.

Example

在此实例中，我们将表 tutorials_tbl 的主键约束名从 tutorials_tbl_pk 改为 tutorials_tbl_pk_constraint 。以下是用于执行此操作的查询。

In this example, we will change the primary key constraint name of the table tutorials_tbl from tutorials_tbl_pk to tutorials_tbl_pk_constraint. Following is the query to do so.

ALTER TABLE tutorials_tbl RENAME CONSTRAINT
tutorials_tbl_pk TO tutorials_tbl_pk_constraint;

以上查询将产生以下输出。

The above query produces the following output.

(1) row (s) effected

Alter Table Alter Column

此命令用于改变特定表中列的结构和属性。改变属性指的是改变列的数据类型、重命名列、改变标识值，或者改变选择性。

This command is used to change the structure and properties of the column of a particular table. Changing the properties means changing the datatype of a column, rename a column, change the identity value, or change the selectivity.

Syntax

以下是修改表修改列命令的通用语法。

Following is the generic syntax of the Alter Table Alter Column command.

ALTER TABLE [ IF EXISTS ] tableName ALTER COLUMN columnName
{ { dataType [ DEFAULT expression ] [ [ NOT ] NULL ] [ AUTO_INCREMENT | IDENTITY ] }
| { RENAME TO name }
| { RESTART WITH long }
| { SELECTIVITY int }
| { SET DEFAULT expression }
| { SET NULL }
| { SET NOT NULL } }

在以上语法中 −

In the above syntax −

  1. RESTART − command changes the next value of an auto increment column.

  2. SELECTIVITY − command sets the selectivity (1-100) for a column. Based on the selectivity value we can image the value of the column.

  3. SET DEFAULT − changes the default value of a column.

  4. SET NULL − sets the column to allow NULL.

  5. SET NOT NULL − sets the column to allow NOT NULL.

Example

在此示例中，我们将使用以下查询将 tutorials_tbl 表的列从 Title 重命名为 Tutorial_Title 。

In this example, we will rename the column of the table tutorials_tbl from Title to Tutorial_Title using the following query.

ALTER TABLE tutorials_tbl ALTER COLUMN title RENAME TO tutorial_title;

以上查询将产生以下输出。

The above query produces the following output.

(0) row(s) effected

以类似的方式，我们可以对 ALTER 命令执行不同的方案。

In a similar way, we can perform different scenarios with the ALTER command.