Shulou(Shulou.com)06/01 Report--

China's mobile Internet has entered a stage of rapid development, and Internet talents have been paid more and more attention by enterprises, among which PHP developer is one of them. In some emerging enterprises and software development enterprises, such as Internet tourism, finance, catering, entertainment, social and so on, PHP development position occupies a relatively core position. Today, the technical knowledge shared with you is how to achieve multi-field filtering in mysql database.

1. Multi-field filtering query

Analogy reality: check the information of colleagues in the company who are registered in Beijing, are over 30 years old, and whose gender is male.

Query scenario: query the goods whose name is' King doll' and the price is 9.49.

Query SQL:

SELECT prod_id, prod_name, prod_price F ROM Products W HERE prod_name = 'King doll' AND prod_price = 9.49

Query results:

two。 Multi-field filter connection

There are multiple fields in the WHERE sentence for query filtering. How are the filter conditions connected? MySql allows multiple WHERE sentences to be filtered, which can be connected using AND or OR!

AND connection analogy reality: flying Eagle Primary School Class 2 5 years tall more than 1.3 meters, no glasses of male students to run on the playground. According to the above description, it can be concluded that students who are more than 1.3 meters tall, do not wear glasses and boys need to run on the playground only if they are not satisfied.

OR connects the analogy reality: students in Class 2 of Flying Eagle Primary School who are more than 1.3 meters tall or do not wear glasses go for a run on the playground. According to the above description, it can be concluded that students who are more than 1.3 meters tall or do not wear glasses should run on the playground as long as they meet either of the two conditions, that is, students who are more than 1.3 meters tall should run, and students who do not wear glasses should run. You need to go to the playground as long as you meet any of the conditions.

2.1AND operator

The function of AND operator? Used to indicate that the rows that meet all given criteria are retrieved. If you need to meet multiple filter conditions at the same time, you only need to add AND between the filter conditions.

Test case: query the order data in which the unit price is between 2 and 5 and the quantity of goods is greater than or equal to 10.

Test SQL:

SELECT * FROM OrderItems W HERE item_price B ETWEEN 2 AND 5 AND quantity > = 10 ORDER BY order_num DESC

Test results:

The results show that if there are multiple filter conditions that need to be met at the same time, then only need to add the AND keyword between the filter conditions, and the number of query conditions is unlimited in theory!

2.2OR operator

What is the function of OR operator? Used to indicate the retrieval of rows that meet any given condition. If you have multiple filter conditions, you need to add OR between the filter conditions.

Test case: query the order data where the unit price of goods is between 3 and 5, or the quantity of goods is greater than or equal to 200.

Test SQL:

SELECT * F ROM OrderItems W HERE item_price B ETWEEN 3 AND 5 OR quantity > = 200 ORDER BY order_num DESC

Test results:

The results show that as long as the unit price of the goods is between [3], or the quantity of goods is greater than or equal to 200, the conditions are met.

2.3AND and OR are compared.

AND must meet all the conditions, and OR only needs to meet any one of them.

Analogy understanding: now there is a group of black and white male penguins, if you take away the black female penguin, that is, SELECT * FROM penguin WHERE color = black AND sex = female, you can't query the penguin, because the two conditions must be met at the same time, sex = female; if you want to take away the penguin whose color is white or gender is female, that is, SELECT * FROM penguin WHERE color = white OR sex = female, then you can query out the white male penguin.

2.4 execution order

If AND and OR are used in combination for complex data filtering, there will be a problem of execution order.

Analogical reality: for example, if there is a mixed operation of parentheses and four in primary school, then the operation must meet a certain order; for example, an employee in a company with a monthly salary of more than 10w and a position of management or developer.

Test scenario: inquire about the order with unit price 3.49 and item number BNBG01 or BNBG03.

Analysis and thinking: query the unit price of goods must meet 3.49, and the commodity number only needs to meet any one of BNBG01 or BNBG03.

Test SQL:

SELECT * F ROM OrderItems W HERE item_price B ETWEEN 3 AND 5 OR quantity > = 200 ORDER BY order_num DESC

Test results:

Result analysis:

The data did not meet our expectations. Why? Unit price must be equal to 3.49.

In the SQL world, the AND operator takes precedence over the OR operator, just as multiplication takes precedence over addition and subtraction.

The result of the actual query of SELECT * F ROM OrderItems W HERE item_price=3.49 AND prod_id='BNBG01' OR prod_id='BNBG03'; is that the unit price is equal to 3.49 and the item number is' BNBG01', or the item number is' BNBG03', so it is not the same as we expected!

How to solve the order problem of AND and OR? Use parentheses to explicitly group the appropriate operations.

Test SQL:

SELECT * F ROM OrderItems W HERE item_price= 3.49 AND (prod_id= 'BNBG01' OR prod_id=' BNBG03')

Test results:

Result analysis:

As you can see from the query results, (prod_id='BNBG01' OR prod_id='BNBG03') becomes an execution unit as a whole.

Parentheses take precedence over AND,AND and OR

If you have too many query filtering conditions and use AND or OR, you should use parentheses to explicitly group operations instead of calculating the order by default! The advantage of using parentheses is to eliminate ambiguity and enhance readability.

Summary

The above is the editor to introduce to you the Mysql database to achieve multi-field filtering methods, I hope to help you, if you have any questions, please leave me a message, the editor will reply you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.