【零基础学Mysql】常用函数讲解,提升数据操作效率的利器

以耳倾听世间繁华，以语表达心中所想
大家好,我是whisperrrr.

前言：

大家好，我是你们的朋友whisrrr。在日常工作中，MySQL作为一款广泛使用的开源关系型数据库，其强大的功能为我们提供了便捷的数据存储和管理手段。而在使用MySQL的过程中，掌握一些常用函数对于提高数据库操作效率具有重要意义。
本文将围绕以下几个方面进行讲解：合计函数，字符串函数、数学函数、日期和时间函数等。相信通过学习这些常用函数，你会对MySQL有更深入的了解，为今后的数据库操作打下坚实基础。

文章目录

• • 一.合计函数/统计
  • • ①Count函数
    • ②Sum函数
    • ③Avg函数
    • ④Max/Min函数
    • ⑤group by
    • ⑥having
  • 二.字符串函数
  • • ①charset(str)函数
    • ②concat(str1,str2,…)函数
    • ③instr(string,substring)
    • ④ucase(string2)函数/lcase(string)
    • ⑤left(string2,length)函数
    • ⑥length(string)函数
    • ⑦replace(str,子串,另一个字符串)函数
    • ⑧strcmp(string1,string2)函数
    • ⑨substr(str,start,len)函数
    • ⑩trim(string)函数
    • ?函数案列演示
  • 三.数学函数
  • 四.日期函数
  • • ①日期格式
    • ②补充时间格式符含义表
    • ③current_data()
    • ④current_time()
    • ⑤current_timestramp()
    • ⑥date_add(日期,interval num 时间)函数
    • ⑦datadiff(unit,start_date,end_date)函数
    • ⑧now()函数
    • ⑨last_day()函数
    • ⑩获取日期和时间中的年、月、日、时、分、秒
  • 五.加密函数
  • • ①md5(str)函数
    • ②password(str)函数
  • 六.流程控制函数
  • • ①IF(expr1,expr2,expr3)
    • ②IFNULL(expr1,expr2)
    • ③SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END

一.合计函数/统计

①Count函数

返回要查询的结果一共有多少行
演示如下:

select count(*) | count(列名) from table_name [where where_definition];

count(*) :统计返回条件的记录行数.
count(列):返回满足条件的某列有多少个,但是会排除为null的列.

②Sum函数

放回满足where条件的列的和,一般使用在数值列;
演示如下:

select sum(列名) from table_name [where where_definition];

③Avg函数

返回满足where条件的列的平均值，一般使用在数组列。
演示如下:

select avg(列名) from table_name [where where_definition];

④Max/Min函数

返回满足where条件的一列的最大值/最小值.
演示如下:

select Max(列名) | Min(列名) from table_name [where where_definition];

⑤group by

使用 group by 对字句进行分组

⑥having

使用 having 子句对分组后的结果进行过滤, group by 和 having 结合使用.

分组查询案例:

# 演示 group by + having
-- having 子句用于限制分组显示结果. 
-- 如何显示每个部门的平均工资和最高工资
-- 分析: avg(sal) max(sal)
-- 按照部分来分组查询
SELECT AVG(sal), MAX(sal) , deptnoFROM emp GROUP BY deptno; -- 使用数学方法，对小数点进行处理
SELECT FORMAT(AVG(sal),2), MAX(sal) , deptnoFROM emp GROUP BY deptno; 
-- 显示每个部门的每种岗位的平均工资和最低工资
-- 分析 1. 显示每个部门的平均工资和最低工资
-- 2. 显示每个部门的每种岗位的平均工资和最低工资
SELECT AVG(sal), MIN(sal) , deptno, jobFROM emp GROUP BY deptno, job; 
-- 显示平均工资低于 2000 的部门号和它的平均工资 // 别名
-- 分析 [写 sql 语句的思路是化繁为简,各个击破]
-- 1. 显示各个部门的平均工资和部门号
-- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000
-- 3. 使用别名进行过滤
SELECT AVG(sal), deptnoFROM emp GROUP BY deptnoHAVING AVG(sal) < 2000; 
-- 使用别名
SELECT AVG(sal) AS avg_sal, deptnoFROM emp GROUP BY deptnoHAVING avg_sal < 2000;

二.字符串函数

接下来,跟随笔者,我们学习一下常用的字符串函数

①charset(str)函数

返回字串字符集

②concat(str1,str2,…)函数

