一、函数的理解
1、函数定义
函数在计算机语言的使用中贯穿始终，函数的作用是什么呢？它可以把我们经常使用的代码封装起来，需要的时候直接调用即可。这样既 提高了代码效率 ，又 提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地 提高用户对数据库的管理效率。
从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的。
2、不同DBMS函数的差异
我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即
DBMS。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是
被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很差的，因此在使用函数的时候需要特别注意。
3、MySQL内置函数及分类
MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率。
MySQL提供的内置函数从实现的功能角度可以分为数值函数、字符串函数、日期和时间函数、流程控制函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，我将这些丰富的内置函数再分为两类： 单行函数 、 聚合函数（或分组函数） 。
4、单行函数的特点
（1）操作数据对象；
（2）接受参数返回一个结果；
（3）只对一行进行变换；
（4）每行返回一个结果；
（5）可以嵌套；
（6）参数可以是一列或一个值。
二、数值函数
1、基本函数
举例：
# 基本操作
SELECT ABS(-123),ABS(32),# 取绝对值
SIGN(-23),SIGN(23),# 返回值的符号，正号返回1，负号返回-1
PI(),# 圆周率
CEIL(32.32),CEIL(-43.23),# 向上取整
FLOOR(32.32),FLOOR(-43.23),# 向下取整
MOD(12,5),12 MOD 5, 12%5 # 取余
FROM DUAL;
# 取随机数
# 注意区别：同样的rand()会产生不同的0-1的随机数，同样的rand(x)会产生相同的0-1的随机数
SELECT RAND(),RAND(),RAND(10),RAND(10),RAND(-1),RAND(-1)
FROM DUAL;
# 四舍五入
# round(x)和round(x,0)都是保留整数，round(x,y)保留到小数点后y位，注意的y的正负号
SELECT ROUND(123.556),ROUND(123.456,0),ROUND(123.456,1),
ROUND(123.456,2),ROUND(123.456,-1),ROUND(153.456,-2)
FROM DUAL;
# 截断操作(TRUNCATE(x,y) 返回数字x截断为y位小数的结果)
SELECT TRUNCATE(123.456,0),TRUNCATE(123.496,1),TRUNCATE(129.45,-1)
FROM DUAL;
# 单行函数嵌套
SELECT TRUNCATE(ROUND(123.456,2),0)
FROM DUAL;
2、角度与弧度互换函数
举例：
#角度与弧度的互换(radinas()和degrees()可以嵌套)
SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90),
DEGREES(2*PI()),DEGREES(RADIANS(60))
FROM DUAL;
3、三角函数
举例：
#三角函数
SELECT SIN(RADIANS(30)),DEGREES(ASIN(1)),# 正弦和反正弦
COS(RADIANS(60)),DEGREES(ACOS(0)),# 余弦和反余弦
TAN(RADIANS(45)),DEGREES(ATAN(1)) # 正切和反正切
FROM DUAL;
4、指数与对数
举例：
# 指数
SELECT POW(2,5),POWER(2,4),EXP(2)
FROM DUAL;
# 对数
SELECT LN(EXP(2)),LOG(EXP(2)),LOG10(10),LOG2(4)
FROM DUAL;
5、进制间的转换
举例：
#进制间的转换
SELECT BIN(10),HEX(10),OCT(10),CONV(10,10,8) #CONV(x,f1,f2) 返回f1进制数变成f2进制数
FROM DUAL;
三、字符串函数
注意：MySQL中，字符串的位置是从1开始的。
举例：
# 区别CHAR_LENGTH和LENGTH
SELECT ASCII('Abcdfsf'),# ASCII(S)返回字符串S中的第一个字符的ASCII码值
CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),#CHAR_LENGTH(s)返回字符串s的字符数(中文一个字算一个字符)
LENGTH('hello'),LENGTH('我们') #LENGTH(s)返回字符串s的字节数(中文一个字是三个字节)
FROM DUAL;
SELECT CONCAT('panpan',' like',' coding'),# CONCAT连接字符串
CONCAT_WS('-','hello','world','hello','beijing') # CONCAT_WS同CONCAT，但每个字符串之间需要加上x
FROM DUAL;
SELECT INSERT('helloworld',2,3,'aaaaa'),
# INSERT(str, idx, len, replacestr)
#将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
REPLACE('hello','llo','mmm')
#REPLACE(str, a, b) 用字符串b替换字符串str中所有出现的字符串a
FROM DUAL;
SELECT UPPER('HelLo'),#UPPER(s)将字符串s的所有字母转成大写字母
LOWER('HelLo') #LOWER(s)将字符串s的所有字母转成小写字母
FROM DUAL;
#LEFT(str,n) 返回字符串str最左边的n个字符
#RIGHT(str,n) 返回字符串str最右边的n个字符
#若超出字符串本身长度，则会全部显示
SELECT LEFT('hello',2),RIGHT('hello',3),RIGHT('hello',13)
FROM DUAL;
#LTRIM(s) 去掉字符串s左侧的空格
#RTRIM(s) 去掉字符串s右侧的空格
#TRIM(s1 FROM s) 去掉字符串s开始与结尾的s1
SELECT CONCAT('---',LTRIM(' h el lo '),'***'),
CONCAT('---',RTRIM(' h el lo '),'***'),
TRIM('oo' FROM 'ooheollo')
FROM DUAL;
#REPEAT(str, n) 返回str重复n次的结果
#SPACE(n) 返回n个空格
#STRCMP(s1,s2) 比较字符串s1,s2的ASCII码值的大小
SELECT REPEAT('hello',4),LENGTH(SPACE(5)),STRCMP('abc','abe')
FROM DUAL;
#SUBSTR(s,index,len)
#返回从字符串s的index位置其len个字符，
#LOCATE(substr,str)
#返回字符串substr在字符串str中首次出现的位置，未找到，返回0
SELECT SUBSTR('hello',2,2),LOCATE('lll','hello')
FROM DUAL;
#ELT(m,s1,s2,…,sn)
#返回指定位置的字符串，如果m=1，则返回s1，如果m=2，则返回s2，如果m=n，则返回sn
#FIELD(s,s1,s2,…,sn) 返回字符串s在字符串列表中第一次出现的位置
#FIND_IN_SET(s1,s2)
#返回字符串s1在字符串s2中出现的位置。其中，字符串s2是一个以逗号分隔的字符串
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;
四、日期和时间函数
1、获取日期、时间
举例：
SELECT CURDATE(),CURRENT_DATE(),#返回当前日期，只包含年、月、日
CURTIME(),#返回当前时间，只包含时、分、秒
NOW(),SYSDATE(),# 返回当前系统日期和时间
UTC_DATE(),UTC_TIME() # 世界标准时间
FROM DUAL;
SELECT CURDATE(),CURDATE() + 0,CURTIME() + 0,NOW() + 0 # +0可以去除间隔符-
FROM DUAL;
2、日期与时间戳的转换
举例：
SELECT UNIX_TIMESTAMP(),#以UNIX时间戳的形式返回当前时间
UNIX_TIMESTAMP('2022-5-9 11:25:32'),#将时间date以UNIX时间戳的形式返回
FROM_UNIXTIME(1652063213),FROM_UNIXTIME(1652066732) # 将UNIX时间戳的时间转换为普通格式的时间
FROM DUAL;
3、获取月份、星期、星期数、天数等函数
举例：
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE()),#返回具体的日期值
HOUR(CURTIME()),MINUTE(NOW()),SECOND(SYSDATE())#返回具体的时间值
FROM DUAL;
SELECT MONTHNAME('2022-5-9'),DAYNAME('2022-5-9'),WEEKDAY('2022-5-9'),# 返回月份、星期几和周几
QUARTER(CURDATE()),WEEK(CURDATE()),DAYOFYEAR(NOW()),# 返回季度、一年中的第几周、一年中的第几天
DAYOFMONTH(NOW()),DAYOFWEEK(NOW())# 返回所在月份的第几天、周几
FROM DUAL;
4、日期的操作函数
举例：
#返回指定日期中特定的部分，type指定返回的值
SELECT EXTRACT(SECOND FROM NOW()),EXTRACT(DAY FROM NOW()),
EXTRACT(HOUR_MINUTE FROM NOW()),EXTRACT(QUARTER FROM '2022-5-9')
FROM DUAL;
5、时间和秒钟转换的函数
举例：
SELECT TIME_TO_SEC(CURTIME()),#将 time 转化为秒并返回结果值。转化的公式为：小时*3600+分钟 *60+秒
SEC_TO_TIME(38351) #将 seconds 描述转化为包含小时、分钟和秒的时间
FROM DUAL;
6、计算日期和时间的函数
举例：
#注意区别：DATE_ADD中的1表示向后一年，-1表示向前一年，而DATE_SUB的1和-1相反
SELECT NOW(),
DATE_ADD(NOW(),INTERVAL 1 YEAR),#返回与给定日期时间相差INTERVAL时间段的日期时间
DATE_ADD(NOW(),INTERVAL -1 YEAR),
DATE_SUB(NOW(),INTERVAL 1 YEAR),#返回与date相差INTERVAL时间间隔的日期
DATE_SUB(NOW(),INTERVAL -1 YEAR)
FROM DUAL;
SELECT DATE_ADD(NOW(), INTERVAL 1 DAY) AS col1,DATE_ADD('2022-5-9 10:43:47',INTERVAL 1 SECOND) AS col2,
ADDDATE('2022-5-9 10:43:47',INTERVAL 1 SECOND) AS col3,
DATE_ADD('2022-5-9 10:43:47',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 ADDTIME(NOW(),20),#返回time1加上time2的时间。当time2为一个数字时，代表的是秒 ，可以为负数
SUBTIME(NOW(),30),SUBTIME(NOW(),'1:1:3'),#返回time1减去time2后的时间。当time2为一个数字时，代表的是秒 ，可以为负数
DATEDIFF(NOW(),'2021-5-9'),#返回date1 - date2的日期间隔天数
TIMEDIFF(NOW(),'2021-5-8 22:10:10'),#返回time1 - time2的时间间隔
FROM_DAYS(366),#返回从0000年1月1日起，N天以后的日期
TO_DAYS('0000-12-25'),#返回日期date距离0000年1月1日的天数
LAST_DAY(NOW()),#返回date所在月份的最后一天的日期
MAKEDATE(YEAR(NOW()),32),#针对给定年份与所在年份中的天数返回一个日期
MAKETIME(10,50,23),#将给定的小时、分钟和秒组合成时间并返回
PERIOD_ADD(20220101010101,10)#返回time加上n后的时间
FROM DUAL;
7、日期的格式化与解析
上述 非GET_FORMAT 函数中fmt参数常用的格式符：
GET_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('2022-May-9th 11:01:30 Sunday 0','%Y-%M-%D %h:%i:%S %W %w')
FROM DUAL;
SELECT GET_FORMAT(DATE,'USA')
FROM DUAL;
SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE,'USA'))
FROM DUAL;
五、流程控制函数
流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。
MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。
举例：
#IF(VALUE,VALUE1,VALUE2)
SELECT last_name,salary,IF(salary>=6000,'高工资','低工资') "details"
FROM employees;
SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0) "details",
salary * 12 * (1 + IF(commission_pct IS NOT NULL,commission_pct,0)) "annual_sal"
FROM employees;
#IFNULL(VALUE1,VALUE2)可以视作是IF(VALUE,VALUE1,VALUE2)的特殊情况(与第二个相同)
SELECT last_name,commission_pct,IFNULL(commission_pct,0) "details"
FROM employees;
#CASE WHEN ... THEN ...WHEN ... THEN ... ELSE ... END
SELECT last_name,salary,CASE WHEN salary>=15000 THEN '精英'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '屌丝'
ELSE '草根' END "details"
FROM employees;
SELECT last_name,salary,CASE WHEN salary >= 15000 THEN '白骨精'
WHEN salary >= 10000 THEN '潜力股'
WHEN salary >= 8000 THEN '小屌丝'
END "details"
FROM employees;
#CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END
/*
查询部门号为 10,20, 30 的员工信息,
若部门号为 10, 则打印其工资的 1.1 倍,
20 号部门, 则打印其工资的 1.2 倍,
30 号部门,打印其工资的 1.3 倍数,
其他部门,打印其工资的 1.4 倍数
*/
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary*1.1
WHEN 20 THEN salary*1.2
WHEN 30 THEN salary*1.3
ELSE salary*1.4 END "details"
FROM employees;
六、加密与解密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在
保证数据库安全时非常有用。
可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数。
举例：
# PASSWORD()在mysql8.0中弃用。
# ENCODE()\DECODE() 在mysql8.0中弃用
SELECT MD5('mysql'),SHA('mysql'),MD5(MD5('mysql'))
FROM DUAL;
七、MySQL信息函数
MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地
对数据库进行维护工作。
举例：
SELECT VERSION(),CONNECTION_ID(),DATABASE(),SCHEMA(),
USER(),CURRENT_USER(),CHARSET('尚硅谷'),COLLATION('尚硅谷')
FROM DUAL;
八、其他函数
MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视
的。
举例：
#如果n的值小于或者等于0，则只保留整数部分
SELECT FORMAT(123.125,2),FORMAT(123.125,0),FORMAT(123.125,-2)
FROM DUAL;
SELECT CONV(16, 10, 2), CONV(8888,10,16), CONV(NULL, 10, 2)
FROM DUAL;
#以“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;
#BENCHMARK()用于测试表达式的执行效率
SELECT BENCHMARK(100000,MD5('mysql'))
FROM DUAL;
#CONVERT():可以实现字符集的转换
SELECT CHARSET('atguigu'),CHARSET(CONVERT('atguigu' USING 'gbk'))
FROM DUAL;
九、小练习
# 1.显示系统时间(注：日期+时间)
SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP(),LOCALTIME(),LOCALTIMESTAMP() #大家只需要掌握一个函数就可以了
FROM DUAL;
# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees;
# 3.将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT last_name,LENGTH(last_name) "name_length"
FROM employees
#order by last_name asc;
ORDER BY name_length ASC;
# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id,',',last_name,',',salary) "OUT_PUT"
FROM employees;
# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT employee_id,DATEDIFF(CURDATE(),hire_date)/365 "worked_years",DATEDIFF(CURDATE(),hire_date) "worked_days",
TO_DAYS(CURDATE()) - TO_DAYS(hire_date) "worked_days1"
FROM employees
ORDER BY worked_years DESC;
# 6.查询员工姓名，hire_date , department_id，满足以下条件：
#雇用时间在1997年之后，department_id 为80 或 90 或110, commission_pct不为空
SELECT last_name,hire_date,department_id
FROM employees
WHERE department_id IN (80,90,110)
AND commission_pct IS NOT NULL
#and hire_date >= '1997-01-01'; #存在着隐式转换
#and date_format(hire_date,'%Y-%m-%d') >= '1997-01-01'; # 显式转换操作，格式化：日期---> 字符串
#and date_format(hire_date,'%Y') >= '1997'; # 显式转换操作，格式化
AND hire_date >= STR_TO_DATE('1997-01-01','%Y-%m-%d');# 显式转换操作，解析：字符串 ----> 日期
# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,hire_date
FROM employees
WHERE DATEDIFF(CURDATE(),hire_date) >= 10000;
# 8.做一个查询，产生下面的结果
#<last_name> earns <salary> monthly but wants <salary*3>
SELECT CONCAT(last_name,' earns ',TRUNCATE(salary,0), ' monthly but wants ',TRUNCATE(salary * 3,0)) "Dream Salary"
FROM employees;
# 9.使用case-when，按照下面的条件：
/*job grade
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
产生下面的结果:
*/
SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
END "Grade"
FROM employees;
SELECT last_name "Last_name",job_id "Job_id",CASE job_id WHEN 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
ELSE "undefined" END "Grade"
FROM employees;
所用数据库请看文章末尾：SQL语言和基本的select语句
