MYSQL getting started 6: MYSQL operator _ MySQL

Source: Internet
Author: User
Tags bitwise operators
MYSQL getting started 6: MYSQL operator bitsCN.com

MYSQL getting started 6: MYSQL operators

Related links:

MYSQL: Basic operations

Http: // database/201212/173868 .html

MYSQL 2: use regular expressions to search

Http: // database/201212/173869 .html

MYSQL 3: full text search

Http: // database/201212/173873 .html

MYSQL entry 4: MYSQL data types

Http: // database/201212/175536 .html

MYSQL entry 5: MYSQL character set

Http: // database/201212/175541 .html

I. arithmetic operators

1. add

Mysql> select 1 + 2;

+ ----- +

| 1 + 2 |

+ ----- +

| 3 |

+ ----- +

2. subtraction

Mysql> select 1-2;

+ ----- +

| 1-2 |

+ ----- +

|-1 |

+ ----- +

3. multiplication

Mysql> select 2*3;

+ ----- +

| 2*3 |

+ ----- +

| 6 |

+ ----- +

4. Division

Mysql & gt; select 2/3;

+ -------- +

| 1, 2/3 |

+ -------- +

| 1, 0.6667 |

+ -------- +

5. vendors

Mysql> select 10 p 4;

+ ---------- +

| 10 p 4 |

+ ---------- +

| 2 |

+ ---------- +

6. obtain the remainder

Mysql> select 10 MOD 4;

+ ---------- +

| 10 MOD 4 |

+ ---------- +

| 2 |

+ ---------- +

II. Comparison operators

1. equal

Mysql> select 2 = 3;

+ ----- +

| 2 = 3 |

+ ----- +

| 0 |

+ ----- +

Mysql> select NULL = NULL;

+ ------------- +

| NULL = NULL |

+ ------------- +

| NULL |

+ ------------- +

2. not equal

Mysql> select 2 <> 3;

+ ------ +

| 2 <> 3 |

+ ------ +

| 1 |

+ ------ +

3. security equals

The difference with "=" is that when both operation codes are NULL, the obtained value is 1 instead of NULL. When an operation code is NULL, the value is 0 instead of NULL.

Mysql> select 2 <=> 3;

+ ------- +

| 2 <=> 3 |

+ ------- +

| 0 |

+ ------- +

Mysql> select null = null;

+ ----------- +

| Null = null |

+ ----------- +

| NULL |

+ ----------- +

Mysql> select null <=> null;

+ ------------- +

| Null <=> null |

+ ------------- +

| 1 |

+ ------------- +

4. less

Mysql> select 2 <3;

+ ----- +

| 2 <3 |

+ ----- +

| 1 |

+ ----- +

5. less than or equal

Mysql> select 2 <= 3;

+ ------ +

| 2 <= 3 |

+ ------ +

| 1 |

+ ------ +

6. greater

Mysql> select 2> 3;

+ ----- +

| 2> 3 |

+ ----- +

| 0 |

+ ----- +

7. greater than or equal

Mysql> select 2> = 3;

+ ------ +

| 2> = 3 |

+ ------ +

| 0 |

+ ------ +

8.

Mysql> select 5 between 1 and 10;

+ -------------------- +

| 5 between 1 and 10 |

+ -------------------- +

| 1 |

+ -------------------- +

9. IN

Mysql> select 5 in (1, 2, 3, 4, 5 );

+ ------------------ +

| 5 in (1, 2, 3, 4, 5) |

+ ------------------ +

| 1 |

+ ------------------ +

10. NOT IN

Mysql> select 5 not in (1, 2, 3, 4, 5 );

+ ---------------------- +

| 5 not in (1, 2, 3, 4, 5) |

+ ---------------------- +

| 0 |

+ ---------------------- +

11. IS NULL

Mysql> select null is NULL;

+ -------------- +

| Null is NULL |

+ -------------- +

| 1 |

+ -------------- +

Mysql> select 'A' is NULL;

+ ------------- +

| 'A' is NULL |

+ ------------- +

| 0 |

+ ------------- +

12. IS NOT NULL

Mysql> select null is not null;

+ ------------------ +

| Null is not null |

+ ------------------ +

| 0 |

+ ------------------ +

Mysql> select 'A' is not null;

