mysql

分组+聚合函数【统计指标】=》 1.分组 lb,<20,30,30> junsong,<18> lb02,<20> 2.聚合函数【统计指标】 avg lb , 80/3 junsong ,18/1

1.分组语法 聚合函数： 多行数据按照一定规则 聚合为 一行 理论上说： 聚合后的行数 <= 聚合前的行数

1.聚合函数 sum avg max min count 
​
1.作用整张表 【不使用group by 】  
select 
sum(age) as age_sum
,avg(age) as age_avg
,max(age) as  age_max
,min(age) as age_min
,count(age) as cnt
from student_info;

统计表中有多个名字重名

select 
name,
count(name) as cnt 
from student_info
group by 
name 
having cnt >1;

对数据进行去重？

app =》 抖音
1.pv    一个人 多次    cnt sum   每个用户登录的次数
2.uv    一个人 多次   1个人      今天 用户在线人数【登录过抖音的人数】
​
1.统计 pv uv 
​
维度：--
指标： pv uv
要什么给什么 
​
select 
count(name) as pv,
count(distinct name) as uv
from student_info;
​
1.pv 
select 
name ,
count(name) as pv 
from student_info
group by 
name 
​
2.uv  去重 
    1.distinct  性能低 
select 
count(distinct name) as cnt 
from student_info; 
​
    2. group by  
select 
count(*) as uv 
from 
(
    select 
    name 
    from student_info
    group by 
    name 
) a;
​
select 
count(1) as uv 
from 
(
    select 
    name 
    from student_info
    group by 
    name 
) a  

count(name) 1. count() => column ： * ，name 2. count(任意值 ) 任意值、第一个字段 1 2

select 
name ,
1 
from student_info
group by 
name
​
select 
name 
from student_info
group by 
name 

原始数据： 俊松 junsong xinyu js java lb lb01 lb02 cmx lb lb lb

group by : lb , cmx, java,

2.按照name进行分组 统计
select 
name,
sum(age) as age_sum,
avg(age) as age_avg,
max(age) as  age_max,
min(age) as age_min,
count(age) as cnt
from student_info 
group by name ;

3.按照name进行分组 结果数据 进一步统计 
    1.having =》 where 【只能在group by 之后使用】  where 是作用到 from 之后的
​
select 
name,
sum(age) as age_sum,
avg(age) as age_avg,
max(age) as  age_max,
min(age) as age_min,
count(age) as cnt
from student_info 
group by name 
having 
cnt >2;
​
    2.子查询 [sql 套sql 套娃]
​
select 
name,
age_sum,
age_avg,
age_max,
age_min,
cnt
from 
(
    select 
        name,
        sum(age) as age_sum,
        avg(age) as age_avg,
        max(age) as  age_max,
        min(age) as age_min,
        count(age) as cnt
    from student_info 
        group by name
) a 
where 
cnt > 2;

2.join （多表联查） 1.广义上 ： inner 、left、right、full 、 CROSS（笛卡尔积）【了解】 2.狭义上 7种

主表 从表 事实表 维度表

create table a1(id int ,name varchar(50),address varchar(20));
create table b1(id int ,name varchar(50),age int(3));

insert into a1 values(1,'js','辽阳');
insert into a1 values(2,'xy','朝阳');
insert into a1 values(4,'hy','辽阳');

insert into b1 values(1,'js',12);
insert into b1 values(2,'xy',18);
insert into b1 values(3,'sxwang',20);

1.内连接 inner inner join 结果数据： 取出两表 共有的数据

select 
a1.*,
b1.*
from a1  inner join b1 
on a1.id = b1.id ;
​
select 
a1.*,
b1.*
from a1   join b1 
on a1.id = b1.id ;
​
select 
a1.*,
age
from a1   join b1 
on a1.id = b1.id ;

2.左连接 (left join ) 以左表为主 数据是全的 右表来匹配 匹配不上就是 null

select 
a1.*,
b1.*
from a1  left join b1 
on a1.id = b1.id ;

3.右连接 (right join ) 以右表为主 数据是全的 左表来匹配 匹配不上就是 null

select 
a1.*,
b1.*
from a1  right join b1 
on a1.id = b1.id and a1.name=b1.name ;

4.full outer join 全连接 mysql 不支持 full join 以左表为主 数据是全的 右表来匹配 匹配不上就是 null 以右表为主 数据是全的 左表来匹配 匹配不上就是 null

select 
a1.*,
b1.*
from a1  full outer join b1 
on a1.id = b1.id and a1.name=b1.name ;
​
select 
a1.*,
b1.*
from a1  left join b1 
on a1.id = b1.id 
​
union
​
select 
a1.*,
b1.*
from a1  right join b1 
on a1.id = b1.id and a1.name=b1.name ;

5.case when 语法结构

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END