文章目录

• • • 一、函数的基本理解
    • • 1.函数的理解
      • • 1.1内置函数的基本理解
        • 1.2不同DBMS函数的差异
        • 1.3MySQL的内置函数的分类
      • 2.图解多行函数和单行函数
    • 二、单行函数
    • • 1.数值函数
      • • 1.1基本函数
        • 1.2角度与弧度互换函数
        • 1.3三角函数
        • 1.4指数与对数
        • 1.5进制间转换
      • 2.字符串函数
      • 3.日期和时间函数
      • • 3.1获取日期时间（基础必知）
        • 3.2日期与时间戳的转换
        • 3.3获取月份、星期、星期数、天数等函数
        • 3.4日期的操作函数
        • 3.5时间和秒针转换的函数
        • 3.6计算日期和时间的函数
        • 3.7日期的格式化与解析
      • 4.流程控制函数
      • • 4.1SQL语句举例
      • 5.加密与解密函数（了解）
      • 6.MySQL信息函数
      • 7. 其他函数

一、函数的基本理解

1.函数的理解

1.1内置函数的基本理解

• 定义：从函数定义的角度出发，我们可以将函数分成 内置函数 和 自定义函数 。在 SQL 语言中，同样也包括了 内置函数和自定义函数。内置函数是系统内置的通用函数，而自定义函数是我们根据自己的需要编写的，下面记录的是内置函数的笔记
• 作用：它可以把我们经常使用的代码封装起来， 需要的时候直接调用即可。这样既提高了代码效率 ，又提高了可维护性 。在 SQL 中我们也可以使用函数对检索出来的数据进行函数操作。使用这些函数，可以极大地提高用户对数据库的管理效率

1.2不同DBMS函数的差异

• 我们在使用 SQL 语言的时候，不是直接和这门语言打交道，而是通过它使用不同的数据库软件，即DBMS（dbms一般指数据库管理系统）。DBMS 之间的差异性很大，远大于同一个语言不同版本之间的差异。实际上，只有很少的函数是 被 DBMS 同时支持的。比如，大多数 DBMS 使用（||）或者（+）来做拼接符，而在 MySQL 中的字符串拼 接函数为concat()。大部分 DBMS 会有自己特定的函数，这就意味着采用 SQL 函数的代码可移植性是很 差的，因此在使用函数的时候需要特别注意

1.3MySQL的内置函数的分类

• MySQL提供了丰富的内置函数，这些函数使得数据的维护与管理更加方便，能够更好地提供数据的分析 与统计功能，在一定程度上提高了开发人员进行数据分析与统计的效率
• MySQL提供的内置函数从 实现的功能角度 可以分为数值函数、字符串函数、日期和时间函数、流程控制 函数、加密与解密函数、获取MySQL信息函数、聚合函数等。这里，内置函数再分为两类： 单行函数 、 聚合函数（或分组函数）

2.图解多行函数和单行函数

• 单行函数
  • 操作数据对象
  • 接受参数返回一个结果
  • 只对一行进行变换
  • 每行返回一个结果
  • 可以嵌套
  • 参数可以是一列或一个值
• 聚合函数：聚合（或聚集、分组）函数，它是对一组数据进行汇总的函数，输入的是一组数据的集合，输出的是单个值

二、单行函数

1.数值函数

1.1基本函数

sql语句测试：

