MySQL—CRUD—进阶—(二) (ಥ

文本目录：

❄️一、新增：

❄️二、查询：

1、聚合查询：

1）、聚合函数：

2）、GROUP BY子句：

3）、HAVING 子句：

2、联合查询：

1）、内连接：

2）、外连接：

3）、自连接：

4）、子查询：

5）、合并查询：

❄️总结：

❄️一、新增：

插入查询结果

也就是创建一个新表，把原来旧表中指定的列插入到新表中。

语法：

INSERT INTO 新的表名 [(column [, column ...])](指定的新表的列) SELECT ...（这里是旧表的列）

我们先来创建两个表来进行演示一下对于这个方法如何使用：

我们先来创建一个 stu 这个表：有 id sn name

我们创建了 stu 这个表，并且往里面放入了 4条数据。

接下来我们再创建一个表：stu1 里面的列为 id name

我们可以看到这个表里面没有任何的数据，接下来我想要把 stu 里面的数据放入到 stu1中，那么要如何才能做到呢？

这个时候就可以使用上面那个新增的语法来达到这个目的：

这样呢，我们就可以实现把 stu 中的 id，name 都复制到 stu1中。

这个就是我们新增的一个语法。

❄️二、查询：

1、聚合查询：

1）、聚合函数：

常见的统计总数、计算平均值等操作呢，可以使用聚合函数来实现，常见的聚合函数：

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

上面的这些操作呢都是对列的操作。

我们先来创建一个 exam 表：

我们一个一个的演示一遍：

1、COUNT ([DISTINCT] expr)：

语法：

select count(列) from 表名;

统计表中的行数

这里注意，当我们去指定列查询的时候呢，如果这个列中存在 NULL 的话呢，我们这个 count 方法呢是不会统计 NULL 这一行的，演示一遍看看：

这个就是不会统计 NULL 值。

2、SUM ([DISTINCT] expr)：

作用：

把查询结果中所有行中的指定的列进行相加

语法：

select sum(列) from 表名;

对于求和的时候呢，我们的中的 NULL 同样不参与运算。

这里要注意我们计算出来的结果并不是存在在表中的，其存在在一个临时表中，并且不受表中字段的长度的约束。

当然在我们计算的时候呢，我们可以在其后面添加一些限制条件，比如：

3、AVG ([DISTINCT] expr)：

作用：

对所有行的指定列进行求平均值

语法：

select avg(列) from 表名;

我们直接来演示一遍如何使用的：

1、对数学成绩进行平均值查询

这个同样不受表中字段长度的约束。

2、对表达式进行求平均值，我们这里同样可以使用别名来代替：

这个呢就是 AVG 的用法了，我们继续往下看

4、MIN ([DISTINCT] expr)、MAX ([DISTINCT] expr)：

作用：

求所有行中指定列的最小值和最大值

语法：

这个呢，我们可以单独使用，也可以一起使用：

select min(列),max(列) from exam;

这个也可以使用别名：

这个呢就是我们的 max 和 min 的使用方法了。

这里我们要注意：

对于使用聚合函数的时候呢，我们可以多个聚合函数一起进行使用，而非是一个一个的进行运算。

当然了，聚合函数不单单只有这几个，还有很多，可以去官网去了解一下：

MySQL官网

2）、GROUP BY子句：

定义：

SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。

需要满足：GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在 SELECT 中则必须包含在聚合函数中。

这样说的话，就不是很好理解，我们来对其进行演示一遍是什么意思，并且对其进行解释：

我们先来创建一个表：

我们来看这个表中的字段哪个才能作为分组字段，id 呢是不会重复的，对于name 呢也不存在相同的，还有工资也不适合分组，所以我们的只能由 role 角色进行分组，所以这里我们的分组依据字段是 —— role。

语法：

select column1, sum(column2), .. from table group by column1,column3;

1、比如：计算不同角色的平均值

这个呢就是我们的对于分组查询的使用。

我们在 group by 之后呢还可以添加 order by 语句进行排序规则：

3）、HAVING 子句：

定义：

在 GROUP BY 子句进行分组之后呢，需要对分组结果进行再进行过滤，不能使用 WHERE 语句，而是需要使用 HAVING 子句。

因为这些的到的查询结果不是表中真实的数据，都是由聚合函数查询出来的。

我们来使用看看这个 HAVING子句如何使用的：

having 使用在 group by 之后，而 where 是在 from 表名之后，也就是分组之前，这里要进行区分

如果需要对于表中真实存在的数据进行过滤，并且也需要对分组的结果进行过滤，那么这时候可以在合适的位置写 where 和 having 即可。

2、联合查询：

联合查询也叫表连接查询：

1、首先确定哪几张表要参与查询

2、根据表与表之间的主外键关系，确定过滤条件

3、精准查询字段，得到想要的结果

我们先来看对于上次博客所说的复合主键：

实际开发中往往数据来自不同的表，所以需要多表联合查询。多表查询是对多张表的数据取笛卡尔积。
那么什么是笛卡尔积呢？我们来看：

我们可以的出对于生成笛卡尔积的过程是：

1、先从第一张表中取出一条记录，然后再与第二张表中的第一条记录进行组合，生成第一条新的记录

2、先从第一张表中取出一条记录，然后再与第二张表中的第二条记录进行组合，生成第一条新的记录

...................

最后的到的结果就是一个全排列的结果集。

1）、内连接：

连接查询方法：

1：select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件;

2：select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;

我们详细演示一下对于内连接的第二种方式：

我们来创建两个表来进行基础的联合查询：

联合查询的语法：

select * from 表名，表名;

1、对这两个表进行联合查询：

这个两张表中呢，在取笛卡尔积之后呢，是存在有效数据和无效数据的，我们可以看到在class_id和 class表中的 id 相等的情况下呢，就是有效的数据，那么我们要如何过滤掉这些无效的数据呢？

2、通过连接条件过滤掉无效数据

连个表中是存在主外键关系的，只需要判断两个表中的主卧阿健字段是否相等就可以过滤掉无效数据。

我们可以通过：表名.列名的方式来解决问题：

这样的得到的结果就是表连接的查询，但是我们是不是不想要 class_id 和 id 这两个列啊，所以接下来我们对其进行简化：

这样呢，我们就可以的到内连接的联合查询的结果了。

我们来看看第一种内连接的查询方式：

这些就是内连接的联合查询的方式了。

2）、外连接：

定义：

外连接分为左外连接和右外连接。如果联合查询，左侧的表完全显示我们就说是左外连接；右侧的表完全显示我们就说是右外连接。

语法：

-- 左外连接，表1完全显示
select 字段名 from 表名1 left join 表名2 on 连接条件;

-- 右外连接，表2完全显示
select 字段 from 表名1 right join 表名2 on 连接条件;

那么这个是什么意思呢？我们这样直接看定义不是很理解，我们呢来演示一遍来理解一下：

我们可以看到，对于 class 表中的四班是没有被使用的，那么当我们在对这两个表进行内连接的查询的时候呢，我们是查询不到 class 中这个四班的，那如果我们要是想要显示出这个四班的话呢，我们这里就需要使用到 —— 外连接。

1、右外连接：

这个就是我们的右外连接了，左边的表没有与之匹配的会赋值为 NULL 。

2、左外连接：

这个呢就是我们的右外链接了。

3）、自连接：

自连接是指在同一张表连接自身进行查询。

我们的 where 都是对列与列之间的比较，如果我们要是想要对其进行行与行之间的比较的话呢，我们就需要先把其行与行变成列与列，这样之后呢，我们才能就进行比较，这个时候呢就需要用到——自连接。

我们来演示一遍这个自连接要如何使用：

我们来查找 “计算机组成原理” 成绩比 “Java” 成绩高的信息：

这个就是我们的自连接查询的示例了。

4）、子查询：

子查询是指嵌入在其他 sql 语句中的 select 语句，也叫嵌套查询。

单行子列查询：

语法：

select * from 表名 where 列名 = (select 列名 from 表名 where ...)；

我们来演示一遍：

我们来看一个学生表：

我们来查找对于和 ‘许仙’ 一个班的同学信息：

1、查询 ‘许仙’ 同学的班级编号：

2、由查询的班级编号查询其余的同学：

这几步骤可以有子查询的一步直接完成，我们来看：

这个就是我们的单行子查询。

多行子查询：返回多行记录的子查询

关键字1：

[NOT] IN 关键字：

语法：

select * from 表名1 where 表名1.列名 [not] in (select 列名 from 表名2 where 表名2.列名....);

查询英语或者语文的成绩(查询不包括语文或者英文的成绩)：

这个呢就是对于多行子查询的使用方法了。

关键词2：

[NOT] EXISTS

EXISTS 后面的括号中查询有结果返回则执行外层的查询

反之则不执行外层查询

当存在 NOT 的时候就是上面的结果反过来。

演示：

在from子句中使用子查询：

子查询语句出现在 from 子句中。这里要用到数据查询的技巧，把一个子查询当做一个临时表使用

查询比 “ 中文系2019级3班” 的平均成绩高的学生信息：

1、先计算出平均分：

我们这里使用 3 张表，因为成绩表和班级表之间没有关系，所以要添加一个表：

1）、从班级表中找到对应的编号

2）、根据班级编号从学生表中找到对应的学生

3）、根据学生编号在成绩表中计算平均分

这个是存放在临时表中的。

接下来是在 from 中的临时表的子查询：

5）、合并查询：

在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all。使用UNION 和 UNION ALL 时，前后查询的结果集中，字段需要一致。

UNION ：

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

假如我们想要对两个表进行查询的话，就可以使用这个方法：

UNION ALL ：

该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。

❄️总结：

OK，我们对于 MySQL 中的 CRUD的增删改查呢，到这里就都结束了，对于这个知识点呢，我们要多的进行练习，这是非常重要的知识。到这里就结束了，拜拜~~~