morilib relations: a relational database


Top - Guide - Reference - Functions - Using JDBC - Download - 日本語


available SQL expression

Available values in morilib relations are shown as follows. Available operators in morilib relations are shown as follows.
(Higher precidence)
Operators Associave Description
FUNCTION-NAME ( expr, ... ) call a function
unary +, unary- left to right compute unary +, unary -
*, / left to right multiplication or division
+, - left to right addition or subtraction
>, <, >=, <=, =, ==, !=, <> left to right comparison of two values; get the value 1 if the expression results true value or 0 otherwise
IS NULL, IS NOT NULL, IN, BETWEEN, LIKE, EXISTS -
  • expr IS NULL, IS NOT NULL:
    get true if expr is NULL(not null). In morilib relations, NULL is identical with an empty string.
  • expr0 IN ( expr1, ..., exprN ):
    get true if expr0 is identical with one of expr1, ... exprN.
  • expr0 IN select-clause:
    get true if expr0 is in the result of select-clause. The select-clause must have only one column.
  • expr LIKE pattern:
    get true if expr is matched with the pattern. The meta characters "_"(any character) and "%"(any characters) are available in the pattern.
  • EXISTS select-clause:
    get true if the result of select-clause has a value.
NOT compute logical negation
&& left to right compute the logical product
|| left to right compute the logical summation
(Lower precidence)


Join tables

You can join tables by below SQL statement.
table-name [INNER|[LEFT] OUTER] JOIN table-name [ON expr]
Joining operators which are available in morilib relations are shown as follows.


Types

Types which is available in morilib relations are shown as following table.
TypeDecription
VARCHAR(length) A variable-length string. You can specify a string with unlimited length by 'VARCHAR(UNLIMITED)'.
NUMERIC(precision[, scale]) A number. You can specify a rational number with unlimited scale by 'NUMERIC(UNLIMITED)'.
DATE A date. Almost human-readable notations are available.


SELECT

SELECT expression[,...] [FROM joined-table] [WHERE expression] [GROUP BY column[,...] [HAVING expression]] [ORDER BY column[,...]]
queries from a database.
In morilib relations, there are limitations of SELECT statement.

WITH var-name AS ( select-clause ) [, var-name AS ( select-clase ) ] select-clause
queries from a database with storing the result of query to temporary variables.
The temporary variables will be cleared after the query.

WITH var-name AS ( select-clause ) [, var-name AS ( select-clase ) ]
queries from a database with storing the result of query to temporary variables.
The temporary variables will be cleared after the session, not the query.


INSERT

INSERT INTO table-name (column[,...]) VALUES (expr[,...])
inserts a record to the specified table.
In morilib relations, there is the limitations of SELECT statement.

INSERT INTO table-name (column[,...]) select-clause
insert the result of the query to the specified table.


UPDATE

UPDATE table-name SET column=expr [,column=expr ...] WHERE expression
updates values of the specified table.


DELETE

DELETE FROM table-name WHERE expression
deletes values of the specified table.


DDL(for tables)

CREATE TABLE table-name (
 column-name type [NOT NULL] [PRIMARY KEY]
 [,column-name type [NOT NULL] [PRIMARY KEY] ...])
creates a table.

ALTER TABLE table-name ADD (
 column-name type [NOT NULL] [PRIMARY KEY]
 [,column-name type [NOT NULL] [PRIMARY KEY] ...])
adds a column to the specified table.

ALTER TABLE table-name MODIFY (
 column-name type [NOT NULL] [PRIMARY KEY]
 [,column-name type [NOT NULL] [PRIMARY KEY] ...])
modifies a column of the specified table.

ALTER TABLE table-name DROP (column-name[, column-name ...])
drops a column of the specified table.

ALTER TABLE table-name RENAME TO new-table-name
renames a name of the specified table.

DROP TABLE table-name
drops the specified table. Informations of the table will be saved if a directory is used as a database.

TRUNCATE TABLE table-name
clears all record of the specified table. Informations of the table will be saved if a directory is used as a database.


Yuichiro Moriguchi
yuichiro-moriguchi@nifty.com