MySQL DML 语句的全面剖析与实战应用

一、DML 语句概述

DML（Data Manipulation Language，数据操纵语言）是 SQL（Structured Query Language，结构化查询语言）中用于操作数据库中数据的部分。它主要包括插入（INSERT）、更新（UPDATE）、删除（DELETE）和查询（SELECT）数据的语句。通过这些语句，用户可以对数据库中的数据进行各种操作，以满足不同的业务需求。

在 MySQL 数据库中，DML 语句是与数据交互的核心工具。无论是开发应用程序、进行数据分析还是进行日常的数据库维护，都离不开对 DML 语句的熟练运用。与 DDL（Data Definition Language，数据定义语言）主要负责数据库结构的定义和修改不同，DML 语句直接作用于数据本身，操作的粒度更细，对数据的实时性要求也更高。

二、INSERT 语句

（一）基本语法

INSERT 语句用于向数据库表中插入新的数据行。其基本语法如下：

INSERT INTO 表名 (列1, 列2, ..., 列n)
VALUES (值1, 值2, ..., 值n);

其中，“表名”是要插入数据的表，“列1, 列2, ..., 列n”是表中要插入数据的列名，而“值1, 值2, ..., 值n”则是对应列的值。

例如，假设有一个名为“students”的表，包含“id”（学生编号）、“name”（学生姓名）和“age”（学生年龄）三个字段，要插入一条数据，学生编号为 1，姓名为“张三”，年龄为 20 岁，可以使用以下语句：

INSERT INTO students (id, name, age)
VALUES (1, '张三', 20);

（二）插入多行数据

如果需要一次性插入多行数据，可以在 VALUES 后面添加多组括号和值，每组值之间用逗号隔开。例如：

INSERT INTO students (id, name, age)
VALUES (2, '李四', 22),
       (3, '王五', 21);

这样就可以同时插入两条数据，学生编号分别为 2 和 3，姓名分别为“李四”和“王五”，年龄分别为 22 岁和 21 岁。

（三）省略列名

如果要插入的数据包含表中所有列的值，并且值的顺序与表中列的定义顺序一致，那么可以省略列名，直接写 VALUES。例如：

INSERT INTO students
VALUES (4, '赵六', 23);

这条语句的效果与前面提到的插入单行数据的语句类似，但省略了列名。需要注意的是，如果表中有默认值的列或者允许为空的列，也可以在省略列名的情况下插入数据，MySQL 会自动为这些列赋予默认值或 NULL 值。

（四）插入查询结果

INSERT 语句还可以将查询的结果直接插入到另一个表中。这种用法在数据迁移或数据备份等场景中非常有用。其语法如下：

INSERT INTO 目标表名 (列1, 列2, ..., 列n)
SELECT 列1, 列2, ..., 列n
FROM 源表名
WHERE 条件;

例如，假设有一个名为“students_backup”的表，其结构与“students”表相同，现在要把“students”表中年龄大于 20 的学生数据插入到“students_backup”表中，可以使用以下语句：

INSERT INTO students_backup (id, name, age)
SELECT id, name, age
FROM students
WHERE age > 20;

这样，“students”表中符合条件的数据就会被复制到“students_backup”表中。

（五）注意事项

插入的数据类型必须与表中列的数据类型一致。如果数据类型不匹配，MySQL 会报错。
如果表中有主键或唯一约束的列，插入的数据不能违反这些约束。否则，MySQL 会拒绝插入操作并报错。
插入的数据长度不能超过列的最大长度限制。例如，如果某个字段是 VARCHAR(10) 类型，那么插入的字符串长度不能超过 10 个字符。
在插入数据时，如果表中有自增字段（AUTO_INCREMENT），MySQL 会自动为该字段生成值，用户无需手动指定。但如果用户指定了自增字段的值，那么必须确保该值是唯一的，并且大于表中已有的最大值。

三、UPDATE 语句

（一）基本语法

UPDATE 语句用于修改数据库表中已存在的数据行。其基本语法如下：

UPDATE 表名
SET 列1 = 值1, 列2 = 值2, ..., 列n = 值n
WHERE 条件;

其中，“表名”是要修改数据的表，“列1 = 值1, 列2 = 值2, ..., 列n = 值n”表示要修改的列及其对应的值，而“WHERE 条件”用于指定需要修改的行。如果没有指定 WHERE 条件，那么所有的行都会被修改，这通常是不希望出现的情况，因为可能会导致数据丢失或混乱。

