Grammar

INSERT
UPDATE
DELETE
SELECT
CREATE TABLE
CREATE INDEX
DROP TABLE
DROP INDEX
ALTER TABLE
COMMIT
ROLLBACK
SET AUTOCOMMIT
GET AUTOINCREMENT KEY
Conditions, Expressions, Values
Data Types
Keywords
Functions

INSERT

INSERT INTO tableName [ (columnName [,...] ) ] VALUES ( value [,...] )

Inserts a single row into a table.

INSERT INTO tableName [ (columnName [,...] ) ] SELECT ...

Inserts rows into a table using a SELECT statement.

Inserting into an table with autoincrement column

If the table contains a autoincrement column, and INSERT INTO ... VALUES is used, then the value for the autoincrement column is set automatically. For example, if the table contains the columns ID and NAME, and ID is the autoincrement column, then the following inserts will work:
INSERT INTO TEST VALUES('Hello')
INSERT INTO TEST(NAME) VALUES('Hello')
However it is also possible to specify the value for ID explicitly:
INSERT INTO TEST(ID, NAME) VALUES(10,'Hello')
If the table contains a autoincrement column, then the values for the autoincrement column need to be set explicitly.

Examples:

INSERT INTO HELLO_WORLD VALUES(1)

INSERT INTO HELLO_WORLD(ID, NAME) SELECT ID, NAME FROM OLD_TABLE

UPDATE

UPDATE tableName
SET columnName=expression [,...]
[ WHERE condition ]

Updates rows in a table.

Example:

UPDATE HELLO_WORLD SET ID=ID+1

DELETE

DELETE FROM tableName
[ WHERE condition ]

Deletes rows from a table.

Example:

DELETE FROM HELLO_WORLD

SELECT

SELECT [DISTINCT] { * | selectList }
FROM tableList
[ WHERE condition ]
[ GROUP BY columnName [,...] ]
[ HAVING condition ]
[ ORDER BY columnName [{ASC|DESC}] [,...] ]

Queries one or more tables.

tableList:
tableName [alias] [, | LEFT OUTER JOIN | INNER JOIN tableList]

selectList:
{ tableName.* | expression [ AS alias ]} [,...]

Example:

SELECT * FROM HELLO_WORLD

SELECT ID, COUNT(*) FROM TEST GROUP BY ID HAVING COUNT(*)>1

CREATE TABLE

CREATE TABLE [IF NOT EXISTS] tableName (
columnDefinition [,...]
[,PRIMARY KEY(column [,...])]
[,FOREIGN KEY(column [,...]) REFERENCES tableName ( column [,...])]
)

Creates a new table.

columnDefinition:
columnName dataType [[NOT] NULL] [PRIMARY KEY] [DEFAULT value]

dataType:
{
INT
| INT AUTOINCREMENT
| VARCHAR(size)
| DECIMAL(precision,scale)
| DATETIME
| BLOB
| CLOB
}

Executing this statement automatically commits any open transaction.

See also:

Data Types

Examples:

CREATE TABLE HELLO_WORLD (ID INT)

CREATE TABLE IF NOT EXISTS TEST(ID INT PRIMARY KEY,NAME VARCHAR(255))

CREATE TABLE ORDERLINE(ORDER_ID INT,LINE INT,TEXT VARCHAR(255),AMOUNT DECIMAL(10,2),PRIMARY KEY(ORDER_ID,LINE)) 

CREATE TABLE PARENT(ID INT PRIMARY KEY)

CREATE TABLE CHILD(P_ID INT,ID INT,PRIMARY KEY(P_ID,ID),FOREIGN KEY(P_ID) REFERENCES PARENT(ID))

CREATE TABLE AUTOINC(ID INT AUTOINCREMENT PRIMARY KEY,VALUE VARCHAR(255))

CREATE INDEX

CREATE INDEX indexName ON tableName ( columnName [,...] )

Creates a new index in a table. To drop the index, the table has to be dropped.

Executing this statement automatically commits any open transaction.

Example:

CREATE INDEX IDXID ON HELLO_WORLD (ID)

DROP TABLE

DROP TABLE [IF EXISTS] tableName

Drops a table.

Executing this statement automatically commits any open transaction.

Example:

DROP TABLE HELLO_WORLD

