如何在 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 命令相当基础，但它们是一些最有用的命令，用于管理表实际维护的数据。了解它们的基本语法和操作将使您能够快速有效地从数据库结构中添加或删除记录。