Mysql常用函数及技巧

立小言先森

于 2024-06-22 11:34:40 发布

阅读量871

点赞数 12

分类专栏：【每日扫盲】【数据库系列】文章标签： mysql round substr concat str_to_date date_format date_add

本文链接：https://blog.csdn.net/yaomingyang/article/details/139879739

版权

【数据库系列】同时被 2 个专栏收录

77 篇文章 12 订阅

订阅专栏

【每日扫盲】

18 篇文章 0 订阅

订阅专栏

开源项目SDK：https://github.com/mingyang66/spring-parent
个人文档：https://mingyang66.github.io/raccoon-docs/#/

一、round()函数

round()函数用于将数字四射侮辱到指定的小数位，语法如下：

round(number, decimals)

number：要四舍五入的数字。
decimals：要保留的小数位数，如果省略此参数，则会四舍五入到最接近的整数。

示例：

SELECT ROUND(123.4567); //123
SELECT ROUND(123.4567, 2); //123.46

二、lpad和rpad字符串函数

用于在字符串左侧和右侧填充指定的字符，知道达到指定的长度

lpad(str, len, padStr) 函数str是原始字符串，len是返回的字符串的长度，padStr是要填充的字符串，填充从左侧开始。
rpad(str, len, padStr) 函数str是原始字符串，len是返回的字符串的长度，padStr是要填充的字符串，填充从右侧开始。

示例如下：

select
	lpad(f.time, 6, '0'),
	rpad(f.time, 6, '0') 
from
	fund f

三、lengh()和char_length()函数

lenght()函数在mysql中用于返回字符串的从航都，它返回的是字符串的字节数，而不是字符数，如果是UTF-8编码的多字节字符串，那么一个字符可能栈多个字节，如：汉字

示例：

SELECT length('123SD');//5
SELECT length('资金明细');//12
SELECT length(null);//null

如果数据库中使用的是多字节字符集（如：汉字），并且是想获取字符数而不是字节数，那就使用char_length()函数，示例如下：

select char_length('123SD');//5
select char_length('资金明细');//4 
select char_length(null);//null

在使用length和char_length的时候如果是null则会返回null，可以使用ifnull或coalesce函数来处理。

四、substring和substr函数

mysql中的substring和substr函数是等价的，它们用于从一个字符串中提取子字符串。

语法如下：

substring(str, pos, len)
substr(str, pos, len)

str：要从中提取子字符串的原始字符串。
pos：开始提起的位置（基于1的索引）。
len：（可选）要提取的字符串，如果省略，则返回从pos到字符串末尾的所有字符串。

示例如下：

select substring('2024061100100000', 1, 10); //2024061100
select substring('2024061100100000', 11, 6); //100000
select substring('2024061100100000', -6, 3);//100
select substring('2024061100100000', -6, -3);//空字符串

select substr('2024061100100000', 1, 10); //2024061100
select substr('2024061100100000', 11, 6); //100000
select substr('2024061100100000', -2, 3);//00
select substr('2024061100100000', -6, -3);//空字符串

注意：

如果pos的值大于字符串的长度，或者pos和len的组合导致超出字符串范围，则返回空字符串。
如果len是负值，函数将返回一个空字符串。

五、concat和concat_ws函数

concat函数用于连接两个或多个字符串，示例如下：

select concat('sd-','23','-233');//sd-23-233
select concat('sd-','23', ''); //sd-23
select concat('sd-','23',null);//null
select concat('sd-','23',ifnull(null,''));//sd-23

注意：

如果连接的属性有一个为null，则会返回null，可以使用ifnull和coalesce函数来处理null值问题；

concat_ws函数用于连接两个或多个字符串，并在他们之间插入一个指定的分隔符，如果任何参数为null，则会忽略此参数。

语法如下：

concat_ws(分隔符，参数1，参数2，...)

示例如下：

select concat_ws('-','dd','s'); //dd-s
select concat_ws('-','dd','s', null);//dd-s

