MySQL数据操作全解析：增删改查全方位实践

未分类 4小时前程序员胖胖胖虎阿

3 0 0

MySQL数据操作全面剖析：增删改查全方位实践

文章目录

MySQL系列
前言
一、创建并插入数据
- 1.1 单行数据与全列插入
- 1.2 多行数据与指定列插入
- 1.3 插入冲突时同步更新
- 1.4 冲突时替换
二、读取数据
- 2.1 全列查询
- 2.2 查询指定列
- 2.3 查询字段为表达式
- 2.4 结果去重 DISTINCT
- 2.5 where条件筛选
- 2.6 order by语句（结果排序）
- 2.6 limit的运用（筛选分页结果）
三、更新修改
四、删除
五、插入查询结果

前言

本篇文章将会讲解表格的数据增删查改（CURD）操作，在数据库技术以及项目开发里，CURD是对应创建（Create）、更新（Update）、读取（Read）、删除（Delete）这四类数据处理动作的核心操作缩写，也是贯穿各类项目开发整个流程的基础数据交互基本操作。

一、创建与数据插入

创建一个用于测试的表格结构：

CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

插入语法

INSERT [INTO] table_name
    [(column1 [, column2, ...])]
VALUES
    (value1 [, value2, ...]),
    [(value1 [, value2, ...]), ...];

其中，方括号内的内容是可选填写的部分。核心结构是INSERT [INTO] table_name [(列名列表)] VALUES (对应值列表)，其中table_name是要插入数据的目标表名称，(列名列表)是要插入的具体属性列，比如id, name，VALUES (值列表)是与列名列表一一对应的数据值。关键语法细节包括：INSERT后面可以加上INTO关键字，也可以直接省略；全列插入的情况下，如果不写列名列表，默认需要一次性插入表中所有属性列，此时VALUES后的值需要按照表定义的列顺序完整提供。

1.1 单行数据与全列插入

insert into students values (100, 10000, '唐三藏', null);
insert students values (101, 10001, '孙悟空', '11111');

1.2 多行数据与指定列插入

插入数据时，使用，分割列名列表，多行数据使用，分割值列表：

insert into students (id, sn, name) values
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

1.3 插入冲突时同步更新

在插入数据时，若遇到主键冲突或唯一键冲突，可通过在INSERT语句后添加特定子句（如ON DUPLICATE KEY UPDATE）实现冲突处理逻辑，确保即使存在冲突也能正常执行操作，避免直接报错导致插入失败。

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON DUPLICATE KEY UPDATE
    column1 = value1,  -- 冲突时更新的字段及值
    column2 = value2;  -- 可指定多个更新字段

需要注意的是，更新后的主键和唯一键不能与表中已存在的产生冲突。可以使用SELECT ROW_COUNT();函数查看受影响的行数。

1.4 冲突时替换

-- 主键 或者 唯一键 没有冲突，则直接插入；
-- 主键 或者 唯一键 如果冲突，则删除后再插入
replace into students (sn, name) values (20001, '曹贼');

发生唯一键冲突时，会删除冲突行后重新插入（id自增长会变化），不发生冲突则直接插入。

二、数据读取

SELECT [DISTINCT]
    {*, column1, column2, ...}  -- 选择列（*表示所有列）
FROM table_name
[WHERE condition]              -- 筛选条件
[ORDER BY column1 [ASC|DESC],  -- 排序规则
          column2 [ASC|DESC]]
[LIMIT count];                 -- 限制返回行数

这个SQL语句有多个可选项，接下来通过实例逐一介绍。创建一个测试表：

create table exam_result (
id int unsigned primary key auto_increment,
name varchar(20) not null comment '同学姓名',
chinese float default 0.0 comment '语文成绩',
math float default 0.0 comment '数学成绩',
english float default 0.0 comment '英语成绩'
);

插入测试数据:

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

2.1 全列查询

SELECT * FROM exam_result;

通常不建议使用*进行全列查询，因为查询的列越多，传输的数据量越大，可能影响索引使用。

2.2 查询指定列

