Otherware/Otherware_1_SB_Development.iso/amiga/misc/sqldb.lzh/sqldb/doc/sqldb_sqlrefman.doc

Text File | 1992-08-28 | 8.6 KB | 362 lines

0.0
SQLdb SQL reference manual.  Everything in this manual applies only to SQLdb
and any resemblence to other SQL dialects is purely coincidental.

1.0
-----------------
|SIMPLE ELEMENTS|
-----------------

1.1
dbname:        SQL identifier

table:        SQL identifier

column:        SQL identifier

alias:        SQL identifier

colalias:    SQL identifier

cursor:        SQL identifier

index:        SQL identifier

An SQL identifier is composed of a letter followed by zero or more characters
from the set of numbers, letters, '_', '#'.  Thus, A#_o1ne is a legal
indentifier, while _a#45 is not.

An SQL identifier cannot be a valid SQL keyword.

1.2
literal:    number or string enclosed in quotes

    1234        integer literal
    0.345        float literal
    'hello'        string literal
    '12/21/70'    date literal
    '14:45:37'    time literal

1.3
data-type:    FLOAT | SMALLINT | INTEGER | DATE | TIME | CHAR ( integer )

    FLOAT        floating point, range IEEE float
    SMALLINT    small integer, range signed short
    INTEGER        large integer, range signed int
    DATE        date mo/da/yr
    TIME        time hr:mn:sc
    CHAR        fixed length character string

2.0
-----------------
|DATA DEFINITION|
-----------------

2.1
CREATE DATABASE dbname

Creates and opens specified database.  CREATE TABLE is valid only with an open
database.  Any tables that are CREATEd are added to the open database.  Command
fails if a database is already open.

2.2
OPEN DATABASE dbname

Opens specified database.  Any tables that are CREATEd while database is open
are added.  Command fails if a database is already open.

2.3
CLOSE DATABASE

Closes database.  All tables in the database are closed as well.

2.4
CREATE TABLE table ( table-def-item-list )

This statement will create the specified table.  If a table already exists,
it will be overwritten.  The table will be OPENed after creation.

2.5
CREATE INDEX index ON table (order-ref-list)

This statement will create an index on the specified table.  The table must
be OPENed first.

3.0
----------
|DATA I/O|
----------

3.1
LOAD TABLE table

This statement will open the specified table and load the rows into RAM.  You
must CREATE TABLE before you can LOAD TABLE.  There must not be any indexes
on a table that you LOAD.  This command will probably go away in the future
unless I hear a compelling reason to keep it.

3.2
OPEN TABLE table

This statement will open the specified table.  You must CREATE TABLE before you
can OPEN TABLE.

3.3
CLOSE TABLE table

This statement will close the specified table.  It writes any changes to the
table to disk.  All user tables must be closed before you may exit SQLdb.

3.4
DROP TABLE table

This statement will delete the specified table.  The table must be OPENed
first.

4.0
-------------------------
|BASIC DATA MANIPULATION|
-------------------------

4.1
query-spec:    SELECT [DISTINCT] { select-item-list | * }
            FROM table-ref-list
            [ WHERE search-condition ]
            [ GROUP BY group-ref-list [ HAVING search-condition ] ]
            [ ORDER BY order-ref-list ]
            [ OUTPUT TO file-name ]

This statement is used to retrieve information from the database.  It is
made up of several clauses, which are explained in the following sections.

If DISTINCT is specified, then all duplicate rows will be removed from the
result table.

4.1.1 FROM clause

This clause specifies which tables SQLdb is supposed to gather information
from.

4.1.2 WHERE clause

This clause is used by several other statements, and is used to restrict
the set of rows to be operated on.  A WHERE clause succeeds if the
search-condition evaluate to true.

4.1.3 GROUP BY clause
4.1.3.1

4.1.3.2 HAVING clause

4.1.4 ORDER BY clause

4.1.5 OUTPUT TO clause

This command will send the normal query output to specified filename.

4.2
subquery:    \[ query-spec \]

4.3
INSERT INTO table [ ( column-list ) ]
    { VALUES ( insert-item-list ) | query-spec }

This statement is used to place information in a table.  The column list is
optional.  If it is supplied, each value is placed in the corresponding
column in the column-list.  Otherwise, the column-list default to all columns
in the table.  Any missing values are treated as AMARKs.

If a query is supplied instead of a value list, the query is executed and all
rows in the result will be inserted into the table.

4.4
DELETE FROM table [ WHERE search-condition ]

This statement is used to delete rows that satisfy the optional WHERE clause.
If the WHERE clause is omitted, all rows in the table are deleted.

4.5
UPDATE table SET assignment-list [ WHERE search-condition ]

