1. 函数的理解
1.1 什么是函数
函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率，又提高了可维护性。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率。
从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，本章及下一章讲解的是 SQL 的内置函数。
1.2 不同DBMS函数的差异
我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。
1.3 MySQL的内置函数及分类
MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类：单行函数、聚合函数（或分组函数）。
两种SQL函数
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
2. 数值函数
2.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; • 1 • 2 • 3
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) FROM DUAL;
2.2 角度与弧度互换函数
SELECT RADIANS(30),RADIANS(60),RADIANS(90),DEGREES(2*PI()),DEGREES(RADIANS(90)) FROM DUAL;
2.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)函数则仍然可以计算。
ATAN2(M,N)函数的使用示例如下：
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),TAN(RADIANS(45)),DEGREES(ATAN(1)),DEGREES(ATAN2(1,1)) FROM DUAL; • 1 • 2
2.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)
2.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)
3. 字符串函数
注意：MySQL中，字符串的位置是从1开始的。
举例：
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> SELECT NULLIF('mysql','mysql'),NULLIF('mysql', ''); +-------------------------+---------------------+ | NULLIF('mysql','mysql') | NULLIF('mysql', '') | +-------------------------+---------------------+ | NULL | mysql | +-------------------------+---------------------+ 1 row in set (0.00 sec)
4. 日期和时间函数
4.1 获取日期、时间
举例：
SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0 FROM DUAL; • 1 • 2
4.2 日期与时间戳的转换
举例：
mysql> SELECT UNIX_TIMESTAMP(now()); +-----------------------+ | UNIX_TIMESTAMP(now()) | +-----------------------+ | 1576380910 | +-----------------------+ 1 row in set (0.01 sec) mysql> SELECT UNIX_TIMESTAMP(CURDATE()); +---------------------------+ | UNIX_TIMESTAMP(CURDATE()) | +---------------------------+ | 1576339200 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP(CURTIME()); +---------------------------+ | UNIX_TIMESTAMP(CURTIME()) | +---------------------------+ | 1576380969 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11') +---------------------------------------+ | UNIX_TIMESTAMP('2011-11-11 11:11:11') | +---------------------------------------+ | 1320981071 | +---------------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT FROM_UNIXTIME(1576380910); +---------------------------+ | FROM_UNIXTIME(1576380910) | +---------------------------+ | 2019-12-15 11:35:10 | +---------------------------+ 1 row in set (0.00 sec)
4.3 获取月份、星期、星
期数、天数等举例：
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()), HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE()) FROM DUAL; • 1 • 2 • 3
函数
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.4 日期的操作函数
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()), EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW()) FROM DUAL;
4.5 时间和秒钟转换的函数
举例：
mysql> SELECT TIME_TO_SEC(NOW()); +--------------------+ | TIME_TO_SEC(NOW()) | +--------------------+ | 78774 | +--------------------+ 1 row in set (0.00 sec)
mysql> SELECT SEC_TO_TIME(78774); +--------------------+ | SEC_TO_TIME(78774) | +--------------------+ | 21:52:54 | +--------------------+ 1 row in set (0.12 sec)
4.6 计算日期和时间的函数
第1组：
上述函数中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;
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;
第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;
mysql> SELECT ADDTIME(NOW(), 50); +---------------------+ | ADDTIME(NOW(), 50) | +---------------------+ | 2019-12-15 22:17:47 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT ADDTIME(NOW(), '1:1:1'); +-------------------------+ | ADDTIME(NOW(), '1:1:1') | +-------------------------+ | 2019-12-15 23:18:46 | +-------------------------+ 1 row in set (0.00 sec)
mysql> SELECT SUBTIME(NOW(), '1:1:1'); +-------------------------+ | SUBTIME(NOW(), '1:1:1') | +-------------------------+ | 2019-12-15 21:23:50 | +-------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBTIME(NOW(), '-1:-1:-1'); +----------------------------+ | SUBTIME(NOW(), '-1:-1:-1') | +----------------------------+ | 2019-12-15 22:25:11 | +----------------------------+ 1 row in set, 1 warning (0.00 sec)
mysql> SELECT FROM_DAYS(366); +----------------+ | FROM_DAYS(366) | +----------------+ | 0001-01-01 | +----------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKEDATE(2020,1); +------------------+ | MAKEDATE(2020,1) | +------------------+ | 2020-01-01 | +------------------+ 1 row in set (0.00 sec) mysql> SELECT MAKEDATE(2020,32); +-------------------+ | MAKEDATE(2020,32) | +-------------------+ | 2020-02-01 | +-------------------+ 1 row in set (0.00 sec)
mysql> SELECT MAKETIME(1,1,1); +-----------------+ | MAKETIME(1,1,1) | +-----------------+ | 01:01:01 | +-----------------+ 1 row in set (0.00 sec)
mysql> SELECT PERIOD_ADD(20200101010101,1); +------------------------------+ | PERIOD_ADD(20200101010101,1) | +------------------------------+ | 20200101010102 | +------------------------------+ 1 row in set (0.00 sec)
mysql> SELECT TO_DAYS(NOW()); +----------------+ | TO_DAYS(NOW()) | +----------------+ | 737773 | +----------------+ 1 row in set (0.00 sec)
举例：查询 7 天内的新增用户数有多少？
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
