picoSQL - Language reference manual
picoSQL - Language reference manual |
|
|
Syntax: |
Format 1: SELECT [ ALL | DISTINCT ] expression-list FROM { table-name [[AS] alias][, table-name [[AS] alias]] ... | join-clause } [ WHERE search-condition ] [ GROUP BY column-name [, column-name] ...] [ HAVING search-condition ] [ UNION select-statement ] [ ORDER BY {integer [ASC|DESC]|expression [ASC | DESC]} [, {integer [ASC|DESC]|expression [ASC | DESC]}]...] [ LIMIT integer ] [ OFFSET integer ]
Format 2:
SELECT expression-list FROM table-name [ WHERE search-condition ] FOR UPDATE Format 3: SELECT SERIAL |
|
Purpose: |
Allows to query the database.
|
|
Vedi anche: |
CREATE VIEW, UNION
|
|
Descrizione:
|
This statement is the more complex one because it is the only one that allows to query the database.
For format 3 use, see the
INSERT statement Format 2 is a subset of format 1 with the FOR UPDATE clause and it is used to lock the read row. The row is unlocked when the next row is read or the cursor is closed. The format 1 clauses are the following.
|
ALL | DISTINCT
If the DISTINCT clause is specified, duplicate output rows are eliminated. This is called 'projection' of the result of the command. Pay attention that to eliminate the duplicate rows, picoSQL execute a sort of the output rows and this operation can take significantly longer to execute, especially the number of retrived rows is very high.
The ALL clause instead returns all the rows and this also the default behaviour.
expression-list
expression-list specifies what will be retrived from the database. It has the following form:
{ * | expression [[ AS ] alias-name] [, expression [[ AS ] alias-name]]... }
If asterisk (*) is specified, it is expanded to select all columns of all tables in the FROM clause. Aggregate functions are allowed in the expression list.
Alias names can be used throughout the query to represent the aliased expression. Alias names are usually displayed by interactive programs, at the top of each column of output from the SELECT statement.
FROM
Specifies the tables list from which the data are retrieved. When data are retrieved from more than one table, usually one or more join conditions must be specified. A join reduces the result set based on a condition that bind a column from one table to a column on another table. PicoSQL allows two methods to specify a join:
you can put the join conditions together the search conditions after the WHERE clause (see in the continuation) ;
you can specify the join condition in the FROM clause directly with an appropriate syntax.
If you use the former method, the FROM clause is only a list of table names. Alias names are necessary to distinguish between table instances when referencing the same table more than once in the same query (self joins).
The latter method is more complex, but it distinguish the join conditions from the search conditions and it allows the outer joins. The join-clause syntax is the following:
table-name [[AS] alias] {INNER | LEFT OUTER| RIGHT OUTER}
JOIN {table-name [[AS] alias] | join-clause}
ON join-condition
This syntax allows to specify any number of joins, also of different types, by nesting the join-clause one in another.
If you put the join conditions after the WHERE clause or you use the join clause with the INNER JOIN option, you get the same results but performances may vary. Infact, in the former case picoSQL choose the optimal scanning table order to minimize the number of reads, in the latter case the scanning tables order is specified by the join clause itself.
WHERE
Specifies a search-condition that restrict the rows that will be selected from the tables. Also the join conditions can be specified in this clause. See "Search conditions".
GROUP BY
Group multiple rows together from the database. GROUP BY expressions must also appear in the select list. The result of the quesry contains one row for each distinct set of values in the named columns. Aggregate functions can then be applied to these groups to get meaningful results.
HAVING
Restricts which groups will be selected based on the group values and not on the individual rows values. The HAVING clause can only be used if the command has a GROUP BY clause.
ORDER BY
Sort the results of a query. Each item in the ORDER BY list can be labeled as ASC for ascending order or DESC for descending order. Ascending is assumed if neither is specified. If the expression is an integer N, then the query results will be sorted by the N'th itm in the select list.
LIMIT
Limits the number of retrieved rows to the number specified as argument. If the total number of selected rows is less than the number specified as argument, this clause has no effects.
OFFSET
Specifies the offset of the first row to return. If the total number of selected rows is less than the number specified as argument, the result is a no data found error.