当前位置:   article > 正文

MySQL8-常用函数以及自定义函数和存储过程总结_mysql8 函数faction

mysql8 函数faction

MySQL8-常用函数以及自定义函数和存储过程总结

一、概念

函数:代表一个独立的可复用的功能。

和Java中的方法类似又有所不同,不同点在于:MySQL中的函数必须有返回值,参数可以有可以没有。

MySQL中函数分类:

系统预定义函数:MySQL数据库管理软件给我提供好的函数,直接用就可以,任何数据库都可以用公共的函数。

  • 分组函数:或者又称为聚合函数,多行函数,表示会对表中的多行记录一起做一个“运算”,得到一个结果。
    • 求平均值的avg,求最大值的max,求最小值的min,求总和sum,求个数的count等
  • 单行函数:表示会对表中的每一行记录分别计算,有n行得到还是n行结果
    • 数学函数、字符串函数、日期时间函数、条件判断函数、窗口函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EG0FryxM-1674890737184)(mysql8函数.assets/8275f0ef95904f26945881a8ff4b09dd.png)]

用户自定义函数:由开发人员自己定义的,通过CREATE FUNCTION语句定义,是属于某个数据库的对象。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1ulpFxAa-1674890737185)(mysql8函数.assets/6efd11806c004d38867f90e101827987.png)]

二、分组函数

分组函数-聚合函数-分组函数

分组函数作用于一组数据,并对一组数据返回一个值。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hhWMyQSC-1674890737185)(mysql8函数.assets/0279c18a18ac4225822eb2abd7b0ce68.png)]

分组函数类型

  • AVG() --求平均值
  • SUM() --求和
  • MAX() --求最大值
  • MIN() --求最小值
  • COUNT() --统计
#演示分组函数,聚合函数,多行函数
#统计t_employee表的员工的数量
SELECT COUNT(*) FROM t_employee;
SELECT COUNT(1) FROM t_employee;
SELECT COUNT(eid) FROM t_employee;
SELECT COUNT(commission_pct) FROM t_employee;
 
/*
count(*)或count(常量值):都是统计实际的行数。
count(字段/表达式):只统计“字段/表达式”部分非NULL值的行数。
*/
 
#找出t_employee表中最高的薪资值
SELECT MAX(salary) FROM t_employee;
 
#找出t_employee表中最低的薪资值
SELECT MIN(salary) FROM t_employee;
 
#统计t_employee表中平均薪资值
SELECT AVG(salary) FROM t_employee;
 
#统计所有人的薪资总和,财务想看一下,一个月要准备多少钱发工资
SELECT SUM(salary) FROM t_employee; #没有考虑奖金
SELECT SUM(salary+salary*IFNULL(commission_pct,0)) FROM t_employee; 
 
#找出年龄最小、最大的员工的出生日期
SELECT MAX(birthday),MIN(birthday) FROM t_employee;
 
#查询最新入职的员工的入职日期
SELECT MAX(hiredate) FROM t_employee;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30

三、单行函数

只对一行进行变换,每行返回一个结果

可以嵌套

参数可以是一字段或一个表达式或一个值

1.数值函数/数学函数

函数用法
ABS(x)返回x的绝对值
CEIL(x)返回大于x的最小整数值
FLOOR(x)返回小于x的最大整数值
MOD(x,y)返回x/y的模
RAND()返回0~1的随机值
ROUND(x,y)返回参数x的四舍五入的有y位的小数的值
TRUNCATE(x,y)返回数字x截断为y位小数的结果
SQRT(x)返回x的平方根
POW(x,y)返回x的y次方
SELECT ROUND(45.926, 2)  -- 45.93
SELECT TRUNCATE(45.926)  -- 45
SELECT MOD(1600, 300)	 -- 100
 
#在“t_employee”表中查询员工无故旷工一天扣多少钱,
#分别用CEIL、FLOOR、ROUND、TRUNCATE函数。
#假设本月工作日总天数是22天,
#旷工一天扣的钱=salary/22。
SELECT ename,salary/22,CEIL(salary/22),
FLOOR(salary/22),ROUND(salary/22,2),
TRUNCATE(salary/22,2) FROM t_employee; 
 
