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

A brief discussion on HTML table tags

Mainly discuss its structure and some important pr...

How to start/stop Tomcat server in Java

1. Project Structure 2.CallTomcat.java package co...

Analysis of the ideas of implementing vertical tables in two ways in Vue project

Problem Description In our projects, horizontal t...

HTML table layout example explanation

The elements in an HTML document are arranged one...

Ubuntu 16.04 mysql5.7.17 open remote port 3306

Enable remote access to MySQL By default, MySQL u...

MySQL online deadlock analysis practice

Preface I believe that everyone has had a simple ...

Analysis of problems caused by MySQL case sensitivity

MYSQL is case sensitive Seeing the words is belie...

js native carousel plug-in production

This article shares the specific code for the js ...

Use Javascript to develop sliding-nav navigation plug-in with sliding bar effect

Table of contents 1. Introduction 2. Usage 3. Dev...

MySQL query statement grouped by time

MySQL query by year, month, week, day group 1. Qu...

How to use JavaScript strategy pattern to validate forms

Table of contents Overview Form validation withou...

How to solve the problem of too many open files in Linux

The cause is that the process opens a number of f...

Summary of Several Methods for Implementing Vertical Centering with CSS

In the front-end layout process, it is relatively...

Super detailed steps to install zabbix3.0 on centos7

Preface Recently, part of the company's busin...

XHTML: Frame structure tag

Frame structure tag <frameset></frameset...