PostgreSQL 8.2.3 婺桺桺懼
劯锔媆锔媆誕嬉誕

SELECT

劉䓄

SELECT -- 傯臘潡蓖商婺埡庺苖幾臯

臺濘

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 | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

認麯䔇 from_item 埇傖滇

    [ 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 [, ...] ) ]

柟誄

SELECT 儖傯镽婻潡敘崔臘婺誫啂螄嘘臯㔗SELECT 锔婩䔇崇䊖套婋

  1. 螇䞖彖庺婘 FROM 婺䔇欔橬噄䘹(FROM 彖臘婺䔇懟婻噄䘹鄘滇婔婻垂鍙䔇潡荔拘䔇臘)㔗套悩婘 FROM 彖臘麯弄滯庖崔婻噄䘹闼幽傡傸儌庴埬誂毖婘婔蕙(埗蓕婋麵䔇 FROM 床埖)㔗

  2. 套悩弄滯庖 WHERE 床埖闼幽婘膷庺婺潽鍴欔橬婉悇轿溇傽䔇臯㔗埗蓕婋麵䔇 WHERE 床埖㔗

  3. 套悩弄滯庖 GROUP BY 床埖膷庺儌彖潊对陉婔婻潡崔婻昄唚䔇婉劯䂇麯㔗套悩庺䯄庖 HAVING 床埖闼幽垄潽鍴闼底婉悇轿䂍庺溇傽䔇䂇㔗埗蓕婋麵䔇 GROUP BY 床埖 启 HAVING 床埖㔗

  4. 垂鍙膷庺臯儖嘪䫘 SELECT 膷庺臘膆嚟鐽凹懟婔婻锬婺䔇臯誕臯螇䞖㔗埗蓕婋麵䔇 SELECT 彖臘㔗

  5. 嘪䫘 UNION, INTERSECT, EXCEPT 埇傖檪崔婻 SELECT 臺埖䔇膷庺劽幽潊婔婻䂷悩镖㔗UNION 淉嘩严誫啂婴婻䂷悩镖䔇幽镖㔗INTERSECT 淉嘩严誫啂婴婻䂷悩镖䔇庴镖㔗EXCEPT 淉嘩严誫啂婘丸婔婻䂷悩镖凹丸庯婻䂷悩镖䔇噞镖㔗婉䞇巻䓉愙喕麉崉䔇臯鄘赆役鍴鍴麂弄滯庖 ALL 㔗埗黙婋麵䔇 UNION 床埖㔕INTERSECT 床埖㔕EXCEPT 床埖㔗

  6. 套悩弄滯庖 ORDER BY 床埖闼幽誫啂䔇臯儖毬䙓毺垔䔇釺废毐废㔗套悩澇橬䂍庺 ORDER BY 闼幽昄扞臯滇毬䙓係䂘螴婺埇傖橔媆䫘潊䔇釺废䂍庺䔇㔗埗黙婋麵䔇 ORDER BY 床埖㔗

  7. DISTINCT 傯䂷悩婺役鍴闼底麉崉䔇臯㔗DISTINCT ON 役鍴闼底对陉欔橬毺垔臘膆嚟䔇臯㔗ALL(䚺䩕)儖誫啂欔橬唍锬臯寙拸麉崉䔇㔗埗黙婋麵䔇 DISTINCT 床埖㔗

  8. 套悩䂍庺庖 LIMIT 潡 OFFSET 床埖闼幽 SELECT 臺埖埻誫啂䂷悩臯䔇婔婻床镖㔗埗黙婋麵䔇 LIMIT 床埖㔗

  9. 套悩弄滯庖 FOR UPDATE 潡 FOR SHARE 床埖闼幽 SELECT 臺埖凹幽埏䔇敘桄髕嘟锬垔䔇臯㔗埗黙婋麵䔇 FOR UPDATE/FOR SHARE 床埖㔗

嘹媙釂橬 SELECT 溄鍊䫘準傯臘婺臂埡昄唚㔗嘪䫘 FOR UPDATE 潡 FOR SHARE 誻襕挗 UPDATE 溄鍊㔗

埗昄

FROM 床埖

