MySQL CRUD 查詢(xún)、插入、更新、刪除全實(shí)戰(zhàn)指南

更新時(shí)間：2026年03月18日 10:47:33 作者：草莓熊Lotso

在 MySQL日常開(kāi)發(fā)中CRUD是最核心的高頻操作,本文基于實(shí)戰(zhàn)場(chǎng)景,全面拆解MySQL的增刪改查操作,所有SQL語(yǔ)句均采用小寫(xiě)形式,貼合實(shí)際開(kāi)發(fā)規(guī)范,同時(shí)涵蓋聚合查詢(xún)、分組統(tǒng)計(jì)等進(jìn)階內(nèi)容,感興趣的朋友跟隨小編一起看看吧

前言：

在 MySQL 日常開(kāi)發(fā)中，CRUD（create/retrieve/update/delete）是最核心的高頻操作。掌握規(guī)范的 CRUD 語(yǔ)法、靈活的查詢(xún)技巧和避坑要點(diǎn)，能大幅提升開(kāi)發(fā)效率和 SQL 可讀性。本文基于實(shí)戰(zhàn)場(chǎng)景，全面拆解 MySQL 的增刪改查操作，所有 SQL 語(yǔ)句均采用小寫(xiě)形式，貼合實(shí)際開(kāi)發(fā)規(guī)范，同時(shí)涵蓋聚合查詢(xún)、分組統(tǒng)計(jì)等進(jìn)階內(nèi)容。

一. 基礎(chǔ)準(zhǔn)備：創(chuàng)建測(cè)試表與測(cè)試數(shù)據(jù)

為了讓所有示例更直觀，先創(chuàng)建兩張測(cè)試表并插入測(cè)試數(shù)據(jù)，后續(xù)操作均基于這兩張表：

語(yǔ)法：

INSERT [INTO] table_name
	[(column [, column] ...)]
	VALUES (value_list) [, (value_list)] ...
value_list: value, [, value] ...

1.1 學(xué)生表（students）

create table students (
  id int unsigned primary key auto_increment,
  sn int not null unique comment '學(xué)號(hào)',
  name varchar(20) not null,
  qq varchar(20)
);
-- 插入測(cè)試數(shù)據(jù)
insert into students values 
(100, 10000, '唐三藏', null),
(101, 10001, '孫悟空', '11111'),
(102, 20001, '曹孟德', null),
(103, 20002, '孫仲謀', null);

1.2 考試成績(jī)表（exam_result）

create table exam_result (
  id int unsigned primary key auto_increment,
  name varchar(20) not null comment '同學(xué)姓名',
  chinese float default 0.0 comment '語(yǔ)文成績(jī)',
  math float default 0.0 comment '數(shù)學(xué)成績(jī)',
  english float default 0.0 comment '英語(yǔ)成績(jī)'
);
-- 插入測(cè)試數(shù)據(jù)
insert into exam_result (name, chinese, math, english) values 
('唐三藏', 67, 98, 56),
('孫悟空', 87, 78, 77),
('豬悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('劉玄德', 55, 85, 45),
('孫權(quán)', 70, 73, 78),
('宋公明', 75, 65, 30);

二. Create（插入數(shù)據(jù)）

插入數(shù)據(jù)核心是insert語(yǔ)句，支持單行 / 多行插入、指定列插入、沖突處理等場(chǎng)景。

2.1 單行全列插入

插入數(shù)據(jù)需與表結(jié)構(gòu)的列數(shù)和順序完全一致（自增主鍵可省略，自動(dòng)生成）：

-- 全列插入（指定id）
insert into students values (104, 20003, '魯智深', '22222');
-- 省略自增主鍵（自動(dòng)生成id）
insert into students (sn, name, qq) values (20004, '林沖', '33333');

2.2 多行指定列插入

一次插入多條數(shù)據(jù)，僅指定需要賦值的列，未指定列使用默認(rèn)值或null：

insert into students (sn, name) values 
(20005, '武松'),
(20006, '楊志');

2.3 插入沖突處理（on duplicate key update）

當(dāng)主鍵或唯一鍵沖突時(shí)，不報(bào)錯(cuò)而是執(zhí)行更新操作：

-- 主鍵沖突（id=100已存在），執(zhí)行更新
insert into students (id, sn, name) values (100, 10010, '唐大師')
on duplicate key update sn = 10010, name = '唐大師'; // 同步更新語(yǔ)法
-- 唯一鍵沖突（sn=20001已存在），執(zhí)行更新
insert into students (sn, name) values (20001, '曹阿瞞')
on duplicate key update name = '曹阿瞞';

2.4 替換插入（replace into）

主鍵或唯一鍵沖突時(shí)，刪除原記錄后重新插入：

-- sn=20002已存在，刪除原記錄后插入新數(shù)據(jù)
replace into students (sn, name) values (20002, '孫伯符');

2.5 插入查詢(xún)結(jié)果

將一張表的查詢(xún)結(jié)果插入另一張表（常用于數(shù)據(jù)遷移、去重）：

-- 創(chuàng)建空表（結(jié)構(gòu)與students一致）
create table students_copy like students;
-- 將students的去重?cái)?shù)據(jù)插入新表
insert into students_copy select distinct * from students;

