SELECT Statement
SELECT select_expression [, select_expression . . .]
[FROM table_expression
[WHERE general_expression]
[ORDER BY general_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ] ]
Note: SELECT statements used with a FROM command will retrieve data from the table(s) specified in the table_expression (see below). Usage without a FROM command will return a single row, executing any specified expressions in the select_expression (see below). At least one select_expression is required.
UPDATE Statement
UPDATE table_expression
SET column1_name=general_expression [, column2_name=general_expression . . .]
[WHERE where_expression]
[ORDER BY order_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ]
Note: UPDATE statements used with an ORDER BY command will control the order in which file operations are performed. This can be useful if the order of updating filenames may otherwise cause a name collision with pre-existing files. If the SET command includes any expressions with column names, the value of the field in the currently updating row will be used. LIMIT will constraint which files are updated from the total UPDATE rowset.
INSERT Statement
INSERT INTO table_name
[(column1_name, ...)]
{VALUES | VALUE} (general_expression, . . .)
INSERT INTO table_name
SET column1_name=general_expression [, column2_name=general_expression . . .]
Note: INSERT statements may use either syntax shown above. If the first syntax is used without specifying column names, the number of VALUES/VALUE expressions specified must equal the number of columns in the table. For read only fields, the value specified is ignored.
DELETE Statement
DELETE {table_name[.*] | *}
FROM table_expression
[WHERE general_expression]
[ORDER BY general_expression [ASC | DESC], . . .]
[LIMIT [row_offset,] row_count ]
Note: DELETE statements used with an ORDER BY command will control the order in which file operations are performed. LIMIT will constraint which files are deleted from the total DELETE rowset.
Select Expressions
{general_expression | [table_name.] { * | column_name} } [[AS] alias]
Note: Table and column names may be delineated using the ` character.
Table Expressions
{table1_name} [[AS] alias] [, {table2_name} [ [AS] alias] . . .]
[[INNER | LEFT [OUTER] | CROSS] JOIN table_name
[ON general_expression] . . .]
Note: Table and column names may be delineated using the ` character. Comma separated tables specified after the first table in a table_expression before JOIN commands will be treated as CROSS JOINed tables.
General Expression Operators and Functions
|
Literal |
Operands/Arguments |
Precedence |
Description |
|
= |
binary |
1 |
Assign. Recognized in UPDATE and INSERT statements |
|
= |
binary |
7 |
Equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
<> |
binary |
7 |
Not equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
> |
binary |
7 |
Greater than. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
>= |
binary |
7 |
Greater than or equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
< |
binary |
7 |
Less than. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
<= |
binary |
7 |
Less than or equal. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
LIKE |
binary |
7 |
String comparison with wildcard matching. '%' matches 0 or more characters. '_' matches 1 character. Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
+ |
binary |
11 |
Add. Parses strings to numeric equivalent. |
|
- |
binary |
11 |
Subtract. Parses strings to numeric equivalent. |
|
* |
binary |
12 |
Multiply. Parses strings to numeric equivalent. |
|
/ |
binary |
12 |
Division. Parses strings to numeric equivalent. |
|
% |
binary |
12 |
Modulo. Parses strings to numeric equivalent. |
|
IS |
binary |
7 |
Equal (NULL safe). Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
IS NOT |
binary |
7 |
Not equal (NULL safe). Case insensitive for strings, case sensitive for BINARY type. Returns boolean value (0 = false, 1 = true) |
|
AND |
binary |
2 |
Logical AND. Returns boolean value (0 = false, 1 = true) |
|
OR |
binary |
4 |
Logical OR. Returns boolean value (0 = false, 1 = true) |
|
NOT |
unary |
5 |
Logical NOT. Returns boolean value (0 = false, 1 = true) |
|
CONCAT |
Variable |
Function |
String concatenation. CONCAT(string1, ....) |
|
CONVERT |
2 |
Function |
Type conversion. CONVERT(value, type) |
|
LOCATE |
2/3 |
Function |
Return starting position of substring. LOCATE(substring, full string, [start index]) |
|
SUBSTR |
2/3 |
Function |
Return substring. SUBSTR(string, [start index,] num of chars) |
SQL Types
CHAR, VARCHAR, LONG VARCHAR, BINARY, SMALLINT, INTEGER, FLOAT, DOUBLE, TIMESTAMP
Miscellaneous ODBC Support
"{d '1995-01-15'}" style date literals, unnamed parameters via '?' literals, single prepare/multiple execution model with parameter updating, thread-safety. Contact Synthetic Dreams regarding any further ODBC support questions.
