MySql基础知识-----MySQL常用函数

最新推荐文章于 2023-05-29 11:51:02 发布

Purple7826

最新推荐文章于 2023-05-29 11:51:02 发布

阅读量658

点赞数 2

文章标签： MySQL 常用函数

本文链接：https://blog.csdn.net/Purple7826/article/details/80573864

版权

MySQL中的常用函数分别为：

（1）字符串函数

（2）数值函数

（3）日期和时间函数

（4）系统信息函数

1. 使用字符串函数

MySQL所支持字符串函数如下表所示：

函数	功能
concat(str1,str2...strn)	连接字符串str1，str2.....strn为一个完整的字符串
insert(str, x,y,instr)	将字符串str从第x位开始，y个字符长的子串替换为字符串instr
lower(str)	将字符串str中所有字母转换为小写
upper(str)	将字符串str中所有字母转换为大写
left(str,x)	返回字符串str最左边的x个字符
right(str,x)	返回字符串str最右边的x个字符
lpad(str,in,pad)	使用字符串pad对字符串str的左边进行填充，直到长度为n个字符长度
rpad(strain,pad)	使用字符串pad对字符串str的右边进行填充，直到长度为n个字符长度
ltrim(str)	去掉字符串str左边的空格
rtrim(str)	去掉字符串str右边的空格
repeat(str,x)	返回字符串str重复x次的结果
replace(str,a,b)	使用字符串b替换字符串str中所有出现的字符串a
strcmp(str1,str2)	比较字符串str1和str2
trim(str)	去掉字符串str行头和行尾的空格
substring(str, x,y)	返回字符串str中从x位置起y个字符长度的字符串

1.1 合并字符串函数CONCAT()和CONCAT_WS()

函数的CONACAT()的定义如下：

CONCAT(str1,str2...strn)

示例：

mysql> select concat('My','S','QL') 合并后的字符串;
+-----------------------+
| 合并后的字符串        |
+-----------------------+
| MySQL                 |
+-----------------------+
1 row in set (0.00 sec)

注意：当传入concat函数中的参数有一个值为NULL时，返回的结果值将为NULL。

函数CONCAT_WS()的定义如下：

CONCAT_WS(SEP,str1,str2....strn)

与函数CONCAT()X相比，多了一个表示表示分隔符的SEP参数，即不仅会将传入的参数连接起来，还会通过分隔符将字符串分割开，示例如下：

mysql> select concat_ws('-','2018','06','09') 合并后的字符串;
+-----------------------+
| 合并后的字符串        |
+-----------------------+
| 2018-06-09            |
+-----------------------+
1 row in set (0.00 sec)

注意：当传入的第一个参数为NULL时，返回的结果值将为NULL；当传入的参数值中包含NULL值时（第一个参数除外），返回的结果将忽略NULL值，示例略。

1.2 比较字符串大小函数STRCMP()

语法形式如下：

STRCMP(str1,str2)

str1,str2	返回结果
str1>str2	1
str1=str2	0
str1<str2	-1

示例略。

1.3 获取字符串长度函数LENGTH()和字符数函数CHAR_LENGTH()

函数LENGTH的自定如下：

LENGTH(str)

上述函数会获取传入的参数str的长度

函数CHAR_LENGTH()的定义如下：

CHAR_LENGTH(str)

上述函数会获取传入的参数str的字符数

示例如下：

mysql> select length('canvas') 计算英文长度,length('星期以') 计算中文长度,char_length('canvas') 计算英文字符数,char_length("星期三") 计算中文字符数;
+--------------------+--------------------+-----------------------+-----------------------+
| 计算英文长度       | 计算中文长度       | 计算英文字符数        | 计算中文字符数        |
+--------------------+--------------------+-----------------------+-----------------------+
|                  6 |                  9 |                     6 |                     3 |
+--------------------+--------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)

1.4 查找字符串

MySQL提供了丰富的函数去查找字符串的位置，分别为FIND_IN_SET()，FIELD()，LOCATE()，POSITION()和INSERT()函数。

