MySQL从入门到高级 --- 6.函数

第六章：

6.函数

作用：提高代码重用性和隐藏实现细节

分类：

• 聚合函数

• 数学函数

• 字符串函数

• 日期函数

• 控制流函数

• 窗口函数

6.1 聚合函数

• group_concat()

作用：根据group by指定的列进行分组，并用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果，实现行的合并

语法：group_concat ([distinct] 字段名 [order by 排序字段 asc/desc] [separator ‘分隔符’] )

separator为一个字符串值，默认为逗号

6.2 数学函数

函数名	功能
ABS(x)	返回x的绝对值
CEIL(x)	返回大于或等于x的最小整数
FLOOR(x)	返回小于或等于x的最大整数
GREATEST(expr1, expr2, expr3, …)	返回列表中的最大值
LEAST(expr1, expr2, expr3, …)	返回列表中的最小值

函数名	功能
MAX(expression)	返回字段expression中的最大值
MIN(expression)	返回字段expression中的最小值
MOD(x,y)	返回x除以y后的余数
PI()	返回圆周率 3.141593
POW(x,y)	返回x的y次方

函数名	功能
RAND()	返回0到1的随机值
ROUND(x)	返回离x最近的整数，遵循四舍五入规则
ROUND(x,y)	返回指定位数的小数，遵循四舍五入规则
TRUNCATE(x,y)	返回数值x保留到小数点后y位的值，不遵循四舍五入规则

6.3 字符串函数

函数名	功能
CHAR_LENGTH(s)	返回字符串s的字符数
CHARACTER_LENGTH(s)	返回字符串s的字符数
CONCAT(s1,s2,s3,…)	s1，s2等多个字符串合并成一个字符串
CONCAT_WS(s1,s2,s3,…)	同CONCAT函数，每个字符串之间加上x,x可以是分隔符
FIELD(s,s1,s2,s3,…)	返回第一个字符串s在字符串列表(s1,s2,…)中的位置

函数名	功能
LTRIM(s)	去除字符串s开始处的空格
MID(s,n,len)	从字符串s的n位置开始截取长度为len的子字符串，同SUBSTRING函数
POSITION(s1 IN s)	从字符串s中获取s1的开始位置
REPLACE(s,s1,s2)	字符串s2替代字符串s中的字符串s1
REVERSE(s)	字符串s的顺序反过来

函数名	功能
RIGHT(s,n)	返回字符串s的后n个字符
RTRIM(s)	去除字符串s结尾处的空格
STRCMP(s1,s2)	比较s1和s2，若相等返回0，s1>s2返回1，s1<s2返回-1
SUBSTR(s,start,len)	从字符串s的start位置开始截取长度为len的子字符串
SUBTRING(s,start,len)	从字符串s的start位置开始截取长度为len的子字符串

函数名	功能
TRIM(s)	去除字符串s开始和结尾处的空格
UCASE(s)	字符串转换为大写
UPPER(s)	字符串转换为大写
LCASE(s)	字符串转换为小写
LOWER(s)	字符串转换为小写

6.4 日期函数

函数名	功能
UNIX_TIMESTAMP()	返回从1970-01-01 00:00:00到当前毫秒值
UNIX_TIMESTAMP(DATE_STRING)	将制定日期转为毫秒值时间戳
FROM_UNIXTIME(BIGINT UNIXTIME[, STRING FORMAT])	将毫秒值时间戳转为指定格式日期
CURDATE()	返回当前日期
CURRENT_DATE()	返回当前日期

函数名	功能
TIMEDIFF(time1, time2)	计算时间差值
DATE_FORMAT(d, f)	按表达式f的要求显示日期d
STR_TO_DATE(string, format_mask)	将字符串转为日期
DATE_SUB(date, INTERVAL expr type)	函数从日期减去指定的时间间隔

6.4.1 日期格式

