关于MySQL运算例子

求平均值
mysql> select avg(price) from tmp151;
+------------+
| avg(price) |
+------------+
|    60.0000 |
+------------+
1 row in set (0.00 sec)

求和
mysql> select sum(price) from tmp151;
+------------+
| sum(price) |
+------------+
|        240 |
+------------+
1 row in set (0.00 sec)

大于70
mysql> select price>70 from tmp151;
+----------+
| price>70 |
+----------+
|        1 |
|        0 |
|        1 |
|        0 |
+----------+
4 rows in set (0.00 sec)

小于70
mysql> select price<70 from tmp151;
+----------+
| price<70 |
+----------+
|        0 |
|        1 |
|        0 |
|        1 |
+----------+
4 rows in set (0.00 sec)

判断是否在30-80之间
mysql> select * from tmp151 where price between 30 and 80;
+------+-------+
| note | price |
+------+-------+
| a    |    80 |
| b    |    35 |
| c    |    75 |
| d    |    50 |
+------+-------+
4 rows in set (0.00 sec)

判断30-70之间最大的
mysql> select note ,max(price) maxprice from tmp151 where price between 30 and 7
0;
+------+----------+
| note | maxprice |
+------+----------+
| b    |       50 |
+------+----------+
1 row in set (0.07 sec)

判断price是否为IN列表（10, 20, 50, 35）中的某个值
mysql> select note,price in(10,20,50,35) from tmp151;
+------+-----------------------+
| note | price in(10,20,50,35) |
+------+-----------------------+
| a    |                     0 |
| b    |                     1 |
| c    |                     0 |
| d    |                     1 |
+------+-----------------------+
4 rows in set (0.05 sec)

判断表tmp15中note字段是否为空
mysql> select note,note is not null from tmp151;
+------+------------------+
| note | note is not null |
+------+------------------+
| a    |                1 |
| b    |                1 |
| c    |                1 |
| d    |                1 |
+------+------------------+
4 rows in set (0.05 sec)

用LIKE判断是否以字母'd'开头
mysql> select * from tmp151 where note like 'd%';
+------+-------+
| note | price |
+------+-------+
| d    |    50 |
+------+-------+
1 row in set (0.01 sec)

使用REGEXP判断是否以字母'y'尾
mysql> select * from tmp151 where note regexp '%y';
Empty set (0.07 sec)
没有以y结尾的 所以没有

将price字段值与NULL，0进行逻辑运算
mysql> select *,price&& not null from tmp151;
+------+-------+------------------+
| note | price | price&& not null |
+------+-------+------------------+
| a    |    80 |             NULL |
| b    |    35 |             NULL |
| c    |    75 |             NULL |
| d    |    50 |             NULL |
+------+-------+------------------+
4 rows in set (0.01 sec)

对tmp15中的字符串数值字段note进行比较运算
mysql> select * from tmp15 where note>'h';
+-------+-------+
| note  | price |
+-------+-------+
| tom   |   487 |
| sun   |    40 |
| hanxx |   459 |
| lvxx  |    86 |
+-------+-------+
4 rows in set (0.00 sec)