Home Mysql Mysql5 Operators & Functions

Operators & Functions
Posted on: March 13, 2008 at 12:00 AM

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

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

Related Tags for Operators & Functions:
sql, mysql, c, documentation, function, null, fun, io, operators, expression, operator, value, this, opera, ai, if, for, unc, writing, expressions, bing, to, express, contains, ssi, bi, e, it, doc, des, section, ls, ul, pe, art, ce, in, part, m, nt, par, ca, less, es, func, all, icu, me, pro, cat, do, tor, bin, which, s, ess, at, any, my, is, ll, press, pre, du, and, ar, rt, xp, va, scr, s, s, ri, th, ati, alu, indic, ica, ica, prod, pr, nd, on, o, nl

«Previous Index Next»