MySQL必知必会 -- 数据过滤

组合WHERE子句

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子句，都应该使用圆括号明确地分组操作符。不要过分依赖默认计算次序，即使它确实是你想要的东西也是如此。

IN操作符

圆括号在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时，计算的次序更容易管理（因为使用的操作符更少）。
IN操作符一般比OR操作符清单执行更快。

NOT操作符

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子句取反，