Explanation of Common Uses of MySQL Query Criteria

This article illustrates the common usage of MySQL query conditions. Share it for your reference, as follows:

Condition

Use the where clause to filter the data in the table, and the row with the result of true will appear in the result set

The syntax is as follows:

select * from  Table name  where  Condition ;

Example:

select * from students where id=1;

Multiple operators are supported after where to process conditions

Comparison operator
Logical operator
Fuzzy query
Scope query
Empty judgment

Comparison operator

Equal to: =
Greater than: >
Greater than or equal to: > =
Less than: <
Less than or equal to: < =
Is not equal to:! = or < >

Example 1: Query students whose number is greater than 3

select * from students where id > 3;

Example 2: Query students with no more than 4

select * from students where id <= 4;

Example 3: Inquire about students whose names are not "Huang Rong"

select * from students where name != ' Huang Rong ';

Example 4: Query students who have not been deleted

select * from students where is_delete=0;

Logical operator

and
or
not

Example 5: Inquire about female students with number greater than 3

select * from students where id > 3 and gender=0;

Example 6: Query students whose number is less than 4 or has not been deleted

select * from students where id < 4 or is_delete=0;

Fuzzy query

like
% for any number of any characters
_ Represents 1 arbitrary character

Example 7: Inquiring about a student surnamed Huang

select * from students where name like ' Yellow %';

Example 8: Inquire about students whose surname is Huang and whose "first name" is one word

select * from students where name like ' Yellow _';

Example 9: Inquire about students named Huang or Jing

select * from students where id=1;

0

Scope query

in means within 1 discontinuous range

Example 10: Query students with number 1 or 3 or 8

select * from students where id=1;

1

between … and … means within a contiguous range

Example 11: Query students numbered 3 to 8

select * from students where id=1;

2

Example 12: Boys with query numbers 3 to 8

select * from students where (id between 3 and 8) and gender=1;

Empty judgment

Note: null is different from ''

Empty is null

Example 13: Query students who have not filled in their height

select * from students where id=1;

4

Judge non-empty is not null

Example 14: Query the students who filled in the height

select * from students where id=1;

5

Example 15: Inquire about boys who fill in their height

select * from students where id=1;

6

Priority

The order of priority from high to low is: parentheses, not, comparison operator, logical operator

and is calculated before or. If or occurs at the same time and you want to calculate or first, you need to use it in conjunction with ()

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.