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 high-order value on the left is removed and discarded, and the empty position on the low-order 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

1. 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;

1. 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;

1. 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;

1. 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;

1. Select the employee name whose third letter is a

SELECT last_name
FROM employees
WHERE last_name LIKE '__a%';

1. 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%';

1. 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'

1. The name and type of work with the department number between 80-100 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);

1. 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);