三. Retrieve（查詢(xún)數(shù)據(jù)）

查詢(xún)是 CRUD 中最復(fù)雜的操作，支持全列查詢(xún)、條件查詢(xún)、排序、分頁(yè)、聚合等功能，核心語(yǔ)法：

select 
  [distinct] {* | column [, column] ...}
from table_name
[where ...]
[order by column [asc | desc], ...]
limit ...

3.1 基礎(chǔ)查詢(xún)

3.1.1 全列查詢(xún)（不推薦）

-- 全列查詢(xún)（數(shù)據(jù)量大時(shí)性能差，不建議在生產(chǎn)環(huán)境使用）
-- 1. 查詢(xún)的列越多，意味著需要傳輸?shù)臄?shù)據(jù)量越大
-- 2. 可能會(huì)影響到索引的使用。(索引待后面我們?cè)龠M(jìn)行理解)
select * from exam_result;

3.1.2 指定列查詢(xún)

按需查詢(xún)列，順序可與表結(jié)構(gòu)不一致：

-- 查詢(xún)姓名、語(yǔ)文、數(shù)學(xué)成績(jī)
select name, chinese, math from exam_result;

3.1.3 查詢(xún)表達(dá)式

支持常量、單字段運(yùn)算、多字段運(yùn)算：

-- 常量表達(dá)式
select id, name, 10 from exam_result;
-- 單字段運(yùn)算（英語(yǔ)成績(jī)+10）
select id, name, english + 10 from exam_result;
-- 多字段運(yùn)算（總分）
select id, name, chinese + math + english from exam_result;

3.1.4 結(jié)果別名（as 可省略）

給查詢(xún)結(jié)果列指定別名，增強(qiáng)可讀性：

select 
  id, 
  name, 
  chinese + math + english as 總分 
from exam_result;

3.1.5 結(jié)果去重（distinct）

去除查詢(xún)結(jié)果中的重復(fù)記錄：

-- 去重查詢(xún)數(shù)學(xué)成績(jī) distinct
select distinct math from exam_result;

3.2 條件查詢(xún)（where）

通過(guò)比較運(yùn)算符和邏輯運(yùn)算符篩選數(shù)據(jù)，支持多種條件組合。

3.2.1 比較運(yùn)算符

以下是您提供的運(yùn)算符說(shuō)明表格：

運(yùn)算符	說(shuō)明
>, >=, <, <=	大于、大于等于、小于、小于等于
=	等于（null 不安全，null = null 結(jié)果為 null）
<=>	等于（null 安全，null <=> null 結(jié)果為 1）
!=, <>	不等于
between a and b	范圍匹配（[a, b]）
in (option...)	匹配選項(xiàng)中的任意一個(gè)
is null	為空
is not null	不為空
like	模糊匹配（% 匹配任意字符，_ 匹配單個(gè)字符）

