MySQL must know that it will filter data.
1. Use the where clause
2.where clause operator
2.1 check a single value
2.2 mismatch check
2.3 range value check
2.4 Null check
Use the where clause
Database tables generally contain a large amount of data, and there is little need to retrieve all the rows in the table. A subset of table data is usually extracted according to the needs of a specific operation or report.
For example: find rows whose age is equal to 22 years old
MariaDB [test] > select age
-> from user
-> where age=22
+-+
| | age |
+-+
| | 22 |
+-+
1 row in set (0.00 sec)
Tip: when using both the order by and where clauses, order by should be placed after where.
Where clause operator
Equal to, not equal to, less than, less than or equal to, greater than, greater than or equal to, using between between two specified values
2.1 check a single value
MariaDB [test] > select id,age,province
-> from user
-> where province = 'Beijing'
+-- +
| | id | age | province | |
+-- +
| | 1 | 22 | Beijing |
| | 4 | 14 | Beijing |
| | 7 | 45 | Beijing |
| | 11 | 29 | Beijing |
| | 13 | 24 | Beijing |
+-- +
5 rows in set (0.01 sec)
2.2 mismatch check
MariaDB [test] > select id, age, province
-> from user
-> where age 22
+-- +
| | id | age | province | |
+-- +
| | 2 | 25 | Guangdong |
| | 3 | 56 | Tianjin |
| | 4 | 14 | Beijing |
| | 5 | 36 | Guangdong |
| | 6 | 68 | Hunan |
| | 7 | 45 | Beijing |
| | 8 | 17 | Hebei |
| | 9 | 33 | Tianjin |
| | 10 | 27 | Hunan |
| | 11 | 29 | Beijing |
| | 12 | 70 | Guangdong |
| | 13 | 24 | Beijing |
+-- +
12 rows in set (0.00 sec)
2.3 range value check
MariaDB [test] > select id,age,province
-> from user
-> where age between 25 and 33
+-- +
| | id | age | province | |
+-- +
| | 2 | 25 | Guangdong |
| | 9 | 33 | Tianjin |
| | 10 | 27 | Hunan |
| | 11 | 29 | Beijing |
+-- +
4 rows in set (0.00 sec)
2.4 Null check
Tip: the null value NULL (no value) is different from 0, empty string, or space.
MariaDB [test] > select id,age,province
-> from user
-> where age IS NULL
Empty set (0.00 sec)