1.4.1 返回字符串位置的FIND_IN_SET()函数

函数语法形式如下：

FIND_IN_SET(str1,str2)

上述函数将会返回在字符串str2中与str1相匹配的字符串的位置，参数str2字符串中将包含若干个用逗号隔开的字符串。

示例如下：

mysql> select find_in_set('MySQL','Hello,world,MySQL,Tokyo') as find_in_set_function;
+----------------------+
| find_in_set_function |
+----------------------+
|                    3 |
+----------------------+
1 row in set (0.00 sec)

1.4.2 返回指定指定字符串位置的FIELD()函数

函数定义如下：

FIELD(str,str1,str2....)

上述函数将会返回第一个与字符串str匹配的字符串的位置。

示例如下：

mysql> select field('MySQL','Str','Good','MySQL','SQLLite') as field_function;
+----------------+
| field_function |
+----------------+
|              3 |
+----------------+
1 row in set (0.00 sec)

1.4.3 返回子字符串相匹配的开始位置

MySQL中可以通过3个函数获取子字符串相匹配的开始位置，分别为LOCATE(),POSITION()和INSTR()函数，LOCATE()函数定义如下：

LOCATE(str1,str)

上述函数将返回参数str中字符串str1的开始位置

其他两个函数定义如下：

POSITION(str1 IN str)

和

INSTR(str,str1)

示例如下：

mysql> select locate('MySQL','Hello,Happy to learn MySQL by myself') as locate_function,position('MySQL' in 'Hello,Happy to learn MySQL by myself') as position_function,instr('Hello,Happy to learn MySQL by myself','MySQL') as instr_function;
+-----------------+-------------------+----------------+
| locate_function | position_function | instr_function |
+-----------------+-------------------+----------------+
|              22 |                22 |             22 |
+-----------------+-------------------+----------------+
1 row in set (0.00 sec)

1.4.4 返回指定位置的字符串的ELT()函数

MySQL中可以通过ELT()函数获取指定位置的字符串，ELT()函数的定义如下：

ELT(n,str1,str2.....)

上述函数将会返回第n个字符串。

示例如下：

mysql> select elt(3,'MySQL','Hell','Happy',' to ','learn',' MySQL',' by', 'myself') as elt_function;
+--------------+
| elt_function |
+--------------+
| Happy        |
+--------------+
1 row in set (0.00 sec)

1.4.5 选择字符串的MAKE_SET()函数

MySQL中可以通过MAKE_SET()函数获取字符串，函数定义如下：

MAKE_SET(num,str1,str2.....strn)

上述函数首先会将数值num转换为二进制数，然后按照二进制数从参数str1，str2，.......，strn中选取相应的字符串。在通过二进制数选择字符串时，按从左到右的顺序读取该值，如果值为1选择该字符串，否则将不选择字符串。

示例如下：

mysql> select bin(5) 二进制数,make_set(5,'MySQL','Oracle','SQL Server','SQLite') 选取后的字符串;
+--------------+-----------------------+
| 二进制数     | 选取后的字符串        |
+--------------+-----------------------+
| 101          | MySQL,SQL Server      |
+--------------+-----------------------+
1 row in set (0.00 sec)

1.5 从现有字符串中截取子字符串

MySQL中截取子字符串的函数有LEFT(),RIGHT(),SUBSTRING()和MID()。

1.5.1 从左边或右边截取子字符串

MySQL中可以通过LEFT()函数获取字符串中从左边数的部分字符串，可以通过RIGHT()函数获取字符串中从右边数的部分字符串，LEFT()和RIGHT()函数的定义如下：

LEFT(str,num)
RIGHT(str,num)

上述函数会返回字符串str中包含前num个字母（从左/右）的字符串。

示例如下：

mysql> select left('MySQL vs SQL-Server',5) 从左选取,right('MySQL vs SQL-Server',10) 从右选取;
+--------------+--------------+
| 从左选取     | 从右选取     |
+--------------+--------------+
| MySQL        | SQL-Server   |
+--------------+--------------+
1 row in set (0.00 sec)

