【MySQL】表的增删查改

点赞👍👍收藏🌟🌟关注💖💖
你的支持是对我最大的鼓励，我们一起努力吧!😃😃

CRUD : Create(创建)， Retrieve(读取)，Update(更新)，Delete（删除）

1.插入

语法：

INSERT [INTO] table_name
	[(column [, column] ...)] #列字段
	VALUES (value_list) [, (value_list)] ... #列字段的内容
	
value_list: value, [, value] ...

案例：

-- 创建一张学生表
CREATE TABLE students (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	sn INT NOT NULL UNIQUE COMMENT '学号',
	name VARCHAR(20) NOT NULL,
	qq VARCHAR(20)
);

1.1单行数据 全列插入+指定列插入

指定列插入

列字段和列字段的内容一定要一一匹配

insert into student (sn,name,qq) values (123,'张飞','12345');

全列插入

全列插入有两种方式，一个是省略 valiues 左侧字段名，一个是都指定

insert into students values (10,124,'关羽','13245');
insert into students (id,sn,name,qq) values (14,125,'刘备','14525')

可以省略into

insert students (sn,name,qq) values (126,'诸葛亮','12525');

1.2多行数据 全列插入+指定例插入

指定列多行插入

 insert students (sn,name,qq) values (127,'曹操','15256'),(128,'许攸','23445');

全列多行插入

insert students values (20,129,'孙权','12256'),(21,130,'吕布','33445');

1.2插入否则更新

由于 主键 或者 唯一键 对应的值已经存在而导致插入失败

但是我就是想让它先确认是不是在数据库中存在，不存在就插入，存在不要拦我然后执行后面的修改语句

选择性的进行同步更新操作 语法：

INSERT ... ON DUPLICATE KEY UPDATE
	column = value [, column = value] ...

如果不存在就插入，存在发生主键或者唯一键冲突不要报错就行后面的修改语句

insert into students values (14,111,'周瑜','56321') on duplicate key update sn=111,name='周瑜',qq=56321;

注意更新的值不能和其他的主键和唯一键冲突，否则不能更新

• 0 row affected: 表中有冲突数据，但冲突数据的值和 update 的值相等
• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，并且数据已经被更新

通过 MySQL 函数获取受到影响的数据行数

select row_count();

1.4替换

主键 或者 唯一键 没有冲突，则直接插入；
主键 或者 唯一键 如果冲突，则删除后再插入

REPLACE INTO students (sn,name,qq) values (131,'孙悟空','98752');

• 1 row affected: 表中没有冲突数据，数据被插入
• 2 row affected: 表中有冲突数据，删除后重新插入

这里从id就可以看到是删除后插入的，因为id是自增的，刚才是22，现在是23了

2.查找

语法：

SELECT
	[DISTINCT] {* | {column [, column] ...}
	[FROM table_name] # 从那个表筛选
	[WHERE ...] # 筛选条件
	[ORDER BY column [ASC | DESC], ...] # 对筛选结果排序
	LIMIT ... # 限定筛选出来的条数

distinct：对内容进行去重
*：全列查询
column，column…：指定列查询

案例：

-- 创建表结构
CREATE TABLE exam_result (
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20) NOT NULL COMMENT '同学姓名',
	chinese float DEFAULT 0.0 COMMENT '语文成绩',
	math float DEFAULT 0.0 COMMENT '数学成绩',
	english float DEFAULT 0.0 COMMENT '英语成绩'
);

-- 插入测试数据
INSERT INTO exam_result (name, chinese, math, english) VALUES
	('唐三藏', 67, 98, 56),
	('孙悟空', 87, 78, 77),
	('猪悟能', 88, 98, 90),
	('曹孟德', 82, 84, 67),
	('刘玄德', 55, 85, 45),
	('孙权', 70, 73, 78),
	('宋公明', 75, 65, 30);

2.1select 列

全列查询

通常情况下不建议使用 * 进行全列查询

• 1. 查询的列越多，意味着需要传输的数据量越大；
• 2. 可能会影响到索引的使用。（索引后面再说）

select * from exam_result;

指定列查询

