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

Maven project remote deployment && How to configure database connection using tomcat

one. Remote deployment using tomcat 1.1 Problems ...

Detailed steps for installing and using vmware esxi6.5

Table of contents Introduction Architecture Advan...

Example code for implementing stacked carousel effect with HTML+CSS+JS

Effect: When the slideshow moves in one direction...

JS implements a simple todoList (notepad) effect

The notepad program is implemented using the thre...

Detailed graphic description of the database installation process of MySQL version 5.7.24

MySQL is the most popular relational database man...

CSS pixels and solutions to different mobile screen adaptation issues

Pixel Resolution What we usually call monitor res...

Detailed explanation of the concepts, principles and common usage of MySQL stored procedures

This article uses examples to explain the concept...

js realizes the magnifying glass function of shopping website

This article shares the specific code of js to re...

jQuery realizes image highlighting

It is very common to highlight images on a page. ...

Docker container explains in detail how to simplify the image and reduce the size

Table of contents 1. Reduce the number of image l...

Example of using JSX to build component Parser development

Table of contents JSX environment construction Se...

Summary of MySQL logical backup and recovery testing

Table of contents 1. What kind of backup is a dat...

Introduction and use of five controllers in K8S

Table of contents Controller type of k8s Relation...

JavaScript to dynamically load and delete tables

This article shares the specific code of JavaScri...

Detailed explanation of the principle of distributed locks and three implementation methods

Currently, almost all large websites and applicat...