投稿
  1. 极客之音首页
  2. 后端笔记

【MySQL】单行函数

飞熊 • • 后端笔记 • 阅读 95

生活中，最使人疲惫的往往不是道路的遥远，而是心中的郁闷;最使人痛苦的往往不是生活的不幸，而是希望的破灭;最使人颓废的往往不是前途的坎坷，而是自信的丧失;最使人绝望的往往不是挫折的打击，而是心灵的死亡。所以我们要有自己的梦想，让梦想的星光指引着我们走出落漠，走出惆怅，带着我们走进自己的理想。

导读：本篇文章讲解 【MySQL】单行函数，希望对大家有帮助，欢迎收藏，转发！站点地址：www.bmabk.com，来源：原文

一、基本概念

1、函数的作用
函数可以把我们经常使用的代码封装起来，需要时直接调用即可
这样既提高了代码效率，又提高了代码可维护性
在SQL中我们也可以使用函数对检索出来的数据进行操作，极大提高用户对数据库的管理效率

2、函数的分类
分为内置函数和自定义函数

3、MySQL内置函数的分类
从功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密和解密函数、获取MySQL信息函数、聚合函数等

也可以只分为单行函数和聚合函数（分组函数）

  • 单行函数：作用于一行，返回一个值
    • 可以嵌套
    • 参数可以是一列或一个值
    • 每行返回一个结果
  • 聚合函数：指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，例如输入一组数据求和、求平均值等

二、单行函数

1、数值函数

1）基本函数

  • ABS(x)：取绝对值
  • SIGN(x)：判断正负，正数返回1，负数返回-1
  • PI()：圆周率
  • CEIL(x)：向上取整
  • FLOOR(x)：向下取整
  • MOD(x,y)：取余
  • LEAST(a1,a2,…)：返回最小值
  • GREAST(a1,a2,…)：返回最大值
  • RAND()：返回0-1随机数，每次返回的不相同
  • RAND(x)：返回0-1随机数，x相同时返回的值是一样的
  • ROUND(x,y)：四舍五入，保留小数点后y位，负数就向前进行四舍五入
  • TRUNCATE(x,y)：截断数值，保留小数点后y位
  • SQRT(x)：开方
SELECT ABS(-123),ABS(32),SIGN(-23),SIGN(32),PI() FROM DUAL;
SELECT CEIL(-32.2),CEILING(32.2),FLOOR(-32.2),FLOOR(32.2),MOD(12,5) FROM DUAL;
SELECT LEAST(1,7,3),GREATEST(1,7,3) FROM DUAL;
SELECT RAND(),RAND(),RAND(10),RAND(10) FROM DUAL;
SELECT ROUND(12.256,0),ROUND(12.256,2),ROUND(12.256,-1) FROM DUAL;
SELECT TRUNCATE(12.256,0),TRUNCATE(12.256,-1),TRUNCATE(12.256,2) FROM DUAL;
SELECT SQRT(4) FROM DUAL;
SELECT TRUNCATE(ROUND(12.2345,3),2); # 单行函数可以嵌套

运行结果
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

2）角度与弧度互换函数以及三角函数

包括正弦、余弦、正切等函数，因为博主在工作中遇到的场景不多，所以这里就简单了解一下就行
在这里插入图片描述
在这里插入图片描述

3）指数与对数函数

  • POWER(x,y)：返回x的y次方
  • EXP(x)：返回e的x次方，这里e是一个常数2.718281828459
  • LN(x),LOG(x)：返回以e为底的对数，当x<=0时，返回null
  • LOG10(x)：返回以10为底的对数，当x<=0时，返回null
  • LOG2(x)：返回以2为底的对数，当x<=0时，返回null
SELECT POWER(2,2),POWER(2,-2),EXP(1),
LN(2.718281828459045),LOG(2.718281828459045),
LOG10(10),LOG2(2) FROM DUAL;

结果为
在这里插入图片描述

