CREATE TABLE syntax
CREATE TABLE [ IF NOT EXISTS ] table_name
( column_declare1, column_declare2, constraint_declare1, ... )
column_declare ::= column_name type [ DEFAULT expression ]
[ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]
type ::= BIT | REAL | CHAR | TEXT | DATE | TIME |
FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC |
DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR |
SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR |
JAVA_OBJECT | LONGVARBINARY
constraint_declare :: = [ CONSTRAINT constraint_name ]
PRIMARY KEY ( col1, col2, ... ) |
FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ]
[ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] |
UNIQUE ( col1, col2, ... ) |
CHECK ( expression )
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ NOT DEFERRABLE | DEFERRABLE ]
triggered_action :: =
NO ACTION | SET NULL | SET DEFAULT | CASCADE
CREATE VIEW syntax
CREATE VIEW table_name [ ( column_name1, column_name2, ... ) ]
AS SELECT ...
INSERT syntax
INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
VALUES ( expression1_1, expression1_2, .... ),
( expression2_1, expression2_2, .... ), ....
INSERT INTO table_name [ ( col_name1, col_name2, .... ) ]
SELECT ...
INSERT INTO table_name
SET col_name1 = expression1, col_name2 = expression2, ....
DELETE syntax
DELETE FROM table_name
[ WHERE expression ]
SELECT syntax
SELECT [ DISTINCT | ALL ]
column_expression1, column_expression2, ....
[ FROM from_clause ]
[ WHERE where_expression ]
[ GROUP BY expression1, expression2, .... ]
[ HAVING having_expression ]
[ ORDER BY order_column_expr1, order_column_expr2, .... ]
column_expression ::= expression [ AS ] [ column_alias ]
from_clause ::= select_table1, select_table2, ...
from_clause ::= select_table1 LEFT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 RIGHT [OUTER] JOIN select_table2 ON expr ...
from_clause ::= select_table1 [INNER] JOIN select_table2 ...
select_table ::= table_name [ AS ] [ table_alias ]
select_table ::= ( sub_select_statement ) [ AS ] [ table_alias ]
order_column_expr ::= expression [ ASC | DESC ]
And/Or
WHERE "simple condition"
{[AND|OR] "simple condition"}+
In
WHERE "column_name" IN ('value1', 'value2', ...)
Between
WHERE "column_name" BETWEEN 'value1' AND 'value2'
Like
WHERE "column_name" LIKE {PATTERN}
Count
SELECT COUNT("column_name")
Group By
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
SELECT "column_name1", SUM("column_name2")
FROM "table_name"
GROUP BY "column_name1"
HAVING (arithematic function condition)