指定列的顺序不需要按定义表的顺序来

select id,name,chinese from exam_result;

查询字段为表达式

select非常特殊，后面可以跟select 自带的子句，筛选条件等，也可以跟 表达式

为查询结果指定别名

语法：

SELECT column [AS] alias_name [...] FROM table_name;

可带as，可不带

结果去重

select distinct math from exam_result;

2.2where 条件

刚刚是对表的整体信息做筛选，但是实际在做查询的时候一定有筛选条件。按条件筛选影响的是未来显示出来信息的条目数或者说是行数，以前是按列位单位把全部行都拿出来了。如果一列想拿那些行由where条件来决定。

where是筛选子句，后面可以跟特定的比较运算符来决策我们应该如何进行筛选，
where就有点像C/C++里面的if语句，根据后面条件进行判断。

比较运算符

运算符	说明
>, >=, <, <=	大于，大于等于，小于，小于等于
=	等于，NULL 不安全，例如 NULL = NULL 的结果是 NULL
<=>	等于，NULL 安全，例如 NULL <=> NULL 的结果是 TRUE(1)
!=, <>	不等于
BETWEEN a0 AND a1	范围匹配，[a0, a1]，如果 a0 <= value <= a1，返回 TRUE(1)
IN (option, …)	如果是 option 中的任意一个，返回 TRUE(1)
IS NULL	是 NULL
IS NOT NULL	不是 NULL
LIKE	模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

<,<=,>,>= 通常用来比较整数和浮点数，判断条件是否满足

= 判断字符串和数组是否相等，另外 = 判断是否等于null是不安全的，而且null和0表示的是不同含义，null真的就是null什么都没有，0就是0。

= 时两侧进行null值比较，是不能参与运算的

如果想判断null是否相等 使用 <=>

不过一般也不这样去判断，一般更喜欢用 IS NULL 去判断一个值是否是null

总之： = 只能进行数字和字符串的比较，不能用数字和null或者两个null是否相等比较，判断一个值是否等于null 建议用 IS NULL

!= 也只能用来数字和字符串比较，不能和null进行比较，并且<>也是一样的，建议用 IS NOT NULL

between a0 and a1 要求范围在【a0，a1】
in(option，…) 要求结果必须在(option，…) 这个范围
like 后面具体例子再说

逻辑运算符

运算符	说明
AND	多个条件必须都为 TRUE(1)，结果才是 TRUE(1)
OR	任意一个条件为 TRUE(1), 结果为 TRUE(1)
NOT	条件为 TRUE(1)，结果为 FALSE(0)

and 条件都要满足，or 满足一个条件就可以，not 不满足条件为真

下面以具体案例熟悉上面的操作：

英语不及格的同学及英语成绩 ( < 60 )

select name,english from exam_result where english<60;

以前没带筛选条件直接显示一列，现在带上筛选条件显示出这列符合条件的

可以看到select 后面跟的是要显示那些列，如果没有where子句就是把指定列所有数据全部显示出来。where条件影响的是你要拿的这些列中所匹配的所有行信息要按条件进行行筛选然后在显示。select 后面影响要那些列，where 后面影响要列中那些行。

语文成绩在 [80, 90] 分的同学及语文成绩

使用 AND 进行条件连接

select name,chinese from exam_result where chinese>=80 and chinese<=90;

使用 BETWEEN … AND … 条件

select name,chinese from exam_result where chinese between 80 and 90;

数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩

使用 OR 进行条件连接，满足任意一个就为真

select name,math from exam_result where math=58 or math=59 or math=98 or math=99;

使用 IN 条件，满足()里任意一个就为真

select name,math from exam_result where math in(58,59,98,99);

姓孙的同学 及 孙某同学

有时候匹配并不给具体值的更细节的字段含义，可能就只给一个模糊搜索的关键字。就如上面。反正条件不给全就给一个模糊的条件。我们就可以用like 模糊匹配

% 匹配任意多个（包括 0 个）任意字符

注意mysql可以用 ‘ ’ 或者 “ “表示字符串

select name from exam_result where name like '孙%';

_ 匹配严格的一个任意字符