-- 指定列的顺序不需要按定义表的顺序来
SELECT id, name, english FROM exam_result;

2.3 查询字段为表达式

select id,name,chinese+english,1 from exam_result;

可以给查询结果指定别名：

select id,name,chinese+english as '语文+英语' from exam_result;

2.4 结果去重

select distinct math from exam_result;

2.5 where条件筛选

比较运算符：
在这里插入图片描述

注意=不可直接用于NULL值比较，可以使用<=>进行比较

null不参与运算（前篇介绍了）

逻辑运算符:

在这里插入图片描述
接下来结合实例，了解运算符的使用

1、查找students表中qq为空的

select name,qq from students where qq<=>null;

select name,qq from students where qq is null;

2、查找students表中qq不为空的

select name,qq from students where qq is not null;

3、英语不及格的同学及英语成绩 ( < 60 )

select name,english from exam_result where english <60;

4、语文成绩在 [80, 90] 分的同学及语文成绩

select name,chinese from exam_result where chinese >= 80 and chinese < 90;

select name,chinese from exam_result where chinese between 80 and 90;

注意使用between...and...查找的区间为闭区间

5、数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

select name,math from exam_result where math=58 or math=59 or math=99 or math=98;

select name ,math from exam_result where math in(58,59,98,99);

6、姓孙的同学

LIKE 模糊匹配：% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

select id,name from exam_result where name like '孙%';

%表示可以匹配任意多字符。

7、孙某同学

select id,name from exam_result where name like '孙_';

_表示仅匹配一个字符。

8、语文成绩好于英语成绩的同学

select name,chinese,english from exam_result where chinese > english;

9、总分在 200 分以下的同学

select name,chinese+math+english from exam_result where chinese+math+english <200;

10、语文成绩 > 80 并且不姓孙的同学

select name,chinese from exam_result where chinese >80 and name not like '孙%';

11、孙某同学，否则要求总成绩 > 200 并且语文成绩 < 数学成绩并且英语成绩 > 80

select name,chinese,math,english,chinese+math+english '总分' 
from exam_result where name like '孙_' or (chinese+math
+english >200 and chinese <math and english > 80);

2.6 order by语句（结果排序）

ASC 为升序（Ascending）（从小到大）
DESC 为降序（Descending）（从大到小）

不做显示声明默认为 ASC方式排序并且没有order by子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序
MySQL中认为NULL值是最小的

SELECT ... FROM table_name [WHERE ...]
ORDER BY column [ASC|DESC], [...];

1、同学及数学成绩，按数学成绩升序显示

select name,math from exam_result order by math asc;

2、查询同学各门成绩，依次按数学降序，英语升序，语文升序的方式显示

SELECT name, math, english, chinese 
FROM exam_result 
ORDER BY 
  math DESC,    -- 数学降序
  english ASC,  -- 英语升序（默认可省略 ASC）
  chinese ASC;  -- 语文升序（默认可省略 ASC）

3、查询同学及总分，由高到低

select name,chinese+math+english total from exam_result 
order by chinese+math+english desc;

在这里插入图片描述
这种写法在标准sql中是不支持的，这里可以直接使用chinese+math+english的别名total进行排序操作，是因为该版本做了特殊处理，而在where的操作下则不被允许：

出现这种差异是因为sql语句的执行顺序造成的，where 子句在 select之前执行，此时 total 别名尚未生成，因此无法引用。

标准 SQL 执行顺序（理论上会报错）

FROM exam_result
读取表数据。

WHERE（隐式）
无过滤条件，保留所有行。

计算表达式
计算 chinese + math + english，但此时未命名为total。

ORDER BY total DESC
报错：total 是 SELECT 中定义的别名，此时尚未生效。

MySQL 的实际执行流程（允许ORDER BY 引用别名）

FROM + 计算表达式
读取表数据，并计算 chinese + math + english，暂存结果 （未命名）。

ORDER BY total DESC
MySQL 允许 ORDER BY 引用尚未正式定义的别名 ，实际使用步骤 1 中暂存的计算结果进行排序。

