Understanding MySQL operators and common built-in functions _ MySQL

Understanding MySQL operators and common built-in functions 1. MySQL operators

Note:

1. in division and modulus operations, if the divisor is 0, it will be an invalid divisor, and the return value is NULL.

In the modulo operation, you can also use the MOD (a, B) function or a % B.

mysql> select 1/0, 100%0;+------+-------+| 1/0 | 100%0 |+------+-------+| NULL | NULL |+------+-------+1 row in set (0.01 sec)mysql> select 3%2,mod(3,2);+------+----------+| 3%2 | mod(3,2) |+------+----------+| 1 | 1 |+------+----------+1 row in set (0.00 sec)

2. NULL can only be compared with <=>. if other comparison operators are used, NULL is returned.

Mysql> select 'A' <'B', 'A' <'A', 1 <2, null <=> null; + --------- + ----- + ----------- + | 'A' <'B' | 'A' <'A' | 1 <2 | null <=> null | + --------- + ----- + ------------- + | 1 | 0 | 1 | 1 | + --------- + ----- + ------------- + 1 row in set (0.02 sec) mysql> select 'A' <'B', 'A' <'A', 1 <2, null
  
  
3. BETWEEN IN
 
The between operator uses "a BETWEEN min AND max". if a is greater than or equal to min AND less than or equal to max, 1 is returned; otherwise, 0 is returned.
The IN operator uses "a IN (values1, values2 ,...) "When the value of a exists in the list, Zheng e expression returns 1; otherwise, 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.00 sec)mysql> select 1 in(1,2,3),'t' in ('t','a','b','f'),0 in(1,2);+-------------+--------------------------+-----------+| 1 in(1,2,3) | 't' in ('t','a','b','f') | 0 in(1,2) |+-------------+--------------------------+-----------+| 1 | 1 | 0 |+-------------+--------------------------+-----------+1 row in set (0.00 sec)
 
  
4. REGEXP operator format "str REGEXP str_pat"
 
Returns 1 if the str string contains a string matched by str_pat. otherwise, 0 is returned.
 
  
mysql> select 'abcdef' regexp 'ac','abcdef' regexp 'ab','abcdefg' regexp 'k';+----------------------+----------------------+----------------------+| 'abcdef' regexp 'ac' | 'abcdef' regexp 'ab' | 'abcdefg' regexp 'k' |+----------------------+----------------------+----------------------+| 0 | 1 | 0 |+----------------------+----------------------+----------------------+1 row in set (0.00 sec)
 
  
5. logic AND logic OR
 
AND: if all operands are non-zero and not null, 1 is returned. if one or more operands are 0, 0 is returned. if either of the operands is NULL, NULL is returned.
 
OR: if both operands are non-NULL values, if any of them is a non-zero value, 1 is returned; otherwise, 0 is returned;
 
If one operand is NULL, if the other is not 0, result 1 is returned; otherwise, NULL is returned;
 
If both operands are NULL, the result is NULL.
 
  
mysql> select (1 and 1),(0 and 1),(3 and 1),(1 and null);+-----------+-----------+-----------+--------------+| (1 and 1) | (0 and 1) | (3 and 1) | (1 and null) |+-----------+-----------+-----------+--------------+| 1 | 0 | 1 | NULL |+-----------+-----------+-----------+--------------+1 row in set (0.00 sec)mysql> select (1 or 0),(0 or 0),(1 or null),(1 or 1),(null or null);+----------+----------+-------------+----------+----------------+| (1 or 0) | (0 or 0) | (1 or null) | (1 or 1) | (null or null) |+----------+----------+-------------+----------+----------------+| 1 | 0 | 1 | 1 | NULL |+----------+----------+-------------+----------+----------------+1 row in set (0.00 sec)
 
  
6. bitwise operations
 
Bit and perform logic and operations on the binary bit of multiple operands
 
  
mysql> select bin(2);+--------+| bin(2) |+--------+| 10 |+--------+1 row in set (0.00 sec)mysql> select bin(3);+--------+| bin(3) |+--------+| 11 |+--------+1 row in set (0.00 sec)mysql> select bin(100);+----------+| bin(100) |+----------+| 1100100 |+----------+1 row in set (0.00 sec)mysql> select 2&3&100;+---------+| 2&3&100 |+---------+| 0 |+---------+1 row in set (0.00 sec)
 
  
7. bitwise inversion
 
In MySQL, constant numbers are represented in 8 bytes by default, 8 bytes are 64-bit, and the binary value of constant 1 is represented as 63 0 plus 1 1, after bitwise inversion, 63 values are added to 1 value and 0 values are converted to decimal format, which is 18446744073709551614.
 
  
 
  
8. shift right
 
  
II. operator priority
  
III. Common built-in functions
  
 
  
 
  
 
  
 
  
Note:
 
Date_format (date, fmt) fmt format:
 
Http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format
 
Date_add (date, INTERVAL expr type) type:
 
Http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add
 
  
 
  
   
  
Http://dev.mysql.com/doc/refman/5.5/en/functions.html