Today is the eighth day, the content is gradually becoming difficult, come on~
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?
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)
Percent sign wildcard (%)
% Means that any character appears any number of times. For example, we look at the following example. In
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"?
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,
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.
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
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.
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
I feel a little down today~