1. 函数的理解
1.1 什么是函数
- 函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。
- 这样既 提高了代码效率 ，又 提高了可维护性 。
- 在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。
- 使用这些函数，可以极大地 提高用户对数据库的管理效率 。
- 从函数定义的角度出发，我们可以将函数分成
内置函数和自定义函数 - 在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的
1.2 不同DBMS函数的差异
- 我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS
- DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异
- 实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。
- 大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意
2. MySQL函数分类
- MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。
- 这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数）
2.1 两种SQL函数
2.2 单行函数
- 操作数据对象
- 接受参数返回一个结果
只对一行进行变换每行返回一个结果- 可以嵌套
- 参数可以是一列或一个值
3. 数值函数
3.1 基本函数
mysql> 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;
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
| ABS(-123) | ABS(32) | SIGN(-23) | SIGN(43) | PI() | CEIL(32.32) | CEILING(-43.23) | FLOOR(32.32) | FLOOR(-43.23) | MOD(12,5) |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
| 123 | 32 | -1 | 1 | 3.141593 | 33 | -43 | 32 | -44 | 2 |
+-----------+---------+-----------+----------+----------+-------------+-----------------+--------------+---------------+-----------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1);
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| RAND() | RAND() | RAND(10) | RAND(10) | RAND(-1) | RAND(-1) |
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
| 0.709063702923305 | 0.5131437885697407 | 0.6570515219653505 | 0.6570515219653505 | 0.9050373219931845 | 0.9050373219931845 |
+-------------------+--------------------+--------------------+--------------------+--------------------+--------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT ROUND(12.33),ROUND(12.343,2),ROUND(12.343,-1),TRUNCATE(12.66,1),TRUNCATE(12.66,-1),SQRT(16), SQRT(-16);
+--------------+-----------------+------------------+-------------------+--------------------+----------+-----------+
| ROUND(12.33) | ROUND(12.343,2) | ROUND(12.343,-1) | TRUNCATE(12.66,1) | TRUNCATE(12.66,-1) | SQRT(16) | SQRT(-16) |
+--------------+-----------------+------------------+-------------------+--------------------+----------+-----------+
| 12 | 12.34 | 10 | 12.6 | 10 | 4 | NULL |
+--------------+-----------------+------------------+-------------------+--------------------+----------+-----------+
1 row in set (0.00 sec)
mysql>
3.2 弧度和角度装换的函数
mysql> SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM dual;
+--------------------+--------------------+--------------------+-----------------+----------------------+
| RADIANS(30) | RADIANS(60) | RADIANS(90) | DEGREES(2*PI()) | DEGREES(RADIANS(90)) |
+--------------------+--------------------+--------------------+-----------------+----------------------+
| 0.5235987755982988 | 1.0471975511965976 | 1.5707963267948966 | 360 | 90 |
+--------------------+--------------------+--------------------+-----------------+----------------------+
1 row in set (0.00 sec)
mysql>
3.3 三角函数
- ATAN2(M,N)函数返回两个参数的反正切值。 与ATAN(X)函数相比，ATAN2(M,N)需要两个参数
- 例如有两个点point(x1,y1)和point(x2,y2)，使用ATAN(X)函数计算反正切值为ATAN((y2-y1)/(x2-x1))，
- 使用ATAN2(M,N)计算反正切值则为ATAN2(y2-y1,x2-x1)。
- 由使用方式可以看出，当x2-x1等于0时，ATAN(X)函数会报错，而ATAN2(M,N)函数则仍然可以计算
mysql> SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1) )FROM DUAL;
+---------------------+------------------+--------------------+------------------+----------------------+
| SIN(RADIANS(30)) | DEGREES(ASIN(1)) | TAN(RADIANS(45)) | DEGREES(ATAN(1)) | DEGREES(ATAN2(1,1) ) |
+---------------------+------------------+--------------------+------------------+----------------------+
| 0.49999999999999994 | 90 | 0.9999999999999999 | 45 | 45 |
+---------------------+------------------+--------------------+------------------+----------------------+
1 row in set (0.00 sec)
mysql>
3.4 幂函数
mysql> SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM dual;
+----------+------------+------------------+-------------------+-----------+---------+
| POW(2,5) | POWER(2,4) | EXP(2) | LN(10) | LOG10(10) | LOG2(4) |
+----------+------------+------------------+-------------------+-----------+---------+
| 32 | 16 | 7.38905609893065 | 2.302585092994046 | 1 | 2 |
+----------+------------+------------------+-------------------+-----------+---------+
1 row in set (0.00 sec)
mysql>
3.5 进制间的转换
mysql> SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM dual;
+---------+---------+---------+--------------+
| BIN(10) | HEX(10) | OCT(10) | CONV(10,2,8) |
+---------+---------+---------+--------------+
| 1010 | A | 12 | 2 |
+---------+---------+---------+--------------+
1 row in set (0.00 sec)
mysql>
4. 字符串函数
mysql> SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM dual;
+----------------------------------+-----------------------------------+
| FIELD('mm','hello','msm','amma') | FIND_IN_SET('mm','hello,mm,amma') |
+----------------------------------+-----------------------------------+
| 0 | 2 |
+----------------------------------+-----------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
+-------------------------+---------------------+
| NULLIF('mysql','mysql') | NULLIF('mysql', '') |
+-------------------------+---------------------+
| NULL | mysql |
+-------------------------+---------------------+
1 row in set (0.00 sec)
mysql>
5. 日期和时间函数
5.1 获取日期、时间
mysql> SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_TIME(),UTC_TIME()+0;
+------------+-----------+---------------------+----------------+------------+------------+--------------+
| CURDATE() | CURTIME() | NOW() | SYSDATE()+0 | UTC_DATE() | UTC_TIME() | UTC_TIME()+0 |
+------------+-----------+---------------------+----------------+------------+------------+--------------+
| 2023-05-25 | 23:50:38 | 2023-05-25 23:50:38 | 20230525235038 | 2023-05-25 | 15:50:38 | 155038 |
+------------+-----------+---------------------+----------------+------------+------------+--------------+
1 row in set (0.00 sec)
mysql>
5.2 日期与时间戳的转换
mysql> SELECT UNIX_TIMESTAMP(now()), UNIX_TIMESTAMP(CURDATE()),UNIX_TIMESTAMP(CURTIME()),UNIX_TIMESTAMP('2011-11-11 11:11:11');
+-----------------------+---------------------------+---------------------------+---------------------------------------+
| UNIX_TIMESTAMP(now()) | UNIX_TIMESTAMP(CURDATE()) | UNIX_TIMESTAMP(CURTIME()) | UNIX_TIMESTAMP('2011-11-11 11:11:11') |
+-----------------------+---------------------------+---------------------------+---------------------------------------+
| 1685030018 | 1684944000 | 1685030018 | 1320981071 |
+-----------------------+---------------------------+---------------------------+---------------------------------------+
1 row in set (0.00 sec)
mysql>
5.3 获取月份、星期、星期数、天数等函数
mysql> SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM DUAL;
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
| YEAR(CURDATE()) | MONTH(CURDATE()) | DAY(CURDATE()) | HOUR(CURTIME()) | MINUTE(NOW()) | SECOND(SYSDATE()) |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
| 2023 | 5 | 25 | 23 | 56 | 39 |
+-----------------+------------------+----------------+-----------------+---------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> 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;
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| MONTHNAME('2021-10-26') | DAYNAME('2021-10-26') | WEEKDAY('2021-10-26') | QUARTER(CURDATE()) | WEEK(CURDATE()) | DAYOFYEAR(NOW()) | DAYOFMONTH(NOW()) | DAYOFWEEK(NOW()) |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
| October | Tuesday | 1 | 2 | 21 | 145 | 25 | 5 |
+-------------------------+-----------------------+-----------------------+--------------------+-----------------+------------------+-------------------+------------------+
1 row in set (0.00 sec)
mysql>
5.4 指定日期中的特定部分
mysql> SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()), EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
+----------------------------+---------------------------+------------------------------+------------------------------------+
| EXTRACT(MINUTE FROM NOW()) | EXTRACT( WEEK FROM NOW()) | EXTRACT( QUARTER FROM NOW()) | EXTRACT( MINUTE_SECOND FROM NOW()) |
+----------------------------+---------------------------+------------------------------+------------------------------------+
| 59 | 21 | 2 | 5924 |
+----------------------------+---------------------------+------------------------------+------------------------------------+
1 row in set (0.00 sec)
mysql>
5.5 时间和秒钟转换的函数
mysql> SELECT TIME_TO_SEC(NOW()), SEC_TO_TIME(32171);
+--------------------+--------------------+
| TIME_TO_SEC(NOW()) | SEC_TO_TIME(32171) |
+--------------------+--------------------+
| 32199 | 08:56:11 |
+--------------------+--------------------+
1 row in set (0.00 sec)
### 5.6 计算日期和时间的函数
- 第1组：
mysql>
mysql> SELECT 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;
+---------------------+---------------------+---------------------+
| col3 | col4 | col5 |
+---------------------+---------------------+---------------------+
| 2021-10-21 23:32:13 | 2021-10-21 23:33:13 | 2022-05-26 09:01:49 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT DATE_SUB('2021-01-21',INTERVAL 31 DAY) AS col1, SUBDATE('2021-01-21',INTERVAL 31 DAY) AS col2, DATE_SUB('2021-01-21 02:01:01',INTERVAL '1 1' DAY_HOUR) AS col3 FROM DUAL;
+------------+------------+---------------------+
| col1 | col2 | col3 |
+------------+------------+---------------------+
| 2020-12-21 | 2020-12-21 | 2021-01-20 01:01:01 |
+------------+------------+---------------------+
1 row in set (0.00 sec)
mysql>
- 第2组：
mysql> 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;
+---------------------+---------------------+------------------------+-------------------------------+---------------------------------------+----------------+-----------------------+-----------------+--------------------------+--------------------+--------------------------------+
| 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) |
+---------------------+---------------------+------------------------+-------------------------------+---------------------------------------+----------------+-----------------------+-----------------+--------------------------+--------------------+--------------------------------+
| 2023-05-26 09:06:21 | 2023-05-26 09:05:31 | 2023-05-26 08:04:58 | NULL | 838:59:59 | 0001-01-01 | 359 | 2023-05-31 | 2023-01-12 | 10:21:23 | 20200101010111 |
+---------------------+---------------------+------------------------+-------------------------------+---------------------------------------+----------------+-----------------------+-----------------+--------------------------+--------------------+--------------------------------+
1 row in set, 2 warnings (0.00 sec)
mysql>
5.7 日期的格式化与解析
- 函数
![]()
- 上述 非GET_FORMAT 函数中fmt参数常用的格式符
![]()
- GET_FORMAT函数中date_type和format_type参数取值如下
![]()
mysql> SELECT DATE_FORMAT(NOW(), '%H:%i:%s'),STR_TO_DATE('09/01/2009','%m/%d/%Y'),STR_TO_DATE('20140422154706','%Y%m%d%H%i%s');
+--------------------------------+--------------------------------------+----------------------------------------------+
| DATE_FORMAT(NOW(), '%H:%i:%s') | STR_TO_DATE('09/01/2009','%m/%d/%Y') | STR_TO_DATE('20140422154706','%Y%m%d%H%i%s') |
+--------------------------------+--------------------------------------+----------------------------------------------+
| 09:41:57 | 2009-09-01 | 2014-04-22 15:47:06 |
+--------------------------------+--------------------------------------+----------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT GET_FORMAT(DATE, 'USA'),DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
+-------------------------+-------------------------------------------+-----------------------------------------------+
| GET_FORMAT(DATE, 'USA') | DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')) | STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d') |
+-------------------------+-------------------------------------------+-----------------------------------------------+
| %m.%d.%Y | 05.26.2023 | 2020-01-01 |
+-------------------------+-------------------------------------------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
5.8 流程处理函数
mysql> SELECT IF(1 > 0,'正确','错误'), IFNULL(null,'Hello Word');
+-----------------------------+---------------------------+
| IF(1 > 0,'正确','错误') | IFNULL(null,'Hello Word') |
+-----------------------------+---------------------------+
| 正确 | Hello Word |
+-----------------------------+---------------------------+
1 row in set (0.01 sec)
mysql>
mysql> SELECT CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END;
+-----------------------------------------------------------------------+
| CASE WHEN 1 > 0 THEN '1 > 0' WHEN 2 > 0 THEN '2 > 0' ELSE '3 > 0' END |
+-----------------------------------------------------------------------+
| 1 > 0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT CASE 1 WHEN 1 THEN '我是1' WHEN 2 THEN '我是2' ELSE '你是谁' END;
+-------------------------------------------------------------------------+
| CASE 1 WHEN 1 THEN '我是1' WHEN 2 THEN '我是2' ELSE '你是谁' END |
+-------------------------------------------------------------------------+
| 我是1 |
+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
6. 加密与解密函数
- 加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。
![]()
mysql> SELECT md5('123'),SHA('Tom123');
+----------------------------------+------------------------------------------+
| md5('123') | SHA('Tom123') |
+----------------------------------+------------------------------------------+
| 202cb962ac59075b964b07152d234b70 | c7c506980abc31cc390a2438c90861d0f1216d50 |
+----------------------------------+------------------------------------------+
1 row in set (0.00 sec)
mysql>
7. MySQL信息函数
- MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作
![]()
mysql> SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER(),VERSION(),CONNECTION_ID(),DATABASE();
+----------------+----------------+----------------+----------------+-----------+-----------------+------------+
| USER() | CURRENT_USER() | SYSTEM_USER() | SESSION_USER() | VERSION() | CONNECTION_ID() | DATABASE() |
+----------------+----------------+----------------+----------------+-----------+-----------------+------------+
| root@localhost | root@localhost | root@localhost | root@localhost | 8.0.26 | 8 | atguigudb |
+----------------+----------------+----------------+----------------+-----------+-----------------+------------+
1 row in set (0.01 sec)
mysql>
- MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的
![]()
mysql> SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2),CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
+--------------------+--------------------+---------------------+-----------------+------------------+-------------------+
| FORMAT(123.123, 2) | FORMAT(123.523, 0) | FORMAT(123.123, -2) | CONV(16, 10, 2) | CONV(8888,10,16) | CONV(NULL, 10, 2) |
+--------------------+--------------------+---------------------+-----------------+------------------+-------------------+
| 123.12 | 124 | 123 | 10000 | 22B8 | NULL |
+--------------------+--------------------+---------------------+-----------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876),BENCHMARK(1, MD5('mysql')),BENCHMARK(1000000, MD5('mysql'));
+----------------------------+-----------------------+----------------------------+----------------------------------+
| INET_ATON('192.168.1.100') | INET_NTOA(3232235876) | BENCHMARK(1, MD5('mysql')) | BENCHMARK(1000000, MD5('mysql')) |
+----------------------------+-----------------------+----------------------------+----------------------------------+
| 3232235876 | 192.168.1.100 | 0 | 0 |
+----------------------------+-----------------------+----------------------------+----------------------------------+
1 row in set (0.12 sec)
mysql> SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));
+------------------+----------------------------------------+
| CHARSET('mysql') | CHARSET(CONVERT('mysql' USING 'utf8')) |
+------------------+----------------------------------------+
| utf8mb4 | utf8mb3 |
+------------------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql>
