不要使用select *查询

1：系统的查询分析器会对*进行解析，然后去找到该表的结构，然后帮你将*号改写成表结构的字段（依次排列），这样就多了一步解析步骤，也加大了查询分析器的开销，从而影响性能
2、在实际应用开发中，我们可能不需要所有字段，只需要某个表的某些数据，那么你用*号查询，会增大磁盘 IO 开销
因为我们实际查询是将记录记录在磁盘上，过程中会用到IO,查询过多不需要的数据库会加载磁盘IO开销
3：有时候我们会将数据进行缓存，对于一些不重要的数据库或者用不到的数据也查询出来，将它缓存，会增大内存开销
4：查询过程中，从服务器端返回到业务端中间需要服务器网络宽带，那么加大服务器带宽消耗。
5：例如一些框架他返回数据是将列列对应，有时候你行列不对应会造成数据的不一致，例如resultMap配置
6：使用* 它必会回表，无论走不走索引，他都会回表，也就是说你使用了*  等于你放弃了进一步对sql的优化
例如：select * ,index1,index2 from biao where index1=1  使用了覆盖索引  如果使用了*  就没使用他了

mysql中的Select查询语句的执行过程

mysql -h 127.0.0.1 -p 3306 -u root -p 8438031100
第一次访问数据库时候，它首先会访问服务器的连接器，然后得到一个链接对象，下次访问这个服务器，就不会再次访问连接器了

他会访问分析器，
分析器：分析语句，访问用户有没有权限，查询语句有没有出错，查询的表存在不存在等 都没有问题 他就会生成解析树，然后再访问优化器
优化器：
它会优化查询过程，看你有没有索引，或写一下优化策略，或者查询语句有没有一样呀，因为系统自带了执行计划缓存，就是当你查询语句一模一样的时候，他直接跳过分析器，直接到优化器，直接从缓存计划中得到执行计划，然后再根据执行计划访问执行器 最终拿到数据

explain select * from biao  查询执行过程

ROW_NUMBER⽤法详解
语法格式：row_number() over(partition by 分组列 order by 排序列 desc)
分组列和排序列都可以多个字段组合
row_number() over()分组排序功能：
使⽤ row_number() over()函数时，over()⾥头的分组以及排序的执⾏晚于 where 、group by、 order by 的执⾏。
1. row_number() over()
使⽤时排序字段放在over(ORDER BY 字段)⾥⾯，查询记录按排序字段升成序号升序输出。
注意事项：⽤了over排序后,sql语句后⾯不⽤再写Order BY⼦句排序。如果再写Order by⼦句，记录会按后⾯的Order by⼦句排序输出，记录
排序不会按row_number产⽣的序号排序。
2. ROW_NUMBER() OVER PARTITION BY
使⽤ ROW_NUMBER() OVER(PARTITION BY 字段1 ORDER BY 字段2 Asc) 语句，按OVER⾥的字段1进⾏分组，然后按OVER⾥的order
BY 字段2 进⾏组内排序，每组都是从1开始。
这时可根据需要在后⾯写另⼀个order BY ⼦句，记录排序按这个⼦句排序输出，跟ROW_NUMBER⽅法产⽣的序号⽆关。

1、利用递归加入数据

INSERT INTO users
WITH RECURSIVE t(id,`name`,pwd,email,create_time) AS (
SELECT 2001,CAST(CONCAT('user',1) as char(50)),'e10adsffa2545145',CAST(CONCAT('user',1,'@qq.com') AS CHAR(50)),'2022-01-01 00:00:00'
UNION ALL 
SELECT id+1,CONCAT('user',CAST(id+1 as char)),CONCAT('password-',CAST(id+1 as char)) ,CONCAT('user',CAST(id+1 as char),'@qq.com'),create_time+INTERVAL MOD(id,2) MINUTE
FROM t WHERE id<3000
)
select * from t

地铁换乘线路查询

SELECT station_name,next_station,1,CAST(CONCAT(line_name,station_name,"-->",line_name,next_station) as char(10000)) path 
FROM `bj_subway`
where station_name='王府井'

WITH recursive transfer(stationn_name,next_station,stops,path) as(
SELECT station_name,next_station,1,CAST(CONCAT(line_name,station_name,"-->",line_name,next_station) as char(10000)) path 
FROM `bj_subway`
where station_name='王府井'
UNION ALL
SELECT t.stationn_name,bs.next_station,stops+1,CONCAT(t.path,"-->",bs.line_name,bs.next_station)
FROM transfer t
JOIN
bj_subway bs
ON(t.next_station=bs.station_name and (INSTR(t.path,bs.next_station)=0))
)
select * from transfer where next_station='东单'

2、根据扫码知道这个离开某个地区的开始时间和最后时间

