三亩地 三亩地SAN MU DI · CODE DIARY
交流投稿
ARTICLE DETAIL

日记详情

真实记录编程学习的某一天，欢迎挑你感兴趣的翻一翻。

首页/学习日记/日记详情

MySQL增删改查实战：从基础语法到性能优化全解析

发布时间：2026/8/15 22:47:19 三亩地 编程学习日记
MySQL增删改查实战：从基础语法到性能优化全解析

1. 项目概述：从零开始掌握数据库的“四则运算”

如果你刚开始接触后端开发、数据分析，或者任何需要处理数据的岗位，那么“MySQL表的增删改查”就是你绕不开的第一道坎。这听起来可能有点枯燥，不就是往数据库里存点东西、查点东西吗？但我想告诉你，这恰恰是数据世界的基石，就像学数学要先学加减乘除一样。很多人觉得增删改查太基础，草草看过，结果在实际工作中，要么写出的查询慢如蜗牛，要么一不小心就把重要数据给误删了。我见过太多因为基础不牢而引发的线上事故。

所以，今天我们不聊那些高深莫测的分布式、高并发，就扎扎实实地回到起点，把MySQL里对一张表进行“增、删、改、查”这四种基本操作掰开揉碎了讲清楚。我会以一个虚拟的“员工信息表”为例，带你从连接数据库开始，一步步完成所有操作，并分享那些只有踩过坑才知道的细节和技巧。无论你是完全的数据库新手，还是想重新巩固基础的老手，这篇内容都能让你对“增删改查”有一个全新、透彻的理解。

2. 环境准备与数据表设计思路

在动手写任何SQL语句之前，有两件事必须明确：你的操作环境和你要操作的对象。环境决定了你怎么连接和交互，而表结构设计则决定了你后续所有操作的效率和准确性。

2.1 连接MySQL的几种姿势与选择

首先，你得能连接到MySQL服务器。对于初学者，我强烈推荐从图形化界面工具开始，比如MySQL Workbench、Navicat或者DBeaver。它们能直观地展示数据库、表和数据，降低初学者的认知门槛。以MySQL Workbench为例，安装后，你需要创建一个新的连接，填写主机名（通常是localhost或127.0.0.1）、端口（默认3306）、用户名（如root）和密码。点击“Test Connection”测试通过后，你就拥有了一个可视化的操作台。

但对于自动化脚本、服务器部署或者追求效率的老手，命令行客户端才是终极武器。在终端或CMD中，使用mysql -u root -p命令，回车后输入密码，就能进入MySQL的命令行交互界面。这里你会看到一个mysql>提示符，所有的SQL魔法都将在这里发生。我个人的习惯是，在图形化工具里进行表结构设计、复杂查询的调试和结果预览，而将最终确定下来的SQL语句放到命令行或程序脚本中执行，这样既直观又便于版本管理。

注意：在生产环境中，切忌使用root账户进行日常的增删改查操作。应该为每个应用创建独立的数据库用户，并授予最小必要的权限（比如只授予某个数据库的增删改查权限），这是最基本的安全规范。

2.2 设计你的第一张数据表：以员工表为例

假设我们要管理一个公司的员工信息，我们需要创建一张employees表。在设计时，不能想到什么字段就加什么，必须遵循一些原则：

  1. 每个字段意义单一：比如“姓名”应该拆分成first_name和last_name，而不是一个full_name，方便按姓氏检索。
  2. 选择合适的数据类型：用最精确的类型存储数据，能节省空间并提升效率。比如年龄用TINYINT UNSIGNED（0-255），工资用DECIMAL(10, 2)（总共10位，小数占2位），入职日期用DATE。
  3. 必须定义主键：主键是表中每一行数据的唯一标识。通常我们会使用一个与业务无关的自增整数id作为主键，这比用身份证号、邮箱等业务字段更高效、更灵活。

基于以上思路，我们为employees表设计以下结构：

  • id: 主键，自增长。
  • first_name: 名，变长字符串。
  • last_name: 姓，变长字符串。
  • email: 邮箱，需要唯一，避免重复注册。
  • department_id: 部门ID，整数，用于关联部门表（为后续联查铺垫）。
  • salary: 薪水，十进制数。
  • hire_date: 入职日期。

