H2 Database 简明教程

H2 Database - Create

CREATE 是用于在 H2 数据库服务器中创建表、模式、序列、视图和用户的通用 SQL 命令。

CREATE is a generic SQL command used to create Tables, Schemas, Sequences, Views, and Users in H2 Database server.

Create Table

创建表是用于在当前数据库中创建用户自定义表的命令。

Create Table is a command used to create a user-defined table in the current database.

Syntax

以下是创建表命令的通用语法。

Following is the generic syntax for the Create Table command.

CREATE [ CACHED | MEMORY ] [ TEMP | [ GLOBAL | LOCAL ] TEMPORARY ]
TABLE [ IF NOT EXISTS ] name
[ ( { columnDefinition | constraint } [,...] ) ]
[ ENGINE tableEngineName [ WITH tableEngineParamName [,...] ] ]
[ NOT PERSISTENT ] [ TRANSACTIONAL ]
[ AS select ]

通过使用创建表命令的通用语法，我们可以创建不同类型的表，如缓存表、内存表和临时表。以下是描述给定语法中不同子句的列表。

By using the generic syntax of the Create Table command, we can create different types of tables such as cached tables, memory tables, and temporary tables. Following is the list to describe different clauses from the given syntax.

CACHED − The cached tables are the default type for regular tables. This means the number of rows is not limited by the main memory.
MEMORY − The memory tables are the default type for temporary tables. This means the memory tables should not get too large and the index data is kept in the main memory.
TEMPORARY − Temporary tables are deleted while closing or opening a database. Basically, temporary tables are of two types − GLOBAL type − Accessible by all connections. LOCAL type − Accessible by the current connection. The default type for temporary tables is global type. Indexes of temporary tables are kept in the main memory, unless the temporary table is created using CREATE CACHED TABLE.
ENGINE − The ENGINE option is only required when custom table implementations are used.
NOT PERSISTENT − It is a modifier to keep the complete table data in-memory and all rows are lost when the database is closed.
TRANSACTIONAL − It is a keyword that commits an open transaction and this command supports only temporary tables.

Example

在这个示例中，让我们使用以下给定数据创建一个名为 tutorials_tbl 的表。

In this example, let us create a table named tutorials_tbl using the following given data.

Sr.No

Column Name

Data Type

Int

Title

Varchar(50)

Author

Varchar(20)

Submission_date

Date

以下查询用于创建表 tutorials_tbl 以及给定的列数据。

The following query is used to create a table tutorials_tbl along with the given column data.

CREATE TABLE tutorials_tbl (
   id INT NOT NULL,
   title VARCHAR(50) NOT NULL,
   author VARCHAR(20) NOT NULL,
   submission_date DATE
);

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

The above query produces the following output.

(0) rows effected

Create Schema

创建模式是一个命令，用于在特定授权下（在当前注册的用户下）创建用户相关的模式。

Create Schema is a command used to create a user-dependent schema under a particular authorization (under the currently registered user).

Syntax

以下是创建模式命令的通用语法。

Following is the generic syntax of the Create Schema command.

CREATE SCHEMA [ IF NOT EXISTS ] name [ AUTHORIZATION ownerUserName ]

在上述通用语法中，AUTHORIZATION 是用于提供各自用户名的一个关键字。此命令是可选的，这意味着如果我们没有提供用户名，则它将考虑当前用户。执行此命令的用户必须拥有管理员权限以及所有者权限。

In the above generic syntax, AUTHORIZATION is a keyword used to provide the respective user name. This command is optional which means if we are not providing the user name, then it will consider the current user. The user that executes the command must have admin rights, as well as the owner.

此命令会提交此连接中的未决事务。

This command commits an open transaction in this connection.

Example

在此示例中，让我们使用以下命令在 SA 用户下创建一个名为 test_schema 的模式。

In this example, let us create a schema named test_schema under SA user, using the following command.

CREATE SCHEMA test_schema AUTHORIZATION sa;

以上命令会产生以下输出。

The above command produces the following output.

(0) rows effected

Create Sequence

序列是一个用于通过遵循 id 或任何随机列值的序列生成数字的概念。

Sequence is concept which is used to generate a number by following a sequence for id or any random column values.

Syntax

以下是 create sequence 命令的通用语法。

Following is the generic syntax of the create sequence command.

CREATE SEQUENCE [ IF NOT EXISTS ] newSequenceName [ START WITH long ]
[ INCREMENT BY long ]
[ MINVALUE long | NOMINVALUE | NO MINVALUE ]
[ MAXVALUE long | NOMAXVALUE | NO MAXVALUE ]
[ CYCLE long | NOCYCLE | NO CYCLE ]
[ CACHE long | NOCACHE | NO CACHE ]

此通用语法用于创建序列。序列的数据类型为 BIGINT 。在此序列中，即使事务回滚，值也不会被重新使用。

This generic syntax is used to create a sequence. The datatype of a sequence is BIGINT. In this the sequence, values are never re-used, even when the transaction is roll backed.

Example

在此示例中，让我们使用以下查询创建一个名为 SEQ_ID 的序列。

In this example, let us create a sequence named SEQ_ID, using the following query.

CREATE SEQUENCE SEQ_ID;

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

The above query produces the following output.

(0) rows effected