MySQL-中阶

1.SQL多表联结查询

1.1联结的类型

类型

1.2 join练习

练习：将订单表、地区表、产品类别表联结成一张表

select * from 
spm_order a 
LEFT JOIN spm_area b
on a.city = b.`城市`
LEFT JOIN spm_product c
on a.product_id = c.`产品 ID`

上述语句中left join查询结果比基表spm_order条数多（应该一致），检查发现是b表中的城市有重复数据。于是修改如下：

注意：用distinct关键字只能过滤查询字段中所有记录相同的（记录集相同），而如果要指定一个字段却没有效果,此时要用group by。

关于查询出字段重复的例子：

spm_area表中仅有城市字段重复，其他字段未重复。查询出重复城市的sql如下：

select *
from spm_area
where `城市` in 
(select `城市` from spm_area GROUP BY `城市`
having count(1)>1)
ORDER BY `城市`

显示重复城市记录数：

select `城市`,count(1) from spm_area GROUP BY `城市`

2.1 条件函数：case when

语法结构：
case when 条件 then '结果'
     when 条件 then '结果'
     when 条件 then '结果'
     ......
     else '默认值'
end as '新字段名'

练习：计算出盈利订单、不盈利订单和不盈不亏订单的销售额

select sum(sales) as '销售额', 
case when profit > 0 then '盈利'
     when profit = 0 then '不盈不亏'
     else '亏损'
     end as '订单类型'
from spm_order
GROUP BY
case when profit > 0 then '盈利'
     when profit = 0 then '不盈不亏'
     else '亏损'
end

2.2分组排序函数(Row_number)

用法1：无分组排序
Row_number() over(order by 字段 desc)
用法2：分组排序
Row_number() over(partition by 字段1 order by 字段2 desc)
表示根据字段1分组,在分组内部根据字段2排序,这个函数计算的值就表示每组内部排序后的顺序编号

解释：
Row_number()     起到了编号的功能
partition by     将相同数据进行分区
order by         使得数据按一定顺序排序

计算销售人员的销售额,结果按从高到低排序,查询结果中要包含销售的排名：

mysql 8.0版本：
select sales_name,sum(sales),Row_number() over(order by sum(sales) desc) as 'rank'
from spm_order
GROUP BY sales_name


mysql 5.7版本：
set @rank=0;
select
t1.*, @rank := @rank + 1 as rank_num
from
(select sales_name,sum(sales) from spm_order
group by sales_name
order by sum(sales) desc)t1

计算销售人员在不同城市的销售额;
要求：结果根据销售人员在不同城市的销售额进行分组排序（降序）,并且查询结果要包含分组排名

select sales_name, city, sum(sales), 
Row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
GROUP BY sales_name,city

2.3 嵌套查询

语法结构

select t1.*
from
(select 字段 from 表名)t1

计算每个销售人员销售额排名top5的城市

select t1.sales_name, t1.city, t1.sales, t1.rank
from
(select sales_name, city,sum(sales) as 'sales', 
Row_number() over(partition by sales_name order by sum(sales) desc) as 'rank'
from spm_order
GROUP BY sales_name, city) t1 
where t1.rank <= 5

2.4 子查询(IN)

语法结构：

select 字段
from 表名
where 字段 in （select 字段 from 表名 where 条件）

2.5 组合查询（union）

语法结构：

select 字段
from 表名

union

select 字段
from 表名

union

select 字段
from 表名

union与union all的区别：

union：去重，返回去重后的结果
union all：不去重，返回所有

练习：

查询利润大于0的订单;
查询城市在"北上广深杭"的订单;
并且将两个查询结果合并（不去重）;
select * from spm_order
where profit > 0
union all
select * from spm_order
where city in ('北京','上海','广州','深圳','杭州')