一,使用WHERE子句
WHERE子句操作符
操作符 | 说明 |
---|---|
= | 等于 |
<> | 不等于 |
!= | 不等于 |
< | 小于 |
<= | 小于等于 |
> | 等于 |
>= | 大于等于 |
mysql> SELECT * FROM departments
-> WHERE location_id=1700;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 30 | Pur | 114 | 1700 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE location_id<1800;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 30 | Pur | 114 | 1700 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE location_id<>1700;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 60 | IT | 103 | 1400 |
| 50 | Shi | 121 | 1500 |
| 20 | Mar | 201 | 1800 |
| 40 | Hum | 203 | 2400 |
| 80 | Sal | 145 | 2500 |
| 70 | Pub | 204 | 2700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE location_id!=1700;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 60 | IT | 103 | 1400 |
| 50 | Shi | 121 | 1500 |
| 20 | Mar | 201 | 1800 |
| 40 | Hum | 203 | 2400 |
| 80 | Sal | 145 | 2500 |
| 70 | Pub | 204 | 2700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE location_id BETWEEN 1700 AND 2000;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 20 | Mar | 201 | 1800 |
| 30 | Pur | 114 | 1700 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE manager_id IS NULL;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 120 | Tre | NULL | 1700 |
| 130 | Cor | NULL | 1700 |
| 140 | Con | NULL | 1700 |
+---------------+-----------------+------------+-------------+
二,组合WHERE子句
- AND操作符
mysql> SELECT * FROM departments
-> WHERE location_id=1700 AND manager_id<200;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 30 | Pur | 114 | 1700 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
+---------------+-----------------+------------+-------------+
- OR操作符
mysql> SELECT * FROM departments
-> WHERE location_id=1700 OR manager_id<200;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 30 | Pur | 114 | 1700 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
- 当同时包含AND和OR操作符时,优先处理AND操作符
mysql> SELECT * FROM departments
-> WHERE location_id<2000 OR department_id>30 AND manager_id<200;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 20 | Mar | 201 | 1800 |
| 30 | Pur | 114 | 1700 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE department_id>30 AND manager_id<200;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
+---------------+-----------------+------------+-------------+
mysql> SELECT * FROM departments
-> WHERE location_id<2000;
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 20 | Mar | 201 | 1800 |
| 30 | Pur | 114 | 1700 |
| 50 | Shi | 121 | 1500 |
| 60 | IT | 103 | 1400 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+
- IN操作符
IN操作符用来指定条件范围,范围中的每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单,全都括在圆括号中。
mysql> SELECT * FROM departments
-> WHERE location_id IN (1700,2500);
+---------------+-----------------+------------+-------------+
| department_id | department_name | manager_id | location_id |
+---------------+-----------------+------------+-------------+
| 10 | Adm | 200 | 1700 |
| 30 | Pur | 114 | 1700 |
| 80 | Sal | 145 | 2500 |
| 90 | Exe | 100 | 1700 |
| 100 | Fin | 108 | 1700 |
| 110 | Acc | 205 | 1700 |
+---------------+-----------------+------------+-------------+