MySQL Operators

Submitted by alpha_luna on Wednesday, July 15, 2015 - 13:51.

Good Day!!!

In this tutorial, we are going to learn about the MySQL Operators and this is the continuation of MySQL Tutorials. My last work was all about the MySQL Reserved Words. In this Chapter, we will know all kinds of Operators in MySQL.

Operator Precedence

Operators which are shown below, in a line together will they have the same precedence. The precedence in operators, has a rules and you will shown from highest to lowest precedence.

INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
&&, AND
XOR
||, OR
= (assignment), :=

In the the table above, it all determines the order of evaluation of terms in an expression.

Example:

mysql> SELECT 10+10*40;
        -> 410
mysql> SELECT (10+20)*30;
        -> 900

Arithmetic Operators

Arithmetic Operators perform basic arithmetic on two values, which are shown below and this operators supported in MySQL:

+       Adds two numerical values
-       Subtracts two numerical values
/       Divides two numerical values
*       Multiplies two numerical values
%       Gives the modulo of two numerical values
|       Performs OR on two integer values
^       Performs exclusive OR on two integer values
&       Performs  AND on two integer values
<<      Performs  left shift on an integer value
>>      Performs right shift on an integer value

Comparison Operators

Comparison operators compare values and return 1 if the comparison is true and 0 otherwise. This operators also supported in MySQL.

=       Equals to
<       if the left value is less than the right value
>       if the left value is greater than the right value
<=      if the left value is less than or equal to the right value
>=      if the left value is greater than or equal to the right value
< >, != if the two values are not equal
Between Checks the mentioned limits
Greatest        Finds the greatest value

Logical Operators

Logical Operators in SQL terms, checks whether its operands are 0, nonzero or NULL. The 0 value means false, nonzero means true and NULL means no value. This kind of operators check the truth value of one or more expressions, which are shown below and this operators supported in MySQL:

NOT or !        not (returns "1" if the value is 0, NULL if it is NULL, otherwise "0")
OR or ||        or (returns "1" if any of the arguments are nonzero and non-NULL, NULL if any are NULL; otherwise, returns "0")
XOR             logical exclusive or (returns "1" if one and only on argument is nonzero and non-NULL, NULL if any are NULL; otherwise. returns "0")
AND or &&       and (returns "0" if any of the arguments are 0, NULL if any are NULL; otherwise, returns "1")

For my next tutorial lets discuss the "MySQL Programs". So what can you say about this tutorial? Share your thoughts in the comment section below and also, don’t forget to like this page. Thank you.

Add new comment

23 views