在创建表之前，我们得先有一个数据库。使用CREATE DATABASE IF NOT EXISTS company CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;语句创建一个名为company的数据库，并指定万能的utf8mb4字符集以支持所有emoji和生僻字。然后通过USE company;命令切换到该数据库。现在，我们可以创建员工表了。

CREATE TABLE employees ( id INT PRIMARY KEY AUTO_INCREMENT, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, department_id INT, salary DECIMAL(10, 2) DEFAULT 0.00, hire_date DATE NOT NULL, INDEX idx_department (department_id), -- 为部门ID创建索引，加速按部门查询 INDEX idx_name (last_name, first_name) -- 复合索引，加速按姓名查询 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这段SQL做了几件关键事：定义了字段和类型；设置了id为主键且自增；为email设置了唯一约束；为salary设置了默认值；最后，还创建了两个索引。这里重点说一下索引，idx_department能让我们在根据department_id筛选员工时速度飞快，而idx_name这个复合索引，则优化了按姓氏、或者同时按姓氏和名字进行查询和排序的效率。在表设计阶段就考虑好索引，是避免未来查询性能问题的关键一步。

3. 核心操作一：增（INSERT）—— 如何安全高效地添加数据

创建好空表之后，第一件事就是往里添加数据，也就是INSERT操作。这看似简单，但里面有不少门道，搞不好就会成为性能瓶颈或者数据混乱的源头。

3.1 基础的INSERT语句与常见陷阱

最基础的插入语句是指定所有列名并一一对应值的写法：

INSERT INTO employees (first_name, last_name, email, department_id, salary, hire_date) VALUES ('张', '三', 'zhangsan@example.com', 1, 15000.00, '2023-05-10');

这种写法非常清晰，即使表结构后续增加新字段，这条语句通常也不会出错。但我见过很多人喜欢用省略列名的简写方式：INSERT INTO employees VALUES (NULL, '张', '三', ...)。这种方式严重依赖表中字段的顺序，一旦表结构变更（比如中间插入了一个新字段），这条SQL就会因为值列表与字段列表不匹配而执行失败，甚至可能把数据错误地插入到其他字段中，造成灾难性后果。所以，务必养成指定列名插入的习惯，这是写出稳健SQL的第一步。

3.2 批量插入：大幅提升数据导入效率

如果你需要一次性插入多条数据，比如初始化数据或导入数据，千万不要在程序里循环执行单条INSERT语句。每执行一次SQL，网络通信、语法解析、事务开销都会产生成本。正确的做法是使用批量插入：

INSERT INTO employees (first_name, last_name, email, department_id, salary, hire_date) VALUES ('李', '四', 'lisi@example.com', 2, 12000.00, '2022-08-15'), ('王', '五', 'wangwu@example.com', 1, 18000.00, '2021-03-22'), ('赵', '六', 'zhaoliu@example.com', 3, 9000.00, '2023-11-30');

一条语句插入三行数据，效率远高于执行三条单独的语句。在需要插入成千上万条记录时，这个性能差异可能是几分钟和几小时的差别。不过也要注意，单条SQL语句的长度是有限制的（由max_allowed_packet参数控制），如果数据量极大，需要分批次进行批量插入。

3.3 从查询结果插入：灵活的数据迁移与备份

INSERT语句还有一个强大的功能，就是可以直接插入另一个查询语句的结果集。这在数据迁移、表备份或者数据汇总时非常有用。

假设我们有一张interns实习生表，现在要将其中已经转正的实习生数据正式加入employees表：

INSERT INTO employees (first_name, last_name, email, department_id, salary, hire_date) SELECT first_name, last_name, email, department_id, 8000.00, CURDATE() -- 给一个默认起薪，入职日期为今天 FROM interns WHERE status = 'converted'; -- 只选择状态为‘已转正’的实习生

这个INSERT ... SELECT ...的组合，一次性完成了数据筛选和插入，既高效又保证了数据逻辑的一致性。它避免了先将数据查询到应用程序内存中，再组织成INSERT语句的繁琐过程和潜在的性能损耗。

4. 核心操作二：查（SELECT）—— 从海量数据中精准定位信息

查，是数据库中使用频率最高的操作。一个高效的查询，能瞬间从海量数据中找到所需；而一个糟糕的查询，则可能拖垮整个数据库。SELECT语句的学问，远不止SELECT *那么简单。

4.1 SELECT语句的完整语法结构与执行顺序

一个完整的SELECT语句包含多个子句，理解它们的执行顺序至关重要，这直接决定了你如何优化查询：

  1. FROM & JOIN: 确定数据来源，连接哪些表。
  2. WHERE: 对原始数据行进行过滤。
  3. GROUP BY: 将过滤后的数据进行分组。
  4. HAVING: 对分组后的结果集进行过滤（WHERE是分组前过滤行，HAVING是分组后过滤组）。
  5. SELECT: 计算选择列表中的表达式，生成最终显示的列。
  6. DISTINCT: 去除重复行。
  7. ORDER BY: 对结果进行排序。
  8. LIMIT / OFFSET: 限制返回的行数，用于分页。

很多人会误以为书写顺序就是执行顺序，于是写出SELECT department_id, AVG(salary) as avg_sal FROM employees WHERE avg_sal > 10000 GROUP BY department_id这样的错误语句。因为WHERE执行时，avg_sal这个别名还未被计算出来。正确的写法应该是使用HAVING子句：SELECT department_id, AVG(salary) as avg_sal FROM employees GROUP BY department_id HAVING avg_sal > 10000。

4.2 WHERE子句：过滤数据的艺术

WHERE子句是筛选数据的核心。除了常用的=、>、<、BETWEEN、IN，有几个点需要特别注意：

  • LIKE模糊查询与索引失效：WHERE last_name LIKE ‘%三’这种以通配符%开头的查询，会导致索引失效，进行全表扫描。如果业务上必须进行前缀模糊匹配，可以考虑使用全文索引（FULLTEXT INDEX）或专门的搜索引擎（如Elasticsearch）。
  • NULL值的判断：在SQL中，NULL代表未知，它不等于任何值，甚至不等于另一个NULL。因此，不能用=或!=来判断NULL，必须使用IS NULL或IS NOT NULL。例如，查找部门未定的员工：WHERE department_id IS NULL。
  • 避免在WHERE子句中对字段进行函数操作：例如WHERE YEAR(hire_date) = 2023，这会导致数据库无法使用hire_date字段上的索引，因为它必须对每一行数据都先计算YEAR()函数的值。更好的写法是使用范围查询：WHERE hire_date >= ‘2023-01-01’ AND hire_date < ‘2024-01-01’。

4.3 JOIN连接：关联多张表的桥梁

当需要的信息分散在多张表时，就需要JOIN。我们为employees表添加一个departments部门表。

CREATE TABLE departments ( id INT PRIMARY KEY, name VARCHAR(50) NOT NULL, location VARCHAR(100) );

最常用的是INNER JOIN（内连接），它只返回两个表中连接字段匹配的行。比如，查询员工及其部门信息：

SELECT e.first_name, e.last_name, d.name AS department_name FROM employees e INNER JOIN departments d ON e.department_id = d.id;

这里使用了表别名（e和d）来简化SQL。ON后面指定了连接条件。如果某个员工的department_id在departments表中找不到对应项（为NULL或不匹配），则该员工不会出现在结果中。

如果需要列出所有员工，即使他没有部门（department_id为NULL），就要用到LEFT JOIN（左连接）：

SELECT e.first_name, e.last_name, d.name AS department_name FROM employees e LEFT JOIN departments d ON e.department_id = d.id;

此时，employees表是左表，即使连接条件不满足，左表的所有行也会被返回，右表对应的字段则以NULL填充。RIGHT JOIN同理，但使用较少，通常可以通过调换表顺序用LEFT JOIN代替。

4.4 聚合函数与分组：让数据开口说话

聚合函数（如COUNT,SUM,AVG,MAX,MIN）能将多行数据汇总成一个值。结合GROUP BY，我们可以进行分组统计。

例如，统计每个部门的员工数量和平均薪资：

SELECT d.name AS department_name, COUNT(e.id) AS employee_count, -- 使用COUNT(id)比COUNT(*)更精确，忽略NULL AVG(e.salary) AS average_salary, MAX(e.salary) AS max_salary FROM departments d LEFT JOIN employees e ON d.id = e.department_id GROUP BY d.id, d.name -- GROUP BY的字段通常要包含SELECT中所有非聚合字段 ORDER BY average_salary DESC;

这里使用了LEFT JOIN，确保即使某个部门没有员工（新成立的部门），也会出现在统计结果中，员工数量显示为0。ORDER BY average_salary DESC则按平均薪资从高到低对结果进行排序，让数据呈现更直观。

5. 核心操作三：改（UPDATE）与删（DELETE）—— 数据变更的谨慎之道

如果说SELECT是读操作，那么UPDATE和DELETE就是写操作。它们会直接修改磁盘上的数据，因此必须慎之又慎。一个没有WHERE条件的UPDATE或DELETE语句，就是一场灾难。

5.1 UPDATE：精准定位，局部修改

UPDATE语句用于修改已有记录。其核心在于SET子句指定要修改的列和新值，以及WHERE子句精确锁定要修改的行。

场景一：普调薪资。公司决定给所有部门ID为2的员工加薪10%。

UPDATE employees SET salary = salary * 1.10 WHERE department_id = 2;

执行前，务必反复检查WHERE条件。你可以先运行一个对应的SELECT语句来确认影响的范围：SELECT * FROM employees WHERE department_id = 2;。确认无误后，再执行UPDATE。

场景二：修改单条记录。员工“张三”换了邮箱。

UPDATE employees SET email = ‘zhangsan_new@example.com’ WHERE first_name = ‘张’ AND last_name = ‘三’;

这里用姓名作为条件有一定风险，因为可能有重名。最佳实践是始终使用主键id来定位单条记录，因为它是绝对唯一的。UPDATE employees SET email = ‘...’ WHERE id = 1;

场景三：基于子查询的复杂更新。将技术部（假设部门id为1）所有员工的薪资，设置为公司平均薪资的1.2倍。

UPDATE employees e JOIN ( SELECT AVG(salary) AS company_avg_sal FROM employees ) AS avg_table SET e.salary = avg_table.company_avg_sal * 1.2 WHERE e.department_id = 1;

这个例子展示了如何使用子查询的结果来更新数据。它先计算出一个全公司的平均薪资，然后用这个值去更新特定部门的员工薪资。

5.2 DELETE：永久移除数据的“危险”操作

DELETE语句用于删除整行记录。删除操作是不可逆的（在没有备份或开启事务的情况下），因此其危险性比UPDATE更高。

删除特定员工（同样，建议用主键）：

DELETE FROM employees WHERE id = 5;

清空整张表（极度危险！）：

DELETE FROM employees;

这条语句会删除employees表中的所有数据，但表结构（字段、索引等）还在。与之类似但更彻底的是TRUNCATE TABLE employees;，它删除所有数据并重置自增计数器，且通常无法通过事务回滚，速度更快。

黄金法则：在执行任何UPDATE或DELETE语句前，尤其是在生产环境，请遵循以下步骤：1) 用SELECT+相同的WHERE条件预览受影响的数据；2) 开启一个数据库事务（BEGIN;）；3) 执行UPDATE/DELETE；4) 再次用SELECT确认修改是否正确；5) 如果正确，提交事务（COMMIT;）；如果错误，回滚事务（ROLLBACK;）。事务是你的安全绳。