SELECT name, … AS total
将排序后的结果命名为 total，返回最终结果集。

可以对执行顺序理解为：1、from:先确定操作表 2、where:确定执行条件 3、根据执行条件去表中筛选

2.6 limit的运用（筛选分页结果）

select * from exam_result limit N;//N表示行数

select * from exam_result limit pos,len;//从pos行开始，筛选len行数据

可以看出表中起始行的下标是从0位置开始的。

select * from exam_result limit len offset pos;//从pos位置开始获取len行

三、更新修改

UPDATE table_name
SET column1 = expr1,      -- 要更新的列及值
    column2 = expr2       -- 可同时更新多列
[WHERE condition]         -- 过滤条件（必加！避免全量更新）
[ORDER BY column ASC/DESC] -- 可选：指定更新顺序
[LIMIT row_count];        -- 可选：限制更新行数

1、将孙悟空同学的数学成绩变更为 80 分

update exam_result set math=80 where name='孙悟空';

2、将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

update exam_result set math=60,chinese=70 where name='曹孟德';

3、将所有同学的数学成绩+30分

update exam_result set math=math+30;

这里可以配合上面介绍的各种方法来完成操作，大家自己尝试吧

四、删除

DELETE FROM table_name
[WHERE condition]         -- 过滤条件（必加！避免全量删除）
[ORDER BY column ASC/DESC] -- 可选：指定删除顺序
[LIMIT row_count];        -- 可选：限制删除行数

1、删除孙悟空同学的考试成绩

delete from exam_result where name='孙悟空';

2、删除整张表数据

准备测试表：

CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);

INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

delete from for_delete;

在这里插入图片描述

可以看到，当我们删除整张表达数据后，递增关键字并不会被重置。

补充：截断表TRUNCATE

TRUNCATE [TABLE] table_name

功能：彻底清空表中所有数据，保留表结构（列定义、索引、约束等）。

不同直接delete的是：

只能对整表操作，不能像 DELETE 一样可以针对部分数据操作；
实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE删除数据的时候，并不经过真正的事务，所以无法回滚。
会重置 AUTO_INCREMENT 项
并且不会记录日志 bin log

五、插入查询结果

在执行此类操作时我们需要保证，整个操作过程要么完全成功并生效，要么完全失败且不留下任何修改痕迹，绝对不允许出现 “

版权声明：程序员胖胖胖虎阿发表于 2025年9月14日下午3:23。
转载请注明：MySQL数据操作全解析：增删改查全方位实践 | 胖虎的工具箱-编程导航

基于Mybatis-Plus实现Geometry字段在PostGis空间数据库中的使用

程序员胖胖胖虎阿

362

JAVA对excle创建、读取、设置单元格颜色、背景色、跨行跨列

程序员胖胖胖虎阿

386

docker面试总结

程序员胖胖胖虎阿

311

Java常见加密算法：哈希算法加密、对称式加密与非对称式加密

程序员胖胖胖虎阿

372

序列图中角色与生命周期的可视化解析

程序员胖胖胖虎阿

127

flutter系列之:构建Widget的上下文环境BuildContext详解

程序员胖胖胖虎阿

341

暂无评论

暂无评论...

MySQL数据操作全解析：增删改查全方位实践

MySQL数据操作全面剖析：增删改查全方位实践

文章目录

前言

一、创建与数据插入

插入语法

1.1 单行数据与全列插入

1.2 多行数据与指定列插入

1.3 插入冲突时同步更新

1.4 冲突时替换

二、数据读取

2.1 全列查询

2.2 查询指定列

2.3 查询字段为表达式

2.4 结果去重

2.5 where条件筛选

2.6 order by语句（结果排序）

2.6 limit的运用（筛选分页结果）

三、更新修改

四、删除

五、插入查询结果

GoLand激活支持断网使用吗？附离线包下载！

Spring IOC容器：原理探微、源码深究与实践全面阐释

相关文章

暂无评论

JetBrains全家桶正版账号

官方正版 JetBrains 账号

2025最新Java面试题合集