[MySQL] Note(3) IN, BETWEEN, LIKE, %, _

Last Updated on 2021-05-15 by Clay

I briefly mentioned that the WHERE instruction can be used as an IF conditional judgment. Today, I will continue to record how to use WHERE.

Some related instruction records are as follows:

• IN
• BETWEEN
• LIKE
• %, _: wildcard

Basic use of WHERE instruction

WHERE instruction can use the following symbols to judge:

We can also use "logical operators" to string together different judgments.

We still use the world database in MySQL to demo:

select
    Name, SurfaceArea
    from country
    where SurfaceArea > 10000 and SurfaceArea < 20000
    order by SurfaceArea desc;

Output:

According to the above instructions, we can query the countries where the surface area is between 10000 and 20000.

IN

IN is an instruction that can judge multiple conditions in WHERE instruction, so when we want to judge whether a specific value in the column or table, we don't have to write a lot of ORs.

For example:

select
    Name, SurfaceArea
    from country
    where SurfaceArea in (10400, 11000, 8000);

Output:

We can determine that countries with attribute SurfaceArea as 10400.00 and 11000.00 exist, but with 8000 do not exist.

BETWEEN

BETWEEN can determine whether the value of the field we are looking for lies in a certain interval, and only needs to connect the two values of BETWEEN with AND.

select
    Name, SurfaceArea
    from country
    where SurfaceArea
    between 10000 and 20000
    order by SurfaceArea desc;

Output:

We can use the BETWEEN instruction to achieve the same result as the above WHERE judgment formula.

LIKE

LIKE instruction is commonly used to match characters, usually with characters such as % and _.

• %: Unlimited character length matching
• _: Only one character length match

For example:

select
    Name
    from country
    where Name
    like '%gol%';

Output:

Two such country names will be found. Because "%xxx%" refers to whether there is a "xxx" in the matching name, there can be "characters of any length" before and after it.

If we modify the command:

select
    Name
    from country
    where Name
    like '%gol_';

Output:

This time, since our command only matches the number of characters in front of "gol", it doesn't matter, but the latter can only accept "one character", so the result we found this time only returns Angola.