mysql常用函数

一,字符串函数

  

mysql字符串函数
函数功能
concat(s1,s2,s3,..sn)连接s1,s2...sn为一个字符串
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,n,pad)用字符串pad对str最左边进行填充,直到长度为n个字符长度
rpad(str,n,pad)用字符串pad对str最右边进行填充,直到长度为n个字符长度
ltrim(str)去掉字符串str左侧空格
rtrim(str)去掉字符串str行尾的空格
repeat(str,x)返回str重复x次的结果
replace(str,a,b)用字符串b替换字符串str中所有的a
strcmp(s1,s2)比较字符串s1和s2
trim(str)去掉字符串尾部和首部的空格
substring(str,x,y)返回从字符串str的x位置起y个字符长度的字串


  1,concat(s1,s2,...sn)

      把“aa”,"bb"连接,注:任何字符串和null连接都为null

mysql> select concat("aa","bb"),concat("aa",null);
+-------------------+-------------------+
| concat("aa","bb") | concat("aa",null) |
+-------------------+-------------------+
| aabb              | NULL              |
+-------------------+-------------------+
1 row in set (0.01 sec)


  2,insert(str,x,y,instr)

    将字符串str从x位置开始,y个字符长的字串替换为instr

  

mysql> select insert('beijing2008you',12,3,'me');
+------------------------------------+
| insert('beijing2008you',12,3,'me') |
+------------------------------------+
| beijing2008me                      |
+------------------------------------+
1 row in set (0.00 sec)


   3,lower(str)和upper(str)

   大小写转换,在字符串比较中,通常要将比较的字符串全部转换为大写或者小写

mysql> select lower('ABc'),upper('abC');
+--------------+--------------+
| lower('ABc') | upper('abC') |
+--------------+--------------+
| abc          | ABC          |
+--------------+--------------+
1 row in set (0.00 sec)

    4,left(str,x)和right(str,x)

 分别返回字符串最左边的x个字符和最右边y个字符,:如果第二个参数是null,那么将不返回任何字符串

mysql> select left('abc',2),right('abc',2),left('abc',null);
+---------------+----------------+------------------+
| left('abc',2) | right('abc',2) | left('abc',null) |
+---------------+----------------+------------------+
| ab            | bc             | NULL             |
+---------------+----------------+------------------+
1 row in set (0.01 sec)

    5,lpad(str,n,pad)和rpad(str,n,pad)

 用字符串pad对str最左边和最右边进行填充,直到长度为n个字符长度

 

mysql> select lpad('abc',6,'hj'),rpad('abc',6,'lm');
+--------------------+--------------------+
| lpad('abc',6,'hj') | rpad('abc',6,'lm') |
+--------------------+--------------------+
| hjhabc             | abclml             |
+--------------------+--------------------+
1 row in set (0.00 sec)

     6,ltrim,rtrim,trim

   分别为去最左侧空格,最右侧空格,和去首尾空格

 

mysql> select concat(trim("  abc   "),length(trim('  abc   '))) as trim,concat(ltrim('   abc    '),length(ltrim('   abc   '))) as ltrim,rtrim('   abc   ');
+------+----------+--------------------+
| trim | ltrim    | rtrim('   abc   ') |
+------+----------+--------------------+
| abc3 | abc    6 |    abc             |
+------+----------+--------------------+
1 row in set (0.00 sec)


    为了更直观的看到trim和ltrim的区别我在后面拼接了长度。


    7,repeat(str,x)

  返回str重复x次的结果

 

mysql> select repeat('abc',3);
+-----------------+
| repeat('abc',3) |
+-----------------+
| abcabcabc       |
+-----------------+
1 row in set (0.00 sec)


     8,replace(str,a,b)

  用字符串b替换字符串str中所有出现的字符串a

 

mysql> select replace('abcabca','a','e');
+----------------------------+
| replace('abcabca','a','e') |
+----------------------------+
| ebcebce                    |
+----------------------------+
1 row in set (0.00 sec)
    

   9,strcmp(s1,s2)

