MySQL增删改查基础教程：熟练掌握DML语句操作【MySQL系列】

本文将以实战演练为主线，深入讲解MySQL日常的增删改查操作，重点聚焦查询语句的条件筛选（WHERE子句）与排序（ORDER BY子句），并通过丰富的示例和最佳实践，帮助读者彻底掌握这些基础命令。

一、DML语句简介与使用环境准备

1.1 DML语句概述

DML（Data Manipulation Language）是SQL语言的一个子集，主要用于对数据库中已存在的数据进行增、删、改、查操作。常用的四类语句包括：

• INSERT：向表中插入新记录。
• UPDATE：修改表中已有记录。
• DELETE：删除表中指定记录。
• SELECT：查询表中的记录。

掌握这四类命令不仅是日常开发的基础，也是学习事务（TRANSACTION）、锁机制、索引以及数据库性能优化的前提。

1.2 环境搭建

1. 安装MySQL：可以通过官网下载社区版（Community Edition），也可使用Docker镜像快速部署。

# Docker方式部署示例：
docker pull mysql:8.0
docker run --name mysql-demo -e MYSQL_ROOT_PASSWORD=Secret123 -p 3306:3306 -d mysql:8.0

2. 连接数据库：使用命令行客户端或图形化工具（如MySQL Workbench、Navicat、DBeaver等）。

mysql -h127.0.0.1 -uroot -pSecret123

3. 创建测试库与测试表：

-- 创建数据库\ nCREATE DATABASE IF NOT EXISTS dml_demo CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE dml_demo;-- 创建示例表：`employees` 员工表
CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,first_name VARCHAR(50) NOT NULL,last_name VARCHAR(50) NOT NULL,department VARCHAR(50) NOT NULL,salary DECIMAL(10,2) NOT NULL,hire_date DATE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

环境准备完成后，即可进入DML实战演练。

---

二、INSERT命令详解与批量插入技巧

2.1 基本单行插入

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES ('Alice', 'Wang', 'Engineering', 8500.00, '2023-01-15');

• INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...); 格式。
• 插入时要注意数据类型与长度限制，避免报错。

2.2 批量插入多行数据

INSERT INTO employees (first_name, last_name, department, salary, hire_date)
VALUES('Bob', 'Li', 'Marketing', 7200.50, '2022-11-01'),('Carol', 'Zhang', 'HR', 6800.00, '2021-05-20'),('David', 'Chen', 'Engineering', 9000.00, '2023-02-10');

批量插入能够显著提升执行效率，适用于初始化数据或导入小批量数据场景。

2.3 使用INSERT ... SELECT从其他表插入

-- 假设有备份表 old_employees
INSERT INTO employees (first_name, last_name, department, salary, hire_date)
SELECT first_name, last_name, department, salary, hire_date
FROM old_employees
WHERE hire_date >= '2020-01-01';

此方式便于将已存历史数据迁移到新表中。

2.4 忽略重复或更新冲突：INSERT IGNORE与ON DUPLICATE KEY UPDATE

-- 忽略主键或唯一索引冲突
INSERT IGNORE INTO employees (id, first_name, last_name, department, salary, hire_date)
VALUES (1, 'Eve', 'Liu', 'Finance', 7800.00, '2022-07-15');-- 或使用冲突更新
INSERT INTO employees (id, first_name, last_name, department, salary, hire_date)
VALUES (1, 'Eve', 'Liu', 'Finance', 7800.00, '2022-07-15')
ON DUPLICATE KEY UPDATE salary = VALUES(salary), department = VALUES(department);

---

三、UPDATE命令及条件更新策略

3.1 基本更新语法

UPDATE employees
SET salary = 8800.00
WHERE id = 3;

• UPDATE table_name SET col1 = val1, col2 = val2, ... [WHERE 条件];
• 千万注意：若省略WHERE，将对全表所有记录执行更新操作！

3.2 多列同时更新

UPDATE employees
SET department = 'R&D', salary = salary * 1.05
WHERE department = 'Engineering';