将字符串拼接，通过输入的参数str1、str2等，将他们拼接成一个字符串。

③instr(string,substring)

返回substring，在string中出现的位置，没有的话返回0

④ucase(string2)函数/lcase(string)

将字符串转为大写/将字符串转为小写.

⑤left(string2,length)函数

从string2中的左边取出length个字符

⑥length(string)函数

获取参数值的字节个数

对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；

对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；

⑦replace(str,子串,另一个字符串)函数

将字符串str中的字串，替换为另一个字符串

⑧strcmp(string1,string2)函数

比较字符串的大小,如果一样,返回0;前大后小放回1;前小后大返回-1.

⑨substr(str,start,len)函数

str为输入字符串，从start位置开始截取字符串，len表示要截取的长度； 没有指定len长度：表示从start开始起，截取到字符串末尾。指定了len长度：表示从start开始起，截取len个长度。

⑩trim(string)函数

去掉字符串前后的空格； 该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格。

函数案列演示

-- CHARSET(str) 返回字串字符集
SELECT CHARSET(ename) FROM emp; 
-- CONCAT (string2 [,... ]) 连接字串, 将多个列拼接成一列
SELECT CONCAT(ename, ' 工作是 ', job) FROM emp; 
-- INSTR (string ,substring ) 返回 substring 在 string 中出现的位置,没有返回 0
-- dual 亚元表, 系统表 可以作为测试表使用
SELECT INSTR('hanshunping', 'ping') FROM DUAL; 
-- UCASE (string2 ) 转换成大写
SELECT UCASE(ename) FROM emp; 
-- LCASE (string2 ) 转换成小写
SELECT LCASE(ename) FROM emp; 
-- LEFT (string2 ,length )从 string2 中的左边起取 length 个字符
-- RIGHT (string2 ,length ) 从 string2 中的右边起取 length 个字符
SELECT LEFT(ename, 2) FROM emp; -
- LENGTH (string )string 长度[按照字节]
SELECT LENGTH(ename) FROM emp; 
-- REPLACE (str ,search_str ,replace_str )
-- 在 str 中用 replace_str 替换 search_str
-- 如果是 manager 就替换成 经理
SELECT ename, REPLACE(job,'MANAGER', '经理') FROM emp;
-- STRCMP (string1 ,string2 ) 逐字符比较两字串大小
SELECT STRCMP('hsp', 'hsp') FROM DUAL; -- SUBSTRING (str , position [,length ])
-- 从 str 的 position 开始【从 1 开始计算】,取 length 个字符
-- 从 ename 列的第一个位置开始取出 2 个字符
SELECT SUBSTRING(ename, 1, 2) FROM emp; -- LTRIM (string2 ) RTRIM (string2 ) TRIM(string)
-- 去除前端空格或后端空格
SELECT LTRIM(' 零基础学Mysql') FROM DUAL;
SELECT RTRIM('零基础学Mysq ') FROM DUAL;
SELECT TRIM(' 零基础学Mysq ') FROM DUAL;

三.数学函数

-- 演示数学相关函数
-- ABS(num) 绝对值
SELECT ABS(-10) FROM DUAL; 
-- BIN (decimal_number )十进制转二进制
SELECT BIN(10) FROM DUAL; 
-- CEILING (number2 ) 向上取整, 得到比 num2 大的最小整数
SELECT CEILING(-1.1) FROM DUAL;
-- CONV(number2,from_base,to_base) 进制转换
-- 下面的含义是 8 是十进制的 8, 转成 2 进制输出
SELECT CONV(8, 10, 2) FROM DUAL;
-- 下面的含义是 8 是 16 进制的 8, 转成 2 进制输出
SELECT CONV(16, 16, 10) FROM DUAL; 
-- FLOOR (number2 ) 向下取整,得到比 num2 小的最大整数
SELECT FLOOR(-1.1) FROM DUAL; 
-- FORMAT (number,decimal_places ) 保留小数位数(四舍五入)
SELECT FORMAT(78.125458,2) FROM DUAL; 
-- HEX (DecimalNumber ) 转十六进制
-- LEAST (number , number2 [,..]) 求最小值
SELECT LEAST(0,1, -10, 4) FROM DUAL; 
-- MOD (numerator ,denominator ) 求余
SELECT MOD(10, 3) FROM DUAL; 
-- RAND([seed]) RAND([seed]) 返回随机数 其范围为 0 ≤ v ≤ 1.0
-- 说明
-- 1. 如果使用 rand() 每次返回不同的随机数 ，在 0 ≤ v ≤ 1.0
-- 2. 如果使用 rand(seed) 返回随机数, 范围 0 ≤ v ≤ 1.0, 如果 seed 不变，
-- 该随机数也不变了
SELECT RAND() FROM DUAL;

