mysql练习

-- MySQL 如何实现upsert的功能
/*
可以在INSERT INTO…..后面加上 ON DUPLICATE KEY UPDATE方法来实现。如果您指定了ON DUPLICATE KEY UPDATE，
并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则执行旧行UPDATE。
例如，如果列a被定义为UNIQUE，并且包含值1，则以下两个语句具有相同的效果：
*/
-- INSERT INTO testupsert (`a`, `b`, `c`) VALUES (1, 2, 3) ON DUPLICATE KEY UPDATE `c`=`c`+1;
-- UPDATE testupsert SET `c`=`c`+1 WHERE `a`=1;
#如果行作为新记录被插入，则受影响行的值为1；如果原有的记录被更新，则受影响行的值为2
# 注释：如果列b也是唯一列，则INSERT与此UPDATE语句相当：
-- UPDATE `testupsert` SET `c`=`c`+1 WHERE `a`=1 OR `b`=2 LIMIT 1;

/*
-- 1 、客户信息分析
-- 1).最受欢迎的信用卡
-- 2).前 5 个最多的客户职业
-- 3).前三个美国女性持有的最流行的行用卡
-- 4).按性别和国家进行客户数量统计

-- 2 、交易分析
-- 1).按月度统计总收益（每年各月）
-- 2).按季度统计总收益（每年各季度）
-- 3).按年统计总收益
-- 4).统计每周各天的总收益
-- 5)按时间段统计平均收益和总收益
-- 6).统计消费次数排行前 10 位的客户
-- 7).统计消费额前10位客户
-- 8).统计每年度、季度总客户数
-- 9).找出平均消费额最大的客户
-- 10).统计最受欢迎的产品（分别从购买客户数量、购买频次、消费额三个维度分析）

-- 3 、门店分析
-- 1).按客流量找出最受欢迎的门店
-- 2).按客户消费额找出最受欢迎的门店
-- 3).按交易频次找出最受欢迎的门店
-- 4).按客流量找出每个门店最受欢迎的商品
-- 5).统计每个门店客流量与雇员的比率
-- 6).按年度-月份统计每家门店的收益
-- 7).找出每家门店最繁忙的时刻
*/
-- 1 、客户信息分析
-- 1).最受欢迎的信用卡
select distinct credit_type
from customer_details;

select credit_type, count(credit_type) cnt
from customer_details group by credit_type order by cnt desc ;

-- 2).前 5 个最多的客户职业
select job, count(job) job_cnt
from customer_details group by job order by job_cnt desc limit 5;

-- 3).前三个美国女性持有的最流行的行用卡
select credit_type, count(credit_type) cnt
from customer_details
where country = 'United States' and gender = 'Female'
group by credit_type order by cnt desc  limit  3;

-- 4).按性别和国家进行客户数量统计
-- 扩充需求：male、female对应成男、女，行转列显示
--  男     女
--  260    240
select country,count(*),gender
from customer_details group by country,gender;

select country,count(country) country_cnt,gender,count(gender) country_gender_cnt
from customer_details group by country,gender;

select distinct gender
from customer_details;
-- male     female
-- 结果 男   女
-- 10       20
select gender,count(gender) ender_cnt
from customer_details group by gender;

-- 2 、交易分析
-- 1).按月度统计总收益（每年各月）
-- 18-12  1000
-- 19-11  2000
select CONCAT_WS('-',YEAR(date),MONTH(date)) as 'year_month',format(sum(price),5) cnt
from transactions group by CONCAT_WS('-',YEAR(date),MONTH(date))
order by cnt desc ;

-- 2).按季度统计总收益（每年各季度） 你要熟悉我们常用的函数
select CONCAT_WS('-',YEAR(date),QUARTER(date)) as 'year_month',format(sum(price),5) cnt
from transactions group by CONCAT_WS('-',YEAR(date),QUARTER(date))
order by cnt desc ;

select year_quarter,format(sum(price),5) from (
	select
		price ,
		CONCAT(DATE_FORMAT(date,'%y'),'-',CEIL(DATE_FORMAT(date,'%m')/3.0)) as year_quarter
	from transactions
) base
GROUP BY year_quarter;

-- 3).按年统计总收益
select YEAR(date) 'year',format(sum(price),5) cnt
from transactions group by YEAR(date)
order by cnt desc ;

select date_format(date,'%Y') 'year',format(sum(price),5) cnt
from transactions group by YEAR(date)
order by cnt desc ;

select date_format(date,'%y') 'year',format(sum(price),5) cnt
from transactions group by YEAR(date)
order by cnt desc ;

