【PostgreSQL 17】12 DML语句

插入数据

指定字段

INSERT INTO dept(department_id, department_name)
VALUES(10, 'Administration');

为所有字段顺序指定值 可省略字段名

INSERT INTO dept
VALUES(20, 'Design');

多行 VALUES之后逗号分隔

INSERT INTO dept
VALUES(30, 'Software'),(40, 'Sale');

复制数据

可以将一个查询语句的结果插入表中

CREATE TABLE dept1 (LIKE dept);INSERT INTO dept1
SELECT * FROM dept;SELECT * FROM dept1;

返回插入结果

INSERT INTO dept
VALUES (50, 'Purchasing')
RETURNING department_id, department_name;

更新数据

单表更新

UPDATE dept
SET department_id = department_id + 100;

跨表更新

UPDATE dept1 t1
SET department_name = t.department_name
FROM dept t
WHERE t.department_id = t1.department_id;

返回更新后的数据

UPDATE dept1
SET department_name='No';UPDATE dept1 t1
SET department_name = t.department_name
FROM dept t
WHERE t.department_id = t1.department_id
RETURNING *;

删除数据

单表删除

DELETE FROM dept
WHERE department_id = 10;TABLE dept;

跨表删除

DELETE FROM dept1 t1
USING dept t
WHERE t.department_id = t1.department_id;

返回所删除的记录

DELETE FROM dept 
WHERE department_id > 20
RETURNING *;

合并数据

INSERT INTO ON CONFLICT

INSERT INTO dept
VALUES (10, 'Design')
ON CONFLICT (department_id)
DO NOTHING;

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第37节