select name from exam_result where name like '孙_';

语文成绩好于英语成绩的同学

WHERE 条件即可以是一列和常数、一列和字符串、一列模糊匹配，一列跟另一个列比较

select name,chinese,english from exam_result where chinese>english;

总分在 200 分以下的同学

可以看到where后面也有跟表达式

曾经不是说过可以对列进行重命名吗，那where后面还用写这么一大堆吗

我们发现它报错了，未知列total，我们不是做过重命名吗。这个total不是已经有了吗，怎么这里报不知道total呢？

这里我们就需要讨论一些东西了
问：from这个表，where这个条件判断和select指明要显示的列，整个sql语句的执行顺序是什么？

说明一下sql这里语句执行顺序非常重要，后面还有order by、limit等，你怎么知道谁先执行呢？所以这个顺序一定要搞清楚。

那这里为什么where 后面无法使用重命名的别名呢？

很简单，我们一定是先执行from，在执行where，然后在执行select。

因为是从1中带着2去3中筛选，也就是说你先告诉我们从那个表筛选数据，一定是先给筛选条件，然后拿着筛选条件去筛选。

以前没有where就不说where的话，现在有where一定是先按照这个条件然后才去筛选的。满足就保留不满足就淘汰。

这里就是先执行1，在执行2，在执行3，而执行3的时候才有重命名，在执行第2的时候就没有这个别名，所以不认识。

而且你在where这里重命名也是不行的，对列重命名是属于显示的范畴，相当于是临门一脚把数据都那完了，只是把列名字改一改，属于最后一步了。不能在筛选的时候就重命名，语法不支持

select name,math+chinese+english total from exam_result where math+chinese+english < 200;

所以这个重命名一定是数据已经筛选出来了，列信息已经拿到了然后在对列进行重命名

语文成绩 > 80 并且不姓孙的同学

AND 与 NOT 的使用

select name,chinese from exam_result where chinese>80 and name not like '孙%';

孙某同学，否则要求总成绩 > 200 并且 语文成绩 < 数学成绩 并且 英语成绩 > 80

要么就是孙某同学，要么就得满足后面的一堆要求，总共就两个条件，在mysql如果条件很多可以用（）把这个条件括起来，表示一个单元

select name,chinese,math,english,chinese+math+english total from exam_result where name like '孙_' or (chinese+math+english>200 and chinese<math and english>80);

NULL 的查询

select name from test where name is null;

这里也可以说明 null 和 ’ ’ 不是一个东西

select name from test where name is not null;

2.3结果排序

语法：

SELECT ... FROM table_name [WHERE ...]
	ORDER BY column [ASC|DESC], [...];

• ASC 为升序（从小到大）(ascending)
• DESC 为降序（从大到小）(descding)
• 默认为 ASC

注意：没有 ORDER BY 子句的查询，返回的顺序是未定义的，永远不要依赖这个顺序

同学及数学成绩，按数学成绩升序显示

select name,math from exam_result order by math asc;

asc可以省略，默认是asc升序

NULL 视为比任何值都小，升序出现在最上面

NULL 视为比任何值都小，降序出现在最下面

最终就想说明的是NULL 视为比任何值都小

查询同学各门成绩，依次按 数学降序，英语升序，语文升序的方式显示
每个同学的各门成绩都要查，首先按照数学降序排序，如果数学成绩相同就按英语升序排序，如果数学和英语成绩都相同就按语文升序排序。

select name,math,english,chinese from exam_result order by math desc,english,chinese;

查询同学及总分，由高到低

ORDER BY 中可以使用表达式

select name,chinese+math+english total from exam_result order by total desc;

为什么在order by这里可以使用别名进行排序，而where后面没有办法使用别名？

你要对表结构的数据进行排序，一定是得先有数据！有人可能说不是表结构不就天然有数据吗我直接把表结构数据全排完，然后在选行不行。当然可以了。可是数据天然没有经过筛选而直接先排序，排完序在筛，这在技术上肯定可以实现。但你会愿意这样做吗？因为你没有筛选，在排序的时候一定有大批数据其实是不需要排序的，而对这些数据排序本身就是浪费空间和时间，mysql没有这么笨！

