select.hlp

关系型数据库 Postgresql 6.5.2

.pgaw:Help.f.t insert end "SELECT" {bold} " will return rows from one or more tables. Candidates for selection are rows which satisfy the WHERE condition; if WHERE is omitted, all rows are candidates.

" {} "Synopsis" {bold} "
" {} "
SELECT \[ALL|DISTINCT \[ON column\] \]
    expression \[ AS
   name \] \[, ...\]
    \[ INTO \[TEMP\] \[TABLE\] new_table \]
    \[ FROM table
   \[alias \] \[, ...\] \]
    \[ WHERE condition \]
    \[ GROUP BY column \[, ...\] \]
    \[ HAVING condition \[, ...\] \]
    \[ { UNION \[ALL\] | INTERSECT | EXCEPT } select \]
    \[ ORDER BY column \[ ASC | DESC \] \[, ...\] \]
    \[ FOR UPDATE \[OF class_name...\]\]
    \[ LIMIT count \[OFFSET|, count\]\]
    
" {code} "Usage" {bold} "

To join the table films with the table distributors: 
" {} "
SELECT f.title, f.did, d.name, f.date_prod, f.kind
    FROM distributors d, films f
    WHERE f.did = d.did

title                    |did|name            | date_prod|kind
-------------------------+---+----------------+----------+----------
The Third Man            |101|British Lion    |1949-12-23|Drama
The African Queen        |101|British Lion    |1951-08-11|Romantic
Une Femme est une Femme  |102|Jean Luc Godard |1961-03-12|Romantic
Vertigo                  |103|Paramount       |1958-11-14|Action
Becket                   |103|Paramount       |1964-02-03|Drama
48 Hrs                   |103|Paramount       |1982-10-22|Action
War and Peace            |104|Mosfilm         |1967-02-12|Drama
West Side Story          |105|United Artists  |1961-01-03|Musical
Bananas                  |105|United Artists  |1971-07-13|Comedy
Yojimbo                  |106|Toho            |1961-06-16|Drama
There's a Girl in my Soup|107|Columbia        |1970-06-11|Comedy
Taxi Driver              |107|Columbia        |1975-05-15|Action
Absence of Malice        |107|Columbia        |1981-11-15|Action
Storia di una donna      |108|Westward        |1970-08-15|Romantic
The King and I           |109|20th Century Fox|1956-08-11|Musical
Das Boot                 |110|Bavaria Atelier |1981-11-11|Drama
Bed Knobs and Broomsticks|111|Walt Disney     |          |Musical
  

To sum the column len of all films and group the results by kind: 

SELECT kind, SUM(len) AS total FROM films GROUP BY kind;

    kind      |total
    ----------+------
    Action    | 07:34
    Comedy    | 02:58
    Drama     | 14:28
    Musical   | 06:42
    Romantic  | 04:38
  

To sum the column len of all films, group the results by kind and show those group totals that are less than 5 hours: 

SELECT kind, SUM(len) AS total
    FROM films
    GROUP BY kind
    HAVING SUM(len) < INTERVAL '5 hour';

    kind      |total
    ----------+------
    Comedy    | 02:58
    Romantic  | 04:38
  

The following two examples are identical ways of sorting the individual results according to the contents of the second column (name): 

SELECT * FROM distributors ORDER BY name;
SELECT * FROM distributors ORDER BY 2;

    did|name
    ---+----------------
    109|20th Century Fox
    110|Bavaria Atelier
    101|British Lion
    107|Columbia
    102|Jean Luc Godard
    113|Luso films
    104|Mosfilm
    103|Paramount
    106|Toho
    105|United Artists
    111|Walt Disney
    112|Warner Bros.
    108|Westward
  

This example shows how to obtain the union of the tables distributors and actors, restricting the results to those that begin with letter W in each table. Only distinct rows are to be used, so
the ALL keyword is omitted: 

    --        distributors:                actors:
    --        did|name                     id|name
    --        ---+------------             --+--------------
    --        108|Westward                  1|Woody Allen
    --        111|Walt Disney               2|Warren Beatty
    --        112|Warner Bros.              3|Walter Matthau
    --        ...                           ...

SELECT distributors.name
    FROM   distributors
    WHERE  distributors.name LIKE 'W%'
UNION
SELECT actors.name
    FROM   actors
    WHERE  actors.name LIKE 'W%'

name
--------------
Walt Disney
Walter Matthau
Warner Bros.
Warren Beatty
Westward
Woody Allen

" {code} "Compatibility" {bold} "

Extensions 

Postgres allows one to omit the FROM clause from a query. This feature was retained from the original PostQuel query language: 
" {} "
SELECT distributors.* WHERE name = 'Westwood';

    did|name
    ---+----------------
    108|Westward
    
" {code}