数据操纵语言(DML，Data Manipulation Language)，用于插入、更新和删除数据，主要包含INSERT语句、UPDATE语句、DELETE语句。

DML语句

1、插入语句

INSERT语句以"INSERT INTO"开头，后面接表名，然后跟VALUES关键字，之后是小括号括起来的以逗号分隔的各列数据。表名后面可以指定要插入的数据列。

postgres=# create table t1(a int , b text);
CREATE TABLE
postgres=# insert into t1 values(1,'abc');
INSERT 0 1
postgres=# insert into t1 (b) values('def');
INSERT 0 1
postgres=# insert into t1 (a) values('3');
INSERT 0 1
postgres=# select * from t1;
 a |  b
---+-----
 1 | abc
   | def
 3 |
(3 rows)

可以使用\h insert查看帮助：

postgres=# \h insert into
No help available for "insert into".
Try \h with no arguments to see available help.
postgres=# \h insert
Command:     INSERT
Description: create new rows in a table
Syntax:
[ WITH [ RECURSIVE ] with_query [, ...] ]
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]
    [ OVERRIDING { SYSTEM | USER } VALUE ]
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
    [ ON CONFLICT [ conflict_target ] conflict_action ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

where conflict_target can be one of:

    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]
    ON CONSTRAINT constraint_name

and conflict_action is one of:

    DO NOTHING
    DO UPDATE SET { column_name = { expression | DEFAULT } |
                    ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                    ( column_name [, ...] ) = ( sub-SELECT )
                  } [, ...]
              [ WHERE condition ]

URL: https://www.postgresql.org/docs/12/sql-insert.html

2、更新语句

更新语句update，后面接表名，然后"set"关键字，之后是需要修改的列

postgres=# update t1 set b='abc';
UPDATE 3
postgres=# select * from t1;
 a |  b
---+-----
 1 | abc
   | abc
 3 | abc
(3 rows)
postgres=# update t1 set b='def' where a=3;
UPDATE 1
postgres=# select * from t1;
 a |  b
---+-----
 1 | abc
   | abc
 3 | def
(3 rows)

使用\h update查看帮助：

postgres=# \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 [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
          ( column_name [, ...] ) = ( sub-SELECT )
        } [, ...]
    [ FROM from_list ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-update.html

3、删除语句

删除语句以"delete from"开始，后面跟表名，然后加"where"子句指定要删除的记录，不指定默认删整个表

postgres=# delete from t1 where a=3;
DELETE 1
postgres=# select * from t1;
 a |  b
---+-----
 1 | abc
   | abc
(2 rows)

postgres=# delete from t1;
DELETE 2
postgres=# select * from t1;
 a | b
---+---
(0 rows)

使用\h delete查看帮助：

postgres=# \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 ] [, ...] ]

URL: https://www.postgresql.org/docs/12/sql-delete.html

PostgreSQL基础：DML语句

DML语句

1、插入语句

2、更新语句

3、删除语句