MYSQL Operator Summary

MYSQL Operator Summary

1. Arithmetic operators

MySQL supports the following arithmetic operators:

Operators
effect
+
addition
-
Subtraction
*
multiplication
/,DIV
Division, returns the quotient
%,MOD
Division, return remainder

Example 1: +, -, *, /, %,

mysql> select 0.1+0.333,0.1-0.333,0.1*0.333,1/2,1%2;
+-----------+-----------+-----------+--------+------+
| 0.1+0.333 | 0.1-0.333 | 0.1*0.333 | 1/2 | 1%2 |
+-----------+-----------+-----------+--------+------+
| 0.433 | -0.233 | 0.0333 | 0.5000 | 1 |
+-----------+-----------+-----------+--------+------+
1 row in set (0.05 sec)

Example 2: In a division operation, if the divisor is 0 , NULL will be returned

mysql> select 1/0;
+------+
| 1/0 |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

Example 3: Another form of modular operation MOD (a,b) is the same as a%b

mysql> select 1%2,mod(1,2);
+------+----------+
| 1%2 | mod(1,2) |
+------+----------+
| 1 | 1 |
+------+----------+
1 row in set (0.00 sec)

2. Comparison Operators

Comparison operators supported by MySQL:

Operators
effect
=
 equal
<> or !=
Not equal to
<=>
NULL-safe
<
Less than
<=
Less than or equal to
>
Greater than
>=
Greater than or equal to
BETWEEN
Existence and Specified Range
IN
Exists in the specified collection
IS NULL
is NULL
IS NOT NULL
Not NULL
LIKE
Wildcard Matching
REGEXP or RLIKE
Regular expression matching

Example 1: " <> " is the opposite of " = ". If the two operations are not equal, the return result is 1, otherwise it returns 0. " NULL " cannot be used for " <> " comparison.

mysql> select 1<>0,1<>1,null<>null;
+------+------+------------+
| 1<>0 | 1<>1 | null<>null |
+------+------+------------+
| 1 | 0 | NULL |
+------+------+------------+
1 row in set (0.00 sec)

Example 2: The " <=> " safe equality operator returns 1 in the same way as the " = " operation, except that " <=> " can also perform comparisons when the value is null .

mysql> select 1<=>1,1<=>0,null<=>null;
+-------+-------+-------------+
| 1<=>1 | 1<=>0 | null<=>null |
+-------+-------+-------------+
| 1 | 0 | 1 |
+-------+-------+-------------+
1 row in set (0.02 sec)

Example 3: “ < ” returns 1 when the left side is less than the right side

mysql> select 'a'<'b','A'<'b','bdf'<'c',1<2;
+---------+---------+-----------+-----+
| 'a'<'b' | 'A'<'b' | 'bdf'<'c' | 1<2 |
+---------+---------+-----------+-----+
| 1 | 1 | 1 | 1 |
+---------+---------+-----------+-----+
1 row in set (0.02 sec)

Example 4: between , the format is " a BETWEEN min AND max ", which means that when a is greater than or equal to min and less than or equal to max , it returns 1, otherwise it returns 0

mysql> select 10 between 10 and 20,9 between 10 and 20;
+----------------------+---------------------+
| 10 between 10 and 20 | 9 between 10 and 20 |
+----------------------+---------------------+
| 1 | 0 |
+----------------------+---------------------+
1 row in set (0.01 sec)

Example 5: regexp , the format is " str REGEXP str_pat ", which means that if the string str contains a string that matches str_pat , it returns 1, otherwise it returns 0

mysql> select 'abcdeef' regexp 'ab','abcdef' regexp 'g','abcedf' regexp 'df';
+----------------------+---------------------+----------------------+
| 'abcdeef' regexp 'ab' | 'abcdef' regexp 'g' | 'abcedf' regexp 'df' |
+----------------------+---------------------+----------------------+
| 1 | 0 | 1 |
+----------------------+---------------------+----------------------+
1 row in set (0.01 sec)

The rest are simple to use, so I will just give you the syntax and not write any examples.

  • in , using the format " a IN (value1,value2,…) "
  • IS NULL , using the format " a IS NULL "
  • IS NOT NULL , using the format " a IS NOT NULL "
  • LIKE , using the format " a LIKE %123% "

3. Logical operators

Logical operators in MySQL:

Operators
effect
NOT or !
Logical NOT
AND or &&
Logical AND
OR or||
Logical OR
XOR
Logical XOR

Example 1: ““ NOT ” or “ ！ " indicates logical negation. It returns the opposite result of the operand. When the operand is 0, it returns 1; when the operand is 1, it returns 0, but NOT NLLL returns null

mysql> select not 0,!0,not 1,not null;
+-------+----+-------+----------+
| not 0 | !0 | not 1 | not null |
+-------+----+-------+----------+
| 1 | 1 | 0 | NULL |
+-------+----+-------+----------+
1 row in set (0.00 sec)

Example 2: " AND " and " && " represent logical AND operations. When both operands are non-zero values, the result is 1, otherwise it returns 0. When any of the operands is NULL, if one of the values ​​is 0, it returns 0; if the other values ​​are > 0 and there is NULL , it returns NULL

mysql> select (1 and 1),(0 and 1),(3 and 1),(0 and null),(1 and null);
+-----------+-----------+-----------+--------------+--------------+
| (1 and 1) | (0 and 1) | (3 and 1) | (0 and null) | (1 and null) |
+-----------+-----------+-----------+--------------+--------------+
| 1 | 0 | 1 | 0 | NULL |
+-----------+-----------+-----------+--------------+--------------+
1 row in set (0.00 sec)
mysql> select 1 and NULL and 0;
+------------------+
| 1 and NULL and 0 |
+------------------+
| 0 |
+------------------+
1 row in set (0.00 sec)

mysql> select 1 and NULL and 3;
+------------------+
| 1 and NULL and 3 |
+------------------+
| NULL |
+------------------+
1 row in set (0.00 sec)

Example 3: “ OR ” or “ || ” represents a logical OR operation. When both operands are not NULL, if either operand is non-zero, the result is 1, otherwise it is 0; when one operand is NULL, if the other operand is non-zero, the result is 1, otherwise the result is NULL

mysql> select (1 or 0),(0 or 0),(1 or NULL),(0 or NULL),(NULL or NULL);
+----------+----------+-------------+-------------+----------------+
| (1 or 0) | (0 or 0) | (1 or NULL) | (0 or NULL) | (NULL or NULL) |
+----------+----------+-------------+-------------+----------------+
| 1 | 0 | 1 | NULL | NULL |
+----------+----------+-------------+-------------+----------------+
1 row in set (0.00 sec)

Example 4: XOR means logical exclusive OR. When either operand is NULL, the return value is NULL. For non-NULL operands, if the two logical truth values ​​are different, the result is 1; otherwise, it returns 0.

mysql> select (0 xor 0),(1 xor 0),(1 xor 1),(1 xor null),(0 xor null),(null xor null);
+-----------+-----------+-----------+--------------+--------------+----------------+
| (0 xor 0) | (1 xor 0) | (1 xor 1) | (1 xor null) | (0 xor null) | (null xor null) |
+-----------+-----------+-----------+--------------+--------------+----------------+
| 0 | 1 | 0 | NULL | NULL | NULL |
+-----------+-----------+-----------+--------------+--------------+----------------+
1 row in set (0.00 sec)

4. Bitwise operators

MySQL supports the following bitwise operators:

Operators
 effect
&
Bitwise AND
|
Bitwise OR
^
Bitwise exclusive OR (Bitwise XOR)
~
Bit Inversion
>>
Bit shift right
<<
Bit Shift Left

Example 1: “Bitwise AND” performs a logical AND operation on the binary bits of multiple operands. 2&3, the binary number of 2 is 10, and the binary number of 3 is 11. If we do the AND operation, the result is still 10, and the result converted to decimal is 2

mysql> select 2&3;
+-----+
| 2&3 |
+-----+
| 2 |
+-----+
1 row in set (0.01 sec)

Example 2: “Bitwise OR” performs a logical OR operation on the binary bits of multiple operands. 2&3, the binary number of 2 is 10, and the binary number of 3 is 11. When we do the AND operation, the result becomes 11, and the result converted to decimal is 3

mysql> select 2|3;
+-----+
| 2|3 |
+-----+
| 3 |
+-----+
1 row in set (0.00 sec)

Example 3: “位異或” performs XOR operation on the binary bits of multiple operands. 2^3, the binary number of 2 is 10, the binary number of 3 is 11, 10^11 is 01, and the result of conversion to decimal is 1

