pg学习_数据更新

数据更新
1、插入
语法结构
highgo=# \h insert
Command:     INSERT
Description: create new rows in a table
Syntax:
[ 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 1
highgo=# select * from test1;
 id | name 
----+------
  1 | adam
  2 | 
(2 rows)
通过其他表插入记录
highgo=# insert into test1(id,name)
highgo-# select id,name from test2;
INSERT 0 1
highgo=# select * from test1;
 id |  name  
----+--------
  1 | adam
  2 | 
  2 | lilith
(3 rows)
通过脚本插入
[highgo@node1 ~]$ vi insert_test1.sql
insert into test1 values(003,'eva');
执行插入
highgo=# \i insert_test1.sql
INSERT 0 1
highgo=# select * from test1;
 id |  name  
----+--------
  1 | adam
  2 | 
  2 | lilith
  3 | eva
(4 rows)

2、更新操作
语法结构
highgo=# \h update
Command:     UPDATE
Description: update rows of a table
Syntax:
[ 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 2
highgo=# select * from test1;
 id | name 
----+------
  1 | adam
  3 | eva
  2 | adam
  2 | adam
(4 rows)
利用子查询更新多列
highgo=# update test2
highgo-# set name=(select name from test1 where id=1),
highgo-# id=(select id from test1 where name='adam')
highgo-# where id=2;
UPDATE 2
highgo=# select * from test2;
 id | name 
----+------
  1 | adam
  1 | adam
(2 rows)
通过其他表的条件更新数据
highgo=# update test1
highgo-# set name=(select name from test2 where id =2)
highgo-# where name='adam';
UPDATE 2
highgo=# select * from test1;
 id |  name  
----+--------
  3 | eva
  1 | eva
  2 | lilith
  2 | lilith
(4 rows)

3、删除操作
语法结构
highgo=# \h delete
Command:     DELETE
Description: delete rows of a table
Syntax:
[ 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 2
highgo=# select * from test1;
 id | name 
----+------
  3 | eva
  1 | eva
(2 rows)
通过其他表的条件删除数据
highgo=# delete from test1 where name in (select name from test2);
DELETE 2
highgo=# select * from test1;
 id | name 
----+------
(0 rows)