求平均值
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)