【MySQL 系列】MySQL 语句篇_DML 语句

快来打我* 2024-04-06 15:37 75阅读 0赞

> DML（Data Manipulation Language），即数据操作语言，用于操作数据库对象中所包含的数据。常用关键字包括：插入（INSERT）、更新（UPDATE）、删除（DELETE）。
> 
> DML有助于管理数据库中的数据。通常用于从数据库中提取信息、修改现有数据或添加新数据。DML 与 DQL 语句是开发人员使用最频繁的操作。

--------------------

#### 文章目录 ####

*   *   *   *  1、MySQL 中的 DQL 语句
             *   *  1.1、数据查询语言--DML
                 *  1.2、DML 的格式
             *  2、MySQL 中 的 DML 语句详解
             *   *  2.1、DML语句：INSERT
                 *   *  2.1.1、使用 INSERT 插入数据
                     *  2.1.2、使用 INSERT 修饰符
                     *  2.1.3、使用 INSERT SELECT 语句
                 *  2.2、DML语句：UPDATE
                 *   *  2.2.1、使用 UPDATE 修改数据
                     *  2.2.2、使用 UPDATE 表达式更新
                     *  2.2.3、使用 UPDATE 子查询更新
                     *  2.2.3、使用 UPDATE 修饰符
                 *  2.3、DML语句：DELETE
                 *   *  2.3.1、使用 DELETE 删除数据
                     *  2.3.2、使用 DELETE 删除数据时限制数量
                     *  2.3.3、使用 DELETE 删除表中所有的行
                     *  2.3.4、使用 DELETE 表别名删除
                     *  2.3.5、使用 DELETE 多表删除
                     *  2.3.6、使用 DELETE 修饰符
                 *  2.4、DML语句：REPLACE

--------------------

##### 1、MySQL 中的 DQL 语句 #####

###### 1.1、数据查询语言–DML ######

DML（Data Manipulation Language），即数据操作语言，用于操作数据库对象中所包含的数据。常用关键字包括：插入（INSERT）、更新（UPDATE）、删除（DELETE）。

DML有助于管理数据库中的数据。通常用于从数据库中提取信息、修改现有数据或添加新数据。DML 与 DQL 语句是开发人员使用最频繁的操作。

###### 1.2、DML 的格式 ######

`INSERT` DML 的语法格式：

INSERT [LOW_PRIORITY|DELAYED|HIGH_PRIORITY] [IGNORE]		#
    INTO table_name (column_1, column_2, ...)								#
    VALUES (value_11, value_12, ...) 
    [, (value_21, value_22, ...) , ...];

`UPDATE` DML 的语法格式：

DELETE FROM table_name
    [WHERE clause]
    [ORDER BY ...]
    [LIMIT row_count]

`DELETE` DML 的语法格式：

UPDATE [IGNORE] table_name
    SET
        column_name1 = value1,
        column_name2 = value2,
        ...
    [WHERE clause];

--------------------

##### 2、MySQL 中 的 DML 语句详解 #####

###### 2.1、DML语句：INSERT ######

在 MySQL 中，`INSERT` 语句用于将一行或者多行数据插入到数据表的指定列中。

以下是 `INSERT` 语句的语法：

INSERT INTO table_name (column_1, column_2, ...)				# 插入单行数据
    VALUES (value_1, value_2, ...);
    ---
    INSERT INTO table_name (column_1, column_2, ...)				# 插入多行数据
    VALUES (value_11, value_12, ...),
           (value_21, value_22, ...)
           ...;

语句说明：① `INSERT INTO` 和 `VALUES` 都是关键字；② `INSERT INTO` 后跟表名 `table_name`；③ 表名 `table_name` 后跟要插入数据的列名列表。列名放在小括号中，多个列表使用逗号分；④ `VALUES` 关键字之后的小括号中是值列表。值的数量要和字段的数量相同。值的位置和列的位置一一对应；⑤ 当插入多行数据时，多个值列表之间使用逗号分隔；⑥ `INSERT` 语句返回插入的行数。

###### 2.1.1、使用 INSERT 插入数据 ######

为了演示 `INSERT` 的用法，我们通过以下`CREATE TABLE` 语句创建一个表命名为 `user`。如下：

CREATE TABLE user (
        id INT AUTO_INCREMENT,
        name VARCHAR(255) NOT NULL,
        age INT,
        birthday DATE,
        PRIMARY KEY (id)
    );

以下语句向 `user` 表中插入新行：

