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