#查询公司平均薪资,并对平均薪资分别
#使用CEIL、FLOOR、ROUND、TRUNCATE函数
SELECT AVG(salary),CEIL(AVG(salary)),
FLOOR(AVG(salary)),ROUND(AVG(salary)),
TRUNCATE(AVG(salary),2) FROM t_employee;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17

2.字符串函数

函数用法
CONCAT(S1,S2,…,Sn)连接S1,S2,…,Sn为一个字符串
CONCAT_WS(s, S1,S2,…,Sn)同CONCAT(s1,s2,…)函数,但是每个字符串之间要加上s
CHAR_LENGTH(s)返回字符串s的字符数
LENGTH(s)返回字符串s的字节数,和字符集有关
INSERT(str, index , len, instr)将字符串str从第index位置开始,len个字符长的子串替换为字符串instr
UPPER(s) 或 UCASE(s)将字符串s的所有字母转成大写字母
LOWER(s) 或LCASE(s)将字符串s的所有字母转成小写字母
LEFT(s,n)返回字符串s最左边的n个字符
RIGHT(s,n)返回字符串s最右边的n个字符
LPAD(str, len, pad)用字符串pad对str最左边进行填充,直到str的长度为len个字符
RPAD(str ,len, pad)用字符串pad对str最右边进行填充,直到str的长度为len个字符
LTRIM(s)去掉字符串s左侧的空格
RTRIM(s)去掉字符串s右侧的空格
TRIM(s)去掉字符串s开始与结尾的空格
TRIM(【BOTH 】s1 FROM s)去掉字符串s开始与结尾的s1
TRIM(【LEADING】s1 FROM s)去掉字符串s开始处的s1
TRIM(【TRAILING】s1 FROM s)去掉字符串s结尾处的s1
REPEAT(str, n)返回str重复n次的结果
REPLACE(str, a, b)用字符串b替换字符串str中所有出现的字符串a
STRCMP(s1,s2)比较字符串s1,s2
SUBSTRING(s,index,len)返回从字符串s的index位置其len个字符
#字符串函数
#mysql中不支持 + 拼接字符串,需要调用函数来拼接
#(1)在“t_employee”表中查询员工姓名ename和电话tel,
#并使用CONCAT函数,CONCAT_WS函数。
SELECT CONCAT(ename,tel),CONCAT_WS('-',ename,tel) FROM t_employee;
 
 
#(2)在“t_employee”表中查询员工姓名和薪资,并把姓名处理成“张xx”的样式。
#LEFT(s,n)函数表示取字符串s最左边的n个字符,
#而RPAD(s,len,p)函数表示在字符串s的右边填充p使得字符串长度达到len。
SELECT  RPAD(LEFT(ename,1),3,'x'),salary
FROM t_employee;
 
#(3)在“t_employee”表中查询薪资高于10000的男员工姓名、
#姓名包含的字符数和占用的字节数。
SELECT ename,CHAR_LENGTH(ename) AS 占用字符数,LENGTH(ename) AS 占用字节数量
FROM t_employee
WHERE salary>10000 AND gender ='男';
 
 
#(4)在“t_employee”表中查询薪资高于10000的男员工姓名和邮箱email,
#并把邮箱名“@”字符之前的字符串截取出来。
SELECT ename,email,
SUBSTRING(email,1, POSITION('@' IN email)-1)
FROM t_employee
WHERE salary > 10000 AND gender ='男';
#mysql中 SUBSTRING截取字符串位置,下标从1开始,不是和Java一样从0开始。
#mysql中 position等指定字符串中某个字符,子串的位置也不是从0开始,都是从1开始。
 
SELECT TRIM('    hello   world   '); #默认是去掉前后空白符
SELECT CONCAT('[',TRIM('    hello   world   '),']'); #默认是去掉前后空白符
SELECT TRIM(BOTH '&' FROM '&&&&hello   world&&&&'); #去掉前后的&符号
SELECT TRIM(LEADING '&' FROM '&&&&hello   world&&&&'); #去掉开头的&符号
SELECT TRIM(TRAILING '&' FROM '&&&&hello   world&&&&'); #去掉结尾的&符号
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34