1.5.2 截取指定位置和长度子字符串

MySQL()中可以通过SUBSTRING()和MID()函数截取指定位置和长度的子字符串，函数定义如下：

SUBSTRING(str,num,len)

上述函数会返回字符串str中的第num个位置开始长度为len的子字符串。MID()函数定义如下：

MID(str,num,len)

示例如下：

mysql> select substring('One of the best database is MySQL',12,4) subtring_function;
+-------------------+
| subtring_function |
+-------------------+
| best              |
+-------------------+
1 row in set (0.00 sec)

1.6 替换字符串

MySQL中可以使用INSERT()和REPLACE()函数实现替换字符串的功能。

1.6.1 使用INSERT()函数

函数定义如下：

INSERT(str,pos,len,newstr)

上述函数会将字符串str中的pos位置开始的长度为len的字符串用新字符串newstr来替换。

可能出现的情况如下：

（1）参数pos超过字符串长度，则返回值为原始字符串。

（2）参数len的长度大于原来字符串中所剩字符串的长度，则从pos位置开始全部替换。

（3）任意参数为NULL时，返回值为NULL。

示例如下：

mysql> select insert('Today i want to date,hhhhhh',22,6,'that is not could happen,sadness') insert_function;
+-------------------------------------------------------+
| insert_function                                       |
+-------------------------------------------------------+
| Today i want to date,that is not could happen,sadness |
+-------------------------------------------------------+
1 row in set (0.00 sec)

1.6.2 使用REPLACE()函数

函数定义如下：

REPLACE(str,substr,newstr)

上述函数会将字符串str中的子字符串substr用字符串newstr来替换，示例略。

2 使用数值函数

MySQL中常用数值函数如下：

函数	功能
ABS(x)	返回数值x的绝对值
CEIL(x)	返回大于或者等于x的最小整数值
FLOOR(x)	返回小于或者等于x的最大整数值
MOD(x,y)	返回x除以y的余数
RAND()	返回0-1内的随机数
ROUND(x,y)	返回数值x的四舍五入后有y位小数的数值
TRUNCATE(x,y)	返回数值x且截断为y为小数的数值

2.1 获取随机数

MySQL中可以通过RAND()和RAND(x)函数来获取随机数。上述两个函数都会返回0～1之间的随机数，其中RAND()函数返回的数是完全随机的，而RAND(x)返回的随机数值是相同的。

示例略。

2.2 截取数值函数

MySQL中通过函数TRUNCATE()函数来实现对数值的小数位数进行截取。函数定义如下：

TRUNCATE(x,y)

上述函数返回数值x保留到小数点后y位的值。示例如下：

mysql> select truncate(3.1415926,4);
+-----------------------+
| truncate(3.1415926,4) |
+-----------------------+
|                3.1415 |
+-----------------------+
1 row in set (0.00 sec)

2.3 四舍五入函数

MySQL中可以通过ROUND()函数实现四舍五入操作。函数定义如下：

ROUND(x)

上述函数会返回x经过四舍五入操作后的数值。

ROUND(x,y)

上述函数会返回数值x保留到小数点后y位的值，在具体截取数值时需要进行四舍五入操作。

示例如下：

mysql> select round(3.14159) 单个参数的四舍五入操作,round(3.1415926,4) 两个参数的四舍五入操作;
+-----------------------------------+-----------------------------------+
| 单个参数的四舍五入操作            | 两个参数的四舍五入操作            |
+-----------------------------------+-----------------------------------+
|                                 3 |                            3.1416 |
+-----------------------------------+-----------------------------------+
1 row in set (0.00 sec)

3 使用日期和时间函数

以下是MySQL中常用的日期和时间函数：

函数	功能
CURDATE()	获取当前日期
CURTIME()	获取当前时间
NOW()	获取当前的日期和时间
UNIX_TIMESTAMP(date)	获取日期date的UNIX时间戳
FROM_UNIXTIME()	获取UNIX时间戳的日期值
WEEK(date)	返回日期date为一年中的第几周
YEAR(date)	返回日期date中的年份
HOUR(time)	返回时间time的小时值
MINUTE(time)	返回时间time的分钟值
MONTHNAME(date)	返回时间date的月份值

