mysql命令行 - IN 和 BETWEEN

在上一节我们学习了mysql的逻辑操作数，这一节我们要学习mysql的另外两个很有用的操作数：

IN 和 BETWEEN

IN - 用来表示满足一个条件数组里的任意一个条件，例如我们要在employee_data里找出职务为Web Designer或者System Administrator的员工信息，如果使用上一节介绍的OR操作数，我们可以这么写：

SELECT f_name, l_name, title from               
    -> employee_data where 
    -> title = 'Web Designer' OR
    -> title = 'System Administrator';
+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.01 sec)

同样，我们可以用IN操作数来实现上述查询：

SELECT f_name, l_name, title from
    -> employee_data where title 
    -> IN ('Web Designer', 'System Administrator');
+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.00 sec)

你会发现，使用IN要比OR简洁，特别在条件数组比较大的时候。

BETWEEN - 用来表示同时满足两个条件，特别是当条件为整形变量。例如我们要查询年龄大于等于32并且年龄小于等于40的员工信息，同样我们可以只使用上一节介绍的AND操作数，但是混用BETWEEN和AND要简洁一点：

select f_name, l_name, age from
    -> employee_data where age BETWEEN
    -> 32 AND 40;
+---------+------------+------+
| f_name  | l_name     | age  |
+---------+------------+------+
| John    | Hagan      |   32 |
| Ganesh  | Pillai     |   32 |
| John    | MacFarland |   34 |
| Alok    | Nanda      |   32 |
| Hassan  | Rajabi     |   33 |
| Arthur  | Hoopla     |   32 |
| Kim     | Hunter     |   32 |
| Roger   | Lewis      |   35 |
| Danny   | Gibson     |   34 |
| Mike    | Harper     |   36 |
| Shahida | Ali        |   32 |
| Peter   | Champion   |   36 |
+---------+------------+------+
12 rows in set (0.00 sec)

上述例子查询了两个条件之间的情况，如果要查询两个条件之外的情况，我们还可以使用NOT BETWEEN:

select f_name, l_name, salary
    -> from employee_data where salary
    -> NOT BETWEEN
    -> 90000 AND 150000;

上述例子查询薪水不在90000和150000间的员工信息，注意不包括90000和150000。