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

An article to understand the advanced features of K8S

Table of contents K8S Advanced Features Advanced ...

How to create a swap partition file in Linux

Introduction to Swap Swap (i.e. swap partition) i...

Quickly solve the problem that CentOS cannot access the Internet in VMware

Yesterday I installed CentOS7 under VMware. I wan...

Manually implement the two-way data binding principle of Vue2.0

In one sentence: Data hijacking (Object.definePro...

Vue's vue.$set() method source code case detailed explanation

In the process of using Vue to develop projects, ...

Quickly install MySQL5.7 compressed package on Windows

This article shares with you how to install the M...

Two simple menu navigation bar examples

Menu bar example 1: Copy code The code is as foll...

Steps to deploy ingress-nginx on k8s

Table of contents Preface 1. Deployment and Confi...

Case analysis of several MySQL update operations

Table of contents Case Study Update account balan...

Linux jdk installation and environment variable configuration tutorial (jdk-8u144-linux-x64.tar.gz)

It is recommended to use the sudo su command to s...

Detailed tutorial on integrating Apache Tomcat with IDEA editor

1. Download the tomcat compressed package from th...

SQL implementation of LeetCode (175. Joining two tables)

[LeetCode] 175.Combine Two Tables Table: Person +...

Exploring the use of percentage values ​​in the background-position property

How background-position affects the display of ba...

Steps for importing tens of millions of data into MySQL using .Net Core

Table of contents Preliminary preparation Impleme...

Various front-end printing methods of web: CSS controls web page printing style

CSS controls the printing style of web pages : Use...