如何在 MySQL 中插入和删除数据

简介

添加和删除数据是相当基础的操作，它们允许你控制数据库维护哪些数据。要插入数据，你需要为每个新行指定满足表每个列要求的数据项。要删除数据，你需要提供要删除的表中行的匹配条件。

在本文中，我们将了解如何使用 INSERT 和 DELETE 命令向 MySQL 表中添加或删除数据。我们将介绍语法，以及更高级的变体，例如在单个语句中操作多行。

回顾表结构

在使用 INSERT 命令之前，你必须了解表的结构，以便能够适应表列、数据类型和约束所施加的要求。

要查找名为 employee 的表的结构，你可以使用 MySQL DESCRIBE 命令

DESCRIBE employee;

+-------------+-----------------+------+-----+-------------------+-------------------+
  Field       | Type            | Null | Key | Default           | Extra             |
+-------------+-----------------+------+-----+-------------------+-------------------+
  employee_id | bigint unsigned | NO   | PRI | NULL              | auto_increment    |
  first_name  | varchar(45)     | NO   |     | NULL              |                   |
  last_name   | varchar(45)     | NO   |     | NULL              |                   |
  last_update | timestamp       | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+-------------+-----------------+------+-----+-------------------+-------------------+
4 rows in set (0.00 sec)

输出显示了表的列名、数据类型和默认值等信息。

另一种方法是显示可用于重新创建表的信息。你可以使用 SHOW CREATE TABLE 命令找到此信息

SHOW CREATE TABLE employee\G

*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `employee_id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL,
  `last_name` varchar(45) NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `employee_id` (`employee_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

在这里，我们使用 \G 终止符以垂直方式显示输出，以提高可读性。除了在表创建期间手动设置的属性外，输出还显示由于 MySQL 默认设置而设置的任何值。

这些应该能让你很好地了解表的结构，从而正确插入值。

使用 INSERT 向表中添加新记录

SQL INSERT 命令用于向现有表中添加数据行。一旦你了解了表的结构，你就可以构建一个命令，将表的列与你希望为新记录插入的相应值进行匹配。

该命令的基本语法如下

INSERT INTO my_table(column1, column2)
VALUES ('value1', 'value2');

列列表中的列直接对应于值列表中提供的值。

例如，要向上面列出的 employee 表中插入一个新员工，我们可以输入

INSERT INTO employee(first_name, last_name)
VALUES ('Bob', 'Smith');

在这里，我们为 first_name 和 last_name 列提供值，而其他列则由其默认值填充。如果你查询该表，你会看到新记录已添加

SELECT * FROM employee;

+-------------+------------+-----------+---------------------+
  employee_id | first_name | last_name | last_update         |
+-------------+------------+-----------+---------------------+
            1 | Bob        | Smith     | 2021-01-26 09:56:42 |
+-------------+------------+-----------+---------------------+
1 row in set (0.00 sec)

使用 INSERT 一次添加多行

一次一条语句地插入记录比一次插入多行更耗时且效率更低。MySQL 允许你指定要添加到同一表的多个行。每个新行都封装在括号中，每组括号之间用逗号分隔。

多记录插入的基本语法如下

INSERT INTO my_table(column_name, column_name_2)
VALUES
    ('value', 'value2'),
    ('value3', 'value4'),
    ('value5', 'value6');

对于我们引用的 employee 表，你可以通过输入以下内容，在单个语句中添加四个新员工

INSERT INTO employee(first_name, last_name)
VALUES
    ('Abigail', 'Spencer'),
    ('Tamal', 'Wayne'),
    ('Katie', 'Singh'),
    ('Felipe', 'Espinosa');

Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

使用 DELETE 从表中删除行

SQL DELETE 命令用于从表中删除行，它与 INSERT 命令互补。为了从表中删除行，你必须通过在 WHERE 子句中提供匹配条件来标识要删除的行。

基本语法如下

DELETE FROM <table>
WHERE <condition>;

例如，要删除我们 employee 表中所有 first_name 设置为 Abigail 的行，我们可以输入以下内容

DELETE FROM employee
WHERE first_name = 'Abigail';

Query OK, 1 row affected (0.01 sec)

这里的返回值表示 DELETE 命令已处理，并删除了单行。

使用 DELETE 一次删除多行

你可以通过操作 WHERE 子句中指定的选择条件来使用 DELETE 一次删除多个项目。

例如，要按 ID 删除多行，你可以输入如下内容

DELETE FROM employee
WHERE employee_id in (3,4);

Query OK, 2 rows affected (0.00 sec)

你甚至可以省略 WHERE 子句来删除给定表中的所有行

DELETE FROM employee;

Query OK, 2 rows affected (0.00 sec)

但是，请注意，使用 DELETE 清空表中的数据可能不如 TRUNCATE 命令高效，后者可以在不扫描表的情况下删除数据，但有一些注意事项。

结论

在本文中，我们讨论了如何从 MySQL 表中插入和删除数据。首先，我们介绍了如何查找表的结构以帮助构建有效的数据插入查询。然后，我们使用 INSERT 命令逐个或批量插入数据。最后，我们介绍了 DELETE 命令，用于根据查询条件从表中删除记录。

INSERT 和 DELETE 命令虽然相当基础，却是管理表实际维护数据最有用的命令之一。理解它们的基本语法和操作将使你能够快速高效地向数据库结构中添加或删除记录。