MySQL的聚合查询，分组查询，连接查询（内，外连接），子查询，联合查询

革凡成圣–wjx

已于 2024-06-18 16:43:46 修改

阅读量2.5k

点赞数

文章标签： sql 数据库

于 2022-11-13 15:32:44 首次发布

本文链接：https://blog.csdn.net/weixin_56738054/article/details/127825930

版权

一、聚合查询：

①下面，先介绍第一个关键字：sum

②第二个关键字：count：用于计算某一张表中/或者某一类当中，有多少行：

③第三个关键字：max，顾名思义，求某一列的最大值：

④第四个关键字：avg：求某一列的平均值：

六、left join,right join,inner join，笛卡尔积的区别

INNER JOIN（或 JOIN）：

LEFT JOIN（或 LEFT OUTER JOIN）：

RIGHT JOIN（或 RIGHT OUTER JOIN）：

两个表的笛卡尔积（Cartesian Product）**

一、聚合查询：

这种查询，都是需要使用到聚合的关键字。

①下面，先介绍第一个关键字：sum

这个关键字的含义是针对某一列来求和。

但是需要注意的是，该列一定需要是可以进行数字运算的列，否则不可以进行聚合运算(sum)

②第二个关键字：count：用于计算某一张表中/或者某一类当中，有多少行：

如果sql语句为：select count(*) from +表名称，说明，查询此表当中存在多少行，如果有某一行的属性全部为空，那么这一行将不算在内。同时，也可以把*替换为具体的某一列：

如图：

如果输入select count(grade) from+表名称，那么会返回2.

③第三个关键字：max，顾名思义，求某一列的最大值：

④第四个关键字：avg：求某一列的平均值：

但是如果该列当中出现了为NULL的元素，那把该元素排除，不计算包括这个属性在内的平均值。

⑤第五个关键字：MIN()

求某一列当中的最小值，如果存在为NULL的情况，那么不把NULL看在内：

二、分组查询：group by

分组查询，需要使用到关键字 group by；同时，分组查询一般需要搭配聚合函数，也就是上面介绍的5种聚合函数使用，这样才有意义：

举个例子：

如图所示，为一张员工表，表的结构为:staffId：员工的id主键，名称，年龄，以及部门的id：

其中，部门id相同的成员就是在同一个部门；

这里存在一个问题，前面说过，分组查询一般需要搭配聚合函数来使用，那么这里，如果不搭配聚合函数，直接使用group by，结果会怎样呢？试一下：可以看出，查出来的只是每个部门的第一个员工的年龄，而这种操作，明显没什么意义。

如果把以上sql语句翻译过来，就是：查询每个部门的年龄；这种查询，显然意义不大。

如果把需求变成：查询每个部门的最大/平均年龄，那这种操作，就会有他特定存在的意义了。

比如：

select max(age),depart_Id from staff group by depart_Id

那这样的化，查出来的就是每个部门的平均年龄，于是有了分组查询的意义。

同理，如果想查询每个部门的平均年龄，就可以把sql语句写成：

select avg(age),depart_Id from staff group by depart_Id

三、连接查询

①内连接查询：

是指所有查询出的结果都是能够在连接的表中有对应记录的。需要使用到关键字：inner join:

如图所示，为两张表：

一张为student5的表：里面存储了4个成员变量

另外的，如图所示：下面这个表存放的是学生的考试成绩：

如果想查询出所有参与了考试，并且有学号记录的同学的成绩，就需要使用inner join来查询：

select *from score inner join student5 on score.studentId = student5.id

查询结果如图：

②左外连接：

其中，student5表当中，有一条数据，也就是最后一条，没有出现在成绩表当中。同时，在成绩表当中，最后一条数据也没有出现在学生表当中，这种情况下面，如果想查看所有同学的考试，包括缺考的同学在内的话，应该如何查询呢？首先，引入了连接查询join。

由于此处想查看出学生表的所有信息，但是不一定包含右表当中的所有信息，应当这样查询：

select student5.id,student5.name,score.score

from student5 left join score on student5.id=score.studentId

查询结果如下：

其中，这个查询的规则是：首先确定需要查询两张表的哪些属性，使用表名称.属性名称的形式，列举。接着，使用from 关键字连接需要查询全部数据的表，上图为student5，使用left join 关键字，拼接需要查询的右表，on 后面跟着的是查询的条件。

于是，这样就查询出来了。同样，如果想查询所有参与了考试的学生的信息，可以使用以下的方式

③右外连接:

以上两条查询，可以得出：如果使用left join，那么数据完整的表应当为left join左边的表，如果使用right join，那么完整数据的表为右边的表。

④自连接：（查询两行之间的关系，二者进行比较）

如图所示：如果想查询出score1的成绩比score高的行，就出现了一个问题：由于sql无法针对行之间进行比较，那应当怎样查询呢？

这里就引入了一个概念：自连接：即：一个表关联他自己

第一步：根据表来取别名：

由于自连接也会产生大量的无效数据，因此也需要指定查询条件s1.studentId=s2.studentId;

select *from score as s1,score as s2 where s1.studentId=s2.studentId

得到结果如下：（红线左侧为s1,红线右侧为s2）

那么，上述条件是否可以转化为：s1当中score1比s2当中score大的值的集合：

select *from score as s1,score as s2 where 
s1.studentId=s2.studentId and s1.score1>s2.score