3.1 获取当前日期和时间的函数

MySQL中获取当前日期和时间的函数有NOW(),CURRENT_TIMESTAMP(),LOCALTIME()和SYSDATE(),这四个函数不仅会获取当前的时间和日期，而且显示的格式也一样，具体使用时推荐使用NOW().

示例如下：

mysql> select now(),current_timestamp(),sysdate(),localtime();
+---------------------+---------------------+---------------------+---------------------+
| now()               | current_timestamp() | sysdate()           | localtime()         |
+---------------------+---------------------+---------------------+---------------------+
| 2018-06-18 14:16:06 | 2018-06-18 14:16:06 | 2018-06-18 14:16:06 | 2018-06-18 14:16:06 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

单独获取日期的函数有CURDATE()和CURRENT_DATE()，单独获取时间函数有CURTIME()和CURRENT_TIME()，示例略。

3.2 通过各种方式显示日期和时间

最常用的显示日期和时间的方式有UNIX和UTC。

3.2.1 通过UNIX方式显示日期和时间

MySQL提供了UNIX_TIMESTAMP()函数返回时间戳格式的时间，FROM_UINIXTIME()函数将时间戳格式时间转换为普通格式时间。

示例如下：

mysql> select now() 当前时间,unix_timestamp(now()) unix格式,from_unixtime(unix_timestamp(now())) 普通格式;
+---------------------+------------+---------------------+
| 当前时间            | unix格式   | 普通格式            |
+---------------------+------------+---------------------+
| 2018-06-18 14:23:24 | 1529303004 | 2018-06-18 14:23:24 |
+---------------------+------------+---------------------+
1 row in set (0.00 sec)

3.2.2 通过UTC方式显示日期和时间

MySQL中提供了两个函数UTC_TIME()和UTC_DATE()来实现时间和日期的UTC格式显示。示例如下：

mysql> select utc_date() UTC日期,utc_time() UTC时间;
+------------+-----------+
| UTC日期    | UTC时间   |
+------------+-----------+
| 2018-06-18 | 06:25:54  |
+------------+-----------+
1 row in set (0.00 sec)

3.3 获取日期和时间各个部分值

MySQL中提供了各种函数来获取当前日期和时间的各部分值,具体使用见以下例子：

mysql> select now() 当前时间,
    -> year(now()) 年,
    -> quarter(now()) 季度,
    -> month(now()) 月,
    -> week(now()) 星期,
    -> dayofmonth(now()) 天,
    -> hour(now()) 时,
    -> minute(now()) 分,
    -> second(now()) 秒;
+---------------------+------+--------+------+--------+------+------+------+------+
| 当前时间            | 年   | 季度   | 月   | 星期   | 天   | 时   | 分   | 秒   |
+---------------------+------+--------+------+--------+------+------+------+------+
| 2018-06-18 14:30:15 | 2018 |      2 |    6 |     24 |   18 |   14 |   30 |   15 |
+---------------------+------+--------+------+--------+------+------+------+------+
1 row in set (0.00 sec)

3.3.1 关于月的函数

函数MONTH()返回日期和时间中的月份，取值范围为1～12。在使用时如果想显示月份的英文名称而不是简单的数值，可以使用MONTHNAME()函数。使用如下：

mysql> select now() 当前时间,month(now()) 月, monthname(now()) 月;
+---------------------+------+------+
| 当前时间            | 月   | 月   |
+---------------------+------+------+
| 2018-06-18 14:35:05 |    6 | June |
+---------------------+------+------+
1 row in set (0.00 sec)

3.3.2 关于星期的函数

MySQL提供了WEEK()和WEEKOFYEAR()函数用来显示当前星期是当前年中的第几个星期。初次之外MySQL还提供了如下几个函数用来显示星期几或是其他的英文名字：

（1）DAYNAME()函数：返回日期和时间中星期的英文名

