MySQL函数

函数

一.聚合函数

（1）概述

group_concat()，该函数用于实现行的合并，group——concat()函数首先根据 group by 指定的列进行分组，并且用分隔符分割，将同一个分组中的值连接起来，返回一个字符串结果。

（2）格式

说明：

• 使用 distinct 可以排除重复值；
• 如果需要对结果中的值进行排序，可以使用 order by 子句；
• separator 是一个字符串值，默认为逗号。

二.数学函数

代码实现：

-- 取绝对值
select abs(-10); -- 10
select abs(10); -- 10

-- 向上取整
select ceil(1.1); -- 2
select ceil(1.0); -- 1

-- 向下取整
select floor(1.1); -- 1
select floor(1.9); -- 1

-- 取列表最大值
select greatest(1,2,3); -- 3

-- 取列表最小值
select least(1,2,3); -- 1

-- 取模
select mod(5,2); -- 1

-- 取x的y次方
select power(2,3); -- 8

-- 取随机数
select rand(); -- 0-1的随机数
select floor(rand()*100);

-- 取小数的四舍五入
select round(3.3415);
select round(3.3415,3); -- 保留3位小数

-- 将小数直接截取到指定位数
select truncate(3.1415,3); -- 3.141

三.字符串函数

-- 1.获取字符串字符个数
select char_length('hello'); -- 5
select char_length('你好吗'); -- 3

-- length取长度，返回的单位是字节
select length('hello'); -- 5
select length('你好吗'); -- 9

-- 2.将多个字符串进行合并
select concat('hello','world'); -- helloworld

-- 使用分隔符将多个字符串合并
select concat_ws(';','hello','World','MySQL');

-- 3.返回第一个字符串在字符串列表中的位置
select field('aaa','aaa','bbb','ccc'); -- 1
select field('bbb','aaa','bbb','ccc'); -- 2

-- 4.去除字符串空格
select ltrim('     aaaaa'); -- 去除左边空格
select rtrim('   aaa   '); -- 去除右边空格
select trim('  aaa    '); -- 去除两边空格

-- 5.字符串截取
select mid('helloworld',2,3); -- 从第二个字符开始截取，截取长度为3

-- 6.获取字符串A在字符串中出现的位置
select position('abc' in 'habcelloabcworld');

-- 7字符串替换
select replace('aaahelloworldaaa','aaa','bbb');

-- 8.字符串反转
select reverse('hello');

-- 9。返回字符串s的后n个字符
select right('hello',3);

-- 10.比较字符串s1和s1，
-- 如果s1与s2相等返回0，如果s1>s2返回1，如果s1<s2返回-1
select strcmp('hello','world');

-- 11.字符串截取
select substr('hello',2,3); -- 从第二个字符开始截取，截取3个字符
select substring('hello',2,3); -- 从第二个字符开始截取，截取3个字符

-- 12.将大写转小写
select ucase('helloWorld');
select upper('helloWorld');

-- 13.将大写转为小写
select lcase('HELLOWORLD');
select lower('APP');

四.日期函数

-- 1.返回从1970-01-01 00：00：00到当前毫秒值
select unix_timestamp();

-- 2.将一个日期字符串转为毫秒值
select unix_timestamp('2021-12-21 08:08:08');

-- 3.把时间的毫秒值转为指定格式的日期
select from_unixtime(1640045288);
select from_unixtime(1640045288,'%Y-%m-%d %H:%i:%s');

-- 4获取当前的年月日
select curdate();
select current_date();

-- 5.获取当前的时分秒
select current_time();
select curtime();

-- 6.获取年月日 和 时分秒
select current_timestamp();

-- 7.从日期字符串中获取年月日
select date('2023-12-18 16:31:09');

-- 8获取日期之间的差值
select datediff('2021-12-23','2008-09-09');

-- 9.获取时间的差值（秒级
select timediff('12:12:34','10:18:56'); -- 01:53:38

-- 10日期格式化
select date_format('2021-1-1 1:1:1','%Y-%m-%d %H-%i-%s');

-- 11将字符串转为日期
select str_to_date('2021-12-13 11:11:11','%Y-%m-%d %H-%i-%s');

-- 12.将日期进行减法 -- 日期向前跳转
select date_sub('2021-10-01',interval 2 day );
select date_sub('2021-10-01',interval 2 month );

-- 14.从日期中获取小时
select extract(hour from '2021-12-13 11:12:13');
select extract(year from '2021-12-13 11:12:13');
select extract(month from '2021-12-13 11:12:13');

-- 15获取给定日期所在月的最后一天
select last_day('2021-08-13');

-- 16获取指定年份和天数的日期
select makedate('2021',53);

-- 17.根据日期获取年月日，时分秒
select year( '2021-12-13 11:12:13');
select month( '2021-12-13 11:12:13');
select day( '2021-12-13 11:12:13');
select quarter( '2021-12-13 11:12:13'); -- 获取季度


