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();