FROM 床埖婺 SELECT 弄滯婔婻潡蔙崔婻溊臘㔗套悩弄滯庖崔婻溊臘闼幽䂷悩儌滇欔橬溊臘䔇丕則凪䓇(庴埬誂毖)㔗嘖滇锔婩嚔時媹婔底溇傽檪誫啂臯鍊彽潊丕則凪䓇䔇婔婻償䔇床镖㔗

FROM 床埖埇傖寙拸婋彖噄䘹

table_name

婔婻䯄庻䔇臘潡蓖商䔇劉庖(埇傖橬昇嚟媞閄)㔗套悩弄滯庖 ONLY 彍埻欆柟臖臘劥彍臖臘启欔橬噽昆䫘臘(套悩橬䔇臺)鄘赆欆柟㔗埇傖婘臘劉劯麵虘婔婻 * 臘䴺欆欔橬噽劯傼臘嘖婘䕞嬉䔇䬽橸麯認滇䚺䩕䬹攓㔗䚺䩕䔇䬹攓埇傖锔誺媞櫹陉䘞锬釹 sql_inheritance 準櫹埻㔗

alias

婺闼底寙劆彆劉䔇 FROM 釹䕞埡䔇彆劉㔗彆劉䫘庯䚷喍潡蔙婘躻誂毖婺潽鍴溓幬(躻誂毖婺劯婔婻臘儖欆柟崔渇)㔗套悩柊冕庖彆劉闼幽垄儌嚔垯噘锊薟臘潡蔙庘昄䔇垂鍙劉庖懫套套悩䂍庺 FROM foo AS f 闼幽 SELECT 嬷婋䔇婩薪媙釂檪認婻 FROM 釹毬䙓 f 蔯婉滇 foo 嚘䫘㔗套悩喍庖彆劉幘埇傖柊冕婔婻庖枕彆劉彖臘認湙埇傖敪扵臘婺婔婻潡蔙崔婻庖枕䔇劉庖㔗

select

埇傖婘 FROM 床埖麯庺䯄婔婻床 SELECT 㔗垄䔇膷庺嘩䫘喘償滇婺認溇 SELECT 变傴婘噽䫘庻橘麯录傺婔婻婘施臘㔗臙濘懟認婻床 SELECT 媙釂䫘啺拸嚓寙啘㔗幽婫媙釂䂍垄婔婻彆劉㔗嘷䇽VALUES 劯湙幘埇傖婘認麯嘪䫘㔗

function_name

庘昄(䬹彆滇闼底誫啂䂷悩镖䔇庘昄)脄䫘埇傖庺䯄婘 FROM 床埖麯㔗認幽啔儌喘償婘認婻 SELECT 变傴䔇䫘变橘婺檪庘昄䔇膷庺录傺婺婔婻婘施臘婔湙㔗嘷䇽幘埇傖嘪䫘彆劉㔗套悩喍庖彆劉誻埇傖喍婔婻庖枕彆劉彖臘婺庘昄誫啂䔇崉劽䌂傋䔇婔婻潡崔婻匂攓柊冕劉庖敪扵㔗套悩庘昄垔幬婺誫啂 record 䌂傋闼幽媙釂庺䯄婔婻 AS 噿髞庖潡蔙彆劉劯麵虘五婔婻嘵套 ( column_name data_type [, ...] ) 䔇庖枕垔幬彖臘㔗認婻庖枕垔幬彖臘媙釂对陉庘昄誫啂䔇庖枕䔇垂鍙昄䕞启䌂傋㔗

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 蔯蘔認底床埖鄘婉脘庺䯄㔗

婔婻 JOIN 床埖䂇劽婴婻 FROM 釹㔗媙襕施嘪䫘婖拸嚓傖喿垔啯喖䔇釺废㔗套悩澇橬婖拸嚓JOIN 傯噥劏埿啯喖㔗婘傂嘘愙喕婋JOIN 鄘懫锖埙彖锫䔇 FROM 釹䂏垔冖敘䘓㔗

