h2 pdf 手册

【实例截图】

【核心代码】

Table of Contents
Quickstart.................................................................................................25
Embedding H2 in an Application...........................................................25
The H2 Console Application...................................................................25
Step-by-Step......................................................................................25
Installation......................................................................................25
Start the Console............................................................................26
Login...............................................................................................26
Sample............................................................................................27
Execute...........................................................................................28
Disconnect......................................................................................29
End.................................................................................................29
Installation...............................................................................................30
Requirements........................................................................................30
Database Engine................................................................................30
H2 Console.........................................................................................30
Supported Platforms..............................................................................30
Installing the Software..........................................................................30
Directory Structure................................................................................30
Tutorial.....................................................................................................32
Starting and Using the H2 Console........................................................32
Firewall..............................................................................................34
Testing Java.......................................................................................34
Error Message 'Port may be in use'....................................................34
Using another Port.............................................................................34
Connecting to the Server using a Browser.........................................35
Multiple Concurrent Sessions.............................................................35
Login..................................................................................................35
Error Messages..................................................................................35
Adding Database Drivers...................................................................35
Using the H2 Console.........................................................................36
Inserting Table Names or Column Names..........................................36
Disconnecting and Stopping the Application......................................36
Special H2 Console Syntax....................................................................36
Settings of the H2 Console....................................................................38
Connecting to a Database using JDBC...................................................38
Creating New Databases.......................................................................39
Using the Server....................................................................................40
Starting the Server Tool from Command Line....................................41
Connecting to the TCP Server............................................................41
Starting the TCP Server within an Application....................................41
Stopping a TCP Server from Another Process.....................................41
2 of 428
Using Hibernate....................................................................................42
Using TopLink and Glassfish..................................................................42
Using EclipseLink...................................................................................43
Using Apache ActiveMQ........................................................................43
Using H2 within NetBeans.....................................................................43
Using H2 with jOOQ...............................................................................44
Using Databases in Web Applications....................................................45
Embedded Mode................................................................................45
Server Mode.......................................................................................45
Using a Servlet Listener to Start and Stop a Database.......................45
Using the H2 Console Servlet.............................................................47
CSV (Comma Separated Values) Support..............................................48
Reading a CSV File from Within a Database.......................................48
Importing Data from a CSV File..........................................................48
Writing a CSV File from Within a Database.........................................48
Writing a CSV File from a Java Application.........................................49
Reading a CSV File from a Java Application........................................49
Upgrade, Backup, and Restore..............................................................50
Database Upgrade.............................................................................50
Backup using the Script Tool..............................................................50
Restore from a Script.........................................................................50
Online Backup....................................................................................51
Command Line Tools.............................................................................51
The Shell Tool........................................................................................52
Using OpenOffice Base..........................................................................53
Java Web Start / JNLP.............................................................................54
Using a Connection Pool........................................................................54
Fulltext Search......................................................................................55
Using the Native Fulltext Search........................................................55
Using the Apache Lucene Fulltext Search..........................................56
User-Defined Variables..........................................................................57
Date and Time.......................................................................................58
Using Spring..........................................................................................59
Using the TCP Server.........................................................................59
OSGi......................................................................................................59
Java Management Extension (JMX)........................................................59
Features...................................................................................................61
Feature List...........................................................................................62
Main Features....................................................................................62
Additional Features............................................................................62
SQL Support.......................................................................................62
Security Features...............................................................................63
Other Features and Tools...................................................................63
H2 in Use...............................................................................................64
3 of 428
Connection Modes.................................................................................64
Embedded Mode................................................................................64
Server Mode.......................................................................................65
Mixed Mode........................................................................................65
Database URL Overview........................................................................66
Connecting to an Embedded (Local) Database......................................68
In-Memory Databases...........................................................................68
Database Files Encryption.....................................................................69
Creating a New Database with File Encryption...................................69
Connecting to an Encrypted Database...............................................69
Encrypting or Decrypting a Database.................................................69
Database File Locking...........................................................................70
Opening a Database Only if it Already Exists.........................................70
Closing a Database...............................................................................71
Delayed Database Closing.................................................................71
Don't Close a Database when the VM Exits........................................71
Execute SQL on Connection..................................................................72
Ignore Unknown Settings......................................................................72
Changing Other Settings when Opening a Connection..........................72
Custom File Access Mode......................................................................73
Multiple Connections.............................................................................73
Opening Multiple Databases at the Same Time..................................73
Multiple Connections to the Same Database: Client/Server................73
Multithreading Support......................................................................73
Locking, Lock-Timeout, Deadlocks.....................................................74
Database File Layout.............................................................................74
Moving and Renaming Database Files................................................75
Backup...............................................................................................76
Logging and Recovery...........................................................................76
Compatibility.........................................................................................76
Compatibility Modes...........................................................................76
REGULAR Compatibility mode............................................................76
STRICT Compatibility Mode................................................................77
LEGACY Compatibility Mode...............................................................77
DB2 Compatibility Mode.....................................................................78
Derby Compatibility Mode..................................................................79
HSQLDB Compatibility Mode..............................................................79
MS SQL Server Compatibility Mode....................................................79
MariaDB Compatibility Mode..............................................................80
MySQL Compatibility Mode.................................................................81
Oracle Compatibility Mode.................................................................82
PostgreSQL Compatibility Mode.........................................................83
Auto-Reconnect.....................................................................................84
Automatic Mixed Mode..........................................................................84
4 of 428
Page Size..............................................................................................86
Using the Trace Options........................................................................86
Trace Options.....................................................................................86
Setting the Maximum Size of the Trace File.......................................86
Java Code Generation........................................................................87
Using Other Logging APIs......................................................................87
Read Only Databases............................................................................88
Read Only Databases in Zip or Jar File...................................................88
Opening a Corrupted Database..........................................................89
Generated Columns (Computed Columns) / Function Based Index........89
Multi-Dimensional Indexes....................................................................90
User-Defined Functions and Stored Procedures.....................................90
Referencing a Compiled Method........................................................91
Declaring Functions as Source Code..................................................91
Method Overloading...........................................................................92
Function Data Type Mapping..............................................................92
Functions That Require a Connection.................................................93
Functions Throwing an Exception.......................................................93
Functions Returning a Result Set.......................................................93
Using SimpleResultSet.......................................................................93
Using a Function as a Table...............................................................94
Pluggable or User-Defined Tables..........................................................95
Triggers.................................................................................................96
Compacting a Database........................................................................97
Cache Settings......................................................................................98
External authentication (Experimental).................................................98
Securing your H2....................................................................................101
Introduction.........................................................................................101
Network exposed................................................................................101
Alias / Stored procedures....................................................................101
Grants / Roles / Permissions................................................................101
Encrypted storage...............................................................................101
Performance...........................................................................................103
Performance Comparison....................................................................103
Embedded........................................................................................103
Client-Server....................................................................................104
Benchmark Results and Comments..................................................105
H2.................................................................................................105
HSQLDB........................................................................................105
Derby............................................................................................105
PostgreSQL...................................................................................106
MySQL...........................................................................................106
SQLite...........................................................................................106
Firebird.........................................................................................107
5 of 428
Why Oracle / MS SQL Server / DB2 are Not Listed.........................107
About this Benchmark......................................................................107
How to Run...................................................................................107
Separate Process per Database....................................................107
Number of Connections.................................................................107
Real-World Tests...........................................................................107
Comparing Embedded with Server Databases..............................108
Test Platform................................................................................108
Multiple Runs................................................................................108
Memory Usage..............................................................................108
Delayed Operations......................................................................108
Transaction Commit / Durability....................................................108
Using Prepared Statements..........................................................109
Currently Not Tested: Startup Time..............................................109
PolePosition Benchmark......................................................................109
Database Performance Tuning............................................................110
Keep Connections Open or Use a Connection Pool...........................110
Use a Modern JVM............................................................................111
Virus Scanners.................................................................................111
Using the Trace Options...................................................................111
Index Usage.....................................................................................111
Index Hints.......................................................................................112
How Data is Stored Internally...........................................................112
Optimizer.........................................................................................113
Expression Optimization...................................................................113
COUNT(*) Optimization....................................................................113
Updating Optimizer Statistics / Column Selectivity...........................113
In-Memory (Hash) Indexes...............................................................114
Use Prepared Statements................................................................114
Prepared Statements and IN(...).......................................................114
Optimization Examples....................................................................115
Cache Size and Type........................................................................115
Data Types.......................................................................................115
Sorted Insert Optimization...............................................................115
Using the Built-In Profiler.....................................................................116
Application Profiling............................................................................116
Analyze First....................................................................................116
Database Profiling...............................................................................117
Statement Execution Plans..................................................................118
Displaying the Scan Count...............................................................119
Special Optimizations.......................................................................119
How Data is Stored and How Indexes Work.........................................120
Indexes............................................................................................121
Using Multiple Indexes.....................................................................122
6 of 428
Fast Database Import..........................................................................123
Advanced...............................................................................................124
Result Sets..........................................................................................125
Statements that Return a Result Set................................................125
Limiting the Number of Rows...........................................................125
Large Result Sets and External Sorting............................................125
Large Objects......................................................................................125
Storing and Reading Large Objects..................................................125
When to use CLOB/BLOB..................................................................125
Linked Tables......................................................................................126
Updatable Views.................................................................................127
Transaction Isolation...........................................................................127
Multi-Version Concurrency Control (MVCC)......................................128
Lock Timeout...................................................................................129
Clustering / High Availability...............................................................129
Using the CreateCluster Tool............................................................130
Detect Which Cluster Instances are Running....................................131
Clustering Algorithm and Limitations...............................................131
Two Phase Commit..............................................................................132
Compatibility.......................................................................................132
Transaction Commit when Autocommit is On...................................132
Keywords / Reserved Words................................................................133
Standards Compliance........................................................................137
Supported Character Sets, Character Encoding, and Unicode..........137
Run as Windows Service.....................................................................137
Install the Service............................................................................138
Start the Service..............................................................................138
Connect to the H2 Console...............................................................138
Stop the Service...............................................................................138
Uninstall the Service........................................................................139
Additional JDBC drivers....................................................................139
ODBC Driver........................................................................................139
ODBC Installation.............................................................................139
Starting the Server...........................................................................139
ODBC Configuration.........................................................................140
PG Protocol Support Limitations.......................................................141
Security Considerations...................................................................141
Using Microsoft Access.....................................................................141
ACID....................................................................................................142
Atomicity..........................................................................................142
Consistency......................................................................................142
Isolation...........................................................................................142
Durability.........................................................................................142
Durability Problems.............................................................................142
7 of 428
Ways to (Not) Achieve Durability.....................................................143
Running the Durability Test..............................................................144
Using the Recover Tool.......................................................................145
File Locking Protocols..........................................................................145
File Locking Method 'File'.................................................................146
File Locking Method 'Socket'............................................................147
File Locking Method 'FS'...................................................................148
Using Passwords.................................................................................148
Using Secure Passwords...................................................................148
Passwords: Using Char Arrays instead of Strings..............................148
Passing the User Name and/or Password in the URL........................149
Password Hash....................................................................................149
Protection against SQL Injection..........................................................150
What is SQL Injection.......................................................................150
Disabling Literals..............................................................................151
Using Constants...............................................................................151
Using the ZERO() Function...............................................................152
Protection against Remote Access......................................................152
Restricting Class Loading and Usage...................................................152
Security Protocols...............................................................................153
User Password Encryption................................................................153
File Encryption.................................................................................154
Wrong Password / User Name Delay................................................155
HTTPS Connections..........................................................................155
TLS Connections..................................................................................155
Universally Unique Identifiers (UUID)..................................................156
Spatial Features..................................................................................157
Recursive Queries...............................................................................158
Settings Read from System Properties................................................159
Setting the Server Bind Address..........................................................159
Pluggable File System.........................................................................160
Split File System..................................................................................161
Java Objects Serialization....................................................................161
Limits and Limitations.........................................................................162
Glossary and Links..............................................................................163
Commands.............................................................................................165
Index...................................................................................................165
Commands (Data Manipulation).......................................................165
Commands (Data Definition)............................................................165
Commands (Other)..........................................................................167
Details.................................................................................................168
Commands (Data Manipulation)..........................................................168
SELECT.............................................................................................168
INSERT.............................................................................................171
8 of 428
UPDATE............................................................................................171
DELETE............................................................................................171
BACKUP............................................................................................172
CALL.................................................................................................172
EXECUTE IMMEDIATE.......................................................................172
EXPLAIN...........................................................................................173
MERGE INTO.....................................................................................173
MERGE USING..................................................................................173
RUNSCRIPT.......................................................................................174
SCRIPT.............................................................................................175
SHOW...............................................................................................176
Explicit table....................................................................................176
Table value......................................................................................177
WITH................................................................................................177
Commands (Data Definition)...............................................................178
ALTER DOMAIN.................................................................................178
ALTER DOMAIN ADD CONSTRAINT...................................................179
ALTER DOMAIN DROP CONSTRAINT.................................................179
ALTER DOMAIN RENAME..................................................................179
ALTER DOMAIN RENAME CONSTRAINT.............................................179
ALTER INDEX RENAME......................................................................180
ALTER SCHEMA RENAME..................................................................180
ALTER SEQUENCE............................................................................180
ALTER TABLE ADD............................................................................180
ALTER TABLE ADD CONSTRAINT......................................................181
ALTER TABLE RENAME CONSTRAINT................................................181
ALTER TABLE ALTER COLUMN..........................................................182
ALTER TABLE DROP COLUMN...........................................................183
ALTER TABLE DROP CONSTRAINT.....................................................184
ALTER TABLE SET.............................................................................184
ALTER TABLE RENAME.....................................................................184
ALTER USER ADMIN..........................................................................185
ALTER USER RENAME.......................................................................185
ALTER USER SET PASSWORD...........................................................185
ALTER VIEW RECOMPILE..................................................................186
ALTER VIEW RENAME.......................................................................186
ANALYZE..........................................................................................186
COMMENT ON..................................................................................187
CREATE AGGREGATE........................................................................187
CREATE ALIAS..................................................................................187
CREATE CONSTANT..........................................................................189
CREATE DOMAIN..............................................................................189
CREATE INDEX.................................................................................190
CREATE LINKED TABLE.....................................................................190
9 of 428
CREATE ROLE...................................................................................191
CREATE SCHEMA..............................................................................192
CREATE SEQUENCE..........................................................................192
CREATE TABLE.................................................................................193
CREATE TRIGGER.............................................................................194
CREATE USER...................................................................................196
CREATE VIEW...................................................................................196
DROP AGGREGATE...........................................................................197
DROP ALIAS......................................................................................197
DROP ALL OBJECTS..........................................................................197
DROP CONSTANT.............................................................................197
DROP DOMAIN..................................................................................198
DROP INDEX.....................................................................................198
DROP ROLE......................................................................................198
DROP SCHEMA.................................................................................199
DROP SEQUENCE.............................................................................199
DROP TABLE.....................................................................................199
DROP TRIGGER.................................................................................200
DROP USER......................................................................................200
DROP VIEW......................................................................................200
TRUNCATE TABLE.............................................................................200
Commands (Other)..............................................................................201
CHECKPOINT....................................................................................201
CHECKPOINT SYNC...........................................................................201
COMMIT............................................................................................201
COMMIT TRANSACTION....................................................................202
GRANT RIGHT...................................................................................202
GRANT ALTER ANY SCHEMA.............................................................202
GRANT ROLE....................................................................................203
HELP................................................................................................203
PREPARE COMMIT............................................................................203
REVOKE RIGHT.................................................................................203
REVOKE ALTER ANY SCHEMA...........................................................204
REVOKE ROLE..................................................................................204
ROLLBACK........................................................................................204
ROLLBACK TRANSACTION................................................................204
SAVEPOINT.......................................................................................205
SET @..............................................................................................205
SET ALLOW_LITERALS......................................................................205
SET AUTOCOMMIT............................................................................206
SET CACHE_SIZE..............................................................................206
SET CLUSTER...................................................................................207
SET BUILTIN_ALIAS_OVERRIDE.........................................................207
SET CATALOG...................................................................................207
10 of 428
SET COLLATION................................................................................208
SET DATABASE_EVENT_LISTENER....................................................208
SET DB_CLOSE_DELAY.....................................................................209
SET DEFAULT_LOCK_TIMEOUT..........................................................209
SET DEFAULT_NULL_ORDERING.......................................................210
SET DEFAULT_TABLE_TYPE...............................................................210
SET EXCLUSIVE................................................................................211
SET IGNORECASE.............................................................................211
SET IGNORE_CATALOGS...................................................................212
SET JAVA_OBJECT_SERIALIZER..........................................................212
SET LAZY_QUERY_EXECUTION..........................................................212
SET LOCK_MODE..............................................................................213
SET LOCK_TIMEOUT.........................................................................213
SET MAX_LENGTH_INPLACE_LOB......................................................214
SET MAX_LOG_SIZE..........................................................................214
SET MAX_MEMORY_ROWS................................................................214
SET MAX_MEMORY_UNDO................................................................215
SET MAX_OPERATION_MEMORY.......................................................215
SET MODE........................................................................................216
SET NON_KEYWORDS.......................................................................216
SET OPTIMIZE_REUSE_RESULTS.......................................................216
SET PASSWORD...............................................................................217
SET QUERY_STATISTICS...................................................................217
SET QUERY_STATISTICS_MAX_ENTRIES............................................217
SET QUERY_TIMEOUT.......................................................................218
SET REFERENTIAL_INTEGRITY...........................................................218
SET RETENTION_TIME.......................................................................218
SET SALT HASH................................................................................219
SET SCHEMA....................................................................................219
SET SCHEMA_SEARCH_PATH............................................................220
SET SESSION CHARACTERISTICS......................................................220
SET THROTTLE.................................................................................220
SET TIME ZONE................................................................................221
SET TRACE_LEVEL............................................................................221
SET TRACE_MAX_FILE_SIZE..............................................................221
SET TRUNCATE_LARGE_LENGTH.......................................................222
SET VARIABLE_BINARY.....................................................................222
SET WRITE_DELAY............................................................................222
SHUTDOWN......................................................................................223
Functions................................................................................................224
Index...................................................................................................224
Numeric Functions...........................................................................224
String Functions...............................................................................225
Time and Date Functions.................................................................226
11 of 428
System Functions.............................................................................227
JSON Functions.................................................................................228
Table Functions................................................................................228
Details.................................................................................................229
Numeric Functions..............................................................................229
ABS..................................................................................................229
ACOS................................................................................................229
ASIN.................................................................................................229
ATAN................................................................................................230
COS..................................................................................................230
COSH...............................................................................................230
COT..................................................................................................230
SIN...................................................................................................231
SINH.................................................................................................231
TAN..................................................................................................231
TANH................................................................................................231
ATAN2..............................................................................................231
BITAND.............................................................................................232
BITOR...............................................................................................232
BITXOR.............................................................................................232
BITNOT.............................................................................................233
BITNAND..........................................................................................233
BITNOR............................................................................................233
BITXNOR..........................................................................................233
BITGET.............................................................................................234
BITCOUNT........................................................................................234
LSHIFT..............................................................................................234
RSHIFT.............................................................................................235
ULSHIFT...........................................................................................235
URSHIFT...........................................................................................236
ROTATELEFT....................................................................................236
ROTATERIGHT..................................................................................236
MOD.................................................................................................237
CEIL..................................................................................................237
DEGREES..........................................................................................237
EXP..................................................................................................237
FLOOR..............................................................................................238
LN....................................................................................................238
LOG..................................................................................................238
LOG10..............................................................................................238
ORA_HASH.......................................................................................239
RADIANS..........................................................................................239
SQRT................................................................................................239
PI......................................................................................................239
12 of 428
POWER.............................................................................................240
RAND...............................................................................................240
RANDOM_UUID.................................................................................240
ROUND.............................................................................................240
ROUNDMAGIC..................................................................................241
SECURE_RAND.................................................................................241
SIGN.................................................................................................241
ENCRYPT..........................................................................................241
DECRYPT..........................................................................................242
HASH................................................................................................242
TRUNC..............................................................................................243
COMPRESS.......................................................................................243
EXPAND............................................................................................243
ZERO................................................................................................244
String Functions..................................................................................244
ASCII................................................................................................244
BIT_LENGTH.....................................................................................244
CHAR_LENGTH.................................................................................244
OCTET_LENGTH................................................................................245
CHAR................................................................................................245
CONCAT...........................................................................................245
CONCAT_WS.....................................................................................245
DIFFERENCE.....................................................................................246
HEXTORAW......................................................................................246
RAWTOHEX......................................................................................246
INSERT Function...............................................................................246
LOWER.............................................................................................247
UPPER..............................................................................................247
LEFT.................................................................................................247
RIGHT...............................................................................................247
LOCATE............................................................................................247
LPAD................................................................................................248
RPAD................................................................................................248
LTRIM...............................................................................................248
RTRIM...............................................................................................249
TRIM.................................................................................................249
REGEXP_REPLACE............................................................................249
REGEXP_LIKE....................................................................................250
REGEXP_SUBSTR..............................................................................250
REPEAT............................................................................................251
REPLACE..........................................................................................251
SOUNDEX.........................................................................................251
SPACE..............................................................................................252
STRINGDECODE...............................................................................252
13 of 428
STRINGENCODE...............................................................................252
STRINGTOUTF8................................................................................252
SUBSTRING......................................................................................253
UTF8TOSTRING................................................................................253
QUOTE_IDENT..................................................................................253
XMLATTR..........................................................................................253
XMLNODE.........................................................................................254
XMLCOMMENT..................................................................................254
XMLCDATA.......................................................................................254
XMLSTARTDOC.................................................................................254
XMLTEXT..........................................................................................255
TO_CHAR..........................................................................................255
TRANSLATE......................................................................................255
Time and Date Functions.....................................................................255
CURRENT_DATE...............................................................................255
CURRENT_TIME................................................................................256
CURRENT_TIMESTAMP......................................................................256
LOCALTIME.......................................................................................257
LOCALTIMESTAMP............................................................................257
DATEADD.........................................................................................258
DATEDIFF.........................................................................................258
DATE_TRUNC....................................................................................258
DAYNAME.........................................................................................259
DAY_OF_MONTH...............................................................................259
DAY_OF_WEEK..................................................................................259
ISO_DAY_OF_WEEK...........................................................................259
DAY_OF_YEAR..................................................................................260
EXTRACT..........................................................................................260
FORMATDATETIME...........................................................................260
HOUR...............................................................................................261
MINUTE............................................................................................261
MONTH.............................................................................................261
MONTHNAME....................................................................................261
PARSEDATETIME..............................................................................261
QUARTER.........................................................................................262
SECOND...........................................................................................262
WEEK...............................................................................................262
ISO_WEEK........................................................................................263
YEAR................................................................................................263
ISO_YEAR.........................................................................................263
System Functions................................................................................263
ABORT_SESSION..............................................................................263
ARRAY_GET......................................................................................264
CARDINALITY....................................................................................264
14 of 428
ARRAY_CONTAINS............................................................................264
ARRAY_CAT......................................................................................265
ARRAY_APPEND................................................................................265
ARRAY_MAX_CARDINALITY...............................................................265
TRIM_ARRAY.....................................................................................265
ARRAY_SLICE....................................................................................266
AUTOCOMMIT...................................................................................266
CANCEL_SESSION.............................................................................266
CASEWHEN Function........................................................................266
COALESCE........................................................................................267
CONVERT.........................................................................................267
CURRVAL..........................................................................................267
CSVWRITE........................................................................................268
CURRENT_SCHEMA...........................................................................268
CURRENT_CATALOG.........................................................................268
DATABASE_PATH..............................................................................269
DATA_TYPE_SQL...............................................................................269
DB_OBJECT_ID..................................................................................270
DB_OBJECT_SQL...............................................................................270
DECODE...........................................................................................271
DISK_SPACE_USED...........................................................................271
SIGNAL.............................................................................................271
ESTIMATED_ENVELOPE.....................................................................272
FILE_READ........................................................................................272
FILE_WRITE......................................................................................272
GREATEST........................................................................................273
LEAST...............................................................................................273
LOCK_MODE.....................................................................................273
LOCK_TIMEOUT................................................................................273
MEMORY_FREE.................................................................................273
MEMORY_USED................................................................................274
NEXTVAL..........................................................................................274
NULLIF..............................................................................................274
NVL2................................................................................................275
READONLY.......................................................................................275
ROWNUM.........................................................................................275
SESSION_ID......................................................................................276
SET...................................................................................................276
TRANSACTION_ID.............................................................................276
TRUNCATE_VALUE............................................................................276
CURRENT_PATH................................................................................277
CURRENT_ROLE................................................................................277
CURRENT_USER...............................................................................277
H2VERSION......................................................................................277
15 of 428
JSON Functions....................................................................................278
JSON_OBJECT....................................................................................278
JSON_ARRAY.....................................................................................278
Table Functions...................................................................................278
CSVREAD.........................................................................................278
LINK_SCHEMA...................................................................................280
TABLE..............................................................................................280
UNNEST............................................................................................280
Aggregate Functions..............................................................................281
Index...................................................................................................281
General Aggregate Functions...........................................................281
Binary Set Functions........................................................................281
Ordered Aggregate Functions..........................................................282
Hypothetical Set Functions...............................................................282
Inverse Distribution Functions..........................................................282
JSON Aggregate Functions...............................................................282
Details.................................................................................................282
General Aggregate Functions..............................................................282
AVG..................................................................................................282
MAX.................................................................................................283
MIN..................................................................................................283
SUM.................................................................................................283
EVERY..............................................................................................284
ANY..................................................................................................284
COUNT.............................................................................................284
STDDEV_POP....................................................................................285
STDDEV_SAMP.................................................................................285
VAR_POP..........................................................................................285
VAR_SAMP........................................................................................285
BIT_AND_AGG..................................................................................286
BIT_OR_AGG.....................................................................................286
BIT_XOR_AGG...................................................................................286
BIT_NAND_AGG................................................................................287
BIT_NOR_AGG..................................................................................287
BIT_XNOR_AGG................................................................................287
ENVELOPE........................................................................................287
Binary Set Functions...........................................................................288
COVAR_POP......................................................................................288
COVAR_SAMP...................................................................................288
CORR...............................................................................................288
REGR_SLOPE....................................................................................289
REGR_INTERCEPT.............................................................................289
REGR_COUNT...................................................................................289
REGR_R2..........................................................................................290
16 of 428
REGR_AVGX.....................................................................................290
REGR_AVGY......................................................................................290
REGR_SXX........................................................................................291
REGR_SYY........................................................................................291
REGR_SXY........................................................................................291
Ordered Aggregate Functions.............................................................291
LISTAGG...........................................................................................291
ARRAY_AGG.....................................................................................292
Hypothetical Set Functions..................................................................293
RANK aggregate...............................................................................293
DENSE_RANK aggregate..................................................................293
PERCENT_RANK aggregate...............................................................293
CUME_DIST aggregate.....................................................................294
Inverse Distribution Functions.............................................................294
PERCENTILE_CONT...........................................................................294
PERCENTILE_DISC............................................................................295
MEDIAN............................................................................................295
MODE...............................................................................................295
JSON Aggregate Functions...................................................................296
JSON_OBJECTAGG.............................................................................296
JSON_ARRAYAGG..............................................................................296
Window Functions..................................................................................298
Index...................................................................................................298
Row Number Function......................................................................298
Rank Functions.................................................................................298
Lead or Lag Functions......................................................................298
Nth Value Functions.........................................................................298
Other Window Functions..................................................................298
Details.................................................................................................298
Row Number Function.........................................................................298
ROW_NUMBER..................................................................................298
Rank Functions....................................................................................299
RANK................................................................................................299
DENSE_RANK....................................................................................299
PERCENT_RANK................................................................................300
CUME_DIST.......................................................................................300
Lead or Lag Functions.........................................................................301
LEAD................................................................................................301
LAG..................................................................................................301
Nth Value Functions............................................................................302
FIRST_VALUE....................................................................................302
LAST_VALUE.....................................................................................302
NTH_VALUE......................................................................................303
Other Window Functions.....................................................................303
17 of 428
NTILE................................................................................................303
RATIO_TO_REPORT...........................................................................304
Data Types.............................................................................................305
Index...................................................................................................305
Details.................................................................................................306
CHARACTER........................................................................................306
CHARACTER VARYING.........................................................................306
CHARACTER LARGE OBJECT................................................................307
VARCHAR_IGNORECASE......................................................................308
BINARY................................................................................................308
BINARY VARYING.................................................................................309
BINARY LARGE OBJECT........................................................................309
BOOLEAN............................................................................................310
TINYINT...............................................................................................310
SMALLINT............................................................................................310
INTEGER..............................................................................................311
BIGINT.................................................................................................311
NUMERIC.............................................................................................311
REAL....................................................................................................311
DOUBLE PRECISION.............................................................................312
DECFLOAT...........................................................................................312
DATE...................................................................................................312
TIME....................................................................................................313
TIME WITH TIME ZONE.........................................................................314
TIMESTAMP.........................................................................................314
TIMESTAMP WITH TIME ZONE..............................................................315
INTERVAL............................................................................................316
JAVA_OBJECT.......................................................................................316
ENUM..................................................................................................317
GEOMETRY..........................................................................................317
JSON....................................................................................................318
UUID....................................................................................................319
ARRAY.................................................................................................319
ROW....................................................................................................320
Interval Data Types.............................................................................320
INTERVAL YEAR................................................................................320
INTERVAL MONTH............................................................................320
INTERVAL DAY..................................................................................321
INTERVAL HOUR...............................................................................321
INTERVAL MINUTE............................................................................321
INTERVAL SECOND...........................................................................321
INTERVAL YEAR TO MONTH..............................................................322
INTERVAL DAY TO HOUR..................................................................322
INTERVAL DAY TO MINUTE...............................................................322
18 of 428
INTERVAL DAY TO SECOND..............................................................323
INTERVAL HOUR TO MINUTE............................................................323
INTERVAL HOUR TO SECOND...........................................................323
INTERVAL MINUTE TO SECOND........................................................324
SQL Grammar.........................................................................................325
Index...................................................................................................325
Literals.............................................................................................325
Datetime fields.................................................................................326
Other Grammar................................................................................326
Details.................................................................................................329
Literals................................................................................................329
Value................................................................................................329
Approximate numeric.......................................................................329
Array................................................................................................329
Boolean............................................................................................330
Bytes................................................................................................330
Date.................................................................................................330
Date and time..................................................................................330
Dollar Quoted String........................................................................331
Exact numeric..................................................................................331
Hex Number.....................................................................................331
Int....................................................................................................331
GEOMETRY.......................................................................................332
JSON.................................................................................................332
Long.................................................................................................332
Null..................................................................................................333
Number............................................................................................333
Numeric...........................................................................................333
String...............................................................................................333
UUID................................................................................................334
Time.................................................................................................334
Time with time zone.........................................................................334
Timestamp.......................................................................................335
Timestamp with time zone...............................................................335
Interval............................................................................................335
INTERVAL YEAR................................................................................336
INTERVAL MONTH............................................................................336
INTERVAL DAY..................................................................................336
INTERVAL HOUR...............................................................................336
INTERVAL MINUTE............................................................................336
INTERVAL SECOND...........................................................................337
INTERVAL YEAR TO MONTH..............................................................337
INTERVAL DAY TO HOUR..................................................................337
INTERVAL DAY TO MINUTE...............................................................337
19 of 428
INTERVAL DAY TO SECOND..............................................................338
INTERVAL HOUR TO MINUTE............................................................338
INTERVAL HOUR TO SECOND...........................................................338
INTERVAL MINUTE TO SECOND........................................................338
Datetime fields....................................................................................339
Datetime field..................................................................................339
Year field..........................................................................................339
Month field.......................................................................................339
Day of month field...........................................................................340
Hour field.........................................................................................340
Minute field......................................................................................340
Second field.....................................................................................340
Timezone hour field.........................................................................340
Timezone minute field......................................................................341
Timezone second field.....................................................................341
Millennium field................................................................................341
Century field....................................................................................341
Decade field.....................................................................................341
Quarter field.....................................................................................342
Millisecond field...............................................................................342
Microsecond field.............................................................................342
Nanosecond field.............................................................................342
Day of year field...............................................................................342
ISO day of week field.......................................................................343
ISO week field..................................................................................343
ISO week year field..........................................................................343
Day of week field.............................................................................343
Week field........................................................................................344
Week year field................................................................................344
Epoch field.......................................................................................344
Other Grammar...................................................................................344
Alias.................................................................................................344
And Condition..................................................................................345
Array element reference..................................................................345
Field reference.................................................................................345
Array value constructor by query.....................................................345
Case expression...............................................................................346
Simple case......................................................................................346
Searched case..................................................................................346
Cast specification.............................................................................347
Cipher..............................................................................................347
Column Definition............................................................................347
Column Constraint Definition...........................................................348
Comment.........................................................................................349
20 of 428
Bracketed comment.........................................................................349
Compare..........................................................................................350
Condition..........................................................................................350
Condition Right Hand Side...............................................................350
Comparison Right Hand Side............................................................351
Quantified Comparison Right Hand Side..........................................351
Null Predicate Right Hand Side.........................................................352
Distinct Predicate Right Hand Side...................................................352
Quantified Distinct Predicate Right Hand Side..................................352
Boolean Test Right Hand Side..........................................................353
Type Predicate Right Hand Side.......................................................353
JSON Predicate Right Hand Side.......................................................354
Between Predicate Right Hand Side.................................................354
In Predicate Right Hand Side............................................................354
Like Predicate Right Hand Side........................................................355
Regexp Predicate Right Hand Side...................................................355
Table Constraint Definition...............................................................355
Constraint Name Definition..............................................................356
Csv Options......................................................................................356
Data Change Delta Table.................................................................357
Data Type or Domain.......................................................................358
Data Type........................................................................................358
Predefined Type...............................................................................358
Digit.................................................................................................359
Expression.......................................................................................359
Factor...............................................................................................359
Grouping element............................................................................359
Hex..................................................................................................359
Index Column...................................................................................360
Insert values....................................................................................360
Interval qualifier...............................................................................360
Join specification..............................................................................360
Merge when clause..........................................................................361
Merge when matched clause............................................................361
Merge when not matched clause.....................................................361
Name...............................................................................................361
Operand...........................................................................................362
Override clause................................................................................362
Query...............................................................................................363
Quoted Name...................................................................................363
Referential Constraint......................................................................364
References Specification..................................................................364
Referential Action............................................................................364
Script Compression Encryption........................................................365
21 of 428
Select order.....................................................................................365
Row value expression......................................................................365
Select Expression.............................................................................366
Sequence value expression..............................................................366
Sequence option..............................................................................366
Alter sequence option......................................................................367
Alter identity column option.............................................................367
Basic sequence option.....................................................................368
Set clause list...................................................................................369
Sort specification.............................................................................369
Sort specification list........................................................................369
Summand........................................................................................369
Table Expression..............................................................................370
Within group specification................................................................370
Wildcard expression.........................................................................370
Window name or specification.........................................................371
Window specification.......................................................................371
Window frame..................................................................................371
Window frame preceding.................................................................372
Window frame bound.......................................................................373
Term................................................................................................373
Time zone........................................................................................374
Column............................................................................................374
System Tables........................................................................................375
Index...................................................................................................375
Information Schema............................................................................376
CHECK_CONSTRAINTS......................................................................376
COLLATIONS.....................................................................................376
COLUMNS.........................................................................................377
COLUMN_PRIVILEGES.......................................................................380
CONSTANTS.....................................................................................381
CONSTRAINT_COLUMN_USAGE.........................................................383
DOMAINS.........................................................................................384
DOMAIN_CONSTRAINTS....................................................................386
ELEMENT_TYPES...............................................................................387
ENUM_VALUES.................................................................................389
FIELDS..............................................................................................389
INDEXES...........................................................................................391
INDEX_COLUMNS.............................................................................392
INFORMATION_SCHEMA_CATALOG_NAME........................................393
IN_DOUBT........................................................................................393
KEY_COLUMN_USAGE.......................................................................394
LOCKS..............................................................................................394
PARAMETERS...................................................................................395
22 of 428
QUERY_STATISTICS..........................................................................397
REFERENTIAL_CONSTRAINTS............................................................398
RIGHTS.............................................................................................399
ROLES..............................................................................................399
ROUTINES........................................................................................399
SCHEMATA.......................................................................................402
SEQUENCES.....................................................................................403
SESSIONS.........................................................................................404
SESSION_STATE...............................................................................405
SETTINGS.........................................................................................405
SYNONYMS.......................................................................................406
TABLES.............................................................................................406
TABLE_CONSTRAINTS.......................................................................407
TABLE_PRIVILEGES...........................................................................408
TRIGGERS........................................................................................409
USERS..............................................................................................410
VIEWS..............................................................................................410
Range Table........................................................................................411
Build.......................................................................................................413
Portability............................................................................................413
Environment........................................................................................413
Building the Software..........................................................................413
Using Apache Lucene.......................................................................414
Using Maven 2.....................................................................................414
Using a Central Repository...............................................................414
Maven Plugin to Start and Stop the TCP Server................................414
Using Snapshot Version...................................................................415
Using Eclipse.......................................................................................415
Translating..........................................................................................415
Submitting Source Code Changes.......................................................416
Reporting Problems or Requests.........................................................417
Automated Build.................................................................................418
Generating Railroad Diagrams............................................................418
History....................................................................................................419
Change Log.........................................................................................419
History of this Database Engine..........................................................419
Why Java.............................................................................................419
Supporters..........................................................................................420
Frequently Asked Questions...................................................................423
I Have a Problem or Feature Request...............................................423
Are there Known Bugs? When is the Next Release?.........................423
Is this Database Engine Open Source?.............................................424
Is Commercial Support Available?....................................................424
How to Create a New Database?......................................................424
23 of 428
How to Connect to a Database?.......................................................424
Where are the Database Files Stored?.............................................424
What is the Size Limit (Maximum Size) of a Database?....................425
Is it Reliable?....................................................................................425
Why is Opening my Database Slow?................................................426
My Query is Slow..............................................................................426
H2 is Very Slow................................................................................427
Column Names are Incorrect?..........................................................427
Float is Double?................................................................................427
How to Translate this Project?..........................................................427
How to Contribute to this Project?....................................................427