【半夜学习MySQL】表的增删改查操作[下篇]（含update、delete、truncate、插入查询结果、聚合函数、group by与having子句详谈）

🏠关于专栏：半夜学习MySQL专栏用于记录MySQL数据相关内容。
🎯每天努力一点点，技术变化看得见

---

Update（更新）

语法：

update table_name set column=expr [,column=expr] [where ...] [order by ...] [limit ...];

update用于对查询到的结果进行列值的更新，详细用法将在下述案例中说明↓↓↓

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

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

案例2： 将曹孟德同学的数学成绩变更为60分，语文成绩变更为60分

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

案例3： 将总分倒数前三的3位同学的数学成绩加上30分

select name, math, chinese+math+english as sum from exam_result order by sum limit 3;

update exam_result set math=math+30 order by chinese+math+english limit 3;

select name, math, chinese+math+english as sum from exam_result where name in ('宋公明', '刘玄德', '曹孟德');

案例4： 将所有同学的语文成绩更新为原来的2倍

select * from exam_result;

update exam_result set chinese=chinese*2;

select * from exam_result;

Delete（删除）

删除数据

语法：

delete from table_name [where ...] [order by ...] [limit ...];

delete语句用于删除表中满足指定条件的行，关于它的详细用法，将在下述案例中挨个介绍↓↓↓
案例1： 删除孙悟空同学的考试成绩

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

案例2： 删除整张表的数据（该操作在实际工程中，慎用）
●首先创建一张测试表，并插入数据↓↓↓

create table TestDelete(
id int unsigned primary key auto_increment,
name varchar(20)
);
insert into TestDelete(name) values('A');
insert into TestDelete(name) values('B');
insert into TestDelete(name) values('C');
select * from TestDelete;

●查看此时的表结构后，将表中数据全部删除，再查看表结构。上述操作后，再插入一定数量的数据↓↓↓

delete from TestDelete;
select * from TestDelete;
show create table TestDelete \G
insert into TestDelete(name) values('D'), ('E');
select * from TestDelete;

★ps：从上面可知，在删除表中数据后，表结构中的auto_increment数值为最后一次插入数据的id字段+1。后序再插入数据时，并不会从1开始，从头开始编号。

截断表

语法：

truncate [table] table_name;

★ps：这个操作相比delete删除数据，需要更加谨慎使用↓↓↓
Ⅰ 只能对整表操作，不能像delete一样针对部分数据操作；
Ⅱ 实际上MySQL不对数据操作，所以比delete更快；但是truncate在删除数据的时候，并不经过真正的事务，所以无法回滚（不会被记入日志）
Ⅲ 会重置auto_increment项

案例1： 截断整个表的数据
●创建一张表，并插入一定数量的数据↓↓↓

create table TestTruncate(
id int unsigned primary key auto_increment,
name varchar(32)
);
insert into TestTruncate(name) values('A'), ('B'), ('C');
select * from TestTruncate;

●查看表结构后，对表进行截断操作，再查看表结构↓↓↓

show create table TestTruncate \G
truncate table TestTruncate;
show create table TestTruncate \G

★ps：从上图输出结果可知，truncate后，表中的auto_increment会被清空。此时尝试插入数据后，id字段将从头开始编号↓↓↓

insert into TestTruncate(name) values('D'),('E');
select * from TestTruncate;

插入查询结果

语法：

insert into table_name[(column [, column ...])] select ...;

★ps：上述语句用于将查询的结果插入到table_name表中

案例： 删除表中的重复记录，重复的数据只保留一份
●创建一个测试表，插入一定量的数据（含重复数据）

create table TestSI(id int, name varchar(32));
insert into TestSI values
(100, 'aaa'),
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');
select * from TestSI;

☆思路：可以将上述结果先使用select distinct找出去重后的结果，将该结果保存到TestSI_TMP中，将原表TestSI重命名为TestSI_OLD，将TestSI_TMP重命名为TestSI，即可实现TestSI的去重操作。

●下面对上述思路进行实现，先将去重后的结果保存到TestSI_TMP中↓↓↓

create table TestSI_TMP(id int, name varchar(32));
insert into TestSI_TMP select distinct * from TestSI; 
select * from TestSI_TMP;

●将TestSI重命名为TestSI_OLD，将TestSI_TMP重命名为TestSI，即可完成TestSI表的去重操作↓↓↓

rename table TestSI to TestSI_OLD;
rename table TestSI_TMP to TestSI;
select * from TestSI;

聚合函数

下面是MySQL中的常用聚合函数

函数	说明
COUNT([DISTINCT] expr)	返回查询到的数据的数量
SUM([DISTINCT] expr)	返回查询到的数据的综合，不是数据时没有意义
AVG([DISTINCT] expr)	返回查询到的数据的平均值，不是数据时没有意义
MAX([DISTINCT] expr)	返回查询到的数据的最大值，不是数据时没有意义
MIN([DISTINCT] expr)	返回查询到的数据的最小值，不是数据时没有意义

关于上表中的各个聚合函数的用法，将于下述案例中介绍↓↓↓

案例1： 统计班级共有多少同学

select COUNT(*) from students;

由于select语句中，选取显式列的名称时，如果指定为不存在的列名时，则显式结果时，各个数据行会新增一列该列名。

select 'jammingpro',* from students;

此时，我们计算该表有多少行数据时，可以转变为计算这个表中一共出现了多少次’jammingpro’

案例2： 统计班级收集的qq号有多少（NULL值不会被COUNT计入）

select count(qq) from students;

案例3： 统计本次考试的数学成绩分数个数

#没有去除相同的数学成绩
select count(math) from students;
#相同的数学成绩只计入一次
select count(distinct math) from students;

案例4： 统计所有学生数学成绩相加的总分

select sum(math) from exam_result;

案例5： 统计所有学生的平均总分

select sum(chinese+math+english)/count(*) '平均总分' from exam_result;
select avg(chinese+math+english) '平均总分' from exam_result;

案例7： 返回英语最高分

select max(english) from exam_result;

案例8： 返回70分以上的数学最低分

select min(math) from exam_result where math > 70;

★ps：上述操作说明了，MySQL执行时，先执行where子句，再执行聚合函数

group by子句的使用

在select中使用group by子句可以对指定列数据进行分组查询

select column1, column2, ... from table_name group by column;

下述案例使用的表为Oracle 9i经典测试表，读者可自行下载，并使用source [数据库文件名]将表数据导入数据库中

案例1： 显式部门表中，每个部门的平均工资和最高工资

select deptno, avg(sal), max(sal) from emp group by deptno;

案例2： 显式各个部门的每种岗位的平均工资和最低工资
☆思路：先按部门分组，再按岗位名称分组

select deptno, job, avg(sal), min(sal) from emp group by deptno, job;

案例3： 显式平均工资低于2000的部分和它的平均工资
●先统计各个部门的平均工资

select avg(sal) from emp group by deptno;

★ps：如果要对分组查询后的结果再做过滤，不可以使用where子句，需要使用having子句+过滤条件

●having和group by配合使用，对group by结果进行过滤

select avg(sal) as avgsal from emp group by deptno having avgsal<2000;

★ps：SQL查询中，各个关键字的执行先后次序为：from > on > join > where > group by > with > having > select > distinct > order by > limit（部分关键字将于后序文章中介绍）

🎈欢迎进入半夜学习MySQL专栏，查看更多文章。
如果上述内容有任何问题，欢迎在下方留言区指正b(￣▽￣)d