Mysql operator (job)

Mysql operator (job) Create Table tmp15, including varchar type field note and int type field price, use the operator to calculate different fields in the table; use logical operators to perform logical operations on data; mysql> create table tmp15 (note varchar (20), price int); Query OK, 0 rows affected (0.63 sec) mysql> select * from tmp15; + -------- + ------- + | note | price | + -------- + ------- + | aa | 20 | yus | 30 | book | 30 | mylife | 30 | love | 67 | | ben | 50 | + -------- + ------- + 6 rows in set (0.00 sec) 1. t The price field in the mp15 table performs the arithmetic operation mysql> select avg (price) 'average price', sum (price) 'price and ', min (price) 'lowest price ', max (price) 'maximum price' from tmp15; + -------------- + ----------- + -------------- + | average price | price and | lowest price | highest price | + ---------------- + ----------- + ------------ + | 37.8333 | 227 | 20 | + -------------- + ----------- + -------------- + 1 row in set (0.00 sec) 2. for the integer value field pri in tmp15 Compare mysql> select 50 <20 & 50> 30 by ce; + ---------------- + | 50 <20 & 50> 30 | + ------------------ + | 0 | + ---------------- + 1 row in set (0.00 sec) mysql> select 50 <20 | 50> 30; + ---------------- + | 50 <20 | 50> 30 | + ------------------ + | 1 | + ---------------- + 1 row in set (0.00 sec) mysql> select 50 <> 20; + -------- + | 50 <> 20 | + -------- + | 1 | + -------- + 1 row in set (0.00 sec) 3. determine whether the price value falls between 30 and 30 ~ Returns the maximum value compared to 70 and 30, and determines whether the price is a value IN the IN List (10, 20, 50, 35). 1) mysql> select max (price) 'maximum 'from tmp15 where price between 30 and 70; + ----------- + | maximum value | + ----------- + | 67 | + ----------- + 1 row in set (0.00 sec) 2) mysql> select * from tmp15 where price in (10, 20, 50, 35 ); + ------ + ------- + | note | price | + ------ + ------- + | aa | 20 | ben | 50 | + ------ + ------- + 2 rows in set (0.06 sec) 4 .. compare the string value field note in tmp15 to determine whether the note field in tmp15 is empty. Use LIKE to determine whether it starts with the letter 'D; use REGEXP to determine whether to end with the letter 'y; determine whether the letter 'g' or 'M' is included to determine whether the note field in Table tmp15 is empty mysql> select note from tmp15 where note is not null; + -------- + | note | + -------- + | aa | yus | book | mylife | love | ben | + -------- + mysql> select note from tmp15 where note is null; empty set (0.00 sec) uses LIKE to determine whether mysql starts with 'A'> select note from tmp15 where note like 'a % '; + ------ + | note | + ------ + | aa | + ------ + 1 row in set (0.00 sec) use REGEXP to determine whether mysql> select 'fo \ no' regexp 'Y $ 'At the end of 'y '; + --------------------- + | 'fo \ no' regexp 'Y $ '| + --------------------- + | 0 | + --------------------- + 1 row in set (0.06 sec) determine whether the letter 'g' or 'M' mysql> select note from tmp15 where note like '% g %' or '% m %'; Empty set, 1 warning (0.00 sec) 5. perform logical operations on mysql> select *, price & not NULL from tmp15 by comparing the value of the price field with null and 0; + -------- + ------- + ----------------- + | note | price & not null | + -------- + ------- + ------------------- + | aa | 20 | NULL | yus | 30 | NULL | book | 30 | NULL | mylife | 30 | NULL | love | 67 | NULL | ben | 50 | NULL | + -------- + ------- + ------------------- + 6 rows in set (0.00 sec)