它肯定是先把你要的数据先给你，然后才进行排序。换言之未来执行的时候
。在表中拿着筛选条件对表指定列筛选，然后对筛选结果进行排序。

所以能不能用别名，完全是取决于当前sql中子句的执行顺序！

查询姓孙的同学或者姓曹的同学数学成绩，结果按数学成绩由高到低显示

根据刚才对order by的理解，一定是先把数据筛选出来，然后再进行排序，

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

2.4筛选分页结果

什么是分页呢？

如果一个表中数据量太大，这个时候如果全列查询就有一大堆，这样不便于查看分析，有时候我们不想一次显示这么多，因此我们就可以对结果进行limit分页。

所以limit可以对结果进行筛选，它本身没有筛选功能，只能按照它后面跟的数字把要显示的结果按照起始位置和步长给我们显示多条记录。

语法：

-- 起始下标为 0

-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n

-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;


-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

建议：对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死

默认从0下标开始，筛选多条记录

也可以指定下标开始筛选后面跟的是步长。从指定位置开始，连续读取多条记录

select * from exam_result limit 4 offset 3;

limit 后面跟的是筛选几行，offset 后面跟的是从那行开始

limit 可以进行分页。就比如数据多就可以这样进行分页读

下面想说还是sql子句执行顺序的问题，如下面对总成绩排序后只想拿第一名同学。

也可以进行分页显示

我们要分清楚一件事情，我们需要有数据才能排序，只有数据准备好了，你才要显示，limit的本质功能是 ”显示“。 意思就是说你一定是要有数据才能最后显示，显示的时候limit知识告诉mysql，你给我显示的时候是从哪里开始，然后从这个开始位置显示几行。limit不是条件筛选，它的本质还是从表中拿着筛选条件对指定列进行筛选，然后排好序，再limit，所以limit的执行会更靠后。

3.更新

语法：

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

set后面跟的是要重新设定的值，可以是多列。一般在update的时候必须采用对应where子句进行条件筛选，如果没有的话会把这个表中指定的列全部都更新，这是不合理的。

对查询到的结果进行列值更新

将孙悟空同学的数学成绩变更为 80 分

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

将曹孟德同学的数学成绩变更为 60 分，语文成绩变更为 70 分

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

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

更新值为原值基础上变更。
注意据更新，不支持 math += 30 这种语法。

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

更新之后可能后三名同学加上数学+30可能就不在后三名了。

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

update exam_result set chinese=chinese*2;

注意：更新全表的语句慎用！

一定要谨慎！

4.删除

语法：

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

一般都是拿着条件去删除，不加条件就是把整表的内容删除了，不过表结构还在。删表结构drop。

删除孙悟空同学的考试成绩

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

删除整张表数据

注意：删除整表操作要慎用！

mysql表分为表和表里面的数据，delete重点是删表里面的数据，表结构不受影响，drop才是删表结构。

-- 准备测试表
CREATE TABLE for_delete (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
);


-- 插入测试数据
INSERT INTO for_delete (name) VALUES ('A'), ('B'), ('C');

我们看到这个id设置了自增，目前已经插入三条记录了，如果在插入一条记录这个id就是4了。

但我们现在不插，先删除

delete from for_delete;

我们看到表的内容已经没有了，但是查看创建表结构这个语句还在，并且自增长也还在。

当新插入一条记录时这个id是4，并且自增长已经变成下一个id值了

说明delete from清空表的方式不会将自增值置0。

清空表还有一种做法叫做截断表。在效果和delete一模一样，但是在细节和原理是有差别的。

截断表

语法：

TRUNCATE [TABLE] table_name

注意：这个操作慎用

1. 只能对整表操作，不能像 DELETE 一样针对部分数据操作；
2. 实际上 MySQL 不对数据操作，所以比 DELETE 更快，但是TRUNCATE在删除数据的时候，并不经过真正的事物，所以无法回滚
3. 会重置 AUTO_INCREMENT 项

-- 准备测试表
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;

