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. (root@127.0.0.1) [test]> select * from city;
   
2. +-----------------------------------------+
   
3. | name |
   
4. +-----------------------------------------+
   
5. | 北京市北京市北京北京北京市 |
   
6. | tom is a good tom boy |
   
7. +-----------------------------------------+
   
8. 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)