六、max和min函数

max和min是聚合函数用于获取数据库中某一列的最大或最小的值，示例如下：

select max(f.price) from sales f
select min(f.price) from sales f

七、trim函数

trim()函数用于去除字符串前后的空格或其它指定的字符，语法如下：

TRIM([BOTH | LEADING | TRAILING] [remstr] FROM] str)

BOTH、LEADING、TRAILING用于指定要删除的位置，如果省略，默认为BOTH。
remstr是要从字符串中删除的字符。如果省略，默认为空格。
str是要处理的字符串。

示例：

去除字符串前后的空格

select trim(' Hello World '); // Hello World

去除字符串后的特定字符串

select trim(leading '-' from '--Hello World--');// Hello World--

去除字符串后的特定字符串

select trim(trailing '-' from '--Hello World--');// --Hello World

去除字符串前后的特定字符串

select trim(both '-' from '--Hello World--');// Hello World

八、count函数

count函数用于计算表中的记录数或特定列的非null值数；

计算一个表中指定条件的所有记录数

SELECT COUNT(*) FROM DEVICE d WHERE d.TYPE IS NULL

计算一个表中指定列非null值的数量

SELECT COUNT(d.TYPE) FROM DEVICE d WHERE d.TYPE IS NULL

九、str_to_date()函数将字符串转日期

str_to_date函数可以将字符串转换为日期对象，如果你有一个字符串日期，可以将其转换为日期对象，转换示例如下：

select str_to_date('2024-06-15 10:24:26', '%Y-%m-%d %H:%i:%s') as mydate from dual
select str_to_date('2024/06/15 10:24:26', '%Y/%m/%d %H:%i:%s') as mydate from dual
select str_to_date('2024-06-15', '%Y-%m-%d') as mydate from dual
select str_to_date('20240615', '%Y%m%d') as mydate from dual

在上述示例中格式代表的含义如下：

%Y代表思维数的年份
%m代表两位数的月份
%d代表两位数的日期
%H代表24小时制的小时
%i代表两位数的分钟
%s代表两位数的秒

十、date_format()函数将日期转字符串

使用date_format()函数可以将日期对象转换为字符串，可以根据指定的格式来格式化日期/时间值，示例如下：

SELECT DATE_FORMAT(s.updateTime, '%Y-%m-%d %H:%i:%s')  FROM sailboat s 
SELECT DATE_FORMAT(s.updateTime, '%Y-%m-%d')  FROM sailboat s 
SELECT DATE_FORMAT(s.updateTime, '%H:%i:%s')  FROM sailboat s

在上述示例中格式代表的含义如下：

%Y代表思维数的年份
%m代表两位数的月份
%d代表两位数的日期
%H代表24小时制的小时
%i代表两位数的分钟
%s代表两位数的秒

十一、date_add函数

date_add()函数用于在给定的日期上添加指定的时间间隔，它的语法如下：

DATE_ADD(date, INTERVAL expr unit)

其中：

date是要修改的日期。
expr是要添加的时间间隔数量。
unit是时间间隔单位，如DAY、MONTH、YEAR、HOUR、MINUTE、SECOND、MICROSECOND、WEEK、QUARTER等

示例：

SELECT DATE_ADD(CURDATE(), INTERVAL 5 YEAR) as date1;
SELECT DATE_ADD('2024-06-15 12:23:25', INTERVAL 2 MONTH);
SELECT DATE_ADD('2024-06-15', INTERVAL 2 DAY);
SELECT DATE_ADD('2024-06-15 12:23:25', INTERVAL 2 HOUR);
SELECT DATE_ADD('2024-06-15 12:23:25', INTERVAL 2 MINUTE);
SELECT DATE_ADD('2024-06-15 12:23:25', INTERVAL 2 SECOND);
select DATE_ADD(CURDATE(), INTERVAL 5 DAY) as time
select DATE_ADD(CURDATE(), INTERVAL 1 MONTH) as time
select DATE_ADD(CURDATE(), INTERVAL 1 YEAR) as time
select DATE_ADD(CURDATE(), INTERVAL 5 HOUR) as time
select DATE_ADD(CURDATE(), INTERVAL 5 MINUTE) as time
select DATE_ADD(CURDATE(), INTERVAL 5 SECOND) as time
select DATE_ADD(CURDATE(), INTERVAL 5 MICROSECOND) as time
select DATE_ADD(CURDATE(), INTERVAL 5 WEEK) as time
select DATE_ADD(CURDATE(), INTERVAL 5 QUARTER) as time

