SELECT

中文man手册

SELECT

NAME
SYNOPSIS
DESCRIPTION æè¿°
PARAMETERS åæ°
FROM åå¥
WHERE åå¥
GROUP BY åå¥
HAVING åå¥
UNION åå¥
INTERSECT åå¥
EXCEPT åå¥
SELECT åè¡¨
ORDER BY åå¥
LIMIT åå¥
DISTINCT åå¥
FOR UPDATE åå¥
EXAMPLES ä¾å
COMPATIBILITY å¼å®¹æ§
çç¥ FROM åå¥
AS å³é®å
GROUP BY å ORDER BY éå¯ç¨çååç©ºé´
éæ ååå¥
è¯è
è·

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 æè¿°

è®¡ç®ååºå¨ FROM ä¸çææåç´ ãï¼FROM ä¸çæ¯ä¸ªåç´ é½æ¯ä¸ä¸ªçæ- £çæèèæçè¡¨ãï¼å¦æå¨ FROM åè¡¨éå£°æäºå¤è¿ä¸ä¸ªåç´ ï¼é£ä¹ä»ä»¬å°±äº¤åè¿æ¥å¨ä¸èµ·ãï¼åéä¸é¢ç FROM Clause [select(7)] ï¼ã

å¦æå£°æäº WHERE åå¥ï¼é£ä¹å¨è¾åºä¸æ¶é¤ææ ä¸æ»¡è¶³æ¡ä»¶çè¡ãï¼åéä¸é¢ç WHERE Clause [select(7)] ï¼ã

å¦æå£°æäº GROUP BY åå¥ï¼è¾åºå°±åæå¹éä¸ä¸ªæå¤ä¸ªæ°å¼çä¸åç»éã å¦æåºç°äº HAVING å- å¥ï¼é£ä¹å®æ¶é¤é£äºä¸æ»¡è¶³ç»åºæ¡ä»¶çç»ãï¼åéä¸é¢ç GROUP BY Clause [select(7)] å HAVING Clause [select(7)] ï¼ã

ä½¿ç¨ UNIONï¼INTERSECTï¼ å EXCEPTï¼æä»¬å¯ä»¥æå¤ä¸ª SELECT è¯- å¥çè¾åºåå¹¶æä¸ä¸ªç»æéãUNION æä½ç¬¦è¿åå¨ä¸¤ä¸ªç»æéæèå¶ä¸ä¸ä¸ªä¸- çè¡ï¼ INTERSECT æä½ç¬¦è¿åä¸¥æ ¼å°å¨ä¸¤ä¸ªç»æéä¸é½æçè¡ã EXCEPT æä½ç¬¦è¿åå¨ç¬¬ä¸ä¸ªç»æéä¸ï¼ä½æ¯ä¸å¨ç¬¬äºä¸ªç»æéä¸- çè¡ãä¸ç®¡åªç§æåµï¼ éå¤çè¡é½è¢«å é¤ï¼é¤éå£°æäº ALLãï¼åéä¸é¢ç UNION Clause [select(7)], INTERSECT Clause [select(l)], å EXCEPT Clause [select(7)] ï¼ã

å®éè¾åºè¡çæ¶åï¼SELECT åä¸ºæ¯ä¸ªéåºçè¡è®¡ç®è¾åºè¡¨è¾¾å¼ ï¼åéä¸é¢ç SELECT List [select(7)] ï¼ã

