Variables & Operators in MySQL

INTRODUCTION

 

In this tutorial, I am going to explain about MySQL variables and operators with examples. Without wasting time, let’s start.

 

VARIABLES 

 

A variable is a characteristic notation with a character value. This value may be changed over time.  Variables in MySQL are represented by the @ symbol.

 

Example 1:

Set @name = 'Hello! Vatsa';
SELECT @name;

 

Example 2:

SET @a = "20/10/2000";
SET @b = "20/10/2020";
SET @sql = CONCAT('SELECT * FROM <table_name> WHERE date IN (', @a, ',', @b, ')' );
SELECT @sql;

 

OPERATORS

 

Operators may be used to build expressions. MySQL operators are very similar to mathematical operators.  

 

There are two kinds of operators.

1. Binary
2. Unary

Note:

Binary operators work with two operands, but unary work with one.

• An operator may have one or two operands.
• An operand is one of the inputs (arguments) of an operator.

There are different types of operators in MySQL, let’s discuss them one by one.

• Arithmetic operators
• Boolean operators
• Relational operators
• Bitwise operators
• Other operators
• Unary operators

UNARY OPERATORS

 

In MySQL, a unary operator is an operator that works on single operand.

 

Syntax: operator operand.

 

Examples:

SELECT +5, 9;
SELECT -(4-54);

Note:

 

The ‘-‘ unary operator changes the positive values to negative and vice versa.

SELECT NOT (4>8);

 

Note:

The NOT operator negates a value. The result of the 4>8 comparison is false and the negation operator negates it to true.

 

ARITHMETIC OPERATORS

 

These are some arithmetic operators in mysql, let’s discuss them one by one.

1. Multiplication
2. Division
3. Integer
4. Division
5. Addition
6. Subtraction modulo

Examples:

1)   Addition and subtraction operators.

SELECT 4 + 7 - 1;

2) These are multiplication and division operators that we know from mathematics.

SELECT 4*3/6;

3) The below SQL statement shows the difference between the division and integer division operators. The first returns a floating point number, the second returns an integer.

SELECT 7/3, 7 DIV 3;

4) The % operator is called the modulo operator. It finds the remainder of division of one number by another. 11 % 3, 11 modulo 3 is 2, because 3 goes into 11 three times with a remainder of 2.

SELECT 15 % 43;

 

LOGICAL OPERATORS

 

MySQL understands these logical operators:

1. AND
2. OR 
3. NOT
4. XOR

Logical operators return TRUE or FALSE. In MySQL, 1 is true, 0 is false.

 

Example 1: AND Operator

 

The AND operator evaluates to true, if both operands are true.

 

A) 

SELECT FALSE AND FALSE,
FALSE AND TRUE,
TRUE AND FALSE,
TRUE AND TRUE;

 

The first three operations evaluate to false, the last one to true.

 

B)

SELECT 5=5 AND 6=6;

 

Both operands are true, so the result is true (1).

 

Example 2: OR Operator

The OR operator evaluates to true, if at least one of the operands is true.

 

A) 

SELECT FALSE OR FALSE,
FALSE OR TRUE,
TRUE OR FALSE,
TRUE OR TRUE;

 

The first operation evaluates to false, and other operations evaluate to true.

 

B) 

SELECT FALSE XOR FALSE,
FALSE XOR TRUE,
TRUE XOR FALSE,
TRUE XOR TRUE;

 

The XOR operator evaluates to true, if exactly one of the operands is true.

 

Two of the operations result in true.

 

The NOT operator is negation operator. It makes true false and false true.

SELECT NOT TRUE, NOT FALSE;
SELECT NOT (3=3);

RELATIONAL OPERATORS

 

Relational operators are used to compare values. Usage of the relational operators is known from mathematics. These operators always result in Boolean value.

 

For Example:

 

A) 

SELECT 3*3=9, 9=9;

Note:

The = is the equality operator.

 

