select 
	s.name,
s.class_code,
s.grade_code,
t.name as head_teacher_name
from students s
join teachers t on s.class_code = t.head_teacher
order by 
	s.student_id

select length(singer_name),count(1)
from singer_info
group by
	length(singer_name)

select singer_name ,char_length(singer_name)
from singer_info

select upper(singer_name)
from singer_info

select dt,tmp_h,tmp_l,con as wnd from weather_rcd_china
where con like '%雪%' and lower(city) = 'beijing'

select dt,tmp_h,tmp_l,con as wnd from weather_rcd_china
where con like '%雪%'

select dt,tmp_h,tmp_l,con,wnd from weather_rcd_china
where con like '%雪%'

select left(name,1)as surname,count(1) as cnt
from students
group by
	surname
order by cnt desc
limit 5;

select 
	city,
sum(if(con like '%多云%',1,0)) as cloudy_days,
concat(cast(sum(if(con like '%多云%',1,0))/count(1)*100 as decimal(4,2)),'%')as '占比'
from weather_rcd_china
where year(dt) = 2021
group by
	city
order by cloudy_days desc

select 
	city,
sum(if(con like '%多云%',1,0)) as cloudy_days,
concat(cast(sum(if(con like '%多云%',1,0))/count(1) as decimal(4,2)),'%')as '占比'
from weather_rcd_china
where year(dt) = 2021
group by
	city
order by cloudy_days

select 
	city,
sum(if(con like '%多云%',1,0)) as cloudy_days,
concat(cast(sum(if(con like '%多云%',1,0))/count(1) as decimal(4,2)),'%')as '占比'
from weather_rcd_china
where year(dt) = 2021
group by
	city

select * 
from hand_permutations
where left(card1,1) = left(card2,1)

select * 
from hand_permutations
where (card1 like 'A%' or card2 like 'A%')and
	right(card1,1) = right(card2,1)

select * 
from hand_permutations 
where card1 rlike '(K|A)' and card2 rlike '(K|A)'

select 
	case
when mch_nm like '%按摩保健休闲%' then '按摩保健休闲'
whenlower(mch_nm) rlike '.*(按摩|保健|休闲|养生|spa|会所).*' then '按摩、保健、休闲、养生、SPA、会所'
end as reg_rules,
count(distinct mch_nm)
from cmb_usr_trx_rcd
where
lower(mch_nm) rlike '.*(按摩|保健|休闲|养生|spa|会所).*' or 
mch_nm like '%按摩保健休闲%'
group by
	reg_rules

select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select count(*)
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲%'

select
	count(distinct if(mch_nm like '%按摩保健休闲%',mch_nm,null)) as mch_cnt
from cmb_usr_trx_rcd

select user_id,date_format(start_time,'%W')as day_of_week ,count(1)
from listen_rcd
group by user_id ,day_of_week
order by user_id,count(1) desc

select * from cmb_usr_trx_rcd
where
	usr_id = '5201314520' and
	(date(trx_time) between '2024-06-08' and '2024-06-10' or date(trx_time) between '2024-09-15' and '2024-09-17')
order by trx_time