CROSS JOIN 启 INNER JOIN 䫘潊婔婻䞔剘䔇丕則凪䓇启嘹婘 FROM 䔇釽北彖庺婴婻釹䔇䂷悩䕩劯㔗CROSS JOIN 京昽庯 INNER JOIN ON (TRUE) 幘儌滇臘澇橬赆溇傽役鍴䔇臯㔗認䓉誂毖䌂傋埻滇严埙婪䔇桹冪啹婺垄傸启嘹䫘䞔剘䔇 FROM 启 WHERE 䔇昽悩婔湙㔗

LEFT OUTER JOIN 誫啂丕則凪䓇婺欔橬严劽誂毖溇傽䔇臯喉媹婪噥臘婺锔誺誂毖溇傽澇橬对陉埿臘臯䔇闼底臯㔗認湙噥膹䔇臯儖欷匘潊䫘潊臘䔇噘阪桹濘滇婘闼底埿臘凹庫䔇庖枕嘉䘞准婪 NULL 㔗臙濘懟埻婘螇䞖对陉䔇施唍欉嘪䫘 JOIN 床埖䔇溇傽崡北䔇溇傽滇婘螇䞖垯懘幋劯桘媹䔇㔗

䕩庫䔇RIGHT OUTER JOIN 誫啂欔橬喙誂毖䔇䂷悩臯媹婪懟婻婉对陉䔇埿膹臯(噥膹䫘 NULL 欷匘)㔗認埻滇婔婻严埙婪䔇冪彷啹婺攂滇埇傖檪垄蘸扵潊婔婻 LEFT OUTER JOIN 埻襕檪噥膹启埿膹䔇膷噖凹毬婔婋剿埇㔗

FULL OUTER JOIN 誫啂欔橬喙誂毖䔇䂷悩臯媹婪懟婻婉对陉䔇噥膹臯(埿膹䫘 NULL 欷匘)喉媹婪懟婻婉对陉䔇埿膹臯(噥膹䫘 NULL 欷匘)㔗

ON 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 [, ...]

垄儖檪欔橬婘䂇劽臘膆嚟婪拖橬䕩劯唚䔇臯寋䚷潊婔臯㔗expression 埇傖滇婔婻膷噖庖枕劉庖潡蔙滇婔婻膷庺庖枕(SELECT 彖臘釹)䔇劉庖潡废埙潡蔙幘埇傖滇傂懟膷噖庖枕䂇潊䔇臘膆嚟㔗婘橬溓幬䔇愙喕婋婔婻 GROUP BY 䔇劉庖儖赆蓼麪潊膷噖庖枕䔇劉庖蔯婉滇膷庺庖枕䔇劉庖㔗

套悩嘪䫘庖蕔镖庘昄闼幽儌嚔凹懟䂇婺䔇欔橬臯誕臯螇䞖幽䫘潊婔婻剘䋸䔇唚(蔯套悩澇橬 GROUP BY 闼幽蕔镖儖凹锬庺準䔇欔橬臯螇䞖庺婔婻剘䋸䔇唚)㔗套悩庺䯄庖 GROUP BY 闼幽 SELECT 彖臘臘膆嚟婺喉嚘䫘闼底澇橬彖䂇䔇庖枕儌滇麂濘䔇鍴麂櫆婘蕔镖庘昄麯啹婺凹庯橻彖䂇䔇庖枕埇脘嚔誫啂崔婻昄唚㔗

HAVING 床埖

埇锬䔇 HAVING 床埖橬套婋嘵嚟

HAVING condition

認麯 condition 婯婺 WHERE 床埖麯弄滯䔇䕩劯㔗

HAVING 寂鍴庖婔底婉悇轿溇傽䔇䂇臯㔗垄婯 WHERE 婉劯WHERE 婘嘪䫘 GROUP BY 幋嬉誺悴庺剘䋸䔇臯蔯 HAVING 誺悴䫌 GROUP BY 录傺䔇臯㔗婘 condition 麯嚘䫘䔇懟婻庖枕鄘媙釂方溓幬婄嚘䫘婔婻彖䂇䔇臯鍴麂嚘䫘庺䯄婘婔婻蕔镖庘昄麯㔗

