1. Arithmetic operator
Arithmetic operators are mainly used for mathematical operations. They can connect two values or expressions before and after the operator, and perform addition (+), subtraction (-), multiplication (*), division (/) and modulus (%) operations on the values or expressions.
1. Addition and subtraction operators
mysql> SELECT 100, 100 + 0, 100 - 0, 100 + 50, 100 + 50 -30, 100 + 35.5, 100 - 35.5 FROM dual; +-----+---------+---------+----------+--------------+------------+------------+ | 100 | 100 + 0 | 100 - 0 | 100 + 50 | 100 + 50 -30 | 100 + 35.5 | 100 - 35.5 | +-----+---------+---------+----------+--------------+------------+------------+ | 100 | 100 | 100 | 150 | 120 | 135.5 | 64.5 | +-----+---------+---------+----------+--------------+------------+------------+ 1 row in set (0.00 sec)
The following conclusions can be drawn from the calculation results:
- An integer type value adds and subtracts an integer, and the result is still an integer;
- An integer type value adds and subtracts floating-point numbers, and the result is a floating-point number;
- The priority of addition and subtraction is the same. The result of adding before subtraction is the same as that of subtraction before addition;
- In Java, if there is a string on the left and right sides of +, it represents the splicing of strings. However, in mysql, + only indicates the addition of values. If a non numeric type is encountered, try to convert it to a numeric value first. If the conversion fails, press 0. (supplement: String splicing in MySQL should be realized by using string function CONCAT())
2. Multiplication and division operators
mysql> SELECT 100, 100 * 1, 100 * 1.0, 100 / 1.0, 100 / 2,100 + 2 * 5 / 2,100 /3, 100 DIV 0 FROM dual; +-----+---------+-----------+-----------+---------+-----------------+---------+-----------+ | 100 | 100 * 1 | 100 * 1.0 | 100 / 1.0 | 100 / 2 | 100 + 2 * 5 / 2 | 100 /3 | 100 DIV 0 | +-----+---------+-----------+-----------+---------+-----------------+---------+-----------+ | 100 | 100 | 100.0 | 100.0000 | 50.0000 | 105.0000 | 33.3333 | NULL | +-----+---------+-----------+-----------+---------+-----------------+---------+-----------+ 1 row in set (0.00 sec)
#Calculate the annual basic salary of employees SELECT employee_id,salary,salary * 12 annual_sal FROM employees;
The following conclusions can be drawn from the calculation results:
- When a number is multiplied by integer 1 and divided by integer 1, the original number is still obtained;
- A number multiplied by floating point number 1 and divided by floating point number 1 becomes a floating point number, and the value is equal to the original number;
- After a number is divided by an integer, whether it can be divided or not, the result is a floating point number;
- When one number is divided by another number, the result will be a floating-point number and will be kept to 4 decimal places;
- The priority of multiplication and division is the same. The result of multiplication before division is the same as that of division before multiplication.
- In mathematical operations, 0 cannot be used as a divisor. In MySQL, a number divided by 0 is NULL.
3. Modulus (remainder) operator
Modulo (remainder) the fields i in t22 table to 3 and 5.
mysql> SELECT 12 % 3, 12 MOD 5 FROM dual; +--------+----------+ | 12 % 3 | 12 MOD 5 | +--------+----------+ | 0 | 2 | +--------+----------+ 1 row in set (0.00 sec)
#Filter out employees_ ID is an even number of employees SELECT * FROM employees WHERE employee_id MOD 2 = 0;
It can be seen that the result of 100 for 3 is 3, and the result of 5 is 0.
2. Comparison operator
The comparison operator is used to compare the operands on the left and right of the expression. If the comparison result is true, it will return 1, if the comparison result is false, it will return 0, and in other cases, it will return NULL.
Comparison operators are often used as conditions of SELECT query statements to return qualified result records.
1. Equal sign operator
-
The equal sign operator (=) determines whether the values, strings or expressions on both sides of the equal sign are equal. If they are equal, it returns 1, and if they are not equal, it returns 0.
-
When using the equal sign operator, follow the following rules:
- If the value, string or expression on both sides of the equal sign are strings, MySQL will compare them according to the string, which compares whether the ANSI encoding of the characters in each string is equal.
- If the values on both sides of the equal sign are integers, MySQL will compare the size of the two values according to integers.
- If one of the values on both sides of the equal sign is an integer and the other is a string, MySQL will convert the string into a number for comparison.
- If one of the values, strings, or expressions on either side of the equal sign is NULL, the comparison result is NULL.
-
Comparison: use of assignment symbols in SQL:=
mysql> SELECT 1 = 1, 1 = '1', 1 = 0, 'a' = 'a', (5 + 3) = (2 + 6), '' = NULL , NULL = NULL; +-------+---------+-------+-----------+-------------------+-----------+-------------+ | 1 = 1 | 1 = '1' | 1 = 0 | 'a' = 'a' | (5 + 3) = (2 + 6) | '' = NULL | NULL = NULL | +-------+---------+-------+-----------+-------------------+-----------+-------------+ | 1 | 1 | 0 | 1 | 1 | NULL | NULL | +-------+---------+-------+-----------+-------------------+-----------+-------------+ 1 row in set (0.00 sec)
mysql> SELECT 1 = 2, 0 = 'abc', 1 = 'abc' FROM dual; +-------+-----------+-----------+ | 1 = 2 | 0 = 'abc' | 1 = 'abc' | +-------+-----------+-----------+ | 0 | 1 | 0 | +-------+-----------+-----------+ 1 row in set, 2 warnings (0.00 sec)
#Query salary=10000. Note that in Java, the comparison is== SELECT employee_id,salary FROM employees WHERE salary = 10000;
2. Safety equals operator
The functions of the safe equal operator (< = >) and the equal operator (=) are similar. The only difference is that '< = >' can be used to judge NULL. When both operands are NULL, the return value is 1 instead of NULL; When an operand is NULL, its return value is 0 instead of NULL.
mysql> SELECT 1 <=> '1', 1 <=> 0, 'a' <=> 'a', (5 + 3) <=> (2 + 6), '' <=> NULL,NULL <=> NULL FROM dual; +-----------+---------+-------------+---------------------+-------------+---------------+ | 1 <=> '1' | 1 <=> 0 | 'a' <=> 'a' | (5 + 3) <=> (2 + 6) | '' <=> NULL | NULL <=> NULL | +-----------+---------+-------------+---------------------+-------------+---------------+ | 1 | 0 | 1 | 1 | 0 | 1 | +-----------+---------+-------------+---------------------+-------------+---------------+ 1 row in set (0.00 sec)
#Query commission_pct equals 0.40 SELECT employee_id,commission_pct FROM employees WHERE commission_pct = 0.40; SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> 0.40; #What if you change 0.40 to NULL?
You can see that when using the safe equal operator, when the values of operands on both sides are NULL, the returned result is 1 instead of NULL, and other returned results are the same as the equal operator.
3. Not equal to operator
The not equal operators (< > and! =) are used to judge whether the values of numbers, strings or expressions on both sides are not equal. If they are not equal, they return 1, and if they are equal, they return 0. Not equal to operator cannot judge NULL value. If either of the values on both sides is NULL, or both sides are NULL, the result is NULL.
Examples of SQL statements are as follows:
mysql> SELECT 1 <> 1, 1 != 2, 'a' != 'b', (3+4) <> (2+6), 'a' != NULL, NULL <> NULL; +--------+--------+------------+----------------+-------------+--------------+ | 1 <> 1 | 1 != 2 | 'a' != 'b' | (3+4) <> (2+6) | 'a' != NULL | NULL <> NULL | +--------+--------+------------+----------------+-------------+--------------+ | 0 | 1 | 1 | 1 | NULL | NULL | +--------+--------+------------+----------------+-------------+--------------+ 1 row in set (0.00 sec)
In addition, there are non symbolic operators:
4. Air transport operator
The airlift operator (IS NULL or ISNULL) determines whether a value IS NULL. If it IS NULL, it returns 1, otherwise it returns 0.
Examples of SQL statements are as follows:
mysql> SELECT NULL IS NULL, ISNULL(NULL), ISNULL('a'), 1 IS NULL;
+--------------+--------------+-------------+-----------+
| NULL IS NULL | ISNULL(NULL) | ISNULL('a') | 1 IS NULL |
+--------------+--------------+-------------+-----------+
| 1 | 1 | 0 | 0 |
+--------------+--------------+-------------+-----------+
1 row in set (0.00 sec)
#Query commission_pct equals NULL. Compare the following four ways of writing SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NULL; SELECT employee_id,commission_pct FROM employees WHERE commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE ISNULL(commission_pct); SELECT employee_id,commission_pct FROM employees WHERE commission_pct = NULL;
SELECT last_name, manager_id FROM employees WHERE manager_id IS NULL;
5. Non air operator
The non air transport operator (IS NOT NULL) determines whether a value IS NOT NULL. If it IS NOT NULL, it returns 1, otherwise it returns 0.
Examples of SQL statements are as follows:
mysql> SELECT NULL IS NOT NULL, 'a' IS NOT NULL, 1 IS NOT NULL; +------------------+-----------------+---------------+ | NULL IS NOT NULL | 'a' IS NOT NULL | 1 IS NOT NULL | +------------------+-----------------+---------------+ | 0 | 1 | 1 | +------------------+-----------------+---------------+ 1 row in set (0.01 sec)
#Query commission_pct is not equal to NULL SELECT employee_id,commission_pct FROM employees WHERE commission_pct IS NOT NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT commission_pct <=> NULL; SELECT employee_id,commission_pct FROM employees WHERE NOT ISNULL(commission_pct);
6. Minimum operator
The syntax format is: leap (value 1, value 2,..., value n). Where, "value n" means there are n values in the parameter list. Returns the minimum value when there are two or more parameters.
mysql> SELECT LEAST (1,0,2), LEAST('b','a','c'), LEAST(1,NULL,2);
+---------------+--------------------+-----------------+
| LEAST (1,0,2) | LEAST('b','a','c') | LEAST(1,NULL,2) |
+---------------+--------------------+-----------------+
| 0 | a | NULL |
+---------------+--------------------+-----------------+
1 row in set (0.00 sec)
It can be seen from the results that when the parameter is an integer or floating point number, leap will return the smallest value; When the parameter is a string, the character with the highest order in the alphabet is returned; When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL.
7. Maximum operator
The syntax format is greatest (value 1, value 2,..., value n). Where n indicates that there are n values in the parameter list. When there are two or more parameters, the return value is the maximum value. If any argument is NULL, the return value of GREATEST() is NULL.
mysql> SELECT GREATEST(1,0,2), GREATEST('b','a','c'), GREATEST(1,NULL,2);
+-----------------+-----------------------+--------------------+
| GREATEST(1,0,2) | GREATEST('b','a','c') | GREATEST(1,NULL,2) |
+-----------------+-----------------------+--------------------+
| 2 | c | NULL |
+-----------------+-----------------------+--------------------+
1 row in set (0.00 sec)
It can be seen from the results that when the parameter is an integer or floating point number, GREATEST will return the largest value; When the parameter is a string, the last character in the alphabet is returned; When there is NULL in the comparison value list, the size cannot be determined, and the return value is NULL.
8. BETWEEN AND operator
The format used by the BETWEEN operator is usually SELECT D FROM TABLE WHERE C BETWEEN A AND B. at this time, when C is greater than or equal to A and C is less than or equal to B, the result is 1, otherwise the result is 0.
mysql> SELECT 1 BETWEEN 0 AND 1, 10 BETWEEN 11 AND 12, 'b' BETWEEN 'a' AND 'c'; +-------------------+----------------------+-------------------------+ | 1 BETWEEN 0 AND 1 | 10 BETWEEN 11 AND 12 | 'b' BETWEEN 'a' AND 'c' | +-------------------+----------------------+-------------------------+ | 1 | 0 | 1 | +-------------------+----------------------+-------------------------+ 1 row in set (0.00 sec)
SELECT last_name, salary FROM employees WHERE salary BETWEEN 2500 AND 3500;
9. IN operator
The IN operator is used to determine whether a given value is a value IN the IN list. If yes, it returns 1, otherwise it returns 0. If the given value is null, or if NULL exists IN the IN list, the result is null.
mysql> SELECT 'a' IN ('a','b','c'), 1 IN (2,3), NULL IN ('a','b'), 'a' IN ('a', NULL);
+----------------------+------------+-------------------+--------------------+
| 'a' IN ('a','b','c') | 1 IN (2,3) | NULL IN ('a','b') | 'a' IN ('a', NULL) |
+----------------------+------------+-------------------+--------------------+
| 1 | 0 | NULL | 1 |
+----------------------+------------+-------------------+--------------------+
1 row in set (0.00 sec)
SELECT employee_id, last_name, salary, manager_id FROM employees WHERE manager_id IN (100, 101, 201);
10. NOT IN operator
NOT IN operator is used to judge whether the given value is not a value IN the IN list. If it is not a value IN the IN list, it returns 1, otherwise it returns 0.
mysql> SELECT 'a' NOT IN ('a','b','c'), 1 NOT IN (2,3);
+--------------------------+----------------+
| 'a' NOT IN ('a','b','c') | 1 NOT IN (2,3) |
+--------------------------+----------------+
| 0 | 1 |
+--------------------------+----------------+
1 row in set (0.00 sec)
11. LIKE operator
The LIKE operator is mainly used to match strings, usually used for fuzzy matching. If the conditions are met, it returns 1, otherwise it returns 0. If the given value or matching condition is NULL, the returned result is NULL.
The LIKE operator usually uses the following wildcards:
"%": Matches 0 or more characters. "_": Only one character can be matched.
Examples of SQL statements are as follows:
mysql> SELECT NULL LIKE 'abc', 'abc' LIKE NULL; +-----------------+-----------------+ | NULL LIKE 'abc' | 'abc' LIKE NULL | +-----------------+-----------------+ | NULL | NULL | +-----------------+-----------------+ 1 row in set (0.00 sec)
SELECT first_name FROM employees WHERE first_name LIKE 'S%';
SELECT last_name FROM employees WHERE last_name LIKE '_o%';
ESCAPE
- Avoiding special symbols: use ESCAPE characters. For example: convert [%] to [$%], [] to [$], and then add [ESCAPE '$'].
SELECT job_id FROM jobs WHERE job_id LIKE 'IT\_%';
- If you use \ to indicate ESCAPE, omit ESCAPE. If it is not \, add ESCAPE.
SELECT job_id FROM jobs WHERE job_id LIKE 'IT$_%' escape '$';
12. REGEXP operator
REGEXP operator is used to match strings. The syntax format is: expr REGEXP matching condition. If expr meets the matching conditions, return 1; If not, 0 is returned. If either expr or matching condition is NULL, the result is NULL.
The following wildcards are commonly used in REGEXP operator matching:
(1)'^'Matches a string that begins with a character after that character. (2)'$'Matches a string that ends with a character that precedes the character. (3)'.'Match any single character. (4)"[...]"Matches any character within square brackets. For example“[abc]"Match“ a"Or“ b"Or“ c". To name the range of characters, use a'-'. "[a-z]"Match any letter, and“[0-9]"Match any number. (5)'*'Matches zero or more characters that precede it. For example“ x*"Match any number of'x'Characters“[0-9]*"Match any number of numbers, and“*"Matches any number of characters.
Examples of SQL statements are as follows:
mysql> SELECT 'shkstart' REGEXP '^s', 'shkstart' REGEXP 't$', 'shkstart' REGEXP 'hk'; +------------------------+------------------------+-------------------------+ | 'shkstart' REGEXP '^s' | 'shkstart' REGEXP 't$' | 'shkstart' REGEXP 'hk' | +------------------------+------------------------+-------------------------+ | 1 | 1 | 1 | +------------------------+------------------------+-------------------------+ 1 row in set (0.01 sec)
mysql> SELECT 'atguigu' REGEXP 'gu.gu', 'atguigu' REGEXP '[ab]'; +--------------------------+-------------------------+ | 'atguigu' REGEXP 'gu.gu' | 'atguigu' REGEXP '[ab]' | +--------------------------+-------------------------+ | 1 | 1 | +--------------------------+-------------------------+ 1 row in set (0.00 sec)
3. Logical operators
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. 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.
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, 1 warning (0.00 sec)
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP');
2. Logic and operators
The logical AND (AND or & &) operator returns 1 when all the given values are non-zero AND are not NULL; When a given value or values are 0, it returns 0; Otherwise, NULL is returned.
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)
SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary >=10000 AND job_id LIKE '%MAN%';
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, return 1; otherwise, return NULL; Returns NULL when both values are NULL.
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, 2 warnings (0.00 sec)
#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%';
be careful:
OR can be used together with AND, but pay attention to their priority when using them. Since the priority of AND is higher than OR, first operate on the operands on both sides of AND, AND then combine them with the operands in OR.
4. Logical XOR operator
The XOR operator returns NULL when any one of the given values is NULL; If both non NULL values are 0 or are not equal to 0, 0 is returned; If one value is 0 and the other value is not 0, 1 is returned.
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)
select last_name,department_id,salary from employees where department_id in (10,20) XOR salary > 8000;
4. 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. Bitwise and operator
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.
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.
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.
Another 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)
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.
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.
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.
5. 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.
Extension: 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.
1. Query records starting with a specific character or string
The character '^' matches text that begins with a specific character or string.
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';
2. Query records ending with a specific character or string
The character '$' matches text that ends with a specific character or string.
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$';
3. Use the symbol "." To replace any character in the string
Character '.' Match any character.
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. 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.
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*';
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+';
5. Match the specified string
A regular expression can match a specified string as long as the string is in the query text. If you want to match multiple strings, separate them with the separator '|'.
In the fruits table, query F_ The name field value contains records with the string "on". The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'on';
In the fruits table, query F_ The name field value contains records with 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.
In the fruits table, use the LIKE operator to query F_ For the record whose name field value is "on", the SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name like 'on'; Empty set(0.00 sec)
6. Match any one of the specified characters
The square brackets "[]" specify a character set, and only match any one of them, that is, the text you are looking for.
In the fruits table, look for F_ The name field contains records with letters' o 'or't'. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP '[ot]';
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]';
7. Match characters other than the specified characters
"[^ character set]" matches any character that is not in the specified set.
In the fruits table, query F_ The ID field contains records with characters other than letters ae and numbers 12. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_id REGEXP '[^a-e1-2]';
8. Use {n,} or {n,m} to specify the number of consecutive occurrences of the string
"String {n,}" means to match the preceding characters at least N times; "String {n,m}" means matching the previous string 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.
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,}';
In the fruits table, query F_ Records with the string "ba" appearing at least once and at most three times in the name field value. The SQL statement is as follows:
mysql> SELECT * FROM fruits WHERE f_name REGEXP 'ba{1,3}';