可以看到表结构还在，但是内容已经被清空了。但是这里值得注意的一点是，之前自增长是4，现在truncata清空表后自增从已经没有了。

然后新插一条记录，id变成1了。自增长变成2了

换句话说truncate会重置自增长。而delete并不会。

实际上truncate和delete还有差别，truncate对应操作的这条记录是直接把表数据清空了，它不走事务。像delete和之前的sql最终在运行的时候都要以事务的方式被包装一下然后再让mysql去运行，而turncate并不会。包不包事务有什么影响呢？mysql一般对操作信息会做记录，会用自己对应的日志来对数据做记录。mysql里重要的日志有 bin.log 、 redo.log 、undo.log。操作痕迹一般放在前两个，bin.log会把历史上操作的优化后的sql语句保存。 redo.log为了保证mysql宕机的时候数据不就丢了吗，这个时候redo.log里面的策略保证能把数据恢复出来。

持久化方式，所谓持久化方式指的是为了快速把数据库的数据恢复出来。

1. 记录历史sql语句
2. 记录数据本身

bin.log记录的就是历史sql语句，然后可以做mysql的主从同步，可以有两个数据库，一个数据把操作做完，然后这些sql同步给另一个数据库然后两个数据就一样了。默认bin.log是被关闭的。

turncate并不把自己的操作记录在日志里，而delete会把操作记录在日志里，因为turncate少做了一些事情，不把自己包装成事务，也不把操作痕迹记录在日志里，而只是单纯把数据库清空，所以truncate比较快。

5.插入查询结果

语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

我们要插就插，要删就删，要改就改，要查就查，实际我们也可以将select和insert组合。可以把数据从其他表里面筛选出来，然后插入到另一个表里面。

删除表中的的重复复记录，重复的数据只能有一份

-- 创建原数据表
CREATE TABLE duplicate_table (id int, name varchar(20));


-- 插入测试数据
INSERT INTO duplicate_table VALUES
	(100, 'aaa'),
	(100, 'aaa'),
	(200, 'bbb'),
	(200, 'bbb'),
	(200, 'bbb'),
	(300, 'ccc');

可能会想到直接把重复信息delete from删除，但是你敢这样做吗，信息都是一样的，删不就删完了吗。

可能还会想到前面select查的时候，不是有一个对结果进行去重复吗，对这样确实是去重，但是这是查的时候去重，但表本身没有变。

我们的做法是，

1. 创建一个和原表一样结构的空表 no_duplicate_table ，
2. 从原始表中把去重之后的结果筛选出来插入到no_duplicate_table 这个表不就是不重复的吗，
3. 然后对duplicate_table重命名，no_duplicate_table改名字为duplicate_table。最终不就是完成了duplicate_table去重了吗。

create table no_duplicate_table like duplicate_table;

创建一个完全一样的表，使用like即可

全列插入就不用指定列了

insert into no_duplicate_table select distinct * from duplicate_table;

现在有两个表，一个duplicate_table，一个no_duplicate_table。然后对这两个表做一下重命名

下面两个sql语句可以写一起

rename table duplicate_table to old_duplicata_table;
rename table no_duplicate_table to duplicate_table;

当前我们就完成了去重

这里有个细节问题，为什么最后是通过rename方式进行的？

如果今天想把一个文件上传到linux上，比如这个文件是1G上传时间可能是10分钟，我们想把这个文件上传号之后放到一个目录下，我要求它是为原子性方式放入的。所以一般我们不能直接把文件上传到对应的目录下，因为它上传的过程一种在写入一定不是原子，它太慢了。所以我们把这个文件上传到临时目标下，全部上传之后然后再把文件move到那个目录下。直接move这个动作实际上是原子的。其实对一个文件进行重命名也是同一个道理，所以我们最后通过rename方式，就是单纯的想等一切都就绪了，然后统一放入，更新，生效等！ 和冗长的其他动作相比，这个动作非常轻。

6.聚合函数

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

这里是为select分组查询做准备的，聚合函数是以查出来的记录为单位帮我们进行数据聚合统计的。这种聚合统计方式通常是产出一个期望的结果，如个数、和、平均值、最大值、最小值。

