SQL基础

一、sql joins

详细讲解
https://blog.csdn.net/qyj123456789/article

二、基本语法

1、不去重： union all
2、保留2位小数：round(number,2)
3、分组  group by  排序 order by

三、count(*) ：统计所有的行数，包括为null的行

四、划分为20岁以下，20-24岁，25岁及以上三个年龄段

select device_id,gender,
    case
        when age>=25 then '25岁及以上'
        when age>=20 then '20-24岁'
        when age<20 then '20岁以下'
        else '其他'
        end as age_cut
from user_profile;

五、字符串截取

select substring_index(profile,",",-1) as gender,count(*) as number
from user_submit
group by gender;

六、

七、连接字符串

select  sell_date,count(distinct product) as num_sold,

//将字符串用逗号隔开
group_concat(distinct product order by product separator',') as products

from Activities
group by sell_date
order by sell_date;

八、字符串匹配

select user_id,name,mail
from Users 
WHERE mail REGEXP '^[a-zA-Z][a-zA-Z0-9_./-]*\\@leetcode\\.com$';