mysql 取和_mysql函数和操作符

mysql> select mod(29,9); //取模函数+-----------+

| mod(29,9) |

+-----------+

| 2 |

+-----------+

1 row in set (0.00sec)

mysql> select mod(29,2);+-----------+

| mod(29,2) |

+-----------+

| 1 |

+-----------+

1 row in set (0.00sec)

mysql> select 2 between 1 and 10; //between xx andxx+--------------------+

| 2 between 1 and 10 |

+--------------------+

| 1 |

+--------------------+

1 row in set (0.06sec)

mysql> select 20 between 1 and 10;+---------------------+

| 20 between 1 and 10 |

+---------------------+

| 0 |

+---------------------+

1 row in set (0.00sec)

mysql> select 20 not between 1 and 10; //not between xx andxx+-------------------------+

| 20 not between 1 and 10 |

+-------------------------+

| 1 |

+-------------------------+

1 row in set (0.00sec)

mysql> select 20 not between 1 and 50;+-------------------------+

| 20 not between 1 and 50 |

+-------------------------+

| 0 |

+-------------------------+

1 row in set (0.00sec)

mysql> select greatest(1,2,56,7,5); //greatest函数，在一串数字中取最大值+----------------------+

| greatest(1,2,56,7,5) |

+----------------------+

| 56 |

+----------------------+

1 row in set (0.08sec)

mysql> select greatest('a','b','c','d','e');//字母取最大值+-------------------------------+

| greatest('a','b','c','d','e') |

+-------------------------------+

| e |

+-------------------------------+

1 row in set (0.35sec)ISNULL(expr) //是空值

如expr 为NULL，那么ISNULL() 的返回值为1，否则返回值为 0mysql> select isnull(5); //是空值，为真则返回1，否则返回0+-----------+

| isnull(5) |

+-----------+

| 0 |

+-----------+

1 row in set (0.00sec)

mysql> select isnull(null);;+--------------+

| isnull(null) |

+--------------+

| 1 |

+--------------+

1 row in set (0.00sec)

LEAST(value1,value2,...)

在有两个或多个参数的情况下， 返回值为最小 (最小值) 参数

mysql> select least(2,0,1,-9,5,4);+---------------------+

| least(2,0,1,-9,5,4) |

+---------------------+

| -9 |

+---------------------+

1 row in set (0.02sec)

mysql> select least('a','b','c','d','e');+----------------------------+

| least('a','b','c','d','e') |

+----------------------------+

| a |

+----------------------------+

1 row in set (0.00sec)

mysql> select (case 1 when 1 then 'one' //case语法-> when 2 then 'two'

-> else 'more'

-> end) ascid;+-----+

| cid |

+-----+

| one |

+-----+

1 row in set (0.00sec)//案例解析select 'AAA', //标记1

(case cid when '3' then 'xxxx' when '4' then 'xxxx' when '5' then 'xxxx' else cid end ) as 渠道名称, //标记2

intdateas注册日期,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赋值为xxxx4)elsecid，当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 |

+--------------------+

1 row in set (0.00sec)

mysql> select if(1<5,'yes','no');+--------------------+

| if(1<5,'yes','no') |

+--------------------+

| yes |

+--------------------+

1 row in set (0.00sec)//CONCAT(str1,str2,...)

mysql> select concat('my','sql'); //mysql+--------------------+

| concat('my','sql') |

+--------------------+

| mysql |

+--------------------+

1 row in set (0.38sec)

mysql> select concat('my','null','sql'); //mynullsql+---------------------------+

| concat('my','null','sql') |

+---------------------------+

| mynullsql |

+---------------------------+

1 row in set (0.00sec)

mysql> select concat('my',null,'sql'); //NULL

+-------------------------+

| concat('my',null,'sql') |

+-------------------------+

| NULL |

+-------------------------+

1 row in set (0.00sec)

mysql> select concat(14.3); //14.3

+--------------+

| concat(14.3) |

+--------------+

| 14.3 |

+--------------+

1 row in set (0.00sec)

mysql> select concat(14.3,25); //14.325

+-----------------+

| concat(14.3,25) |

+-----------------+

| 14.325 |

+-----------------+

1 row in set (0.00sec)//INSTR(str,substr)

返回字符串str中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒

mysql> select instr('foobarbar','bar');+--------------------------+

| instr('foobarbar','bar') |

+--------------------------+

| 4 |

+--------------------------+

1 row in set (0.35sec)

mysql> select lower('MySQL'); //lower和lcase转化为小写+----------------+

| lower('MySQL') |

+----------------+

| mysql |

+----------------+

1 row in set (0.00sec)

mysql> select lcase('MySQL');+----------------+

| lcase('MySQL') |

+----------------+

| mysql |

+----------------+

1 row in set (0.00sec)

mysql> select left('foobar',4); //从左向右取数据，取4个数据+------------------+

| left('foobar',4) |

+------------------+

| foob |

+------------------+

1 row in set (0.00sec)

mysql> select right('foobar',4); //从右向左取数据，取4个数据+-------------------+

| right('foobar',4) |

+-------------------+

| obar |

+-------------------+

1 row in set (0.36sec)

mysql> select length('mysql'); //length求字符串的长度+-----------------+

| length('mysql') |

+-----------------+

| 5 |

+-----------------+

1 row in set (0.00sec)//返回字符串 str，其引导空格字符被删除

mysql> select ltrim('bar') as str; //ltrim删除左边的空格引导字符+------+

| str |

+------+

| bar |

+------+

1 row in set (0.00sec)

mysql> select rtrim('bar') as str; //rtrim删除右边的空格引导字符+-------+

| str |

+-------+

| bar |

+-------+

1 row in set (0.05sec)

mysql> select trim('bar') as str; //trim删除2边的空格引导符+------+

| str |

+------+

| bar |

+------+

1 row in set (0.00sec)//SUBSTRING

substring(str, pos); substring(str, pos, len)

从字符串的第pos个字符位置开始取，取len个数据，直到结束。

mysql> select substring('example',4,2);+--------------------------+

| substring('example',4,2) |

+--------------------------+

| mp |

+--------------------------+

1 row in set (0.00 sec)