MySQL_07：单行函数

最新推荐文章于 2025-09-05 17:29:15 发布

原创最新推荐文章于 2025-09-05 17:29:15 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了MySQL中的内置函数，包括数值函数、字符串函数、日期和时间函数等，并通过实例展示了如何使用这些函数来处理数据。

一、函数的基本理解

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.1
                                                                     WHEN 20 THEN salary * 1.2
                                                                     WHEN 30 THEN salary * 1.3
                                                                     ELSE 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.1
                                                                     WHEN 20 THEN salary * 1.2
                                                                     WHEN 30 THEN salary * 1.3
                                                                     END 'details'
FROM employees
WHERE department_id IN(10,20,30);