MySQL Knowledge Learning-Day Seven: Data Filtering


We learned the basic usage of the where statement yesterday, but we found that it only has one filter condition. What should we do if there are multiple conditions?

Today we will learn more about data filtering

AND operator

We mentioned AND when we introduced BEWTEEN yesterday, so today we will learn more about its usage.
First explain what an operator is

Operator: The key
word used to connect or change the clauses in the WHERE clause . Also known as logical operator

That is to say, the operator refers specifically to the keyword that joins or changes the clause in the where clause , that is to say, the operator is a special keyword, so sometimes we will say the keyword AND, but also the operator AND.

When we have multiple filter conditions, they can be separated by the AND operator, which is logically equivalent to "and". In fact, it is easy to understand, we will also use and when using other languages, the core is similar. For example, we look at the following example

Insert picture description here

The above example has two filter conditions, that is, to filter records whose math and english are both greater than 80, which must be met at the same time to be filtered out.

Similarly, we can also have three filter conditions, and the

Insert picture description here

other more filter conditions can be used similarly.

To summarize,
AND is a keyword used in the WHERE clause to indicate that rows that meet all given conditions are retrieved

OR operator

The or operator is logically equivalent to or, which means that one of the filter conditions can be filtered out.

Insert picture description here

In the above example, the column that satisfies math greater than 80 or English greater than 80 can be filtered out if one of them is satisfied, such as the record with ID 3, of course, it can also be filtered out if both are satisfied, such as ID 1 recording.

Similarly, multiple filter conditions can also use OR, as long as one of them is met, it can be filtered out

Insert picture description here

Summarize as follows
OR is a keyword used in the WHERE clause to indicate that rows that match any given condition are retrieved.

Calculation order

Earlier we mentioned AND and OR. Any number of AND and OR operators can be included in the WHERE clause, and the combination of the two is also allowed to perform complex and advanced filtering.

Since we mix the two of them, what should we calculate first? Just like the addition, subtraction, multiplication, and division we learned, there is always a sequence of calculations. Let’s take a look at the following example first.

Insert picture description here

According to the results, the meaning of the above code is to filter records whose math is greater than 80, or both English and Chinese are greater than 80, that is, SQL is executed first and then or is executed.

SQL (like most languages) prioritizes the AND operator before processing the OR operator.

In order to make the code easier to read, we use parentheses to group, SQL will give priority to the content in the parentheses (that is, the priority of parentheses in SQL is higher than AND and OR).

Insert picture description here

After adding the brackets, it becomes to filter records where both math and English are greater than 80, or Chinese is greater than 80. (Although the result is the same, because the settings of each record in this table are not good enough, you can try it yourself if you are interested)

Whenever you use a WHERE clause with AND and OR operators, you should use parentheses to clearly group the operators . Don't rely too much on the default calculation order.

IN operator

The IN operator is used to specify the range of conditions, and each condition in the range can be matched.

Insert picture description here

Note that IN is followed by a field similar to a collection , which is used to filter the records whose values ​​are in the collection, including the left and right endpoints. For example, the above example is to filter records whose math is 79 or 80.

Why is it similar to a set? Look at the following example. In

Insert picture description here

other words, the number in front can be greater than the number in the back, and the result will not be different. Note that if there is a record in the table whose math is 80, it will not be filtered out, because it is a set, not an interval, which means that only records with a math of 79 or 90 will be filtered

Insert picture description here

The above effect can also be achieved through the OR operator, so you might as well try it.

Now that OR is introduced, why should IN be introduced? Of course it makes sense

Insert picture description here

IN is a keyword used in the WHERE clause to specify the list of values ​​to be matched, and its function is equivalent to OR.

NOT operator

Yes, this actually also appears in other languages, indicating a negative.

The NOT operator in the WHERE clause has one and only one function, that is, it negates any conditions that follow it.

Look at the following example. The

Insert picture description here

above example means to filter records whose math is not 79 and 90. Note that records with null values ​​will not be filtered (regardless of whether it is reversed, records with null values ​​will not be filtered out, such as our sixth record)

Insert picture description here

Good night, I wish you all a happy June 1st~