-- 4).统计每周各天的总收益
-- 你可以使用  date_format(date,'%y')
select date_format(now(),'%w'); -- 2
select date_format(now(),'%W'); -- Tuesday
-- （7）DAYNAME(d)、DAYOFWEEK(d)、WEEKDAY(d)
select DAYNAME(now()); -- Tuesday

select DAYNAME(date) `day`,format(sum(price),5)
from transactions group by DAYNAME(date);

select
	case
	when DATE_FORMAT(date,'%w')='6' then 'Sunday'
	when DATE_FORMAT(date,'%w')='5' then 'Saturday'
	when DATE_FORMAT(date,'%w')='4' then 'Friday'
	when DATE_FORMAT(date,'%w')='3' then 'Thursday'
	when DATE_FORMAT(date,'%w')='2' then 'Wednesday'
	when DATE_FORMAT(date,'%w')='1' then 'Tuesday'
	when DATE_FORMAT(date,'%w')='0' then 'Monday'
	end as week_day,
    format(sum(price),5)
from transactions
group by week_day;

-- 5)按时间段统计平均收益和总收益
/*
when time_in_hrs > 5 and time_in_hrs <= 8 then 'early morning'
	when time_in_hrs > 8 and time_in_hrs <= 11 then 'morning'
	when time_in_hrs > 11 and time_in_hrs <= 13 then 'noon'
	when time_in_hrs > 13 and time_in_hrs <= 18 then 'afternoon'
	when time_in_hrs > 18 and time_in_hrs <= 22 then 'evening'
	else 'night'
*/
select
format(sum(price),3) as time_total_revenue,
format(avg(price),3) as time_avg_revenue,
case
	when time_in_hrs > 5 and time_in_hrs <= 8 then 'early morning'
	when time_in_hrs > 8 and time_in_hrs <= 11 then 'morning'
	when time_in_hrs > 11 and time_in_hrs <= 13 then 'noon'
	when time_in_hrs > 13 and time_in_hrs <= 18 then 'afternoon'
	when time_in_hrs > 18 and time_in_hrs <= 22 then 'evening'
	else 'night'
end as time_slot
from
(
	select
		time,
		price,
	       -- 时间转为统一的单位：小时
		(cast(SUBSTRING_INDEX(time,':',1) as DECIMAL(4,2)) + cast(SUBSTRING_INDEX(time,':',2) as DECIMAL(4,2))/60) as time_in_hrs
	from transactions
) base
group by time_slot;

select hour(now());

select
format(sum(price),3) as time_total_revenue,
format(avg(price),3) as time_avg_revenue,
       case
           	when hour(time) > 5 and hour(time)<= 8 then 'early morning'
            when hour(time) > 8 and hour(time)<= 11 then 'morning'
            when hour(time) > 11 and hour(time) <= 13 then 'noon'
            when hour(time) > 13 and hour(time) <= 18 then 'afternoon'
            when hour(time) > 18 and hour(time) <= 22 then 'evening'
	else 'night'
       end as time_slot
from transactions
group by time_slot;

-- 6).统计消费次数排行前 10 位的客户 (若需要客户的其他信息，需要关联客户表)
-- 注意：这里的 transaction_id 有重复
-- todo 取出重复的记录，以及对应的客户id
select t.customer_id,concat_ws('-',cd.first_name,cd.last_name),t.cnt
from customer_details cd
inner join (
select customer_id,count(transaction_id) cnt
from transactions group by customer_id order by cnt desc limit  10) t
on cd.customer_id = t.customer_id;

-- 参考代码
select
	cd.first_name as cust_name,
	count(DISTINCT ts.transaction_id) as trans_count
from transactions ts
join customer_details cd on ts.customer_id = cd.customer_id
GROUP BY ts.customer_id order by trans_count desc limit 10;

-- 7).统计消费额前10位客户
select cust_name, sum(price) as spend_total from
(
	select
		DISTINCT ts.transaction_id,
		cd.first_name as cust_name,
		ts.price,ts.customer_id
	from transactions ts
	join customer_details cd on ts.customer_id = cd.customer_id
)base
GROUP BY customer_id order by spend_total desc;

-- 8).统计每年度、季度总客户数 2年，最多8条数据
-- 18 18-11  10
-- 18 18-10  20
select YEAR(date) 'year',concat_ws('-',year(date),quarter(date)) as 'year_quarter',
       count(distinct customer_id) total_count
from transactions group by YEAR(date), concat_ws('-',year(date),quarter(date));

-- todo 这个参考存在质疑
select
`year`,
year_quarter,
count(DISTINCT customer_id) as total_cust
from
(
	select
		customer_id,
		CONCAT(DATE_FORMAT(date,'%y'),'-',CEIL(DATE_FORMAT(date,'%m')/3.0)) as year_quarter,
		DATE_FORMAT(date,'%y') as year
	from transactions
) base
group by `year`,year_quarter
order by total_cust desc ;