DROP INDEX

DROP INDEX indexName ON tableName

Drops an index.

Executing this statement automatically commits any open transaction.

Example:

DROP INDEX IDXID ON HELLO_WORLD

ALTER TABLE

ALTER TABLE tableName DROP CONSTRAINT constraintName

Drops a foreign key constraint. The first constraint is called FK_1, the second FK_2 and so on.

Executing this statement automatically commits any open transaction.

ALTER TABLE tableName RENAME TO newTableName

Renames a table.

Executing this statement automatically commits any open transaction.

ALTER TABLE tableName ADD COLUMN columnName dataType [[NOT] NULL] [DEFAULT value]

Adds a column to an existing table.

Executing this statement automatically commits any open transaction.

Examples:

ALTER TABLE CHILD DROP CONSTRAINT FK_1

ALTER TABLE TEST RENAME TO TESTING

ALTER TABLE TEST ADD COLUMN Y INT NOT NULL

COMMIT

COMMIT

Commits the current transaction.

ROLLBACK

ROLLBACK

The current transaction is rolled back.

SET AUTOCOMMIT

SET AUTOCOMMIT [ TRUE | FALSE ]

Sets the autocommit mode.

Example:

SET AUTOCOMMIT FALSE

GET AUTOINCREMENT KEY

GET AUTOINCREMENT KEY

Gets the last inserted autoincrement value.

Conditions, Expressions, Values

condition:
logicalTerm [ OR logicalTerm ...]

logicalTerm:
logicalFactor [ AND logicalFactor ...]

logicalFactor:
EXISTS ( <select> )
| logicalComparison

logicalComparison:
expression {
= expression
| > expression
| < expression
| >= expression
| <= expression
| <> expression
| != expression
| [NOT] BETWEEN expression AND expression
| [NOT] LIKE expression [ESCAPE quotedString]
| IS [NOT] NULL
| IN (expression [, expression...])
}

expression:
term [ { + | - } term ...]

Remark: The operator + is for integer or decimal addition, not String concatenation.

term:
factor [ {* | / } factor ...]

Remark: the results of overflow or underflow are undefined and different across databases.

factor:
factorPlusMinus [ || factorPlusMinus...]

Remark: The operator || is for String concatenation, not a logical OR.

factorPlusMinus:
[ NOT ] factorPlusMinus | value | columnName | ( expression ) | function

value:
[-] number
| 'string'
| NULL
| ?
| DATE 'yyyy-mm-dd'
| TIMESTAMP 'yyyy-mm-dd hh:mm:ss'
| X'001122'

In a String, the ' character needs to be written twice, for example 'Joes''s Taxi' will result in: Joe's Taxi

Examples:

condition:
NAME LIKE 'T%' OR NAME IS NULL

expression:
( ID + 1 ) * 2
DATE '2002-01-01'
X'01ab'

Data Types

The list of data types is:

Data type name Limitation Searchable Data type definition Constant java.sql.Types
INT -(2^31) .. (2^31-1) Yes INT 1 INTEGER
VARCHAR 255 characters Yes VARCHAR(255) 'Hello' VARCHAR
DECIMAL 20 precision, 10 scale Yes DECIMAL(10,2) 3.14 DECIMAL
DATETIME 1 second precision,
years 1800 - 9999
Yes DATETIME

DATE '2002-01-01' or
TIMESTAMP '2002-01-01 20:00:00'

TIMESTAMP
BLOB
400'000 bytes
No
BLOB
X'01020a0b0c'
BLOB
CLOB
400'000 characters No
CLOB
'Text'
CLOB

Data Type Aliases

In order to support existing applications, the following data type aliases are supported. Please don't use them in new applications:

Date Type
Aliases
INT
INTEGER, SMALLINT, BIT, TINYINT, BOOLEAN
VARCHAR
CHAR
DECIMAL
NUMERIC, DEC, REAL, FLOAT, DOUBLE, BIGINT
DATETIME
DATE, TIME, TIMESTAMP
BLOB
BINARY, VARBINARY, LONGVARBINARY, IMAGE
CLOB
TEST, LONGVARCHAR

Keywords

Keywords are identifiers that can not be used as table names or column names. The list of words is:

ABS, ADD, ALL, ALTER, AND, AS, ASC, AVG, BEFORE, BETWEEN,
BIGINT, BINARY, BIT, BLOB, BOOLEAN, BOTH, BY, CACHED,
CASCADE, CASE, CAST, CHAR, CHARACTER, CHARACTER_LENGTH,
CHAR_LENGTH, CLOB, COLUMN, COMMIT, CONCAT, CONSTRAINT, COUNT,
CREATE, CROSS, CURRENT_DATE, CURRENT_TIME,
CURRENT_TIMESTAMP, DATABASE, DATE, DATETIME, DEC,
DECIMAL, DEFAULT, DELETE, DESC, DISTINCT, DOUBLE, DROP,
EXISTS, EXTRACT, FALSE, FLOAT, FOR, FOREIGN, FROM, GRANT,
GROUP, HAVING, IF, IMAGE, IN, INDEX, INFILE, INNER, INSERT,
INT, INTEGER, INTO, IS, JOIN, KEY, KILL, LEADING, LEFT,
LENGTH, LIKE, LIMIT, LINENO, LOAD, LOB, LOCAL, LOCATE,
LOCK, LONG, LONGVARBINARY, LONGVARCHAR, LOWER, MATCH,
MAX, MEDIUMINT, MIN, MOD, NATURAL, NOT, NULL, NUMERIC,
OBJECT, OCTET_LENGTH, ON, OPTION, OR, ORDER, OTHER, OUTER,
OUTFILE, POSITION, PRECISION, PRIMARY, PRIVILEGES,
PROCEDURE, READ, REAL, REFERENCES, RENAME, REPLACE,
RESTRICT, RETURNS, REVOKE, RIGHT, ROLLBACK, SAVEPOINT,
SELECT, SESSION_USER, SET, SMALLINT, SQRT, SUBSTRING,
SUM, SYSDATE, TABLE, TEMP, TEXT, TIME, TIMESTAMP, TINYINT,
TO, TOP, TRAILING, TRIGGER, TRIM, TRUE, UNION, UNIQUE,
UNSIGNED, UPDATE, UPPER, USER, USING, VALUES, VARBINARY,
VARCHAR, VARCHAR_IGNORECASE, WHEN, WHERE, WITH, WRITE,
ZEROFILL

Functions

The built-in functions are:

Function Data type Description Example
CAST(value AS type) variable

Convert a value.

value: original value
type: target data type

CAST('5' AS INT) = 5
LENGTH(text) INT text: VARCHAR LENGTH('Hello') = 5
MOD(value,dividend) INT

Modulo function: returns the remainder after x is divided by the dividend

MOD(10, 3) = 1
CONCAT(s1,s2) VARCHAR Concatenates two Strings CONCAT('A', 'B') = 'AB'
LOWER(s) VARCHAR Converts a String to lowercase.
LOWER('Hello') = 'hello'
UPPER(s) VARCHAR Converts a String to lowercase. UPPER('Hello') = 'HELLO'
NOW() DATETIME Gets the current timestamp

NOW() = '2002-08-16'

Aggregates are only supported for SELECT:

Function Data type Description Example
COUNT(*) INT

Count all rows

COUNT(*)
COUNT(value) INT

Count all rows

COUNT(1)
COUNT(column|function) INT Count the columns that are non-null COUNT(VALUE)
COUNT(DISTINCT column|function) INT Count the unique columns/values that are non-null COUNT(DISTINCT ID)
MIN(column) any *

The lowest value, NULL if no value

MIN(ID)
MAX(column)
any *

The highest value, NULL if no value

MAX(ID)+1
SUM(column) numeric * Sums a column, NULLs are counted as 0 SUM(VALUE)
AVG(column) numeric * Sums a column, and then divides by number of non-null values; same as SUM(column)/COUNT(column) AVG(VALUE)

*any: except BLOB or CLOB

* numeric: INT or DECIMAL

Function Aliases

In order to support existing applications, the following function type aliases are supported. Please don't use them in new applications:

Function
Aliases
UPPER(s) UCASE(s)
LOWER(s) LCASE(s)
CAST(value AS type) CONVERT(value, type)
LENGTH(text) CHAR_LENGTH(text), CHARACTER_LENGTH(text)
NOW() CURRENT_DATE(), CURRENT_TIME(), CURDATE(), CURTIME()