MySQL基础篇补充 | 单行函数（数值函数、字符串函数、日期函数、流程控制函数、加密与解密函数、MySQL信息函数）

目录

一：单行函数

 1. 数值函数

（1）基本函数

（2）角度与弧度互换函数

（3）三角函数

（4）指数与对数

（5）进制间的转换

2. 字符串函数

3. 日期和时间函数

（1）获取日期、时间

（2）日期与时间戳的转换

（3） 获取月份、星期、星期数、天数等函数

（4）日期的操作函数

（5）时间和秒钟转换的函数

（6）计算日期和时间的函数 

（7）日期的格式化与解析

4. 流程控制函数

5. 加密与解密函数

6. MySQL信息函数

7. 其他函数

一：单行函数

        我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是 被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很 差的，因此在使用函数的时候需要特别注意。

        MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。

两种SQL函数

单行函数

①操作数据对象； 

②接受参数返回一个结果；

③只对一行进行变换； 

④每行返回一个结果；

⑤可以嵌套；

⑥参数可以是一列或一个值；

 1. 数值函数

（1）基本函数

①第一组

测试：

SELECT 
ABS(-123),ABS(32),SIGN(-23),SIGN(43),PI(),CEIL(32.32),CEILING(-43.23),FLOOR(32.32),FLOOR(-43.23),MOD(12,5)
FROM DUAL;

执行结果：

②第二组 

测试： 

SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;

执行结果：

③第三组

测试： 

SELECT 
ROUND(12.33),ROUND(12.343,2),ROUND(12.324,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1),SQRT(2)
FROM DUAL;

执行结果： 

（2）角度与弧度互换函数

 测试：

SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90))
FROM DUAL;

执行结果： 

（3）三角函数

测试： 

SELECT 
SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1))
FROM DUAL;

执行结果：

（4）指数与对数

测试：

SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4)
FROM DUAL;

执行结果： 

（5）进制间的转换

测试：

SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;

执行结果：

2. 字符串函数

注意：MySQL中，字符串的位置是从1开始的。

①第一组

测试：ASCII、CHAR_LENGTH、LENGTH

SELECT ASCII('Abcdfsf'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')
FROM DUAL;

执行结果：

测试：CONCAT、CONCAT_WS

SELECT CONCAT(e1.ename,' worked for ',e2.ename)
FROM emp e1
JOIN emp e2
ON e1.mgr = e2.empno;

SELECT CONCAT_WS('-','hello','world','hello','beijing')
FROM DUAL;

执行结果：

测试：INSERT、REPLACE

SELECT INSERT('helloworld',2,3,'aaaaa'),REPLACE('hello','ll','mm')
FROM DUAL;

执行结果：

测试： UPPER、LOWER

SELECT UPPER('HelLo'),LOWER('HelLo')
FROM DUAL;

执行结果：

测试：LEFT、RIGHT

SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;

执行结果：

测试：LPAD（实现左对齐）、RPAD（实现右对齐）；小数点也算一位

SELECT ename,LPAD(sal,8,'*')
FROM emp;

执行结果：

②第二组

测试：LTRIM、RTRIM

select concat('--->',LTrim('  123   '),'<---'),concat('--->',RTrim('  123   '),'<---')
from dual;

执行结果：

测试：TRIM

SELECT CONCAT('--->',TRIM('  123   '),'<---')
FROM DUAL;

SELECT CONCAT('--->',TRIM( 'a' FROM 'aabbcca'),'<---')
FROM DUAL;

SELECT CONCAT('--->',TRIM(LEADING 'a' FROM 'aabbcca'),'<---'),CONCAT('--->',TRIM(TRAILING 'a' FROM 'aabbcca'),'<---')
FROM DUAL;

 执行结果：

③第三组

测试：REPEAT、SPACE、STRCMP

SELECT REPEAT('hello',4),CONCAT(1,SPACE(5),2),STRCMP('abc','abe')
FROM DUAL;

执行结果：

测试：SUBSTR、LOCATE

SELECT SUBSTR('hello',2,2),LOCATE('ll','hello')
FROM DUAL;

执行结果：

测试：ELT、FIFLD、FIND_IN_SET

SELECT ELT(2,'a','b','c','d'),FIELD('mm','gg','jj','mm','dd','mm'),
FIND_IN_SET('mm','gg,mm,jj,dd,mm,gg')
FROM DUAL;

执行结果：

测试：REVERSE、NULLIF

SELECT REVERSE("abcd") 
FROM DUAL;

SELECT empno,NULLIF(LENGTH(ename),LENGTH(job)) 'compare'
FROM emp;

执行结果： 

3. 日期和时间函数

（1）获取日期、时间

测试：

select curdate(),curTime(),now(),utc_date(),utc_time()
from dual;

执行结果：

（2）日期与时间戳的转换

测试： 

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('1999-01-01'),FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()))
FROM DUAL;

执行结果：

（3） 获取月份、星期、星期数、天数等函数

测试： 

SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())
FROM DUAL;

执行结果： 

测试： 

SELECT MONTHNAME('2021-10-26'),DAYNAME('2021-10-26'),WEEKDAY('2021-10-26'),
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())
FROM DUAL;

执行结果： 

（4）日期的操作函数

XTRACT(type FROM date)函数中type的取值与含义：

测试： 

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

执行结果： 

（5）时间和秒钟转换的函数

测试：

select time_to_sec(now()),sec_to_time(TIME_TO_SEC(NOW()))
from dual;

执行结果：

