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 functi

Operators & Functions

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 

Name

Symbol 

EQUAL  = 
 OR   ||
AND  &&
GRATER THEN EQUAL >=
GRATER THEN  >
LESS THEN  <
LESS THEN OR GRATER THEN  <>
NOT EQUAL OR EQUAL !=
SUBTRACTION   -
ADDITION  +
MULTIPLICATION  *
DIVISION /
MODULUS  %
UNARY MINUS  -
UNARY BIT INVERSION  ~

MySQL Logical Operators Symbol

Logical Operator 

Symbol

Logical NOT NOT, !
Logical AND AND, &&
Logical OR OR, ||
Logical XOR XOR

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:

SYMBOL 

DESCRIPTION

%a Weekday Name (Sun..Sat)
%b Month Name (Jan..Dec. )
%c  Month numeric(0...12)
%D Day or Month (0th,1st, 2nd, 3rd ......)
%d  Day or Month numeric(00....31)
%e Day or Month numeric(0...31) 
%f Microsecond (00000.................9999999)
%H Hour (00..23)
%h Hour(01..12)
%I  Hour(01..12)
%i  Minute and numeric 
%j Day of  year (001...366)
%k Hour(01...23)
%l Hour(0...12)
%M Month (Jan...Dec)
%m Month, Numeric(00..12)
%p AM OR PM 
%Y year, numeric, four digits 
%y year, numeric (two digits)
%% literal' % 'Character 

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.

mysql> select case 2 when 2 then 'two'
-> when 4 then 'four' else 'more' END;
-> 'two'
mysql> select case when1>0 then 'true' else 'false' END;
-> 'true'
mysql> select case binary 'B'
-> when 'a' then 1 when 'b' then 2 END;
-> 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