描述	描述
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时(00-23)
%h	小时(01-12)
%I	小时(01-12)
%i	分钟，数值(00-59)
%j	年的天(001-366)
%k	小时(0-23)
%l	小时(1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时(hh:mm:ss AM 或PM)
%S	秒(00-59)
%s	秒(00-59)
%T	时间，24-小时(hh:mm:ss)
%U	周(00-53)星期日是一周的第一天
%u	周(00-53)星期一是一周的第一天
%V	周(01-53)星期日是一周的第一天，与%X使用
%v	周(01-53)星期一是一周的第一天，与%x使用
%W	星期名
%w	周的天(0=星期日, 6=星期六)
%X	年，其中的星期日是周的第一天，4位，与%V使用
%x	年，其中的星期一是周的第一天，4位，与%v使用
%Y	年，4位
%y	年，2位

6.5 控制流函数

6.5.1 if逻辑判断语句

格式	含义
IF(expr, v1, v2)	若表达式expr成立，返回结果v2，否则返回结果v2
IFNULL(v1, v2)	若v1的值不为NULL，返回v1，否则返回v2
ISNULL(expression)	判断表达式是否为NULL
NULLIF(expr1, expr2)	若字符串expr1与expr2字符串相等返回NULL，否则返回expr1

6.5.2 case when语句

格式：

CASE expression

WHEN conditon1 THEN result1WHEN conditon2 THEN result2....WHEN conditonN THEN resultNELSE result

END

含义：CASE表示函数开始，END表示函数结束。若condition1成立，返回result1，condition2成立，返回result2,当全部不成立返回result，而当有一个成立后，后面将不再执行

6.6 窗口函数

窗口函数又被称为开窗函数。

非聚合窗口函数对于聚合函数来说，聚合函数是一组数据计算后返回单个值，非聚合函数一次指挥处理一行数据。窗口聚合函数在行记录上计算某个字段的结果时，可将窗口范围内的数据输入到聚合函数中，并不改变行数。

语法：

windows_function (expr) OVER(PARTITION BY ...    ORDER BY ...frame_clause
)

• windows_function：窗口函数名

• expr：参数

• OVER：包含三个选项

1. 分区(PARTITION BY)：用于将数据行拆分成多个分区。若省略PARTITION BY，所有数据作为一个组进行计算

2. 排序(ORDER BY)：用于指定分区内的排序方式

3. 窗口大小(frame_clause)：用于在当前分区内指定一个计算窗口

6.6.1 序号函数

• ROW_NUMBER()

• RANK()

• DENSE_RANK()

作用：实现分组排序，并添加序号

语法：

row_number() | rank() | dense_rank() over(partition by ...order by ...
)

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');-- 对每个部门员工按薪资排序，给出排名
select dname,ename,sal,row_number() over(partition by dname order by sal desc) as rn1,rank() over(partition by dname order by sal desc) as rn2,dense_rank() over(partition by dname order by sal desc) as rn3
from emp;-- 求出每个部门薪水排前两名的员工，分组求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;-- 对所有员工进行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;

6.6.2 开窗聚合函数

SUM, AVG, MIN, MAX

• 在窗口中每条记录动态地应用聚合函数，可动态计算在指定地窗口内的各种聚合函数值

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');-- 对每个部门员工按薪资排序，给出排名
select dname,ename,sal,row_number() over(partition by dname order by sal desc) as rn1,rank() over(partition by dname order by sal desc) as rn2,dense_rank() over(partition by dname order by sal desc) as rn3
from emp;-- 求出每个部门薪水排前两名的员工，分组求TOPN
select * from (select dname,ename,sal, dense_rank() over(partition by dname order by sal desc) as rn from emp)t where t.rn <= 2;-- 对所有员工进行全局排序 
select dname,ename,sal,dense_rank() over(partition by dname order by sal desc) as rn from emp;-- 若没有ORDER BY排序语句， 默认把分组内所有数据进行sum操作
select dname,ename,sal,sum(sal) over(partition by dname order by sal desc) as p1 from emp;
select dname,ename,sal,sum(sal) over(partition by dname ) as p2 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and current row) as p3 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between 3 preceding and 1 following) as p4 from emp;
select dname,ename,sal,sum(sal) over(partition by dname rows between current row and unbounded following) as p5 from emp;
select dname,ename,sal,max(sal) over(partition by dname order by sal desc) as p5 from emp;

