Detailed explanation of common usage of MySQL query conditions

This article uses examples to illustrate the common usage of MySQL query conditions. Share with you for your reference, the details are as follows:

condition

Use the where clause to filter the data in the table, and the rows with a true result will appear in the result set.

The syntax is as follows:

select * from table name where condition;

example:

select * from students where id=1;

Where supports multiple operators to process conditions

Comparison operator Logical operator Fuzzy query Range query Empty judgment

Comparison Operators

Equals: =
Greater than: >
Greater than or equal to: >=
Less than: <
Less than or equal to: <=
Not equal to: != or <>

Example 1: Query students whose ID is greater than 3

select * from students where id > 3;

Example 2: Query students whose ID is not greater than 4

select * from students where id <= 4;

Example 3: Query students whose name is 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 operators

and
or
not

Example 5: Query female classmates whose ID is greater than 3

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

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

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

Fuzzy query

like
% represents any number of characters
_ represents an arbitrary character

Example 7: Query students with the surname Huang

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

Example 8: Search for students whose surname is Huang and whose first name is one character

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

Example 9: Search for students with the surname Huang or Jing

select * from students where name like '黄%' or name like '%靖';

Range Query

in means in a non-contiguous range

Example 10: Query students whose ID is 1, 3, or 8

select * from students where id in(1,3,8);

between ... and ... means in a continuous range

Example 11: Query students whose IDs are 3 to 8

select * from students where id between 3 and 8;

Example 12: Query the boys whose IDs are 3 to 8

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

Empty judgment

Note: null is different from ''

Is null

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

select * from students where height is null;

Is not null

Example 14: Query students who have filled in their height

select * from students where height is not null;

Example 15: Query boys who have filled in their height

select * from students where height is not null and gender=1;

Priority

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

and is calculated before or. If both appear and you want to calculate or first, you need to use it in conjunction with ()

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.