企业官网建设流程全解析

MySQL CRUD 实操｜Create | Retrieve(查) | where|新增｜查询｜更新｜删除｜聚合函数｜分组查询

一、Markmap 思维导图

二、准备练习环境

2.1 创建数据库和表

createdatabaseifnotexistscrud_demodefaultcharactersetutf8mb4;usecrud_demo;createtablestudent_score(idbigintprimarykeyauto_incrementcomment'记录编号',student_novarchar(20)notnulluniquecomment'学号',student_namevarchar(30)notnullcomment'姓名',class_namevarchar(30)notnullcomment'班级',subjectvarchar(30)notnullcomment'科目',scoredecimal(5,2)notnulldefault0.00comment'成绩',created_atdatetimenotnulldefaultcurrent_timestampcomment'创建时间')engine=innodbdefaultcharset=utf8mb4comment='学生成绩表';

执行效果：

Query OK, 0 rows affected

• student_no使用唯一键，防止学号重复。
• score使用decimal保存精确数值。
• id和created_at由数据库自动生成。
• 后续示例都围绕student_score表展开。

三、新增数据 create

3.1 单行全列插入

insertintostudent_scorevalues(null,'s001','张三','java一班','mysql',88.50,default);

• values后的数据顺序必须与表中字段顺序一致。
• 自增长主键可以填写null，默认字段可以写default。
• 全列插入依赖字段顺序，表结构变化后较容易出错。
• 实际开发更推荐指定列插入。

3.2 单行指定列插入

insertintostudent_score(student_no,student_name,class_name,subject,score)values('s002','李四','java一班','mysql',92.00);

执行效果：

Query OK, 1 row affected

• 字段名与值必须一一对应。
• 没有列出的id和created_at使用自动值。
• 指定列插入更清晰，也更容易适应表结构变化。

3.3 多行数据插入

insertintostudent_score(student_no,student_name,class_name,subject,score)values('s003','王五','java二班','mysql',76.50),('s004','赵六','java二班','mysql',85.00),('s005','小明','java一班','java',95.50),('s006','小红','java二班','java',89.00),('s007','小刚','java一班','linux',68.00),('s008','小丽','java二班','linux',91.50);

执行效果：

Query OK, 6 rows affected Records: 6 Duplicates: 0 Warnings: 0

• 多组值之间使用英文逗号分隔。
• 一条语句插入多行通常比逐行插入效率更高。
• 所有值组的字段数量和顺序必须一致。

3.4 插入或更新

当唯一键发生冲突时，可以更新已有记录：

insertintostudent_score(student_no,student_name,class_name,subject,score)values('s001','张三','java一班','mysql',90.00)asnewonduplicatekeyupdatescore=new.score;

• student_no = 's001'已存在，因此不会新增重复记录。
• on duplicate key update会转而更新指定字段。
• as new是 MySQL 8.0.19 及以上推荐的行别名写法。
• 使用旧版本 MySQL 时，需要根据版本调整语法。

3.5 替换记录 replace

replaceintostudent_score(student_no,student_name,class_name,subject,score)values('s002','李四','java一班','mysql',94.00);

• replace遇到主键或唯一键冲突时，通常会先删除旧行再插入新行。
• 未提供的字段会重新使用默认值，原有字段值可能丢失。
• 自增长主键、外键和触发器会使行为更复杂。
• 普通业务更新优先使用update，不要把replace当作通用更新方式。

四、基础查询 retrieve

4.1 查询全部列

select*fromstudent_score;

• *表示查询表中的全部字段。
• 学习和临时检查时使用方便。
• 正式业务代码建议明确列名，减少不必要的数据读取。

4.2 查询指定列

selectstudent_no,student_name,subject,scorefromstudent_score;

执行效果示例：

• 只查询当前页面需要的字段。
• 查询结果中的列顺序与select后的书写顺序一致。

4.3 使用别名和表达式