This statement is used to modify existing rows that satisfy the optional WHERE
clause.  If the WHERE clause is omitted, all rows in the table are modified.

5.0
-------------------------
|CURSOR BASED STATEMENTS|
-------------------------

The following statements can be type interactively, but they are intended to
be used through an ARexx port.

5.1
DECLARE cursor CURSOR FOR query-expr

This statement will create and define a cursor.

5.2
OPEN cursor

This statement will execute a previously DECLAREd cursor's query-expr.  You
must CLOSE an OPENed cursor.

5.3
CLOSE cursor

This statement will close and remove an existing cursor.  A cursor that has
been CLOSEd must be re-DECLAREd and OPENed.

5.4

FETCH { COLUMNS | FIRST | LAST | PREVIOUS | NEXT |
    ABSOLUTE integer | RELATIVE integer } OF cursor-name

This statement will retrieve the specified row or column list, of the table
created by the cursor's query-expr.

6.0
-------------------
|SEARCH CONDITIONS|
-------------------

6.1
search-condition:    search-item | search-item { AND | OR } search-item

Each search-item evaluates to a true, false, AMARK, or IMARK.  This is a
four value logic system.

AND| F  A  I  T        OR| F  A  I  T
---------------        --------------
 F | F  F  F  F        F | F  A  F  T
 A | F  A  I  A        A | A  A  A  T
 I | F  I  I  I        I | F  A  I  T
 T | F  A  I  T        T | T  T  T  T

6.2
search-item:        { search-test | { NOT | MAYBE_A | MAYBE_I | MAYBE } ( search-condition ) }

The logical operators NOT, MAYBE_A, MAYBE_I, MAYBE are defined below:

    P |NOT P   P | MAYBE_I P   P | MAYBE_A P   P | MAYBE P
    --------   -------------   -------------   -----------
    t | f       t | f       t | f       t | f
    a | a      a | f           a | t       a | t
    i | i      i | t        i | f       i | t
      f | t      f | f        f | f       f | f

6.3
search-test:        comparison-test | like-test | set-test |
            quantified-test | existence-test

6.4
comparison-test:    expr { = | <> | < | <= | > | >= } { expr | subquery }

6.5
like-test:        column-ref [ NOT ] LIKE
                string-pattern [ ESCAPE charstr ]

A string pattern is a string literal.  The '%' is a wildcard to match
0 or more characters, and the '_' is a wildcard to match one character.
The escape character, if specified, will turn a '%' or '_' into a normal
character.

    Example:  foo LIKE 'ab#%c' ESCAPE '#' will match the string 'ab%c'.

6.6
set-test:        expr [ NOT ] IN { \( literal-list \) | subquery }

6.7
quantified-test:    expr { = | <> | < | <= | > | >= } { ALL | ANY | SOME } subquery

6.8
existence-test:        [ NOT ] EXISTS subquery

This evaluates to true if table resulting from subquery has at least one row
in it.

7.0
-------------
|EXPRESSIONS|
-------------

7.1
expr:        function | expr2

This definition means that you cannot nest functions, and functions must
appear by themselves.  Thus, AVG(4*(7-5)) is a valid expr, but MIN(col1)*3 is
not.

7.2
expr2:        expr-item | expr-item { + | - | * | / } expr-item

The four operators have equal precedence.  Use parentheses to force a
particular evaluation order.

7.3
expr-item:    value | column-ref | ( expr2 )

7.4
value:        literal | AMARK | IMARK | NULL

7.5
function:    { AVG | MAX | MIN | SUM | COUNT } ( expr2 )

Functions can only be applied to numeric (FLOAT, SMALLINT, INTEGER) columns.

    AVG        average of all values in an expression
    MAX        maximum value in an expression
    MIN        minimum value in an expression
    SUM        total of values in an expression
    COUNT        number of values (rows)

8.0
--------------------
|STATEMENT ELEMENTS|
--------------------

assignment:    column = expr

insert-item:    value

select-item:    expr [AS colalias]

Column aliases can be used to reference an expression later in the
select-item-list.

    Example:  SELECT col1,col1 AS foocol, foocol*2 AS bleh FROM footab;

    col1  foocol  bleh
    ----  ------  ----
    1     1       2
    3     3       6

table-ref:    table [ alias ]

column-ref:    [ { table | alias } . ] column

group-ref:    column-ref | number

order-ref:    group-ref [ { ASC | DESC } ]

table-def-item:    column-def

column-def:    column data-type


9.0
------
|MISC|
------

9.1
DISPLAY TABLE table;

This statement will display information about specified table.  The table
must have been OPENed or LOADed.

9.2
DISPLAY DATABASE;

This statement will display the names of all OPENed or LOADed tables.