第07章_单行函数
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 基本函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
SIGN(X) | 返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(x),CEILING(x) | 返回大于或等于某个值的最小整数 |
FLOOR(x) | 返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3…) | 返回列表中的最小值 |
GREATEST(e1,e2,e3…) | 返回列表中的最大值 |
MOD(x,y) | 返回X除以Y后的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根。当X的值为负数时,返回NULL |
举例:
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)
FROM DUAL;
2.2 角度与弧度互换函数
函数 | 用法 |
---|---|
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 |
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;
2.4 指数与对数
SELECT POW(2,5),POWER(2,4),EXP(2),LN(10),LOG10(10),LOG2(4) FROM DUAL;
2.5 进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8) FROM DUAL;
3. 字符串函数
注意
:MySQL中,字符串的位置是从1开始的。
举例:
SELECT FIELD('mm','hello','msm','amma'),FIND_IN_SET('mm','hello,mm,amma') FROM DUAL;
SELECT NULLIF('mysql','mysql'),NULLIF('mysql', '');
4. 日期和时间函数
4.1 获取日期、时间
举例:
SELECT
CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;
4.2 日期与时间戳的转换
举例:
SELECT UNIX_TIMESTAMP(now());
SELECT UNIX_TIMESTAMP(CURDATE());
SELECT UNIX_TIMESTAMP(CURTIME());
SELECT UNIX_TIMESTAMP('2011-11-11 11:11:11')
SELECT FROM_UNIXTIME(1576380910);
4.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.4 日期的操作函数
EXTRACT(type FROM date)函数中type的取值与含义:
SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;
4.5 时间和秒钟转换的函数
举例:
SELECT TIME_TO_SEC(NOW());
SELECT SEC_TO_TIME(78774);
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;
SELECT ADDTIME(NOW(), 50);
SELECT ADDTIME(NOW(), '1:1:1');
SELECT SUBTIME(NOW(), '1:1:1');
SELECT SUBTIME(NOW(), '-1:-1:-1');
SELECT FROM_DAYS(366);
SELECT MAKEDATE(2020,1);
SELECT MAKEDATE(2020,32);
SELECT MAKETIME(1,1,1);
SELECT PERIOD_ADD(20200101010101,1);
SELECT TO_DAYS(NOW());
举例:查询 7 天内的新增用户数有多少?
SELECT COUNT(*) as num FROM new_user WHERE TO_DAYS(NOW())-TO_DAYS(regist_time)<=7
4.7 日期的格式化与解析
上述 非GET_FORMAT
函数中fmt参数常用的格式符:
GET_FORMAT函数中date_type和format_type参数取值如下:
举例:
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');
SELECT STR_TO_DATE('09/01/2009','%m/%d/%Y')
FROM DUAL;
SELECT STR_TO_DATE('20140422154706','%Y%m%d%H%i%s')
FROM DUAL;
SELECT STR_TO_DATE('2014-04-22 15:47:06','%Y-%m-%d %H:%i:%s')
FROM DUAL;
SELECT GET_FORMAT(DATE, 'USA');
SELECT DATE_FORMAT(NOW(),GET_FORMAT(DATE,'USA')),
FROM DUAL;
SELECT STR_TO_DATE('2020-01-01 00:00:00','%Y-%m-%d');
5. 流程控制函数
流程处理函数可以根据不同的条件,执行不同的处理流程,可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
SELECT IF(1 > 0,'正确','错误');
SELECT IFNULL(null,'Hello Word')
SELECT CASE
WHEN 1 > 0
THEN '1 > 0'
WHEN 2 > 0
THEN '2 > 0'
ELSE '3 > 0'
END
SELECT CASE 1
WHEN 1 THEN '我是1'
WHEN 2 THEN '我是2'
ELSE '你是谁'
END
SELECT employee_id,salary, CASE WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "描述"
FROM employees;
SELECT oid,`status`, CASE `status` WHEN 1 THEN '未付款'
WHEN 2 THEN '已付款'
WHEN 3 THEN '已发货'
WHEN 4 THEN '确认收货'
ELSE '无效订单' END
FROM t_order;
SELECT CASE WHEN 1 > 0 THEN 'yes' WHEN 1 <= 0 THEN 'no' ELSE 'unknown' END;
SELECT CASE WHEN 1 < 0 THEN 'yes' WHEN 1 = 0 THEN 'no' ELSE 'unknown' END;
SELECT CASE 1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
SELECT CASE -1 WHEN 0 THEN 0 WHEN 1 THEN 1 ELSE -1 END;
SELECT employee_id,12 * salary * (1 + IFNULL(commission_pct,0))
FROM employees;
SELECT last_name, job_id, salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary END "REVISED_SALARY"
FROM employees;
6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理,以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
可以看到,ENCODE(value,password_seed)函数DECODE(value,password_seed)函数互为反函数。
举例:
SELECT PASSWORD('mysql'), PASSWORD(NULL);
mysql5 版本命令
SELECT md5('123')
SELECT ENCODE('mysql', 'mysql');
SELECT DECODE(ENCODE('mysql','mysql'),'mysql');
mysql5 版本命令
7. MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数,这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
举例:
SELECT DATABASE();
SELECT USER(), CURRENT_USER(), SYSTEM_USER(),SESSION_USER();
SELECT CHARSET('ABC');
SELECT COLLATION('ABC');
8. 其他函数
MySQL中有些函数无法对其进行具体的分类,但是这些函数在MySQL的开发和运维过程中也是不容忽视的。
举例:
# 如果n的值小于或者等于0,则只保留整数部分
SELECT FORMAT(123.123, 2), FORMAT(123.523, 0), FORMAT(123.123, -2);
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2);
SELECT INET_ATON('192.168.1.100');
# 以“192.168.1.100”为例,计算方式为192乘以256的3次方,加上168乘以256的2次方,加上1乘以256,再加上100。
SELECT INET_NTOA(3232235876);
SELECT BENCHMARK(1, MD5('mysql'));
SELECT BENCHMARK(1000000, MD5('mysql'));
SELECT CHARSET('mysql'), CHARSET(CONVERT('mysql' USING 'utf8'));