（2）DAYOFWEEK()函数：返回日期和时间中星期是星期几，返回值的取值范围是1～7，如果返回值是1则表示星期日，2表示星期一，依此类推。

（3）WEEKDAY()返回日期和时间中星期是星期几，返回的取值范围是0～6。0表示星期一，1表示星期二，依此类推。

示例如下：

mysql> select now() 当前日期和时间,
    -> week(now()) 年中的第一个星期,
    -> weekofyear(now()) 年中的第几个星期,
    -> dayname(now()) 星期,
    -> dayofweek(now()) 星期,
    -> weekday(now()) 星期;
+-----------------------+--------------------------+--------------------------+--------+--------+--------+
| 当前日期和时间        | 年中的第一个星期         | 年中的第几个星期         | 星期   | 星期   | 星期   |
+-----------------------+--------------------------+--------------------------+--------+--------+--------+
| 2018-06-18 14:45:54   |                       24 |                       25 | Monday |      2 |      0 |
+-----------------------+--------------------------+--------------------------+--------+--------+--------+
1 row in set (0.00 sec)

3.3.3 关于天的函数

MySQL提供了函数DAYOFMONTH()和DAYOFYEAR(),分别用来显示当天是当前月的第几天和当前年的第几天，示例如下：

mysql> select dayofmonth(now()) 当前月的第几天,
    -> dayofyear(now()) 当前年的第几天;
+-----------------------+-----------------------+
| 当前月的第几天        | 当前年的第几天        |
+-----------------------+-----------------------+
|                    18 |                   169 |
+-----------------------+-----------------------+
1 row in set (0.00 sec)

3.3.4 获取指定值的EXTRACT()函数

获取日期和时间的各部分值，需要记住的函数较多，不方便记忆，于是MySQL提供了EXTRACT()函数，函数语法如下：

EXTRACT(type FROM date)

上述函数会从日期和时间参数date中获取指定类型参数type值，关于type参数的取值可以是：YEAR，MONTH，DAY，HOUR，MINUTE，SECOND。示例如下：

mysql> select now() 当前日期和时间, extract(year from now()) 年, extract(month from now()) 月, extract(day from now()) 日, extract(hour from now()) 小时, extract(minute from now()) 分钟, extract(second from now()) 秒;
+-----------------------+------+------+------+--------+--------+------+
| 当前日期和时间        | 年   | 月   | 日   | 小时   | 分钟   | 秒   |
+-----------------------+------+------+------+--------+--------+------+
| 2018-06-18 15:05:25   | 2018 |    6 |   18 |     15 |      5 |   25 |
+-----------------------+------+------+------+--------+--------+------+
1 row in set (0.00 sec)

3.4 计算日期和时间的函数

MySQL中计算日期和时间的函数分为两种，第一种是计算与默认时期和时间相互操作的函数，第二种是计算与指定日期和时间相互操作的函数。

3.4.1 与默认日期和时间操作

MySQL提供了两个函数用来实现与默认日期和时间的操作：

（1）TO_DAYS(date)函数：该函数计算日期参数date与默认日期和时间（0000年1月1日）之间相隔天数。

（2）FROM_DAYS(number)函数：该函数计算从默认日期和时间（0000年1月1日）开始经历number天后的日期和时间。

示例如下：

mysql> select now() 当前日期和时间,
    -> to_days(now()) 相隔天数,
    -> from_days(to_days(now())) 一段时间后的日期和时间;
+-----------------------+--------------+-----------------------------------+
| 当前日期和时间        | 相隔天数     | 一段时间后的日期和时间            |
+-----------------------+--------------+-----------------------------------+
| 2018-06-18 15:36:20   |       737228 | 2018-06-18                        |
+-----------------------+--------------+-----------------------------------+

有时需要获取指定两个日期之间的天数，MySQL提供了函数DATEDIFF()函数，该函数定义如下：

DATEDIFF(date1,date2)

上述函数会返回日期参数date1和date2之间相隔的天数，使用方法如下：

