“This is the 13th day of my participation in the Gwen Challenge in November. Check out the details: The Last Gwen Challenge in 2021”

1, the introduction of

When we need to fetch a specific subset of database table data, we can use the WHERE clause to specify search criteria for filtering. The WHERE clause can be used in various scenarios, which is a key knowledge in MySQL statements. All of the functionality implemented by WHERE can be implemented outside of MySQL, but filtering queries directly in MySQL is faster and saves on network transport overhead.

2, the body

First prepare a User table, DDL and table data as shown below, can be directly copied use.

SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`;  CREATE TABLE 'user' (' id 'bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键', 'name' varchar(255) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT 'username ', 'age' int(11) NOT NULL COMMENT 'age ',' sex 'smallint(6) NOT NULL COMMENT' gender ', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 8 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - the Records of the user -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- INSERT INTO ` user ` VALUES (1, 'plum', 18, 1); INSERT INTO 'user' VALUES (2, '3 ', 22, 1); INSERT INTO 'user' VALUES (3, '55 ', 1); INSERT INTO 'user' VALUES (4, '王五', 25, 1); INSERT INTO 'user' VALUES (5, '6 ', 13, 0); INSERT INTO 'user' VALUES (6, '7 ', 37, 1); INSERT INTO 'user' VALUES (7, '80 ', 1); SET FOREIGN_KEY_CHECKS = 1;Copy the code

The initial order of the data is as follows:

mysql> select * from user; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 2 threes | | 1 | | | 3 |, dick, and harry 38 | 1 | | | 4 fifty and 25 | | 1 | | 5 6 pitted | | | | 0 13 | | 6 notoginseng 37 | 1 | | | | 7 honoraria 18 | 1 | | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 7 rows in the set (0.00 SEC)Copy the code

2.1 Where clause position

  • The WHERE clause follows from, as in:
mysql> select * from user where age=18; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 7 honoraria | | 1 18 | | | 8 plums and pure 18 | | | + 1 - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code
  • If order by is used, the WHERE clause precedes order by, as in:
mysql> select * from user where age = 18 order by name; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 8 plums and pure 18 | | 7 | 1 | | honoraria 18 | | | + 1 - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

2.2 the operator

The WHERE clause supports eight operators, as follows:

The operator Operator description
= Is equal to the
<> Is not equal to
! = Is not equal to
< Less than
< = Less than or equal to
> Is greater than
> = Greater than or equal to
BETWEEN AND The interval BETWEEN two values like BETWEEN 1 AND 100

Next, use the operators in the table to perform where subqueries one by one.

2.2.1 Operator =

The = operator can be used to query exactly matched data. Note that MySQL is case-insensitive by default.

Requirements:

Query data where name equals plum eight

Statement:

Select * from user where name = 'eight ';Copy the code

Results:

+, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum | | 1 | 18 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

Operator = If there are more than one matching data, then all the data that match the CONDITIONS of the WHERE clause will be returned. If you need to specify a sort method, you can combine the order by to sort the data.

2.2.2 Operators <> and! =

Both operators achieve the same effect, matching unequal data.

Requirements:

Query data where name is not equal to eight

Statement:

Select * from user where name <> 'eight '; select * from user where name ! = 'Leo eight ';Copy the code

Results:

+, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | | | 3 |, dick, and harry 38 | 1 | | | 4 fifty and 25 | | 1 | | 5 6 pitted | | | | 0 13 | | 6 notoginseng 37 | 1 | | | | 7 honoraria 18 | 1 | | | | 8 plums and pure 18 | 1 | | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 7 rows in the set (0.00 SEC)Copy the code

2.2.3 Operators <=, <, >=, >

These four operators are used to compare column data of numeric type, but if applied to a text field, MySQL can perform this as well, but it may not return the data you expected (theoretically no one would play this way, but it does!).

Requirements:

Query all users whose age is younger than or equal to 20

Statement:

 select * from user where age <= 20;
Copy the code

Results:

+, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | | 1 | six pitted 13 | | | | 5 7 0 | | | honoraria 18 | 1 | | | | 8 plums and pure 18 | | | + 1 - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

2.2.4 BETWEEN the AND

BETWEEN AND is used to query the value BETWEEN two numeric ranges, which are two closed ranges AND therefore contain the start AND end values. For example, BETWEEN 1 AND 100 contains data from 1 AND 100.

Requirements:

Query the users whose age is greater than 20 and smaller than 50

Statement:

select * from user where age between 20 and 50;
Copy the code
+ - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | | | 3 |, dick, and harry 38 | 1 | | 4 | fifty and 25 | 1 | | | | 6 notoginseng 37 | | | + 1 - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

2.3 null null

A null value means that no data is contained, and it can specify whether columns in a table can contain null values while building the table. Note that null is not the same as the data value type 0. The character type space is different. Null means there is no value.

For null queries, MySQL provides the special WHERE clause is NULL.

Requirements:

Query data whose name is null

Statement:

select * from user where name is null;
Copy the code

Results:

The Empty set (0.00 SEC)Copy the code

Because there is no data in the user table with a null value of name, no data is returned. What if we need to query for data whose name column is not null?

In this case we can use is not null

mysql> select * from user where name is not null; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 2 threes | | 1 | | | 3 |, dick, and harry 38 | 1 | | | 4 fifty and 25 | | 1 | | 5 6 pitted | | | | 0 13 | | 6 notoginseng 37 | 1 | | | | 7 honoraria 18 | 1 | | | | 8 plums and pure 18 | | 1 | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 8 rows in the set (0.00 SEC)Copy the code