【MYSQL笔记】分组计算&多表查询_mysql分组计算

分组显示

 

select 列名 from 表名 group by 用于分组的列名

分组后的处理对象是“同一组的所有记录”

例：id，sales,month

根据员工id分组，并显示每位员工的总销售额

select id,sum(sales) as 合计
from tb 
group by id

 根据员工id分组，并计算每组销售额的平均值

select id,avg(sales) from tb
group by id;

 设置条件分组显示

select 统计列 from 表名 by 分组列 having 条件

用于提取记录的having是在分组之后执行的 

例：按员工id分组计算总销售额，仅显示小组总销售额大于等于200万元的记录

select id,sum(sales) from tb
group by id
having sum(sales)>=200;

如果提取记录后分组：

例：仅提取销售额大于等于50万元的记录，并以该记录为对象计算各员工的平均销售额

select id,avg(sales)
from tb where sales>=50
group by id;

如果 分组后排序：

例：将各员工的平均销售额按从高到底的顺序显示

select id,avg(sales)
from tb
group by id
order by avg(sales) desc;//先分组后排序

记住顺序：where->group by->order by (desc)

使用多个表：

示例表：

 从多个表取出记录汇总到一起显示

select 列表1 from 表名1 union select 列名2 from 表名2;

如果合并相同列结构的两个表

select *from tb1 
union 
select *
from tb2;

 按条件合并多条提取结果进行显示：

在各个命令的最后加上where条件

例：将tb中销售额大于等于200万元的员工号，表tb1中年龄大于等于35岁的老员工的员工号（满足任意一个即可）合并起来

(select id from tb where sales>=200)
union
(select id from tb1 where age>=35);

执行该语句时会自动执行“消除重复记录”的操作。如果对大量记录执行“消除重复”的操作，就会产生的一定的等待时间。

消除重复：

(select id from tb where sales>=200)
union all
(select id from tb1 where age>=35);
//在union后加上all允许重复

连接多个表并显示（内连接）：

将多个表通过某个连接键连接在一起的处理称为“连接”。使用join连接两个表

select 列名 from 表1
join 要连接的表2
on 表1的列=表2的列

选择列进行显示：把列名写成“表名.列名”。

只显示id,name,sales

select tb.id,tb1.name,tb.sales from tb
join tb1
on tb.id=tb1.id;

给表添加别名：

select *from tb as x;

 显示列名可以写成“别名.列名”,可以化简书写

例：tb表别名为x，tb1表别名为y，显示tb中的id和sales，以及tb1中的姓名

思路：将tb的列id与tb1的列id相匹配的记录进行连接，然后显示表tb的列id，表tb1,的列name和表tb的列sales。这些处理在给表tb添加别名"x",给表tb1添加别名“y”的前提下进行。

select x.id,y.name,x.sales
from tb as x
join tb1 as y
on x.id=y.id;

 使用using 使on 的部分更容易阅读

当作为连接键的两个列名相同，可以用using化简

select x.id,y.name,x.sales
from tb as x
join tb1 as y
using(id);

通过where设置条件从连接表中提取记录

在最后加上where条件。列名要按照“表名.列名”（或者别名.列名）进行书写。

例：将tb的中的销售额大于等于100万元的优秀成绩为对象的id，sales，连同tb1中的姓名合并显示处来。

思路：根据id将表tb和表tb1进行连接，显示表tb的列sales中值大于等于100的记录。显示列id,列name,列sales,并给它们分别加上别名“员工号”，“姓名”，“销售额”

select tb.id as 员工号,tb1.name as 姓名,tb.sales as 销售额
from tb join tb1
using(id)
where tb.sales>=100

提取多个表中的记录

select ~ from tb
join tb1 连接条件(on或者using)
join tb2 连接条件
join tb3 连接条件
...;

例:连接销售信息表tb，员工信息表tb1，员工出生地表tb3，显示员工号(tb.id),销售额(tb.sales),姓名（tb1.name），出生地(tb3.religion)。

select tb.id,tb.sales,tb1.name,tb3.region
from tb
join tb1 using(id)
join tb3 using(id);

显示多个表的所有记录（外连接）

使用join的内连接只会提取与连接键相匹配的记录，即共有的记录。如果要显示不是共有的记录，就需要使用外连接。

即使与连接键不匹配，外连接也会提取另一个表中的所有记录。

外连接的种类：

select from 表1 join 表2

左外连接：显示相匹配的记录和表1的全部记录

右外连接：显示相匹配的记录和表2的全部记录

使用左外连接：

select 列名
from 表1
left join 要连接的表2
on 表1的列=表2的列

 例：将id作为连接键，使用左外连接显示表tb和表tb1相匹配的记录，以及表tb的所有记录。但是仅显示表tb的列id，和tb1的列name

select tb.id,tb1.name
from tb
left join tb1
using(id);

如果tb中的id在tb1中没有记录，那么name显示为null

使用右外连接：

select 列名 from 表1
right join 要连接的表2
on 表1的列=表2的列;

例：以id为键，使用右外连接显示表tb和表tb1相匹配的记录，以及表tb1的所有即。仅显示表tb的列id和表tb1的列name

select tb.id,tb1.name
from tb
right join
tb1
using(id);

 混合使用左外连接和右外连接可能会导致日后发生错误。

注：left join 可以写成left outer join 。同样，right join 可以写成right outer join 

自连接：

将表与其自身，也就是和同名的表进行连接。为了区分不同的列，连接时必须起别名

select 列名 from 表名 as 别名1 
join 表名 as 别名2;

例：对员工信息tb1进行自连接，并把所有的列显示出来

select * from tb1
as a
join tb1
as b;

 由于表tb1是和不同名的自身进行连接，所以自身拥有的所有记录将与自身拥有的所有记录进行连接（n*n）

自连接的作用：

自连接会包含所有的组合。可以通过筛选设置条件选出想要的内容？

子查询：

执行查询（子查询），然后使用检索到的记录进一步执行查询。

例如，在第一阶段提取“销售额大于等于200万元的员工号”，然后在第二阶段中进行“从提取出来的员工号中提取相对应的姓名”。第一阶段的查询叫做子查询

第一阶段的子查询可以返回值，列和记录等。

例：显式表tb中sales最大值的记录(返回值的子查询)

思路：在第一阶段查询中取出max(sales)的值，然后在第二阶段选择了包含了最大值列sales的记录

select * from tb 
where sales in (select max(sales) from tb); 

例；计算tb1中员工的平均年龄，并提取大于等于平均年龄的员工记录（返回值的子查询）

select * from tb1 
where age>=(select avg(age) from tb1);

例：显式销售额大于等于200万元的员工姓名（返回列的子查询）：使用in

思路：提取销售信息表tb中的销售额（sales）大于等于200的id，然后显示tb1中的姓名

select * from tb1
where id in
(select id from tb where sales>=200);

当只有一条记录时， 使用=也无妨

子查询和内连接提取结果的差异:

子查询：

select id,name
from tb1
where id in(select id from tb);

这里，会显示tb1中和tb id相匹配的记录

内连接：

select tb1.id,tb1.name
from tb1
join tb
on tb1.id=tb.id;

这里，会显示tb中的所有记录