四.日期函数

日期的含义：指的是我们常说的年、月、日。

时间的含义：指的是我们常说的时、分、秒。

year：年份、month：月份、day：天、hour：小时、minute 分钟、second：秒、microsecond：微秒、week：周数、quarter：季度

①日期格式

DATE_FORMAT("20000101", '%Y-%m-%d') -- 2020-01-01
DATE_FORMAT("2000-01-01", '%Y-%m-%d') -- 2020-01-01
DATE_FORMAT('2000-05-07 05:06:07', '%H:%i:%s') -- 05:06:07 (24小时制)
DATE_FORMAT('2000-05-07 05:06:07', '%h:%i:%s') -- 05:06:07 (12小时制)
DATE_FORMAT('2000-05-07 05:06:07', '%Y-%m-%d %H:%i:%s') -- 2000-05-07 05:06:07

②补充时间格式符含义表

序号

格式符

含义

1

%Y

四位的年份

2

%y

2位的年份

3

%m

月份（01,02，…11,12）

4

%c

月份（1,2,3…11,12）

5

%d

日（01,02，…）

6

%H

小时（24小时）

7

%h

小时（12小时）

8

%i

分钟（00,01，…59）

9

%s

秒（00,01，…59）

③current_data()

该函数返回当前日期
演示案例:

-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_DATE() FROM DUAL;

④current_time()

该函数返回当前时间
演示案例:

-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_time() FROM DUAL;

⑤current_timestramp()

该函数返回当前时间戳
演示案例:

-- CURRENT_DATE ( ) 当前日期
SELECT CURRENT_TIMESTAMP() FROM DUAL;

⑥date_add(日期,interval num 时间)函数

向前、向后偏移日期和时间，正号为向后，负号为向前，除此之外还有hour（小时），minute（分钟），second（秒）
演示案例:

select data_add(now(),interval 1 year) from dual;

⑦datadiff(unit,start_date,end_date)函数

返回两个时间相差的天数
演示案例:

SELECT DATEDIFF('2011-11-11', '1990-01-01') FROM DUAL;

⑧now()函数

返回当前系统的日期和时间

演示如下：

select now() as 当前时间 from dual;

⑨last_day()函数

提取某个月最后一天的日期

SELECT last_day(now()) FROM DUAL;

⑩获取日期和时间中的年、月、日、时、分、秒

获取年份：year()

获取月份:month()

获取日:day()

获取小时:hour()

获取分钟:minute()

获取秒数:second()
演示案例:

SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2013-11-10') FROM DUAL;

五.加密函数

①md5(str)函数

对密码进行加密
演示案例:

select MD5('12345') from dual;

结果:

②password(str)函数

从原文密码str计算并返回密码字符串,通常用于对mysql数据库的用户密码进行加密
演示案例:

select password('12345') from dual;

结果:

六.流程控制函数

对于以上问题,我们引入流程控制函数.

①IF(expr1,expr2,expr3)

如果 expr1 为 True ,则返回 expr2 否则返回 expr3

SELECT IF(TRUE, '北京', '上海') FROM DUAL;

②IFNULL(expr1,expr2)

如果 expr1 不为空 NULL,则返回 expr1,否则返回 expr2

SELECT IFNULL( NULL, '零基础学mysql') FROM DUAL;

③SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END

[类似多重分支.]
如果 expr1 为 TRUE,则返回 expr2,如果 expr2 为 t, 返回 expr4, 否则返回 expr5

---

结尾：

通过本文的介绍，相信大家对MySQL常用函数有了更加全面的了解。掌握这些函数，不仅能够提高我们的工作效率，还能使我们的数据库操作更加灵活。当然，MySQL的函数远不止本文所提到的这些，大家在日常学习和工作中，还需不断探索和积累。

希望这篇文章能为你带来帮助，如有疑问或建议，欢迎在评论区留言交流。最后，感谢大家的阅读，祝大家技能不断提升，工作顺利！

以耳倾听世间繁华，以语表达心中所想
感谢友友们的阅读,咱们下期再见