StelsDBF SQL Syntax

 

StelsDBF JDBC driver supports the following SQL statements and syntax:

 

SELECT

INSERT

UPDATE

DELETE 

 

CREATE TABLE

CREATE VIEW

CREATE INDEX

 

DROP TABLE

DROP VIEW

DROP INDEX

 

 

SELECT

Selects data from a table or multiple tables. GROUP BY groups the the result by the given expression(s). HAVING filter rows after grouping. ORDER BY sorts the result by the given column(s) or expression(s). UNION combines the result of this query with the results of another query.

LIMIT limits the number of rows returned by the query, OFFSET specified how many rows to skip. SAMPLE_SIZE limits the number of rows read for aggregate queries. Multiple set operators (UNION / INTERSECT / MINUS/ EXPECT) are evaluated from left to right.

 

select:

SELECT [ TOP term ] [ DISTINCT | ALL ] selectExpression [,...]
FROM tableExpression [,...]

[ WHERE expression ]
[ GROUP BY expression [,...] ]

[ HAVING expression ]
[ { UNION [ ALL ] | MINUS | EXCEPT | INTERSECT } select ]

[ ORDER BY order [,...] ]
[ LIMIT expression [ OFFSET expression ]

[ SAMPLE_SIZE rowCountInt ] ]
[ FOR UPDATE ]

 

selectExpression:

* | expression [ [ AS ] columnAlias ] | tableAlias.*

 

tableExpression:

{ [ schemaName. ] tableName | ( select ) } [ [ AS ] newTableAlias ]
[ { { LEFT | RIGHT } [ OUTER ] | [ INNER ] | CROSS | NATURAL }
JOIN tableExpression [ ON expression ] ]

 

expression:
andCondition [ { OR andCondition } [...] ]

 

andCondition:

condition [ { AND condition } [...] ]

 

condition:

operand [ conditionRightHandSide ] | NOT condition | EXISTS ( select )

 

conditionRightHandSide:

compare { { { ALL | ANY | SOME } ( select ) } | operand }
| IS [ NOT ] NULL
| BETWEEN operand AND operand
| IN ( { select | expression [,...] } )
| [ NOT ] LIKE operand [ ESCAPE string ]
| [ NOT ] REGEXP operand

 

compare:

<> | <= | >= | = | < | > | !=

 

operand:

summand [ { || summand } [...] ]

 

summand:
factor [ { { + | - } factor } [...] ]

 

factor:

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

 

order:

{ int | expression } [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

 

term:

value
| columnName
| ?[ int ]
| NEXT VALUE FOR sequenceName
| function
| { - | + } term
| ( expression )
| select
| case
| caseWhen
| tableAlias.columnName

 

name:

{ { A-Z|_ } [ { A-Z|_|0-9 } [...] ] } | quotedName

 

quotedName: "anything"

 

alias: name
 

case:

CASE expression { WHEN expression THEN expression } [...]
[ ELSE expression ] END
 

caseWhen:

CASE { WHEN expression THEN expression} [...]
[ ELSE expression ] END
 

value:

string | dollarQuotedString | hexNumber | int | long | decimal | double
| date | time | timestamp | boolean | bytes | array | null

 

string: 'anything'

dollarQuotedString: $$anything$$

hexNumber: [ + | - ] 0xhex

int: -2147483647 to 2147483647

long: -9223372036854775808 to 9223372036854775807

decimal: [ + | - ] number [ . number ]

double: $$anything$$

date: DATE 'yyyy-MM-dd'

time: TIME 'hh:mm:ss'

timestamp: TIMESTAMP 'yyyy-MM-dd hh:mm:ss[.nnnnnnnnn]'

boolean: TRUE | FALSE

bytes: X'hex'

array: ( expression [,...] )

null: NULL

number: digit[...]

hex: { { digit | a-f | A-F } { digit | a-f | A-F } } [...]

digit: 0-9

 

Example:

SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM test GROUP BY a HAVING AVG(a) > 30;
SELECT name FROM salesreps WHERE (rep_office IN ( 22, 11, 12 )) OR (manager IS NULL AND hire_date >= PARSEDATETIME('01-05-2002','dd-MM-yyyy') OR (sales > quota AND NOT sales > 600000.0);
SELECT city, target, sales FROM offices WHERE region = 'Eastern' AND sales > target ORDER BY city;
SELECT * FROM prices ps JOIN regions regs ON ps.regionid = regs.id JOIN products prod ON prod.prodid = ps.prodid;
SELECT * FROM prices ps, products prod WHERE prod.prodid = ps.prodid;

 

 

INSERT

Inserts a new row / new rows into a table.

 

INSERT INTO tableName [ ( columnName [,...] ) ]
{ VALUES { ( { DEFAULT | expression } [,...] ) } [,...] | select }

 

Example:

INSERT INTO salesreps (name, age, empl_num, sales, title, hiredate) VALUES ('Henry Smith', 35, 111, NULL, 'Sales Mgr', PARSEDATETIME('01:12:2002','dd:MM:yyyy'));

INSERT INTO test SELECT * FROM test2;

 

 

UPDATE

Updates data in a table.

 

UPDATE tableName [ [ AS ] newTableAlias ] SET { columnName = { DEFAULT | expression } } [,...]
[ WHERE expression ]

 

Example:

UPDATE test SET a=1 WHERE id=2;

 

 

DELETE

Deletes rows form a table.

 

DELETE FROM tableName [ WHERE expression ]

 

Example:

DELETE FROM test WHERE a=1 OR b=3;

 

 

CREATE TABLE

Creates a new table.

 

CREATE TABLE tableName ( columnDefinition [,...] )

 

columnDefinition:

columnName dataType

 

Example:

CREATE TABLE new_table (int_col INT, long_col LONG, float_col REAL, double_col DOUBLE, str_col VARCHAR(20), date_col DATETIME, bool_col BOOLEAN, num_col DECIMAL(15,2));

 

 

CREATE VIEW

Creates a new view.

 

CREATE VIEW [IF NOT EXISTS] viewName AS select

 

Example:

CREATE VIEW v1 AS SELECT * FROM test;

 

 

CREATE INDEX

Creates a new index.

 

CREATE INDEX [IF NOT EXISTS] newIndexName ON tableName ( indexColumn [,...] )

 

indexColumn:
columnName [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

 

Example:

CREATE INDEX index_1 ON test(int_col);

 

 

DROP TABLE

Drops an existing table.

 

DROP TABLE tableName

 

Example:

DROP TABLE test;

 

 

DROP VIEW

Drops an existing view.

 

DROP TABLE viewName

 

Example: 

DROP VIEW v1;

 

DROP INDEX

Drops an existing index.

 

DROP INDEX indexName

 

Example:

DROP INDEX index_1;

 

 

 

Related Documents: 

 

 

 

 

 

 [HOME]   [TOP]