pg学习_数据更新

数据更新1、插入语法结构highgo=# \h insertCommand:     INSERTDescription: create new rows in a tableSyntax:[ WITH [ RECURSIVE ] with_query [, ...] ]INSERT INTO table_name [ ( column_name [, ...] ) ]    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]单条记录插入highgo=# select * from test1; id | name ----+------(0 rows)highgo=# insert into test1 values(001,'adam');INSERT 0 1部分字段插入highgo=# insert into test1(id) values(002);INSERT 0 1highgo=# select * from test1; id | name ----+------  1 | adam  2 | (2 rows)通过其他表插入记录highgo=# insert into test1(id,name)highgo-# select id,name from test2;INSERT 0 1highgo=# select * from test1; id |  name  ----+--------  1 | adam  2 |   2 | lilith(3 rows)通过脚本插入[highgo@node1 ~]$ vi insert_test1.sqlinsert into test1 values(003,'eva');执行插入highgo=# \i insert_test1.sqlINSERT 0 1highgo=# select * from test1; id |  name  ----+--------  1 | adam  2 |   2 | lilith  3 | eva(4 rows)2、更新操作语法结构highgo=# \h updateCommand:     UPDATEDescription: update rows of a tableSyntax:[ WITH [ RECURSIVE ] with_query [, ...] ]UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]    SET { column_name = { expression | DEFAULT } |          ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]    [ FROM from_list ]    [ WHERE condition | WHERE CURRENT OF cursor_name ]    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]highgo=# update test1 set name='adam' where id=2;UPDATE 2highgo=# select * from test1; id | name ----+------  1 | adam  3 | eva  2 | adam  2 | adam(4 rows)利用子查询更新多列highgo=# update test2highgo-# set name=(select name from test1 where id=1),highgo-# id=(select id from test1 where name='adam')highgo-# where id=2;UPDATE 2highgo=# select * from test2; id | name ----+------  1 | adam  1 | adam(2 rows)通过其他表的条件更新数据highgo=# update test1highgo-# set name=(select name from test2 where id =2)highgo-# where name='adam';UPDATE 2highgo=# select * from test1; id |  name  ----+--------  3 | eva  1 | eva  2 | lilith  2 | lilith(4 rows)3、删除操作语法结构highgo=# \h deleteCommand:     DELETEDescription: delete rows of a tableSyntax:[ WITH [ RECURSIVE ] with_query [, ...] ]DELETE FROM [ ONLY ] table_name [ * ] [ [ AS ] alias ]    [ USING using_list ]    [ WHERE condition | WHERE CURRENT OF cursor_name ]    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]highgo=# delete from test1 where id=2;DELETE 2highgo=# select * from test1; id | name ----+------  3 | eva  1 | eva(2 rows)通过其他表的条件删除数据highgo=# delete from test1 where name in (select name from test2);DELETE 2highgo=# select * from test1; id | name ----+------(0 rows)