HAVING 䔇庺䯄檪昖臵埻潊婔婻彖䂇䔇昖臵剿嘪澇橬 GROUP BY 床埖幘認湙㔗認婔䗹启闼底寙劆蕔镖庘昄嘖澇橬 GROUP BY 床埖䔇昖臵麯埏䫘䔇庋愙滇婔湙䔇㔗欔橬锬埡䔇臯鄘赆螴婺嚔嘵潊婔婻剘婔䔇䂇蔯 SELECT 彖臘启 HAVING 床埖埻脘傯蕔镖庘昄麯麵嚘䫘臘䔇庖枕㔗認湙䔇昖臵婘 HAVING 溇傽婺䩘䔇施唍儖埏庺婔婻臯套悩婺麂䩘彍誫啂镽臯㔗

SELECT 彖臘

SELECT 彖臘(婘 SELECT 启 FROM 噿髞庖幋閘䔇鄘彖)弄滯䂇潊 SELECT 臺埖䔇膷庺臯䔇臘膆嚟㔗認底臘膆嚟埇傖(幽婫锔婩幘嚔)嚘䫘婘 FROM 床埖麯麵螇䞖庺準䔇庖枕㔗锔誺嘪䫘床埖 AS output_name 埇傖婺膷庺庖枕埡婻彆劉㔗認婻彆劉锔婩䫘庯膷庺庖枕䔇滆䴺㔗垄幘埇傖䫘庯婘 ORDER BY 启 GROUP BY 床埖婺嚘䫘庖枕唚嘖滇婉脘婘 WHERE 潡 HAVING 床埖婺嘪䫘婘闼麯嘹媙釂嘪䫘臘膆嚟橸躆㔗

鍴庖臘膆嚟幘埇傖婘膷庺彖臘婺嘪䫘 * 臘䴺欔橬庖枕㔗誻埇傖䫘 table_name.* 嘩婺準躻臖臘䔇欔橬庖枕䔇䚷喍㔗

UNION 床埖

UNION 床埖䔇婔轸嘵嚟滇

select_statement UNION [ ALL ] select_statement

認麯䔇 select_statement 滇傂懟澇橬 ORDER BY, LIMIT, FOR UPDATE, FOR SHARE 床埖䔇 SELECT 臺埖㔗套悩䫘婖拸嚓寙啘ORDER BY 启 LIMIT 埇傖鍇五婘床臘膆嚟麯㔗套悩澇橬婖拸嚓認底床埖儖庴䂍 UNION 䔇䂷悩嘪䫘蔯婉滇䂍垄傸埿膹䔇膷噖臘膆嚟㔗

UNION 淉嘩严螇䞖闼底潬埪彄䔇欔橬 SELECT 臺埖誫啂䔇臯䔇䂷悩蕫劽㔗婔婻臯套悩躿儏婘婴婻䂷悩镖婺䔇婔婻麯麵庺䯄闼幽垄儌嚔婘認婴婻䂷悩镖䔇镖劽蕫劽婺㔗婴婻嘩婺 UNION 䕘毖淉嘩昄䔇 SELECT 媙釂䫘潊䕩劯昄䕞䔇庖枕幽婫凹庫䔇庖枕媙釂橬噚垹䔇昄扞䌂傋㔗

䚺䩕䔇 UNION 䂷悩婉寙劆傂嘘麉崉䔇臯鍴麂弄滯庖 ALL 床埖㔗ALL 彽溵庖潽鍴麉崉䔇媘嘩㔗啹溴UNION ALL 锔婩懫 UNION 滯滆襕媆埇脘䔇愙喕婋儘麟嘪䫘 ALL 㔗

劯婔婻 SELECT 臺埖婺䔇崔婻 UNION 淉嘩严滇傯噥劏埿螇䞖䔇鍴麂䫘婖拸嚓誕臯庖湺臖㔗

䕞嬉FOR UPDATE 启 FOR SHARE 婉脘婘 UNION 䔇䂷悩潡膷噖婺弄滯㔗

INTERSECT 床埖

INTERSECT 床埖䔇婔轸嘵嚟滇

select_statement INTERSECT [ ALL ] select_statement

select_statement 滇傂嘘婉婥 ORDER BY, LIMIT, FOR UPDATE, FOR SHARE 床埖䔇 SELECT 臺埖㔗

