Chinaunix首页 | 论坛 | 博客
  • 博客访问: 939399
  • 博文数量: 119
  • 博客积分: 6248
  • 博客等级: 准将
  • 技术积分: 1419
  • 用 户 组: 普通用户
  • 注册时间: 2008-08-08 14:14
文章分类

全部博文(119)

文章存档

2014年(1)

2012年(1)

2011年(2)

2010年(22)

2009年(81)

2008年(12)

分类: LINUX

2009-09-09 14:23:07

最新在看关于mysql书籍,看到了关于mysql函数这一章,感觉东西很多,于是总结下,方便以后的查询.
 
1.字符串函数
  1)concat(s1,s2,...sn)函数:把传入的参数连接成一个字符串,任何字符串与null进行连接的结果都
                           是null.
 
    eg:   mysql>select concat(‘aaa’,'bbb','ccc'),concat(‘aaa’,null);
          +---------------------------+--------------------+
          | concat('aaa','bbb','ccc') | concat('aaa',null) |
          +---------------------------+--------------------+
          | aaabbbccc                 | NULL               |
          +---------------------------+--------------------+
 
  2)insert(str,x,y,instr)函数:将字符串str从第X位置开始,Y个字符长的字串替换成字符串instr.
 
    eg:   mysql> select insert ('beijing2008you',12,3,'me');
          +-------------------------------------+
          | insert ('beijing2008you',12,3,'me') |
          +-------------------------------------+
          | beijing2008me                       |
          +-------------------------------------+
 
  3)lower(str)和upper(str)函数:把字符串转化成小写或者大写.
 
    eg:   mysql> select lower('BEIJING2008'),upper('beijing2008');
          +----------------------+----------------------+
          | lower('BEIJING2008') | upper('beijing2008') |
          +----------------------+----------------------+
          | beijing2008          | BEIJING2008          | 
          +----------------------+----------------------+
 
  4)left(str,x)和right(str,x)函数:分别返回字符串最左边的x个字符和最右边的x个字符,如果第二个
                                 参数是null,那么将不返回任何字符串.
    eg:  mysql> select left('beijing2008',7),left('beijing',null),right
                ('beijing2008',4);
         +-----------------------+----------------------+------------------------+
         | left('beijing2008',7) | left('beijing',null) | right('beijing2008',4) |
         +-----------------------+----------------------+------------------------+
         | beijing               | NULL                 | 2008                   | 
         +-----------------------+----------------------+------------------------+
 
 5)lpad(str,n,pad)和rpad(str,n,pad)函数:用字符串pad对str最左边和最右边进行填充,直到长度为
                                        n个字符长度.
    eg:  mysql> select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
         +---------------------------+---------------------------+
         | lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
         +---------------------------+---------------------------+
         | beijingbeijingbe2008      | beijing2008200820082      | 
         +---------------------------+---------------------------+
 
 6)ltrim(str)和rtrim(str)函数:去掉字符串str左侧和右侧空格.
 
    eg:  mysql> select ltrim(' |beijing'),rtrim('beijing| ');
         +--------------------+--------------------+
         | ltrim(' |beijing') | rtrim('beijing| ') |
         +--------------------+--------------------+
         | |beijing           | beijing|           |
         +--------------------+--------------------+
 
 7)repeat(str,x)函数:返回str重复x次的结果.
  
   eg:  mysql> select repeat('mysql ',3);
        +--------------------+
        | repeat('mysql ',3) |
        +--------------------+
        | mysql mysql mysql  |
        +--------------------+
 
 8)replace(str,a,b)函数:用字符串b替换字符串str中所有出现的字符串a.
 
  eg:  mysql> select replace('beijing_2010','_2010','2008');
       +----------------------------------------+
       | replace('beijing_2010','_2010','2008') |
       +----------------------------------------+
       | beijing2008                            |
       +----------------------------------------+
 
 9)strcmp(s1,s2)函数:比较字符串s1和s2的ASCLL码值的大小.s1
                    s1>s2,返回1.
 
  eg:  mysql> select strcmp('a','b'),strcmp('b','b'),strcmp('c','b');
       +-----------------+-----------------+-----------------+
       | strcmp('a','b') | strcmp('b','b') | strcmp('c','b') |
       +-----------------+-----------------+-----------------+
       |              -1 |               0 |               1 |
       +-----------------+-----------------+-----------------+
 
 10)trim(str)函数:去掉目标字符串的开头和结尾的空格.
 
  eg:  mysql> select trim(' $ beijing2008 $ ');
      +---------------------------+
      | trim(' $ beijing2008 $ ') |
      +---------------------------+
      | $ beijing2008 $           |
      +---------------------------+
 
 11)substring(str,x,y)函数:返回从字符串str中的第x位置起y个字符长度的字串.
 
  eg:  mysql> select substring('beijing2008',8,4),substring('beijing2008',1,7);
       +------------------------------+------------------------------+
       | substring('beijing2008',8,4) | substring('beijing2008',1,7) |
       +------------------------------+------------------------------+
       | 2008                         | beijing                      |
       +------------------------------+------------------------------+
 
 今天就先写这么多,以后陆续更新






        
                                   
阅读(1784) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~