MySQL_07：单行函数

一、函数的基本理解

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
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

---

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
2
3
4
5
6
7
8

---

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
2
3
4
5
6
7

---

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
2
3
4
5
6
7
8
9
10
11

---

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;
1
2
3
4

---

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;
1
2
3
4

• 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;
1
2
3
4

• 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');
1
2

3.日期和时间函数

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

SQL语句测试

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

3.2日期与时间戳的转换

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

SQL语句测试:

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;
1
2
3
4

3.4日期的操作函数

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

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

sql语句举例

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

3.5时间和秒针转换的函数

SQL语句测试：

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

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;
1
2
3
4
5
6
7

• 第二部分

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;
1
2
3
4
5
6
7

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');
1
2
3
4

• 解析：字符串—>日期

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

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

---

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

SELECT GET_FORMAT(DATE, 'USA')
1

• GET_FORMAT和DATE_FORMAT的巧用

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

4.流程控制函数

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

4.1SQL语句举例

• IF(value,value1,value2）

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

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

• IFNULL(value1, value2)

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

• 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;
1
2
3
4
5
6
7
8

• 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.1
                                                                     WHEN 20 THEN salary * 1.2
                                                                     WHEN 30 THEN salary * 1.3
                                                                     ELSE salary * 1.4 END 'details'
FROM employees;
1
2
3
4
5
6
7
8
9
10
11

练习题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.1
                                                                     WHEN 20 THEN salary * 1.2
                                                                     WHEN 30 THEN salary * 1.3
                                                                     END 'details'
FROM employees
WHERE department_id IN(10,20,30);
1
2
3
4
5
6
7
8
9
10
11

---

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

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

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

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

（md5解密）

6.MySQL信息函数

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

7. 其他函数

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