INSERT INTO user (name, age)															# 插入单行数据
    VALUES ("Jim", 18);
    ---
    Query OK, 1 row affected (0.00 sec)												# 输出中的 1 row affected 代表已经成功插入了 1 行数据

我们也可以通过以下 SQL 查询 `user` 表的数据，以验证是否成功插入：

SELECT * FROM user;

![image-20240226113522010][]

注意：① `id` 列的值是自动生成的，因为它是 [`AUTO_INCREMENT`][AUTO_INCREMENT] 列；② `birthday` 列值为 `NULL`，因为我们只插入了 `name` 和 `age` 列。

插入多行数据：

INSERT INTO user (name, age)															# 插入多行数据
    VALUES ("Tim", 19), ("Lucy", 16);
    ---
    Query OK, 2 rows affected (0.00 sec)											# 输出中的 2 row affected 代表已经成功插入了 2 行数据
    Records: 2  Duplicates: 0  Warnings: 0										# Records: 2 代表有 2 行数据要插入到表中
    																													# Duplicates: 0 代表重复的行数是 0
    																													# Warnings: 0 代表需要注意的行数是 0

我们也可以通过以下 SQL 查询 `user` 表的数据，以验证是否成功插入：

SELECT * FROM user;

![image-20240226114139193][]

如果要插入日期类型的字段，可以使用 `YYYY-MM-DD` 格式的文本字符串：

INSERT INTO user(name, age, birthday)
    VALUES('Jack', 20, '2000-02-05');
    ---
    Query OK, 1 row affected (0.00 sec)

我们可以通过以下 SQL 查询 `user` 表的数据，以验证是否成功插入：

SELECT * FROM user;

![image-20240226121032350][]

###### 2.1.2、使用 INSERT 修饰符 ######

在 MySQL 中， `INSERT` 语句支持 4 个修饰符:

1.  `LOW_PRIORITY`: 如果你指定了 `LOW_PRIORITY` 修饰符，MySQL 服务器将延迟执行 `INSERT` 操作直到没有客户端对表进行读操作。`LOW_PRIORITY` 修饰符影响那些只支持表级锁的存储引擎，比如： `MyISAM`, `MEMORY`, 和 `MERGE`；
2.  `HIGH_PRIORITY`: 如果你指定了 `HIGH_PRIORITY` 修饰符，它会覆盖掉服务器启动时的 `--low-priority-updates` 选项。与`LOW_PRIORITY` 一样 `HIGH_PRIORITY` 修饰符影响那些只支持表级锁的存储引擎，比如： `MyISAM`, `MEMORY`, 和 `MERGE`；
3.  `IGNORE`: 如果你指定了 `IGNORE` 修饰符，MySQL 服务器会在执行 `INSERT` 操作期间忽略那些可忽略的错误（可以忽略插入重复的数据）。这些错误最终会作为 `WARNING` 返回；
4.  `DELAYED`: 这个修饰符已经在 MySQL 5.6 版本中弃用，将来会被删除。在 MySQL 8.0 中，这个修饰符可用但会被忽略。

修饰符的用法如下：

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    INTO table_name
    ...

###### 2.1.3、使用 INSERT SELECT 语句 ######

`INSERT` 也可以将一条 `SELECT` 语句的结果插入到表中。

INSERT INTO table_name [(column1, column2,...)]  
    SELECT column1, column2, ... 
    FROM source_table [WHERE condition];

语句说明：① 增加表中列的数目要与查询表中列的数目一致，列的类型也要相同；② 查询的表可以通过 `WHERE` 子句进行过滤；③ `SELECT` 的第一个列，对应着增加的第一个列，以此类推。

举例：

INSERT INTO `user` ( `name`, age ) 
    SELECT `user`.`name`, `user`.age
    FROM `user` 
    WHERE age > 18
    ---
    Query OK, 2 rows affected (0.00 sec)

我们可以通过以下 SQL 查询 `user` 表的数据，以验证是否成功插入：

SELECT * FROM user;

![image-20240226121859047][]

###### 2.2、DML语句：UPDATE ######

`UPDATE` 语句可以更新表中的一行或者多行数据，可以更新表中的一个或者多个字段（列）。

以下是 `UPDATE` 语句的基本语法：

UPDATE [IGNORE] table_name
    SET
        column_name1 = value1,
        column_name2 = value2,
        ...
    [WHERE clause];

