Understanding MySQL operators and common built-in functions _ MySQL

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.