å¦æå£°æäº ORDER BY åå¥ï¼é£ä¹è¿åçè¡æ¯æç§æå®çé¡ºåºæåºçã å¦ææ²¡æç»åº ORDER BYï¼é£ä¹æ°æ®è¡æ¯æç§ç³»ç»è®¤ä¸ºå¯ä»¥æå¿«çæçæ¹æ³ç»åºçã ï¼åéä¸é¢ç ORDER BY Clause [select(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-åå¥å¯ä»¥åæ¬ï¼
table_name

alias

select

function_name

join_type

	•		[ INNER ] JOIN
	•		LEFT [ OUTER ] JOIN
	•		RIGHT [ OUTER ] JOIN
	•		FULL [ OUTER ] JOIN
	•		CROSS JOIN

ä¹ä¸ã å°± INNER å OUTER è¿æ¥ç±»åï¼ æä»¬å¿é¡»å£°æä¸ä¸ªè¿æ¥æ¡ä»¶ï¼ä¹å°±æ¯è¯´ä¸ä¸ª NATURALï¼ ON join_conditionï¼ æè USING (join_column [, ...])ã è§ä¸æè·åå®ä»¬çå«ä¹ï¼å¯¹äº CROSS JOINï¼è¿äºåå¥é½ä¸è½åºç°ã

CROSS JOIN å INNER JOIN çæä¸ä¸ªç®åçç¬å¡å¿ç§¯ï¼åä½ å¨ FROM çé¡¶å±ååºä¸¤ä¸ªé¡¹çç»æç¸åã CROSS JOIN çæäº INNER JOIN ON (true)ï¼ ä¹å°±æ¯è¯´ï¼æ²¡æè¢«æ¡ä»¶å é¤çè¡ãè¿ç§è¿æ¥ç±»ååªæ¯ç¬¦å·ä¸çæ¹ä¾¿ï¼ å ä¸ºå®ä»¬åä½ ç¨ç®åç FROM å 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

GROUP BY åå¥

å¯éç GROUP BY åå¥çä¸è¬å½¢å¼

GROUP BY expression [, ...]

HAVING åå¥

å¯éç HAVING åå¥æå¦ä¸å½¢å¼ï¼

HAVING condition

è¿é condition åä¸º WHERE åå¥éå£°æçç¸åã

UNION åå¥

UNION åå¥çä¸è¬å½¢å¼æ¯ï¼

select_statement UNION [ ALL ] select_statement

è¿é select_statement æ¯ä»»ææ²¡æ ORDER BYï¼LIMITï¼æè FOR UPDATE åå¥ç SELECTè¯å¥ã ï¼å¦æç¨åæ¬å¼§åå´ï¼ORDER BY å LIMIT å¯ä»¥éçå¨åè¡¨è¾¾å¼éã å¦ææ²¡æåæ¬å¼§ï¼è¿äºåå¥å°äº¤ç» UNION çç»æä½¿ç¨ï¼ èä¸æ¯ç»å®ä»¬å³æè¾¹çè¾å¥è¡¨è¾¾å¼ãï¼

ç®åï¼FOR UPDATE ä¸è½å¨ UNION çç»ææè¾å¥ä¸å£°æã

INTERSECT åå¥

INTERSECT åå¥çä¸è¬å½¢å¼æ¯ï¼

select_statement INTERSECT [ ALL ] select_statement

select_statement æ¯ä»»ä½ä¸å¸¦ ORDER BYï¼ LIMITï¼æè FOR UPDATE åå¥ç SELECT è¯å¥ã

é¤éç¨åæ¬å·ææé¡ºåºï¼ åä¸ 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 BYï¼LIMITï¼æè FOR UPDATE å- å¥ç SELECT è¡¨è¾¾å¼ã

SELECT åè¡¨

ORDER BY åå¥

å¯éç ORDER BY åå¥æä¸é¢çä¸è¬å½¢å¼ï¼

ORDER BY expression [ ASC | DESC | USING operator ] [, ...]

SELECT name FROM distributors ORDER BY code;

è¯·æ³¨æå¦æä¸ä¸ª 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

DISTINCT åå¥

SELECT DISTINCT ON (location) location, time, report
FROM weather_reports
ORDER BY location, time DESC;

DISTINCT ON è¡¨è¾¾å¼å¿é¡»å¹éæå·¦è¾¹ç ORDER BY è¡¨è¾¾å¼ã ORDER BY å- å¥å°éå¸¸åå«é¢å¤çè¡¨è¾¾å¼æ¥å¤ææ¯ä¸ª DISTINCT ON ç»éé¢éè¦çè¡çä¼åçº§ã

FOR UPDATE åå¥

FOR UPDATE åå¥æä¸é¢çå½¢å¼

FOR UPDATE [ OF table_name [, ...] ]

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

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

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

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

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

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 å¼å®¹æ§

çç¥ FROM åå¥

SELECT 2+2;

?column?
----------
4

SELECT distributors.* WHERE distributors.name = ’Westward’;

did | name
-----+----------
108 | Westward

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 å³é®å

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>

è·

æ¬é¡µé¢ä¸æçç±ä¸æ man æåé¡µè®¡åæä¾ã
ä¸æ man æåé¡µè®¡åï¼https://github.com/man-pages-zh/manpages-zh

手册导航

man1

man2

man3

man4

man5

man6

man7

man8

SELECT

目录

SELECT

NAME

SYNOPSIS

DESCRIPTION æè¿°

PARAMETERS åæ°

FROM åå¥

WHERE åå¥

GROUP BY åå¥

HAVING åå¥

UNION åå¥

INTERSECT åå¥

EXCEPT åå¥

SELECT åè¡¨

ORDER BY åå¥

LIMIT åå¥

DISTINCT åå¥

FOR UPDATE åå¥

EXAMPLES ä¾å

COMPATIBILITY å¼å®¹æ§

çç¥ FROM åå¥

AS å³é®å

GROUP BY å ORDER BY éå¯ç¨çååç©ºé´

éæ ååå¥

è¯è

è·