当前位置：网站首页>MySQL advanced (VI) introduction to four common uses of fuzzy query

MySQL advanced (VI) introduction to four common uses of fuzzy query

2022-07-19 13:08:00 【InfoQ】

application mysql When performing query operation , Fuzzy query is an application scenario often encountered . This article mainly introduces
mysql
There are four common uses of fuzzy queries in . 

One 、%

Express arbitrarily 0 Characters or more . Matches characters of any type and length , In some cases, it's Chinese , Please use two percent signs （
%%
） Express . 
such as
SELECT * FROM [user] WHERE u_name LIKE '% 3、 ... and %';
It will put u_name by “ Zhang San ”,“ Zhang maosan ”、“ Three legged cat ”,“ Tang Sanzang ” Wait, there are “ 3、 ... and ” I've got all my records . 
in addition , If you need to find out u_name There is already “ 3、 ... and ” And then there is “ cat ” The record of , Please use and Conditions .
SELECT * FROM [user] WHERE u_name LIKE '% 3、 ... and %' AND u_name LIKE '% cat %';
If you use  
SELECT * FROM [user] WHERE u_name LIKE '% 3、 ... and % cat %' 
Although we can find out “ Three legged cat ”, But you can't find the right ones “ Zhang maosan ”. 

Two 、_

Represents any single character . Match a single arbitrary character , It is often used to limit the character length of an expression ： 
such as
SELECT * FROM [user] WHERE u_name LIKE '_ 3、 ... and _';
Just find out “ Tang Sanzang ” such u_name It's three words and the middle word is “ 3、 ... and ” Of ; 
Another example
SELECT * FROM [user] WHERE u_name LIKE ' 3、 ... and __';
Just find out “ Three legged cat ” such name It's three words and the first word is “ 3、 ... and ” Of ; 

3、 ... and 、[ ]

Represents one of the characters listed in brackets （ Like regular expressions ）. Specify a character 、 String or range , Require the matched object to be any of them . 
such as  
SELECT * FROM [user] WHERE u_name LIKE '[ Zhang liwang ] 3、 ... and ';
Will find out “ Zhang San ”、“ Li San ”、“ The king of the three ”（ instead of “ Zhang Li, Wang San ”）; 
Such as
 [ ] 
There's a series of characters in it （01234、abcde And so on. ） It can be abbreviated as “0-4”、“a-e” 
SELECT * FROM [user] WHERE u_name LIKE ' The old [1-9]';
Will find out “ The old 1”、“ The old 2”、……、“ The old 9”; 

Four 、[^ ]

Represents a single character that is not listed in parentheses . Its value and  []  identical , But it requires the matched object to be any character other than the specified character . 
such as  
SELECT * FROM [user] WHERE u_name LIKE '[^ Zhang liwang ] 3、 ... and ';
Will find out the last name “ Zhang ”、“ Li ”、“ king ” Of “ Zhao San ”、“ Sun San ” etc. ; 
SELECT * FROM [user] WHERE u_name LIKE ' The old [^1-4]'; 
Will exclude “ The old 1” To “ The old 4”, seek “ The old 5”、“ The old 6”、…… 

5、 ... and 、 Query content contains wildcard processing

Because of wildcards , Causes us to query for special characters “
%
”、“
_
”、“
[
” The statement cannot be implemented properly , And use special characters with “
[ ]
” You can query it normally . So let's write the following function ： 
function sqlencode(str) 

str=replace(str,"';","';';") 

str=replace(str,"[","[[]") ';

str=replace(str,"_","[_]") 

str=replace(str,"%","[%]") 

sqlencode=str 

end function

6、 ... and 、 Expanding reading  MySQL Data table with OR Multi criteria query of


OR
Keywords can be combined with multiple conditions for query . Use
OR
When a keyword ：
  • As long as one of these query conditions is met , Such records will be found out .
  • If you do not meet any of these query conditions , Such records will be excluded .

OR The basic syntax format of keywords is as follows ：
Conditional expression 1 OR  Conditional expression 2 [...OR  Conditional expression n]
OR You can connect two conditional expressions , Multiple can be used at the same time OR keyword , To connect more conditional expressions .

6.1  example 1

Use OR Keyword query employee In the table d_id by 1001, perhaps sex by ' male ' The record of .SELECT The code of the statement is as follows ：
SELECT * FROM employee WHERE d_id=1001 OR sex LIKE ' male ';
Can be d_id by 1001 The records of were queried , meanwhile ,sex by ' male ' The records of have also been queried .
This explanation , Use OR When a keyword , As long as any one of the multiple conditions is met , Can be found .

6.2  example 2

Use OR Keyword query employee The records in the table . The query conditions are num The value is (1,2,3) Collection , perhaps age For the range of 24～26, perhaps homeaddress The value of contains “ Xiamen City ”.SELECT The code of the statement is as follows ：
SELECT * FROM employee WHERE

         num IN(1,2,3) OR age BETWEEN 24 AND 26

         OR homeaddress LIK '% Xiamen City %';
Results show , As long as it meets this 3 Any condition in a conditional expression , Such records will be found out .

Be careful ️：
OR
You can talk to
AND
Use it together . When the two are used together ,AND than OR Calculate first .

6.3  example 3

Use OR and AND Keyword query employee The records in the table .SELECT The code of the statement is as follows ：
SELECT * FROM employee WHERE num IN(1,3,4) AND age=25 OR sex=' Woman ';
Results show , As long as meet num IN(1,3,4) AND age=25 Record or conform to sex=' Woman ' All the records are displayed .
If you change the order of conditions , take
SELECT
The statement becomes the following ：
SELECT * FROM employee WHERE sex=' Woman ' OR num IN(1,3,4) AND age=25;
Results show , As in the previous
SELECT
The execution result of the statement is the same .
This explanation ,
AND
The conditions before and after keywords are combined first , And then with
OR
Keyword conditions . in other words ,AND than OR Calculate first .

Be careful ️：
AND
and
OR
Keywords can connect conditional expressions . These conditional expressions can be used “
=
”、“
>
” Wait for the operator , You can also use
IN
、
BETWEEN AND
and
LIKE
Other key words , and ,
LIKE
Keywords can be used when matching strings “
%
” and “
_
” Equal wildcard .
原网站

版权声明
本文为[InfoQ]所创，转载请带上原文链接，感谢
https://yzsam.com/2022/200/202207170946465522.html

边栏推荐

猜你喜欢

随机推荐