DELETE

描述

删除与谓词匹配的行。 如果未提供谓词，则删除所有行。

语法

DELETE FROM table_name [table_alias] [WHERE predicate]

参数

• table_name

  要删除数据的表

  语法: [ database_name. ] table_name

• table_alias

  定义表的别名。 该别名不得包含列的列表。

• WHERE

  按谓词筛选要删除的行。

  WHERE 谓词不支持子查询。

示例

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p1|                     1|       1.0|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+
|       p4|                     4|       4.0|
+---------+----------------------+----------+
|       p5|                     5|       5.0|
+---------+----------------------+----------+
|       p6|                     6|       6.0|
+---------+----------------------+----------+


DELETE FROM t1 WHERE c2 = 1;

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+
|       p4|                     4|       4.0|
+---------+----------------------+----------+
|       p5|                     5|       5.0|
+---------+----------------------+----------+
|       p6|                     6|       6.0|
+---------+----------------------+----------+

DELETE FROM t1 tt WHERE tt.c2 < 3;

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+
|       p4|                     4|       4.0|
+---------+----------------------+----------+
|       p5|                     5|       5.0|
+---------+----------------------+----------+
|       p6|                     6|       6.0|
+---------+----------------------+----------+

DELETE FROM t1 WHERE c2 IN (1, 2, 3)；

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p4|                     4|       4.0|
+---------+----------------------+----------+
|       p5|                     5|       5.0|
+---------+----------------------+----------+
|       p6|                     6|       6.0|
+---------+----------------------+----------+

DELETE FROM t1；

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
+---------+----------------------+----------+


-- rows with null values
SELECT * FROM t2;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     null|                     1|       1.0|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+

DELETE FROM t1 WHERE c1 is null

SELECT * FROM t2;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+


-- rows with null values using EqualNullSafe
SELECT * FROM t2;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     null|                     1|       1.0|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+

DELETE FROM t1 WHERE c1 <=> null

SELECT * FROM t2;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+

相关文档

INSERT
UPDATE
COPY INTO
MERGE INTO