齐全且实用的MySQL函数使用大全

目录

一、MySQL函数介绍

二、MySQL函数分类

（一）单行函数

①字符串函数

②数学函数

③日期函数

④流程控制函数

⑤系统信息函数

⑥其他函数

（二）聚合函数

三、函数使用示例

（一）字符函数

①length(str)函数

②concat(str1,str2,...)函数

③upper(str)、lower(str)函数

④substr(str,start,len)函数

⑤instr(str,要查找的子串)函数

③trim(str)函数

⑧lpad(str,len,填充字符)、rpad(str,len,填充字符)函数

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

（二）数学函数

①round(x,保留位数)函数

②ceil(x)函数

③floor(x)函数

④truncate(x,D)函数

⑤mod(被除数,除数)函数

⑥pow(x,D)函数

 （三）时间与日期函数

①时间与日期函数含义

②now()函数

③curdate()函数

④curtime()函数

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

⑥weekofyear()函数

⑦ quarter()函数

⑧ str_to_date()函数

⑨date_format()函数

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

⑪last_day()函数

⑫datediff(end_date,start_date)函数

⑬timestampdiff(unit,start_date,end_date)函数

（四） 流程控制函数

①if(expr,v1,v2)函数

②ifnull()函数

③case…when函数的三种用法

（五）系统信息函数

①version()函数

②connection_id()函数

③processlist

database()%2Cschema()-toc" style="margin-left:80px;">④database(),schema()函数

⑤user(),current_user(),system_user()函数

⑥charset()函数

⑦collation()函数

（六） 其他函数

①FORMAT(x,y)函数

② I***_ATON(ip)函数

③I***_NTOA(NUM)函数

④password(str)函数

⑤md5(str)函数

⑥encode(str，pswd_str)、decode(加密的字符串,pswd_str)函数

（七）聚合函数

①功能与分类

②聚合函数的传入参数，数据类型

一、MySQL函数介绍

MySQL是 MySQL 数据库提供的内部函数，这些内部函数可以帮助用户更加方便地处理表中的数据。函数就像预定的公式一样存放在数据库里，每个用户都可以调用已经存在的函数来完成某些功能。

同时MySQL数据库还支持用户自己建立函数，以适应实际的业务操作。正确使用函数会让读者在编写 SQL 语句时起到事半功倍的效果。

二、MySQL函数分类

（一）单行函数

①字符串函数

主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。

②数学函数

主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。

③日期函数

主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。

④流程控制函数

主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句

⑤系统信息函数

主要用于获取 MySQL 数据库的系统信息。其中包括获取数据库名的函数、获取当前用户的函数和获取数据库版本的函数等。

⑥其他函数

主要包括格式化函数和锁函数等。

（二）聚合函数

AVG（平均值）函数：返回指定组的平均值，空值会被忽略。

COUNT（统计）函数：返回指定组中项目的总数量。

MAX（最大值）函数：返回指定数据的最大值。

MIN（最小值）函数：返回指定数据的最小值。

SUM（求和）函数：返回指定数据的和，只能用于数字列，空值会被忽略。

三、函数使用示例

（一）字符函数

①length(str)函数

获取参数值的字节个数

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

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

演示如下：

select length('关注作者') as 长度;

②concat(str1,str2,...)函数

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

演示如下：

select concat('作者','-','Sheenky') as 作者信息;

③upper(str)、lower(str)函数

upper(str):将字符中的所有字母变为大写

lower(str)将字符中的所有字母变成小写

演示如下：

select upper('sheenky') as 大写;
select lower('SHEENKY') as 小写;

④substr(str,start,len)函数

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

select substr('学SQL就关注博主sheenky',2,8) as out_put;

注：2是开始位置，此处起始位置为‘学’所对应的位置是1，这和编程语言中的0为起始位置不一样。

此外8是开始到结束位置的长度，并不是索引的结束位置，这很容易和Python的索引弄错。

⑤instr(str,要查找的子串)函数

返回子串第一次出现的索引，如果找不到，返回0； 当查找的子串存在于字符串中：返回该子串在字符串中【第一次】出现的索引。当查找的子串不在字符串中：返回0。

演示如下：

select instr('学SQL就关注博主sheenky','关注') as 第一次出现;

③trim(str)函数

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

演示如下：

select trim(' 学SQL就 关注博主 sheenky ') as 空格去除;

 ⑧lpad(str,len,填充字符)、rpad(str,len,填充字符)函数

lpad（左填充）：用指定的字符，实现对字符串左填充指定长度

rpad（右填充）：用指定的字符，实现对字符串右填充指定长度

select lpad('编程秃头',10,'tu') as out_put;
select rpad('编程秃头',10,'tu') as out_put;

 注：这里的填充len指的是用填充字符填充后的总长度，也就是若你的len选择5你的字符串含有位置为4则只能填充一个字符，也就是填充字符的第一个字符。

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

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

演示如下：

select replace(NAME,'符兴','符强') as 替换之后 from tb_teacher;

 注：replace()方法只能查找指定列，而不能使用全局查找,想要全局查找替换，可以试试循环语句。

（二）数学函数

①round(x,保留位数)函数

四舍五入； 当对正数进行四舍五入：按照正常的计算方式，四舍五入即可。当对负数进行四舍五入：先把符号丢到一边，对去掉负号后的正数进行四舍五入，完成以后，再把这个负号，补上即可。

演示如下：

select round(1.595658,3) as out_put;

②ceil(x)函数

向上取整，返回>=该参数的最小整数。求的是大于等于这个数字的最小整数

演示如下：

select ceil(1.9) as out_put;
select ceil(1.1) as out_put;

③floor(x)函数

向下取整，返回<=该参数的最大整数，求的是小于等于这个数字的最大整数。

演示如下：