+ ----------------- +

| 'A' is not null |

+ ----------------- +

| 1 |

+ ----------------- +

13. LIKE

Mysql> select '000000' like '000000 ';

+ -------------------- +

| '000000' like '000000' |

+ -------------------- +

| 1 |

+ -------------------- +

Mysql> select '000000' like '12 _';

+ -------------------- +

| '000000' like '12 _ '|

+ -------------------- +

| 0 |

+ -------------------- +

14. REGEXP

Mysql> select 'Beijing' REGEXP 'Jing ';

+ ------------------------- +

| 'Beijing' REGEXP 'Jing' |

+ ------------------------- +

| 1 |

+ ------------------------- +

Mysql> select 'Beijing' REGEXP 'Si ';

+ ----------------------- +

| 'Beijing' REGEXP 'Xi '|

+ ----------------------- +

| 0 |

+ ----------------------- +

III. logical operators

1.

Mysql> select 2 and 0;

+ --------- +

| 2 and 0 |

+ --------- +

| 0 |

+ --------- +

Mysql> select 2 and 1;

+ --------- +

| 2 and 1 |

+ --------- +

| 1 |

+ --------- +

2. or

Mysql> select 2 or 0;

+ -------- +

| 2 or 0 |

+ -------- +

| 1 |

+ -------- +

Mysql> select 2 or 1;

+ -------- +

| 2 or 1 |

+ -------- +

| 1 |

+ -------- +

Mysql> select 0 or 0;

+ -------- +

| 0 or 0 |

+ -------- +

| 0 |

+ -------- +

Mysql> select 1 | 0;

+ -------- +

| 1 | 0 |

+ -------- +

| 1 |

+ -------- +

3. non-

Mysql> select not 1;

+ ------- +

| Not 1 |

+ ------- +

| 0 |

+ ------- +

Mysql> select! 0;

+ ---- +

|! 0 |

+ ---- +

| 1 |

+ ---- +

4. exclusive or

Mysql> select 1 xor 1;

+ --------- +

| 1 xor 1 |

+ --------- +

| 0 |

+ --------- +

Mysql> select 0 xor 0;

+ --------- +

| 0 xor 0 |

+ --------- +

| 0 |

+ --------- +

Mysql> select 1 xor 0;

+ --------- +

| 1 xor 0 |

+ --------- +

| 1 |

+ --------- +

Mysql> select null or 1;

+ ----------- +

| Null or 1 |

+ ----------- +

| 1 |

+ ----------- +

Mysql> select 1 ^ 0;

+ ------- +

| 1 ^ 0 |

+ ------- +

| 1 |

+ ------- +

IV. bitwise operators

1. bitwise AND

Mysql> select 3 & 5;

+ ----- +

| 3 & 5 |

+ ----- +

| 1 |

+ ----- +

2. by bit or

Mysql> select 3 | 5;

+ ----- +

| 3 | 5 |

+ ----- +

| 7 |

+ ----- +

3. bitwise OR

Mysql> select 3 ^ 5;

+ ----- +

| 3 ^ 5 |

+ ----- +

| 6 |

+ ----- +

4. bitwise inversion

+ ---------------------- +

| ~ 3 |

+ ---------------------- +

| 1, 18446744073709551612 |

+ ---------------------- +

Mysql> select ~ 18446744073709551612;

+ ----------------------- +

| ~ 18446744073709551612 |

+ ----------------------- +

| 3 |

+ ----------------------- +

5. shift right by bit

Mysql> select 3> 1;

+ ------ +

| 3> 1 |

+ ------ +

| 1 |

+ ------ +

6. shift left by bit

Mysql> select 3 <1;

+ ------ +

| 3 <1 |

+ ------ +

| 6 |

+ ------ +

5. operator priority order

Highest priority: =

1 |, OR, XOR

2 &, AND

3 BETWEEN, CASE, WHEN, THEN, ELSE

4 =, <=>, >=, >,<=, <, <> ,! =, IS, LIKE, REGEXP, IN

5 |

6 &

7 <,>

8-, +

9 *,/, p, %, MOD

10 ^

11-(unary minus ),~ (Unary bit inversion)

12 !, NOT

Lowest priority BINARY, COLLATE

BitsCN.com

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.