婘埥婔婻PostgreSQL昖臵床昖臵潡喙鄘昖臵潡啯喖昖臵滇昖臵幽啯噖婘WHERE床埖婺㔗

嘪䫘床昖臵誫啂䔇昄扞儖赆䫘準婘婂昖臵婺嘩婺溇傽傖誕婔準鍊彽襕演䘵䔇昄扞㔗 gitbook.net

埇傖嘪䫘床昖臵䔇橬SELECTINSERTUPDATE启DELETE臺埖婯誊䞖严套=<>>=<=IN京婔蕙嘪䫘㔗

橬庹婻床昖臵媙釂镕冻䔇蓇彍

gitbook.net

• 媙釂䫘拸埙拸蕙準䔇床昖臵㔗

  www.gitbook.net

• 床昖臵埻脘橬婔婻婘SELECT床埖婺䔇彖鍴麂崔彖婘婂昖臵䔇昖臵準懫膄噽锬垔䔇彖㔗

  gitbook.net

• ORDER BY婉脘嘪䫘婘床昖臵婺荘䇽婂昖臵儌埇傖嘪䫘ORDER BY㔗GROUP BY埇傖䫘準欓臯䕩劯䔇媘脘婘床昖臵婺䔇ORDER BY㔗 gitbook.net

• 床昖臵誫啂崔庯婔臯埻脘䫘庯嘪䫘崔唚䔇誊䞖严套婺INEXISTSINANY / SOMEALL誊䞖严㔗

  gitbook.net

• 幋閘䔇誊䞖严婉脘䫘床昖臵䇽蔯幋閘埇傖婘床昖臵婺嘪䫘㔗

  www.gitbook.net

SELECT臺埖婺䔇床昖臵嘪䫘

橔婩䫘䔇床昖臵䔇SELECT臺埖㔗嘺橸臺濘套婋

gitbook.net

SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE]) 
gitbook.net
 

冋套

蔄荏COMPANY 臘婺橬傖婋螄嘘

 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000
(7 rows)
 www.gitbook.net 

䯄婘螷潏傸昖臵傖婋床昖臵SELECT臺埖 gitbook.net

testdb=# SELECT *
     FROM COMPANY
     WHERE ID IN (SELECT ID
                  FROM COMPANY
                  WHERE SALARY > 45000) ; 
www.gitbook.net
 

認儖库䫘傖婋䂷悩

www.gitbook.net

 id | name  | age |  address    | salary
----+-------+-----+-------------+--------
  4 | Mark  |  25 | Rich-Mond   |  65000
  5 | David |  27 | Texas       |  85000
(2 rows)
 gitbook.net 

INSERT臺埖䔇床昖臵

床昖臵幘埇傖嘪䫘INSERT臺埖㔗INSERT臺埖婺嘪䫘床昖臵誫啂䔇昄扞某噖彄埥婔婻臘㔗婘床昖臵婺欔锬拷䔇昄扞埇傖赆媞櫹䔇傂嘘庖严斖橘潡昄庖庘昄㔗 www.gitbook.net

嘺橸臺濘套婋 gitbook.net

INSERT INTO table_name [ (column1 [, column2 ]) ]
           SELECT [ *|column1 [, column2 ]
           FROM table1 [, table2 ]
           [ WHERE VALUE OPERATOR ] 
www.gitbook.net
 

垂冋:

蔄荏婔婻臘COMPANY_BKP䔇噙橬䕩嚚䂷悇䔇COMPANY臘幽埇傖录傺嘪䫘䕩劯䔇CREATE TABLE嘪䫘COMPANY_BKP嘩婺臘劉㔗䯄婘崉彽垯昘䔇噸埩臘潊COMPANY_BKP滇臺濘套婋 www.gitbook.net

testdb=# INSERT INTO COMPANY_BKP
     SELECT * FROM COMPANY
     WHERE ID IN (SELECT ID
                  FROM COMPANY) ; www.gitbook.net 

UPDATE臺埖䔇床昖臵

床昖臵埇傖嘪䫘UPDATE臺埖䔇䂷劽㔗剘婻潡崔婻臘婺䔇彖䔇UPDATE臺埖婺嘪䫘床昖臵施埇傖敘桄㔗 www.gitbook.net

嘺橸臺濘套婋 gitbook.net

UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ] gitbook.net 

冋套

啺螆潏傸橬柊冕COMPANY_BKP臘認滇COMPANY臘䔇崺傘㔗 gitbook.net

婋麵䔇䴺冋敘桄COMPANY臘婺䔇噖蕇婺寘準䔇0.50唉欔橬䔇垵潙噽幘醇崓庯潡京庯27 gitbook.net

testdb=# UPDATE COMPANY
     SET SALARY = SALARY * 0.50
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE >= 27 ); 
www.gitbook.net
 

認儖嘌巉婴臯启橔劯COMPANY臘儖橬傖婋螄嘘 www.gitbook.net

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  1 | Paul  |  32 | California  |  10000
  5 | David |  27 | Texas       |  42500
(7 rows)
 
gitbook.net
 

DELETE臺埖䔇床昖臵

臖床昖臵埇䫘庯䂷劽婯DELETE臺埖京婯婪麵柊彄䔇傂嘘臺埖㔗 www.gitbook.net

嘺橸臺濘套婋

gitbook.net

DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
   (SELECT COLUMN_NAME
   FROM TABLE_NAME)
   [ WHERE) ] gitbook.net 

垂冋:

啺螆潏傸橬柊冕COMPANY_BKP臘認滇COMPANY 臘䔇崺傘㔗 gitbook.net

婋麵䔇䴺冋傯COMPANY 臘役鍴螄嘘噽幘醇崓庯潡京庯27䔇欔橬垵潙

gitbook.net

testdb=# DELETE FROM COMPANY
     WHERE AGE IN (SELECT AGE FROM COMPANY_BKP
                   WHERE AGE > 27 ); gitbook.net 

認儖嘌巉婴臯启橔劯COMPANY臘儖橬傖婋螄嘘

 id | name  | age | address     | salary
----+-------+-----+-------------+--------
  2 | Allen |  25 | Texas       |  15000
  3 | Teddy |  23 | Norway      |  20000
  4 | Mark  |  25 | Rich-Mond   |  65000
  6 | Kim   |  22 | South-Hall  |  45000
  7 | James |  24 | Houston     |  10000
  5 | David |  27 | Texas       |  42500
(6 rows) gitbook.net