Filter the data

The student_info and student_score tables have very few records, but a table can store tens or even hundreds of millions of records. And we are usually not interested in all records, just those that meet certain criteria. For example, we only want to query the basic information of the student whose name is Fan Jian, or what are the students of the School of Computer science. These conditions are also called search conditions or filtering conditions. When a certain record meets the search conditions, it will be put into the result set.

Simple search criteria

Student_info = student_info; student_info = student_info; student_info = student_info; student_info = student_info

mysql> SELECT number, name, id_number, major FROM student_info WHERE name = 'Fan Jian'; +----------+--------+--------------------+-----------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180105 | Fan Jian | 181048199308156368 | | aircraft design +----------+--------+--------------------+-----------------+ 1 rowin set(0.01 SEC) mysql >Copy the code

In this example, the search condition is name = ‘Fan Jian’, that is, the number, name, ID_number, and major fields of the record can only be added to the result set if the value of the name column is’ Fan Jian ‘. Search conditions like name = ‘Fan Jian’ are called exact matches, and = is called the conditional operator. MySQL has some simple conditional operators:

The operator The sample describe
= a = b Is equal to the
<>or! = a <> b Is not equal to
< a < b Less than
< = a <= b No greater than
> a > b Is greater than
> = a >= b Not less than
BETWEEN a BETWEEN b AND c B <= a <= c
NOT BETWEEN a NOT BETWEEN b AND c B <= A <= c

We want to query information about students whose student id is greater than 20180103

mysql> SELECT number, name, id_number, major FROM student_info WHERE number > 20180103; +----------+-----------+--------------------+-----------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180104 | Shi Zhenxiang | | 141992199701078600 | software engineering | 181048199308156368 | 20180105 | Fan Jian | aircraft design | | 20180106 | Zhu Yiqun | | 197995199501078445 | electronic information +----------+-----------+--------------------+-----------------+ 3 rowsin set(0.01 SEC) mysql >Copy the code

Select * from student where the major is not computer Science and engineering.

mysql> SELECT number, name, id_number, major FROM student_info WHERE major ! ='Computer Science and Engineering'; +----------+-----------+--------------------+-----------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180103 | Fan Tong | | 17156319980116959 | x software engineering | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | | 181048199308156368 aircraft design | | 20180106 | Zhu Yiqun | Electronic information | 197995199501078445 | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 4 rowsin set(0.00 SEC) mysql >Copy the code

BETWEEN… AND … Select * from ‘20180102’ to ‘20180104’; select * from ‘20180102’ to ‘20180104’;

mysql> SELECT number, name, id_number, major FROM student_info WHERE number BETWEEN 20180102 AND 20180104; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180102 | Du Qiyan | 151008199801178529 | Computer science and engineering | | 20180103 | Fan Tong 17156319980116959 x | | software engineering | | 20180104 | Shi Zhenxiang | | 141992199701078600 | software engineering +----------+-----------+--------------------+--------------------------+ 3 rowsin set(0.00 SEC) mysql >Copy the code

If you want to query data records outside the specified range, you can use NOT BETWEEN… AND … For example:

mysql> SELECT number, name, id_number, major FROM student_info WHERE number NOT BETWEEN 20180102 AND 20180104; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180101 | Du Ziteng | 158177199901044792 | Computer science and engineering | | 20180105 | Fan Jian | | 181048199308156368 aircraft design | | 20180106 | Zhu Yiqun | | 197995199501078445 | electronic information +----------+-----------+--------------------+--------------------------+ 3 rowsin set(0.00 SEC) mysql >Copy the code

In this way, you can find all the information of students whose student id is not 20180102~20180104.

Multiple values match

Sometimes the specified match is not a single value, but a list, and a match is successful if it matches one of the items IN the list. IN this case, the IN operator can be used:

The operator The sample describe
IN a IN (b1, b2, ...) A is b1, B2… One of them
NOT IN a NOT IN (b1, b2, ...) A is not b1, B2… Any one of the

If you want to query the information of software engineering and aircraft design students, you can write:

mysql> SELECT number, name, id_number, major FROM student_info WHERE major IN ('Software Engineering'.'Aircraft Design'); +----------+-----------+--------------------+-----------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180103 | Fan Tong | | 17156319980116959 | x software engineering | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | 181048199308156368 | | aircraft design +----------+-----------+--------------------+-----------------+ 3 rowsin set(0.01 SEC) mysql >Copy the code

