What is the mysql conditional query statement?

In mysql, you can use the SELECT statement and the WHERE keyword to implement conditional queries. The implementation statement is "SELECT field name FROM data table WHERE query conditions;"; the SELECT statement is used to query data, and the WHERE keyword Used to specify query conditions.

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

In mysql, you can use the SELECT statement and WHERE keyword to implement conditional queries.

The SELECT statement can query data. Querying data refers to using different query methods to obtain different data from the database according to needs. It is the most frequently used and important operation.

The WHERE keyword is used to specify query conditions. If you need to conditionally query data from a data table, you can use the WHERE keyword.

The syntax format 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

• With IN key Word query conditions

• Query conditions with LIKE keyword

Single condition query statement

A 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.

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 What is the mysql conditional query statement?. For more information, please follow other related articles on the PHP Chinese website!