MySQL学习（函数整理）

约 1953 字大约 7 分钟

2025-02-26

包括一些基本的如：count，sum，min，max，avg等
group_concat() 函数: 实现行的合并，首先根据group by指定的列进行分组，并且用分隔符进行分隔，然后对每一组中的值进行连接，返回一个字符串结果。

    -- 将所有员工名字合并为一行
    select group_concat(ename) from emp; -- 分隔符默认为逗号
    -- 将所有员工名字合并为一行，并指定分隔符
    select group_concat(ename separator ';') from emp; -- 指定分隔符为分号
    -- 将每个部门的员工名字合并为一行
    select a.deptno, group_concat(ename separator ';') from emp a group by a.deptno;
    -- 将每个部门的员工名字合并为一行，并按照工资排序
    select a.deptno, group_concat(ename order by a.sal separator ';') from emp a group by a.deptno;

数学函数

常见的有：abs：绝对值，mod：取余数，ceil：向上取整. floor：向下取整，round：四舍五入. greatest：返回列表最大值，least：返回列表最小值. max：字段最大值，min：字段最小值. pi：圆周率，rand()：0到1随机数. power：幂运算，sqrt：开平方. truncate：截断

字符串函数

获取字符串字符个数select char_length('hello world'); length() 函数: 获取字符串字节长度select length('你好');
字符串拼接select concat(ename, job) from emp; 指定分隔符合并字符串

	select concat(ename, ':', job) from emp;
    select concat_ws(',', ename, job) from emp;

返回字符串(如aaa)在列表中第一次出现的位置select field('aaa', 'aaa', 'bbb', 'ccc');
去除字符串的空格（左：ltrim，右：rtrim，两端空格：trim）

	select ltrim('  aaa');
    select rtrim('aaa  ');

字符串截取select mid('hello world', 2, 5); -- 截取从2开始，长度为5的字符串
获取字符串在字符中出现的位置select position('lo' in 'hello world');
替换字符串select replace('hello world', 'l', '*');
字符串反转select reverse('hello world');
返回字符串后几个字符select right('hello world', 5);
字符串比较select strcmp('hello', 'world');
字符串截取select substr('hello world', 2, 5); -- 截取从2开始，长度为5的字符串
大小写转换

    select lower('HELLO');
    select upper('hello');

日期函数

获取时间戳（毫秒值）：select unix_timestamp();
将日期字符串转换为毫秒值：select unix_timestamp('2024-9-11 20:39:27');
将毫秒值转换为日期字符串：select from_unixtime(1536704982, '%Y-%m-%d %H:%i:%s');
获取当前日期：select curdate(); -- 或select current_date();
获取当前时间：获取当前时间
获取当前日期时间：select current_timestamp();
从日期字符串中获取年月日：select date('2018-9-11 20:39:27');
获取日期之间的天数差值：select datediff(current_date(), '2023-2-18');
获取日期之间的秒数差值：select timediff(current_time(), '12:30:59');
日期格式化：select date_format('2018-9-1 2:39:7', '%Y-%m-%d %H:%i:%s');
日期减法：select date_sub(current_date(), interval 10 day); -- (second, minute, hour, day, week, month, quarter, year)
日期加法：select date_add(current_date(), interval 10 day);
从日期中获取指定部分：

    select extract(year from current_timestamp()); -- (second, minute, hour, day, week, month, quarter, year)
    select month(current_timestamp());

获取给定日期的月的最后一天：select last_day('2018-9-1');
获取指定年份过了一定天数的日期：select makedate(2024, 65);
根据日期获取信息：

    select monthname(current_timestamp()); -- 获取月份
    select dayname(current_timestamp()); -- 获取星期几
    select dayofmonth(current_timestamp()); -- 获取月份中的第几天
    select dayofweek(current_timestamp()); -- 获取星期几，1为周日
    select dayofyear(current_timestamp()); -- 获取一年中的第几天

    select week(current_timestamp()); -- 获取一年中的第几周

控制流函数

if逻辑判断语句

        -- 1:if函数: 判断表达式是否为真，如果为真则返回第二个参数，否则返回第三个参数
        select if(10 > 5, 'true', 'false'); -- true
        use test1;
        select *, if(a.sal > 2500, '高薪', '低薪') from emp a;
        -- 2:ifnull函数: 判断表达式是否为null，如果为null则返回第二个参数，否则返回第一个参数
        select ifnull(null, 'default'); -- default
        select *, ifnull(a.comm, 0) from emp a; -- 显示奖金，如果为null则显示0
        -- 3:nullif函数: 判断两个表达式是否相等，如果相等则返回null，否则返回第一个参数
        select nullif(10, 5); -- 10
        select nullif(10, 10); -- null