（6）计算日期和时间的函数 

第1组：

 以下两个函数记住一个就行，使用DATE_ADD的INTERVAL值设置为负数就能达到DATE_SUB的效果！

上述函数中type的取值：

测试：

SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2021-10-21 23:32:12',INTERVAL 
1 SECOND) AS col2,
ADDDATE('2021-10-21 23:32:12',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2021-10-21 23:32:12',INTERVAL '1_1' MINUTE_SECOND) AS col4,
DATE_ADD(NOW(), INTERVAL -1 YEAR) AS col5, #可以是负数
DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH) AS col6 #需要单引号
FROM DUAL;

执行结果：

第2组：

测试：

SELECT ADDTIME(NOW(),20),SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),DATEDIFF(NOW(),'2021-10-01'),
TIMEDIFF(NOW(),'2021-10-25 22:10:10'),FROM_DAYS(366),TO_DAYS('0000-12-25'),
LAST_DAY(NOW()),MAKEDATE(YEAR(NOW()),12),MAKETIME(10,21,23),PERIOD_ADD(20200101010101,10)
FROM DUAL;

执行结果：

（7）日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数（相当于DATE_FORMAT的提升版）中date_type和format_type参数取值如下：

格式化：日期 ---> 字符串

SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),
DATE_FORMAT(NOW(),'%Y-%m-%d'),TIME_FORMAT(CURTIME(),'%h:%i:%S'),
DATE_FORMAT(NOW(),'%Y-%M-%D %h:%i:%S %W %w %T %r')
FROM DUAL;

执行结果：

解析：字符串 ----> 日期

SELECT STR_TO_DATE('2021-October-25th 11:37:30 Monday 1','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;

执行结果：

测试：ET_FORMAT、DATE_FORMAT

SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;

 执行结果：

4. 流程控制函数

流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

测试：if

# 判断工资
SELECT ename,IF(sal >= 3000,'高工资','低工资') "details"
FROM emp;
# comm为null就转换成0
SELECT ename,(sal+IF(comm IS NOT NULL,comm,0))*12 AS yearSal
FROM emp;

执行结果：

测试：ifnull实际上是if的一种特殊情况

# 使用if
SELECT ename,(sal+IF(comm IS NOT NULL,comm,0))*12 AS yearSal
FROM emp;

#使用ifnull
SELECT ename,(sal+IFNULL(comm,0))*12 AS yearSal
FROM emp;

执行结果：

测试：CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END

SELECT empno,ename,sal,CASE WHEN sal >=5000 THEN '老板'
			WHEN sal >=3000 THEN '潜力股'
			WHEN sal >=1000 THEN '小屌丝'
			ELSE '草根' -- 可以没有else
			END AS 'detail'
FROM emp;

执行结果：

测试：CASE.....WHEN ... THEN ...WHEN ... THEN ... ELSE ... END

需求：查询部门号为 10,20, 30 的员工信息, 若部门号为 10, 则打印其工资的 1.1 倍, 20 号部门, 则打印其 工资的 1.2 倍, 30 号部门打印其工资的 1.3 倍数。

SELECT empno,ename,sal,deptno,CASE deptno WHEN 10 THEN sal*1.1
					WHEN 20 THEN sal*1.2
					WHEN 30 THEN sal*1.3
					END AS 'detail'
FROM emp
WHERE deptno IN (10,20,30);

执行结果：

5. 加密与解密函数

加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在 保证数据库安全时非常有用。

注：以上的PASSWORD、MD5、SHA加密方式都是不可逆的，其中PASSWORD在MySQL8中已经被弃用了！

SELECT MD5('zhang'),SHA('zhang')
FROM DUAL;

执行结果：

注：以上的ENCODE的加密方式是可逆的，实际上和DECODE是一对，ENCODE负责加密、DECODE负责解密。但是MySQL8.0也已经弃用了！

# MySQL8已经弃用，第二个参数'mysql'相当于某个加密的种子（或者说加密的方式）
SELECT ENCODE('atguigu','mysql'),DECODE(ENCODE('atguigu','mysql'),'mysql')
FROM DUAL;

6. MySQL信息函数

MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作。

测试：

SELECT VERSION(),CONNECTION_ID(),DATABASE(),
USER(),CHARSET('hello'),COLLATION('hello')
FROM DUAL

执行结果： 

7. 其他函数

MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。

测试：FORMAT，如果n的值小于或者等于0，则只保留整数部分

fromat也是四舍五入的和round作用很相似；但是当是负数时，对于round会继续四舍五入，到那时format不会，当n的值小等于于0时，只保留整数部分！

SELECT FORMAT(123.125,2),FORMAT(123.125,-2),ROUND(123.125,-2)
FROM DUAL;

执行结果：

测试：ONV，实现进制之间的转换

SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;

执行结果：

测试：INET_ATON、INET_NTOA

INET_ATON是将一个IP转换成一个数字，采用的算法是以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。INET_NTOA是将这个数字再转换成IP

SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;

执行结果：

测试：ENCHMARK、用于测试表达式的执行效率

SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;

执行结果：

测试：CONVERT、可以实现字符集的转换

SELECT CHARSET('hello'),CHARSET(CONVERT('hello' USING 'gbk'))
FROM DUAL;

执行结果：

文章来源地址https://www.toymoban.com/news/detail-438422.html

到了这里，关于MySQL基础篇补充 | 单行函数（数值函数、字符串函数、日期函数、流程控制函数、加密与解密函数、MySQL信息函数）的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！