6.6.3 分布函数

CUME_DIST 和 PERECENT_RANK

• CUME_DIST：用于分组内小于、等于当前rank值的行数/分组内总行数，应用于查询小于等于当前薪资(sal)比例

create table emp(dname varchar(20),ename varchar(20),eid varchar(20),sal double
);insert into emp values('技术部', '101', 'xiaoming', '3500');
insert into emp values('技术部', '101', 'xiaowang', '4000');
insert into emp values('技术部', '101', 'xiaoli', '3600');
insert into emp values('技术部', '101', 'xiaoni', '3500');
insert into emp values('技术部', '101', 'xiaona', '3400');
insert into emp values('技术部', '101', 'xiaone', '3300');
insert into emp values('技术部', '101', 'xiaonl', '3200');insert into emp values('运营部', '102', 'xiaohua', '3000');
insert into emp values('运营部', '102', 'xiaohong', '3100');
insert into emp values('运营部', '102', 'xiaolu', '3300');
insert into emp values('运营部', '102', 'xiaolo', '3800');
insert into emp values('运营部', '102', 'xiaola', '3700');
insert into emp values('运营部', '102', 'xiaole', '3600');
insert into emp values('运营部', '102', 'xiaolt', '3000');select dname,ename,sal, cume_dist() over(order by sal) as rn1, -- 没有partition语句 所有数位于一组cume_dist() over(partition by ename order by sal) as rn2 
from emp;

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

select dname,ename,sal,rank() over(partition by dname order by sal desc) as rn,percent_rank() over(partition by dname order by sal desc) as rn2from emp;

6.6.4 前后函数

LAG和LEAD

• 用于返回位于当前行的前n行(LAG(expr, n)或后n行(LEAD(expr, n)的expr值，应用于查询前1名同学的成绩和当前同学成绩的差值

 -- LAG select dname,ename,sal,lag(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,lag(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;-- LEAD select dname,ename,sal,lead(sal,1,3500) over(partition by dname order by sal ) as last_1_sal,lead(sal,2) over(partition by dname order by sal ) as last_2_sal
from emp;

6.6.5 头尾函数

FIRST_VALUE和LAST_VALUE

• 用于返回第一个(FIRST_VALUE)或最后一个(LAST_VALUE)的expr值。应用于截止到目前，按入职编号排序查询第一个入职和最后一个入职的员工薪资

select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果first_value(sal) over(partition by dname order by eid) as first,last_value(sal) over(partition by dname order by eid) as last 
from emp;

6.6.6 其他函数

NTH_VALUE(expr, n)和NTILE(n)

• 用于返回窗口中第n个expr值。应用于截止到当前薪资，显示每个员工中薪资排第2或3的薪资

-- NTH_VALUE()
-- 查询各部门截止目前薪资排第2或3的员工个人信息
select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果nth_value(sal,2) over(partition by dname order by eid) as second_sal,nth_value(sal,3) over(partition by dname order by eid) as thrid_sal 
from emp;-- NTILE()
-- 根据入职编号将各部门员工分3组
select dname, ename, sal, eid, -- 若不指定order by 将会排序混乱，出现错误的结果ntile(3) over(partition by dname order by eid) as rn
from emp;-- 取出各部门的第一组员工
select * from (select dname, ename, sal, eid, ntile(3) over(partition by dname order by eid) as rn from emp)t 
where t.rn = 1;

6.7 练习

-- 查询各部门平均薪水最高的部门名
select a.deptno,a.dname,a.location,avg_sal
fromdept a,(select *from (select *,rank() over(order by avg_sal desc ) rn from(select deptno,avg(sal) avg_sal from emp group by deptno)t1)t2where rn = 1)t3 where a.deptno = t3.deptno;--  查询员工比所属领导薪资高的员工个人信息
create view test_view asselect a.ename ename,a.sal esal,b.ename mgrname,b.sal mgrsal, a.deptno fromemp a,emp b where a.mgr = b.deptnoand a.sal > b.sal;select * from dept a join test_view b on a.deptno = b.deptno;