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