【MySQL】一些内置函数（时间函数、字符串函数、数学函数等，学会了有妙用）

先搞面包再谈爱

已于 2024-03-13 11:49:46 修改

阅读量326

点赞数

分类专栏： MySQL 文章标签： mysql android 数据库

于 2023-11-21 16:09:44 首次发布

本文链接：https://blog.csdn.net/m0_62782700/article/details/134523166

版权

MySQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

内置函数

前言
正式开始

在这里插入图片描述

本篇博客由 CSDN@先搞面包再谈爱原创，转载请标注清楚，请勿抄袭。

前言

MySQL中内置类很多函数，这些函数可以让我们在进行数据统计、查表、建表的时候更加方便，本篇将MySQL中的内置函数分为四类，分别是：

时间函数
字符串函数
数学函数
其他函数

正式开始

时间函数

讲下面这些（先不用细看）：
在这里插入图片描述

简单介绍介绍。

首先声明一点，日期是指年月日，时间是指时分秒，日期时间是年月日时分秒。这在我前面的博客也是说过的。

显示当前日期、时间、日期时间的

current_date，当前日期，就是返回你调用这个函数时的日期:

不加括号也行，不过既然叫了函数那就最好把括号带上。

【注意】这些内置函数都要搭配着select使用，不然显示不出来：
在这里插入图片描述

关于select我就不说了，在前面博客已经详细说过了。

current_time，当前时间，就是返回你调用这个函数时的时间:

就不演示不带括号的了。

current_timestamp()，当前时间戳，时间和日期都会显示：
、
我前面在讲数据类型的时候，有个类型就叫做timestamp，默认值就是插入数据时的时间戳。

还有一个和这个时间戳很像，就是now：

其实就和current_timestamp一样的效果。

日期计算

两个，一个加一个减。

date_add，可以加年、月、日、时、分、秒。

还可以搭配前面的now来用：

会自动按照你给的类型来计算，类型不同结果也就不同。注意interval不能掉，还有类型也不能掉。

date_sub同理，就不说了。

相差多少天

date_diff，这个是计算两个日期之间相差多少天：
在这里插入图片描述

这个是用前者前去后者，故会出现负数的情况：
在这里插入图片描述

也可以嵌套调用：
在这里插入图片描述

统计工龄的时候就可以用这个函数。

时间的接口就讲这么些来两个例子。

示例

创建一张表，记录生日

生日一般精确到年月日就可以了，时分秒这里就不记录了。

创表：
在这里插入图片描述
里面的这些类型什么的我就不讲了，我前面的博客中有。

插入点数据：
在这里插入图片描述
因为id是自增的，所以不用手动插入。

再来嵌套调用：
在这里插入图片描述

能不能插入纯时分秒的呢？
在这里插入图片描述
竟然可以。

再来试一个：
在这里插入图片描述
竟然也可以。

我为啥说竟然呢？
因为我前面在讲数据类型的时候说过数据类型其实算是一种约束，MySQL对于数据匹配的要求是很严格的，但这里并没有那么严格，像隐式类型转换，把这当成例外。

其实可以理解成所有的时间函数底层用的都是一个函数，不过是要哪个显示哪个罢了。不过建议插入的时候类型为日期就用日期插，不要加时间。

留言表

平时很多软件都有评论区，评论有一个很重要的字段就是时间，比如说xxx发布于2023-10-23，其实原理就是客户点击发送，数据发送到后台服务器后，后台服务器将其转为sql语句对数据库做插入动作，这里就简单实现一个。

在这里插入图片描述

插入点数据：
在这里插入图片描述

这样就能把评论内容和评论时间拿到手。

如果显示时只想显示日期，就在sendtime外套个date就行：
在这里插入图片描述

再插入点：
在这里插入图片描述

来个问题：统计2分钟内发布过的帖子。
怎么确定表中的哪些数据是两分钟以内的？
找到当前时间的两分钟前的时间，假如说当前时间是 $t_0$ ，当前时间的2分钟前是 $t_1$ ，所以 $t_1$ 的值就是 $t_0 - 2$ ，对应图中就应该是这样：
在这里插入图片描述

此时在 $t_1 \sim t_0$ 之内的帖子就是我们想要的，那这之中的帖子在时间上有什么特点呢？假如说帖子的时间是 $t$ ，那么就是这样：
在这里插入图片描述

这里t一定是小于 $t_0$ 的，这点对于所有的帖子都会满足。

那么就好说了，只要让 $t > t_1$ 就行了，也就是 $t > t_0 - 2$ ，那么就会有两种解法：
$t > t_0 - 2$ 和 $t + 2 > t_0$

都来试一试，插入点数据：
在这里插入图片描述

然后筛选：
在这里插入图片描述

再重新插入点：
在这里插入图片描述

筛选：
在这里插入图片描述

可以看到都是ok的。

字符串函数

讲这些：
在这里插入图片描述

charset

charset，查看字符集：
在这里插入图片描述

在这里插入图片描述

再用一下我前面博客定义的一张表：
在这里插入图片描述

