MySQL complete collapse: detailed explanation of query filter conditions

Overview

In actual business scenario applications, we often need to obtain and filter our target data based on business conditions. We call this process data query filtering. The various conditions used in the filtering process (such as date, time, user, and status) are necessary steps for us to obtain accurate data.

This is the only way to get the results we expect. So in this chapter we will learn various uses of query filtering conditions in MySQL.

Relational operations

A relational operation is a where statement followed by one or n conditions. Data that meets the conditions after where will be returned, otherwise data that does not meet the conditions will be filtered out. Operators refers to operators, which have the following cases:

Operators	illustrate
=	equal
<> or !=	Not equal to
>	Greater than
>=	Greater than or equal to
<	Less than
<=	Less than or equal to

The basic syntax format of relational operations is as follows:

 select cname1,cname2,... from tname where cname operators cval

Query the data that is strictly equal to the value behind it. If it is a non-value type, you need to add quotation marks to the value behind it, but if it is a value type, you do not need to add quotation marks to it.

The syntax format is as follows:

select cname1,cname2,... from tname where cname = cval;

mysql> select * from user2;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> select * from user2 where name='helen';
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
+----+-------+-----+----------+-----+
1 row in set

mysql> select * from user2 where age=21;
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+---------+-----+
2 rows in set

Not equal to (<>, !=)

There are two ways to write "not equal", one is <> and the other is !=, they have the same meaning and can be used interchangeably, but <> appears before !=, so looking at many previous examples, <> appears more frequently and is more portable, so it is recommended.

The purpose of "not equal" is to query the results that do not meet the conditions. The format is as follows:

select cname1,cname2,... from tname where cname <> cval;
or select cname1,cname2,... from tname where cname != cval;

mysql> select * from user2;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> select * from user2 where age<>20;
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+---------+-----+
2 rows in set

Greater than or less than (> <)

Generally used for comparison of numerical values, dates, and times, the format is as follows:

select cname1,cname2,... from tname where cname > cval;

select cname1,cname2,... from tname where cname < cval;

select cname1,cname2,... from tname where cname >= cval;

select cname1,cname2,... from tname where cname <= cval;

mysql> select * from user2 where age>20;
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+---------+-----+
2 rows in set

mysql> select * from user2 where age>=20;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

mysql> select * from user2 where age<21;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
+----+-------+-----+----------+-----+
1 row in set

mysql> select * from user2 where age<=21;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+----------+-----+
3 rows in set

Logical operations

Operators	illustrate
AND	Multiple conditions are met
OR	One of the multiple conditions is met
NOT	Negate the condition

AND

When multiple conditions are needed to filter data, this method is used. Each expression of and must be true, and the filtered data is what the user needs.

The following filters out data where both age and gender conditions are met. The syntax format is as follows:

select cname1,cname2,... from tname where cname1 operators cval1 and cname2 operators cval2

mysql> select * from user2;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+----------+-----+
4 rows in set

mysql> select * from user2 where age >20 and sex=1;
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+---------+-----+
2 rows in set

When multiple conditions are met, data filtering is performed as long as one condition is met.

The following condition filters out data older than 21 and younger than 21. The syntax format is as follows:

select cname1,cname2,... from tname where cname1 operators cval1 or cname2 operators cval2

mysql> select * from user2;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+----------+-----+
4 rows in set

mysql> select * from user2 where age>21 or age<21;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+----------+-----+
2 rows in set

NOT

Negate a certain satisfied condition, and the filtered data is what the user needs.

The following filters are not data whose age is greater than 20. The syntax format is as follows:

select cname1,cname2,... from tname where not(cname operators cval)

mysql> select * from user2;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+----------+-----+
4 rows in set

mysql> select * from user2 where not(age>20);
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 2 | helen | 20 | quanzhou | 0 |
+----+-------+-----+----------+-----+
1 row in set

Fuzzy matching

Just like the user table information table above (including name, age, address, and gender), when we want to query users whose names begin with s, we can use the like keyword, which is used to fuzzy match data.

The syntax format is as follows. Pattern can contain wildcards, of which there are two types. %: means matching any one or n characters; _: means matching any one character.

select cname1,cname2,... from tname where cname like pattern;

% Use

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name like 's%';
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+---------+-----+
2 rows in set

Use of _

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where name like 's_l';
+----+------+-----+---------+-----+
| id | name | age | address | sex |
+----+------+-----+---------+-----+
| 3 | sol | 21 | xiamen | 0 |
+----+------+-----+---------+-----+
1 row in set

