mysql常用函数总结

聚合函数

count

使用方式有4种

select count(*) from tb_student_grade;--统计总行数
select count(0) from tb_student_grade;--统计总行数,括号里面的0表示什么意思？
select count(id) from tb_student_grade;--统计列id不为null的总行数
select count(distinct id) from tb_student_grade;--统计id不为null并且去掉id重复的总行数

sum

select sum(id) from tb_student_grade;--任意行的id不能为非数字，可以为null,但是如果所有的值都为null，则会返回null

avg

select avg(id) from tb_student_grade;--计算之前会把null值排除在外,但是如果所有的值都为null，则会返回null

max

select max(id) from tb_student_grade;--会忽略null值，但是如果所有的值都为null，则会返回null

min

select min(id) from tb_student_grade;--会忽略null值，但是如果所有的值都为null，则会返回null

字符函数

大小写

select lower(name) from tb_student_grade;
select upper(name) from tb_student_grade;

数据内容合并

select concat(id,name,course) from tb_student_grade;--参数至少有一个

字符串截取

substr

SUBSTR(str,pos), SUBSTR(str,pos,len)

SUBSTR()是 的同义词 SUBSTRING()

select substr('HelloWorld',0,3) value from dual;--pos为0时返回空串
select substr('HelloWorld',0) value from dual;--pos为0时返回空串

select substr('HelloWorld',1,3) value from dual;--返回结果：Hel，截取从“H”开始3个字符
select substr('HelloWorld',1,100) value from dual;--返回结果：HelloWorld，100虽然超出预处理的字符串最长度，但不会影响返回结果，系统按预处理字符串最大数量返回。
select substr('Hello World',5,3) value from dual;--返回结果：o W (中间的空格也算一个字符串，结果是：o空格W)
select substr('HelloWorld',2) value from dual;--返回结果：elloWorld，截取从“e”开始之后所有字符

select substr('HelloWorld',-1,3) value from dual;--返回结果：d （从后面倒数第一位开始往后取1个字符，而不是3个。）
select substr('HelloWorld',-4,3) value from dual;--返回结果：orl （从后面倒数第四位开始往后取3个字符）
select substr('HelloWorld',-3) value from dual;--返回结果：rld，从倒数第三个“r”开始,截取所有字符

字符串长度计算

char_length按照字符计算

length按照字节计算

查找字符

select instr('helloworld', 'o') from dual;--instr从左向右找第一次出现的位置，从1开始
select instr('helloworld', 'z') from dual;--找不到返回0

去除空格

--基本用法，注意全角空格去不掉
SELECT trim('    aaa  bbb  ccc     ')  trim FROM dual;
SELECT ltrim('    aaa  bbb  ccc     ')  trim FROM dual;
SELECT rtrim('    aaa  bbb  ccc     ')  trim FROM dual;
--高级用法，去除特定字符 trim( leading | trailing | both string1 FROM string2) 注意string1只能是单个字符，如果没有设置string1参数，默认去除空字符串
SELECT trim(leading  '$'  from  '$aaa  bbb  ccc$')  FROM dual;
SELECT trim(trailing  '$'  from  '$aaa  bbb  ccc$')  FROM dual;
SELECT trim(both '$'  from  '$aaa  bbb  ccc$')  FROM dual; 
SELECT trim(both  from  '  aaa  bbb  ccc  ')  FROM dual;  

字符串替换

select replace('heLLo','LL','ll') from dual;
select replace('heLLo','LL') from dual;--未设置第三个参数时表示用空字符串替换

转换函数/日期时间处理

STR_TO_DATE(str,format)

这是DATE_FORMAT()函数的反 函数。它需要一个字符串str和一个格式字符串format。 如果格式字符串包含日期和时间部分，则STR_TO_DATE()返回一个 DATETIME值，如果字符串只包含日期或时间部分，则返回一个 DATE或 TIME值。如果从中提取的日期、时间或日期时间值不str 合法，则STR_TO_DATE() 返回NULL并产生警告。

DATE_FORMAT(date,format)

日期时间格式字符请参考文章日期时间总结_cc的博客-CSDN博客中mysql部分

DATE_ADD

    DATE_ADD() 函数向日期添加指定的时间间隔。

    语法：DATE_ADD(date,INTERVAL expr type)

    参数说明:

　　date:起始日期或者起始时间

　　expr:指定的是一个间隔值,在起始时间中增加或者减少,注意:expr是一个字符串.对于负值间隔,可以以"-"开头

　　unit:表示的是一个单位,比如,加上的是1天还是一个小时.

    例如：DATE_ADD('时间',INTERVAL 2 HOUR)   当前时间➕2小时

    可结合between  and使用

unix_timestamp

    语法：unix_timestamp('时间')函数将日期转化为时间戳
    例如：unix_timestamp('2019-08-15 17:46:33') - unix_timestamp( '2019-08-15 17:46:33' ) > 时间（秒）

timestampdiff

    timestampdiff函数日期或日期时间表达式之间的整数差。
    语法：TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值
    FRAC_SECOND。表示间隔是毫秒
    SECOND：秒
    MINUTE：分钟
    HOUR：小时
    DAY：天
    WEEK：星期
    MONTH：月
    QUARTER：季度
    YEAR：年

窗口函数

OVER() 函数是窗口函数的一部分，它允许在数据集的分区（partition）上进行计算，而不改变原始表的数据行顺序。窗口函数能够对查询结果集中的一系列行执行计算，这些行通常与当前行有某种逻辑关系，如按照某个字段排序后的相邻行。

OVER() 子句通常与聚合函数一起使用，例如 SUM(), AVG(), MAX(), MIN(), COUNT() 等，以及排名函数如 ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG() 等。

一个基本的 OVER() 结构通常如下：

Sql

SELECT 
    column1, 
    aggregate_function(column2) OVER (
        [PARTITION BY column3, ...]
        ORDER BY column4 [ASC|DESC], ...
    ) as calculated_column
FROM 
    table_name;

• PARTITION BY：将结果集分为多个分区，在每个分区内部独立进行计算。
• ORDER BY：定义了窗口内的行排序方式，窗口函数的计算基于这个排序结果。
• 聚合函数或排名函数在 OVER() 子句中指定如何在给定的分区和排序规则上进行计算。

例如，计算每个部门（department）内员工薪水的累计总和：

Sql

SELECT 
    employee_id,
    department_id,
    salary,
    SUM(salary) OVER (
        PARTITION BY department_id
        ORDER BY employee_id
    ) AS cumulative_salary
FROM 
    employees;

这样会为每个部门内的员工按ID顺序列出薪水，并显示每个员工到目前为止（包括当前行）其所在部门的薪水累计总和。