MySQL之基本查询（二）（update || delete || 聚合函数 || group by）
目录
一、表的更新update
语法：
UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]使用实列：
~ 将孙悟空同学的数学成绩变更为 80 分
update exam_result set math=80 where name='孙悟空';原先分数：
更改后分数：
~ 将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分
mysql> update exam_result set math=60, chinese=70 where name='曹孟德';原先分数：
更改后分数：
~ 将总成绩倒数前三的 3 位同学的数学成绩加上 30 分
对于这个问题，我们可以先拿到总成绩倒数前三的3位同学的总成绩和数学成绩：
select name,math,chinese+math+english total from exam_result order by total limit 3;
上图显示出来的数据其实也是一张表，我们可以对该表进行数据修改：
update exam_result set math=math+30 order by chinese + math + english limit 3; 
注：如果没有筛选条件，update将进行整表更新。
二、表的删除delete
删除数据
语法：
DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]使用实例：
~ 删除孙悟空同学的考试成绩
mysql> delete from exam_result where name='孙悟空';删除前的数据：
删除后数据：
~ 删除总分第一名的同学的考试成绩
mysql> delete from exam_result order by chinese+math+english desc limit 1;原先数据：总分第一名是猪悟能，所以我们需要删除他的成绩。
删除后的数据：
删除表的所有数据
我们先创建一张用于测试的表：
mysql> CREATE TABLE for_delete (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(20)
-> );
然后插入测试数据：
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C'); 
然后删除表的所有数据，并查看：
mysql> delete from for_delete;
我们查看一下创建语句：
mysql> show create table for_delete\G; 
我们发现，auto_increment是4，那么如果我们再插入一条新的数据：自增 id 在原值上增长
INSERT INTO for_delete (name) VALUES ('D'); 
查看表结构，会有 AUTO_INCREMENT项：
所以，对于delete清空表数据的方式，其不会清空AUTO_INCREMENT的值。
截断表
语法：
TRUNCATE [TABLE] table_name；注：这个操作慎用，其特点如下，
1、只能对整表操作，不能像 DELETE 一样可以针对部分数据操作，即只能用于清空表的所有的数据。
2、实际上 TRUNCATE 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事务，所以无法回滚。
3、会重置 AUTO_INCREMENT 项。
我们先创建一个测试表：
CREATE TABLE for_truncate (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);
插入一些测试数据：
然后，查看一下该表的创建语句：auto_increment是4。
接着，我们截断整表数据，注意影响行数是 0，所以实际上没有对数据真正操作：
然后，查看一下该表截断后的创建语句：auto_increment已经被清空了。
然后，向表中插入一条新的数据：
再查看一下该表的创建语句：auto_increment是2。
注：delete和truncate都是对表中的数据进行操作。所以数据没了，但是表任然存在。
三、聚合函数
聚合函数可以对一组值执行计算并返回单一的值。
| 函数 | 说明 |
| COUNT([DISTINCT] expr) | 返回查询到的数据的数量 |
| SUM([DISTINCT] expr) | 返回查询到的数据的总和，不是数字没有意义 |
| AVG([DISTINCT] expr) | 返回查询到的数据的平均值，不是数字没有意义 |
| MAX([DISTINCT] expr) | 返回查询到的数据的最大值，不是数字没有意义 |
| MIN([DISTINCT] expr) | 返回查询到的数据的 最小值，不是数字没有意义 |
使用实例：
下面的所有操作和结果均来源下表：
~ 统计班级共有多少同学
mysql> select count(*) from exam_result;
~ 统计数学成绩总分
mysql> select sum(math) from exam_result;
~ 统计数学成绩平均分
mysql> select avg(math) from exam_result;
~ 返回英语成绩的最高分
mysql> select max(english) from exam_result;
~ 返回 < 70 分以下的数学成绩的最低分
mysql> select min(math) from exam_result where math<70;
四、group by 分组查询
在select中使用group by 子句可以对指定列进行分组查询。分组的目的是为了进行分组之后，方便进行聚合统计。
语法：
select column1, column2, .. from table group by column;使用实例
首先，创建一个雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
~ 显示每个部门的平均工资和最高工资
select deptno,avg(sal) 平均工资,max(sal) 最高工资 from emp group by deptno;
所以说，分组统计的本质，就是把一组按照条件拆成了多个组，然后进行各自组内的统计。即分组就是，把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。
~ 显示每个部门的每种岗位的平均工资和最低工资
首先，我们分析一下需求，我们需要的数据是平均工资和最低工资，这个可以使用函数avg和min，来实现。限制条件就是，我们需要根据部门和岗位进行分组，可以使用group by。
mysql> select deptno,job,avg(sal),min(sal) from emp group by deptno,job;
~ 显示平均工资低于2000的部门和它的平均工资
首先，我们分析一下需求，我们需要根据部门分组后，得到部门的平均工资。
mysql> select deptno,avg(sal) from emp group by deptno;
然后，我们需要根据分组聚合的结果，进行筛选，显示平均工资低于2000的部门和它的平均工资。
select deptno,avg(sal) mysal from emp group by deptno having mysal<2000;
注：其中，having是对聚合统计后的数据，进行条件筛选。
having 和 where
两者区别：条件筛选的阶段是不同的。
where——对具体的任意列进行条件筛选。
having——对分组聚合之后的数据结果进行条件筛选。
注：SQL查询中各个关键字的执行先后顺序，from > on> join > where > group by > with > having > select > distinct > order by > limit
原文地址：https://blog.csdn.net/zdlynj/article/details/140872286
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！