7. 单行函数
1. 函数的理解
函数可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既提高了代码效率 ，又提高了可维护性。在 SQL 中我们也可以使用函数 对检索出来的数据进行函数操作。使用这些函数，可以**极大地提高用户对数据库的管理效率 **。
从函数定义的角度出发，我们可以将函数分成内置函数和自定义函数。
不同DBMS函数的差异
不同的数据库软件，即 DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。采用 SQL 函数的代码可移植性是很差的
MySQL的内置函数及分类
数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。
下面我们将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。
单行函数
- 操作数据对象
- 接受参数返回一个结果
- 只对一行进行变换
- 每行返回一个结果
- 可以嵌套
- 参数可以是一列或一个值
2. 数值函数
基本函数
SELECT CEIL(15.32),FLOOR(15.32), -- 16,15
ROUND(123.456),ROUND(125.456,-1),TRUNCATE(129.456,-1) -- 123,130,120
FROM DUAL;
ROUND()只会对保留位的下一位进行判断，进行四舍五入；
角度与弧度互换函数
SELECT RADIANS(360),DEGREES(1)-- 2pi(),57.3
FROM DUAL;
三角函数
指数与对数
进制间的转换
3. 字符串函数
select ascii('asdf'),length('asdf'),char_length('asdf'),-- 97,4,,4
length('中国'),char_length('中国')-- 6,2
from dual;
SELECT CONCAT('a','b','c'),CONCAT_WS('-','a','b','c')-- abc,a-b-c
FROM DUAL;
-- 右对齐
SELECT employee_id,LPAD(salary,10,' ')
FROM employees
还有一个较为常用的字符串函数：
substring_index(str,delim,count)
delim：分隔符，如"/"和"."，
count：计数，
按分隔符的定位截取其之前的字符串,如：
str=www.wibi.com，substring_index(str,".",2)=www.wibi
SELECT employee_id,NULLIF(LENGTH(first_name),LENGTH(last_name)) "compare"
FROM employees;
4. 日期和时间函数
获取日期、时间
日期与时间戳的转换
获取月份、星期、星期数、天数等函数
日期的操作函数
时间和秒钟转换的函数
SELECT TIME_TO_SEC(NOW()),SEC_TO_TIME(78774)
FROM DUAL;
计算日期和时间的函数
SELECT DATE_ADD(NOW(),INTERVAL 9 DAY),
DATE_ADD(NOW(),INTERVAL -5 DAY_HOUR)
FROM DUAL;
SELECT ADDTIME(NOW(),'-5:3:8'),
ADDTIME(NOW(),'-3:8')
FROM DUAL;
注意：ADDTIME(time1,time2)方法中的time2只能整体加或减，不能出现加小时并减分钟的操作，当其为'-3:8'类型时，是将time1减去3小时8分钟并返回。
日期的格式化与解析
SELECT DATE_FORMAT(NOW(),'%Y/%c/%e %H:%i:%s %W'),-- 2022/9/30 16:20:32 Friday
DATE_FORMAT(NOW(),GET_FORMAT(DATETIME,'USA'))-- 2022-09-30 16.25.46
FROM DUAL;
-- 雇佣时间在1997年之后
SELECT last_name,hire_date,department_id
FROM employees
WHERE hire_date>='1997-1-1' -- 此处存在隐式转换
-- datediff(hire_date,'1997-1-1')>=0
AND department_id IN(80,90,110)
AND commission_pct IS NOT NULL;
5. 流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
SELECT last_name,salary,IF(salary >= 6000,'高工资','低工资') "details"
FROM employees;
-- IFNULL是IF的一种特殊情况
SELECT last_name,IF(commission_pct IS NULL,0,commission_pct)
FROM employees;
-- 类似于java的if ... else if ... else if ... else
SELECT last_name,CASE WHEN salary>15000 THEN '富裕'
WHEN salary>10000 THEN '中产'
WHEN salary>6000 THEN '小康'
ELSE '贫困' END details
FROM employees;
-- 类似于java的swich ... case...default...
SELECT last_name,CASE department_id WHEN 10 THEN '人事部'
WHEN 20 THEN '技术部'
WHEN 30 THEN '财政部'
ELSE '车间' END details
FROM employees
两种结构中的else是可选的
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
END "REVISED_SALARY"
FROM employees;
没有在WHEN...THEN...中声明的数据会在搜索出来的字段中为NULL
6. 加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用。
PASSWORD()在mysql8.0中弃用。
ENCODE()\DECODE() 在mysql8.0中弃用。
7. MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地对数据库进行维护工作。
8. 其他函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的。
-- 如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123456.123, 2),ROUND ('123456.523',-3) ,
FORMAT(123.923, -2),FORMAT(123.523, 0)
FROM DUAL;
-- 123,456.12,123000,124,124
FORMAT(X,D) 会四舍五入，返回类型是字符串，满3位会加一个逗号
-- 以“192.168.1.100”为例，计算方式为192乘以256的3次方，加上168乘以256的2次方，加上1乘以256，再加上100。
SELECT INET_ATON('192.168.1.100'),INET_NTOA(3232235876)
FROM DUAL;
SELECT CHARSET('中国'),CHARSET(CONVERT('中国' USING 'gbk'))-- utf8mb3,gbk
FROM DUAL;
浙公网安备 33010602011771号