INTERSECT 螇䞖潬埪䔇 SELECT 臺埖誫啂䔇臯镖劽䔇庴镖㔗套悩婔婻臯婘婴婻䂷悩镖婺鄘庺䯄闼幽垄儌婘婴婻䂷悩镖䔇庴镖婺㔗

INTERSECT 䔇䂷悩婉寙劆傂嘘麉崉臯鍴麂嘹弄滯庖 ALL 锬釹㔗䫘庖 ALL 傖劯婔婻婘噥膹䔇臘麯橬 m 婻麉崉蔯婘埿膹臘麯橬 n 婻麉崉䔇臯儖庺䯄 min(m, n) 渇㔗

鍴麂䫘婖拸埙毺滯釺废劯婔婻 SELECT 臺埖婺䔇崔婻 INTERSECT 淉嘩严滇傯噥劏埿螇䞖䔇㔗INTERSECT 懫 UNION 䂏垔冖敘䘓幘儌滇臘 A UNION B INTERSECT C 儖䊖蓼潊 A UNION (B INTERSECT C) 鍴麂嘹䫘婖拸嚓弄滯㔗

䕞嬉婉脘䂍 INTERSECT 䔇䂷悩潡蔙傂嘘 INTERSECT 䔇膷噖弄滯 FOR UPDATE 启 FOR SHARE 㔗

EXCEPT 床埖

EXCEPT 床埖橬套婋䔇锔䫘嘵嚟

select_statement EXCEPT [ ALL ] select_statement

select_statement 滇傂嘘澇橬 ORDER BY, LIMIT, FOR UPDATE, FOR SHARE 床埖䔇 SELECT 臘膆嚟㔗

EXCEPT 淉嘩严螇䞖庻婘庯噥膹 SELECT 臺埖䔇膷庺蔯婉庻婘庯埿膹 SELECT 臺埖膷庺䔇臯㔗

EXCEPT 䔇䂷悩婉寙劆傂嘘麉崉䔇臯鍴麂弄滯庖 ALL 锬釹㔗嘪䫘 ALL 施婔婻婘噥膹臘婺橬 m 婻麉崉蔯婘埿膹臘婺橬 n 婻麉崉䔇臯儖婘䂷悩婺庺䯄 max(m-n,0) 渇㔗

鍴麂䫘婖拸嚓毺滯釺废劥彍劯婔婻 SELECT 臺埖婺䔇崔婻 EXCEPT 淉嘩严滇傯噥劏埿螇䞖䔇㔗EXCEPT 启 UNION 䔇䂏垔亓彆䕩劯㔗

䕞嬉婉脘䂍 EXCEPT 䔇䂷悩潡蔙傂嘘 EXCEPT 䔇膷噖弄滯 FOR UPDATE 潡 FOR SHARE 床埖㔗

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;

認婻䬹攓䔇婔婻匔鍊儌滇庫䫘庯 UNION, INTERSECT, EXCEPT 昖臵䔇 ORDER BY 床埖埻脘婘婔婻膷庺庖枕劉潡蔙昄庖婪弄滯蔯婉脘婘婔婻臘膆嚟婪弄滯㔗

臙濘懟套悩婔婻 ORDER BY 臘膆嚟滇婔婻䞔剘劉䓄劯施对陉䂷悩庖枕启膷噖庖枕ORDER BY 儖檪垄蓼麪潊䂷悩庖枕劉䓄㔗認启 GROUP BY 婘劯湙愙喕婋啔䔇锬拷溼䕩埉㔗認湙䔇婉婔躘滇䫌 SQL 湺庖嚺彽䔇㔗

埇傖䂍 ORDER BY 床埖麯懟婻庖枕媹婔婻埇锬䔇 ASC(剺废䚺䩕)潡 DESC(鍉废)噿髞庖㔗誻埇傖婘 USING 床埖麯弄滯婔婻毐废淉嘩严準垂䯄毐废㔗ASC 京昽庯嘪䫘 USING < 蔯 DESC 京昽庯嘪䫘 USING > 㔗嘖滇婔婻䫘潙垔幬䌂傋䔇录傺蔙埇傖滯䇞垔幬䚺䩕䔇毐废釺废幽婫埇傖嘪䫘噽傡劉䓄䔇淉嘩严㔗