例如，假设要将“students”表中学生编号为 1 的学生的姓名改为“张三丰”，年龄改为 25 岁，可以使用以下语句：

UPDATE students
SET name = '张三丰', age = 25
WHERE id = 1;

（二）更新多列

在一条 UPDATE 语句中可以同时更新多个列的值，只需要用逗号将要更新的列及其值分隔开即可。例如：

UPDATE students
SET name = '李四', age = 23
WHERE id = 2;

这条语句会将学生编号为 2 的学生的姓名改为“李四”，年龄改为 23 岁。

（三）更新条件的多样性

WHERE 子句中的条件可以是各种表达式，包括比较运算符（=、<>、>、<、>=、<=）、逻辑运算符（AND、OR、NOT）等。例如：

UPDATE students
SET age = age + 1
WHERE age >= 20 AND age <= 25;

这条语句会将“students”表中年龄在 20 到 25 岁之间的学生的年龄都增加 1 岁。

（四）注意事项

在执行 UPDATE 语句之前，最好先使用 SELECT 语句查看需要修改的行，以确保修改的范围是正确的。否则，可能会不小心修改了不该修改的数据。
如果要更新的列是外键列，那么需要确保更新后的值在关联的表中存在，否则可能会违反外键约束。
在更新数据时，如果涉及到计算或函数，MySQL 会先计算出结果，然后再将结果赋值给对应的列。例如，SET age = age + 1 中，MySQL 会先计算 age + 1 的值，然后再将结果赋值给 age 列。
如果要更新的表非常大，那么执行 UPDATE 语句可能会花费较长的时间。在这种情况下，可以考虑使用批量更新的方式，或者对表进行索引优化，以提高更新效率。

四、DELETE 语句

（一）基本语法

DELETE 语句用于从数据库表中删除数据行。其基本语法如下：

DELETE FROM 表名
WHERE 条件;

其中，“表名”是要删除数据的表，“WHERE 条件”用于指定需要删除的行。与 UPDATE 语句类似，如果没有指定 WHERE 条件，那么所有的行都会被删除，这通常是不希望出现的情况，因为可能会导致数据丢失。

例如，假设要删除“students”表中学生编号为 3 的学生，可以使用以下语句：

DELETE FROM students
WHERE id = 3;

（二）删除多行数据

可以通过指定合适的 WHERE 条件来删除多行数据。例如：

DELETE FROM students
WHERE age > 25;

这条语句会删除“students”表中年龄大于 25 岁的所有学生。

（三）注意事项

在执行 DELETE 语句之前，最好先使用 SELECT 语句查看需要删除的行，以确保删除的范围是正确的。否则，可能会不小心删除了不该删除的数据。
如果要删除的表中有外键约束，那么需要确保删除操作不会违反外键约束。否则，MySQL 会报错。
在删除数据时，如果涉及到级联删除（CASCADE），那么 MySQL 会自动删除关联表中的相关数据。但需要注意的是，级联删除可能会导致大量数据的丢失，因此在使用级联删除时需要格外小心。
如果要删除的表非常大，那么执行 DELETE 语句可能会花费较长的时间。在这种情况下，可以考虑使用批量删除的方式，或者对表进行索引优化，以提高删除效率。

五、SELECT 语句

（一）基本语法

SELECT 语句用于从数据库表中查询数据。其基本语法如下：

SELECT 列1, 列2, ..., 列n
FROM 表名
WHERE 条件;

其中，“列1, 列2, ..., 列n”是要查询的列名，“表名”是要查询的表，“WHERE 条件”用于指定查询的条件。如果省略 WHERE 子句，那么会查询表中的所有行。

例如，假设要查询“students”表中所有学生的姓名和年龄，可以使用以下语句：

SELECT name, age
FROM students;

（二）查询所有列

如果要查询表中的所有列，可以使用星号（*）代替列名。例如：

SELECT *
FROM students;

这条语句会查询“students”表中的所有列和所有行。

（三）查询条件的多样性

WHERE 子句中的条件可以是各种表达式，包括比较运算符、逻辑运算符、IN、BETWEEN、LIKE 等。例如：

SELECT name, age
FROM students
WHERE age BETWEEN 20 AND 25;

这条语句会查询“students”表中年龄在 20 到 25 岁之间的学生的姓名和年龄。

（四）排序

可以使用 ORDER BY 子句对查询结果进行排序。例如：

SELECT name, age
FROM students
ORDER BY age ASC;