十二、date_sub函数

date_sub用于从日期中键入指定的时间间隔，它的语法如下：

date_sub(date, INTERVAL expr unit)

date 是你希望从中减去时间间隔的日期。
expr 是你想要减去的时间间隔的数量。
unit 是时间间隔的单位，可以是 MICROSECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, 或 YEAR。

示例：

select DATE_SUB(CURDATE(), INTERVAL 5 DAY) as time
select DATE_SUB(CURDATE(), INTERVAL 1 MONTH) as time
select DATE_SUB(CURDATE(), INTERVAL 1 YEAR) as time
select DATE_SUB(CURDATE(), INTERVAL 5 HOUR) as time
select DATE_SUB(CURDATE(), INTERVAL 5 MINUTE) as time
select DATE_SUB(CURDATE(), INTERVAL 5 SECOND) as time
select DATE_SUB(CURDATE(), INTERVAL 5 MICROSECOND) as time
select DATE_SUB(CURDATE(), INTERVAL 5 WEEK) as time
select DATE_SUB(CURDATE(), INTERVAL 5 QUARTER) as time

十三、datediff函数

datediff函数用于返回两个日期之间的天数，它的语法如下：

datediff(date1, date2)

date1 和 date2 是要比较的两个日期。
函数返回 date1 - date2 的结果，即 date1 比 date2 早多少天。如果 date1 晚于 date2，则结果为正数；如果 date1 早于 date2，则结果为负数；如果两个日期相同，则返回 0。

示例：

select datediff('20240619','20240617') from dual 
select DATEDIFF(CURDATE(), s.updateTime) from sailboat s

十四、sysdate和now函数

sysdate和now都用于获取当前的日期和函数，但是它们之间也存在一些差异；

select SYSDATE() from dual 
select NOW() from dual

now()返回当前的日期和时间，基于从服务器获取的时间；sysdate()也返回当前的日期和时间，但是它基于执行该函数的客户端机器的系统时间。
now()在事务开始和结束时其值是一样，即使在事务内部花费了很长时间；sysdate()在事务中多次调用，每次调用返回值都可能不同。

十五、currdate和currtime函数

currdate()和currtime()用于分别获取当前的日期和当前时间，示例如下：

select CURDATE() FROM DUAL
select CURTIME() from dual

十六、cast和convert函数数据类型转换

mysql中cast和convert函数都用于数据类型转换，但是它们之间确实存在一些差异。

语法差异

cast函数的语法如下：

sql cast(expression as type)

convert函数的语法如下：

sql convert(expression, type)

案例如下：

SELECT cast(202102 as char)
SELECT convert(202102, char)

立小言先森

关注

12
点赞
踩
16

收藏

觉得还不错? 一键收藏
0
评论
Mysql常用函数及技巧

lenght()函数在mysql中用于返回字符串的从航都，它返回的是字符串的字节数，而不是字符数，如果是UTF-8编码的多字节字符串，那么一个字符可能栈多个字节，如：汉字。concat_ws函数用于连接两个或多个字符串，并在他们之间插入一个指定的分隔符，如果任何参数为null，则会忽略此参数。mysql中的substring和substr函数是等价的，它们用于从一个字符串中提取子字符串。count函数用于计算表中的记录数或特定列的非null值数；用于在字符串左侧和右侧填充指定的字符，知道达到指定的长度。
复制链接

扫一扫

专栏目录