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转化为小写 +----------------+ | 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(‘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函数   null   rom   expr   not   转化   一个   赋值   foo