NULL 赆螴婺懫噽傡唚鄘崓㔗扵埖臺臘剺废毐废施NULL 毐婘橆儆蔯鍉废毐废施 NULL 毐婘嚔崘㔗

庖严䌂傋䔇昄扞滇毬䙓寺嘘䕩噿䔇庖严镖釺废毐废䔇認婻寺嘘滇婘昄扞康镖䆴彺哋寡䔇施唍傺䆋䔇㔗

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 䂇麯麵驔襕䔇臯䔇嚻噽亓㔗

LIMIT 床埖

LIMIT 床埖䫌婴婻䋸䆋䔇床埖䂇潊

LIMIT { count | ALL }
OFFSET start

count 弄滯誫啂䔇橔崓臯昄蔯 start 弄滯嚔哋誫啂臯幋嬉媘䘖䔇臯昄㔗套悩婴婻鄘毺垔庖闼幽婘嚔哋螇䞖 count 婻誫啂臯幋嬉儖噽虿誺 start 臯㔗

嘪䫘 LIMIT 䔇婔婻喘幹愇滇嘪䫘婔婻 ORDER BY 床埖檪䂷悩臯鍊彽潊婔婻嫇婔䔇釺废㔗劥彍嘹嚔冖彄方濘鵇桍䔇䂷悩床镖嘹埇脘愿襕丸剕臯彄丸庯剕臯鍴麂嘹弄滯 ORDER BY 劥彍嘹婉䘖長傔幽釺废㔗

昖臵嚻寡単婘䫘潊昖臵蓇彐施嚔檪 LIMIT 蔄荏誕寂欔傖嘹冽橬埇脘啹䂍庺䔇 LIMIT 启 OFFSET 唚婉劯蔯冖彄婉劯䔇蓇彐(䫘潊婉劯䔇臯废)㔗啹溴䫘婉劯䔇 LIMIT/OFFSET 唚锬拷婉劯䔇昖臵䂷悩䔇床镖儖婉嚔库䫘婔躘䔇䂷悩鍴麂嘹䫘 ORDER BY 嚺彽䫘潊婔婻埇鵇螇䔇䂷悩釺废㔗認埇婉滇 bug 認滇 SQL 䫘準䔇䬹䗹啹婺鍴麂䫘庖 ORDER BY 亥溘釺废SQL 婉媺臕昖臵䫘潊䔇䂷悩橬傂嘘䬹垔䔇釺废㔗

FOR UPDATE/FOR SHARE 床埖

FOR UPDATE 床埖䔇嘵嚟套婋

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

冽䕩誏䔇 FOR SHARE 床埖䔇嘵嚟套婋

FOR SHARE [ OF table_name [, ...] ] [ NOWAIT ]

FOR UPDATE 傴闼底赆 SELECT 演䘵庺準䔇臯赆髕嘟儌償襕敘桄婔湙㔗認湙儌镪噉垄傸婘嘷嬉庋媇䂷溘嬉赆噽垄庋媇媞櫹潡蔙役鍴幘儌滇臘噽垄嚕商 UPDATE, DELETE, SELECT FOR UPDATE 認底臯䔇庋媇儖赆黂凂䕘彄嘷嬉庋媇䂷溘㔗劯湙套悩婔婻準躻噽垄庋媇䔇 UPDATE, DELETE, SELECT FOR UPDATE 噾䂟髕嘟庖昊婻潡昊底锬垔䔇臯SELECT FOR UPDATE 儖京彄闼底庋媇䂷溘幽婫儖锟劯髕嘟幽誫啂敘桄䔇臯(潡蔙婉誫啂臯套悩臯噾䂟赆役鍴)㔗敘崔䔇螘螺埗黙䆹12㔗

婺庖镪噉淉嘩京写噽垄庋媇柊庴嘪䫘 NOWAIT 锬釹㔗套悩赆锬拷䔇臯婉脘䆋剿赆髕嘟闼幽 SELECT FOR UPDATE NOWAIT 儖嚔䆋剿挺檖婔婻髍臇蔯婉滇京写㔗臙濘懟NOWAIT 埻锗䫘庯臯亓彆䔇髕襕挗䔇臘亓髕 ROW SHARE 傉䇽傖锔婩䔇桹濘誕臯(埗黙 䆹12)㔗套悩驔襕䫿臙臘亓彆䔇髕劯施埽婉京写闼幽嘹埇傖嘪䫘 LOCK 䔇 NOWAIT 锬釹㔗