示例中，对Engineering部门的所有员工加薪5%，并更新部门名称。

3.3 结合子查询进行复杂更新

UPDATE employees e
JOIN (SELECT id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department
) dept_avg ON e.department = dept_avg.department
SET e.salary = dept_avg.avg_salary
WHERE e.salary < dept_avg.avg_salary;

将所有低于本部门平均薪资的员工工资调整至部门平均值。

---

四、DELETE命令与数据安全措施

4.1 基本删除语法

DELETE FROM employees
WHERE id = 5;

如省略WHERE，则会清空整表（效果同TRUNCATE TABLE，但TRUNCATE重置自增计数）。

4.2 批量删除与子查询

DELETE FROM employees
WHERE hire_date < '2020-01-01';

或结合子查询：

DELETE e FROM employees e
JOIN outdated_list o ON e.id = o.employee_id;

4.3 软删除理念与实现

为了避免误删导致的数据丢失，常用“软删除”方案：在表中新增is_deleted或deleted_at字段。

ALTER TABLE employees ADD COLUMN is_deleted TINYINT(1) DEFAULT 0;
-- 软删除
UPDATE employees SET is_deleted = 1 WHERE id = 8;
-- 查询时过滤软删除数据
SELECT * FROM employees WHERE is_deleted = 0;

---

五、SELECT语句全方位解析

SELECT语句是DML中最灵活、最强大的部分，本节将深入分解其各个功能模块。

5.1 基本查询

SELECT * FROM employees;
SELECT id, first_name, last_name, salary FROM employees;

5.2 WHERE条件过滤

WHERE子句用于对查询结果进行行级过滤，是学习SQL核心之处。常见条件运算符包括：=、<>、>、<、>=、<=、BETWEEN、IN、LIKE、IS NULL等。

• 等值判断：WHERE department = 'HR'
• 范围判断：WHERE salary BETWEEN 7000 AND 9000
• 集合判断：WHERE department IN ('HR', 'Finance', 'Marketing')
• 模糊匹配：WHERE last_name LIKE 'C%' 匹配以C开头的姓氏
• 空值判断：WHERE manager_id IS NULL

多条件组合使用AND、OR，并可嵌套小括号：

SELECT * FROM employees
WHERE (department = 'Engineering' OR department = 'R&D')AND salary > 8000;

5.3 ORDER BY排序

ORDER BY用于对查询结果进行排序，默认升序（ASC），可指定降序（DESC）。

SELECT id, first_name, salary FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC, first_name ASC;

• 多列排序时，按列顺序依次排序。
• 若未指定NULLS FIRST/NULLS LAST（MySQL无此关键字），则根据默认行为排序。

5.4 LIMIT限制结果数

常用于分页或只取Top N：

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 5;

或结合偏移量：

SELECT * FROM employees ORDER BY hire_date DESC LIMIT 10, 5;

5.5 组合查询（AND、OR、IN、BETWEEN、LIKE）

详见WHERE讲解，上述各种条件可灵活组合，注意逻辑优先级与括号使用。

5.6 聚合与分组（GROUP BY、HAVING）

聚合函数：COUNT()、SUM()、AVG()、MAX()、MIN()。

SELECT department, COUNT(*) AS num, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING num >= 3
ORDER BY avg_salary DESC;

HAVING用于过滤分组结果，作用类似于WHERE但按分组后数据过滤。

---

六、实战演练：日常数据库操作演示

（此处可结合具体业务场景，模拟员工管理系统的增删改查操作脚本，并附上演示截图与SQL执行结果）

…（省略，可根据读者需要补充完整脚本与输出示例，约2500字）

---

七、性能优化与常见误区

1. 避免SELECT *：只查询所需字段，减少网络带宽与IO。
2. 合理使用索引：对常用的WHERE和ORDER BY列加索引。
3. 注意批量更新/删除的事务大小：避免长事务导致锁争用。
4. 分页查询优化：当数据量大时，使用索引分页或基于WHERE id > 上次最大值方式。
5. 监控慢查询日志：定位执行缓慢的SQL。

---