5.3 软删除：一种更安全的数据删除策略

在实际业务中，我们很少真正物理删除数据，因为数据可能关联其他记录，或者有审计需求。更通用的做法是“软删除”（Soft Delete）。我们在表中增加一个标志位字段，如is_deleted TINYINT DEFAULT 0，0代表未删除，1代表已删除。

“删除”操作就变成了一个UPDATE：

UPDATE employees SET is_deleted = 1, delete_time = NOW() WHERE id = 5;

而所有的SELECT查询都需要默认加上WHERE is_deleted = 0这个条件，以确保不会看到“已删除”的数据。这样，数据实际上还在数据库中，只是对应用“不可见”了，在需要时可以恢复或进行数据分析。这是一种非常重要的设计模式。

6. 避坑指南与性能优化实战

掌握了基本语法，不代表就能写出好的SQL。下面这些是我在多年实践中总结的常见“坑”和优化技巧，很多都是在付出代价后才学到的。

6.1 索引失效的典型场景与应对

我们之前为last_name和department_id创建了索引，但错误的写法会让索引失效，查询速度急剧下降。

  1. 对索引列进行运算或函数操作：WHERE YEAR(hire_date) = 2023会导致hire_date索引失效。应改为范围查询。
  2. 使用OR连接条件：如果OR两边的条件涉及不同列，且这些列都有索引，MySQL有时可能无法有效利用索引。可以考虑拆分成两个查询用UNION连接，或者使用WHERE (condition1) OR (condition2)并确保condition1和condition2都能有效利用索引。
  3. 模糊查询LIKE ‘%xxx’：前导百分号是索引杀手。如果业务允许，尽量使用LIKE ‘xxx%’（后缀匹配），这样索引仍然有效。如果必须进行全文模糊匹配，请考虑使用MySQL的全文索引或引入Elasticsearch。
  4. 不恰当的数据类型转换：如果WHERE条件中，字符串类型的索引字段与数字进行比较，如WHERE employee_id = ‘1001’（employee_id是整数列），MySQL会进行隐式类型转换，也可能导致索引失效。应确保比较双方类型一致。

