make a website-SQL Advanced Filtering Query Usage
SQLinquiry statement is often used frequently in database operations， For stronger filter control， Sometimes simple filtering simply doesn't work to filter the data， This is where we need to use advanced filteringAND harmonyOR、IN harmonyNOT operator。 Today do website editorial will discuss with you aboutsql Advanced filtering statement usage...
statement：SELECT * FROM dbo.Products WHERE prod_name='king doll' AND prod_price>8;
--Retrieve dbo. All columns in the Products table, filtered by all items manufactured by supplier king doll at a price greater than 8.
Caution.-- The characters that follow are this statement annotations to， this article statement There are two conditions， use respectivelyAND Key words linked together， And the filtered results must satisfy both conditions， If only one of these is satisfied the data will not be retrieved；
For example, the price corresponding to the known supplier king doll manufacturing goods is $8. Now change the price of the goods to less than 8 or the supplier to king add and conclude that no data will be retrieved because it does not make sense to use the AND operator to satisfy only one condition.
OR operator (or)
statement：SELECT * FROM dbo.Products WHERE prod_name='king doll' OR prod_price>8;
--Retrieve dbo. All columns in the Products table, filtered by all values of items with a manufacturing price greater than 8 by the supplier king doll.
Caution.-- The point to make here is thatOR operator is the same as theAND The operator differs in that as long as one of the conditions is met， The values are then retrieved， for example： By supplierking doll Manufacturing price less than8 goods or by the supplierking add Manufacturing price is greater than8 of the goods as long as one of the conditions is met， The data is then retrieved；
Order of values (mix of AND and OR)
Let's start with an example： The price was retrieved as8 More than $， and byking doll perhapsQueen dool All products manufactured， We enter statement：
SELECT * FROM dbo.Products WHERE prod_name='king doll' OR prod_name='Queen dool' AND prod_price>8;
The results were obtained as follows.
Now change the value of prod_price greater than 8 to greater than 10 and see what happens.
The value now retrieved is1 go， Next inprod_name='king doll' OR prod_name='Queen dool' statement Add the brackets， Look how that turned out.：
There's nothing left this time.
Now explain why: it's actually caused by operators being combined incorrectly, because the order in which AND is evaluated in the database system takes precedence over the order in which OR is evaluated.
The order of the first chart is： Implement firstprod_name='Queen dool' AND prod_price>10 data before executing theprod_name='king doll' OR prod_name='Queen dool' data， Since the first condition and the first2 The conditions are met so two lines of values appear。
The order of the second chart is also： Implement firstprod_name='Queen dool' AND prod_price>10 data before executing theprod_name='king doll' OR prod_name='Queen dool' data， However, since the first condition is not met，Queen dool The corresponding value is not greater than10 values， So only the first2 Data for each condition。
The order of the third chart is： First execute the bracketed， Then after executingAND prod_price>10， (located) atSQL The bracketed content in the database will be executed by the system first， Since the result only satisfies the condition in parentheses， but does not satisfy the conditions outside the brackets， owing toprod_price The value of9.49 less than, <10， So nothing is showing.。
IN operator (specifying a range of conditions)
statement：SELECT * FROM dbo.Products WHERE prod_name IN ('king doll' ,'Queen dool')
--Retrieve dbo. All columns in the Products table, filtered by items manufactured by the suppliers king doll and queen dool.
Caution. It actually functions the same way asOR the same as， But it's faster and cleaner to execute， The biggest advantage is the ability to include otherSELECT statement， The ability to more dynamically buildWHERE expressions。
NOT operator (negates any condition that follows it)
statement：SELECT * FROM dbo.Products WHERE NOT prod_name='king doll';
--Retrieve dbo. All columns in the Products table, with a filter condition of negativeking doll all values contained in the supplier.
Previous Top Articles.
# Make a website - How to make charts in PHP using JpGraph
# Do the website - recommend 3 kinds of CSS, JS merge the way
# Make a website - How to make a web page with DIV+CSS
# Make a Website - 3 Foreign VPS Hosting Providers Comparison
# Make a website - several ways to position anchors within a page
# Making a website - How to reduce a design to a web page
# Make a Website - Object-Oriented Process-Oriented Difference
# Making a website - 10 essential website performance testing tools (recommended)
Do website public platform (zwangz888) daily for you to share original web development information, development experience, to charge your skills. We look forward to your attention and sharing, and welcome your comments to make us a little better every day!
Recommended>>
1、100 Days of Machine Learning Day 1403 MultiClass Classification MultiLabel Classification MultiOutput Classification
2、Flyme 7 which Meizu users have been waiting for more than a year is officially released more than 15 models can be updated immediately
3、The server side is heavily CLOSE
4、Musk Model 3 is approaching 5000 units per week capacity process is hell
5、A lazy programmers tool