⑤子查询

一个sql当中，把另外一个sql查询的结果当成条件，来拼接成的一个新的sql语句。

继续回到这个表当中：如果想查询score+scor1大于220的同学的行数，可以这样查询：

（但是不推荐这样操作）

select *from score where studentId in
                         (select studentid from score where (score+score1)>220)

需要使用 in 关键字（当返回的列数>1时候），在后面的表达式当中，返回的是，score+score1>200的studentID。如果仅仅有1行返回的话，可以把in替换为=

但是由于这样比较复杂，不易于代码的维护，因此这种做法不常用。

三、联合查询(union)

如图所示：此为一个student表：

表的结构是：name varchar(20),age int

此时，如果想查询出age<15或者age>25的列数，可以这样查询（使用关键字:or)

select *from student where  age>25 or age<15

但是,or关键字只能查询出本表当中的数据，无法查询出不同的两个表并在一起的结果。

倘如有另外一张表，列数和当前表的一模一样，那就可以进行联合查询，使用(union)

如图所示，为两张相同的表；结构都是name varchar(20),age int

如果想原封不动查看两个表的所有数据，就使用union all来查询：哪怕另外一张表中含有第一张表重复的数据；

select *from student6 union all(select *from student)

但是如果想查询第一张表当中的所有数据，但是第二张表里面不包括跟第一张表重复的数据，就使用union：

select * from student6 union (select *from student)

如果这样查询，就可以查询出student6当中所有的数据，但是如果student当中出现了和student6当中相同的数据，那么不会重复出现：

四，mysql删除表的方式，有什么不同?

①使用drop关键字(丢弃数据)

用法:drop table+表名称。

这样会直接把一整张表删除，包括表的结构和表当中的所有数据，相当于这张表从磁盘上面彻底消失。

②truncate(清空数据)

用法:truncate table +表的名称。

需要注意的是，truncate有点类似于恢复"出厂设置"。

执行了truncate table+表的名称这条命令之后，表当中的数据全部删除，如果存在自增的主键，当再次插入数据的时候，就会从1重新开始。

③delete(删除数据)

用法:delete from table（删除全部数据）或者delete from table where ...

和truncate不同的是：

(1)delete可以指定删除某一行数据，可以根据条件删除;

(2)使用delete删除数据之后，如果表当中存在自增的主键，那么下次新增数据的时候，仍然会从上次删除的位置开始新增。例如原来的表当中删除了id为7，6...1的数据，下次再新增的时候，会从8开始。

即使使用delete from +table +表的名称之后，下次新增依然是从最大的自增id那里开始。

五、DDL&DQL&DML&DCL的区别

DDL(database、table)

DDL语句是对于操作数据库(databsee)进行查看、创建、删除、使用操作。

或者对于数据库的表的结构进行一系列的操作(alter table，create table等等)。

DQL(数据查询语言)

例如：select ... from ... where...这样的语句。对于数据库当中表的数据的查询操作。

DML

对于数据库表当中的数据进行增删改操作。

例如：insert、delete、update操作等等。

DCL(权限)

这一个层次的语句，都是和数据库的权限有关系的。并控制数据库操纵事务发生的时间及效果，对数据库实行监视等

六、left join,right join,inner join，笛卡尔积的区别

INNER JOIN（或 JOIN）：

INNER JOIN 返回两个表中都有匹配的行。
如果某个表中的行在另一个表中没有匹配的行，那么该行就不会出现在结果集中。
这是最常见的连接类型，因为它只返回两个表之间真正相关的数据。
示例：假设有两个表，一个是员工表（employees），另一个是部门表（departments）。如果你想要找到每个部门及其员工的列表，并且只关心那些有员工的部门，你会使用 INNER JOIN。

LEFT JOIN（或 LEFT OUTER JOIN）：

LEFT JOIN 返回左表中的所有行，以及右表中匹配的行。如果左表中的某行在右表中没有匹配的行，则结果集中右表的部分将包含 NULL 值。
这种连接类型通常用于当你想要从左表中选择所有行，并且只关心与右表相关的那些行时。
示例：再次使用上面的员工和部门表。如果你想要找到每个员工及其所在的部门，但也要包括那些没有员工的部门（可能表示部门是空的或正在招聘中），你会使用 LEFT JOIN。

RIGHT JOIN（或 RIGHT OUTER JOIN）：

RIGHT JOIN 与 LEFT JOIN 相反。它返回右表中的所有行，以及左表中匹配的行。如果右表中的某行在左表中没有匹配的行，则结果集中左表的部分将包含 NULL 值。
在实际使用中，RIGHT JOIN 不如 LEFT JOIN 常见，因为你可以通过简单地交换两个表的顺序并将 LEFT JOIN 转换为 RIGHT JOIN 来达到相同的效果。
示例：虽然不常见，但如果你想要找到每个部门及其员工，但也要包括那些没有员工的部门，并且你更习惯于从右表（部门表）开始查询，你可以使用 RIGHT JOIN。但请注意，这只是一个习惯问题，使用 LEFT JOIN 和交换表顺序可以达到相同的效果。
总结：选择哪种连接类型取决于你的具体需求和你想要从查询中获得的结果。在大多数情况下，INNER JOIN 和 LEFT JOIN 是最常用的连接类型。