MySQL常用单行函数

小咪咪 2023-10-09 22:19 41阅读 0赞

## 一、MySQL函数介绍 ##

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

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

## 二、MySQL函数分类 ##

### （一）单行函数 ###

1、字符串函数  
主要用于处理字符串。其中包括字符串连接函数、字符串比较函数、将字符串的字母都变成小写或大写字母的函数和获取子串的函数等。  
2、数学函数  
主要用于处理数字。这类函数包括绝对值函数、正弦函数、余弦函数和获得随机数的函数等。  
3、日期函数  
主要用于处理日期和时间。其中包括获取当前时间的函数、获取当前日期的函数、返回年份的函数和返回日期的函数等。  
4、流程控制函数  
主要用于在 SQL 语句中控制条件选择。其中包括 IF 语句、CASE 语句和 WHERE 语句

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

## 三、函数使用示例 ##

### （一）字符函数 ###

#### length(str)函数 ####

获取参数值的字节个数  
对于utf-8字符集来说，一个英文占1个字节；一个中文占3个字节；  
对于gbk字符集来说，一个英文占1个字节；一个中文占2个字节；  
演示如下：  
select length(‘今天天气不错’) as 长度;

#### concat(str1,str2,…)函数 ####

将字符串拼接，通过输入的参数str1、str2等，将他们拼接成一个字符串。  
演示如下：  
select concat(‘天气预报’,‘-’,‘局部地区多云’) as 天气信息;

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

upper(str):将字符中的所有字母变为大写  
lower(str)将字符中的所有字母变成小写  
演示如下：  
select upper(‘helloworld’) as 大写;  
select lower(‘HELLOWORLD’) as 小写;

#### substr(str,start,len)函数 ####

str为输入字符串，从start位置开始截取字符串，len表示要截取的长度； 没有指定len长度：表示从start开始起，截取到字符串末尾。指定了len长度：表示从start开始起，截取len个长度。  
select substr(‘今天天气不错心情也挺好的’,2,8) as out\_put;  
注：根据输出结果分析各参数的含义

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

