Tag: H2 Database Cheat Sheet

Select

SELECT * FROM table_name;

Insert

INSERT INTO tableName 
{ [ ( columnName [,...] ) ] 
{ VALUES 
{ ( { DEFAULT | expression } [,...] ) } [,...] | [ DIRECT ] [ SORTED ] select } } | 
{ SET { columnName = { DEFAULT | expression } } [,...] }

Update

UPDATE tableName [ [ AS ] newTableAlias ] SET 
{ { columnName = { DEFAULT | expression } } [,...] } | 
{ ( columnName [,...] ) = ( select ) } 
[ WHERE expression ] [ ORDER BY order [,...] ] [ LIMIT expression ]

Delete

DELETE [ TOP term ] FROM tableName [ WHERE expression ] [ LIMIT term ]

Backup

BACKUP TO fileNameString;

Call

CALL expression;

Explain

EXPLAIN { [ PLAN FOR ] | ANALYZE } { select | insert | update | delete | merge} 

Merge

MERGE INTO tableName [ ( columnName [,...] ) ] 
[ KEY ( columnName [,...] ) ] 
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select } 

Show

SHOW { SCHEMAS | TABLES [ FROM schemaName ] | 
COLUMNS FROM tableName [ FROM schemaName ] } 

Create

Create Table

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

Where,

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

Create Schema

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

Create Sequence

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

Alter

Alter Table Add

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

Alter Table Add Constraint

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

Alter Table Rename Constraint

ALTER TABLE [ IF EXISTS ] tableName RENAME oldConstraintName TO newConstraintName

Alter Table Alter Column

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

In the above syntax

• RESTART − command changes the next value of an auto-increment column.
• SELECTIVITY − the command sets the selectivity (1-100) for a column. Based on the selectivity value we can image the value of the column.
• SET DEFAULT − changes the default value of a column.
• SET NULL − sets the column to allow NULL.
• SET NOT NULL − sets the column to allow NOT NULL.

Drop

Drop Table

DROP TABLE [ IF EXISTS ] tableName [,...] [ RESTRICT | CASCADE ]

Note: The command will fail if we are using RESTRICT and the table having dependent views exist. All dependent views are dropped, when we are using CASCADE keyword.

Drop Schema

DROP SCHEMA [ IF EXISTS ] schemaName 

Drop Sequence

DROP SEQUENCE [ IF EXISTS ] sequenceName

Drop View

DROP VIEW [ IF EXISTS ] viewName [ RESTRICT | CASCADE ]

Truncate

TRUNCATE TABLE tableName 

Commit

The generic syntax for the commit command to commit the current transaction.

COMMIT [ WORK ] 

The generic syntax for the commit command to commit the specific transaction.

COMMIT TRANSACTION transactionName

Grant

Grant Right

GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } [,...] ON 
{ { SCHEMA schemaName } | { tableName [,...] } } 
TO { PUBLIC | userName | roleName }

Grant Alter Any Schema

GRANT ALTER ANY SCHEMA TO userName 

Savepoint

SAVEPOINT savepointName

Rollback

The generic syntax for the rollback command.

ROLLBACK [ TO SAVEPOINT savepointName ] 

The generic syntax of the Rollback command to the specific transaction.

ROLLBACK TRANSACTION transactionName 

JDBC Connection

Step 1 − Registering the JDBC database driver.

Class.forName ("org.h2.Driver"); 

Step 2 − Opening the connection.

Connection conn = DriverManager.getConnection ("jdbc:h2:~/test", "sa",""); 

Step 3 − Creating a statement.

Statement st = conn.createStatement(); 

Step 4 − Executing a statement and receiving Resultset.

Stmt.executeUpdate("sql statement"); 

Step 5 − Closing a connection.

conn.close();