Using SQL phần 2

Figure 3.6: Products where ProductName is like Cha% The next SELECT statement uses the LIKE operator to retrieve products where the ProductName column
Nội dung trích xuất từ tài liệu:
Using SQL phần 2Figure 3.6: Products where ProductName is like Cha%The next SELECT statement uses the LIKE operator to retrieve products where theProductName column is like [ABC]%:SELECT ProductID, ProductNameFROM ProductsWHERE ProductName LIKE [ABC]%;Figure 3.7 shows the results of this SELECT statement. LIKE [ABC]% matchesproducts with a name that starts with any of the letters A, B, or C, and ends with anynumber of characters.Figure 3.7: Products where ProductName is like [ABC]%The next SELECT statement uses the LIKE operator to retrieve products where theProductName column is like [^ABC]%:SELECT ProductID, ProductNameFROM ProductsWHERE ProductName LIKE [^ABC]%;Figure 3.8 shows the results of this SELECT statement. LIKE [^ABC]% matchesproducts with names that dont start with any of the letters A, B, or C, and end with anynumber of characters.Figure 3.8: Products where ProductName is like [^ABC]%The next SELECT statement uses the LIKE operator to retrieve products where theProductName column is like [A-E]%:SELECT ProductID, ProductNameFROM ProductsWHERE ProductName LIKE [A-E]%;Figure 3.9 shows the results of this SELECT statement. LIKE [A-E]% matches productswith names that start with any of the letters A through E, and end with any number ofcharacters.Figure 3.9: Products where ProductName is like [A-E]%Specifying a List of ValuesYou use the IN operator in a WHERE clause to retrieve rows with columns that containvalues in a specified list. For example, the following SELECT statement uses the INoperator to retrieve products with a ProductID of 1, 2, 5, 15, 20, 45, or 50:SELECT ProductID, ProductName, QuantityPerUnit, UnitPriceFROM ProductsWHERE ProductID IN (1, 2, 5, 15, 20, 45, 50);Heres another example that displays the OrderID column from the Orders table for therows where the CustomerID column is in the list retrieved by a subquery; the subqueryretrieves the CustomerID column from the Customers table where the CompanyName islike Fu%:SELECT OrderIDFROM OrdersWHERE CustomerID IN ( SELECT CustomerID FROM Customers WHERE CompanyName LIKE Fu%);The results of the subquery are used in the outer query.Specifying a Range of ValuesYou use the BETWEEN operator in a WHERE clause to retrieve rows with columns thatcontain values in a specified range. For example, the following SELECT statement usesthe BETWEEN operator to retrieve products with a ProductID between 1 and 12:SELECT ProductID, ProductName, QuantityPerUnit, UnitPriceFROM ProductsWHERE ProductID BETWEEN 1 AND 12;Heres another example that displays the OrderID column for the rows from the Orderstable where the OrderDate is between 1996-07-04 and 1996-07-08:SELECT OrderIDFROM OrdersWHERE OrderDate BETWEEN 1996-07-04 AND 1996-07-08;Reversing the Meaning of an OperatorYou use the NOT keyword with an operator in a WHERE clause to reverse the meaningof that operator. For example, the following SELECT statement uses the NOT keywordto reverse the meaning of the BETWEEN operator:SELECT ProductID, ProductName, QuantityPerUnit, UnitPriceFROM ProductsWHERE ProductID NOT BETWEEN 1 AND 12;Note You can use the NOT keyword to reverse other operators, for example, NOT LIKE, NOT IN.Retrieving Rows with Columns Set to NullEarlier, I mentioned that columns can contain null values. A null value is different from ablank string or zero: A null value represents a value that hasnt been set, or is unknown.You can use the IS NULL operator in a WHERE clause to determine if a column containsa null value. For example, the following SELECT statement uses the IS NULL operatorto retrieve customers where the Fax column contains a null value:SELECT CustomerID, CompanyName, FaxFROM CustomersWHERE Fax IS NULL;Figure 3.10 shows the results of this SELECT statement.Figure 3.10: Using the IS NULL operator to retrieve customers where Fax contains a nullvalueAs you can see, null values are displayed as NULL in the Query Analyzer.Specifying Multiple ConditionsYou can use the logical operators shown in Table 3.3 to specify multiple conditions in aWHERE clause. Table 3.3: LOGICAL OPERATORSOPERATOR DESCRIPTIONa AND b Evaluates to true when a and b are both truea OR b Evaluates to true when either a or b are trueNOT a Evaluates to true if a is false, and false if a is trueFor example, the following SELECT statement uses the AND operator to retrieveproducts where the UnitsInStock column is less than 10 and the ReorderLevel column isless than or equal to 20:SELECT ProductID, ProductName, UnitsInStock, ReorderLevelFROM ProductsWHERE UnitsInStock < 10AND ReorderLevel Figure 3.11: Using the AND operator to retrieve products where UnitsInStock is less than10 and ReorderLevel is less than or equal to 20In the next example, the SELECT statement uses the OR operator to retrieve productswhere either the UnitsInStock column is less than 10 or the ReorderLevel column is lessthan or equal to 20:SELECT ProductID, ProductName, UnitsInStock, ReorderLevelFROM ProductsWHERE UnitsInStock < 10OR ReorderLevel The next SELECT statement uses the NOT operator to retrieve products where theUnitsInStock column is not less than 10:SELECT ProductID, ProductName, UnitsInStock, ReorderLevelFROM ProductsWHERE NOT (UnitsInStock < 10);Sorting RowsYou can use the ORDER BY clause to sort rows retrieved from the database. You specifythe column (or columns) to sort in the ORDER BY clause. By default, rows are sorted inascending order. For example, the following SELECT statement orders the rows usingthe ProductName column:SELECT ProductID, ProductName, UnitsInStock, ReorderLevelFROM ProductsORDER BY ProductName;Figure 3.13 shows the results of this SELECT statement. As you can see, the rows areordered in ascending order using the ProductName c ...