MYSQL getting started 6: MYSQL Operators

MYSQL getting started 6: MYSQL operator links: MYSQL getting started 1: basic operations http://www.bkjia.com/database/201212/173868.htmlMYSQL Entry 2: Search Using Regular Expressions http://www.bkjia.com/database/201212/173869.htmlMYSQL Entry 3: Full Text Search http://www.bkjia.com/database/201212/173873.htmlMYSQL Entry 4: MYSQL Data Types http://www.bkjia.com/database/201212/175536.htmlMYSQL Entry 5: MYSQL Character Set http://www.bkjia.com/database/201212/175541.html I. Arithmetic Operators 1. Add www.2cto.com mysql> select 1 + 2; + ----- + | 1 + 2 | + ----- + | 3 | + ----- + 2. Remove mysql> select 1-2; + ----- + | 1-2 | + ----- + |-1 | + ----- + 3. Take mysql> select 2*3; + ----- + | 2*3 | + ----- + | 6 | + ----- + 4, except mysql> select 2/3; + -------- + | 2/3 | + -------- + | 0.6667 | + -------- + 5, www.2cto.com mysql> select 10 DIV 4; + ---------- + | 10 DIV 4 | + ---------- + | 2 | + ---------- + 6. Obtain the remainder mysql> select 10 MOD 4; + ---------- + | 10 MOD 4 | + ---------- + | 2 | + ---------- + 2. Comparison operator 1, equal to mysql> select 2 = 3; + ----- + | 2 = 3 | + ----- + | 0 | + ----- + mysql> select NULL = NULL; + ------------- + | NULL = NULL | + ------------- + | NULL | + ------------- + 2, not equal to mysql> select 2 <> 3; + ------ + | 2 <> 3 | + ------ + | 1 | + ------ + 3. The difference between security and "=" is that when both operation codes are NULL, the obtained value is 1 rather than NULL. When an operation code is NULL, the obtained value is 0 rather than NULL. Mysql> select 2 <=> 3; + ------- + | 2 <=> 3 | + ------- + | 0 | + ------- + mysql> select null = null; + ----------- + | null = null | + ----------- + | NULL | + ----------- + mysql> select null <=> null; + ------------- + | null <=> null | + ------------- + | 1 | + ------------- + 4, less than mysql> select 2 <3; + ----- + | 2 <3 | + ----- + | 1 | + ----- + 5, less than or equal to mysql> select 2 <= 3; + ------ + | 2 <= 3 | + ------ + | 1 | + ------ + 6, greater than mysql> select 2> 3; + ----- + | 2> 3 | + ----- + | 0 | + ----- + 7, greater than or equal to mysql> select 2> = 3; + ------ + | 2> = 3 | + ------ + | 0 | + ------ + 8, BETWEEN 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> selec T 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 '20160301' like '20160301 '; + -------------------- + | '000000' like '000000' | + ------------------------ + | 1 | + -------------------- + mysql> select '000000' like '12 _'; + ------------------ + | '000000' li Ke '12 _ '| + -------------------- + | 0 | + -------------------- + 14. REGEXP mysql> select 'beijing' REGEXP 'jing '; + ------------------------- + | 'beijing' REGEXP 'jing' | + --------------------------- + | 1 | + ----------------------- + mysql> select 'beijing' REGEXP 'xi '; + ----------------------- + | 'beijing' REGEXP 'xi' | + ----------------------- + | 0 | + --------------------- + 3. logical operators www.2cto.com 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 www.2cto.com 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 | + ------- + 4. bitwise operator 1. bitwise AND mysql> select 3 & 5; + ----- + | 3 & 5 | + ----- + | 1 | + ----- + 2, by bit or mysql> select 3 | 5; + ----- + | 3 | 5 | + ----- + | 7 | + ----- + 3. select 3 ^ 5 by bit or mysql>; + ----- + | 3 ^ 5 | + ----- + | 6 | + ----- + 4. bitwise inversion + -------------------- + | ~ 3 | + -------------------- + | 18446744073709551612 | + ------------------------ + mysql> select ~ 18446744073709551612; + --------------------- + | ~ 18446744073709551612 | + ----------------------- + | 3 | + ----------------------- + 5, shift right by bit www.2cto.com mysql> select 3> 1; + ------ + | 3> 1 | + ------ + | 1 | + ------ + 6. Shift left by bit mysql> select 3 <1; + ------ + | 3 <1 | + ------ + | 6 | + ------ + 5. Highest operator priority: = 1 |, OR, XOR 2 &&, AND 3 BETWEEN, CASE, WHEN, THEN, ELSE 4 =, <=>, >=, >,<=, <<> ,! =, IS, LIKE, REGEXP, IN 5 | 6 & 7 <,> 8-, + 9 *,/, DIV, %, MOD 10 ^ 11-(unary minus ),~ (Unary bit inversion) 12 !, NOT lowest priority BINARY, COLLATE