6.2 分页查询的大数据量优化

最常见的分页写法是LIMIT offset, row_count，例如SELECT * FROM employees ORDER BY id LIMIT 10000, 20;，表示跳过前10000条，取20条。问题：当offset非常大时（比如翻到第5000页），MySQL需要先读取offset+row_count条数据，然后丢弃前offset条，效率极低。

优化方案：使用“游标分页”或“基于索引的分页”。

-- 传统低效分页 SELECT * FROM employees ORDER BY id LIMIT 10000, 20; -- 优化方案：记录上一页最后一条记录的id SELECT * FROM employees WHERE id > 10000 ORDER BY id LIMIT 20;

假设上一次查询返回的最后一条记录的id是10000，那么下一页查询就使用WHERE id > 10000。这样，数据库可以利用id的主键索引直接定位到开始位置，跳过了前10000行的扫描，性能有数量级的提升。这种方法的限制是排序必须基于一个唯一且连续的字段（如自增主键），且不能随意跳页。

6.3 事务与锁的简要理解

当你需要连续执行多条INSERT/UPDATE/DELETE语句，并希望它们作为一个整体（要么全部成功，要么全部失败）时，就需要事务。例如银行转账：A账户减钱和B账户加钱必须同时成功或失败。

BEGIN; -- 或 START TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = ‘A’; UPDATE accounts SET balance = balance + 100 WHERE user_id = ‘B’; -- 检查是否有错误... COMMIT; -- 提交事务，使更改永久化 -- 如果发生错误，则执行 ROLLBACK; 回滚所有更改