2.1、字符串截取

字符串之间用逗号拼接起来

CONCAT_WS( ',', consumable_id, consumables_name ) AS info,
  • 1

截取第一个逗号之前的字符串

substring_index('info,123456', ',', 1 )
  • 1

结果

info
  • 1

截取第一个逗号之后的字符串

substring_index('info,123456', ',', -1 )
  • 1

结果

123456
  • 1

我的例子

CONCAT_WS( ',', consumable_id, consumables_name ) AS info,
  • 1
substring_index( CONCAT_WS( ',', consumable_id, consumables_name ), ',', 1 ) AS consumablesNewId,//截取第一个逗号之前的字符串
substring_index( CONCAT_WS( ',', consumable_id, consumables_name ), ',', - 1 ) AS consumablesNewName,//截取第一个逗号之后的字符串
  • 1
  • 2

3.日期函数

函数功能描述
CURDATE()或CURRENT_DATE()返回当前系统日期
CURTIME()或CURRENT_TIME()返回当前系统时间
NOW()/SYSDATE()/CURRENT_TIMESTAMP()/ LOCALTIME()/LOCALTIMESTAMP()返回当前系统日期时间
UTC_DATE()/UTC_TIME()返回当前UTC日期值/时间值
UNIX_TIMESTAMP(date)返回一个UNIX时间戳
YEAR(date)/MONTH(date)/DAY(date)/ HOUR(time)/MINUTE(time)/SECOND(time)返回具体的时间值
EXTRACT(type FROM date)从日期中提取一部分值
DAYOFMONTH(date)/DAYOFYEAR(date)返回一月/年中第几天
WEEK(date)/WEEKOFYEAR(date)返回一年中的第几周
DAYOFWEEK()返回周几,注意,周日是1,周一是2,…周六是7
WEEKDAY(date)返回周几,注意,周一是0,周二是1,…周日是6
DAYNAME(date)返回星期,MONDAY,TUESDAY,…SUNDAY
MONTHNAME(date)返回月份,January,…
DATEDIFF(date1,date2)/TIMEDIFF(time1,time2)返回date1-date2的日期间隔/返回time1-time2的时间间隔
DATE_ADD(date,INTERVAL expr type)或ADDDATE/DATE_SUB/SUBDATE返回与给定日期相差INTERVAL时间段的日期
ADDTIME(time,expr)/SUBTIME(time,expr)返回给定时间加上/减去expr的时间值
DATE_FORMAT(datetime,fmt)/ TIME_FORMAT(time,fmt)按照字符串fmt格式化日期datetime值/时间time值
STR_TO_DATE(str,fmt)按照字符串fmt对str进行解析,解析为一个日期
GET_FORMAT(val_type,format_type)返回日期时间字符串的显示格式

函数中日期时间类型说明

参数类型描述参数类型描述
YEARYEAR_MONTH年月
MONTHDAY_HOUR日时
DAYDAY_MINUTE日时分
HOURDAY_SECOND日时分秒
MINUTEHOUR_MINUTE时分
SECONDHOUR_SECOND时分秒
WEEK星期MINUTE_SECOND分秒
QUARTER一刻

函数中format参数说明

