mysql命令行 - 逻辑操作符

在上一节我们学习了简单的文本模式匹配在mysql条件查询语句中的使用，但是我们学到的只是单一条件查询。如果有多个查询条件，就要用到逻辑操作符。这一节我们要学习：

如何使用逻辑操作符？

mysql里逻辑操作符有三种：

1. AND - 与
2. OR   - 或
3. NOT - 非

1. AND 代表同时满足多个条件，例如：

SELECT f_name, l_name from employee_data
where salary > 70000 AND salary < 90000;
+--------+------------+
| f_name | l_name     |
+--------+------------+
| Mary   | Anchor     |
| Fred   | Kruger     |
| John   | MacFarland |
| Edward | Sakamuro   |
| Paul   | Simon      |
| Arthur | Hoopla     |
| Joseph | Irvine     |
+--------+------------+
7 rows in set (0.00 sec)

上述例子里，我们查询工资在7000和90000之间的员工的姓名。

2. OR 代表在多个条件中至少满足一个，例如：

SELECT l_name from employee_data where
l_name like 'S%' OR l_name like 'A%';
+----------+
| l_name   |
+----------+
| Sharma   |
| Anchor   |
| Sakamuro |
| Simon    |
| Sehgal   |
| Simlai   |
| Ali      |
+----------+
7 rows in set (0.00 sec)

上述例子里，我们查询起始字母为S或者A的员工的姓。

3. NOT 代表不满足某个条件，例如：

SELECT f_name, l_name, title from employee_data
where title NOT LIKE "%programmer%";
+---------+----------+----------------------------+
| f_name  | l_name   | title                      |
+---------+----------+----------------------------+
| Manish  | Sharma   | CEO                        |
| Anamika | Pandit   | Web Designer               |
| Mary    | Anchor   | Web Designer               |
| Kim     | Hunter   | Senior Web Designer        |
| Roger   | Lewis    | System Administrator       |
| Danny   | Gibson   | System Administrator       |
| Mike    | Harper   | Senior Marketing Executive |
| Monica  | Sehgal   | Marketing Executive        |
| Hal     | Simlai   | Marketing Executive        |
| Joseph  | Irvine   | Marketing Executive        |
| Shahida | Ali      | Customer Service Manager   |
| Peter   | Champion | Finance Manager            |
+---------+----------+----------------------------+
12 rows in set (0.00 sec)

上述例子里，我们查询职务名称不含programmer的员工的姓名和职务名称。

当然，我们还可以混用AND、OR、NOT，例如：

SELECT f_name, l_name , age from employee_data
where (l_name like 'S%' OR l_name like 'A%') AND
age < 30;
+--------+----------+------+
| f_name | l_name   | age  |
+--------+----------+------+
| Manish | Sharma   |   28 |
| Mary   | Anchor   |   26 |
| Edward | Sakamuro |   25 |
| Hal    | Simlai   |   27 |
+--------+----------+------+
4 rows in set (0.00 sec)

上述例子里，我们查询员工的姓名和年龄，需要满足的条件为：姓以S或者A开头，并且年龄小于30岁。