MySql文本处理函数
来源:互联网 发布:网络h小说合集 编辑:程序博客网 时间:2024/05/16 18:58
函数的功能:
函数一般是在数据上执行的,它给数据的转换和处理提供了方便。
多数SQL语句可以方便的进行平台上的移植,但是函数在各个数据库平台上的差异较大,所以。在运用函数时需要对函数的功能进行注释。
mysql> select mod(29,9); //取模函数
+-----------+
| mod(29,9) |
+-----------+
| 2 |
+-----------+
row in set (0.00 sec)
mysql> select mod(29,2);
+-----------+
| mod(29,2) |
+-----------+
| 1 |
+-----------+
row in set (0.00 sec)
mysql> select 2 between 1 and 10; //between xx and xx
+--------------------+
| 2 between 1 and 10 |
+--------------------+
| 1 |
+--------------------+
row in set (0.06 sec)
mysql> select 20 between 1 and 10;
+---------------------+
| 20 between 1 and 10 |
+---------------------+
| 0 |
+---------------------+
row in set (0.00 sec)
mysql> select 20 not between 1 and 10; //not between xx and xx
+-------------------------+
| 20 not between 1 and 10 |
+-------------------------+
| 1 |
+-------------------------+
row in set (0.00 sec)
mysql> select 20 not between 1 and 50;
+-------------------------+
| 20 not between 1 and 50 |
+-------------------------+
| 0 |
+-------------------------+
row in set (0.00 sec)
mysql> select greatest(1,2,56,7,5); //greatest函数,在一串数字中取最大值
+----------------------+
| greatest(1,2,56,7,5) |
+----------------------+
| 56 |
+----------------------+
row in set (0.08 sec)
mysql> select greatest('a','b','c','d','e');//字母取最大值
+-------------------------------+
| greatest('a','b','c','d','e') |
+-------------------------------+
| e |
+-------------------------------+
row in set (0.35 sec)
ISNULL(expr) //是空值
如expr 为NULL,那么ISNULL() 的返回值为 1,否则返回值为 0
mysql> select isnull(5); //是空值,为真则返回1,否则返回0
+-----------+
| isnull(5) |
+-----------+
| 0 |
+-----------+
row in set (0.00 sec)
mysql> select isnull(null);;
+--------------+
| isnull(null) |
+--------------+
| 1 |
+--------------+
row in set (0.00 sec)
LEAST(value1,value2,...)
在有两个或多个参数的情况下, 返回值为最小 (最小值) 参数
mysql> select least(2,0,1,-9,5,4);
+---------------------+
| least(2,0,1,-9,5,4) |
+---------------------+
| -9 |
+---------------------+
row in set (0.02 sec)
mysql> select least('a','b','c','d','e');
+----------------------------+
| least('a','b','c','d','e') |
+----------------------------+
| a |
+----------------------------+
row in set (0.00 sec)
mysql> select (case 1 when 1 then 'one' //case语法
-> when 2 then 'two'
-> else 'more'
-> end) as cid;
+-----+
| cid |
+-----+
| one |
+-----+
row in set (0.00 sec)
//案例解析
select 'AAA', //标记1
(case cid when '3' then 'xxxx' when '4' then 'xxxx' when '5' then 'xxxx' else cid end ) as 渠道名称, //标记2
intdate as 注册日期,
from 表名 where intdate>= '20161020' and intdate<= '20161103'
//解析
1)标记2为一个语句
2)as将语句重命名为渠道名称
3)case语法结构:(case cid when '1' then 'xxxx' when '2' then 'xxxx' else cid end ) ,从cid中匹配到编号1的时候,返回的结果将1赋值为xxxx
4)else cid,当cid不为1,2时,将直接返回cid本身
//if函数
//IF(expr1,expr2,expr3)
如果 expr1 是TRUE,则 IF()的返回值为expr2; 否则返回值则为 expr3。
IF() 的返回值为数字值或字符串值,具体情况视其所在语境而定
mysql> select if(1>5,'yes','no'); //和excel中的if函数用法一致
+--------------------+
| if(1>5,'yes','no') |
+--------------------+
| no |
+--------------------+
row in set (0.00 sec)
mysql> select if(1<5,'yes','no');
+--------------------+
| if(1<5,'yes','no') |
+--------------------+
| yes |
+--------------------+
row in set (0.00 sec)
//CONCAT(str1,str2,...)
mysql> select concat('my','sql'); //mysql
+--------------------+
| concat('my','sql') |
+--------------------+
| mysql |
+--------------------+
row in set (0.38 sec)
mysql> select concat('my','null','sql'); //mynullsql
+---------------------------+
| concat('my','null','sql') |
+---------------------------+
| mynullsql |
+---------------------------+
row in set (0.00 sec)
mysql> select concat('my',null,'sql'); //NULL
+-------------------------+
| concat('my',null,'sql') |
+-------------------------+
| NULL |
+-------------------------+
row in set (0.00 sec)
mysql> select concat(14.3); //14.3
+--------------+
| concat(14.3) |
+--------------+
| 14.3 |
+--------------+
row in set (0.00 sec)
mysql> select concat(14.3,25); //14.325
+-----------------+
| concat(14.3,25) |
+-----------------+
| 14.325 |
+-----------------+
row in set (0.00 sec)
//INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同,除非参数的顺序被颠倒
mysql> select instr('foobarbar','bar');
+--------------------------+
| instr('foobarbar','bar') |
+--------------------------+
| 4 |
+--------------------------+
row in set (0.35 sec)
mysql> select lower('MySQL'); //lower和lcase转化为小写
MySQL的位置也可以为列名,这样的话,对整个列的大写字母都转成了小写。
+----------------+
| lower('MySQL') |
+----------------+
| mysql |
+----------------+
row in set (0.00 sec)
mysql> select lcase('MySQL');
+----------------+
| lcase('MySQL') |
+----------------+
| mysql |
+----------------+
row in set (0.00 sec)
mysql> select left('foobar',4); //从左向右取数据,取4个数据
+------------------+
| left('foobar',4) |
+------------------+
| foob |
+------------------+
row in set (0.00 sec)
mysql> select right('foobar',4); //从右向左取数据,取4个数据
+-------------------+
| right('foobar',4) |
+-------------------+
| obar |
+-------------------+
row in set (0.36 sec)
mysql> select length('mysql'); //length求字符串的长度
length后面也可以为列名。就对整个列取了长度
+-----------------+
| length('mysql') |
+-----------------+
| 5 |
+-----------------+
row in set (0.00 sec)
//返回字符串 str ,其引导空格字符被删除
mysql> select ltrim(' bar') as str; //ltrim删除左边的空格引导字符
+------+
| str |
+------+
| bar |
+------+
row in set (0.00 sec)
mysql> select rtrim(' bar ') as str; //rtrim删除右边的空格引导字符
+-------+
| str |
+-------+
| bar |
+-------+
row in set (0.05 sec)
mysql> select trim(' bar ') as str; //trim删除2边的空格引导符
+------+
| str |
+------+
| bar |
+------+
row in set (0.00 sec)
//SUBSTRING
substring(str, pos); substring(str, pos, len)
从字符串的第pos个字符位置开始取,取len个数据,直到结束。
mysql> select substring('example',4,2);
+--------------------------+
| substring('example',4,2) |
+--------------------------+
| mp |
+--------------------------+
row in set (0.00 sec)
- mysql 文本处理函数
- MySql文本处理函数
- MySQL常用文本处理函数
- 文本输入输出处理函数
- Matlab 文本处理函数
- Makefile文本处理函数
- Makefile文本处理函数
- Makefile文本处理函数
- Makefile文本处理函数
- mysql 文本和日期处理
- Hibernate 处理 mysql 大文本
- 文本处理中的失效函数
- R 字符函数 文本处理
- Makefile字符串、文本处理函数
- c++ 文本处理基础函数
- 【转】Makefile文本处理函数
- 自定义处理函数,文本处理,汉字截取
- MySQL学习足迹记录09--常用文本,日期,数值处理函数
- Java泛型用法总结
- 静态代码块和静态域初始化顺序(java7核心技术与最佳实践)
- 关于echarts和ajax的简单运用
- java-支付宝支付-支付宝退款
- Local Binary Convolutional Neural Networks ---卷积深度网络移植到嵌入式设备上?
- MySql文本处理函数
- 深入解读缓存(一)——缓存的力量
- 区块链的那些事,你知道和不知道的都在这里!
- Git常见命令
- CentOS7 安装 RabbitMQ
- 2串口收发数据
- Exception & Issue for Selenium
- 【模板】(新)快速幂+快速乘
- DeepLearing学习笔记-从逻辑回归出发