mysql operators

these MySQL operators can be classified into the following four categories:

  • arithmetic operators
  • comparison operators
  • logical operators
  • bit operators

MySQL arithmetic operators

operatordescriptionsyntaxresult
+addition operatorselect    12+34, 2.30 + 1.7046 and 4.00
-subtraction operatorselect    3 - 2, 999 - 10001 and -1
*multiplication operatorselect    3*3, 4*09 and 0
/division operatorselect    12/34
%modulus operatorselect    4 % 31

division by zero is, obviously not permitted mysql returns a null if you try.

a string with a leading number is used in an arithmetic operation,it is converted to the value of that number.

a string containing a combination of characters and numbers cannot be correctly converted into a number, it is converted to 0.


MySQL comparison operators

the comparison operators can be used to compare both numbers and strings.the result of such a comparison operation is always 1(true), 0(false) or null(could not be determined).

by default, string comparisons are not case sensitive and use the current character set.

operatordescriptionsyntaxresult
=equal operatorselect    12 = 121(true)
<=>null-safe equal.select    null<=>null, 1<=>null1 ,    0(false)
!= <>is not equalselect    12 <>13, 12<>121(true),0(false)
<is less than select    12 < 131(true)
>is greater thanselect    12 > 120(false)
<=is less than or equal toselect    12 <= 121(true)
>=is greater than or equal toselect    13 >= 131(true)
betweenexists in specified rangeselect    10   between   0 and 111
inexists in specified setselect    8   in  (1,2,3,4,5,6,7,8,9)1
is nullis nullselect    0 is null, null  is null0,1
is not nullis not nullselect    2 is not null1
likewildcard matchselect    'sandeep'   like   '%ee%'1

MySQL logical operators

the result of an operation involving these logical operators is always 1(true), 0(false) or null(could not be determined).

operatordescriptionsyntaxresult
&&andselect   1 &&  11(true)
||orselect   1 ||  01(true)
!notselect   not   10(false)
xorexclusive orselect   1 xor  10(false)

MySQL bit operators

the all bitwise operators have a maximum range of 64 bits.

operatordescriptionsyntaxresult
&bitwise andselect     29   &  1513
|bitwise orselect     29   &  1531
^bitwise xor (exclusive or)select     11   ^  38
~bit inversionselect   5 &  ~14
<<bitwise left siftselect   1 <<  24
>>bitwise right siftselect   4 >>  21