mysql常用函数--个人笔记

----ifnull   isnull函数

SELECT * FROM t3;
id name
1
2
3  NULL


SELECT IFNULL(NAME,'kkkk') FROM t3;

if函数

点击(此处)折叠或打开

1. mysql> select * from t3;
   +------+------+
   | id   | cj   |
   +------+------+
   |    1 |    2 |
   |    2 |    3 |
   |    3 |    3 |
   |    4 |    5 |
   |    5 |    5 |
   |    6 |    7 |
   +------+------+
   6 rows in set (0.00 sec)
   
   
   mysql> select *,if(id=cj,'eq','neq') from t3;
   +------+------+----------------------+
   | id   | cj   | if(id=cj,'eq','neq') |
   +------+------+----------------------+
   |    1 |    2 | neq                  |
   |    2 |    3 | neq                  |
   |    3 |    3 | eq                   |
   |    4 |    5 | neq                  |
   |    5 |    5 | eq                   |
   |    6 |    7 | neq                  |
   +------+------+----------------------+
   
   

substring_index函数

1. MySQL> select * from t1;
   +--------+---------------------+
   | userid | atime |
   +--------+---------------------+
   | 1 | 2013-08-12 11:05:25 |
   | 2 | 2013-08-12 11:05:29 |
   | 3 | 2013-08-12 11:05:32 |
   | 5 | 2013-08-12 11:05:34 |
   | 1 | 2013-08-12 11:05:40 |
   | 2 | 2013-08-12 11:05:43 |
   | 3 | 2013-08-12 11:05:48 |
   | 5 | 2013-08-12 11:06:03 |
   +--------+---------------------+
   8 rows in set (0.00 sec)
   
   
2. 
   
3. 其中userid不唯一，要求取表中每个userid对应的时间离现在最近的一条记录．初看到一个这条件一般都会想到借用临时表及添加主建借助于join操作之类的．
   给一个简方法：
4. MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid;
   +--------+---------------------+
   | userid | atime |
   +--------+---------------------+
   | 1 | 2013-08-12 11:05:40 |
   | 2 | 2013-08-12 11:05:43 |
   | 3 | 2013-08-12 11:05:48 |
   | 5 | 2013-08-12 11:06:03 |
   +--------+---------------------+
   4 rows in set (0.03 sec)
   
   
5. substring_index(col_name,"分隔符",n)    ----n表示前n部分
   

字符串出现的次数

(root@127.0.0.1) [test]> select * from city;
+-----------------------------------------+
| name |
+-----------------------------------------+
| 北京市北京市北京北京北京市 |
| tom is a good tom boy |
+-----------------------------------------+
2 rows in set (0.00 sec)

查看name中tom出现的次数
(root@127.0.0.1) [test]> select (length(name)-length(replace(name,"tom","")))/length("tom")  as count ,name from city;
+--------+-----------------------------------------+
| count  | name                                    |
+--------+-----------------------------------------+
| 0.0000 | 北京市北京市北京北京北京市              |
| 2.0000 | tom is a good tom boy                   |
+--------+-----------------------------------------+
2 rows in set (0.00 sec)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29096438/viewspace-1755476/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29096438/viewspace-1755476/