Operators & Functions

In this section we are describing the function and operators, which are allowed for writing expressions in MySQL. If any expression, which contains NULL then it always produce a NULL value unless else indicated in the documentation for a particular function or operator.

MySQL Operator Table 

MySQL Logical Operators Symbol

MySQL Date and Time function 
In this section we will describes the functions, which can be used to manipulate temporal values. Temporal values are DATETIME , DATE, TIMESTAMP, TIME and YEAR. And each temporal type has a range of legal values and where you specify any illegal value, which MySQL cannot represent then it used “zero”.

The following specifiers may be used in the format string. The ‘%’ character is required before format specifier characters

MySQL formats the date according to format string:

MySQL Control Flow Functions 
MySQL use the two types of Control Flow Function are:

CASE value WHEN [compare_value] THEN result [WHEN [compare_value] Then result...][ELSE result] END 
and
CASE WHEN [condition] THEN result [WHEN [condition] THEN result... ][ELSE result] END  

The first case returns the result if value = compare_value. And the second case returns the result if first condition is true. If there was no matching then the Else part is returned as a result but Else part is not available then its return NULL.

• IF(expr1, expr2 expr3)
  If expr1 is TRUE then it returns expr2, expr1 true means expr1 <>0 and expr1 <> NULL. But if expr1 is not TRUE then it returns expr3. IF () function can return a string or numeric value but it’s depend on the context in which it is used.

  mysql> select if(2>3, 2, 3); -> 3 mysql> select if(2<3,'yes', 'no'); -> 'yes' mysql> select if(STRCMP('true', 'true1'),'no', 'yes'); -> 'no'

• IFNULL (expr1, expr2)
  In this function if expr1 is NULL it returns expr1 but when expr1 is not NULL then it returns expr1. This function is also returns a numeric or string value, depending upon the context in which it is used.
      

  mysql> select IFNULL(2,1); -> 2 mysql> select IFNULL(NULL,1); -> 1 mysql> select IFNULL(1/0,10); -> 10 mysql> select IFNULL(1/0,'yes'); -> 'yes'

     
• NULLIF (expre1, expre2)
  In this function if expr1=expr2 is true then it returns NULL else it returns expr1.
  

  mysql> SELECT NULLIF (2,2); -> NULL mysql> SELECT NULLIF(2,1); ->2