"MySQL is better than know." Seventh chapter data filtering

1. Combining WHERE clauses

Operator (operator): a keyword used to join or change clauses in a WHERE clause.

2, and operator

Input: SELECT prod_id,prod_price,prod_name

From Products

WHERE vend_id = 1003 and Prod_price <= 10;

Analysis: Display three columns of--prod_id,prod_price,prod_name, the filter is, the Supplier ID is 1003, the price is less than 10. and is a filter condition that needs to be met at the same time.

3, or operator

Input: SELECT Prod_name,prod_price

From Products

WHERE vend_id = 1002 OR vend_id = 1003;

Analysis: Displays two columns of--prod_name,prod_price, the filter is: The vendor ID is 1002 or 1003. The OR keyword satisfies one of these conditions.

4. Order of calculation (combination direction)

The WHERE clause can contain any number of and, or operators for complex and advanced filtering, but the binding order is a problem.

For this reason, the SQL statement specifies that the and priority is higher than or, that is, the AND keyword is filtered first.

Input: SELECT prod_name, Prod_price

From Products

WHERE vend_id = 1002 OR vend_id = 1003 and Prod_price >= 10

Analysis: Show two columns--prod_name, Prod_price, filter condition is the supplier is 1003 and the commodity price is more than 10 or the supplier is 1002 and the commodity price is arbitrary.

The expectation of the previous example is that the suppliers are 1002 or 1003, and their prices are greater than 10. To achieve this expectation, we must use parentheses to forcibly change the priority of and and OR.

Input: SELECT prod_name, Prod_price

From Products

WHERE (vend_id = 1002 OR vend_id = 1003) and Prod_price >= 10

Analysis: The parentheses have the highest precedence, so first filter in parentheses, that is, only the supplier is 1002 or 1003 of the goods name and price, and then the prices of more than 10 filter.

Note: It is recommended that you use parentheses more to achieve ambiguity elimination.

5, in operator

The in operator is used to specify a range of conditions, and each condition in the range can be matched.

Input: SELECT Prod_name,prod_price

From Products

WHERE vend_id in (1002,1003)

ORDER by Prod_name;

Analysis: Show two columns-the product name and price, the filter is that the supplier ID must be within the range of 1002 to 1003, sorted by the Prod_name column, sorted alphabetically in ascending order (A-Z).

6, not operator

Any conditions that are followed after denying it

Input: SELECT Prod_name,prod_price

From Products

WHERE vend_id not in (1002,1003)

ORDER by Prod_name;

Analysis: Show two columns--item name and price, filter criteria, except for other vendors with vendor ID 1002 to 1003, sorting criteria, sorted by Prod_name, alphabetical ascending order (A-Z).

"MySQL is better than know." Seventh chapter data filtering