mysql中其实也是有函数的，这个函数可以被直接调用，我们可以在mysql直接使用聚合函数直接对一组结果进行聚合统计。

聚合函数（）里面可以是全列，可以是指定列。

统计班级共有多少同学

select count(*) from exam_result;

为什么1也行呢？
实际上在做select的时候，select 1 就是这个表有多少行就拼多少1，只不过只是显示1

统计班级数学成绩有多少

 select count(math) from exam_result;

统计班级去重后数学成绩有多少

COUNT DISTINCT (math) 是对统计的结果去重
COUNT(DISTINCT math) 统计的是去重成绩数量

select count(distinct math) from exam_result;

统计数学成绩总分

select sum(math) from exam_result;

统计平均总分

select avg(math+chinese+english) from exam_result;

统计数学成绩小于60分的个数

select count(math) from exam_result where math<60;

返回英语最高分

select max(english) from exam_result;

返回 > 70 分以上的数学最低分

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

这里为什么不能把name带上呢？

聚合统计的前提条件，一定是你先把我要聚合的数据先拿出来，然后才能聚合。做聚合的时候必须保证你要显示的或者你要查询的数据列是被允许聚合的。最低成绩只有一个，但name每个人都不一样没有办法做聚合。所以你要查告诉我们要分组之后才能进行聚合。

7.分组查询

分组是对表中的数据进行分组，分完组之后，在对表中每一组进行相关聚合统计。而分组的目的是为了进行分组之后，方便进行聚合统计。 如班级里有男生女生，我们相对男生女生成绩分别统计，所以可以对性别进行分组然后在进行成绩的聚合统计。

在select中使用group by 子句可以对指定列进行分组查询。我们也可以把数据筛选之后再进行分组然后再聚合统计。

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

准备工作，创建一个雇员信息表（来自oracle 9i的经典测试表）

• EMP员工表
• DEPT部门表
• SALGRADE工资等级表

正是因为我们表有各种不同的信息，因此我们可以对表进行分组查询。

如何显示每个部门的平均工资和最高工资

注意说的是每个部门！我们未来是要分组的，凭什么分组，是由需求来决定的。这里很明显说的是每个部门，公司员工信息全部都在员工表里，部门号不同员工一定隶属于不同部门，所以我们要显示每一个部门的平均和最高工资。

以前我们找公司最高工资和平均工资。我们是把这一张表当作一个大的组，这一张表在我看来就是一个整体的组，我要统计的就是全公司最高工资和平均工资。

但今天需求是按照组来统计的，根据的emo表中deptno列来进行分组。

select max(sal) 最高, avg(sal) 平均 from emp group by deptno;

group by后面跟的是按什么分组。

我现在也想看见是那个部门的最高工资和平均工资

select deptno,max(sal) 最高, avg(sal) 平均 from emp group by deptno;

首先再次强调group by不是你想用就能用，一定要结合需求。其次group by的核心作用是让我们继续分组聚合统计的，所以你要把需求分清楚然后和group by功能对上，才能用group by。

我们在进行分组统计的时候，group by后面指定列名，指明我们要分组的列是谁，但实际分组是用该列的不同的行数据是否相同进行分组的！当我们分完组之后，那分组的条件（deptno），组内一定是相同的，因此可以被聚合压缩。

分组，不就是把一组按照条件拆分多个组，进行各组内的统计。
分组（" 分表 "），不就是把一张表按照条件在逻辑上拆成多个子表，然后分别对各自的子表进行聚合统计。

拆成各个组不就是在逻辑上拆成各个表，然后分别在每个表里做聚合统计，以前我们做的聚合统计是在一张表里进行的，换句话说，只要掌握在一张表里查询，在查询之前先做好分组，后面的工作和思路理解上和之前单表上的聚合统计是一模一样的。

显示每个部门的每种岗位的平均工资和最低工资
需求是每个部门的每种岗位，注定了一定是要分组的，而且还不是分一组，我们首先按部门来分组，然后在按岗位来分组。

