8 . mysql 学习笔记 - 删除数据

千度麒麟
192 阅读5分钟

在 MySQL 中，删除数据是常见的操作，主要通过 DELETE 语句来实现。DELETE 语句用于从表中删除符合条件的记录。在一些情况下，还可能使用 TRUNCATE 来删除所有记录。以下是 MySQL 删除数据的常用方法汇总，以及具体的案例和笔记。

基础删除 (DELETE)

DELETE 语句用于删除表中的记录。如果没有指定条件，DELETE 会删除表中的所有记录。常见的语法如下：

语法

DELETE FROM table_name WHERE condition;

  • table_name：指定要删除记录的表名。

  • condition：指定删除条件，只有符合条件的记录会被删除。如果省略 WHERE 条件，所有记录都会被删除。

DELETE FROM userTable WHERE id = 1;

删除 id 为 1 的记录 ↑。

DELETE FROM userTable WHERE age > 30;

删除所有 age 大于 30 的记录 ↑。

2. 删除所有记录（不删除表结构）

如果希望删除表中所有记录，但保留表结构，可以使用 DELETE 语句，但不加 WHERE 条件。

DELETE FROM userTable;

该语句会删除 userTable 表中的所有记录，但保留表结构和表中的列 ↑。

注意：删除所有记录时会触发删除操作的触发器（如果存在），并且通常比 TRUNCATE 慢。

3. 删除某一条件下的多条记录

你可以使用 DELETE 语句来删除满足特定条件的多条记录。常用的条件有比较运算符、逻辑运算符、范围运算符等。

DELETE FROM userTable
WHERE age BETWEEN 20 AND 30;

删除 age 在 20 到 30 范围内的记录 ↑。

DELETE FROM userTable
WHERE gender = 'female' AND age < 25;

删除 gender 为 female 且 age 小于 25 的记录 ↑。

DELETE FROM userTable
WHERE phone LIKE '123%';

删除 phone 字段以 123 开头的记录 ↑。

4. 删除特定条数的记录

有时你可能希望删除特定条数的记录。可以结合 LIMIT 子句来限制删除的数量。

DELETE FROM userTable
WHERE age > 30
LIMIT 5;

删除 age 大于 30 的前 5 条记录 ↑。

注意：MySQL 的 DELETE 语句在某些情况下可以结合 LIMIT 使用来删除限制条数的记录。

5. 使用 JOIN 删除

有时你可能需要基于两个表的关联关系来删除记录。可以使用 JOIN 来进行删除操作。

DELETE u FROM userTable u
INNER JOIN userDetails d ON u.id = d.user_id
WHERE d.phone = '1234567890';

  • 这条查询删除所有在 userDetails 表中 phone 为 '1234567890' 的用户记录，同时也删除 userTable 中相关联的记录。

  • 注意：DELETE 操作的语法允许通过 JOIN 删除多个表中的记录，但要确保语法正确。

6. 删除前检查 (SELECT)

在执行 DELETE 语句之前，最好先运行一个 SELECT 语句来查看将被删除的记录，以避免误删数据。

SELECT * FROM userTable
WHERE age > 30;

先查看所有 age 大于 30 的记录，再决定是否执行删除操作 ↑。

7. 删除表中的重复记录

如果你希望删除表中的重复记录，可以通过使用 DELETE 和 JOIN 来实现。为了删除重复的记录，通常需要有一个唯一标识符（如 id）。

假设 userTable 表中存在重复的记录，可以通过以下方式删除重复的记录：

DELETE u1 FROM userTable u1
INNER JOIN userTable u2
ON u1.id > u2.id AND u1.name = u2.name;

这条查询删除所有 name 相同且 id 较大的记录，保留较小的 id（也可以根据需要进行调整） ↑。

8. 删除外键约束下的记录

在删除数据时，若表与其他表存在外键约束，删除操作可能受到限制。MySQL 提供了三种外键约束行为：

  • CASCADE：当父表记录删除时，子表记录也会被自动删除。

  • SET NULL：当父表记录删除时，子表对应字段会被设置为 NULL。

  • NO ACTION / RESTRICT：如果外键约束存在，无法删除相关记录。

DELETE FROM orders
WHERE user_id = 1;

如果 orders 表中的 user_id 与 users 表通过外键关联，并且外键约束设置为 CASCADE，则删除 users 表中的 id = 1 记录时，orders 表中所有 user_id = 1 的记录也会被删除 ↑。

9. TRUNCATE 删除所有记录

TRUNCATE 是另一种用于删除表中所有数据的方法，它与 DELETE 不同，TRUNCATE 删除所有记录且不会触发删除触发器。与 DELETE 的区别：

  • TRUNCATE 比 DELETE 更高效，因为它不逐行删除数据，而是直接清空整个表。

  • TRUNCATE 无法通过 WHERE 子句删除特定记录，它总是删除所有记录。

TRUNCATE TABLE userTable;

该语句会删除 userTable 表中的所有记录，并且会重置自增列的计数器 ↑。

注意：TRUNCATE 操作是不可恢复的，且无法在事务中回滚。

10. 删除带有 LIMIT 的记录

对于某些较大的表，可能需要分批删除记录。你可以结合 LIMIT 和 WHERE 子句，逐步删除记录。

DELETE FROM userTable
WHERE age > 30
LIMIT 100;

这将删除 age > 30 的前 100 条记录。你可以通过多次执行来删除更多的记录 ↑。

方法说明示例
DELETE删除符合条件的记录DELETE FROM userTable WHERE id = 1;
删除所有记录删除表中的所有记录，但保留表结构DELETE FROM userTable;
删除多条记录删除符合特定条件的多条记录DELETE FROM userTable WHERE age > 30;
使用 LIMIT 限制删除记录数限制删除记录的数量DELETE FROM userTable WHERE age > 30 LIMIT 5;
使用 JOIN 删除基于多个表的关联来删除记录DELETE u FROM userTable u INNER JOIN userDetails d ON u.id = d.user_id WHERE d.phone = '1234567890';
删除前检查使用 SELECT 查询来查看将要删除的记录SELECT * FROM userTable WHERE age > 30;
删除重复记录删除表中的重复记录DELETE u1 FROM userTable u1 INNER JOIN userTable u2 ON u1.id > u2.id AND u1.name = u2.name;
外键约束下的删除删除时会影响到与其他表有关的记录（如 CASCADE）DELETE FROM orders WHERE user_id = 1;
使用 TRUNCATE 删除所有记录删除所有记录并重置自增列计数器TRUNCATE TABLE userTable;
分批删除记录使用 LIMIT 分批删除大量数据DELETE FROM userTable WHERE age > 30 LIMIT 100;
avatar
文章
阅读
粉丝