1. Bitwise operator

Bitwise operators are operators that evaluate on binary numbers. The bit operator first converts the operand into a binary number, then performs a bit operation, and finally changes the calculation result from binary to decimal.

MySQL supports the following bitwise operators:
1.1 bitwise and operators
 The bitwise and (&) operator performs a bitwise logical and operation on the binary number corresponding to the given value. When the value of the binary bit corresponding to the given value is 1, the bit returns 1, otherwise it returns 0.
mysql> SELECT 1 & 10, 20 & 30; +++  1 & 10  20 & 30  +++  0  20  +++ 1 row in set (0.00 sec)
 The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1 & 10 is 0000 and the corresponding decimal number is 0. The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20 & 30 is 10100 and the corresponding decimal number is 20.
1.2 bitwise OR operator
 Bitwise OR () operator performs logical or operation on the binary number corresponding to a given value bit by bit. When one or both of the values of the binary bit corresponding to the given value are 1, the bit returns 1, otherwise it returns 0.
mysql> SELECT 1  10, 20  30; +++  1  10  20  30  +++  11  30  +++ 1 row in set (0.00 sec)
 The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1  10 is 1011 and the corresponding decimal number is 11. The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20  30 is 11110 and the corresponding decimal number is 30.
1.3 bitwise XOR operator
 Bitwise exclusive or (^) operator performs logical exclusive or operation on the binary number corresponding to a given value bit by bit. When the value of the binary bit corresponding to the given value is different, the bit returns 1, otherwise it returns 0.
mysql> SELECT 1 ^ 10, 20 ^ 30; +++  1 ^ 10  20 ^ 30  +++  11  10  +++ 1 row in set (0.00 sec)

The binary number of 1 is 0001 and the binary number of 10 is 1010, so the result of 1 ^ 10 is 1011 and the corresponding decimal number is 11. The binary number of 20 is 10100 and the binary number of 30 is 11110, so the result of 20 ^ 30 is 01010 and the corresponding decimal number is 10.

give an example:
mysql> SELECT 12 & 5, 12  5,12 ^ 5 FROM DUAL; ++++  12 & 5  12  5  12 ^ 5  ++++  4  13  9  ++++ 1 row in set (0.00 sec)
1.4 bitwise negation operator
 Bitwise negation (~) operator negates the binary number of a given value bit by bit, that is, 1 becomes 0 and 0 becomes 1.
mysql> SELECT 10 & ~1; ++  10 & ~1  ++  10  ++ 1 row in set (0.00 sec)
 Since the priority of the bitwise negation (~) operator is higher than that of the bitwise and (&) operators, 10 & ~ 1. First, carry out the bitwise negation operation on the number 1, and the result is 1 except that the lowest bit is 0. Then, carry out the bitwise and operation with 10, and the result is 10.
1.5 bitwise shift right operator
 The shift right by bit (> >) operator shifts all bits of the binary number of a given value to the right by the specified number of bits. After the specified number of bits is shifted to the right, the lower value on the right is removed and discarded, and the empty position on the left is filled with 0.
mysql> SELECT 1 >> 2, 4 >> 2; +++  1 >> 2  4 >> 2  +++  0  1  +++ 1 row in set (0.00 sec)
 The binary number of 1 is 0000 0001, the two bits shifted to the right is 0000 0000, and the corresponding decimal number is 0. The binary number of 4 is 0000 0100, the two bits shifted to the right is 0000 0001, and the corresponding decimal number is 1.
1.6 bitwise shift left operator
 The shift left by bit (< <) operator shifts all bits of the binary number of a given value to the left by a specified number of bits. After moving the specified number of bits to the left, the highorder value on the left is removed and discarded, and the empty position on the loworder on the right is filled with 0.
mysql> SELECT 1 << 2, 4 << 2; +++  1 << 2  4 << 2  +++  4  16  +++ 1 row in set (0.00 sec)
 The binary number of 1 is 0000 0001, the two bits shifted to the left is 0000 0100, and the corresponding decimal number is 4. The binary number of 4 is 0000 0100, the two bits shifted to the left is 00010000, and the corresponding decimal number is 16.
2. Operator priority
 The larger the number, the higher the priority. Operators with higher priority are calculated first. It can be seen that the assignment operator has the lowest priority and the expression enclosed by "()" has the highest priority.
3. Practice
 Select the name and salary of employees whose salary is not between 5000 and 12000
SELECT last_name,salary FROM employees #where salary not between 5000 and 12000; WHERE salary < 5000 OR salary > 12000;
 Select the name and department number of the employee working in department 20 or 50
SELECT last_name,department_id FROM employees # where department_id in (20,50); WHERE department_id = 20 OR department_id = 50;
 Select the name and job of the employee who has no manager in the company_ id
SELECT last_name,job_id FROM employees WHERE manager_id IS NULL; SELECT last_name,job_id,manager_id FROM employees WHERE manager_id <=> NULL;
 Select the name, salary and bonus level of employees with bonus in the company
SELECT last_name,salary,commission_pct FROM employees WHERE commission_pct IS NOT NULL; SELECT last_name,salary,commission_pct FROM employees WHERE NOT commission_pct <=> NULL;
 Select the employee name whose third letter is a
SELECT last_name FROM employees WHERE last_name LIKE '__a%';
 Select the employee name with letters a and k in the name
SELECT last_name FROM employees WHERE last_name LIKE '%a%k%' OR last_name LIKE '%k%a%'; #where last_name like '%a%' and last_name LIKE '%k%';
 Displays the first in the employees table_ Name employee information ending with 'e'
SELECT first_name,last_name FROM employees WHERE first_name LIKE '%e'; SELECT first_name,last_name FROM employees WHERE first_name REGEXP 'e$'; # Writing beginning with E: '^ e'
 The name and type of work with the department number between 80100 in the table employees are displayed
SELECT last_name,job_id FROM employees #Method 1: Recommended WHERE department_id BETWEEN 80 AND 100; #Mode 2: recommended, same as mode 1 #where department_id >= 80 and department_id <= 100; #Mode 3: only applicable to the mode of this question. #where department_id in (80,90,100);
 Displays the manager of the table employees_ id is the employee name, salary and manager id of 100101110
SELECT last_name,salary,manager_id FROM employees WHERE manager_id IN (100,101,110);