selectstudent_nameas姓名,subjectas科目,scoreas原成绩,score+5as模拟加分后成绩fromstudent_score;

• as可以给字段或表达式设置结果列名。
• 表达式只影响查询结果，不会修改表中的原数据。
• 中文别名适合演示，程序接口通常使用稳定的英文别名。

4.4 查询不重复值

selectdistinctclass_name,subjectfromstudent_score;

• distinct去除查询结果中的重复组合。
• 同时查询多列时，所有列值都相同才算重复。
• distinct可能增加排序或去重开销，不应无目的使用。

五、where 条件查询

5.1 比较运算

查询成绩大于等于 90 分的学生：

selectstudent_name,subject,scorefromstudent_scorewherescore>=90;

执行效果示例：

• 常用比较符包括=、<>、!=、>、>=、<和<=。
• 字符串和日期值通常需要使用单引号包裹。

5.2 and、or 与 not

selectstudent_name,class_name,subject,scorefromstudent_scorewhereclass_name='java一班'andscore>=85;

• and要求多个条件同时成立。
• or要求至少一个条件成立。
• not用于对条件取反。
• and的优先级高于or，复杂条件建议使用括号明确含义。

5.3 between 与 in

selectstudent_name,subject,scorefromstudent_scorewherescorebetween80and90andsubjectin('mysql','java');

• between 80 and 90包含边界值80和90。
• in适合判断字段是否属于一组候选值。
• 相同字段的多个or条件通常可以改写为in。

5.4 like 模糊查询

selectstudent_no,student_namefromstudent_scorewherestudent_namelike'小%';

• %可以匹配任意数量的字符，包括零个字符。
• _只能匹配一个字符。
• 前缀匹配如'小%'通常比'%小%'更容易利用索引。

5.5 判断空值

select*fromstudent_scorewherecreated_atisnotnull;

• 判断空值应使用is null或is not null。
• 不能使用= null判断空值。
• null表示未知值，不等于空字符串或数字0。

六、结果排序 order by

selectstudent_name,class_name,subject,scorefromstudent_scoreorderbyscoredesc,idasc;

执行效果：

• 首先按成绩从高到低排列。
• 成绩相同时，再按id从小到大排列。
• asc表示升序，也是默认排序方式。
• desc表示降序。
• 没有使用order by时，数据库不保证结果顺序。

七、分页查询 limit

7.1 查询第一页

selectstudent_no,student_name,scorefromstudent_scoreorderbyidlimit0,3;

7.2 查询第二页

selectstudent_no,student_name,scorefromstudent_scoreorderbyidlimit3,3;

• limit 偏移量, 每页数量用于分页。
• 偏移量从0开始。
• 每页 3 条时，第n页偏移量为(n - 1) * 3。
• 分页查询应搭配稳定的order by，否则可能出现重复或遗漏。
• 数据量很大时，深分页应考虑使用主键范围查询。

八、select 关键字书写与执行顺序

常见书写顺序：

select查询列from表名where行筛选条件groupby分组字段having分组筛选条件orderby排序字段limit偏移量,数量;

可以帮助理解结果的逻辑执行顺序：

from → where → group by → having → select → order by → limit

• SQL 必须按照固定的语法顺序书写。
• where在分组前筛选原始记录。
• having在分组后筛选统计结果。
• order by对结果排序，limit最后截取数据。
• 数据库优化器可能调整物理执行方案，但不会改变查询语义。

九、更新数据 update

9.1 更新单个字段

updatestudent_scoresetscore=80.00wherestudent_no='s003';

执行效果：

Query OK, 1 row affected Rows matched: 1 Changed: 1 Warnings: 0

• set后面指定新的字段值。
• where决定哪些记录会被修改。
• 更新前建议先用相同的where条件执行一次select。

9.2 同时更新多个字段

updatestudent_scoresetclass_name='java进阶班',score=96.00wherestudent_no='s005';

• 多个字段赋值之间使用英文逗号分隔。
• 一条语句可以同时修改一行中的多个字段。
• 唯一键字段更新后仍需满足唯一性要求。

