Provided by:
manpages-zh_1.5.2-1_all 
NAME
SELECT -
SYNOPSIS
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
* | expression [ AS output_name ] [, ...]
[ FROM from_item [, ...] ]
[ WHERE condition ]
[ GROUP BY expression [, ...] ]
[ HAVING condition [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [, ...] ]
[ LIMIT { count | ALL } ]
[ OFFSET start ]
[ FOR UPDATE [ OF table_name [, ...] ] ]
where from_item can be one of:
[ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
function_name ( [ argument [, ...] ] ) [ AS ] alias [ ( column_alias [, ...] | column_definition [, ...] ) ]
function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]
[Comment: FIXME: This last syntax is incorrect if the join type is an
INNER or OUTER join (in which case one of NATURAL, ON ..., or USING ...
is mandatory, not optional). What's the best way to fix this?]
DESCRIPTION
SELECT SELECT
1.
FROM FROM FROM FROM Clause [select(7)]
2.
WHERE WHERE Clause [select(7)]
3.
GROUP BY HAVING GROUP BY Clause [select(7)] HAVING Clause
[select(7)]
4.
UNIONINTERSECT EXCEPT SELECT UNION INTERSECT EXCEPT ALL
UNION Clause [select(7)], INTERSECT Clause [select(l)], EXCEPT
Clause [select(7)]
5.
SELECT SELECT List [select(7)]
6.
ORDER BY ORDER BY ORDER BY Clause [select(7)]
7.
LIMIT OFFSET SELECT LIMIT Clause [select(7)]
8. DISTINCT DISTINCT ON ALL DISTINCT Clause [select(7)]
9. FOR UPDATE SELECT FOR UPDATE Clause [select(7)]
SELECT FOR UPDATE UPDATE
PARAMETERS
FROM
FROM SELECT
FROM-
table_name
ONLY ONLY * PostgreSQL 7.1 ONLY sql_interitance
alias
FROM FROM foo AS f SELECT FROM f foo
select
SELECT FROM SELECT SELECT
function_name
FROM SELECT record AS ( column_name data_type [, ...
])
join_type
o [ INNER ] JOIN
o LEFT [ OUTER ] JOIN
o RIGHT [ OUTER ] JOIN
o FULL [ OUTER ] JOIN
o CROSS JOIN
INNER OUTER NATURAL ON join_condition USING (join_column [,
...]) CROSS JOIN
JOIN FROM JOIN JOIN FROM
CROSS JOIN INNER JOIN FROM CROSS JOIN INNER JOIN ON (true) FROM
WHERE
LEFT OUTER JOIN JOIN
RIGHT OUTER JOIN LEFT OUTER JOIN
FULL OUTER JOIN
ON join_condition
join_condition boolean WHERE
USING (join_column [, ...])
USING ( a, b, ... ) ON left_table.a = right_table.a AND
left_table.b = right_table.b ... USING
NATURAL
NATURAL USING
WHERE
WHERE
WHERE condition
condition boolean
GROUP BY
GROUP BY
GROUP BY expression [, ...]
GROUP BY expression SELECT GROUP BY
GROUP BY GROUP BY SELECT
HAVING
HAVING
HAVING condition
condition WHERE
HAVING HAVING WHERE WHERE GROUP BY HAVING GROUP BY condition
UNION
UNION
select_statement UNION [ ALL ] select_statement
select_statement ORDER BYLIMIT FOR UPDATE SELECT ORDER BY LIMIT
UNION
UNION SELECT UNION SELECT
UNION ALL ALL
SELECT UNION
FOR UPDATE UNION
INTERSECT
INTERSECT
select_statement INTERSECT [ ALL ] select_statement
select_statement ORDER BY LIMIT FOR UPDATE SELECT
INTERSECT SELECT
NTERSECT ALL ALL m n min(m,n)
SELECT INTERSECT INTERSECT UNION --- A UNION B INTERSECT C A
UNION (B INTERSECT C)
EXCEPT
EXCEPT
select_statement EXCEPT [ ALL ] select_statement
fIselect_statement ORDER BYLIMIT FOR UPDATE SELECT
EXCEPT SELECT
EXCEPT ALL ALL m n max(m-n,0)
SELECT EXCEPT EXCEPT UNION
SELECT
SELECT SELECT FROM) SELECT FROM AS output_name ORDER BY GROUP
BY WHERE HAVING
* table_name.*
ORDER BY
ORDER BY
ORDER BY expression [ ASC | DESC | USING operator ] [, ...]
expression SELECT
ORDER BY
/ / AS /
ORDER BY SELECT
SELECT name FROM distributors ORDER BY code;
UNIONINTERSECT EXCEPT ORDER BY
ORDER BY ORDER BY GROUP BY SQL
ORDER BY / DESC ASC ASC USING ASC USING < DESC USING > (But
the creator of a user-defined data type can define exactly what the
default sort ordering is, and it might correspond to operators with
other names.)
LIMIT
LIMIT
LIMIT { count | ALL }
OFFSET start
count start
.PP
LIMIT
LIMIT ORDER BY --- ORDER BY
LIMIT LIMIT OFFSET LIMIT/OFFSET ORDER BY SQL ORDER BY SQL
DISTINCT
DISTINCT ALL
DISTINCT ON ( expression [, ...] ) DISTINCT ON ORDER BY ORDER BY
""
SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;
ORDER BY
DISTINCT ON ORDER BY ORDER BY DISTINCT ON
FOR UPDATE
FOR UPDATE
FOR UPDATE [ OF table_name [, ...] ]
FOR UPDATE SELECT UPDATEDELETE SELECT FOR UPDATE UPDATE DELETE
SELECT FOR UPDATE SELECT FOR UPDATE Chapter 12 ``Concurrency
Control''
FOR UPDATE SELECT
FOR UPDATE
FOR UPDATE LIMIT 7.3 PostgreSQL LIMIT LIMIT
EXAMPLES
films 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
...
kind / len
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
films/ len kind 5
SELECT kind, sum(len) AS total
FROM films
GROUP BY kind
HAVING sum(len) < interval '5 hours';
kind | total
----------+-------
Comedy | 02:58
Romantic | 04:38
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
distributors actors W ALL
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
FROM
CREATE FUNCTION distributors(int) RETURNS SETOF distributors AS '
SELECT * FROM distributors WHERE did = $1;
' LANGUAGE SQL;
SELECT * FROM distributors(111);
did | name
-----+-------------
111 | Walt Disney
CREATE FUNCTION distributors_2(int) RETURNS SETOF record AS '
SELECT * FROM distributors WHERE did = $1;
' LANGUAGE SQL;
SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
f1 | f2
-----+-------------
111 | Walt Disney
COMPATIBILITY
SELECT SQL
FROM
PostgreSQL FROM
SELECT 2+2;
?column?
----------
4
SQL SELECT
SELECT
SELECT distributors.* WHERE distributors.name = 'Westward';
did | name
-----+----------
108 | Westward
SELECT FROM FROM
SELECT distributors.* FROM distributors d;
SELECT d.* FROM distributors d;
SELECT distributors.* FROM distributors d, distributors distributors;
PostgreSQL FROM FROM Also, it is possible to disable the implicit-
FROM feature by setting the ADD_MISSING_FROM parameter to false.
AS
SQL AS PostgreSQL / AS FROM
GROUP BY ORDER BY
SQL92 ORDER BY GROUP BY PostgreSQL PostgreSQL
SQL99 uses a slightly different definition which is not upward
compatible with SQL92. In most cases, however, PostgreSQL will
interpret an ORDER BY or GROUP BY expression the same way SQL99 does.
DISTINCT ON, LIMIT, OFFSET SQL
Postgresql <laser@pgsqldb.org>