Snytax

1. 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

When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,
CREATE TABLE Table ( str_col VARCHAR(100) )
When handling strings the database will only allocate as much storage space as the string uses up. If a 10 character string is stored in str_col then only space for 10 characters will be allocated in the database. So if you need a column that can store a string of any size, use an arbitrarily large number when declaring the column. Mckoi SQL Database does not use a fixed size storage mechanism when storing variable length column data.
JAVA_OBJECT is a column type that can contain serializable Java objects. The JAVA_OBJECT type has an optional Java class definition that is used for runtime class constraint checking. The following example demonstrates creating a JAVA_OBJECT column.
CREATE TABLE ObjectTable (
obj_id NUMERIC, obj JAVA_OBJECT(java.awt.Point))
If the Java class is not specified the column defaults to java.lang.Object which effectively means any type of serializable Java object can be kept in the column.
String types may have a COLLATE clause that changes the collation ordering of the string based on a language. For example, the folling statement creates a string that can store and order Japanese text;
CREATE TABLE InternationalTable (
japanese_text VARCHAR(4000) COLLATE 'jaJP')
The 'jaJP' is an ISO localization code for the Japanese language in Japan. Other locale codes can be found in the documentation to java.text.Collate.
Unique, primary/foreign key and check integrity constraints can be defined in the CREATE TABLE statement. The following is an example of defining a table with integrity constraints.
CREATE TABLE Customer (
number VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
ssn VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,

CONSTRAINT cust_pk PRIMARY KEY (number),
UNIQUE ( ssn ), // (An anonymous constraint)
CONSTRAINT age_check CHECK (age >= 0 AND age < 200)
)

2. 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, ....
This is the SQL command to insert records into a table in the database. This statement comes in three forms. The first inserts data from a VALUES clause;
INSERT INTO table ( col1, col2, col3 )
VALUES ( 10, 4 + 3, CONCAT('1', '1', 'c') ),
( 11, (28 / 2) - 7, CONCAT(col1, 'c') )
The second form is used to copy information from a SELECT query into the table specified in the INSERT statement. For example;
INSERT INTO table ( col1, col2, col3 )
SELECT id, num, description
FROM table2
WHERE description LIKE '11%'
The third form uses a list of column SET assignments. For example;
INSERT INTO table
SET col1 = 10, col2 = 4 + 3, col3 = CONCAT(col1, 'c')
If a column of the table is not specified in an INSERT the default value declared for the column is used. If no default value was declared a NULL value is inserted in the column. If the column is declared as NOT NULL the insert operation fails.
3. 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 ]
The SELECT statement is used to form queries for extracting information out of the database. The following example query will return the number, quantity and price of all orders for more than 5 items sorted in descending order by order number. In addition it rounds the order price to two decimal places and applies a dollar ($) sign to the output.
SELECT number, quantity, CONCAT('$', ROUND(price, 2))
FROM Order
WHERE quantity > 5
ORDER BY number DESC
The ORDER BY and GROUP BY clause may refer to a column, a column alias, or an expression. The HAVING clause is evaluated after the grouping and aggregate columns have been resolved.
For examples of using SELECT with aggregate functions see the 'Internal SQL Functions' section.
4. UPDATE syntax

UPDATE table_name
SET col_name1 = expression1, col_name2 = expression2, ....
[ WHERE expression ]
[ LIMIT limit_amount ]
Updates information in a table. The SET clause is a list of assignments that describe how the columns of the data matched by the WHERE clause are to be updated. Any columns not assigned in the SET clause are left unchanged. Examples of using UPDATE;
UPDATE Employee
SET salary = salary * 1.25
WHERE name = 'Bob'
UPDATE Order
SET id = id + 3, part = CONCAT(part, '-00')
WHERE part LIKE 'PO-%'
LIMIT 10

5. DELETE syntax

DELETE FROM table_name
[ WHERE expression ]
[ LIMIT limit_amount ]
Deletes all the rows from the table that match the WHERE clause. An optional LIMIT clause specifies the maximum number of matched rows to be removed. An example of using the DELETE statement;
DELETE FROM table
WHERE col3 LIKE '11%' AND col1 < 1000 LIMIT 200