# ABS(X):返回x的绝对值
SELECT ABS(-1.6),ABS(2)
FROM DUAL;# SIGN(X)：返回x的符号，正数返回1，负数返货-1，0返回0
SELECT SIGN(23),SIGN(6.4),SIGN(-2),SIGN(0)
FROM DUAL;# PI()：圆周率
SELECT PI()
FROM DUAL;# CEIL(X)、CEILING(x)：返回大于或者等于x的最小整数
SELECT CEIL(4.5),CEIL(3.5),CEILING(4.5),CEILING(3.5)
FROM DUAL;# FLOOR(x)：返回小于或者等于x的最大整数
SELECT FLOOR(4.5),FLOOR(3.5)
FROM DUAL;# LEAST(value1,value2,...)：求最小值
SELECT LEAST(1,3,4,5,6,7,8,232,32323,2323)
FROM DUAL;# GREATEST(value1,value2,...)：求最大值
SELECT GREATEST(23,42,5534,32,231,13)
FROM DUAL;# MOD(x,y)：求x除以y的余数
SELECT MOD(6,5)
FROM DUAL;# RAND()、RAND(x) ：返回0~1的随机值，如果x相同，则返回的随机值相同
SELECT RAND(1),RAND(2),RAND(1),RAND()
FROM DUAL;# ROUND(X)：返回x的四舍五入
SELECT ROUND(6.789)
FROM DUAL;# ROUND(x,y)：返回x小数点后y位的四舍五入的值
SELECT ROUND(5.7892,2)
FROM DUAL;# TRUNCATE(x,y)：返回数字x后保留y位小数的值
SELECT TRUNCATE(4.56543,3)
FROM DUAL;# SQRT(X)：求x的平方根，如果x为负数则返回null
SELECT SQRT(16),SQRT(-45),SQRT(9)
FROM DUAL;

1.2角度与弧度互换函数

sql语句测试：

# 角度与弧度互换
# RADIANS(X):角度转换为弧度，x为角度值
SELECT RADIANS(30),RADIANS(45),RADIANS(60),RADIANS(90)
FROM DUAL;# DEGREES(X):弧度转换为角度，x为弧度值
SELECT DEGREES(2*PI())
FROM DUAL;

1.3三角函数

(参数中的x均为弧度值)

sql语句测试：

（这里简单举例数学常用的sin、cos、tan）

# 三角函数
# SIN(X)
SELECT SIN(RADIANS(30));
# COS(X)
SELECT COS(RADIANS(30));
# TAN(X)
SELECT TAN(RADIANS(30));

1.4指数与对数

sql语句测试：

#指数和对数
#POW(X,Y):x的y次方
SELECT POW(2,3);
# EXP(X):返回常熟e的x次方
SELECT EXP(4);
# LN(X)、LOG(X):返回以e为底x的对数，当x小于等于0时返回null
SELECT LN(3),LOG(3);
# LOG10(X):返回以10为底x的对数，当x小于等于0时返回null
SELECT LOG10(3);
# LOG2(X):返回以2为底的x的对数，当x小于等于0时，返回null
SELECT LOG2(3);

1.5进制间转换

sql语句测试：

# 进制间的转换（其中的x均为10进制）
# BIN(x) HEX(x) OCT(x) CONV(x,from_base,to_base)
SELECT BIN(10),HEX(10),OCT(10),CONV(10,2,8)
FROM DUAL;

2.字符串函数

注意：MySQL中，字符串的位置是从1开始的

个别函数举例

• CONCAT(s1,s2,…,sn) ：连接s1,s2,…,sn为一个字符串

# CONCAT(s1,s2,......,sn) 使用举例
SELECT CONCAT(e1.last_name,'的老板是',e2.last_name) 员工的对应上司
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id;

• CONCAT_WS(x, s1,s2,…,sn)：同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上x

# CONCAT(s1,s2,......,sn) 使用举例
SELECT CONCAT_WS('__',e1.last_name,'的老板是',e2.last_name) 员工的对应上司
FROM employees e1,employees e2
WHERE e1.manager_id=e2.employee_id;

• INSERT(str, idx, len, replacestr)：将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr

  （特别说明：MySQL中字符串的位置是从1开始的）

# INSERT(str, idx, len, replacestr)：将字符串str从第idx位置开始，len个字符长的子串替换为字符串replacestr
SELECT INSERT('abcdefg',1,3,'a');

3.日期和时间函数

3.1获取日期时间（基础必知）

SQL语句测试

SELECT CURDATE(),CURRENT_DATE(),CURTIME(),CURRENT_TIME(),NOW(),UTC_DATE(),UTC_TIME();

3.2日期与时间戳的转换

3.3获取月份、星期、星期数、天数等函数

SQL语句测试:

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;

3.4日期的操作函数

• EXTRACT(type FROM date) ：返回指定日期中特定的部分，type指定返回的值

• EXTRACT(type FROM date)函数中type的取值与含义：

sql语句举例