B) 

SELECT 3 < 4, 3 <> 5, 4 <= 4, 5 != 5;

 

BITWISE OPERATORS

 

In the Bitwise operators, Decimal numbers are natural to humans. Binary numbers are native to computers. Binary, octal, decimal or hexadecimal symbols are only notations of the same number. These operators work with bits of a binary number. We have binary logical operators and shift operators.

 

A)  The bitwise AND operator performs bit-by-bit comparison between two numbers. The result for a bit position is 1 only if both corresponding bits in the operands are 1.

 

For example:

SELECT 6 & 3, 3 & 6;

   00110

& 00011

=  00010

 

The first number is a binary notation of 6. The second is 3. The result is 2 (00010).

 

B)  The bitwise OR operator performs bit-by-bit comparison between two numbers. The result for a bit position is 1 if either of the corresponding bits in the operands is 1.

 

For example:

SELECT 7 | 3, 3 | 7;

     00111

  |  00011

  =  00111

The result is 00111 or decimal 7.

 

C) The bitwise shift operators shift bits to the right or left.

• number << n : multiply number 2 to the nth power 
• number >> n : divide number by 2 to the nth power

For example 1:

SELECT 6 >> 1;

These operators are also called arithmetic shift.

 

      00110  

>>  00001

    =00011

We shift each of the bits of the number six to the right. It is equal to dividing the six by 2. The result is 00011 or decimal 3.

 

For example 2:

SELECT 6 << 1;

         00110

    << 00001

     =  01100

We shift each of the bits of the number six to the left. It is equal to multiplying the number six by 2. The result is 01100 or decimal 12.

 

 

OTHER OPERATORS

 

There are some other operators left. These include IS, IN, LIKE, REGEXP, BETWEEN.

 

The IS operator tests if an operand is a Boolean value.

 

For example:

SET @vatsa = TRUE;
SELECT @vatsa IS TRUE;

 

We set a variable to Boolean false. We check if the variable is FALSE using the IS operator.

 

We can use the IN operator in two cases.

SELECT 'Ram' IN ('Ram', 'Hari', 'Mohan',’vatsa’);

 

Note:

Here we check, if the string value 'Ra,' is in the list of names, following the IN operator. The return is a boolean value.

 

PRECEDENCE

 

The operator precedence tells us which operators are evaluated first. The precedence level is necessary to avoid ambiguity in expressions.

 

For example:

Select 4 + 5 * 5;

Note:

Like in mathematics, the multiplication operator has a higher precedence than addition operator. So the outcome is 29.

SELECT (3 + 8) * 5, ((1+2)*3)*5-(8+(1*5));

To change the order of evaluation, we can use square brackets. Expressions inside square brackets are always evaluated first.

 

 

The first expression evaluates to 28, because the multiplication operator has a higher precedence, than the addition one. In the second example, we have used square brackets to change the order of precedence. So the second expressions evaluates to 55, and third expression evaluates to 32 according to BODMAS rule.

 

ASSOCIATIVITY

 

Sometimes the precedence is not satisfactory to determine the outcome of an expression. There is another rule called associativity. The associativity of operators determines the order of evaluation of operators with the same precedence level.

SELECT 9 / 3 * 3;

What is the outcome of this expression? 9 or 1? The multiplication, deletion and the modulo operator are left to right associated. So the expression is evaluated this way: (9 / 3) * 3 and the result is 9.

SELECT 9 / 3 * 3;

Note:

The associativity rule is left to right.

SELECT 0 AND 0 OR 1;

 

The associativity rule is again left to right. If it was right to left, the result would be 0. Arithmetic, boolean, relational and bitwise operators are all left to right associated.

 

CONCLUSION

 

In this article, I have discussed the concept MySQL variables and operators with various examples.

 

I hope you enjoyed this article. Follow C# Corner to learn more new and amazing things about MySQL. 

 

Thanks for reading this article!