-- 9).找出平均消费额最大的客户
-- 1.拿到每个客户的平均消费，
-- 2.拿平均值的最大值，然后跟客户信息关联
select cd.customer_id, cd.first_name, t.avg_price
from customer_details cd
         inner join (
    select customer_id, avg(price) avg_price
    from transactions
    group by customer_id
    order by avg_price desc
    limit 1) t
                    on cd.customer_id = t.customer_id;

-- todo 啥破答案
select customer_id,max(avg_price) from
(
	select
		customer_id,
		avg(price) as avg_price
	from transactions
	GROUP BY customer_id
) base;

-- 10).统计最受欢迎的产品（分别从购买客户数量、购买频次、消费额三个维度分析）
-- todo 能不能将三个维度放在一起：最多15条数据
/*
  product   freq_cust    freq_buy  price_sum
  p1            15         0          0
  p2            15          10       20
  p3            0           10        10
*/
-- 1)购买客户数量
select
	product,
	count(customer_id) as freq_cust
from transactions
group by product order by freq_cust desc limit 5;
-- 2)购买频次
select
	product,
	count(DISTINCT transaction_id) as freq_buy
from transactions
GROUP BY product order by freq_buy desc limit 5;
-- 3)消费额
select
	product,
	sum(price) as price_sum
from transactions
GROUP BY product order by price_sum desc limit 5;

-- 3 、门店分析
-- 1).按客流量找出最受欢迎的门店
select sd.store_name,total_cust from
(
    -- 1.1 拿到每个门店有多少人光临过
	select
		store_id,
		count(DISTINCT customer_id) as total_cust
	from transactions
	group by store_id
) ts
join store_details sd  on ts.store_id = sd.store_id
order by total_cust desc ;

-- 2).按客户消费额找出最受欢迎的门店
select sd.store_name,store_total_price from
(
    -- 2.1 统计每个门店的销售额
	select
		store_id,
		sum(price) as store_total_price
	from transactions
	group by store_id
) ts
join store_details sd  on ts.store_id = sd.store_id
order by store_total_price desc;

-- 3).按交易频次找出最受欢迎的门店
select sd.store_name,store_total_trans from
(
	select
		store_id,
		count(DISTINCT transaction_id) as store_total_trans
	from transactions
	group by store_id
) ts
join store_details sd  on ts.store_id = sd.store_id
order by store_total_trans desc;

-- 4).按客流量找出每个门店最受欢迎的商品
-- 这里面可能最难的一个
select store_name,product,max_cust from
(
	select temp.store_id,temp.product,A.max_cust from
	(
	    -- 4.1 根据门店和商品拿到每个门店的每个商品的客流量
			select
				store_id,
				product,
				count(DISTINCT customer_id) as freq_cust
			from transactions
			group by store_id,product
	) temp
	join
	    -- 4.2 拿到每个门店最大的客流量
	(select store_id,max(freq_cust) as max_cust from
		(
			select
				store_id,
				count(DISTINCT customer_id) as freq_cust
			from transactions
			group by store_id,product
		) temp
	group by store_id) A
	on temp.freq_cust = A.max_cust and temp.store_id = A.store_id
) base
join store_details sd on base.store_id = sd.store_id;

-- 5).统计每个门店客流量与雇员的比率
-- 5.1 拿到每个门店的客流量
-- 5.2 关联store_details表，有员工数，进行除法即可
select sd.store_id,sd.store_name,format(t.freq_cust/sd.employee_number,5) percent
from store_details sd
left join (
select
    store_id,
    count(DISTINCT customer_id) as freq_cust
from transactions
group by store_id ) t
on sd.store_id = t.store_id;

select
	sd.store_name,
	base.cust_visit,
	sd.employee_number,
	round(base.cust_visit/sd.employee_number,5) as cust_per_employee_within_period
from (
	select
		store_id,
		count(DISTINCT customer_id,date) as cust_visit
	from transactions
	group by store_id
) base
join store_details sd on sd.store_id = base.store_id;

-- 6).按年度-月份统计每家门店的收益
select
	store_name,
	per_month,
	total_revenue_per_month
from (
	select
		store_id,
		DATE_FORMAT(date,'%y-%m') as per_month,
		sum(price) as total_revenue_per_month
	from transactions
	group by store_id,per_month
) base
join store_details sd on base.store_id = sd.store_id;

