mysql函数的使用

最近总感觉sql语句不对劲，所以就看了一些官方文档发现了一些以前没有注意的函数：感觉在查询的时候可以用得上，毕竟是内置函数，用起来效率应该会好一些的：

 FIND_IN_SET(str,strlist)

mysql> SELECT FIND_IN_SET('b','a,b,c,d');
        -> 2

如：SELECT FIND_IN_SET(a.roleId,r.roleName), a.* from t_admin a ,t_role r where a.roleId = r.roleId AND FIND_IN_SET(a.roleId,r.roleId);

代替in、not in

 

 INSTR(str,substr)

mysql> SELECT INSTR('foobarbar', 'bar');
        -> 4

如：select a.* FROM  t_admin a where INSTR(a.account,'发达');

可以用于模糊查询 代替like  提高效率

 LOCATE(substr,str), LOCATE(substr,str,pos)

mysql> SELECT LOCATE('xbar', 'foobar');
        -> 0
mysql> SELECT LOCATE('bar', 'foobarbar', 5);
        -> 7

select a.* FROM  t_admin a where LOCATE('发达',a.account);

可以用于模糊查询 代替like  提高效率

• 
  

  
  
  select  
      substring_index(email,'@',-1) as domain,  
      count(distinct email) as count,  
      count(distinct email)  
          / (select count(distinct email) from t_user where email like '%@%')  
          * 100 as percent  
  from t_user  
  where email like '%@%'   
  group by 1   
  order by 3 ASC   
  limit 10; 

select  
    substring_index(email,'@',-1) as domain,  
    count(distinct email) as count,  
    count(distinct email)  
        / (select count(distinct email) from t_user where email like '%@%')  
        * 100 as percent  
from t_user  
where email like '%@%'   
group by 1   
order by 3 ASC   
limit 10; 

**********************************************

6个常用的Mysql字符串连接函数

有时直接用mysql的字符串函数比查出来再用java字符串函数来处理简单的多，在这里收集了6个常用的Mysql字符串连接函数,以备有用之时。 1、concat()函数   1.1 MySQL的concat函数可以连接一个或者多个字符串,如       mysql> select concat('10');       +--------------+       | concat('10') |       +--------------+       | 10           |       +--------------+       1 row in set (0.00 sec)       mysql> select concat('11','22','33');       +------------------------+       | concat('11','22','33') |       +------------------------+       | 112233                 |       +------------------------+       1 row in set (0.00 sec)       而Oracle的concat函数只能连接两个字符串       SQL> select concat('11','22') from dual;   1.2 MySQL的concat函数在连接字符串的时候，只要其中一个是NULL,那么将返回NULL       mysql> select concat('11','22',null);       +------------------------+       | concat('11','22',null) |       +------------------------+       | NULL                   |       +------------------------+       1 row in set (0.00 sec)       而Oracle的concat函数连接的时候，只要有一个字符串不是NULL,就不会返回NULL       SQL> select concat('11',NULL) from dual;       CONCAT       --       11 2、concat_ws()函数, 表示concat with separator,即有分隔符的字符串连接   如连接后以逗号分隔       mysql> select concat_ws(',','11','22','33');       +-------------------------------+       | concat_ws(',','11','22','33') |       +-------------------------------+       | 11,22,33                      |       +-------------------------------+       1 row in set (0.00 sec)      和concat不同的是, concat_ws函数在执行的时候,不会因为NULL值而返回NULL       mysql> select concat_ws(',','11','22',NULL);       +-------------------------------+       | concat_ws(',','11','22',NULL) |       +-------------------------------+       | 11,22                         |       +-------------------------------+       1 row in set (0.00 sec) 3、group_concat()可用来行转列, Oracle没有这样的函数   完整的语法如下   group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])   如下例子   mysql> select * from aa;   +------+------+   | id   | name |   +------+------+   |    1 | 10   |   |    1 | 20   |   |    1 | 20   |   |    2 | 20   |   |    3 | 200  |   |    3 | 500  |   +------+------+   6 rows in set (0.00 sec)   3.1 以id分组，把name字段的值打印在一行，逗号分隔(默认)       mysql> select id,group_concat(name) from aa group by id;       +------+--------------------+       | id   | group_concat(name) |       +------+--------------------+       |    1 | 10,20,20           |       |    2 | 20                 |       |    3 | 200,500            |       +------+--------------------+       3 rows in set (0.00 sec)   3.2 以id分组，把name字段的值打印在一行，分号分隔       mysql> select id,group_concat(name separator ';') from aa group by id;       +------+----------------------------------+       | id   | group_concat(name separator ';') |       +------+----------------------------------+       |    1 | 10;20;20                         |       |    2 | 20                               |       |    3 | 200;500                          |       +------+----------------------------------+       3 rows in set (0.00 sec)   3.3 以id分组，把去冗余的name字段的值打印在一行，逗号分隔       mysql> select id,group_concat(distinct name) from aa group by id;       +------+-----------------------------+       | id   | group_concat(distinct name) |       +------+-----------------------------+       |    1 | 10,20                       |       |    2 | 20                          |       |    3 | 200,500                     |       +------+-----------------------------+       3 rows in set (0.00 sec)   3.4 以id分组，把name字段的值打印在一行，逗号分隔,以name排倒序       mysql> select id,group_concat(name order by name desc) from aa group by id;       +------+---------------------------------------+       | id   | group_concat(name order by name desc) |       +------+---------------------------------------+       |    1 | 20,20,10                              |       |    2 | 20                                    |       |    3 | 500,200                               |       +------+---------------------------------------+       3 rows in set (0.00 sec) 4、repeat()函数，用来复制字符串,如下'ab'表示要复制的字符串，2表示复制的份数   mysql> select repeat('ab',2);   +----------------+   | repeat('ab',2) |   +----------------+   | abab           |   +----------------+   1 row in set (0.00 sec)   又如   mysql> select repeat('a',2);   +---------------+   | repeat('a',2) |   +---------------+   | aa            |   +---------------+   1 row in set (0.00 sec) 5、SPACE(N) 函数。生成N个空格，如   mysql> select space(3);   +----------+   | space(3) |   +----------+   |          |   +----------+   1 row in set (0.00 sec) 6、STRCMP(STR1,STR2) 字符串比较函数,该函数和字符集有关系，默认区分大小写   若STR1和STR2相同, 则返回 0,   若STR1小于STR2,   则返回 -1,   若STR1大于STR2,   则返回 1   mysql> select strcmp('abc','abc');   +---------------------+   | strcmp('abc','abc') |   +---------------------+   |                   0 |   +---------------------+   1 row in set (0.00 sec)   mysql> select strcmp('a','ab');   +------------------+   | strcmp('a','ab') |   +------------------+   |               -1 |   +------------------+   1 row in set (0.00 sec)   mysql> select strcmp('abc','ab');   +--------------------+   | strcmp('abc','ab') |   +--------------------+   |                  1 |   +--------------------+   1 row in set (0.00 sec)