MySQL必知必會---資料過濾

標籤：mysql

組合where子句

1.1 and操作符

1.2 or操作符

1.3 計算次序

2. in 操作符
3. 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)

2. 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)

MySQL必知必會---資料過濾