格式符说明格式符说明
%Y4位数字表示年份%y两位数字表示年份
%M月名表示月份(January,…)%m两位数字表示月份(01,02,03,…)
%b缩写的月名(Jan.,Feb.,…)%c数字表示月份(1,2,3…)
%D英文后缀表示月中的天数(1st,2nd,3rd,…)%d两位数字表示表示月中的天数(01,02,…)
%e数字形式表示月中的天数(1,2,3,…)%pAM或PM
%H两位数字表示小数,24小时制(01,02,03,…)%h和%I两位数字表示小时,12小时制(01,02,03,…)
%k数字形式的小时,24小时制(1,2,3,…)%l数字表示小时,12小时制(1,2,3,…)
%i两位数字表示分钟(00,01,02,…)%S和%s两位数字表示秒(00,01,02,…)
%T时间,24小时制(hh:mm:ss)%r时间,12小时制(hh:mm:ss)后加AM或PM
%W一周中的星期名称(Sunday,…)%a一周中的星期缩写(Sun.,Mon.,Tues.,…)
%w以数字表示周中的天数(0=Sunday,1=Monday,…)%j以3位数字表示年中的天数(001,002,…)
%U以数字表示的的第几周(1,2,3,…) 其中Sunday为周中的第一天%u以数字表示年中的年份(1,2,3,…) 其中Monday为周中第一天
%V一年中第几周(01~53),周日为每周的第一天,和%X同时使用%X4位数形式表示该周的年份,周日为每周第一天,和%V同时使用
%v一年中第几周(01~53),周一为每周的第一天,和%x同时使用%x4位数形式表示该周的年份,周一为每周第一天,和%v同时使用
%%表示%

GET_FORMAT函数中val_type 和format_type参数说明

值类型格式化类型显示格式字符串
DATEEUR%d.%m.%Y
DATEINTERVAL%Y%m%d
DATEISO%Y-%m-%d
DATEJIS%Y-%m-%d
DATEUSA%m.%d.%Y
TIMEEUR%H.%i.%s
TIMEINTERVAL%H%i%s
TIMEISO%H:%i:%s
TIMEJIS%H:%i:%s
TIMEUSA%h:%i:%s %p
DATETIMEEUR%Y-%m-%d %H.%i.%s
DATETIMEINTERVAL%Y%m%d %H%i%s
DATETIMEISO%Y-%m-%d %H:%i:%s
DATETIMEJIS%Y-%m-%d %H:%i:%s
DATETIMEUSA%Y-%m-%d %H.%i.%s
#日期时间函数
/*
获取系统日期时间值
获取某个日期或时间中的具体的年、月等值
获取星期、月份值,可以是当天的星期、当月的月份
获取一年中的第几个星期,一年的第几天
计算两个日期时间的间隔
获取一个日期或时间间隔一定时间后的另个日期或时间
和字符串之间的转换
*/
#(1)获取系统日期。CURDATE()和CURRENT_DATE()函数都可以获取当前系统日期。将日期值“+0”会怎么样?
SELECT CURDATE(),CURRENT_DATE();
 
#(2)获取系统时间。CURTIME()和CURRENT_TIME()函数都可以获取当前系统时间。将时间值“+0”会怎么样?
SELECT CURTIME(),CURRENT_TIME();
 
#(3)获取系统日期时间值。CURRENT_TIMESTAMP()、LOCALTIME()、SYSDATE()和NOW()
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),NOW();
 
#(4)获取当前UTC(世界标准时间)日期或时间值。
#本地时间是根据地球上不同时区所处的位置调整 UTC 得来的,
#例如,北京时间比UTC时间晚8个小时。
#UTC_DATE(),CURDATE(),UTC_TIME(), CURTIME()
SELECT UTC_DATE(),CURDATE(),UTC_TIME(), CURTIME();
 
 
#(5)获取UNIX时间戳。
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-1-1');
 
#(6)获取具体的时间值,比如年、月、日、时、分、秒。
#分别是YEAR(date)、MONTH(date)、DAY(date)、HOUR(time)、MINUTE(time)、SECOND(time)。
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE());
SELECT HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());
 
 
#(7)获取日期时间的指定值。EXTRACT(type FROM date/time)函数
SELECT EXTRACT(YEAR_MONTH FROM CURDATE());
 
#(8)获取两个日期或时间之间的间隔。
#DATEDIFF(date1,date2)函数表示返回两个日期之间间隔的天数。
#TIMEDIFF(time1,time2)函数表示返回两个时间之间间隔的时分秒。
 
#查询今天距离员工入职的日期间隔天数
SELECT ename,DATEDIFF(CURDATE(),hiredate) FROM t_employee;
 
