MySQL基础之 AND和OR运算符

AND和OR运算符

　　作用：用于基于一个以上的条件对记录进行过滤

　　用法：可在WHERE子句中把两个或多个条件结合在一起。

　　AND：如果第一个条件和第二个条件都成立，才会显示一条记录

　　OR：如果第一个条件和第二个条件中只要有一个成立，则OR运算符就会显示一条记录

　　　　如果两个条件都成立，则显示两条记录

先来查看一下表

mysql> select *from information;
+------+----------+-----------------+-----------+
| id   | name     | address         | city      |
+------+----------+-----------------+-----------+
| 1001 | xiaoming | NiuJin          | England   |
| 1002 | xiaochen | Oxford street   | New York  |
| 1003 | xiaoming | chaoyang street | Beijing   |
| 1004 | xiaohua  | haha street     | Zhengzhou |
+------+----------+-----------------+-----------+
4 rows in set (0.00 sec)

AND运算符实例

显示名字为“xiaoing”，地址为“NiuJin”的人

mysql> SELECT * from information WHERE name='xiaoming' AND address='NiuJin';
+------+----------+---------+---------+
| id   | name     | address | city    |
+------+----------+---------+---------+
| 1001 | xiaoming | NiuJin  | England |
+------+----------+---------+---------+
1 row in set (0.00 sec)

OR运算符实例

显示名字是“xiaoming”，或者城市是“Zhengzhou”的人

mysql> SELECT * FROM  information WHERE name='xiaoming' OR city='Zhengzhou';
+------+----------+-----------------+-----------+
| id   | name     | address         | city      |
+------+----------+-----------------+-----------+
| 1001 | xiaoming | NiuJin          | England   |
| 1003 | xiaoming | chaoyang street | Beijing   |
| 1004 | xiaohua  | haha street     | Zhengzhou |
+------+----------+-----------------+-----------+
3 rows in set (0.00 sec)

显示要么名字为“xiaoming”，要么城市是“England”的人

mysql> SELECT * FROM information WHERE name='xiaochen' OR city='England';
+------+----------+---------------+----------+
| id   | name     | address       | city     |
+------+----------+---------------+----------+
| 1001 | xiaoming | NiuJin        | England  |
| 1002 | xiaochen | Oxford street | New York |
+------+----------+---------------+----------+
2 rows in set (0.00 sec)

AND 和 OR 的复杂结合（使用圆括号来组成复杂的表达式）

这种表达式比较的复杂，但也是常见的。

mysql> SELECT name,address,city  FROM information WHERE (city='England' OR city='Beijing') AND name='xiaoming';
+----------+-----------------+---------+
| name     | address         | city    |
+----------+-----------------+---------+
| xiaoming | NiuJin          | England |
| xiaoming | chaoyang street | Beijing |
+----------+-----------------+---------+
2 rows in set (0.00 sec)

这种适用于我们已经定好一个查询条件的内容，另外一个条件的内容待定的情况