9.3 根据原值计算更新

updatestudent_scoresetscore=least(score+2,100)wheresubject='mysql';

• score + 2基于当前成绩计算新值。
• least(..., 100)保证结果不超过 100。
• 该语句会更新所有符合条件的记录，不只是一行。

9.4 忘记 where 的风险

updatestudent_scoresetscore=0;

• 该语句会把整张表的成绩全部修改为0。
• 示例仅用于说明风险，请不要在现有练习数据上执行。
• 重要更新应在事务中进行，并先备份或确认影响行数。

十、删除数据 delete

10.1 删除指定记录

deletefromstudent_scorewherestudent_no='s008';

执行效果：

Query OK, 1 row affected

• delete删除符合条件的整行数据。
• 删除前应使用相同条件执行select，确认目标记录。
• 删除操作不能只删除一行中的某个字段；清空字段应使用update。

10.2 删除全部记录

deletefromstudent_score;

• 没有where时会删除表中的全部记录。
• 表结构、索引和约束仍然保留。
• InnoDB 通常会逐行记录删除过程，事务未提交前可以回滚。
• 示例仅用于语法展示，不要在当前练习中直接执行。

十一、截断表 truncate

truncatetablestudent_score;

• truncate快速清空整张表，但保留表结构。
• 自增长计数器通常会被重置。
• 它属于数据定义操作，不能添加where条件。
• 与delete的事务、触发器和日志行为不同，使用前必须确认环境。
• 示例仅用于说明，请完成其他练习后再测试。

11.1 delete 与 truncate 对比

• 两种操作都具有破坏性。
• 日志细节与 MySQL 版本、存储引擎和复制设置有关。
• 生产环境清空数据前必须完成备份并确认影响范围。

十二、事务保护实验数据

在测试更新或删除时，可以使用事务：

starttransaction;updatestudent_scoresetscore=score+1whereclass_name='java一班';select*fromstudent_scorewhereclass_name='java一班';rollback;

• start transaction开启事务。
• 在提交前可以检查修改后的效果。
• rollback撤销当前事务中的修改。
• 确认结果正确后，可用commit代替rollback正式提交。
• truncate等数据定义语句可能隐式提交事务，不能依赖该方法撤销。

十三、聚合函数

聚合函数对多行数据进行统计，并返回一个汇总结果。

13.1 count 统计数量

selectcount(*)as学生记录数fromstudent_score;

• count(*)统计结果中的所有记录。
• count(字段名)只统计该字段不为null的记录。
• 判断记录总数时通常优先使用count(*)。

13.2 sum 求和

selectsum(score)as成绩总和fromstudent_scorewheresubject='mysql';

• sum用于数值求和。
• where会先筛选 MySQL 科目记录，再进行求和。
• 没有匹配记录时，聚合结果可能为null。

13.3 avg 求平均值

selectround(avg(score),2)as平均成绩fromstudent_score;

• avg计算非空值的平均数。
• round(..., 2)将结果保留两位小数。
• 聚合函数通常会忽略null值。

13.4 max 和 min

selectmax(score)as最高分,min(score)as最低分fromstudent_score;

执行效果示例：

• max返回最大值。
• min返回最小值。
• 两者也可以用于日期和可比较的字符串字段。

十四、分组查询 group by

14.1 按科目分组

selectsubject,count(*)as人数,round(avg(score),2)as平均分,max(score)as最高分,min(score)as最低分fromstudent_scoregroupbysubject;

执行效果示例：

• group by subject将相同科目的记录放入同一组。
• 聚合函数分别对每个分组进行计算。
• 查询列通常应是分组字段或聚合表达式。
• 表格结果按顺序执行前文更新与指定记录删除示例计算。
• 如果跳过某个修改步骤，统计结果会相应变化。

14.2 多字段分组

