目录
1. 字符串
1.1. mysql 截取字符串: left() ,right() 和IFNULL()用法
-
left() : 从字符串左边开始提取;
-
right() : 从字符串右边开始提取;
-
ifnull(expr1, expr2) :
- 若expr1 不为NULL,则返回 expr1;
- 否则,返回expr2;
select left('windy,2) as name; --name: wi select right('windy',2) as name; --name: dy select u.id, u.account, ifnull(u.amount, 0) as amount from sys_user u where u.id in (7,8);
参考:
1.2. CAST 与 CONVERT 函数
- MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值,并产生另一个类型的值。两者具体的语法如下:
CAST(value as type);
CONVERT(value, type);
--示例
MySQL [(none)]> SELECT CONVERT('23',SIGNED);
+----------------------+
| CONVERT('23',SIGNED) |
+----------------------+
| 23 |
+----------------------+
MySQL [(none)]> SELECT CAST('125e342.83' AS signed);
+------------------------------+
| CAST('125e342.83' AS signed) |
+------------------------------+
| 125 |
+------------------------------+
参考:
2. 排序
2.1. field()
- field()
MySQL中的field()函数,可以用来对SQL中查询结果集进行指定顺序排序。select * from ta order by field(name,'seiki','iris','xut'); +----+--------+------+-------+ | id | name | age | class | +----+--------+------+-------+#不在str1,str2,str3中的内容,放在最前面返回,str值相同按照主键的顺序 | 5 | xuding | 55 | a5 | | 11 | xuding | 50 | a0 | | 3 | seiki | 33 | a3 | | 4 | seiki | 44 | a4 | | 9 | seiki | 36 | a6 | | 10 | seiki | 48 | a8 | | 1 | iris | 11 | a1 | | 2 | iris | 22 | a2 | | 7 | iris | 12 | a2 | | 8 | iris | 24 | a4 | | 6 | xut | 66 | a6 | | 12 | xut | 77 | a7 | +----+--------+------+-------+ 12 rows in set (0.00 sec)