with temp as(select uid,area ,scan_time ,//简历虚标
row_number() over(partition by uid order by scan_time) num1,
row_number() over(partition by uid,area order by scan_time) num2,
row_number() over(partition by uid order by scan_time) -  row_number() over(partition by uid,area order by scan_time) diff
from erweima)

select uid, area,min(scan_time) start_time ,max(scan_time) end_time
from temp
group by uid,area, diff

3、联系N天登陆

#from里面都是相同的表数据  查询结果结构一样，然后在三张表里面找出需要的联系N天登录数据

SELECT DISTINCT ap1.`user`,ap1.`name`,ap1.data1,ap2.data2,ap3.data3
from(
SELECT DISTINCT user,name,date(createdate) data1
FROM anyi_purchase #TIMESTAMP是时间戳  将后面的时间转换成时间戳比对
where createdate  BETWEEN TIMESTAMP '2022-01-01 01:27:21' AND '2022-06-22 01:27:21')  ap1 #一张表
JOIN
(
SELECT DISTINCT user,name,date(createdate) data2
FROM anyi_purchase #TIMESTAMP是时间戳  将后面的时间转换成时间戳比对
where createdate  BETWEEN TIMESTAMP '2022-01-01 01:27:21' AND '2022-06-22 01:27:21')  ap2
ON( ap1.`user`=ap2.`user` and DATEDIFF(ap2.data2,ap1.data1)=1) #DATEDIFF 大的减去小的
JOIN
(
SELECT DISTINCT user,name,date(createdate) data3
FROM anyi_purchase #TIMESTAMP是时间戳  将后面的时间转换成时间戳比对
where createdate  BETWEEN TIMESTAMP '2022-01-01 01:27:21' AND '2022-06-22 01:27:21')  ap3
ON( ap3.`user`=ap2.`user` and DATEDIFF(ap3.data3,ap2.data2)=1)

with t1 as(select   distinct  user, date(createdate) datetime 
from anyi_purchase
where createdate between  timestamp '2022-01-01 12:42:41' and timestamp '2022-05-31 12:42:41'
order by user, datetime),
t2 as(select datetime,user ,
date_sub(datetime,interval row_number() over(partition by user order by datetime) day) datasub -- 这个datetime减去多少天5天
from t1)

select user,count(datasub),min(datetime),max(datetime) from t2
group by user ,datasub
having  count(datasub) >=1  //连续登录大于等于1天的

lag(param1, param2, param3)取前N行

• 取前N行
• param1：表中列名
• param2：前N行
• param3：超出行数时默认设置值, 没设置就是null
  

-- lag：取向上偏移量  例如 a= 1，2，3，4  ,lag(a,2),当处于4的时候 lag向上偏移量2位  就是2的值   
-- lead 取向下偏移量
with t1 as(
select distinct user,date(createdate) datetime
from bishejiexi.anyi_purchase 
order by user ,datetime),
t2 as(
select distinct user,datetime,
lag (datetime,2) over(partition by user order by datetime) lagtime,
datediff(datetime,lag (datetime,2) over(partition by user order by datetime) ) diff 
from t1)
select user,datetime endtime,
date_sub(lagtime,interval -1 day) zjtime,
lagtime,diff
from t2
having lagtime is not  null  and diff=2

字符串的拆分和合并

# 用法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )
# 合并
select user,group_concat( distinct name 
order by createdate ,id separator "；") 
from anyi_purchase ap 
group by user

# 递归
WITH recursive 表名 AS ( 
    初始语句（非递归部分） 
    UNION ALL 
    递归部分语句
)
[ SELECT| INSERT | UPDATE | DELETE]

# 字符串的拆分
with recursive mm(ukey,uvalue,info,str) as(
select ukey,uvalue ,
substr(info,1,instr(info,"，")-1 ),
substr(concat(info,"，") ,instr(info,"，")+1 )
from anyi_strconfig
where instr(info,"，")>0
union all
select ukey,uvalue ,
substr(str,1,instr(str,"，")-1 ),
substr(str,instr(str,"，")+1 )
from mm
where instr(str,"，")>0
)
select ukey,uvalue,info from mm;

/*
infoo:递归要要拆分的字符串的字段名
info：初始化最初要拆分的字符串的字段名

*/

with recursive mm(ukey,uvalue,infoo,info) as(
SELECT 
ukey,uvalue,
SUBSTR(anyi_strconfig.info,instr(anyi_strconfig.info,"，")+1) ,
SUBSTR(anyi_strconfig.info,1,instr(anyi_strconfig.info,"，")-1)
FROM `anyi_strconfig`
union all
select ukey,uvalue,
SUBSTR(infoo,1,instr(infoo,"，")-1),
SUBSTR(infoo,instr(infoo,"，")+1)
FROM mm  # 因为递归体要循环递归，那么from后面必须是递归的临时表名
where instr(infoo,"，")>0

)
select ukey,uvalue,info from mm;