#查询现在距离中午放学还有多少时间
SELECT TIMEDIFF(CURTIME(),'12:0:0');
 
#(9)在“t_employee”表中查询本月生日的员工姓名、生日。
SELECT ename,birthday
FROM t_employee
WHERE MONTH(CURDATE()) = MONTH(birthday);
 
 
#(10)#查询入职时间超过5年的
SELECT ename,hiredate,DATEDIFF(CURDATE(),hiredate) 
FROM t_employee
WHERE DATEDIFF(CURDATE(),hiredate)  > 365*5;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58

我的DATE_FORMAT例子

AND DATE_FORMAT( coo.odo_date,'%Y-%m') LIKE CONCAT('%',#{findInOutStockDTO.useDateFormat},'%')  
-- odo_date字段的数据库表类型为date  
  • 1
  • 2
// 注意:前端传参useDateFormat字段的数据库表类型为string(年-月),而不是LocalDate类型的useDate,不然会出错

@ApiModelProperty(value = "日期")
private LocalDate useDate;

@ApiModelProperty(value = "日期格式化")
private String useDateFormat;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

4.流程函数

函数功能
IF(value,t,f)如果value是真,返回t,否则返回f
IFNULL(value1,value2)如果value1不为空,返回value1,否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … [ELSE resultn] END依次判断条件,哪个条件满足了,就返回对应的result,所有条件都不满足就返回ELSE的result。如果没有单独的ELSE子句,当所有WHEN后面的条件都不满足时则返回NULL值结果。相当于Java的if…else if…else…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN 值1 … [ELSE 值n] END判断表达式expr与哪个常量值匹配,找到匹配的就返回对应值,都不匹配就返回ELSE的值。如果没有单独的ELSE子句,当所有WHEN后面的常量值都不匹配时则返回NULL值结果。相当于Java的switch…case…
#条件判断函数
/*
这个函数不是筛选记录的函数,
而是根据条件不同显示不同的结果的函数。
*/
#如果薪资大于20000,显示高薪,否则显示正常
SELECT ename,salary,IF(salary>20000,'高薪','正常')
FROM t_employee;
 
#计算实发工资
#实发工资 = 薪资 + 薪资 * 奖金比例
SELECT ename,salary,commission_pct,
salary + salary * commission_pct
FROM t_employee;
#如果commission_pct是,计算完结果是NULL
 
SELECT ename,salary,commission_pct,
salary + salary * IFNULL(commission_pct,0) AS 实发工资
FROM t_employee;
 
 
SELECT ename,salary,commission_pct,
ROUND(salary + salary * IFNULL(commission_pct,0),2) AS 实发工资
FROM t_employee;
 
#查询员工编号,姓名,薪资,等级,等级根据薪资判断,
#如果薪资大于20000,显示“羡慕级别”,
#如果薪资15000-20000,显示“努力级别”,
#如果薪资10000-15000,显示“平均级别”
#如果薪资10000以下,显示“保底级别”
/*mysql中没有if...elseif函数,有case 函数。
等价于if...elseif 
*/
SELECT eid,ename,salary,
CASE WHEN salary>20000 THEN '羡慕级别'
     WHEN salary>15000 THEN '努力级别'
     WHEN salary>10000 THEN '平均级别'
     ELSE '保底级别'
END AS "等级"
FROM t_employee;  
 
#在“t_employee”表中查询入职7年以上的
#员工姓名、工作地点、轮岗的工作地点数量情况。
/*
计算工作地点的数量,转换为求 work_place中逗号的数量+1。
 work_place中逗号的数量 = work_place的总字符数 -  work_place去掉,的字符数
 work_place去掉, ,使用replace函数
*/
SELECT work_place, 
CHAR_LENGTH(work_place)-CHAR_LENGTH(REPLACE(work_place,',',''))
FROM t_employee;
 
 #类似于Java中switch...case
SELECT ename,work_place,
CASE (CHAR_LENGTH(work_place)-CHAR_LENGTH(REPLACE(work_place,',',''))+1)
WHEN 1 THEN '只在一个地方工作'
WHEN 2 THEN '在两个地方来回奔波'
WHEN 3 THEN '在三个地方流动'
ELSE '频繁出差'
END AS "工作地点数量情况"
FROM t_employee
WHERE DATEDIFF(CURDATE(),hiredate)  > 365*7;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62

5.其他函数

函数用法
database()返回当前数据库名
version()返回当前数据库版本
user()返回当前登录用户名
password(str)返回字符串str的加密版本,41位长的字符串。mysql8弃用
md5(str)返回字符串str的md5值,也是一种加密方式

6.窗口函数

窗口函数也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据进行实时分析处理。窗口函数是每条记录都会分析,有几条记录执行完还是几条,因此也属于单行函数。MySQL8版本开始支持窗口函数。

函数分类函数功能描述
序号函数ROW_NUMBER()顺序排序,每行按照不同的分组逐行编号,例如:1,2,3,4
RANK()并列排序,每行按照不同的分组进行编号,同一个分组中排序字段值出现重复值时,并列排序并跳过重复序号,例如:1,1,3
DENSE_RANK()并列排序,每行按照不同的分组进行编号,同一个分组中排序字段值出现重复值时,并列排序不跳过重复序号,例如:1,1,2
分布函数PERCENT_RANK()排名百分比,每行按照公式(rank-1)/ (rows-1)进行计算。其中,rank为RANK()函数产生的序号,rows为当前窗口的记录总行数
CUME_DIST()累积分布值,表示每行按照当前分组内小于等于当前rank值的行数 / 分组内总行数
前后函数LAG(expr,n)返回位于当前行的前n行的expr值
LEAD(expr,n)返回位于当前行的后n行的expr值
首尾函数FIRST_VALUE(expr)返回当前分组第一行的expr值
LAST_VALUE(expr)返回当前分组每一个rank最后一行的expr值
其他函数NTH_VALUE(expr,n)返回当前分组第n行的expr值
NTILE(n)用于将分区中的有序数据分为n个等级,记录等级数

窗口函数的语法格式如下

函数名([参数列表]) OVER ()
函数名([参数列表]) OVER (子句)

over关键字用来指定窗口函数的窗口范围。如果OVER后面是空(),则表示SELECT语句筛选的所有行是一个窗口。OVER后面的()中支持以下4种语法来设置窗口范围。

  • WINDOW:给窗口指定一个别名;
  • PARTITION BY子句:一个窗口范围还可以分为多个区域。按照哪些字段进行分区/分组,窗口函数在不同的分组上分别处理分析;
  • ORDER BY子句:按照哪些字段进行排序,窗口函数将按照排序后结果进行分析处理;
  • FRAME子句:FRAME是当前分区的一个子集,FRAME子句用来定义子集的规则。
#(1)在“t_employee”表中查询薪资在[8000,10000]之间的员工姓名和薪资并给每一行记录编序号
SELECT ROW_NUMBER() OVER () AS "row_num",ename,salary
FROM t_employee WHERE salary BETWEEN 8000 AND 10000;
 
#(2)计算每一个部门的平均薪资与全公司的平均薪资的差值。
SELECT  did,AVG(salary) OVER() AS avg_all,
AVG(salary) OVER(PARTITION BY did) AS avg_did,
ROUND(AVG(salary) OVER()-AVG(salary) OVER(PARTITION BY did),2) AS deviation
FROM  t_employee;
 
 
#(3)在“t_employee”表中查询女员工姓名,部门编号,薪资,查询结果按照部门编号分组后在按薪资升序排列,并分别使用ROW_NUMBER()、RANK()、DENSE_RANK()三个序号函数给每一行记录编序号。
SELECT ename,did,salary,gender,
ROW_NUMBER() OVER (PARTITION BY did ORDER BY salary) AS "row_num",
RANK() OVER (PARTITION BY did ORDER BY salary) AS "rank_num" ,
DENSE_RANK() OVER (PARTITION BY did ORDER BY salary) AS "ds_rank_num" 
FROM t_employee WHERE gender='女';
 
#或
 
SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary);
 
 
#(4)在“t_employee”表中查询每个部门最低3个薪资值的女员工姓名,部门编号,薪资值。
SELECT ROW_NUMBER() OVER () AS "rn",temp.*
FROM(SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary))temp 
WHERE temp.rank_num<=3;
 