4）进制转换

  • BIN(x)：返回x的二进制编码
  • HEX()：返回x的十六进制编码
  • OCT()：返回x的八进制编码
  • CONV(x,f1,f2)：将x从f1进制转换为f2进制
SELECT BIN(10),HEX(12),OCT(8),CONV(10,2,8) FROM DUAL;

结果为
在这里插入图片描述

2、字符串函数

注意：MySQL中字符串的下标都是从1开始的

  • ASCII(S)：返回字符串S中的第一个字符的ASCII码值
  • CHAR_LENGTH(s)：返回字符串s的字符数。作用与CHARACTER_LENGTH(s)相同
  • LENGTH(s)：返回字符串s的字节数，和字符集有关
  • CONCAT(s1,s2,…,sn)：连接s1,s2,…,sn为一个字符串
  • CONCAT_WS(x,s1,s2,…,sn)：同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x
  • INSERT(str, idx, len,replacestr)：将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
  • REPLACE(str, a, b)：用字符串b替换字符串str中所有出现的字符串a
  • UPPER(s) 或 UCASE(s)：将字符串s的所有字母转成大写字母
  • LOWER(s) 或LCASE(s)：将字符串s的所有字母转成小写字母
  • LEFT(str,n)：返回字符串str最左边的n个字符
  • RIGHT(str,n)：返回字符串str最右边的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(s1 FROM s)：去掉字符串s开始与结尾的s1
  • TRIM(LEADING s1 FROM s)：去掉字符串s开始处的s1
  • TRIM(TRAILING s1 FROM s)：去掉字符串s结尾处的s1
  • REPEAT(str, n)：返回str重复n次的结果
  • SPACE(n)：返回n个空格
  • STRCMP(s1,s2)：比较字符串s1,s2的ASCII码值的大小，如果后面的大就返回-1，反之返回1
  • SUBSTR(s,index,len)：返回从字符串s的index位置其len个字符，作用与SUBSTRING(s,n,len)、MID(s,n,len)相同
  • LOCATE(substr,str)：返回字符串substr在字符串str中首次出现的位置，作用于POSITION(substr IN str)、INSTR(str,substr)相同。未找到，返回0
  • ELT(m,s1,s2,…,sn)：返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
  • FIELD(s,s1,s2,…,sn)：返回字符串s在字符串列表中第一次出现的位置，找不到就返回0
  • FIND_IN_SET(s1,s2)：返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串，找不到就返回0
  • REVERSE(s)：返回s反转后的字符串
  • NULLIF(value1,value2)：比较两个字符串，如果value1与value2相等，则返回NULL，否则返回 value1
SELECT ASCII('a'),CHAR_LENGTH('hello'),CHAR_LENGTH('你好'),LENGTH('hello'),LENGTH('你好')
FROM DUAL;

SELECT CONCAT('hello','world'),CONCAT_WS('-','hello','world','LOL'),
INSERT('hello',2,2,'aaaa') FROM DUAL;

SELECT REPLACE('helloworld','l','s'),UPPER('aa'),LOWER('BB'),
LEFT('hello',2),RIGHT('world',2) FROM DUAL;

SELECT LPAD('hello',8,'*'),RPAD('world',8,'---'),LENGTH(LTRIM('   hello   ')) FROM DUAL;

SELECT LENGTH(RTRIM('   hello   ')),LENGTH(TRIM('   hello   ')),TRIM('o' FROM 'ollo'),
TRIM(LEADING 'o' FROM 'oolo'),TRIM(TRAILING 'o' FROM 'laoo')
FROM DUAL;

SELECT REPEAT('asa',3),CONCAT('hello',SPACE(3),'world'),
STRCMP('abc','abe'),STRCMP('abc','aba') FROM DUAL;

SELECT SUBSTR('world',2,3),LOCATE('worldaaa','a'),ELT(2,'aa','bb','cc','dd'),
FIELD('aa','sd','wqas'),FIELD('aa','sd','aa'),
FIND_IN_SET('a','sd,as'),FIND_IN_SET('as','sd,as') FROM DUAL;