case when语句（类似于c语言的switch case）

        select a.ename, case
            when a.sal > 3000 then '高薪'
            when a.sal > 1500 and a.sal <= 3000 then '中薪'
            else '低薪'
        end as 工资等级
        from emp a;

窗口函数

分区：将数据分为不同的组，每一组分别执行函数。排序：根据指定的列进行排序。

序号函数：row_number()、rank()、dense_rank() row_number()：序号不重复 rank()：序号重复不连续 dense_rank()：序号重复连续

按照部门分组，按照工资倒序排序

        select
            a.deptno,
            a.ename,
            a.sal,
            row_number() over(partition by a.deptno order by a.sal desc) as 工资排名1,
            rank() over(partition by a.deptno order by a.sal desc) as 工资排名2,
            dense_rank() over(partition by a.deptno order by a.sal desc) as 工资排名3
        from emp a;

获取每个部门的工资排名前2的员工（子查询）

        select * from
        (select
            a.deptno,
            a.ename,
            a.sal,
            dense_rank() over(partition by a.deptno order by a.sal desc) as rn
        from emp a) as b
        where b.rn <= 2;

对所有员工进行全局排序（不分组）

        select
            a.deptno,
            a.ename,
            a.sal,
            dense_rank() over(order by a.sal desc) as 工资排名1
        from emp a;

开窗聚合函数如下选择：sum()、avg()、max()、min()、count() n preceding: 往前n行. current row: 当前行. unbounded following: 最后一行. n following: 往后n行. 以sum函数为例：

        select
            a.deptno,
            a.ename,
            a.sal,
            sum(a.sal) over(partition by a.deptno order by a.hiredate) as c1, -- 获取每个部门工资的总和(默认从每个分组的第一行加到当前行)
            sum(a.sal) over(partition by a.deptno order by a.hiredate rows between 3 preceding and 1 following) as c2, -- 从当前行的前3行到当前行的后1行累加
            sum(a.sal) over(partition by a.deptno order by a.hiredate rows between current row and unbounded following) as c3 -- 从当前行到最后一行累加
        from emp a;

分布函数 percent_rank()：(rank - 1) / (总行数 - 1) cume_dist()：获取每个部门小于等于该字段的人的比例

        select
            a.deptno,
            a.ename,
            a.sal,
            rank() over(partition by a.deptno order by a.sal desc ) as c1, -- 获取每个部门工资的排名
            percent_rank() over(partition by a.deptno order by a.sal) as c2, -- (rank - 1) / (总行数 - 1)
            cume_dist() over(partition by a.deptno order by a.sal) as c3 -- 获取每个部门小于等于该工资的人的比例
        from emp a;

前后函数 LAG(col, n) 往前第n行数据 LEAD(col, n) 往后第n行数据. 应用场景：查询前一名同学的成绩和当前同学的成绩的差值

        select
            a.deptno,
            a.ename,
            a.sal,
            a.hiredate,
            lag(a.hiredate, 1, '2000-01-01') over(partition by a.deptno order by a.hiredate) as time1, -- 获取每个部门前1名同学的入职时间,默认为2001-01-01
            lead(a.hiredate, 1) over(partition by a.deptno order by a.hiredate) as time2 -- 获取每个部门后1名同学的
        from emp a;

头尾函数 FIRST_VALUE(col) 获取分组内排序后第一行数据的值 LAST_VALUE() 获取分组内排序后最后一行的数据

        select
            a.deptno,
            a.ename,
            a.sal,
            a.hiredate,
            first_value(a.sal) over (partition by a.deptno order by a.hiredate) as first, -- 截止到当前，获取每个部门按照入职日期排序的工资的第一名
            last_value(a.sal) over (partition by a.deptno order by a.hiredate) as last -- 截止到当前，获取每个部门按照入职日期排序的工资的最后一名
        from emp a;

其他函数 NTH_VALUE(col, n) 获取分组内排序后第n行的数据 NTILE(n) 将分组内数据按顺序平分成n组

        select
            a.deptno,
            a.ename,
            a.sal,
            a.hiredate,
            nth_value(a.sal, 2) over (partition by a.deptno order by a.hiredate) as nth, -- 截止到当前，获取每个部门按照入职日期排序的第二个员工的工资
            ntile(3) over (partition by a.deptno order by a.sal) as nt -- 截止到当前，将每个部门的工资按照升序分成3组
        from emp a;

取出每一个部门的第一组员工(子查询)

        select * from
        (select
            a.deptno,
            a.ename,
            a.sal,
            a.hiredate,
            ntile(3) over (partition by a.deptno order by a.sal) as nt
        from emp a) as b
        where b.nt = 1;