Examples of common MySQL Operators

Operator priority order
Highest priority: =
1 |, or, xor
2 &, and
3 between, case, when, then, else
4 =, <=>, >=, >,<=, <, <> ,! =, Is, like, regexp, in
5 |
6 &
7 <,>
8-, +
9 *,/, div, %, mod
10 ^
11-(unary minus ),~ (Unary bit inversion)
12 !, Not
Lowest priority binary, collate

Arithmetic Operators
Add
Mysql tutorial> select 1 + 2;
Subtraction
Mysql> select 2-1;
Multiplication
Mysql> select 2*3;
Division
Mysql & gt; select 5/3;
Vendors
Mysql> select 5 div 2;
Module
Mysql> select 5% 2, mod );

 

Logical operators
Non
Mysql> select not 0, not 1, not null;
Mysql> select! 0 ,! 1 ,! Null;
And
Mysql> select (1 and 1), (0 and 1), (3 and 1), (1 and null );
Mysql> select (1 & 1), (0 & 1), (3 & 1), (1 & null );
Or
Mysql> select (1 or 0), (0 or 0), (1 or null), (1 or 1), (null or null );
Mysql> select (1 | 0), (0 | 0), (1 | null), (1 | 1), (null | null );
Exclusive or
Mysql> select (1 xor 1), (0 xor 0), (1 xor 0), (0 xor 1), (null xor 1 );
Mysql> select (1 ^ 1), (0 ^ 0), (1 ^ 0), (0 ^ 1), (null ^ 1 );

Comparison Operators
Equal
Mysql> select 1 = 0, 1 = 1, null = null;
Not equal
Mysql> select 1 <> 0, 1 <> 1, null <> null;
Security equals
Mysql> select 1 <=> 1, 2 <=> 0 <=> 0, null <=> null;
Less
Mysql> select 'A' <'B', 'A' <'A', 'A' <'A', 'A' <'C', 1 <2;
Less than or equal
Mysql> select 'bdf '<=' B ',' B '<=' B ', 0 <1;
Greater
Mysql> select 'A'> 'B', 'abc'> 'A', 1> 0;
Greater than or equal
Mysql> select 'A'> = 'B', 'abc'> = 'A', 1> = 0, 1> = 1;
Between
Mysql> select 10 between 10 and 20, 9 between 10 and 20;
In
Mysql> select 1 in (1, 2, 3), 't'in ('T', 'A', 'B', 'l', 'E '), 0 in (1, 2 );
Is null
Mysql> select 0 is null, null is null;
Is not null
Mysql> select 0 is not null, null is not null;
Like
Mysql> select 123456 like '2008080', 123% like '% 100000', 123456 like' % 100000 ';
Regexp
Mysql> select 'abcdef 'regexp 'AB', 'abcdefg' regexp 'K ';

Bitwise operators
Bitwise AND
Mysql> select 2 & 3;
Mysql> select 2 & 3 & 4;
Bit or
Mysql> select 2 | 3;
Bitwise OR
Mysql> select 2 ^ 3;
Bitwise Inversion
Mysql> select ~ 1 ,~ 18446744073709551614;
Shift right
Mysql> select 100> 3;
Shift left
Mysql> select 100 <3;