SQL Server: Reading notes from the book of "The Must-know-all" (iv)

4th lessonFiltering Data

4.1 Using the WHERE clause: filtering

Retrieving only the data you need requires you to specify the search criteria (that is, the filtering criteria).

The WHERE clause is given after the table name (FROM clause) and is filtered according to the criteria specified in the WHERE clause.

SELECT Prod_name, Prod_price  from  Products WHERE = 3.49 ; --     Analysis: Retrieving two columns from the Products table, returning only rows with a Prod_price value of 3.49

　　"Hint" SQL filtering and application filtering:

Data can also be filtered at the application level. However, the data can be filtered more quickly after the database is optimized, and if the client application (or development language) processes the database, it will greatly affect the performance of the application and make the application created completely non-scalable. In addition, it can lead to a waste of network bandwidth.

　　"Attention" Location of the WHERE clause:

When you use both the ORDER BY and WHERE clauses, the order by is located in the where.

4.2 WHERE clause operator

--     ===== table 4-1 WHERE clause operator =====- -     operator    description            operator    Description --     =        equal to            >        greater than - -    <>        not equal to        >=        greater than equals -- !     =        not        equal        to!> --    <        less than            between    Between the specified two values - -    <=        less than or equals        is null null    value --    ! <        not less than

4.2.1 Checking a single value

List all items priced at less than $10:

SELECT Prod_name, Prod_price  from  Products WHERE < ten;

　　

Retrieve all products with a price less than or equal to $10: (Result same)

SELECT Prod_name, Prod_price  from  Products WHERE <= ten;

4.2.2 Mismatch Check

List all products not manufactured by supplier DLL01 :

SELECT vend_id, Prod_name  from  Products WHERE <> ' DLL01 ';

-- The     second way SELECT  vend_id, prod_name   from Products WHERE! ='DLL01';

4.2.3 Range Value Check (between)

Retrieve all products priced between $5 and $10 :

SELECT Prod_name, Prod_price  from  Products WHERE between 5  and Ten

4.2.4 null value check (NULL)

When a column does not contain a value, it is said to contain a null value of NULL

NULL: No value, unlike field contains 0, empty string, or contains only spaces

　　"Note" determines whether the value is null, cannot be simply checked for =null, should use is null

To retrieve a customer without e-mail:

SELECT Cust_name, Cust_email  from Customers WHERE  is NULL

4.3 Summary

• Using WHERE to filter data
• Learning to test equality, inequality, greater than, less than, the range of values and NULL values, etc.

Sequel:

SQL Server: Reading notes from the book of "The Must-know-all" (iii)

SQL Server: Reading notes from the book of "The Must-know-all" (iv)