#或
SELECT ROW_NUMBER() OVER () AS "rn",temp.*
FROM(SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary))temp 
WHERE temp.ds_rank_num<=3;
 
 
#(5)在“t_employee”表中查询每个部门薪资排名前3的员工姓名,部门编号,薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee
WINDOW w AS (PARTITION BY did ORDER BY salary DESC))temp 
WHERE temp.ds_rank_num<=3;
 
#(6)在“t_employee”表中查询全公司薪资排名前3的员工姓名,部门编号,薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee
WINDOW w AS (ORDER BY salary DESC))temp 
WHERE temp.ds_rank_num<=3;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64

四、自定义函数

4.1、创建函数

功能:递归查询顶级部门id和部门名称

函数名:findRootDeptInfo

-- 自定义查询顶级父id的函数
DELIMITER //

CREATE DEFINER=`root`@`%` FUNCTION `findRootDeptInfo`(args VARCHAR ( 50 )) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
RETURNS VARCHAR ( 50 )

BEGIN
  DECLARE tempDeptId VARCHAR ( 50 );
  DECLARE rootDeptId VARCHAR ( 50 );
  DECLARE rootTitle VARCHAR ( 50 );
  DECLARE deptInfo VARCHAR ( 50 );
	
  SET rootDeptId = args;
	
  WHILE rootDeptId != '0' DO
		SET tempDeptId = rootDeptId;
  SELECT parent_id INTO rootDeptId FROM view_department where id = rootDeptId;
  END WHILE;
	
  SELECT title INTO rootTitle FROM view_department where id = tempDeptId;
  SELECT CONCAT_WS( ',', tempDeptId, rootTitle ) INTO deptInfo;
  RETURN deptInfo;