mysql> select 2^3;
+-----+
| 2^3 |
+-----+
| 1 |
+-----+
1 row in set (0.01 sec)

Example 4: "Bitwise inversion" performs a NOT operation on the binary bit of the operand. Here, the operand can only be one bit. Explanation: In MySQL, the constant number is represented by 8 bytes by default, and 8 bytes is 64 bits. The binary of the constant 1 is 63 0s and 1 1. After bitwise inversion, it is 63 1s and 1 0. After conversion to binary, it is 18446744073709551614.

mysql> select ~1,~18446744073709551614
    -> ;
+----------------------+-----------------------+
| ~1 | ~18446744073709551614 |
+----------------------+-----------------------+
| 18446744073709551614 | 1 |
+----------------------+-----------------------+
1 row in set (0.01 sec)
mysql> select bin(18446744073709551614);
+----------------------------------------------------------------------------------+
| bin(18446744073709551614) |
+----------------------------------------------------------------------------------+
| 1111111111111111111111111111111111111111111111111111111111110 |
+----------------------------------------------------------------------------------+
1 row in set (0.03 sec)

Example 5: “Bit right shift” moves the left operand to the right by the number of bits specified by the operand. For example, 100>>3, the binary number of 100 0001100100 is shifted right by 3 bits, 0000001100, which is converted to 12 in binary:

mysql> select 100>>3;
+--------+
| 100>>3 |
+--------+
| 12 |
+--------+
1 row in set (0.00 sec)

Example 6: “Bitwise Left Shift” shifts the left operand to the left by the number of bits specified by the operand. For example, 100<<3, the binary number of 100 0001100100000 is shifted right by 3 bits, 1100100000000, which is converted to binary number 800:

mysql> select 100<<3;
+--------+
| 100<<3 |
+--------+
| 800 |
+--------+
1 row in set (0.00 sec)

5. Operator precedence

Operator precedence in MySQL:

Priority Order
Operators
1
:=
2
||, OR, XOR
3
&&, AND
4
NOT
5
BETWEEN, CASE, WHEN, THEN, ELSE
6
=, <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
7
|
8
&
9
<<, >>
10
-, +
11
*, /, DIV, %, MOD
12
^
13
- (unary minus), ~ (unary bit inversion)
14
!

This is the end of this article about the summary of MYSQL operators. For more relevant MYSQL operator content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!

You may also be interested in:
  • Introduction to MySQL <> and <=> operators
  • Detailed explanation of the use of MySQL comparison operator regular expression matching REGEXP
  • Summary of the use of special operators in MySql
  • Summary of commonly used operators and functions in MySQL
  • MySQL Notes — SQL Operators

<<:  30 free high-quality English ribbon fonts

>>:  JavaScript realizes the drag effect of modal box

Recommend

Install MySQL (including utf8) using Docker on Windows/Mac

Table of contents 1. Docker installation on Mac 2...

Eight common SQL usage examples in MySQL

Preface MySQL continued to maintain its strong gr...

Automatic file synchronization between two Linux servers

When server B (172.17.166.11) is powered on or re...

Summary of Docker Data Storage

Before reading this article, I hope you have a ba...

HTML Grammar Encyclopedia_HTML Language Grammar Encyclopedia (Must Read)

Volume Label, Property Name, Description 002 <...

Extract specific file paths in folders based on Linux commands

Recently, there is a need to automatically search...

Add unlimited fonts to your website with Google Web Fonts

For a long time, website development was hampered...

Summary of the differences between Mysql primary key and unique key

What is a primary key? A primary key is a column ...

A brief discussion on the underlying principle of mysql join

Table of contents join algorithm The difference b...

CSS modular solution

There are probably as many modular solutions for ...

Basic usage of @Font-face and how to make it compatible with all browsers

@Font-face basic introduction: @font-face is a CSS...

A simple method to implement scheduled backup of MySQL database in Linux

Here are the detailed steps: 1. Check the disk sp...

Detailed example of sharedWorker in JavaScript to achieve multi-page communication

This is what happened. Today I was playing with G...

nginx+tomcat example of accessing the project through the domain name

I was curious about how to access the project usi...

Vue implements dynamic query rule generation component

1. Dynamic query rules The dynamic query rules ar...