mysql> select mod(29,9);    //取模函数+-----------+| mod(29,9) |+-----------+|         2 |+-----------+1 row in set (0.00 sec)mysql> select mod(29,2);+-----------+| mod(29,2) |+-----------+|         1 |+-----------+1 row in set (0.00 sec)mysql> select 2 between 1 and 10;      //between xx  and  xx+--------------------+| 2 between 1 and 10 |+--------------------+|                  1 |+--------------------+1 row in set (0.06 sec)mysql> select 20 between 1 and 10;+---------------------+| 20 between 1 and 10 |+---------------------+|                   0 |+---------------------+1 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 |+-------------------------+1 row in set (0.00 sec)mysql> select 20 not between 1 and 50;+-------------------------+| 20 not between 1 and 50 |+-------------------------+|                       0 |+-------------------------+1 row in set (0.00 sec)mysql> select greatest(1,2,56,7,5); //greatest函数，在一串数字中取最大值+----------------------+| greatest(1,2,56,7,5) |+----------------------+|                   56 |+----------------------+1 row in set (0.08 sec)mysql> select greatest('a','b','c','d','e');//字母取最大值+-------------------------------+| greatest('a','b','c','d','e') |+-------------------------------+| e                             |+-------------------------------+1 row in set (0.35 sec)ISNULL(expr)     //是空值如expr 为NULL，那么ISNULL() 的返回值为 1，否则返回值为 0mysql> select isnull(5);    //是空值，为真则返回1，否则返回0+-----------+| isnull(5) |+-----------+|         0 |+-----------+1 row in set (0.00 sec)mysql> select isnull(null);;+--------------+| isnull(null) |+--------------+|            1 |+--------------+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 |+---------------------+1 row in set (0.02 sec)mysql> select least('a','b','c','d','e');+----------------------------+| least('a','b','c','d','e') |+----------------------------+| a                          |+----------------------------+1 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 |+-----+1 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赋值为xxxx4）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                 |+--------------------+1 row in set (0.00 sec)mysql> select if(1<5,'yes','no');+--------------------+| if(1<5,'yes','no') |+--------------------+| yes                |+--------------------+1 row in set (0.00 sec)//CONCAT(str1,str2,...)mysql> select concat('my','sql');   //mysql+--------------------+| concat('my','sql') |+--------------------+| mysql              |+--------------------+1 row in set (0.38 sec)mysql> select concat('my','null','sql');  //mynullsql+---------------------------+| concat('my','null','sql') |+---------------------------+| mynullsql                 |+---------------------------+1 row in set (0.00 sec)mysql> select concat('my',null,'sql');  //NULL+-------------------------+| concat('my',null,'sql') |+-------------------------+| NULL                    |+-------------------------+1 row in set (0.00 sec)mysql> select concat(14.3);   //14.3+--------------+| concat(14.3) |+--------------+| 14.3         |+--------------+1 row in set (0.00 sec)mysql> select concat(14.3,25);   //14.325+-----------------+| concat(14.3,25) |+-----------------+| 14.325          |+-----------------+1 row in set (0.00 sec)//INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。这和LOCATE()的双参数形式相同，除非参数的顺序被颠倒mysql> select instr('foobarbar','bar');+--------------------------+| instr('foobarbar','bar') |+--------------------------+|                        4 |+--------------------------+1 row in set (0.35 sec)mysql> select lower('MySQL');   //lower和lcase转化为小写+----------------+| lower('MySQL') |+----------------+| mysql          |+----------------+1 row in set (0.00 sec)mysql> select lcase('MySQL');+----------------+| lcase('MySQL') |+----------------+| mysql          |+----------------+1 row in set (0.00 sec)mysql> select left('foobar',4);   //从左向右取数据，取4个数据+------------------+| left('foobar',4) |+------------------+| foob             |+------------------+1 row in set (0.00 sec)mysql> select right('foobar',4);  //从右向左取数据，取4个数据+-------------------+| right('foobar',4) |+-------------------+| obar              |+-------------------+1 row in set (0.36 sec)mysql> select length('mysql');  //length求字符串的长度+-----------------+| length('mysql') |+-----------------+|               5 |+-----------------+1 row in set (0.00 sec)//返回字符串 str ，其引导空格字符被删除mysql> select ltrim('  bar') as str;   //ltrim删除左边的空格引导字符+------+| str  |+------+| bar  |+------+1 row in set (0.00 sec)mysql> select rtrim('  bar  ') as str;    //rtrim删除右边的空格引导字符+-------+| str   |+-------+|   bar |+-------+1 row in set (0.05 sec)mysql> select trim('  bar  ') as str;  //trim删除2边的空格引导符+------+| str  |+------+| bar  |+------+1 row in set (0.00 sec)//SUBSTRINGsubstring(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)