1. Introduction
When using the where clause in MySQL to filter query data, multiple filter conditions are often needed to meet at the same time, or one of the multiple filter conditions is met. At this time, we can use operators to connect the where clauses.
The functions of several operators:
| Operator | effect |
|---|---|
| and | and, the conditions in the where clause need to be met at the same time |
| or | Or, just need to match one condition in multiple where clauses |
| in | Used to specify the scope of a where clause query |
| not | No, generally used with in, between and exists, to indicate inverse |
2. Text
First prepare a User table, DDL and table data are shown below, 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 '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; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'Li Ziba', 18, 1); INSERT INTO `user` VALUES (2, 'Zhang San', 22, 1); INSERT INTO `user` VALUES (3, 'Li Si', 38, 1); INSERT INTO `user` VALUES (4, 'Wang Wu', 25, 1); INSERT INTO `user` VALUES (5, 'Liu Mazi', 13, 0); INSERT INTO `user` VALUES (6, 'Tian Qi', 37, 1); INSERT INTO `user` VALUES (7, 'Thank you', 18, 0); SET FOREIGN_KEY_CHECKS = 1;
The initial order of data is as follows:
mysql> select * from user; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 1 | Li Ziba | 18 | 1 | | 2 | Zhang San | 22 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | | 5 | Six Mazi | 13 | 0 | | 6 | Tian Qi | 37 | 1 | | 7 | Thank you | 18 | 0 | +----+--------+-----+-----+ 7 rows in set (0.00 sec)
2.1 and operator
When the query needs to satisfy the conditions in the where clause at the same time, you can use the and operator, and the conditions are a relationship.
need:
Query age = 18 and gender is male (note: sex = 1 represents male)
Statement:
mysql> select * from user where age = 18 and sex =1;
result:
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Li Ziba | 18 | 1 |
+----+--------+-----+-----+
1 row in set (0.00 sec)
At this time, you can see that only users who meet age=18 and sex=1 are queryed. And so on, and can exist multiple times at the same time. For example, on the basis of the above, you need to query the name = Li Ziqi**, and you only need to add another and operator.
mysql> select * from user where age = 18 and sex =1 and name = 'Li Ziqi'; Empty set (0.00 sec)
2.2 or operator
Unlike and, or only needs to satisfy one of multiple where conditions, and does not need to be satisfied at the same time. The conditions are one or the relationship.
need:
Query age = 18 or users with male gender (note: sex = 1 represents male)
Statement:
mysql> select * from user where age = 18 or sex =1;
result:
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Li Ziba | 18 | 1 |
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
| 6 | Tian Qi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
6 rows in set (0.00 sec)
At this time, you can see that users who meet age=18 or sex=1 have been found. The same or operator can also act on multiple where clauses at the same time.
2.3 in operator
The in operator is used to specify the query scope of the where clause. It means the meaning of inclusion, and it can be implemented with multiple or operators.
need:
The query name is equal to the user information of Zhang San, Li Si, and Wang Wu.
Statement:
Use or operator
mysql> select * from user where name = 'Zhang San' or name = 'Li Si' or name = 'Wang Wu'; +----+------+-----+-----+ | id | name | age | sex | +----+------+-----+-----+ | 2 | Zhang San | 22 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | +----+------+-----+-----+ 3 rows in set (0.00 sec)
Use the in operator
mysql> select * from user where name in ('Zhang San', 'Li Si', 'Wang Wu');
+----+------+-----+-----+
| id | name | age | sex |
+----+------+-----+-----+
| 2 | Zhang San | 22 | 1 |
| 3 | Li Si | 38 | 1 |
| 4 | Wang Wu | 25 | 1 |
+----+------+-----+-----+
3 rows in set (0.00 sec)
The above requirements can be implemented through the or operator and the in operator, but the in operator is obvious that the SQL statement is concise according to it.
2.4 not operator
When we need to query that a certain value is not within a range or does not exist, we can use the not operator. The not operator is not used alone, it is often used with the in operator, like operator, between and exists, etc.
not in
need:
Querying the user information of Zhang San, Li Si, and Wang Wu does not mean that the user information is.
Statement:
mysql> select * from user where name not in ('Zhang San', 'Li Si', 'Wang Wu');
+----+--------+-----+-----+
| id | name | age | sex |
+----+--------+-----+-----+
| 1 | Li Ziba | 18 | 1 |
| 5 | Six Mazi | 13 | 0 |
| 6 | Tian Qi | 37 | 1 |
| 7 | Thank you | 18 | 0 |
+----+--------+-----+-----+
4 rows in set (0.00 sec)
not like
need:
Query users whose names do not start with plums
Statement:
mysql> select * from user where name not like 'Plum%'; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 2 | Zhang San | 22 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | | 5 | Six Mazi | 13 | 0 | | 6 | Tian Qi | 37 | 1 | | 7 | Thank you | 18 | 0 | +----+--------+-----+-----+ 6 rows in set (0.00 sec)
not between and
need:
Query age does not belong to users between 20 and 30
Statement:
mysql> select * from user where age not between 20 and 30; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 1 | Li Ziba | 18 | 1 | | 3 | Li Si | 38 | 1 | | 5 | Six Mazi | 13 | 0 | | 6 | Tian Qi | 37 | 1 | | 7 | Thank you | 18 | 0 | +----+--------+-----+-----+ 5 rows in set (0.00 sec)
not exists
The not exists table, which is consistent with the exists usage, is used to determine whether the result of the current where clause should be returned. not exists and exists act on a subquery, returning true and false to the superior;
Example syntax:
SELECT … FROM table WHERE EXISTS (subquery) SELECT … FROM table WHERE NOT EXISTS (subquery)
To demonstrate the effect, we create a simple order table order with the table creation statements and data as follows:
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for order -- ---------------------------- DROP TABLE IF EXISTS `order`; CREATE TABLE `order` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key', `number` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Order number', `user_id` bigint(20) NULL DEFAULT NULL COMMENT 'User ID', `price` decimal(10, 2) NULL DEFAULT NULL COMMENT 'Amount', `create_date` datetime(0) NULL DEFAULT NULL COMMENT 'Create date', PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of order -- ---------------------------- INSERT INTO `order` VALUES (1, 'DD-20211110-000001', 1, 250.00, '2021-11-10 22:37:19'); SET FOREIGN_KEY_CHECKS = 1;
Note: Since order is a keyword for MySQL, it is not recommended to directly name it order when creating tables. I named order here to tell how to solve this problem.
mysql> select * from `order`; +----+--------------------+---------+--------+---------------------+ | id | number | user_id | price | create_date | +----+--------------------+---------+--------+---------------------+ | 1 | DD-20211110-000001 | 1 | 250.00 | 2021-11-10 22:37:19 | +----+--------------------+---------+--------+---------------------+ 1 row in set (0.00 sec)
If you are careful, you can find that order is modified with `, so that MySQL will not parse it as a keyword. If MySQL is not added, an exception will be thrown.
Return to the topic, we use exists for querying at this time
need:
Query the user information that has been placed
Statement:
mysql> select * from user where exists(select id from `order` where user_id = ); +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 1 | Li Ziba | 18 | 1 | +----+--------+-----+-----+ 1 row in set (0.00 sec)
At this time, if we want to query the user information that has not placed an order, we just need to use not exists
mysql> select * from user where not exists (select id from `order` where user_id = ); +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 2 | Zhang San | 22 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | | 5 | Six Mazi | 13 | 0 | | 6 | Tian Qi | 37 | 1 | | 7 | Thank you | 18 | 0 | +----+--------+-----+-----+ 6 rows in set (0.00 sec)
2.5 Operator Order
There are several operators mentioned above, but in many cases multiple operators need to be used together. At this time, we need to pay attention to the order of operators.
For example, the following requirements:
In the query user table, users who are older than 20 years old or have male gender and whose name is not equal to Zhang San.
Statement:
mysql> select * from user where age > 20 or sex = 1 and name != 'Zhang San'; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 1 | Li Ziba | 18 | 1 | | 2 | Zhang San | 22 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | | 6 | Tian Qi | 37 | 1 | +----+--------+-----+-----+ 5 rows in set (0.00 sec)
At this time, I found that the return result of the query actually contained Zhang San. This is because the priority of and is higher than or. In the SQL parser at the bottom of MySQL, the above SQL parser parses the above SQL into sex = 1 and name != 'Zhang San' or age > 20; because Zhang San satisfies age > 20, it was also queryed. To solve this problem, just use brackets to enclose the or statement.
mysql> select * from user where (age > 20 or sex = 1) and name != 'Zhang San'; +----+--------+-----+-----+ | id | name | age | sex | +----+--------+-----+-----+ | 1 | Li Ziba | 18 | 1 | | 3 | Li Si | 38 | 1 | | 4 | Wang Wu | 25 | 1 | | 6 | Tian Qi | 37 | 1 | +----+--------+-----+-----+ 4 rows in set (0.00 sec)
At this time, Zhang San is no longer included in the returned data of the query.
Therefore, when we write SQL, we can develop the habit of using brackets and group operators through brackets to avoid the error risk caused by using the default order.
This is the article about the specific use of MySQL operators (and, or, in, not). For more related contents of MySQL operators, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!