mysql函數和操作符

標籤：mysql函數   null   rom   expr   not   轉化   一個   賦值   foo   

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，否則傳回值為 0mysql> 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賦值為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                 |+--------------------+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)//SUBSTRINGsubstring(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函數和操作符