H2 SQL cheatsheet

README.md

H2 Console SQL Cheatsheet

This is a collection of the most common commands I run while consulting H2 databases from the console. The variables shown between "[" and "]", should be replaced with your custom names. To make things simpler, other commands just use examples.

Version

https://www.h2database.com/html/systemtables.html#information_schema

SELECT value FROM information_schema.settings WHERE name='info.VERSION'; 

Database

list tables

http://www.h2database.com/html/grammar.html#show

SHOW TABLES;

schemas

http://www.h2database.com/html/grammar.html#show

SHOW SCHEMAS;

Table

list columns

http://www.h2database.com/html/grammar.html#show

SHOW COLUMNS FROM [table];

SELECT

http://h2database.com/html/grammar.html#select

SELECT * FROM [table];

SELECT * FROM [table] WHERE ID=455;

SELECT * FROM [table] WHERE username='paul';

SELECT address, username FROM [table] WHERE ID=455;

SELECT * FROM [table] ORDER BY [column] DESC LIMIT 5;

UPDATE

http://h2database.com/html/grammar.html#update

UPDATE [table] SET username='john' WHERE ID=455;

DELETE

http://h2database.com/html/grammar.html#delete

DELETE FROM [table] WHERE ID = 6;

Delete all records from table

DELETE FROM [table]; 

DROP

http://h2database.com/html/grammar.html#drop_table

DROP TABLE IF EXISTS [table];

http://h2database.com/html/grammar.html#drop_user

DROP USER [user];

Special syntax

http://www.h2database.com/html/tutorial.html#console_syntax

history

List command history, user can easily rerun any of these commands. Show the last 20 statements.

history