Mysql高级

Mysql要求8.0+

窗口函数：

create table user_trabe
(
user_name varchar(20),-- 用户名
piece int ,-- 购买数量
price double,-- 价格
pay_amount double,-- 支付金额
goods_category varchar(20), -- 品类
pay_time date -- 支付日期
);

-- 查出2021年每月的支付总额和当年累计支付总额

-- step1 过滤出2021年数据
select * from user_trabe where year(pay_time)=2021;

-- step2 在1的基础上，按照月份进行group by分组，统计每个月份的支付总额
select month(pay_time),sum(pay_amount) from user_trabe where year(pay_time)=2021 GROUP BY month(pay_time);
-- step3 在2的基础上应用窗口函数是实现需求
select 
 a.mointh,     --月份
 a.pay_amount, --当月总支付金额
 sum(a.pay_amount) over (ORDER BY a.month)

--此时没有使用rows指定窗口数据范围，默认当前行及其之前的所有行
from
(select month(pay_time) month,sum(pay_amount) pay_amount

from user_trabe

where year(pay_time)=2021
GROUP BY month(pay_time)
)a

-- 需求3 查询出2021年每月的近三月移动平均支付金额
-- 操作rows窗口范围
select a.month,
             a.pay_amount,
             avg(a.pay_amount) over(ORDER BY a.`month` rows between 2 preceding and current row)avg_pay_amount
FROM
(select month(pay_time) month,sum(pay_amount) pay_amount from user_trabe where year(pay_time)=2021
GROUP BY month(pay_time))a

--需求4  查出每四个月的最大月总支付金额

select
        a.month,
        a.pay_amount,
        max(a.pay_amount) over(ORDER BY a.month rows between 3 preceding and current row)max_pay_amount
        from
-- 1把各个月（yyyy-mm）的月度支付总额算出来（分组聚合sum统计）
(select 
    substring(pay_time,1,7) month,
    sum(pay_amount)pay_amount
from
    user_trabe
GROUP BY
substring(pay_time,1,7))a

排序函数

-- 需求5 2021年6月，购买商品品类的用户排名

-- 先把各个用户所购买商品涉及的品类数给统计出来
select user_name,
             count(distinct goods_category) category_count,
             row_number() over(ORDER BY count(distinct goods_category)) order1, -- row_number生成了行的编号1开始
             rank() over(ORDER BY count(distinct goods_category)) order2,-- 排名1有多名用户是显示1，从第四为开始变化是显示是4
             dense_rank() over(ORDER BY count(distinct goods_category)) order3-- 多名用户排名为一都显示为1，发生变化时从2继续开始记录
from user_trabe 
where substring(pay_time,1,7)='2021-06' 
GROUP BY user_name

-- 需求6 查出2021年6月的支付用户，按照支付金额分成5组的结果
-- 过滤基础数据
select * from user_trabe where substring(pay_time,1,7)='2021-06'

-- 求出各个用户的支付总金额
select user_name,
             sum(pay_amount) pay_amount
 -- 将支付总金额[最小，最大]切成5片，看一看各个用户处于哪一片（什么水平）
             ntile(5) over(ORDER BY sum(pay_amount) desc)leve1
from user_trabe 
where substring(pay_time,1,7)='2021-06'
GROUP BY user_name

-- 需求7 查出2021年支付金额排名前30%的所有用户
select a.user_name,a.pay_amount,a.leve1
FROM
(select user_name,-- 基础数据前提2021 
             sum(pay_amount)  pay_amount,
             ntile(10) over(ORDER BY  sum(pay_amount) desc) leve1
from user_trabe
where year(pay_time)='2021' 
GROUP BY user_name)a where a.leve1 in(1,2,3)

偏移分析函数

 
-- 需求10 查询出支付时间间隔超过100天的用户数
select count(distinct user_name)
from
(select user_name,
             pay_time,
             lead(pay_time,1) over(partition by user_name ORDER BY pay_time)lead_time
FROM user_trabe)a where datediff(a.lead_time,a.pay_time)?100

 

索引

什么是索引

索引可以提升查询速度，会影响where条件查询（where xxx=''),order by(order by yyy)排序

索引是针对字段的，需要添加到字段上

索引在大量数据场景下效果明显

常见索引分类

从索引的存储结构划分：B Tree索引、Hash索引、fulltext全文索引、R Tree索引

从应用层次划分：主键索引、唯一索引、普通索引、复合索引

从索引的键值（字段）类型划分：主键索引、辅助索引（二级索引）

从索引和内容数据逻辑关系划分：聚集索引（聚族索引）、非聚集索引（非聚族索引）

 普通索引

创建普瑞索引

create index 索引名 on 表名(列名[长度])
create index book_index on book(name)  单列索引
create index book_index on book(name,sex) 复合索引（name要字段要在sex字段前面）
删除索引
alter table book drop index index_book

 视图

创建视图
create view user_view
as
select * from user

查询视图 
select * from user_view