组合where子句
1.1 and操作符
1.2 or操作符
1.3 计算次序
in 操作符
not操作符
1.1 AND操作符
MariaDB [test]> select id,age,province
-> from user
-> where age < 30 and province = ‘北京‘;
+----+------+----------+
| id | age | province |
+----+------+----------+
| 1 | 22 | 北京 |
| 4 | 14 | 北京 |
| 11 | 29 | 北京 |
| 13 | 24 | 北京 |
+----+------+----------+
4 rows in set (0.00 sec)
1.2 OR操作符
MariaDB [test]> select id,age,province
-> from user
-> where province = ‘天津‘ or province = ‘北京‘;
+----+------+----------+
| id | age | province |
+----+------+----------+
| 1 | 22 | 北京 |
| 3 | 56 | 天津 |
| 4 | 14 | 北京 |
| 7 | 45 | 北京 |
| 9 | 33 | 天津 |
| 11 | 29 | 北京 |
| 13 | 24 | 北京 |
+----+------+----------+
7 rows in set (0.00 sec)
1.3 计算次序
注意： 在处理or操作符之前，优先处理and操作符。
解决办法使用圆括号()
MariaDB [test]> select id,age,province
-> from user
-> where (province = ‘北京‘ or province = ‘天津‘) and age > 23;
+----+------+----------+
| id | age | province |
+----+------+----------+
| 3 | 56 | 天津 |
| 7 | 45 | 北京 |
| 9 | 33 | 天津 |
| 11 | 29 | 北京 |
| 13 | 24 | 北京 |
+----+------+----------+
5 rows in set (0.00 sec)
IN 操作符
IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配，使用逗号分隔。
MariaDB [test]> select id,age,province
-> from user
-> where age in (22,23,24,33)
-> order by age;
+----+------+----------+
| id | age | province |
+----+------+----------+
| 1 | 22 | 北京 |
| 13 | 24 | 北京 |
| 9 | 33 | 天津 |
+----+------+----------+
3 rows in set (0.00 sec)
3.NOT操作符
where子句中的not操作符只有一个功能，那就是否定它之后所跟的任何条件。
MariaDB [test]> select id,age,province-> from user-> where province not in (‘北京‘,‘天津‘);+----+------+----------+| id | age | province |+----+------+----------+| 2 | 25 | 广东 || 5 | 36 | 广东 || 6 | 68 | 湖南 || 8 | 17 | 河北 || 10 | 27 | 湖南 || 12 | 70 | 广东 |+----+------+----------+6 rows in set (0.00 sec)
