where
# where
根据给定条件匹配过滤结果。
mysql> select * from student where name='Jim';
+--------+------+------+---------+
| stu_id | name | age | address |
+--------+------+------+---------+
| 1 | Jim | 20 | Hubei |
+--------+------+------+---------+
mysql> select * from student where age>=19;
+--------+------+------+---------+
| stu_id | name | age | address |
+--------+------+------+---------+
| 1 | Jim | 20 | Hubei |
| 2 | Tom | 19 | Hubei |
+--------+------+------+---------+
mysql> select * from student where age>18 and age<=20;
+--------+------+------+---------+
| stu_id | name | age | address |
+--------+------+------+---------+
| 1 | Jim | 20 | Hubei |
| 2 | Tom | 19 | Hubei |
+--------+------+------+---------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# where .. in()
mysql> select name,address from student where address in('Hubei');
+------+---------+
| name | address |
+------+---------+
| Jim | Hubei |
| Tom | Hubei |
+------+---------+
1
2
3
4
5
6
7
2
3
4
5
6
7
# whree .. not in()
mysql> select name,address from student where address not in('Hubei');
+------+----------+
| name | address |
+------+----------+
| Anna | Shanghai |
+------+----------+
1
2
3
4
5
6
2
3
4
5
6
# where .. between and
mysql> select name,age from student where age between 18 and 20;
+------+------+
| name | age |
+------+------+
| Jim | 20 |
| Tom | 19 |
| Anna | 18 |
+------+------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
包含 18 和 20
# where .. not between and
mysql> select name,age from student where age not between 18 and 20;
Empty set (0.00 sec)
1
2
2
# where .. is null
mysql> select * from student where age is null;
+--------+------+------+---------+
| stu_id | name | age | address |
+--------+------+------+---------+
| 4 | Emma | NULL | Beijing |
+--------+------+------+---------+
1
2
3
4
5
6
2
3
4
5
6
# where .. is not null
mysql> select * from student where age is not null;
+--------+------+------+----------+
| stu_id | name | age | address |
+--------+------+------+----------+
| 1 | Jim | 20 | Hubei |
| 2 | Tom | 19 | Hubei |
| 3 | Anna | 18 | Shanghai |
+--------+------+------+----------+
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
编辑 (opens new window)
上次更新: 2022/09/26, 16:55:15