mysql> select datediff(now(),'2018-06-10') datediff_function;
+-------------------+
| datediff_function |
+-------------------+
|                 8 |
+-------------------+
1 row in set (0.00 sec)

3.4.2与指定日期和时间操作

MySQL提供了两个函数用来实现与指定日期操作：

（1）ADDDATE(date,n)函数：计算日期参数date加上n天后的日期。

（2）SUBDATE(date,n)函数：计算日期参数date减去n天后的日期。

示例如下：

mysql> select now() 当前日期和时间,adddate(now(),5) 加上5天后的日期,subdate(now(),5) 减去5天后的日期;
+-----------------------+------------------------+------------------------+
| 当前日期和时间        | 加上5天后的日期        | 减去5天后的日期        |
+-----------------------+------------------------+------------------------+
| 2018-06-18 15:48:11   | 2018-06-23 15:48:11    | 2018-06-13 15:48:11    |
+-----------------------+------------------------+------------------------+
1 row in set (0.00 sec)

ADDDATE()和SUNBDATE()函数除了可以接受上述参数之外，还可以接受其他参数。定义如下：

ADDDATE(d,INTERVAL expr type)

SUBDATE(d,INTERVAL expr type)

上述函数返回日期参数加上/减去一段时间后的日期，表达式expr决定了时间长度，参数type决定所操作的对象。其中type的值如下表所示：

type的值	含义	expr表达式
YEAR	年	YY
MONTH	月	MM
DAY	日	DD
HOUR	时	hh
MINUTE	分	mm
SECOND	秒	ss
YEAR_MONTH	年和月	YY和MM之间用任意符号隔开
DAY_HOUR	日和小时	DD和hh之间用任意符号隔开
DAY_MINUTE	日和分钟	DD和mm之间用任意符号隔开
DAY_SECOND	日和秒	DD和ss之间用任意符号隔开
HOUR_MINUTE	小时和分钟	hh和mm之间用任意符号隔开
HOUR_SECOND	小时和秒	hh和ss之间用任意符号隔开
MINUTE_SECOND	分钟和秒	mm和ss之间用任意符号隔开

使用示例如下：

mysql> select now() 当前日期和时间, adddate(now(),interval '2,3' year_month) 2年3个月后的日期和时间, subdate(now(), interval '2,3' year_month) 2年3个月前的日期和时间;
+-----------------------+----------------------------------+----------------------------------+
| 当前日期和时间        | 2年3个月后的日期和时间           | 2年3个月前的日期和时间           |
+-----------------------+----------------------------------+----------------------------------+
| 2018-06-18 16:45:45   | 2020-09-18 16:45:45              | 2016-03-18 16:45:45              |
+-----------------------+----------------------------------+----------------------------------+
1 row in set (0.00 sec)

在MySQL中，除了可以使用ADDDATE()和SUBDATE()函数来操作指定日期之外，还可以通过ADDTIEME()和SUBTIME()函数来实现操作时间，作用如下：

（1）ADDTIME(time,n)函数：计算时间参数time加上n秒后的时间

（2）SUBTIME(time,n)函数：计算时间参数time减去n秒后的时间

使用如下：

mysql> select curtime() 当前时间, addtime(curtime(),30) 加上30秒后的时间, subtime(curtime(),30) 减去30秒后的时候;
+--------------+-------------------------+-------------------------+
| 当前时间     | 加上30秒后的时间        | 减去30秒后的时候        |
+--------------+-------------------------+-------------------------+
| 16:51:59     | 16:52:29                | 16:51:29                |
+--------------+-------------------------+-------------------------+
1 row in set (0.00 sec)

Purple7826

关注

2
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
MySql基础知识-----MySQL常用函数

MySQL中的常用函数分别为：（1）字符串函数（2）数值函数（3）日期和时间函数（4）系统信息函数1. 使用字符串函数MySQL所支持字符串函数如下表所示：函数功能concat(str1,str2...strn)连接字符串str1，str2.....strn为一个完整的字符串insert(str, x,y,instr)将字符串str从第x位开始，y个字符长的子串替换为字符串instrlower(s...
复制链接

扫一扫