在这里插入图片描述

都是utf8的。因为我的配置文件搞了默认的字符集。

再显示一下数字的：
在这里插入图片描述

concat

concat，追加的函数，只要是字符串就能追加，不管字符串的内容，有点像C中的strcat，来点例子：
在这里插入图片描述

在这里插入图片描述

里面可以有多个串：
在这里插入图片描述

没啥难的。

instr(string, substring)

这个函数是用来查询substring是否在string中，如果在就会返回其在第一个字符的位置，如果不在就返回0。

例子：
在这里插入图片描述
这里1234在abcde12345efg中，1在其中的第六个位置，这里是从1开始的，不是从0开始的。

再来个不存在的：
在这里插入图片描述

有点像find_in_set，这个是用来查找某一项是否在一个集合当中，如果在会返回其在集合中的位置，如果不在会返回0。想了解一下的可以看我这篇博客：【MySQL】数据类型

ucase和lcase

这两个是用来转大小写的，u是up，转大；l是low，转小。

转大写：
在这里插入图片描述

其中不是字母的就不会管。

转小写：
在这里插入图片描述

left(string, length)

这个函数用来取string左边的length个字符。

演示：
在这里插入图片描述

那有没有right？
在这里插入图片描述
有的。

length求字符串长度

在这里插入图片描述

这里编码会导致结果有点和你想的不一样：
在这里插入图片描述

因为utf8下一个汉字占用3个字节。而且utf8还是变长的编码，一部分是1字节的，比如说我在其中加上一点英文字母：
在这里插入图片描述
其中你好占用6字节，abc占用3字节。

replace(str, search_str, replace_str)

这个函数在str中找到searche_str字符串并将searche_str替换成replace_str。

例如：
在这里插入图片描述

这里将123456789中的567替换成了你好。

trim、ltrim、rtrim

这三个用来去掉左右两边字符串的空格。

trim是两边都去掉，ltrim是只去掉左边的，rtrim是只去掉右边的。
在这里插入图片描述

有时可能因为网络传输而导致传输的字符串左右两侧出现一些空格，此时不能让这些左右两侧的空格保存在数据库中，不然如果是用户的密码的话，用户后面就没法认证了，而且如果出现了这种问题还很难查出来问题所在，因为空格不可显。此时就可以用trim剪掉两边的空格，来避免这些问题。

一些示例

下面的所有示例都要用到刚刚讲的一些内置函数。

要求显示exam_result表中的信息，显示格式：“XXX的语文是XXX分，数学XXX分，英语XXX分"

下面的两个例子，基于这张表的：
在这里插入图片描述

就是改一下打印格式，打印的时候不要像上面表中那样分格打印，而是全部拼接到一块，假如说是为了发短信要这样搞。

直接用一个concat：
在这里插入图片描述
但是这样搞出来很难看，加点东西：

这样就可以将所有的内容拼接一块发出去，没人对应一条信息。

求学生表中学生姓名占用的字节数

其实就是用length。
在这里插入图片描述

还是因为utf8中一个汉字3个字节。

再强调一下：length函数返回字符串长度，以字节为单位。如果是多字节字符则计算多个字节数；如果是单字节字符则算作一个字节。比如：字母，数字算作一个字节，中文表示多个字节数（与字符集编码有关）

将EMP表中所有名字中有S的替换成’上海’

下面的三个例子基于我前一篇中搞的这张表：
在这里插入图片描述

将EMP表中所有名字中有S的替换成’上海’，就是用replace：
在这里插入图片描述
这些内置函数并不会影响原表中的数据。

截取EMP表中ename字段的第二个到第三个字符

不能用left或者right，二者取不到中间的数，不过嵌套调用也能实现，不过比较麻烦。

用substring，这个函数前面没说，其实就和C++中string的substr一样，取子串的。
在这里插入图片描述

以首字母小写的方式显示所有员工的姓名

嵌套调用一下：
在这里插入图片描述
其中substring如果没有给第三个参数，就会将指定子串位置后面的所有子串全部返回。

用left拿到第一个字符，然后转成小写，再和后面的字符串拼接。

数学函数

这些：
在这里插入图片描述

演示一下：

abs

求绝对值的：
在这里插入图片描述

bin

求二进制的：

hex

求十六进制的：

conv(num, from_base, to_base)

指定num从from_base进制转换成to_base进制：

其实这里覆盖了前面的两个进制转换。

ceiling 和 floor

一个向上取整（ceiling，天花板的意思），一个向下取整（floor，地板）。说说啥意思。这两个都是针对小数的。

向上取整：只要有小数部分，就直接取大的值，比如说2.1，就直接直接转成3。转成最近的大的数。再比如说-2.1，那就回转成-2，转成比原数大的最近的整数，也就是-2。看这张图：

如果是2.5，向上取整就是3，如果是-2.9，线上取整为-2。
这就是向上取整。

向下取整，只要有小数，就取小的值，比如说下图中2.1，取的是2，-2.1取的是-3：

如果是-2.9，向下取整为-3，如果是2.9，向下取整为2。