MySQL knowledge learning-eighth day: use wildcards to filter

Preface

Today is the eighth day, the content is gradually becoming difficult, come on~

LIKE operator

Today our title mentioned a new word-wildcard, so let me explain what a wildcard is

Wildcard (wildcard) A special character used to match part of a value

Introduce a basic concept-search mode

Search pattern (search pattern) is a search condition composed of literal values, wildcards, or a combination of both.

So what is an operator?

Insert picture description here


The above concepts are the best to understand, it doesn’t matter if you don’t understand, as long as you know how to use it.

In order to use wildcards for filtering, we have to introduce the LIKE operator. In order to use wildcards in the search clause, the LIKE operator must be used. LIKE instructs MySQL, the following search pattern uses wildcard matching instead of direct equal matching for comparison

Before that, we created the following table (right click grade-design table-add fields)

Insert picture description here

Percent sign wildcard (%)

% Means that any character appears any number of times. For example, we look at the following example. In

Insert picture description here


this example, we use the search mode "%jing", which means to retrieve all the values ​​in the address column where the last letter is "jing", such as "nanjing", "beijing" will If it is retrieved, other results will not be retrieved. Although "jingxi" also contains "jing" but it will not be retrieved. This is determined by the search mode. "%" is equivalent to any character or string, so no matter how many characters there are before "jing", it can be retrieved. Search, but there can be no after "jing".

So the question is, what should I do if I want to search for "jingxi"?

Insert picture description here

We just put the wildcards behind, wildcards can be used anywhere in the search pattern, and multiple wildcards can be used. It is also possible to put it in the middle,

Insert picture description here


The first three search modes can retrieve "jing", try it yourself~

Pay special attention to spaces during the matching process , such as the first search mode, that is "%jing", if there is a record with a value "nanjing "(that is, there is a space at the end), it will not be matched. Just in case, we can add a% at the end of the search pattern, but this method is not very good (because it will match other redundant records). The best way is to remove the leading and trailing spaces. Later we will learn how to remove the first and the last. Spaces.

Insert picture description here

Summarized as follows

1.% can be located at any position in the search pattern and can appear any number of times;
2.% represents 0, 1 or more characters at a given position in the search pattern;
3.% pay attention to the leading and trailing spaces when matching;
4 .% Does not match a null value (NULL)

Underscore wildcard (_)

His usage is the same as% usage, but the underscore can only match a single character (cannot match 0, nor can it match multiple).
Let’s look at the following table and example

Insert picture description here
Insert picture description here

We see that the first record has not been retrieved because zheijing has two more characters than aging, but the underscore can only match one character .

Look at the following example again.

Insert picture description here


There is no search result above, that is to say, the underscore cannot match 0 characters, only one character, no more and no less .

Tips for using wildcards

Wildcards are useful, but they come at a cost.
The processing of wildcard searches generally takes longer than the other searches discussed earlier

A few things to keep in mind here

Insert picture description here

I feel a little down today~