SELECT REVERSE('asdf'),NULLIF('aa','bb')
FROM DUAL;

SQL执行结果如下
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

3、日期时间函数

下面是一些常用的日期时间函数

# 获取日期、时间
SELECT CURRENT_TIME(),CURRENT_DATE(),CURDATE(),now() FROM DUAL;

# 日期与时间戳的转换
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-12-12 12:00:00'),FROM_UNIXTIME(1679487187),FROM_UNIXTIME(1670817600) FROM DUAL;

# 获取年、月份、天数
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()) FROM DUAL;
# 获取星期几、周几(周一是0)、季度、第几周
SELECT DAYNAME(CURDATE()),WEEKDAY(CURDATE()),QUARTER(CURDATE()),WEEKOFYEAR(CURDATE()) FROM DUAL;
# 获取每年的第几天、每月的第几天、每周的第几天(周日是1)
SELECT DAYOFYEAR(CURDATE()),DAYOFMONTH(CURDATE()),DAYOFWEEK(CURDATE()) FROM DUAL;

# 获取距离目标时间的天数，或者与某个时间点的时间间隔
SELECT DATEDIFF(NOW(),'2022-01-01'),TIMEDIFF(NOW(),'2021-12-21 12:00:00') FROM DUAL;

# 日期的格式化与解析
# 格式化：日期-->字符串 DATE_FORMAT(date,fmt)和TIME_FORMAT(date,fmt),fmt表示要格式化成什么样的格式
SELECT DATE_FORMAT(CURDATE(),'%Y-%m-%d'),DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%S'),TIME_FORMAT(NOW(),'%H:%i:%S') FROM DUAL;
# 解析： 字符串-->日期,STR_TO_DATE(str,fmt)，按照ftm格式对字符串str进行逆向转换
SELECT STR_TO_DATE('2023-03-22','%Y-%m-%d %H:%i:%S');
# 获取想要的格式，GET_FORMAT(date_type,format_type)，比如想获取满足IOS标准的日期，data_type就是DATE，format_type就是ISO
SELECT GET_FORMAT(DATE, 'ISO'),DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'ISO')) FROM DUAL;

执行结果如下
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

4、流程控制函数

流程控制函数一般分为顺序结构、分支结构和循环结构
其中代码从上往下执行就是顺序结构，SQL按行处理数据就是循环遍历

下面我们介绍一下分支结构

  • IF(value,value1,value2)：如果value判断为true，则返回value1，否则返回value2
  • IFNULL(value1,value2)：如果value1不为null，则返回value1，否则就返回value2
  • CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2...ELSE 结果n END：相当于Java语言中的if…else if…else。注意这里和Java一样，最后兜底的那个ELSE可以不要，那些没有被兜底的字段数就显示null
  • CASE 表达式expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2...ELSE 值n END：相当于Java语言中的switch…case…，同上，最后兜底的那个ELSE可以不要，那些没有被兜底的字段数就显示null

我们先创建一个blog表，插入一些数据以便使用

