MYSQL substring

MYSQL substring_index

1.substring_index( 参数1,参数2 ,参数3 )

2.group by 也可以用我们起的别名来划分，以及起别名可以不用as

SELECT IF(profile LIKE '%female','female','male') gender,COUNT(*) number
FROM user_submit
GROUP BY gender;

3.切割、截取、删除、替换

select 
-- 替换法 replace(string, '被替换部分','替换后的结果')
-- device_id, replace(blog_url,'http:/url/','') as user_name

-- 截取法 substr(string, start_point, length*可选参数*)
-- device_id, substr(blog_url,11,length(blog_url)-10) as user_nam

-- 删除法 trim('被删除字段' from 列名)
-- device_id, trim('http:/url/' from blog_url) as user_name

-- 字段切割法 substring_index(string, '切割标志', 位置数（负号：从后面开始）)
device_id, substring_index(blog_url,'/',-1) as user_name

from user_submit;

4.这边join 相当join了一张新表，太帅了，还可以这样

select
    a.device_id,
    a.university,
    a.gpa
from
    user_profile a
    right join 
    # 相当自己又创建了一张表，太帅了
    (
        select
            university,
            min(gpa) as gpa
        from
            user_profile
        group by
            university
    ) as b on a.university = b.university
    and a.gpa = b.gpa
order by
    a.university

5.concat拼接，upper 变大写