比较字符串s1和s2的ascii码值的大小,如果s1比s2小则返回-1,如果s1和s2相等,则返回0,如果s1大于s2,则返回1

mysql> select strcmp('abc','acb'),strcmp('abc','abc'),strcmp('cab','abc');
+---------------------+---------------------+---------------------+
| strcmp('abc','acb') | strcmp('abc','abc') | strcmp('cab','abc') |
+---------------------+---------------------+---------------------+
|                  -1 |                   0 |                   1 |
+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)

二,数值函数


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位小数的结果


三,日期和时间函数

     

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的月份名
date_format(date,fmt)返回按字符串fmt格式化日期date值
date_add(date,interval expr type)返回一个日期或时间值加上一个时间间隔的时间值
datediff(expr,expr2)返回起始时间expr和结束时间expr2之间的天数


1,curdate(),curtime(),now(),unix_timestamp(date)

mysql> select curdate(),curtime(),now(),unix_timestamp(now());
+------------+-----------+---------------------+-----------------------+
| curdate()  | curtime() | now()               | unix_timestamp(now()) |
+------------+-----------+---------------------+-----------------------+
| 2016-09-29 | 18:40:58  | 2016-09-29 18:40:58 |            1475145658 |
+------------+-----------+---------------------+-----------------------+
1 row in set (0.01 sec)

2,, from_unixtime

mysql> select from_unixtime(1475145658);
+---------------------------+
| from_unixtime(1475145658) |
+---------------------------+
| 2016-09-29 18:40:58       |
+---------------------------+
1 row in set (0.00 sec)

3,week(date),year(date),hour(time),minute(time),monthname(date)

mysql> select week(now()),year(now()),hour(curtime()),minute(curtime()),monthname(now());
+-------------+-------------+-----------------+-------------------+------------------+
| week(now()) | year(now()) | hour(curtime()) | minute(curtime()) | monthname(now()) |
+-------------+-------------+-----------------+-------------------+------------------+
|          39 |        2016 |              18 |                46 | September        |
+-------------+-------------+-----------------+-------------------+------------------+
1 row in set (0.00 sec)

4,dare_add(date,interval expr type)

  在了解增加时间前我们先看一下有那些时间间隔:

   

日期时间间隔
表达式类型描述格式
hour小时hh
minutemm
secondss
yearYY
monthMM
daydd
year_month年和月YY-MM
day_hour日和小时DD hh
day_minute日和分钟DD hh:mm
day_scond日和秒DD hh:mm:ss
hour_minute小时和分hh:mm
hour_second小时和秒hh:ss
minute_second分钟和秒mm:ss


    例子:第一列返回当前时间,第二列返回距离当前日期31天后的时间,第三列返回距当前日期一年两个月后的日期时间

mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneYear_twoMonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneYear_twoMonth |
+---------------------+---------------------+------------------------+
| 2016-09-29 19:11:50 | 2016-10-30 19:11:50 | 2017-11-29 19:11:50    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

     当然,我们也可以得到当前日期以前的

  例子,第一列返回当前时间,第二列返回距离当前日期31天前的时间,第三列返回距当前日期一年两个月前的日期时间

mysql> select now() current,date_add(now(),interval -31 day) after31days,date_add(now(),interval '-1_-2' year_month) after_oneYear_twoMonth;
+---------------------+---------------------+------------------------+
| current             | after31days         | after_oneYear_twoMonth |
+---------------------+---------------------+------------------------+
| 2016-09-29 19:14:04 | 2016-08-29 19:14:04 | 2015-07-29 19:14:04    |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)

  5,计算两个日期之间相差的天数

mysql> select datediff(now(),'2015-12-15');
+------------------------------+
| datediff(now(),'2015-12-15') |
+------------------------------+
|                          289 |
+------------------------------+
1 row in set (0.00 sec)

四,流程函数

  