DROP TABLE IF EXISTS `t_decade_blog`;
CREATE TABLE `t_decade_blog`  (
  `id` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客id',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客名称',
  `author` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '博客作者',
  `create_time` datetime(0) NOT NULL COMMENT '创建时间',
  `views` int(20) NOT NULL COMMENT '博客浏览量'
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

INSERT INTO `t_decade_blog` VALUES ('76782763-48d0-4cef-b8e1-1054e181e41d', 'Mybatis系列', 'Decade0712', '2022-03-28 22:30:14', 2000);
INSERT INTO `t_decade_blog` VALUES ('06bd6c69-e47e-4e78-9e02-5110a013e457', 'Spring系列', 'Decade0712', '2022-03-28 22:30:14', 4000);
INSERT INTO `t_decade_blog` VALUES ('d3258b79-d543-49bb-9850-16cac7565f57', '设计模式系列', 'Decade0712', '2022-03-28 22:30:14', 6000);

数据结果如下
在这里插入图片描述

然后我们使用上方的流程函数做一些查询操作

SELECT name,views,IF(views>2000,'浏览量不足2000','浏览量让人满意') result
FROM t_decade_blog;

SELECT name,views,IFNULL('有人浏览','没人浏览') result
FROM t_decade_blog;

SELECT name,views,CASE WHEN views=2000 THEN'初级浏览量'
	WHEN views=4000 THEN '中级浏览量'
	ELSE '高级浏览量或者没人浏览'
END result
FROM t_decade_blog;

SELECT name,views,CASE WHEN views=2000 THEN'初级浏览量'
	WHEN views=4000 THEN '中级浏览量'
END result
FROM t_decade_blog;

SELECT name,views,CASE views
	WHEN 2000 THEN '初级浏览量'
	WHEN 4000 THEN '中级浏览量'
	ELSE '高级浏览量或者没人浏览'
END result
FROM t_decade_blog;

SELECT name,views,CASE views
	WHEN 2000 THEN '初级浏览量'
	WHEN 4000 THEN '中级浏览量'
END result
FROM t_decade_blog;

执行结果如下
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

5、加密与解密函数

加密结果不可逆如何比较？
直接比较加密后的参数，相同的明文加密得到的结果是相同的

  • PASSWORD(str)：返回字符串的加密版本，加密结果不可逆，但是在mysql8.0中废弃了
  • MD5(str)：加密不可逆，返回字符串str的MD5加密之后的值，若参数是null，返回的结果也是null
  • SHA(str)：加密不可逆，比MD5加密更安全，若参数是null，返回的结果也是null
  • ENCODE(value,password_seed)：返回使用password_seed作为加密密码加密value，在mysql8.0中废弃了
  • DECODE(value,password_seed)：返回使用password_seed作为加密密码解密value，在mysql8.0中废弃了
SELECT PASSWORD('mysql'),MD5('mysql'),MD5('mysql'),SHA('mysql') FROM DUAL;

SELECT DECODE(ENCODE(123,'mysql'),'mysql'),ENCODE(123,'mysql') FROM DUAL;

执行结果如下
在这里插入图片描述
在这里插入图片描述

6、MySQL信息函数

  • VERSION()：当前MySQL版本号
  • CONNECTION_ID()：返回当前MySQL服务器连接数
  • DATABASE()或者SCHEMA()：返回当前所在数据库
  • USER()：返回当前连接用户
  • CHARSET(str)：返回字符串str自变量的字符集
  • COLLATION(str)：返回字符串str的比较规则
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CHARSET('十年'),COLLATION('十年') FROM DUAL;

执行结果如下
在这里插入图片描述

7、其他函数

  • FORMAT(value,num)：将value进行四舍五入，保留到小数点后num位，如果是0或者负数，就向前取整
  • CONV(value,from,to)：将value进行位转换，从from位转换成to位
  • INET_ATON(str)：将IP地址转换成整数
  • INET_NTOA(str)：将整数转换回IP
  • BENCHMARK(times,expr)：将表达式expr重复执行times次，查看执行耗时
  • CONVERT(str USING transcoding_name)：将str所使用的字符编码修改为transcoding_name格式
SELECT FORMAT(123.356,2),CONV(100,10,2),INET_ATON('192.168.1.1'),INET_NTOA('3232235777') FROM DUAL;

SELECT BENCHMARK(800000,SHA('mysql')) FROM DUAL;

SELECT CHARSET('十年'),CHARSET(CONVERT('十年' USING 'utf8mb3')),CONVERT('十年' USING 'utf8mb4') FROM DUAL;

执行结果如下
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

如有错误，欢迎指正！！！

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/136724.html

赞 (0)
飞熊的头像飞熊bm
0 0

相关推荐

发表回复

登录后才能评论
极客之音——专业性很强的中文编程技术网站，欢迎收藏到浏览器，订阅我们！