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 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 tableName
SET columnName=expression [,...]
[ WHERE condition ]
Updates rows in a table.
Example:
UPDATE HELLO_WORLD SET ID=ID+1
DELETE FROM tableName
[ WHERE condition ]
Deletes rows from a table.
Example:
DELETE FROM HELLO_WORLD
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 [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 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 [IF EXISTS] tableName
Drops a table.
Executing this statement automatically commits any open transaction.
Example:
DROP TABLE HELLO_WORLD
DROP INDEX indexName ON tableName
Executing this statement automatically commits any open transaction.
Example:
DROP INDEX IDXID ON HELLO_WORLD
ALTER TABLE tableName DROP CONSTRAINT constraintName
Executing this statement automatically commits any open transaction.
ALTER TABLE tableName RENAME TO newTableName
Executing this statement automatically commits any open transaction.
ALTER TABLE tableName ADD COLUMN columnName dataType [[NOT] NULL] [DEFAULT value]
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
Commits the current transaction.
ROLLBACK
The current transaction is rolled back.
SET AUTOCOMMIT [ TRUE | FALSE ]
Sets the autocommit mode.
Example:
SET AUTOCOMMIT FALSE
GET AUTOINCREMENT KEY
Gets the last inserted autoincrement value.
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'
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 |
BLOB |
400'000 bytes |
No |
BLOB |
X'01020a0b0c' |
BLOB |
CLOB |
400'000 characters | No |
CLOB |
'Text' |
CLOB |
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 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
The built-in functions are:
Function | Data type | Description | Example |
CAST(value AS type) | variable |
Convert a value. value: original value |
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 |
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() |