END //

DELIMITER ;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25

解析

(1)DELIMITER // -- 1、开始处不以分号作为结束符,而是修改结束符为:' // ' 方便后面写一个或多个sql语句;2、结尾处改回结束符为:' ; ' 也即 DELIMITER ;

(2)RETURNS VARCHAR ( 50 ) -- 定义函数的返回类型,必不可少

(3)BEGIN ... END // -- BEGIN和END之间写自定义的内容,可定义变量(DECLARE),写条件判断( IF...END IF; )、循环逻辑( WHILE...END WHILE; )、选择逻辑( 
CASE WHEN a.parentDeptId = '0' THEN
	a.deptId 
WHEN a.parentDeptId != '0' THEN
( SELECT vdt.id FROM view_department vdt WHERE vdt.id = a.parentDeptId ) 
END 
) rootDeptId,

(4)、最后RETURN 要返回的变量;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

创建后的函数sql

CREATE DEFINER=`root`@`%` FUNCTION `findRootDeptInfo`(args VARCHAR ( 50 )) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
  DECLARE tempDeptId VARCHAR ( 50 );
  DECLARE rootDeptId VARCHAR ( 50 );
  DECLARE rootTitle VARCHAR ( 50 );
  DECLARE deptInfo VARCHAR ( 50 );
	
  SET rootDeptId = args;
	
  WHILE rootDeptId != '0' DO
		SET tempDeptId = rootDeptId;
  SELECT parent_id INTO rootDeptId FROM view_department where id = rootDeptId;
  END WHILE;
	
  SELECT title INTO rootTitle FROM view_department where id = tempDeptId;
  SELECT CONCAT_WS( ',', tempDeptId, rootTitle ) INTO deptInfo;
  RETURN deptInfo;
END
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

4.2、解决NO SQL报错的问题

-- 解决NO SQL报错的问题
-- 1、临时解决
SET GLOBAL log_bin_trust_function_creators = TRUE;
或者
SET GLOBAL log_bin_trust_function_creators = 1;