select floor(1.99) as out_put;
select floor(1.1) as out_put;

④truncate(x,D)函数

此函数叫截断函数，顾名思义就是就是截取不要的部分，然后删掉（断掉）它。在小数点的D位置处，截取数字直接删去数字，若在左边就是位置取整不使用任何法则。

这个函数理解起来也不难，我们把truncate当作小数点（.）x是要截取的数字。D为正数时是小数点的右侧部分，D为0时则不要小数部分，D为负数时是小数点左边部分，具体使用看例子演示。

演示如下：

select truncate(314159.2673525,5) as 截取之后;
select truncate(314159.2673525,0) as 截取之后;
select truncate(314159.2673525,-4) as 截取之后;

 ⑤mod(被除数,除数)函数

取余； 当被除数为正数，结果就是正数。当被除数为负数，结果就是负数。

演示如下：

select mod(10,3) as out_put;

⑥pow(x,D)函数

此函数是用于计算指数函数，x为底，D为指数

演示如下：

select pow(5,2) as 平方运算;

 （三）时间与日期函数

①时间与日期函数含义

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

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

补充时间格式符含义表

②now()函数

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

演示如下：

select now() as 当前时间;

③curdate()函数

只返回系统当前的日期，不包含时间

演示如下：

select surdate() as 当前日期;

 ④curtime()函数

只返回当前的时间，不包含日期

演示如下：

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

获取年份：year()

获取月份:month()

获取日:day()

获取小时:hour()

获取分钟:minute()

获取秒数:second()

⑥weekofyear()函数

获取当前时刻所属周数

演示如下：

⑦ quarter()函数

获取当前时刻所属的季度

⑧ str_to_date()函数

将日期格式转换为字符串，转换成指定格式的日期

⑨date_format()函数

将日期转换成日期字符串

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

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

⑪last_day()函数

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

 ⑫datediff(end_date,start_date)函数

计算两个时间相差的天数

演示如下：

 ⑬timestampdiff(unit,start_date,end_date)函数

计算两个时间返回的年/月/天数；

unit参数是确定(start_date,end_date)结果的单位，表示为整数，以下是有效单位：

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

（四） 流程控制函数

①if(expr,v1,v2)函数

实现if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2

演示如下

 ②ifnull()函数

判断值是否为null，是null用指定值填充；如果v1不为NULL，返回v2。否则返回v1

 ③case…when函数的三种用法

1.等值判断：可以实现多条件的查询值赛选；

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

2.区间判断：类似于python中if-elif-else的效果；

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
...
else 要显示的值n或语句n
end

3.case … when和聚合函数联用；

此处的实例，作者引用网上的一个例子进行举例

 得出如下结果：

（五）系统信息函数

①version()函数

查看MySQL系统版本信息号

②connection_id()函数

查看当前登入用户的连接次数数

直接调用CONNECTION_ID()函数--不需任何参数--就可以看到当下连接MySQL服务器的连接次数，不同时间段该函数返回值可能是不一样的

③processlist

查看用户的连接信息

Id列：登录MySQL的用户标识，是系统自动分配的CONNECTION ID；

User列：显示当前的“用户名”；

Host列：显示执行这个语句的IP，用来追踪出现问题语句的用户；

db列：显示这个进程目前连接的是哪个数据库；

***mand列：显示当前连接执行的命令，一般是休眠（Sleep）、查询（Query）、连接（Connect）；

Time列：显示这个状态持续的时间，单位是秒；

State列：显示使用当前连接的SQL语句的状态，包含有：Copying to tmptable、Sorting result、Sending data等状态；

Info列：显示当前SQL的内容，如果语句过长可能无法显示完全。

 ④database(),schema()函数

查看当前使用的数据库

 ⑤user(),current_user(),system_user()函数

获取当前用户

⑥charset()函数

使用CHARSET()函数返回字符串使用的字符集

 ⑦collation()函数

使用COLLATION()函数返回字符串排列方式

（六） 其他函数

①FORMAT(x,y)函数

把x格式化为以逗号隔开的数字序列，y是结果的小数位数。

② I***_ATON(ip)函数

返回IP地址的数字表示

③I***_NTOA(NUM)函数

返回数字所代表的IP地址。

 ④password(str)函数

密码加密函数，str为NULL，返回NULL。
password在MySQL服务器鉴定系统中使用。不应该用在个人的应用程序中。
加密是单向的（不可逆），加密后的密码保存到用户权限表中。
执行密码加密与UNIX中密码加密方式不同。

⑤md5(str)函数

加密函数;

参数为字符串，该函数为字符串算出一个MD5 128比特校验和
返回值以32位16进制数字的二进制字符串形式返回
str为NULL，返回NULL

 ⑥encode(str，pswd_str)、decode(加密的字符串,pswd_str)函数

加密：encode(被加密的密码，密码);

解密：decode(encode(被加密的密码，密码),密码);   //也可以用上面返回的二进制字符串

（七）聚合函数

①功能与分类

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类：sum 求和avg 平均值max 最大值min 最小值count 计算个数

 ②聚合函数的传入参数，数据类型

1、sum()函数和avg()函数：传入整型/小数类型才有意义；

2、sum()函数和avg()函数对于字符串类型、日期/时间类型的计算都没有太大意义。因此，sum()函数和avg()函数，我们只用来对小数类型和整型进行求和。跳过空值行。

3、max()函数和min()函数：传入整型/小数类型、日期/时间类型意义较大

4、可以传入任何数据类型，但是碰到null要注意，空值跳过，不计数。

注;sum()/count(*)方法计算平均值时，有时候得到的结果和AVG()函数不一定一样。当存在某计算列空值但其他列不是空值的时候就会出现不一样的结果，因为分母count(*)并没有跳过空值列。