mysql条件查询-查询在某个范围内的数据

1.查询在工资为10000的员工信息

mysql> select * from t_employee;
+----+--------+------------+--------+-----+
| id | name   | address    | salary | sex |
+----+--------+------------+--------+-----+
|  5 | 小明   | 四川       |   6978 | f   |
|  6 | 小兰   | 天津       |  12000 | f   |
|  7 | 张三   | 广东       |   9807 | f   |
|  9 | 小华   | 广东       |  12000 | f   |
| 10 | 小静   | 山东       |   7980 | m   |
| 11 | 王小二 | 上海       |   9809 | f   |
| 12 | 王二   | 中山火炬区 |  10000 | m   |
| 13 | 小龄   | 上海       |   3000 | f   |
| 15 | 小煦   | 海南       |   2334 | m   |
+----+--------+------------+--------+-----+
9 rows in set

mysql> select * from t_employee where salary=10000;
+----+------+------------+--------+-----+
| id | name | address    | salary | sex |
+----+------+------------+--------+-----+
| 12 | 王二 | 中山火炬区 |  10000 | m   |
+----+------+------------+--------+-----+

2.查询工资在5000-10000的员工信息

方法一：and
mysql> select * from t_employee where salary>5000 and salary<10000;
+----+--------+---------+--------+-----+
| id | name   | address | salary | sex |
+----+--------+---------+--------+-----+
|  5 | 小明   | 四川    |   6978 | f   |
|  7 | 张三   | 广东    |   9807 | f   |
| 10 | 小静   | 山东    |   7980 | m   |
| 11 | 王小二 | 上海    |   9809 | f   |
+----+--------+---------+--------+-----+
4 rows in set

方法二：between
mysql>  select * from t_employee where salary between 5000 and 10000;
+----+--------+------------+--------+-----+
| id | name   | address    | salary | sex |
+----+--------+------------+--------+-----+
|  5 | 小明   | 四川       |   6978 | f   |
|  7 | 张三   | 广东       |   9807 | f   |
| 10 | 小静   | 山东       |   7980 | m   |
| 11 | 王小二 | 上海       |   9809 | f   |
| 12 | 王二   | 中山火炬区 |  10000 | m   |
+----+--------+------------+--------+-----+
5 rows in set

3.查询工资小于5000和大于10000的员工信息

方法一：or
mysql> select * from t_employee where salary<5000 or salary>10000;
+----+------+---------+--------+-----+
| id | name | address | salary | sex |
+----+------+---------+--------+-----+
|  6 | 小兰 | 天津    |  12000 | f   |
|  9 | 小华 | 广东    |  12000 | f   |
| 13 | 小龄 | 上海    |   3000 | f   |
| 15 | 小煦 | 海南    |   2334 | m   |
+----+------+---------+--------+-----+
4 rows in set

方法二：not between
mysql> select * from t_employee where salary not between 5000 and 10000;
+----+------+---------+--------+-----+
| id | name | address | salary | sex |
+----+------+---------+--------+-----+
|  6 | 小兰 | 天津    |  12000 | f   |
|  9 | 小华 | 广东    |  12000 | f   |
| 13 | 小龄 | 上海    |   3000 | f   |
| 15 | 小煦 | 海南    |   2334 | m   |
+----+------+---------+--------+-----+
4 rows in set