Detailed explanation of using MySQL where

1. Introduction

When we need to obtain a specific subset of database table data, we can use the where clause to specify search conditions for filtering. The where clause has a wide range of usage scenarios and is a key point in MySQL statements that you need to master. All the functions implemented by where can be implemented outside of MySQL, but filtering queries directly in MySQL is faster and saves network transmission overhead.

2. Main text

First, prepare a User table. The DDL and table data are as follows and can be copied and used directly.

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 'Primary key',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'User name',
  `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;
 
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '李子８', 18, 1);
INSERT INTO `user` VALUES (2, '张三', 22, 1);
INSERT INTO `user` VALUES (3, '李四', 38, 1);
INSERT INTO `user` VALUES (4, '王五', 25, 1);
INSERT INTO `user` VALUES (5, '六麻子', 13, 0);
INSERT INTO `user` VALUES (6, '田七', 37, 1);
INSERT INTO `user` VALUES (7, 'Thank you', 18, 1);
 
SET FOREIGN_KEY_CHECKS = 1;

The initial order of the data is as follows:

mysql> select * from user;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

2.1 Where Clause Position

The where clause comes after the from clause, for example:

mysql> select * from user where age=18;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 7 | Thank you | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
+----+--------+-----+-----+
3 rows in set (0.00 sec)

If you use order by, the where clause is placed before order by, for example:

mysql> select * from user where age = 18 order by name;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
| 7 | Thank you | 18 | 1 |
+----+--------+-----+-----+
3 rows in set (0.00 sec)

2.2 Operators

The where clause supports 8 operators, which are as follows:

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

2.2.1 Operator =

Operator = can be used to query data that matches exactly. Note that MySQL is not case sensitive by default.

need:
The query statement for data whose name is equal to Li Ziba is:

select * from user where name = '李子捌';

result:

+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)

Operator = If there are multiple matching data, all data that meet the conditions of the where clause will be returned. If you need to specify the sorting method, you can use order by to sort the data.

2.2.2 Operators <> and !=

These two operators achieve the same effect, both matching unequal data.

need:
The query statement for data where name is not equal to Li Zi Ba is:

select * from user where name <> '李子捌';
select * from user where name != '李子捌';

result:

+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
+----+--------+-----+-----+
7 rows in set (0.00 sec)

2.2.3 Operators <=, <, >=, >
These four operators are used to compare column data of numeric type, but if they are applied to text fields, MySQL can also perform the operations, but the returned results may not be the data you expect (in theory, no one will do this, but it will not report an error!)

need:
Query all users whose age is less than or equal to 20:

select * from user where age <= 20;

result:

+----+--------+-----+-----+

| id | name | age | sex |

+----+--------+-----+-----+

| 1 | Plum 8 | 18 | 1 |

| 5 | Liu Mazi | 13 | 0 |

| 7 | Thank you | 18 | 1 |

| 8 | Li Ziqi | 18 | 1 |

+----+--------+-----+-----+

4 rows in set (0.00 sec)

2.2.4 BETWEEN AND
BETWEEN AND is used to query the value between two numeric ranges. This range is two closed intervals, so it includes the starting value and the ending value. For example, BETWEEN 1 AND 100 includes data of 1 and 100.

need:
Query the user whose age is greater than or equal to 20 and less than or equal to 50:

select * from user where age between 20 and 50;

+----+------+-----+-----+

| id | name | age | sex |

+----+------+-----+-----+

| 2 | Zhang San | 22 | 1 |

| 3 | Li Si | 38 | 1 |

| 4 | Wang Wu | 25 | 1 |

| 6 | Tianqi | 37 | 1 |

+----+------+-----+-----+

4 rows in set (0.00 sec)

2.3 Null value

The null value means that it contains no data. It can be used to specify whether the column can contain null values ​​when creating a table. It should be noted that null is different from 0 of data value type and space of character type. The null value means no value.
Regarding the query of null value, MySQL provides a special where clause is null.

need:
Query the data statement with empty value for name:

select * from user where name is null;

result:

Empty set (0.00 sec)

Because there is no data in the user table with a null name value, no data is returned. If we need to query the data where the name column is not empty, how should we query it?

At this time we can use is not null

mysql> select * from user where name is not null;
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Plum 8 | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 5 | Liu Mazi | 13 | 0 |
| 6 | Tianqi | 37 | 1 |
| 7 | Thank you | 18 | 1 |
| 8 | Li Ziqi | 18 | 1 |
+----+--------+-----+-----+

This is the end of this article about the detailed use of MySQL where. For more relevant content on the use of MySQL where, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!