这条语句会查询“students”表中所有学生的姓名和年龄，并按照年龄升序排列。如果要按照降序排列，可以将 ASC 改为 DESC。

（五）分组

可以使用 GROUP BY 子句对查询结果进行分组。例如：

SELECT age, COUNT(*)
FROM students
GROUP BY age;

这条语句会查询“students”表中每个年龄段的学生人数。GROUP BY 子句通常与聚合函数（如 COUNT、SUM、AVG 等）一起使用，以对每个分组进行统计。

（六）连接查询

可以使用 JOIN 子句将多个表连接起来进行查询。例如，假设有一个名为“courses”的表，包含“course_id”（课程编号）和“course_name”（课程名称）两个字段，还有一个名为“student_courses”的表，包含“student_id”（学生编号）和“course_id”（课程编号）两个字段，现在要查询每个学生选修的课程名称，可以使用以下语句：

SELECT students.name, courses.course_name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.course_id;

这条语句通过 JOIN 子句将“students”表、“student_courses”表和“courses”表连接起来，查询每个学生选修的课程名称。

（七）子查询

子查询是指在一个查询语句中嵌套另一个查询语句。子查询可以出现在 SELECT、FROM、WHERE 等子句中。例如：

SELECT name, age
FROM students
WHERE age = (SELECT MAX(age) FROM students);

这条语句会查询“students”表中年龄最大的学生的姓名和年龄。子查询 (SELECT MAX(age) FROM students) 用于获取“students”表中最大的年龄，然后将其作为 WHERE 子句中的条件。

（八）注意事项

在查询数据时，尽量避免使用 SELECT *，因为这样会查询表中的所有列，可能会导致查询效率低下。如果只需要查询部分列，应该明确指定列名。
如果查询的表非常大，那么查询可能会花费较长的时间。在这种情况下，可以考虑对表进行索引优化，以提高查询效率。
在使用 JOIN 子句进行连接查询时，需要注意连接条件的正确性。如果连接条件不正确，可能会导致查询结果不准确。
在使用子查询时，需要注意子查询的返回值类型。如果子查询返回多个值，那么在使用子查询的地方需要能够接受多个值，否则 MySQL 会报错。

六、DML 语句的事务处理

在 MySQL 中，DML 语句的操作通常是自动提交的，也就是说，每执行一条 DML 语句，就会立即生效。但是，在某些情况下，可能需要将多个 DML 语句作为一个事务来执行，要么全部成功，要么全部失败。这时，可以使用事务控制语句来实现。

（一）事务的开启和提交

可以使用以下语句开启一个事务：

START TRANSACTION;

或者

BEGIN;

在事务中执行了多个 DML 语句后，如果所有操作都成功，可以使用以下语句提交事务：

COMMIT;

提交事务后，事务中的所有操作都会被永久生效。

（二）事务的回滚

如果在事务中某个操作失败了，可以使用以下语句回滚事务：

ROLLBACK;

回滚事务后，事务中的所有操作都会被撤销，数据库状态恢复到事务开始之前的状态。

（三）事务的隔离级别

MySQL 支持多种事务隔离级别，包括 READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ（默认隔离级别）和 SERIALIZABLE。不同的隔离级别对并发操作的处理方式不同，可能会导致不同的现象，如脏读、不可重复读和幻读。

READ UNCOMMITTED：最低的隔离级别，允许读取未提交的数据，可能会导致脏读。
READ COMMITTED：允许读取已提交的数据，但可能会导致不可重复读。
REPEATABLE READ：默认隔离级别，保证在同一个事务中多次读取同一数据的结果是一致的，但可能会导致幻读。
SERIALIZABLE：最高的隔离级别，保证事务的串行执行，避免了所有并发问题，但性能开销最大。

可以通过以下语句设置事务的隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL 隔离级别;

例如：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

（四）注意事项

事务只对 DML 语句有效，对 DDL 语句和 DCL（Data Control Language，数据控制语言）语句无效。
在使用事务时，需要注意事务的大小和执行时间。如果事务过大或执行时间过长，可能会导致数据库性能下降，甚至出现死锁。
在高并发的场景下，需要根据实际需求选择合适的事务隔离级别，以平衡并发性能和数据一致性。
在事务中，如果出现了错误，MySQL 会自动回滚事务。但如果需要手动回滚事务，可以使用 ROLLBACK 语句。

七、DML 语句的性能优化