语句说明：① `UPDATE` 关键字后指定要更新数据的表名；② 使用 `SET` 子句设置字段的新值。多个字段使用逗号分隔。字段的值可以是普通的字面值，也可以是表达式运算，还可以是子查询；③ 使用 [`WHERE`][WHERE] 子句指定要更新的行。只有符合 `WHERE` 条件的行才会被更新；④ `WHERE` 子句是可选的。如果不指定 `WHERE` 子句，则更新表中的所有行。

###### 2.2.1、使用 UPDATE 修改数据 ######

在以下实例中，我们使用 Sakila 示例数据库中的 `customer` 表进行演示。

在这个例子中，我们将把 `customer_id` 等于 `1` 的客户的电子邮件修改为 `NEW.MARY.SMITH@sakilacustomer.org`。

使用以下 `SELECT` 语句查看更新前的数据：

SELECT first_name, last_name, email
    FROM customer
    WHERE customer_id = 1;

![image-20240226140852408][]

使用以下 `UPDATE` 语句更新 `email` 字段的值：

UPDATE customer
    SET email = 'NEW.MARY.SMITH@sakilacustomer.org'				# 通过 SET 子句将列的值设置为新电子邮件
    WHERE customer_id = 1;																# 通过 WHERE 子句指定更新的条件为 customer_id = 1
    ---
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

使用以下 `SELECT` 语句查看更新后的数据，以验证是否更新成功：

SELECT first_name, last_name, email
    FROM customer
    WHERE customer_id = 1;

![image-20240226141215465][]

###### 2.2.2、使用 UPDATE 表达式更新 ######

使用 `UPDATE` 更新时，字段的值可以设置为表达式的运算结果，比如函数或其他的运算。

下面的 `UPDATE` 更新所有客户的电子邮件的域名部分：

UPDATE customer
    SET email = REPLACE(email, 'sakilacustomer.org', 'sjkjc.com');	# 字符串替换操作
    ---
    Query OK, 599 rows affected (0.03 sec)
    Rows matched: 599  Changed: 599  Warnings: 0

注意，本例中没有使用 `WHERE` 子句，所以表中所有的数据都进行了更新。

###### 2.2.3、使用 UPDATE 子查询更新 ######

下面实例展示了如何为没有绑定商店的客户绑定一个随机商店。

UPDATE customer
    SET store_id = (
        SELECT store_id
        FROM store
        ORDER BY RAND()
        LIMIT 1
      )
    WHERE store_id IS NULL;

在本例中，我们通过以下 `SELECT` 语句返回一个随机的商店 id：

SELECT store_id
    FROM store
    ORDER BY RAND()
    LIMIT 1

在 `SET` 子句中，将 `store_id` 的值设置为上面的子查询。

###### 2.2.3、使用 UPDATE 修饰符 ######

在 MySQL 中， `UPDATE` 语句支持 2 个修饰符:

1.  `LOW_PRIORITY`: 如果你指定了 `LOW_PRIORITY` 修饰符，MySQL 服务器将延迟执行 `UPDATE` 操作直到没有客户端对表进行读操作。`LOW_PRIORITY` 修饰符影响那些只支持表级锁的存储引擎，比如： `MyISAM`, `MEMORY`, 和 `MERGE`；
2.  `IGNORE`: 如果你指定了 `IGNORE` 修饰符，MySQL 服务器会在执行 `UPDATE` 操作期间忽略那些可忽略的错误。这些错误最终会作为 `WARNING` 返回。

修饰符的用法如下：

UPDATE [LOW_PRIORITY] [IGNORE] table_name SET column_name = value

###### 2.3、DML语句：DELETE ######

在 MySQL 中，`DELETE` 语句用于从表中删除满足条件的记录行。

`DELETE` 语句单表删除语法如下：

DELETE FROM table_name
    [WHERE clause]
    [ORDER BY ...]
    [LIMIT row_count]