selectclass_name,subject,count(*)as人数,round(avg(score),2)as平均分fromstudent_scoregroupbyclass_name,subjectorderbyclass_name,subject;

• 先按照班级和科目的组合进行分组。
• 班级相同但科目不同的记录属于不同分组。
• 多字段分组适合生成多维度统计结果。

十五、having 分组筛选

查询平均分大于等于 85 分的科目：

selectsubject,count(*)as人数,round(avg(score),2)as平均分fromstudent_scoregroupbysubjecthavingavg(score)>=85orderby平均分desc;

• having在分组和聚合之后筛选结果。
• 聚合条件如avg(score) >= 85应使用having。
• where负责筛选原始行，不能直接筛选聚合结果。

15.1 where 与 having 同时使用

selectclass_name,count(*)as优秀人数,round(avg(score),2)as优秀学生平均分fromstudent_scorewherescore>=85groupbyclass_namehavingcount(*)>=2orderby优秀学生平均分desc;

• where score >= 85先留下成绩不低于 85 分的原始记录。
• group by class_name再按照班级分组。
• having count(*) >= 2只保留至少有两名优秀学生的班级。
• order by最后对统计结果排序。

十六、综合查询练习

需求：统计每个班级的 MySQL 成绩，只显示人数不少于 2 的班级，并按照平均分从高到低排列。

selectclass_name,count(*)as人数,round(avg(score),2)as平均分,max(score)as最高分,min(score)as最低分fromstudent_scorewheresubject='mysql'groupbyclass_namehavingcount(*)>=2orderby平均分desc;

• where先筛选 MySQL 科目的成绩。
• group by按班级形成多个分组。
• 聚合函数计算每个班级的统计值。
• having删除人数不足 2 的分组。
• order by按平均分降序展示最终结果。

十七、常见错误与注意事项

17.1 字符串没有加引号

错误示例：

select*fromstudent_scorewherestudent_name=张三;

正确示例：

select*fromstudent_scorewherestudent_name='张三';

• 字符串和日期通常要使用英文单引号包裹。
• 字段名和关键字不应使用字符串引号。

17.2 update 或 delete 忘记 where

• 没有where会影响整张表的所有记录。
• 执行前先把语句改成select检查目标范围。
• 重要操作应开启事务，并核对受影响行数。
• 不确定时不要提交事务。

17.3 where 中使用聚合函数

错误示例：

selectsubject,avg(score)fromstudent_scorewhereavg(score)>=85groupbysubject;

正确示例：

selectsubject,avg(score)fromstudent_scoregroupbysubjecthavingavg(score)>=85;

• where执行时还没有形成分组统计结果。
• 筛选聚合结果应使用having。

十八、效果图插入方法

在 CSDN 上传执行截图后，把生成的图片地址放在对应示例之后：

![多表查询执行效果](https://你的图片地址.png)

• 图片应紧跟相关 SQL 示例，避免读者来回寻找。
• 截图建议包含执行语句、查询结果或完整报错信息。
• 图片说明要明确，例如“where 条件查询结果”。
• 发布前隐藏账号、密码、服务器地址等敏感内容。
• 代码块背景由 CSDN 主题控制，选择浅色主题即可显示白色或灰色背景。

十九、实操检查清单

• 创建数据库和学生成绩表。
• 分别完成单行插入、指定列插入和多行插入。
• 练习指定列、条件、模糊匹配、排序和分页查询。
• 使用事务练习更新和删除，并通过rollback恢复数据。
• 对比delete与truncate的区别。
• 使用五个常见聚合函数统计成绩。
• 使用group by和having完成分组筛选。

总结

• insert负责新增数据，推荐明确指定插入列。
• select配合where、order by和limit完成筛选、排序与分页。
• update和delete必须谨慎检查where条件。
• truncate用于快速清空表，与普通删除的行为不同。
• 聚合函数负责统计，group by负责分组，having负责筛选分组结果。
• 按顺序实际执行成功案例和错误案例，能够更直观地理解 CRUD。