MySQL常用函数介绍

前言

MySQL数据库中提供了很丰富的函数比如我们常用的聚合函数日期及字符串处理函数等。SELECT语句及其条件表达式都可以使用这些函数函数可以帮助用户更加方便的处理表中的数据使MySQL数据库的功能更加强大。本篇文章主要为大家介绍几类常用函数的用法。

1.聚合函数

聚合函数是平时比较常用的一类函数这里列举如下

• COUNT(col)   统计查询结果的行数
• MIN(col)   查询指定列的最小值
• MAX(col)   查询指定列的最大值
• SUM(col)   求和返回指定列的总和
• AVG(col)   求平均值返回指定列数据的平均值

2.数值型函数

数值型函数主要是对数值型数据进行处理得到我们想要的结果常用的几个列举如下具体使用方法大家可以试试看。

• ABS(x)   返回x的绝对值
• BIN(x)   返回x的二进制
• CEILING(x)   返回大于x的最小整数值
• EXP(x)   返回值e自然对数的底的x次方
• FLOOR(x)   返回小于x的最大整数值
• GREATEST(x1,x2,...,xn)   返回集合中最大的值
• LEAST(x1,x2,...,xn)   返回集合中最小的值
• LN(x)   返回x的自然对数
• LOG(x,y)   返回x的以y为底的对数
• MOD(x,y)   返回x/y的模余数
• PI()   返回pi的值圆周率
• RAND()   返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
• ROUND(x,y)   返回参数x的四舍五入的有y位小数的值
• TRUNCATE(x,y)   返回数字x截短为y位小数的结果

一些示例

# concat
SELECT CONCAT('Hello','MySQL');
# lower
SELECT LOWER('Hello');
# upper
SELECT UPPER('Hello');
# lpad
SELECT LPAD('01',5,'-');
# rpad
SELECT RPAD('01',5,'-');
# trim
SELECT TRIM(' Hello  World ');
# substring
SELECT SUBSTRING('Hello MySQL',1,5);


# 根据业务需求变更企业员工的工号统一为5位数目前不足5位数的全部在前面补0。比如1号员工的工号应该为00001。
UPDATE emp SET workno=LPAD(workno,5,'0');

3.字符串函数

字符串函数可以对字符串类型数据进行处理在程序应用中用处还是比较大的同样这里列举几个常用的如下

• LENGTH(s)   计算字符串长度函数返回字符串的字节长度
• CONCAT(s1,s2...,sn)   合并字符串函数返回结果为连接参数产生的字符串参数可以是一个或多个
• INSERT(str,x,y,instr)   将字符串str从第x位置开始y个字符长的子串替换为字符串instr返回结果
• LOWER(str)   将字符串中的字母转换为小写
• UPPER(str)   将字符串中的字母转换为大写
• LEFT(str,x)   返回字符串str中最左边的x个字符
• RIGHT(str,x)   返回字符串str中最右边的x个字符
• TRIM(str)   删除字符串左右两侧的空格
• REPLACE   字符串替换函数返回替换后的新字符串
• SUBSTRING   截取字符串返回从指定位置开始的指定长度的字符换
• REVERSE(str)   返回颠倒字符串str的结果

一些示例

# ceil 
SELECT CEIL(1.2);
# floor
SELECT FLOOR(1.5);
# mod
SELECT MOD(3,4);
SELECT MOD(6,4);
SELECT MOD(3,2);
# rand
SELECT RAND();
# round
SELECT ROUND(2.345,2);

# 通过数据库的函数生成一个六位数的随机验证码
SELECT RPAD(ROUND(RAND()*1000000 , 0),6,'0');

4.日期和时间函数