3.2.2 邏輯運(yùn)算符

運(yùn)算符	說(shuō)明
and	多個(gè)條件同時(shí)成立
or	任意一個(gè)條件成立
not	條件取反

3.2.3 條件查詢(xún)示例

-- 1. 英語(yǔ)不及格（<60）
select name, english from exam_result where english < 60;
-- 2. 語(yǔ)文成績(jī)?cè)赱80, 90]之間（between...and）
select name, chinese from exam_result where chinese between 80 and 90;
-- 3. 數(shù)學(xué)成績(jī)是58、59、98、99中的一個(gè)（in）
select name, math from exam_result where math in (58, 59, 98, 99);
-- 4. 姓孫的同學(xué)（like %）
select name from exam_result where name like '孫%';
-- 5. 姓名是兩個(gè)字且姓孫（like _）
select name from exam_result where name like '孫_';
-- 6. 語(yǔ)文成績(jī)好于英語(yǔ)成績(jī)
select name, chinese, english from exam_result where chinese > english;
-- 7. 總分低于200分（表達(dá)式作為條件）
select name, chinese + math + english as 總分 
from exam_result 
where chinese + math + english < 200; // 不能直接用總分 < 20,因?yàn)閳?zhí)行這里的時(shí)候還沒(méi)執(zhí)行前面的部分
-- 8. 語(yǔ)文>80且不姓孫（and + not）
select name, chinese from exam_result where chinese > 80 and name not like '孫%';
-- 9. qq號(hào)不為空（is not null）
select name, qq from students where qq is not null;
-- 10. null安全比較（<=>）
select name, qq from students where qq <=> null;

3.3 結(jié)果排序（order by）

默認(rèn)升序（asc），可指定降序（desc），支持多字段排序。

-- 1. 按數(shù)學(xué)成績(jī)升序
select name, math from exam_result order by math;
-- 2. 按數(shù)學(xué)降序，英語(yǔ)升序，語(yǔ)文升序
select name, math, english, chinese 
from exam_result 
order by math desc, english, chinese;
-- 3. 按總分降序（表達(dá)式排序）
select name, chinese + math + english as 總分 
from exam_result 
order by 總分 desc;
-- 4. null排序（null視為最小值，升序在最前）
select name, qq from students order by qq;
select name, qq from students order by qq desc;

3.4 分頁(yè)查詢(xún)（limit）

限制查詢(xún)結(jié)果數(shù)量，避免數(shù)據(jù)量過(guò)大導(dǎo)致性能問(wèn)題，起始下標(biāo)從 0 開(kāi)始。

-- 語(yǔ)法1：limit 條數(shù)（取前n條）
select * from exam_result order by id limit 3;
-- 語(yǔ)法2：limit 起始下標(biāo), 條數(shù)（從s(下標(biāo)從0開(kāi)始)開(kāi)始取n條）
select * from exam_result order by id limit 3, 3;
-- 語(yǔ)法3：limit 條數(shù) offset 起始下標(biāo)（推薦，更清晰）
select * from exam_result order by id limit 3 offset 6;
-- 分頁(yè)示例：每頁(yè)3條，第1-3頁(yè)
select * from exam_result order by id limit 3 offset 0; -- 第1頁(yè)
select * from exam_result order by id limit 3 offset 3; -- 第2頁(yè)
select * from exam_result order by id limit 3 offset 6; -- 第3頁(yè)

3.5 聚合查詢(xún)（聚合函數(shù)）

對(duì)查詢(xún)結(jié)果進(jìn)行統(tǒng)計(jì)計(jì)算，常用聚合函數(shù)如下：