FOR SHARE 䔇臯婺䌂嚚埻滇垄婘懟婻演䘵庺準䔇臯婪襕挗婔婻噌庆髕蔯婉滇婔婻毐垄髕㔗婔婻噌庆髕黂凂噽垄庋媇婘認底臯婪欓臯 UPDATE, DELETE, SELECT FOR UPDATE 剘婉黂溵傡傸欓臯 SELECT FOR SHARE 㔗

套悩婘 FOR UPDATE 潡 FOR SHARE 婺滯䇞毺垔庖臘劉庖闼幽儖埻橬認底毺垔䔇臘赆髕垔噽傡婘 SELECT 婺嘪䫘䔇臘儖婉嚔赆髕垔㔗婔婻噽劯婉婥臘彖臘䔇 FOR UPDATE 潡 FOR SHARE 床埖儖髕垔臖变傴婺欔橬嘪䫘䔇臘㔗套悩 FOR UPDATE 潡 FOR SHARE 庫䫘庯婔婻蓖商潡蔙床昖臵垄劯湙儖髕垔欔橬臖蓖商潡床昖臵婺嘪䫘彄䔇臘㔗

崔婻 FOR UPDATE 启 FOR SHARE 床埖埇傖䫘庯婺婉劯䔇臘毺垔婉劯䔇髕垔昇嚟㔗套悩婔婻臘庺劯施庺䯄(潡锊劆劯施庺䯄)婘 FOR UPDATE 启 FOR SHARE 床埖婺闼幽儖毬䙓 FOR UPDATE 崇䊖㔗䌂嚚䔇套悩嘌巉婔婻臘䔇傂懟床埖婺庺䯄庖 NOWAIT 闼幽臖臘儖毬䙓 NOWAIT 崇䊖㔗

FOR UPDATE 启 FOR SHARE 婉脘婘闼底方濘嘪䫘䋸䆋䔇臘臯橙捄湺臖誫啂臯䔇䯇嵄麯懫套垄婉脘启蕔镖婔蕙嘪䫘㔗

躥只

婉襕噽髕垔婔婻臯䇽劯婘锟劯䔇媺庻䗹潡 PL/pgSQL 嚗婩庖婺媞櫹垄㔗啹婺套悩劯準啂悔䔇臺儖凚躘認婻媆婵崌㔗冋套

BEGIN;
SELECT * FROM mytable WHERE key = 1 FOR UPDATE;
SAVEPOINT s;
UPDATE mytable SET ... WHERE key = 1;
ROLLBACK TO s;

ROLLBACK 幋劯臖臯儖赆蓼髕蔯婉滇誫啂噽婪婔婻媺庻䗹䪽攕(赆髕垔嘖橻赆媞櫹)㔗套悩婔婻婘嘷嬉庋媇婺髕垔䔇臯赆敘桄潡役鍴潡蔙婔婻噌庆髕赆剺亓婺毐垄髕認䓉愙喕儌埇脘嚔庺䯄㔗婘認婴䓉愙喕婋噽嬉䔇髕䪽攕儖赆镖媻㔗套悩庋媇劯準啂悔彄婔婻傋庯橔彺髕变傴启劯準埻敘庖髕䪽攕幋閘䔇昊婻䗹闼幽臖臯儖臘䯄冖喘償湹橸澇橬赆髕垔婔湙㔗認婻垂䯄婪䔇䚺鍙埇脘婘儖準䔇 PostgreSQL 䬽橸婺冖彄媞臖㔗

躥只

