【零基础学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) , deptno FROM emp GROUP BY deptno; -- 使用数学方法，对小数点进行处理 SELECT FORMAT(AVG(sal),2), MAX(sal) , deptno FROM emp GROUP BY deptno; -- 显示每个部门的每种岗位的平均工资和最低工资 -- 分析 1. 显示每个部门的平均工资和最低工资 -- 2. 显示每个部门的每种岗位的平均工资和最低工资 SELECT AVG(sal), MIN(sal) , deptno, job FROM emp GROUP BY deptno, job; -- 显示平均工资低于 2000 的部门号和它的平均工资 // 别名 -- 分析 [写 sql 语句的思路是化繁为简,各个击破] -- 1. 显示各个部门的平均工资和部门号 -- 2. 在 1 的结果基础上，进行过滤，保留 AVG(sal) < 2000 -- 3. 使用别名进行过滤 SELECT AVG(sal), deptno FROM emp GROUP BY deptno HAVING AVG(sal) < 2000; -- 使用别名 SELECT AVG(sal) AS avg_sal, deptno FROM emp GROUP BY deptno HAVING 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的函数远不止本文所提到的这些，大家在日常学习和工作中，还需不断探索和积累。

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

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