在事务执行过程中，涉及的数据行可能会被“锁定”，以防止其他并发事务修改，确保数据一致性。但不当的事务设计（如大事务、事务中包含慢查询）会导致锁持有时间过长，引发其他请求阻塞，这是数据库性能问题的常见原因。原则是：事务要尽可能短小，尽快提交或回滚。

6.4 EXPLAIN命令：你的SQL性能诊断仪

如何知道你的SQL语句是否用到了索引？执行效率如何？MySQL提供了EXPLAIN这个强大的工具。只需在SELECT语句前加上EXPLAIN即可。

EXPLAIN SELECT * FROM employees WHERE last_name = ‘张’ AND first_name = ‘三’;

查看结果中的几个关键列：

  • type: 访问类型，从好到坏大致是：system>const>eq_ref>ref>range>index>ALL。ALL表示全表扫描，需要优化。
  • key: 实际使用的索引。如果为NULL，说明没用到索引。
  • rows: MySQL预估需要扫描的行数。这个值越小越好。
  • Extra: 额外信息，如Using where（使用了WHERE过滤）、Using index（使用了覆盖索引，非常好）、Using filesort（需要额外排序，可能影响性能）。

学会阅读EXPLAIN的输出，是进行SQL性能调优的必备技能。它能直观地告诉你，你的查询是如何被执行的，瓶颈可能在哪里。

编程学习 学习日记 实战经验
← 返回列表