函數(shù)	說(shuō)明
count([distinct] expr)	統(tǒng)計(jì)記錄數(shù)（distinct 去重）
sum([distinct] expr)	求和（僅數(shù)字類(lèi)型）
avg([distinct] expr)	求平均值（僅數(shù)字類(lèi)型）
max([distinct] expr)	求最大值（僅數(shù)字類(lèi)型）
min([distinct] expr)	求最小值（僅數(shù)字類(lèi)型）

聚合查詢(xún)示例：

-- 1. 統(tǒng)計(jì)學(xué)生總數(shù)（count(*)不受null影響）
select count(*) as 學(xué)生總數(shù) from students;
-- 2. 統(tǒng)計(jì)qq號(hào)非空的學(xué)生數(shù)（null不計(jì)入）
select count(qq) as qq已收集人數(shù) from students;
-- 3. 統(tǒng)計(jì)數(shù)學(xué)成績(jī)總分和去重后總分
select sum(math) as 數(shù)學(xué)總分, sum(distinct math) as 去重?cái)?shù)學(xué)總分 from exam_result;
-- 4. 統(tǒng)計(jì)語(yǔ)文成績(jī)平均分
select avg(chinese) as 語(yǔ)文平均分 from exam_result;
-- 5. 英語(yǔ)最高分和最低分
select max(english) as 英語(yǔ)最高分, min(english) as 英語(yǔ)最低分 from exam_result;
-- 6. 統(tǒng)計(jì)70分以上的數(shù)學(xué)最低分
select min(math) as 70+數(shù)學(xué)最低分 from exam_result where math > 70;

3.6 分組查詢(xún)（group by + having）

group by按指定列分組，having篩選分組結(jié)果（類(lèi)似where，但作用于分組）。

-- 準(zhǔn)備雇員表（經(jīng)典測(cè)試表）
create table emp (
  empno int primary key,
  ename varchar(20),
  job varchar(20),
  deptno int,
  sal float
);
insert into emp values
(7369, 'smith', 'clerk', 20, 800),
(7499, 'allen', 'salesman', 30, 1600),
(7521, 'ward', 'salesman', 30, 1250),
(7566, 'jones', 'manager', 20, 2975),
(7654, 'martin', 'salesman', 30, 1250),
(7698, 'blake', 'manager', 30, 2850),
(7782, 'clark', 'manager', 10, 2450),
(7788, 'scott', 'analyst', 20, 3000);
-- 1. 按部門(mén)分組，統(tǒng)計(jì)每個(gè)部門(mén)的平均工資和最高工資
select deptno, avg(sal) as 平均工資, max(sal) as 最高工資 from emp group by deptno;
-- 2. 按部門(mén)和崗位分組，統(tǒng)計(jì)平均工資和最低工資
select deptno, job, avg(sal) as 平均工資, min(sal) as 最低工資 from emp group by deptno, job;
-- 3. 篩選平均工資低于2000的部門(mén)（having篩選分組結(jié)果）
select deptno, avg(sal) as 平均工資 from emp group by deptno having avg(sal) < 2000;

四. Update（更新數(shù)據(jù)）

修改表中已有數(shù)據(jù)，支持單字段、多字段更新，結(jié)合where、order by、limit精準(zhǔn)控制更新范圍。

語(yǔ)法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

-- 1. 更新單字段（孫悟空數(shù)學(xué)成績(jī)改為80）
update exam_result set math = 80 where name = '孫悟空';
-- 2. 更新多字段（曹孟德數(shù)學(xué)60、語(yǔ)文70）
update exam_result set math = 60, chinese = 70 where name = '曹孟德';
-- 3. 按表達(dá)式更新（所有同學(xué)語(yǔ)文成績(jī)翻倍）
update exam_result set chinese = chinese * 2;
-- 4. 結(jié)合排序和limit（總分倒數(shù)前三的同學(xué)數(shù)學(xué)+30）
update exam_result 
set math = math + 30 
order by chinese + math + english 
limit 3;
-- 5. 條件更新（英語(yǔ)<60的同學(xué)英語(yǔ)+10）
update exam_result set english = english + 10 where english < 60;

