MYSQL getting started 6: MYSQL operator _ MySQL

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