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

大熊计算机

发布于 2025-07-14 20:06:52

7360

文章被收录于专栏：C博文C博文

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

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

1.1 DML语句概述

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

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

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

1.2 环境搭建

安装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

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

mysql -h127.0.0.1 -uroot -pSecret123

创建测试库与测试表：

-- 创建数据库\ 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_salary
  FROM employees
  GROUP 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但按分组后数据过滤。

七、性能优化与常见误区

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

本项目适用于后台管理系统、电商用户中心、SaaS 用户模块等场景，特别适合开发者进行实战演练与面试准备。

一、项目背景与需求概述

我们将构建一个基础版的用户管理系统，具备以下业务功能：

用户注册与登录
用户角色与权限分配
日志记录与用户状态追踪
多条件用户查询与分页

涉及的核心业务对象包括：用户、角色、权限、日志等。

二、数据库建模与表结构设计

2.1 实体关系图（ER图）简要说明

一位用户可以拥有多个角色（多对多）
一个角色可以拥有多个权限（多对多）
用户与登录日志是一对多关系

2.2 用户表（`users`）

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL UNIQUE,
  password VARCHAR(100) NOT NULL,
  email VARCHAR(100),
  status TINYINT DEFAULT 1 COMMENT '0:禁用, 1:启用',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

2.3 角色表（`roles`）

CREATE TABLE roles (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  description VARCHAR(255)
);

2.4 权限表（`permissions`）

CREATE TABLE permissions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL UNIQUE,
  code VARCHAR(50) NOT NULL UNIQUE COMMENT '用于权限标识，如 user:view'
);

2.5 用户-角色关联表（`user_role`）

CREATE TABLE user_role (
  user_id INT,
  role_id INT,
  PRIMARY KEY (user_id, role_id),
  FOREIGN KEY (user_id) REFERENCES users(id),
  FOREIGN KEY (role_id) REFERENCES roles(id)
);

2.6 角色-权限关联表（`role_permission`）

CREATE TABLE role_permission (
  role_id INT,
  permission_id INT,
  PRIMARY KEY (role_id, permission_id),
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (permission_id) REFERENCES permissions(id)
);

2.7 登录日志表（`login_logs`）

CREATE TABLE login_logs (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  ip_address VARCHAR(45),
  login_time DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

三、数据初始化脚本

3.1 插入初始角色与权限

INSERT INTO roles(name, description) VALUES ('admin', '系统管理员'), ('user', '普通用户');

INSERT INTO permissions(name, code) VALUES
('查看用户', 'user:view'),
('新增用户', 'user:create'),
('删除用户', 'user:delete');

-- 分配权限给角色
INSERT INTO role_permission(role_id, permission_id) VALUES
(1, 1), (1, 2), (1, 3), -- admin 拥有全部权限
(2, 1);                 -- user 仅能查看用户

3.2 插入测试用户

INSERT INTO users(username, password, email) VALUES
('alice', 'hashed_pwd1', 'alice@example.com'),
('bob', 'hashed_pwd2', 'bob@example.com');

-- 分配角色
INSERT INTO user_role(user_id, role_id) VALUES
(1, 1), -- alice 为管理员
(2, 2); -- bob 为普通用户

四、典型查询场景实现

4.1 查询所有启用用户及其角色

SELECT u.id, u.username, r.name AS role
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
WHERE u.status = 1;

4.2 查询某用户拥有的所有权限

SELECT p.name, p.code
FROM users u
JOIN user_role ur ON u.id = ur.user_id
JOIN role_permission rp ON ur.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.id
WHERE u.username = 'alice';

4.3 查询最近7天登录日志

SELECT u.username, l.ip_address, l.login_time
FROM login_logs l
JOIN users u ON l.user_id = u.id
WHERE l.login_time >= NOW() - INTERVAL 7 DAY
ORDER BY l.login_time DESC;

4.4 用户分页查询（带关键字搜索）

SELECT *
FROM users
WHERE username LIKE '%bob%'
ORDER BY created_at DESC
LIMIT 0, 10;

五、事务控制与一致性保障

在角色授权或用户注册等业务流程中，可以使用事务来确保数据完整性。

5.1 注册用户 + 分配默认角色（事务）

START TRANSACTION;

INSERT INTO users(username, password, email) VALUES('charlie', 'hashed_pwd3', 'charlie@example.com');
SET @uid = LAST_INSERT_ID();
INSERT INTO user_role(user_id, role_id) VALUES(@uid, 2); -- 默认赋普通角色

COMMIT;

5.2 授权失败时回滚

START TRANSACTION;

-- 假设某权限不存在导致失败
INSERT INTO role_permission(role_id, permission_id) VALUES(1, 999);

-- 失败时回滚
ROLLBACK;

六、索引优化与执行分析

6.1 建议加索引字段

users.username：用于登录验证、搜索
login_logs.user_id：日志查询
user_role.user_id / role_permission.role_id：JOIN 优化

CREATE INDEX idx_username ON users(username);
CREATE INDEX idx_user_log ON login_logs(user_id);

6.2 执行计划分析

EXPLAIN SELECT u.username, r.name FROM users u JOIN user_role ur ON u.id = ur.user_id JOIN roles r ON ur.role_id = r.id;

可查看索引是否使用、JOIN 类型、Rows 扫描数量等。

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2025-05-31，如有侵权请联系 cloudcommunity@tencent.com 删除

mysql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！

登录后参与评论

0 条评论

热度