警告：無(wú)where子句會(huì)更新全表數(shù)據(jù)，生產(chǎn)環(huán)境需謹(jǐn)慎

五. Delete（刪除數(shù)據(jù)）

刪除表中數(shù)據(jù)，支持條件刪除、全表刪除，還有高效的truncate截?cái)啾怼?/p>

5.1 條件刪除

-- 1. 刪除孫悟空的考試成績(jī)
delete from exam_result where name = '孫悟空';
-- 2. 刪除英語(yǔ)<60的同學(xué)成績(jī)
delete from exam_result where english < 60;

5.2 全表刪除（delete）

-- 刪除for_delete表所有數(shù)據(jù)（自增id不重置）
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;
-- 插入新數(shù)據(jù)，自增id從4開(kāi)始
insert into for_delete (name) values ('d');
select * from for_delete; -- id=4

5.3 截?cái)啾恚╰runcate）

快速刪除全表數(shù)據(jù)，重置自增 id，比delete更高效（不記錄事務(wù)）：

-- 截?cái)啾恚ㄗ栽鰅d重置為1）
create table for_truncate (
  id int primary key auto_increment,
  name varchar(20)
);
insert into for_truncate (name) values ('a'), ('b'), ('c');
truncate for_truncate;
-- 插入新數(shù)據(jù)，自增id從1開(kāi)始
insert into for_truncate (name) values ('d');
select * from for_truncate; -- id=1

區(qū)別：delete是逐行刪除（可回滾），truncate直接重置表（不可回滾），效率更高。

六. SQL 執(zhí)行順序與避坑指南

6.1 SQL 關(guān)鍵字執(zhí)行順序

from → on → join → where → group by → with → having → select → distinct → order by → limit

別名不能在where中使用（select在where之后執(zhí)行）；
having用于篩選分組結(jié)果（group by之后執(zhí)行），where用于篩選行數(shù)據(jù)（group by之前執(zhí)行）。

6.2 避坑要點(diǎn)和總結(jié)

避免全列查詢(xún)：僅查詢(xún)需要的列，減少數(shù)據(jù)傳輸和內(nèi)存占用；
null判斷用is null/is not null：=和!=對(duì)null無(wú)效；
更新 / 刪除必加where：防止誤操作全表數(shù)據(jù)；
分頁(yè)查詢(xún)必加order by：避免分頁(yè)結(jié)果混亂；
聚合函數(shù)忽略null：count(qq)不計(jì)入qq為null的記錄；
truncate不可回滾：刪除全表數(shù)據(jù)優(yōu)先考慮delete（需回滾）或truncate（高效）。

總結(jié)： MySQL CRUD 是數(shù)據(jù)庫(kù)開(kāi)發(fā)的基礎(chǔ)，核心要點(diǎn)：

插入數(shù)據(jù)支持單行 / 多行、沖突處理、查詢(xún)結(jié)果插入；
查詢(xún)是核心，靈活組合where、order by、limit、聚合函數(shù)、分組查詢(xún)滿(mǎn)足復(fù)雜需求；
更新 / 刪除需精準(zhǔn)控制范圍，避免全表操作；
遵循 SQL 執(zhí)行順序，避開(kāi)null判斷、別名使用等常見(jiàn)坑。

到此這篇關(guān)于MySQL CRUD 查詢(xún)、插入、更新、刪除全實(shí)戰(zhàn)指南的文章就介紹到這了,更多相關(guān)mysql crud 查詢(xún) 插入更新刪除內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

最新国产好看的视频,伊人天堂AV在线,国产Aaaaaa视频,蜜臀视频在线观看一区,人妻av色图,密臀久久久精品影片,青青视频免费观看毛片,久草在线观看视,国产三级精品色情在线

MySQL CRUD 查詢(xún)、插入、更新、刪除全實(shí)戰(zhàn)指南

目錄

前言：