函数
MySQL函数与其存储过程类似,是一系列完成某种功能的SQL语句。函数一旦定义后,与过程一样是存储在MySQL的服务器上。调用函数就是一次性执行这些语句。所以函数可以降低语句重复。
MySQL本身提供了内置函数,这些函数的存在给我们日常的开发和数据操作带来了很大的便利,MySQL提供的常用函数包括聚合函数,字符串函数,日期时间函数,控制流函数等。
MySQL除了可以使用它提供的内置函数外,我们亦可以按照我们的要求来实现我们自己的功能,可是这个函数式系统不能提供的因为这中需求的不确定性。因此我们需要自己来解决这种需求。还好MySQL设计的扩展性给了我们这个机会,我们可以通过自定义函数的功能解决这个问题。
函数名称作用abs绝对值mod求余数sqrt求二次方根ceil和ceiling都是小于参数的最小整数,向上取整floor向下取整,返回值转换为bigintrand生成0-1的之间的随机数round四舍五入sign返回带参数的符号pow和 power都是所传代数的次方的结果值sin求正弦值asin求反正弦值,cos求余弦值acos求反余弦值tan求正切值atan求反正切值cot求余切值
abs:求绝对值
函数abs(x)返回x的绝对值,正数的绝对值时本身,负数的绝对值时相反数,0的绝对值是0
select abs(5),abs(1.1),abs(-3.3),abs(0);
![在这里插入图片描述](https://img-blog.csdnimg.cn/1792e08bb6444fc6ba4760160dcf6677.png)
sqrt:求二次方根(开方)
函数SQRT(x)返回非负数x的二次方根,负数没有平方根,返回结果为null
select sqrt(25),sqrt(120),sqrt(-9);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MdxwTPsy-1679467513331)(MySQL函数.assets/image-20220923154424057.png)]](https://img-blog.csdnimg.cn/f0657657a76746789ddaa8b4476ded1e.png)
mod:求余数
函数mod(x,y)返回x被y除后的余数,mod()对于带小数部分的数据也会起作用,返回除法算后的余数
select mod(63,8),mod (120,10),mod(15.5,3);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HyH3wzZz-1679467513331)(MySQL函数.assets/image-20220923154835020.png)]](https://img-blog.csdnimg.cn/bd6866d9af294a1796b112a2e27bef8d.png)
ceil和ceiling:向上取整
函数ceil和ceiling的意义是相同的,返回不小于x的最小整数值,返回值转换为一个bigint
select ceil(-2.5),ceil(3.3),ceiling(2.5);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-oe8yCdAg-1679467513332)(MySQL函数.assets/image-20220923155130131.png)]](https://img-blog.csdnimg.cn/89a2c5be0ff14e138c9f76cd8831379c.png)
floor:向下取整
函数floor(x)返回小于x的最大整数值
select floor(5),floor(5.66),floor(-4),floor(-4.66);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j4UmjHe9-1679467513332)(MySQL函数.assets/image-20220923155403289.png)]](https://img-blog.csdnimg.cn/09e1cbea4790470a8fbe67da86ee0dfc.png)
rand:生成一个随机数
生成一个0-1之间的随机数
select rand(),rand(),rand();
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-sCdSDOy9-1679467513332)(MySQL函数.assets/image-20220923155616569.png)]](https://img-blog.csdnimg.cn/576770fa1e8d4ed38013aecde8c53e08.png)
round:四舍五入
返回最近于参数x的整数,round(x)函数对参数x进行四舍五入的操作,
select round(-6.6),round(-8.44),round(3.44);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zgtU85zJ-1679467513332)(MySQL函数.assets/image-20220923155847344.png)]](https://img-blog.csdnimg.cn/8be51f661cf84d0d87d4e59f823eb95b.png)
sign:返回参数的符号
返回参数的符号x的值为负数,零和正数时分别返回-1,0,1
select sign(-6),sign(0),sign(34);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Ef06bKCn-1679467513333)(MySQL函数.assets/image-20220923160127912.png)]](https://img-blog.csdnimg.cn/dc2be764c8be4a3a90db0db3857f5b43.png)
pow和 power:次方函数
函数用于计算x的y次方
select pow(5,-2),pow(10,3),power(4,3),pow(100,0),power(6,-3);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3bobDcKJ-1679467513333)(MySQL函数.assets/image-20220923160615569.png)]](https://img-blog.csdnimg.cn/56ec1bff4a3b44c398cffc1abf72974e.png)
sin:正弦函数
sin(x)返回x的正弦值,x值为弧度值
select sin(1),sin(0.5*pi()),pi();
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JFaTisfi-1679467513333)(MySQL函数.assets/image-20220923160946225.png)]](https://img-blog.csdnimg.cn/2aca44a8e67f437499beacd464200286.png)
asin:求反正弦值,
select asin(30),asin(25);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-vo35IU5G-1679467513334)(MySQL函数.assets/image-20220923161226836.png)]](https://img-blog.csdnimg.cn/13d5ca2689ee450fab546efaf26f4378.png)
MySQL字符串函数
函数名称作用length计算字符串长度函数,concat字符串拼接insert替换字符串函数lower将字符串中的字母转换为小写upper将字符串中的字母转换为大写left从左侧截取字符串,返回字符串左边的若干个字符right从右侧截取字符串,返回字符串右边的若干个字符trim删除字符串左右两侧的空格replace字符串替换函数,返回替换后的新字符串substr和substring截取字符串reverse字符串反转
length:返回字符串长度
返回值为字符串的字节长度,unicode码为utf8,为万国码,变长字符出,一个汉字占3个字节,一个数据或者字母占一个字节
select length('helloworld');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-crBFXETZ-1679467513334)(MySQL函数.assets/image-20220923162533019.png)]](https://img-blog.csdnimg.cn/53e1b1da08fc4fa8b11b5ea3aadad61a.png)
concat:字符串拼接
select concat('张三','java'),concat('李','四','java');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HAMJhzOA-1679467513334)(MySQL函数.assets/image-20220923162852991.png)]](https://img-blog.csdnimg.cn/3d1ff14407124ff885fe48c3670d42e5.png)
insert:替换字符串
insert(s1,x,len,s2)返回字符串s1,子字符串起始于x位置,并且用len个字符长的字符串代替为s2
select insert('java',2,4,'**')As col1,
insert('shell',-1,4,'**')as col2,
insert('张三学习java',3,20,'**')as col3;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZNzgzUGh-1679467513335)(MySQL函数.assets/image-20220923162940629.png)]](https://img-blog.csdnimg.cn/c038cb10be254489be2ce4d242c50dfa.png)
lower:将字母转换为小写
lower(str)可以将字符串str中的字母全部转换为小写
select lower('张三JAVAZHANGSAN');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-g2jAJjHu-1679467513335)(MySQL函数.assets/image-20220923163402463.png)]](https://img-blog.csdnimg.cn/d8ccbd6973914c7a8a61366d43199d36.png)
upper:将字母转换为大写
upper(str)可以将字符串str中的字母字符全部转换为大写
select upper('张三javazhangsan');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GXqHz2t6-1679467513335)(MySQL函数.assets/image-20220923163517990.png)]](https://img-blog.csdnimg.cn/4a8f5a9d44f34f0fb7989534dfe0042c.png)
left:从左侧截取字符串
select left('张三java',2),left('李四java',4),left('王五',-1);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MPSsvQaq-1679467513336)(MySQL函数.assets/image-20220923170220693.png)]](https://img-blog.csdnimg.cn/9b6cb41dd24f4d469322b2fe29d3cba4.png)
right:从右侧截取字符串
select right('张三java',2),right('李四java',4),right('王五',-1);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EF3QRS51-1679467513336)(MySQL函数.assets/image-20220923170301064.png)]](https://img-blog.csdnimg.cn/e5f28cd1a7674fa5b4e4050ade1b133e.png)
trim:删除字符串两侧空格
select ' [ zhagnsan ] ',concat('[ ,李四 ]'),trim( ' [ zhagnsan ] ');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-M2Lr1Bk9-1679467513336)(MySQL函数.assets/image-20220923170532433.png)]](https://img-blog.csdnimg.cn/1dc192556da04dca8b83758d4107b692.png)
substr和substring截取字符串
select substr('javaScipt',3)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OQCbcxVj-1679467513337)(MySQL函数.assets/image-20220923170710775.png)]](https://img-blog.csdnimg.cn/a250ef3b746e4fb0a78ae2d029cbddec.png)
select substr('javaScipt',3,2)
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dOWhcNu5-1679467513337)(MySQL函数.assets/image-20220923170834961.png)]](https://img-blog.csdnimg.cn/033cc07ba10f4b4db29aaa4282b3b7c2.png)
reverse:反转字符串
select reverse('张三java')
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-I3CfpmVg-1679467513337)(MySQL函数.assets/image-20220923170940648.png)]](https://img-blog.csdnimg.cn/10a3ec011792433b9498a3a28fa041d2.png)
MySQL日期和时间函数
函数名称作 用curdate 和 current_date两个函数作用相同,返回当前系统的日期值curtime 和 current_time两个函数作用相同,返回当前系统的时间值now 和 sysdate两个函数作用相同,返回当前系统的日期和时间值unix_timestamp获取UNIX时间戳函数,返回一个以 UNIX 时间戳为基础的无符号整数from_unixtime将 UNIX 时间戳转换为时间格式,与UNIX_TIMESTAMP互为反函数month获取指定日期中的月份monthname获取指定日期中的月份英文名称dayname获取指定曰期对应的星期几的英文名称dayofweek获取指定日期是一周中是第几天,返回值范围是1~7,1=周日week获取指定日期是一年中的第几周,返回值的范围是否为 0〜52 或 1〜53dayofyear获取指定曰期是一年中的第几天,返回值范围是1~366dayofmonth获取指定日期是一个月中是第几天,返回值范围是1~31year获取年份,返回值范围是 1970〜2069time_to_sec将时间参数转换为秒数sec_to_time将秒数转换为时间,与TIME_TO_SEC 互为反函数date_add 和 adddate两个函数功能相同,都是向日期添加指定的时间间隔date_sub 和 subdate两个函数功能相同,都是向日期减去指定的时间间隔addtime时间加法运算,在原始时间上添加指定的时间subtime时间减法运算,在原始时间上减去指定的时间datediff获取两个日期之间间隔,返回参数 1 减去参数 2 的值date_format格式化指定的日期,根据参数返回指定格式的值weekday获取指定日期在一周内的对应的工作日索引
curdate 和 current_date:返回当前系统的日期值
select curdate(),current_date,current_date+1;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Dbsbtx9q-1679467513338)(MySQL函数.assets/image-20220924105200282.png)]](https://img-blog.csdnimg.cn/daa3eb69907143c496a8f2afa93f2439.png)
curtime 和 current_time:返回系统当前时间
select curtime(),current_time,current_time+1;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rwkmulGK-1679467513338)(MySQL函数.assets/image-20220924105408355.png)]](https://img-blog.csdnimg.cn/f6a4febf67a44bc7817be8d553703087.png)
now 和 sysdate:获取当前时间日期
select now(),sysdate();
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-1wRCiEeL-1679467513339)(MySQL函数.assets/image-20220924105509084.png)]](https://img-blog.csdnimg.cn/c6f4a1bf8a874eb48a1f366a77da9824.png)
unix_timestamp:获取unix时间戳
select unix_timestamp();
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-G0BDxryc-1679467513339)(MySQL函数.assets/image-20220924105713044.png)]](https://img-blog.csdnimg.cn/7464f888d481456e935e361ec11066eb.png)
from_unixtime:时间戳转日期
格式说明%M月名字%W星期名字%D有英语前缀的月份和日期%Y年,数字,4位%y年,数字,4位%d月份的天数,(00-31)%e月份的天数,(0-31)%a缩写的星期名字%m月,数字(01-12)%c月,数字(1-12)%b缩写的月份名字(Jan-Dec)%j一年中的天数(001-366)%H小时(00-23)%h小时(01-12)%l(L的小写)小时(1-12)%k小时(0-23)%I(i的大写)小时(01-12)%i分钟,数字(00-59)%S秒(00-59)%s秒(00-59)%r时间,12小时(hh:mm:ss [AP]M)%T时间,24小时(hh:mm:ss)%pAM或者PM%U星期(0-52)星期天是星期第一天%u星期(0-52)星期一是星期的第一天%%输出%
select from_unixtime(1663989244),from_unixtime(1663989244,'%Y-%m-%d %H:%i:%s');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xkUjD8hb-1679467513339)(MySQL函数.assets/image-20220924111426674.png)]](https://img-blog.csdnimg.cn/43e387e247c74b07b64bd7b4c790de7f.png)
month:指定日期的月份
select month('2022-9-24'),month(now());
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U3TRACNd-1679467513340)(MySQL函数.assets/image-20220924111654916.png)]](https://img-blog.csdnimg.cn/d573e825097644af8c781b44974087ce.png)
monthname:获取指定日期月份的英文名称
select monthname('2022-9-24'),monthname(now());
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-qCREpM7U-1679467513340)(MySQL函数.assets/image-20220924111806380.png)]](https://img-blog.csdnimg.cn/9ee613fec7a04985a2d5dbeb098b51e9.png)
dayname:获取指定日期的星期名称
select now(),dayname(now());
![ [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-chqPAnaN-1679467513340)(MySQL函数.assets/image-20220924112006619.png)]](https://img-blog.csdnimg.cn/572c4ee1352c41b49958864595dd5722.png)
dayofweek:获取日期对应的周索引
1表示周天,2表示周一,。。。。
select now(),dayofweek('2022-9-25');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pobri3KN-1679467513340)(MySQL函数.assets/image-20220924112405251.png)]](https://img-blog.csdnimg.cn/78c9651cd92948f69bdb1c85be479a24.png)
week:获取指定日期是一年中的第几周
模式一周的第一天范围0星期日0-531星期一0-532星期日1-533星期一1-534星期日0-535星期一0-536星期日1-537星期一1-53
select now(),week('2022-12-31');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zO7lwgEM-1679467513341)(MySQL函数.assets/image-20220924112816292.png)]](https://img-blog.csdnimg.cn/de1e447f024144d69780010a870ae23b.png)
dayofyear:获取指定曰期是一年中的位置
select now(),dayofyear(now()),dayofyear('2020-12-31');
![在这里插入图片描述](https://img-blog.csdnimg.cn/b8baa9a70ebf412bbb1b745a040a9efb.png)
dayofmonth:获取指定日期在一个月的位置
select now(),dayofmonth(now()),dayofmonth('2020-12-31');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-2z8![在这里插入图片描述](https://img-blog.csdnimg.cn/c98cd62962994527af347ec3125c1417.png)
year:获取年份
select now(),year(now()),year('2020-12-31');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WhXYMF0F-1679467513341)(MySQL函数.assets/image-20220924113730723.png)]](https://img-blog.csdnimg.cn/6dfe81cc394b4f66807bc7f9e80f2502.png)
time_to_sec:将时间转换位秒值
select time_to_sec('5:5:5'),now(),time_to_sec(now());
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ik4uaRM8-1679467513342)(MySQL函数.assets/image-20220924114042818.png)]](https://img-blog.csdnimg.cn/99456373261149f9ac6a8c3457b63f3e.png)
sec_to_time:将秒转换为时间格式
select sec_to_time('18305'),now(),sec_to_time('42004');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lHefYe6U-1679467513342)(MySQL函数.assets/image-20220924114157252.png)]](https://img-blog.csdnimg.cn/366b8c726d4b4d3ea8259624af9fed95.png)
date_add 和 adddate:向日期添加指定时间间隔
select date_add('2022-9-21',interval 365 day );
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7XwurT3l-1679467513342)(MySQL函数.assets/image-20220924114514683.png)]](https://img-blog.csdnimg.cn/7a33e876f32247e38c1561ebff21fd99.png)
date_sub 和 subdate:日期减法运算
select date_sub('2022-9-21',interval 365 day );
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-umN6Mb6R-1679467513342)(MySQL函数.assets/image-20220924114627260.png)]](https://img-blog.csdnimg.cn/04a4571e7bc74c9a83ad1e0e1bde67f0.png)
addtime:时间加法运算
select addtime('2022-9-24 11:47:36','0:1:1');
![v!](https://img-blog.csdnimg.cn/174e5787379642459671a0bfc40bda60.png)](https://img-blog.csdnimg.cn/4cff663a018749c3a764d03c935d257e.png)
subtime:时间减法运算
select subtime('2022-9-24 11:47:36','0:1:1');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3edAVuf0-1679467513343)(MySQL函数.assets/image-20220924114926291.png)]](https://img-blog.csdnimg.cn/a881b41abb224c37bf9daf7c87111bcc.png)
datediff:获取两个时间的时间间隔
select datediff('2022-9-24','2019-12-26');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-p2Dfwj5c-1679467513343)(MySQL函数.assets/image-20220924115150427.png)]](https://img-blog.csdnimg.cn/e9ac274930894efe8a67986a829efffc.png)
date_format:格式化指定的日期
select date_format('2022-9-24','%Y年%m月%d日');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XXrU6K6M-1679467513343)(MySQL函数.assets/image-20220924115432611.png)]](https://img-blog.csdnimg.cn/774f61bbb70843cdafaa2cf294e1fe21.png)
weekday:获取指定日期在一周内的索引位置
select now(),weekday(now());
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-YyC9ZKjQ-1679467513344)(MySQL函数.assets/image-20220924115559965.png)]](https://img-blog.csdnimg.cn/47b57012306f424f98a096823f6132f1.png)
MySQL聚合函数
函数名称作用max查询指定列的最大值min查询指定列的最小值count统计查询结果的行数sum求和,返回指定列的总和avg求平均值,返回指定列数据的平均值
MySQL流程控制函数
函数名称作用if判断,流程控制ifnull判断是否为空case搜索语句
if:判断
if(expr,v1,v2)
当expr为真是返回v1的值,为假返回v2的值
select if(15,'√','×');
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UyQyviNP-1679467513344)(MySQL函数.assets/image-20220926103001342.png)]](https://img-blog.csdnimg.cn/63d97fbc42fa4fcaa54176ad0abdb49a.png)
ifnull:判断是否为空
ifnull(v1,v2):当v1为空则放回v2,当v1有值则放回v1
select ifnull('非空','为空'),ifnull(null,'空'),ifnull('空',null),ifnull(null,null);
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0PpenBrs-1679467513344)(MySQL函数.assets/image-20220926103358296.png)]](https://img-blog.csdnimg.cn/e4fa6c7b04ee432291e21fee2328f206.png)
case:搜索语句
方式1:
case
when then
。。。
else
end case;
方式2:
case
when then
。。。
else commands
end case;
create table t_stu(
id int auto_increment comment '学号',
name varchar(20) comment '姓名',
sex tinyint comment '性别,0:未知,1:男,2:女',
primary key (id)
)charset = utf8mb4 collate = utf8mb4_bin engine = InnoDB;
insert into t_stu(name,sex) values ('张三',1),
('李四',2),
('王五',1);
insert into t_stu(name, sex) values ('赵六',3),('唐七',0);
select id, name, sex from t_stu;
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SujGqAly-1679467513345)(MySQL函数.assets/image-20220926105155200.png)]](https://img-blog.csdnimg.cn/ac4f206bc9f44df4a9bb3a94da168e68.png)
需求:查询所有学生信息,输出姓名,性别(男,女,未知)
select name '名字', (case sex
when 1
then '男'
when 2
then '女'
else '未知' end) '性别' from t_stu;
其他函数
函数名称作用version数据库版本号database当前数据库user当前连接用户名password返回字符串密码形式md5返回字符串的md5数据
select version();
select database();
select user();
select password('123456');
select md5('zhangsan123456lisiwangwu123456')
|