SELECT EXTRACT(day FROM NOW()),EXTRACT(SECOND FROM NOW())

3.5时间和秒针转换的函数

SQL语句测试：

SELECT TIME_TO_SEC(CURTIME()),SEC_TO_TIME(38989)

3.6计算日期和时间的函数

• 第一部分

上述函数中type的取值

SQL语句测试：

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;

• 第二部分

sql语句测试

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)
FRO DUAL;

3.7日期的格式化与解析

上述 非GET_FORMAT 函数中fmt参数常用的格式符：

GET_FORMAT函数中date_type和format_type参数取值如下：

• 格式化：日期—>字符串

# 格式化
SELECT DATE_FORMAT(CURDATE(),'%Y-%M-%D'),DATE_FORMAT(NOW(),'%y-%m-%d')
# 显示当前时间
SELECT DATE_FORMAT(NOW(), '%H:%i:%s');

• 解析：字符串—>日期

注解：这里的逆过程需要和格式化当时的格式一一匹配

# 格式化的逆过程解析的简单举例
SELECT STR_TO_DATE('22-11-23','%y-%m-%d')

• GET_FORMAT：返回日期字符串的显示格式，这里的format_type参数多代表一些国家

SELECT GET_FORMAT(DATE, 'USA')

• GET_FORMAT和DATE_FORMAT的巧用

SELECT DATE_FORMAT(CURDATE(),GET_FORMAT(DATE, 'USA'))

4.流程控制函数

• 流程处理函数可以根据不同的条件，执行不同的处理流程，可以在SQL语句中实现不同的条件选择。 MySQL中的流程处理函数主要包括IF()、IFNULL()和CASE()函数。

4.1SQL语句举例

• IF(value,value1,value2）

SELECT last_name,salary,if(salary>=6000,'高工资','低工资')
FROM employees;

SELECT last_name,commission_pct,IF(commission_pct IS NOT NULL,commission_pct,0)
FROM employees;

• IFNULL(value1, value2)

# IFNULL(expr1,expr2):可以看作是if的特殊情况，只能判断null的情况
SELECT last_name,commission_pct,IFNULL(commission_pct,0) datails
FROM employees;

• CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2… [ELSE resultn] END（相当于Java的if…else if…else…）

# CASE WHEN... THEN... WHEN... THEN...WHEN... THEN...
SELECT last_name,salary,CASE 
WHEN salary>=15000 THEN '高薪'
WHEN salary>=10000 THEN '潜力股'
WHEN salary>=8000 THEN '白领'
ELSE '草根'
END
FROM employees;

• CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值1 THEN值1 … [ELSE 值n] END（相当于Java的switch…case…）

练习题1：

# CASE WHEN... THEN... WHEN... THEN...WHEN... THEN...
-- 练习1：查询部门号为 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.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3ELSE salary * 1.4 END 'details'
FROM employees;

练习题2：

# CASE WHEN... THEN... WHEN... THEN...WHEN... THEN...
-- 练习2：查询部门号为 10,20, 30 的员工信息, 
-- 若部门号为 10, 则打印其工资的 1.1 倍, 
-- 20 号部门, 则打印其 工资的 1.2 倍, 
-- 30 号部门打印其工资的 1.3 倍数,
SELECT employee_id,last_name,department_id,salary,CASE department_id WHEN 10 THEN salary * 1.1WHEN 20 THEN salary * 1.2WHEN 30 THEN salary * 1.3END 'details'
FROM employees
WHERE department_id IN(10,20,30);

5.加密与解密函数（了解）

• 加密与解密函数主要用于对数据库中的数据进行加密和解密处理，以防止数据被他人窃取。这些函数在保证数据库安全时非常有用

注：可以看到，ENCODE(value,password_seed)函数与DECODE(value,password_seed)函数互为反函数

注意：MySQL8.0弃用password函数和ENCODE和DECODE

（md5解密）

6.MySQL信息函数

• MySQL中内置了一些可以查询MySQL信息的函数，这些函数主要用于帮助数据库开发或运维人员更好地 对数据库进行维护工作

7. 其他函数

• MySQL中有些函数无法对其进行具体的分类，但是这些函数在MySQL的开发和运维过程中也是不容忽视的