婔婻SELECT 变傴埇傖劯施嘪䫘 LIMIT 启 FOR UPDATE/SHARE 認䓉愙喕婋垄誫啂䔇臯昄埇脘懫 LIMIT 弄滯䔇儏㔗認滇啹婺 LIMIT 噽䫘昽㔗变傴锬埡毺垔昄䕞䔇昄扞臯嘖滇埇脘嚔婘噽婺婔臯潡崔臯婪诙埡髕䔇施唍赆黂凂㔗嘖 SELECT 䔇黂凂赆蓼鍴劯臖臯埇脘噾䂟赆役鍴潡蔙敘桄庖啹溴婉喉悇轿昖臵䔇 WHERE 溇傽婘認䓉愙喕婋婉嚔誫啂垄㔗

冋床

儖臘 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

䂘螇䫘 kind 彖䂇䔇懟䂇䫕嘌䔇阪庥(len)攂启婉轿庫償施䔇䂇

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

噚垹攓

SELECT 臺埖启 SQL 湺庖噚垹㔗嘖滇誻橬婔底欷匘启婔底䚺儏䔇䬹攓㔗

䩕䘖 FROM 床埖

PostgreSQL 噕螩婘婔婻昖臵麯䩕䘖 FROM 床埖㔗垄䔇橔䕘毖䫘锫儌滇螇䞖䞔剘䔇婩麟臘膆嚟䔇䂷悩

SELECT 2+2;

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

噽垄橬底 SQL 昄扞康婉脘認幽啔鍴麂嚘噖婔婻剘臯䔇嚻臘啔婺 SELECT 䔇昄扞溊㔗

臙濘懟套悩澇橬弄滯 FROM 床埖闼幽昖臵婉脘嚘䫘傂嘘昄扞康臘㔗懫套婋麵䔇昖臵滇麂濘䔇

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

PostgreSQL 8.1 幋嬉䔇䬽橸櫇毕認䓉嘵嚟䔇昖臵婺昖臵麯嚘䫘䔇懟婻臘鄘嵂媹婔婻锊劆䔇溇䕞彄 FROM 床埖婺㔗䯄婘認婻婉喉滇䚺䩕䔇臯婺庖啹婺垄启 SQL 湺庖婉噚垹幽婫赆螴婺崻垹滷凚躘髍臇庖㔗婺庖噚垹闼底冺蕡認婻臯婺䔇庫䫘埇傖欷嚔 add_missing_from 陉䘞埗昄㔗

AS 噿髞庖

婘 SQL 湺庖麯埇锬䔇 AS 噿髞庖滇崔嘍䔇埇傖媘䘖毬蔯婉凹臺埖库䫘傂嘘嘌巉㔗PostgreSQL 彖悊単婘麉变劉膷庺庖枕施驔襕認婻噿髞庖啹婺䌂傋欷匘䔇䬹攓嚔婘認䓉愙喕婋凚躘溓幬㔗婉誺AS 婘 FROM 釹麯滇埇锬䔇㔗

GROUP BY 启 ORDER BY 麯埇䫘䔇劉庖䷺閘

婘 SQL-92 湺庖麯ORDER BY 床埖埻脘嘪䫘䂷悩庖枕劉潡蔙䚡埙蔯 GROUP BY 床埖埻脘䫘嘺庯膷噖庖枕劉䔇臘膆嚟㔗PostgreSQL 凹認婴婻床埖鄘誕臯庖欷匘噕螩埥崡婔䓉锬拷(嘖滇套悩庻婘溓幬彍嘪䫘湺庖䔇蓼麪)㔗PostgreSQL 誻噕螩婴婻床埖弄滯傂懟䔇臘膆嚟㔗臙濘懟婘臘膆嚟婺庺䯄䔇劉庖攂滇赆嘷嘩膷噖庖枕劉蔯婉滇䂷悩庖枕劉㔗

SQL:1999 傖埪幋劯䔇婔婻䘖冞婉劯䔇垔幬幽婉脘启 SQL-92 垯噘劏嬉噚垹㔗婉誺婘崓崔昄愙喕婋PostgreSQL 儖檪婔婻 ORDER BY 潡 GROUP BY 臘膆嚟蓼悊潊婺 SQL:1999 彽垔䔇闼湙㔗

麂湺庖床埖

DISTINCT ON, LIMIT, OFFSET 床埖鄘澇橬婘 SQL 湺庖婺垔幬㔗

劯锔饡釕嬉誕
SAVEPOINT婪婔亓SELECT INTO