Home Mysql Mysql5 Operators & Functions
Questions:Ask|Latest


 
 

Share on Google+Share on Google+

Operators & Functions

Advertisement
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
Advertisements

Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: March 13, 2008

Ask Questions?    Discuss: Operators & Functions   View All Comments

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments
DMCA.com