-- 18.根据日期获取信息
select monthname('2021-12-13 11:12:13'); -- 获取月份的英文
select dayname('2021-12-13 11:12:13'); -- 获取周几：Monday
select dayofmonth('2021-12-13 11:12:13'); -- 当月的第几天
select dayofweek('2021-12-13 11:12:13'); -- 1：周日 2：周一
select dayofyear('2021-12-13 11:12:13'); -- 获取一年的第几天

五.控制流函数

if 逻辑判断语句

-- if
select if(1>0,'true','false');

-- ifnull
select ifnull(5,0);
select ifnull(null,0);

-- isnull
select isnull(5);
select isnull(null);

-- nullif
select nullif(12,12); -- null
select nullif(12,13); -- 12

case-when 语句

代码应用实现

create table orders(
    oid int primary key , -- 订单id
    price double, -- 订单价格
    payType int -- 支付方式（1：微信支付 2：支付宝支付 3：银行卡支付 4：其他）
);

insert into orders values (1,1200,1);
insert into orders values (2,1000,2);
insert into orders values (3,200,3);
insert into orders values (4,3000,3);
insert into orders values (5,1500,1);

select
    *,
    case payType
        when 1 then '微信支付'
        when 2 then '支付宝支付'
        when 3 then '银行支付'
        when 4 then '其他'
    else 'sorry'
end as payTypeStr
from orders;

执行结果：

六.窗口函数

（1）介绍

• MySQl8.0 新增窗口函数，窗口函数又被称为开窗函数，与 Oracle 窗口函数类似，属于 MySQl 的一大特点
• 非聚合窗口函数是相对于聚函数来说的。聚合函数是对一组数据计算后返回单个值（即分组），非聚合函数一次只会处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

（2）序号函数

partition by 表示分组，order by 表示按照什么方式排序
例子：对每个部门的员工按照薪资排序，并输出排名

select
    dname,
    ename,
    eid,
    salary,
    row_number() over (partition by dname order by salary desc) as rn1,
    rank() over (partition by dname order by salary desc) as rn2,
    dense_rank() over (partition by dname order by salary desc) as rn3
from employee;

执行结果：

（3）开窗聚合函数

此以 sum 为例，

1).从开头加到当前行

两种方法：

select
    dname,
    ename,
    salary,
    sum(salary) over(partition by dname order by hiredate) as pv1
from employee;  

select
    dname,
    ename,
    hiredate,
    salary,
    sum(salary) over(partition by dname order by hiredate rows between unbounded preceding and current row ) as pv1
from employee;

执行结果：

2).没有 order by，默认把分组后的所有相加

select
    dname,
    ename,
    hiredate,
    salary,
    sum(salary) over(partition by dname ) as pv2
from employee;

执行结果：

3).从前三行加到当前行

select
    dname,
    ename,
    hiredate,
    salary,
    sum(salary) over(partition by dname order by hiredate rows between 3 preceding and current row ) as pv1
from employee;

执行结果：

4）从当前行加到最后一行

select
    dname,
    ename,
    hiredate,
    salary,
    sum(salary) over(partition by dname order by hiredate rows between current row  and unbounded following) as pv1
from employee;

(4).分布函数——cume_dist 和 percent_rank

cume_dist

• 用途：分组内小于，等于当前 rank 值的行数/分组内总行数
• 应用场景：查询小于等于当前薪资的比例

select
    dname,
    ename,
    salary,
    cume_dist() over (order by salary) as c1,
    cume_dist() over (partition by dname order by salary) as c2
from employee;

执行结果：

解释：（rn1 = c1,rn2 = c2)

percent_rank

（不常用）

• 用途：每行按照公式(rank - 1) / (rows - 1)进行计算。其中，rank 为 rank（）函数产生的序号，rows 为当前窗口的记录总行数

（5）.前后函数 lag 和 lead

• 用途：返回位于当前行的前 n 行(lag(expr,n))或后 n 行(lead(expr,n)的 expr 的值。
• 应用场景：查询前 1 名同学 的成绩和当前同学成绩从差值

select
    dname,
    ename,
    salary,
    hiredate,
    lag(hiredate,1,'2020-11-01') over (partition by dname order by hiredate) as c1,
    lead(hiredate,2) over (partition by dname order by hiredate) as c2
from employee;

执行结果：

c1：表示的是前一个的时间，如果是第一个，则是自身所填的默认值
c2：表示的是后两个的时间，没有写默认值，所有在没有时，为 null

（6）头尾函数 first_value 和 last_value

• 用途：返回第一个（first_value（expr））或最后一个（last_value（expr）)expr 的值
• 应用场景：截止到当前，按照日期排序查询第 1 个入职和最后 1 个入职员工的薪资

select
    dname,
    ename,
    salary,
    hiredate,
    first_value(salary) over (partition by dname order by hiredate) as first,
    last_value(salary) over (partition by dname order by hiredate) as last
from employee;

执行结果：

（7）其他函数 nth_value,ntile

nth_value

• 用途：返回窗口中第 n 个 expr 的值，expr 可以是表达式，也可以是列名
• 应用场景：截止到当前薪资，显示每个员工的薪资中排名第二或第三的薪资

执行结果：

ntile

• 用途：将分区中的有序数据分为 n 个等级，记录等级数
• 应用场景：将每个部门员工按照入职日期分成 3 组