在实际应用中，DML 语句的性能优化是非常重要的。以下是一些常见的优化方法：

（一）索引优化

索引是提高查询效率的关键手段。通过在表中创建合适的索引，可以加快查询速度，减少扫描的数据量。例如，如果经常根据某个列进行查询，那么可以在该列上创建索引。但是，索引也会带来一些开销，如插入、更新和删除数据时需要维护索引，因此需要根据实际情况合理创建索引。

（二）批量操作

对于插入、更新和删除操作，如果需要对多行数据进行操作，尽量使用批量操作的方式，而不是逐条操作。批量操作可以减少网络交互次数和数据库的开销，提高操作效率。例如，使用 INSERT 语句一次性插入多行数据，或者使用 UPDATE 语句一次性更新多行数据。

（三）避免全表扫描

全表扫描是指查询时扫描整个表的数据，这在表数据量较大时会导致性能问题。为了避免全表扫描，可以在查询语句中尽量使用索引，或者通过优化查询条件来减少扫描的数据量。例如，使用 WHERE 子句中的条件来过滤数据，或者使用 LIMIT 子句来限制查询结果的数量。

（四）合理使用 JOIN

在连接查询时，需要注意连接条件的正确性和效率。如果连接的表非常多，可能会导致查询效率低下。在这种情况下，可以考虑优化连接条件，或者将多个表合并为一个表，以减少连接的复杂度。

（五）使用临时表

在某些复杂的查询或操作中，可以使用临时表来存储中间结果，以提高操作效率。临时表只在当前会话中有效，会话结束后自动删除。使用临时表可以避免重复计算，减少查询的复杂度。

（六）避免使用 SELECT *

如前面提到的，尽量避免使用 SELECT *，因为这样会查询表中的所有列，可能会导致查询效率低下。如果只需要查询部分列，应该明确指定列名。

（七）使用合适的存储引擎

MySQL 支持多种存储引擎，如 InnoDB、MyISAM 等。不同的存储引擎在性能、功能和适用场景上有所不同。例如，InnoDB 支持事务、行级锁和外键，适合处理高并发的事务性应用；而 MyISAM 不支持事务，但查询速度较快，适合读多写少的应用。根据实际需求选择合适的存储引擎可以提高数据库的性能。

（八）数据库参数优化

MySQL 提供了许多参数用于控制数据库的性能，如内存大小、缓存大小、连接数等。通过合理配置这些参数，可以提高数据库的性能。但是，参数的优化需要根据实际的硬件环境和应用需求进行调整，不能一概而论。

八、DML 语句的权限管理

在 MySQL 中，对 DML 语句的使用需要相应的权限。通过授权语句可以授予用户对表的 DML 操作权限，也可以通过撤销权限语句收回用户的权限。

（一）授权

可以使用以下语句授予用户对表的 DML 操作权限：

GRANT 权限1, 权限2, ..., 权限n
ON 数据库名.表名
TO '用户名'@'主机名';

例如，授予用户“user1”对“students”表的 SELECT、INSERT、UPDATE 和 DELETE 权限，可以使用以下语句：

GRANT SELECT, INSERT, UPDATE, DELETE
ON students
TO 'user1'@'localhost';

（二）撤销权限

可以使用以下语句撤销用户的权限：

REVOKE 权限1, 权限2, ..., 权限n
ON 数据库名.表名
FROM '用户名'@'主机名';

例如，撤销用户“user1”对“students”表的 UPDATE 权限，可以使用以下语句：

REVOKE UPDATE
ON students
FROM 'user1'@'localhost';

（九）查看权限

可以使用以下语句查看用户的权限：

SHOW GRANTS FOR '用户名'@'主机名';

例如，查看用户“user1”在“localhost”上的权限，可以使用以下语句：

SHOW GRANTS FOR 'user1'@'localhost';

（十）注意事项

权限管理是非常重要的，需要根据用户的实际需求授予合适的权限，避免过度授权导致安全问题。
在授权和撤销权限时，需要注意权限的范围和对象。例如，权限可以授予到整个数据库、某个表或某个列。
在 MySQL 中，权限的生效需要刷新权限。可以通过执行以下语句刷新权限：

FLUSH PRIVILEGES;

权限管理不仅适用于 DML 语句，也适用于 DDL 语句和 DCL 语句。

九、DML 语句的案例分析

为了更好地理解 DML 语句的使用，以下通过一个实际案例来展示 DML 语句在业务场景中的应用。

（一）案例背景

