picoSQL - linguaggio SQL supportato

Corso Italia, 178 - 56125 Pisa Telefono/fax 050 46380 e-mail picosoft@picosoft.it

Istruzioni
----------

alter-table-statement ::= alter-table-statement-add
      | alter-table-statement-drop
      | alter-table-statement-rename

alter-table-statement-add ::=
      ALTER TABLE base-table-name ADD [COLUMN] column-identifier data-type

alter-table-statement-drop ::=
      ALTER TABLE base-table-name DROP [COLUMN] column-identifier

alter-table-statement-rename ::=
      ALTER TABLE base-table-name RENAME base-table-name

create-index-statement ::=
      CREATE [UNIQUE] INDEX index-name ON base-table-name
      (column-identifier [ASC|DESC][,column-identifier [ASC|DESC]]...)

create-table-statement ::=
      CREATE TABLE base-table-name
      (column-identifier data-type [PRIMARY KEY]
         [,column-identifier data-type [PRIMARY KEY]]...)
      [PRIMARY KEY (column-identifier[,column-identifier]...)]

create-view-statement ::=
      CREATE VIEW base-view-name [(column-identifier[,column-identifier...])]
           AS select-for-view

delete-statement-searched ::=
      DELETE FROM table-name [WHERE search-condition]

drop-index-statement ::=
      DROP INDEX base-index-name 

drop-table-statement ::=
      DROP TABLE base-table-name 

drop-view-statement ::=
      DROP VIEW base-view-name 

insert-statement ::=
      INSERT INTO table-name [( column-identifier [, column-identifier]...)]
      VALUES (insert-value[, insert-value]... )

replace-statement ::=
      REPLACE INTO table-name [( column-identifier [, column-identifier]...)]
      VALUES (insert-value[, insert-value]... )

select-statement ::=
      SELECT [ALL | DISTINCT] select-list
      FROM table-reference-list
      [WHERE search-condition]
      [GROUP BY column-name [,column-name]...]
      [HAVING search-condition]
      [UNION select-statement]
      [order-by-clause]
      [LIMIT unsigned-integer]
      [OFFSET unsigned-integer]

select-for-update-statement ::=
      SELECT [ALL | DISTINCT] select-list
      FROM base-table-name
      [WHERE search-condition]
      FOR UPDATE

statement ::= create-index-statement
      | alter-table-statement
      | create-table-statement
      | create-view-statement
      | delete-statement-searched
      | drop-index-statement
      | drop-table-statement
      | drop-view-statement
      | insert-statement
      | replace-statement
      | select-statement
      | select-for-update-statement
      | update-statement-searched

update-statement-searched
      UPDATE table-name
      SET column-identifier = {expression | NULL }
           [, column-identifier = {expression | NULL}]...
      [WHERE search-condition]


Elementi
--------
I seguenti elementi sono usati nelle istruzioni precedenti

all-function :: {AVG|MAX|MIN|SUM}(expression)

base-index-identifier ::= user-defined-name

base-index-name ::= base-index-identifier

base-table-identifier ::= user-defined-name

base-table-name ::= base-table-identifier

base-view-identifier ::= user-defined-name

base-view-name ::= base-view-identifier

between-predicate ::= expression [NOT] BETWEEN expression AND expression

boolean-factor ::= [NOT] boolean-primary

boolean-primary ::= comparison-predicate | ( search-condition )

boolean-term ::= boolean-factor [AND boolean-term]

character-string-literal ::= '{character}'

column-identifier ::= user-defined-name

column-name ::= [table-name.]column-identifier

comparison-operator ::= < | > | <= | >= | = | <>

comparison-predicate ::= expression comparison-operator expression

data-type ::= CHAR | VARCHAR | NUMERIC | SMALLINT | INTEGER | INT | SERIAL |
              REAL | FLOAT | DATE | TIME | TIMESTAMP | BLOB | CLOB

digit ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 

distinct-function :: {AVG|COUNT|MAX|MIN|SUM}(DISTINCT column-name)

dynamic-parameter ::= ?

exists-predicate ::= EXISTS (sub-query)

expression ::= term | expression {+|} term

factor ::= [+|]primary

inner-join ::= table-name INNER JOIN ON {table-name|join} ON search-condition

in-predicate ::= expression [NOT] IN expression [, expression]

insert-value ::=
      dynamic-parameter
      | literal
      | NULL

join ::= outer-join | inner-join

letter ::= lower-case-letter | upper-case-letter

like-predicate ::= column-name [NOT] LIKE pattern-value

literal ::= character-string-literal

lower-case-letter ::= a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z

null-predicate ::= IS [NOT] NULL

order-by-clause ::=  ORDER BY sort-specification [, sort-specification]...

outer-join ::= table-name {LEFT|RIGHT} OUTER JOIN ON {table-name|join} ON search-condition

predicate ::= between-predicate | comparison-predicate | exists-predicate | in-predicate | like-predicate | null-predicate

primary ::= column-name
      | dynamic-parameter
      | literal
      | set-function-reference
      | scalar-function
      | ( expression )

scalar-func ::= ABS(expression) | ACOS(expression) | ASIN(expression) |
                ATAN(expression) | CEILING(expression) | COS(expression) |
                EXP(expression) | FLOOR(expression) |  LOG(expression) |
                LOG10(expression) | SIGN(expression) | SIN(expression) |
                SQRT(expression) | TAN(expression) | COT (expression) |
                LOWER(expression) | UPPER(expression) | LCASE(expression)|
                UCASE(expression) | RTRIM(expression) | case(expression) |
                CURRENT_TIMESTAMP(expression) |
                ROUND(expression,expression)|TRUNCATE(expression,expression)|
                CHAR(expression) | SPACE(expression) | ASCII(expression) |
                LENGTH(expression) | CONCAT(expression,expression) |
                SUBSTRING(expression,expression)|
                LOCATE(expression,expression,expression) |
                YEAR(expression) | MONTH(expression) | DAYOFMONTH (expression) |
                HOUR(expression) | MINUTE(expression) | SECOND (expression) |
                MILLISECOND (expression)

search-condition ::= boolean-term [OR search-condition]

select-for-view ::=
      SELECT [ALL | DISTINCT] select-list
      FROM table-reference-list-for-view
      [WHERE search-condition]

select-list ::= * | select-sublist [, select-sublist]... 

select-sublist ::= expression

set-function-reference ::= COUNT(*)|distinct-function|all-function
 
sort-specification ::= {unsigned-integer | column-name} [ASC | DESC]

sub-query ::=
      SELECT [ALL | DISTINCT] select-list
      FROM table-reference-list
      [WHERE search-condition]
      [GROUP BY column-name [,column-name]...]
      [HAVING search-condition]

table-identifier ::= user-defined-name

table-name ::= table-identifier

table-reference ::= table-name

table-reference-list ::= table-reference [,table-reference] | join

table-reference-list-for-view ::= table-reference | join

term ::= factor | term {*|/} factor

unsigned-integer ::= {digit}

upper-case-letter ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

user-defined-name ::= letter[digit | letter | _]...