 | 一時期／支部 2. SQL Language |  |
| 一時期／支部 1. Running and Using HyperSQL |  | 一時期／支部 3. Schemas and Database 反対するs |
|
このページはEtoJ逐語翻訳フィルタによって翻訳生成されました。 |
| | 一時期／支部 2. SQL Language | |
| 一時期／支部 1. Running and Using HyperSQL | | 一時期／支部 3. Schemas and Database 反対するs |
（米）棚上げする／（英）提議する of Contents
The SQL language consists of 声明s for different 操作／手術s. HyperSQL 2.x supports the dialect of SQL defined progressively by ISO (also ANSI) SQL 基準s 92, 1999, 2003, 2008, 2011, 2016 and 2023. This means the syntax 明示するd by the 基準 text is 受託するd for any supported 操作／手術. Almost all features of SQL-92 up to 前進するd Level are supported, 同様に as the 付加 features that （不足などを）補う the SQL:2023 核心 and many optional features of this 基準.
At the time of this 解放（する）, HyperSQL supports the widest 範囲 of SQL 基準 features の中で all open source RDBMS.
さまざまな 一時期／支部s of this guide 名簿（に載せる）／表（にあげる） the supported syntax. When 令状ing or 変えるing 存在するing SQL DDL (Data 鮮明度／定義 Language), DML (Data 巧みな操作 Language) or DQL (Data Query Language) 声明s for HSQLDB, you should 協議する the supported syntax and 修正する the 声明s accordingly.
Over 300 words are reserved by the 基準 and should not be used
as （米）棚上げする／（英）提議する or column 指名するs. For example, the word POSITION is reserved as it
is a 機能（する）／行事 defined by the 基準s with a 類似の 役割 as
String.indexOf(String) in Java. By default,
HyperSQL does not 妨げる you from using a reserved word if it does not
support its use or can distinguish it. For example, CUBE is a reserved
word for a feature that is supported by HyperSQL from 見解／翻訳／版 2.5.1.
Before this 見解／翻訳／版, CUBE was 許すd as a （米）棚上げする／（英）提議する or column 指名する but it is
no longer 許すd. You should 避ける using such 指名するs as 未来 見解／翻訳／版s of
HyperSQL are likely to support the reserved words and may 拒絶する your
（米）棚上げする／（英）提議する 鮮明度／定義s or queries. The 十分な 名簿（に載せる）／表（にあげる） of SQL reserved words is in
the 虫垂 名簿（に載せる）／表（にあげる）s of Keywords. You
can 始める，決める a 所有物／資産／財産 to disallow the use of reserved keywords for 指名するs of
（米）棚上げする／（英）提議するs and other database 反対するs. There are several other 使用者-defined
所有物／資産／財産s to 支配（する）／統制する the strict 使用／適用 of the SQL 基準 in
different areas.
If you have to use a reserved keyword as the 指名する of a database 反対する, you can enclose it in 二塁打 引用するs.
HyperSQL also supports enhancements with keywords and 表現s
that are not part of the SQL 基準. 表現s such as SELECT
TOP 5 FROM .., SELECT LIMIT 0 10 FROM ... or
DROP TABLE mytable IF EXISTS are の中で such
建設するs.
Many 調書をとる／予約するs cover SQL 基準 syntax and can be 協議するd.
In HyperSQL 見解／翻訳／版 2, all features of JDBC4 that 適用する to the
能力s of HSQLDB are fully supported. The 関連した JDBC classes are
完全に 文書d with 付加 解明s and HyperSQL 明確な／細部
comments. See the JavaDoc for the
org.hsqldb.jdbc.* classes.
The に引き続いて sections 名簿（に載せる）／表（にあげる） the keywords that start さまざまな SQL 声明s grouped by their 機能（する）／行事.
鮮明度／定義 声明s create, 修正する, or 除去する database 反対するs. （米）棚上げする／（英）提議するs and 見解（をとる）s are 反対するs that 含む／封じ込める data. There are other types of 反対するs that do not 含む／封じ込める data. These 声明s are covered in the Schemas and Database 反対するs 一時期／支部.
CREATE
Followed by { SCHEMA | TABLE | VIEW | SEQUENCE | PROCEDURE | FUNCTION | USER | ROLE | ... }, the keyword is used to create the database 反対するs.
ALTER
Followed by the same keywords as CREATE, the keyword is used to 修正する the 反対する.
DROP
Followed by the same keywords as above, the keyword is used to 除去する the 反対する. If the 反対する 含む／封じ込めるs data, the data is 除去するd too.
GRANT
Followed by the 指名する of a 役割 or 特権, the keyword 割り当てるs a 役割 or gives 許可s to a USER or 役割.
REVOKE
Followed by the 指名する of a 役割 or 特権, REVOKE is the opposite of GRANT.
COMMENT ON
Followed by the same keywords as CREATE, the keyword is used to 追加する a text comment to TABLE, VIEW, COLUMN, ROUTINE, and TRIGGER 反対するs.
EXPLAIN REFERENCES
These keywords are followed by TO or FROM to 名簿（に載せる）／表（にあげる） the other database 反対するs that 言及／関連 the given 反対する, or 副／悪徳行為 versa.
DECLARE
This is used for 宣言するing 一時的な 開会／開廷／会期 （米）棚上げする／（英）提議するs and variables.
Data 巧みな操作 声明s 追加する, update, or 削除する data in （米）棚上げする／（英）提議するs and 見解（をとる）s. These 声明s are covered in the Data 接近 and Change 一時期／支部.
INSERT
挿入するs one or more 列／漕ぐ／騒動s into a （米）棚上げする／（英）提議する or 見解（をとる）.
UPDATE
Updates one or more 列／漕ぐ／騒動s in a （米）棚上げする／（英）提議する or 見解（をとる）.
DELETE
削除するs one or more 列／漕ぐ／騒動s from a （米）棚上げする／（英）提議する or 見解（をとる）.
TRUNCATE
削除するs all the 列／漕ぐ／騒動s in a （米）棚上げする／（英）提議する.
MERGE
成し遂げるs a 条件付きの INSERT, UPDATE or DELETE on a （米）棚上げする／（英）提議する or 見解（をとる） using the data given in the 声明.
Data query 声明s retrieve and 連合させる data from （米）棚上げする／（英）提議するs and 見解（をとる）s and return result 始める，決めるs. These 声明s are covered in the Data 接近 and Change 一時期／支部.
SELECT
Returns a result 始める，決める formed from a subset of 列／漕ぐ／騒動s and columns in one or more （米）棚上げする／（英）提議するs or 見解（をとる）s.
VALUES
Returns a result 始める，決める formed from constant values.
WITH ...
This keyword starts a 一連の SELECT 声明s that form a query. The first SELECTs 行為／法令／行動する as subqueries for the final SELECT 声明 in the same query.
EXPLAIN PLAN
These keywords are followed by the 十分な text of any DQL or DML 声明. The result 始める，決める shows the anatomy of the given DQL or DML 声明, 含むing the 索引s used to 接近 the （米）棚上げする／（英）提議するs.
CALL
Calls a 手続き or 機能（する）／行事. Calling a 機能（する）／行事 can return a result 始める，決める or a value, while calling a 手続き can return one or more result 始める，決めるs and values at the same time. This 声明 is covered in the SQL-Invoked 決まりきった仕事s 一時期／支部.
SET
The SET 声明 has many variations and is used for setting the values of the general 所有物／資産／財産s of the database or the 現在の 開会／開廷／会期. Usage of the SET 声明 for the database is covered in the System 管理／経営 一時期／支部. Usage for the 開会／開廷／会期 is covered in the 開会／開廷／会期s and 処理／取引s 一時期／支部.
General 操作／手術s on the database 含む backup, 検問所, and other 操作／手術s. These 声明s are covered in 詳細（に述べる） in the System 管理／経営 一時期／支部.
BACKUP
Creates a backup of the database in a 的 directory.
PERFORM
含むs 命令（する）s to 輸出（する） and 輸入する SQL scripts from / to the database. Also 含むs a 命令（する） to check the consistency of the 索引s.
SCRIPT
Creates a script of SQL 声明s that creates the database 反対するs and settings.
CHECKPOINT
Saves all the changes to the database up to this point to disk とじ込み／提出するs.
SHUTDOWN
Shuts 負かす／撃墜する the database after saving all the changes.
These 声明s are used in a 開会／開廷／会期 to start, end or 支配（する）／統制する 処理／取引s. They are covered in the 開会／開廷／会期s and 処理／取引s 一時期／支部.
（米ソ間の）戦略兵器削減交渉 TRANSACTION
This 声明 始めるs a new 処理／取引 with the given 処理／取引 特徴
SET TRANSACTION
Introduces one of more 特徴 for the next 処理／取引.
COMMIT
Commits the changes to data made in the 現在の 処理／取引.
ROLLBACK
Rolls 支援する the changes to data made in the 現在の 処理／取引. It is also possible to roll 支援する to a savepoint.
SAVEPOINT
記録，記録的な／記録するs a point in the 現在の 処理／取引 so that 未来 changes can be rolled 支援する to this point.
RELEASE SAVEPOINT
解放（する）s an 存在するing savepoint.
LOCK
Locks a 始める，決める of （米）棚上げする／（英）提議するs for 処理／取引 支配（する）／統制する.
CONNECT
Starts a new 開会／開廷／会期 and continues 操作／手術s in this 開会／開廷／会期.
DISCONNECT
Ends the 現在の 開会／開廷／会期.
Any SQL 声明 can 含む comments. The comments are stripped before the 声明 is 遂行する／発効させるd.
SQL style line comments start with two dashes
-- and 延長する to the end of the line.
C style comments can cover part of the line or 多重の lines.
They start with /* and end with
*/.
The 団体／死体 of 使用者-defined SQL 手続きs and 機能（する）／行事s (collectively called 決まりきった仕事s) may 含む／封じ込める several other types of 声明s and keywords in 新規加入 to DML and DQL 声明s. These 含む: BEGIN and END for 封鎖するs; FOR, WHILE and REPEAT 宙返り飛行s; IF, ELSE and ELSEIF 封鎖するs; SIGNAL and RESIGNAL 声明s for 扱うing exceptions.
These 声明s are covered in 詳細（に述べる） in the SQL-Invoked 決まりきった仕事s 一時期／支部.
All data is 蓄える／店d in （米）棚上げする／（英）提議するs. Therefore, creating a database 要求するs defining the （米）棚上げする／（英）提議するs and their columns. The SQL 基準 supports 一時的な （米）棚上げする／（英）提議するs, which are for 一時的な data managed by each 開会／開廷／会期, and 永久の base （米）棚上げする／（英）提議するs, which are for 執拗な data 株d by different 開会／開廷／会期s.
A HyperSQL database can be an all-in-memory mem: database with no (a)自動的な／(n)自動拳銃 persistence, or a とじ込み／提出する-based, 執拗な とじ込み／提出する: database.
基準 SQL is not 事例／患者 極度の慎重さを要する, except when 指名するs of 反対するs
are enclosed in 二塁打-引用するs. SQL keywords can be written in any 事例／患者;
for example, sElect, SELECT and
select are all 許すd and 変えるd to uppercase.
Identifiers, such as 指名するs of （米）棚上げする／（英）提議するs, columns and other 反対するs defined
by the 使用者, are also 変えるd to uppercase. For example,
myTable, MyTable and
MYTABLE all 言及する to the same （米）棚上げする／（英）提議する and are 蓄える／店d in
the database in the 事例／患者-normal form, which is all uppercase for
unquoted identifiers. When the 指名する of an 反対する is enclosed in 二塁打
引用するs when it is created, the exact 指名する is used as the 事例／患者-normal
form and it must be 言及／関連d with the exact same 二塁打-引用するd string.
For example, "myTable" and
"MYTABLE" are different （米）棚上げする／（英）提議するs. When the
二塁打-引用するd 指名する is all-uppercase, it can be 言及／関連d in any 事例／患者;
"MYTABLE" is the same as myTable
and MyTable because they are all 変えるd to
MYTABLE.
HyperSQL supports the 基準 鮮明度／定義 of 執拗な base （米）棚上げする／（英）提議する, but defines three types によれば the way the data is 蓄える／店d. These are MEMORY （米）棚上げする／（英）提議するs, CACHED （米）棚上げする／（英）提議するs, and TEXT （米）棚上げする／（英）提議するs.
Memory （米）棚上げする／（英）提議するs are the default type when the CREATE TABLE 命令（する）
is used. Their data is held 完全に in memory. In とじ込み／提出する-based databases,
MEMORY （米）棚上げする／（英）提議するs are 執拗な and any change to their structure or
contents is written to the *.スピードを出す／記録につける and
*.script とじ込み／提出するs. The *.script
とじ込み／提出する and the *.スピードを出す／記録につける とじ込み／提出する are read the next time the
database is opened, and the MEMORY （米）棚上げする／（英）提議するs are recreated with all the
data. This 過程 may take a long time if the database is larger than
tens of megabytes. When the database is shutdown, all the data is
saved.
CACHED （米）棚上げする／（英）提議するs are created with the CREATE CACHED TABLE 命令（する）. Only part of their data or 索引s is held in memory, 許すing large （米）棚上げする／（英）提議するs that would さもなければ （問題を）取り上げる to several hundred megabytes of memory. Another advantage of （武器などの）隠匿場所d （米）棚上げする／（英）提議するs is that the database engine takes いっそう少なく time to start up when a （武器などの）隠匿場所d （米）棚上げする／（英）提議する is used for large 量s of data. The disadvantage of （武器などの）隠匿場所d （米）棚上げする／（英）提議するs is a 削減 in 速度(を上げる）. Do not use （武器などの）隠匿場所d （米）棚上げする／（英）提議するs if your data 始める，決める is 比較して small. In an 使用／適用 with some small （米）棚上げする／（英）提議するs and some large ones, it is better to use the default, MEMORY 方式 for the small （米）棚上げする／（英）提議するs.
TEXT （米）棚上げする／（英）提議するs use a CSV (Comma Separated Value) or other delimited text とじ込み／提出する as the source of their data. You can 明示する an 存在するing CSV とじ込み／提出する, such as a 捨てる from another database or program, as the source of a TEXT （米）棚上げする／（英）提議する. Alternatively, you can 明示する an empty とじ込み／提出する to be filled with data by the database engine. TEXT （米）棚上げする／（英）提議するs are efficient in memory usage as they （武器などの）隠匿場所 only part of the text data and all of the 索引s. The Text （米）棚上げする／（英）提議する data source can always be 再任命するd to a different とじ込み／提出する if necessary. The 命令（する）s are needed to 始める，決める up a TEXT （米）棚上げする／（英）提議する as 詳細（に述べる）d in the Text （米）棚上げする／（英）提議するs 一時期／支部.
With all-in-memory mem: databases, both MEMORY （米）棚上げする／（英）提議する and CACHED （米）棚上げする／（英）提議する 宣言s are 扱う／治療するd as 宣言s for MEMORY （米）棚上げする／（英）提議するs which last only for the duration of the Java 過程. In the 最新の 見解／翻訳／版s of HyperSQL, TEXT （米）棚上げする／（英）提議する 宣言s are 許すd in all-in-memory databases.
The default type of （米）棚上げする／（英）提議するs resulting from 未来 CREATE TABLE 声明s can be 明示するd with the SQL 命令（する）:
SET DATABASE DEFAULT TABLE TYPE { CACHED | MEMORY };
The type of an 存在するing （米）棚上げする／（英）提議する can be changed with the SQL 命令（する）:
SET TABLE <（米）棚上げする／（英）提議する 指名する> TYPE { CACHED | MEMORY };
SQL 声明s such as INSERT or SELECT 接近 different types of （米）棚上げする／（英）提議するs uniformly. No change to 声明s is needed to 接近 different types of （米）棚上げする／（英）提議する.
Data in TEMPORARY （米）棚上げする／（英）提議するs is not saved and lasts only for the lifetime of the 開会／開廷／会期. The contents of each TEMP （米）棚上げする／（英）提議する are 明白な only from the 開会／開廷／会期 that is used to 居住させる it.
HyperSQL supports two types of 一時的な （米）棚上げする／（英）提議するs.
The GLOBAL TEMPORARY type is a schema 反対する.
It is created with the CREATE GLOBAL TEMPORARY TABLE
声明. The 鮮明度／定義 of the （米）棚上げする／（英）提議する 固執するs, and each 開会／開廷／会期 has
接近 to the （米）棚上げする／（英）提議する. But each 開会／開廷／会期 sees its own copy of the （米）棚上げする／（英）提議する,
which is empty at the beginning of the 開会／開廷／会期.
The LOCAL TEMPORARY type is not a schema
反対する. It is created with the DECLARE LOCAL TEMPORARY
TABLE 声明. The （米）棚上げする／（英）提議する 鮮明度／定義 lasts only for the
duration of the 開会／開廷／会期 and is not 固執するd in the database. The （米）棚上げする／（英）提議する
can be 宣言するd in the middle of a 処理／取引 without committing the
処理／取引. If a schema 指名する is needed to 言及／関連 these （米）棚上げする／（英）提議するs in a
given SQL 声明, the pseudo schema 指名する SESSION
can be used.
When the 開会／開廷／会期 commits, the contents of all 一時的な （米）棚上げする／（英）提議するs are
（疑いを）晴らすd by default. If the （米）棚上げする／（英）提議する 鮮明度／定義 声明 含むs
ON COMMIT PRESERVE ROWS, then the contents are kept
when a commit takes place.
The 列／漕ぐ／騒動s in 一時的な （米）棚上げする／（英）提議するs are 蓄える／店d in memory by default. If
the hsqldb.result_max_memory_rows 所有物／資産／財産 has been
始める，決める or the SET SESSION RESULT MEMORY ROWS <列／漕ぐ／騒動
count> has been 明示するd, （米）棚上げする／（英）提議するs with 列／漕ぐ／騒動 count above the
setting are 蓄える／店d on disk.
The SQL 基準 is 堅固に typed and 完全に type-安全な. It supports the に引き続いて basic types, which are all supported by HyperSQL.
Numeric types TINYINT, SMALLINT, INTEGER and BIGINT are types with 直す／買収する，八百長をするd binary precision. These types are more efficient to 蓄える／店 and retrieve. NUMERIC and DECIMAL are types with 使用者-defined decimal precision. They can be used with 無 規模 to 蓄える／店 very large integers, or with a 非，不，無-無 規模 to 蓄える／店 decimal fractions. The DOUBLE type is a 64-bit, approximate floating point types. HyperSQL even 許すs you to 蓄える／店 infinity in this type.
The BOOLEAN type is for 論理（学）の values and can 持つ／拘留する TRUE, FALSE or UNKNOWN. Although HyperSQL 許すs you to use one and 無 in assignment or comparison, you should use the 基準 values for this type.
Character string types are CHAR(L), VARCHAR(L) and CLOB (here, L stands for length parameter, an integer). CHAR is for 直す／買収する，八百長をするd width strings and any string that is 割り当てるd to this type is padded with spaces at the end. If you use CHAR without the length L, then it is 解釈する／通訳するd as a 選び出す／独身 character string. Do not use this type for general 貯蔵 of strings. Use VARCHAR(L) for general strings. There are only memory 限界s and 業績／成果 関わりあい／含蓄s for the 最大限 length of VARCHAR(L). If the strings are larger than a few kilobytes, consider using CLOB. The CLOB types is a better choice for very long strings. Do not use this type for short strings as there are 業績／成果 関わりあい／含蓄s. By default LONGVARCHAR is a synonym for a long VARCHAR and can be used without 明示するing the size. You can 始める，決める LONGVARCHAR to 地図／計画する to CLOB, with the sql.longvar_is_lob 関係 所有物／資産／財産 or the SET DATABASE SQL LONGVAR IS LOB TRUE 声明.
Binary string types are BINARY(L), VARBINARY(L) and BLOB. Do not use BINARY(L) unless you are 蓄える／店ing 直す／買収する，八百長をするd length binary strings. This type pads short binary strings with 無 bytes. BINARY without the length L means a 選び出す／独身 byte. Use VARBINARY(L) for general binary strings, and BLOB for large binary 反対するs. You should 適用する the same considerations as with the character string types. By default, LONGVARBINARY is a synonym for a long VARBINARY and can be used without 明示するing the size. You can 始める，決める LONGVARBINARY to 地図／計画する to BLOB, with the sql.longvar_is_lob 関係 所有物／資産／財産 or the SET DATABASE SQL LONGVAR IS LOB TRUE 声明.
The BIT(L) and BITVARYING(L) types are for bit 地図／計画するs. Do not use them for other types of data. BIT without the length L argument means a 選び出す／独身 bit and is いつかs used as a 論理（学）の type. Use BOOLEAN instead of this type.
The UUID type is for UUID (also called GUID) values. The value is 蓄える／店d as BINARY. UUID character strings, 同様に as BINARY strings, can be used to 挿入する or to compare.
The datetime types DATE, TIME, and TIMESTAMP, together with their WITH TIME ZONE variations are 利用できる. Read the 詳細（に述べる）s in this 一時期／支部 on how to use these types.
The INTERVAL type is very powerful when used together with the
datetime types. This is very 平易な to use, but is supported おもに by
企業 database systems. 公式文書，認める that 機能（する）／行事s that 追加する days or
months to datetime values are not really a 代用品，人 for the INTERVAL
type. 表現s such as (datecol - 7 DAY) >
CURRENT_DATE are optimised to use 索引s when it is
possible, while the 同等（の） 機能（する）／行事 calls are not
optimised.
The OTHER type is for 貯蔵 of Java 反対するs. If your 反対するs are large, serialize them in your 使用／適用 and 蓄える／店 them as BLOB in the database.
The ARRAY type supports all base types except LOB and OTHER types. ARRAY data 反対するs are held in memory while 存在 過程d. It is therefore not recommended to 蓄える／店 more than about a thousand 反対するs in an ARRAY in normal 操作／手術s with disk-based databases. For specialised 使用／適用s, use ARRAY with as many elements as your memory 配分 can support.
HyperSQL 2.7 has several compatibility 方式s which 許す the type 指名するs that are used by other RDBMS to be 受託するd and translated into the closest SQL 基準 type. For example, the type TEXT, supported by MySQL and PostgreSQL is translated in these compatibility 方式s.
（米）棚上げする／（英）提議する 2.1. 名簿（に載せる）／表（にあげる） of SQL types
| Type | Description |
|---|---|
| TINYINT, SMALLINT, INT or INTEGER, BIGNIT | binary number types with 8, 16, 32, 64 bit precision それぞれ |
| DOUBLE or FLOAT | 64 bit precision floating point number |
| DECIMAL(P,S), DEC(P,S) or NUMERIC(P,S) | 同一の types for 直す／買収する，八百長をするd precision number (*) |
| BOOLEAN | boolean type supports TRUE, FALSE and UNKNOWN |
| CHAR(L) or CHARACTER(L) | 直す／買収する，八百長をするd-length UTF-16 string type - padded with space to length L (**) |
| VARCHCHAR(L) or CHARACTER VARYING(L) | variable-length UTF-16 string type (***) |
| CLOB(L) | variable-length UTF-16 long string type (***) |
| LONGVARCHAR(L) | a 非，不，無-基準 synonym for VARCHAR(L) (***) |
| BINARY(L) | 直す／買収する，八百長をするd-length binary string type - padded with 無 to length L (**) |
| VARBINARY(L) or BINARY VARYING(L) | variable-length binary string type (***) |
| BLOB(L) | variable length binary string type (***) |
| LONGVARBINARY(L) | a 非，不，無-基準 synonym for VARBINARY(L) (***) |
| BIT(L) | 直す／買収する，八百長をするd-length bit 地図／計画する - padded with 0 to length L - 最大限 value of L is 1024 |
| BIT VARYING(L) | variable-length bit 地図／計画する - 最大限 value of L is 1024 |
| UUID | 16 byte 直す／買収する，八百長をするd binary type 代表するd as UUID string |
| DATE | date |
| TIME(S) | time of day (****) |
| TIME(S) WITH TIME ZONE | time of day with zone 排水（気）量 value (****) |
| TIMESTAMP(S) | date with time of day (****) |
| TIMESTAMP(S) WITH TIME ZONE | timestamp with zone 排水（気）量 value (****) |
| INTERVAL | date or time interval - has many variants |
| OTHER | 非，不，無-基準 type for Java serializable 反対する |
| ARRAY | array of a base type |
In the （米）棚上げする／（英）提議する above: (*) The parameters are optional. P is used for 最大限 precision and S for 規模 of DECIMAL and NUMERIC. If only P is used, S defaults to 0. If 非，不，無 is used, P defaults to 128 and S defaults to 0. The 最大限 value of each parameter is 制限のない. (**) The parameter L is used for 直す／買収する，八百長をするd length. If not used, it defaults to 1. (***) The parameter L is used for 最大限 length. It is optional. If not used, it defaults to 32K for VARCHAR and VARBINARY, 1G for BLOB or CLOB, and 16M for the LONGVARCHAR and LONGVARBINARY. The 最大限 value of the parameter is 制限のない for CLOB and BLOB. It is 2 * 1024 *1024 *1024 for other string types. (****) The parameter S is optional and 示すs sub-second fraction precision of time (0 to 9). When not used, it defaults to 6 for TIMESTAMP and 0 for TIME.
HyperSQL supports all the types defined by SQL-92, 加える BOOLEAN, BINARY, ARRAY and LOB types that were later 追加するd to the SQL 基準. It also supports the 非，不，無-基準 OTHER type to 蓄える／店 serializable Java 反対するs.
SQL is a 堅固に typed language. All data 蓄える／店d in 明確な／細部 columns of （米）棚上げする／（英）提議するs and other 反対するs (such as sequence 発生させる人（物）s) have 明確な／細部 types. Each data item 適合するs to the type 限界s such as precision and 規模 for the column. It also 適合するs to any 付加 正直さ 強制s that are defined as CHECK 強制s in domains or （米）棚上げする／（英）提議するs. Types can be explicitly 変えるd using the CAST 表現, but in most 表現s, they are 変えるd automatically.
Data is returned to the 使用者 (or the 使用／適用 program) as a result of 遂行する／発効させるing SQL 声明s such as query 表現s or 機能（する）／行事 calls. All 声明s are 収集するd 事前の to 死刑執行 and the return type of the data is known after 編集 and before 死刑執行. Therefore, once a 声明 is 用意が出来ている, the data type of each column of the returned result is known, 含むing any precision or 規模 所有物／資産／財産. The type does not change when the same query that returned one 列／漕ぐ／騒動, returns many 列／漕ぐ／騒動s as a result of 追加するing more data to the （米）棚上げする／（英）提議するs.
Some SQL 機能（する）／行事s used within SQL 声明s are polymorphic, but the exact type of the argument and the return value is 決定するd at 収集する time.
When a 声明 is 用意が出来ている, using a JDBC
PreparedStatement 反対する, it is 収集するd by the
engine and the type of the columns of its ResultSet
and / or its parameters are accessible through the methods of
PreparedStatement.
TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a
decimal point) are the supported integral types. They correspond
それぞれ to byte,
short, int,
long, BigDecimal and
BigDecimal Java types in the 範囲 of values that
they can 代表する (NUMERIC and DECIMAL are 同等（の）). The type
TINYINT is an HSQLDB 拡張 to the SQL 基準, while the others
適合する to the 基準 鮮明度／定義. The SQL type dictates the 最大限
and 最小限 values that can be held in a field of each type. For example
the value 範囲 for TINYINT is -128 to +127. The bit precision of
TINYINT, SMALLINT, INTEGER and BIGINT is それぞれ 8, 16, 32 and 64.
For NUMERIC and DECIMAL, decimal precision is used.
DECIMAL and NUMERIC with decimal fractions are mapped to
java.math.BigDecimal and can have very large
numbers of digits. In HyperSQL the two types are 同等（の）. These
types, together with integral types, are called exact numeric
types.
In HyperSQL, REAL, FLOAT and DOUBLE are 同等（の）: they are all
mapped to 二塁打 in Java. These types are defined
by the SQL 基準 as approximate numeric types. The bit-precision of
all these types is 64 bits.
The decimal precision and 規模 of NUMERIC and DECIMAL types can be optionally defined. For example, DECIMAL(10,2) means 最大限 total number of digits is 10 and there are always 2 digits after the decimal point, while DECIMAL(10) means 10 digits without a decimal point. The bit-precision of FLOAT can be defined but it is ignored and the default bit-precision of 64 is used. The default precision of NUMERIC and DECIMAL (when not defined) is 128.
公式文書，認める: If a database has been 始める，決める to ignore type precision 限界s with the SET DATABASE SQL SIZE FALSE 命令（する）, then a type 鮮明度／定義 of DECIMAL with no precision and 規模 is 扱う／治療するd as DECIMAL(128,32). In normal 操作／手術, it is 扱う／治療するd as DECIMAL(128).
Integral Types
In 表現s, values of TINYINT, SMALLINT, INTEGER, BIGINT, NUMERIC and DECIMAL (without a decimal point) types can be 自由に 連合させるd and no data 狭くするing takes place. The resulting value is of a type that can support all possible values.
If the SELECT 声明 言及するs to a simple column or 機能（する）／行事, then the return type is the type corresponding to the column or the return type of the 機能（する）／行事. For example:
CREATE TABLE t(a INTEGER, b BIGINT); SELECT MAX(a), MAX(b) FROM t;
will return a ResultSet where the type of
the first column is java.lang.Integer and the
second column is java.lang.Long. However,
SELECT MAX(a) + 1, MAX(b) + 1 FROM t;
will return java.lang.Long and
BigDecimal values, 生成するd as a result of
uniform type 昇進／宣伝 for all possible return values. 公式文書，認める that type
昇進／宣伝 to BigDecimal 確実にするs the 訂正する value
is returned if MAX(b) 評価するs to
Long.MAX_VALUE.
There is no built-in 限界 on the size of 中間の integral
values in 表現s. As a result, you should check for the type of the
ResultSet column and choose an appropriate
getXXXX() method to retrieve it. Alternatively,
you can use the getObject() method, then cast
the result to java.lang.Number and use the
intValue() or
longValue() if the value is not an instance of
java.math.BigDecimal.
When the result of an 表現 is 蓄える／店d in a column of a
database （米）棚上げする／（英）提議する, it has to fit in the 的 column, さもなければ an error
is returned. For example, when 1234567890123456789012 /
12345687901234567890 is 評価するd, the result can be 蓄える／店d in
any integral type column, even a TINYINT column, as it is a small
value.
In SQL 声明s, an integer literal is 扱う／治療するd as INTEGER, unless its value does not fit. In this 事例／患者 it is 扱う／治療するd as BIGINT or DECIMAL, depending on the value.
Depending on the types of the operands, the result of the
操作／手術 is returned in a JDBC ResultSet in any
of the 関係のある Java types: Integer,
Long or BigDecimal. The
ResultSet.getXXXX() methods can be used to
retrieve the values so long as the returned value can be 代表するd by
the resulting type. This type is deterministically based on the query,
not on the actual 列／漕ぐ／騒動s returned.
Other Numeric Types
In SQL 声明s, number literals with a decimal point are
扱う／治療するd as DECIMAL unless they are written with an exponent. Thus
0.2 is considered a DECIMAL value but
0.2E0 is considered a DOUBLE value.
When an approximate numeric type, REAL, FLOAT or DOUBLE (all
synonymous) is part of an 表現 伴う／関わるing different numeric types,
the type of the result is DOUBLE. DECIMAL values can be 変えるd to
DOUBLE unless they are beyond the 二塁打.MIN_VALUE -
二塁打.MAX_VALUE 範囲. For example, A * B, A / B, A + B,
etc., will return a DOUBLE value if either A or B is a DOUBLE.
さもなければ, when no DOUBLE value 存在するs, if a DECIMAL or NUMERIC value is part an 表現, the type of the result is DECIMAL or NUMERIC. 類似の to integral values, when the result of an 表現 is 割り当てるd to a （米）棚上げする／（英）提議する column, the value has to fit in the 的 column, さもなければ an error is returned. This means a small, 4 digit value of DECIMAL type can be 割り当てるd to a column of SMALLINT or INTEGER, but a value with 15 digits cannot.
When a DECIMAL value is multiplied by a DECIMAL or integral type, the resulting 規模 is the sum of the 規模s of the two 条件. When they are divided, the result is a value with a 規模 (number of digits to the 権利 of the decimal point) equal to the larger of the 規模s of the two 条件. The precision for both 操作／手術s is calculated (usually 増加するd) to 許す all possible results.
The distinction between DOUBLE and DECIMAL is important when a
分割 takes place. For example, 10.0/8.0 (DECIMAL)
equals 1.2 but 10.0E0/8.0E0
(DOUBLE) equals 1.25. Without 分割 操作／手術s,
DECIMAL values 代表する exact arithmetic.
REAL, FLOAT and DOUBLE values are all 蓄える／店d in the database as
java.lang.二塁打 反対するs. Special values such as
NaN and +-Infinity are also 蓄える／店d and supported. These values can be
submitted to the database 経由で JDBC
PreparedStatement methods and are returned in
ResultSet 反対するs. ーするために 許す 分割 by
無 of DOUBLE values in SQL 声明s (which returns NaN or
+-Infinity) you should 始める，決める the 所有物／資産／財産
hsqldb.double_nan as 誤った (SET DATABASE SQL DOUBLE
NAN FALSE). The 二塁打 values can be retrieved from a
ResultSet in the 要求するd type so long as they
can be 代表するd. For setting the values, when
PreparedStatement.setDouble() or
setFloat() is used, the value is 扱う／治療するd as a
DOUBLE automatically.
In short,
<numeric type> ::= <exact numeric type> |
<approximate numeric type>
<exact numeric type> ::= NUMERIC [ <left
paren> <precision> [ <comma> <規模> ] <権利
paren> ] | { DECIMAL | DEC } [ <left paren> <precision> [
<comma> <規模> ] <権利 paren> ] | TINYINT | SMALLINT
| INTEGER | INT | BIGINT
<approximate numeric type> ::= FLOAT [ <left
paren> <precision> <権利 paren> ] | REAL | DOUBLE
PRECISION
<precision> ::= <unsigned
integer>
<規模> ::= <unsigned
integer>
The BOOLEAN type 適合するs to the SQL 基準 and 代表するs the
values TRUE, FALSE and
UNKNOWN. This type of column can be initialised with
Java boolean values, or with NULL for the
UNKNOWN value.
The three-value logic is いつかs misunderstood. For example, x IN (1, 2, NULL) does not return true if x is NULL.
In previous 見解／翻訳／版s of HyperSQL, BIT was 簡単に an 偽名，通称 for BOOLEAN. In 見解／翻訳／版 2, BIT is a 選び出す／独身-bit bit 地図／計画する.
<boolean type> ::= BOOLEAN
The SQL 基準 does not support type 転換 to BOOLEAN apart from character strings that consists of boolean literals. Because the BOOLEAN type is 比較して new to the 基準, several database 製品s used other types to 代表する boolean values. For 改善するd compatibility, HyperSQL 許すs some type 転換s to boolean.
Values of BIT and BIT VARYING types with length 1 can be 変えるd to BOOLEAN. If the bit is 始める，決める, the result of 転換 is the TRUE value, さもなければ it is FALSE.
Values of TINYINT, SMALLINT, INTEGER and BIGINT types can be 変えるd to BOOLEAN. If the value is 無, the result is the FALSE value, さもなければ it is TRUE.
The CHARACTER, CHARACTER VARYING and CLOB types are the SQL 基準 character string types. CHAR, VARCHAR and CHARACTER LARGE OBJECT are synonyms for these types. HyperSQL also supports LONGVARCHAR as a synonym for VARCHAR. If LONGVARCHAR is used without a length, then a length of 16M is 割り当てるd. You can 始める，決める LONGVARCHAR to 地図／計画する to CLOB, with the sql.longvar_is_lob 関係 所有物／資産／財産 or the SET DATABASE SQL LONGVAR IS LOB TRUE 声明..
HyperSQL's default character 始める，決める is Unicode, therefore all possible character strings can be 代表するd by these types.
The SQL 基準 behaviour of the CHARACTER type is a 残余 of 遺産／遺物 systems in which character strings are padded with spaces to fill a 直す／買収する，八百長をするd width. These spaces are いつかs 重要な while in other 事例／患者s they are silently discarded. It would be best to 避ける the CHARACTER type altogether. With the 残り／休憩（する） of the types, the strings are not padded when 割り当てるd to columns or variables of the given type. The 追跡するing spaces are still considered discardable for all character types. Therefore, if a string with 追跡するing spaces is too long to 割り当てる to a column or variable of a given length, the spaces beyond the type length are discarded and the assignment 後継するs (供給するd all the characters beyond the type length are spaces).
The VARCHAR and CLOB types have length 限界s, but the strings are not padded by the system. 公式文書，認める that if you use a large length for a VARCHAR or CLOB type, no extra space is used in the database. The space used for each 蓄える／店d item is 比例する to its actual length.
If CHARACTER is used without 明示するing the length, the length
defaults to 1. For the CLOB type, the length 限界 can be defined in
部隊s of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G,
1024 * 1024 * 1024), using the <multiplier>. If
CLOB is used without 明示するing the length, the length defaults to
1GB.
<character string type> ::= { CHARACTER | CHAR }
[ <left paren> <character length> <権利 paren> ] | {
CHARACTER VARYING | CHAR VARYING | VARCHAR } <left paren>
<character length> <権利 paren> | LONGVARCHAR [ <left
paren> <character length> <権利 paren> ] | <character
large 反対する type>
<character large 反対する type> ::= { CHARACTER
LARGE OBJECT | CHAR LARGE OBJECT | CLOB } [ <left paren>
<character large 反対する length> <権利 paren>
]
<character length> ::= <unsigned integer>
[ <char length 部隊s> ]
<large 反対する length> ::= <length> [
<multiplier> ] | <large 反対する length
記念品>
<character large 反対する length> ::= <large
反対する length> [ <char length 部隊s> ]
<large 反対する length 記念品> ::= <digit>...
<multiplier>
<multiplier> ::= K | M | G
<char length 部隊s> ::= CHARACTERS |
OCTETS
Each character type has a collation. This is either a default collation or 明言する／公表するd explicitly with the COLLATE 条項. Collations are discussed in the Schemas and Database 反対するs 一時期／支部.
CHAR(10) CHARACTER(10) VARCHAR(2) CHAR VARYING(2) CLOB(1000) CLOB(30K) CHARACTER LARGE OBJECT(1M) LONGVARCHAR
The BINARY, BINARY VARYING and BLOB types are the SQL 基準 binary string types. VARBINARY and BINARY LARGE OBJECT are synonyms for BINARY VARYING and BLOB types. HyperSQL also supports LONGVARBINARY as a synonym for VARBINARY. You can 始める，決める LONGVARBINARY to 地図／計画する to BLOB, with the sql.longvar_is_lob 関係 所有物／資産／財産 or the SET DATABASE SQL LONGVAR IS LOB TRUE 声明.
Binary string types are used in a 類似の way to character string types. There are several built-in 機能（する）／行事s that are 積みすぎる to support character, binary and bit strings.
The BINARY type 代表するs a 直す／買収する，八百長をするd width-string. Each shorter string is padded with 無s to fill the 直す／買収する，八百長をするd width. 類似の to the CHARACTER type, the 追跡するing 無s in the BINARY string are 簡単に discarded in some 操作／手術s. For the same 推論する／理由, it is best to 避ける this particular type and use VARBINARY instead.
When two binary values are compared, if one is of BINARY type, then 無 padding is 成し遂げるd to 延長する the length of the shorter string to the longer one before comparison. No padding is 成し遂げるd with other binary types. If the bytes compare equal to the end of the shorter value, then the longer string is considered larger than the shorter string.
If BINARY is used without 明示するing the length, the length
defaults to 1. For the BLOB type, the length 限界 can be defined in
部隊s of kilobyte (K, 1024), megabyte (M, 1024 * 1024) or gigabyte (G,
1024 * 1024 * 1024), using the <multiplier>. If
BLOB is used without 明示するing the length, the length defaults to
1GB.
The UUID type 代表するs a UUID string. The type is 類似の to
BINARY(16) but with the extra 施行 that disallows 割り当てるing,
casting, or comparing with shorter or longer strings. Strings such as
'24ff1824-01e8-4dac-8eb3-3fee32ad2b9c' or
'24ff182401e84dac8eb33fee32ad2b9c' are 許すd. When a value of the UUID
type is 変えるd to a CHARACTER type, the hyphens are 挿入するd in the
要求するd positions. Java UUID 反対するs can be used with
java.sql.PreparedStatement to 挿入する values of
this type. The getObject() method of ResultSet
returns the Java 反対する for UUID column data.
<binary string type> ::= BINARY [ <left
paren> <length> <権利 paren> ] | { BINARY VARYING |
VARBINARY } <left paren> <length> <権利 paren> |
LONGVARBINARY [ <left paren> <length> <権利 paren> ]
| UUID | <binary large 反対する string type>
<binary large 反対する string type> ::= { BINARY
LARGE OBJECT | BLOB } [ <left paren> <large 反対する length>
<権利 paren> ]
<length> ::= <unsigned
integer>
BINARY(10) VARBINARY(2) BINARY VARYING(2) BLOB(1000) BLOB(30G) BINARY LARGE OBJECT(1M) LONGVARBINARY
The BIT and BIT VARYING types are the supported bit string types. These types were defined by SQL:1999 but were later 除去するd from the 基準. Bit types 代表する bit 地図／計画するs of given lengths. Each bit is 0 or 1. The BIT type 代表するs a 直す／買収する，八百長をするd width-string. Each shorter string is padded with 無s to fill the 直す／買収する，八百長をするd with. If BIT is used without 明示するing the length, the length defaults to 1. The BIT VARYING type has a 最大限 width and shorter strings are not padded.
Before the introduction of the BOOLEAN type to the SQL 基準, a 選び出す／独身-bit string of the type BIT(1) was 一般的に used. For compatibility with other 製品s that do not 適合する to, or 延長する, the SQL 基準, HyperSQL 許すs values of BIT and BIT VARYING types with length 1 to be 変えるd to and from the BOOLEAN type. BOOLEAN TRUE is considered equal to B'1', BOOLEAN FALSE is considered equal to B'0'.
For the same 推論する／理由, numeric values can be 割り当てるd to columns and variables of the type BIT(1). For assignment, the numeric value 無 is 変えるd to B'0', while all other values are 変えるd to B'1'. For comparison, numeric values 1 is considered equal to B'1' and numeric value 無 is considered equal to B'0'.
It is not 許すd to 成し遂げる other arithmetic or boolean 操作／手術s 伴う／関わるing BIT(1) and BIT VARYING(1). The kid of 操作／手術s 許すd on bit strings are analogous to those 許すd on BINARY and CHARACTER strings. Several built-in 機能（する）／行事s support all three types of string.
<bit string type> ::= BIT [ <left paren>
<length> <権利 paren> ] | BIT VARYING <left paren>
<length> <権利 paren>
BIT BIT(10) BIT VARYING(2)
BLOB and CLOB are 高く弓形に打ち返す types. These types are used for very long strings that do not やむを得ず fit in memory. Small 高く弓形に打ち返すs that fit in memory can be 接近d just like BINARY or VARCHAR column data. But 高く弓形に打ち返すs are usually much larger and therefore 接近d with special JDBC methods.
To 挿入する a 高く弓形に打ち返す into a （米）棚上げする／（英）提議する, or to update a column of 高く弓形に打ち返す type
with a new 高く弓形に打ち返す, you can use the
setBinaryStream() and
setCharacterStream() methods of JDBC
java.sql.PreparedStatement. These are very
efficient methods for long 高く弓形に打ち返すs. Other methods are also supported. If
the data for the BLOB or CLOB is already a memory 反対する, you can use
the setBytes() or
setString() methods, which are efficient for
memory data. Another method is to 得る a 高く弓形に打ち返す with the
getBlob() and
getClob() methods of
java.sql.関係, 居住させる its data, then use
the setBlob() or
setClob() methods of
PreparedStatement. Yet another method 許すs to
create instances of org.hsqldb.jdbc.JDBCBlobFile
and org.hsqldb.jdbc.JDBCClobFile and 建設する a
large 高く弓形に打ち返す for use with setBlob() and
setClob() methods.
A 高く弓形に打ち返す is retrieved from a ResultSet with
the getBlob() or
getClob() method. The steaming methods of the
高く弓形に打ち返す 反対するs are then used to 接近 the data. HyperSQL also 許すs
efficient 接近 to chunks of 高く弓形に打ち返すs with
getBytes() or
getString() methods. その上に, parts of a
BLOB or CLOB already 蓄える／店d in a （米）棚上げする／（英）提議する can be 修正するd. An updatable
ResultSet is used to select the 列／漕ぐ／騒動 from the
（米）棚上げする／（英）提議する. The getBlob() or
getClob() methods of
ResultSet are used to 接近 the 高く弓形に打ち返す as a
java.sql.Blob or
java.sql.Clob 反対する. The
setBytes() and
setString() methods of these 反対するs can be
used to 修正する the 高く弓形に打ち返す. Finally the updateRow()
method of the ResultSet is used to update the 高く弓形に打ち返す
in the 列／漕ぐ／騒動. 公式文書，認める these modifications are not 許すd with compressed or
encrypted 高く弓形に打ち返すs.
高く弓形に打ち返すs are 論理（学）上 蓄える／店d in columns of （米）棚上げする／（英）提議するs. Their physical
貯蔵 is a separate *.高く弓形に打ち返すs とじ込み／提出する. This とじ込み／提出する is
created as soon as a BLOB or CLOB is 挿入するd into the database. The
とじ込み／提出する will grow as new 高く弓形に打ち返すs are 挿入するd into the database. In 見解／翻訳／版 2,
the *.高く弓形に打ち返すs とじ込み／提出する is never 削除するd even if all 高く弓形に打ち返すs
are 削除するd from the database. In this 事例／患者 you can 削除する the
*.高く弓形に打ち返すs とじ込み／提出する after a SHUTDOWN. When a CHECKPOINT
happens, the space used for 削除するd 高く弓形に打ち返すs is 解放する／自由なd and is 再使用するd for
未来 高く弓形に打ち返すs. By default, clobs are 蓄える／店d without compression. You can
use a database setting to enable compression of clobs. This can
意味ありげに 減ずる the 貯蔵 size of clobs.
From 見解／翻訳／版 2.3.4 there are two 選択s for 蓄える／店ing Java 反対するs.
The default 選択 許すs 蓄える／店ing Serializable 反対する. The 反対するs remain serialized inside the database until they are retrieved. The 使用／適用 program that retrieves the 反対する must 含む in its classpath the Java Class for the 反対する, さもなければ it cannot retrieve the 反対する.
Any serializable Java 反対する can be 挿入するd 直接／まっすぐに into a
column of type OTHER using any variation of
PreparedStatement.setObject() methods.
The 代案／選択肢 Live 反対する 選択 is for
mem: databases only and is enabled when the
database 所有物／資産／財産 sql.live_object=true is appended
to the 関係 所有物／資産／財産 that creates the mem database. For example
'jdbc:hsqldb:mem:mydb;sql.live_object=true'. With
this 選択, any Java 反対する can be 蓄える／店d as it is not serialized. The
SQL 声明 SET DATABASE SQL LIVE OBJECT TRUE can
be also used. 公式文書，認める the SQL 声明 must be 遂行する／発効させるd on the first
関係 to the database before any data is 挿入するd. No data 接近
should be made from this 関係. Instead, new 関係s should be
used for data 接近.
For comparison 目的s and in 索引s, any two Java 反対するs are considered equal unless one of them is NULL. You cannot search for a 明確な／細部 反対する or 成し遂げる a join on a column of type OTHER.
Java 反対するs can 簡単に be 蓄える／店d internally and no 操作／手術s can
be 成し遂げるd on them other than assignment between columns of type OTHER
or checking for NULL. 実験（する）s such as WHERE object1 = object2
do not mean what you might 推定する／予想する, as any 非，不，無-null 反対する
would 満足させる such a 実験（する）s. But WHERE object1 IS NOT
NULL is perfectly 許容できる.
The engine does not 許す normal column values to be 割り当てるd to
Java 反対する columns (for example, 割り当てるing an INTEGER or STRING to such
a column with an SQL 声明 such as UPDATE mytable SET
objectcol = intcol WHERE ...).
<java 反対する type> ::= OTHER
The default method of 貯蔵 is used when the 反対するs and their 明言する／公表する needs to be saved and retrieved in the 未来. This method is also used when memory 資源s are 限られた／立憲的な and collections of 反対するs are 蓄える／店d and retrieved only when needed.
The Live 反対する 選択 uses the database （米）棚上げする／（英）提議する as a collection of 反対するs. This 許すs 蓄える／店ing some せいにするs of the 反対するs in the same （米）棚上げする／（英）提議する と一緒に the 反対する itself and 急速な／放蕩な search and retrieval of 反対するs on their せいにするs. For example, when many thousands of live 反対するs 含む／封じ込める 詳細（に述べる）s of films, the film 肩書を与える and the director can be 蓄える／店d in the （米）棚上げする／（英）提議する and searches can be 成し遂げるd for films on these せいにするs:
CREATE TABLE movies (director VARCHAR(30), 肩書を与える VARCHAR(40), obj OTHER) SELECT obj FROM movies WHERE director LIKE 'Luc%'
In any 事例／患者, at least one せいにする of the 反対する should be 蓄える／店d to 許す efficient retrieval of the 反対するs from both Live 反対する and Serialized 貯蔵. An ID number is often used as the 蓄える／店d column せいにする.
In HyperSQL, column length, precision and 規模 qualifiers are
要求するd and are always 施行するd. The VARCHAR and VARBINARY types
要求する a size parameter and do not have a default. For compatibility
with CREATE TABLE 声明s from other databases that do not have size
parameters for VARCHAR column, the URL 所有物／資産／財産
hsqldb.enforce_size=誤った or the SQL 声明
SET DATABASE SQL SIZE FALSE can be used to 許す the
（米）棚上げする／（英）提議する 創造 and automatically 適用する a large value for the 最大限
size of the VARCHAR column. You should 実験（する） your 使用／適用 to 確実にする
the length, precision and 規模 that is used for column 鮮明度／定義s is
appropriate for the 使用／適用 data.
All other types have defaults for size or precision parameters. However, the defaults may not be what your 使用／適用 要求するs and you may have to 明示する the parameters.
String types, 含むing all BIT, BINARY and CHAR string types 加える CLOB and BLOB, are 一般に defined with a length. If no length is 明示するd for BIT, BINARY and CHAR, the default length is 1. For CLOB and BLOB an 実施 defined length of 1G is used.
TIME and TIMESTAMP types can be defined with a わずかの second precision between 0 and 9. INTERVAL type 鮮明度／定義 may have precision and, in some 事例／患者s, fraction second precision. DECIMAL and NUMERIC types may be defined with precision and 規模. For all of these types a default precision or 規模 value is used if one is not 明示するd. The default 規模 is 0. The default わずかの precision for TIME is 0, while it is 6 for TIMESTAMP.
Values can be 変えるd from one type to another in two different ways: by using explicit CAST 表現 or by implicit 転換 used in assignment, comparison, and aggregation.
String values cannot be 割り当てるd to VARCHAR columns if they are longer than the defined type length. For CHARACTER columns, a long string can be 割り当てるd (with truncation) only if all the characters after the length are spaces. Shorter strings are padded with the space character when 挿入するd into a CHARACTER column. 類似の 支配するs are 適用するd to VARBINARY and BINARY columns. For BINARY columns, the padding and truncation 支配するs are 適用するd with 無 bytes, instead of spaces.
Explicit CAST of a value to a CHARACTER or VARCHAR type will
result in 軍隊d truncation or padding. So a 実験（する） such as CAST
(mycol AS VARCHAR(2)) = 'xy' will find the values beginning
with 'xy'. This is the 同等（の） of SUBSTRING(mycol FROM 1 FOR
2)= 'xy'.
For all numeric types, the 支配するs of explicit cast and implicit 転換 are the same. If cast or 転換 原因（となる）s any digits to be lost from the わずかの part, it can take place. If the 非，不，無-わずかの part of the value cannot be 代表するd in the new type, cast or 転換 cannot take place and will result in a data exception.
There are special 支配するs for DATE, TIME, TIMESTAMP and INTERVAL casts and 転換s.
HSQLDB fully supports datetime and interval types and 操作／手術s, 含むing all 関連した optional features, as 明示するd by the SQL 基準 since SQL-92. The two groups of types are complementary.
The DATE type 代表するs a calendar date with YEAR, MONTH and DAY fields.
The TIME type 代表するs time of day with HOUR, MINUTE and SECOND fields, 加える an optional SECOND FRACTION field.
The TIMESTAMP type 代表するs the combination of DATE and TIME types.
TIME and TIMESTAMP types can 含む WITH TIME ZONE or WITHOUT TIME ZONE (the default) qualifiers. They can have わずかの second parts. For example, TIME(6) has six わずかの digits for the second field.
If わずかの second precision is not 明示するd, it defaults to 0 for TIME and to 6 for TIMESTAMP.
<datetime type> ::= DATE | TIME [ <left
paren> <time precision> <権利 paren> ] [ <with or
without time zone> ] | TIMESTAMP [ <left paren> <timestamp
precision> <権利 paren> ] [ <with or without time zone>
]
<with or without time zone> ::= WITH TIME ZONE |
WITHOUT TIME ZONE
<time precision> ::= <time わずかの seconds
precision>
<timestamp precision> ::= <time わずかの
seconds precision>
<time わずかの seconds precision> ::=
<unsigned integer>
DATE TIME(6) TIMESTAMP(2) WITH TIME ZONE
TIME or TIMESTAMP literals 含む／封じ込めるing a zone 排水（気）量 value are WITH TIME ZONE. Examples of the string literals used to 代表する date time values, some with time zone, some without, are below:
DATE '2008-08-22' TIMESTAMP '2008-08-08 20:08:08' TIMESTAMP '2008-08-08 20:08:08+8:00' /* Beijing */ TIME '20:08:08.034900' TIME '20:08:08.034900-8:00' /* US 太平洋の */
Time Zone
DATE values do not take time zones. For example, 国際連合 指定するs 5 June as World 環境 Day, which was 観察するd on DATE '2008-06-05' in different time zones.
TIME and TIMESTAMP values without time zone, usually have a 状況 that 示すs some 地元の time zone. For example, a database for college course 時刻表／予定表s usually 蓄える／店s class dates and times without time zones. This 作品 because the 場所 of the college is 直す／買収する，八百長をするd and the time zone 排水（気）量 is the same for all the values. Even when the events take place in different time zones, for example international flight times, it is possible to 蓄える／店 all the datetime （警察などへの）密告，告訴（状） as 言及／関連s to a 選び出す／独身 time zone, usually GMT. For some databases it may be useful to 蓄える／店 the time zone 排水（気）量 together with each datetime value. SQL’s TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE values 含む a time zone 排水（気）量 value.
The time zone 排水（気）量 is of the type INTERVAL HOUR TO MINUTE. This data type is 述べるd in the next section. The 合法的な values are between '–18:00' and '+18:00'.
操作／手術s on Datetime Types
The 表現 <datetime 表現> AT TIME ZONE {
<interval 最初の／主要な> | <time zone 指名する> } 評価するs to
a datetime value 代表するing 正確に／まさに the same point of time in the
明示するd <time 排水（気）量> or the geographical
<time zone 指名する>. The 表現, AT
LOCAL is 同等（の） to AT TIME ZONE <地元の time
排水（気）量>. If AT TIME ZONE is used
with a datetime operand of type WITHOUT TIME ZONE, the operand is first
変えるd to a value of type WITH TIME ZONE using the 開会／開廷／会期's calendar,
then the 明示するd time zone 排水（気）量 is 始める，決める for the value. Therefore,
in these 事例／患者s, the final value depends on the time zone of the 開会／開廷／会期 in
which the 声明 was used, calculated at the exact point of time (of
the input) and accounting for daylight saving time at that point of
time.
From 見解／翻訳／版 2.7.0 it is possible to use 地域の time zones with AT TIME ZONE. Any zone 指名する used must match 正確に／まさに a TimeZone id supported by the JVM. These 含む 指名するs such as 'America/New_York'. Some zones 含む daylight saving time periods which are used when 変えるing the time zone.
See also the FROM_TZ 機能（する）／行事 which 許すs you to 変える to a time zone without changing the date-time values such as hour and minute.
AT TIME ZONE, 修正するs the field values of the datetime operand. This is done by the に引き続いて 手続き:
決定する the corresponding datetime at UTC using the 開会／開廷／会期's calendar.
find the datetime value at the given time zone that corresponds with the UTC value from step 1.
Example a:
VALUES TIMESTAMP'2022-03-28 11:00:00' AT TIME ZONE INTERVAL '-5:00' HOUR TO MINUTE C1 ------------------------ 2022-03-28 14:00:00-5:00 VALUES TIMESTAMP'2022-03-28 11:00:00+4:00' AT TIME ZONE 'America/Chicago' C1 ------------------------ 2022-03-28 02:00:00-5:00
In the first example above, the 開会／開廷／会期's time zone 排水（気）量 is '-8:00'. In step 1, time '11:00:00' is 変えるd to UTC, which is time '19:00:00+0:00'. In step 2, this value is 表明するd as time '14:00:00-5:00' in the 的 zone.
In the second example, the 開会／開廷／会期's time zone 排水（気）量 is not considered because the timestamp has a time zone. In step 1, time is 変えるd to UTC, which is time '07:00:00+0:00', In step 2, this value is 表明するd as time '02:00:00-5:00' in the 的 zone.
Example b:
TIME '12:00:00-5:00' AT TIME ZONE INTERVAL '1:00' HOUR TO MINUTE
Because the operand has a time zone, the result is 独立した・無所属 of the 開会／開廷／会期 time zone 排水（気）量. Step 1 results in TIME '17:00:00+0:00', and step 2 results in TIME '18:00:00+1:00'
公式文書，認める that the operand is not 限られた／立憲的な to datetime literals used in these examples. Any valid 表現 that 評価するs to a datetime value can be the operand.
Type 転換
CAST is used for all other 転換s. Examples:
CAST (<value> AS TIME WITHOUT TIME ZONE) CAST (<value> AS TIME WITH TIME ZONE)
In the first example, if <value> has a time
zone 構成要素, it is 簡単に dropped. For example, TIME '12:00:00-5:00' is
変えるd to TIME '12:00:00'
In the second example, if <value> has no
time zone 構成要素, the 現在の time zone 排水（気）量 of the 開会／開廷／会期 is
追加するd. For example, TIME '12:00:00' is 変えるd to TIME '12:00:00-8:00'
when the 開会／開廷／会期 time zone 排水（気）量 is '-8:00'.
転換 between DATE and TIMESTAMP is 成し遂げるd by 除去するing the TIME 構成要素 of a TIMESTAMP value or by setting the hour, minute and second fields to 無. TIMESTAMP '2008-08-08 20:08:08+8:00' becomes DATE '2008-08-08', while DATE '2008-08-22' becomes TIMESTAMP '2008-08-22 00:00:00'.
転換 between TIME and TIMESTAMP is 成し遂げるd by 除去するing the DATE field values of a TIMESTAMP value or by appending the fields of the TIME value to the fields of the 現在の 開会／開廷／会期 date value.
Assignment
When a value is 割り当てるd to a datetime 的, e.g., a value is used to update a 列／漕ぐ／騒動 of a （米）棚上げする／（英）提議する, the type of the value must be the same as the 的, but the WITH TIME ZONE or WITHOUT TIME ZONE 特徴 can be different. If the types are not the same, an explicit CAST must be used to 変える the value into the 的 type.
Comparison
When values WITH TIME ZONE are compared, they are 変えるd to UTC values before comparison. If a value WITH TIME ZONE is compared to another WITHOUT TIME ZONE, then the WITH TIME ZONE value is 変えるd to AT LOCAL, then 変えるd to WITHOUT TIME ZONE before comparison.
It is not recommended to design 使用／適用s that rely on comparisons and 転換s between TIME values WITH TIME ZONE. The 転換s may 伴う／関わる normalisation of the time value, resulting in 予期しない results. For example, the 表現: BETWEEN(TIME '12:00:00-8:00', TIME '22:00:00-8:00') is 変えるd to BETWEEN(TIME '20:00:00+0:00', TIME '06:00:00+0:00') when it is 評価するd in the UTC zone, which is always FALSE.
機能（する）／行事s
Several 機能（する）／行事s return the 現在の 開会／開廷／会期 timestamp in different datetime types:
|
CURRENT_DATE |
DATE |
|
CURRENT_TIME |
TIME WITH TIME ZONE |
|
CURRENT_TIMESTAMP |
TIMESTAMP WITH TIME ZONE |
|
LOCALTIME |
TIME WITHOUT TIME ZONE |
|
LOCALTIMESTAMP |
TIMESTAMP WITHOUT TIME ZONE |
HyperSQL supports a very 広範囲にわたる 範囲 of 機能（する）／行事s for 転換, extraction and 巧みな操作 of DATE and TIMESTAMP values. See the Built In 機能（する）／行事s 一時期／支部.
開会／開廷／会期 Time Zone 排水（気）量
When an SQL 開会／開廷／会期 is started (with a JDBC 関係) the 地元の time zone of the （弁護士の）依頼人 JVM (含むing any seasonal time 調整s such as daylight-saving time) is used as the 開会／開廷／会期 time zone 排水（気）量. In 見解／翻訳／版 2.7.0 a Java Calendar 反対する with the 地元の time zone of the （弁護士の）依頼人 JVM is created and used. Therefore when a seasonal time 調整 for daylight saving time is made while the 開会／開廷／会期 is open, the SQL 開会／開廷／会期 zone 排水（気）量 is changed. In some older 見解／翻訳／版s of HyperSQL, the SQL 開会／開廷／会期 time 排水（気）量 was not changed when a seasonal time 調整 took place.
To change the SQL 開会／開廷／会期 time zone 排水（気）量, use the に引き続いて 命令（する）s:
SET TIME ZONE <time
排水（気）量>
SET TIME ZONE LOCAL
The first 命令（する） 始める，決めるs the 排水（気）量 to the given value. The second 命令（する） 回復するs the 初めの, real time zone 排水（気）量 of the 開会／開廷／会期.
Datetime Values and Java
When datetime values are sent to the database using the
PreparedStatement or
CallableStatement interfaces, the Java 反対する is
変えるd to the type of the 用意が出来ている or callable 声明 parameter.
This type may be DATE, TIME, or TIMESTAMP (with or without time zone). The
time zone 排水（気）量 is the time zone of the JDBC 開会／開廷／会期.
When datetime values are retrieved from the database using the
ResultSet interface, there are two 代表s. The
getString(…) methods of the
ResultSet interface, return an exact 代表
of the value in the SQL type as it is 蓄える／店d in the database. This
含むs the 訂正する number of digits for the わずかの second field, and
for values with time zone 排水（気）量, the time zone 排水（気）量.
Therefore, if TIME '12:00:00' is 蓄える／店d in the database, all 使用者s in
different time zones will get '12:00:00' when they retrieve the value as a
string. The getTime(…) and
getTimestamp(…) methods of the
ResultSet interface return Java 反対するs that are
訂正するd for the 開会／開廷／会期 time zone. The UTC millisecond value 含む／封じ込めるd
the java.sql.Time or
java.sql.Timestamp 反対するs will be adjusted to the
time zone of the 開会／開廷／会期, therefore the
toString() method of these 反対するs return the
same values in different time zones.
If you want to 蓄える／店 and retrieve UTC values that are 独立した・無所属 of
any 開会／開廷／会期's time zone, you can use a TIMESTAMP WITH TIME ZONE column.
The setTime(...) and
setTimestamp(...) methods of the
PreparedStatement interface which have a Calendar parameter can be used to
割り当てる the values. The time zone of the given Calendar argument is used as
the time zone. Conversely, the getTime(...) and
getTimestamp(...) methods of the ResultSet
interface which have a Calendar parameter can be used with a Calendar
argument to retrieve the values.
Java 8 拡張s
JDBC 4 and JAVA6 did not 含む type codes for SQL datetime types that have a TIME ZONE 所有物／資産／財産. Therefore, HyperSQL 報告（する）／憶測d these types by default as datetime types without TIME ZONE.
JAVA 8 introduced new type codes for TIMESTAMP WITH TIME ZONE and
TIME WITH TIME ZONE. HyperSQL supports this in
ResultSet, PreparedStatement
and CallableStatement.
The getObject(int columnIndex) method
on a column of TIMESTAMP WITH TIME ZONE returns an
java.time.OffsetDateTime 反対する.
The getObject(int columnIndex) method
on a column of TIME WITH TIME ZONE returns an
java.time.OffsetTime 反対する.
The getObject(int columnIndex, Class
type) method on any date, time and timestamp supports the
java.time 一括 types:
LocalDate, LocalTime,
LocalDateTime,
OffsetTime,
OffsetDateTime, and
Instance 同様に as
java.sql 一括 types,
Date, Time and
Timestamp.
The setObject methods also support Java
反対するs of the types 名簿（に載せる）／表（にあげる）d above.
The getObject and
setObject methods with column 指名する parameters
behave just like their 相当するものs with columnIndexe
parameters.
非，不，無-基準 拡張s
HyperSQL 見解／翻訳／版 2.7 supports some 拡張s to the SQL 基準 治療 of datetime and interval types. For example, the 基準 表現 to 追加する a number of days to a date has an explicit INTERVAL value but HSQLDB also 許すs an integer to be used without 明示するing DAY. Examples of some 基準 表現s and their 非，不，無-基準 代案／選択肢s are given below:
-- 基準 forms CURRENT_DATE + '2' DAY SELECT (LOCALTIMESTAMP - atimestampcolumn) DAY TO SECOND FROM atable -- 非，不，無-基準 forms CURRENT_DATE + 2 SELECT LOCALTIMESTAMP - atimestampcolumn FROM atable
It is recommended to use the SQL 基準 syntax as it is more 正確な and 避けるs ambiguity.
Interval types are used to 代表する differences between date time values. The difference between two date time values can be 手段d in seconds or in months. For 測定s in months, the 部隊s YEAR and MONTH are 利用できる, while for 測定s in seconds, the 部隊s DAY, HOUR, MINUTE, SECOND are 利用できる. The 部隊s can be used 個々に, or as a 範囲. An interval type can 明示する the precision of the most 重要な field and the second fraction digits of the SECOND field (if it has a SECOND field). The default precision is 2, に引き続いて the 基準. The default second precision is 0. The default precision is too small for many 使用／適用s and should be overridden.
<interval type> ::= INTERVAL <interval
qualifier>
<interval qualifier> ::= <start field> TO
<end field> | <選び出す／独身 datetime field>
<start field> ::= <非，不，無-second 最初の／主要な datetime
field> [ <left paren> <interval 主要な field precision>
<権利 paren> ]
<end field> ::= <非，不，無-second 最初の／主要な datetime
field> | SECOND [ <left paren> <interval わずかの seconds
precision> <権利 paren> ]
<選び出す／独身 datetime field> ::= <非，不，無-second 最初の／主要な
datetime field> [ <left paren> <interval 主要な field
precision> <権利 paren> ] | SECOND [ <left paren>
<interval 主要な field precision> [ <comma> <interval
わずかの seconds precision> ] <権利 paren>
]
<最初の／主要な datetime field> ::= <非，不，無-second
最初の／主要な datetime field> | SECOND
<非，不，無-second 最初の／主要な datetime field> ::= YEAR |
MONTH | DAY | HOUR | MINUTE
<interval わずかの seconds precision> ::=
<unsigned integer>
<interval 主要な field precision> ::=
<unsigned integer>
Examples of INTERVAL type 鮮明度／定義:
INTERVAL YEAR TO MONTH INTERVAL YEAR(3) INTERVAL DAY(4) TO HOUR INTERVAL MINUTE(4) TO SECOND(6) INTERVAL SECOND(4,6)
The word INTERVAL 示すs the general type 指名する. The 残り／休憩（する） of the
鮮明度／定義 is called an <interval qualifier>.
This 任命 is important, as in most 表現s
<interval qualifier> is used without the word
INTERVAL.
Interval Values
An interval value can be 消極的な, 肯定的な or 無. An interval type has all the datetime fields in the 明示するd 範囲. These fields are 類似の to those in the TIMESTAMP type. The differences are as follows:
The first field of an interval value can 持つ／拘留する any numeric value up to the 明示するd precision. For example, the hour field in HOUR(2) TO SECOND can 持つ／拘留する values above 23 (up to 99). The year and month fields can 持つ／拘留する 無 (unlike a TIMESTAMP value) and the 最大限 value of a month field that is not the most 重要な field, is 11.
The 基準 機能（する）／行事 ABS(<interval value
表現>) can be used to 変える a 消極的な interval value
to a 肯定的な one.
The literal 代表 of interval values consists of the type 鮮明度／定義, with a string 代表するing the interval value 挿入するd after the word INTERVAL. Some examples of interval literal below:
INTERVAL '145 23:12:19.345' DAY(3) TO SECOND(3) INTERVAL '3503:12:19.345' HOUR TO SECOND(3) /* equal to the first value */ INTERVAL '19.345' SECOND(4,3) /* 最大限 number of digits for the second value is 4, and each value is 表明するd with three fraction digits. */ INTERVAL '-23-10' YEAR(2) TO MONTH
Interval values of the types that are based on seconds can be cast into one another. 類似して, those that are based on months can be cast into one another. It is not possible to cast or 変える a value based on seconds to one based on months, or 副／悪徳行為 versa.
When a cast is 成し遂げるd to a type with a smaller least-重要な field, nothing is lost from the interval value. さもなければ, the values for the 行方不明の least-重要な fields are discarded. Examples:
CAST ( INTERVAL '145 23:12:19' DAY TO SECOND AS INTERVAL DAY TO HOUR ) = INTERVAL '145 23' DAY TO HOUR CAST(INTERVAL '145 23' DAY TO HOUR AS INTERVAL DAY TO SECOND) = INTERVAL '145 23:00:00' DAY TO SECOND
A numeric value can be cast to an interval type. In this 事例／患者 the numeric value is first 変えるd to a 選び出す／独身-field INTERVAL type with the same field as the least 重要な field of the 的 interval type. This value is then 変えるd to the 的 interval type For example CAST( 22 AS INTERVAL YEAR TO MONTH) 評価するs to INTERVAL '22' MONTH and then INTERVAL '1 10' YEAR TO MONTH. 公式文書，認める that SQL 基準 only supports casts to 選び出す／独身-field INTERVAL types, while HyperSQL 許すs casting to multi-field types 同様に.
An interval value can be cast to a numeric type. In this 事例／患者 the interval value is first 変えるd to a 選び出す／独身-field INTERVAL type with the same field as the least 重要な とじ込み／提出するd of the interval value. The value is then 変えるd to the 的 type. For example, CAST (INTERVAL '1-11' YEAR TO MONTH AS INT) 評価するs to INTERVAL '23' MONTH, and then 23.
An interval value can be cast into a character type, which results in an INTERVAL literal. A character value can be cast into an INTERVAL type so long as it is a string with a 判型 両立できる with an INTERVAL literal.
Two interval values can be 追加するd or subtracted so long as the types of both are based on the same field, i.e., both are based on MONTH or SECOND. The values are both 変えるd to a 選び出す／独身-field interval type with same field as the least-重要な field between the two types. After 新規加入 or subtraction, the result is 変えるd to an interval type that 含む／封じ込めるs all the fields of the two 初めの types.
An interval value can be multiplied or divided by a numeric value. Again, the value is 変えるd to a numeric, which is then multiplied or divided, before 変えるing 支援する to the 初めの interval type.
An interval value is negated by 簡単に prefixing with the minus 調印する.
Interval values used in 表現s are either typed values,
含むing interval literals, or are interval casts. The 表現:
<表現> <interval qualifier> is a cast
of the result of the <表現> into the
INTERVAL type 明示するd by the <interval qualifier>. The
cast can be formed by 追加するing the keywords and parentheses as follows: CAST
( <表現> AS INTERVAL <interval qualifier>
).
The examples below feature different forms of 表現
that 代表する an interval value, which is then 追加するd to the given date
literal.
DATE '2000-01-01' + INTERVAL '1-10' YEAR TO MONTH /* interval literal */ DATE '2000-01-01' + '1-10' YEAR TO MONTH /* the string '1-10' is cast into INTERVAL YEAR TO MONTH */ DATE '2000-01-01' + 22 MONTH /* the integer 22 is cast into INTERVAL MONTH, same value as above */ DATE '2000-01-01' - 22 DAY /* the integer 22 is cast into INTERVAL DAY */ DATE '2000-01-01' + COL2 /* the type of COL2 must be an INTERVAL type */ DATE '2000-01-01' + COL2 MONTH /* COL2 may be a number, it is cast into a MONTH interval */
Datetime and Interval 操作／手術s
An interval can be 追加するd to or subtracted from a datetime value so long as they have some fields in ありふれた. For example, an INTERVAL MONTH cannot be 追加するd to a TIME value, while an INTERVAL HOUR TO SECOND can. The interval is first 変えるd to a numeric value, then the value is 追加するd to, or subtracted from, the corresponding field of the datetime value.
If the result of 新規加入 or subtraction is beyond the permissible 範囲 for the field, the field value is normalised and carried over to the next 重要な field until all the fields are normalised. For example, 追加するing 20 minutes to TIME '23:50:10' will result successively in '23:70:10', '24:10:10' and finally TIME '00:10:10'. Subtracting 20 minutes from the result is 成し遂げるd as follows: '00:-10:10', '-1:50:10', finally TIME '23:50:10'. 公式文書，認める that if DATE or TIMESTAMP normalisation results in the YEAR field value out of the 範囲 (1,10000), then an exception 条件 is raised.
If an interval value based on MONTH is 追加するd to, or subtracted from a DATE or TIMESTAMP value, the result may have an 無効の day (30 or 31) for the given result month. In this 事例／患者 an exception 条件 is raised.
The result of subtraction of two datetime 表現s is an interval
value. The two datetime 表現s must be of the same type. The type of
the interval value must be 明示するd in the 表現, using only the
interval field 指名するs. The two datetime 表現s are enclosed in
parentheses, followed by the <interval qualifier>
fields. In the first example below, COL1 and COL2 are of the same datetime
type, and the result is 評価するd in INTERVAL YEAR TO MONTH type.
(COL1 – COL2) YEAR TO MONTH /* the difference between two DATE or two TIMESTAMP values in years and months */ (CURRENT_DATE – COL3) DAY /* the number of days between the value of COL3 and the 現在の date */ (CURRENT_DATE - DATE '2000-01-01') YEAR TO MONTH /* the number of years and months since the beginning of this century */ CURRENT_DATE - 2 DAY /* the date of the day before yesterday */ (CURRENT_TIMESTAMP - TIMESTAMP '2009-01-01 00:00:00') DAY(4) TO SECOND(2) /* days to seconds since the given date */
The individual fields of both datetime and interval values can be 抽出するd using the EXTRACT 機能（する）／行事. The same 機能（する）／行事 can also be used to 抽出する the time zone 排水（気）量 fields of a datetime value.
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND |
TIMEZONE_HOUR | TIMEZONE_MINUTE | DAY_OF_WEEK | WEEK_OF_YEAR } FROM
{<datetime value> | <interval value>})
The dichotomy between interval types based on seconds, and those based on months, 茎・取り除くs from the fact that the different calendar months have different numbers of days. For example, the 表現, “nine months and nine days since an event” is not exact when the date of the event is unknown. It can 代表する a period of around 284 days give or take one. SQL interval values are 独立した・無所属 of any start or end dates or times. However, when they are 追加するd to or subtracted from 確かな date or timestamp values, the result may be 無効の and 原因（となる） an exception (e.g. 追加するing one month to January 30 results in February 30, which is 無効の).
JDBC has an unfortunate 制限 and does not 含む type codes for SQL INTERVAL types. Therefore, for compatibility with database 道具s that are 限られた／立憲的な to the JDBC type codes, HyperSQL 報告（する）／憶測s these types by default as VARCHAR. You can use the URL 所有物／資産／財産 hsqldb.translate_dti_types=誤った to 無視／無効 the default behaviour.
Java 8 拡張s
JAVA 8 does not have SQL type codes for INTERVAL types. HyperSQL
supports java.time types for INTERVAL types in
ResultSet, PreparedStatement
and CallableStatement.
The getObject(int columnIndex, Class
type) method on an INTERVAL supports the
java.time.Period type for YEAR and MONTH
interval and the java.time.Duration type for
other interval types that cover DAY to SECOND.
The setObject(int columnIndex) method
受託するs java.time.Period and
java.time.Duration 反対するs for columns of
関連した INTERVAL types.
The getObject and
setObject methods with column 指名する parameters
behave just like their 相当するものs with columnIndexe
parameters.
Array are a powerful feature of SQL:2023 and can help solve many ありふれた problems. Arrays should not be used as a 代用品，人 for （米）棚上げする／（英）提議するs.
HyperSQL supports arrays of values によれば the 基準.
Elements of the array are either NULL, or of the same data type. It is possible to define arrays of all supported types, 含むing the types covered in this 一時期／支部 and 使用者-defined types, except LOB types. An SQL array is one dimensional and is 演説（する）／住所d from position 1. An empty array can also be used, which has no element.
Arrays can be 蓄える／店d in the database, 同様に as 存在 used as 一時的な コンテナs of values for 簡単にするing SQL 声明s. They 容易にする data 交流 between the SQL engine and the 使用者's 使用／適用.
The 十分な 範囲 of supported syntax 許すs array to be created, used in SELECT or other 声明s, 連合させるd with 列／漕ぐ／騒動s of （米）棚上げする／（英）提議するs, and used in 決まりきった仕事 calls.
The type of a （米）棚上げする／（英）提議する column, a 決まりきった仕事 parameter, a variable, or the return value of a 機能（する）／行事 can be defined as an array.
<array type> ::= <data type> ARRAY [ <left
bracket or trigraph> <最大限 cardinality> <権利 bracket or
trigraph> ]
The word ARRAY is 追加するd to any valid type 鮮明度／定義 except BLOB
and CLOB type 鮮明度／定義s. If the optional <最大限
cardinality> is not used, the default value is 1024. The
size of the array cannot be 延長するd beyond 最大限 cardinality.
In the example below, the （米）棚上げする／（英）提議する 含む／封じ込めるs a column of INTEGER
arrays and a column of VARCHAR arrays. The VARCHAR array has an explicit
最大限 size of 10, which means each array can have between 0 and 10
elements. The INTEGER array has the default 最大限 size of 1024. The
得点する／非難する／２０s column has a default 条項 with an empty array. The default
条項 can be defined only as DEFAULT NULL or
DEFAULT ARRAY[] and does not 許す arrays 含む／封じ込めるing
elements.
CREATE TABLE t (id INT PRIMARY KEY, 得点する／非難する／２０s INT ARRAY DEFAULT ARRAY[], 指名するs VARCHAR(20) ARRAY[10])
An array can be 建設するd from value 表現s or a query 表現.
<array value 建設者 by enumeration> ::= ARRAY
<left bracket or trigraph> <array element 名簿（に載せる）／表（にあげる）> <権利
bracket or trigraph>
<array element 名簿（に載せる）／表（にあげる）> ::= <value 表現> [
{ <comma> <value 表現> }... ]
<array value 建設者 by query> ::= ARRAY
<left paren> <query 表現> [ <order by 条項> ]
<権利 paren>
In the examples below, arrays are 建設するd from values, column 言及／関連s or variables, 機能（する）／行事 calls, or query 表現s.
ARRAY [ 1, 2, 3 ] ARRAY [ 'HOT', 'COLD' ] ARRAY [ var1, var2, CURRENT_DATE ] ARRAY (SELECT lastname FROM namestable ORDER BY id)
挿入するing and updating a （米）棚上げする／（英）提議する with an ARRAY column can use array 建設者s, not only for updated column values, but also in equality search 条件s:
INSERT INTO t VALUES 10, ARRAY[1,2,3], ARRAY['HOT', 'COLD'] UPDATE t SET 指名するs = ARRAY['LARGE', 'SMALL'] WHERE id = 12 UPDATE t SET 指名するs = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND 得点する／非難する／２０s = ARRAY[3,4]
When using a PreparedStatement with an ARRAY parameter, a Java
array or a java.sql.Array 反対する may be used to
始める，決める the parameter.
In the example below 用意が出来ている 声明s for INSERT and UPDATE with array parameters are used.
// create
String create = "CREATE TABLE t (id INT PRIMARY KEY, 得点する／非難する／２０s INT ARRAY DEFAULT ARRAY[], 指名するs VARCHAR(20) ARRAY[10])";
声明 st = 関係.createStatement();
st.遂行する／発効させる(create);
// 挿入する
String 挿入する = "INSERT INTO t VALUES ?, ?, ?";
PreparedStatement ps = 関係.prepareStatement(挿入する);
反対する[] numbers = new 反対する[]{17, 19};
反対する[] data = new 反対する[]{"one", "two"};
ps.setInt(1, 10);
ps.setObject(2, numbers);
ps.setObject(3, data);
ps.遂行する／発効させる();
// update
String update_a = "UPDATE t SET 指名するs = ? WHERE id = ?";
ps = 関係.prepareStatement(update_a);
data = new 反対する[]{"I", "II", "III", "IV"};
ps.setObject(1, data);
ps.setInt(2, 10);
ps.executeUpdate();
In the example below, a java.sql.Array is
used for the update, using the same data as above:
data = new 反対する[]{"I", "II", "III", "IV"};
Array array = 関係.createArrayOf("VARCHAR", data);
ps.setArray(1, array);
ps.setInt(2, 10);
ps.executeUpdate();
A trigraph is a 代用品，人 for <left bracket> and <権利 bracket>.
<left bracket trigraph> ::= ??(
<権利 bracket trigraph> ::= ??)
The example below shows the use of trigraphs instead of brackets.
INSERT INTO t VALUES 10, ARRAY??(1,2,3??), ARRAY['HOT', 'COLD']
UPDATE t SET 指名するs = ARRAY ??('LARGE', 'SMALL'??) WHERE id = 12
UPDATE t SET 指名するs = ARRAY['LARGE', 'SMALL'] WHERE id < 12 AND 得点する／非難する／２０s = ARRAY[3,4]
The most ありふれた 操作／手術s on an array are element 言及／関連 and assignment, which are used when reading or 令状ing an element of the array. Unlike Java and many other languages, arrays are 延長するd if an element is 割り当てるd to an 索引 beyond the 現在の length. This can result in gaps 含む／封じ込めるing NULL elements. Array length cannot 越える the 最大限 cardinality.
Elements of all arrays, 含むing those that are the result of 機能（する）／行事 calls or other 操作／手術s can be 言及／関連d for reading.
<array element 言及／関連> ::= <array value
表現> <left bracket> <numeric value 表現>
<権利 bracket>
Elements of arrays that are （米）棚上げする／（英）提議する columns or 決まりきった仕事 variables can be 言及／関連d for 令状ing. This is done in a SET 声明, either inside an UPDATE 声明, or as a separate 声明 in the 事例／患者 of 決まりきった仕事 variables, OUT and INOUT parameters.
<的 array element specification> ::= <的
array 言及／関連> <left bracket or trigraph> <simple value
specification> <権利 bracket or trigraph>
<的 array 言及／関連> ::= <SQL parameter
言及／関連> | <column 言及／関連>
公式文書，認める that only simple values or variables are 許すd for the array 索引 when an assignment is 成し遂げるd. The examples below 論証する how elements of the array are 言及／関連d in SELECT and UPDATE 声明s.
SELECT 得点する／非難する／２０s[最高位の], 指名するs[最高位の] FROM t JOIN t1 on (t.id = t1.tid) UPDATE t SET 得点する／非難する／２０s[2] = 123, 指名するs[2] = 'Reds' WHERE id = 10
Several SQL 操作／手術s and 機能（する）／行事s can be used with arrays.
CONCATENATION
Array concatenation is 成し遂げるd 類似の to string concatenation. All elements of the array on the 権利 are appended to the array on left.
<array concatenation> ::= <array value
表現 1> <concatenation 操作者> <array value
表現 2>
<concatenation 操作者> ::= ||
FUNCTIONS
機能（する）／行事s 名簿（に載せる）／表（にあげる）d below operate on arrays. 詳細（に述べる）s are 述べるd in the Built In 機能（する）／行事s 一時期／支部.
ARRAY_AGG is an aggregate 機能（する）／行事 and produces
an array 含む／封じ込めるing values from different 列／漕ぐ／騒動s of a SELECT 声明.
詳細（に述べる）s are 述べるd in the Data 接近 and Change 一時期／支部.
SEQUENCE_ARRAY creates an array with sequential
elements.
CARDINALITY <left paren> <array value
表現> <権利 paren>
MAX_CARDINALITY <left paren> <array value
表現> <権利 paren>
Array cardinality and max cardinality are 機能（する）／行事s that return an integer. CARDINALITY returns the element count, while MAX_CARDINALITY returns the 最大限 宣言するd cardinality of an array.
POSITION_ARRAY <left paren> <value
表現> IN <array value 表現> [FROM <numeric value
表現>] <権利 paren>
The POSITION_ARRAY 機能（する）／行事 returns the position of the first match for the <value 表現> from the start or from the given start position when <numeric value 表現> is used.
TRIM_ARRAY <left paren> <array value
表現> <comma> <numeric value 表現> <権利
paren>
The TRIM_ARRAY 機能（する）／行事 returns a copy of an array with the
明示するd number of elements 除去するd from the end of the array. The
<array value 表現> can be any 表現
that 評価するs to an array.
SORT_ARRAY <left paren> <array value
表現> [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] <権利
paren>
The SORT_ARRAY 機能（する）／行事 returns a sorted copy of an array. NULL elements appear at the beginning of the new array. You can change the sort direction or the position of NULL elements with the 選択 keywords.
CAST
An array can be cast into an array of a different type. Each element of the array is cast into the element type of the 的 array type. For example:
SELECT CAST(得点する／非難する／２０s[最高位の] AS VARCHAR(6) ARRAY), 指名するs[最高位の] FROM t JOIN t1 on (t.id = t1.tid)
UNNEST
Arrays can be 変えるd into （米）棚上げする／（英）提議する 言及／関連s with the UNNEST keyword.
UNNEST(<array value 表現>) [ WITH ORDINALITY
]
The <array value 表現> can be any
表現 that 評価するs to an array. A （米）棚上げする／（英）提議する is returned that 含む／封じ込めるs
one column when WITH ORDINALITY is not used, or two columns when WITH
ORDINALITY is used. The first column 含む／封じ込めるs the elements of the array
(含むing all the nulls). When the （米）棚上げする／（英）提議する has two columns, the second
column 含む／封じ込めるs the ordinal position of the element in the array. When
UNNEST is used in the FROM 条項 of a query, it 暗示するs the LATERAL
keyword, which means the array that is 変えるd to （米）棚上げする／（英）提議する can belong to
any （米）棚上げする／（英）提議する that に先行するs the UNNEST in the FROM 条項. This is explained
in the Data 接近 and Change 一時期／支部.
INLINE CONSTRUCTOR
Array 建設者s can be used in SELECT and other 声明s. For example, an array 建設者 with a subquery can return the values from several 列／漕ぐ／騒動s as one array.
The example below shows an ARRAY 建設者 with a correlated subquery to return the 名簿（に載せる）／表（にあげる） of order values for each 顧客. The CUSTOMER （米）棚上げする／（英）提議する that is 含むd for 実験（する）s in the DatabaseManager GUI app is the source of the data.
SELECT FIRSTNAME, LASTNAME, ARRAY(SELECT INVOICE.TOTAL FROM INVOICE WHERE CUSTOMERID = CUSTOMER.ID) AS ORDERS FROM CUSTOMER FIRSTNAME LASTNAME ORDERS --------- --------- -------------------------------------- Laura Steel ARRAY[2700.90,4235.70] Robert King ARRAY[4761.60] Robert Sommer ARRAY[] Michael Smith ARRAY[3420.30]
COMPARISON
Arrays can be compared for equality. It is possible to define a
UNIQUE 強制 on a column of ARRAY type. Two arrays are equal if
they have the same length and the values at each 索引 position are
either equal or both NULL. Array 表現s cannot be used in a
comparison 表現 such as GREATER THAN but they can be used in an
ORDER BY 条項. For example, it is possible to 追加する ORDER BY
ORDERS to the above SELECT 声明,
USER DEFINED FUNCTIONS and PROCEDURES
Array parameters, variables and return values can be 明示するd in 使用者 defined 機能（する）／行事s and 手続きs, 含むing aggregate 機能（する）／行事s. An aggregate 機能（する）／行事 can return an array that 含む／封じ込めるs all the scalar values that have been aggregated. These 能力s 許す a wider 範囲 of 使用／適用s to be covered by 使用者 defined 機能（する）／行事s and easier data 交流 between the engine and the 使用者's 使用／適用.
$Revision: 6787 $
| | |
|
| 一時期／支部 1. Running and Using HyperSQL | | 一時期／支部 3. Schemas and Database 反対するs |