【MySQL】五、条件查询（where，between ... and 关键字）

1. 语法格式

select 字段1,字段2 ,..., from 表名 where 条件;

注意：
首先执行的是from，然后是where，最后select。

2. 案例

2.1 查询工资等于5000的员工姓名？

mysql> select ename from emp where sal = 5000;
+-------+
| ename |
+-------+
| KING  |
+-------+
1 row in set (0.00 sec)

2.2 查询SMITH的工资？

mysql> select sal from emp where ename = 'SMITH'; # ename 是VARCHAR数据类型,SMITH需要用单引号括起来
+--------+ 
| sal    |
+--------+
| 800.00 |
+--------+
1 row in set (0.00 sec)

2.3 查询工资大于等于3000的员工？

mysql> select ename,sal from emp where sal >= 3000;
+-------+---------+
| ename | sal     |
+-------+---------+
| SCOTT | 3000.00 |
| KING  | 5000.00 |
| FORD  | 3000.00 |
+-------+---------+
3 rows in set (0.00 sec)

2.4 查询工资不等于3000的员工？

写法1：

select ename,sal from emp where sal != 3000;

写法2（<>关键符号）：

select ename,sal from emp where sal <> 3000; 

查询结果：

+--------+---------+
| ename  | sal     |
+--------+---------+
| SMITH  |  800.00 |
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| KING   | 5000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| JAMES  |  950.00 |
| MILLER | 1300.00 |
+--------+---------+
12 rows in set (0.00 sec)

2.5 找出工资在1100和3000之间的员工（包括1100和3000）

写法1：

select ename, sal from emp where sal>=1100 and sal<=3000;

写法2：（between关键字）

between … and …;
between表示一个区间范围，是一个闭区间；在使用时一定要左小右大！

select ename, sal from emp where sal between 1100 and 3000;

查询结果：

+--------+---------+
| ename  | sal     |
+--------+---------+
| ALLEN  | 1600.00 |
| WARD   | 1250.00 |
| JONES  | 2975.00 |
| MARTIN | 1250.00 |
| BLAKE  | 2850.00 |
| CLARK  | 2450.00 |
| SCOTT  | 3000.00 |
| TURNER | 1500.00 |
| ADAMS  | 1100.00 |
| FORD   | 3000.00 |
| MILLER | 1300.00 |
+--------+---------+
11 rows in set (0.00 sec)

注意：select ename, sal from emp where sal between 3000 and 1100 ;查询不到任何数据！
相当于 3000<=sal<=1100，不存在这样的数据.

mysql> select ename, sal from emp where sal between 3000 and 1100;
Empty set (0.00 sec)

2.6 between … and … 应用在字符（左闭右开）

select ename from emp where ename between 'A' and 'C';

查询结果：

+-------+
| ename |
+-------+
| ALLEN |
| BLAKE |
| ADAMS |
+-------+
3 rows in set (0.00 sec)