-- 7).找出每家门店最繁忙的时刻
select store_name,time_slot,total_transaction from (
	select
		store_id,
		count(transaction_id) as total_transaction,
		case
			when time_in_hrs > 5 and time_in_hrs <= 8 then 'early morning'
			when time_in_hrs > 8 and time_in_hrs <= 11 then 'morning'
			when time_in_hrs > 11 and time_in_hrs <= 13 then 'noon'
			when time_in_hrs > 13 and time_in_hrs <= 18 then 'afternoon'
			when time_in_hrs > 18 and time_in_hrs <= 22 then 'evening'
			else 'night'
		end as time_slot
	from
	(
		select
			store_id,
			time,
			transaction_id,
			(cast(SUBSTRING_INDEX(time,':',1) as DECIMAL(4,2)) + cast(SUBSTRING_INDEX(time,':',2) as DECIMAL(4,2))/60) as time_in_hrs
		from transactions
	) tmp
	group by time_slot,store_id
)base
join store_details sd on sd.store_id = base.store_id ;

-- ------------------窗口函数 8.0 开始支持 ------------------------
--  你可以装个8.0尝鲜一把，或者装个oracle也可以 ------------------------
-- https://blog.csdn.net/weixin_39010770/article/details/87862407
-- ①序号函数：ROW_NUMBER()、RANK()、DENSE_RANK()

-- ------------------视图-----------------------
-- create or replace view v_trans
create view v_store_details
as
select store_id, store_name, employee_number
from store_details;

select *
from v_store_details;

-- 修改视图名称
RENAME table v_store_details TO aaaa;

-- 修改视图数据对原始数据【基表】是否有影响？  有
-- 修改基表数据是否对视图有影响？  有
-- 删除/新增视图/基表数据，对基表/视图是否有影响？
-- 删除视图数据，没有影响
-- 新增数据，基表也发生变化了

delete from v_store_details where store_id = 6;

drop view v_store_details;

-- ------------------数据库设计 -----------------


-- ------------------存储过程 --------------------
-- 涉及到SQL编程 ---业务 分支、循环
-- PLSQL编程 ---》 Oracle的ERP的二次开发  ERP实施
-- SQL中变量的声明和使用
-- 最基本的变量声明，直接赋值
set @my_name := 'zs';
select @my_name;

-- 某个变量值来源于查询结果
select @my_store_name :=store_name ,@emp_num := employee_number
from store_details limit 1;

select @my_store_name;

-- 	只赋值，不看过程
select store_name,employee_number into @my_store_name2,@emp_num2
from store_details limit 1;

select @my_store_name2;

-- 假设没有大小写转换，你能否自定义这样的函数 ，求和 。。。
delimiter ##
create function my_fun(p1 int) returns int
begin
    if p1 > 10 then return p1 + 10;
        end if;
end ##

-- 调用函数
select my_fun(20);

-- -------------------触发器 ---------------------
-- 需求：你在某个系统上执行了一个删除操作后，可以在另外一个日志表中记录下来
/*
Create trigger 触发器名字 触发时机 触发事件 on 表 for each row
Begin
End
触发对象：on 表 for each row，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器。

触发时机
触发时机：每张表中对应的行都会有不同的状态，当SQL指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后

Before：在表中数据发生改变前的状态
After：在表中数据已经发生改变后的状态

触发事件
触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）
Insert：插入操作
Update：更新操作
Delete：删除操作

注意： 6个
因此，一张表中最多的触发器只能有6个：before insert，before update，before delete，after insert，after update，after delete
*/
delimiter ##
create trigger after_insert_order_t after insert  on my_orders for each row
begin
    -- 更新商品库存
    update my_goods set inv = inv - 1 where id = 1;
end ##

-- 存储过程的简单示例
#需求：传入一个学生的学号，查询学生信息
DELIMITER $
CREATE PROCEDURE pro_findById(IN storeId INT) #in:输入参数
BEGIN
  SELECT * FROM store_details WHERE store_id = storeId;
END $

#调用
CALL pro_findById(1);

-- 使用循环向store_details表中插入n条数据
delimiter ##
    create procedure pro_addStore(in nums int)
begin
    declare i int DEFAULT 1;
    while i <= nums do
        insert into store_details values (i,'zs',i+100);
          SET i=i+1;
        end while;
end ##

call pro_addStore(100);

-- 1、	查看全部触发器
Show triggers;

# 2、	查看触发器的创建语句
# Show create trigger 触发器名字;

-- 向my_orders表中添加一条数据，观察my_goods表对应的数据是否发生变化
start transaction ;
-- 没有触发器，我们要完成上述需求，可能需要如下的动作
insert into my_orders values (1,1,1);
update my_goods set inv = inv - 1 where id = 1;
commit;