MySQL Functions and operators

MySQL> select mod (29,9);//Modulo function+-----------+|         MoD (29,9) |+-----------+| 2 |+-----------+row in Set (0.00sec)MySQL> select mod (29,2);+-----------+|         MoD (29,2) |+-----------+| 1 |+-----------+row in Set (0.00sec)MySQL> select 2 between 1 and 10;//between XX and xx+--------------------+|                  2 between 1 and |+--------------------+| 1 |+--------------------+row in Set (0.06sec)MySQL> select between 1 and 10;+---------------------+|                   Between 1 and |+---------------------+| 0 |+---------------------+row in Set (0.00sec)MySQL> select not between 1 and 10;//Not between XX and xx+-------------------------+|                       Not between 1 and |+-------------------------+| 1 |+-------------------------+row in Set (0.00sec)MySQL> select not between 1 and 50;+-------------------------+|                       Not between 1 and |+-------------------------+| 0 |+-------------------------+row in Set (0.00sec)MySQL> select Greatest (1,2,56,7,5);//greatest function to take the maximum value in a string of numbers+----------------------+|                   Greatest (1,2,56,7,5) |+----------------------+| |+----------------------+row in Set (0.08sec)MySQL> select Greatest (' A ', ' B ', ' C ', ' d ', ' e ');//Maximum number of letters+-------------------------------+| Greatest (' A ', ' B ', ' C ', ' d ', ' e ') |+-------------------------------+| E |+-------------------------------+row in Set (0.35sec) ISNULL (expr)//is a null valueIf expr is null, then the return value of IsNull () is 1, otherwise the return value is 0MySQL> select IsNull (5);//is null, returns 1 for true, otherwise returns 0+-----------+|         IsNull (5) |+-----------+| 0 |+-----------+row in Set (0.00sec)MySQL> select IsNull (NULL);;+--------------+| IsNullNULL) |+--------------+| 1 |+--------------+row in Set (0.00sec) LEAST (value1, value2,...in the case of two or more parameters, the return value is the minimum (minimum) parameterMySQL> Select Least (2,0,1,-9,5,4);+---------------------+|                  Least (2,0,1,-9,5,4) |+---------------------+| -9 |+---------------------+row in Set (0.02sec)MySQL> Select least (' A ', ' B ', ' C ', ' d ', ' e ');+----------------------------+| Least (' A ', ' B ', ' C ', ' d ', ' e ') |+----------------------------+| A |+----------------------------+row in Set (0.00sec)MySQL> select ( Case1 when 1 Then ' one '//Case SyntaxWhen 2 Then 'Else' More 'End) asCID;+-----+| CID |+-----+| One |+-----+row in Set (0.00sec)//Case AnalysisSelect ' AAA ',//Mark 1( CaseCID when ' 3 ' and ' xxxx ' when ' 4 ' then ' xxxx ' when ' 5 ' Then ' xxxx 'ElseCidEnd) asChannel name,//Mark 2Intdate asRegistration date,From table name where Intdate>= ' 20161020 ' and intdate<= ' 20161103 '//parsing1) Mark 2 as a statement2) As to rename the statement to the channel name3) Case Syntax structure: ( CaseCID when ' 1 ' and ' xxxx ' when ' 2 ' Then ' xxxx 'ElseCidEnd), when matched from CID to number 1, the returned result assigns a value of 1 to XXXX4)ElseCID, when the CID is not 1,2 o'clock, the CID itself will be returned directly//IF function//if (EXPR1,EXPR2,EXPR3)If Expr1 is true, theIFThe return value of () is expr2; otherwise the return value is EXPR3. IFThe return value of () is a numeric value or a string value, depending on the context in which it is locatedMySQL> selectif(1>5, ' yes ', ' no ');//consistent with the IF function usage in Excel+--------------------+|if(1>5, ' yes ', ' no ') |+--------------------+| No |+--------------------+row in Set (0.00sec)MySQL> selectif(1<5, ' yes ', ' no ');+--------------------+|if(1<5, ' yes ', ' no ') |+--------------------+| Yes |+--------------------+row in Set (0.00sec)//CONCAT (str1,str2,...)MySQL> select Concat (' My ', ' SQL ');//MySQL+--------------------+| Concat (' My ', ' sql ') |+--------------------+|MySQL|+--------------------+row in Set (0.38sec)MySQL> select Concat (' my ', ' null ', ' SQL ');//Mynullsql+---------------------------+| Concat (' my ', ' null ', ' SQL ') |+---------------------------+| Mynullsql |+---------------------------+row in Set (0.00sec)MySQL> select Concat (' my ',NULL, ' SQL ');//NULL+-------------------------+| Concat (' my ',NULL, ' SQL ') |+-------------------------+|NULL|+-------------------------+row in Set (0.00sec)MySQL> select concat (14.3);//14.3+--------------+| Concat (14.3) |+--------------+| 14.3 |+--------------+row in Set (0.00sec)MySQL> select Concat (14.3,25);//14.325+-----------------+| Concat (14.3,25) |+-----------------+| 14.325 |+-----------------+row in Set (0.00sec)//INSTR (STR,SUBSTR)returns the first occurrence of a substring of string str. This is the same as the two-parameter form of locate (), unless the order of the arguments is reversedMySQL> select InStr (' Foobarbar ', ' Bar ');+--------------------------+|                        InStr (' Foobarbar ', ' Bar ') |+--------------------------+| 4 |+--------------------------+row in Set (0.35sec)MySQL> select lower (' MySQL ');//Lower and LCase converted to lowercase+----------------+| Lower (' MySQL ') |+----------------+|MySQL|+----------------+row in Set (0.00sec)MySQL> select LCase (' MySQL ');+----------------+| LCase (' MySQL ') |+----------------+|MySQL|+----------------+row in Set (0.00sec)MySQL> select Left (' Foobar ', 4);//fetch data from left to right, fetch 4 data+------------------+| Left (' Foobar ', 4) |+------------------+| Foob |+------------------+row in Set (0.00sec)MySQL> select Right (' Foobar ', 4);//fetch data from right to left, fetch 4 data+-------------------+| Right (' Foobar ', 4) |+-------------------+| Obar |+-------------------+row in Set (0.36sec)MySQL> select Length (' MySQL ');//length to find string lengths+-----------------+|               Length (' mysql ') |+-----------------+| 5 |+-----------------+row in Set (0.00sec)//returns the string str, whose boot space character is deletedMySQL> selectLTrim(' Bar ') asStr//LTrim Remove the left space guide character+------+| STR |+------+| Bar |+------+row in Set (0.00sec)MySQL> selectRTrim(' Bar ') asStr//RTrim Remove the right space guide character+-------+|   Str |+-------+| Bar |+-------+row in Set (0.05sec)MySQL> selectTrim(' Bar ') asStr//Trim Remove 2-side space guide+------+| STR |+------+| Bar |+------+row in Set (0.00sec)//SUBSTRINGSUBSTRING (str,POS); SUBSTRING (str,POS,Len) starts at the POS character position of the string and takes Len data until the end. MySQL> select substring (' Example ', 4,2);+--------------------------+| SUBSTRING (' example ', 4,2) |+--------------------------+| MP |+--------------------------+row in Set (0.00 sec)

MySQL functions and operators