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 |
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Current Comments
0 comments so far (post your own) View All Comments Latest 10 Comments: