AND和OR运算符
作用:用于基于一个以上的条件对记录进行过滤
用法:可在WHERE子句中把两个或多个条件结合在一起。
AND:如果第一个条件和第二个条件都成立,才会显示一条记录
OR:如果第一个条件和第二个条件中只要有一个成立,则OR运算符就会显示一条记录
如果两个条件都成立,则显示两条记录
先来查看一下表
mysql> select *from information;
+------+----------+-----------------+-----------+
| id | name | address | city |
+------+----------+-----------------+-----------+
| | xiaoming | NiuJin | England |
| | xiaochen | Oxford street | New York |
| | xiaoming | chaoyang street | Beijing |
| | xiaohua | haha street | Zhengzhou |
+------+----------+-----------------+-----------+
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)
这种适用于我们已经定好一个查询条件的内容,另外一个条件的内容待定的情况