0
0

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

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

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

文章目录

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 列；② 

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

子句，则更新表中的所有行。

2.2.1、使用 UPDATE 修改数据

在以下实例中，我们使用 Sakila 示例数据库中的 

customer

表进行演示。

在这个例子中，我们将把 

customer_id

等于 

1

的客户的电子邮件修改为 

[email protected]

。

使用以下 

SELECT

语句查看更新前的数据：

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

image-20240226140852408

使用以下 

UPDATE

语句更新 

email

字段的值：

UPDATE customer
SET email = '[email protected]'                # 通过 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 示例数据库中的 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

函数查询一下，表中数据数目：

SELECTCOUNT(*)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 和 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

子句，则会删除表中的所有行。

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

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

SELECTCOUNT(*)FROM actor_copy;

image-20240226143943490

此时， 

actor_copy

表已经空了。

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

TRUNCATE actor_copy;
2.3.4、使用 DELETE 表别名删除

在早期的 MySQL 版本中， 单表删除 

DELETE

语句不支持为表设置别名。比如：

DELETEFROM main_table m
WHERENOTEXISTS(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
WHERENOTEXISTS(SELECT*FROM another_table a
    WHERE a.main_id = m.id
  );

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

DELETEFROM main_table
WHERENOTEXISTS(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 INNERJOIN t2
WHERE t1.id = t2.id;

以下语句删除 

t1

表中满足条件的行：

DELETE t1
FROM t1 INNERJOIN t2
WHERE t1.id = t2.id;

以下语句在删除时使用 

LEFT JOIN

：

DELETE t1
FROM
  t1 LEFTJOIN t2 ON t1.id = t2.id
WHERE t2.id ISNULL;

只要是 SELECT 语句中允许使用的 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

只更新符合条件的行的指定字段的值，未指定的字段保留原值。

REPLACE

则会删掉旧行，再插入新行，

REPLACE

语句中未指定的字段则为默认值或者 

NULL

。

如果想要正常使用 

REPLACE

，当前操作的用户必须对表具有 INSERT 和 DELETE 权限。

标签： 数据库 MySQL MySQL DML 命令
本文转载自: https://blog.csdn.net/weixin_45187434/article/details/136572941
版权归原作者 栗筝i 所有， 如有侵权，请联系我们删除。
登录后发布评论

“【MySQL 系列】MySQL 语句篇_DML 语句”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

机器人技术在安全领域的重要作用

安全学习记录——网络篇（一）

图腾柱PFC：HP1010为您的电动两轮车之旅提供绿色，高效，安全的动力

单元测试数据库回滚问题

这才是单元测试，之前我们都错了

【C语言基础】：字符函数和字符串函数

ChatGPT-Next-Web SSRF漏洞+XSS漏洞复现(CVE-2023-49785)

文章导航