删除数据

本章将讨论如何使用 DELETE 语句删除 PostgreSQL 数据，以及配合 RETURNING 子句在删除数据的同时，返回已删除的内容。最后，还将介绍 DELETE JOIN：根据另一张表中的数据进行删除。

DELETE

DELETE FROM 表名
WHERE 条件
[RETURNING <字段列表 | *>]

示例

示例数据：

DROP TABLE IF EXISTS links;

CREATE TABLE links (
    id serial PRIMARY KEY,
    url varchar(255) NOT NULL,
    name varchar(255) NOT NULL,
    description varchar(255),
    rel varchar(10),
    last_update date DEFAULT now()
);

INSERT INTO  
   links 
VALUES 
   ('1', 'https://axum.rs', 'AXUM中文网', '学习AXUM开发', 'follow', '2013-06-02'),
   ('2', 'http://www.oreilly.com', 'O''Reilly Media', 'O''Reilly Media', 'nofollow', '2013-06-02'),
   ('3', 'http://www.google.com', 'Google', 'Google', 'nofollow', '2013-06-02'),
   ('4', 'http://www.yahoo.com', 'Yahoo', 'Yahoo', 'nofollow', '2013-06-02'),
   ('5', 'http://www.bing.com', 'Bing', 'Bing', 'nofollow', '2013-06-02'),
   ('6', 'http://www.facebook.com', 'Facebook', 'Facebook', 'nofollow', '2013-06-01'),
   ('7', 'https://www.tumblr.com/', 'Tumblr', 'Tumblr', 'nofollow', '2013-06-02'),
   ('8', 'http://www.postgresql.org', 'PostgreSQL', 'PostgreSQL', 'nofollow', '2013-06-02');

删除ID为8的记录

DELETE FROM links
WHERE id = 8;

删除ID为7的记录，并返回删除的数据：

DELETE FROM links
WHERE id = 7
RETURNING *;

DELETE JOIN

语法：

示例

示例数据：

DROP TABLE IF EXISTS contacts;
CREATE TABLE contacts(
   contact_id serial PRIMARY KEY,
   first_name varchar(50) NOT NULL,
   last_name varchar(50) NOT NULL,
   phone varchar(15) NOT NULL
);


DROP TABLE IF EXISTS blacklist;
CREATE TABLE blacklist(
    phone varchar(15) PRIMARY KEY
);


INSERT INTO contacts(first_name, last_name, phone)
VALUES ('John','Doe','(408)-523-9874'),
       ('Jane','Doe','(408)-511-9876'),
       ('Lily','Bush','(408)-124-9221');


INSERT INTO blacklist(phone)
VALUES ('(408)-523-9874'),
       ('(408)-511-9876');

删除存在于黑名单的联系人

DELETE FROM contacts 
USING blacklist
WHERE contacts.phone = blacklist.phone;

可以使用子查询实现以上功能：

DELETE FROM contacts
WHERE phone IN (SELECT phone FROM blacklist);