在我看来每一张组都是独立的组，在组内做聚合也没什么问题。无非就是比以前多做一份工作，先分组，再聚合。 一张整表内做聚合和多个子表内做聚合，总之还是在一张表内聚合。

select avg(sal) 平均,min(sal) 最高 from emp group by deptno,job;

我想看一看分别是哪个部门的那个岗位的平均工资和最高工资

lect deptno,job,avg(sal) 平均,min(sal) 最高 from emp group by deptno,job;

在想看看是哪个部门的那个岗位的谁的平均工资和最高工资

select ename,deptno,job,avg(sal) 平均,min(sal) 最高 from emp group by deptno,job;

故意加了一个ename，然后就报错了，说的是ename没有在分组条件内出现不属于分组条件，所以无法进行压缩和聚合。刚才我们说了分组内一定是某个相同的。走到一个组内一定是部门号相同工作总类相同。所以相同的列可以进行压缩聚合。现在出来一个名字，名字一定是人人都不同的，没有办法进行聚合，所以直接报错。

一般大原则，只有在group by中出现的具体的列名称，才可以在select后面具体出现，除此之外另一类可以直接出现的就是聚合函数，其余不能在select后面直接跟不是具体分组条件的列。

显示平均工资低于2000的部门和它的平均工资

需求是平均工资低于2000的部门，要的是部门，和它的平均工资是多少。

1. 我们统计出来每一个部门的平均工资(结果先聚合出来)
2. 在进行判断(对聚合的结果，进行判断)

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

我要的不是所有部门，我要的是平均工资低于2000的部门，此时要对聚合的结果拿出来判断。我们再说一个语法结构

having和group by配合使用，对group by结果进行过滤， 先要有数据然后having对这一批数据做判断，筛选出想要的。

select deptno,avg(sal) 平均 from emp group by deptno having 平均<2000;

having经常和group by搭配使用，作用是对聚合后的统计数据，进行条件筛选，作用有些像where。

having 和 where 区别理解？执行顺序？构建对 “结果” 的理解。

首先having和where都是够进行条件筛选，但是它们两个是完全不同的筛选。

比如说这里把having换成where就报错了，

虽然这里可以查询出来，但是并不意外，因为having本就是分组查询的，刚开始说过对于组的理解不要死板理解上面是一张整表下面是分组，我们把分组理解成分表就可以了。我们把一个表拆成很多表，每一张表内做聚合统计，其实都是之前说的聚合统计。但是如果我们没有做分组这张表宏观上在我们看来也是一组数据。 所以having这里可以充当where。但是一般在select这里严重不推荐这样使用having。

而上面用where不行，至少说明having和where因为一些特征是无法互相取代的。

再来一个需求，SMITH员工不参与统计，显示平均工资低于2000的部门和它的平均工资

SMITH不参与统计，首先去掉SMITH

去掉之后，以前是一张表，去掉之后还是一张表，然后对现在查出来的一张表在进行分组之后在进行条件筛选。

select deptno,job,avg(sal) 平均 from emp where ename!='SMITH' group by deptno,job having 平均<2000;

having 和 where 区别理解？执行顺序？构建对 “结果” 的理解。

在我们看来where是对具体的任意列进行条件筛选，having对分组聚合之后的结果进行条件筛选。 它们俩的应用场景是完全不同的。换句话说where是先对原始表进行条件过滤，对过滤后的结果在进行分组。

所以这条sql顺序如下：1. 先从那个表拿数据，2. 在这个表拿数据的筛选条件是什么，数据筛选之后，3. 对这些数据进行分组，4. 分组之后结果进行聚合统计以及重命名，5. 数据全都出来了然后才是对结果进行筛选

所以where和having都叫条件筛选，它们的区别在于条件筛选的阶段是不同的。执行顺序我们刚才也说了。

构建对 “结果” 的理解（小小的建模）
不要单纯的认为，只有磁盘上表结构导入的mysql，真实存在的表，才叫做表。中间筛选出来的，包括最终结果，在我看来，全部都是逻辑的表。在我看来"MySQL一切皆表"，所以未来只要我们能够处理好单表的CURD，所有的sql场景，我们全部都能用统一的方式进行。