MySQL operator (and, or, in, not)

“This is the 14th 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 using the WHERE clause in MySQL to filter the query data, it is often necessary to satisfy multiple filtering conditions at the same time, or satisfy one of multiple filtering conditions. In this case, we can use the operator to join the WHERE clause.

Several operators do the following:

The operator	role
and	Both conditions in the WHERE clause need to be satisfied
or	Or, you only need to match one condition in multiple WHERE clauses
in	Used to specify the scope of the WHERE clause query
not	No: it is used together with in, between, and exists

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, '1 ', 18, 0); 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 | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 7 rows in the set (0.00 SEC)Copy the code

2.1 and operator

The AND operator can be used when a query needs to satisfy both conditions in the WHERE clause. The and conditions are related to and.

Requirements:

Select * from user where age =18 and gender = male

Statement:

mysql> select * from user where age = 18 and sex =1;
Copy the code

Results:

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

Only users whose age=18 and sex=1 are queried. In this way, more than one and can exist at the same time, such as the above need to query the name = pruzidian, just need another and operator.

Mysql > select * from user where age = 18 and sex =1 and name = 'seven '; The Empty set (0.00 SEC)Copy the code

2.2 The OR operator

Unlike and, OR only needs to satisfy one of several WHERE conditions, not all of them.

---

Requirements:

Select * from user where age =18 or gender = male

Statement:

mysql> select * from user where age = 18 or sex =1;
Copy the code

Results:

+, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 2 threes | | 1 | | | 3 |, dick, and harry 38 | 1 | | | 4 fifty and 25 | 1 | | | | 6 notoginseng 37 | 1 | | | | 7 honoraria 18 | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 6 rows in Set (0.00 SEC)Copy the code

Users whose age=18 or sex=1 have been identified. The same OR operator can operate on multiple WHERE clauses simultaneously.

2.3 In operator

The IN operator is used to specify the query scope of the WHERE clause. It represents the meaning of inclusion, which can be implemented with multiple OR operators.

Requirements:

Query information about users whose name is equal to John, John, and John.

Statement:

Use the OR operator

Mysql > select * from user where name = 'zhang3' or name = 'wang4' or name = 'wang5 '; + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | | | 3 |, dick, and harry 38 | 1 | | 4 | fifty and | | 1 | + 25 - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

Use the in operator

Mysql > select * from user where name in (' mysql ', 'mysql ',' mysql '); + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- + | 2 | zhang SAN 22 | 1 | | | | 3 |, dick, and harry 38 | 1 | | 4 | fifty and | | 1 | + 25 - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- - + 3 rows in the set (0.00 SEC)Copy the code

The above requirements can be achieved with the OR and IN operators, but the IN operator is obvious to SQL statements based on brevity.

2.4 The NOT operator

The NOT operator is not used alone. It is often used in conjunction with the IN operator, like operator, between and exists operator, etc.

not in

Requirements:

Query user information whose name is not equal to the user name of John, John, or John.

Statement:

Mysql > select * from user where name not in (' mysql ', 'mysql ',' mysql '); +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | | 1 | six pitted 13 | | | | 5 6 0 | | | notoginseng 37 | 1 | | | | 7 honoraria 18 | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 4 rows in the set (0.00 SEC)Copy the code

not like

Requirements:

Query for users whose names do not begin with plums

Statement:

Mysql > select * from user where name not like 'plum %'; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 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 | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 6 rows in Set (0.00 SEC)Copy the code

not between and

Requirements:

Query the users whose age is not between 20 and 30

Statement:

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

---

not exists

The NOT EXISTS table, consistent with the usage of EXISTS, is used to determine whether the result of the current WHERE clause should be returned. Not exists and exists operate on a subquery, returning true and false to the parent;

Example syntax:

The SELECT... FROM table WHERE EXISTS (subquery) SELECT... FROM table WHERE NOT EXISTS (subquery)Copy the code

To demonstrate the effect, we create a simple order table order with the following table construction terms and data:

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 '主键', 'number' varchar(255) CHARACTER SET utf8 COLLATE UTf8_general_ci NOT NULL COMMENT '数 字 ', '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-2021-11-10 22:37:19', 1, 250.00,' 21-11-10 22:37:19'); SET FOREIGN_KEY_CHECKS = 1;Copy the code

Note: since order is the MySQL keyword, it is not recommended to create a table named order. I use order to explain how to solve this problem.

mysql> select * from `order`; +----+--------------------+---------+--------+---------------------+ | id | number | user_id | price | create_date | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | the DD - 20211110-000001 | 1 | | 250.00 2021-11-10 22:37:19 | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

As you can see, order is decorated with ‘so that MySQL does not parse it as a keyword. MySQL will throw an exception if not added.

Returning to the subject, we now use exists for the query

Requirements:

Query information about users who have placed orders

Statement:

mysql> select * from user where exists(select id from `order` where user_id = user.id); +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum | | 1 | 18 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.00 SEC)Copy the code

In this case, if we want to query the information of an unordered user, we only need to use not exists

mysql> select * from user where not exists (select id from `order` where user_id = user.id); +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | 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 | | | 0 + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 6 rows in Set (0.00 SEC)Copy the code

2.5 Operator order

There are several operators mentioned above, but many cases require multiple operators to be used together. In this case, we need to pay attention to the order of the operators.

For example, the following requirements:

Select * from user table where age > 20 or gender is male and name is not equal to username.

Statement:

mysql> select * from user where age > 20 or sex = 1 and name ! = '3 '; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 1 | | | | 2 threes | | 1 | | 3 | li si 38 | 1 | | | | 4 fifty and 25 | 1 | | | | 6 notoginseng 37 | | | + 1 - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- - + 5 rows in the set (0.00 SEC)Copy the code

MySQL > select * from ‘sex = 1’ and ‘name’ where ‘sex = 1’ and ‘name! Or age > 20; Age > 20 = age > 20 = age > 20 All you need to do to solve this problem is enclose the OR statement in parentheses.

mysql> select * from user where (age > 20 or sex = 1) and name ! = '3 '; +, + + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- + | | id name | age | sex | + - + -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- + | 1 | plum 18 | 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

At this time, the returned data of the query does not contain Zhang SAN.

Therefore, when writing SQL, we can get into the habit of using parentheses to group operators and avoid the error risk of using the default order.