How to use mysql where keyword

In mysql, the where keyword needs to be used together with the SELECT statement to specify query conditions, that is, conditionally query and return data from the data table; the syntax "SELECT {*|field column name} FROM Data table name WHERE query condition;".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL WHERE: Conditional query data

In MySQL, if you need to conditionally query data from a data table, you can use the WHERE keyword to specify query conditions.

The syntax format for using the WHERE keyword is as follows:

SELECT {*|字段列名} FROM 数据表名 WHERE 查询条件;

The query conditions can be:

• Query conditions with comparison operators and logical operators

• Query conditions with BETWEEN AND keyword

• Query conditions with IS NULL keyword

• Query conditions with IN keyword

• Query conditions with LIKE keyword

Single condition query Statement

Single condition refers to only one query condition after the WHERE keyword.

Example 1

Query the names of students with a height of 170cm in the tb_students_info data table. The SQL statement and running results are as follows.

mysql> SELECT name,height FROM tb_students_info
    -> WHERE height=170;
+-------+--------+
| name  | height |
+-------+--------+
| Susan |    170 |
+-------+--------+
1 row in set (0.17 sec)

As you can see, the value of the height field recorded in the query results is equal to 170. If there are no records matching the query conditions in the data table when querying based on the specified conditions, the system will prompt "Empty set (0.00sec)".

Example 2

Query the names of students younger than 22 in the tb_students_info data table. The SQL statement and running results are as follows.

mysql> SELECT name,age FROM tb_students_info
    -> WHERE age<22;
+------+------+
| name | age  |
+------+------+
| John |   21 |
+------+------+
1 row in set (0.05 sec)

It can be seen that the value of the age field of all records in the query results is less than 22 years old, and records greater than or equal to 22 years old are not returned.

Multi-condition query statements

There can be multiple query conditions after the WHERE keyword, which can make the query results more accurate. Multiple query conditions are separated by logical operators AND (&&), OR (||) or XOR.

• AND: Records will be queried only when they meet all query conditions.

• OR: Records will be queried only when they meet any query condition.

• XOR: The record will be queried only when it meets one of the conditions and does not meet the other condition.

Example 3

Query the student information whose age is greater than 21 and height is greater than or equal to 175 in the tb_students_info table. The SQL statement and running results are as follows .

mysql> SELECT name,age,height FROM tb_students_info 
    -> WHERE age>21 AND height>=175;
+--------+------+--------+
| name   | age  | height |
+--------+------+--------+
| Henry  |   23 |    185 |
| Jim    |   24 |    175 |
| Thomas |   22 |    178 |
+--------+------+--------+
3 rows in set (0.00 sec)

You can see that the age field of all records in the query results is greater than 21 and the height field is greater than or equal to 175.

Example 4

Query the student information in the tb_students_info table whose age is greater than 21, or whose height is greater than or equal to 175. The SQL statement and running results are as follows.

mysql> SELECT name,age,height FROM tb_students_info 
    -> WHERE age>21 OR height>=175;
+--------+------+--------+
| name   | age  | height |
+--------+------+--------+
| Dany   |   25 |    160 |
| Green  |   23 |    158 |
| Henry  |   23 |    185 |
| Jane   |   22 |    162 |
| Jim    |   24 |    175 |
| Lily   |   22 |    165 |
| Susan  |   23 |    170 |
| Thomas |   22 |    178 |
| Tom    |   23 |    165 |
+--------+------+--------+
9 rows in set (0.00 sec)

You can see that the age field of all records in the query results is greater than 21 or the height field is greater than or equal to 175.

Example 5

Query in the tb_students_info table the student information whose age is greater than 21, and the height is less than 175, and the student information whose age is less than 21, and the height is greater than or equal to 175, The SQL statements and running results are as follows.

mysql> SELECT name,age,height FROM tb_students_info 
    -> WHERE age>21 XOR height>=175;
+-------+------+--------+
| name  | age  | height |
+-------+------+--------+
| Dany  |   25 |    160 |
| Green |   23 |    158 |
| Jane  |   22 |    162 |
| Lily  |   22 |    165 |
| Susan |   23 |    170 |
| Tom   |   23 |    165 |
+-------+------+--------+
7 rows in set (0.00 sec)

You can see that the age field of all records in the query results is greater than 21 and the height field is less than 175. There are no records in the tb_students_info data table whose age field is less than 21 and whose height field is greater than or equal to 175.

OR, AND and XOR can be used together, but pay attention to the precedence of the operators when using them. For information about the priority of operators in MySQL, you can read the section "Priority of MySQL Operators".

The more query conditions there are, the fewer records will be queried. Because the more conditions are set, the more restrictions the query statement has, and the fewer records that can meet all conditions. In order to make the queried records exactly what you want, you can set the query conditions in the WHERE statement to be more specific.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to use mysql where keyword. For more information, please follow other related articles on the PHP Chinese website!