1, Logical operator
Logical operators are mainly used to judge whether an expression is true or false. In mysql, the return result of logical operators is 1, 0 or NULL. MySQL supports four logical operators as follows:
1.1 logical non operator
- Logical non (NOT or!) Operator means to return 1 when the given value is 0
- Returns 0 when the given value is a non-zero value
- Returns NULL when the given value is NULL
Example 1:
mysql> SELECT NOT 1, NOT 0, NOT(1+1), NOT !1, NOT NULL; +-------+-------+----------+--------+----------+ | NOT 1 | NOT 0 | NOT(1+1) | NOT !1 | NOT NULL | +-------+-------+----------+--------+----------+ | 0 | 1 | 0 | 1 | NULL | +-------+-------+----------+--------+----------+ 1 row in set (0.00 sec)
Example 2:
SELECT last_name, job_id FROM employees WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
1.2 logic and operators
- The logical AND (AND or & &) operator returns when all the given values are non-zero AND none of them is NULL
1 - When one or more given values are 0, it returns 0; otherwise, it returns NULL
Example 1:
mysql> SELECT 1 AND -1, 0 AND 1, 0 AND NULL, 1 AND NULL; +----------+---------+------------+------------+ | 1 AND -1 | 0 AND 1 | 0 AND NULL | 1 AND NULL | +----------+---------+------------+------------+ | 1 | 0 | 0 | NULL | +----------+---------+------------+------------+ 1 row in set (0.00 sec)
Example 2:
mysql> SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%'; +-------------+-----------+--------+----------+ | employee_id | last_name | job_id | salary | +-------------+-----------+--------+----------+ | 114 | Raphaely | PU_MAN | 11000.00 | | 145 | Russell | SA_MAN | 14000.00 | | 146 | Partners | SA_MAN | 13500.00 | | 147 | Errazuriz | SA_MAN | 12000.00 | | 148 | Cambrault | SA_MAN | 11000.00 | | 149 | Zlotkey | SA_MAN | 10500.00 | | 201 | Hartstein | MK_MAN | 13000.00 | +-------------+-----------+--------+----------+ 7 rows in set (0.00 sec)
1.3 logical or operator
- The logical OR (OR or |) operator returns 1 when the given value is not NULL and any value is a non-0 value, otherwise it returns 0
- When one value is NULL and the other value is non-zero, it returns 1; otherwise, it returns NULL
- Returns NULL when both values are NULL
Example 1:
mysql> SELECT 1 OR -1, 1 OR 0, 1 OR NULL, 0 || NULL, NULL || NULL; +---------+--------+-----------+-----------+--------------+ | 1 OR -1 | 1 OR 0 | 1 OR NULL | 0 || NULL | NULL || NULL | +---------+--------+-----------+-----------+--------------+ | 1 | 1 | 1 | NULL | NULL | +---------+--------+-----------+-----------+--------------+ 1 row in set (0.00 sec)
Example 2:
#Query employee number and basic salary whose basic salary is not between 9000-12000 SELECT employee_id,salary FROM employees WHERE NOT (salary >= 9000 AND salary <= 12000); SELECT employee_id,salary FROM employees WHERE salary <9000 OR salary > 12000; SELECT employee_id,salary FROM employees WHERE salary NOT BETWEEN 9000 AND 12000; SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >= 10000 OR job_id LIKE '%MAN%';
Note: OR can be used together with AND, but pay attention to their priority when using. Since the priority of AND is higher than OR, first operate the operands on both sides of AND, AND then combine them with the operands in OR.
1.4 logical XOR operator
- The XOR operator returns NULL when any one of the given values is NULL
- Returns 0 if both non NULL values are 0 or are not equal to 0
- If one value is 0 and the other value is not 0, 1 is returned
Example 1:
mysql> SELECT 1 XOR -1, 1 XOR 0, 0 XOR 0, 1 XOR NULL, 1 XOR 1 XOR 1, 0 XOR 0 XOR 0; +----------+---------+---------+------------+---------------+---------------+ | 1 XOR -1 | 1 XOR 0 | 0 XOR 0 | 1 XOR NULL | 1 XOR 1 XOR 1 | 0 XOR 0 XOR 0 | +----------+---------+---------+------------+---------------+---------------+ | 0 | 1 | 0 | NULL | 1 | 0 | +----------+---------+---------+------------+---------------+---------------+ 1 row in set (0.00 sec)
Example 2:
#Query people in department 10 or 20 whose salary is not more than 8000 or those who are not in department 10 or 20 whose salary is more than 8000 select last_name,department_id,salary from employees where department_id in (10,20) XOR salary > 8000;
2, 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:
Bitwise and 1.2 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
Example:
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
2.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
Example:
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
2.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
Example:
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
2.4 comprehensive 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)
2.5 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
Example:
mysql> SELECT 10 & ~1; +---------+ | 10 & ~1 | +---------+ | 10 | +---------+ 1 row in set (0.00 sec)
- Since the bitwise negation (~) operator has higher priority than the bitwise and (&) operator, 10 & ~ 1
- First, reverse the digit 1 by bit, and the result is 1 except that the lowest bit is 0. Then, perform the bitwise and operation with 10, and the result is 10
2.6 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 low-order value on the right is removed and discarded, and the empty position of the high-order value on the left is filled with 0
Example:
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
2.5 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 position on the right is filled with 0
Example:
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
3, Priority of operator
- The larger the number, the higher the priority. Operators with higher priority are calculated first
- The assignment operator has the lowest priority, and the expression enclosed by () has the highest priority
4, Using regular expression queries
- Regular expressions are usually used to retrieve or replace the text content that conforms to a certain pattern, and match the special strings that meet the requirements in the text according to the specified matching pattern. For example, regular expressions can be used to extract phone numbers from a text file, find repeated words in an article, or replace some sensitive words entered by users. Regular expressions are powerful and flexible, and can be applied to very complex queries.
Use the REGEXP keyword in MySQL to specify the character matching pattern of regular expressions. The following table lists the common character matches in the REGEXP operator:
4.1 query records starting with a specific character or string
The character ^ matches text that begins with a specific character or string.
Example: in the fruits table, query F_ For the record whose name field starts with the letter b, the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^b';
4.2 query records ending with specific characters or strings
The word $matches text that ends with a specific character or string.
Example: in the fruits table, query F_ For the record whose name field ends with the letter y, the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'y$';
4.3 use the symbol "." To replace any character in the string
Character Match any character.
Example: in the fruits table, query F_ The name field value contains the letters a and g, and there is only one letter between the two letters. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'a.g';
4.4 use "*" and "+" to match multiple characters
- The asterisk * matches the preceding character any number of times, including 0 times
- The plus sign + matches the preceding character at least once
Example 1: in the fruits table, query F_ The name field value starts with the letter b and the letter a appears after b. the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba*';
Example 2: in the fruits table, query F_ The name field value starts with the letter b and the letter a appears at least once after b. the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '^ba+';
4.5 match specified string
The regular expression can match the specified string as long as the string is in the query text. If you want to match multiple
Use string separator to separate multiple strings.
Example 1: in the fruits table, query F_ The name field value contains the record of the string on. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
Example 2: in the fruits table, query F_ The name field value contains the record of the string on or ap. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on|ap';
As mentioned earlier, the LIKE operator can also match the specified string, but unlike REGEXP, if the string matched by LIKE appears in the middle of the text, it cannot be found and the corresponding line will not be returned.
REGEXP matches in the text. If the matched string appears in the text, REGEXP will find it and the corresponding line will be returned. The comparison results are as follows:
mysql> SELECT * FROM fruits WHERE f_name like 'on'; Empty set(0.00 sec)
4.6 match any one of the specified characters
Square brackets [] specify a set of characters that match only any one of them, that is, the text you are looking for.
Example 1: in the fruits table, find F_ The name field contains records with letters o or t, and the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
Example 2: in the fruits table, query s_ The ID field contains records of 4, 5 or 6. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE s_id REGEXP '[456]';
4.7 match characters other than specified characters
[^ character set] matches any character that is not in the specified set.
Example: in the fruits table, query F_ The ID field contains records with characters other than letters a~e and numbers 1 ~ 2. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
4.8 use {n,} or {n,m} to specify the number of consecutive occurrences of a string
- The string {n,} represents matching the preceding character at least N times
- The string {n,m} indicates that the previous string is matched no less than n times and no more than m times. For example, a{2,} indicates that the letter a appears at least twice in a row, or more than twice; a{2,4} indicates that the letter a appears at least 2 times in a row and no more than 4 times at most
Example 1: in the fruits table, query F_ The name field value appears with the letter x at least twice. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'x{2,}';
Example 2: in the fruits table, query F_ The name field value is a record in which the string ba appears at least once and at most three times. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';