MYSQL函数使用实例分析及应用场景说明

MYSQL函数

1:聚合函数

聚合函数主要由:count,sum,min,max,avg,group_count()

重点说一下group_count()函数,首先根据group by指定的列进行分组,并且用分隔符分隔,将同一个分组中的值连接起来,返回一个字符串结果. 

格式 : group_count([distinct]字段名 [order by 排序字段 asc/desc] [separator '分隔符'])

说明:

      1:使用distinct可以排除重复值.
      2:如果需要对结果值进行排序,可以使用order by 子句.
      3:separator是一个字符串值,默认为逗号.

2:数学函数

1:ABS(x) 返回x的绝对值
2:CEIL(x)返回大于等于x的最小整数(向上取整)
3:FLOOR(x) 返回小于等于x的最大整数(向下取整)
4:GREATEST(expr1,expr2...)返回列表中的最大值
5:LEAST(expr1,expr2.....)返回列表的最小值
6:MAX(x) 返回字段x的最大值
7:MIN(x) 返回字段x的最小值
8:MOD(x,y) 返回x除以y以后的余数
9:PI()返回圆周率(3.141593)
10:POW(x,y) 返回x的y次方
11:RAND() 返回0到1的随机数
12:ROUND(x) 返回离x最近的整数(遵循四舍五入)
13:ROUND(x,y) 返回指定位数的小数(遵循四舍五入)
14:TRUNCATE(x,y) 返回数值x保留到小数点后y位的值,(与ROUND最大的去区别是不会进行四舍五入)

2:字符串函数

1:char_length(s) 返回字符串s的字符数
2:character_length 返回字符串s的字符数
3:concat(s1,s2,s3) 字符串s1,s2等多个字符串合并为一个字符串
4:concat_ws(x,s1,s2..) 同concat(s1,s2,s3)函数,但是每个字符串之间要加上x,x可以是分隔符
5:field(s,s1,s2) 返回第一个字符串s在字符串列表(s1,s2..)中的位置
6:length() 返回的是字节数 mysql中utf-8的编码 一个中文汉字是三个字节
7:ltrim(s) 去掉字符串s开始处的空格 去除左边的空格  rtrim()去除右边的空格 trim()去除两边的空格
8:mid(s,n,len) 从字符串s的n位置截取长度为len的子字符串同substring(s,n,len)
9:position(s1,in,s) 从字符串s中获取s1的开始位置
10:replcae(s,s1,s2) 将字符串s2替代字符串s中的字符串s1
11:reverse(s) 将字符串s的顺序反过来
12:right(s,n)返回字符串s的后n个字符(从右取n个字符)
13:strcmp(s1,s2) 比较字符串s1和s2,如果s1与s2相等返回0,如果s1>s2返回1 如果s1小于s2返回-1
14:substr(s,start,length) 从字符串s的start位置截取长度为length的子字符串
15:ucase(s) upper(s)将字符串转换为大写
16:lcase(s) lower(s) 将字符串转换为小写

3:日期函数

1:unix_timestamp() 返回1970-01-01 00:00:00 到当前毫秒值
2:unix_timestamp(date_string) 将指定日期转换为毫秒值时间戳
3:from_unixtime(bigint unixtime,string-format) 将毫秒值时间戳转为指定格式日期
4:curdate() 返回当前日期
5:current_date() 返回当前日期
6:current_timestamp() 返回当前日期和时间
7:datediff(d1,d2) 计算日期d1>d2之间间隔的天数  eg:datediff('2022-01-01','2022-02-01')
8:currtime() 返回当前时间 
9:date_format(d,f) 按照表达式f的要求显示日期d

4:控制流函数

1:if(expr,v1,v2) 如果表达式expr成立,返回结果v1,否则返回结果v2
2:ifnull(v1,v2) 如果v1的值为null则返回v1,否则返回v2 
3:isnull(expression) 判断表达式是否为null 
4:nullif(expr1,expr2)  比较两个字符串 如果字符串expr1和expr2相等 返回null 否则返回expr1
5:case expression when condition1 then result1  when condition2 then result2 else result end 表示case函数开始,end表示函数结束,如果condition1成立则返回result1,如果condition2成立则返回result2,当全部不成立则返回result,而当有一个成立之后,后面的就不执行了.

5:窗口函数

mysql8.0新增的窗口函数又称为开窗函数,非聚合窗口函数是相对于聚合函数来说,聚合函数是对一组数据计算后返回单个值(即分组),非聚合函数一次只会处理一行数据,窗口聚合函数在行记录上计算某个字段的结果时,可将窗口范围内的数据输入到聚合函数中,并不改变行数

5.1 序号函数

可以实现分组排序,并添加序号

1: row_number()
2: rank()
3: dense_rank() 

写法:select id,...,dense_rank() over(partition by dname order by salary desc) as rn from employee;

注意: 不加 partition by 表示全局排序

5.2 分布函数

1: percent_rank()
  用途:每行按照公式(rank-1)/(row-1)进行计算.其中rank为rank()函数产生的序号,row为当前窗口的记录总行数
2: cume_dist()
  用途:分组内小于,等于当前rank值的行数/分组内总行数
  应用场景:查询小于等于当前薪资的比例

写法:select dname,ename,salary,cume_dist() over(order by salary) as rn1,
cume_dist() over(partition by dname order by salary) as rn2 from employe;

5.3 前后函数

1: lag(expr,n)
2: lead(expr,n)

用途:返回位于当前行的前n行(lag(exor,n))或后n行(lead(expr,n))的expr的值

应用场景:查询前1名同学的成绩和当前同学成绩的差值 (当前行内可以有个上一行数据的某一字段值)

5.4 头尾函数

1: first_value(expr)
2: last_value(expr)

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

5.5 其他函数

1: nth_value(expr,n)
2: ntile(n)

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

5.6 开窗聚合函数

1: sum()
2: avg()
3: min()
4: max()

写法:select id,...,sum(salary) over(partition by dname order by hiredate desc) as rn from employee;

每一行rn的数据是当期行和之前每一行的salary之和
如果没有order by排序语句,默认把分组内的所有数据进行sum操作