If you want to query information about students from either of these two majors, you can write:

mysql> SELECT number, name, id_number, major FROM student_info WHERE major NOT IN ('Software Engineering'.'Aircraft Design'); +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180101 | Du Ziteng | 158177199901044792 | Computer science and engineering | | 20180102 | Du Qiyan | 151008199801178529 | computer science and engineering | | 20180106 | Zhu Yiqun | | 197995199501078445 | electronic information +----------+-----------+--------------------+--------------------------+ 3 rowsin set(0.00 SEC) mysql >Copy the code

NULLValue to check

As we mentioned earlier, NULL means that there is no value, which means that you do not know what data should be filled into the column. Instead of using the = operator to determine whether a column is NULL, you need to use the operator to determine whether a column is NULL:

The operator The sample describe
IS NULL a IS NULL A value isNULL
IS NOT NULL a IS NOT NULL The value of A is notNULL

Student_info = student_info; student_info = student_info;

mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NULL;
Empty set(0.00 SEC) mysql >Copy the code

The name column of all records is not NULL, so the result is NULL.

mysql> SELECT number, name, id_number, major FROM student_info WHERE name IS NOT NULL; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180101 | Du Ziteng | 158177199901044792 | Computer science and engineering | | 20180102 | Du Qiyan | 151008199801178529 | computer science and engineering | | 20180103 | Fan Tong 17156319980116959 x | | software engineering | | 20180104 | Shi Zhenxiang | 141992199701078600 | software engineering | | 20180105 | Fan Jian | | 181048199308156368 aircraft design | | 20180106 | Zhu Yiqun | 197995199501078445 Electronic information | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 6 rowsin set(0.00 SEC) mysql >Copy the code

The name column is not NULL.

Again, you can’t compare a NULL value directly with a normal operator. You must use IS NULL or IS NOT NULL!

A query with multiple search criteria

All of the above are queries that specify a single search criterion. We can also specify multiple search criteria in a single query.

The AND operator

When given multiple search criteria, we sometimes need a record to be added to the result set only if all search criteria are met. In this case, we can use the AND operator to join multiple search criteria. For example, if you want to find a score greater than 75 for ‘sow postpartum care’ from the student_score table, you can write:

mysql> SELECT * FROM student_score WHERE subject = 'Post-natal care for sows'AND score > 75; +----------+-----------------------+-------+ | number | subject | score | +----------+-----------------------+-------+ | 20180101 | sow postpartum care | 78 | | 20180102 | sow postpartum care | 100 | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 2 rowsin set(0.00 SEC) mysql >Copy the code

Subject = ‘postpartum care of sows’ AND score > 75 are two search conditions, AND we use the AND operator to connect these two search conditions to indicate that only records that meet both conditions can be added to the result set.

The OR operator

When given multiple search criteria, we sometimes need a record that can be added to the result set as soon as a search criteria is met. In this case, we can use the OR operator to join multiple search criteria. Student_score = > 95 or < 55

mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55; +----------+-----------------------------+-------+ | number | subject | score | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 100 | 20180102 | sow postpartum nursing theory of | 20180102 | | 98 war preparedness for saddam hussein | | 20180104 | theory of saddam hussein's war 46 | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rowsin set(0.00 SEC) mysql >Copy the code

A more complex combination of search criteria

If we need to specify a lot of search criteria in a query, let’s say we want to find ‘Preparation for Saddam Hussein’s war’ from the student_score table, with a score greater than 95 or less than 55, we might write:

mysql> SELECT * FROM student_score WHERE score > 95 OR score < 55 AND subject = 'On Saddam's War Preparations'; +----------+-----------------------------+-------+ | number | subject | score | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | 100 | 20180102 | sow postpartum nursing theory of | 20180102 | | 98 war preparedness for saddam hussein | | 20180104 | theory of saddam hussein's war 46 | | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + 3 rowsin set(0.00 SEC) mysql >Copy the code

Why is there still a record of the ‘sow postnatal care’ course in the results? By default, the: AND operator has higher precedence than the OR operator, which means that the search criteria on both sides of the AND operator are executed first. so

score > 95 OR score < 55 AND subject = 'On Saddam's War Preparations'
Copy the code

