MySQL中的常用函数
在MySQL中,函数不仅可以出现在select语句及其子句中,而且还可以出现在update、delete语句中。
常用的函数有:
1.字符串函数;主要用于处理字符串。
2.数值函数;主要用于处理数字。
3.日期和时间函数;主要用于处理日期和事件。
4.系统信息函数;获取系统信息。
1.使用字符串函数:
虽然每种数据库都支持SQL,但是每种数据库拥有各自所支持的函数。
1.1合并字符串函数concat()和concat_ws():
在MySQL中可以通过函数concat()和concat_ws()将传入的参数连接成为一个字符串。
语法定义为:
concat(s1,s2,...sn) //该函数会将传入的参数连接起来返回合并的字符串类型的数据。如果其中一个参数为null,则返回值为null.
示例:
mysql>selectconcat('my','s','ql'); +-----------------------+ |concat('my','s','ql')| +-----------------------+ |mysql| +-----------------------+ 1rowinset(0.00sec) mysql>selectconcat('my','s','ql',null); +----------------------------+ |concat('my','s','ql',null)| +----------------------------+ |NULL| +----------------------------+ 1rowinset(0.00sec) mysql>selectconcat(curdate(),12.2); +-------------------------+ |concat(curdate(),12.2)| +-------------------------+ |2016-08-2512.2| +-------------------------+ 1rowinset(0.00sec) //说明:将当前时间和数值12.2合并。即concat()函数不仅可以接受字符串参数,而且还可以接受其他类型参数。
concat_ws()的定义:
concat_ws(sep,s1,s2,...sn) //该函数与concat()相比,多了一个表示分隔符的seq参数,不仅将传入的其他参数连接起来,而且还会通过分隔符将各个字符串分割开来。 //分隔符可以是一个字符串,也可以是其他参数。如果分割符为null,则返回结果为null。函数会忽略任何分割符后的参数null.
示例:
mysql>selectconcat_ws('-','020','87658907'); +---------------------------------+ |concat_ws('-','020','87658907')| +---------------------------------+ |020-87658907| +---------------------------------+ 1rowinset(0.00sec) mysql>selectconcat_ws(null,'020','87658907'); +----------------------------------+ |concat_ws(null,'020','87658907')| +----------------------------------+ |NULL| +----------------------------------+ 1rowinset(0.00sec) //当分隔符为null时,则返回结果为null mysql>selectconcat_ws('-','020',null,'87658907'); +--------------------------------------+ |concat_ws('-','020',null,'87658907')| +--------------------------------------+ |020-87658907| +--------------------------------------+ 1rowinset(0.00sec) //不是第一个参数的null将被忽略
1.2比较字符串大小函数strcmp():
strcmp()定义为:
strcmp(str1,str2); //如果参数str1大于str2,返回1;如果str1小于str2,则返回-1;如果str1等于str2,则返回0;
示例:
mysql>selectstrcmp('abc','abd'),strcmp('abc','abc'),strcmp('abc','abb'); +---------------------+---------------------+---------------------+ |strcmp('abc','abd')|strcmp('abc','abc')|strcmp('abc','abb')| +---------------------+---------------------+---------------------+ |-1|0|1| +---------------------+---------------------+---------------------+ 1rowinset(0.00sec)
1.3获取字符串长度函数length()和字符数函数char_length():
length()的定义如下:
length(str)
char_length(str)的定义如下:
char_length(str)
示例:
mysql>selectlength('mysql'),length('汉字'),char_length('mysql'),char_length('汉字'); +-----------------+----------------+----------------------+---------------------+ |length('mysql')|length('汉字')|char_length('mysql')|char_length('汉字')| +-----------------+----------------+----------------------+---------------------+ |5|4|5|4| +-----------------+----------------+----------------------+---------------------+ 1rowinset,2warnings(0.00sec) //字符串‘MySQL'共有5个字符,但是占6个字节空间。这是因为每个字符串都是以\0结束。两个函数都是获取字符串的字符数而不是所占空间大小。
1.4字母的大小写转换upper()和lower():
字母大小转换函数:upper(s);ucase(s);
字母小写转换函数:lower(s);lcase(s);
示例:
mysql>selectupper('mysql'),ucase('mYsql'),lower('MYSQL'),lcase('MYsql'); +----------------+----------------+----------------+----------------+ |upper('mysql')|ucase('mYsql')|lower('MYSQL')|lcase('MYsql')| +----------------+----------------+----------------+----------------+ |MYSQL|MYSQL|mysql|mysql| +----------------+----------------+----------------+----------------+ 1rowinset(0.00sec)
1.5查找字符串:
mysql中提供了丰富的函数去查找字符串的位置。分别有find_in_set()函数、field()函数、locate()函数、position()函数和instr()函数。同时还提供了查找指定位置的字符串的函数elt()。
1.5.1返回字符串位置的find_in_set()函数:
函数定义为:
find_in_set(str1,str2) //会返回在字符串str2中与str1相匹配的字符串的位置,参数str2字符串中将包含若干个用逗号隔开的字符串。
示例:
mysql>selectfind_in_set('mysql','oracle,mysql,db2'); +-----------------------------------------+ |find_in_set('mysql','oracle,mysql,db2')| +-----------------------------------------+ |2| +-----------------------------------------+ 1rowinset(0.00sec)
1.5.2返回指定字符串位置的field()函数:
函数定义为:
filed(str,str1,str2...) //返回第一个与字符串str匹配的字符串的位置。
示例:
mysql>selectfield('mysql','oracle','db2','redis','mysql'); +-----------------------------------------------+ |field('mysql','oracle','db2','redis','mysql')| +-----------------------------------------------+ |4| +-----------------------------------------------+ 1rowinset(0.00sec)
1.5.3返回子字符串相匹配的开始位置:
mysql中有三个函数可以获取子字符串相匹配的开始位置,分别是locate()、position()、instr()函数。
locate(str1,str)//返回参数str中字符串str1的开始位置
position(str1instr)和instr(str,str1)
示例:
mysql>selectlocate('sql','mysql'),position('sql'in'mysql'),instr('mysql','sql'); +-----------------------+----------------------------+----------------------+ |locate('sql','mysql')|position('sql'in'mysql')|instr('mysql','sql')| +-----------------------+----------------------------+----------------------+ |3|3|3| +-----------------------+----------------------------+----------------------+ 1rowinset(0.00sec)
1.5.4返回指定位置的字符串的elt()函数:
函数语法为:
elt(n,str1,str2...);
示例:
mysql>selectelt(1,'mysql','db2','oracle'); +-------------------------------+ |elt(1,'mysql','db2','oracle')| +-------------------------------+ |mysql| +-------------------------------+ 1rowinset(0.00sec)
1.5.5选择字符串的make_set()函数:
函数定义为:
make_set(num,str1,str2...strn)
示例:
mysql>selectbin(5),make_set(5,'mysql','db2','oracle','redus'); +--------+--------------------------------------------+ |bin(5)|make_set(5,'mysql','db2','oracle','redus')| +--------+--------------------------------------------+ |101|mysql,oracle| +--------+--------------------------------------------+ 1rowinset(0.00sec) //make_set()首先会将数值num转换成二进制数,然后按照二进制从参数str1,str2,...,strn中选取相应的字符串。再通过二进制从右到左的顺序读取该值,如果值为1选择该字符串,否则将不选择该字符串。
1.6从现有字符串中截取子字符串:
截取子字符串的函数有:left(),right(),substring(),mid();
1.6.1从左边或右边截取子字符串:
函数定义为:
left(str,num) //返回字符串str中包含前num个字母(从左边数)的字符串。 right(str,num) //返回字符串str中包含后num个字母(从右边数)的字符串。
示例:
mysql>selectleft('mysql',2),right('mysql',3); +-----------------+------------------+ |left('mysql',2)|right('mysql',3)| +-----------------+------------------+ |my|sql| +-----------------+------------------+ 1rowinset(0.00sec)
1.6.2截取指定位置和长度的字符串:
可以通过substring()和mid()函数截取指定位置和长度的字符串。
函数语法为:
substring(str,num,len)//返回字符串str中的第num个位置开始长度为len的子字符串。 mid(str,num,len)
示例:
mysql>selectsubstring('zhaojd',2,3),mid('zhaojd',2,4); +-------------------------+-------------------+ |substring('zhaojd',2,3)|mid('zhaojd',2,4)| +-------------------------+-------------------+ |hao|haoj| +-------------------------+-------------------+ 1rowinset(0.00sec)
1.7去除字符串的首尾空格:
去除字符串首尾空格的函数有:ltrim()、rtrim()、trim()
1.7.1去除字符串开始处的空格:
函数定义如下:
ltrim(str)//返回去掉开始处空格的字符串
示例:
mysql>selectlength(concat('-','mysql','-')),length(concat('-',ltrim('mysql'),'-')); +-----------------------------------+------------------------------------------+ |length(concat('-','mysql','-'))|length(concat('-',ltrim('mysql'),'-'))| +-----------------------------------+------------------------------------------+ |9|8| +-----------------------------------+------------------------------------------+ 1rowinset(0.00sec)
1.7.2去除字符串结束处的空格:
rtrim(str)//返回去掉结束处空格的字符串。
示例:
mysql>selectlength(concat('-','mysql','-')),length(concat('-',rtrim('mysql'),'-')); +-----------------------------------+------------------------------------------+ |length(concat('-','mysql','-'))|length(concat('-',rtrim('mysql'),'-'))| +-----------------------------------+------------------------------------------+ |9|8| +-----------------------------------+------------------------------------------+ 1rowinset(0.00sec)
1.7.3去除字符串首尾空格:
trim(str)//返回去掉首尾空格的字符串
示例:
mysql>selectconcat('mysql')origi,length(concat('mysql'))orilen,concat(trim('mysql'))after,length(concat(trim('mysql')))afterlen; +---------+--------+-------+----------+ |origi|orilen|after|afterlen| +---------+--------+-------+----------+ |mysql|7|mysql|5| +---------+--------+-------+----------+ 1rowinset(0.00sec)
1.8替换字符串:
实现替换字符串的功能,分别为insert()和replace()
1.8.1使用insert()函数:
函数定义为:
insert(str,pos,len,newstr)
//insert()函数会将字符串str中的pos位置开始长度为len的字符串用字符串newstr来替换。
//如果参数pos的值超过字符串长度,则返回值为原始字符串str。
//如果len的长度大于原来str中所剩字符串的长度,则从位置pos开始进行全部替换。若任何一个参数为null,则返回值为null.
示例:
mysql>selectinsert('这是mysql数据库系统',3,5,'oracle')bieming; +----------------------+ |bieming| +----------------------+ |这oracleql数据库系统| +----------------------+ 1rowinset,1warning(0.00sec)
1.8.1使用replace()函数:
函数的定义为:
replace(str,substr,newstr)//将字符串str中的子字符串substr用字符串newstr来替换。
示例:
mysql>selectreplace('这是mysql数据库','mysql','db2')bieming; +---------------+ |bieming| +---------------+ |这是db2数据库| +---------------+ 1rowinset,1warning(0.00sec)
2.使用数值函数:
2.1获取随机数:
通过rand()和rand(x)函数来获取随机数。这两个函数都会返回0-1之间的随机数,其中rand()函数返回的数是完全随机的,而rand(x)函数返回的随机数值是完全相同的。
示例:
mysql>selectrand(),rand(),rand(3),rand(3); +--------------------+--------------------+--------------------+--------------------+ |rand()|rand()|rand(3)|rand(3)| +--------------------+--------------------+--------------------+--------------------+ |0.9600886758045188|0.7006410161970565|0.9057697559760601|0.9057697559760601| +--------------------+--------------------+--------------------+--------------------+ 1rowinset(0.00sec)
2.2获取整数的函数:
在具体应用中,如果想要获取整数,可以通过ceil()和floor()函数来实现。
ceil()函数的定义为:
ceil(x)//函数返回大于或等于数值x的最小整数。
floor()//函数返回小于或等于数值x的最大整数。
示例:
mysql>selectceil(4.3),ceil(-2.5),floor(4.3),floor(-2.5); +-----------+------------+------------+-------------+ |ceil(4.3)|ceil(-2.5)|floor(4.3)|floor(-2.5)| +-----------+------------+------------+-------------+ |5|-2|4|-3| +-----------+------------+------------+-------------+ 1rowinset(0.00sec)
2.3截取数值函数:
可以通过truncate()对数值的小数位进行截取:
函数定义为:
truncate(x,y)//返回数值x,保留小数点后y位
示例:
mysql>selecttruncate(903.343434,2),truncate(903.343,-1); +------------------------+----------------------+ |truncate(903.343434,2)|truncate(903.343,-1)| +------------------------+----------------------+ |903.34|900| +------------------------+----------------------+ 1rowinset(0.00sec)
2.4四舍五入函数:
对数值进行四舍五入可以通过round()函数实现:
round(x)
//函数返回值x经过四舍五入操作后的数值。
round(x,y)
//返回数值x保留到小数点后y位的值。在具体截取数据时需要进行四舍五入的操作。
示例:
mysql>selectround(903.53567),round(-903.53567),round(903.53567,2),round(903.53567,-1); +------------------+-------------------+--------------------+---------------------+ |round(903.53567)|round(-903.53567)|round(903.53567,2)|round(903.53567,-1)| +------------------+-------------------+--------------------+---------------------+ |904|-904|903.54|900| +------------------+-------------------+--------------------+---------------------+ 1rowinset(0.00sec)
3.使用日期和时间函数:
3.1获取当前日期和时间的函数:
3.1.1获取当前日期和时间(日期+时间):
MySQL中可以通过四个函数获取当前日期和时间,分别是now(),current_timestamp(),localtime(),sysdate(),这四个函数不仅可以获取当前日期和时间,而且显示的格式也一样。推荐使用now()
示例:
mysql>selectnow(),current_timestamp(),localtime(),sysdate(); +---------------------+---------------------+---------------------+---------------------+ |now()|current_timestamp()|localtime()|sysdate()| +---------------------+---------------------+---------------------+---------------------+ |2016-08-2516:09:20|2016-08-2516:09:20|2016-08-2516:09:20|2016-08-2516:09:20| +---------------------+---------------------+---------------------+---------------------+ 1rowinset(0.00sec)
3.1.2获取当前日期:
获取当前日期的函数curdate()和current_date()函数。
示例:
mysql>selectcurdate(),current_date(); +------------+----------------+ |curdate()|current_date()| +------------+----------------+ |2016-08-25|2016-08-25| +------------+----------------+ 1rowinset(0.00sec)
3.1.3获取当前时间:
获取当前时间的函数,curtime()或者current_time();推荐使用curtime();
示例:
mysql>selectcurtime(),current_time(); +-----------+----------------+ |curtime()|current_time()| +-----------+----------------+ |16:15:04|16:15:04| +-----------+----------------+ 1rowinset(0.00sec)
3.2获取日期和时间各部分值:
在MySQL中,可以通过各种函数来获取当前日期和时间的各部分值,其中year()函数返回日期中的年份,quarter()函数返回日期属于第几个季度,month()函数返回日期属于第几个月,week()函数返回日期属于第几个星期,dayofmonth()函数返回日期属于当前月的第几天,hour()函数返回时间的小时,minute()函数返回时间的分钟,second()函数返回时间的秒。
示例:
mysql>selectnow(),year(now()),quarter(now()),month(now()),week(now()),dayofmonth(now()),hour(now()),minute(now()),second(now()); +---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ |now()|year(now())|quarter(now())|month(now())|week(now())|dayofmonth(now())|hour(now())|minute(now())|second(now())| +---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ |2016-08-2516:27:37|2016|3|8|34|25|16|27|37| +---------------------+-------------+----------------+--------------+-------------+-------------------+-------------+---------------+---------------+ 1rowinset(0.00sec)
3.2.1关于月的函数:
示例:
mysql>selectnow(),month(now()),monthname(now()); +---------------------+--------------+------------------+ |now()|month(now())|monthname(now())| +---------------------+--------------+------------------+ |2016-08-2516:29:37|8|August| +---------------------+--------------+------------------+ 1rowinset(0.00sec) //month()函数返回数字表示的月份,monthname()函数返回了英文表示的月份。
3.2.2关于星期的函数:
示例:
mysql>selectnow(),week(now()),weekofyear(now()),dayname(now()),dayofweek(now()),weekday(now()); +---------------------+-------------+-------------------+----------------+------------------+----------------+ |now()|week(now())|weekofyear(now())|dayname(now())|dayofweek(now())|weekday(now())| +---------------------+-------------+-------------------+----------------+------------------+----------------+ |2016-08-2516:34:35|34|34|Thursday|5|3| +---------------------+-------------+-------------------+----------------+------------------+----------------+ 1rowinset(0.00sec)
3.2.3关于天的函数:
示例:
mysql>selectnow(),dayofyear(now()),dayofmonth(now()); +---------------------+------------------+-------------------+ |now()|dayofyear(now())|dayofmonth(now())| +---------------------+------------------+-------------------+ |2016-08-2516:37:12|238|25| +---------------------+------------------+-------------------+ 1rowinset(0.00sec)
3.2.4获取指定值的extract():
函数定义为:
extract(typefromdate) //上述函数会从日期和时间参数date中获取指定类型参数type的值。type的取值可以是:year,month,day,hour,minute和second
示例:
mysql>selectnow(),extract(yearfromnow())year,extract(monthfromnow())month,extract(dayfromnow())day,extract(hourfromnow())hour,extract(mi nutefromnow())minute,extract(secondfromnow())second; +---------------------+------+-------+------+------+--------+--------+ |now()|year|month|day|hour|minute|second| +---------------------+------+-------+------+------+--------+--------+ |2016-08-2516:43:45|2016|8|25|16|43|45| +---------------------+------+-------+------+------+--------+--------+ 1rowinset(0.00sec)
3.3计算日期和时间的函数:
3.3.1与默认日期和时间操作:
两个函数来实现与默认日期和时间的操作,分别为to_days()和from_days()
to_days(date):该函数计算日期参数date与默认日期和时间(0000年1月1日)之间的想个天数。
from_days(number):该函数计算从默认日期和时间(0000年1月1日)开始经历number天后的日期和时间。
示例:
mysql>selectnow(),to_days(now()),from_days(to_days(now())); +---------------------+----------------+---------------------------+ |now()|to_days(now())|from_days(to_days(now()))| +---------------------+----------------+---------------------------+ |2016-08-2516:50:30|736566|2016-08-25| +---------------------+----------------+---------------------------+ 1rowinset(0.00sec) //指定两个日期之间相隔的天数; mysql>selectnow(),datediff(now(),'2000-12-01'); +---------------------+------------------------------+ |now()|datediff(now(),'2000-12-01')| +---------------------+------------------------------+ |2016-08-2516:52:16|5746| +---------------------+------------------------------+ 1rowinset(0.00sec)
3.3.2与指定日期和时间操作:
adddate(date,n)函数:该函数计算日期参数date加上n天后的日期。
subdate(date,n)函数:该函数计算日期参数date减去n天后的日期。
adddate(d,intervalexprtype):返回日期参数d加上一段时间后的日期,表达式参数expr决定了时间的长度,参数type决定了所操作的对象。
subdate(d,intervalexprtype):返回日期参数d减去一段时间后的日期,表达式expr决定了时间的长度。参数type决定了所操作的对象。
addtime(time,n):计算时间参数time加上n秒后的时间。
subtime(time,n):计算时间参数time减去n秒后的时间。
示例一:
mysql>selectcurdate(),adddate(curdate(),5),subdate(curdate(),5); +------------+----------------------+----------------------+ |curdate()|adddate(curdate(),5)|subdate(curdate(),5)| +------------+----------------------+----------------------+ |2016-08-25|2016-08-30|2016-08-20| +------------+----------------------+----------------------+ 1rowinset(0.00sec)
示例二:
mysql>selectcurdate(),adddate(curdate(),interval'2,3'year_month),subdate(curdate(),interval'2,3'year_month); +------------+----------------------------------------------+----------------------------------------------+ |curdate()|adddate(curdate(),interval'2,3'year_month)|subdate(curdate(),interval'2,3'year_month)| +------------+----------------------------------------------+----------------------------------------------+ |2016-08-25|2018-11-25|2014-05-25| +------------+----------------------------------------------+----------------------------------------------+ 1rowinset(0.00sec)
示例三:
mysql>selectcurtime(),addtime(curtime(),5),subtime(curtime(),5); +-----------+----------------------+----------------------+ |curtime()|addtime(curtime(),5)|subtime(curtime(),5)| +-----------+----------------------+----------------------+ |17:12:21|17:12:26|17:12:16| +-----------+----------------------+----------------------+ 1rowinset(0.00sec)
4.使用系统信息函数:
selectversion(),database(),user();
示例:
mysql>selectversion(),database(),user(); +------------+------------+----------------+ |version()|database()|user()| +------------+------------+----------------+ |5.5.51-log|NULL|root@localhost| +------------+------------+----------------+ 1rowinset(0.00sec) //获取auto_increment约束的最后ID selectlast_insert_id();
以上所述是小编给大家介绍的MySQL中的常用函数,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!