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

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