-- 2、永久解决
在mysql的my.cnf配置文件中添加:
log_bin_trust_function_creators =1
-- 然后重启mysql服务即可
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

4.3、调用函数

-- 调用函数
SELECT findRootDeptInfo('258809317615552512')
  • 1
  • 2

4.4、总的sql

-- 1、自定义查询顶级父id的函数
DELIMITER //	

CREATE DEFINER=`root`@`%` FUNCTION `findRootDeptInfo`(args VARCHAR ( 50 )) RETURNS varchar(50) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
RETURNS VARCHAR ( 50 )

BEGIN
  DECLARE tempDeptId VARCHAR ( 50 );
  DECLARE rootDeptId VARCHAR ( 50 );
  DECLARE rootTitle VARCHAR ( 50 );
  DECLARE deptInfo VARCHAR ( 50 );
	
  SET rootDeptId = args;
	
  WHILE rootDeptId != '0' DO
		SET tempDeptId = rootDeptId;
  SELECT parent_id INTO rootDeptId FROM view_department where id = rootDeptId;
  END WHILE;
	
  SELECT title INTO rootTitle FROM view_department where id = tempDeptId;
  SELECT CONCAT_WS( ',', tempDeptId, rootTitle ) INTO deptInfo;
  RETURN deptInfo;
END //

DELIMITER ;


-- 2、解决NO SQL报错的问题
SET GLOBAL log_bin_trust_function_creators = TRUE;

-- 3、调用函数
SELECT findRootCatId('258809317615552512')
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

五、自定义存储过程

5.1、创建一个存储过程

创建一个存储过程avg_svi,有3个参数,分别是deptno,job,接收平均工资, 功能查询emp表dept为30,job为销售员的平均工资

mysql> \d $
  • 1
CREATE PROCEDURE avg_svi ( IN p1 INT, IN p2 VARCHAR ( 30 ), OUT avg FLOAT ) BEGIN
	SELECT
		avg( sal ) INTO avg 
	FROM
		emp 
	WHERE
		deptno = p1 
		AND job = p2;

END $
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

5.2、调用存储过程

mysql> call avg_svi(30,'销售员',@a) /
  • 1
mysql> select @a/
  • 1

±-------------------+
| @a |
±-------------------+
| 3222.22249609375 |
±-------------------|
1 row in set (0.00 sec)

六、问题

MySQL8中创建函数报错的问题

解决

6.1、问题描述

在使用MySQL8.x版本的数据库创建函数时, 报出一下错误

HY000][1418] This function has none of DETERMINISTIC, 
NO SQL, or READS SQL DATA in its declaration and binary logging is enabled 
you might want to use the less safe log_bin_trust_function_creators variable
  • 1
  • 2
  • 3

简单翻译一下 , 函数定义不明确 , 没有SQl语句 , 或者只是读取数据不会执行 , 并且已经启用了二进制文件记录, 你可能需要使用不太安全的 log_bin_trust_function_creators 变量;

说人话 : MySQL5.x 中 可以直接创建函数 , 但是在MySQL8.x 中, 不能直接创建函数 , 需要首先设置 log_bin_trust_function_creators=true , 才可以;

6.2、解决方案

方案一 : 设置全局环境变量

特点 : 不需要重启服务器, 在服务器的一次运行中始终有效 , 重启MySQL服务之后会失效!

SHOW VARIABLES LIKE '%log_bin_trust_function_creators%';-- 查看默认值

SET GLOBAL log_bin_trust_function_creators = TRUE;-- 修改默认值为true
  • 1
  • 2
  • 3
方案二 : 修改系统配置文件

特点 : 需要重启服务器, 终身有效 !
找到my.cnf配置文件中添加:

log_bin_trust_function_creators =1
  • 1

修改完成以后, 记得重启一下服务哦 ! 如果不想重启服务 , 也可以将方案一二结合使用 , 即修改全局环境变量 , 又修改系统配置文件 !

声明:本文内容由网友自发贡献,转载请注明出处:【wpsshop】
推荐阅读
相关标签
  

闽ICP备14008679号