mysql流程函数
函数功能
if(value,t,f)如果value是真,返回t,否则返回f
ifnull(value1,value2)如果value1不为空,返回value1,否则返回value2
case when[value] then[result]...else[default] end如果value1是真,返回result1,否则返回default
case[expr] when[value] then[result]...else[default]end如果expr等于value1,返回result1,否则返回default

   例子:下面模拟了对职员薪水进行分类,首先创建并初始化一个职员薪水表

mysql> create table salary(user_id int,salary decimal(9,2));
Query OK, 0 rows affected (0.48 sec)

mysql> insert into salary(user_id,salary)values(1,1000),(2,2000),(3,3000),(4,4000),(5,5000),(1,null);
Query OK, 6 rows affected (0.10 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select user_id,salary from salary;
+---------+---------+
| user_id | salary  |
+---------+---------+
|       1 | 1000.00 |
|       2 | 2000.00 |
|       3 | 3000.00 |
|       4 | 4000.00 |
|       5 | 5000.00 |
|       1 |    NULL |
+---------+---------+
6 rows in set (0.00 sec)

     
      将salary>2000的标记为h,其他标记为l

 

mysql> select if(salary>2000,'h','l') grade from salary;
+-------+
| grade |
+-------+
| l     |
| l     |
| h     |
| h     |
| h     |
| l     |
+-------+
6 rows in set (0.00 sec)

       将null值替换为0

mysql> select ifnull(salary,0) sal from salary;
+---------+
| sal     |
+---------+
| 1000.00 |
| 2000.00 |
| 3000.00 |
| 4000.00 |
| 5000.00 |
|    0.00 |
+---------+
6 rows in set (0.00 sec)

   将salary<1000的标记为l,1000~2000的标记为m,>2000标记为h

mysql> select case when ifnull(salary,0)<1000 then 'l' when salary<2000 then 'm' else 'h' end sal from salary;
+-----+
| sal |
+-----+
| m   |
| h   |
| h   |
| h   |
| h   |
| l   |
+-----+
6 rows in set (0.00 sec)


五,其他常用的函数

   

mysql其他常用函数
函数功能
database()返回当前数据库名
version()返回当前数据库版本
user()返回当前数据库登陆用户名
inet_aton(ip)返回ip地址的数字表示
inet_ntoa(num)返回数字代表的ip地址
password(str)返回字符串str的加密版本
md5()返回字符串str的md5值


   1,database(),version(),user()

mysql> select database(),version(),user();
+------------+-------------------------+----------------+
| database() | version()               | user()         |
+------------+-------------------------+----------------+
| test1      | 5.7.15-0ubuntu0.16.04.1 | root@localhost |
+------------+-------------------------+----------------+
1 row in set (0.00 sec)

   2,inet_aton , inet_nton

mysql> select inet_aton('192.168.1.1');
+--------------------------+
| inet_aton('192.168.1.1') |
+--------------------------+
|               3232235777 |
+--------------------------+
1 row in set (0.02 sec)

mysql> select inet_ntoa(3232235777 );
+------------------------+
| inet_ntoa(3232235777 ) |
+------------------------+
| 192.168.1.1            |
+------------------------+
1 row in set (0.00 sec)
        inet_aton和inet_nton函数的主要用途是将字符串的ip地址转换为数字表示的网络字节序,这样可以方便的进行ip或者网段的比较(根据ip地址转换成的数值去比较,而不能直接通过ip比较)。

   

     3, password(str)

    返回字符串str的加密版本,一个41位长的字符串,此函数指用来设置系统用户的密码,但是不能用来对应用的数据加密。如有应用方面有加密的需求,可以使用md5等加密实现。

  

mysql> select password('123456');
+-------------------------------------------+
| password('123456')                        |
+-------------------------------------------+
| *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

     4,md5(str)

    返回字符串str的md5值,常用来对数据加密

mysql> select md5('123456');
+----------------------------------+
| md5('123456')                    |
+----------------------------------+
| e10adc3949ba59abbe56e057f20f883e |
+----------------------------------+
1 row in set (0.00 sec)


 

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值