假设有一个在线教育平台，平台中有学生、课程和学生选课等信息。需要通过 DML 语句对这些数据进行操作，以满足业务需求。

（二）数据库表结构

students 表
- id（学生编号，主键）
- name（学生姓名）
- age（学生年龄）
- gender（学生性别）
- email（学生邮箱）
courses 表
- course_id（课程编号，主键）
- course_name（课程名称）
- teacher（授课教师）
- credit（学分）
student_courses 表
- student_id（学生编号，外键）
- course_id（课程编号，外键）
- score（成绩）

（三）业务需求及 DML 语句实现

插入数据

插入一个新学生

INSERT INTO students (id, name, age, gender, email)
VALUES (1, '张三', 20, '男', 'zhangsan@example.com');

插入一门新课程

INSERT INTO courses (course_id, course_name, teacher, credit)
VALUES (1, '数据库原理', '王老师', 3);

插入一个学生选课记录

INSERT INTO student_courses (student_id, course_id, score)
VALUES (1, 1, 85);

更新数据

更新学生信息

UPDATE students
SET name = '张三丰', age = 25
WHERE id = 1;

更新课程信息

UPDATE courses
SET teacher = '李老师'
WHERE course_id = 1;

更新学生成绩

UPDATE student_courses
SET score = 90
WHERE student_id = 1 AND course_id = 1;

删除数据

删除一个学生
```
DELETE FROM students
WHERE id = 1;
```

删除一门课程

DELETE FROM courses
WHERE course_id = 1;

删除一个学生选课记录

DELETE FROM student_courses
WHERE student_id = 1 AND course_id = 1;

查询数据

查询所有学生信息
```
SELECT * FROM students;
```
查询所有课程信息
```
SELECT * FROM courses;
```

查询学生选课信息

SELECT students.name, courses.course_name, student_courses.score
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.course_id;

查询年龄大于 20 岁的学生
```
SELECT * FROM students
WHERE age > 20;
```

查询选修了“数据库原理”课程的学生

SELECT students.name
FROM students
JOIN student_courses ON students.id = student_courses.student_id
JOIN courses ON student_courses.course_id = courses.course_id
WHERE courses.course_name = '数据库原理';

事务处理

插入一个学生和选课记录作为一个事务

START TRANSACTION;
INSERT INTO students (id, name, age, gender, email)
VALUES (2, '李四', 22, '女', 'lisi@example.com');
INSERT INTO student_courses (student_id, course_id, score)
VALUES (2, 1, 80);
COMMIT;

性能优化

在学生表的“name”列上创建索引

CREATE INDEX idx_name ON students (name);

使用批量插入

INSERT INTO students (id, name, age, gender, email)
VALUES (3, '王五', 21, '男', 'wangwu@example.com'),
       (4, '赵六', 23, '女', 'zhaoliu@example.com');

权限管理
- 授权用户“teacher”对“courses”表的 SELECT 和 UPDATE 权限
```
GRANT SELECT, UPDATE
ON courses
TO 'teacher'@'localhost';
FLUSH PRIVILEGES;
```

posted @ 2025-04-08 16:34 软件职业规划阅读(74) 评论(0) 收藏举报

刷新页面返回顶部

MySQL DML 语句的全面剖析与实战应用

一、DML 语句概述

二、INSERT 语句

（一）基本语法

（二）插入多行数据

（三）省略列名

（四）插入查询结果

（五）注意事项

三、UPDATE 语句

（一）基本语法

（二）更新多列

（三）更新条件的多样性

（四）注意事项

四、DELETE 语句

（一）基本语法

（二）删除多行数据

（三）注意事项

五、SELECT 语句

（一）基本语法

（二）查询所有列

（三）查询条件的多样性

（四）排序

（五）分组

（六）连接查询

（七）子查询

（八）注意事项

六、DML 语句的事务处理

（一）事务的开启和提交

（二）事务的回滚

（三）事务的隔离级别

（四）注意事项

七、DML 语句的性能优化

（一）索引优化

（二）批量操作

（三）避免全表扫描

（四）合理使用 JOIN

（五）使用临时表

（六）避免使用 SELECT *

（七）使用合适的存储引擎

（八）数据库参数优化

八、DML 语句的权限管理

（一）授权

（二）撤销权限

（九）查看权限

（十）注意事项

九、DML 语句的案例分析

（一）案例背景

（二）数据库表结构

（三）业务需求及 DML 语句实现

公告