mysql函数的使用

创建时间：2013-07-21 投稿人：浏览次数：9688

最近总感觉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)

声明：该文观点仅代表作者本人，牛骨文系教育信息发布平台，牛骨文仅提供信息存储空间服务。

热门文章: CTF writeup 2_南邮网络攻防训...; SSM框架——详细整合教程（...; Linux Shell脚本编程－－curl命...; HttpClient使用详解; Java面试题全集（上）; JAVA设计模式之单例模式; java.lang.OutOfMemoryError: PermGen ...; TCP协议中的三次握手和四次...; form表单的两种提交方式，su...; String,StringBuffer与StringBuilder...

最新文章: Java之品优购课程讲义_day20（7）; 剑指 Offer - 8：跳台阶; Netty权威指南_札记02_NIO编程; mysql时间属性之时间戳和datetime之...; 虚拟现实或许可以拯救古埃及的“...; spring cloud服务注册中心eureka---集群...; Java SE 第六章; HTTP请求+数据库; HIDL学习笔记之HIDL C++（第二天）; ubuntu系统下指定tomcat运行时为JDK1.8...