It can be regarded as if one of the following two conditions is true:

  1. score > 95

  2. Score < 55 AND subject = 'On Saddam hussein's war preparations'

Since the result set subject is the record of ‘post-natal care of sows’ with a score value of 100, which meets the first condition, the whole record will be added to the result set. To avoid this embarrassment, it is best to use parentheses () to explicitly specify the order in which search criteria are executed in a query. For example, the above example could be written as follows:

mysql> SELECT * FROM student_score WHERE (score > 95 OR score < 55) AND subject = 'On Saddam's War Preparations'; +----------+-----------------------------+-------+ | number | subject | score | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + | | theory of saddam hussein's 20180102 war | 98 | | 20180104 | theory of saddam hussein's war 46 | | +----------+-----------------------------+-------+ 2 rowsin set(0.00 SEC) mysql >Copy the code

The wildcard

Sometimes we can’t describe exactly what we want to query. For example, we just want to see the information of students with the surname ‘Du’, but can’t accurately describe the complete name of the students with the surname ‘Du’. We call this kind of query fuzzy query. MySQL uses the following two operators to support fuzzy queries:

The operator The sample describe
LIKE a LIKE b A match b
NOT LIKE a NOT LIKE b A does not match B

Since we can’t fully describe the information we’re looking for, we replace the vague information with a symbol called a wildcard. MySQL supports the following two wildcards:

  1. % : indicates any character string.

    Student_info = student_info; student_info = student_info;

    mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE 'd %'; +----------+-----------+--------------------+--------------------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180101 | Du Ziteng | 158177199901044792 | Computer science and engineering | | 20180102 | Du Qiyan | 151008199801178529 | | computer science and engineering +----------+-----------+--------------------+--------------------------+ 2 rowsin set(0.00 SEC) mysql >Copy the code

    Or if we only know that the student’s name contains the Chinese character “xiang”, we can look it up like this:

    mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE '% fragrance %'; +----------+-----------+--------------------+--------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180104 | Shi Zhenxiang | | 141992199701078600 | software engineering +----------+-----------+--------------------+--------------+ 1 rowin set(0.00 SEC) mysql >Copy the code

  2. _ : indicates any character.

    Sometimes we know how many characters are in the string we are looking for, and when % is too wide to match, we can use _ as the wildcard. Like Alipay’s Universal Fuka, one universal Fuka can only represent any one fuka (i.e., it cannot represent multiple Fukas).

    Select * from ‘fan’; select * from ‘fan’;

    mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE Fan '_'; +----------+--------+--------------------+-----------------+ | number | name | id_number | major | + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | 20180103 | Fan Tong | | 17156319980116959 | x software engineering | 20180105 | Fan Jian | 181048199308156368 | design | + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 2 rowsin set(0.00 SEC) mysql >Copy the code

    The following query does not find anything:

    mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE 'd _';
Empty set(0.00 SEC) mysql >Copy the code

    This is because an _ can only stand for one character (% is any string), and student_info doesn’t have a ‘du’ and a two-character name, so it’s useless.

Escape wildcards

What if we match a string that contains a common character ‘%’ or ‘_’? How do we tell if it is a wildcard or a common character?

Answer: If the matching string requires the ordinary character ‘%’ or ‘_’, they need to be preceded by a backslash \ to distinguish them from wildcards:

  • '\ %'Stands for ordinary charactersThe '%'
  • \ '_'Stands for ordinary characters'_'Like this:
mysql> SELECT number, name, id_number, major FROM student_info WHERE name LIKE 'van \ _';
Empty set(0.00 SEC) mysql >Copy the code

Because there is no student named Fan in the student_info table, the query result is empty.

Matters needing attention

When using wildcards, note that wildcards cannot represent NULL. To match NULL, use IS NULL or IS NOT NULL!

Small volumes

This series of columns is an introduction to MySQL. For more information, see the links to how MySQL works: Finding ways to Understand MySQL. The content of the volume is mainly from the perspective of small white, using popular language to explain some core concepts about MySQL advanced, such as record, index, page, table space, query optimization, transaction and lock, a total of about 300,000 or 400,000 words, with hundreds of original illustrations. Mainly want to reduce the ordinary programmer learning MySQL advanced difficulty, let the learning curve a little smoother ~