Note

1. Do not overuse fuzzy matching wildcards. If other operators can achieve the same purpose, you should use other operators.

2. When performing fuzzy matching on a large table, try not to start with %, such as like '%username', as this will result in a table scan and slow efficiency. Try to specify the beginning part of the fuzzy search, such as like 'brand%', which will first locate the data starting with brand, which is much more efficient.

Range value check

BETWEEN AND (interval query)

The operator BETWEEN ... AND selects a range of data between two values. These values can be numbers, text or dates, and belong to a closed interval query.

The left side val1 and the right side val2 of and represent two critical values, which are equivalent to the mathematical formula [val1,val2]. Data belonging to these two intervals will be filtered out (>=val1 and <=val2), so the syntax format is as follows:

 selec cname1,cname2,... from tname where cname between val1 and val2;
 Equivalent to selec cname1,cname2,... from tname where cname >= val1 and cname <= val2;

Query data with age between [21,25]:

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where age between 21 and 25;
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+---------+-----+
3 rows in set

mysql> select * from user2 where age >= 21 and age <= 25;
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+---------+-----+
3 rows in set

IN (inclusive query)

According to the above data, if we want to find out the user data whose residence is in Fuzhou and Xiamen, we should use the IN operator, because the IN operator allows us to specify multiple values in the WHERE clause, and if one of these values is matched, the condition is met and the data is returned.

The syntax format is as follows. The value types in the list following in must be consistent or compatible, and wildcards are not supported:

select cname1,cname2,... from tname where cname in (val1,val2,...);

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where address in('fuzhou','xiamen');
+----+-------+-----+---------+-----+
| id | name | age | address | sex |
+----+-------+-----+---------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 3 | sol | 21 | xiamen | 0 |
+----+-------+-----+---------+-----+
2 rows in set

NOT IN (negate a containment query)

We have already learned about the users of not above. Let’s test it by negating the expression executed after not:

mysql> select * from user2;
+----+--------+-----+----------+-----+
| id | name | age | address | sex |
+----+--------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+----------+-----+
5 rows in set

mysql> select * from user2 where address not in('fuzhou','quanzhou','xiamen');
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 4 | weng | 33 | guizhou | 1 |
| 5 | selina | 25 | taiwang | 0 |
+----+--------+-----+---------+-----+
2 rows in set

Null value checking

IS NULL/IS NOT NULL

To determine whether it is empty, the syntax format is as follows. Please note that for data with a value of null, various comparison operators, like, between and, in, and not in queries do not work. Only is null can filter it out.

 select cname1,cname2,... from tname where cname is null;
 Or select cname1,cname2,... from tname where cname is not null;

mysql> select * from user2 where address is null;
+----+--------+-----+---------+-----+
| id | name | age | address | sex |
+----+--------+-----+---------+-----+
| 5 | selina | 25 | NULL | 0 |
+----+--------+-----+---------+-----+
1 row in set

mysql> select * from user2 where address is not null;
+----+-------+-----+----------+-----+
| id | name | age | address | sex |
+----+-------+-----+----------+-----+
| 1 | brand | 21 | fuzhou | 1 |
| 2 | helen | 20 | quanzhou | 0 |
| 3 | sol | 21 | xiamen | 0 |
| 4 | weng | 33 | guizhou | 1 |
+----+-------+-----+----------+-----+
4 rows in set

There is a keyword <=> that can include null value judgment, but it is rarely used now. If you are interested, you can check it out. I will not go into details here.

Summarize

1. The % in the like expression matches one to multiple arbitrary characters, and _ matches an arbitrary character

2. To query null values, you need to use IS NULL or IS NOT NULL. Other query operators are invalid for NULL values. Even though the % wildcard character can match anything, it cannot match data with the value NULL.

3. It is recommended that when creating a table, the table fields should not be set empty and a default value should be given to the fields.

4. MySQL supports using NOT to negate IN, BETWEEN, and EXISTS clauses.

This is the end of this article about the filter conditions for queries that completely break MySQL. For more information about filter conditions for MySQL queries, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in:

The difference and reasons between the MySQL query conditions not in and in
MySQL conditional query and or usage and priority example analysis
Detailed explanation of the problem of matching even when there is a space at the end of the string in the Mysql query condition
Detailed explanation of common usage of MySQL query conditions
Will the index be used in the MySQL query condition?
MySQL query method with multiple conditions

<<: Installation tutorial of docker in linux

>>: How to deploy stand-alone Pulsar and clustered Redis using Docker (development artifact)