返回子串第一次出现的索引，如果找不到，返回0； 当查找的子串存在于字符串中：返回该子串在字符串中【第一次】出现的索引。当查找的子串不在字符串中：返回0。  
演示如下：  
select instr('今天天气不错心情也挺好的,‘天气’) as 第一次出现;

#### trim(str)函数 ####

去掉字符串前后的空格； 该函数只能去掉字符串前后的空格，不能去掉字符串中间的空格。  
演示如下：  
select trim(’ 今天天气 不错心 情也挺好的 ') 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(ENAME,‘SMITH’,‘钢弹’) as 替换之后 from emp;  
注：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 平方运算;

### （三）时间与日期函数 ###

时间与日期函数含义  
日期的含义：指的是我们常说的年、月、日。  
时间的含义：指的是我们常说的时、分、秒。  
补充时间格式符含义表

序号 格式符 含义  
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）

#### now()函数 ####

返回当前系统的日期和时间  
演示如下：  
select now() as 当前时间;

#### curdate()函数 ####

只返回系统当前的日期，不包含时间  
演示如下：  
select curdate() as 当前日期;

#### curtime()函数 ####

只返回当前的时间，不包含日期  
演示如下：  
select curtime() as 当前日期;  
获取日期和时间中的年、月、日、时、分、秒  
获取年份：year()  
获取月份:month()  
获取日:day()  
获取小时:hour()  
获取分钟:minute()  
获取秒数:second()

#### weekofyear()函数 ####

获取当前时刻所属周数  
演示如下：  
select weekofyear(now()) as 当前时间下的周数；

#### quarter()函数 ####

获取当前时刻所属的季度  
select quarter(curdate()) as 当前季度；

#### str\_to\_date()函数 ####

将字符串格式转换为日期格式，转换成指定格式的日期  
select str\_to\_date(“2023,05,05”,’%Y,%c,%d’) as 转换后格式；

#### date\_format()函数 ####

将日期转换成日期字符串  
select date\_format(“2023/5/8”,’%Y年%m月%d日’) as 转换格式；

#### date\_add(日期,interval num 时间)函数 ####

向前、向后偏移日期和时间，正号为向后，负号为向前，除此之外还有hour（小时），minute（分钟），second（秒）  
select curdate() as 当前时间,date\_add(curdate(),interval 1year) as 一年后，date\_add(curdate(),interval 1 month) as 一月后,date\_add(curdate(),interval 1 day) as 一天后；

#### last\_day()函数 ####

提取某个月最后一天的日期  
select last\_day(now()) as 当月最后一天的日期；

#### datediff(end\_date,start\_date)函数 ####

计算两个时间相差的天数  
select concat(‘我出生’,datediff(curdate(),’2000-05-01’),’天了！‘) as 出生天数;  
timestampdiff(unit,start\_date,end\_date)函数  
计算两个时间返回的年/月/天数；  
unit参数是确定(start\_date,end\_date)结果的单位，表示为整数，以下是有效单位：  
year：年份、month：月份、day：天、hour：小时、minute 分钟、second：秒、microsecond：微秒、week：周数、quarter：季度  
select timestampdiff(year,’2000-05-01’,curdate()) as 出生到现在的年数;

### （四） 流程控制函数 ###

#### if(expr,v1,v2)函数 ####

实现if-else的效果，如果expr是true，返回v1。如果expr是false，返回v2  
演示如下  
select score,if(score<60,’不及格’,’合格’) as 备注 from 成绩表;

#### ifnull()函数 ####

判断值是否为null，是null用指定值填充；如果v1不为NULL，返回v2。否则返回v1  
select ifnull(null,1),ifnull(1,2);

#### case…when函数的三种用法 ####

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

case 要判断的字段或表达式  
when 常量1 then 要显示的值1或语句1  
when 常量2 then 要显示的值2或语句2  
…  
else 要显示的值n或语句n  
end  
演示案例：  
select job,  
case job  
when ‘CLERK’ then ‘职员’  
when ‘ANALYST’ then ‘分析师’  
else ‘其他’  
end as 岗位备注  
from emp

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

case  
when 条件1 then 要显示的值1或语句1  
when 条件2 then 要显示的值2或语句2  
…  
else 要显示的值n或语句n  
end  
演示案例：  
select sal ,  
case sal  
when sal<1000 then ‘基本生活’  
when sal>=100 and sal<=2000 then ‘小资生活’  
else ‘小康’  
end as 工资情况  
from emp

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

![在这里插入图片描述][eb859359219a4c07b1d082c6dde40997.png]  
得出如下结果：  
![在这里插入图片描述][22cc58887d5a40d1a8a6d94aec2524fa.png]

### （五）系统信息函数 ###

#### version()函数 ####

查看MySQL系统版本信息号  
select version();

#### connection\_id()函数 ####

查看当前登入用户的连接次数数  
直接调用CONNECTION\_ID()函数–不需任何参数–就可以看到当下连接MySQL服务器的连接次数，不同时间段该函数返回值可能是不一样的  
select connection\_id();

#### processlist ####

查看用户的连接信息  
show processlist;  
Id列：登录MySQL的用户标识，是系统自动分配的CONNECTION ID；  
User列：显示当前的“用户名”；  
Host列：显示执行这个语句的IP，用来追踪出现问题语句的用户；  
db列：显示这个进程目前连接的是哪个数据库；  
Command列：显示当前连接执行的命令，一般是休眠（Sleep）、查询（Query）、连接（Connect）；  
Time列：显示这个状态持续的时间，单位是秒；  
State列：显示使用当前连接的SQL语句的状态，包含有：Copying to tmptable、Sorting result、Sending data等状态；  
Info列：显示当前SQL的内容，如果语句过长可能无法显示完全。

#### database(),schema()函数 ####

查看当前使用的数据库  
select database(),schema();

#### user(),current\_user(),system\_user()函数 ####

获取当前用户  
select user(),current\_user(),system\_user();

#### charset()函数 ####

使用CHARSET()函数返回字符串使用的字符集  
select charset(‘haha’);

#### collation()函数 ####

使用COLLATION()函数返回字符串排列方式  
select connation(‘abc’);

### （六） 其他函数 ###

#### FORMAT(x,y)函数 ####

把x格式化为以逗号隔开的数字序列，y是结果的小数位数。  
select format(1235.234564,2);

#### INET\_ATON(ip)函数 ####

返回IP地址的数字表示  
select inet\_aton(’192.168.1.11’);

#### INET\_NTOA(NUM)函数 ####

返回数字所代表的IP地址。  
select inet\_ntoa(175790383);

#### password(str)函数 ####

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

#### md5(str)函数 ####

加密函数;  
参数为字符串，该函数为字符串算出一个MD5 128比特校验和  
返回值以32位16进制数字的二进制字符串形式返回  
str为NULL，返回NULL  
select md5(‘mypassword‘);

#### encode(str，pswd\_str)、decode(加密的字符串,pswd\_str)函数 ####

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

[eb859359219a4c07b1d082c6dde40997.png]: https://img-blog.csdnimg.cn/eb859359219a4c07b1d082c6dde40997.png
[22cc58887d5a40d1a8a6d94aec2524fa.png]: https://img-blog.csdnimg.cn/22cc58887d5a40d1a8a6d94aec2524fa.png