本文共 4197 字，大约阅读时间需要 13 分钟。
WHERE可以使用多个子句。这些子句可以两种方式使用：以AND子句的方式或OR子句的方式使用。
AND 用在WHERE子句中的关键字，用来指示检索满足所有给定条件的行。
OR操作符与AND操作符不同，它指示MySQL检索匹配任一条件的行。请看如下的SELECT语句：MariaDB [test]> select * from linux;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user1 | 111 | boy | 18 || user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user4 | 444 | boy | 22 || user5 | 555 | boy | 30 || a | passa | boy | 22 || 1 | pass1 | girl | 30 || user1 | pass1 | girl | 18 |+-------+--------+------+------+8 rows in set (0.00 sec)MariaDB [test]> select user,passwd from linux where sex='boy' and age<='22';+-------+--------+| user | passwd |+-------+--------+| user1 | 111 || user3 | 333 | /检索年龄小于22的男孩| user4 | 444 || a | passa |+-------+--------+4 rows in set (0.00 sec)MariaDB [test]> select user,passwd from linux where sex='girl' or age>=30;+-------+--------+| user | passwd |+-------+--------+| user2 | 222 | /检索女孩 或 年龄大于30的用户名，密码| user5 | 555 || 1 | pass1 || user1 | pass1 |+-------+--------+4 rows in set (0.00 sec)
WHERE可包含任意数目的AND和OR操作符。允许两者结合以进行复杂和高级的过滤。
并且优先处理 AND 操作符MariaDB [test]> select * from linux where sex='girl' and age>=30 or sex='boy' and age<=22;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user1 | 111 | boy | 18 | /检索大于30的女性或者小于22的男性| user3 | 333 | boy | 12 || user4 | 444 | boy | 22 || a | passa | boy | 22 || 1 | pass1 | girl | 30 |+-------+--------+------+------+5 rows in set (0.00 sec)MariaDB [test]> select * from linux where (age=18 or age=22) and sex='boy';+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| user1 | 111 | boy | 18 | /想要先处理OR 关键字需要加上（）进行| user4 | 444 | boy | 22 || a | passa | boy | 22 |+-------+--------+-----+------+3 rows in set (0.00 sec)
因此任何时候使用具有AND和OR操作符的WHERE子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。
圆括号在WHERE子句中还有另外一种用法就是 IN 操作符。IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，用逗号隔开。
MariaDB [test]> select * from linux where age IN (18,22) and sex='boy';+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| user1 | 111 | boy | 18 || user4 | 444 | boy | 22 || a | passa | boy | 22 |+-------+--------+-----+------+3 rows in set (0.00 sec)MariaDB [test]> select * from linux where age IN (18,22) and sex='boy' order by user;+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| a | passa | boy | 22 || user1 | 111 | boy | 18 || user4 | 444 | boy | 22 |+-------+--------+-----+------+3 rows in set (0.00 sec)MariaDB [test]> select * from linux where age IN (18,22) and sex='boy' order by user DESC;+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| user4 | 444 | boy | 22 || user1 | 111 | boy | 18 || a | passa | boy | 22 |+-------+--------+-----+------+3 rows in set (0.00 sec)
在使用长的合法选项清单时，IN操作符的语法更清楚且更直观。
IN操作符一般比OR操作符清单执行更快。 WHERE子句中的NOT操作符有且只有一个功能，那就是否定它之后所跟的任何条件.
MariaDB [test]> select * from linux where age NOT IN (18,22) and sex='boy';+-------+--------+-----+------+| user | passwd | sex | age |+-------+--------+-----+------+| user3 | 333 | boy | 12 || user5 | 555 | boy | 30 |+-------+--------+-----+------+2 rows in set (0.00 sec)MariaDB [test]> select * from linux where age NOT IN (18,22) ;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user5 | 555 | boy | 30 || 1 | pass1 | girl | 30 |+-------+--------+------+------+4 rows in set (0.00 sec)MariaDB [test]> select * from linux where age NOT IN (18,22) order by user;+-------+--------+------+------+| user | passwd | sex | age |+-------+--------+------+------+| 1 | pass1 | girl | 30 || user2 | 222 | girl | 23 || user3 | 333 | boy | 12 || user5 | 555 | boy | 30 |+-------+--------+------+------+4 rows in set (0.00 sec)
MySQL支持使用NOT对IN、BETWEEN和EXISTS子句取反，
转载地址：http://huxli.baihongyu.com/