说明：① `DELETE FROM` 后跟的是要从中删除数据的表 ② `WHERE` 子句用来过滤需要删除的行。满足条件的行会被删除；③ `WHERE` 子句是可选的。没有 `WHERE` 子句时，`DELETE` 语句将删除表中的所有行；④ `ORDER BY` 子句用来指定删除行的顺序。它是可选的；⑤ `LIMIT` 子句用来指定删除的最大行数。它是可选的；⑥ DELETE\` 语句返回删除的行数。

###### 2.3.1、使用 DELETE 删除数据 ######

在以下实例中，我们使用 [Sakila 示例数据库][Sakila]中的 [`actor` 表][actor_] 进行演示。

请注意，一旦使用 `DELETE` 语句删除了数据，数据就会消失。请谨慎操作。

为了防止数据丢失，我们通过以下 SQL 创建一个表 `actor_copy` 做为 `actor` 表的拷贝。如下：

CREATE TABLE actor_copy AS (SELECT * FROM actor);
    ---
    Query OK, 201 rows affected (0.01 sec)
    Records: 201  Duplicates: 0  Warnings: 0

下面开始我们的实例，开始之前我们先通过 `COUNT` 函数查询一下，表中数据数目：

SELECT COUNT(*) FROM actor_copy;

![image-20240226143515222][]

删除 `actor_id` 等于 `1` 的行：

DELETE FROM actor_copy WHERE actor_id = 1;
    ---
    Query OK, 1 row affected (0.00 sec)

删除 `last_name` 等于 `KILMER` 的行：

DELETE FROM actor_copy WHERE last_name = 'KILMER';
    ---
    Query OK, 5 rows affected (0.01 sec)

再次确认表中数据数目：

SELECT COUNT(*) FROM actor_copy;

![image-20240226143638142][]

###### 2.3.2、使用 DELETE 删除数据时限制数量 ######

考虑这些需求：① 删除排名最靠后的 5 个成绩；② 删除最新注册的 10 名用户。

这时，我们可以结合使用 [`ORDER BY`][ORDER BY] 和 [`LIMIT`][LIMIT] 子句。

以下语句用来删除 `actor_copy` 中 `actor_id` 最大的 10 行：

DELETE FROM actor_copy
    ORDER BY actor_id DESC
    LIMIT 10;
    ---
    Query OK, 10 rows affected (0.01 sec)

如果单独使用 `LIMIT` 子句，删除的顺序是不明确的。大多数情况下， `DELETE` 语句中的 `LIMIT` 子句都应该和 `ORDER BY` 子句一起使用。

###### 2.3.3、使用 DELETE 删除表中所有的行 ######

如果我们不在 `DELETE` 语句中使用 `WHERE` 或者 `LIMIT` 子句，则会删除表中的所有行。

DELETE FROM actor_copy;
    ---
    Query OK, 194 rows affected (0.00 sec)

我们通过以下语句检查表中是否还有数据行：

SELECT COUNT(*) FROM actor_copy;

![image-20240226143943490][]

此时， `actor_copy` 表已经空了。

如果你只是想清空表，可以使用 [`TRUNCATE TABLE`][TRUNCATE TABLE] 语句以获得更好的性能。如下：

TRUNCATE actor_copy;

###### 2.3.4、使用 DELETE 表别名删除 ######

在早期的 MySQL 版本中， 单表删除 `DELETE` 语句不支持为表设置别名。比如：

DELETE FROM main_table m
    WHERE NOT EXISTS (
        SELECT *
        FROM another_table a
        WHERE a.main_id = m.id
      );

将会产生错误：`Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't' at line 1 0.016 sec`。

可以改成如下语句：

DELETE m FROM main_table m
    WHERE NOT EXISTS (
        SELECT *
        FROM another_table a
        WHERE a.main_id = m.id
      );

或者不使用别名，而是使用表名：

DELETE FROM main_table
    WHERE NOT EXISTS (
        SELECT *
        FROM another_table a
        WHERE a.main_id = main_table.id
      );

###### 2.3.5、使用 DELETE 多表删除 ######

我们也可以在一个 `DELETE` 语句中指定多个表，以便在一个或多个表中删除符合 `WHERE` 子句中的条件的行。

以下语句删除 `t1` 和 `t2` 表中满足条件的行：

DELETE t1, t2
    FROM t1 INNER JOIN t2
    WHERE t1.id = t2.id;

以下语句删除 `t1` 表中满足条件的行：

DELETE t1
    FROM t1 INNER JOIN t2
    WHERE t1.id = t2.id;

以下语句在删除时使用 `LEFT JOIN`：

DELETE t1
    FROM
      t1 LEFT JOIN t2 ON t1.id = t2.id
    WHERE t2.id IS NULL;

只要是 [`SELECT`][SELECT] 语句中允许使用的 [`JOIN`][JOIN] 类型，多表删除语句都可以使用。

多表删除语句中不能使用 `LIMIT` 子句和 `ORDER BY` 子句。

###### 2.3.6、使用 DELETE 修饰符 ######

在 MySQL 中， `DELETE` 语句支持 3 个修饰符:

1.  `LOW_PRIORITY`: 如果你指定了 `LOW_PRIORITY` 修饰符，MySQL 服务器将延迟执行 `DELETE` 操作直到没有客户端对表进行读操作。这个修饰符影响那些只支持表级锁的存储引擎，比如： `MyISAM`, `MEMORY`, 和 `MERGE`；
2.  `QUICK`: 如果你指定了 `QUICK` 修饰符，`MyISAM` 存储引擎不会在 `DELETE` 操作期间合并索引。这在某种程度上会加快 `DELETE` 操作；
3.  `IGNORE`: 如果你指定了 `IGNORE` 修饰符，MySQL 服务器会在执行 `DELETE` 操作期间忽略那些可忽略的错误。这些错误最终会作为 `WARNING` 返回。

修饰符的用法如下：

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM table_name

###### 2.4、DML语句：REPLACE ######

在 MySQL 中，如果你想向表中插入数据，除了使用 `INSERT` 语句，还可以使用 `REPLACE` 语句。

`REPLACE` 语句和 `INSERT` 语句很像，它们的不同之处在于，当插入过程中出现了重复的主键或者重复的唯一索引的时候，`INSERT` 语句会产生一个错误，而 `REPLACE` 语句则先删除旧的行，再插入新的行。

`REPLACE` 语句不在标准 SQL 的范畴。

我们可以使用一个 `REPLACE` 语句插入一行或多行数据。 `REPLACE` 语句的语法如下：

REPLACE [INTO] table_name (column_1, column_2, ...)
    VALUES (value_11, value_12, ...),
           (value_21, value_22, ...)
           ...;

说明：① `REPLACE INTO` 和 `VALUES` 都是关键字。`INTO` 可省略；② `REPLACE INTO` 后跟表名 `table_name`；③ 表名 `table_name` 后跟要插入数据的列名列表。列名放在小括号中，多个列表使用逗号分隔；④ `VALUES` 关键字之后的小括号中是值列表。值的数量要和字段的数量相同。值的位置和列的位置一一对应； ⑤ 当插入多行数据时，多个值列表之间使用逗号分隔。

`REPLACE` 语句与 `INSERT` 语句类似。

`REPLACE` 语句还可以使用 `SET` 关键词，这只适用于操作单行。语法如下：

REPLACE [INTO] table_name
    SET column1 = value1,
        column2 = value2,
        ...;

这种用法与 [`UPDATE`][UPDATE] 语句的相似，但也是不同的。 `UPDATE` 只更新符合条件的行的指定字段的值，未指定的字段保留原值。`REPLACE` 则会删掉旧行，再插入新行，`REPLACE` 语句中未指定的字段则为默认值或者 `NULL`。

如果想要正常使用 `REPLACE`，当前操作的用户必须对表具有 [`INSERT`][INSERT] 和 [`DELETE`][DELETE] 权限。

[image-20240226113522010]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/207514910ecb4f0aa3bbf45373e69f5c.png
[AUTO_INCREMENT]: https://www.sjkjc.com/mysql/auto-increment/
[image-20240226114139193]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/54e5a19ebd974ed69bfbc8357706e0e0.png
[image-20240226121032350]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/82057215527b47f8baf27230fa48332a.png
[image-20240226121859047]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/d29e2e081dc44917b53523667468d232.png
[WHERE]: https://www.sjkjc.com/mysql/where/
[image-20240226140852408]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/459da8d0e09b47eaa96f7e0496c2d4d3.png
[image-20240226141215465]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/98af8eacb8a54b80b3d0a94dd780f9cf.png
[Sakila]: https://www.sjkjc.com/mysql/sample-database/
[actor_]: https://www.sjkjc.com/sakila/table-actor/
[image-20240226143515222]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/01308eb6c382423a9ee6c374b84b651b.png
[image-20240226143638142]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/ae2c2fc197f04b2897fc9ca60f130bd8.png
[ORDER BY]: https://www.sjkjc.com/mysql/order-by/
[LIMIT]: https://www.sjkjc.com/mysql/limit/
[image-20240226143943490]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/06/e7d39c2a523040aeb0abdabdec35942e.png
[TRUNCATE TABLE]: https://www.sjkjc.com/mysql/table-truncate/
[SELECT]: https://www.sjkjc.com/mysql/select/
[JOIN]: https://www.sjkjc.com/mysql/join/
[UPDATE]: https://www.sjkjc.com/mysql/update/
[INSERT]: https://www.sjkjc.com/mysql/insert/
[DELETE]: https://www.sjkjc.com/mysql/delete/