MySQL视图

目录

1.使用环境

2.视图的概念和作用

3.视图操作

4.视图应用

---

1.使用环境

数据库：MySQL 8.0.30

客户端：Navicat 15.0.12

2.视图的概念和作用

        什么是视图？数据库中的视图是一种虚拟的表，是根据对一个或多个实际表的查询结果来创建的。视图看起来就像真实的表，但实际上并不存储数据。用户可以像操作普通表一样对视图进行查询、更新和删除操作，而不必直接访问源表。

        视图的作用包括简化复杂查询、提供安全性、隐藏表的结构和限制数据访问等。通过视图，可以将复杂的查询逻辑封装起来，使用户能够通过简单的接口访问数据，同时也能保护源表的数据完整性和安全性。

        如果视图引用的原始数据发生了变化，视图引用的数据也会随之发生变化。

3.视图操作

创建视图：

create view 视图名称 [ (视图字段列表) ] as select语句;
create view t_view as select * from student;
create view t_view(sname,gender) as select sname,gender from student;

查询视图：

select * from 视图名称;

修改视图：

alter view 视图名称 as SQL语句;

删除视图：

drop view 视图名称;

4.视图应用

这里使用RFM模型来举栗子，可参考之前文章中的用户分层(RFM模型)小节：

网易云音乐用户消费行为分析-CSDN博客

按用户价值分层—RFM模型：

• 最近一次消费-R：客户最近一次交易时间的间隔,上一次消费时间离现在越近。即 R 值越小，用户的活跃度越大，用户的价值就越高。
• 消费频率-F：客户在最近一段时间内交易的次数。购买频率越高，说明用户对品牌产生一定的信任和情感维系。即 F 值越大，用户的忠诚度就越大，用户的价值就越高。
• 消费金额-M：客户在最近一段时间内交易的金额。M值越大，表示客户价值越高，反之则表示客户价值越低。

我们在test数据库中创建表RFM：

create table rfm(order_id int(10) PRIMARY KEY,
				 date_time date,
                 customer_name varchar(50),
                 count int(10),
                 pay_money int(10));

> OK
> 时间: 0.007s

填充数据：

insert into rfm values
(1001 , '2024-01-01' ,'张三', 8 , 109),
(1002 , '2024-01-01' ,'李四', 11 , 121),
(1003 , '2024-01-01' ,'王五', 35 , 409),
(1004 , '2024-01-01' ,'赵六', 26 , 301),
(1005 , '2024-01-02' ,'陈七', 45 , 531),
(1006 , '2024-01-02' ,'孙八', 66 , 779),
(1007 , '2024-01-02' ,'王五', 42 , 521),
(1008 , '2024-01-03' ,'赵六', 33 , 411),
(1009 , '2024-01-03' ,'陈七', 58 , 655),
(10010 , '2024-01-03' ,'孙八', 65 , 781),
(10011 , '2024-01-03' ,'张三', 77 , 935),
(10012 , '2024-01-04' ,'李四', 98 , 1115),
(10013 , '2024-01-04' ,'王五', 87 , 1065),
(10014 , '2024-01-04' ,'赵六', 25 , 267),
(10015 , '2024-01-04' ,'王五', 18 , 190);

> Affected rows: 15
> 时间: 0.002s

计算R：

计算时间间隔：

select date_time,current_date() as currentdate,datediff(current_date(),r.date_time) as 'interval' from rfm r;

 创建视图v_r(计算R指标的sql)：

create view v_r as (select a.customer_name as name1,min(a.interval) as R from
       (select customer_name,datediff(current_date(),date_time) as 'interval' 
       from rfm) as a group by a.customer_name);

> OK
> 时间: 0.002s

将计算出R的表和原始rfm表的数据进行汇总：

select * from rfm as r inner join v_r on v_r.name1 = r.customer_name;

为汇总后的表创建视图v_r_new：

create view v_r_new as
      (select * from rfm as r inner join v_r on v_r.name1 = r.customer_name);

> OK
> 时间: 0.002s

根据用户的名字分组，统计每组用户出现的次数，即每个用户消费的次数F：

select customer_name, count(1) as F from rfm group by customer_name;

创建视图v_f：

create view v_f as(select customer_name as name2, count(1) as F from rfm group by customer_name);

> OK
> 时间: 0.001s

将v_f和v_r_new进行汇总：

select * from v_r_new inner join v_f on v_r_new.name1 = v_f.name2;

将汇总后的表创建为视图v_f_new：

create view v_f_new as ( select * from v_r_new inner join v_f on v_r_new.name1 = v_f.name2);

> OK
> 时间: 0.002s

计算M：

计算每位客户的平均消费金额：

select customer_name,avg(pay_money) as M from rfm group by customer_name;

创建视图v_m：

create view v_m as (select customer_name as name3,avg(pay_money) as M from rfm group by customer_name);

> OK
> 时间: 0.002s

汇总数据：

select * from v_f_new inner join v_m on v_m.name3 = v_f_new.customer_name;