LumantoBlog

Appearance

MySQL 第四章：数据增删改查 DML + DQL ​

本章目标：掌握日常开发中最高频的数据库操作能力，能够完成新增、删除、修改、查询，并写出可维护、可扩展的 SQL。

1. 概念总览 ​

1.1 DML 是什么 ​

DML（Data Manipulation Language）用于操作表中的数据。

  • INSERT：新增数据
  • UPDATE：修改数据
  • DELETE：删除数据

1.2 DQL 是什么 ​

DQL（Data Query Language）用于查询数据，核心命令是 SELECT。

2. 准备测试表与数据 ​

sql
CREATE TABLE IF NOT EXISTS employees (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department VARCHAR(30) NOT NULL,
  salary DECIMAL(10,2) NOT NULL,
  hire_date DATE NOT NULL,
  status TINYINT NOT NULL DEFAULT 1
);

INSERT INTO employees (name, department, salary, hire_date, status) VALUES
('张三', '研发', 18000.00, '2023-03-15', 1),
('李四', '研发', 22000.00, '2022-08-01', 1),
('王五', '测试', 15000.00, '2024-01-10', 1),
('赵六', '产品', 20000.00, '2021-11-20', 0);

3. DML：新增（INSERT） ​

3.1 插入单行 ​

sql
INSERT INTO employees (name, department, salary, hire_date, status)
VALUES ('钱七', '运营', 13000.00, '2024-05-01', 1);

3.2 插入多行 ​

sql
INSERT INTO employees (name, department, salary, hire_date, status) VALUES
('孙八', '研发', 21000.00, '2023-09-01', 1),
('周九', '测试', 14000.00, '2024-03-05', 1);

3.3 插入时的注意事项 ​

  • 字段顺序和值顺序必须一一对应
  • 建议显式写字段名，不要依赖“全字段顺序”
  • 对 NOT NULL 字段必须提供有效值（或设置默认值）

4. DML：修改（UPDATE） ​

4.1 修改单条记录 ​

sql
UPDATE employees
SET salary = 23000.00
WHERE id = 2;

4.2 批量修改 ​

sql
UPDATE employees
SET salary = salary * 1.1
WHERE department = '研发' AND status = 1;

4.3 UPDATE 高风险点 ​

  • 必须带 WHERE，否则会更新整张表
  • 先用 SELECT 验证条件，再执行 UPDATE
  • 生产环境建议在事务中执行，可回滚

5. DML：删除（DELETE） ​

5.1 删除指定记录 ​

sql
DELETE FROM employees
WHERE id = 4;

5.2 条件删除 ​

sql
DELETE FROM employees
WHERE status = 0;

5.3 DELETE 与 TRUNCATE 的区别 ​

命令类型是否可带 WHERE是否重置自增适用场景
DELETEDML可以通常不重置按条件删除
TRUNCATEDDL不可以会重置快速清空全表

6. DQL：基础查询（SELECT） ​

6.1 查询所有字段 ​

sql
SELECT * FROM employees;

6.2 查询指定字段 ​

sql
SELECT id, name, department, salary
FROM employees;

6.3 条件查询（WHERE） ​

sql
SELECT id, name, salary
FROM employees
WHERE department = '研发' AND salary >= 20000;

6.4 排序（ORDER BY） ​

sql
SELECT id, name, salary
FROM employees
ORDER BY salary DESC, id ASC;

6.5 分页（LIMIT） ​

sql
-- 第 1 页，每页 2 条
SELECT id, name, salary
FROM employees
ORDER BY id ASC
LIMIT 0, 2;

6.6 去重（DISTINCT） ​

sql
SELECT DISTINCT department
FROM employees;

7. DQL：聚合与分组 ​

7.1 常见聚合函数 ​

  • COUNT()：计数
  • SUM()：求和
  • AVG()：平均值
  • MAX()：最大值
  • MIN()：最小值

7.2 分组统计（GROUP BY） ​

sql
SELECT department,
       COUNT(*) AS emp_count,
       ROUND(AVG(salary), 2) AS avg_salary
FROM employees
WHERE status = 1
GROUP BY department
HAVING AVG(salary) >= 15000;

WHERE 是分组前过滤，HAVING 是分组后过滤。

8. 查询执行逻辑（便于面试理解） ​

flowchart LR
    A[FROM] --> B[WHERE]
    B --> C[GROUP BY]
    C --> D[HAVING]
    D --> E[SELECT]
    E --> F[ORDER BY]
    F --> G[LIMIT]

9. 综合实战 SQL ​

9.1 查询研发部门工资 Top 3 ​

sql
SELECT id, name, salary
FROM employees
WHERE department = '研发' AND status = 1
ORDER BY salary DESC
LIMIT 3;

9.2 每个部门在职人数和最高工资 ​

sql
SELECT department,
       COUNT(*) AS on_job_count,
       MAX(salary) AS max_salary
FROM employees
WHERE status = 1
GROUP BY department;

9.3 统计入职时间在 2023 年后的员工 ​

sql
SELECT COUNT(*) AS after_2023_count
FROM employees
WHERE hire_date >= '2023-01-01';

10. 面试高频点 ​

10.1 WHERE 和 HAVING 的区别 ​

  • WHERE：在分组前过滤行
  • HAVING：在分组后过滤组

10.2 DELETE、TRUNCATE、DROP 区别 ​

  • DELETE：删数据，保留表结构，可按条件删
  • TRUNCATE：快速清空表，重置自增
  • DROP：删除整张表（结构 + 数据）

10.3 为什么不建议线上直接 SELECT * ​

  • 字段冗余，传输成本高
  • 容易受表结构变化影响
  • 不利于索引覆盖与性能优化

11. 练习题 ​

  1. 插入 3 条员工数据，包含不同部门和薪资。
  2. 把“测试”部门在职员工工资整体上调 8%。
  3. 删除状态为离职（status = 0）的数据。
  4. 查询工资最高的前 5 位员工（按工资倒序）。
  5. 统计每个部门平均薪资，并筛选平均薪资大于 16000 的部门。

12. 本章小结 ​

  • DML 负责改数据，DQL 负责查数据，二者是业务开发中的核心操作。
  • 写 SQL 时优先保证正确性，再考虑性能和可维护性。
  • 先把“单表增删改查 + 分组统计”打扎实，再进入下一章多表查询（JOIN）。