【MySQL】5. 数据更新与查询详解

目录一、数据更新 (UPDATE)1.1 基本语法1.2 复杂更新示例二、数据删除 (DELETE)2.1 DELETE 语句2.2 TRUNCATE 语句2.3 DELETE 与 TRUNCATE 区别三、去重操作3.1 使用 DISTINCT3.2 实际去重方法四、聚合函数4.1 常用聚合函数4.2 使用示例4.3 常见错误五、分组查询 (GROUP BY)5.1 基本分组六、HAVING 筛选6.1 基本用法6.2 WHERE 与 HAVING 的区别6.3 综合示例七、其他插入语法7.1 INSERT ... ON DUPLICATE KEY UPDATE7.2 REPLACE八、查询详解 (SELECT)8.1 表达式查询8.2 别名 (AS)8.3 WHERE 子句运算符8.4 执行顺序问题8.5 排序 (ORDER BY)8.6 分页显示 (LIMIT)一、数据更新 (UPDATE)1.1 基本语法-- 将表中所有行的 name 改为 a UPDATE t1 SET name a; -- 将 id 为 3 的行 name 改为 b UPDATE t1 SET name b WHERE id 3;1.2 复杂更新示例需求将成绩总分倒数前3名同学的数学分数加30分UPDATE exam_result SET math math 30 ORDER BY chinese math english ASC LIMIT 3;说明SET左侧为列名右侧可以是表达式。二、数据删除 (DELETE)2.1 DELETE 语句-- 删除名字为 a 的学生成绩记录 DELETE FROM exam_result WHERE name a;特点使用DELETE删除带有AUTO_INCREMENT的表后AUTO_INCREMENT值不会重置。-- 查看表结构auto_increment 值未变 SHOW CREATE TABLE t2 \G;2.2 TRUNCATE 语句TRUNCATE TABLE t2;特点删除AUTO_INCREMENT表后计数器重置为 0。2.3 DELETE 与 TRUNCATE 区别特性DELETETRUNCATE日志记录记录到日志不记录日志执行速度慢快回滚能力可以回滚不能回滚自增索引不重置重置三、去重操作3.1 使用 DISTINCTSELECT DISTINCT * FROM t5;注意DISTINCT仅用于查询显示不会实际删除表中的重复数据。3.2 实际去重方法-- 1. 创建空表插入去重数据 INSERT INTO t6 SELECT DISTINCT * FROM t5; -- 2. 重命名表 RENAME TABLE t5 TO no; RENAME TABLE t6 TO t5;四、聚合函数4.1 常用聚合函数函数作用COUNT计数SUM求和AVG平均值MAX最大值MIN最小值4.2 使用示例-- 统计总行数 SELECT COUNT(*) FROM exam_result; SELECT COUNT(1) FROM exam_result; -- 同样效果原理SELECT 1 FROM exam_result会生成一列全为 1 的临时表行数与exam_result相同。-- 计算总成绩平均分 SELECT AVG(math english chinese) AS 平均分 FROM exam_result;4.3 常见错误-- 错误示例name 是多行AVG 是单值无法一起显示 SELECT name, AVG(math english chinese) AS 平均分 FROM exam_result; -- 报错-- 正确查找大于70分的最低数学分数 SELECT MIN(math) FROM exam_result WHERE math 70;五、分组查询 (GROUP BY)5.1 基本分组需求查询每个部门、每个工作的平均薪资和最高薪资SELECT MAX(sal) AS 最高, AVG(sal) AS 平均 FROM emp GROUP BY deptno, job;显示分组字段SELECT deptno, job, MAX(sal) AS 最高, AVG(sal) AS 平均 FROM emp GROUP BY deptno, job;注意GROUP BY只使用了deptno和job两个属性因此SELECT中只能直接显示这两个字段其他字段会出现多对一问题。六、HAVING 筛选6.1 基本用法需求找出平均薪资小于 2000 的部门-- 步骤1统计各部门平均薪资 SELECT deptno, AVG(sal) AS 平均 FROM emp GROUP BY deptno; -- 步骤2筛选小于2000的部门 SELECT deptno, AVG(sal) AS 平均 FROM emp GROUP BY deptno HAVING 平均 2000;6.2 WHERE 与 HAVING 的区别关键字筛选时机适用场景WHERE原始数据过滤分组前对原始行进行条件筛选HAVING结果过滤分组后对聚合结果进行条件筛选6.3 综合示例需求员工 JONES 不参与统计统计平均薪资小于 2000 的部门SELECT deptno, job, MAX(sal) AS 最高, AVG(sal) AS 平均 FROM emp WHERE ename ! JONES -- 1. 原始数据筛选 GROUP BY deptno, job -- 2. 分组 HAVING 平均 2000; -- 3. 结果筛选执行顺序FROM emp— 选取表WHERE ename ! JONES— 原始数据筛选GROUP BY— 分组AVG、SUM等 — 聚合计算HAVING— 结果筛选核心理念MySQL 可以看作一切皆表每一个步骤的结果都可以视为一张表。七、其他插入语法7.1 INSERT ... ON DUPLICATE KEY UPDATE当插入时遇到主键或唯一键重复执行更新操作-- 如果 id1 存在则更新否则插入 INSERT INTO t1 (id, name) VALUES (1, 2) ON DUPLICATE KEY UPDATE id 3, name c;注意如果id3仍然重复照样插入失败。7.2 REPLACE-- 有重复时替换无重复时插入 REPLACE INTO t1 (id, name) VALUES (2, c);返回结果说明2 rows affected— 有冲突先删除后插入1 row affected— 无冲突直接插入八、查询详解 (SELECT)8.1 表达式查询SELECT 1 2; -- 可以查询表达式sql-- 查询指定列 SELECT name, chinese FROM exam_result;建议避免使用SELECT *因为数据库可能在远端服务器数据量大时会消耗大量带宽。8.2 别名 (AS)SELECT chinese math english AS sum FROM exam_result;-- 配合 DISTINCT 去重 SELECT DISTINCT chinese math english AS sum FROM exam_result;8.3 WHERE 子句运算符运算符说明普通相等NULL NULL返回NULL安全相等NULL NULL返回真!或不等于BETWEEN a AND b区间[a, b]IN (a0, a1, ...)值在列表中AND/OR/NOT逻辑运算LIKE模糊匹配%任意字符_单个字符使用示例-- 数学成绩接近满分97,98,99 SELECT name, math chinese english FROM exam_result WHERE math IN (97, 98, 99); -- 姓孙的学生 SELECT name, math chinese english FROM exam_result WHERE name LIKE 孙%; -- 姓孙且名字只有一个字 SELECT name, math chinese english FROM exam_result WHERE name LIKE 孙_;8.4 执行顺序问题-- 错误示例别名在 WHERE 中不可用 SELECT name, math chinese english AS sum FROM exam_result WHERE sum 300; -- 报错执行顺序FROM→WHERE→ 重命名AS→SELECT因此WHERE无法识别AS定义的别名必须使用原始表达式。-- 正确写法 SELECT name, math chinese english AS sum FROM exam_result WHERE math chinese english 300;8.5 排序 (ORDER BY)-- 多列排序未指定时行为未定义建议明确 SELECT name, math, english, chinese FROM exam_result ORDER BY math DESC, english DESC, chinese DESC; -- 使用别名排序ORDER BY 优先级在别名之后 SELECT name, math english chinese AS sum FROM exam_result ORDER BY sum DESC;DESC— 降序ASC— 升序默认8.6 分页显示 (LIMIT)-- 显示前3行 SELECT name, math english chinese AS sum FROM exam_result ORDER BY sum DESC LIMIT 3; -- 从索引2第3行开始显示3行 SELECT name, math english chinese AS sum FROM exam_result ORDER BY sum DESC LIMIT 2, 3;优先级LIMIT在所有数据处理完成后最后执行属于临门一脚的显示操作。