• CURDATE 和 CURRENT_DATE   两个函数作用相同返回当前系统的日期值
• CURTIME 和 CURRENT_TIME   两个函数作用相同返回当前系统的时间值
• NOW 和 SYSDATE   两个函数作用相同返回当前系统的日期和时间值
• UNIX_TIMESTAMP   获取UNIX时间戳函数返回一个以 UNIX 时间戳为基础的无符号整数
• FROM_UNIXTIME   将 UNIX 时间戳转换为时间格式与UNIX_TIMESTAMP互为反函数
• MONTH   获取指定日期中的月份
• MONTHNAME   获取指定日期中的月份英文名称
• DAYNAME   获取指定曰期对应的星期几的英文名称
• DAYOFWEEK   获取指定日期对应的一周的索引位置值
• WEEK   获取指定日期是一年中的第几周返回值的范围是否为 0〜52 或 1〜53
• DAYOFYEAR   获取指定曰期是一年中的第几天返回值范围是1~366
• DAYOFMONTH   获取指定日期是一个月中是第几天返回值范围是1~31
• YEAR   获取年份返回值范围是 1970〜2069
• TIME_TO_SEC   将时间参数转换为秒数
• SEC_TO_TIME   将秒数转换为时间与TIME_TO_SEC 互为反函数
• DATE_ADD 和 ADDDATE   两个函数功能相同都是向日期添加指定的时间间隔
• DATE_SUB 和 SUBDATE   两个函数功能相同都是向日期减去指定的时间间隔
• ADDTIME   时间加法运算在原始时间上添加指定的时间
• SUBTIME   时间减法运算在原始时间上减去指定的时间
• DATEDIFF   获取两个日期之间间隔返回参数 1 减去参数 2 的值
• DATE_FORMAT   格式化指定的日期根据参数返回指定格式的值
• WEEKDAY   获取指定日期在一周内的对应的工作日索引

一些示例

# curtime()
SELECT CURTIME();
# curdate()
SELECT CURDATE();
# now()
SELECT NOW();
# year,month,day
SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT DAY(NOW());
# date_add()
SELECT DATE_ADD(NOW(),INTERVAL 70 YEAR);
# datediff()
SELECT DATEDIFF('2023-10-1','2022-10-1');
# 查询所有员工的入职天数并根据入职天数倒序排序
SELECT NAME,DATEDIFF(CURDATE(),entrydate) AS d FROM emp ORDER BY d DESC;

5.流程控制函数

流程控制类函数可以进行条件操作用来实现SQL的条件逻辑允许开发者将一些应用程序业务逻辑转换到数据库后台列举如下

• IF(test,t,f)   如果test是真返回t否则返回f
• IFNULL(arg1,arg2)   如果arg1不是空返回arg1否则返回arg2
• NULLIF(arg1,arg2)   如果arg1=arg2返回NULL否则返回arg1
• CASE WHEN[test1] THEN [result1]...ELSE [default] END   如果testN是真则返回resultN否则返回default
• CASE [test] WHEN[val1] THEN [result]...ELSE [default]END   如果test和valN相等则返回resultN否则返回default

一些示例

# 流程控制函数
# if
SELECT IF(TRUE,'OK','Error');
# ifnull
SELECT IFNULL(NULL,'default');
SELECT IFNULL('','default');
SELECT IFNULL(' -','default');
# case when then else end
# 需求查询emp表的员工姓名和工作地址北京/上海---->一线城市其他---->二线城市
SELECT 
NAME,(CASE workaddress WHEN '北京' THEN '一线城市' WHEN '上海' THEN '一线城市' ELSE '二线城市' END) AS '工作地址'
FROM emp;

-- 案例统计班级各个学员的成绩展示的规则如下
-- >=85,显示优秀
-- >=60,显示及格
-- 否则显示不及格

CREATE TABLE score(
id INT,
NAME VARCHAR(5),
math INT,
english INT,
chinese INT
);
INSERT INTO score(id,NAME,math,english,chinese) VALUES(1,'Tom',67,88,95),(2,'Rose',23,66,90),(3,'Jack',56,98,76);
SELECT*FROM score;

SELECT
id,
NAME,
(CASE WHEN math>=85 THEN '优秀' WHEN math>=60 THEN '及格' ELSE '不及格' END) AS '数学',
(CASE WHEN chinese>=85 THEN '优秀' WHEN chinese>=60 THEN '及格' ELSE '不及格' END) AS '语文',
(CASE WHEN english>=85 THEN '优秀' WHEN english>=60 THEN '及格' ELSE '不及格' END) AS '英语'
FROM score;