DELETE

语法说明

DELETE 用于删除单表或多表中的记录。

语法结构

单表语法结构

  1. DELETE FROM tbl_name [[AS] tbl_alias]
  2.     [WHERE where_condition]
  3.     [ORDER BY ...]
  4.     [LIMIT row_count]

DELETE 语句从 tbl_name 中删除行，并返回已删除的行数。

参数释义

  • WHERE 从句用于指定用于标识要删除哪些行的条件。若无 WHERE 从句，则删除所有行。

  • ORDER BY 从句，指按照指定的顺序删除行。

  • LIMIT 从句用于限制可删除的行数。

示例

  • 单表示例
  1. CREATE TABLE t1 (a bigint(3), b bigint(5) primary key);
  2. insert INTO t1 VALUES (1,1),(1,2);
  3. delete from t1 where a=1 limit 1;
  5. mysql> select * from t1;
  6. +------+------+
  7. | a    | b    |
  8. +------+------+
  9. |    1 |    2 |
  10. +------+------+
  • 多表示例：

同时也支持多表 JOIN 语句。

  1. drop table if exists t1;
  2. drop table if exists t2;
  3. create table t1 (a int);
  4. insert into t1 values(1), (2), (4);
  5. create table t2 (b int);
  6. insert into t2 values(1), (2), (5);
  7. delete t1 from t1 join t2 where t1.a = 2;
  9. mysql> select * from t1;
  10. +------+
  11. | a    |
  12. +------+
  13. |    1 |
  14. |    4 |
  15. +------+
  16. 2 rows in set (0.00 sec)
  1. drop database if exists db1;
  2. drop database if exists db2;
  3. create database db1;
  4. create database db2;
  5. use db2;
  6. drop table if exists t1;
  7. create table t1 (a int);
  8. insert into t1 values (1),(2),(4);
  9. use db1;
  10. drop table if exists t2;
  11. create table t2 (b int);
  12. insert into t2 values(1),(2),(3);
  13. delete from db1.t2, db2.t1 using db1.t2 join db2.t1 on db1.t2.b = db2.t1.a where 2 > 1;
  15. mysql> select * from db1.t2;
  16. +------+
  17. | b    |
  18. +------+
  19. |    3 |
  20. +------+
  21. mysql> select * from db2.t1;
  22. +------+
  23. | a    |
  24. +------+
  25. |    4 |
  26. +------+
  27. 1 row in set (0.00 sec)