MySQL中超全的函数

函数目的：为了提高代码重用性和隐藏实现细节 

--数据准备

CREATE DATABASE IF NOT EXISTS mydb_proc;

USE mydb_proc;

#部门表

CREATE TABLE IF NOT EXISTS dept(
	deptno INT PRIMARY KEY,
  dname VARCHAR(20),
	loc VARCHAR(20)
);
INSERT INTO dept VALUES
(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

#员工表

CREATE TABLE IF NOT EXISTS emp(
	empno INT PRIMARY KEY,
	ename VARCHAR(20),
	job VARCHAR(20),
	mgr INT,
	hiredate DATE,
	sal NUMERIC(8,2),
	comm NUMERIC(8, 2),
	deptno INT,
-- 	FOREIGN KEY (mgr) REFERENCES emp(empno),
	FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO emp VALUES
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, NULL, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, NULL, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, NULL, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, NULL, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, NULL, 20),
(1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000.00, NULL, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, NULL, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, NULL, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, NULL, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, NULL, 10);

#员工工资等级表

CREATE TABLE IF NOT EXISTS salgrade(
	grade INT PRIMARY KEY,
	losal INT,
	hisal INT
);
INSERT INTO salgrade VALUES
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

1、聚合函数：group_concat()

函数首先根据group by指定的列进行分组，并且用分隔符分隔，将同一个分组中的值连接起来，返回一个字符串结果

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

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

指定排序方式，分组并添加分隔符(将同一组的emp_name 整合到一行,然后按照薪资对里面的人进行排序)

select department,group_concat(emp_name order by salary separator ';')from emp group by department;



2、数学函数：

(1)ABS()：求绝对值

select ABS(表达式或者字段或者数值) from 表名; --取绝对值

(2)CEIL():向上取整 FLOOR():向下取整

如：select ceil(1.1); --结果为2

(3)greatest():取列表最大值 least():取列表最小值

(4)mod()：取余 如：select mod(5,2); -----1

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

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

(7)round(x,y):取小数的四舍五入，取y位小数



3、字符串函数

(8)truncate(x,y):将小数x直接截取到 y位小数（截断）

(9)char_length("你好吗");——3 ——获取字符串的个数

length("你好吗"); ——9 ——取长度，获取的是字节的个数，utf-8一个汉字三个字节

(10)concat(c1,c2,c3...);字符串合并 concat_ws(x,c1,c2,c3...)指定x为分隔符进行合并

(11)field('a','b','a')——2；返回字符串在列表中第一次出现的位置

(12)ltrim()；rtrim()；trim()；——去重左边，右边，两边的空格

(13)mid(s,n,len);同subtring() 从字符串s的n位置截取长度为len的字符串

(14)position(s1 in s);s中获取s1开始的位置

(15)replace(s,s1,s2);将字符s2替代字符串s中s1

(16)reverse();将字符串顺序反过来

(17)ucase();upper(); 转成大写； lcase();lower(); 转成小写



4、日期函数

unix_timestamp() ——当前时间戳，距离1970年多少毫秒值

unix_timestamp(date_string)——该日期距离1970年多少毫秒值

from_unixtime(timestamp,'%Y-%m-%d %H:%i:%s')——将时间戳转化成指定的日期格式

curdate(),current_date();——获取当前的年月日

current_time(),curtime();——获取当前的时分秒

current_timestamp();——获取年月日时分秒

date();——从日期或者日期时间中提取日期值，年月日

datediff(d1,d2);——计算d1，d2之间相隔的天数

timediff(time1,time2);——计算时分秒的差值，返回时分秒

date_format();——日期格式化

str_to_date();——将字符串转成日期

date_sub();date_add();/adddate()——日期减时间，加时间的语法

extract();——日期中获取年、月、日、时、分、秒等

last_day();——获取给定日期所在月的最后一天

makedate('2021',53);——2021年的53天是哪一个日期



5、控制流函数

if(expr,v1,v2) ——条件成立，取v1，否则取v2

ifnull(v1,v2) ——如果是v1是null，取v2，如果v1不是null，则还是取v1

isnull(v1) ——如果v1是null，则返回1，否则返回0

nullif(v1,v2) ——v1-=v2的话，返回null，不一样的话则返回v1

case expression when condition1 then result1 ...else result end（判断成立后不会接下去执行）



6、窗口函数

window function(expr) over(

partition by ... ——对数据进行分区

order by... ——指定分区的排序

frame clause ——窗口大小

)



6.1 序号函数

row_number()|rank()|dense_rank() over(

partition by ... ——对数据进行分区，不加的话，就是对全局的数据进行排序

order by... ——指定分区的排序

)

row_number()：按顺序排序，数据相同时，按其他的接着排，序号不会断

rank()：数据相同时，并列排名，但是下一个会跳过这个序号

dense_rank()：数据相同时，并列排名，但是会接下来下个序号

select * from (

select dname,ename,salary,dense_rank() over(partition by dname order by salary desc) as rn

from employee

)t

where t.rn——取薪资前三，没有子查询的话，rn无法使用，where在select之后



6.2 开窗聚合函数(sum,avg,max,min,count)

可以动态地计算指定的窗口内的各种聚合函数值

select * from (

select dname,ename,salary,

sum(salary) over(partition by dname order by salary desc ——累计到该行的值的总数

rows between 3 preceding and 1 following) as rn ——从该行开始，向上三行，先下一行进行求和或者平均值等操作 

from employee;



6.3 分布函数(cume_dist 和 percent_rank )

cume_dist 介绍

用途：分组内小于、等于当前rank值的分数（包含自己当前行）/分组内总行数

应用场景：查询小于等于当前薪资的比例

select * from (

select dname,ename,salary,

cume_dist() over(order by salary desc)as rn1, ——没有partition语句，所有的数据位于一组

cume_dist() over(partition by dept order by salary) as rn2

from employee;

percent_rank(不常用)，用法同cume_dist()

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

select * from (

select dname,ename,salary,

rank() over(partition by dname order by salary desc)as rn1, ——没有partition语句，所有的数据位于一组

percent_rank() over(partition by dname order by salary) as rn2

from employee;



6.4 前后函数（lag()和lead() ）

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

应用场景：查询前1名同学的成绩和当前同学成绩的差值



6.5 头尾函数：first_value(),last_value()

用途：返回第一个 first_value(expr) 或者最后一个 last_value(expr)的值

应用场景：截止当前，按照日期排序查询第一个入职和最后一个入职员工的薪资



6.6 其他函数：nth_value(expr,n)和 ntile(n)

nth_value(expr,n)

用途：返回窗口中的第n个expr的值，expr可以是表达式，也可以是列名

应用场景：截止到当前薪资，显示每个员工的薪资中排名第二或者第三 的薪资

select * from (

select dname,ename,salary,

nth_value(salary,2) over(partition by dname order by hiredate desc)as first_score,

nth_value(salary,3) over(partition by dname order by hiredate desc)as second_score,

from employee;

ntile(n)

用途：将分区中的有序数据分成n个等级，记录等级数

应用场景：将每个部门员工按照入职日期分成3组

取出每个部门的第一组员工（组是ntile()进行分组的）

select * from (

select dname,ename,salary,



ntile(3) over(partition by dname order by hiredate desc)as nt

from employee

)t where t.nt=1;